# LotAutoMonitorInqDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    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.server.automonitor.dao.LotAutoMonitorInqDao;
    import com.mycim.server.automonitor.dao.mapper.*;
    import com.mycim.valueobject.SystemConstant;
    import com.mycim.valueobject.automonitor.dto.LotAutoMonitorQueryDTO;
    import com.mycim.valueobject.automonitor.dto.MonitorJobQueryDTO;
    import com.mycim.valueobject.automonitor.entity.*;
    import com.mycim.valueobject.automonitor.util.MonitorJobStatusConts;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.wip.LotStatus;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.util.*;
    import java.util.stream.Collectors;

    /**
     * @author finatice.yang
     * @date 2021/8/27
     **/
    @Repository
    public class LotAutoMonitorInqDaoImpl implements LotAutoMonitorInqDao {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public boolean checkLotIsAutoMonitorLot(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(LOT_RRN) FROM ");
            sql.append(DataBaseNames.AUTO_MONITOR_LOT_STORE);
            sql.append(" WHERE lot_rrn = :lotRrn ");
            // sql.append(" or (base_lot_rrn = :lotRrn and JOB_STATUS NOT IN (:jobStatus)) ");

            Map<String, Object> paramMap = MapUtils.newHashMap();
            paramMap.put("lotRrn", lotRrn);
            paramMap.put("jobStatus", Arrays.asList(MonitorJobStatusConts.getCloseJobStatus()));

            Long count = jdbcTemplate.queryForObject(sql.toString(), paramMap, Long.class);
            return Objects.nonNull(count) && count.longValue() > 0;
        }

        @Override
        public boolean checkLotInAutoMonitorBaseLot(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(LOT_RRN) FROM ");
            sql.append(DataBaseNames.AUTO_MONITOR_LOT_STORE);
            sql.append(" WHERE BASE_LOT_RRN = :lotRrn AND JOB_STATUS NOT IN (:jobStatus)");

            Map<String, Object> paramMap = MapUtils.newHashMap();
            paramMap.put("lotRrn", lotRrn);
            paramMap.put("jobStatus", Arrays.asList(MonitorJobStatusConts.getCloseJobStatus()));

            Long count = jdbcTemplate.queryForObject(sql.toString(), paramMap, Long.class);
            return Objects.nonNull(count) && count.longValue() > 0;
        }

        @Override
        public LotMonitorJobStore getLotMonitorJobStore(Long lotRrn) {
            StringBuilder sql = new StringBuilder(getMonitorJobSql());
            sql.append(" WHERE STORE.LOT_RRN = ? ");

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

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), args, new LotMonitorJobStoreMapper());
        }

        @Override
        public Page getMonitorJobListPage(Page page, MonitorJobQueryDTO queryDTO) {
            StringBuilder sql = new StringBuilder(getMonitorJobSql());
            sql.append(" WHERE 1=1 ");

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

            if (StringUtils.isNotBlank(queryDTO.getEqptId())) {
                sql.append(" AND STORE.EQUIPMENT_ID LIKE ? ");
                argList.add(queryDTO.getEqptId());
            }

            if (StringUtils.isNotBlank(queryDTO.getItemType())) {
                sql.append(" AND STORE.ITEM_TYPE like ? ");
                argList.add(queryDTO.getItemType());
            }

            if (StringUtils.isNotBlank(queryDTO.getProductId())) {
                sql.append(" AND L.PRODUCT_ID like ? ");
                argList.add(queryDTO.getProductId());
            }

            if(StringUtils.isNotBlank(queryDTO.getCarrierId())){
                sql.append(" AND L.CARRIER_ID LIKE ? ");
                argList.add(queryDTO.getCarrierId());
            }

            if(StringUtils.isNotBlank(queryDTO.getLotId())) {
                sql.append(" AND L.LOT_ID LIKE ? ");
                argList.add(queryDTO.getLotId());
            }

            if (CollectionUtils.isNotEmpty(queryDTO.getJobStatus())) {
                if (!queryDTO.getJobStatus().contains(SystemConstant.Str.ALL)) {
                    String statusList = queryDTO.getJobStatus().stream().map(status -> {
                        return "'" + status + "'";
                    }).collect(Collectors.joining(StringUtils.COMMA_SIGN));
                    sql.append(" AND STORE.JOB_STATUS IN (").append(statusList).append(")");
                }
            }

            return jdbcTemplate.queryForPage(page, sql.toString(), argList.toArray(), new LotMonitorJobStoreMapper());
        }

        @Override
        public LotAutoMonitorInfo getLotActiveAutoMonitorInfo(Long lotRrn) {
            StringBuilder sb = getLotMonitorInfoSql();
            sb.append(" WHERE JOB.JOB_STATUS NOT IN ('CANCEL','CLOSED') ");
            sb.append(" AND L.LOT_RRN = ?");

            Object[] args = new Object[]{lotRrn};
            return jdbcTemplate.queryForObjectWithNull(sb.toString(), args, new LotAutoMonitorInfoMapper());
        }

        @Override
        public LotAutoMonitorInfo getLotAutoMonitorInfo(Long lotRrn) {
            StringBuilder sb = getLotMonitorInfoSql();
            sb.append(" WHERE L.LOT_RRN = ?");

            Object[] args = new Object[]{lotRrn};
            return jdbcTemplate.queryForObjectWithNull(sb.toString(), args, new LotAutoMonitorInfoMapper());
        }

        @Override
        public Page getLotMonitorLotPage(Page page, LotAutoMonitorQueryDTO queryDTO) {
            StringBuilder sb = getLotMonitorInfoSql();
            sb.append(" WHERE 1=1 ");

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

            if (StringUtils.isNotEmpty(queryDTO.getLotId())) {
                sb.append(" AND L.LOT_ID LIKE ? ");
                argList.add(queryDTO.getLotId());
            }
            if (StringUtils.isNotEmpty(queryDTO.getCarrierId())) {
                sb.append(" AND L.CARRIER_ID LIKE ? ");
                argList.add(queryDTO.getCarrierId());
            }
            if (StringUtils.isNotEmpty(queryDTO.getMainEqptId())) {
                sb.append(" AND item.EQUIPMENT_ID LIKE ? ");
                argList.add(queryDTO.getMainEqptId());
            }
            if (StringUtils.isNotEmpty(queryDTO.getMainEqptLocation())) {
                sb.append(" AND ITEM.EQUIPMENT_LOCATION LIKE ? ");
                argList.add(queryDTO.getMainEqptLocation());
            }
            if (StringUtils.isNotEmpty(queryDTO.getItemType())) {
                sb.append(" AND item.item_type LIKE ? ");
                argList.add(queryDTO.getItemType());
            }
            if (StringUtils.isNotEmpty(queryDTO.getProductId())) {
                sb.append(" AND L.PRODUCT_ID LIKE ?");
                argList.add(queryDTO.getProductId());
            }

            if (CollectionUtils.isNotEmpty(queryDTO.getLotStatus())) {
                if (!queryDTO.getLotStatus().contains(SystemConstant.Str.ALL)) {
                    String statusList = queryDTO.getLotStatus().stream().map(status -> {
                        return "'" + status + "'";
                    }).collect(Collectors.joining(StringUtils.COMMA_SIGN));
                    sb.append(" AND L.LOT_STATUS IN (").append(statusList).append(")");
                }
            } else {
                sb.append(" AND JOB.JOB_STATUS NOT IN (?,?) ");
                argList.addAll(Arrays.asList(MonitorJobStatusConts.getCloseJobStatus()));
            }

            if (CollectionUtils.isNotEmpty(queryDTO.getAutoMonitorJobStatus())) {
                if (!queryDTO.getAutoMonitorJobStatus().contains(SystemConstant.Str.ALL)) {
                    String statusList = queryDTO.getAutoMonitorJobStatus().stream().map(status -> {
                        return "'" + status + "'";
                    }).collect(Collectors.joining(StringUtils.COMMA_SIGN));
                    sb.append(" AND JOB.JOB_STATUS IN (").append(statusList).append(")");
                }
            }

            return jdbcTemplate.queryForPage(page, sb.toString(), argList.toArray(), new LotAutoMonitorInfoMapper());
        }

        @Override
        public List<LotAutoMonitorInfo> getLotMonitorLotList(LotAutoMonitorQueryDTO queryDTO) {
            StringBuilder sb = getLotMonitorInfoSql();
            sb.append(" WHERE JOB.JOB_STATUS NOT IN (:jobStatus) ");

            Map<String, Object> paramMap = MapUtils.newHashMap();
            paramMap.put("jobStatus", Arrays.asList(MonitorJobStatusConts.getCloseJobStatus()));

            if (CollectionUtils.isNotEmpty(queryDTO.getLotRrns())) {
                sb.append(" AND L.LOT_RRN IN (:lotRrns) ");
                paramMap.put("lotRrns", queryDTO.getLotRrns());
            }

            if (queryDTO.getCarrierRrn() != null && queryDTO.getCarrierRrn().longValue() > 0) {
                sb.append(" AND L.carrier_rrn = :carrierRrn ");
                paramMap.put("carrierRrn", queryDTO.getCarrierRrn());
            }

            if (queryDTO.getBaseLotRrn() != null && queryDTO.getBaseLotRrn().longValue() > 0) {
                sb.append(" AND job.base_lot_rrn = :baseLotRrn ");
                paramMap.put("baseLotRrn", queryDTO.getBaseLotRrn());
            }

            return jdbcTemplate.query(sb.toString(), paramMap, new LotAutoMonitorInfoMapper());
        }

        @Override
        public List<MonitorCarrierMapping> getMonitorCarrierMappings(Long monitorCarrierRrn) {
            StringBuffer sql = new StringBuffer("SELECT ");
            sql.append(" MONITOR_CARRIER_RRN,MONITOR_CARRIER_ID,UNIT_RRN,UNIT_ID,POSITION,SOURCE_POSITION,LOT_RRN,LOT_ID ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_CARRIER_MAPPING);
            sql.append(" WHERE MONITOR_CARRIER_RRN = ? ");
            return jdbcTemplate.query(sql.toString(), new Object[]{monitorCarrierRrn}, new MonitorCarrierMappingMapper());
        }

        @Override
        public List<LotMonitorJobStepEdcInfo> getStepEdcInfoList(Long transRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" TRANS_RRN,LOT_RRN,LOT_ID,STEP_SEQUENCE,SEQUENCE,UNIT_RRN,UNIT_ID, ");
            sql.append(" SPC_JOB_ID,SPC_JOB_NAME,DATA_VALUE,FORMULA,VIO_INFO ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_LOT_EDC_INFO);
            sql.append(" WHERE TRANS_RRN = ?");
            return jdbcTemplate.query(sql.toString(), new Object[]{transRrn}, new LotAutoMonitorEdcInfoMapper());
        }

        @Override
        public Integer getLotStepEdcCount(Long lotRrn,Long stepSequence) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" count(sequence) FROM ").append(DataBaseNames.AUTO_MONITOR_LOT_EDC_INFO);
            sql.append(" WHERE LOT_RRN = ? AND STEP_SEQUENCE = ? ");
            return jdbcTemplate.queryForObjectWithNull(sql.toString(),new Object[]{lotRrn,stepSequence},Integer.class);
        }

        @Override
        public List<String> getFinishLotIdList(Long baseLotRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" AMS.LOT_ID FROM ").append(DataBaseNames.AUTO_MONITOR_LOT_STORE).append(" AMS ");
            sql.append(" JOIN ").append(DataBaseNames.LOT).append(" L ");
            sql.append(" ON AMS.LOT_RRN = L.LOT_RRN ");
            sql.append(" WHERE AMS.BASE_LOT_RRN = :baseLotRrn ");
            sql.append(" AND L.LOT_STATUS = :lotStatus");
            sql.append(" AND AMS.JOB_STATUS NOT IN (:jobStatus) ");

            Map<String, Object> paramMap = MapUtils.newHashMap();
            paramMap.put("baseLotRrn", baseLotRrn);
            paramMap.put("lotStatus", LotStatus.AUTO_MONITOR_JOB_FINISH);
            paramMap.put("jobStatus", Arrays.asList(MonitorJobStatusConts.getCloseJobStatus()));

            return jdbcTemplate.query(sql.toString(), paramMap, String.class);
        }

        @Override
        public List<AutoMonitorUnitInfo> getAutoMonitorUnitInfoList(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("UNIT_RRN,UNIT_ID,LOT_RRN,LOT_ID,CARRIER_RRN,CARRIER_ID,POSITION,");
            sql.append("MONITOR_CARRIER_RRN,MONITOR_CARRIER_ID,MONITOR_CARRIER_POSITION ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_UNIT);
            sql.append(" WHERE LOT_RRN = ?");
            return jdbcTemplate.query(sql.toString(), new AutoMonitorUnitInfoMapper(), lotRrn);
        }

        @Override
        public Page getAutoMonitorJobHistoryPage(Page page, MonitorJobQueryDTO queryDTO) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" trans_rrn,trans_id,trans_user_id,trans_time,trans_comments,");
            sql.append(" lot_rrn,lot_id,base_lot_rrn,base_lot_id,equipment_rrn,equipment_id,item_type,qty1,");
            sql.append(" process_rrn,process_id,process_version,product_rrn,product_id,");
            sql.append(" job_status,workflow_rrn,workflow_version,workflow_id,wfl_step,");
            sql.append(" monitor_carrier_rrn,monitor_carrier_id,carrier_rrn,carrier_id,merge_wfl_step ");
            sql.append(" from ").append(DataBaseNames.AUTO_MONITOR_LOT_STORE_H);
            sql.append(" WHERE 1=1 ");

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

            if (StringUtils.isNotBlank(queryDTO.getEqptId())) {
                sql.append(" AND EQUIPMENT_ID LIKE ? ");
                argList.add(queryDTO.getEqptId());
            }

            if (StringUtils.isNotBlank(queryDTO.getItemType())) {
                sql.append(" AND ITEM_TYPE like ? ");
                argList.add(queryDTO.getItemType());
            }

            if (StringUtils.isNotBlank(queryDTO.getProductId())) {
                sql.append(" AND PRODUCT_ID like ? ");
                argList.add(queryDTO.getProductId());
            }

            if (StringUtils.isNotBlank(queryDTO.getCarrierId())) {
                sql.append(" AND CARRIER_ID LIKE ? ");
                argList.add(queryDTO.getCarrierId());
            }

            if (StringUtils.isNotBlank(queryDTO.getLotId())) {
                sql.append(" AND LOT_ID LIKE ? ");
                argList.add(queryDTO.getLotId());
            }

            sql.append(" order by trans_time desc");
            return jdbcTemplate.queryForPage(page, sql.toString(), argList.toArray(), new AutoMonitorJobHistoryMapper());
        }

        private String getMonitorJobSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" STORE.LOT_RRN,L.LOT_ID,L.CARRIER_RRN,L.CARRIER_ID,STORE.BASE_LOT_RRN,STORE.BASE_LOT_ID,");
            sql.append(" STORE.EQUIPMENT_RRN,STORE.EQUIPMENT_ID,STORE.ITEM_TYPE,STORE.QTY1,L.PRODUCT_RRN,L.PRODUCT_ID, ");
            sql.append(" STORE.PROCESS_RRN,STORE.PROCESS_ID,STORE.PROCESS_VERSION,STORE.JOB_STATUS,STORE.MERGE_WFL_STEP, ");
            sql.append(" STORE.WORKFLOW_RRN,STORE.WORKFLOW_VERSION,STORE.WFL_STEP, ");
            sql.append(" STORE.MONITOR_CARRIER_RRN,STORE.MONITOR_CARRIER_ID ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_LOT_STORE).append(" STORE ");
            sql.append(" LEFT JOIN ").append(" ( ");
            sql.append(" SELECT L.LOT_RRN,L.LOT_ID,L.CARRIER_RRN,C.CARRIER_ID,L.QTY1,L.PRODUCT_RRN,P.PRODUCT_ID ");
            sql.append(" FROM ").append(DataBaseNames.LOT).append(" L ");
            sql.append(" LEFT JOIN ").append(DataBaseNames.CARRIER).append(" C ON L.CARRIER_RRN = C.CARRIER_RRN ");
            sql.append(" LEFT JOIN ").append(DataBaseNames.PRODUCT).append(" P ON L.PRODUCT_RRN = P.SYS_RRN ");
            sql.append(" ) L ").append(" ON STORE.LOT_RRN = L.LOT_RRN ");

            return sql.toString();
        }

        private StringBuilder getLotMonitorInfoSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("L.LOT_RRN,L.LOT_ID,L.CARRIER_RRN,L.CARRIER_ID,L.QTY1,L.LOT_STATUS,L.PRODUCT_ID,");
            sql.append("l.lot_flow_seq,l.lot_stage_id,");
            sql.append("JOB.BASE_LOT_ID,JOB.BASE_LOT_RRN,JOB.JOB_STATUS,JOB.MONITOR_CARRIER_RRN,JOB.MONITOR_CARRIER_ID,");
            sql.append("ITEM.EQUIPMENT_RRN AS MAIN_EQUIPMENT_RRN,ITEM.EQUIPMENT_ID AS MAIN_EQUIPMENT_ID,");
            sql.append("ITEM.EQUIPMENT_LOCATION,item.item_type,item.workflow_rrn,item.workflow_version,");
            sql.append("ITEM.START_SPLIT_FLAG,");
            sql.append("ITEM.MAIN_EQUIPMENT_TYPE,STEP.STEP_SEQUENCE,STEP.EQUIPMENT_TYPE,step.flow_seq,");
            sql.append("step.operation_desc,step.recipe_id,step.stage_id,STEP.NONRT_SLOT_TYPE,");
            sql.append("STEP.EQUIPMENT_RRN,STEP.EQUIPMENT_ID,STEP.EQUIPMENT_GROUP_RRN,STEP.EQUIPMENT_GROUP_ID,");
            sql.append("STEP.RETICLE_RRN,STEP.RETICLE_ID, STEP.RETICLE_GROUP_RRN,STEP.RETICLE_GROUP_ID ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_LOT_STORE).append(" JOB ");
            sql.append(" LEFT JOIN ");
            sql.append(" (SELECT LOT.LOT_RRN,LOT.LOT_ID,LOT.CARRIER_RRN,LOT.QTY1,LOT.LOT_STATUS,LOT.OPERATION_RRN, ");
            sql.append("  lot.flow_seq as lot_flow_seq, lot.stage_id as lot_stage_id, ");
            sql.append("  P.PRODUCT_ID,C.CARRIER_ID FROM ").append(DataBaseNames.LOT);
            sql.append("  LEFT JOIN ").append(DataBaseNames.CARRIER).append(" C ON LOT.CARRIER_RRN = C.CARRIER_RRN ");
            sql.append("  LEFT JOIN ").append(DataBaseNames.PRODUCT).append(" P ON LOT.PRODUCT_RRN = P.SYS_RRN ");
            sql.append(" ) L ON JOB.LOT_RRN = L.LOT_RRN");
            sql.append(" LEFT JOIN");
            sql.append(" (SELECT ITEM.WORKFLOW_RRN,ITEM.WORKFLOW_VERSION,ITEM.EQUIPMENT_RRN,ITEM.EQUIPMENT_ID,");
            sql.append("  E.EQUIPMENT_LOCATION,ITEM.ITEM_TYPE,ITEM.MAIN_EQUIPMENT_TYPE,ITEM.START_SPLIT_FLAG ");
            sql.append("  FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM).append(" ITEM");
            sql.append("  LEFT JOIN ").append(DataBaseNames.EQUIPMENT).append(" E ON ITEM.EQUIPMENT_RRN = E.EQPT_RRN ");
            sql.append(" ) ITEM ON JOB.WORKFLOW_RRN = ITEM.WORKFLOW_RRN AND JOB.WORKFLOW_VERSION = ITEM.WORKFLOW_VERSION ");
            sql.append(" LEFT JOIN ").append(DataBaseNames.AUTO_MONITOR_ITEM_STEP).append(" STEP ");
            sql.append(" ON JOB.WORKFLOW_RRN = STEP.WORKFLOW_RRN AND JOB.WORKFLOW_VERSION = STEP.WORKFLOW_VERSION ");
            sql.append(" AND L.OPERATION_RRN = STEP.OPERATION_RRN ");
            return sql;
        }

    }