# MaterialDAOImpl.java

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

    import com.fa.sesa.threadlocal.LocalContext;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.server.asm.dao.MaterialDAO;
    import com.mycim.server.asm.dao.mapper.MaterialDORowMapper;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.bas.TransactionLog;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.LinkTypeList;
    import com.mycim.valueobject.inv.LotStepBomHistoryDO;
    import com.mycim.valueobject.inv.MaterialDO;
    import com.mycim.valueobject.inv.value.WarehouseNames;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;
    import org.springframework.util.CollectionUtils;

    import java.util.ArrayList;
    import java.util.List;

    @Repository
    public class MaterialDAOImpl implements MaterialDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public void insertMaterial(MaterialDO material) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");

            sql.append(DataBaseNames.ITEM);
            sql.append(" (ITEM_RRN, ITEM_EXTEND_NUMBER, ITEM_CLASS, DEFAULT_WAREHOUSE_RRN, DEFAULT_LOCATION_RRN, ");
            sql.append(" OPENING_BALANCE, RECEIPT_QTY, ISSUE_QTY, ADJUST_QTY, STORE_UOM, PURCHASE_UOM, SALES_UOM, ");
            sql.append(" LOT_TRACKING_FLAG, PRODUCT_FAMILY_RRN, SUBPRODUCT_FLAG, PRODUCT_ENGINEER_RRN, ");
            sql.append(
                    " HOLD_ON_LOW_YIELD, EXPIRATION_LENGTH, EXPIRATION_ALARM_ID, WAFER_SIZE, MASK_ID, " + "ITEM_OWNER, " +
                            "QUANTITY_OF_WARNING) ");
            sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

            Object[] args = {material.getInstanceRrn(), material.getItemExtendNumber(), material.getItemClass(),
                    material.getDefaultWarehouseRrn(), material.getDefaultLocationRrn(), material.getOpeningBalance(),
                    material.getReceiptQty(), material.getIssueQty(), material.getAdjustQty(), material.getStoreUom(),
                    material.getPurchaseUom(), material.getSalesUom(), material.getLotTrackingFlag(),
                    material.getProductFamilyRrn(), material.getSubproductFlag(), material.getProductEngineerRrn(),
                    material.getHoldOnLowYield(), material.getExpirationLength(), material.getExpirationAlarmId(),
                    material.getWaferSize(), material.getMaskId(), material.getItemOwner(),
                    material.getQuantityOfWarning()};

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

        @Override
        public void insertMaterial(MaterialDO m, Long userRrn) {
            String sql = "INSERT INTO " + DataBaseNames.MATERIAL + "(SYS_RRN,MATERIAL_ID,FACILITY_RRN,DESCRIPTION," +
                    "OBJ_SPACE," + "OBJ_TYPE,OBJ_SUBTYPE,OBJ_STATUS,OBJ_CURRENT_VERSION,OBJ_ACTIVE_VERSION," +
                    "ITEM_EXTEND_NUMBER,ITEM_CLASS,DEFAULT_WAREHOUSE_RRN,DEFAULT_LOCATION_RRN," + "OPENING_BALANCE," +
                    "RECEIPT_QTY,ISSUE_QTY,ADJUST_QTY,STORE_UOM,PURCHASE_UOM," + "SALES_UOM,LOT_TRACKING_FLAG," +
                    "PRODUCT_FAMILY_RRN,SUBPRODUCT_FLAG,PRODUCT_ENGINEER_RRN," + "HOLD_ON_LOW_YIELD,EXPIRATION_LENGTH," +
                    "EXPIRATION_ALARM_ID,WAFER_SIZE,MASK_ID," + "ITEM_OWNER,QUANTITY_OF_WARNING,CREATE_TIME,UPDATE_TIME," +
                    "CREATE_USER_RRN," + "UPDATE_USER_RRN,LOCK_VERSION )" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                    "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

            Object[] args = {m.getInstanceRrn(), m.getInstanceId(), LocalContext.getFacilityRrn(), m.getInstanceDesc(),
                    m.getNamedSpace(), m.getObjectType(), m.getObjectSubtype(), m.getInstanceStatus(),
                    m.getCurrentVersion(), m.getActiveVersion(), m.getItemExtendNumber(), m.getItemClass(),
                    m.getDefaultWarehouseRrn(), m.getDefaultLocationRrn(), m.getOpeningBalance(), m.getReceiptQty(),
                    m.getIssueQty(), m.getAdjustQty(), m.getStoreUom(), m.getPurchaseUom(), m.getSalesUom(),
                    m.getLotTrackingFlag(), m.getProductFamilyRrn(), m.getSubproductFlag(), m.getProductEngineerRrn(),
                    m.getHoldOnLowYield(), m.getExpirationLength(), m.getExpirationAlarmId(), m.getWaferSize(),
                    m.getMaskId(), m.getItemOwner(), m.getQuantityOfWarning(), m.getCreatedTimestamp(),
                    m.getCreatedTimestamp(), LocalContext.getUserRrn(), LocalContext.getUserRrn(), m.getLockVersion()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void deleteMaterial(Long materialRrn) {
            String sql = "DELETE FROM " + DataBaseNames.ITEM + " WHERE ITEM_RRN = ? ";

            jdbcTemplate.update(sql, materialRrn);
        }

        @Override
        public void deleteMaterial(MaterialDO material) {
            String sql = "DELETE FROM " + DataBaseNames.MATERIAL + " WHERE SYS_RRN = ? ";
            jdbcTemplate.update(sql, material.getInstanceRrn());
        }

        @Override
        public void updateMaterial(MaterialDO material) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(DataBaseNames.ITEM).append(" SET ");
            sql.append(" ITEM_EXTEND_NUMBER = ?, ITEM_CLASS = ?, STORE_UOM = ?, EXPIRATION_LENGTH = ?, ");
            sql.append(
                    " EXPIRATION_ALARM_ID = ?, ITEM_OWNER = ?, MASK_ID = ?, WAFER_SIZE = ?, QUANTITY_OF_WARNING" + " = ? ");
            sql.append(" WHERE ITEM_RRN = ? ");

            Object[] args = new Object[]{material.getItemExtendNumber(), material.getItemClass(), material.getStoreUom(),
                    material.getExpirationLength(), material.getExpirationAlarmId(), material.getItemOwner(),
                    material.getMaskId(), material.getWaferSize(), material.getQuantityOfWarning(),
                    material.getInstanceRrn()};

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

        @Override
        public void updateMaterial(MaterialDO mr, Long userRrn) {
            String sql = "UPDATE " + DataBaseNames.MATERIAL + " SET " + " ITEM_EXTEND_NUMBER = ?, ITEM_CLASS = ?, " +
                    "STORE_UOM = ?, EXPIRATION_LENGTH = ?," + "EXPIRATION_ALARM_ID = ?, " + "ITEM_OWNER = ?, MASK_ID = ?," +
                    " WAFER_SIZE = ?, QUANTITY_OF_WARNING = ? , " + "UPDATE_USER_RRN=?," + " UPDATE_TIME=?, " +
                    "DESCRIPTION=?, OBJ_SUBTYPE=?" + " WHERE SYS_RRN = ? ";
            Object[] args = new Object[]{mr.getItemExtendNumber(), mr.getItemClass(), mr.getStoreUom(),
                    mr.getExpirationLength(), mr.getExpirationAlarmId(), mr.getItemOwner(), mr.getMaskId(),
                    mr.getWaferSize(), mr.getQuantityOfWarning(), userRrn, mr.getLastUpdateTimestamp(),
                    mr.getInstanceDesc(), mr.getObjectSubtype(), mr.getInstanceRrn()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public MaterialDO getMaterial(Long materialRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" i.ITEM_RRN, i.ITEM_EXTEND_NUMBER, i.ITEM_CLASS, i.DEFAULT_WAREHOUSE_RRN, i" +
                               ".DEFAULT_LOCATION_RRN, ");
            sql.append(" i.OPENING_BALANCE, i.RECEIPT_QTY, i.ISSUE_QTY, i.ADJUST_QTY, i.STORE_UOM, i.PURCHASE_UOM," + " i" +
                               ".SALES_UOM, ");
            sql.append(" i.LOT_TRACKING_FLAG, i.PRODUCT_FAMILY_RRN, i.SUBPRODUCT_FLAG, i.PRODUCT_ENGINEER_RRN, ");
            sql.append(" i.HOLD_ON_LOW_YIELD, i.EXPIRATION_LENGTH, i.EXPIRATION_ALARM_ID, i.WAFER_SIZE, i.MASK_ID," + " i" +
                               ".ITEM_OWNER, i.QUANTITY_OF_WARNING ");
            sql.append(" FROM ").append(DataBaseNames.ITEM).append(" i ");
            sql.append(" WHERE i.ITEM_RRN = ? ");

            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new Object[]{materialRrn}, new MaterialDORowMapper());
        }

        @Override
        public List<Long> getMaterialInWarehouse(Long materialRrn) {
            String sql = "SELECT DISTINCT WAREHOUSE_RRN FROM " + DataBaseNames.WAREHOUSE_INVENTORY + " WHERE ITEM_RRN =" +
                    materialRrn;

            return jdbcTemplate.query(sql, Long.class);
        }

        @Override
        public boolean hasMaterialExtNumber(Long materialRrn, String itemExtendNumber) {
            String sql = "SELECT DISTINCT ITEM_EXTEND_NUMBER FROM " + DataBaseNames.ITEM + " WHERE ITEM_EXTEND_NUMBER = ?" +
                    " AND ITEM_RRN <> ?";

            Object[] args = new Object[]{itemExtendNumber, materialRrn};

            List items = jdbcTemplate.query(sql, args, String.class);

            if (!CollectionUtils.isEmpty(items)) {
                return true;
            }

            return false;
        }

        @Override
        public List<Long> getMaterialInBor(Long materialRrn) {
            String sql = "SELECT DISTINCT BOR_RRN FROM " + DataBaseNames.BILL_OF_RESOURCE + " WHERE RESOURCE_RRN =" +
                    materialRrn;

            return jdbcTemplate.query(sql, Long.class);
        }

        @Override
        public void insertMaterialHistory(Long materialRrn, Long transRrn, Long transSequence, Double transQty) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");

            sql.append(DataBaseNames.ITEM_H);
            sql.append(" (ITEM_RRN, ITEM_EXTEND_NUMBER, ITEM_CLASS, DEFAULT_WAREHOUSE_RRN, DEFAULT_LOCATION_RRN, ");
            sql.append(" OPENING_BALANCE, RECEIPT_QTY, ISSUE_QTY, ADJUST_QTY, STORE_UOM, PURCHASE_UOM, SALES_UOM, ");
            sql.append(" LOT_TRACKING_FLAG, PRODUCT_FAMILY_RRN, SUBPRODUCT_FLAG, PRODUCT_ENGINEER_RRN, ");
            sql.append(
                    " HOLD_ON_LOW_YIELD, EXPIRATION_LENGTH, EXPIRATION_ALARM_ID, WAFER_SIZE, MASK_ID, " + "ITEM_OWNER, " +
                            "QUANTITY_OF_WARNING, ");
            sql.append(" TRANS_RRN, TRANS_SEQUENCE, TRANS_QTY) ");
            sql.append(" SELECT ");
            sql.append(" i.ITEM_RRN, i.ITEM_EXTEND_NUMBER, i.ITEM_CLASS, i.DEFAULT_WAREHOUSE_RRN, i" +
                               ".DEFAULT_LOCATION_RRN, ");
            sql.append(" i.OPENING_BALANCE, i.RECEIPT_QTY, i.ISSUE_QTY, i.ADJUST_QTY, i.STORE_UOM, i.PURCHASE_UOM," + " i" +
                               ".SALES_UOM, ");
            sql.append(" i.LOT_TRACKING_FLAG, i.PRODUCT_FAMILY_RRN, i.SUBPRODUCT_FLAG, i.PRODUCT_ENGINEER_RRN, ");
            sql.append(" i.HOLD_ON_LOW_YIELD, i.EXPIRATION_LENGTH, i.EXPIRATION_ALARM_ID, i.WAFER_SIZE, i.MASK_ID," + " i" +
                               ".ITEM_OWNER, i.QUANTITY_OF_WARNING, ");
            sql.append(transRrn).append(", ").append(transSequence).append(", ").append(transQty);
            sql.append(" FROM ").append(DataBaseNames.ITEM).append(" i ");
            sql.append(" WHERE i.ITEM_RRN = ? ");

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

        @Override
        public void insertMaterialHistory(MaterialDO m, TransactionLog tr) {
            String sql = "INSERT INTO " + DataBaseNames.MATERIAL_H + "(TRANS_RRN,TRANS_SEQUENCE,TRANS_ID,SYS_RRN," +
                    "MATERIAL_ID,FACILITY_RRN,DESCRIPTION," + "OBJ_SPACE," + "OBJ_TYPE,OBJ_SUBTYPE,OBJ_STATUS," +
                    "OBJ_CURRENT_VERSION,OBJ_ACTIVE_VERSION," + "ITEM_EXTEND_NUMBER,ITEM_CLASS,DEFAULT_WAREHOUSE_RRN," +
                    "DEFAULT_LOCATION_RRN," + "OPENING_BALANCE," + "RECEIPT_QTY,ISSUE_QTY,ADJUST_QTY,STORE_UOM," +
                    "PURCHASE_UOM," + "SALES_UOM,LOT_TRACKING_FLAG,PRODUCT_FAMILY_RRN,SUBPRODUCT_FLAG," +
                    "PRODUCT_ENGINEER_RRN," + "HOLD_ON_LOW_YIELD,EXPIRATION_LENGTH,EXPIRATION_ALARM_ID,WAFER_SIZE," +
                    "MASK_ID," + "ITEM_OWNER,QUANTITY_OF_WARNING,TRANS_TIME,TRANS_USER_RRN)" + " SELECT ?,?,?,SYS_RRN," +
                    "MATERIAL_ID,FACILITY_RRN,DESCRIPTION,OBJ_SPACE," + "OBJ_TYPE,OBJ_SUBTYPE,OBJ_STATUS," +
                    "OBJ_CURRENT_VERSION,OBJ_ACTIVE_VERSION," + "ITEM_EXTEND_NUMBER,ITEM_CLASS,DEFAULT_WAREHOUSE_RRN," +
                    "DEFAULT_LOCATION_RRN," + "OPENING_BALANCE," + "RECEIPT_QTY,ISSUE_QTY,ADJUST_QTY,STORE_UOM," +
                    "PURCHASE_UOM," + "SALES_UOM,LOT_TRACKING_FLAG,PRODUCT_FAMILY_RRN,SUBPRODUCT_FLAG," +
                    "PRODUCT_ENGINEER_RRN," + "HOLD_ON_LOW_YIELD,EXPIRATION_LENGTH,EXPIRATION_ALARM_ID,WAFER_SIZE," +
                    "MASK_ID," + "ITEM_OWNER,QUANTITY_OF_WARNING,?,? " + " FROM " + DataBaseNames.MATERIAL + " WHERE " +
                    "SYS_RRN=?";
            jdbcTemplate.update(sql, tr.getTransRrn(), tr.getTransSequence(), tr.getTransId(), tr.getTransStartTimestamp(),
                                LocalContext.getUserRrn(), m.getInstanceRrn());
        }

        @Override
        public void updateMaterialForReceiptQty(Long materialRrn, Double receiptQty) {
            updateMaterialForQty(materialRrn, receiptQty, 0D, 0D);
        }

        @Override
        public void updateMaterialForIssueQty(Long materialRrn, Double issueQty) {
            updateMaterialForQty(materialRrn, 0D, issueQty, 0D);
        }

        @Override
        public void updateMaterialForAdjustQty(Long materialRrn, Double adjustQty) {
            updateMaterialForQty(materialRrn, 0D, 0D, adjustQty);
        }

        @Override
        public String getMaterials4lot(Long warehouseRrn, Long productRrn) {
            StringBuilder sb = new StringBuilder();
            sb.append(" SELECT LISTAGG(INSTANCE_ID, ',') WITHIN GROUP (ORDER BY INSTANCE_ID) AS RESULTS");
            sb.append(" FROM WAREHOUSE_INVENTORY W, NAMED_OBJECT OI ");
            sb.append(" WHERE W.ITEM_RRN = OI.INSTANCE_RRN ");
            sb.append(" AND OI.OBJECT = ? ");
            sb.append(" AND OI.OBJECT_TYPE = ? ");
            sb.append(" AND W.WAREHOUSE_RRN = ? ");
            sb.append(" AND OI.INSTANCE_RRN IN (SELECT TO_RRN ");
            sb.append(" FROM RELATION ");
            sb.append(" WHERE LINK_TYPE = ? ");
            sb.append(" AND FROM_RRN = ?) ");

            return jdbcTemplate.queryForObjectWithNull(sb.toString(),
                                                       new Object[]{ObjectList.ITEM_KEY, ObjectList.ITEM_MATERIAL_KEY,
                                                               warehouseRrn, LinkTypeList.PRODUCT_TO_MATERIAL_KEY,
                                                               productRrn},
                                                       String.class);
        }

        @Override
        public int getMaterialTotalQuantity(long materialRrn) {
            String sql = "SELECT SUM(RECEIPT_QTY + ADJUST_QTY - ISSUE_QTY) FROM  LOT_INVENTORY "
                    + "WHERE RECEIPT_QTY + ADJUST_QTY - ISSUE_QTY > 0 AND INSTR(LOT_NUMBER,'"+ WarehouseNames.TYPE + "') <=0 "
                    + "AND ITEM_RRN = ?";
            return jdbcTemplate.queryForObject(sql, new Object[]{materialRrn}, int.class);
        }

        @Override
        public void insertLotStepBomDao(LotStepBomHistoryDO lotStepBomHistoryDO) {
            StringBuilder sql = new StringBuilder(" INSERT INTO ");
            sql.append(DataBaseNames.LOT_STEP_BOM_H);
            sql.append("(TRANS_RRN, TRANS_SEQUENCE, TRANS_ID, TRANS_QTY, TRANS_TIME, WAREHOUSE_RRN, WAREHOUSE_ID, ITEM_RRN,");
            sql.append("MATERIAL_LOT_NUMBER, LOT_RRN, STEP_SEQUENCE, BATCH_ID, BOM_CONSUME_TYPE, BOM_CONSUME_MODE, ");
            sql.append("COMMENTS, FACILITY_RRN ) ");
            sql.append("VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)");
            List args = new ArrayList();
            args.add(lotStepBomHistoryDO.getTransRrn());
            args.add(lotStepBomHistoryDO.getTransSequence());
            args.add(lotStepBomHistoryDO.getTransId());
            args.add(lotStepBomHistoryDO.getTransQty());
            args.add(lotStepBomHistoryDO.getTransTime());
            args.add(lotStepBomHistoryDO.getWarehouseRrn());
            args.add(lotStepBomHistoryDO.getWarehouseId());
            args.add(lotStepBomHistoryDO.getItemRrn());
            args.add(lotStepBomHistoryDO.getMaterialLotNumber());
            args.add(lotStepBomHistoryDO.getLotRrn());
            args.add(lotStepBomHistoryDO.getStepSequence());
            args.add(lotStepBomHistoryDO.getBatchId());
            args.add(lotStepBomHistoryDO.getBomConsumeType());
            args.add(lotStepBomHistoryDO.getBomConsumeMode());
            args.add(lotStepBomHistoryDO.getComments());
            args.add(lotStepBomHistoryDO.getFacilityRrn());
            jdbcTemplate.update(sql.toString(), args.toArray());
        }

        private void updateMaterialForQty(Long materialRrn, Double receiptQty, Double issueQty, Double adjustQty) {
            StringBuilder sql = new StringBuilder("UPDATE ITEM 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(" WHERE ITEM_RRN = ? ");
            jdbcTemplate.update(sql.toString(), receiptQty, issueQty, adjustQty, materialRrn);
            //同步新表中的数据
            String sqlNewTable = "UPDATE " + DataBaseNames.MATERIAL + " SET RECEIPT_QTY = NVL(RECEIPT_QTY, 0) + ?," +
                    "ISSUE_QTY = NVL(ISSUE_QTY, 0) + ?, ADJUST_QTY = NVL(ADJUST_QTY, 0) + ? " + " WHERE SYS_RRN = ? ";
            jdbcTemplate.update(sqlNewTable, receiptQty, issueQty, adjustQty, materialRrn);
        }

    }