# RunCardQueryDaoImpl.java

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

    import com.fa.sesa.exception.Assert;
    import com.fa.sesa.exception.Errors;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    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.wip.dao.RunCardQueryDao;
    import com.mycim.server.wip.dao.mapper.*;
    import com.mycim.valueobject.Constants;
    import com.mycim.valueobject.MessageIdList;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.TransactionNames;
    import com.mycim.valueobject.runcard.dto.RunCardPortalQueryDTO;
    import com.mycim.valueobject.wip.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.*;

    /**
     * @author pinyan.song
     * @version 6.0.0
     * @date 2019-10-9
     **/
    @Repository
    public class RunCardQueryDaoImpl implements RunCardQueryDao {
        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public List<LotRunCard> getLotRunCardByLot(Long lotRrn, List<String> statusList) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" RUN_CARD_RRN, FACILITY_RRN, RUN_CARD_TYPE, ");
            sql.append(" LOT_RRN, LOT_STEP_SEQUENCE, PRODUCT_RRN, PROCESS_RRN, PROCESS_VERSION, LOT_QTY, " +
                               "CARRIER_MAP_RRN, ");
            sql.append(" SPLIT_FLOW_SEQ, SPLIT_STEP_PATH, SPLIT_OPERATION_RRN, ");
            sql.append(" MERGE_POLLUTION_LEVEL, MERGE_FLOW_SEQ, MERGE_STEP_PATH, MERGE_OPERATION_RRN, NEXT_ACTION," + " ");
            sql.append(" SUPERVISOR, RELATED_MODULE_MANAGER, LOT_OWNER, E1_MANAGER, MFG_MANAGER, ");
            sql.append(" STATUS, EFFECTIVE_TIME, FROZEN_TIME, ACTIVE_TIME, START_TIME, FINISHED_TIME, ");
            sql.append(
                    " CREATED_USER, CREATED_USER_GROUP, CREATED_TIME, LAST_UPDATED_USER, LAST_UPDATED_TIME, " + "COMMENTS" +
                            " ");
            sql.append(" FROM LOT_RUN_CARD ");
            sql.append(" WHERE LOT_RRN = ? ");

            StringBuilder statusStr = new StringBuilder();
            for (String status : statusList) {
                statusStr.append("'").append(status).append("',");
            }
            if (StringUtils.isNotBlank(statusStr.toString())) {
                sql.append(" AND STATUS IN (")
                   .append(StringUtils.substring(statusStr.toString(), 0, statusStr.length() - 1)).append(") ");
            }

            Object[] args = new Object[]{lotRrn};

            return jdbcTemplate.query(sql.toString(), args, new RowMapper<LotRunCard>() {

                @Override
                public LotRunCard mapRow(ResultSet rs, int rowNum) throws SQLException {
                    LotRunCard lotRunCard = new LotRunCard();

                    lotRunCard.setRunCardRrn(rs.getLong("RUN_CARD_RRN"));
                    lotRunCard.setFacilityRrn(rs.getLong("FACILITY_RRN"));
                    lotRunCard.setRunCardType(rs.getString("RUN_CARD_TYPE"));
                    lotRunCard.setLotRrn(rs.getLong("LOT_RRN"));
                    lotRunCard.setLotStepSequence(rs.getLong("LOT_STEP_SEQUENCE"));
                    lotRunCard.setProductRrn(rs.getLong("PRODUCT_RRN"));
                    lotRunCard.setProcessRrn(rs.getLong("PROCESS_RRN"));
                    lotRunCard.setProcessVersion(rs.getInt("PROCESS_VERSION"));
                    lotRunCard.setLotQty(rs.getDouble("LOT_QTY"));
                    lotRunCard.setCarrierMapRrn(rs.getLong("CARRIER_MAP_RRN"));
                    lotRunCard.setSplitFlowSeq(rs.getString("SPLIT_FLOW_SEQ"));
                    lotRunCard.setSplitStepPath(rs.getString("SPLIT_STEP_PATH"));
                    lotRunCard.setSplitOperationRrn(rs.getLong("SPLIT_OPERATION_RRN"));
                    lotRunCard.setMergeFlowSeq(rs.getString("MERGE_FLOW_SEQ"));
                    lotRunCard.setMergeStepPath(rs.getString("MERGE_STEP_PATH"));
                    lotRunCard.setMergeOperationRrn(rs.getLong("MERGE_OPERATION_RRN"));
                    lotRunCard.setMergedPollutionLevel(rs.getString("MERGE_POLLUTION_LEVEL"));
                    lotRunCard.setMergedAction(rs.getString("NEXT_ACTION"));
                    lotRunCard.setSupervisor(rs.getString("SUPERVISOR"));
                    lotRunCard.setRelatedModuleManager(rs.getString("RELATED_MODULE_MANAGER"));
                    lotRunCard.setLotOwner(rs.getString("LOT_OWNER"));
                    lotRunCard.setE1Manager(rs.getString("E1_MANAGER"));
                    lotRunCard.setMfgManager(rs.getString("MFG_MANAGER"));
                    lotRunCard.setStatus(rs.getString("STATUS"));
                    lotRunCard.setEffectiveTimestamp(rs.getTimestamp("EFFECTIVE_TIME"));
                    lotRunCard.setFrozenTimestamp(rs.getTimestamp("FROZEN_TIME"));
                    lotRunCard.setActiveTimestamp(rs.getTimestamp("ACTIVE_TIME"));
                    lotRunCard.setStartTimestamp(rs.getTimestamp("START_TIME"));
                    lotRunCard.setFinishedTimestamp(rs.getTimestamp("FINISHED_TIME"));
                    lotRunCard.setCreatedUserId(rs.getString("CREATED_USER"));
                    lotRunCard.setCreatedUserGroupId(rs.getString("CREATED_USER_GROUP"));
                    lotRunCard.setCreatedTimestamp(rs.getTimestamp("CREATED_TIME"));
                    lotRunCard.setLastUpdatedUserId(rs.getString("LAST_UPDATED_USER"));
                    lotRunCard.setLastUpdatedTimestamp(rs.getTimestamp("LAST_UPDATED_TIME"));
                    lotRunCard.setComments(rs.getString("COMMENTS"));

                    return lotRunCard;
                }
            });
        }

        @Override
        public LotRecoveryRunCard getLotRecoveryRunCard(Long runCardRrn) {

            Object[] args = new Object[]{runCardRrn};

            String sql = "SELECT " + " RUN_CARD_RRN, RUN_CARD_ACTION, ROUTE_RRN, OPERATION_RRN, " + " OPERATION_DESC, " +
                    "FLOW_SEQ, AREA_ID, STAGE_ID, " + " EQUIPMENT_RRN, EQUIPMENT_GROUP_RRN, STATION_RRN, " + " " +
                    "RECIPE_RRN, PROCESS_LOCATION " + " FROM LOT_RECOVERY_RUN_CARD " + " WHERE RUN_CARD_RRN = ? ";
            List<LotRecoveryRunCard> list = jdbcTemplate.query(sql, args, new RowMapper<LotRecoveryRunCard>() {

                @Override
                public LotRecoveryRunCard mapRow(ResultSet rs, int rowNum) throws SQLException {
                    LotRecoveryRunCard lotRecoveryRunCard = new LotRecoveryRunCard();

                    lotRecoveryRunCard.setRunCardRrn(rs.getLong("RUN_CARD_RRN"));
                    lotRecoveryRunCard.setRunCardAction(rs.getString("RUN_CARD_ACTION"));
                    lotRecoveryRunCard.setRouteRrn(rs.getLong("ROUTE_RRN"));
                    lotRecoveryRunCard.setOperationRrn(rs.getLong("OPERATION_RRN"));
                    lotRecoveryRunCard.setOperationDesc(rs.getString("OPERATION_DESC"));
                    lotRecoveryRunCard.setFlowSeq(rs.getString("FLOW_SEQ"));
                    lotRecoveryRunCard.setAreaId(rs.getString("AREA_ID"));
                    lotRecoveryRunCard.setStageId(rs.getString("STAGE_ID"));
                    lotRecoveryRunCard.setEquipmentRrn(rs.getLong("EQUIPMENT_RRN"));
                    lotRecoveryRunCard.setEquipmentGroupRrn(rs.getLong("EQUIPMENT_GROUP_RRN"));
                    lotRecoveryRunCard.setStationRrn(rs.getLong("STATION_RRN"));
                    lotRecoveryRunCard.setRecipeRrn(rs.getLong("RECIPE_RRN"));
                    lotRecoveryRunCard.setProcessLocation(rs.getString("PROCESS_LOCATION"));

                    return lotRecoveryRunCard;
                }
            });

            if (list.size() > 0) {
                return list.get(0);
            } else {
                return null;
            }
        }

        @Override
        public LotRunCard getLotRunCard(Long runCardRrn) {
            String sql = "SELECT " + " RUN_CARD_RRN, FACILITY_RRN, RUN_CARD_TYPE, " + " LOT_RRN, LOT_STEP_SEQUENCE, " +
                    "PRODUCT_RRN, PROCESS_RRN, PROCESS_VERSION, LOT_QTY, " + "CARRIER_MAP_RRN, " + " SPLIT_FLOW_SEQ, " +
                    "SPLIT_STEP_PATH, SPLIT_OPERATION_RRN, " + " MERGE_POLLUTION_LEVEL, MERGE_FLOW_SEQ, MERGE_STEP_PATH, " +
                    "MERGE_OPERATION_RRN, " + "NEXT_ACTION," + " " + " SUPERVISOR, RELATED_MODULE_MANAGER, LOT_OWNER, " +
                    "E1_MANAGER, MFG_MANAGER, " + " STATUS, EFFECTIVE_TIME, FROZEN_TIME, ACTIVE_TIME, START_TIME, " +
                    "FINISHED_TIME, " + " CREATED_USER, CREATED_USER_GROUP, CREATED_TIME, LAST_UPDATED_USER, " +
                    "LAST_UPDATED_TIME, " + "COMMENTS, " + " MERGE_HOLD_CODE, MERGE_HOLD_REASON, PROCESS_LOCATION, " +
                    "POLLUTION_LEVEL " + " FROM LOT_RUN_CARD " + " WHERE RUN_CARD_RRN = ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{runCardRrn}, new LotRunCardRowMapper());
        }

        @Override
        public List<LotRunCardSplit> getLotRunCardSplitSet(Long runcardRrn) {

            StringBuilder sql = new StringBuilder("WITH ");
            sql.append(" V_LRCS AS (SELECT DISTINCT RUN_CARD_RRN,SPLIT_SEQ,REGEXP_REPLACE(REGEXP_SUBSTR(UNIT_RRNS, '[^,]+', " +
                            "1, LEVEL),'[^0-9]') UNIT_RRN ");
            sql.append(
                    " FROM LOT_RUN_CARD_SPLIT WHERE RUN_CARD_RRN = ? CONNECT BY PRIOR RUN_CARD_RRN = RUN_CARD_RRN AND " +
                            "LEVEL <= REGEXP_COUNT(UNIT_RRNS, ',') + 1 ");
            sql.append(
                    " AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) SELECT LR.SPLIT_SEQ, LR.RUN_CARD_RRN, LR.UNIT_IDS, LC" +
                            ".STEP_SERIALS,LC.UNIT_RRNS ");
            sql.append(
                    " FROM (SELECT L.SPLIT_SEQ,L.RUN_CARD_RRN,LISTAGG('#' || REGEXP_SUBSTR(U.UNIT_ID, '[^#]+', 1, 2), '," +
                            "') ");
            sql.append(
                    " WITHIN GROUP(ORDER BY('#' || REGEXP_SUBSTR(U.UNIT_ID, '[^#]+', 1, 2))) UNIT_IDS FROM V_LRCS L, UNIT" +
                            " U WHERE L.UNIT_RRN = U.UNIT_RRN ");
            sql.append(
                    " GROUP BY L.SPLIT_SEQ, L.RUN_CARD_RRN) LR,LOT_RUN_CARD_SPLIT LC WHERE LR.RUN_CARD_RRN = LC" +
                            ".RUN_CARD_RRN AND LR.SPLIT_SEQ = LC.SPLIT_SEQ ");
            sql.append(" ORDER BY LR.SPLIT_SEQ");

           /* String sql = "SELECT RUN_CARD_RRN,SPLIT_SEQ,STEP_SERIALS,UNIT_RRNS FROM LOT_RUN_CARD_SPLIT " + " WHERE " +
                    "RUN_CARD_RRN=? ";*/
            Object[] args = new Object[]{runcardRrn};
            return jdbcTemplate.query(sql.toString(), args, new RowMapper<LotRunCardSplit>() {
                @Override
                public LotRunCardSplit mapRow(ResultSet rs, int rowNum) throws SQLException {
                    LotRunCardSplit lotRunCardSplit = new LotRunCardSplit();
                    lotRunCardSplit.setRunCardRrn(rs.getLong("RUN_CARD_RRN"));
                    lotRunCardSplit.setSplitSeq(rs.getInt("SPLIT_SEQ"));
                    lotRunCardSplit.setStepSerials(rs.getString("STEP_SERIALS"));
                    lotRunCardSplit.setUnitRrns(rs.getString("UNIT_RRNS"));
                    lotRunCardSplit.setUnitIds(rs.getString("UNIT_IDS"));
                    return lotRunCardSplit;
                }
            });
        }

        @Override
        public List<LotRunCardStep> getLotRunCardStepList(Long runCardRrn) {
            String sql = "SELECT " + " RUN_CARD_RRN, STEP_SERIAL_NO, STEP_TYPE, " + " FLOW_SEQ, STEP_PATH, OPERATION_RRN," +
                    " AREA_ID, " + " CHECK_WITH_RECIPE_FLAG, EQUIPMENT_RRN, EQUIPMENT_GROUP_RRN, RECIPE_ID, RECIPE_RRN, " +
                    " RETICLE_RRN,RETICLE_GROUP_RRN,UNIT_RRNS, MEASUREMENT_UNIT_RRNS, POLLUTION_LEVEL, " +
                    " OUT_PARAMETER_SET_RRN, IN_PARAMETER_SET_RRN, COMMENTS, PROCESS_LOCATION, STAGE_ID, " +
                    " OPERATOR_COMMENTS, START_OF_MAIN_QTIME, END_OF_MAIN_QTIME, START_OF_RC_QTIME, " +
                    " END_OF_RC_QTIME, ROUTE_RRN, TIMELIMIT, MODULE, FLIP_TYPE FROM LOT_RUN_CARD_STEP WHERE RUN_CARD_RRN = ? " +
                    " ORDER BY STEP_SERIAL_NO ";
            return jdbcTemplate.query(sql, new LotRunCardStepRowMapper(), runCardRrn);
        }

        @Override
        public LotRunCardStep getLotRunCardStep(Long runCardRrn, int stepSerialNo) {
            String sql = "SELECT " + " RUN_CARD_RRN, STEP_SERIAL_NO, STEP_TYPE, " + " FLOW_SEQ, STEP_PATH, OPERATION_RRN," +
                    " AREA_ID, " + " CHECK_WITH_RECIPE_FLAG, EQUIPMENT_RRN, EQUIPMENT_GROUP_RRN, RECIPE_ID, RECIPE_RRN, " +
                    " RETICLE_RRN,RETICLE_GROUP_RRN,UNIT_RRNS, MEASUREMENT_UNIT_RRNS, POLLUTION_LEVEL, " +
                    " OUT_PARAMETER_SET_RRN, IN_PARAMETER_SET_RRN, COMMENTS, PROCESS_LOCATION, STAGE_ID, " +
                    " OPERATOR_COMMENTS, START_OF_MAIN_QTIME, END_OF_MAIN_QTIME, START_OF_RC_QTIME, " +
                    " END_OF_RC_QTIME, ROUTE_RRN, TIMELIMIT, MODULE, FLIP_TYPE FROM LOT_RUN_CARD_STEP WHERE RUN_CARD_RRN = ? " +
                    " and step_serial_no=? ";
            return jdbcTemplate
                    .queryForObjectWithNull(sql, new Object[]{runCardRrn, stepSerialNo}, new LotRunCardStepRowMapper());
        }

        @Override
        public List<LotRunCardStepParameter> getLotRunCardStepParameterList(Long runCardRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("L.RUN_CARD_RRN,L.STEP_SERIAL_NO,L.PARAMETER_SET_RRN,L.PARAMETER_SET_VERSION,L.PARAMETER_SEQUENCE,");
            sql.append("L.PARAMETER_RRN,L.READING_PROMPTS_SIZE,L.LOWER_CONTROL_LIMIT,L.UPPER_CONTROL_LIMIT,");
            sql.append("L.LOWER_SPECIFICATION_LIMIT,L.UPPER_SPECIFICATION_LIMIT,P.PARAMETER_ID ");
            sql.append(" FROM ").append(DataBaseNames.LOT_RUN_CARD_STEP_PARAMETER).append(" L ");
            sql.append(" LEFT JOIN ").append(DataBaseNames.PARAMETER).append(" P ");
            sql.append(" ON L.PARAMETER_RRN=P.PARAMETER_RRN ");
            sql.append(" WHERE L.RUN_CARD_RRN = ? ");
            sql.append(" ORDER BY L.STEP_SERIAL_NO, L.PARAMETER_SEQUENCE ");
            return jdbcTemplate.query(sql.toString(), new LotRunCardStepParameterRowMapper(), runCardRrn);
        }

        @Override
        public List<LotRunCardStepParameter> getLotRunCardStepParameterList(Long runCardRrn, Integer stepSerialNo,
                                                                            Long parameterSetRrn) {

            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("L.RUN_CARD_RRN,L.STEP_SERIAL_NO,L.PARAMETER_SET_RRN,L.PARAMETER_SET_VERSION,L.PARAMETER_SEQUENCE,");
            sql.append("L.PARAMETER_RRN,L.READING_PROMPTS_SIZE,L.LOWER_CONTROL_LIMIT,L.UPPER_CONTROL_LIMIT,");
            sql.append("L.LOWER_SPECIFICATION_LIMIT,L.UPPER_SPECIFICATION_LIMIT,P.PARAMETER_ID ");
            sql.append(" FROM ").append(DataBaseNames.LOT_RUN_CARD_STEP_PARAMETER).append(" L ");
            sql.append(" LEFT JOIN ").append(DataBaseNames.PARAMETER).append(" P ");
            sql.append(" ON L.PARAMETER_RRN=P.PARAMETER_RRN ");
            sql.append(" WHERE L.RUN_CARD_RRN = ? AND L.STEP_SERIAL_NO = ? AND L.PARAMETER_SET_RRN = ? ");
            sql.append(" ORDER BY L.PARAMETER_SEQUENCE ");

            Object[] args = new Object[]{runCardRrn, stepSerialNo, parameterSetRrn};

            return jdbcTemplate.query(sql.toString(), args, new LotRunCardStepParameterRowMapper());
        }

        @Override
        public LotRunCardStore getSplitRunCardLotStore(long lotRrn, long runcardRrn, String showLotId) {
            List<Object> condition = new ArrayList<>();

            String sql = "SELECT LOT_RRN,LOT_ID,STATUS,PROCESS_ID,PROCESS_RRN,PROCESS_VERSION,WFL_STEP,OPERATION_ID," +
                    "OPERATION_RRN,FLOW_SEQ,CARRIER_ID,CARRIER_RRN,SUB_STATUS,LOT_SPECIAL_STEP_RRN,STEP_SEQUENCE," +
                    "BASE_LOT_RRN,MAIN_LOT_ID,SHOW_LOT_ID,RUNCARD_RRN,getinstanceid(RUNCARD_RRN) as RUNCARD_ID," +
                    "RUNCARD_RRN_H,RUNCARD_ID_H,RUNCARD_TYPE FROM SRC_LOT_STORE WHERE 1=1";

            if (lotRrn > 0L) {
                sql += " AND LOT_RRN = ?";
                condition.add(lotRrn);
            }

            if (runcardRrn > 0L) {
                sql += " AND RUNCARD_RRN = ?";
                condition.add(runcardRrn);
            }

            if (StringUtils.isNotEmpty(showLotId)) {
                sql += " AND SHOW_LOT_ID = ?";
                condition.add(showLotId);
            }

            Assert.isFalse(condition.size() == 0,
                           Errors.create().key(MessageIdList.SYSTEM_NULL_EXCEPTION).content("{} parameter is null!")
                                 .args(condition).build());

            List<LotRunCardStore> list = jdbcTemplate.query(sql, condition.toArray(), new RowMapper<LotRunCardStore>() {

                @Override
                public LotRunCardStore mapRow(ResultSet rs, int rowNum) throws SQLException {
                    LotRunCardStore store = new LotRunCardStore();
                    store.setLotRrn(rs.getLong("LOT_RRN"));
                    store.setLotId(rs.getString("LOT_ID"));
                    store.setMainLotId(rs.getString("MAIN_LOT_ID"));
                    store.setStatus(rs.getString("STATUS"));
                    store.setProcessId(rs.getString("PROCESS_ID"));
                    store.setProcessRrn(rs.getLong("PROCESS_RRN"));
                    store.setProcessVersion(rs.getInt("PROCESS_VERSION"));
                    store.setWflStep(rs.getString("WFL_STEP"));
                    store.setOperationId(rs.getString("OPERATION_ID"));
                    store.setOperationRrn(rs.getLong("OPERATION_RRN"));
                    store.setFlowSeq(rs.getString("FLOW_SEQ"));
                    store.setCarrierId(rs.getString("CARRIER_ID"));
                    store.setCarrierRrn(rs.getLong("CARRIER_RRN"));
                    store.setSubStatus(rs.getString("SUB_STATUS"));
                    store.setLotSpecialStepRrn(rs.getLong("LOT_SPECIAL_STEP_RRN"));
                    store.setStepSequence(rs.getInt("STEP_SEQUENCE"));
                    store.setBaseLotRrn(rs.getLong("BASE_LOT_RRN"));
                    store.setRuncardRrn(rs.getLong("RUNCARD_RRN"));
                    store.setRuncardId(rs.getString("RUNCARD_ID"));
                    store.setMainLotId(rs.getString("MAIN_LOT_ID"));
                    store.setShowLotId(rs.getString("SHOW_LOT_ID"));

                    String runcardRrnHClob = rs.getString("RUNCARD_RRN_H");
                    if (runcardRrnHClob != null) {
                        store.setRuncardRrnHistory(runcardRrnHClob);
                    } else {
                        store.setRuncardRrnHistory("");
                    }

                    String runcardIdHClob = rs.getString("RUNCARD_ID_H");
                    if (runcardIdHClob != null) {
                        store.setRuncardIdHistory(runcardIdHClob);
                    } else {
                        store.setRuncardIdHistory("");
                    }

                    store.setRuncardType(rs.getString("RUNCARD_TYPE"));

                    return store;
                }
            });

            if (list.size() > 0) {
                return list.get(0);
            } else {
                return null;
            }
        }

        @Override
        public List<Map<String, Object>> getRunCardLotsByRuncardRrn(Long runcardRrn) {
            String sql = "SELECT LOT_RRN,LOT_ID,STATUS,PROCESS_ID,PROCESS_RRN,PROCESS_VERSION,WFL_STEP,OPERATION_ID," +
                    "OPERATION_RRN,FLOW_SEQ," + "CARRIER_ID,CARRIER_RRN,SUB_STATUS,LOT_SPECIAL_STEP_RRN,STEP_SEQUENCE," +
                    "BASE_LOT_RRN" + " FROM SRC_LOT_STORE WHERE RUNCARD_RRN = ?";
            Object[] args = new Object[]{runcardRrn};

            return jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap<String, Object>();
                    map.put("lotRrn", rs.getLong("LOT_RRN"));
                    map.put("lotId", rs.getString("LOT_ID"));
                    map.put("status", rs.getString("STATUS"));
                    map.put("processId", rs.getString("PROCESS_ID"));
                    map.put("processRrn", rs.getLong("PROCESS_RRN"));
                    map.put("processVersion", rs.getLong("PROCESS_VERSION"));
                    map.put("wflStep", rs.getString("WFL_STEP"));
                    map.put("operationId", rs.getString("OPERATION_ID"));
                    map.put("operationRrn", rs.getLong("OPERATION_RRN"));
                    map.put("flowSeq", rs.getString("FLOW_SEQ"));
                    map.put("carrierId", rs.getString("CARRIER_ID"));
                    map.put("carrierRrn", rs.getLong("CARRIER_RRN"));
                    map.put("subStatus", rs.getString("SUB_STATUS"));
                    map.put("lotSpecialStepRrn", rs.getLong("LOT_SPECIAL_STEP_RRN"));
                    map.put("stepSeq", rs.getInt("STEP_SEQUENCE"));
                    map.put("baseLotRrn", rs.getLong("BASE_LOT_RRN"));
                    return map;
                }

            });
        }

        @Override
        public SRCLotSpecialStep getLotSplitRunCardSpecialStep(Long lotSpecialStepRrn, Integer stepSequence) {
            String sql = "SELECT LOT_SPECIAL_STEP_RRN,RUNCARD_ID,RUNCARD_RRN,LOT_RRN,STEP_SEQUENCE,UNIT_ID," +
                    "MEAS_SLOT_ID,CONTAMINATION_FLAG," + "PROCESS_LOCATION,REMARK,EQPT_ID,EQPT_RRN,EQPT_GROUP_ID," +
                    "EQPT_GROUP_RRN,STEP_NO," + "CHECK_RCP,RECIPE_ID,WORK_AREA,PARAMETER_SET_RRN,GETINSTANCEID" +
                    "(PARAMETER_SET_RRN) AS " + "PARAMETER_SET_ID,RETICLE_RRN,RETICLE_GROUP_RRN,FLIP_TYPE" + " FROM " +
                    "SRC_LOT_SPECIAL_STEP WHERE LOT_SPECIAL_STEP_RRN = ? AND STEP_SEQUENCE=?";
            Object[] args = new Object[]{lotSpecialStepRrn, stepSequence};
            return jdbcTemplate.queryForObjectWithNull(sql, args, (RowMapper<SRCLotSpecialStep>) (rs, rowNum) -> {
                SRCLotSpecialStep lotSpecialStep = new SRCLotSpecialStep();
                lotSpecialStep.setLotSpecialStepRrn(rs.getLong("LOT_SPECIAL_STEP_RRN"));
                lotSpecialStep.setRuncardId(rs.getString("RUNCARD_ID"));
                lotSpecialStep.setRuncardRrn(rs.getLong("RUNCARD_RRN"));
                lotSpecialStep.setLotRrn(rs.getLong("LOT_RRN"));
                lotSpecialStep.setStepSequence(rs.getInt("STEP_SEQUENCE"));
                lotSpecialStep.setUnitId(rs.getString("UNIT_ID"));
                lotSpecialStep.setMeasSlotId(rs.getString("MEAS_SLOT_ID"));
                lotSpecialStep.setContaminationFlag(rs.getString("CONTAMINATION_FLAG"));
                lotSpecialStep.setProcessLocation(rs.getString("PROCESS_LOCATION"));
                lotSpecialStep.setRemark(rs.getString("REMARK"));
                lotSpecialStep.setEqptId(rs.getString("EQPT_ID"));
                lotSpecialStep.setEqptRrn(rs.getLong("EQPT_RRN"));
                lotSpecialStep.setEqptGroupId(rs.getString("EQPT_GROUP_ID"));
                lotSpecialStep.setEqptGroupRrn(rs.getLong("EQPT_GROUP_RRN"));
                lotSpecialStep.setStepNo(rs.getString("STEP_NO"));
                lotSpecialStep.setCheckRcp(rs.getString("CHECK_RCP"));
                lotSpecialStep.setRecipeId(rs.getString("RECIPE_ID"));
                lotSpecialStep.setWorkArea(rs.getString("WORK_AREA"));
                lotSpecialStep.setParameterSetId(rs.getString("PARAMETER_SET_ID"));
                lotSpecialStep.setParameterSetRrn(rs.getLong("PARAMETER_SET_RRN"));
                lotSpecialStep.setReticleRrn(rs.getLong("RETICLE_RRN"));
                lotSpecialStep.setReticleGroupRrn(rs.getLong("RETICLE_GROUP_RRN"));
                lotSpecialStep.setFlipType(rs.getString("FLIP_TYPE"));
                return lotSpecialStep;
            });
        }

        @Override
        public List<Map<String, Object>> getSplitedRunCardLots(long runcardRrn) {
            String sql = "SELECT LT.LOT_ID,LT.LOT_RRN,L.CARRIER_ID,L.CARRIER_RRN,LT.LOT_STATUS,L.MAIN_LOT_ID,L" +
                    ".SHOW_LOT_ID,L.SPLIT_SEQ" + " FROM SRC_LOT_STORE L,LOT LT" + " WHERE L.LOT_RRN=LT.LOT_RRN AND L" +
                    ".RUNCARD_RRN = ? AND SPLIT_SEQ IS NOT NULL";
            Object[] args = new Object[]{runcardRrn};
            return jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap<String, Object>();
                    map.put("lotId", rs.getString("LOT_ID"));
                    map.put("lotRrn", rs.getLong("LOT_RRN"));
                    map.put("mainLotId", rs.getString("MAIN_LOT_ID"));
                    map.put("showLotId", rs.getString("SHOW_LOT_ID"));
                    map.put("carrierRrn", rs.getLong("CARRIER_RRN"));
                    map.put("carrierId", rs.getString("CARRIER_ID"));
                    map.put("lotStatus", rs.getString("LOT_STATUS"));
                    map.put("splitSeq", rs.getInt("SPLIT_SEQ"));
                    return map;
                }
            });
        }

        @Override
        public Map<String, Object> getLotRunCardReviewQueueHistory(Long transRrn, Integer transSequence) {
            String sql = "SELECT " + " TRANS_RRN, TRANS_SEQUENCE, RUN_CARD_RRN, QUEUE_SEQUENCE, " + " USER_RRN, " +
                    "USER_GROUP_RRN, REVIEW_USER_RRN, REVIEW_TIMESTAMP, REVIEW_RESULT, " + "REVIEW_ROLE, " +
                    "REVIEW_COMMENTS " + " FROM LOT_RUN_CARD_REVIEW_QUEUE_H " + " WHERE TRANS_RRN = ? AND TRANS_SEQUENCE " +
                    "= ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{transRrn, transSequence},
                                                       new RowMapper<Map<String, Object>>() {

                                                           Map<String, Object> map;

                                                           @Override
                                                           public Map<String, Object> mapRow(ResultSet rs,
                                                                                             int rowNum) throws SQLException {
                                                               map = new HashMap<>();

                                                               map.put("transRrn", rs.getLong("TRANS_RRN"));
                                                               map.put("transSequence", rs.getInt("TRANS_SEQUENCE"));

                                                               map.put("runCardRrn", rs.getLong("RUN_CARD_RRN"));
                                                               map.put("queueSequence", rs.getInt("QUEUE_SEQUENCE"));

                                                               map.put("userRrn", rs.getLong("USER_RRN"));
                                                               map.put("userGroupRrn", rs.getLong("USER_GROUP_RRN"));
                                                               map.put("reviewUserRrn", rs.getLong("REVIEW_USER_RRN"));
                                                               map.put("reviewTimestamp",
                                                                       rs.getTimestamp("REVIEW_TIMESTAMP"));
                                                               map.put("reviewResult", rs.getString("REVIEW_RESULT"));

                                                               map.put("reviewRole", rs.getString("REVIEW_ROLE"));
                                                               map.put("reviewComments", rs.getString("REVIEW_COMMENTS"));

                                                               return map;
                                                           }
                                                       });
        }

        @Override
        public LotRunCardReviewQueue getLotRunCardReviewQueue(Long runCardRrn, Integer queueSequence) {
            String sql = "SELECT RUN_CARD_RRN, QUEUE_SEQUENCE, USER_RRN, USER_GROUP_RRN, REVIEW_USER_RRN, " +
                    "REVIEW_TIMESTAMP, REVIEW_RESULT, REVIEW_ROLE, REVIEW_COMMENTS FROM LOT_RUN_CARD_REVIEW_QUEUE " +
                    " WHERE RUN_CARD_RRN = ? AND QUEUE_SEQUENCE = ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{runCardRrn, queueSequence},
                                                       new LotRunCardReviewQueueRowMapper());
        }

        @Override
        public LotRunCardReviewQueue getLotRunCardReviewQueueFirst(Long runCardRrn) {
            String sql = "SELECT RUN_CARD_RRN, QUEUE_SEQUENCE, USER_RRN, USER_GROUP_RRN, REVIEW_USER_RRN," +
                    " REVIEW_TIMESTAMP, REVIEW_RESULT, REVIEW_ROLE, REVIEW_COMMENTS " +
                    " FROM (SELECT RUN_CARD_RRN, QUEUE_SEQUENCE, USER_RRN, USER_GROUP_RRN, REVIEW_USER_RRN," +
                    " REVIEW_TIMESTAMP, REVIEW_RESULT, REVIEW_ROLE, REVIEW_COMMENTS " +
                    " FROM LOT_RUN_CARD_REVIEW_QUEUE WHERE RUN_CARD_RRN = ? AND REVIEW_RESULT IS NULL" +
                    " ORDER BY QUEUE_SEQUENCE) n  WHERE ROWNUM = 1 ";
            return jdbcTemplate.queryForObjectWithNull(sql, new LotRunCardReviewQueueRowMapper(), runCardRrn);
        }

        @Override
        public LotRunCardReviewQueue getLotRunCardReviewQueueLast(Long runCardRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" RUN_CARD_RRN, QUEUE_SEQUENCE, USER_RRN, USER_GROUP_RRN, REVIEW_USER_RRN, REVIEW_TIMESTAMP,");
            sql.append(" REVIEW_RESULT, REVIEW_ROLE, REVIEW_COMMENTS ");
            sql.append(" FROM (SELECT ");
            sql.append(" RUN_CARD_RRN, QUEUE_SEQUENCE, USER_RRN, USER_GROUP_RRN, REVIEW_USER_RRN, REVIEW_TIMESTAMP, ");
            sql.append(" REVIEW_RESULT, REVIEW_ROLE, REVIEW_COMMENTS ");
            sql.append(" FROM LOT_RUN_CARD_REVIEW_QUEUE ");
            sql.append(" WHERE RUN_CARD_RRN = ? ");
            sql.append(" ORDER BY QUEUE_SEQUENCE DESC) ");
            sql.append(" WHERE ROWNUM = 1 ");

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new LotRunCardReviewQueueRowMapper(), runCardRrn);
        }

        @Override
        public List<LotRunCardReviewQueue> getLotRunCardReviewQueueByReviewRole(Long runCardRrn, String reviewRole) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" RUN_CARD_RRN, QUEUE_SEQUENCE, USER_RRN, USER_GROUP_RRN, REVIEW_USER_RRN, REVIEW_TIMESTAMP, ");
            sql.append(" REVIEW_RESULT, REVIEW_ROLE, REVIEW_COMMENTS ");
            sql.append(" FROM LOT_RUN_CARD_REVIEW_QUEUE ");
            sql.append(" WHERE RUN_CARD_RRN = ? AND REVIEW_ROLE = ?");
            sql.append(" ORDER BY QUEUE_SEQUENCE DESC ");

            return jdbcTemplate.query(sql.toString(), new LotRunCardReviewQueueRowMapper(), runCardRrn, reviewRole);
        }

        @Override
        public Map<String, Object> queryRunCardLotCount(Map param) {
            Map<String, Object> sqlMap = this.getRuncardLotCountSql(param);
            String sql = MapUtils.getString(sqlMap, "sql");
            List<Object> argList = (List<Object>) MapUtils.getObject(sqlMap, "argList");

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, argList.toArray());
            int lotCount = 0;
            int qtyCount = 0;
            while (rs.next()) {
                lotCount = rs.getInt("LOT_COUNT");
            }

            sqlMap = new HashMap<String, Object>();
            sqlMap.put("lotCount", lotCount);
            return sqlMap;
        }

        @Override
        public List<Map<String, Object>> querySplitRunCardLotInfoByPage(Map param, int startRow, int currentTotalRow,
                                                                        Map<String, Object> countMap) {
            Map<String, Object> sqlMap = this.getRuncardLotPortalListSql(param, startRow, currentTotalRow);
            String sql = MapUtils.getString(sqlMap, "sql");
            List<Object> argList = (List<Object>) MapUtils.getObject(sqlMap, "argList");

            List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

            dataList = jdbcTemplate.query(sql, argList.toArray(), new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {

                    Map<String, Object> dataMap = new HashMap<String, Object>();
                    dataMap.put("lotID", rs.getString("LOT_ID"));
                    dataMap.put("mainLotId", rs.getString("MAIN_LOT_ID"));
                    dataMap.put("showLotId", rs.getString("SHOW_LOT_ID"));
                    dataMap.put("lotRRN", rs.getLong("LOT_RRN"));
                    dataMap.put("lotStatus", rs.getString("LOT_STATUS"));
                    dataMap.put("lotType", rs.getString("LOTTYPE"));
                    dataMap.put("lotPiority", rs.getString("PRIORITY")); // 已拼接
                    dataMap.put("qty1", rs.getString("QTY1")); // 数量
                    dataMap.put("carrierId", rs.getString("CARRIER_ID"));
                    dataMap.put("productID", rs.getString("PRODUCTID"));
                    if (rs.getObject("PRODUCT_VERSION") != null) {
                        dataMap.put("productVersion", rs.getInt("PRODUCT_VERSION"));
                    }

                    dataMap.put("productRrn", rs.getLong("PRODUCT_RRN"));
                    dataMap.put("routeSeq", rs.getString("ROUTESEQ"));
                    dataMap.put("stepSeq", rs.getString("STEPSEQ"));
                    dataMap.put("recipeRrn", rs.getString("CONTEXT_RECIPE_RRN")); // 情境值上的 recipe rrn
                    dataMap.put("recipeID", rs.getString("RECIPE_ID")); // 情境值上的 recipe
                    dataMap.put("processRrn", rs.getLong("PROCESS_RRN"));
                    dataMap.put("routeRrn", rs.getLong("ROUTE_RRN"));
                    dataMap.put("stepRrn", rs.getLong("STEP_RRN"));
                    dataMap.put("processId", rs.getString("PROCESS_ID"));
                    dataMap.put("routeID", rs.getString("ROUTE_ID"));
                    dataMap.put("stepID", rs.getString("STEP_ID"));
                    dataMap.put("runningEqpt", rs.getString("RUNNING_EQPT"));
                    if (StringUtils.isNotBlank(rs.getString("EQPT_ID"))) {
                        dataMap.put("eqptList", rs.getString("EQPT_ID"));
                    } else {
                        dataMap.put("eqptList", rs.getString("EQPT_LIST"));
                    }

                    long runTime = 0;
                    if (rs.getTimestamp("LOT_RUN_TIME") != null) {
                        long runSpent = timeToLongSecs(new Date()) - timeToLongSecs(rs.getTimestamp("LOT_RUN_TIME"));
                        String runTimeHr = (new java.text.DecimalFormat("#0.00")).format(runSpent / 60f / 60f);
                        // running状态批次才显示runTime
                        if (StringUtils.equalsIgnoreCase(LotStatus.RUNNING, rs.getString("lotStatus"))) {
                            dataMap.put("runTime", runTimeHr);
                            runTime = timeToLongSecs(rs.getTimestamp("LOT_RUN_TIME"));
                        } else {
                            dataMap.put("runTime", "");
                        }
                    } else {
                        dataMap.put("runTime", "");
                    }

                    long spent = timeToLongSecs(new Date()) - timeToLongSecs(rs.getTimestamp("QUEUE_TIMESTAMP"));
                    // if (runTime > 0) {
                    // spent = runTime - MiscUtils.timeToLongSecs(rs.getTimestamp("QUEUE_TIMESTAMP"));
                    // }
                    String waitTimeHr = (new java.text.DecimalFormat("#0.00")).format(spent / 60f / 60f);
                    dataMap.put("queueTime", waitTimeHr); // 小时 new Long(spent / 60 / 60)

                    if (rs.getTimestamp("HOLD_TIMESTAMP") != null) {
                        long holdSpent = timeToLongSecs(new Date()) - timeToLongSecs(rs.getTimestamp("HOLD_TIMESTAMP"));
                        String holdTimeHr = (new java.text.DecimalFormat("#0.00")).format(holdSpent / 60f / 60f);
                        // hold状态批次才显示holdTime
                        if (StringUtils.equalsIgnoreCase(LotStatus.HOLD, rs.getString("lotStatus")) ||
                                StringUtils.equalsIgnoreCase(LotStatus.RUNNINGHOLD, rs.getString("lotStatus"))) {
                            dataMap.put("holdTime", holdTimeHr);
                        } else {
                            dataMap.put("holdTime", "");
                        }
                    } else {
                        dataMap.put("holdTime", "");
                    }

                    dataMap.put("stageID", rs.getString("STAGE_ID"));
                    dataMap.put("pollutionLevel", rs.getString("POLLUTION_LEVEL"));
                    dataMap.put("recipePara", rs.getString("RECIPE_STRING"));// 工艺规格
                    dataMap.put("workArea", rs.getString("WORK_AREA"));
                    dataMap.put("eqptGroupId", rs.getString("EQPT_GROUP_ID"));
                    dataMap.put("eqptId", rs.getString("EQPT_ID")); // 没有设备就显示工步上设备组的所有设备
                    dataMap.put("holdDept", rs.getString("HOLD_DEPT"));
                    dataMap.put("holdComment", rs.getString("HOLD_REASON"));
                    dataMap.put("isRework", rs.getString("REWORK_CATEGORY"));
                    dataMap.put("lotCategory", rs.getString("LOTCATEGORY"));
                    dataMap.put("processVersion", rs.getString("PROCESS_VERSION"));
                    dataMap.put("podID", rs.getString("PODID"));
                    dataMap.put("doorId", rs.getString("DOORID"));
                    dataMap.put("shippingCode", rs.getString("SHIPPING_CODE"));
                    dataMap.put("customerId", rs.getString("CUSTOMER_ID"));
                    dataMap.put("outerOrderNO", rs.getString("OUTER_ORDER_NO"));
                    dataMap.put("outOrderType", rs.getString("OUT_ORDER_TYPE"));
                    dataMap.put("customerLotId", rs.getString("CUSTOMER_LOT_ID"));
                    dataMap.put("wflSeq", rs.getString("CONTEXT_FLOW_SEQ"));
                    dataMap.put("stepDesc", rs.getString("CONTEXT_STEP_DESC"));
                    dataMap.put("timeLimit", rs.getDouble("Q_TIME"));

                    Long locationRrn = rs.getLong("LOCATION");
                    Long pLocationRrn = rs.getLong("PLOCATION");

                    dataMap.put("locationRrn", locationRrn);
                    dataMap.put("pLocationRrn", pLocationRrn);

                    // dataMap.put("lotCount", MapUtils.getIntValue(countMap, "lotCount"));
                    // dataMap.put("qtyCount", MapUtils.getIntValue(countMap, "qtyCount"));
                    dataMap.put("runcardId", rs.getString("RUNCARD_ID"));
                    dataMap.put("runcardRrn", rs.getString("RUNCARD_RRN"));

                    /*Clob outStr = rs.getClob("RUNCARD_ID_H");
                    if (outStr != null) {
                        dataMap.put("runcardIdHistory", outStr.getSubString(1L, (int) outStr.length()));
                    } else {
                        dataMap.put("runcardIdHistory", "");
                    }

                    outStr = rs.getClob("RUNCARD_RRN_H");
                    if (outStr != null) {
                        dataMap.put("runcardRrnHistory", outStr.getSubString(1L, (int) outStr.length()));
                    } else {
                        dataMap.put("runcardRrnHistory", "");
                    }*/

                    dataMap.put("runcardIdHistory", rs.getString("RUNCARD_ID_H"));
                    dataMap.put("runcardRrnHistory", rs.getString("RUNCARD_RRN_H"));

                    dataMap.put("comments", rs.getString("OPERATOR_COMMENTS"));
                    dataMap.put("subStatus", rs.getString("SUB_STATUS"));
                    dataMap.put("autoFlag", rs.getString("AUTO_FLAG"));
                    byte[] dataStruct=  rs.getBytes("CURRENT_STEP_TIME_STRUCT");
                    LotStepStatSpeedTime lotStepStatSpeedTime= new LotStepStatSpeedTime(dataStruct);
                    dataMap.putAll(lotStepStatSpeedTime.resolveDataStruct());
                    // dataMap.put("lotEqptId", rs.getString("LOT_EQPT_ID"));
                    // dataMap.put("lotEqptGroupId", rs.getString("LOT_EQPT_GROUP_ID"));
                    return dataMap;
                }
            });

            for (Map<String, Object> dataMap : dataList) {
                dataMap.put("lotCount", MapUtils.getIntValue(countMap, "lotCount"));
                dataMap.put("qtyCount", MapUtils.getIntValue(countMap, "qtyCount"));
            }

            return dataList;
        }

        @Override
        public Map<String, Object> getRCLotStepInfo(String lotId, Long runCardRrn) {
            String sql = " SELECT SLS.MAIN_LOT_ID, LRCS.FLOW_SEQ, LRC.MERGE_FLOW_SEQ FROM " +
                    " SRC_LOT_STORE SLS, LOT_RUN_CARD_STEP LRCS, LOT_RUN_CARD LRC " +
                    " WHERE SLS.SPLIT_SEQ = LRCS.STEP_SERIAL_NO " + " AND LRC.RUN_CARD_RRN = LRCS.RUN_CARD_RRN " +
                    " AND LRCS.RUN_CARD_RRN = ? " + " AND SLS.SHOW_LOT_ID = ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{runCardRrn, lotId}, (rs, rowNum) -> {
                Map result = new HashMap();
                result.put("lotId", rs.getString("MAIN_LOT_ID"));
                result.put("startFlowSeq", rs.getString("FLOW_SEQ"));
                result.put("endFlowSeq", rs.getString("MERGE_FLOW_SEQ"));
                return result;
            });
        }

        @Override
        public int getRCChildLotCount(String lotId, long facilityRrn, String runCardIdPrefix) {
            String sql = "SELECT COUNT(LOT_RRN) FROM LOT WHERE FACILITY_RRN = ? AND LOT_ID LIKE ?";
            Object[] args = new Object[]{facilityRrn, lotId + "." + runCardIdPrefix + "%"};
            return jdbcTemplate.queryForObject(sql, args, int.class);
        }

        @Override
        public Page qryRuncardLotTransHistory(Page page, Long lotRrn) {
            StringBuffer sql = new StringBuffer("SELECT ");
            sql.append("lt.trans_rrn,lt.trans_sequence,lt.trans_id,tr.trans_start_timestamp,tr.trans_end_timestamp,lt.TRANS_MODULE, " );
            sql.append("tr.trans_performed_by,ls.stage_id,ls.lot_rrn,ls.lot_id,ls.facility_rrn,ls.step_sequence,");
            sql.append("ls.in_qty1,lt.qty1,ls.out_qty1,ls.product_rrn,ls.eqpt_rrn,lt.trans_comments,ls.route_seq,");
            sql.append("PROD_N.INSTANCE_ID AS PRODUCT_ID,EQPT_N.INSTANCE_ID AS EQPT_ID,EQPT_N2.INSTANCE_ID AS LTH_EQPT_ID,");
            sql.append("ls.operation_seq,ls.step_type,ls.work_area,ls.flow_seq,ls.operation_rrn,");
            sql.append("OPERATION_N.INSTANCE_ID AS operation_id,ls.operation_desc,ls.product_layer,ls.recipe_physical_id,");
            sql.append("ls.priority,ls.hot_flag,smh.target_lot_id,smh.target_unit_id,smh.source_lot_id,lt.CUSTOMER_ID,");
            sql.append("lt.SHIPPING_CODE,lt.OUTER_ORDER_NO,lt.OUT_ORDER_TYPE,lt.location,lt.prev_location,SLS.SHOW_LOT_ID,");
            sql.append("CASE WHEN SLS.LOT_RRN=SLS.BASE_LOT_RRN THEN SLS.CARRIER_ID ELSE GETINSTANCEID(LT.CARRIER_RRN) END CARRIER_ID,");
            sql.append("SLS.OBJECT_TYPE AS CARRIER_TYPE,SLS.PROCESS_ID,SLS.PROCESS_VERSION,");
            sql.append("SLS.PROCESS_STEP_ID_VERSION,sls.process_step_version,ls.FLIP_TYPE ");

            sql.append(" FROM lot_step_history ls LEFT JOIN NAMED_OBJECT OPERATION_N ");
            sql.append("ON LS.OPERATION_RRN = OPERATION_N.INSTANCE_RRN ");
            sql.append("LEFT JOIN NAMED_OBJECT EQPT_N ON LS.EQPT_RRN=EQPT_N.INSTANCE_RRN ");
            sql.append("LEFT JOIN NAMED_OBJECT PROD_N ON LS.PRODUCT_RRN=PROD_N.INSTANCE_RRN, ");
            sql.append("lot_trans_history lt LEFT JOIN transaction_log tr ON lt.trans_rrn = tr.trans_rrn ");
            sql.append("LEFT JOIN NAMED_OBJECT EQPT_N2 ON LT.EQPT_RRN=EQPT_N2.INSTANCE_RRN ");
            sql.append("LEFT JOIN split_merge_history smh ON tr.trans_rrn = smh.trans_rrn ");

            sql.append("LEFT JOIN (SELECT SLS.LOT_RRN,SLS.BASE_LOT_RRN,SLS.RUNCARD_RRN,SLS.CARRIER_ID,SLS.PROCESS_ID,SLS.PROCESS_VERSION,");
            sql.append("SLS.SHOW_LOT_ID,N.OBJECT_TYPE,SLS.MAIN_LOT_ID,lsh.process_step_id_version,lsh.process_step_version ");
            sql.append("FROM SRC_LOT_STORE SLS, NAMED_OBJECT N, lot_step_history lsh ");
            sql.append(" WHERE SLS.CARRIER_RRN = N.INSTANCE_RRN and sls.main_lot_id = lsh.lot_id ");
            sql.append("and sls.main_lot_step_sequence = lsh.step_sequence) SLS ");

            sql.append("on LT.LOT_RRN = SLS.LOT_RRN ");
            sql.append("where lt.lot_rrn = ? AND lt.lot_rrn = ls.lot_rrn AND lt.step_sequence = ls.step_sequence ");
            sql.append("AND (smh.source_lot_rrn <> smh.target_lot_rrn or ");
            sql.append("(smh.target_lot_rrn is null and smh.source_lot_id is null)) ");
            sql.append("AND (lt.lot_rrn = smh.target_lot_rrn or lt.lot_rrn = smh.source_lot_rrn or ");
            sql.append("(smh.target_lot_rrn is null and smh.source_lot_id is null)) ");
            sql.append(" AND lt.trans_id not in (?, ?, ?, ?) ");
            sql.append("ORDER BY trans_start_timestamp DESC, trans_sequence DESC ");

            List<Object> args = new ArrayList<>();
            args.add(lotRrn);
            args.add(TransactionNames.LBRD_KEY);
            args.add(TransactionNames.MOVENEXT_KEY);
            args.add(TransactionNames.WS_MOVENEXT_KEY);
            args.add(TransactionNames.CREATE_KEY);

            return jdbcTemplate.queryForPage(page, sql.toString(), args.toArray(),
                                             (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                                                 Map<String, Object> m = new HashMap<>();
                                                 m.put("seq", rowNum + 1);
                                                 String transId = rs.getString("trans_id");
                                                 m.put("trans_id", transId);
                                                 m.put("transRrn", rs.getLong("trans_rrn"));
                                                 m.put("lotRrn", rs.getLong("lot_rrn"));
                                                 m.put("trans_start_timestamp", DateUtils.formatDate(rs.getTimestamp("trans_start_timestamp")));
                                                 m.put("trans_end_timestamp", DateUtils.formatDate(rs.getTimestamp("trans_end_timestamp")));
                                                 m.put("trans_performed_by", rs.getString("trans_performed_by"));
                                                 m.put("qty_1", rs.getDouble("qty1"));
                                                 m.put("qty_2", rs.getDouble("out_qty1"));

                                                 String processStepIdVer = rs.getString("process_step_id_version");

                                                 m.put("route_id", parseRoute(processStepIdVer));
                                                 String transComments = rs.getString("trans_comments");
                                                 if (rs.getString("trans_comments") != null) {
                                                     if (StringUtils.equalsIgnoreCase(transId, TransactionNames.HOLD_KEY) ||
                                                             StringUtils.equalsIgnoreCase(transId, TransactionNames.HOLD_RUNNING_LOT)) {
                                                         m.put("transComments", rs.getString("trans_comments"));
                                                     } else {
                                                         if (!(rs.getString("trans_comments")).equals(getTransReason(rs.getLong("trans_rrn"), lotRrn))) {
                                                             if (StringUtils.contains(transComments, "[changed lot location]")) {
                                                                 m.put("transComments", getTransReason(rs.getLong("trans_rrn"), lotRrn));
                                                             } else {
                                                                 m.put("transComments", rs.getString("trans_comments") + " " +
                                                                         getTransReason(rs.getLong("trans_rrn"), lotRrn));
                                                             }
                                                         } else {
                                                             m.put("transComments", getTransReason(rs.getLong("trans_rrn"), lotRrn));
                                                         }
                                                     }

                                                 } else {
                                                     m.put("transComments", getTransReason(rs.getLong("trans_rrn"), lotRrn));
                                                 }

                                                 // m.put("operation_id", parseOperation(processStepIdVer));
                                                 m.put("technology_id", rs.getString("process_id"));
                                                 m.put("stageId", rs.getString("stage_id"));
                                                 m.put("operationId", rs.getString("OPERATION_ID"));
                                                 m.put("carrierId", rs.getString("carrier_id"));
                                                 m.put("operationRrn", rs.getLong("operation_rrn"));
                                                 m.put("productRrn", rs.getLong("product_rrn"));
                                                 m.put("eqpt_rrn", rs.getLong("eqpt_rrn"));
                                                 m.put("productId", rs.getString("product_id"));
                                                 String lotTransHistoryEquipmentId = rs.getString("LTH_EQPT_ID");
                                                 m.put("eqptId", StringUtils.defaultIfBlank(lotTransHistoryEquipmentId, rs.getString("eqpt_id")));
                                                 m.put("operationDesc",rs.getString("operation_desc"));

                                                 String processStepString = "";
                                                 int firstSep = 0;
                                                 int secondSep = 0;
                                                 String routeRrn = "";
                                                 processStepString = rs.getString("process_step_version");
                                                 if (StringUtils.isNotBlank(processStepString)) {
                                                     firstSep = processStepString.indexOf("|");
                                                     secondSep = processStepString.indexOf("|", firstSep + 1);
                                                     if (secondSep >= 0) {
                                                         routeRrn = processStepString.substring(firstSep + 1, processStepString.indexOf(",", firstSep));
                                                     } else {
                                                         routeRrn = processStepString.substring(0, processStepString.indexOf(","));
                                                     }
                                                 }
                                                 m.put("productLayer", rs.getString("product_layer"));
                                                 long eqptRrn = rs.getLong("eqpt_rrn");

                                                 m.put("recipeId", rs.getString("recipe_physical_id"));
                                                 m.put("targetLotId", rs.getString("target_lot_id"));
                                                 m.put("targetUnitId", rs.getString("target_unit_id"));
                                                 m.put("sourceLotId", rs.getString("source_lot_id"));
                                                 m.put("routeRrn", NumberUtils.toLong(routeRrn));
                                                 m.put("processId", rs.getString("PROCESS_ID"));
                                                 m.put("processVersion", rs.getInt("PROCESS_VERSION"));
                                                 m.put("operationDesc", rs.getString("operation_desc"));
                                                 m.put("operationType", rs.getString("step_type"));
                                                 m.put("workArea", rs.getString("work_area"));
                                                 m.put("flowSeq", rs.getString("flow_seq"));
                                                 m.put("priority", rs.getString("priority"));
                                                 m.put("hotFlag", rs.getString("hot_flag"));
                                                 m.put("customerId", rs.getString("CUSTOMER_ID"));
                                                 m.put("shippingCode", rs.getString("SHIPPING_CODE"));
                                                 m.put("outerOrderNo", rs.getString("OUTER_ORDER_NO"));
                                                 m.put("outOrderType", rs.getString("OUT_ORDER_TYPE"));
                                                 m.put("carrierType", rs.getString("carrier_type"));
                                                 m.put("stepSequence", rs.getLong("step_sequence"));
                                                 m.put("location", rs.getString("location"));
                                                 m.put("pLocation", rs.getString("prev_location"));
                                                 m.put("showLotId", rs.getString("SHOW_LOT_ID"));
                                                 m.put("flipType", rs.getString("FLIP_TYPE"));
                                                 m.put("transModule", rs.getString("TRANS_MODULE"));
                                                 return m;
                                             });
        }

        @Override
        public List<Map<String, Object>> qryRuncardLotTransHistory(Map<String, Object> condition, int thisPage,
                                                                   int pageSize) {
            int fixPagesize = pageSize == 0 ? 10 : pageSize;
            StringBuilder where = new StringBuilder(" WHERE 1=1 ");

            if ((condition != null) && (condition.size() != 0)) {
                if (condition.get("lotRrn") != null) {
                    where.append(" AND lt.lot_rrn = ").append(MapUtils.getLong(condition, "lotRrn"));
                }

                if (condition.get("lotTransId") != null) {
                    where.append(" AND lt.trans_id = ? ");
                }
            }

            String filterTransIDParams = " AND lt.trans_id not in (?, ?, ?, ?) ";

            String sql = " SELECT *,LE.CURRENT_STEP_TIME_STRUCT FROM ( SELECT * FROM ( SELECT lt.trans_rrn,lt.trans_sequence,lt.trans_id,tr" +
                    ".trans_start_timestamp,tr" + ".trans_end_timestamp,tr.trans_performed_by, " +
                    " ls.stage_id,ls.lot_rrn,ls.lot_id,ls.facility_rrn,ls.step_sequence,ls.in_qty1,lt" +
                    ".qty1,ls.out_qty1,ls.product_rrn, " +
                    " ls.eqpt_rrn,lt.trans_comments,ls.route_seq,ls.operation_seq, " +
                    " ls.step_type,ls.work_area,ls.flow_seq,ls.operation_rrn,getinstanceid(ls" +
                    ".operation_rrn) as operation_id,ls.operation_desc, " +
                    " ls.product_layer,ls.recipe_physical_id,ls.priority,ls.hot_flag,smh.target_lot_id," +
                    "smh.target_unit_id, " + " smh.source_lot_id,lt.CUSTOMER_ID,lt.SHIPPING_CODE,lt.OUTER_ORDER_NO,lt" +
                    ".OUT_ORDER_TYPE,lt.location,lt.prev_location, " +
                    " SLS.SHOW_LOT_ID,SLS.CARRIER_ID,SLS.OBJECT_TYPE AS CARRIER_TYPE,SLS.PROCESS_ID,SLS" +
                    ".PROCESS_VERSION,SLS.PROCESS_STEP_ID_VERSION,sls.process_step_version " +
                    " FROM lot_step_history ls,lot_trans_history lt LEFT JOIN transaction_log tr ON lt" +
                    ".trans_rrn = tr.trans_rrn" +
                    " LEFT JOIN split_merge_history smh ON tr.trans_rrn = smh.trans_rrn left join " +
                    " (SELECT SLS.LOT_RRN,SLS.RUNCARD_RRN,SLS.CARRIER_ID,SLS.PROCESS_ID,SLS" +
                    ".PROCESS_VERSION,SLS.SHOW_LOT_ID,N.OBJECT_TYPE,SLS.MAIN_LOT_ID," +
                    "  lsh.process_step_id_version,lsh.process_step_version FROM SRC_LOT_STORE SLS," +
                    "NAMED_OBJECT N,lot_step_history lsh " +
                    "  WHERE SLS.CARRIER_RRN = N.INSTANCE_RRN  and sls.main_lot_id = lsh.lot_id and sls" +
                    ".main_lot_step_sequence = lsh.step_sequence) SLS on LT.LOT_RRN=SLS.LOT_RRN " + where.toString() +
                    " AND lt.lot_rrn = ls.lot_rrn AND lt.step_sequence = ls.step_sequence  " +
                    " AND (smh.source_lot_rrn <> smh.target_lot_rrn or (smh.target_lot_rrn is null and " +
                    " smh.source_lot_id is null)) " +
                    " AND (lt.lot_rrn = smh.target_lot_rrn or lt.lot_rrn = smh.source_lot_rrn or (smh" +
                    ".target_lot_rrn is null and smh.source_lot_id is null)) " + filterTransIDParams +
                    " ORDER BY trans_start_timestamp DESC, trans_sequence DESC ) WHERE ROWNUM >? ) WHERE " + "ROWNUM <=? ";

            List<Object> args = new ArrayList<>();
            if (condition.get("lotTransId") != null) {
                args.add(condition.get("lotTransId").toString());
            }
            args.add(TransactionNames.LBRD_KEY);
            args.add(TransactionNames.MOVENEXT_KEY);
            args.add(TransactionNames.WS_MOVENEXT_KEY);
            args.add(TransactionNames.CREATE_KEY);

            args.add(fixPagesize * (thisPage - 1));
            args.add(fixPagesize);

            return jdbcTemplate.query(sql, args.toArray(), new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> m = new HashMap<>();

                    m.put("seq", rowNum + 1);
                    String transId = rs.getString("trans_id");
                    m.put("trans_id", transId);
                    m.put("transRrn", rs.getLong("trans_rrn"));
                    m.put("lotRrn", rs.getLong("lot_rrn"));
                    m.put("trans_start_timestamp", DateUtils.formatDate(rs.getTimestamp("trans_start_timestamp")));
                    m.put("trans_end_timestamp", DateUtils.formatDate(rs.getTimestamp("trans_end_timestamp")));
                    m.put("trans_performed_by", rs.getString("trans_performed_by"));
                    m.put("qty_1", new Double(rs.getDouble("qty1")));
                    m.put("qty_2", rs.getDouble("out_qty1"));

                    String processStepIdVer = rs.getString("process_step_id_version");

                    m.put("route_id", parseRoute(processStepIdVer));
                    String transComments = rs.getString("trans_comments");
                    if (rs.getString("trans_comments") != null) {
                        if (StringUtils.equalsIgnoreCase(transId, TransactionNames.HOLD_KEY) ||
                                StringUtils.equalsIgnoreCase(transId, TransactionNames.HOLD_RUNNING_LOT)) {
                            m.put("transComments", rs.getString("trans_comments"));
                        } else {
                            if (!(rs.getString("trans_comments"))
                                    .equals(getTransReason(rs.getLong("trans_rrn"), (Long) condition.get("lotRrn")))) {
                                if (StringUtils.contains(transComments, "[changed lot location]")) {
                                    m.put("transComments",
                                          getTransReason(rs.getLong("trans_rrn"), (Long) condition.get("lotRrn")));
                                } else {
                                    m.put("transComments", rs.getString("trans_comments") + " " +
                                            getTransReason(rs.getLong("trans_rrn"), (Long) condition.get("lotRrn")));
                                }
                            } else {
                                m.put("transComments",
                                      getTransReason(rs.getLong("trans_rrn"), (Long) condition.get("lotRrn")));
                            }
                        }

                    } else {
                        m.put("transComments", getTransReason(rs.getLong("trans_rrn"), (Long) condition.get("lotRrn")));
                    }

                    // m.put("operation_id", parseOperation(processStepIdVer));
                    m.put("technology_id", rs.getString("process_id"));
                    m.put("stageId", rs.getString("stage_id"));
                    m.put("operationId", rs.getString("OPERATION_ID"));
                    m.put("carrierId", rs.getString("carrier_id"));
                    m.put("operationRrn", rs.getLong("operation_rrn"));
                    m.put("productRrn", rs.getLong("product_rrn"));
                    m.put("eqpt_rrn", rs.getLong("eqpt_rrn"));

                    String processStepString = "";
                    int firstSep = 0;
                    int secondSep = 0;
                    String routeRrn = "";
                    processStepString = rs.getString("process_step_version");
                    if (StringUtils.isNotBlank(processStepString)) {
                        firstSep = processStepString.indexOf("|");
                        secondSep = processStepString.indexOf("|", firstSep + 1);
                        if (secondSep >= 0) {
                            routeRrn = processStepString.substring(firstSep + 1, processStepString.indexOf(",", firstSep));
                        } else {
                            routeRrn = processStepString.substring(0, processStepString.indexOf(","));
                        }
                    }
                    m.put("productLayer", rs.getString("product_layer"));
                    long eqptRrn = rs.getLong("eqpt_rrn");

                    m.put("recipeId", rs.getString("recipe_physical_id"));
                    m.put("targetLotId", rs.getString("target_lot_id"));
                    m.put("targetUnitId", rs.getString("target_unit_id"));
                    m.put("sourceLotId", rs.getString("source_lot_id"));
                    m.put("routeRrn", NumberUtils.toLong(routeRrn));
                    m.put("processId", rs.getString("PROCESS_ID"));
                    m.put("processVersion", rs.getInt("PROCESS_VERSION"));
                    m.put("operationDesc", rs.getString("operation_desc"));
                    m.put("operationType", rs.getString("step_type"));
                    m.put("workArea", rs.getString("work_area"));
                    m.put("flowSeq", rs.getString("flow_seq"));
                    m.put("priority", rs.getString("priority"));
                    m.put("hotFlag", rs.getString("hot_flag"));

                    m.put("customerId", rs.getString("CUSTOMER_ID"));
                    m.put("shippingCode", rs.getString("SHIPPING_CODE"));
                    m.put("outerOrderNo", rs.getString("OUTER_ORDER_NO"));
                    m.put("outOrderType", rs.getString("OUT_ORDER_TYPE"));
                    m.put("carrierType", rs.getString("carrier_type"));
                    m.put("stepSequence", rs.getLong("step_sequence"));
                    m.put("location", rs.getString("location"));
                    m.put("pLocation", rs.getString("prev_location"));
                    m.put("showLotId", rs.getString("SHOW_LOT_ID"));
                    return m;
                }
            });
        }

        private String getTransReason(long transRrn, long lotRrn) {
            String sql = "select  listagg(tr.reason,',') WITHIN GROUP (ORDER BY tr.reason_code_sequence)  as reason " +
                    "from trans_reason tr where tr.trans_rrn=? " + "and tr.instance_rrn=?";

            String reason = jdbcTemplate.queryForObjectWithNull(sql, new Object[]{transRrn, lotRrn}, String.class);
            return reason == null ? "" : reason;
        }

        private long timeToLongSecs(Date date) {
            return date.getTime() / 1000;
        }

        private String parseRoute(String processStepIdVersion) {
            String routeId = null;

            if (StringUtils.isNotEmpty(processStepIdVersion)) {
                int start = processStepIdVersion.indexOf("|");
                int _start = processStepIdVersion.lastIndexOf("|");

                // handle case "Route_120898,0|WAFER_CODE,0|1000,1"
                // and case "WAFER_CODE,0|1000,1"
                if (start == _start) {
                    start = -1;
                }

                int end = processStepIdVersion.indexOf(",", start);

                routeId = processStepIdVersion.substring(start + 1, end);
            }

            return routeId;
        }

        private Map<String, Object> getRuncardLotCountSql(Map param) {
            List<Object> argList = new ArrayList<Object>();
            StringBuffer sql = new StringBuffer("SELECT count(*) as LOT_COUNT ");
            sql.append("FROM (SELECT RN.*, ROW_NUMBER() OVER(PARTITION BY RN.LOT_RRN ORDER BY LOT_RRN DESC) AS RN_NUM ");
            sql.append("FROM (SELECT CONCAT(CONCAT(HOTFLAG, '-'), PRIORITY) AS PRIORITY, ");
            sql.append(
                    "getinstanceid(getcontextreciperrn(PRODUCT_RRN, PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION,L" +
                            ".PRODUCT_VERSION)) AS CONTEXT_RECIPE, ");
            sql.append("getcontextreciperrn(PRODUCT_RRN, PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION,L" +
                               ".PRODUCT_VERSION) AS CONTEXT_RECIPE_RRN, ");
            sql.append("getflowseq(PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION) AS CONTEXT_FLOW_SEQ, ");
            sql.append("getflowstepdesc(PRODUCT_RRN, PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION) AS " +
                               "CONTEXT_STEP_DESC, ");
            sql.append("ROUND((GETMINREMAINTIMEBYLOTRRN(LOTRRN, ?) / 3600), 2) AS Q_TIME, ");
            argList.add(Constants.TIME_LIMIT.TIME_TYPE_MAX);
            sql.append("(CASE WHEN LENGTH(EQPTID) > 0 THEN EQPTID WHEN EQPTID = '' OR EQPTID IS NULL THEN EPT_GROUP END) " +
                               "EQPTID, ");
            sql.append("EQPTID AS RUNNING_EQPT, EPT_GROUP AS EQPT_LIST, ");
            sql.append(
                    "PODID, DOORID, LOT_RUNTIME.LOT_RUN_TIME, LOTID, REWORK_CATEGORY, LOTRRN, LOT_STATUS_REF.DATA_1_VALUE" +
                            " AS LOTSTATUS, LOT_CATEGORY_REF.DATA_1_VALUE AS LOTCATEGORY, ");
            sql.append("LOTTYPE, CARRIERID, PRODUCTID, PRODUCT_RRN, L.PRODUCT_VERSION, ROUTESEQ, STEPSEQ, RECIPEID, " +
                               "PROCESS_RRN, PROCESS_VERSION, ROUTE_RRN, ROUTE_VERSION, L.STEP_RRN, STEP_VERSION, ");
            sql.append("PROCESS_ID, ROUTE_ID, L.STEP_ID, L.EQPT_RRN, QUEUE_TIMESTAMP, STAGE_ID, POLLUTION_LEVEL, " +
                               "RECIPE_STRING, REA.HOLD_DEPT, REA.HOLD_REASON, ");
            sql.append("HOLD_TIMESTAMP, CUSTOMER_ID, SHIPPING_CODE, OUTER_ORDER_NO, OUT_ORDER_TYPE, CUSTOMER_LOT_ID, ");
            sql.append("LOCATION, PLOCATION,SRC_LOT_S.MAIN_LOT_ID, SRC_LOT_S.SHOW_LOT_ID, SRC_LOT_S.LOT_ID, SRC_LOT_S" +
                               ".CARRIER_ID, SRC_LOT_S.RUNCARD_ID, SRC_LOT_S.RECIPE_ID, ");
            sql.append("SRC_LOT_S.EQPT_ID, SRC_LOT_S.EQPT_GROUP_ID, SRC_LOT_S.LOT_STATUS, SRC_LOT_S.QTY1,SRC_LOT_S" +
                               ".WORK_AREA,SRC_LOT_S.LOT_RRN ");
            sql.append("FROM (SELECT ");
            sql.append("GETINSTANCEID(PA.POD_RRN) AS PODID, GETINSTANCEID(PA.DOOR_RRN) AS DOORID, T.LOT_RRN AS LOTRRN, T" +
                               ".REWORK_CATEGORY, T.LOT_STATUS AS LOTSTATUS, ");
            sql.append("T.CREATE_CATEGORY AS LOTCATEGORY, T.LOT_TYPE AS LOTTYPE, T.PRIORITY AS PRIORITY, T.HOT_FLAG AS " +
                               "HOTFLAG, T.QTY1 AS QTY1, GETINSTANCEID(T.CARRIER_RRN) AS CARRIERID, ");
            sql.append("GETINSTANCEID(T.PRODUCT_RRN) PRODUCTID, T.ROUTE_SEQ AS ROUTESEQ, T.OPERATION_SEQ AS STEPSEQ, " +
                               "GETINSTANCEID(T.RECIPE_LOGICAL_RRN) AS RECIPEID, T.LOT_ID AS LOTID, ");
            sql.append("T.PRODUCT_RRN, T.PRODUCT_VERSION,  T.EQPT_RRN, GETINSTANCEID(T.EQPT_RRN) AS EQPTID, LX" +
                               ".ATTRIBUTE_DATA1 AS POLLUTION_LEVEL, RECIPE_STRING, T.HOLD_TIMESTAMP, LX.CUSTOMER_ID, LX" +
                               ".SHIPPING_CODE, ");
            sql.append(
                    "LX.OUT_ORDER_TYPE, LX.CUSTOMER_LOT_ID, QUEUE_TIMESTAMP, STAGE_ID, T.PROCESS_RRN, T.PROCESS_VERSION, " +
                            "getinstanceid(T.PROCESS_RRN) AS PROCESS_ID, LX.OUTER_ORDER_NO, ");
            sql.append("LX.LOCATION, LX.PLOCATION, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1)" +
                            " ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 2) + 1, 1)" +
                            " ");
            sql.append("END AS ROUTE_RRN, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 1) +" +
                            " 1, 1) ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 2) +" +
                            " 1, 1) ");
            sql.append("END AS ROUTE_ID, ");
            sql.append("CASE  WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^|]+', INSTR(T.PROCESS_STEP_VERSION, ',', 1, 2) + 1, 1)" +
                            " ");
            sql.append("ELSE SUBSTR(PROCESS_STEP_VERSION, INSTR(PROCESS_STEP_VERSION, ',', 1, 3) + 1) ");
            sql.append("END AS ROUTE_VERSION, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 2) + 1, 1)" +
                            " ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1)" +
                            " ");
            sql.append("END AS STEP_RRN, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 2) +" +
                            " 1, 1) ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 1) +" +
                            " 1, 1) ");
            sql.append("END AS STEP_ID, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append("THEN SUBSTR(PROCESS_STEP_VERSION, INSTR(PROCESS_STEP_VERSION, ',', 1, 3) + 1) ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^|]+', INSTR(T.PROCESS_STEP_VERSION, ',', 1, 2) + 1, 1)" +
                            " ");
            sql.append("END AS STEP_VERSION ");
            sql.append("FROM LOT T, LOT_EXT LX, PCD_ASSEMBLY PA, NAMED_OBJECT PRODUCT_N ");
            sql.append("WHERE T.LOT_RRN = LX.LOT_RRN(+) ");
            sql.append("AND T.CARRIER_RRN = PA.CARRIER_RRN(+) AND T.PRODUCT_RRN = PRODUCT_N.INSTANCE_RRN(+) AND T" +
                               ".FACILITY_RRN = ? ");
            argList.add(MapUtils.getIntValue(param, "facilityRrn", 0));

            if (MapUtils.getObject(param, "lotCategoryArray") != null) {
                String[] lotCategoryArray = (String[]) MapUtils.getObject(param, "lotCategoryArray");
                String categoryCondition = "AND (";
                for (int i = 0; i < lotCategoryArray.length; i++) {
                    if (StringUtils.isNotBlank(lotCategoryArray[i])) {
                        if (i == 0) {
                            categoryCondition += "T.CREATE_CATEGORY = ? ";
                        } else {
                            categoryCondition += "OR T.CREATE_CATEGORY = ? ";
                        }
                        argList.add(lotCategoryArray[i]);
                    }
                }
                categoryCondition += ") ";
                if (StringUtils.contains(categoryCondition, "CREATE_CATEGORY")) {
                    sql.append(categoryCondition);
                }
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "lotType"))) {
                sql.append("AND T.LOT_TYPE = ? ");
                argList.add(MapUtils.getString(param, "lotType"));
            }
            String[] filterStatus = LotStatus.getFilterStatus();
            for (String filter : filterStatus) {
                sql.append("AND T.LOT_STATUS <> ? ");
                argList.add(filter);
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "priority"))) {
                if (MapUtils.getString(param, "priority").indexOf("-") > 0) {
                    String[] pris = MapUtils.getString(param, "priority").split("-");
                    if (pris != null && pris.length == 2) {
                        sql.append("AND T.HOT_FLAG = ? AND T.PRIORITY = ? ");
                        argList.add(Long.parseLong(pris[0]));
                        argList.add(Long.parseLong(pris[1]));
                    }
                }
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "productType"))) {
                sql.append("AND PRODUCT_N.OBJECT_TYPE = ? ");
                argList.add(MapUtils.getString(param, "productType"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "productId"))) {
                sql.append("AND getinstanceid(T.PRODUCT_RRN) LIKE ? ");
                argList.add(MapUtils.getString(param, "productId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "processId"))) {
                sql.append("AND getinstanceid(T.PROCESS_RRN) LIKE ? ");
                argList.add(MapUtils.getString(param, "processId"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "routeId"))) {
                sql.append(
                        "AND REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', instr(T.PROCESS_STEP_ID_VERSION, '|', 1, " +
                                "1) + 1, 1) LIKE ? ");
                argList.add(MapUtils.getString(param, "routeId"));
            }

            sql.append(
                    ") L, (SELECT DISTINCT RS.TO_RRN AS STEP_RRN, RS.FROM_RRN AS STATION_RRN, GETINSTANCEID(RS.FROM_RRN) " +
                            "AS STATION_ID ");
            sql.append("FROM RELATION RS WHERE RS.LINK_TYPE = 'STATION_TO_OPERATION' ");
            sql.append("UNION ");
            sql.append("SELECT DISTINCT O.OPERATION_RRN AS STEP_RRN, EQPT_GROUP_T.STATION_RRN, EQPT_GROUP_T.STATION_ID ");
            sql.append("FROM OPERATION O, (SELECT EQPT_GROUP_R.TO_RRN AS EPQT_GROUP_RRN, EQPT_STATION_T.STATION_RRN, " +
                               "EQPT_STATION_T.STATION_ID ");
            sql.append("FROM RELATION EQPT_GROUP_R, (SELECT EQPT_STATION_R.TO_RRN AS EQPT_RRN, STATION_T.FROM_RRN AS " +
                               "STATION_RRN, STATION_ID ");
            sql.append("FROM RELATION EQPT_STATION_R, (SELECT N.INSTANCE_RRN AS FROM_RRN, N.INSTANCE_ID  AS STATION_ID ");
            sql.append("FROM NAMED_OBJECT N WHERE N.OBJECT = 'STATION') STATION_T WHERE EQPT_STATION_R.LINK_TYPE = " +
                               "'STATION_TO_EQUIPMENT' ");
            sql.append("AND EQPT_STATION_R.FROM_RRN IN STATION_T.FROM_RRN) EQPT_STATION_T ");
            sql.append(
                    "WHERE EQPT_GROUP_R.LINK_TYPE = 'ENTITY_TO_ENTITY_GROUP' AND EQPT_GROUP_R.FROM_RRN = EQPT_STATION_T" +
                            ".EQPT_RRN) EQPT_GROUP_T ");
            sql.append("WHERE O.ENTITY_GROUP_RRN IN EQPT_GROUP_T.EPQT_GROUP_RRN) STATION_T, ");
            sql.append(
                    "(SELECT TR.TRANS_RRN AS HOLD_RRN, TR.INSTANCE_RRN AS LOT_RRN, MAXMH.HOLD_USER_RRN, TR.RESPONSIBILITY" +
                            " AS HOLD_USER_ID, ");
            sql.append("CASE WHEN LENGTH(SUBSTR(TR.REASON, 1, INSTR(TR.REASON, 'Reason:', 1, 1)-1)) > 0 THEN SUBSTR(TR" +
                               ".REASON, 1, INSTR(TR.REASON, 'Reason:', 1, 1)-1) ");
            sql.append("ELSE 'SYSTEM' END AS HOLD_DEPT, SUBSTR(TR.REASON, INSTR(TR.REASON, 'Reason:', 1, 1)+7, LENGTH(TR" +
                               ".REASON)) AS HOLD_REASON ");
            sql.append("FROM TRANS_REASON TR, (SELECT M.TRANS_RRN, M.HOLD_BY AS HOLD_USER_RRN, M.INSTANCE_RRN AS LOT_RRN " +
                               "FROM MULTIPLE_HOLD M, ");
            sql.append(
                    "(SELECT MAX(MH.HOLD_TIMESTAMP) AS HOLD_DATE, MAX(MH.SEQUENCE_NUMBER) AS HOLD_SEQ, MH.INSTANCE_RRN AS" +
                            " LOT_RRN ");
            sql.append("FROM MULTIPLE_HOLD MH WHERE MH.OBJECT = 'LOT' GROUP BY MH.INSTANCE_RRN) MAXM ");
            sql.append(
                    "WHERE M.HOLD_TIMESTAMP = MAXM.HOLD_DATE AND M.SEQUENCE_NUMBER = MAXM.HOLD_SEQ AND M.INSTANCE_RRN = " +
                            "MAXM.LOT_RRN) MAXMH ");
            sql.append("WHERE TR.TRANS_RRN = MAXMH.TRANS_RRN AND TR.INSTANCE_RRN = MAXMH.LOT_RRN) REA, ");
            sql.append("(SELECT LISTAGG(GETINSTANCEID(R.FROM_RRN), ',') WITHIN GROUP(ORDER BY R.TO_RRN) AS EPT_GROUP, R" +
                               ".TO_RRN AS ENTITY_GROUP_RRN ");
            sql.append("FROM RELATION R WHERE R.LINK_TYPE = 'ENTITY_TO_ENTITY_GROUP' OR R.LINK_TYPE = " +
                               "'ENTITY_TO_ENTITY_GROUP_INACTIVE' GROUP BY R.TO_RRN) EGR, ");
            sql.append("(SELECT MAX(TLOG.TRANS_START_TIMESTAMP) AS LOT_RUN_TIME, LOT_TRANS.LOT_RRN FROM TRANSACTION_LOG " +
                               "TLOG, ");
            sql.append("(SELECT TRANS_RRN, LOT_RRN FROM LOT_TRANS_HISTORY WHERE TRANS_ID = 'MOVEIN') LOT_TRANS ");
            sql.append("WHERE TLOG.TRANS_RRN IN (LOT_TRANS.TRANS_RRN) GROUP BY LOT_TRANS.LOT_RRN) LOT_RUNTIME, ");
            sql.append("(SELECT RF.KEY_1_VALUE, RF.DATA_1_VALUE FROM NAMED_OBJECT N, REFERENCE_FILE_DETAIL RF ");
            sql.append(
                    "WHERE N.INSTANCE_ID = '$LOT_CREATE_CATEGORY' AND N.NAMED_SPACE = ? AND N.OBJECT = 'REFERENCEFILE' ");
            argList.add(MapUtils.getString(param, "referenceNamedSpace"));

            sql.append("AND N.INSTANCE_RRN = RF.REFERENCE_FILE_RRN) LOT_CATEGORY_REF, ");
            sql.append("(SELECT RF.KEY_1_VALUE, RF.DATA_1_VALUE FROM NAMED_OBJECT N, REFERENCE_FILE_DETAIL RF ");
            sql.append("WHERE N.INSTANCE_ID = '$$LOT_STATUS' AND N.NAMED_SPACE = ? AND N.OBJECT = 'REFERENCEFILE' ");
            argList.add(MapUtils.getString(param, "referenceNamedSpace"));

            sql.append("AND N.INSTANCE_RRN = RF.REFERENCE_FILE_RRN) LOT_STATUS_REF, ");

            sql.append("(SELECT SLS.LOT_RRN,SLS.MAIN_LOT_ID,SLS.LOT_ID,SLS.SHOW_LOT_ID,SLS.CARRIER_ID, ");
            sql.append("getinstanceid(SLS.RUNCARD_RRN) AS RUNCARD_ID,SLSS.RECIPE_ID,SLSS.WORK_AREA,SLSS.EQPT_ID,SLSS" +
                               ".EQPT_GROUP_ID,SLSS.EQPT_GROUP_RRN,T.LOT_STATUS,T.QTY1 ");
            sql.append(" FROM SRC_LOT_STORE SLS, SRC_LOT_SPECIAL_STEP SLSS,LOT T, NAMED_OBJECT N ");
            sql.append(" WHERE SLS.LOT_SPECIAL_STEP_RRN = SLSS.LOT_SPECIAL_STEP_RRN(+) AND SLS.STEP_SEQUENCE = SLSS" +
                               ".STEP_SEQUENCE(+) AND SLS.LOT_RRN = T.LOT_RRN ");
            sql.append(
                    " AND to_number(substr(SLS.runcard_rrn_h, instr(SLS.runcard_rrn_h, ',', -1) + 1)) = N.INSTANCE_RRN ");

            if (StringUtils.isNotBlank(MapUtils.getString(param, "portalType"))) {
                sql.append("AND N.OBJECT_TYPE = ? ");
                argList.add(MapUtils.getString(param, "portalType"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "lotId"))) {
                sql.append("AND SLS.LOT_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "lotId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "carrierId"))) {
                sql.append("AND SLS.CARRIER_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "carrierId"));
            }
            if (MapUtils.getObject(param, "lotStatusArray") != null) {
                String[] lotStatusArray = (String[]) MapUtils.getObject(param, "lotStatusArray");
                String statusCondition = "AND (";
                boolean reworkFlag = false;
                for (int i = 0; i < lotStatusArray.length; i++) {
                    if (StringUtils.equals(LotStatus.ALL, lotStatusArray[i])) {
                        continue;
                    }
                    if (!reworkFlag && StringUtils.equals(lotStatusArray[i], LotStatus.REWORK)) {
                        reworkFlag = true;
                    }

                    if (i == 0) {
                        statusCondition += "T.LOT_STATUS = ? ";
                    } else {
                        statusCondition += "OR T.LOT_STATUS = ? ";
                    }
                    argList.add(lotStatusArray[i]);

                    if (StringUtils.equals(LotStatus.ACTIVE, lotStatusArray[i])) {
                        statusCondition +=
                                "OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS =" + " ? ";
                        argList.add(LotStatus.RUNNING);
                        argList.add(LotStatus.WAITING);
                        argList.add(LotStatus.PROCESSED);
                        argList.add(LotStatus.DISPATCH);
                    } else if (StringUtils.equals(LotStatus.UNACTIVE, lotStatusArray[i])) {
                        statusCondition +=
                                "OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS =" + " ? ";
                        argList.add(LotStatus.BONDED);
                        argList.add(LotStatus.FINISH);
                        argList.add(LotStatus.TERMINATED);
                        argList.add(LotStatus.SCRAPPED);
                    }
                }
                if (reworkFlag) {
                    statusCondition += "OR REWORK_CATEGORY IS NOT NULL ";
                }
                statusCondition += ") ";
                if (StringUtils.contains(statusCondition, "LOT_STATUS")) {
                    sql.append(statusCondition);
                }
            }

            sql.append(") SRC_LOT_S ");

            sql.append("WHERE L.STEP_RRN = STATION_T.STEP_RRN(+) ");
            sql.append("AND SRC_LOT_S.MAIN_LOT_ID = L.LOTID(+) AND SRC_LOT_S.LOT_RRN = REA.LOT_RRN(+) ");
            sql.append(
                    "AND SRC_LOT_S.EQPT_GROUP_RRN = EGR.ENTITY_GROUP_RRN(+) AND SRC_LOT_S.LOT_RRN = LOT_RUNTIME.LOT_RRN" +
                            "(+) AND L.LOTCATEGORY = LOT_CATEGORY_REF.KEY_1_VALUE ");
            sql.append("AND SRC_LOT_S.LOT_STATUS = LOT_STATUS_REF.KEY_1_VALUE(+) ");

            if (StringUtils.isNotBlank(MapUtils.getString(param, "stationId"))) {
                sql.append("AND STATION_T.STATION_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "stationId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "eqptId"))) {
                sql.append(" AND SRC_LOT_S.EQPT_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "eqptId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "eqptGroupId"))) {
                sql.append("AND SRC_LOT_S.EQPT_GROUP_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "eqptGroupId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "workArea"))) {
                sql.append("AND SRC_LOT_S.WORK_AREA = ? ");
                argList.add(MapUtils.getString(param, "workArea"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "stageId"))) {
                sql.append("AND L.STAGE_ID like ? ");
                argList.add(MapUtils.getString(param, "stageId"));
            }

            sql.append(") RN) RN_T WHERE RN_T.RN_NUM = 1 ");

            Map<String, Object> sqlMap = new HashMap<String, Object>();
            sqlMap.put("sql", sql.toString());
            sqlMap.put("argList", argList);
            return sqlMap;
        }

        private Map<String, Object> getRuncardLotPortalListSql(Map param, int startRow, int currentTotalRow) {
            List<Object> argList = new ArrayList<Object>();
            StringBuffer sql = new StringBuffer("SELECT * FROM (SELECT ROW_T.*, ROWNUM AS ROW_NO FROM " + "(SELECT * ");
            sql.append(
                    "FROM (SELECT RN.*, ROW_NUMBER() OVER(PARTITION BY RN.LOT_RRN ORDER BY LOT_RRN DESC) AS " + "RN_NUM ");
            sql.append("FROM (SELECT CONCAT(CONCAT(HOTFLAG, '-'), PRIORITY) AS PRIORITY, ");
            sql.append(
                    "getinstanceid(getcontextreciperrn(PRODUCT_RRN, PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION,L" +
                            ".PRODUCT_VERSION)) AS CONTEXT_RECIPE, ");
            sql.append("getcontextreciperrn(PRODUCT_RRN, PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION,L" +
                               ".PRODUCT_VERSION) AS CONTEXT_RECIPE_RRN, ");
            sql.append("getflowseq(PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION) AS CONTEXT_FLOW_SEQ, ");
            sql.append("getflowstepdesc(PRODUCT_RRN, PROCESS_RRN, ROUTE_RRN, L.STEP_RRN, PROCESS_VERSION) AS " +
                               "CONTEXT_STEP_DESC, ");
            sql.append("ROUND((GETMINREMAINTIMEBYLOTRRN(SRC_LOT_S.LOT_RRN, ?) / 3600), 2) AS Q_TIME, ");
            argList.add(Constants.TIME_LIMIT.TIME_TYPE_MAX);
            sql.append("(CASE WHEN LENGTH(EQPTID) > 0 THEN EQPTID WHEN EQPTID = '' OR EQPTID IS NULL THEN EPT_GROUP END) " +
                               "EQPTID, ");
            sql.append("EQPTID AS RUNNING_EQPT, EPT_GROUP AS EQPT_LIST, ");
            sql.append(
                    "PODID, DOORID, LOT_RUNTIME.LOT_RUN_TIME, LOTID, REWORK_CATEGORY, LOTRRN, LOT_STATUS_REF.DATA_1_VALUE" +
                            " AS LOTSTATUS, LOT_CATEGORY_REF.DATA_1_VALUE AS LOTCATEGORY, ");
            sql.append("LOTTYPE, CARRIERID, PRODUCTID, PRODUCT_RRN, L.PRODUCT_VERSION, ROUTESEQ, STEPSEQ, RECIPEID, " +
                               "PROCESS_RRN, PROCESS_VERSION, ROUTE_RRN, ROUTE_VERSION, L.STEP_RRN, STEP_VERSION, ");
            sql.append("PROCESS_ID, ROUTE_ID, L.STEP_ID, L.EQPT_RRN, QUEUE_TIMESTAMP, STAGE_ID, " +
                               "RECIPE_STRING, REA.HOLD_DEPT, REA.HOLD_REASON, ");
            sql.append("HOLD_TIMESTAMP, CUSTOMER_ID, SHIPPING_CODE, OUTER_ORDER_NO, OUT_ORDER_TYPE, CUSTOMER_LOT_ID," +
                               "LOCATION, PLOCATION, ");
            sql.append("SRC_LOT_S.MAIN_LOT_ID, SRC_LOT_S.SHOW_LOT_ID, SRC_LOT_S.LOT_ID, SRC_LOT_S.CARRIER_ID, SRC_LOT_S" +
                               ".RUNCARD_ID,SRC_LOT_S.RUNCARD_RRN,SRC_LOT_S.OPERATOR_COMMENTS, SRC_LOT_S.RECIPE_ID," +
                               "SRC_LOT_S" + ".SUB_STATUS, ");
            sql.append("SRC_LOT_S.EQPT_ID, SRC_LOT_S.EQPT_GROUP_ID, SRC_LOT_S.LOT_STATUS, SRC_LOT_S.QTY1,SRC_LOT_S" +
                               ".WORK_AREA,SRC_LOT_S.LOT_RRN,SRC_LOT_S.RUNCARD_RRN_H,SRC_LOT_S.RUNCARD_ID_H,SRC_LOT_S" +
                               ".AUTO_FLAG,SRC_LOT_S.POLLUTION_LEVEL, ");
            sql.append("SRC_LOT_S.LOT_EQPT_ID, ENTITY_G.LOT_EQPT_GROUP_ID ");
            sql.append("FROM (SELECT ");
            sql.append("GETINSTANCEID(PA.POD_RRN) AS PODID, GETINSTANCEID(PA.DOOR_RRN) AS DOORID, T.LOT_RRN AS LOTRRN, T" +
                               ".REWORK_CATEGORY, T.LOT_STATUS AS LOTSTATUS, ");
            sql.append("T.CREATE_CATEGORY AS LOTCATEGORY, T.LOT_TYPE AS LOTTYPE, T.PRIORITY AS PRIORITY, T.HOT_FLAG AS " +
                               "HOTFLAG, T.QTY1 AS QTY1, GETINSTANCEID(T.CARRIER_RRN) AS CARRIERID, ");
            sql.append("GETINSTANCEID(T.PRODUCT_RRN) PRODUCTID, T.ROUTE_SEQ AS ROUTESEQ, T.OPERATION_SEQ AS STEPSEQ, " +
                               "GETINSTANCEID(T.RECIPE_LOGICAL_RRN) AS RECIPEID, T.LOT_ID AS LOTID, ");
            sql.append("T.PRODUCT_RRN, T.PRODUCT_VERSION, T.EQPT_RRN, GETINSTANCEID(T.EQPT_RRN) AS EQPTID, " +
                               "RECIPE_STRING, T.HOLD_TIMESTAMP, LX.CUSTOMER_ID, LX.SHIPPING_CODE, ");
            sql.append(
                    "LX.OUT_ORDER_TYPE, LX.CUSTOMER_LOT_ID, QUEUE_TIMESTAMP, STAGE_ID, T.PROCESS_RRN, T.PROCESS_VERSION, " +
                            "getinstanceid(T.PROCESS_RRN) AS PROCESS_ID, LX.OUTER_ORDER_NO, ");
            sql.append("LX.LOCATION, LX.PLOCATION, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1)" +
                            " ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 2) + 1, 1)" +
                            " ");
            sql.append("END AS ROUTE_RRN, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 1) +" +
                            " 1, 1) ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 2) +" +
                            " 1, 1) ");
            sql.append("END AS ROUTE_ID, ");
            sql.append("CASE  WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^|]+', INSTR(T.PROCESS_STEP_VERSION, ',', 1, 2) + 1, 1)" +
                            " ");
            sql.append("ELSE SUBSTR(PROCESS_STEP_VERSION, INSTR(PROCESS_STEP_VERSION, ',', 1, 3) + 1) ");
            sql.append("END AS ROUTE_VERSION, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 2) + 1, 1)" +
                            " ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1)" +
                            " ");
            sql.append("END AS STEP_RRN, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append(
                    "THEN REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 2) +" +
                            " 1, 1) ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', INSTR(T.PROCESS_STEP_ID_VERSION, '|', 1, 1) +" +
                            " 1, 1) ");
            sql.append("END AS STEP_ID, ");
            sql.append("CASE WHEN REWORK_CATEGORY IS NULL OR REWORK_CATEGORY = '' ");
            sql.append("THEN SUBSTR(PROCESS_STEP_VERSION, INSTR(PROCESS_STEP_VERSION, ',', 1, 3) + 1) ");
            sql.append(
                    "ELSE REGEXP_SUBSTR(T.PROCESS_STEP_VERSION, '[^|]+', INSTR(T.PROCESS_STEP_VERSION, ',', 1, 2) + 1, 1)" +
                            " ");
            sql.append("END AS STEP_VERSION ");
            sql.append("FROM LOT T, LOT_EXT LX, PCD_ASSEMBLY PA, NAMED_OBJECT PRODUCT_N ");
            sql.append("WHERE T.LOT_RRN = LX.LOT_RRN(+) ");
            sql.append("AND T.CARRIER_RRN = PA.CARRIER_RRN(+) AND T.PRODUCT_RRN = PRODUCT_N.INSTANCE_RRN(+) AND T" +
                               ".FACILITY_RRN = ? ");
            argList.add(MapUtils.getIntValue(param, "facilityRrn", 0));

            if (MapUtils.getObject(param, "lotCategoryArray") != null) {
                String[] lotCategoryArray = (String[]) MapUtils.getObject(param, "lotCategoryArray");
                String categoryCondition = "AND (";
                for (int i = 0; i < lotCategoryArray.length; i++) {
                    if (StringUtils.isNotBlank(lotCategoryArray[i])) {
                        if (i == 0) {
                            categoryCondition += "T.CREATE_CATEGORY = ? ";
                        } else {
                            categoryCondition += "OR T.CREATE_CATEGORY = ? ";
                        }
                        argList.add(lotCategoryArray[i]);
                    }
                }
                categoryCondition += ") ";
                if (StringUtils.contains(categoryCondition, "CREATE_CATEGORY")) {
                    sql.append(categoryCondition);
                }
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "lotType"))) {
                sql.append("AND T.LOT_TYPE = ? ");
                argList.add(MapUtils.getString(param, "lotType"));
            }
            String[] filterStatus = LotStatus.getFilterStatus();
            for (String filter : filterStatus) {
                sql.append("AND T.LOT_STATUS <> ? ");
                argList.add(filter);
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "priority"))) {
                if (MapUtils.getString(param, "priority").indexOf("-") > 0) {
                    String[] pris = MapUtils.getString(param, "priority").split("-");
                    if (pris != null && pris.length == 2) {
                        sql.append("AND T.HOT_FLAG = ? AND T.PRIORITY = ? ");
                        argList.add(Long.parseLong(pris[0]));
                        argList.add(Long.parseLong(pris[1]));
                    }
                }
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "productType"))) {
                sql.append("AND PRODUCT_N.OBJECT_TYPE = ? ");
                argList.add(MapUtils.getString(param, "productType"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "productId"))) {
                sql.append("AND getinstanceid(T.PRODUCT_RRN) LIKE ? ");
                argList.add(MapUtils.getString(param, "productId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "processId"))) {
                sql.append("AND getinstanceid(T.PROCESS_RRN) LIKE ? ");
                argList.add(MapUtils.getString(param, "processId"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "routeId"))) {
                sql.append(
                        "AND REGEXP_SUBSTR(T.PROCESS_STEP_ID_VERSION, '[^,]+', instr(T.PROCESS_STEP_ID_VERSION, '|', 1, " +
                                "1) + 1, 1) LIKE ? ");
                argList.add(MapUtils.getString(param, "routeId"));
            }

            sql.append(
                    ") L, (SELECT DISTINCT RS.TO_RRN AS STEP_RRN, RS.FROM_RRN AS STATION_RRN, GETINSTANCEID(RS.FROM_RRN) " +
                            "AS STATION_ID ");
            sql.append("FROM RELATION RS WHERE RS.LINK_TYPE = 'STATION_TO_OPERATION' ");
            sql.append("UNION ");
            sql.append("SELECT DISTINCT O.OPERATION_RRN AS STEP_RRN, EQPT_GROUP_T.STATION_RRN, EQPT_GROUP_T.STATION_ID ");
            sql.append("FROM OPERATION O, (SELECT EQPT_GROUP_R.TO_RRN AS EPQT_GROUP_RRN, EQPT_STATION_T.STATION_RRN, " +
                               "EQPT_STATION_T.STATION_ID ");
            sql.append("FROM RELATION EQPT_GROUP_R, (SELECT EQPT_STATION_R.TO_RRN AS EQPT_RRN, STATION_T.FROM_RRN AS " +
                               "STATION_RRN, STATION_ID ");
            sql.append("FROM RELATION EQPT_STATION_R, (SELECT N.INSTANCE_RRN AS FROM_RRN, N.INSTANCE_ID  AS STATION_ID ");
            sql.append("FROM NAMED_OBJECT N WHERE N.OBJECT = 'STATION') STATION_T WHERE EQPT_STATION_R.LINK_TYPE = " +
                               "'STATION_TO_EQUIPMENT' ");
            sql.append("AND EQPT_STATION_R.FROM_RRN IN STATION_T.FROM_RRN) EQPT_STATION_T ");
            sql.append(
                    "WHERE EQPT_GROUP_R.LINK_TYPE = 'ENTITY_TO_ENTITY_GROUP' AND EQPT_GROUP_R.FROM_RRN = EQPT_STATION_T" +
                            ".EQPT_RRN) EQPT_GROUP_T ");
            sql.append("WHERE O.ENTITY_GROUP_RRN IN EQPT_GROUP_T.EPQT_GROUP_RRN) STATION_T, ");
            sql.append(
                    "(SELECT TR.TRANS_RRN AS HOLD_RRN, TR.INSTANCE_RRN AS LOT_RRN, MAXMH.HOLD_USER_RRN, TR.RESPONSIBILITY" +
                            " AS HOLD_USER_ID, ");
            sql.append("CASE WHEN LENGTH(SUBSTR(TR.REASON, 1, INSTR(TR.REASON, 'Reason:', 1, 1)-1)) > 0 THEN SUBSTR(TR" +
                               ".REASON, 1, INSTR(TR.REASON, 'Reason:', 1, 1)-1) ");
            sql.append("ELSE 'SYSTEM' END AS HOLD_DEPT, SUBSTR(TR.REASON, INSTR(TR.REASON, 'Reason:', 1, 1)+7, LENGTH(TR" +
                               ".REASON)) AS HOLD_REASON ");
            sql.append("FROM TRANS_REASON TR, (SELECT M.TRANS_RRN, M.HOLD_BY AS HOLD_USER_RRN, M.INSTANCE_RRN AS LOT_RRN " +
                               "FROM MULTIPLE_HOLD M, ");
            sql.append(
                    "(SELECT MAX(MH.HOLD_TIMESTAMP) AS HOLD_DATE, MAX(MH.SEQUENCE_NUMBER) AS HOLD_SEQ, MH.INSTANCE_RRN AS" +
                            " LOT_RRN ");
            sql.append("FROM MULTIPLE_HOLD MH WHERE MH.OBJECT = 'LOT' GROUP BY MH.INSTANCE_RRN) MAXM ");
            sql.append(
                    "WHERE M.HOLD_TIMESTAMP = MAXM.HOLD_DATE AND M.SEQUENCE_NUMBER = MAXM.HOLD_SEQ AND M.INSTANCE_RRN = " +
                            "MAXM.LOT_RRN) MAXMH ");
            sql.append("WHERE TR.TRANS_RRN = MAXMH.TRANS_RRN AND TR.INSTANCE_RRN = MAXMH.LOT_RRN) REA, ");
            sql.append("(SELECT LISTAGG(GETINSTANCEID(R.FROM_RRN), ',') WITHIN GROUP(ORDER BY R.TO_RRN) AS EPT_GROUP, R" +
                               ".TO_RRN AS ENTITY_GROUP_RRN ");
            sql.append("FROM RELATION R WHERE R.LINK_TYPE = 'ENTITY_TO_ENTITY_GROUP' OR R.LINK_TYPE = " +
                               "'ENTITY_TO_ENTITY_GROUP_INACTIVE' GROUP BY R.TO_RRN) EGR, ");
            sql.append("(SELECT MAX(TLOG.TRANS_START_TIMESTAMP) AS LOT_RUN_TIME, LOT_TRANS.LOT_RRN FROM TRANSACTION_LOG " +
                               "TLOG, ");
            sql.append("(SELECT TRANS_RRN, LOT_RRN FROM LOT_TRANS_HISTORY WHERE TRANS_ID = 'MOVEIN') LOT_TRANS ");
            sql.append("WHERE TLOG.TRANS_RRN IN (LOT_TRANS.TRANS_RRN) GROUP BY LOT_TRANS.LOT_RRN) LOT_RUNTIME, ");
            sql.append("(SELECT RF.KEY_1_VALUE, RF.DATA_1_VALUE FROM NAMED_OBJECT N, REFERENCE_FILE_DETAIL RF ");
            sql.append(
                    "WHERE N.INSTANCE_ID = '$LOT_CREATE_CATEGORY' AND N.NAMED_SPACE = ? AND N.OBJECT = 'REFERENCEFILE' ");
            argList.add(MapUtils.getString(param, "referenceNamedSpace"));

            sql.append("AND N.INSTANCE_RRN = RF.REFERENCE_FILE_RRN) LOT_CATEGORY_REF, ");
            sql.append("(SELECT RF.KEY_1_VALUE, RF.DATA_1_VALUE FROM NAMED_OBJECT N, REFERENCE_FILE_DETAIL RF ");
            sql.append("WHERE N.INSTANCE_ID = '$$LOT_STATUS' AND N.NAMED_SPACE = ? AND N.OBJECT = 'REFERENCEFILE' ");
            argList.add(MapUtils.getString(param, "referenceNamedSpace"));

            sql.append("AND N.INSTANCE_RRN = RF.REFERENCE_FILE_RRN) LOT_STATUS_REF, ");

            sql.append(
                    "(SELECT SLS.LOT_RRN,SLS.MAIN_LOT_ID,SLS.LOT_ID,SLS.SHOW_LOT_ID," +
                            "CASE WHEN T.LOT_RRN=T.BASED_LOT_RRN THEN SLS.CARRIER_ID ELSE GETINSTANCEID(T.CARRIER_RRN) END CARRIER_ID,SLS.RUNCARD_ID_H,SLS" +
                            ".RUNCARD_RRN_H,SLS.SUB_STATUS, ");
            sql.append(
                    "getinstanceid(SLS.RUNCARD_RRN) AS RUNCARD_ID,SLS.RUNCARD_RRN,SLSS.OPERATOR_COMMENTS,SLSS.RECIPE_ID," +
                            "SLSS.WORK_AREA,  ");
            sql.append(
                    "SLSS.EQPT_ID,SLSS.EQPT_RRN,SLSS.EQPT_GROUP_ID,SLSS.EQPT_GROUP_RRN,T.EQPT_RRN AS LOT_EQPT_RRN,EQPT_N" +
                            ".INSTANCE_ID AS LOT_EQPT_ID,T.LOT_STATUS,T.QTY1,LRC.AUTO_FLAG,LE.ATTRIBUTE_DATA1 AS POLLUTION_LEVEL ");
            sql.append(" FROM SRC_LOT_STORE SLS, SRC_LOT_SPECIAL_STEP SLSS,LOT T,LOT_RUN_CARD LRC,NAMED_OBJECT N," +
                               "NAMED_OBJECT EQPT_N,LOT_EXT LE ");
            sql.append(" WHERE SLS.LOT_SPECIAL_STEP_RRN = SLSS.LOT_SPECIAL_STEP_RRN(+) AND SLS.STEP_SEQUENCE = SLSS" +
                               ".STEP_SEQUENCE(+) AND SLS.LOT_RRN = T.LOT_RRN AND SLS.RUNCARD_RRN = LRC.RUN_CARD_RRN(+)");
            sql.append(" AND to_number(substr(SLS.runcard_rrn_h,instr(SLS.runcard_rrn_h,',',-1)+1)) = N.INSTANCE_RRN ");
            sql.append(" AND T.EQPT_RRN = EQPT_N.INSTANCE_RRN(+) AND T.LOT_RRN=LE.LOT_RRN ");

            if (StringUtils.isNotBlank(MapUtils.getString(param, "eqptId"))) {
                sql.append(" AND SLSS.EQPT_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "eqptId"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "eqptGroupId"))) {
                sql.append(" AND (SLSS.EQPT_GROUP_ID LIKE ? OR ");
                sql.append(" SLSS.EQPT_RRN IN (SELECT R.FROM_RRN FROM RELATION R,NAMED_OBJECT N WHERE R.TO_RRN = N" +
                                   ".INSTANCE_RRN AND N.INSTANCE_ID LIKE ? GROUP BY R.FROM_RRN) OR");
                sql.append(
                        " T.EQPT_RRN IN (SELECT R.FROM_RRN FROM RELATION R,NAMED_OBJECT N WHERE R.TO_RRN = N.INSTANCE_RRN" +
                                " AND N.INSTANCE_ID LIKE ? GROUP BY R.FROM_RRN) )");
                argList.add(MapUtils.getString(param, "eqptGroupId"));
                argList.add(MapUtils.getString(param, "eqptGroupId"));
                argList.add(MapUtils.getString(param, "eqptGroupId"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "portalType"))) {
                sql.append("AND N.OBJECT_TYPE = ? ");
                argList.add(MapUtils.getString(param, "portalType"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "lotId"))) {
                sql.append("AND SLS.LOT_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "lotId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(param, "carrierId"))) {
                sql.append("AND (SLS.CARRIER_ID LIKE ? OR GETINSTANCEID(T.CARRIER_RRN)  LIKE ? )");
                argList.add(MapUtils.getString(param, "carrierId"));
                argList.add(MapUtils.getString(param, "carrierId"));
            }
            if (MapUtils.getObject(param, "lotStatusArray") != null) {
                String[] lotStatusArray = (String[]) MapUtils.getObject(param, "lotStatusArray");
                String statusCondition = "AND (";
                boolean reworkFlag = false;
                for (int i = 0; i < lotStatusArray.length; i++) {
                    if (StringUtils.equals(LotStatus.ALL, lotStatusArray[i])) {
                        continue;
                    }
                    if (!reworkFlag && StringUtils.equals(lotStatusArray[i], LotStatus.REWORK)) {
                        reworkFlag = true;
                    }

                    if (i == 0) {
                        statusCondition += "T.LOT_STATUS = ? ";
                    } else {
                        statusCondition += "OR T.LOT_STATUS = ? ";
                    }
                    argList.add(lotStatusArray[i]);

                    if (StringUtils.equals(LotStatus.ACTIVE, lotStatusArray[i])) {
                        statusCondition +=
                                "OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS =" + " ? ";
                        argList.add(LotStatus.RUNNING);
                        argList.add(LotStatus.WAITING);
                        argList.add(LotStatus.PROCESSED);
                        argList.add(LotStatus.DISPATCH);
                    } else if (StringUtils.equals(LotStatus.UNACTIVE, lotStatusArray[i])) {
                        statusCondition +=
                                "OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS = ? OR T.LOT_STATUS =" + " ? ";
                        argList.add(LotStatus.BONDED);
                        argList.add(LotStatus.FINISH);
                        argList.add(LotStatus.TERMINATED);
                        argList.add(LotStatus.SCRAPPED);
                    }
                }
                if (reworkFlag) {
                    statusCondition += "OR REWORK_CATEGORY IS NOT NULL ";
                }
                statusCondition += ") ";
                if (StringUtils.contains(statusCondition, "LOT_STATUS")) {
                    sql.append(statusCondition);
                }
            }

            sql.append(") SRC_LOT_S, ");
            sql.append("(select R.FROM_RRN,max(n.instance_id) as LOT_EQPT_GROUP_ID from relation r,NAMED_OBJECT N ");
            sql.append(" where r.link_type='ENTITY_TO_ENTITY_GROUP' AND R.TO_RRN=N.INSTANCE_RRN GROUP BY R.FROM_RRN) " +
                               "ENTITY_G ");

            sql.append("WHERE L.STEP_RRN = STATION_T.STEP_RRN(+) ");
            sql.append("AND SRC_LOT_S.MAIN_LOT_ID = L.LOTID(+) AND SRC_LOT_S.LOT_RRN = REA.LOT_RRN(+) ");
            sql.append(
                    "AND SRC_LOT_S.EQPT_GROUP_RRN = EGR.ENTITY_GROUP_RRN(+) AND SRC_LOT_S.LOT_RRN = LOT_RUNTIME.LOT_RRN" +
                            "(+) AND L.LOTCATEGORY = LOT_CATEGORY_REF.KEY_1_VALUE ");
            sql.append("AND SRC_LOT_S.LOT_STATUS = LOT_STATUS_REF.KEY_1_VALUE(+) ");
            sql.append("AND SRC_LOT_S.LOT_EQPT_RRN = ENTITY_G.FROM_RRN(+) ");

            if (StringUtils.isNotBlank(MapUtils.getString(param, "stationId"))) {
                sql.append("AND STATION_T.STATION_ID LIKE ? ");
                argList.add(MapUtils.getString(param, "stationId"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "workArea"))) {
                sql.append("AND SRC_LOT_S.WORK_AREA = ? ");
                argList.add(MapUtils.getString(param, "workArea"));
            }

            if (StringUtils.isNotBlank(MapUtils.getString(param, "stageId"))) {
                sql.append("AND L.STAGE_ID like ? ");
                argList.add(MapUtils.getString(param, "stageId"));
            }

            sql.append(
                    ") RN) RN_T  LEFT JOIN LOT_EXT LE ON LE.LOT_RRN = RN_T.LOT_RRN WHERE RN_T.RN_NUM = 1 ORDER BY LOTID ASC) ROW_T WHERE ROWNUM <= ? ) PAGE_T WHERE PAGE_T" +
                            ".ROW_NO > ? ");
            argList.add(currentTotalRow);
            argList.add(startRow);

            Map<String, Object> sqlMap = new HashMap<String, Object>();
            sqlMap.put("sql", sql.toString());
            sqlMap.put("argList", argList);
            return sqlMap;
        }

        @Override
        public String getRCRunCardIdByLotId(String lotId) {
            String sql = "SELECT A .INSTANCE_ID AS RUNCARD_ID FROM SRC_LOT_STORE SLS JOIN NAMED_OBJECT A " + " ON SLS" +
                    ".RUNCARD_RRN = A .INSTANCE_RRN WHERE SLS.LOT_ID LIKE  ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lotId}, String.class);
        }

        @Override
        public List getSumbitAndActiveRunCardCreateTime(Long facilityRrn, Long lotRrn) {
            List resutl = new ArrayList();
            String sql = "SELECT  RUN_CARD_RRN, GETINSTANCEID(RUN_CARD_RRN) RUN_CARD_ID, CREATED_TIME " + "FROM " +
                    "LOT_RUN_CARD WHERE STATUS IN('ACTIVE', 'SUBMIT') AND LOT_RRN = ? AND " + "FACILITY_RRN = ?";
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql.toString(), new Object[]{lotRrn, facilityRrn});
            while (rs.next()) {
                Map map = new HashMap();
                map.put("runCardRrn", rs.getLong("RUN_CARD_RRN"));
                map.put("runCardId", rs.getString("RUN_CARD_ID"));
                map.put("createdTime", rs.getTimestamp("CREATED_TIME"));
                resutl.add(map);
            }
            return resutl;
        }

        @Override
        public List<Map<String, Object>> queryFecthStepBySrc(long lotRrn) {
            String sql =
                    "SELECT L.LOT_ID, SLS.SHOW_LOT_ID, L.LOT_RRN, SLSS.STEP_SEQUENCE, L.QTY1, ML.STAGE_ID," +
                            " LS.FLOW_SEQ, LS.AREA_ID WORK_AREA, LS.RECIPE_ID, LS.EQUIPMENT_GROUP_RRN EQP_GROUP_RRN, " +
                            " LS.EQUIPMENT_RRN EQP_RRN, SLS.step_sequence as current_step_sequence, " +
                            " EQP_GROUP.INSTANCE_ID EQP_GROUP_ID, EQP.INSTANCE_ID EQP_ID, RETICLE_GROUP.INSTANCE_ID " +
                            "RETICLE_GROUP_ID," +
                            " LS.RETICLE_GROUP_RRN, RETICLE.INSTANCE_ID RETICLE_ID, LS.RETICLE_RRN, EDC_PLAN.INSTANCE_ID " +
                            "EDC_PLAN," +
                            " LS.POLLUTION_LEVEL POLLUTION_LEVEL, LS.OPERATION_RRN, OPERATION.INSTANCE_ID OPERATION_ID," +
                            " OPERATION.OBJECT_TYPE OPERATION_TYPE, SLS.PROCESS_ID, SLS.PROCESS_VERSION,SLSS.FLIP_TYPE" +
                            " FROM SRC_LOT_SPECIAL_STEP SLSS, SRC_LOT_STORE SLS, LOT_RUN_CARD_STEP LS, LOT L," +
                            " LOT ML, NAMED_OBJECT EQP_GROUP, NAMED_OBJECT EQP, NAMED_OBJECT RETICLE_GROUP," +
                            " NAMED_OBJECT RETICLE, NAMED_OBJECT EDC_PLAN, NAMED_OBJECT OPERATION" +
                            " WHERE SLSS.RUNCARD_RRN = SLS.RUNCARD_RRN AND SLSS.RUNCARD_RRN = LS.RUN_CARD_RRN" +
                            " AND SLSS.STEP_NO = LS.STEP_SERIAL_NO AND SLSS.LOT_SPECIAL_STEP_RRN = SLS" +
                            ".LOT_SPECIAL_STEP_RRN" + " AND SLS.LOT_RRN = L.LOT_RRN AND ML.LOT_ID = SLS.MAIN_LOT_ID" +
                            " AND EQP_GROUP.INSTANCE_RRN(+) = LS.EQUIPMENT_GROUP_RRN" +
                            " AND RETICLE_GROUP.INSTANCE_RRN(+) = LS.RETICLE_GROUP_RRN" +
                            " AND EQP.INSTANCE_RRN(+) = LS.EQUIPMENT_RRN AND RETICLE.INSTANCE_RRN(+) = LS.RETICLE_RRN" +
                            " AND EDC_PLAN.INSTANCE_RRN(+) = LS.OUT_PARAMETER_SET_RRN" +
                            " AND OPERATION.INSTANCE_RRN(+) = LS.OPERATION_RRN AND SLS.LOT_RRN = ?";
            sql += " AND SLSS.STEP_SEQUENCE >= SLS.STEP_SEQUENCE ORDER BY SLSS.STEP_SEQUENCE";
            return jdbcTemplate.query(sql, new Object[]{lotRrn}, (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                Map<String, Object> process = new HashMap<>();
                process.put("stepSeq", rowNum + 1);
                process.put("lotId", rs.getString("LOT_ID"));
                process.put("showLotId", rs.getString("SHOW_LOT_ID"));
                process.put("lotRrn", rs.getLong("LOT_RRN"));
                process.put("stepSequence", rs.getLong("STEP_SEQUENCE"));
                process.put("currentStepSequence",rs.getLong("current_step_sequence"));
                process.put("processId", rs.getString("PROCESS_ID"));
                process.put("processVersion", rs.getString("PROCESS_VERSION"));
                process.put("operationRrn", rs.getString("OPERATION_RRN"));
                process.put("operationId", rs.getString("OPERATION_ID"));
                process.put("operationType", rs.getString("OPERATION_TYPE"));
                String edcPlan = rs.getString("EDC_PLAN");
                process.put("edcFlag", StringUtils.isNotEmpty(edcPlan));
                process.put("parameterSetId", edcPlan);
                process.put("oPollutionLevel", rs.getString("POLLUTION_LEVEL"));
                process.put("workArea", rs.getString("WORK_AREA"));
                process.put("qty1", rs.getInt("QTY1"));
                String reticleGroupId = rs.getString("RETICLE_GROUP_ID");
                process.put("rectileFamilyRrn", rs.getLong("RETICLE_GROUP_RRN"));
                process.put("rectileFamilyId", reticleGroupId);
                process.put("reticleRrn", rs.getLong("RETICLE_RRN"));
                String reticleId = rs.getString("RETICLE_ID");
                process.put("reticleId", reticleId);
                process.put("reticleFlag",
                            (StringUtils.isNotEmpty(reticleId) || StringUtils.isNotEmpty(reticleGroupId)));

                process.put("entityGroupRrn", rs.getString("EQP_GROUP_RRN"));
                process.put("entityGroupId", rs.getString("EQP_GROUP_ID"));
                process.put("equipRrn", rs.getString("EQP_RRN"));
                process.put("equipId", rs.getString("EQP_ID"));
                process.put("flowSeq", rs.getString("FLOW_SEQ"));
                String recipeId = rs.getString("RECIPE_ID");
                if (StringUtils.isNotBlank(recipeId)) {
                    process.put("recipeId", recipeId);
                } else {
                    process.put("recipeId", StringUtils.EMPTY);
                }
                String stageId = rs.getString("STAGE_ID");
                if (StringUtils.isNotBlank(stageId)) {
                    process.put("stageId", stageId);
                } else {
                    process.put("stageId", StringUtils.EMPTY);
                }
                process.put("flipType", StringUtils.defaultIfBlank(rs.getString("FLIP_TYPE"), StringUtils.EMPTY));
                return process;
            });
        }

        @Override
        public Map<String, Long> getRCLotRouteRrnAndOperationRrn(String lotId) {
            String sql = "SELECT LS.ROUTE_RRN, LS.OPERATION_RRN FROM SRC_LOT_SPECIAL_STEP SLSS, SRC_LOT_STORE SLS, " +
                    " LOT_RUN_CARD_STEP LS WHERE LOT_ID = ? AND SLSS.STEP_SEQUENCE = SLS.STEP_SEQUENCE" +
                    " AND SLSS.LOT_SPECIAL_STEP_RRN = SLS.LOT_SPECIAL_STEP_RRN " +
                    " AND SLSS.STEP_NO = LS.STEP_SERIAL_NO AND SLSS.RUNCARD_RRN = LS.RUN_CARD_RRN";

            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lotId}, (rs, rowNum) -> {
                Map<String, Long> map = new HashMap();
                map.put("routeRrn", rs.getLong("ROUTE_RRN"));
                map.put("operationRrn", rs.getLong("OPERATION_RRN"));
                return map;
            });
        }

        @Override
        public List<Map<String, Object>> getRcLotInfoOfRcQtime(long lotRrn) {
            String sql = "SELECT S.LOT_RRN, S.LOT_ID, LRC.STEP_TYPE, LRC.RUN_CARD_RRN, S.SPLIT_SEQ, S.STEP_SEQUENCE," +
                    " SL.STEP_NO," +
                    " ML.PRODUCT_RRN, ML.PROCESS_RRN, ML.PROCESS_VERSION, LRC.ROUTE_RRN, LRC.OPERATION_RRN, S" +
                    ".MAIN_LOT_ID," + " LRC.START_OF_MAIN_QTIME, LRC.END_OF_MAIN_QTIME, LRC.START_OF_RC_QTIME, LRC" +
                    ".END_OF_RC_QTIME" + " FROM LOT ML, SRC_LOT_STORE S, SRC_LOT_SPECIAL_STEP SL, LOT_RUN_CARD_STEP LRC" +
                    " WHERE ML.LOT_ID = S.MAIN_LOT_ID AND S.STEP_SEQUENCE = SL.STEP_SEQUENCE" +
                    " AND S.RUNCARD_RRN = SL.RUNCARD_RRN AND SL.STEP_NO = LRC.STEP_SERIAL_NO " +
                    " AND SL.LOT_SPECIAL_STEP_RRN = S.LOT_SPECIAL_STEP_RRN" +
                    " AND SL.RUNCARD_RRN = LRC.RUN_CARD_RRN AND SL.LOT_RRN = ?";

            return jdbcTemplate.query(sql, new Object[]{lotRrn}, new RowMapper<Map<String, Object>>() {

                Map<String, Object> temp = null;

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    temp = new HashMap<String, Object>();
                    temp.put("lotRrn", rs.getLong("LOT_RRN"));
                    temp.put("lotId", rs.getString("LOT_ID"));
                    temp.put("stepType", rs.getString("STEP_TYPE"));
                    temp.put("runcardRrn", rs.getLong("RUN_CARD_RRN"));
                    temp.put("splitSeq", rs.getLong("SPLIT_SEQ"));
                    temp.put("stepSequence", rs.getLong("STEP_SEQUENCE"));
                    temp.put("stepNo", rs.getLong("STEP_NO"));
                    temp.put("productRrn", rs.getLong("PRODUCT_RRN"));
                    temp.put("processRrn", rs.getLong("PROCESS_RRN"));
                    temp.put("processVersion", rs.getInt("PROCESS_VERSION"));
                    temp.put("routeRrn", rs.getLong("ROUTE_RRN"));
                    temp.put("operationRrn", rs.getLong("OPERATION_RRN"));
                    temp.put("startOfMainQtime", rs.getString("START_OF_MAIN_QTIME"));
                    temp.put("endOfMainQtime", rs.getString("END_OF_MAIN_QTIME"));
                    temp.put("startOfRcQtime", rs.getString("START_OF_RC_QTIME"));
                    temp.put("endOfRcQtime", rs.getString("END_OF_RC_QTIME"));
                    temp.put("mainLotId", rs.getString("MAIN_LOT_ID"));
                    return temp;
                }
            });
        }

        @Override
        public Long getLotRrnByRunCardRrn(long runCardRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("LOT_RRN FROM LOT_RUN_CARD WHERE RUN_CARD_RRN = ?");

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{runCardRrn}, Long.class);
        }

        @Override
        public List<Map<String, Object>> getRunCardGridInfo(String lotId) {
            String sql = "SELECT SLS.PROCESS_ID,SLS.PROCESS_RRN,LS.ROUTE_RRN,LS.OPERATION_RRN,SLS.PROCESS_VERSION,"  +
                    " GETINSTANCEID(LS.ROUTE_RRN) ROUTE_ID,GETINSTANCEID(LS.OPERATION_RRN) OPERATION_ID, " +
                    " LS.FLOW_SEQ,SLSS.STAGE_ID,SLSS.RECIPE_ID,SLSS.EQPT_GROUP_ID,LS.STEP_TYPE, " +
                    " SLSS.PROCESS_LOCATION,LS.COMMENTS STEP_DESC,LS.POLLUTION_LEVEL,LS.FLIP_TYPE,  " +
                    " SLS.STEP_SEQUENCE CURRENT_STEP_SEQ ,LS.STEP_SERIAL_NO FROM " +
                    DataBaseNames.SRC_LOT_SPECIAL_STEP +" SLSS,"+DataBaseNames.SRC_LOT_STORE + " SLS," +
                    DataBaseNames.LOT_RUN_CARD_STEP+" LS WHERE " +
                    " SLSS.RUNCARD_RRN = SLS.RUNCARD_RRN AND SLSS.RUNCARD_RRN = LS.RUN_CARD_RRN " +
                    " AND SLSS.STEP_NO = LS.STEP_SERIAL_NO " +
                    " AND SLSS.LOT_SPECIAL_STEP_RRN = SLS.LOT_SPECIAL_STEP_RRN " +
                    " AND SLS.LOT_ID = ? ORDER BY SLSS.STEP_SEQUENCE ";

            return jdbcTemplate.query(sql, new Object[]{lotId}, new RowMapper<Map<String, Object>>() {
                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> temp = new HashMap<>();
                    temp.put("processId", rs.getString("PROCESS_ID"));
                    temp.put("processRrn", rs.getLong("PROCESS_RRN"));
                    temp.put("processVersion", rs.getInt("PROCESS_VERSION"));
                    temp.put("routeRrn", rs.getLong("ROUTE_RRN"));
                    temp.put("routeId", rs.getString("ROUTE_ID"));
                    temp.put("operationRrn", rs.getLong("OPERATION_RRN"));
                    temp.put("operationId", rs.getString("OPERATION_ID"));
                    temp.put("operationType", rs.getString("STEP_TYPE"));
                    temp.put("flowSeq", rs.getString("FLOW_SEQ"));
                    temp.put("stageId", rs.getString("STAGE_ID"));
                    temp.put("recipeId", rs.getString("RECIPE_ID"));
                    temp.put("eqptGroupId", rs.getString("EQPT_GROUP_ID"));
                    temp.put("processLocation", rs.getString("PROCESS_LOCATION"));
                    temp.put("operationDesc", rs.getString("STEP_DESC"));
                    temp.put("contamination", rs.getString("POLLUTION_LEVEL"));
                    temp.put("filpType", rs.getString("FLIP_TYPE"));
                    temp.put("rcCurrentStepSeq", rs.getString("CURRENT_STEP_SEQ"));
                    temp.put("rcStepSerialNo", rs.getString("STEP_SERIAL_NO"));
                    return temp;
                }
            });
        }
    }