# EquipmentConstrainPairDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.oid.IDGenerators;
    import com.mycim.framework.oid.type.IDNames;
    import com.mycim.framework.oid.type.SequenceNames;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.server.constrain.dao.EquipmentConstrainPairDAO;
    import com.mycim.server.constrain.dao.mapper.EquipmentConstrainPairDetailMapper;
    import com.mycim.server.constrain.dao.mapper.EquipmentConstrainPairMapper;
    import com.mycim.server.constrain.dao.mapper.LotConstrainTriggerMapper;
    import com.mycim.valueobject.ems.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.util.List;

    /**
     * @author shijie.deng
     * @version 6.0.0
     * @date 2019/9/9
     **/
    @Repository
    public class EquipmentConstrainPairDAOImpl implements EquipmentConstrainPairDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public long insertConstrainPair(EquipmentConstrainPair constrain) {
            Long rrn = Long.valueOf(IDGenerators.get(IDNames.SEQUENCE).generateId(SequenceNames.MODULE_SEQ_OBJECT_RRN));
            constrain.setConstrainPairRrn(rrn);

            final String sql = "INSERT INTO EQUIPMENT_CONSTRAIN_PAIR (CONSTRAIN_PAIR_RRN,FACILITY_RRN,PRODUCT_RRN," +
                    "PROCESS_RRN,LOT_RRN,TYPE," + "REMARK,STATUS,VERSION,CREATE_BY," + "CREATE_TIME,MODIFY_BY," +
                    "MODIFY_TIME,ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3," + "ATTRIBUTE_DATA4,ATTRIBUTE_DATA5)" +
                    " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            Object[] args = {constrain.getConstrainPairRrn(), constrain.getFacilityRrn(), constrain.getProductRrn(),
                    constrain.getProcessRrn(), constrain.getLotRrn(), constrain.getType(), constrain.getRemark(),
                    constrain.getStatus(), constrain.getVersion(), constrain.getCreateBy(), constrain.getCreateTime(),
                    constrain.getModifyBy(), constrain.getModifyTime(), constrain.getAttributeData1(),
                    constrain.getAttributeData2(), constrain.getAttributeData3(), constrain.getAttributeData4(),
                    constrain.getAttributeData5()};

            return jdbcTemplate.update(sql, args) > 0 ? rrn : 0L;
        }

        @Override
        public void updateConstrainPair(EquipmentConstrainPair constrain) {

            String sql = "UPDATE EQUIPMENT_CONSTRAIN_PAIR SET REMARK=?,STATUS=?,VERSION=?,MODIFY_BY=?,MODIFY_TIME=?," +
                    "ATTRIBUTE_DATA1=?," + "ATTRIBUTE_DATA2=?,ATTRIBUTE_DATA3=?,ATTRIBUTE_DATA4=?,ATTRIBUTE_DATA5=? WHERE" +
                    " " + "CONSTRAIN_PAIR_RRN=?";
            Object[] args = {constrain.getRemark(), constrain.getStatus(), constrain.getVersion(),
                    constrain.getModifyBy(), constrain.getModifyTime(), constrain.getAttributeData1(),
                    constrain.getAttributeData2(), constrain.getAttributeData3(), constrain.getAttributeData4(),
                    constrain.getAttributeData5(), constrain.getConstrainPairRrn()};
            jdbcTemplate.update(sql, args);

        }

        @Override
        public EquipmentConstrainPair getConstrainPairByRrn(long constrainRrn) {
            String sql = "SELECT ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4," + "ATTRIBUTE_DATA5," +
                    "CONSTRAIN_PAIR_RRN,CREATE_BY," + "CREATE_TIME,FACILITY_RRN,LOT_RRN,MODIFY_BY,MODIFY_TIME," +
                    "PROCESS_RRN,PRODUCT_RRN," + "REMARK,STATUS,TYPE,VERSION FROM EQUIPMENT_CONSTRAIN_PAIR WHERE " +
                    "CONSTRAIN_PAIR_RRN=?";
            Object[] args = {constrainRrn};
            EquipmentConstrainPair constrain = jdbcTemplate
                    .queryForObjectWithNull(sql, args, new EquipmentConstrainPairMapper());
            return constrain;
        }

        @Override
        public void insertConstrainPairH(EquipmentConstrainPairHistory constrainHistory) {
            String sql = "INSERT INTO EQUIPMENT_CONSTRAIN_PAIR_H (TRANS_RRN,CONSTRAIN_RRN,FACILITY_RRN,PRODUCT_ID," +
                    "PROCESS_ID," + "LOT_ID,REMARK,CREATE_BY,CREATE_TIME)" + " VALUES(?,?,?,?,?,?,?,?,?)";
            Object[] args = {constrainHistory.getTransRrn(), constrainHistory.getConstrainRrn(),
                    constrainHistory.getFacilityRrn(), constrainHistory.getProductId(), constrainHistory.getProcessId(),
                    constrainHistory.getLotId(), constrainHistory.getRemark(), constrainHistory.getCreateBy(),
                    constrainHistory.getCreateTime()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public Long insertConstrainPairDetail(EquipmentConstrainPairDetail detail) {
            Long rrn = Long.valueOf(IDGenerators.get(IDNames.SEQUENCE).generateId(SequenceNames.MODULE_SEQ_OBJECT_RRN));
            final String sql = "INSERT INTO CONSTRAIN_PAIR_DETAIL (CONSTRAIN_DETAIL_RRN,CONSTRAIN_RRN,PRODUCT_RRN," +
                    "PROCESS_RRN,ROUTE_ID," + "ROUTE_RRN,ROUTE_SEQ,OPERATION_ID," + "OPERATION_RRN,OPERATION_SEQ," +
                    "RETICLEFAMILY_ID,LAYER_ID,ISBENCHMARK,PLANA,PLANB,PLANC," + "PLAND,PLANE,PLANF,PLANG,PLANH," +
                    "ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4,ATTRIBUTE_DATA5)" + " VALUES(?,?,?," +
                    "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            Object[] args = {rrn, detail.getConstrainRrn(), detail.getProductRrn(), detail.getProcessRrn(),
                    detail.getRouteId(), detail.getRouteRrn(), detail.getRouteSeq(), detail.getOperationId(),
                    detail.getOperationRrn(), detail.getOperationSeq(), detail.getReticlefamilyId(), detail.getLayerId(),
                    detail.getIsBenchmark(), detail.getPlanA(), detail.getPlanB(), detail.getPlanC(), detail.getPlanD(),
                    detail.getPlanE(), detail.getPlanF(), detail.getPlanG(), detail.getPlanH(),
                    detail.getAttributeData1(), detail.getAttributeData2(), detail.getAttributeData3(),
                    detail.getAttributeData4(), detail.getAttributeData5()};

            return jdbcTemplate.update(sql, args) > 0 ? rrn : 0L;
        }

        @Override
        public void updateConstrainPairDetail(EquipmentConstrainPairDetail detail) {

            String sql = "UPDATE CONSTRAIN_PAIR_DETAIL SET ISBENCHMARK=?,PLANA=?,PLANB=?,PLANC=?,PLAND=?,PLANE=?," +
                    "PLANF=?,PLANG=?,PLANH=?," + "ATTRIBUTE_DATA1=?,ATTRIBUTE_DATA2=?,ATTRIBUTE_DATA3=?," +
                    "ATTRIBUTE_DATA4=?," + "ATTRIBUTE_DATA5=? WHERE CONSTRAIN_DETAIL_RRN=?";
            Object[] args = {detail.getIsBenchmark(), detail.getPlanA(), detail.getPlanB(), detail.getPlanC(),
                    detail.getPlanD(), detail.getPlanE(), detail.getPlanF(), detail.getPlanG(), detail.getPlanH(),
                    detail.getAttributeData1(), detail.getAttributeData2(), detail.getAttributeData3(),
                    detail.getAttributeData4(), detail.getAttributeData5(), detail.getConstrainDetailRrn()};
            jdbcTemplate.update(sql, args);

        }

        @Override
        public void insertConstrainPairDetailH(EquipmentConstrainPairDetailH detailhistory) {
            String sql = "INSERT INTO CONSTRAIN_PAIR_DETAIL_H (TRANS_RRN,CONSTRAIN_DETAIL_RRN,CONSTRAIN_RRN," +
                    "PRODUCT_ID,PROCESS_ID,LOT_ID," + "OPERATION_DETAIL,REMARK)" + " VALUES(?,?,?,?,?,?,?,?)";
            Object[] args = {detailhistory.getTransRrn(), detailhistory.getConstrainDetailRrn(),
                    detailhistory.getConstrainRrn(), detailhistory.getProductId(), detailhistory.getProcessId(),
                    detailhistory.getLotId(), detailhistory.getOperationDetail(), detailhistory.getRemark()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public EquipmentConstrainPair getConstrainPairByProductAndProcess(long productRrn, long processRrn) {
            String sql = "SELECT ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4," + "ATTRIBUTE_DATA5," +
                    "CONSTRAIN_PAIR_RRN,CREATE_BY," + "CREATE_TIME,FACILITY_RRN,LOT_RRN,MODIFY_BY,MODIFY_TIME," +
                    "PROCESS_RRN,PRODUCT_RRN," + "REMARK,STATUS,TYPE,VERSION FROM EQUIPMENT_CONSTRAIN_PAIR WHERE  " +
                    "PRODUCT_RRN=? AND " + "PROCESS_RRN=?";
            Object[] args = {productRrn, processRrn};
            EquipmentConstrainPair equipmentConstrainPair = jdbcTemplate
                    .queryForObjectWithNull(sql, args, new EquipmentConstrainPairMapper());
            return equipmentConstrainPair;
        }

        @Override
        public EquipmentConstrainPair getConstrainPairByLot(long lotRrn) {

            String sql = "SELECT ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4," + "ATTRIBUTE_DATA5," +
                    "CONSTRAIN_PAIR_RRN,CREATE_BY," + "CREATE_TIME,FACILITY_RRN,LOT_RRN,MODIFY_BY,MODIFY_TIME," +
                    "PROCESS_RRN,PRODUCT_RRN," + "REMARK,STATUS,TYPE,VERSION FROM EQUIPMENT_CONSTRAIN_PAIR WHERE LOT_RRN=?";
            Object[] args = {lotRrn};
            EquipmentConstrainPair constrainPair = jdbcTemplate
                    .queryForObjectWithNull(sql, args, new EquipmentConstrainPairMapper());
            return constrainPair;

        }

        @Override
        public List<EquipmentConstrainPairDetail> getConstrainPairDetailsByConstrainRrn(long constrainRrn) {
            String sql = "SELECT ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4," + "ATTRIBUTE_DATA5," +
                    "CONSTRAIN_DETAIL_RRN,CONSTRAIN_RRN,ISBENCHMARK,LAYER_ID," + "OPERATION_ID,OPERATION_RRN," +
                    "OPERATION_SEQ,PLANA,PLANB,PLANC,PLAND,PLANE,PLANF,PLANG," + "PLANH,PROCESS_RRN,PRODUCT_RRN," +
                    "RETICLEFAMILY_ID,ROUTE_ID,ROUTE_RRN,ROUTE_SEQ FROM " + "CONSTRAIN_PAIR_DETAIL WHERE CONSTRAIN_RRN=?";

            List<EquipmentConstrainPairDetail> details = jdbcTemplate
                    .query(sql, new EquipmentConstrainPairDetailMapper(), constrainRrn);
            return details;
        }

        @Override
        public void insertLotTrigger(LotConstrainTrigger trigger) {

            String sql = "INSERT INTO LOT_CONSTRAIN_TRIGGER (TRIGGER_RRN,LOT_ID,LOT_RRN,TRIGGER_ROUTE_ID," +
                    "TRIGGER_ROUTE_SEQ," + "TRIGGER_OPERATION_ID,TRIGGER_OPERATION_SEQ,CONSTRAIN_RRN,PLAN,TRIGGER_TIME," +
                    "STEP_SEQ)" + " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
            Object[] args = {trigger.getTriggerRrn(), trigger.getLotId(), trigger.getLotRrn(),
                    trigger.getTriggerRouteId(), trigger.getTriggerRouteSeq(), trigger.getTriggerOperationId(),
                    trigger.getTriggerOperationSeq(), trigger.getConstrainRrn(), trigger.getPlan(), new java.sql.Date(
                    trigger.getTriggerTime().getTime()), trigger.getStepSeq()};
            jdbcTemplate.update(sql, args);

        }

        @Override
        public EquipmentConstrainPairDetail getConstrainPairDetailByRunInfo(long constrainRrn, long routeRrn,
                                                                            long operationRrn, String routeSeq,
                                                                            String operationSeq) {
            String sql = "SELECT ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4," + "ATTRIBUTE_DATA5," +
                    "CONSTRAIN_DETAIL_RRN,CONSTRAIN_RRN,ISBENCHMARK,LAYER_ID," + "OPERATION_ID,OPERATION_RRN," +
                    "OPERATION_SEQ,PLANA,PLANB,PLANC,PLAND,PLANE,PLANF,PLANG," + "PLANH,PROCESS_RRN,PRODUCT_RRN," +
                    "RETICLEFAMILY_ID,ROUTE_ID,ROUTE_RRN,ROUTE_SEQ from " + "CONSTRAIN_PAIR_DETAIL where CONSTRAIN_RRN=? " +
                    "and ROUTE_RRN=? and OPERATION_RRN=? and" + " OPERATION_SEQ=?";
            Object[] args;
            if (StringUtils.isBlank(routeSeq)) {
                sql = sql + " and ROUTE_SEQ is null";
                args = new Object[]{constrainRrn, routeRrn, operationRrn, operationSeq};
            } else {
                sql = sql + " and ROUTE_SEQ = ?";
                args = new Object[]{constrainRrn, routeRrn, operationRrn, operationSeq, routeSeq};
            }
            EquipmentConstrainPairDetail detail = jdbcTemplate
                    .queryForObjectWithNull(sql, args, new EquipmentConstrainPairDetailMapper());
            return detail;

        }

        @Override
        public LotConstrainTrigger getLotNewTrigger(long lotRrn, long constrainPairRrn) {
            String sql = "SELECT CONSTRAIN_RRN,LOT_ID,LOT_RRN,PLAN,STEP_SEQ,TRIGGER_OPERATION_ID," +
                    "TRIGGER_OPERATION_SEQ,TRIGGER_ROUTE_ID,TRIGGER_ROUTE_SEQ,TRIGGER_RRN,TRIGGER_TIME " + "FROM " +
                    "LOT_CONSTRAIN_TRIGGER WHERE STEP_SEQ=(SELECT MAX(STEP_SEQ) FROM " + "LOT_CONSTRAIN_TRIGGER WHERE " +
                    "LOT_RRN=? AND CONSTRAIN_RRN=?)" + " AND LOT_RRN=? AND CONSTRAIN_RRN=?";
            Object[] args = {lotRrn, constrainPairRrn, lotRrn, constrainPairRrn};
            LotConstrainTrigger trigger = jdbcTemplate.queryForObjectWithNull(sql, args, new LotConstrainTriggerMapper());
            return trigger;
        }

        @Override
        public EquipmentConstrainPairDetail getBenchMarkByConstrainRrn(long constrainRrn) {
            String sql = "SELECT ATTRIBUTE_DATA1,ATTRIBUTE_DATA2,ATTRIBUTE_DATA3,ATTRIBUTE_DATA4," + "ATTRIBUTE_DATA5," +
                    "CONSTRAIN_DETAIL_RRN,CONSTRAIN_RRN,ISBENCHMARK,LAYER_ID," + "OPERATION_ID,OPERATION_RRN," +
                    "OPERATION_SEQ,PLANA,PLANB,PLANC,PLAND,PLANE,PLANF,PLANG," + "PLANH,PROCESS_RRN,PRODUCT_RRN," +
                    "RETICLEFAMILY_ID,ROUTE_ID,ROUTE_RRN,ROUTE_SEQ FROM " + "CONSTRAIN_PAIR_DETAIL WHERE CONSTRAIN_RRN=? " +
                    "AND ISBENCHMARK='true'";
            Object[] args = {constrainRrn};
            EquipmentConstrainPairDetail detail = jdbcTemplate
                    .queryForObjectWithNull(sql, args, new EquipmentConstrainPairDetailMapper());
            return detail;
        }

        @Override
        public void deleteLotConstrainTrigger(Long triggerRrn) {
            String sql = "delete from lot_constrain_trigger where trigger_rrn = ?";
            jdbcTemplate.update(sql, new Object[]{triggerRrn});
        }

    }