# ProcessSpecItemDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.server.spec.dao.ProcessSpecItemDao;
    import com.mycim.server.spec.dao.mapper.ProcessSpecItemRowMapper;
    import com.mycim.valueobject.prp.ProcessSpecItem;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

    @Repository
    public class ProcessSpecItemDaoImpl implements ProcessSpecItemDao {

        @Autowired
        private JdbcTemplate jdbcTemplate;

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

            sql.append(" PROCESS_SPEC_ITEM ");
            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 deleteProcessSpecItems(List<ProcessSpecItem> processSpecItems) {
            StringBuilder sql = new StringBuilder("delete ");

            sql.append(" PROCESS_SPEC_ITEM ");
            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.getProcessRrn(), processSpecItem.getProcessVersion(),
                                          processSpecItem.getRouteRrn(), processSpecItem.getOperationRrn()});

            }

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

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

            sql.append(" PROCESS_SPEC_ITEM ");
            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 void updateProcessSpecItemsForStatus(List<ProcessSpecItem> processSpecItems) {
            StringBuilder sql = new StringBuilder("update ");

            sql.append(" PROCESS_SPEC_ITEM ");
            sql.append(" set ECN_RRN = ?, STATUS = ?, EFFECTIVE_TIME = ?, TERMINATED_TIME = ?, ");
            sql.append(" UPDATED_USER = ?, UPDATED_TIME = ? ");
            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.getEcnRrn(), processSpecItem.getStatus(),
                                          processSpecItem.getEffectiveTime(), processSpecItem.getTerminatedTime(),
                                          processSpecItem.getUpdatedUser(), processSpecItem.getUpdatedTime(),
                                          processSpecItem.getProcessRrn(), processSpecItem.getProcessVersion(),
                                          processSpecItem.getRouteRrn(), processSpecItem.getOperationRrn()});
            }

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

        @Override
        public ProcessSpecItem getProcessSpecItemByPrimaryKey(Long processRrn, Integer processVersion, Long routeRrn,
                                                              Long operationRrn) {
            StringBuilder sql = new StringBuilder("select ");

            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 ");
            sql.append(getProcessSpecItemColumn());
            sql.append(" from ").append(" PROCESS_SPEC_ITEM ");
            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? and ROUTE_RRN = ? and OPERATION_RRN = ? ");

            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new ProcessSpecItemRowMapper(), processRrn, processVersion, routeRrn,
                                    operationRrn);
        }

        @Override
        public List<ProcessSpecItem> getProcessSpecItems(Long processRrn, Integer processVersion) {
            StringBuilder sql = new StringBuilder("select ");

            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 ");
            sql.append(getProcessSpecItemColumn());
            sql.append(" from ").append(" PROCESS_SPEC_ITEM ");
            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? ");

            return jdbcTemplate.query(sql.toString(), new ProcessSpecItemRowMapper(), processRrn, processVersion);
        }

        @Override
        public List<ProcessSpecItem> getProcessSpecItemsWithVariables(Long processRrn, Integer processVersion) {
            StringBuilder sql = new StringBuilder("select ");

            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 ");
            sql.append(getProcessSpecItemColumn());
            sql.append(" from ").append(" PROCESS_SPEC_ITEM ");
            sql.append(" WHERE PROCESS_RRN = ? AND PROCESS_VERSION = ? AND VARIABLE_FLAG = ? ");

            return jdbcTemplate.query(sql.toString(), new ProcessSpecItemRowMapper(), processRrn, processVersion, true);
        }

        @Override
        public List<ProcessSpecItem> getProcessSpecItemsForStatus(Long processRrn, Integer processVersion, String status) {
            StringBuilder sql = new StringBuilder("select ");

            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 ");
            sql.append(getProcessSpecItemColumn());
            sql.append(" from ").append(" PROCESS_SPEC_ITEM ");
            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? and STATUS = ? ");

            return jdbcTemplate.query(sql.toString(), new ProcessSpecItemRowMapper(), processRrn, processVersion, status);
        }

        @Override
        public boolean checkFlipSetTogether(Long processRrn, Integer processVersion) {
            String sql = "SELECT COUNT(1) SIZE_COUNT, SUM(CASE WHEN LENGTH(TRIM(FLIP_TYPE))>0 THEN 1 ELSE 0 END) FLIP_COUNT"
            + " FROM PROCESS_SPEC_ITEM WHERE PROCESS_RRN = ? AND PROCESS_VERSION = ? ";
            Map<String, Integer> countMap = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{processRrn, processVersion},
                                                                         (RowMapper<Map>) (rs, rowNum) -> {
                                                                             Map map = new HashMap();
                                                                             map.put("sizeCount", rs.getInt("SIZE_COUNT"));
                                                                             map.put("flipCount", rs.getInt("FLIP_COUNT"));
                                                                             return map;
                                                                         });
            int sizeCount = countMap.get("sizeCount");
            int flipCount = countMap.get("flipCount");
            return flipCount != 0 && sizeCount != flipCount;
        }

        private String getProcessSpecItemColumn() {
            StringBuilder sb = new StringBuilder();
            sb.append(" PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ROUTE_RRN, ROUTE_ID, ROUTE_VERSION, ");
            sb.append(" ROUTE_SEQ, OPERATION_RRN, OPERATION_ID, OPERATION_DESC, OPERATION_TYPE, ");
            sb.append(" OPERATION_SEQ, FLOW_SEQ, WORK_AREA, STAGE_ID, RECIPE_RRN, RECIPE_ID, ");
            sb.append(" RETICLE_FAMILY_RRN, RETICLE_FAMILY_ID, PARAMETER_SET_RRN, PARAMETER_SET_ID, ");
            sb.append(" EQUIPMENT_GROUP_RRN, EQUIPMENT_GROUP_ID, POLLUTION_LEVEL, PROCESS_LOCATION, BANK_FLAG, ");
            sb.append(" CP_DEFINE, ECN_RRN, STATUS, EFFECTIVE_TIME, TERMINATED_TIME, CREATED_USER, CREATED_TIME, ");
            sb.append(" UPDATED_USER, UPDATED_TIME, VARIABLE_FLAG, EQUIPMENT_GROUP_RRNS ");
            return sb.toString();
        }

    }