# JobQueryDAOImpl.java

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

    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.wip.dao.JobQueryDAO;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.wip.Job;
    import com.mycim.valueobject.wip.LotStatus;
    import com.mycim.valueobject.wip.Run;
    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/21
     **/
    @Repository
    public class JobQueryDAOImpl implements JobQueryDAO {

        private final JdbcTemplate jdbcTemplate;

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

        @Override
        public List<Map> getJobList(long instanceRrn, String type, Map operationInfo) {
            List jobList = new ArrayList();

            String strWhere = "";
            StringBuffer sql = new StringBuffer();

            String processStepString = " ";
            String processStepIdString = " ";
            int firstSep;
            int secondSep;
            String routeRrn;
            String routeId;

            sql.append("SELECT J.JOB_RRN,J.JOB_STATUS,J.TRANS_EXECUTION_ID,L.STAGE_ID,L.lot_owner,");
            sql.append("J.EXECUTION_RRN EXECUTION_RRN_IN_JOB,L.LOT_RRN,L.LOT_ID,L.LOT_TYPE,L.HOT_FLAG,L.PRIORITY,L" +
                               ".EXECUTION_RRN EXECUTION_RRN_IN_LOT,");
            sql.append("L.LOT_STATUS,L.QTY1,L.QTY2,L.QUEUE_TIMESTAMP,R.PROCESS_START_TIME,NVL(N.INSTANCE_ID," + "'N/A')" +
                               " CARRIER_ID,N.INSTANCE_RRN CARRIER_RRN,");
            sql.append("L.OPERATION_RRN,L.PRODUCT_RRN,L.OPERATION_VERSION,L.DUMMY_FLAG,L.DUMMY_QTY1,L.PROCESS_RRN," + "L" +
                               ".PROCESS_STEP_VERSION,L.PROCESS_STEP_ID_VERSION,L.recipe_string,L.EQPT_RRN, " +
                               "PROCESS_START_TIME,");
            sql.append("L.REWORK_TRANS_RRN,H.EXECUTION_RRN EXECUTION_RRN_IN_REWORK,L.RETICLE_RRN,L.PRODUCT_LAYER,L" +
                               ".PRODUCT_LAYER,L.LAYER_ID,L.RECIPE_LOGICAL_RRN,L.recipe_physical_id,L.FACILITY_RRN,L" +
                               ".PROCESS_VERSION,l.FLOW_SEQ,PCAD.RECIPE_PARAMETER ");
            sql.append("from (((((JOB J inner join LOT L on J.JOB_RRN = L.JOB_RRN )");
            sql.append("inner join NAMED_OBJECT N on L.CARRIER_RRN = N.INSTANCE_RRN ");
            sql.append("and N.OBJECT = 'ENTITY' and N.OBJECT_TYPE = 'CARRIER')");
            sql.append("left join RUN R on J.RUN_RRN = R.RUN_RRN) left join REWORK_INFO_HISTORY H ");
            sql.append("on L.REWORK_TRANS_RRN = H.TRANS_RRN and L.LOT_RRN = H.LOT_RRN) ");
            sql.append("left join PRO_CONTEXT_ACTIVE_DTL PCAD on L.PRODUCT_RRN = PCAD.PRODUCT_RRN ");
            sql.append("and L.PRODUCT_VERSION = PCAD.PRODUCT_VERSION ");
            sql.append("and L.PROCESS_RRN = PCAD.PROCESS_RRN ");
            sql.append("and L.PROCESS_VERSION = PCAD.PROCESS_VERSION ");
            sql.append("and L.FLOW_SEQ = PCAD.FLOW_SEQ )");
            if (type.equals(ObjectList.EQUIPMENT_KEY)) {
                sql.append(" WHERE J.EQPT_RRN = ? ");
            } else if (type.equals(ObjectList.OPERATION_KEY)) {
                sql.append(" WHERE J.OPERATION_RRN = ? ");
            } else {
                sql.append(" WHERE J.JOB_RRN = ? ");
            }

            String UOM1 = null;
            String UOM2 = null;
            long operRrn = 0;
            String operId = null;

            if (type.equals(ObjectList.OPERATION_KEY)) {
                operRrn = instanceRrn;
                UOM1 = MapUtils.getString(operationInfo, "UOM1");
                UOM2 = MapUtils.getString(operationInfo, "UOM2");
                operId = MapUtils.getString(operationInfo, "operId");
            } else if (type.equals(ObjectList.JOB_KEY)) {
                Job job = getJob(instanceRrn);
                UOM1 = job.getUOM1();
                UOM2 = job.getUOM2();
                operRrn = job.getOperationRrn();
                //            operId = job.getOperationId();
            }

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

            while (rs.next()) {
                Map jobItem = new HashMap<>();
                jobItem.put("facilityRrn", rs.getLong("FACILITY_RRN"));
                jobItem.put("processVersion", rs.getInt("PROCESS_VERSION"));
                jobItem.put("jobRrn", rs.getString("JOB_RRN"));
                jobItem.put("jobStatus", rs.getString("JOB_STATUS"));
                jobItem.put("transExecutionId", rs.getString("TRANS_EXECUTION_ID"));
                jobItem.put("executionRrn", rs.getString("EXECUTION_RRN_IN_JOB"));
                jobItem.put("lotRrn", rs.getString("LOT_RRN"));
                jobItem.put("lotId", rs.getString("LOT_ID"));
                jobItem.put("dummyFlag", rs.getString("DUMMY_FLAG"));
                jobItem.put("dummyQty1", rs.getString("DUMMY_QTY1"));
                jobItem.put("hotFlag", rs.getString("HOT_FLAG"));
                jobItem.put("priority", rs.getString("PRIORITY"));
                jobItem.put("lotType", rs.getString("LOT_TYPE"));
                jobItem.put("stageId", rs.getString("STAGE_ID"));
                jobItem.put("executionRrnLot", rs.getLong("EXECUTION_RRN_IN_LOT"));
                jobItem.put("lotOwner", rs.getString("lot_owner"));
                // if rework get execution rrn in rework info history
                if (rs.getLong("REWORK_TRANS_RRN") > 0) {
                    jobItem.put("lotExecutionRrn", rs.getString("EXECUTION_RRN_IN_REWORK"));
                } else {
                    jobItem.put("lotExecutionRrn", rs.getString("EXECUTION_RRN_IN_LOT"));
                }

                jobItem.put("lotStatus", rs.getString("LOT_STATUS"));
                jobItem.put("qty1", rs.getString("QTY1"));
                jobItem.put("qty2", rs.getString("QTY2"));

                long spent = 0;

                if (rs.getTimestamp("QUEUE_TIMESTAMP") == null) {
                    jobItem.put("queueTimestamp", null);
                } else {
                    spent = DateUtils.getDistanceTime4Now(rs.getTimestamp("QUEUE_TIMESTAMP"));
                    // mintues
                    jobItem.put("queueTimestamp", new Long(spent / 60).toString());
                }

                jobItem.put("processStartTime", DateUtils.formatDate(rs.getTimestamp("PROCESS_START_TIME")));
                jobItem.put("carrierRrn", rs.getString("CARRIER_RRN"));
                jobItem.put("carrierId", rs.getString("CARRIER_ID"));
                jobItem.put("processRrn", rs.getString("PROCESS_RRN"));

                // get route_rrn from processStepString
                processStepString = rs.getString("PROCESS_STEP_VERSION");
                firstSep = processStepString.indexOf("|");
                secondSep = processStepString.indexOf("|", firstSep + 1);
                if (secondSep >= 0) {
                    routeRrn = processStepString.substring(firstSep + 1, processStepString.indexOf(",", firstSep));
                } else {
                    routeRrn = processStepString.substring(0, processStepString.indexOf(","));
                }
                // get route_id from processStepIdString
                processStepIdString = rs.getString("PROCESS_STEP_ID_VERSION");
                firstSep = processStepIdString.indexOf("|");
                secondSep = processStepIdString.indexOf("|", firstSep + 1);
                if (secondSep >= 0) {
                    routeId = processStepIdString.substring(firstSep + 1, processStepIdString.indexOf(",", firstSep));
                } else {
                    routeId = processStepIdString.substring(0, processStepIdString.indexOf(","));
                }

                jobItem.put("routeRrn", routeRrn);
                jobItem.put("routeId", routeId);

                // get uom for equipment type
                if (type.equals(ObjectList.EQUIPMENT_KEY)) {
                    jobItem.put("operationRrn", rs.getLong("OPERATION_RRN"));

                } else {
                    //job里面有多个批次进站时，job表中只存入了其中一个批次的步骤RRN,所以不能从job表中获取
                    //jobItem.put("operationId", operId);
                    //jobItem.put("operationRrn", operRrn);
                    jobItem.put("operationRrn", rs.getLong("OPERATION_RRN"));
                    jobItem.put("UOM1", UOM1);
                    jobItem.put("UOM2", UOM2);
                }

                jobItem.put("productRrn", rs.getLong("PRODUCT_RRN"));
                jobItem.put("operationVersion", rs.getInt("OPERATION_VERSION"));
                jobItem.put("equipmentRrn", rs.getLong("EQPT_RRN"));

                jobItem.put("layerId", rs.getString("LAYER_ID"));
                jobItem.put("productLayer", rs.getString("PRODUCT_LAYER"));

                jobItem.put("recipeRrn", rs.getLong("RECIPE_LOGICAL_RRN"));
                jobItem.put("recipeParam", rs.getString("RECIPE_PARAMETER"));
                jobItem.put("recipePhysicalId", rs.getString("recipe_physical_id"));

                jobItem.put("reticleRrn", rs.getLong("RETICLE_RRN"));

                if (rs.getTimestamp("PROCESS_START_TIME") == null) {
                    jobItem.put("processingTime", null);
                } else {
                    spent = DateUtils.getDistanceTime4Now(rs.getTimestamp("PROCESS_START_TIME"));

                    // mintues
                    jobItem.put("processingTime", Long.toString(spent / 60));

                }
                jobItem.put("flowSeq", rs.getString("FLOW_SEQ"));
                jobList.add(jobItem);
            }

            return jobList;
        }

        @Override
        public Job getJob(long jobRrn) {

            String sql = "SELECT JOB_RRN,EQPT_RRN,OPERATION_RRN,RECIPE_STRING," + "JOB_STATUS,BEFORE_STATUS,JOB_COMMENTS," +
                    "RUN_RRN," + "TRANS_EXECUTION_ID,EXECUTION_RRN,CHAMBER_EQPTS_ID,PROCESS_STATUS" + " FROM " +
                    DataBaseNames.JOB + " WHERE JOB_RRN = ?";
            String sql1 = "SELECT O.UNIT_OF_MEASURE1,O.UNIT_OF_MEASURE2 FROM " + DataBaseNames.OPERATION + " O WHERE O" +
                    ".OPERATION_RRN=?";
            String sql2 = "SELECT E.CURRENT_STATUS FROM ENTITY_STATUS E WHERE E.ENTITY_RRN=?";

            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{jobRrn}, new RowMapper<Job>() {
                @Override
                public Job mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Job job = new Job();
                    job.setEqptStatus(jdbcTemplate.queryForObjectWithNull(sql2, String.class, rs.getLong("EQPT_RRN")));

                    job.setJobRrn(rs.getLong("JOB_RRN"));
                    job.setEqptRrn(rs.getLong("EQPT_RRN"));
                    job.setOperationRrn(rs.getLong("OPERATION_RRN"));
                    //                job.setOperationId(namedObjectDAO.getInstanceId(job.getOperationRrn()));

                    job.setRecipeString(rs.getString("RECIPE_STRING"));
                    job.setProcessStatus(rs.getString("PROCESS_STATUS"));
                    job.setChamberEqptsId(rs.getString("CHAMBER_EQPTS_ID"));

                    Map uom = jdbcTemplate.queryForObjectWithNull(sql1, Map.class, rs.getLong("OPERATION_RRN"));
                    if (MapUtils.isNotEmpty(uom)) {
                        job.setUOM1(MapUtils.getString(uom, "UNIT_OF_MEASURE1"));

                        job.setUOM2(MapUtils.getString(uom, "UNIT_OF_MEASURE2"));
                    }

                    job.setJobStatus(rs.getString("JOB_STATUS"));
                    job.setBeforeStatus(rs.getString("BEFORE_STATUS"));
                    job.setJobComments(rs.getString("JOB_COMMENTS"));
                    job.setRunRrn(rs.getLong("RUN_RRN"));
                    job.setTransExecutionId(rs.getString("TRANS_EXECUTION_ID"));
                    job.setExecutionRrn(rs.getLong("EXECUTION_RRN"));

                    return job;
                }
            });
        }

        @Override
        public long getJobRrn(long lotRrn) {
            String sql = "select t.job_rrn r from lot t where t.lot_rrn=?";
            return jdbcTemplate.queryForObject(sql, new Object[]{lotRrn}, long.class);
        }

        @Override
        public Page queryRunHistory(Page page, Long runRrn) {
            List<Object> args = new ArrayList<Object>();

            StringBuilder sql = new StringBuilder(
                    " SELECT lot.lot_id, lot.dummy_flag, l.carrier_rrn, l.carrier_map_rrn, l.reticle_rrn, l" + ".in_qty1," +
                            "l.out_qty1,r.process_start_time,r.process_end_time   ");
            sql.append("  FROM");
            sql.append(" RUN r, LOT lot, LOT_STEP_HISTORY l");
            sql.append(" WHERE r.run_rrn = ? AND r.run_rrn = l.run_rrn AND lot.lot_rrn = l.lot_rrn ");

            args.add(runRrn);

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

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

                    map.put("lot_id", rs.getString("LOT_ID"));
                    map.put("dummy_flag", rs.getString("DUMMY_FLAG"));

                    map.put("carrier_rrn", rs.getLong("CARRIER_RRN"));
                    map.put("carrier_map_rrn", rs.getLong("CARRIER_MAP_RRN"));
                    map.put("reticle_rrn", rs.getLong("RETICLE_RRN"));

                    map.put("in_qty1", rs.getString("IN_QTY1"));
                    map.put("out_qty1", rs.getString("OUT_QTY1"));

                    map.put("start_time", DateUtils.formatDate(rs.getTimestamp("PROCESS_START_TIME")));
                    map.put("end_time", DateUtils.formatDate(rs.getTimestamp("PROCESS_END_TIME")));
                    return map;
                }
            });
        }

        @Override
        public Run getRun(Long runRrn) {
            String sql = "select RUN_RRN, JOB_RRN, OPERATION_RRN, EQPT_RRN, RECIPE_STRING, RUN_STATUS, " +
                    "PROCESS_START_TIME, " + "PROCESS_END_TIME, MOVE_IN_DCOL_RRN, MOVE_OUT_DCOL_RRN ";
            sql += " FROM RUN";
            sql += " WHERE RUN_RRN=? ";
            Object[] args = new Object[]{new Long(runRrn)};
            return jdbcTemplate.queryForObjectWithNull(sql, args, new RowMapper<Run>() {
                @Override
                public Run mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Run run = new Run();
                    run.setRunRrn(rs.getLong("RUN_RRN"));
                    run.setJobRrn(rs.getLong("JOB_RRN"));
                    run.setOperationRrn(rs.getLong("OPERATION_RRN"));
                    run.setEqptRrn(rs.getLong("EQPT_RRN"));
                    run.setRecipeString(rs.getString("RECIPE_STRING"));
                    run.setRunStatus(rs.getString("RUN_STATUS"));
                    run.setProcessStartTime(rs.getTimestamp("PROCESS_START_TIME"));
                    run.setProcessEndTime(rs.getTimestamp("PROCESS_END_TIME"));
                    run.setMoveInDcolRrn(rs.getLong("MOVE_IN_DCOL_RRN"));
                    run.setMoveOutDcolRrn(rs.getLong("MOVE_OUT_DCOL_RRN"));

                    return run;
                }
            });
        }

        @Override
        public Run getRunByJob(long jobRrn) {
            String sql = "select RUN_RRN, JOB_RRN, OPERATION_RRN, EQPT_RRN, RECIPE_STRING, RUN_STATUS, " +
                    "PROCESS_START_TIME, " + "PROCESS_END_TIME, MOVE_IN_DCOL_RRN, MOVE_OUT_DCOL_RRN ";
            sql += " FROM RUN";
            sql += " WHERE JOB_RRN=? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{jobRrn}, new RowMapper<Run>() {
                @Override
                public Run mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Run run = new Run();
                    run.setRunRrn(rs.getLong("RUN_RRN"));
                    run.setJobRrn(rs.getLong("JOB_RRN"));
                    run.setOperationRrn(rs.getLong("OPERATION_RRN"));
                    run.setEqptRrn(rs.getLong("EQPT_RRN"));
                    run.setRecipeString(rs.getString("RECIPE_STRING"));
                    run.setRunStatus(rs.getString("RUN_STATUS"));
                    run.setProcessStartTime(rs.getTimestamp("PROCESS_START_TIME"));
                    run.setProcessEndTime(rs.getTimestamp("PROCESS_END_TIME"));
                    run.setMoveInDcolRrn(rs.getLong("MOVE_IN_DCOL_RRN"));
                    run.setMoveOutDcolRrn(rs.getLong("MOVE_OUT_DCOL_RRN"));

                    return run;
                }
            });
        }

        @Override
        public boolean checkRunningJobByEquip(long equipRrn, long jobRrn) {
            String sql = " SELECT count(*) FROM job j, lot l " + " WHERE l.job_rrn = j.job_rrn AND l.eqpt_rrn = j" +
                    ".eqpt_rrn AND j.job_status='RUNNING' " + " AND j.eqpt_rrn=" + equipRrn + " and j.job_rrn<>" + jobRrn;
            int count = jdbcTemplate.queryForObject(sql, int.class);
            return count>0;
        }

        @Override
        public boolean checkIsRunning4Chamber(Long entityParentRrn, Long jobRrn, String chamberId) {
            if (entityParentRrn == null || entityParentRrn <= 0 || jobRrn == null || jobRrn <= 0) {
                return false;
            }

            String countSql =
                    " SELECT COUNT(1) FROM (SELECT SUBSTR(L.RECIPE_PHYSICAL_ID,INSTR(L.RECIPE_PHYSICAL_ID,'-'," + "1,1)" +
                            "+1) AS SUB_RECIPE_ID FROM LOT L WHERE L.EQPT_RRN = ? AND L.JOB_RRN<>? ) K WHERE K" +
                            ".SUB_RECIPE_ID LIKE '%" + chamberId + "%'";

            int count = this.jdbcTemplate
                    .queryForObjectWithNull(countSql, new Object[]{entityParentRrn, jobRrn}, int.class);
            return count > 0;
        }

        @Override
        public String getSemiAutoParamsInExection(long exectionRrn) {
            String sql = " select execution_data from WORKFLOW_EXECUTION WHERE execution_rrn = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, String.class, exectionRrn);
        }

        @Override
        public boolean checkMainEqptIsRunning(long mainEqptRrn, List<Long> excludeJobRrn) {
            String sql = " select count(jb.JOB_RRN) from job jb left join Lot lt on jb.JOB_RRN=lt.JOB_RRN where jb.EQPT_RRN =? and lt.LOT_STATUS in ('%s','%s') ";
            sql = String.format(sql, LotStatus.RUNNING,LotStatus.RUNNINGHOLD);
            if(CollectionUtils.isNotEmpty(excludeJobRrn)){
                sql+=" and jb.JOB_RRN not in (%s)  ";
                String inParam=StringUtils.join(excludeJobRrn,",");
                sql = String.format(sql,inParam);
            }
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{mainEqptRrn}  , long.class)>0;
        }

    }