# EquipmentQueryDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.lang.BooleanUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.wip.dao.EquipmentQueryDAO;
    import com.mycim.utils.EmasUtil;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.consts.LinkTypeList;
    import com.mycim.valueobject.ems.Entity;
    import com.mycim.valueobject.ems.EntityGroup;
    import com.mycim.valueobject.ems.Equipment;
    import com.mycim.valueobject.ems.Season;
    import com.mycim.valueobject.security.Station;
    import com.mycim.valueobject.wip.LotStatus;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

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

    /**
     * @author Johnson.Wang
     * @version 6.0.0
     * @date 2019/9/24
     **/
    @Repository
    public class EquipmentQueryDAOImpl implements EquipmentQueryDAO {

        private final JdbcTemplate jdbcTemplate;

        public EquipmentQueryDAOImpl(JdbcTemplate jdbcTemplate) {
            this.jdbcTemplate = jdbcTemplate;
        }

        @Override
        public List<Equipment> getEquipment4Ext(long equipmentRrn, long stationRrn) {
            String whereSql = " WHERE 1=1 ";

            if (equipmentRrn > 0) {
                whereSql += " AND eqpt_rrn=" + equipmentRrn;
            } else {
                whereSql += " AND eqpt_rrn IN (SELECT r.to_rrn FROM relation r WHERE r.link_type = " +
                        "'STATION_TO_EQUIPMENT' AND r.from_rrn = " + stationRrn + ") ";
            }
            String sql = "SELECT o.instance_rrn,o.instance_id,o.instance_desc,es.status_group_id,es.current_status," +
                    "controling_station_rrn," + "batching_rule,dispatching_rule,alarm_id,speed_factor,transport_time," +
                    "processing_batch_type,equipment_model_rrn,processing_batch_size_min," + "processing_batch_size_max," +
                    "processing_batch_size_avg,trace_file_dir,recipe_file_dir," + "cost_job_size,cost_lot_size," +
                    "dynamic_batch_flag,concurrent_job_size,current_setup_id," + "next_setup_id,process_engineer_rrn," +
                    "(SELECT COUNT(*) FROM job j WHERE j.eqpt_rrn=e" + ".eqpt_rrn " + " AND job_status = 'RUNNING') " +
                    "number_of_running_jobs FROM equipment e, named_object o," + "entity_status es " + whereSql + " AND o" +
                    ".instance_rrn = eqpt_rrn AND es.entity_rrn=eqpt_rrn";

            return jdbcTemplate.query(sql, new RowMapper<Equipment>() {
                @Override
                public Equipment mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Equipment equipment = new Equipment();
                    equipment.setInstanceRrn(rs.getLong("instance_rrn"));
                    equipment.setInstanceId(rs.getString("instance_id"));
                    equipment.setInstanceDesc(rs.getString("instance_desc"));
                    equipment.setCurrentStatus(rs.getString("current_status"));
                    equipment.setControlingStationRrn(new Long(rs.getLong("CONTROLING_STATION_RRN")));
                    equipment.setBatchingRule(rs.getString("BATCHING_RULE"));
                    equipment.setDispatchingRule(rs.getString("DISPATCHING_RULE"));
                    equipment.setAlarmId(rs.getString("ALARM_ID"));
                    equipment.setSpeedFactor((new Double(rs.getDouble("SPEED_FACTOR"))));
                    equipment.setTransportTime(new Long(rs.getLong("TRANSPORT_TIME")));
                    equipment.setProcessingBatchType(rs.getString("PROCESSING_BATCH_TYPE"));
                    equipment.setProcessingBatchSizeMin(new Long(rs.getLong("PROCESSING_BATCH_SIZE_MIN")));
                    equipment.setProcessingBatchSizeMax(new Long(rs.getLong("PROCESSING_BATCH_SIZE_MAX")));
                    equipment.setProcessingBatchSizeAvg(new Long(rs.getLong("PROCESSING_BATCH_SIZE_AVG")));
                    equipment.setTraceFileDir(rs.getString("TRACE_FILE_DIR"));
                    equipment.setRecipeFileDir(rs.getString("RECIPE_FILE_DIR"));
                    equipment.setCostJobSize(rs.getString("COST_JOB_SIZE"));
                    equipment.setCostLotSize(rs.getString("COST_LOT_SIZE"));
                    equipment.setDynamicBatchFlag(rs.getString("DYNAMIC_BATCH_FLAG"));
                    equipment.setConcurrentJobSize(new Long(rs.getLong("CONCURRENT_JOB_SIZE")));
                    equipment.setCurrentSetupId(rs.getString("CURRENT_SETUP_ID"));
                    equipment.setNextSetupId(rs.getString("NEXT_SETUP_ID"));
                    equipment.setProcessEngineerRrn(new Long(rs.getLong("PROCESS_ENGINEER_RRN")));

                    equipment.setEquipmentModelRrn(new Long(rs.getLong("EQUIPMENT_MODEL_RRN")));

                    equipment.setNumberOfRunningJobs(new Long(rs.getLong("number_of_running_jobs")));
                    equipment.setJobBuffer(rs.getLong("CONCURRENT_JOB_SIZE") - rs.getLong("number_of_running_jobs"));

                    return equipment;
                }
            });

        }

        @Override
        public List<Station> getEntities4ExtByUser(long userRrn, String namedSpace, String filter) {

            List<Station> stationList = new ArrayList<>();

            String sql = "SELECT DISTINCT s.instance_rrn as s_instance_rrn," + "s.instance_id as s_instance_id,s" +
                    ".instance_desc as s_instance_desc,s.object as " + "s_object," + "r2.key_1_value AS " +
                    "g_equipment_group,r2.data_1_value AS g_equipment_group_desc," + "ent.PARENT_ENTITY_RRN,ee" +
                    ".CHAMBER_TYPE,ee.ATTRIBUTE_DATA5 as isChamberEquip," + "n.instance_rrn,n.instance_id,n" +
                    ".instance_desc,ee.attribute_data1 as pollution_level," + "ee.attribute_data2 as pm_hold_flag,e" +
                    ".current_status " + "FROM (((((((named_object s inner join relation r on s.instance_rrn = r.from_rrn" +
                    " " + "and s.OBJECT = 'STATION' and r.link_type = 'STATION_TO_EQUIPMENT') INNER JOIN " +
                    "named_object n on n.instance_rrn = r.to_rrn  and n.named_space = ? and " + "n.instance_status = " +
                    "'ACTIVE' and n.OBJECT = 'ENTITY' and n.object_type = 'MAINEQPT')" + "inner join entity ent on ent" +
                    ".entity_rrn = n.instance_rrn )" + "inner join entity_ext ee on ee.eqpt_rrn = n.instance_rrn )" +
                    "inner join relation r1 on r1.from_rrn = r.from_rrn and r1.link_type = " + "'STATION_TO_USER' " +
                    "and r1.to_rrn IN ( SELECT r4.to_rrn FROM relation r4  WHERE r4.link_type = " + "'USER_TO_USER_GROUP'" +
                    " AND r4.from_rrn = ?)) inner join reference_file_detail r2 " + "on r2.key_1_value = ee" +
                    ".equipment_owner) inner join named_object G " + "on r2.reference_file_rrn = G.instance_rrn and G" +
                    ".INSTANCE_ID = '$EQPT_OWNER' and " + "G.OBJECT = 'REFERENCEFILE') left join entity_status e on n" +
                    ".instance_rrn = e" + ".entity_rrn " + "WHERE 1=1";

            if (filter.equals("AVAILABLE")) {
                sql = sql + "AND e.current_status in ('LT', 'RUN')";
            } else {
                sql = sql + "AND e.current_status IS NOT NULL ";
            }

            sql = sql + " ORDER BY s.instance_id,g_equipment_group,n.instance_id";

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, namedSpace, userRrn);

            while (rs.next()) {

                Entity entity = new Entity();
                entity.setInstanceRrn(rs.getLong("instance_rrn"));
                entity.setInstanceId(rs.getString("instance_id"));
                entity.setInstanceDesc(rs.getString("instance_desc"));
                entity.setCurrentStatus(rs.getString("current_status"));
                entity.setPollutionLevel(rs.getString("pollution_level"));
                entity.setPmHoldFlag(BooleanUtils.toBoolean(rs.getString("pm_hold_flag")));
                entity.setParentEntityRrn(rs.getLong("PARENT_ENTITY_RRN"));
                entity.setIsChamberEquip(rs.getString("isChamberEquip"));
                entity.setChamberType(rs.getString("CHAMBER_TYPE"));
                EntityGroup entityGroup = new EntityGroup();
                Collection entities = new ArrayList();
                entityGroup.setEntities(entities);
                entityGroup.setInstanceId(rs.getString("g_equipment_group"));
                entityGroup.setInstanceDesc(rs.getString("g_equipment_group_desc"));
                entityGroup.getEntities().add(entity);

                Station station = new Station();
                Collection entityGroups = new ArrayList();
                station.setEntityGroups(entityGroups);
                station.setInstanceRrn(rs.getLong("s_instance_rrn"));
                station.setInstanceId(rs.getString("s_instance_id"));
                station.setInstanceDesc(rs.getString("s_instance_desc"));
                station.getEntityGroups().add(entityGroup);

                if (stationList.contains(station)) {
                    Station stationTemp = null;
                    for (Iterator iterator = stationList.iterator(); iterator.hasNext(); ) {
                        stationTemp = (Station) iterator.next();
                        if (stationTemp.equals(station)) {
                            Collection entityGroupsTemp = stationTemp.getEntityGroups();
                            EntityGroup entityGroupTemp = null;
                            boolean flag = false;
                            for (Iterator iterator2 = entityGroupsTemp.iterator(); iterator2.hasNext(); ) {
                                entityGroupTemp = (EntityGroup) iterator2.next();
                                if (entityGroupTemp.getInstanceId().equals(entityGroup.getInstanceId())) {
                                    flag = true;
                                    break;
                                }
                            }
                            if (flag) {
                                entityGroupTemp.getEntities().addAll(entityGroup.getEntities());
                            } else {
                                entityGroupsTemp.addAll(station.getEntityGroups());
                            }
                        }
                    }
                } else {
                    stationList.add(station);
                }
            }
            return stationList;
        }

        /**
         * 修改逻辑:
         * 1.按照 PROCESS_SPEC_ITEM_ACTIVE 获取 ENTITYGROUP
         * 2.此处逻辑参考 LOT QUERY 保证数量和查出来的结果能保持一致
         * @param lotStatus
         * @return
         */
        @Override
        public Map<String, Integer> countLotByStausForEntity(String lotStatus) {
            if(StringUtils.isEmpty(lotStatus)){
                return new HashMap<>();
            }
            List<Object> args = new ArrayList<Object>();
            StringBuilder sql =new StringBuilder();
            sql.append(" SELECT  TEMP2.ENTITY_ID ENTITY_ID, COUNT(TEMP1.LOT_ID) LOT_COUNT  ");
            sql.append(" FROM (SELECT T.LOT_ID,  ");
            sql.append("              PROCESS_RRN,  ");
            sql.append("              PROCESS_VERSION,  ");
            sql.append("              ROUTE_RRN,  ");
            sql.append("              OPERATION_RRN,  ");
            sql.append("              REGEXP_SUBSTR(T.EQUIPMENT_GROUP_RRNS, '[^,]+', 1, LEVEL) EQUIPMENT_GROUP_RRN  ");
            sql.append("       FROM (SELECT L.LOT_ID,  ");
            sql.append("                    PSA.EQUIPMENT_GROUP_RRNS,  ");
            sql.append("                    PSA.PROCESS_RRN,  ");
            sql.append("                    PSA.PROCESS_VERSION,  ");
            sql.append("                    PSA.ROUTE_RRN,  ");
            sql.append("                    PSA.OPERATION_RRN,  ");
            sql.append("                    ROWNUM NUM  ");
            sql.append("             FROM PROCESS_SPEC_ITEM_ACTIVE PSA,  ");
            sql.append("                  LOT L  ");
            sql.append("             WHERE PSA.PROCESS_RRN = L.PROCESS_RRN  ");
            sql.append("               AND PSA.PROCESS_VERSION = L.PROCESS_VERSION  ");
            sql.append("               AND PSA.OPERATION_RRN = L.OPERATION_RRN  ");
            sql.append("               AND PSA.ROUTE_RRN = REGEXP_SUBSTR(L.PROCESS_STEP_VERSION, '[^,]+',  ");
            sql.append("                                                 INSTR(L.PROCESS_STEP_VERSION, '|', 1, 1) + 1, 1)  ");
            sql.append("               AND L.LOT_STATUS = ?  ");
            sql.append("            ) T  ");
            sql.append("       CONNECT BY LEVEL <= REGEXP_COUNT(T.EQUIPMENT_GROUP_RRNS, '[^,]+')  ");
            sql.append("              AND PRIOR NUM = NUM  ");
            sql.append("              AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL  ");
            sql.append("      ) TEMP1  ");
            sql.append("          LEFT JOIN (  ");
            sql.append("     SELECT E.INSTANCE_ID AS ENTITY_ID, R.TO_RRN AS ENTITY_GROUP_RRN  ");
            sql.append("     FROM NAMED_OBJECT E,  ");
            sql.append("          RELATION R  ");
            sql.append("     WHERE E.OBJECT = ? ");
            sql.append("       AND E.OBJECT_TYPE = ?  ");
            sql.append("       AND (R.LINK_TYPE = ? OR R.LINK_TYPE = ?)  ");
            sql.append("       AND E.INSTANCE_RRN = R.FROM_RRN(+)  ");
            sql.append(" ) TEMP2 on TEMP1.EQUIPMENT_GROUP_RRN = ENTITY_GROUP_RRN  ");
            sql.append(" WHERE ENTITY_ID IS NOT NULL  ");
            sql.append(" GROUP BY ENTITY_ID  ");

            args.add(lotStatus);
            args.add(ObjectList.ENTITY_KEY);
            args.add(ObjectList.EQUIPMENT_KEY);
            args.add(LinkTypeList.ENTITY_ENTITYROUP_KEY);
            args.add(LinkTypeList.ENTITY_GROUP_INACTIVE_KEY);

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql.toString(), args.toArray());

            Map<String, Integer> map = new HashMap<>();
            while (rs.next()) {
                map.put(rs.getString("ENTITY_ID"), rs.getInt("LOT_COUNT"));
            }

            return map;
        }

        /***
         * 根据设备号查询所包含的状态
         */
        @Override
        public List<String> getLotsStatusEntity(Long entityRrn) {
            // 查询Running和Processed作业时需要关联JOB表

            String sql = "SELECT LOT_STATUS FROM ( SELECT LOT.LOT_STATUS from Lot, LOT_EXT ," + "(SELECT LOT_RRN, DECODE" +
                    "(SIGN(TIME_REMAINING),-1,0,TIME_REMAINING) AS TIME_REMAINING" + " FROM (SELECT lot_rrn,MIN((ROUND" +
                    "(TO_NUMBER( TO_DATE(TO_CHAR" + "(T0.START_TIME,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') - " +
                    "SYSDATE )* " + "86400))+T0.TIMELIMIT) AS " + " TIME_REMAINING from TIMELIMIT_STATUS t0 WHERE t0" +
                    ".timelimit>=0 and t0" + ".status='NORMAL' group by lot_rrn))  T ,JOB" + " WHERE LOT.LOT_RRN = " +
                    "LOT_EXT.LOT_RRN(+)  AND (LOT.LOT_STATUS='RUNNING' OR LOT" + ".LOT_STATUS='PROCESSED')" + " AND ((lot" +
                    ".operation_rrn IN (SELECT o.operation_rrn FROM operation o WHERE o" + ".entity_group_rrn in (SELECT " +
                    "distinct to_rrn FROM relation" + " T WHERE t.link_type='ENTITY_TO_ENTITY_GROUP' AND t.from_rrn IN " +
                    "(?) ) ) ))" + " AND ROWNUM<=500 AND LOT.LOT_RRN = T.LOT_RRN(+) AND  LOT.JOB_RRN=JOB.JOB_RRN AND " +
                    "JOB.EQPT_RRN in (?)" + " AND LOT.LOT_RRN NOT IN (select l.LOT_RRN from lot l," + " (select " +
                    "context_key4 as operation_rrn, context_key5 as lot_rrn from context_value c" + " where status = " +
                    "'ACTIVE' AND context_rrn in (select instance_rrn from named_object " + "where instance_id = " +
                    "'LOT_SPECIAL_EQUIP_CONTEXT')) T" + " WHERE to_char(L.LOT_RRN) = T.lot_rrn(+) AND to_char(L" +
                    ".OPERATION_RRN) = T" + ".OPERATION_RRN))" + " union " + " SELECT LOT_STATUS from ( SELECT LOT" +
                    ".LOT_STATUS from Lot, LOT_EXT ,(SELECT LOT_RRN," + " DECODE(SIGN(TIME_REMAINING),-1,0," +
                    "TIME_REMAINING)" + " AS TIME_REMAINING FROM (select lot_rrn,min((ROUND(TO_NUMBER( TO_DATE(TO_CHAR" +
                    "(T0" + ".START_TIME,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') - SYSDATE )* 86400))" + "+T0" +
                    ".TIMELIMIT) AS TIME_REMAINING from TIMELIMIT_STATUS t0 where t0.timelimit>=0 and" + " t0.status" +
                    "='NORMAL' group by lot_rrn))  T   WHERE LOT.LOT_RRN = LOT_EXT.LOT_RRN(+)" + " AND (LOT" +
                    ".LOT_STATUS='WAITING' OR LOT.LOT_STATUS='HOLD' OR LOT" + ".LOT_STATUS='DISPATCHER' )  AND ((lot" +
                    ".operation_rrn IN (SELECT o.operation_rrn FROM" + " operation o " + " WHERE o.entity_group_rrn in " +
                    "(SELECT distinct to_rrn FROM relation t WHERE t" + ".link_type='ENTITY_TO_ENTITY_GROUP' AND t" +
                    ".from_rrn IN (?) ) ) ))" + " AND ROWNUM<=500 AND LOT.LOT_RRN = T.LOT_RRN(+)" + " AND LOT.LOT_RRN NOT" +
                    " IN (select l.LOT_RRN from lot l," + " (select context_key4 as operation_rrn, context_key5 as " +
                    "lot_rrn from context_value c" + " where status = 'ACTIVE' AND context_rrn in (select instance_rrn " +
                    "from named_object " + "where instance_id = 'LOT_SPECIAL_EQUIP_CONTEXT')) T" + " WHERE to_char(L" +
                    ".LOT_RRN) = T.lot_rrn(+) AND to_char(L.OPERATION_RRN) = T" + ".OPERATION_RRN))" + " union " + " " +
                    "select lot_status from lot L, (select context_key4  as operation_rrn, context_key5" + "  as lot_rrn," +
                    " result_value1 as entity_rrn" + " from context_value c where status = 'ACTIVE' AND context_rrn in" +
                    " (select instance_rrn from named_object where instance_id = " + "'LOT_SPECIAL_EQUIP_CONTEXT')) T " +
                    " WHERE to_char(L.LOT_RRN) = T.lot_rrn(+) AND to_char(L.OPERATION_RRN) = T" + ".OPERATION_RRN AND T" +
                    ".ENTITY_RRN = ?";

            return jdbcTemplate.query(sql, new Object[]{entityRrn, entityRrn, entityRrn, StringUtils.toString(entityRrn)},
                                      String.class);
        }

        @Override
        public List<Map<String, Object>> getDiffEqpRecipeBatchSet(long equipmentRrn) {
            String sql = "SELECT EQPT_RRN,RECIPE_RRN,getinstanceid(RECIPE_RRN) as RECIPE_ID," + "getinstanceid" +
                    "(MONITOR_PRODUCT_RRN) as MONITOR_PRODUCT_ID,MONITOR_PRODUCT_RRN," + "MONITOR_LOWER_LIMIT,EDC_FLAG" +
                    " FROM DIFF_EQPT_BATCH_SET WHERE EQPT_RRN = ?";
            return jdbcTemplate.query(sql, new Object[]{equipmentRrn}, (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                Map<String, Object> map = new HashMap<>();
                map.put("eqptRrn", rs.getLong("EQPT_RRN"));
                map.put("recipeId", rs.getString("RECIPE_ID"));
                map.put("recipeRrn", rs.getLong("RECIPE_RRN"));
                map.put("monitorProductRrn", rs.getLong("MONITOR_PRODUCT_RRN"));
                map.put("monitorProductId", rs.getString("MONITOR_PRODUCT_ID"));
                map.put("monitorLimit", rs.getLong("MONITOR_LOWER_LIMIT"));
                map.put("edcFlag", rs.getString("EDC_FLAG"));
                return map;
            });
        }

        @Override
        public String getEqpGroupIdsByEquipmentRrn(long equipmentRrn) {
            String sql = "SELECT LISTAGG(A.ENTITYGROUP_ID, ',') WITHIN GROUP(ORDER BY A.ENTITYGROUP_ID) " +
                    " AS EQP_GROUP_IDS FROM (SELECT R.FROM_RRN, EG.ENTITYGROUP_ID FROM REL_ENTITY_GROUP R," +
                    " EQUIPMENT_GROUP EG WHERE R.TO_RRN = EG.SYS_RRN AND R.LINK_TYPE = ? AND R.FROM_RRN = ?) A";

            Object[] args = new Object[]{LinkTypeList.ENTITY_ENTITYROUP_KEY, equipmentRrn};
            return jdbcTemplate.queryForObjectWithNull(sql, args, String.class);
        }

        @Override
        public String getEquipmentLastMoveOutTime(String equipmentId) {
            String sql = "select TO_CHAR(max(equipment_run_time),?) from equipment_run_history " +
                    "where run_type in (?,?) and equipment_id = ? ";
            Object[] args = {DateUtils.DATE_FORMAT24, EmasUtil.RUN_TYPE_MOVEOUT, EmasUtil.RUN_TYPE_RUNCARDMOVEOUT,
                             equipmentId};
            return jdbcTemplate.queryForObjectWithNull(sql, args, String.class);
        }

        @Override
        public boolean checkEquipmentHasRunningLot(Long eqpRrn) {
            String sql = "select count(lot_id) from lot where eqpt_rrn = ? and lot_status = ? ";
            Object[] args = {eqpRrn.longValue(), LotStatus.RUNNING};
            return jdbcTemplate.queryForObjectWithNull(sql, args, Integer.class) > 0 ? true : false;
        }

        @Override
        public String getLastHoldTimeOnEqp(Long eqpRrn) {
            StringBuffer sql = new StringBuffer();
            sql.append("select to_char(max(mh.hold_timestamp),?) from lot l,multiple_hold mh ");
            sql.append("where l.eqpt_rrn = ? and l.lot_rrn = mh.instance_rrn order by mh.hold_timestamp ");
            Object[] args = {DateUtils.DATE_FORMAT24, eqpRrn.longValue()};

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args, String.class);
        }

        @Override
        public String getLastProcessEndTime(String equipmentId, String chamberType) {
            String sql = "select to_char(process_end_time,?) from (select process_end_time " +
                    " from elog_sub_sheet where equipment_id = ? and chamber_type = ? " +
                    " and process_end_time is not null order by process_end_time desc) " + " where rownum = 1 ";
            Object[] args = {DateUtils.DATE_FORMAT24, equipmentId, chamberType};
            return jdbcTemplate.queryForObjectWithNull(sql, args, String.class);
        }

        @Override
        public String getLastMoveInTimeByEqp(String equipmentId, Long eqpRrn) {
            StringBuilder sql = new StringBuilder();
            sql.append("select to_char(min(equipment_run_time),?) ");
            sql.append("from (select row_number()over(partition by erh.lot_id order by ");
            sql.append("erh.equipment_run_time desc) row_num, ");
            sql.append("erh.equipment_run_time from lot l,equipment_run_history erh ");
            sql.append("where l.lot_id = erh.lot_id and erh.run_type in (?,?) and l.eqpt_rrn = ? ");
            sql.append("and erh.equipment_id = ? ) a where a.row_num = 1 ");

            Object[] args = {DateUtils.DATE_FORMAT24, EmasUtil.RUN_TYPE_MOVEIN, EmasUtil.RUN_TYPE_RUNCARDMOVEIN,
                             eqpRrn.longValue(), equipmentId};
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args, String.class);
        }

        @Override
        public String getLastProcessStartTime(Long eqpRrn, String equipmentId, String chamberType) {
            StringBuilder sql = new StringBuilder();
            sql.append("select to_char(process_start_time,?) ");
            sql.append("from (select esch.process_start_time from lot l, ");
            sql.append("elog_sub_sheet_event esch where l.lot_id = esch.lot_id ");
            sql.append("and l.eqpt_rrn = ? and esch.equipment_id = ? ");
            sql.append("and esch.chamber_type = ? ");
            sql.append("order by esch.process_start_time desc) where rownum = 1 ");
            Object[] args = {DateUtils.DATE_FORMAT24, eqpRrn.longValue(), equipmentId, chamberType};
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args, String.class);
        }

        @Override
        public Map<String,Object> getLastEquipmentRunRecipe(String equipmentId) {
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT PHYSICAL_RECIPE_ID, TO_CHAR(EQUIPMENT_RUN_TIME,?) AS EQUIPMENT_RUN_TIME FROM ");
            sql.append(" (SELECT PHYSICAL_RECIPE_ID, EQUIPMENT_RUN_TIME FROM EQUIPMENT_RUN_HISTORY ");
            sql.append(" WHERE EQUIPMENT_ID = ? AND RUN_TYPE IN (?,?) ");
            sql.append(" ORDER BY EQUIPMENT_RUN_TIME DESC) WHERE ROWNUM=1 ");
            Object[] args = {DateUtils.DATE_FORMAT24, equipmentId, EmasUtil.RUN_TYPE_MOVEIN, EmasUtil.RUN_TYPE_RUNCARDMOVEIN};
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), 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("recipeId", rs.getString("PHYSICAL_RECIPE_ID"));
                    map.put("processTime", rs.getString("EQUIPMENT_RUN_TIME"));
                    return map;
                }
            });
        }

        @Override
        public Map<String,Object> getLastChamberEquipmentRunRecipe(String equipmentId, Long equipmentRrn, String chamberType) {
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT RECIPE_PHYSICAL_ID, TO_CHAR(PROCESS_START_TIME,?) AS PROCESS_START_TIME FROM ");
            sql.append(" (SELECT RECIPE_PHYSICAL_ID, PROCESS_START_TIME FROM ");
            sql.append(" (SELECT RECIPE_PHYSICAL_ID, PROCESS_START_TIME FROM ELOG_SUB_SHEET ");
            sql.append(" WHERE EQUIPMENT_ID = ? AND EQUIPMENT_RRN = ? AND CHAMBER_TYPE = ? UNION ");
            sql.append(" SELECT RECIPE_PHYSICAL_ID, PROCESS_START_TIME FROM ELOG_SUB_SHEET_EVENT ");
            sql.append(" WHERE EQUIPMENT_ID = ? AND EQUIPMENT_RRN = ? AND CHAMBER_TYPE = ? )");
            sql.append(" ORDER BY PROCESS_START_TIME DESC) WHERE ROWNUM = 1 ");
            Object[] args = {DateUtils.DATE_FORMAT24, equipmentId, equipmentRrn.longValue(), chamberType,
                             equipmentId, equipmentRrn.longValue(), chamberType};
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), 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("recipeId", rs.getString("RECIPE_PHYSICAL_ID"));
                    map.put("processTime", rs.getString("PROCESS_START_TIME"));
                    return map;
                }
            });
        }

        @Override
        public String getLastEquipmentLogEvent(Long eqpRrn) {
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT TO_CHAR(LOG_EVENT_TIME, ?) LOG_EVENT_TIME FROM ");
            sql.append(" (SELECT EEH.LOG_EVENT_TIME FROM ENTITY_EVENT_HISTORY EEH, ");
            sql.append(" NAMED_OBJECT NO WHERE EEH.EVENT_RRN = NO.INSTANCE_RRN ");
            sql.append(" AND EEH.ENTITY_RRN = ? AND NO.INSTANCE_ID LIKE ? ");
            sql.append(" ORDER BY EEH.LOG_EVENT_TIME DESC) WHERE ROWNUM = 1");
            Object[] args = {DateUtils.DATE_FORMAT24, eqpRrn.longValue(), Season.SEASON_PM_EQP_EVENT};
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args, String.class);
        }

    }