# LotInventoryDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.server.asm.dao.LotInventoryDAO;
    import com.mycim.server.asm.dao.mapper.LotInventoryDORowMapper;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.inv.LotInventoryDO;
    import com.mycim.valueobject.inv.value.WarehouseNames;
    import com.mycim.valueobject.wip.LotConsumesMaterialHistory;
    import org.apache.commons.collections.CollectionUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.*;

    /**
     * @author shijie.deng
     * @date 2019/8/30
     **/
    @Repository
    public class LotInventoryDAOImpl implements LotInventoryDAO {
        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public void insertLotInventory(LotInventoryDO lotInventory) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");

            sql.append(DataBaseNames.LOT_INVENTORY);
            sql.append(" (LOT_NUMBER, ITEM_RRN, MATERIAL_TYPE, WAREHOUSE_RRN, WAREHOUSE_ID, LOCATION_RRN, ");
            sql.append(" RECEIPT_QTY, ISSUE_QTY, ADJUST_QTY, ");
            sql.append(" RECEIPT_DATE, EXPIRATION_DATE, RE_TEST_DATE, QA_APPROVAL_DATE, ");
            sql.append(" STATUS, AVAILABILITY_DAYS, ISSUE_PRIORITY, ");
            sql.append(" WAFERPNTYPE, WAFERLATTICEORIENTATION, WAFERRESISTANCE, WAFEREXTENSION, WAFERSUPPLIER) ");
            sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

            Object[] args = new Object[]{lotInventory.getLotNumber(), lotInventory.getItemRrn(),
                    lotInventory.getMaterialType(), lotInventory.getWarehouseRrn(), lotInventory.getWarehouseId(),
                    lotInventory.getLocationRrn(), lotInventory.getReceiptQty(), lotInventory.getIssueQty(),
                    lotInventory.getAdjustQty(), lotInventory.getReceiptDate(), lotInventory.getExpirationDate(),
                    lotInventory.getReTestDate(), lotInventory.getQaApprovalDate(), lotInventory.getStatus(),
                    lotInventory.getAvailabilityDays(), lotInventory.getIssuePriority(), lotInventory.getWaferPNType(),
                    lotInventory.getWaferLatticeOrientation(), lotInventory.getWaferResistance(),
                    lotInventory.getWaferExtension(), lotInventory.getWaferSupplier()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void deleteLotInventory(String lotNumber, Long itemRrn, Long warehouseRrn) {
            String sql = "DELETE FROM " + DataBaseNames.LOT_INVENTORY + " WHERE LOT_NUMBER = ? AND ITEM_RRN = ? AND " +
                    "WAREHOUSE_RRN = ? ";

            jdbcTemplate.update(sql, lotNumber, itemRrn, warehouseRrn);
        }

        @Override
        public void updateLotInventory(LotInventoryDO lotInventory) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(DataBaseNames.LOT_INVENTORY).append(" SET ");
            sql.append(" EXPIRATION_DATE = ?, AVAILABILITY_DAYS = ? ");
            sql.append(" WHERE LOT_NUMBER = ? AND ITEM_RRN = ?  AND WAREHOUSE_RRN = ? ");

            Object[] args = new Object[]{lotInventory.getExpirationDate(), lotInventory.getAvailabilityDays(),
                    lotInventory.getLotNumber(), lotInventory.getItemRrn(), lotInventory.getWarehouseRrn()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void updateLotInventoryForQty(LotInventoryDO lotInventory) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(DataBaseNames.LOT_INVENTORY).append(" SET ");
            sql.append(" RECEIPT_QTY = NVL(RECEIPT_QTY, 0) + ?, ");
            sql.append(" ISSUE_QTY = NVL(ISSUE_QTY, 0) + ?, ");
            sql.append(" ADJUST_QTY = NVL(ADJUST_QTY, 0) + ?, ");
            sql.append(" STATUS = ? ");
            sql.append(" WHERE LOT_NUMBER = ? AND ITEM_RRN = ?  AND WAREHOUSE_RRN = ? ");

            Object[] args = new Object[]{lotInventory.getReceiptQty(), lotInventory.getIssueQty(),
                    lotInventory.getAdjustQty(), lotInventory.getStatus(), lotInventory.getLotNumber(),
                    lotInventory.getItemRrn(), lotInventory.getWarehouseRrn()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void insertLotInventoryExt(LotInventoryDO lotInventory) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");

            sql.append(DataBaseNames.LOT_INVENTORY_EXT);
            sql.append(" (ITEM_RRN, WAREHOUSE_RRN, LOT_NUMBER, ");
            sql.append(" CUSTOMER_ID, OFF_ORIENTATION, TYPE_DOPANT, THICKNESS, PRODUCTION_DATE, VALID_TERM, ");
            sql.append(" INCOMING_DATE, CHECK_DATE, CHECK_USER, CHECK_RESULT,");
            sql.append(" ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3, ATTRIBUTE_DATA4, ATTRIBUTE_DATA5) ");
            sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

            Object[] args = new Object[]{lotInventory.getItemRrn(), lotInventory.getWarehouseRrn(),
                    lotInventory.getLotNumber(), lotInventory.getCustomerId(), lotInventory.getOffOrientation(),
                    lotInventory.getTypeDopant(), lotInventory.getThickness(), lotInventory.getProductionDate(),
                    lotInventory.getValidTerm(), lotInventory.getIncomingDate(), lotInventory.getCheckDate(),
                    lotInventory.getCheckUser(), lotInventory.getCheckResult(), lotInventory.getAttributeData1(),
                    lotInventory.getAttributeData2(), lotInventory.getAttributeData3(), lotInventory.getAttributeData4(),
                    lotInventory.getAttributeData5()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void deleteLotInventoryExt(String lotNumber, Long itemRrn, Long warehouseRrn) {
            String sql = "DELETE FROM " + DataBaseNames.LOT_INVENTORY_EXT + " WHERE LOT_NUMBER = ? AND ITEM_RRN = ? AND " +
                    "WAREHOUSE_RRN = ? ";

            jdbcTemplate.update(sql, lotNumber, itemRrn, warehouseRrn);
        }

        @Override
        public void updateLotInventoryExt(LotInventoryDO lotInventory) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(DataBaseNames.LOT_INVENTORY_EXT).append(" SET ");
            sql.append(" CUSTOMER_ID = ?, OFF_ORIENTATION = ?, TYPE_DOPANT = ?, THICKNESS = ?, PRODUCTION_DATE = " + "?, " +
                               "VALID_TERM = ?, ");
            sql.append(" INCOMING_DATE = ?, CHECK_DATE = ?, CHECK_USER = ?, CHECK_RESULT = ?, ");
            sql.append(" ATTRIBUTE_DATA1 = ?, ATTRIBUTE_DATA2 = ?, ATTRIBUTE_DATA3 = ?, ATTRIBUTE_DATA4 = ?, " +
                               "ATTRIBUTE_DATA5 = ? ");
            sql.append(" WHERE LOT_NUMBER = ? AND ITEM_RRN = ?  AND WAREHOUSE_RRN = ? ");

            Object[] args = new Object[]{lotInventory.getCustomerId(), lotInventory.getOffOrientation(),
                    lotInventory.getTypeDopant(), lotInventory.getThickness(), lotInventory.getProductionDate(),
                    lotInventory.getValidTerm(), lotInventory.getIncomingDate(), lotInventory.getCheckDate(),
                    lotInventory.getCheckUser(), lotInventory.getCheckResult(), lotInventory.getAttributeData1(),
                    lotInventory.getAttributeData2(), lotInventory.getAttributeData3(), lotInventory.getAttributeData4(),
                    lotInventory.getAttributeData5(), lotInventory.getLotNumber(), lotInventory.getItemRrn(),
                    lotInventory.getWarehouseRrn()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public LotInventoryDO getLotInventoryAndExt(String lotNumber, Long itemRrn, Long warehouseRrn) {
            Object[] args = new Object[]{lotNumber, itemRrn, warehouseRrn};
            String sql = getLotInventoryBaseSql()  + " AND LI.LOT_NUMBER = ? AND LI.ITEM_RRN = ? AND LI.WAREHOUSE_RRN = ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, args, new LotInventoryDORowMapper());
        }

        private String getLotInventoryBaseSql() {
            return "SELECT LI.LOT_NUMBER, LI.ITEM_RRN, LI.MATERIAL_TYPE, LI.WAREHOUSE_RRN, LI.WAREHOUSE_ID, LI.LOCATION_RRN, "
                    + " LI.RECEIPT_QTY, LI.ISSUE_QTY, LI.ADJUST_QTY, LI.RECEIPT_DATE, LI.EXPIRATION_DATE, LI.RE_TEST_DATE, "
                    + " LI.QA_APPROVAL_DATE, LI.STATUS, LI.AVAILABILITY_DAYS, LI.ISSUE_PRIORITY, LI.WAFERPNTYPE, "
                    + " LI.WAFERLATTICEORIENTATION, LI.WAFERRESISTANCE, LI.WAFEREXTENSION, LI.WAFERSUPPLIER, "
                    + " LIE.CUSTOMER_ID, LIE.OFF_ORIENTATION, LIE.TYPE_DOPANT, LIE.THICKNESS, LIE.PRODUCTION_DATE, "
                    + " LIE.VALID_TERM, LIE.INCOMING_DATE, LIE.CHECK_DATE, LIE.CHECK_USER, LIE.CHECK_RESULT, "
                    + " LIE.ATTRIBUTE_DATA1, LIE.ATTRIBUTE_DATA2, LIE.ATTRIBUTE_DATA3, LIE.ATTRIBUTE_DATA4, LIE.ATTRIBUTE_DATA5 "
                    + " FROM " + DataBaseNames.LOT_INVENTORY + " LI, " + DataBaseNames.LOT_INVENTORY_EXT + " LIE "
                    + " WHERE LI.LOT_NUMBER = LIE.LOT_NUMBER AND LI.ITEM_RRN = LIE.ITEM_RRN AND LI.WAREHOUSE_RRN = LIE.WAREHOUSE_RRN ";
        }

        @Override
        public List<LotInventoryDO> getLotInventoryList(Long itemRrn, Long warehouseRrn, String lotNumber,
                                                        String transType) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" li.LOT_NUMBER, li.ITEM_RRN, li.MATERIAL_TYPE, li.WAREHOUSE_RRN, li.WAREHOUSE_ID, li" +
                               ".LOCATION_RRN, ");
            sql.append(" li.RECEIPT_QTY, li.ISSUE_QTY, li.ADJUST_QTY, ");
            sql.append(" li.RECEIPT_DATE, li.EXPIRATION_DATE, li.RE_TEST_DATE, li.QA_APPROVAL_DATE, ");
            sql.append(" li.STATUS, li.AVAILABILITY_DAYS, li.ISSUE_PRIORITY, ");
            sql.append(" li.WAFERPNTYPE, li.WAFERLATTICEORIENTATION, li.WAFERRESISTANCE, li.WAFEREXTENSION, li" +
                               ".WAFERSUPPLIER, ");
            sql.append(
                    " lie.CUSTOMER_ID, lie.OFF_ORIENTATION, lie.TYPE_DOPANT, lie.THICKNESS, lie" + ".PRODUCTION_DATE, lie" +
                            ".VALID_TERM, ");
            sql.append(" lie.INCOMING_DATE, lie.CHECK_DATE, lie.CHECK_USER, lie.CHECK_RESULT, ");
            sql.append(" lie.ATTRIBUTE_DATA1, lie.ATTRIBUTE_DATA2, lie.ATTRIBUTE_DATA3, lie.ATTRIBUTE_DATA4, lie" +
                               ".ATTRIBUTE_DATA5 ");
            sql.append(" FROM ");
            sql.append(DataBaseNames.LOT_INVENTORY).append(" li, ");
            sql.append(DataBaseNames.LOT_INVENTORY_EXT).append(" lie ");
            sql.append(
                    " WHERE li.LOT_NUMBER = lie.LOT_NUMBER AND li.ITEM_RRN = lie.ITEM_RRN AND li.WAREHOUSE_RRN " + "= lie" +
                            ".WAREHOUSE_RRN ");
            sql.append(" AND li.RECEIPT_QTY + li.ADJUST_QTY - li.ISSUE_QTY > 0 ");
            sql.append(" AND li.LOT_NUMBER NOT LIKE '%").append(WarehouseNames.TYPE_RETURN).append("' ");
            sql.append(" AND li.ITEM_RRN = ? AND li.WAREHOUSE_RRN = ? ");

            List<Object> args = new ArrayList<>(Arrays.asList(new Object[]{itemRrn, warehouseRrn}));
            if (StringUtils.isNotEmpty(lotNumber)) {
                sql.append(" AND li.LOT_NUMBER LIKE ? ");
                args.add(lotNumber + "%");
            }

            if (StringUtils.isNotEmpty(transType)) {
                if (StringUtils.equals(transType, WarehouseNames.TYPE_G)) {
                    sql.append(" AND li.LOT_NUMBER NOT LIKE '%").append(WarehouseNames.TYPE_R).append("' ");
                    sql.append(" AND li.LOT_NUMBER NOT LIKE '%").append(WarehouseNames.TYPE_F).append("' ");
                } else {
                    sql.append(" AND li.LOT_NUMBER LIKE '%" + transType + "'");
                }
            }

            sql.append(" ORDER BY li.EXPIRATION_DATE ASC ");

            return jdbcTemplate.query(sql.toString(), args.toArray(), new LotInventoryDORowMapper());
        }

        @Override
        public Page pageLotInventoryList(Page page, Long itemRrn, Long warehouseRrn, String lotNumber) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" li.LOT_NUMBER, li.ITEM_RRN, li.MATERIAL_TYPE, li.WAREHOUSE_RRN, li.WAREHOUSE_ID, li" +
                               ".LOCATION_RRN, ");
            sql.append(" li.RECEIPT_QTY, li.ISSUE_QTY, li.ADJUST_QTY, ");
            sql.append(" li.RECEIPT_DATE, li.EXPIRATION_DATE, li.RE_TEST_DATE, li.QA_APPROVAL_DATE, ");
            sql.append(" li.STATUS, li.AVAILABILITY_DAYS, li.ISSUE_PRIORITY, ");
            sql.append(" li.WAFERPNTYPE, li.WAFERLATTICEORIENTATION, li.WAFERRESISTANCE, li.WAFEREXTENSION, li" +
                               ".WAFERSUPPLIER, ");
            sql.append(
                    " lie.CUSTOMER_ID, lie.OFF_ORIENTATION, lie.TYPE_DOPANT, lie.THICKNESS, lie" + ".PRODUCTION_DATE, lie" +
                            ".VALID_TERM, ");
            sql.append(" lie.INCOMING_DATE, lie.CHECK_DATE, lie.CHECK_USER, lie.CHECK_RESULT, ");
            sql.append(" lie.ATTRIBUTE_DATA1, lie.ATTRIBUTE_DATA2, lie.ATTRIBUTE_DATA3, lie.ATTRIBUTE_DATA4, lie" +
                               ".ATTRIBUTE_DATA5 ");
            sql.append(" FROM ");
            sql.append(DataBaseNames.LOT_INVENTORY).append(" li, ");
            sql.append(DataBaseNames.LOT_INVENTORY_EXT).append(" lie ");
            sql.append(
                    " WHERE li.LOT_NUMBER = lie.LOT_NUMBER AND li.ITEM_RRN = lie.ITEM_RRN AND li.WAREHOUSE_RRN " + "= lie" +
                            ".WAREHOUSE_RRN ");
            sql.append(" AND li.RECEIPT_QTY + li.ADJUST_QTY - li.ISSUE_QTY > 0 ");
            sql.append(" AND li.LOT_NUMBER NOT LIKE '%").append(WarehouseNames.TYPE_RETURN).append("' ");
            sql.append(" AND li.ITEM_RRN = ? AND li.WAREHOUSE_RRN = ? ");

            List<Object> args = new ArrayList<>(Arrays.asList(new Object[]{itemRrn, warehouseRrn}));
            if (StringUtils.isNotEmpty(lotNumber)) {
                sql.append(" AND li.LOT_NUMBER LIKE ? ");
                args.add(lotNumber + "%");
            }

            sql.append(" ORDER BY li.EXPIRATION_DATE ASC ");

            return jdbcTemplate.queryForPage(page, sql.toString(), args.toArray(), new LotInventoryDORowMapper());
        }

        @Override
        public Boolean checkLotInventoryIsExistedInWarehouse(String lotNumber, Long itemRrn, Long warehouseRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" NVL(COUNT(*), 0) ");
            sql.append(" FROM ");
            sql.append(" (SELECT LOT_NUMBER FROM ").append(DataBaseNames.LOT_INVENTORY);
            sql.append(" WHERE LOT_NUMBER = ? AND ITEM_RRN = ? AND WAREHOUSE_RRN = ?  and rownum = 1 ) TAB");
            Object[] ags = new Object[]{lotNumber, itemRrn, warehouseRrn};
            return jdbcTemplate.queryForObject(sql.toString(), ags, Boolean.class);
        }

        @Override
        public List<Map<String, Object>> getLotInventoryTransHistory(Long transRrn) {
            String sql = " SELECT TRANS_RRN,  TRANS_SEQUENCE,  ITEM_RRN,  TRANS_QTY,  TRANS_DATE, " + " WAREHOUSE_RRN,  " +
                    "CHECKLIST_JOB_RRN,COMMENTS FROM " + DataBaseNames.INVENTORY_TRANS_HISTORY + " WHERE trans_rrn = " +
                    transRrn;

            return jdbcTemplate.query(sql, new RowMapper<Map<String, Object>>() {
                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> item = new HashMap<>();
                    item.put("transRrn", transRrn);
                    item.put("transSequence", rs.getLong("TRANS_SEQUENCE"));
                    item.put("itemRrn", rs.getLong("ITEM_RRN"));
                    item.put("transQty", rs.getDouble("TRANS_QTY"));
                    item.put("comments", rs.getString("COMMENTS"));
                    return item;
                }
            });
        }

        @Override
        public void insertLotConsumesMaterialHistory(LotConsumesMaterialHistory lotConsumesMaterialHistory) {
            StringBuilder sql = new StringBuilder("INSERT INTO LOT_CONSUMES_MATERIAL_HISTORY");
            sql.append(" (TRANS_RRN, TRANS_SEQUENCE, BOR_RRN, BOR_VERSION, BOR_RESOURCE_SEQ, BOR_LOSS_TYPE, " +
                               "BOR_BASIS_CODE, ");
            sql.append(" LOT_NUMBER, ITEM_RRN, WAREHOUSE_RRN, MATERIAL_TYPE, RUN_RRN, LOT_RRNS, LOT_IDS, ");
            sql.append(" CONSUMPTION_QTY, CONSUMPTION_DATE, CONSUMPRION_USER_ID) ");
            sql.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate, ?) ");

            Object[] args = new Object[]{lotConsumesMaterialHistory.getTransRrn(),
                    lotConsumesMaterialHistory.getTransSequence(), lotConsumesMaterialHistory.getBorRrn(),
                    lotConsumesMaterialHistory.getBorVersion(), lotConsumesMaterialHistory.getBorResourceSeq(),
                    lotConsumesMaterialHistory.getBorLossType(), lotConsumesMaterialHistory.getBorBasisCode(),
                    lotConsumesMaterialHistory.getLotNumber(), lotConsumesMaterialHistory.getItemRrn(),
                    lotConsumesMaterialHistory.getWarehouseRrn(), lotConsumesMaterialHistory.getMaterialType(),
                    lotConsumesMaterialHistory.getRunRrn(), lotConsumesMaterialHistory.getLotRrns(),
                    lotConsumesMaterialHistory.getLotIds(), lotConsumesMaterialHistory.getConsumptionQty(),
                    lotConsumesMaterialHistory.getConsumptionUserId()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public List<Map<String, Object>> getMaterialInfoByLot(long lotRrn) {
            String sql = "SELECT DISTINCT T.*, U.WAREHOUSE_RRN, U.MATERIAL_LOT_NUMBER LOT_NUMBER, V.LOT_BOX " +
                    " FROM INVENTORY_TRANS_HISTORY U, INVENTORY_TRANS_HISTORY_EXT V, (SELECT ITEM_ID, " +
                    " (SELECT SYS_RRN FROM MATERIAL M WHERE M.MATERIAL_ID = U.ITEM_ID AND U.FACILITY_RRN = " +
                    " M.FACILITY_RRN ) ITEM_RRN, COUNT(*) ITEM_QTY FROM UNIT U WHERE LOT_RRN = ? GROUP BY " +
                    " U.ITEM_ID, U.FACILITY_RRN) T WHERE T.ITEM_RRN = U.ITEM_RRN AND U.LOT_RRN = ? " +
                    " AND U.TRANS_RRN = V.TRANS_RRN AND U.TRANS_SEQUENCE = V.TRANS_SEQUENCE ";

            return jdbcTemplate.query(sql, new Object[]{lotRrn, lotRrn}, new RowMapper<Map<String, Object>>() {
                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> item = new HashMap<>();
                    item.put("lotNumber", rs.getString("lot_number"));
                    item.put("itemRrn", rs.getLong("item_rrn"));
                    item.put("wareHouseRrn", rs.getLong("warehouse_rrn"));
                    item.put("specifiedQty", -rs.getDouble("item_qty"));
                    item.put("lotBox", rs.getString("lot_box"));
                    item.put("itemId", rs.getString("item_id"));
                    return item;
                }
            });
        }

        @Override
        public Map<String, Object> getMaterialInfoByUnit(long unitRrn) {
            String sql="SELECT DISTINCT T.*, U.WAREHOUSE_RRN, LOT_NUMBER\n" + "FROM  UNIT_INVENTORY U,\n" +
                    "      (SELECT ITEM_ID,\n" + "              (SELECT SYS_RRN\n" + "               FROM MATERIAL M\n" +
                    "               WHERE M.MATERIAL_ID = U.ITEM_ID AND U.FACILITY_RRN = M.FACILITY_RRN) ITEM_RRN,\n" +
                    "              COUNT(*)                                                              ITEM_QTY\n" +
                    "       FROM UNIT U\n" + "       WHERE UNIT_RRN = ?\n" +
                    "       GROUP BY U.ITEM_ID, U.FACILITY_RRN) T\n" + "WHERE T.ITEM_RRN = U.ITEM_RRN\n" +
                    "  AND U.UNIT_RRN = ?";

            List<Map<String, Object>> list = jdbcTemplate.query(sql, new Object[]{unitRrn, unitRrn}, new RowMapper<Map<String, Object>>() {
                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> item = new HashMap<>();
                    item.put("lotNumber", rs.getString("lot_number"));
                    item.put("itemRrn", rs.getLong("item_rrn"));
                    item.put("wareHouseRrn", rs.getLong("warehouse_rrn"));
                    item.put("specifiedQty", -rs.getDouble("item_qty"));
                    item.put("itemId", rs.getString("item_id"));
                    return item;
                }
            });
            if (CollectionUtils.isNotEmpty(list)){//只有单条数据，故只返回一个map
                return list.get(0);
            }
            return new HashMap<String, Object>();
        }

        @Override
        public String getReturnMaterialLotNumber(Long itemRrn, long warehouseRrn, String lotNumber, String orderId) {
            String sql = "SELECT li.LOT_NUMBER FROM LOT_INVENTORY li, LOT_INVENTORY_EXT lie "
                    + " WHERE li.LOT_NUMBER = lie.LOT_NUMBER AND li.ITEM_RRN = lie.ITEM_RRN AND li.WAREHOUSE_RRN = lie.WAREHOUSE_RRN "
                    + " AND li.LOT_NUMBER like ? AND li.ITEM_RRN = ? AND li.WAREHOUSE_RRN = ? AND LIE.ATTRIBUTE_DATA3=? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lotNumber+"_%", itemRrn, warehouseRrn, orderId}, String.class);
        }

        @Override
        public List<LotInventoryDO> getLotInventoryList4Erp(Long itemRrn, Long warehouseRrn, String lotNumber) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" li.LOT_NUMBER, li.ITEM_RRN, li.MATERIAL_TYPE, li.WAREHOUSE_RRN,")
                .append(" li.WAREHOUSE_ID, li.LOCATION_RRN, ")
                .append(" li.RECEIPT_QTY, li.ISSUE_QTY, li.ADJUST_QTY, ")
                .append(" li.RECEIPT_DATE, li.EXPIRATION_DATE, li.RE_TEST_DATE, li.QA_APPROVAL_DATE, ")
                .append(" li.STATUS, li.AVAILABILITY_DAYS, li.ISSUE_PRIORITY, ")
                .append(" li.WAFERPNTYPE, li.WAFERLATTICEORIENTATION, li.WAFERRESISTANCE, ")
                .append("li.WAFEREXTENSION, li.WAFERSUPPLIER, ")
                .append(" lie.CUSTOMER_ID, lie.OFF_ORIENTATION, lie.TYPE_DOPANT, lie.THICKNESS, ")
                .append( "lie.PRODUCTION_DATE, lie.VALID_TERM, ")
                .append(" lie.INCOMING_DATE, lie.CHECK_DATE, lie.CHECK_USER, lie.CHECK_RESULT, ")
                .append(" lie.ATTRIBUTE_DATA1, lie.ATTRIBUTE_DATA2, lie.ATTRIBUTE_DATA3,")
                .append(" lie.ATTRIBUTE_DATA4, lie.ATTRIBUTE_DATA5 ")
                .append(" FROM ")
                .append(DataBaseNames.LOT_INVENTORY).append(" li, ")
                .append(DataBaseNames.LOT_INVENTORY_EXT).append(" lie ")
                .append(" WHERE li.LOT_NUMBER = lie.LOT_NUMBER AND li.ITEM_RRN = lie.ITEM_RRN ")
                .append("AND li.WAREHOUSE_RRN = lie.WAREHOUSE_RRN ")
                .append(" AND li.RECEIPT_QTY + li.ADJUST_QTY - li.ISSUE_QTY > 0 ")
                .append(" AND li.LOT_NUMBER NOT LIKE '%").append(WarehouseNames.TYPE_RETURN).append("' ")
                .append(" AND li.ITEM_RRN = ? AND li.WAREHOUSE_RRN = ? ")
                .append(" AND LIE.ATTRIBUTE_DATA5 IS NULL ");

            List<Object> args = new ArrayList<>(Arrays.asList(itemRrn, warehouseRrn));
            if (StringUtils.isNotEmpty(lotNumber)) {
                sql.append(" AND li.LOT_NUMBER LIKE ? ");
                args.add(lotNumber + "%");
            }

            sql.append(" ORDER BY li.EXPIRATION_DATE ASC ");

            return jdbcTemplate.query(sql.toString(), args.toArray(), new LotInventoryDORowMapper());
        }

        @Override
        public List<LotInventoryDO> getAvailableLotInventory(long itemRrn, long warehouseRrn, String lotNumber) {
            StringBuilder sql = new StringBuilder("SELECT li.LOT_NUMBER, li.ITEM_RRN, li.MATERIAL_TYPE, li.WAREHOUSE_RRN,");

            sql.append(" li.WAREHOUSE_ID, li.LOCATION_RRN, ")
               .append(" li.RECEIPT_QTY, li.ISSUE_QTY, li.ADJUST_QTY, ")
               .append(" li.RECEIPT_DATE, li.EXPIRATION_DATE, li.RE_TEST_DATE, li.QA_APPROVAL_DATE, ")
               .append(" li.STATUS, li.AVAILABILITY_DAYS, li.ISSUE_PRIORITY, ")
               .append(" li.WAFERPNTYPE, li.WAFERLATTICEORIENTATION, li.WAFERRESISTANCE, ")
               .append("li.WAFEREXTENSION, li.WAFERSUPPLIER, ")
               .append(" lie.CUSTOMER_ID, lie.OFF_ORIENTATION, lie.TYPE_DOPANT, lie.THICKNESS, ")
               .append( "lie.PRODUCTION_DATE, lie.VALID_TERM, ")
               .append(" lie.INCOMING_DATE, lie.CHECK_DATE, lie.CHECK_USER, lie.CHECK_RESULT, ")
               .append(" lie.ATTRIBUTE_DATA1, lie.ATTRIBUTE_DATA2, lie.ATTRIBUTE_DATA3,")
               .append(" lie.ATTRIBUTE_DATA4, lie.ATTRIBUTE_DATA5 ")
               .append(" FROM ")
               .append(DataBaseNames.LOT_INVENTORY).append(" li, ")
               .append(DataBaseNames.LOT_INVENTORY_EXT).append(" lie ")
               .append(" WHERE li.LOT_NUMBER = lie.LOT_NUMBER AND li.ITEM_RRN = lie.ITEM_RRN ")
               .append("AND li.WAREHOUSE_RRN = lie.WAREHOUSE_RRN ")
               .append(" AND li.RECEIPT_QTY + li.ADJUST_QTY - li.ISSUE_QTY > 0 ")
               .append(" AND INSTR(li.LOT_NUMBER,'").append(WarehouseNames.TYPE).append("') <=0")
               .append(" AND li.ITEM_RRN = ? AND li.WAREHOUSE_RRN = ?  ")
               .append(" AND (LIE.ATTRIBUTE_DATA5 IS NULL OR LIE.ATTRIBUTE_DATA5 = '') ");//当物料批由mes发送退料请求后，物料批不可被消耗

            List<Object> args = new ArrayList<>(Arrays.asList(itemRrn, warehouseRrn));
            if (StringUtils.isNotEmpty(lotNumber)) {
                sql.append(" AND li.LOT_NUMBER = ? ");
                args.add(lotNumber);
            }

            sql.append(" ORDER BY li.EXPIRATION_DATE ASC ");

            return jdbcTemplate.query(sql.toString(), args.toArray(), new LotInventoryDORowMapper());
        }

        @Override
        public List<LotInventoryDO> getLotInventoryListByLotNumberLike(long itemRrn, long warehouseRrn, String lotNumber) {
            String sql = getLotInventoryBaseSql()  + " AND LI.ITEM_RRN = ? AND LI.WAREHOUSE_RRN = ?  AND LI.LOT_NUMBER LIKE ?";
            Object[] args = new Object[]{itemRrn, warehouseRrn, lotNumber + "%"};
            return jdbcTemplate.query(sql, args, new LotInventoryDORowMapper());
        }

        @Override
        public String getReturnMaterialLotNumberForBom(Long itemRrn, long warehouseRrn, String lotNumber) {
            String sql = "SELECT li.LOT_NUMBER FROM LOT_INVENTORY li, LOT_INVENTORY_EXT lie "
                    + " WHERE li.LOT_NUMBER = lie.LOT_NUMBER AND li.ITEM_RRN = lie.ITEM_RRN AND li.WAREHOUSE_RRN = lie.WAREHOUSE_RRN "
                    + " AND li.LOT_NUMBER like ? AND li.ITEM_RRN = ? AND li.WAREHOUSE_RRN = ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lotNumber, itemRrn, warehouseRrn}, String.class);
        }

    }