# PackageDaoImpl.java

    package com.mycim.server.workorder.dao.impl;

    import com.mycim.framework.jdbc.BatchPreparedStatementSetter;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.math.NumberUtils;
    import com.mycim.server.workorder.dao.PackageDao;
    import com.mycim.valueobject.erp.lotScrapShip.ErpPackage;
    import com.mycim.valueobject.erp.lotScrapShip.ErpPackageStatus;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.stereotype.Repository;

    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;

    @Repository
    public class PackageDaoImpl implements PackageDao {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public void insertPackage(ErpPackage erpPackage) {
            String sql = "INSERT INTO ERP_PACKAGE (TRANS_RRN,LPN,WORK_ORDER_ID,LOT_ID,LOT_RRN,PRODUCT_ID," + " BIN," +
                    "PRINT_COUNT,QUANTITY,LOCATOR,SUB_INVENTORY,BIN_PRODUCT_NAME,PACKAGE_TYPE," + "SEND_FLAG," +
                    "RECEIVE_FLAG)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            jdbcTemplate.update(sql, new Object[]{erpPackage.getTransRrn(), NumberUtils.toLong(erpPackage.getLpn(),
                                                                                               0),
                    erpPackage.getWorkOrderId(), erpPackage.getLotId(), NumberUtils.toLong(
                    erpPackage.getLotRrn(),
                    0), erpPackage.getProductId(), erpPackage.getBin(), erpPackage.getPrintCount(),
                    erpPackage.getQuantity(), erpPackage.getLocator(), erpPackage.getSubInventory(),
                    erpPackage.getBinProductName(), erpPackage.getPackageType(), erpPackage.getSendFlag(),
                    erpPackage.getReceiveFlag()});
        }

        @Override
        public void insertPackageUnitList(List<ErpPackage.UnitInfo> unitList, long transRrn) {

            String sql = "INSERT INTO ERP_PACKAGE_UNIT (TRANS_RRN,LOT_RRN,LOT_ID,UNIT_ID,UNIT_RRN,BIN,PACKAGE_QUANTITY," +
                    "SOURCE_QUANTITY, " + " SOURCE_WAFER_ID) VALUES (?,?,?,?,?,?,?,?,?)";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, transRrn);
                    ps.setLong(2, unitList.get(i).getLotRrn());
                    ps.setString(3, unitList.get(i).getLotId());
                    ps.setString(4, unitList.get(i).getUnitId());
                    ps.setLong(5, unitList.get(i).getUnitRrn());
                    ps.setString(6, unitList.get(i).getBin());
                    ps.setInt(7, unitList.get(i).getPackageQuantity());
                    ps.setInt(8, unitList.get(i).getSourceQuantity());
                    ps.setString(9, unitList.get(i).getSourceWaferId());
                }

                @Override
                public int getBatchSize() {
                    return unitList.size();
                }
            });

        }

        @Override
        public Page getPackage(Page page, String packageType, String lotId, String productId) {
            StringBuffer sbf = new StringBuffer("SELECT * FROM ERP_PACKAGE WHERE 1=1 ");
            List<Object> args = new ArrayList<Object>();
            if (StringUtils.isNotEmptyTrim(packageType)) {
                sbf.append("and PACKAGE_TYPE=?");
                args.add(packageType);
            }
            if (StringUtils.isNotEmptyTrim(lotId)) {
                sbf.append("and lot_id like ?");
                args.add(lotId);
            }
            if (StringUtils.isNotEmptyTrim(productId)) {
                sbf.append("and product_id like ?");
                args.add(productId);
            }
            sbf.append(" ORDER BY TRANS_RRN DESC");
            return jdbcTemplate
                    .queryForPage(page, sbf.toString(), args.toArray(), new BeanPropertyRowMapper(ErpPackage.class));
        }

        @Override
        public List<ErpPackage.UnitInfo> getPackageDetails(Long transRrn) {
            String sql = "SELECT * FROM ERP_PACKAGE_UNIT WHERE TRANS_RRN=?";
            return jdbcTemplate.query(sql, new Object[]{transRrn}, new BeanPropertyRowMapper(ErpPackage.UnitInfo.class));
        }

        @Override
        public List<ErpPackage> getPackage(long transRrn, String packageType) {
            String sql = "SELECT * FROM ERP_PACKAGE WHERE TRANS_RRN >? AND PACKAGE_TYPE=? ORDER BY TRANS_RRN";
            return jdbcTemplate
                    .query(sql, new Object[]{transRrn, packageType}, new BeanPropertyRowMapper(ErpPackage.class));
        }

        @Override
        public int getPackageDieCount(long workorderRrn, String packageType) {
            String sql = "SELECT SUM(PU.SOURCE_QUANTITY) FROM ERP_PACKAGE P JOIN ERP_PACKAGE_UNIT PU ON P.TRANS_RRN=PU" +
                    ".TRANS_RRN" + " JOIN WORKORDER W ON W.WORKORDER_ID=P.WORK_ORDER_ID WHERE W.WORKORDER_RRN=? " +
                    "AND P" + ".PACKAGE_TYPE=? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{workorderRrn, packageType}, Integer.class);
        }

        @Override
        public int getErpPackageCount(long lotRrn) {
            String sql = "SELECT COUNT(*) FROM ERP_PACKAGE WHERE LOT_RRN=?";
            return jdbcTemplate.queryForObject(sql, new Object[]{lotRrn}, Integer.class);
        }

        @Override
        public String getMaxLpnNum(String lpnPrex) {
            String sql = "SELECT MAX(LPN) FROM ERP_PACKAGE WHERE LPN LIKE ? ";
            long i = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lpnPrex}, long.class);
            if (i == 0) {
                return null;
            } else {
                return (new Long(i)).toString();
            }
        }

        @Override
        public void setPackageSendFlagY(List<ErpPackage> list) {
            String sql = "UPDATE ERP_PACKAGE SET SEND_FLAG='Y' WHERE TRANS_RRN=?";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, list.get(i).getTransRrn());
                }

                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });
        }

        @Override
        public void updatePackageFlag(List<ErpPackageStatus> list) {
            String sql = "UPDATE ERP_PACKAGE SET RECEIVE_FLAG=? WHERE TRANS_RRN=?";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setString(1, list.get(i).getReceiveFlag());
                    ps.setLong(2, list.get(i).getTransRrn());
                }

                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });
        }

    }