# CarrierDAOImpl.java

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

    import com.fa.sesa.threadlocal.LocalContext;
    import com.mycim.framework.jdbc.BatchPreparedStatementSetter;
    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.CollectionUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.carrier.dao.CarrierDAO;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.bas.TransactionLog;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.TransactionNames;
    import com.mycim.valueobject.ems.Carrier;
    import com.mycim.valueobject.wip.UnitStatus;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

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

    @Repository
    public class CarrierDAOImpl implements CarrierDAO {

        private final static Long DEFAULT_SLOT_COUNT = 25L;

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public Carrier getCarrier(Carrier carrier) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" A.SLOT_COUNT, A.AVAILABLE_SLOT_COUNT, A.CARRIER_CODE, ");
            sql.append(" A.MCS_MOVE_STATUS, A.MCS_LAST_MOVE_TIME, A.MCS_LAST_OPERATOR_RRN, ");
            sql.append(" A.EQPT_RRN, A.JOB_RRN, A.RUN_RRN, ");
            sql.append(" A.CARRIER_MAP_RRN, A.LOAD_POSITION,A.POLLUTION_LEVEL,C.CURRENT_STATUS,B.FLAG_TYPE, B.DMM_TYPE");
            sql.append(" FROM ");
            sql.append(DataBaseNames.CARRIER);
            sql.append(" A LEFT JOIN " + DataBaseNames.NAMEDOBJECT_EXT + " B ON A.CARRIER_RRN = B.INSTANCE_RRN LEFT JOIN " +
                               DataBaseNames.ENTITY_STATUS + " C ");
            sql.append(" ON A.CARRIER_RRN = C.ENTITY_RRN WHERE CARRIER_RRN = ? ");

            Object[] args = new Object[]{new Long(carrier.getInstanceRrn())};

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args, new RowMapper<Carrier>() {

                @Override
                public Carrier mapRow(ResultSet rs, int rowNum) throws SQLException {
                    // construct the value object
                    carrier.setSlotCount(rs.getLong("SLOT_COUNT"));
                    carrier.setAvailableSlotCount(rs.getLong("AVAILABLE_SLOT_COUNT"));
                    carrier.setCarrierCode(rs.getString("CARRIER_CODE"));
                    carrier.setMcsMoveStatus(rs.getString("MCS_MOVE_STATUS"));

                    Timestamp timestampTemp = rs.getDate("MCS_LAST_MOVE_TIME") != null ? new Timestamp(
                            rs.getDate("MCS_LAST_MOVE_TIME").getTime()) : null;

                    carrier.setMcsLastMoveTime(timestampTemp);
                    carrier.setMcsLastOperatorRrn(new Long(rs.getLong("MCS_LAST_OPERATOR_RRN")));
                    carrier.setEqptRrn(new Long(rs.getLong("EQPT_RRN")));
                    carrier.setJobRrn(new Long(rs.getLong("JOB_RRN")));
                    carrier.setRunRrn(new Long(rs.getLong("RUN_RRN")));
                    carrier.setCarrierMapRrn(new Long(rs.getLong("CARRIER_MAP_RRN")));
                    carrier.setLoadPosition(new Short(rs.getShort("LOAD_POSITION")));
                    carrier.setPollutionLevel(rs.getString("POLLUTION_LEVEL"));
                    carrier.setCarrierStatus(rs.getString("CURRENT_STATUS"));
                    carrier.setFlagType(rs.getString("FLAG_TYPE"));
                    carrier.setDmmType(rs.getString("DMM_TYPE"));
                    return carrier;
                }
            });
        }

        @Override
        public Carrier getCarrier(long carrierRrn) {
            Carrier carrier = null;

            // get the value object by object rrn
            String sql = "select n.instance_rrn, noe.flag_type, noe.dmm_type, n.named_space, n.object_type, n" +
                    ".object, n.instance_id, n.instance_desc, n.instance_status, " + "es.current_status, p" +
                    ".mcs_move_status, p.mcs_last_move_time, p" + ".mcs_last_operator_rrn, p" + ".eqpt_rrn, p.job_rrn, p" +
                    ".run_rrn,p.slot_count,p.available_slot_count, " + "p.carrier_map_rrn, p.load_position,e" +
                    ".location_rrn,getinstanceid(e.location_rrn) as " + "location_id, " + "p.pollution_level, p" +
                    ".carrier_code, getinstanceid(e.allowable_events_rrn) as " + "allowable_events_id, " + "n" +
                    ".object_subtype as carrier_type, getinstanceid(e.maintenance_engineer_rrn) as " + "engineer_group_id" +
                    " " + "from carrier p, entity e, named_object n left join named_object_ext noe on n" + ".instance_rrn" +
                    " = noe.instance_rrn left join entity_status es " + " on n.instance_rrn = es.entity_rrn where n" +
                    ".instance_rrn = p.carrier_rrn " + "and n.instance_rrn = e.entity_rrn and n.instance_rrn = ?";

            Object[] params = new Object[]{carrierRrn};

            carrier = jdbcTemplate.queryForObject(sql, params, new RowMapper<Carrier>() {

                @Override
                public Carrier mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Carrier carrier = new Carrier();
                    // construct the value object
                    carrier.setInstanceRrn(rs.getLong("instance_rrn"));
                    carrier.setInstanceId(rs.getString("instance_id"));
                    carrier.setInstanceDesc(rs.getString("instance_desc"));
                    carrier.setCarrierStatus(rs.getString("current_status"));
                    carrier.setCarrierPollutionLevel(rs.getString("pollution_level"));
                    carrier.setCarrierCode(rs.getString("carrier_code"));
                    carrier.setAllowableEventsId(rs.getString("allowable_events_id"));
                    carrier.setObjectSubtype(rs.getString("carrier_type"));
                    carrier.setMaintenanceEngineerId(rs.getString("engineer_group_id"));
                    carrier.setNamedSpace(rs.getString("named_space"));
                    carrier.setObject(rs.getString("object"));
                    carrier.setObjectType(rs.getString("object_type"));
                    carrier.setMcsMoveStatus(rs.getString("mcs_move_status"));
                    carrier.setMcsLastMoveTime(rs.getTimestamp("mcs_last_move_time"));
                    carrier.setMcsLastOperatorRrn(new Long(rs.getLong("mcs_last_operator_rrn")));
                    carrier.setEqptRrn(new Long(rs.getLong("eqpt_rrn")));
                    carrier.setJobRrn(new Long(rs.getLong("job_rrn")));
                    carrier.setRunRrn(new Long(rs.getLong("run_rrn")));
                    carrier.setCarrierMapRrn(new Long(rs.getLong("carrier_map_rrn")));
                    carrier.setLoadPosition(new Short(rs.getShort("load_position")));
                    carrier.setLocationRrn(rs.getLong("location_rrn"));
                    carrier.setLocationId(rs.getString("location_id"));
                    carrier.setSlotCount(rs.getLong("slot_count"));
                    carrier.setFlagType(rs.getString("flag_type"));
                    carrier.setDmmType(rs.getString("dmm_type"));
                    carrier.setAvailableSlotCount(rs.getLong("available_slot_count"));

                    return carrier;
                }
            });

            return carrier;
        }

        @Override
        public Map<String, Object> qryCarrierInfoDetailById(String carrierId, String namedSpace) {
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("carrierId", carrierId);
            dataMap.put("namedSpace", namedSpace);
            String sql = this.getCarrierListSql(dataMap);

            dataMap = jdbcTemplate.queryForObject(sql, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> dataMap = new HashMap();
                    dataMap.put("carrierId", rs.getString("instance_id"));
                    dataMap.put("lotId", rs.getString("lot_id"));
                    dataMap.put("carrierRrn", rs.getString("instance_rrn"));
                    dataMap.put("carrierDesc", rs.getString("instance_desc"));
                    dataMap.put("carrierInstanceStatus", rs.getString("instance_status"));
                    dataMap.put("availableSlotCount", rs.getString("available_slot_count"));
                    dataMap.put("slotCount", rs.getString("slot_count"));
                    dataMap.put("carrierStatus", rs.getString("current_status"));
                    dataMap.put("carrierPollution", rs.getString("pollution_level"));
                    dataMap.put("carrierColor", rs.getString("color_data"));
                    dataMap.put("carrierAllowable", rs.getString("allowable_events_id"));
                    dataMap.put("carrierType", rs.getString("carrier_type"));
                    dataMap.put("carrierEngineer", rs.getString("engineer_group_id"));
                    String createTime = rs.getString("TRANS_START_TIMESTAMP");
                    if (StringUtils.isNotBlank(createTime)) {
                        createTime = (createTime.split("\\."))[0];
                    }
                    dataMap.put("carrierCreateTime", createTime);
                    dataMap.put("carrierCreateUser", rs.getString("create_user"));
                    dataMap.put("lotQty", rs.getInt("qty1"));
                    dataMap.put("carrierCategory", rs.getString("category_data"));
                    dataMap.put("carrierComment", rs.getString("carrier_comment"));
                    return dataMap;
                }
            });
            return dataMap;
        }

        @Override
        public int getCarrierTotalCount(Map<String, Object> argMap) {
            int count = 0;
            String countSql = "select count(*) from (" + getCarrierListSql(argMap) + ") countt";
            count = this.jdbcTemplate.queryForObject(countSql, new Object[]{}, int.class);
            return count;
        }

        //    @Override
        //    public List<Map> getCarrierListByPage(Map<String, Object> argMap, int startRow, int pageSize) {
        //        String rownumSql = "select * from (select rownum as rowno, rowt.* from (" + getCarrierListSql
        //        (argMap)
        //                           + ")rowt )rowtt where rowno <= ? " ;
        //        String pageSql = "select * from (" + rownumSql + ") paget where rowno > ? ";
        //
        //        return jdbcTemplate.query(pageSql, new Object[]{pageSize, startRow}, (RowMapper<Map>) (rs,
        //        rowNum) -> {
        //            Map<String, Object> dataMap = new HashMap<String, Object>();
        //            dataMap.put("carrierId", rs.getString("instance_id"));
        //            dataMap.put("lotId", rs.getString("lot_id"));
        //            dataMap.put("carrierRrn", rs.getString("instance_rrn"));
        //            dataMap.put("carrierDesc", rs.getString("instance_desc"));
        //            dataMap.put("carrierInstanceStatus", rs.getString("instance_status"));
        //            dataMap.put("availableSlotCount", rs.getString("available_slot_count"));
        //            dataMap.put("slotCount", rs.getString("slot_count"));
        //            dataMap.put("carrierStatus", rs.getString("current_status"));
        //            dataMap.put("carrierPollution", rs.getString("pollution_level"));
        //            dataMap.put("carrierColor", rs.getString("color_data"));
        //            dataMap.put("carrierColorCode", rs.getString("color_code"));
        //            dataMap.put("carrierAllowable", rs.getString("allowable_events_id"));
        //            dataMap.put("carrierType", rs.getString("carrier_type"));
        //            dataMap.put("carrierEngineer", rs.getString("engineer_group_id"));
        //            String createTime = rs.getString("TRANS_START_TIMESTAMP");
        //            if (StringUtils.isNotBlank(createTime)) {
        //                createTime = (createTime.split("\\."))[0];
        //            }
        //            dataMap.put("carrierCreateTime", createTime);
        //            dataMap.put("carrierCreateUser", rs.getString("create_user"));
        //            dataMap.put("lotQty", rs.getInt("qty1"));
        //            dataMap.put("carrierCategory", rs.getString("category_data"));
        //            dataMap.put("carrierCategoryCode", rs.getString("category_code"));
        //            dataMap.put("carrierCleanOverDate", rs.getString("clean_over_date"));
        //            dataMap.put("carrierRemainCleanDay", rs.getString("remain_clean_day"));
        //            return dataMap;
        //        });
        //    }

        @Override
        public Page getCarrierListByPage(Page page, Map<String, Object> argMap) {
            String sql = getCarrierListSql(argMap);
            return jdbcTemplate.queryForPage(page, sql, new Object[]{}, (RowMapper<Map>) (rs, rowNum) -> {
                Map<String, Object> dataMap = new HashMap<>();
                dataMap.put("carrierId", rs.getString("instance_id"));
                dataMap.put("lotId", rs.getString("lot_id"));
                dataMap.put("carrierRrn", rs.getString("instance_rrn"));
                dataMap.put("carrierDesc", rs.getString("instance_desc"));
                dataMap.put("carrierInstanceStatus", rs.getString("instance_status"));
                dataMap.put("availableSlotCount", rs.getString("available_slot_count"));
                dataMap.put("slotCount", rs.getString("slot_count"));
                dataMap.put("carrierStatus", rs.getString("current_status"));
                dataMap.put("carrierPollution", rs.getString("pollution_level"));
                dataMap.put("carrierColor", rs.getString("color_data"));
                dataMap.put("carrierColorCode", rs.getString("color_code"));
                dataMap.put("carrierAllowable", rs.getString("allowable_events_id"));
                dataMap.put("carrierType", rs.getString("carrier_type"));
                dataMap.put("carrierEngineer", rs.getString("engineer_group_id"));
                String createTime = rs.getString("TRANS_START_TIMESTAMP");
                if (StringUtils.isNotBlank(createTime)) {
                    createTime = (createTime.split("\\."))[0];
                }
                dataMap.put("carrierCreateTime", createTime);
                dataMap.put("carrierCreateUser", rs.getString("create_user"));
                dataMap.put("lotQty", rs.getInt("qty1"));
                dataMap.put("carrierCategory", rs.getString("category_data"));
                dataMap.put("carrierCategoryCode", rs.getString("category_code"));
                dataMap.put("carrierCleanOverDate", rs.getString("clean_over_date"));
                dataMap.put("carrierRemainCleanDay", rs.getString("remain_clean_day"));
                return dataMap;
            });
        }

        @Override
        public void insertCarrierH(Long carrierRrn, String comments, String targetCarrier, Long transRrn) {
            Map basicInfo = getBasicHistInfo(carrierRrn);

            StringBuilder sql = new StringBuilder("INSERT INTO CARRIER_H ");

            sql.append(" (CARRIER_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, CARRIER_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(" EQPT_RRN, JOB_RRN, RUN_RRN, CARRIER_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,status,");
            sql.append(" FLAG_TYPE,OBJECT_SUBTYPE,DESCription,ALLOWABLE_EVENTS_RRN,MAINTENANCE_ENGINEER_RRN,")
               .append("COMMENTS,");
            sql.append(" LOT_ID,TARGET_CARRIER,TRANS_RRN)");
            sql.append(" (SELECT CARRIER_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, CARRIER_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(" EQPT_RRN, JOB_RRN, RUN_RRN, CARRIER_MAP_RRN,LOAD_POSITION,POLLUTION_LEVEL,STATUS,?,?,?,")
               .append("?,?,?,?,?,? ");
            sql.append(" FROM CARRIER WHERE CARRIER_RRN = ?) ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{MapUtils.getString(basicInfo, "category"), MapUtils.getString(basicInfo,
                                                                                                           "objectType"),
                                        MapUtils.getString(
                                        basicInfo, "instanceDesc"), MapUtils.getLong(basicInfo,
                                                                                     "allowableRrn"), MapUtils.getLong(
                                        basicInfo, "engineerRrn"), comments, MapUtils.getString(basicInfo,
                                                                                                "lotId"), targetCarrier,
                                        transRrn, carrierRrn});
        }

        @Override
        public void insertCarrierH(Long carrierRrn, String comments, String targetCarrier, TransactionLog transLog) {
            StringBuilder sql = new StringBuilder(DataBaseNames.INSERT_INTO).append(DataBaseNames.CARRIER_HISTORY);
            sql.append(" (CARRIER_RRN,SLOT_COUNT,AVAILABLE_SLOT_COUNT,CARRIER_CODE,MCS_MOVE_STATUS,");
            sql.append("MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN,EQPT_RRN,JOB_RRN,RUN_RRN,CARRIER_MAP_RRN,LOAD_POSITION,");
            sql.append("POLLUTION_LEVEL,STATUS,FLAG_TYPE,OBJECT_SUBTYPE,DESCRIPTION,ALLOWABLE_EVENTS_RRN,");
            sql.append("MAINTENANCE_ENGINEER_RRN,COMMENTS,LOT_ID,TARGET_CARRIER,TRANS_RRN,TRANS_ID,SYS_RRN,FACILITY_RRN,");
            sql.append("CARRIER_ID,OBJ_SPACE,OBJ_TYPE,OBJ_SUBTYPE,OBJ_STATUS,DMM_TYPE,AVAILABILITY,TRANS_USER_RRN,");
            sql.append("TRANS_TIME) (SELECT ");
            sql.append("C.CARRIER_RRN,C.SLOT_COUNT,C.AVAILABLE_SLOT_COUNT,C.CARRIER_CODE,C.MCS_MOVE_STATUS,");
            sql.append("C.MCS_LAST_MOVE_TIME,C.MCS_LAST_OPERATOR_RRN,C.EQPT_RRN,C.JOB_RRN,C.RUN_RRN,C.CARRIER_MAP_RRN,");
            sql.append("C.LOAD_POSITION,C.POLLUTION_LEVEL,C.STATUS,C.FLAG_TYPE,C.OBJ_SUBTYPE,C.DESCRIPTION,");
            sql.append("C.ALLOWABLE_EVENTS_RRN,C.MAINTENANCE_ENGINEER_RRN,?,L.LOT_ID,?,?,?,C.SYS_RRN,C.FACILITY_RRN,");
            sql.append("C.CARRIER_ID,C.OBJ_SPACE,C.OBJ_TYPE,C.OBJ_SUBTYPE,C.OBJ_STATUS,C.DMM_TYPE,C.AVAILABILITY,?,? ");
            sql.append("FROM CARRIER C LEFT JOIN LOT L ON C.CARRIER_RRN= L.CARRIER_RRN WHERE C.CARRIER_RRN = ?) ");
            jdbcTemplate.update(sql.toString(), comments, targetCarrier, transLog.getTransRrn(), transLog.getTransId(),
                                LocalContext.getUserRrn(), transLog.getTransStartTimestamp(), carrierRrn);
        }

        @Override
        public Map getBasicHistInfo(Long instanceRrn) {
            Map basicInfo = new HashMap();
            Object[] args = new Object[]{instanceRrn};

            String namedObjSql =
                    "SELECT object_type,object_subtype,instance_desc FROM named_object where " + "instance_rrn=?";
            Map<String, Object> namedObj = jdbcTemplate.queryForObject(namedObjSql, args, Map.class);
            String objectType = MapUtils.getString(namedObj, "OBJECT_TYPE");
            String objectSubType = MapUtils.getString(namedObj, "OBJECT_SUBTYPE");
            String desc = MapUtils.getString(namedObj, "INSTANCE_DESC");
            basicInfo.put("objectType", objectSubType);
            basicInfo.put("instanceDesc", desc);

            long carrierRrn = 0;
            String sql = StringUtils.EMPTY;
            if (StringUtils.equals(objectType, ObjectList.CARRIER_KEY)) {
                carrierRrn = instanceRrn;
            } else if (StringUtils.equals(objectType, ObjectList.POD_KEY)) {
                sql = "SELECT carrier_rrn FROM pcd_assembly WHERE pod_rrn=?";
                carrierRrn = jdbcTemplate.queryForObjectWithNull(sql, args, long.class);
            } else {
                sql = "SELECT carrier_rrn FROM pcd_assembly WHERE door_rrn=?";
                carrierRrn = jdbcTemplate.queryForObjectWithNull(sql, args, long.class);
            }
            String lotSql = "SELECT lot_id FROM lot where carrier_rrn=?";
            String lotId = jdbcTemplate.queryForObjectWithNull(lotSql, new Object[]{carrierRrn}, String.class);
            basicInfo.put("lotId", lotId);

            String namedObjExtSql = "SELECT FLAG_TYPE FROM named_object_ext where instance_rrn=?";
            String category = jdbcTemplate.queryForObjectWithNull(namedObjExtSql, args, String.class);
            basicInfo.put("category", category);

            String entitySql = "SELECT ALLOWABLE_EVENTS_RRN,MAINTENANCE_ENGINEER_RRN FROM entity where " + "entity_rrn=?";
            Map<String, Object> entity = jdbcTemplate.queryForObject(entitySql, args, Map.class);
            long allowableRrn = MapUtils.getLong(entity, "ALLOWABLE_EVENTS_RRN", 0L);
            long engineerRrn = MapUtils.getLong(entity, "MAINTENANCE_ENGINEER_RRN", 0L);
            basicInfo.put("allowableRrn", allowableRrn);
            basicInfo.put("engineerRrn", engineerRrn);
            return basicInfo;
        }

        @Override
        public void insertCarrier(Carrier c) {
            Object[] obj = {c.getInstanceRrn(), (c.getSlotCount() ==
                    null) ? DEFAULT_SLOT_COUNT : c.getSlotCount(), c.getAvailableSlotCount(), c.getCarrierCode(),
                            c.getMcsMoveStatus(), c.getMcsLastMoveTime(), c.getMcsLastOperatorRrn(), c.getEqptRrn(),
                            c.getJobRrn(), c.getRunRrn(), c.getCarrierMapRrn(), c.getLoadPosition(),
                            c.getPollutionLevel(), c.getCarrierStatus(), c.getInstanceRrn(), c.getInstanceId(),
                            LocalContext.getFacilityRrn(), c.getInstanceDesc(), c.getNamedSpace(), c.getObjectType(),
                            c.getObjectSubtype(), c.getInstanceStatus(), c.getCurrentVersion(), c.getActiveVersion(),
                            c.getFlagType(), c.getDmmType(), c.getAllowableEventsRrn(), c.getMaintenanceEngineerRrn(),
                            c.getAvailability(), c.getCreatedTimestamp(), c.getCreatedTimestamp(),
                            LocalContext.getUserRrn(), LocalContext.getUserRrn(), c.getLockVersion()};

            String sql = "INSERT INTO " + DataBaseNames.CARRIER + " (CARRIER_RRN,SLOT_COUNT,AVAILABLE_SLOT_COUNT," +
                    "CARRIER_CODE,MCS_MOVE_STATUS,MCS_LAST_MOVE_TIME,MCS_LAST_OPERATOR_RRN,EQPT_RRN," + "JOB_RRN,RUN_RRN," +
                    "CARRIER_MAP_RRN,LOAD_POSITION,POLLUTION_LEVEL,STATUS " + ",SYS_RRN,CARRIER_ID,FACILITY_RRN," +
                    "DESCRIPTION,OBJ_SPACE,OBJ_TYPE,OBJ_SUBTYPE," + "OBJ_STATUS,OBJ_CURRENT_VERSION," + " " +
                    "OBJ_ACTIVE_VERSION,FLAG_TYPE,DMM_TYPE,ALLOWABLE_EVENTS_RRN," + "MAINTENANCE_ENGINEER_RRN," +
                    "AVAILABILITY,CREATE_TIME," + " UPDATE_TIME,CREATE_USER_RRN,UPDATE_USER_RRN,LOCK_VERSION " + ") " +
                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(sql, obj);
        }

        @Override
        public void updateCarrier(Carrier carrier, Long transRrn) {
            String sql = "UPDATE NAMED_OBJECT SET INSTANCE_DESC = ?,OBJECT_SUBTYPE = ?,LAST_UPDATE_TIMESTAMP = " +
                    "sysdate WHERE INSTANCE_RRN = ?";
            jdbcTemplate.update(sql, carrier.getInstanceDesc(), carrier.getObjectSubtype(), carrier.getInstanceRrn());
            String sql3 = "UPDATE ENTITY SET ALLOWABLE_EVENTS_RRN=?,MAINTENANCE_ENGINEER_RRN=?,LOCATION_RRN=? " + "WHERE " +
                    "ENTITY_RRN =?";
            jdbcTemplate.update(sql3, carrier.getAllowableEventsRrn(), carrier.getMaintenanceEngineerRrn(),
                                carrier.getLocationRrn(), carrier.getInstanceRrn());
            //Synchronously add Carrier table newly added fields
            String sql2 = "UPDATE CARRIER SET CARRIER_CODE=?,POLLUTION_LEVEL=?,SLOT_COUNT=?, AVAILABLE_SLOT_COUNT=? " +
                    " ,ALLOWABLE_EVENTS_RRN=?,MAINTENANCE_ENGINEER_RRN=?,DESCRIPTION=?,OBJ_SUBTYPE=?,FLAG_TYPE=?" + " ," +
                    "UPDATE_USER_RRN = ?,UPDATE_TIME=? " + "WHERE CARRIER_RRN = ?";
            jdbcTemplate.update(sql2, carrier.getCarrierCode(), carrier.getPollutionLevel(), carrier.getSlotCount(),
                                carrier.getAvailableSlotCount(), carrier.getAllowableEventsRrn(),
                                carrier.getMaintenanceEngineerRrn(), carrier.getInstanceDesc(), carrier.getObjectSubtype(),
                                carrier.getFlagType(), carrier.getLastUpdateUserRrn(), carrier.getLastUpdateTimestamp(),
                                carrier.getInstanceRrn());
            TransactionLog tr = new TransactionLog(transRrn, LocalContext.getUserRrn());
            tr.setTransId(carrier.getTransId());
            insertCarrierH(carrier.getInstanceRrn(), StringUtils.EMPTY, carrier.getInstanceId(), tr);
        }

        @Override
        public void deleteCarrier(Carrier carrier) {
            String sql = "DELETE FROM " + DataBaseNames.CARRIER + " WHERE CARRIER_RRN = " + carrier.getInstanceRrn();
            jdbcTemplate.update(sql);
        }

        @Override
        public void updateCarrierAvailableSlotCount(Long carrierRrn) {
            String sql = "update carrier set available_slot_count=slot_count where carrier_rrn = ?";
            jdbcTemplate.update(sql, carrierRrn);
        }

        @Override
        public void updateCarrierForStatus(Long carrierRrn, String status) {
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(DataBaseNames.CARRIER);
            sql.append(" SET STATUS=? WHERE CARRIER_RRN = ? ");
            jdbcTemplate.update(sql.toString(), status, carrierRrn);
        }

        @Override
        public Page queryCarrierHistory(Page page, Map<String, Object> query) {
            String pcdId = MapUtils.getString(query, "pcdId");
            long pcdRrn = MapUtils.getLongValue(query, "pcdRrn");
            String pcdType = MapUtils.getString(query, "pcdType");
            String dueDateStart = MapUtils.getString(query, "dueDateS");
            String dueDateEnd = MapUtils.getString(query, "dueDateE");

            StringBuilder sql = buildPcdHistSqlAndLot(pcdType);

            List<Object> args = new ArrayList<Object>();
            args.add(pcdId);
            args.add(pcdId);
            args.add(pcdRrn);
            args.add(pcdRrn);
            args.add(pcdId);

            sql.append("where 1=1 ");
            if (StringUtils.isNotEmptyTrim(dueDateStart)) {
                sql.append(" AND TEMP.TRANS_START_TIMESTAMP >= to_timestamp(?, '" + DateUtils.DATE_FORMAT24 + "')");
                args.add(dueDateStart);
            }
            if (StringUtils.isNotEmptyTrim(dueDateEnd)) {
                sql.append(" AND TEMP.TRANS_START_TIMESTAMP <= to_timestamp(?, '" + DateUtils.DATE_FORMAT24 + "')");
                args.add(dueDateEnd);
            }

            sql.append(" ORDER BY TEMP.TRANS_START_TIMESTAMP DESC, TEMP.STATUS DESC  ");
            return jdbcTemplate.queryForPage(page, sql.toString(), args.toArray(), new RowMapper<Map<String, String>>() {

                @Override
                public Map<String, String> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, String> map = new HashMap<String, String>();
                    map.put("carrierId", rs.getString("INSTANCE_ID"));
                    map.put("status", rs.getString("STATUS"));

                    String sourceType = rs.getString("SOURCE_TYPE");
                    if (StringUtils.isNotBlank(sourceType) && StringUtils.equals(sourceType, "LOT")) {
                        map.put("transId", TransactionNames.CHANGE_LOCATION);
                    } else {
                        map.put("transId", rs.getString("TRANS_ID"));
                    }

                    map.put("pollutionLevel", rs.getString("POLLUTION_LEVEL"));

                    String transByName = rs.getString("USER_NAME");
                    if (StringUtils.isNotBlank(transByName)) {
                        map.put("performer", rs.getString("TRANS_PERFORMED_BY") + " " + transByName);
                    } else {
                        map.put("performer", rs.getString("TRANS_PERFORMED_BY"));
                    }
                    map.put("lotId", rs.getString("LOT_ID"));
                    map.put("transDate", rs.getString("TRANS_START_TIMESTAMP"));
                    map.put("reasonCode", rs.getString("REASON_CODE"));
                    map.put("reason", rs.getString("REASON"));
                    map.put("targetCarrier", rs.getString("TARGET_PCD"));
                    map.put("slotCount", rs.getString("SLOT_COUNT"));
                    map.put("availableCount", rs.getString("AVAILABLE_SLOT_COUNT"));
                    map.put("pcdSubType", rs.getString("OBJECT_SUBTYPE"));
                    map.put("pcdCategory", rs.getString("category_data"));
                    map.put("comments", rs.getString("comments"));
                    map.put("pcdType", pcdType);
                    map.put("cleanCycle", rs.getString("CLEAN_CYCLE"));
                    map.put("pLocation", rs.getString("PREV_LOCATION"));
                    map.put("location", rs.getString("LOCATION"));
                    map.put("sourceType", sourceType);
                    int seq = rowNum + 1;
                    map.put("seq", Integer.toString(seq));
                    map.put("processRrn", rs.getString("PROCESS_RRN"));
                    return map;
                }
            });
        }

        @Override
        public List<String> getInUseCarrierLotIdList(Long carrierRrn) {
            String sql = "SELECT LOT_ID FROM LOT WHERE CARRIER_RRN = ? ";
            return jdbcTemplate.query(sql, new Object[]{carrierRrn}, String.class);
        }

        @Override
        public Long getNumberOfAvailableInCarrier(Long carrierRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ");
            sql.append(DataBaseNames.CARRIER_MAPPING);
            sql.append(" WHERE UNIT_RRN IS NULL ");
            sql.append(" AND AVAILABLE_FLAG = '1' ");
            sql.append(" AND CARRIER_MAP_RRN IN ").append(" (SELECT CARRIER_MAP_RRN FROM CARRIER WHERE CARRIER_RRN = ?) ");

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

        @Override
        public String getCarrierTypeIsPcdOrOpen(String carrierType) {
            String sql = "SELECT  CASE WHEN r.DATA_2_VALUE='1' THEN  '1'  ELSE '0' END  AS is_pcd   FROM " +
                    "Reference_File_Detail r, named_object n  where n.instance_id = '$CARRIER_TYPE' and " + "n" +
                    ".instance_rrn = r.reference_file_rrn  and n.object = 'REFERENCEFILE' and r" + ".data_1_value = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{carrierType}, String.class);
        }

        @Override
        public void updateCarrierForInit(Long carrierRrn, String carrierStatus) {
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(DataBaseNames.CARRIER);
            sql.append(" SET AVAILABLE_SLOT_COUNT=SLOT_COUNT, ");
            sql.append(" EQPT_RRN = null, JOB_RRN = null, RUN_RRN = null, ");
            sql.append(" CARRIER_MAP_RRN = null, LOAD_POSITION = '0', STATUS='" + carrierStatus + "'");
            sql.append(" WHERE CARRIER_RRN = ? ");
            jdbcTemplate.update(sql.toString(), carrierRrn);
        }

        @Override
        public List<Long> getInUseCarrierLotRrnList(Long carrierRrn) {
            String sql = "SELECT LOT_RRN FROM LOT WHERE CARRIER_RRN = ? ";
            return jdbcTemplate.query(sql, new Object[]{carrierRrn}, Long.class);
        }

        @Override
        public void updateLotForCarrierRrn(List<Long> inUseLotRrns, Long carrierRrn) {
            String sql = "UPDATE  " + DataBaseNames.LOT + " SET CARRIER_RRN = " + carrierRrn + " WHERE LOT_RRN = ?";
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

                Iterator<Long> it = inUseLotRrns.iterator();

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setLong(1, it.next());
                }

                @Override
                public int getBatchSize() {
                    return inUseLotRrns.size();
                }
            });
        }

        @Override
        public void updateLotForCarrierRrn(long lotRrn, long carrierRrn) {
            String sql = "UPDATE  " + DataBaseNames.LOT + " SET CARRIER_RRN = ? WHERE LOT_RRN = ?";
            jdbcTemplate.update(sql, carrierRrn, lotRrn);
        }

        @Override
        public void updateSrcLotForCarrierRrn(long srcLotRrn, long carrierRrn, String carrierId) {
            String sql = "UPDATE  " + DataBaseNames.SRC_LOT_STORE + " SET CARRIER_RRN = ?, CARRIER_ID = ? WHERE LOT_RRN = ?";
            jdbcTemplate.update(sql, carrierRrn, carrierId, srcLotRrn);
        }

        @Override
        public void updateUnitForCarrierRrn(Long oldCarrierRrn, Long newcarrierRRn) {
            String sql = "UPDATE " + DataBaseNames.UNIT + " SET CARRIER_RRN =? WHERE CARRIER_RRN = ?";
            jdbcTemplate.update(sql, newcarrierRRn, oldCarrierRrn);
        }

        @Override
        public void updateUnitListForCarrierRrn(List<Map> carrierMappings, Long oldCarrierRrn, Long newCarrierRrn) {
            String sql = " UPDATE " + DataBaseNames.UNIT + " SET CARRIER_RRN = ?,POSITION_IN_CARRIER=? WHERE UNIT_RRN = ? AND CARRIER_RRN = ?";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                Iterator it = carrierMappings.iterator();
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    HashMap map = (HashMap) it.next();
                    String unitRrn = MapUtils.getString(map, "unitRrn");
                    ps.setLong(1, newCarrierRrn);
                    Long positionInCarrier = MapUtils.getLong(map, "position");
                    ps.setLong(2, positionInCarrier);
                    if (StringUtils.isEmpty(unitRrn)) {
                        ps.setNull(3, java.sql.Types.BIGINT);
                    } else {
                        ps.setLong(3, Long.parseLong(unitRrn));
                    }
                    ps.setLong(4, oldCarrierRrn);
                }

                @Override
                public int getBatchSize() {
                    return carrierMappings.size();
                }
            });
        }

        @Override
        public void updateUnitForCarrierMapping(List<Map> carrierMappings) {
            String sql = " UPDATE " + DataBaseNames.UNIT + " SET POSITION_IN_CARRIER = ? " + " WHERE UNIT_RRN = ?";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

                Iterator it = carrierMappings.iterator();

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    HashMap map = (HashMap) it.next();
                    Long postionInCarrier = new Long(MapUtils.getLong(map, "position"));
                    String unitRrn = MapUtils.getString(map, "unitRrn");
                    ps.setLong(1, postionInCarrier.longValue());
                    if (StringUtils.isEmpty(unitRrn)) {
                        ps.setNull(2, java.sql.Types.BIGINT);
                    } else {
                        ps.setLong(2, Long.parseLong(unitRrn));
                    }

                }

                @Override
                public int getBatchSize() {
                    return carrierMappings.size();
                }
            });
        }

        @Override
        public void insertBatchCarrierMapping(Long carrierMapRrn, Long carrierRrn, List<Map> carrierMappings) {
            StringBuilder sql = new StringBuilder("INSERT INTO CARRIER_MAPPING ");
            sql.append(" (CARRIER_MAP_RRN, CARRIER_RRN, POSITION_IN_CARRIER, AVAILABLE_FLAG, UNIT_RRN) ");
            sql.append(" VALUES (?, ?, ?, ?, ?) ");

            jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

                Iterator it = carrierMappings.iterator();

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    HashMap map = (HashMap) it.next();
                    Long postionInCarrier = MapUtils.getLong(map, "position");
                    String available = MapUtils.getString(map, "available");
                    String unitRrn = MapUtils.getString(map, "unitRrn");

                    ps.setLong(1, carrierMapRrn);
                    ps.setLong(2, carrierRrn);
                    ps.setLong(3, postionInCarrier.longValue());
                    ps.setString(4, available);

                    if (StringUtils.isEmpty(unitRrn)) {
                        ps.setNull(5, java.sql.Types.BIGINT);
                    } else {
                        long temp = Long.parseLong(unitRrn);
                        ps.setLong(5, temp);

                    }
                }

                @Override
                public int getBatchSize() {
                    return carrierMappings.size();
                }
            });

            updateUnitForCarrierMapping(carrierMappings);
        }

        @Override
        public void updateCarrierMapRrnAndPollutionLevel(Long carrierRrn, Long carrierMapRrn, String pollutionLevel,
                                                         String status) {
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(DataBaseNames.CARRIER);
            sql.append(" SET CARRIER_MAP_RRN = ?, ");
            sql.append(" AVAILABLE_SLOT_COUNT = (SELECT SLOT_COUNT - COUNT(*) FROM CARRIER_MAPPING WHERE " +
                               "CARRIER_MAP_RRN = ?), ");
            sql.append(" POLLUTION_LEVEL = (CASE WHEN SIGN((POLLUTION_LEVEL) -?)=1 THEN " + "POLLUTION_LEVEL " + "ELSE ? " +
                               "END),STATUS=? ");
            sql.append(" WHERE CARRIER_RRN = ? ");
            jdbcTemplate.update(sql.toString(), carrierMapRrn, carrierMapRrn, pollutionLevel, pollutionLevel, status,
                                carrierRrn);
        }

        @Override
        public void insertCarrierMappingH(Long transRrn, Long carrierMappingRrn) {
            StringBuilder sql = new StringBuilder("INSERT INTO CARRIER_MAPPING_H ");
            sql.append(" (CARRIER_MAP_RRN, CARRIER_RRN, POSITION_IN_CARRIER, AVAILABLE_FLAG, UNIT_RRN, TRANS_RRN) ");
            sql.append(" (SELECT CARRIER_MAP_RRN, CARRIER_RRN, POSITION_IN_CARRIER, AVAILABLE_FLAG, UNIT_RRN, ? ");
            sql.append(" FROM CARRIER_MAPPING WHERE CARRIER_MAP_RRN = ? )");
            jdbcTemplate.update(sql.toString(), transRrn, carrierMappingRrn);
        }

        @Override
        public void dispatchCarrier(long carrierRrn, int consumedSlotCount) {

            String sql = "UPDATE CARRIER SET AVAILABLE_SLOT_COUNT = AVAILABLE_SLOT_COUNT - ?" + " WHERE CARRIER_RRN = ?";
            jdbcTemplate.update(sql, consumedSlotCount, carrierRrn);
        }

        @Override
        public Long getCarrierMapRrn(Long carrierRrn) {
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT CARRIER_MAP_RRN FROM ");
            sql.append(DataBaseNames.CARRIER);
            sql.append(" WHERE CARRIER_RRN = ? ");
            Long carrierMapRrn = jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{carrierRrn}, Long.class);
            return carrierMapRrn;
        }

        @Override
        public void updateCarrierForDispatch(Long lotRrn, Long jobRrn, Long eqptRrn) {
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(DataBaseNames.CARRIER);
            sql.append(" SET EQPT_RRN = ?, JOB_RRN = ? ");
            sql.append(" WHERE CARRIER_RRN IN ").append("(SELECT CARRIER_RRN FROM LOT WHERE LOT_RRN = ?) ");

            Object[] args = new Object[]{eqptRrn, jobRrn, lotRrn};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public List<Map<String, Object>> getCarrierChecklistJob(Long carrierRrn) {

            String sql = "SELECT C.CHECKLIST_JOB_RRN,C.ENTITY_RRN,C.CHECKLIST_RRN,C.CHECKLIST_JOB_STATE,P" +
                    ".ACTION_FLAG_1,P.ACTION_FLAG_2" + " FROM CHECKLIST_JOB C,PM_SCHEDULE P WHERE C.ENTITY_RRN=P" +
                    ".ENTITY_RRN AND C" + ".ENTITY_RRN = " + carrierRrn;
            return jdbcTemplate.query(sql, (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("checklistJobRrn", rs.getLong("CHECKLIST_JOB_RRN"));
                map.put("entityRrn", rs.getLong("ENTITY_RRN"));
                map.put("checklistRrn", rs.getLong("CHECKLIST_RRN"));
                map.put("checklistJobState", rs.getString("CHECKLIST_JOB_STATE"));
                map.put("actionFlag1", rs.getString("ACTION_FLAG_1"));
                map.put("actionFlag2", rs.getString("ACTION_FLAG_2"));
                return map;
            });
        }

        @Override
        public void updateRunRrn(long runRrn, long carrierRrn) {
            String sql = "UPDATE CARRIER SET RUN_RRN = ? WHERE CARRIER_RRN = ?";

            jdbcTemplate.update(sql, runRrn, carrierRrn);
        }

        @Override
        public void updateLoadPosition(Short loadPosition, long carrierRrn) {
            String sql = "UPDATE CARRIER SET LOAD_POSITION = ? WHERE CARRIER_RRN = ?";

            jdbcTemplate.update(sql, loadPosition, carrierRrn);
        }

        @Override
        public void updateCarrierForCarrierMapRrn(Long carrierRrn, Integer unitCount, Long carrierMapRrn) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(DataBaseNames.CARRIER);
            sql.append(" SET CARRIER_MAP_RRN = ?, ");
            sql.append(" AVAILABLE_SLOT_COUNT = (SLOT_COUNT - ?) ");
            sql.append(" WHERE CARRIER_RRN = ? ");

            jdbcTemplate.update(sql.toString(), carrierMapRrn, unitCount, carrierRrn);
        }

        @Override
        public Long getNumberOfUnitInCarrier(Long carrierRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(UNIT_RRN) FROM ");
            sql.append(DataBaseNames.CARRIER_MAPPING);
            sql.append(" WHERE CARRIER_MAP_RRN IN ")
               .append(" (SELECT CARRIER_MAP_RRN FROM CARRIER WHERE CARRIER_RRN = ?) ");

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

        @Override
        public void updateCarrierPollutionLevel(Carrier carrier, long transRrn) {
            String sql2 = "UPDATE CARRIER SET POLLUTION_LEVEL=? WHERE CARRIER_RRN = ?";
            jdbcTemplate.update(sql2, carrier.getPollutionLevel(), carrier.getInstanceRrn());
            insertCarrierH(carrier.getInstanceRrn(), StringUtils.EMPTY, carrier.getInstanceId(),
                           new TransactionLog(transRrn, LocalContext.getUserRrn()));
        }

        private String getCarrierListSql(Map<String, Object> argMap) {

            String paramSql1 = "";
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "carrierId"))) {
                paramSql1 += " and n.instance_id like '" + MapUtils.getString(argMap, "carrierId") + "' ";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "status"))) {
                paramSql1 += " and es.current_status = '" + MapUtils.getString(argMap, "status") + "' ";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "type"))) {
                paramSql1 += " and n.object_subtype = '" + MapUtils.getString(argMap, "type") + "' ";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "category"))) {
                paramSql1 += " and noe.flag_type = '" + MapUtils.getString(argMap, "category") + "' ";
            }

            String namedSpace = MapUtils.getString(argMap, "namedSpace").toUpperCase();

            String sql = "select distinct carrier_info.clean_over_date, carrier_info.remain_clean_day, " + "carrier_info" +
                    ".instance_rrn, to_char(new_comment.entity_comment) as carrier_comment," + "lot.lot_id," + " lot" +
                    ".qty1, carrier_info.instance_id, " + "carrier_info.instance_desc, carrier_info.instance_status, " +
                    "carrier_info.slot_count, carrier_info.available_slot_count, carrier_info" + ".current_status, " +
                    "carrier_info.pollution_level, " + "ref_color.color_data, ref_color.color_code, carrier_info" +
                    ".carrier_type, carrier_info" + ".allowable_events_id, carrier_info.engineer_group_id, " +
                    "create_trans_info.trans_start_timestamp, (create_trans_info.trans_performed_by || '" + " ' || " +
                    "user_info.instance_desc) as create_user, " + "ref_category.category_data, ref_category.category_code," +
                    "  least(COALESCE(carrier_info.CLEAN_OVER_DATE, door_clean_over_date, pod_clean_over_date), COALESCE(pod_clean_over_date, carrier_info.CLEAN_OVER_DATE, door_clean_over_date),    " +
                    "  COALESCE(door_clean_over_date, pod_clean_over_date, carrier_info.CLEAN_OVER_DATE))as clean_leastDate "+
                    " from (select n.instance_rrn, noe.flag_type, n.instance_id, n.instance_desc, n" +
                    ".instance_status, p.slot_count, p.available_slot_count, " + "es.current_status, p.pollution_level, p" +
                    ".carrier_code, n.object_subtype as " + "carrier_type, " + "to_char(TRUNC(clean_over_date - sysdate, " +
                    "2),'fm9999999990.00') as remain_clean_day," + " pc.clean_over_date, " + "getinstanceid(e" +
                    ".allowable_events_rrn) as allowable_events_id, getinstanceid(e" + ".maintenance_engineer_rrn) as " +
                    "engineer_group_id,(select temp_pc1.CLEAN_OVER_DATE from PCD_CLEAN temp_pc1 where temp_pc1.INSTANCE_RRN = asbly.POD_RRN)  as pod_clean_over_date, " +
                    " (select temp_pc2.CLEAN_OVER_DATE from PCD_CLEAN temp_pc2 where temp_pc2.INSTANCE_RRN = asbly.DOOR_RRN ) as door_clean_over_date from  carrier p, entity e, entity_status es,named_object n left join " +
                    "NAMED_OBJECT_EXT NOE ON n.instance_rrn = noe.instance_rrn left join " + "pcd_clean pc on n" +
                    ".instance_rrn = pc.instance_rrn left join PCD_ASSEMBLY  asbly on asbly.CARRIER_RRN= n.INSTANCE_RRN " + "where n.instance_rrn = p.carrier_rrn and n.instance_rrn = e" +
                    ".entity_rrn and n" + ".instance_rrn = es.entity_rrn and n.named_space = '" + namedSpace + "' " +
                    "and n.object_subtype <> 'DUMMY' and  n.object = '" + ObjectList.ENTITY_KEY + "' and n.object_type = " +
                    "'" + ObjectList.CARRIER_KEY + "' " + paramSql1 + ") carrier_info left join " + "(select TL" +
                    ".TRANS_START_TIMESTAMP, TL.TRANS_PERFORMED_BY, CH.CARRIER_RRN  from " + "transaction_log tl, " +
                    "CARRIER_H CH, " + "(select MAX(TL.TRANS_START_TIMESTAMP) AS TRANS_START_TIMESTAMP, CH.CARRIER_RRN " +
                    "from" + " CARRIER_H CH, transaction_log tl " + "where CH.trans_rrn = tl.trans_rrn and tl.trans_id = " +
                    "'" + TransactionNames.CREATE_KEY + "' GROUP BY CH.CARRIER_RRN) max_create_time_info " + "where tl" +
                    ".trans_start_timestamp = max_create_time_info.TRANS_START_TIMESTAMP and tl" + ".trans_id = '" +
                    TransactionNames.CREATE_KEY + "' " +
                    "and ch.trans_rrn = tl.trans_rrn and ch.carrier_rrn = max_create_time_info" +
                    ".CARRIER_RRN) create_trans_info on carrier_info.instance_rrn = create_trans_info" +
                    ".carrier_rrn left join " + " (select instance_id, instance_desc from named_object where object = '" +
                    ObjectList.USER_KEY +
                    "') user_info on user_info.instance_id=create_trans_info.trans_performed_by left " +
                    "join (select listagg(lot_id,',') within group(order by lot_id) as lot_id,sum(qty1) as qty1,carrier_rrn" +
                    " from lot  group by carrier_rrn) lot" +
                    " on lot.carrier_rrn = carrier_info.instance_rrn left join " +
                    "(select refd.key_1_value as color_code, refd.data_1_value as color_data from " +
                    "reference_file_detail refd, named_object n " +
                    "where n.instance_id = '$CARRIER_COLOR' and n.named_space = 'MYCIM2' and n" +
                    ".instance_rrn = refd.reference_file_rrn) ref_color on carrier_info.carrier_code = " +
                    "ref_color.color_code left join " +
                    "(select refd.key_1_value as category_code, refd.data_1_value as category_data from " +
                    "reference_file_detail refd, named_object n " +
                    "where n.instance_id = '$PCD_CATEGORY' and n.named_space = 'MYCIM2' and n" +
                    ".instance_rrn = refd.reference_file_rrn) ref_category on carrier_info.flag_type = " +
                    "ref_category.category_code left join " + "(select e1.entity_rrn, e1.create_time, e1.entity_comment " +
                    "from entity_comment_h e1, (select e.entity_rrn, max(e.create_time) as " +
                    "max_create_time from entity_comment_h e group by e.entity_rrn) e2 " +
                    "where e1.entity_rrn = e2.entity_rrn and e1.create_time = e2.max_create_time) " +
                    "new_comment on new_comment.entity_rrn = carrier_info.instance_rrn "
                    // + "order by create_trans_info.trans_start_timestamp desc ";
                    + "order by clean_leastDate,carrier_info.instance_id";
            return sql;
        }

        private StringBuilder buildPcdHistSqlAndLot(String pcdType) {
            StringBuilder sb = new StringBuilder("SELECT TEMP.*, U.USER_NAME FROM (");
            sb.append(getPCDHistSql(pcdType));
            sb.append(" UNION ALL ");
            sb.append("SELECT LOT_T.AVAILABLE_SLOT_COUNT, LOT_T.SLOT_COUNT, GETINSTANCEID(LOT_T.CARRIER_RRN), " + "LOT_T" +
                              ".TRANS_ID, LOT_T.POLLUTION_LEVEL, LOT_T.TRANS_START_TIMESTAMP, LOT_T" +
                              ".TRANS_PERFORMED_BY, ");
            sb.append("LOT_T.OBJECT_SUBTYPE, LOT_T.CATEGORY_DATA, LOT_T.TRANS_COMMENTS, LOT_T.LOCATION, LOT_T" +
                              ".PREV_LOCATION, 'LOT', '', '', '', LOT_T.STATUS, LOT_T.LOT_ID, LOT_T.CLEAN_CYCLE, LOT_T" +
                              ".LOT_RRN,LOT_T.PROCESS_RRN ");
            sb.append("FROM (SELECT T1.*, GETINSTANCEID(T1.CARRIER_RRN) AS CARRIER_ID, GETINSTANCEID(T2.POD_RRN) " + "AS " +
                              "POD_ID, GETINSTANCEID(T2.DOOR_RRN) AS DOOR_ID ");
            sb.append(
                    "FROM (SELECT LT.CARRIER_RRN, LT.POLLUTION_LEVEL, LT.TRANS_RRN, LT.TRANS_SEQUENCE, LT" + ".TRANS_ID, " +
                            "LT.TRANS_COMMENTS, ");
            sb.append("LT.TRANS_START_TIMESTAMP, LT.TRANS_PERFORMED_BY, LT.LOT_RRN, LT.LOT_ID,LT.PROCESS_RRN, LT" +
                              ".FACILITY_RRN, " + "CN.OBJECT_SUBTYPE AS CARRIER_TYPE, LT.LOCATION, LT.PREV_LOCATION, ");
            sb.append("ROW_NUMBER() OVER(PARTITION BY LT.TRANS_START_TIMESTAMP ORDER BY PCD_T" + ".TRANS_START_TIMESTAMP " +
                              "DESC) AS ROW_NUM, ");
            sb.append("PCD_T.CLEAN_CYCLE, PCD_T.STATUS, PCD_T.CATEGORY_DATA, PCD_T.OBJECT_SUBTYPE, PCD_T" +
                              ".AVAILABLE_SLOT_COUNT, PCD_T.SLOT_COUNT ");
            sb.append(
                    "FROM (SELECT C.INSTANCE_RRN, C.OBJECT_SUBTYPE FROM NAMED_OBJECT C WHERE C.OBJECT = " + "'ENTITY' AND" +
                            " C.OBJECT_TYPE = 'CARRIER' AND C.OBJECT_SUBTYPE <> 'DUMMY') CN LEFT JOIN ");
            sb.append("(SELECT LT.CARRIER_RRN, LT.TRANS_RRN, LT.TRANS_SEQUENCE, LT.TRANS_ID, LT.TRANS_COMMENTS, " + "LT" +
                              ".LOCATION, LT.PREV_LOCATION, LT.STEP_SEQUENCE, ");
            sb.append("LT.LOT_RRN, LS.POLLUTION_LEVEL, LS.LOT_ID, LS.FACILITY_RRN, TR.TRANS_START_TIMESTAMP, TR" +
                              ".TRANS_PERFORMED_BY,LS.PROCESS_RRN FROM LOT_TRANS_HISTORY LT, ");
            sb.append("(SELECT POLLUTION_LEVEL, LOT_RRN, LOT_ID, FACILITY_RRN, STEP_SEQUENCE,PROCESS_RRN FROM " +
                              "LOT_STEP_HISTORY) LS, (SELECT TRANS_RRN, TRANS_START_TIMESTAMP, TRANS_PERFORMED_BY FROM " +
                              "TRANSACTION_LOG) TR ");
            sb.append("WHERE LT.LOT_RRN = LS.LOT_RRN AND LT.TRANS_RRN = TR.TRANS_RRN AND LT.STEP_SEQUENCE = LS" +
                              ".STEP_SEQUENCE AND (LT.LOCATION IS NOT NULL OR LT.PREV_LOCATION IS NOT NULL) ");
            sb.append(
                    "AND (((LT.TRANS_ID = 'MOVEOUT' OR LT.TRANS_ID = 'MOVEIN' OR LT.TRANS_ID = 'AUTOCANCELIN') " + "AND " +
                            "TR.TRANS_PERFORMED_BY LIKE 'AUTO-%') ");
            sb.append("OR (LT.TRANS_ID = 'ADJUST' AND LT.TRANS_COMMENTS LIKE 'change lot location to%' AND TR" +
                              ".TRANS_PERFORMED_BY LIKE 'AUTO-%') ");
            sb.append("OR (LT.TRANS_ID = 'ADJUST' AND LT.TRANS_COMMENTS LIKE '%[changed lot location]%') OR LT" +
                              ".TRANS_ID = 'CANCELMOVEIN')) LT ON LT.CARRIER_RRN = CN.INSTANCE_RRN LEFT JOIN");
            sb.append("(");
            sb.append(getPCDHistSql(pcdType));
            sb.append(") PCD_T ON PCD_T.LOT_RRN = LT.LOT_RRN");
            sb.append(" WHERE LT.TRANS_START_TIMESTAMP > PCD_T.TRANS_START_TIMESTAMP) T1 LEFT JOIN ");

            sb.append("(SELECT ASSEMBLY_TIME, DEASSEMBLY_TIME, CARRIER_RRN, POD_RRN, DOOR_RRN FROM (SELECT A_1" +
                              ".TRANS_START_TIMESTAMP AS ASSEMBLY_TIME, ");
            sb.append("CASE WHEN A_2.TRANS_START_TIMESTAMP IS NOT NULL THEN A_2.TRANS_START_TIMESTAMP ELSE " + " sysdate " +
                              "END AS DEASSEMBLY_TIME, ");
            sb.append("A_1.CARRIER_RRN, A_1.POD_RRN, A_1.DOOR_RRN, ROW_NUMBER() OVER(PARTITION BY A_1" +
                              ".TRANS_START_TIMESTAMP ORDER BY A_1.TRANS_START_TIMESTAMP DESC) AS A_RN ");
            sb.append("FROM (SELECT T.TRANS_ID, T.TRANS_START_TIMESTAMP, PH.* FROM PCD_ASSEMBLY_H  PH LEFT JOIN " +
                              "TRANSACTION_LOG T ON T.TRANS_RRN = PH.TRANS_RRN WHERE T.TRANS_ID = 'ASSEMBLY' ");
            if (StringUtils.equals(pcdType, ObjectList.CARRIER_KEY)) {
                sb.append("AND PH.CARRIER_RRN = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.POD_KEY)) {
                sb.append("AND PH.POD_RRN = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.DOOR_KEY)) {
                sb.append("AND PH.DOOR_RRN = ? ");
            }
            sb.append(") A_1 LEFT JOIN ");
            sb.append("(SELECT T.TRANS_ID, T.TRANS_START_TIMESTAMP, PH.* FROM PCD_ASSEMBLY_H  PH LEFT JOIN " +
                              "TRANSACTION_LOG" + " T ON T.TRANS_RRN = PH.TRANS_RRN WHERE T.TRANS_ID = 'DEASSEMBLY' ");
            if (StringUtils.equals(pcdType, ObjectList.CARRIER_KEY)) {
                sb.append("AND PH.CARRIER_RRN = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.POD_KEY)) {
                sb.append("AND PH.POD_RRN = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.DOOR_KEY)) {
                sb.append("AND PH.DOOR_RRN = ? ");
            }
            sb.append(
                    ") A_2 ON A_1.CARRIER_RRN = A_2.CARRIER_RRN AND A_1.POD_RRN = A_2.POD_RRN AND A_1.DOOR_RRN " + "= A_2" +
                            ".DOOR_RRN ");
            sb.append(" AND A_1.TRANS_START_TIMESTAMP < A_2.TRANS_START_TIMESTAMP" + ") AA ");
            sb.append("WHERE AA.A_RN = 1 ORDER BY AA.ASSEMBLY_TIME   DESC, AA.DEASSEMBLY_TIME DESC) T2 ");
            sb.append(" ON T1.CARRIER_RRN = T2.CARRIER_RRN ");
            sb.append("AND T1.TRANS_START_TIMESTAMP > T2.ASSEMBLY_TIME AND T1.TRANS_START_TIMESTAMP < T2" +
                              ".DEASSEMBLY_TIME AND ROWNUM = 1) LOT_T WHERE 1=1 ");
            if (StringUtils.equals(pcdType, ObjectList.CARRIER_KEY)) {
                sb.append("AND LOT_T.CARRIER_ID = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.POD_KEY)) {
                sb.append("AND LOT_T.POD_ID = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.DOOR_KEY)) {
                sb.append("AND LOT_T.DOOR_ID = ? ");
            }
            sb.append(") TEMP LEFT JOIN ");
            sb.append(
                    "(SELECT P.USER_RRN, N.INSTANCE_ID AS USER_ID, P.USER_NAME FROM NAMED_OBJECT N, " + "USER_PROFILE P " +
                            "WHERE N.INSTANCE_RRN = P.USER_RRN AND N.OBJECT = 'USER') U ");
            sb.append(" ON TEMP.TRANS_PERFORMED_BY = U.USER_ID ");

            return sb;
        }

        private StringBuilder getPCDHistSql(String pcdType) {
            String refSql = " (select refd.key_1_value  as category_code, refd.data_1_value as category_data from " +
                    "reference_file_detail refd, named_object n where n.instance_id = '$PCD_CATEGORY'" + " and n" +
                    ".named_space = 'MYCIM2' and n.instance_rrn = refd.reference_file_rrn) ref_category";

            StringBuilder sb = new StringBuilder("SELECT DISTINCT ");
            sb.append("CH.AVAILABLE_SLOT_COUNT, CH.SLOT_COUNT, N.INSTANCE_ID, TH.TRANS_ID, CH.POLLUTION_LEVEL, TH" +
                              ".TRANS_START_TIMESTAMP, TH.TRANS_PERFORMED_BY, CH.OBJECT_SUBTYPE, ");
            sb.append("REF_CATEGORY.CATEGORY_DATA, CH.COMMENTS, '' AS LOCATION, '' AS PREV_LOCATION, 'PCD' AS " +
                              "SOURCE_TYPE, ");

            if (StringUtils.equals(pcdType, ObjectList.CARRIER_KEY)) {
                sb.append(
                        " TR.REASON_CODE,TR.REASON,CH.TARGET_CARRIER TARGET_PCD,CH.STATUS,CH.LOT_ID,PCH" + ".CLEAN_CYCLE," +
                                " L.LOT_RRN,L.PROCESS_RRN ");
                sb.append(" FROM TRANSACTION_LOG TH,NAMED_OBJECT N, CARRIER_H CH LEFT JOIN (SELECT H.LOT_RRN, H" +
                                  ".LOT_ID,H.PROCESS_RRN FROM LOT_H H " + "GROUP BY H" + ".LOT_RRN, H" + ".LOT_ID,H" +
                                  ".PROCESS_RRN) L ON CH.LOT_ID = L.LOT_ID LEFT JOIN PCD_CLEAN_H PCH ON CH" +
                                  ".TRANS_RRN =" + " PCH.TRANS_RRN AND CH.CARRIER_RRN = PCH.INSTANCE_RRN LEFT JOIN " +
                                  "TRANS_REASON TR ON " + "CH.TRANS_RRN = TR.TRANS_RRN LEFT JOIN ");
                sb.append(refSql);
                sb.append(" ON ref_category.category_code = CH.FLAG_TYPE  WHERE CH.TRANS_RRN = TH.TRANS_RRN ");
                sb.append(" AND CH.CARRIER_RRN = N.INSTANCE_RRN AND N.OBJECT_TYPE = 'CARRIER' AND N.INSTANCE_ID =" + " ?");
            } else if (StringUtils.equals(pcdType, ObjectList.POD_KEY)) {
                sb.append(" TR.REASON_CODE,TR.REASON,CH.TARGET_POD TARGET_PCD,CH.STATUS,CH.LOT_ID,PCH" + ".CLEAN_CYCLE, L" +
                                  ".LOT_RRN,L.PROCESS_RRN ");
                sb.append(" FROM TRANSACTION_LOG TH, NAMED_OBJECT N,POD_H CH LEFT JOIN (SELECT H.LOT_RRN, H" + ".LOT_ID,H" +
                                  ".PROCESS_RRN FROM LOT_H H GROUP " + "BY H" + ".LOT_RRN, H.LOT_ID,H.PROCESS_RRN)" +
                                  " L " + "ON CH.LOT_ID = L.LOT_ID LEFT JOIN PCD_CLEAN_H PCH ON CH.TRANS_RRN = PCH" +
                                  ".TRANS_RRN " +
                                  " AND CH.POD_RRN = PCH.INSTANCE_RRN LEFT JOIN TRANS_REASON TR ON CH.TRANS_RRN = TR" +
                                  ".TRANS_RRN LEFT JOIN ");
                sb.append(refSql);
                sb.append(" ON ref_category.category_code = CH.FLAG_TYPE WHERE CH.TRANS_RRN = TH.TRANS_RRN ");
                sb.append(" AND CH.POD_RRN = N.INSTANCE_RRN AND N.OBJECT_TYPE = 'POD' AND N.INSTANCE_ID = ? ");
            } else if (StringUtils.equals(pcdType, ObjectList.DOOR_KEY)) {
                sb.append(
                        " TR.REASON_CODE,TR.REASON,CH.TARGET_DOOR TARGET_PCD,CH.STATUS,CH.LOT_ID,PCH" + ".CLEAN_CYCLE, L" +
                                ".LOT_RRN,L.PROCESS_RRN ");
                sb.append(" FROM TRANSACTION_LOG TH,NAMED_OBJECT N,DOOR_H CH LEFT JOIN (SELECT H.LOT_RRN, H" + ".LOT_ID,H" +
                                  ".PROCESS_RRN FROM LOT_H H GROUP BY H.LOT_RRN, H" + ".LOT_ID,H.PROCESS_RRN) L ON CH" +
                                  ".LOT_ID = L.LOT_ID LEFT JOIN PCD_CLEAN_H PCH ON CH" +
                                  ".TRANS_RRN = PCH.TRANS_RRN AND CH" + ".DOOR_RRN = PCH.INSTANCE_RRN LEFT JOIN " +
                                  "TRANS_REASON TR ON CH.TRANS_RRN = TR" + ".TRANS_RRN LEFT JOIN ");
                sb.append(refSql);
                sb.append(" ON ref_category.category_code = CH.FLAG_TYPE WHERE CH.TRANS_RRN = TH.TRANS_RRN ");
                sb.append(" AND CH.DOOR_RRN = N.INSTANCE_RRN AND N.OBJECT_TYPE = 'DOOR' AND N.INSTANCE_ID = ? ");
            }
            return sb;
        }

        @Override
        public boolean checkCarrierInCarrierMappingH(long carrierRrn) {
            String sql = "SELECT COUNT(*) FROM CARRIER_MAPPING_H WHERE CARRIER_RRN = ?";
            long count = jdbcTemplate.queryForObject(sql, new Object[]{carrierRrn}, long.class);
            return count > 0;
        }

        @Override
        public boolean checkCarrierInSortJob(long carrierRrn) {
            String sql = "SELECT COUNT(*) FROM SORT_JOB WHERE TARGET_CARRIER_RRN = ?";
            long count = jdbcTemplate.queryForObject(sql, new Object[]{carrierRrn}, long.class);
            return count > 0;
        }

        @Override
        public Boolean getCarrierRefreshInfo(Long carrierRrn) {
            String sql = "SELECT REFRESH_FLAG FROM CARRIER WHERE CARRIER_RRN = ?";

            Integer flag = jdbcTemplate.queryForObjectWithNull(sql, Integer.class, carrierRrn);
            return flag == null ? Boolean.FALSE : flag.intValue() > 0;
        }

        @Override
        public void refreshCarrier(Long carrierRrn) {
            String sql = "UPDATE CARRIER SET REFRESH_FLAG = ? WHERE CARRIER_RRN = ?";
            jdbcTemplate.update(sql, Boolean.TRUE, carrierRrn);
        }

        @Override
        public String generateCstIdByType(String prefix, int width) {
            String  sql = " select INSTANCE_ID from  ";
            sql+= " (select nob.INSTANCE_ID from CARRIER  left join NAMED_OBJECT nob on  nob.INSTANCE_RRN = CARRIER_RRN ";
            sql+=" where nob.INSTANCE_ID like ? and trim(translate(nvl(replace(nob.INSTANCE_ID,?,''),'x'),'0123456789',' ')) is null ";
            sql+=" order by to_number(replace(nob.INSTANCE_ID, ?, '')) desc  )"; //一旦出现手改的情况 不能使用创建时间倒排
            sql+=" where ROWNUM = 1 ";
            String likeParam = prefix+StringUtils.repeat("_",width);
            String podId =jdbcTemplate.queryForObjectWithNull(sql,new Object[]{likeParam,prefix,prefix},String.class);
            if(StringUtils.isEmpty(podId)){//从1开始
                return prefix+StringUtils.repeat("0",width-1)+"1";
            }
            //累加操作 //数据转换 在sql已经检查了
            long value = Long.parseLong(podId.replace(prefix,""));
            value++;
            if(value> Long.parseLong(StringUtils.repeat("9",width))){
                return "-1";
            }
            return prefix+StringUtils.leftPad(String.valueOf(value),width,"0");
        }

        @Override
        public void updateCarrierType(Carrier carrier, String comments, Long transRrn) {
            Date date = DateUtils.parse(DateUtils.getNowTime());
            String sql = "UPDATE NAMED_OBJECT SET OBJECT_SUBTYPE = ?,LAST_UPDATE_TIMESTAMP = ? WHERE INSTANCE_RRN = ?";
            jdbcTemplate.update(sql, carrier.getObjectSubtype(), date, carrier.getInstanceRrn());

            String sql2 = "UPDATE CARRIER SET OBJ_SUBTYPE=?, UPDATE_USER_RRN = ?,UPDATE_TIME=? WHERE CARRIER_RRN = ?";
            jdbcTemplate.update(sql2, carrier.getObjectSubtype(), carrier.getLastUpdateUserRrn(), carrier.getLastUpdateTimestamp(),
                                carrier.getInstanceRrn());
            TransactionLog tr = new TransactionLog(transRrn, LocalContext.getUserRrn());
            tr.setTransId(carrier.getTransId());
            insertCarrierH(carrier.getInstanceRrn(), comments, carrier.getInstanceId(), tr);
        }

        @Override
        public String generatePcdIdByPcdType(String pcdType, String prefix, List<String> ignorePrefix, int length) {
            String matchPrefix = prefix + StringUtils.repeat("_", length);
            StringBuilder ignoreSql = new StringBuilder();
            if(CollectionUtils.isNotEmpty(ignorePrefix)){
                for(String ignoreStr:ignorePrefix){
                    if(StringUtils.isEmpty(ignoreStr)){
                        continue;
                    }
                    ignoreSql.append(" AND NOB.INSTANCE_ID NOT LIKE '").append(ignoreStr).append("%'");
                }
            }
            String  sql = " SELECT INSTANCE_ID FROM (SELECT NOB.INSTANCE_ID FROM "
                    + pcdType + " LEFT JOIN NAMED_OBJECT NOB ON  NOB.INSTANCE_RRN = " + pcdType + "_RRN "
                    + " WHERE NOB.INSTANCE_ID LIKE ? " + ignoreSql + " AND LENGTH(NOB.INSTANCE_ID) = ?"
                    + " ORDER BY NOB.INSTANCE_RRN DESC  ) WHERE ROWNUM = 1 ";//一旦出现手改的情况 不能使用创建时间倒排

            String pcdId = jdbcTemplate.queryForObjectWithNull(sql,new Object[]{matchPrefix, matchPrefix.length()},String.class);
            if(StringUtils.isEmpty(pcdId)){//从1开始
                return StringUtils.leftPad("0",length,"0");
            }

            return StringUtils.replace(pcdId, prefix, StringUtils.EMPTY);
        }

    }