# EquipmentConstrainInfoDAOImpl.java

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

    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.collections.MapUtils;
    import com.mycim.framework.utils.lang.math.NumberUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.constrain.dao.EquipmentConstrainInfoDAO;
    import com.mycim.server.constrain.dao.mapper.EquipmentConstrainRowMapper;
    import com.mycim.server.constrain.dao.mapper.EquipmentConstrainTmpRowMapper;
    import com.mycim.valueobject.SystemConstant;
    import com.mycim.valueobject.prp.EquipmentConstrainInfo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.Timestamp;
    import java.util.*;

    @Repository
    public class EquipmentConstrainInfoDAOImpl implements EquipmentConstrainInfoDAO {

        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public List<EquipmentConstrainInfo> qryEnableEquipmentConstrains(List<String> constrainRules, String equipmentId,
                                                                         String constrainAction) {
            List<String> params = new ArrayList();
            String sql = buildSqlForEnableEquipmentConstrains(params, equipmentId, constrainRules, constrainAction);
            return jdbcTemplate.query(sql, params.toArray(), new EquipmentConstrainRowMapper());
        }

        @Override
        public EquipmentConstrainInfo getEquipmentConstrainInfoForCheck(EquipmentConstrainInfo equipmentConstrainInfo) {
            String sql = " SELECT " + getEquipConstrainsTmpSql() + " FROM EQUIPMENT_CONSTRAIN_INFO_TMP  WHERE " +
                    "FACILITY_RRN = ? AND EQUIPMENT_ID = ? AND" + " CONSTRAIN_SEQ = ? AND CONSTRAIN_TYPE=?";
            Object[] args = new Object[]{equipmentConstrainInfo.getFacilityRrn(), equipmentConstrainInfo.getEquipmentId()
                    , NumberUtils.toLong(
                    equipmentConstrainInfo.getConstrainSeq()), equipmentConstrainInfo.getConstrainType()};
            List equipmentConstrainInfos = jdbcTemplate.query(sql, args, new EquipmentConstrainTmpRowMapper());
            if (equipmentConstrainInfos.isEmpty()) {
                return null;
            } else {
                return (EquipmentConstrainInfo) equipmentConstrainInfos.get(0);
            }
        }

        @Override
        public EquipmentConstrainInfo getEquipmentConstrainInfoForCheck(Long constrainInfoRrn) {
            String sql = " SELECT " + getEquipConstrainsTmpSql() + " FROM EQUIPMENT_CONSTRAIN_INFO_TMP  WHERE " +
                    "CONSTRAIN_RRN = ?";
            return (EquipmentConstrainInfo) jdbcTemplate
                    .queryForObject(sql, new Object[]{constrainInfoRrn}, new EquipmentConstrainTmpRowMapper());
        }

        @Override
        public EquipmentConstrainInfo getEquipmentConstrainInfo(Long constrainInfoRrn) {
            String sql = " SELECT " + getEquipConstrainsSql() + " FROM EQUIPMENT_CONSTRAIN_INFO  WHERE CONSTRAIN_RRN = ?";
            return jdbcTemplate
                    .queryForObjectWithNull(sql, new Object[]{constrainInfoRrn}, new EquipmentConstrainRowMapper());
        }

        @Override
        public EquipmentConstrainInfo getEquipmentConstrainInfo(EquipmentConstrainInfo equipmentConstrainInfo) {
            String sql = " select " + getEquipConstrainsSql() + " from equipment_constrain_info  where facility_rrn = ? " +
                    "and equipment_id = ? and " + "constrain_seq = ? and constrain_type=?";
            Object[] args = new Object[]{equipmentConstrainInfo.getFacilityRrn(), equipmentConstrainInfo.getEquipmentId()
                    , NumberUtils.toLong(
                    equipmentConstrainInfo.getConstrainSeq()), equipmentConstrainInfo.getConstrainType()};
            List equipmentConstrainInfos = jdbcTemplate.query(sql, args, new EquipmentConstrainRowMapper());
            if (equipmentConstrainInfos.isEmpty()) {
                return null;
            } else {
                return (EquipmentConstrainInfo) equipmentConstrainInfos.get(0);
            }
        }

        @Override
        public void insertEquipmentConstrainInfoTmp(EquipmentConstrainInfo equipmentConstrainInfo) {
            String sql = " insert into equipment_constrain_info_tmp(facility_rrn,constrain_rrn,product_id," +
                    "process_id,operation_id,recipe_id,lot_id,equipment_id,constrain_action,constrain_type," +
                    "constrain_status,remark,create_by,modify_by,constrain_seq,create_time,modify_time,route_id," +
                    "stage_id,customer_id,expire_date,DAILY_WAFER_COUNT,TOTAL_WAFER_COUNT,PARENT_EQUIPMENT_ID," +
                    "PARENT_EQUIPMENT_RRN,trans_Name,effective_date, lot_type,eqpgroup_id,DAILY_LOT_COUNT," +
                    "TOTAL_LOT_COUNT," +
                    "AUTO_SWITCH_TIME_INTERVAL,LOT_PRIORITY,INTERNAL_PRIORITY,EXCEPT_PRODUCT_ID,EXCEPT_PROCESS_ID," +
                    "EXCEPT_LOT_ID,EXCEPT_OPERATION_ID,EXCEPT_STAGE_ID,EXCEPT_RECIPE_ID,EXCEPT_CUSTOMER_ID," +
                    "EXCEPT_LOT_TYPE," + "EXCEPT_LOT_PRIORITY, EXCEPT_INTERNAL_PRIORITY, EXCEPT_EQPGROUP_ID,QE_FLAG)" +
                    " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate," + "sysdate,?,?,?,to_timestamp(?,'" +
                    DateUtils.DATE_FORMAT24 + "'),?,?,?,?,?,to_timestamp(?,'" + DateUtils.DATE_FORMAT24 +
                    "'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            Object[] objectArgs = new Object[]{equipmentConstrainInfo.getFacilityRrn(),
                                               equipmentConstrainInfo.getConstrainRrn(),
                                               equipmentConstrainInfo.getProductId(), equipmentConstrainInfo.getProcessId(),
                                               equipmentConstrainInfo.getOperationId(),
                                               equipmentConstrainInfo.getRecipeId(), equipmentConstrainInfo.getLotId(),
                                               equipmentConstrainInfo.getEquipmentId(),
                                               equipmentConstrainInfo.getConstrainAction(),
                                               equipmentConstrainInfo.getConstrainType(),
                                               equipmentConstrainInfo.getConstrainStatus(),
                                               equipmentConstrainInfo.getRemark(), equipmentConstrainInfo.getCreateBy(),
                                               equipmentConstrainInfo.getCreateBy(),
                                               NumberUtils.toLong(equipmentConstrainInfo.getConstrainSeq()),
                                               equipmentConstrainInfo.getRouteId(), equipmentConstrainInfo.getStageId(),
                                               equipmentConstrainInfo.getCustomerId(),
                                               equipmentConstrainInfo.getExpireDate(),
                                               equipmentConstrainInfo.getDailyWaferCount(),
                                               equipmentConstrainInfo.getTotalWaferCount(),
                                               equipmentConstrainInfo.getParentEquipmentId(),
                                               equipmentConstrainInfo.getParentEquipmentRrn(),
                                               equipmentConstrainInfo.getTransName(),
                                               equipmentConstrainInfo.getEffectiveDate(),
                                               equipmentConstrainInfo.getLotType(), equipmentConstrainInfo.getEqpGroupId(),
                                               equipmentConstrainInfo.getDailyLotCount(),
                                               equipmentConstrainInfo.getTotalLotCount(),
                                               DateUtils.getSecondsTime(equipmentConstrainInfo.getAutoSwitchTimeInterval()),
                                               equipmentConstrainInfo.getLotPriority(),
                                               equipmentConstrainInfo.getInternalPriority(),
                                               equipmentConstrainInfo.getExceptProductId(),
                                               equipmentConstrainInfo.getExceptProcessId(),
                                               equipmentConstrainInfo.getExceptLotId(),
                                               equipmentConstrainInfo.getExceptOperationId(),
                                               equipmentConstrainInfo.getExceptStageId(),
                                               equipmentConstrainInfo.getExceptRecipeId(),
                                               equipmentConstrainInfo.getExceptCustomerId(),
                                               equipmentConstrainInfo.getExceptLotType(),
                                               equipmentConstrainInfo.getExceptLotPriority(),
                                               equipmentConstrainInfo.getExceptInternalPriority(),
                                               equipmentConstrainInfo.getExceptEqpGroupId(),
                                               equipmentConstrainInfo.getQeFlag()};
            jdbcTemplate.update(sql, objectArgs);
        }

        @Override
        public void insertEquipmentConstrainHistory(EquipmentConstrainInfo equipmentConstrainInfo, long transRrn) {
            String sql = " INSERT INTO EQUIPMENT_CONSTRAIN_HISTORY(TRANS_RRN,FACILITY_RRN,CONSTRAIN_RRN,PRODUCT_ID," +
                    "PROCESS_ID,OPERATION_ID,RECIPE_ID,LOT_ID," + "EQUIPMENT_ID,CONSTRAIN_ACTION,CONSTRAIN_TYPE," +
                    "CONSTRAIN_STATUS,REMARK,CREATE_BY," + "MODIFY_BY,CONSTRAIN_SEQ,CREATE_TIME,MODIFY_TIME,ROUTE_ID," +
                    "STAGE_ID,CUSTOMER_ID," + "EXPIRE_DATE,EFFECTIVE_DATE,DAILY_WAFER_COUNT,TOTAL_WAFER_COUNT," +
                    "PARENT_EQUIPMENT_ID, PARENT_EQUIPMENT_RRN, DAILY_LOT_COUNT, TOTAL_LOT_COUNT, " +
                    "AUTO_SWITCH_TIME_INTERVAL,LOT_PRIORITY,INTERNAL_PRIORITY,EXCEPT_PRODUCT_ID,EXCEPT_PROCESS_ID, " +
                    "EXCEPT_LOT_ID, EXCEPT_OPERATION_ID, EXCEPT_STAGE_ID,EXCEPT_RECIPE_ID,EXCEPT_CUSTOMER_ID," +
                    "EXCEPT_LOT_TYPE, EXCEPT_LOT_PRIORITY,EXCEPT_INTERNAL_PRIORITY, EXCEPT_EQPGROUP_ID,QE_FLAG)"
                    // + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now())";
                    + " SELECT ?,FACILITY_RRN,CONSTRAIN_RRN,PRODUCT_ID,PROCESS_ID,OPERATION_ID,RECIPE_ID,LOT_ID," + " " +
                    "EQUIPMENT_ID,CONSTRAIN_ACTION,CONSTRAIN_TYPE,CONSTRAIN_STATUS,REMARK,CREATE_BY," + "MODIFY_BY," +
                    "CONSTRAIN_SEQ,CREATE_TIME,MODIFY_TIME,ROUTE_ID,STAGE_ID,CUSTOMER_ID," + "EXPIRE_DATE,EFFECTIVE_DATE," +
                    "DAILY_WAFER_COUNT,TOTAL_WAFER_COUNT,PARENT_EQUIPMENT_ID,PARENT_EQUIPMENT_RRN, " +
                    "DAILY_LOT_COUNT,TOTAL_LOT_COUNT,AUTO_SWITCH_TIME_INTERVAL,LOT_PRIORITY,INTERNAL_PRIORITY," +
                    "EXCEPT_PRODUCT_ID, EXCEPT_PROCESS_ID, EXCEPT_LOT_ID, EXCEPT_OPERATION_ID, EXCEPT_STAGE_ID, " +
                    "EXCEPT_RECIPE_ID,EXCEPT_CUSTOMER_ID, EXCEPT_LOT_TYPE,EXCEPT_LOT_PRIORITY,EXCEPT_INTERNAL_PRIORITY," +
                    "EXCEPT_EQPGROUP_ID,QE_FLAG  FROM EQUIPMENT_CONSTRAIN_INFO_TMP WHERE CONSTRAIN_RRN=?";

            Object[] objectArgs = new Object[]{transRrn, equipmentConstrainInfo.getConstrainRrn()};
            jdbcTemplate.update(sql, objectArgs);
        }

        @Override
        public void modifyEquipmentConstrainInfoTmp(EquipmentConstrainInfo equipmentConstrainInfo) {
            String sql = " update equipment_constrain_info_tmp set  facility_rrn=?,product_id=?,process_id=?," +
                    "operation_id=?,recipe_id=?,lot_id=?," + " equipment_id=?,constrain_action=?,constrain_type=?," +
                    "constrain_status=?,remark=?," + "modify_by=?,constrain_seq=?," + " modify_time=sysdate,route_id=?," +
                    "stage_id = ?, customer_id= ?,expire_date = " + "to_timestamp" + "(?,'" + DateUtils.DATE_FORMAT24 +
                    "'),DAILY_WAFER_COUNT=?,TOTAL_WAFER_COUNT=?," + "PARENT_EQUIPMENT_ID = ?,PARENT_EQUIPMENT_RRN = ? ," +
                    "trans_name=?,effective_date = to_timestamp(?,'" + DateUtils.DATE_FORMAT24 +
                    "'),LOT_TYPE=?,EQPGROUP_ID=?, DAILY_LOT_COUNT = ?,TOTAL_LOT_COUNT = ?,AUTO_SWITCH_TIME_INTERVAL = ?, " +
                    " LOT_PRIORITY=?, INTERNAL_PRIORITY=?,EXCEPT_PRODUCT_ID=?, EXCEPT_PROCESS_ID=?, EXCEPT_LOT_ID=?, " +
                    "EXCEPT_OPERATION_ID=?,EXCEPT_STAGE_ID=?,EXCEPT_RECIPE_ID=?,EXCEPT_CUSTOMER_ID=?,EXCEPT_LOT_TYPE=?," +
                    "EXCEPT_LOT_PRIORITY=?,EXCEPT_INTERNAL_PRIORITY=?,EXCEPT_EQPGROUP_ID=?,QE_FLAG=? where constrain_rrn=?";

            Object[] objectArgs = new Object[]{equipmentConstrainInfo.getFacilityRrn(),
                                               equipmentConstrainInfo.getProductId(), equipmentConstrainInfo.getProcessId(),
                                               equipmentConstrainInfo.getOperationId(),
                                               equipmentConstrainInfo.getRecipeId(),
                                               equipmentConstrainInfo.getLotId(), equipmentConstrainInfo.getEquipmentId(),
                                               equipmentConstrainInfo.getConstrainAction(),
                                               equipmentConstrainInfo.getConstrainType(),
                                               equipmentConstrainInfo.getConstrainStatus(),
                                               equipmentConstrainInfo.getRemark(), equipmentConstrainInfo.getModifyBy(),
                                               NumberUtils.toLong(equipmentConstrainInfo.getConstrainSeq()),
                                               equipmentConstrainInfo.getRouteId(), equipmentConstrainInfo.getStageId(),
                                               equipmentConstrainInfo.getCustomerId(),
                                               equipmentConstrainInfo.getExpireDate(),
                                               equipmentConstrainInfo.getDailyWaferCount(),
                                               equipmentConstrainInfo.getTotalWaferCount(),
                                               equipmentConstrainInfo.getParentEquipmentId(),
                                               equipmentConstrainInfo.getParentEquipmentRrn(),
                                               equipmentConstrainInfo.getTransName(),
                                               equipmentConstrainInfo.getEffectiveDate(),
                                               equipmentConstrainInfo.getLotType(), equipmentConstrainInfo.getEqpGroupId(),
                                               equipmentConstrainInfo.getDailyLotCount(),
                                               equipmentConstrainInfo.getTotalLotCount(),
                                               DateUtils.getSecondsTime(equipmentConstrainInfo.getAutoSwitchTimeInterval()),
                                               equipmentConstrainInfo.getLotPriority(),
                                               equipmentConstrainInfo.getInternalPriority(),
                                               equipmentConstrainInfo.getExceptProductId(),
                                               equipmentConstrainInfo.getExceptProcessId(),
                                               equipmentConstrainInfo.getExceptLotId(),
                                               equipmentConstrainInfo.getExceptOperationId(),
                                               equipmentConstrainInfo.getExceptStageId(),
                                               equipmentConstrainInfo.getExceptRecipeId(),
                                               equipmentConstrainInfo.getExceptCustomerId(),
                                               equipmentConstrainInfo.getExceptLotType(),
                                               equipmentConstrainInfo.getExceptLotPriority(),
                                               equipmentConstrainInfo.getExceptInternalPriority(),
                                               equipmentConstrainInfo.getExceptEqpGroupId(),
                                               equipmentConstrainInfo.getQeFlag(),
                                               equipmentConstrainInfo.getConstrainRrn()};
            jdbcTemplate.update(sql, objectArgs);
        }

        @Override
        public Page qryEquipmentConstrainsForCheck(Page equipmentConstrainPage, Map conditionMap) {
            List<Object> params = new ArrayList<>();
            String equipmentId = MapUtils.getString(conditionMap, "equipmentId");

            Collection constrainRules = (Collection) conditionMap.get("constrainRule");

            StringBuilder sql = new StringBuilder("SELECT * FROM (");

            for (Iterator iterator = constrainRules.iterator(); iterator.hasNext(); ) {
                String constrainRule = (String) iterator.next();
                if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.NORMAL_EQUIPMENT_RULE)) {
                    sql.append(" SELECT ").append(getEquipConstrainsTmpSql())
                       .append(" FROM EQUIPMENT_CONSTRAIN_INFO_TMP WHERE ")
                       .append("CONSTRAIN_TYPE='0' ");

                    if (StringUtils.isNotEmpty(equipmentId)) {
                        sql.append(" AND  EQUIPMENT_ID LIKE ? ");
                        params.add(equipmentId);
                    }
                    if (MapUtils.getBooleanValue(conditionMap, "isQeUser")) {
                        sql.append(" AND QE_FLAG = 'on' ");
                    }
                } else if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.FREE_EQUIPMENT_RULE)) {
                    sql.append(" SELECT ").append(getEquipConstrainsTmpSql())
                       .append(" FROM EQUIPMENT_CONSTRAIN_INFO_TMP WHERE ")
                       .append("CONSTRAIN_TYPE='1' ");
                }

                if (iterator.hasNext()) {
                    sql.append(" UNION ALL ");
                }
            }

            sql.append(" ) temp_1 WHERE LENGTH(TRANS_NAME) > 0 ");

            this.buildArgs(conditionMap, sql);

            sql.append(" ORDER BY EQUIPMENT_ID ,CONSTRAIN_SEQ ,CONSTRAIN_TYPE  ");

            return jdbcTemplate.queryForPage(equipmentConstrainPage, sql.toString(), params.toArray(),
                                             new EquipmentConstrainTmpRowMapper());
        }

        @Override
        public Page qryEquipmentConstrains(Page equipmentConstrainPage, Map conditionMap) {
            List<Object> params = new ArrayList<>();
            String sql = buildSqlForEquipmentConstrains(params, conditionMap);
            return jdbcTemplate
                    .queryForPage(equipmentConstrainPage, sql, params.toArray(), new EquipmentConstrainRowMapper());
        }

        @Override
        public List<EquipmentConstrainInfo> validationEquipmentConstrains(Map condtionMap) {
            Collection params = new ArrayList();
            String sql = validationSqlForEquipmentConstrains(params, condtionMap);
            return jdbcTemplate.query(sql, params.toArray(), new EquipmentConstrainTmpRowMapper());
        }

        @Override
        public void insertEquipmentConstrainInfo(EquipmentConstrainInfo equipmentConstrainInfo) {
            String sql = " insert into equipment_constrain_info(facility_rrn,constrain_rrn,product_id,process_id," +
                    "operation_id,recipe_id,lot_id, equipment_id,constrain_action,constrain_type,constrain_status," +
                    "remark,create_by,modify_by, constrain_seq,create_time,modify_time,route_id,stage_id," +
                    "customer_id,expire_date,effective_date,DAILY_WAFER_COUNT,TOTAL_WAFER_COUNT,PARENT_EQUIPMENT_ID," +
                    "PARENT_EQUIPMENT_RRN,LOT_TYPE,EQPGROUP_ID,DAILY_LOT_COUNT, TOTAL_LOT_COUNT," +
                    "AUTO_SWITCH_TIME_INTERVAL,LOT_PRIORITY,INTERNAL_PRIORITY,EXCEPT_PRODUCT_ID,EXCEPT_PROCESS_ID," +
                    "EXCEPT_LOT_ID, EXCEPT_OPERATION_ID,EXCEPT_STAGE_ID, EXCEPT_RECIPE_ID,EXCEPT_CUSTOMER_ID, " +
                    "EXCEPT_LOT_TYPE, EXCEPT_LOT_PRIORITY,EXCEPT_INTERNAL_PRIORITY, EXCEPT_EQPGROUP_ID,QE_FLAG) " +
                    " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,sysdate,?,?,?,to_timestamp" + "(?,'" +
                    DateUtils.DATE_FORMAT24 + "'),to_timestamp(?,'" + DateUtils.DATE_FORMAT24 +
                    "'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            Object[] objectArgs = new Object[]{equipmentConstrainInfo.getFacilityRrn(),
                                               new Long(equipmentConstrainInfo.getConstrainRrn()),
                                               equipmentConstrainInfo.getProductId(), equipmentConstrainInfo.getProcessId(),
                                               equipmentConstrainInfo.getOperationId(),
                                               equipmentConstrainInfo.getRecipeId(), equipmentConstrainInfo.getLotId(),
                                               equipmentConstrainInfo.getEquipmentId(),
                                               equipmentConstrainInfo.getConstrainAction(),
                                               equipmentConstrainInfo.getConstrainType(),
                                               equipmentConstrainInfo.getConstrainStatus(),
                                               equipmentConstrainInfo.getRemark(), equipmentConstrainInfo.getCreateBy(),
                                               equipmentConstrainInfo.getCreateBy(),
                                               NumberUtils.toLong(equipmentConstrainInfo.getConstrainSeq()),
                                               equipmentConstrainInfo.getRouteId(), equipmentConstrainInfo.getStageId(),
                                               equipmentConstrainInfo.getCustomerId(),
                                               equipmentConstrainInfo.getExpireDate(),
                                               equipmentConstrainInfo.getEffectiveDate(),
                                               equipmentConstrainInfo.getDailyWaferCount(),
                                               equipmentConstrainInfo.getTotalWaferCount(),
                                               equipmentConstrainInfo.getParentEquipmentId(),
                                               equipmentConstrainInfo.getParentEquipmentRrn(),
                                               equipmentConstrainInfo.getLotType(), equipmentConstrainInfo.getEqpGroupId(),
                                               equipmentConstrainInfo.getDailyLotCount(),
                                               equipmentConstrainInfo.getTotalLotCount(),
                                               DateUtils.getSecondsTime(equipmentConstrainInfo.getAutoSwitchTimeInterval()),
                                               equipmentConstrainInfo.getLotPriority(),
                                               equipmentConstrainInfo.getInternalPriority(),
                                               equipmentConstrainInfo.getExceptProductId(),
                                               equipmentConstrainInfo.getExceptProcessId(),
                                               equipmentConstrainInfo.getExceptLotId(),
                                               equipmentConstrainInfo.getExceptOperationId(),
                                               equipmentConstrainInfo.getExceptStageId(),
                                               equipmentConstrainInfo.getExceptRecipeId(),
                                               equipmentConstrainInfo.getExceptCustomerId(),
                                               equipmentConstrainInfo.getExceptLotType(),
                                               equipmentConstrainInfo.getExceptLotPriority(),
                                               equipmentConstrainInfo.getExceptInternalPriority(),
                                               equipmentConstrainInfo.getExceptEqpGroupId(),
                                               equipmentConstrainInfo.getQeFlag()};
            jdbcTemplate.update(sql, objectArgs);
        }

        @Override
        public void modifyEquipmentConstrainInfo(EquipmentConstrainInfo equipmentConstrainInfo) {
            String sql = " update equipment_constrain_info set  facility_rrn=?,product_id=?,process_id=?," +
                    "operation_id=?,recipe_id=?,lot_id=?," + " equipment_id=?,constrain_action=?,constrain_type=?," +
                    "constrain_status=?,remark=?," + "modify_by=?," + "constrain_seq=?,modify_time=sysdate,route_id=?," +
                    "stage_id = ?, customer_id= ?," + "expire_date = to_timestamp(?,'" + DateUtils.DATE_FORMAT24 + "')," +
                    "effective_date = to_timestamp(?,'" + DateUtils.DATE_FORMAT24 + "'),daily_Wafer_Count = ?, " +
                    "total_Wafer_Count = ?" + ",PARENT_EQUIPMENT_ID = ?,PARENT_EQUIPMENT_RRN = ?," +
                    "LOT_TYPE=?,EQPGROUP_ID=?,DAILY_LOT_COUNT = ?, TOTAL_LOT_COUNT = ?, AUTO_SWITCH_TIME_INTERVAL = ?, " +
                    "LOT_PRIORITY=?, INTERNAL_PRIORITY = ?,EXCEPT_PRODUCT_ID=?, EXCEPT_PROCESS_ID=?, " +
                    "EXCEPT_LOT_ID=?, EXCEPT_OPERATION_ID=?,EXCEPT_STAGE_ID=?, EXCEPT_RECIPE_ID=?," +
                    "EXCEPT_CUSTOMER_ID=?, EXCEPT_LOT_TYPE=?,EXCEPT_LOT_PRIORITY=?,EXCEPT_INTERNAL_PRIORITY=?, " +
                    "EXCEPT_EQPGROUP_ID=?, QE_FLAG=? where constrain_rrn=?";

            Object[] objectArgs = new Object[]{equipmentConstrainInfo.getFacilityRrn(),
                                               equipmentConstrainInfo.getProductId(), equipmentConstrainInfo.getProcessId(),
                                               equipmentConstrainInfo.getOperationId(),
                                               equipmentConstrainInfo.getRecipeId(), equipmentConstrainInfo.getLotId(),
                                               equipmentConstrainInfo.getEquipmentId(),
                                               equipmentConstrainInfo.getConstrainAction(),
                                               equipmentConstrainInfo.getConstrainType(),
                                               equipmentConstrainInfo.getConstrainStatus(),
                                               equipmentConstrainInfo.getRemark(), equipmentConstrainInfo.getModifyBy(),
                                               NumberUtils.toLong(equipmentConstrainInfo.getConstrainSeq()),
                                               equipmentConstrainInfo.getRouteId(), equipmentConstrainInfo.getStageId(),
                                               equipmentConstrainInfo.getCustomerId(),
                                               equipmentConstrainInfo.getExpireDate(),
                                               equipmentConstrainInfo.getEffectiveDate(),
                                               equipmentConstrainInfo.getDailyWaferCount(),
                                               equipmentConstrainInfo.getTotalWaferCount(),
                                               equipmentConstrainInfo.getParentEquipmentId(),
                                               equipmentConstrainInfo.getParentEquipmentRrn(),
                                               equipmentConstrainInfo.getLotType(), equipmentConstrainInfo.getEqpGroupId(),
                                               equipmentConstrainInfo.getDailyLotCount(),
                                               equipmentConstrainInfo.getTotalLotCount(),
                                               DateUtils.getSecondsTime(equipmentConstrainInfo.getAutoSwitchTimeInterval()),
                                               equipmentConstrainInfo.getLotPriority(),
                                               equipmentConstrainInfo.getInternalPriority(),
                                               equipmentConstrainInfo.getExceptProductId(),
                                               equipmentConstrainInfo.getExceptProcessId(),
                                               equipmentConstrainInfo.getExceptLotId(),
                                               equipmentConstrainInfo.getExceptOperationId(),
                                               equipmentConstrainInfo.getExceptStageId(),
                                               equipmentConstrainInfo.getExceptRecipeId(),
                                               equipmentConstrainInfo.getExceptCustomerId(),
                                               equipmentConstrainInfo.getExceptLotType(),
                                               equipmentConstrainInfo.getExceptLotPriority(),
                                               equipmentConstrainInfo.getExceptInternalPriority(),
                                               equipmentConstrainInfo.getExceptEqpGroupId(),
                                               equipmentConstrainInfo.getQeFlag(),
                                               new Long(equipmentConstrainInfo.getConstrainRrn())};
            jdbcTemplate.update(sql, objectArgs);
        }

        @Override
        public void deleteEquipmentConstrainInfoTmp(Long constrainInfoRrn) {
            String sql = "DELETE FROM EQUIPMENT_CONSTRAIN_INFO_TMP L WHERE L.CONSTRAIN_RRN=?";
            jdbcTemplate.update(sql, new Object[]{constrainInfoRrn});
        }

        @Override
        public void deleteEquipmentConstrainInfo(Long constrainInfoRrn) {
            String sql = "DELETE FROM EQUIPMENT_CONSTRAIN_INFO L WHERE L.CONSTRAIN_RRN=?";
            jdbcTemplate.update(sql, new Object[]{constrainInfoRrn});
        }

        @Override
        public Integer getMaxConstrainSeqByEqpt(String eqptId) {
            String sql = "SELECT NVL(MAX(CONSTRAIN_SEQ),0) FROM EQUIPMENT_CONSTRAIN_INFO_TMP WHERE " + "EQUIPMENT_ID=?";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{eqptId}, Integer.class);
        }

        @Override
        public void changeEquipmentConstantToEnable() {
            String sql = "UPDATE EQUIPMENT_CONSTRAIN_INFO SET CONSTRAIN_STATUS = 'ENABLE', MODIFY_TIME = ?," +
                    " EFFECTIVE_DATE = ? WHERE AUTO_SWITCH_TIME_INTERVAL > 0 AND (TO_DATE(?, '" + DateUtils.DATE_FORMAT24 +
                    "') - MODIFY_TIME) * 24 * 60 * 60 > AUTO_SWITCH_TIME_INTERVAL AND CONSTRAIN_STATUS = 'DISABLE'";
            Timestamp now = new Timestamp(System.currentTimeMillis());
            jdbcTemplate.update(sql, now, now, DateUtils.formatDate(now, DateUtils.DATE_FORMAT));
        }

        private String buildSqlForEnableEquipmentConstrains(List<String> params, String equipmentId,
                                                            List<String> constrainRules, String constrainAction) {
            StringBuffer bufferSql = new StringBuffer("SELECT * FROM (");
            for (Iterator iterator = constrainRules.iterator(); iterator.hasNext(); ) {
                String constrainRule = (String) iterator.next();
                if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.NORMAL_EQUIPMENT_RULE)) {
                    bufferSql.append(" select * from equipment_constrain_info where constrain_type='0' and " +
                                             "constrain_status = 'ENABLE' ");
                    if (StringUtils.isNotEmpty(equipmentId)) {
                        bufferSql.append(" AND  equipment_id = ? ");
                        params.add(equipmentId);
                    }

                    if (StringUtils.isNotBlank(constrainAction)) {
                        bufferSql.append(" and  constrain_action = ? ");
                        params.add(constrainAction);
                    }
                }
                if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.FREE_EQUIPMENT_RULE)) {
                    bufferSql.append(" select * from equipment_constrain_info where constrain_type='1' and " +
                                             "constrain_status = 'ENABLE' ");
                    if (StringUtils.isNotBlank(constrainAction)) {
                        bufferSql.append(" and  constrain_action = ? ");
                        params.add(constrainAction);
                    }
                }
                if (iterator.hasNext()) {
                    bufferSql.append(" UNION ALL ");
                }
            }
            bufferSql.append(" ) constrain ");

            bufferSql.append(" ORDER BY CONSTRAIN_SEQ,CONSTRAIN_TYPE  ");

            return bufferSql.toString();
        }

        private String validationSqlForEquipmentConstrains(Collection params, Map condtionMap) {

            Collection constrainRules = (Collection) condtionMap.get("constrainRule");
            String equipmentId = MapUtils.getString(condtionMap, "equipmentId");
            String productId = MapUtils.getString(condtionMap, "productId");
            String processId = MapUtils.getString(condtionMap, "processId");
            String lotId = MapUtils.getString(condtionMap, "lotId");
            String operationId = MapUtils.getString(condtionMap, "operationId");
            String recipeId = MapUtils.getString(condtionMap, "recipeId");
            String constrainSeq = MapUtils.getString(condtionMap, "constrainSeq");
            String routeId = MapUtils.getString(condtionMap, "routeId");
            String customerId = MapUtils.getString(condtionMap, "customerId");
            String stageId = MapUtils.getString(condtionMap, "stageId");
            String lotType = MapUtils.getString(condtionMap, "lotType");
            String eqpGroupId = MapUtils.getString(condtionMap, "eqpGroupId");
            String lotPriority = MapUtils.getString(condtionMap, "lotPriority");
            String internalPriority = MapUtils.getString(condtionMap, "internalPriority");
            String exceptProductId = MapUtils.getString(condtionMap, "exceptProductId");
            String exceptProcessId = MapUtils.getString(condtionMap, "exceptProcessId");
            String exceptLotId = MapUtils.getString(condtionMap, "exceptLotId");
            String exceptOperationId = MapUtils.getString(condtionMap, "exceptOperationId");
            String exceptStageId = MapUtils.getString(condtionMap, "exceptStageId");
            String exceptRecipeId = MapUtils.getString(condtionMap, "exceptRecipeId");
            String exceptCustomerId = MapUtils.getString(condtionMap, "exceptCustomerId");
            String exceptLotType = MapUtils.getString(condtionMap, "exceptLotType");
            String exceptLotPriority = MapUtils.getString(condtionMap, "exceptLotPriority");
            String exceptInternalPriority = MapUtils.getString(condtionMap, "exceptInternalPriority");
            String exceptEqpGroupId = MapUtils.getString(condtionMap, "exceptEqpGroupId");

            StringBuffer bufferSql = new StringBuffer("SELECT * FROM (");
            for (Iterator iterator = constrainRules.iterator(); iterator.hasNext(); ) {
                String constrainRule = (String) iterator.next();
                if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.NORMAL_EQUIPMENT_RULE) ||
                        StringUtils.equals(constrainRule, EquipmentConstrainInfo.FREE_EQUIPMENT_RULE)) {
                    bufferSql
                            .append(" select " + getEquipConstrainsTmpSql() + " from equipment_constrain_info_tmp  where ");
                    bufferSql.append(" equipment_id = ? ");
                    params.add(equipmentId);

                    if (StringUtils.isNotEmpty(constrainSeq)) {
                        bufferSql.append(" AND  (constrain_seq = ? or (");
                        params.add(constrainSeq);
                    }

                    if (StringUtils.isNotEmpty(productId)) {
                        bufferSql.append(" product_id = ? ");
                        params.add(productId);
                    } else {
                        bufferSql.append(" product_id is null ");
                    }

                    if (StringUtils.isNotEmpty(processId)) {
                        bufferSql.append(" AND process_id = ? ");
                        params.add(processId);
                    } else {
                        bufferSql.append(" AND process_id is null ");
                    }

                    if (StringUtils.isNotEmpty(lotId)) {
                        bufferSql.append(" AND lot_id = ? ");
                        params.add(lotId);
                    } else {
                        bufferSql.append(" AND lot_id is null ");
                    }

                    if (StringUtils.isNotEmpty(operationId)) {
                        bufferSql.append(" AND operation_id = ? ");
                        params.add(operationId);
                    } else {
                        bufferSql.append(" AND operation_id is null ");
                    }

                    if (StringUtils.isNotEmpty(recipeId)) {
                        bufferSql.append(" AND recipe_id = ? ");
                        params.add(recipeId);
                    } else {
                        bufferSql.append(" AND recipe_id is null ");
                    }

                    if (StringUtils.isNotEmpty(routeId)) {
                        bufferSql.append(" AND route_id = ? ");
                        params.add(recipeId);
                    } else {
                        bufferSql.append(" AND route_id is null ");
                    }

                    if (StringUtils.isNotEmpty(stageId)) {
                        bufferSql.append(" AND stage_id = ? ");
                        params.add(stageId);
                    } else {
                        bufferSql.append(" AND stage_id is null ");
                    }

                    if (StringUtils.isNotEmpty(customerId)) {
                        bufferSql.append(" AND customer_id = ? ");
                        params.add(customerId);
                    } else {
                        bufferSql.append(" AND customer_id is null ");
                    }

                    if (StringUtils.isNotEmpty(lotType)) {
                        bufferSql.append(" AND lot_type = ? ");
                        params.add(customerId);
                    } else {
                        bufferSql.append(" AND lot_type is null ");
                    }

                    if (StringUtils.isNotEmpty(eqpGroupId)) {
                        bufferSql.append(" AND eqpgroup_id = ? ");
                        params.add(customerId);
                    } else {
                        bufferSql.append(" AND eqpgroup_id is null ");
                    }

                    if (StringUtils.isNotEmpty(lotPriority)) {
                        bufferSql.append(" AND LOT_PRIORITY = ? ");
                        params.add(lotPriority);
                    } else {
                        bufferSql.append(" AND LOT_PRIORITY is null ");
                    }

                    if (StringUtils.isNotEmpty(internalPriority)) {
                        bufferSql.append(" AND INTERNAL_PRIORITY = ? ");
                        params.add(internalPriority);
                    } else {
                        bufferSql.append(" AND INTERNAL_PRIORITY is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptProductId)) {
                        bufferSql.append(" AND EXCEPT_PRODUCT_ID = ? ");
                        params.add(exceptProductId);
                    } else {
                        bufferSql.append(" AND EXCEPT_PRODUCT_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptProcessId)) {
                        bufferSql.append(" AND EXCEPT_PROCESS_ID = ? ");
                        params.add(exceptProcessId);
                    } else {
                        bufferSql.append(" AND EXCEPT_PROCESS_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptLotId)) {
                        bufferSql.append(" AND EXCEPT_LOT_ID = ? ");
                        params.add(exceptLotId);
                    } else {
                        bufferSql.append(" AND EXCEPT_LOT_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptOperationId)) {
                        bufferSql.append(" AND EXCEPT_OPERATION_ID = ? ");
                        params.add(exceptOperationId);
                    } else {
                        bufferSql.append(" AND EXCEPT_OPERATION_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptStageId)) {
                        bufferSql.append(" AND EXCEPT_STAGE_ID = ? ");
                        params.add(exceptStageId);
                    } else {
                        bufferSql.append(" AND EXCEPT_STAGE_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptRecipeId)) {
                        bufferSql.append(" AND EXCEPT_RECIPE_ID = ? ");
                        params.add(exceptRecipeId);
                    } else {
                        bufferSql.append(" AND EXCEPT_RECIPE_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptCustomerId)) {
                        bufferSql.append(" AND EXCEPT_CUSTOMER_ID = ? ");
                        params.add(exceptCustomerId);
                    } else {
                        bufferSql.append(" AND EXCEPT_CUSTOMER_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptLotType)) {
                        bufferSql.append(" AND EXCEPT_LOT_TYPE = ? ");
                        params.add(exceptLotType);
                    } else {
                        bufferSql.append(" AND EXCEPT_LOT_TYPE is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptLotPriority)) {
                        bufferSql.append(" AND EXCEPT_LOT_PRIORITY = ? ");
                        params.add(exceptLotPriority);
                    } else {
                        bufferSql.append(" AND EXCEPT_LOT_PRIORITY is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptInternalPriority)) {
                        bufferSql.append(" AND EXCEPT_INTERNAL_PRIORITY = ? ");
                        params.add(exceptInternalPriority);
                    } else {
                        bufferSql.append(" AND EXCEPT_INTERNAL_PRIORITY is null ");
                    }

                    if (StringUtils.isNotEmpty(exceptEqpGroupId)) {
                        bufferSql.append(" AND EXCEPT_EQPGROUP_ID = ? ");
                        params.add(exceptEqpGroupId);
                    } else {
                        bufferSql.append(" AND EXCEPT_EQPGROUP_ID is null ");
                    }

                    if (StringUtils.isNotEmpty(constrainSeq)) {
                        bufferSql.append(" ))");
                    }
                }
                if (iterator.hasNext()) {
                    bufferSql.append(" UNION ALL ");
                }
            }
            bufferSql.append(" ) constrain ");

            bufferSql.append(" ORDER BY CONSTRAIN_SEQ,CASE WHEN CONSTRAIN_ACTION='" + EquipmentConstrainInfo.REJECTSTOP +
                                     "' THEN " + "0 ELSE 1 END ,CONSTRAIN_TYPE");

            return bufferSql.toString();
        }

        private String buildSqlForEquipmentConstrains(List<Object> params, Map conditionMap) {
            Collection constrainRules = (Collection) conditionMap.get("constrainRule");
            String equipmentId = MapUtils.getString(conditionMap, "equipmentId");

            StringBuilder bufferSql = new StringBuilder("SELECT * FROM (");

            for (Iterator iterator = constrainRules.iterator(); iterator.hasNext(); ) {
                String constrainRule = (String) iterator.next();
                if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.NORMAL_EQUIPMENT_RULE)) {
                    bufferSql.append(" select ").append(getEquipConstrainsSql())
                             .append(" from equipment_constrain_info  where ").append("constrain_type='0' ");
                    if (StringUtils.isNotEmpty(equipmentId)) {
                        bufferSql.append(" AND  equipment_id LIKE ? ");
                        params.add(equipmentId);
                    }
                }
                if (StringUtils.equals(constrainRule, EquipmentConstrainInfo.FREE_EQUIPMENT_RULE)) {
                    bufferSql.append(" select ").append(getEquipConstrainsSql())
                             .append(" from equipment_constrain_info  where  ")
                             .append("constrain_type='1' ");
                }
                if (iterator.hasNext()) {
                    bufferSql.append(" UNION ALL ");
                }
            }
            bufferSql.append(" ) constrain WHERE 1=1 ");

            this.buildArgs(conditionMap, bufferSql);

            bufferSql.append(" ORDER BY EQUIPMENT_ID ,CONSTRAIN_SEQ ,CASE WHEN CONSTRAIN_ACTION='" + EquipmentConstrainInfo.REJECTSTOP +
                                     "' THEN 0 ELSE 1 END ,CONSTRAIN_TYPE");

            return bufferSql.toString();
        }

        private void buildArgs(Map conditionMap, StringBuilder bufferSql) {
            String condition;
            if (StringUtils.isNotBlank(condition = MapUtils.getString(conditionMap, "runFunc"))){
                bufferSql.append(" AND CONSTRAIN_ACTION = '")
                         .append(StringUtils.equalsIgnoreCase(condition, "RN")?EquipmentConstrainInfo.REJECTSTOP:EquipmentConstrainInfo.ACCEPT)
                         .append("'");
            }
            if (StringUtils.isNotBlank(condition = MapUtils.getString(conditionMap, "isExpired"))){
                bufferSql.append(" AND EXPIRE_DATE ")
                         .append(StringUtils.equalsIgnoreCase(condition, "YES")?SystemConstant.Str.MORE_THAN:SystemConstant.Str.LESS_THAN)
                         .append("SYSDATE ");
            }
        }

        private String getEquipConstrainsTmpSql() {
            return getEquipConstrainsSql() + ",trans_Name";
        }

        private String getEquipConstrainsSql() {
            return " FACILITY_RRN,CONSTRAIN_RRN,PRODUCT_ID,PROCESS_ID,OPERATION_ID,RECIPE_ID,LOT_ID,"
                    + "EQUIPMENT_ID,CONSTRAIN_ACTION,CONSTRAIN_TYPE,CONSTRAIN_STATUS,REMARK,CREATE_BY,"
                    + "MODIFY_BY,CREATE_TIME,MODIFY_TIME,ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,"
                    + "ATTRIBUTE_DATA4,ATTRIBUTE_DATA5,CONSTRAIN_SEQ,ROUTE_ID,STAGE_ID,CUSTOMER_ID,EXPIRE_DATE,"
                    + "EFFECTIVE_DATE,DAILY_WAFER_COUNT,TOTAL_WAFER_COUNT,PARENT_EQUIPMENT_ID,PARENT_EQUIPMENT_RRN,"
                    + "LOT_TYPE, EQPGROUP_ID, DAILY_LOT_COUNT,TOTAL_LOT_COUNT,AUTO_SWITCH_TIME_INTERVAL,LOT_PRIORITY,"
                    + "INTERNAL_PRIORITY,EXCEPT_PRODUCT_ID, EXCEPT_PROCESS_ID, EXCEPT_LOT_ID,EXCEPT_OPERATION_ID, "
                    + "EXCEPT_STAGE_ID, EXCEPT_RECIPE_ID, EXCEPT_CUSTOMER_ID, EXCEPT_LOT_TYPE, EXCEPT_LOT_PRIORITY, "
                    + "EXCEPT_INTERNAL_PRIORITY, EXCEPT_EQPGROUP_ID,QE_FLAG ";
        }

    }