# ProcessSpecItemActiveDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.server.spec.dao.ProcessSpecItemActiveDao;
    import com.mycim.server.spec.dao.mapper.ProcessSpecItemRowMapper;
    import com.mycim.valueobject.prp.ProcessSpecItem;
    import com.mycim.valueobject.prp.ProcessSpecItemDto;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

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

    @Repository
    public class ProcessSpecItemActiveDaoImpl implements ProcessSpecItemActiveDao {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public void insertProcessSpecItemsForActive(List<ProcessSpecItem> processSpecItems) {
            StringBuilder sql = new StringBuilder("insert into ");

            sql.append(" PROCESS_SPEC_ITEM_ACTIVE ");
            sql.append(" (PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ROUTE_RRN, ROUTE_ID, ROUTE_VERSION, ");
            sql.append(" ROUTE_SEQ, OPERATION_RRN, OPERATION_ID, OPERATION_DESC, OPERATION_TYPE, ");
            sql.append(" OPERATION_SEQ, FLOW_SEQ, WORK_AREA, STAGE_ID, RECIPE_RRN, RECIPE_ID, ");
            sql.append(" RETICLE_FAMILY_RRN, RETICLE_FAMILY_ID, PARAMETER_SET_RRN, PARAMETER_SET_ID, ");
            sql.append(" EQUIPMENT_GROUP_RRN, EQUIPMENT_GROUP_ID, POLLUTION_LEVEL, PROCESS_LOCATION, ");
            sql.append(" BANK_FLAG, FLIP_TYPE, CP_DEFINE, ECN_RRN, STATUS, EFFECTIVE_TIME, TERMINATED_TIME, ");
            sql.append(" CREATED_USER, CREATED_TIME, UPDATED_USER, UPDATED_TIME, VARIABLE_FLAG, EQUIPMENT_GROUP_RRNS) ");
            sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
            sql.append(" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

            List<Object[]> batchArgs = new ArrayList<>();
            for (ProcessSpecItem processSpecItem : processSpecItems) {
                batchArgs.add(new Object[]{processSpecItem.getProcessRrn(), processSpecItem.getProcessId(),
                                           processSpecItem.getProcessVersion(), processSpecItem.getRouteRrn(),
                                           processSpecItem.getRouteId(), processSpecItem.getRouteVersion(),
                                           processSpecItem.getRouteSeq(), processSpecItem.getOperationRrn(),
                                           processSpecItem.getOperationId(), processSpecItem.getOperationDesc(),
                                           processSpecItem.getOperationType(), processSpecItem.getOperationSeq(),
                                           processSpecItem.getFlowSeq(), processSpecItem.getWorkArea(),
                                           processSpecItem.getStageId(), processSpecItem.getRecipeRrn(),
                                           processSpecItem.getRecipeId(), processSpecItem.getReticleFamilyRrn(),
                                           processSpecItem.getReticleFamilyId(), processSpecItem.getParameterSetRrn(),
                                           processSpecItem.getParameterSetId(), processSpecItem.getEquipmentGroupRrn(),
                                           processSpecItem.getEquipmentGroupId(), processSpecItem.getPollutionLevel(),
                                           processSpecItem.getProcessLocation(), processSpecItem.getBankFlag(),
                                           processSpecItem.getFlipType(), processSpecItem.getCpDefine(),
                                           processSpecItem.getEcnRrn(), processSpecItem.getStatus(),
                                           processSpecItem.getEffectiveTime(), processSpecItem.getTerminatedTime(),
                                           processSpecItem.getCreatedUser(), processSpecItem.getCreatedTime(),
                                           processSpecItem.getUpdatedUser(), processSpecItem.getUpdatedTime(),
                                           processSpecItem.getVariableFlag(), processSpecItem.getEquipmentGroupRrns()});

            }

            jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
        }

        @Override
        public void updateProcessSpecItemsForActive(List<ProcessSpecItem> processSpecItems) {
            StringBuilder sql = new StringBuilder("update ");

            sql.append(" PROCESS_SPEC_ITEM_ACTIVE ");
            sql.append(" set OPERATION_DESC = ?, OPERATION_TYPE = ?, OPERATION_SEQ = ?, FLOW_SEQ = ?, ");
            sql.append(" WORK_AREA = ?, STAGE_ID = ?, RECIPE_RRN = ?, RECIPE_ID = ?, ");
            sql.append(" RETICLE_FAMILY_RRN = ?, RETICLE_FAMILY_ID = ?, PARAMETER_SET_RRN = ?, PARAMETER_SET_ID = ?, ");
            sql.append(" EQUIPMENT_GROUP_RRN = ?, EQUIPMENT_GROUP_ID = ?, POLLUTION_LEVEL = ?, PROCESS_LOCATION = ?, ");
            sql.append(" BANK_FLAG = ?, FLIP_TYPE = ?, CP_DEFINE = ?, ECN_RRN = ?, STATUS = ?, EFFECTIVE_TIME = ?, " +
                               "TERMINATED_TIME = ?, ");
            sql.append(" UPDATED_USER = ?, UPDATED_TIME = ?, VARIABLE_FLAG = ?, EQUIPMENT_GROUP_RRNS = ? ");
            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? and ROUTE_RRN = ? and OPERATION_RRN = ? ");

            List<Object[]> batchArgs = new ArrayList<>();
            for (ProcessSpecItem processSpecItem : processSpecItems) {
                batchArgs.add(new Object[]{processSpecItem.getOperationDesc(), processSpecItem.getOperationType(),
                                           processSpecItem.getOperationSeq(), processSpecItem.getFlowSeq(),
                                           processSpecItem.getWorkArea(), processSpecItem.getStageId(),
                                           processSpecItem.getRecipeRrn(), processSpecItem.getRecipeId(),
                                           processSpecItem.getReticleFamilyRrn(), processSpecItem.getReticleFamilyId(),
                                           processSpecItem.getParameterSetRrn(), processSpecItem.getParameterSetId(),
                                           processSpecItem.getEquipmentGroupRrn(), processSpecItem.getEquipmentGroupId(),
                                           processSpecItem.getPollutionLevel(), processSpecItem.getProcessLocation(),
                                           processSpecItem.getBankFlag(), processSpecItem.getFlipType(),
                                           processSpecItem.getCpDefine(), processSpecItem.getEcnRrn(),
                                           processSpecItem.getStatus(), processSpecItem.getEffectiveTime(),
                                           processSpecItem.getTerminatedTime(), processSpecItem.getUpdatedUser(),
                                           processSpecItem.getUpdatedTime(), processSpecItem.getVariableFlag(),
                                           processSpecItem.getEquipmentGroupRrns(), processSpecItem.getProcessRrn(),
                                           processSpecItem.getProcessVersion(), processSpecItem.getRouteRrn(),
                                           processSpecItem.getOperationRrn()});
            }

            jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
        }

        @Override
        public List<ProcessSpecItem> getProcessSpecItemsForActive(Long processRrn, Integer processVersion) {
            String sql = buildProcessSpecItemSql() + " FROM  PROCESS_SPEC_ITEM_ACTIVE " +
                    " WHERE PROCESS_RRN = ? and PROCESS_VERSION = ? ";
            return jdbcTemplate.query(sql, new ProcessSpecItemRowMapper(), processRrn, processVersion);
        }

        @Override
        public String getEqptGroupIds(Long processRrn, Integer processVersion, Long routeRrn, Long operationRrn) {
            String sql = "SELECT EQUIPMENT_GROUP_ID FROM PROCESS_SPEC_ITEM_ACTIVE WHERE PROCESS_RRN = ? " +
                    " AND PROCESS_VERSION = ? AND ROUTE_RRN = ? AND OPERATION_RRN = ? ";
            String result = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{processRrn, processVersion, routeRrn,
                                                                                  operationRrn}, String.class);
            return result == null ? "" : result;
        }

        @Override
        public String getEqptGroupRrns(Long processRrn, Integer processVersion, Long routeRrn, Long operationRrn) {
            String sql = "SELECT EQUIPMENT_GROUP_RRNS FROM PROCESS_SPEC_ITEM_ACTIVE WHERE PROCESS_RRN = ? " +
                    " AND PROCESS_VERSION = ? AND ROUTE_RRN = ? AND OPERATION_RRN = ? ";
            String result = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{processRrn, processVersion, routeRrn,
                                                                                  operationRrn}, String.class);
            return result == null ? "" : result;
        }

        @Override
        public ProcessSpecItem getProcessSpecItemForActive(ProcessSpecItemDto psid) {
            List<Object> args = new ArrayList<>();
            String sql = buildProcessSpecItemSql() + " FROM  PROCESS_SPEC_ITEM_ACTIVE  WHERE ";
            if (StringUtils.isNotBlank(psid.getFlowSeq())) {
                sql += "PROCESS_RRN = ? AND PROCESS_VERSION = ? AND FLOW_SEQ = ? ";
                args.add(psid.getProcessRrn());
                args.add(psid.getProcessVersion());
                args.add(psid.getFlowSeq());
            } else {
                sql += "PROCESS_RRN = ? AND PROCESS_VERSION = ? AND ROUTE_RRN = ? AND OPERATION_RRN = ?";
                args.add(psid.getProcessRrn());
                args.add(psid.getProcessVersion());
                args.add(psid.getRouteRrn());
                args.add(psid.getOperationRrn());
            }
            return jdbcTemplate.queryForObjectWithNull(sql, new ProcessSpecItemRowMapper(), args.toArray());
        }

        @Override
        public String getFlipType(Long processRrn, Integer processVersion, Long routeRrn, Long operationRrn) {
            String sql = "SELECT FLIP_TYPE FROM PROCESS_SPEC_ITEM_ACTIVE WHERE PROCESS_RRN = ? " +
                    " AND PROCESS_VERSION = ? AND ROUTE_RRN = ? AND OPERATION_RRN = ? ";
            String result = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{processRrn, processVersion, routeRrn,
                                                                                  operationRrn}, String.class);
            return result == null ? "" : result;
        }

        @Override
        public String getProcessLocation(Long processRrn, Integer processVersion, Long routeRrn, Integer routeVersion,
                                         Long operationRrn) {
            String sql = "SELECT PROCESS_LOCATION FROM PROCESS_SPEC_ITEM_ACTIVE WHERE PROCESS_RRN = ? " +
                    " AND PROCESS_VERSION = ? AND ROUTE_RRN = ? AND ROUTE_VERSION = ? AND OPERATION_RRN = ? ";
            String result = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{processRrn, processVersion, routeRrn,
                                                                                  routeVersion, operationRrn}, String.class);
            return result == null ? "" : result;
        }

        private String buildProcessSpecItemSql() {
            return "SELECT PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ROUTE_RRN, ROUTE_ID, ROUTE_VERSION, " +
                    " ROUTE_SEQ, OPERATION_RRN, OPERATION_ID, OPERATION_DESC, OPERATION_TYPE, " +
                    " OPERATION_SEQ, FLOW_SEQ, WORK_AREA, STAGE_ID, RECIPE_RRN, RECIPE_ID, " +
                    " RETICLE_FAMILY_RRN, RETICLE_FAMILY_ID, PARAMETER_SET_RRN, PARAMETER_SET_ID, " +
                    " EQUIPMENT_GROUP_RRN, EQUIPMENT_GROUP_ID, POLLUTION_LEVEL, PROCESS_LOCATION, " +
                    " BANK_FLAG, FLIP_TYPE, CP_DEFINE, ECN_RRN, STATUS, EFFECTIVE_TIME, TERMINATED_TIME, " +
                    " CREATED_USER, CREATED_TIME, UPDATED_USER, UPDATED_TIME, VARIABLE_FLAG, EQUIPMENT_GROUP_RRNS ";
        }

    }