# ProcessSpecItemHistoryDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.server.spec.dao.ProcessSpecItemHistoryDao;
    import com.mycim.server.spec.dao.mapper.ProcessSpecItemHistoryRowMapper;
    import com.mycim.valueobject.prp.ProcessSpecItemHistory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

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

    @Repository
    public class ProcessSpecItemHistoryDaoImpl implements ProcessSpecItemHistoryDao {

        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public void insertProcessSpecItemHistories(List<ProcessSpecItemHistory> processSpecItemHistories) {
            StringBuilder sql = new StringBuilder("insert into ");

            sql.append(" PROCESS_SPEC_ITEM_HISTORY ");

            sql.append(" (TRANS_RRN, TRANS_ID, TRANS_SEQUENCE, SUB_TRANS_ID, TRANS_TIMESTAMP, TRANS_PERFORMED_BY, ");
            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 (ProcessSpecItemHistory processSpecItemHistory : processSpecItemHistories) {
                batchArgs
                        .add(new Object[]{processSpecItemHistory.getTransRrn(), processSpecItemHistory.getTransId(),
                                          processSpecItemHistory.getTransSequence(),
                                          processSpecItemHistory.getSubTransId(),
                                          processSpecItemHistory.getTransTimestamp(),
                                          processSpecItemHistory.getTransPerformedBy(),
                                          processSpecItemHistory.getProcessRrn(), processSpecItemHistory.getProcessId(),
                                          processSpecItemHistory.getProcessVersion(),
                                          processSpecItemHistory.getRouteRrn(), processSpecItemHistory.getRouteId(),
                                          processSpecItemHistory.getRouteVersion(), processSpecItemHistory.getRouteSeq(),
                                          processSpecItemHistory.getOperationRrn(),
                                          processSpecItemHistory.getOperationId(),
                                          processSpecItemHistory.getOperationDesc(),
                                          processSpecItemHistory.getOperationType(),
                                          processSpecItemHistory.getOperationSeq(), processSpecItemHistory.getFlowSeq(),
                                          processSpecItemHistory.getWorkArea(), processSpecItemHistory.getStageId(),
                                          processSpecItemHistory.getRecipeRrn(), processSpecItemHistory.getRecipeId(),
                                          processSpecItemHistory.getReticleFamilyRrn(),
                                          processSpecItemHistory.getReticleFamilyId(),
                                          processSpecItemHistory.getParameterSetRrn(),
                                          processSpecItemHistory.getParameterSetId(),
                                          processSpecItemHistory.getEquipmentGroupRrn(),
                                          processSpecItemHistory.getEquipmentGroupId(),
                                          processSpecItemHistory.getPollutionLevel(),
                                          processSpecItemHistory.getProcessLocation(),
                                          processSpecItemHistory.getBankFlag(), processSpecItemHistory.getFlipType(), processSpecItemHistory.getCpDefine(),
                                          processSpecItemHistory.getEcnRrn(), processSpecItemHistory.getStatus(),
                                          processSpecItemHistory.getEffectiveTime(),
                                          processSpecItemHistory.getTerminatedTime(),
                                          processSpecItemHistory.getCreatedUser(),
                                          processSpecItemHistory.getCreatedTime(),
                                          processSpecItemHistory.getUpdatedUser(),
                                          processSpecItemHistory.getUpdatedTime(),
                                          processSpecItemHistory.getVariableFlag(),
                                          processSpecItemHistory.getEquipmentGroupRrns()});

            }

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

        @Override
        public Page queryProcessSpecItemHistories(Page page, String processId, Integer processVersion, Date queryStartTime,
                                                  Date queryEndTime) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" TRANS_RRN, TRANS_ID, SUB_TRANS_ID, TRANS_SEQUENCE, TRANS_TIMESTAMP, TRANS_PERFORMED_BY, ");
            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(" (SELECT MAX(INNER.TRANS_RRN)  ");
            sql.append(" KEEP ( DENSE_RANK FIRST ORDER BY INNER.TRANS_TIMESTAMP DESC,INNER.TRANS_SEQUENCE ) AS BEFORE_TRANS_RRN ");
            sql.append(" FROM PROCESS_SPEC_ITEM_HISTORY INNER ");
            sql.append(" WHERE INNER.ROUTE_RRN = T.ROUTE_RRN AND INNER.PROCESS_RRN = T.PROCESS_RRN  AND INNER.FLOW_SEQ=T.FLOW_SEQ ");
            sql.append(" AND INNER.PROCESS_VERSION = T.PROCESS_VERSION  ");
            sql.append(" AND INNER.UPDATED_TIME < T.UPDATED_TIME) AS BEFORE_TRANS_RRN ");

            sql.append(" from ").append(" PROCESS_SPEC_ITEM_HISTORY T ");
            sql.append(" where 1 = 1 ");

            List<Object> args = new ArrayList<>();

            if (StringUtils.isNotEmpty(processId)) {
                sql.append(" and PROCESS_ID = ? ");
                args.add(processId);
            }

            if (processVersion != null && processVersion > 0) {
                sql.append(" and PROCESS_VERSION = ? ");
                args.add(processVersion);
            }

            if (queryStartTime != null) {
                sql.append(" and TRANS_TIMESTAMP >= ? ");
                args.add(queryStartTime);
            }

            if (queryEndTime != null) {
                sql.append(" and TRANS_TIMESTAMP <= ? ");
                args.add(queryEndTime);
            }

            sql.append(" ORDER BY TRANS_TIMESTAMP DESC, TRANS_SEQUENCE ASC ");

            StringBuilder tempSql = new StringBuilder(" SELECT T1.*, ");
            tempSql.append(" (SELECT DECODE(T2.RECIPE_ID, T1.RECIPE_ID, 0, 1 )  || ';'  || ");
            tempSql.append(" DECODE( T2.RETICLE_FAMILY_ID ,  T1.RETICLE_FAMILY_ID ,0, 1) || ';' || ");
            tempSql.append(" DECODE( T2.PARAMETER_SET_ID ,  T1.PARAMETER_SET_ID , 0, 1)|| ';' || ");
            tempSql.append("  DECODE( T2.BANK_FLAG ,  T1.BANK_FLAG , 0, 1 ) || ';' || ");
            tempSql.append("  DECODE( T2.FLIP_TYPE ,  T1.FLIP_TYPE , 0, 1 ) ");
            tempSql.append("  FROM PROCESS_SPEC_ITEM_HISTORY T2 ");
            tempSql.append("    WHERE T2.TRANS_RRN= T1.BEFORE_TRANS_RRN ");
            tempSql.append("    AND T2.FLOW_SEQ=T1.FLOW_SEQ ");
            tempSql.append("    ) AS IS_UPDATE_FLAGS ");
            tempSql.append(" FROM ( ").append(sql).append(" ) t1 ");
            return jdbcTemplate.queryForPage(page, tempSql.toString(), args.toArray(), new ProcessSpecItemHistoryRowMapper());
        }

    }