# AutoMonitorItemInqDaoImpl.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.server.automonitor.dao.AutoMonitorItemInqDao;
    import com.mycim.server.automonitor.dao.mapper.AutoMonitorItemMapper;
    import com.mycim.server.automonitor.dao.mapper.AutoMonitorItemStepEdcInfoMapper;
    import com.mycim.server.automonitor.dao.mapper.AutoMonitorItemStepMapper;
    import com.mycim.valueobject.automonitor.dto.AutoMonitorItemQueryDTO;
    import com.mycim.valueobject.automonitor.entity.AutoMonitorItem;
    import com.mycim.valueobject.automonitor.entity.AutoMonitorItemStep;
    import com.mycim.valueobject.automonitor.entity.AutoMonitorItemStepEdcInfo;
    import com.mycim.valueobject.consts.DataBaseNames;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

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

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public AutoMonitorItem getAutoMonitorActiveItem(Long eqptRrn, String itemType) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" AMIS.WORKFLOW_RRN,AMIS.WORKFLOW_VERSION,AMIS.WORKFLOW_ID,AMIS.equipment_rrn,AMIS.equipment_id, ");
            sql.append(" AMIS.ITEM_TYPE, AMIS.QTY,AMIS.PRODUCT_ID, AMIS.PRODUCT_RRN, AMIS.PROCESS_ID, AMIS.PROCESS_RRN, ");
            sql.append(" AMIS.MONITOR_CARRIER_FLAG,AMIS.MONITOR_CARRIER_TYPE,AMIS.SYS_INACTIVE_FLAG, ");
            sql.append(" AMIS.START_SPLIT_FLAG,");
            sql.append(" AMIS.main_equipment_type,WORK_N.OBJ_CURRENT_VERSION, WORK_N.OBJ_ACTIVE_VERSION ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM).append(" AMIS ");
            sql.append(" JOIN ").append(DataBaseNames.ROUTE).append(" WORK_N ON ");
            sql.append(" AMIS.WORKFLOW_RRN = WORK_N.SYS_RRN AND AMIS.WORKFLOW_VERSION = WORK_N.OBJ_ACTIVE_VERSION ");
            sql.append(" WHERE AMIS.EQUIPMENT_RRN = ? AND AMIS.ITEM_TYPE = ? ");

            Object[] args = new Object[]{eqptRrn, itemType};

            return jdbcTemplate.queryForObject(sql.toString(), args, new AutoMonitorItemMapper());
        }

        @Override
        public AutoMonitorItem getAutoMonitorItem(Long eqptRrn, String itemType) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" AMIS.WORKFLOW_RRN,AMIS.WORKFLOW_VERSION,AMIS.WORKFLOW_ID,AMIS.equipment_rrn,AMIS.equipment_id, ");
            sql.append(" AMIS.ITEM_TYPE, AMIS.QTY,AMIS.PRODUCT_ID, AMIS.PRODUCT_RRN, AMIS.PROCESS_ID, AMIS.PROCESS_RRN, ");
            sql.append(" AMIS.MONITOR_CARRIER_FLAG,AMIS.MONITOR_CARRIER_TYPE,AMIS.SYS_INACTIVE_FLAG, ");
            sql.append(" AMIS.START_SPLIT_FLAG,");
            sql.append(" AMIS.main_equipment_type,WORK_N.OBJ_CURRENT_VERSION, WORK_N.OBJ_ACTIVE_VERSION ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM).append(" AMIS ");
            sql.append(" JOIN ").append(DataBaseNames.ROUTE).append(" WORK_N ON ");
            sql.append(" AMIS.WORKFLOW_RRN = WORK_N.SYS_RRN AND AMIS.WORKFLOW_VERSION = WORK_N.OBJ_CURRENT_VERSION ");
            sql.append(" WHERE AMIS.EQUIPMENT_RRN = ? AND AMIS.ITEM_TYPE = ? ");

            Object[] args = new Object[]{eqptRrn, itemType};

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

        @Override
        public AutoMonitorItem getAutoMonitorItem(Long workflowRrn, Integer workflowVersion) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" AMIS.WORKFLOW_RRN,AMIS.WORKFLOW_VERSION,AMIS.WORKFLOW_ID,AMIS.equipment_rrn,AMIS.equipment_id, ");
            sql.append(" AMIS.ITEM_TYPE, AMIS.QTY,AMIS.PRODUCT_ID, AMIS.PRODUCT_RRN, AMIS.PROCESS_ID, AMIS.PROCESS_RRN, ");
            sql.append(" AMIS.MONITOR_CARRIER_FLAG,AMIS.MONITOR_CARRIER_TYPE,AMIS.SYS_INACTIVE_FLAG, ");
            sql.append(" AMIS.START_SPLIT_FLAG,");
            sql.append(" AMIS.main_equipment_type,WORK_N.OBJ_CURRENT_VERSION, WORK_N.OBJ_ACTIVE_VERSION ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM).append(" AMIS ");
            sql.append(" JOIN ").append(DataBaseNames.ROUTE).append(" WORK_N ON ");
            sql.append(" AMIS.WORKFLOW_RRN = WORK_N.SYS_RRN ");
            sql.append(" WHERE AMIS.WORKFLOW_RRN = ? AND AMIS.WORKFLOW_VERSION = ? ");

            Object[] args = new Object[]{workflowRrn, workflowVersion};

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

        @Override
        public List<AutoMonitorItem> getAutoMonitorItemList(AutoMonitorItemQueryDTO conditions) {
            List<Object> argList = new ArrayList<>();
            String sql = buildAutoMonitorItemListSqlArgs(conditions, argList);
            return jdbcTemplate.query(sql, argList.toArray(), new AutoMonitorItemMapper());
        }

        @Override
        public Page getAutoMonitorItemPage(Page page, AutoMonitorItemQueryDTO conditions) {
            List<Object> argList = new ArrayList<>();
            String sql = buildAutoMonitorItemListSqlArgs(conditions, argList);
            return jdbcTemplate.queryForPage(page, sql, argList.toArray(), new AutoMonitorItemMapper());
        }

        @Override
        public List<String> getAutoMonitorItemTypeList(Long eqptRrn) {
            StringBuilder sql = new StringBuilder("SELECT AMIS.ITEM_TYPE FROM ");
            sql.append(DataBaseNames.AUTO_MONITOR_ITEM).append(" AMIS ");
            sql.append(" JOIN ").append(DataBaseNames.ROUTE).append(" WORK_N ON ");
            sql.append(" AMIS.WORKFLOW_RRN = WORK_N.SYS_RRN AND AMIS.WORKFLOW_VERSION = WORK_N.OBJ_ACTIVE_VERSION ");
            sql.append(" AND AMIS.EQUIPMENT_RRN = ? ");
            sql.append(" AND (AMIS.SYS_INACTIVE_FLAG = 0 OR AMIS.SYS_INACTIVE_FLAG IS NULL)");

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

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

        @Override
        public AutoMonitorItemStep getAutoMonitorItemStep(Long workflowRrn, Integer workflowVersion, Long operationRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" WORKFLOW_RRN,WORKFLOW_VERSION,WORKFLOW_ID,STEP_SEQUENCE,EQUIPMENT_TYPE,MAIN_EQUIPMENT_FLAG, ");
            sql.append(" EQUIPMENT_ID,EQUIPMENT_RRN,EQUIPMENT_GROUP_ID,EQUIPMENT_GROUP_RRN,NONRT_SLOT_TYPE, ");
            sql.append(" FLOW_SEQ,OPERATION_RRN,OPERATION_ID,OPERATION_DESC,RECIPE_ID,RECIPE_RRN,STAGE_ID, ");
            sql.append(" RETICLE_ID,RETICLE_RRN,RETICLE_GROUP_ID,RETICLE_GROUP_RRN ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM_STEP);
            sql.append(" WHERE WORKFLOW_RRN = ? AND WORKFLOW_VERSION = ? AND OPERATION_RRN = ?");

            Object[] args = new Object[]{workflowRrn, workflowVersion, operationRrn};

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

        @Override
        public List<AutoMonitorItemStep> getAutoMonitorItemSteps(Long workflowRrn, Integer workflowVersion) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" WORKFLOW_RRN,WORKFLOW_VERSION,WORKFLOW_ID,STEP_SEQUENCE,EQUIPMENT_TYPE,MAIN_EQUIPMENT_FLAG, ");
            sql.append(" EQUIPMENT_ID,EQUIPMENT_RRN,EQUIPMENT_GROUP_ID,EQUIPMENT_GROUP_RRN,NONRT_SLOT_TYPE, ");
            sql.append(" FLOW_SEQ,OPERATION_RRN,OPERATION_ID,OPERATION_DESC,RECIPE_ID,RECIPE_RRN,STAGE_ID, ");
            sql.append(" RETICLE_ID,RETICLE_RRN,RETICLE_GROUP_ID,RETICLE_GROUP_RRN ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM_STEP);
            sql.append(" WHERE WORKFLOW_RRN = ? AND WORKFLOW_VERSION = ? ");

            Object[] args = new Object[] { workflowRrn, workflowVersion };

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

        @Override
        public List<AutoMonitorItemStepEdcInfo> getItemStepEdcInfo(Long workflowRrn, Integer workflowVersion) {

            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" WORKFLOW_RRN, WORKFLOW_VERSION, STEP_SEQUENCE, WAFER_SLOT, NONRT_JOB_IDS, NONRT_JOB_NAMES ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM_STEP_EDC);
            sql.append(" WHERE WORKFLOW_RRN = ? AND WORKFLOW_VERSION = ? ORDER BY STEP_SEQUENCE,WAFER_SLOT ");

            Object[] args = new Object[] { workflowRrn, workflowVersion };

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

        @Override
        public List<AutoMonitorItemStepEdcInfo> getItemStepEdcInfo(Long workflowRrn, Integer workflowVersion,
                                                                   Long stepSequence) {

            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" WORKFLOW_RRN, WORKFLOW_VERSION, STEP_SEQUENCE, WAFER_SLOT, NONRT_JOB_IDS, NONRT_JOB_NAMES ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM_STEP_EDC);
            sql.append(" WHERE WORKFLOW_RRN = ? AND WORKFLOW_VERSION = ? AND STEP_SEQUENCE = ?  ORDER BY WAFER_SLOT ");

            Object[] args = new Object[] { workflowRrn, workflowVersion, stepSequence };

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

        private String buildAutoMonitorItemListSqlArgs(AutoMonitorItemQueryDTO conditions, List<Object> argList) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" AMIS.WORKFLOW_RRN,AMIS.WORKFLOW_VERSION,AMIS.WORKFLOW_ID,AMIS.equipment_rrn,AMIS.equipment_id, ");
            sql.append(" AMIS.ITEM_TYPE, AMIS.QTY,AMIS.PRODUCT_ID, AMIS.PRODUCT_RRN, AMIS.PROCESS_ID, AMIS.PROCESS_RRN, ");
            sql.append(" AMIS.MONITOR_CARRIER_FLAG,AMIS.MONITOR_CARRIER_TYPE,AMIS.SYS_INACTIVE_FLAG, ");
            sql.append(" AMIS.START_SPLIT_FLAG,");
            sql.append(" AMIS.main_equipment_type,WORK_N.OBJ_CURRENT_VERSION, WORK_N.OBJ_ACTIVE_VERSION ");
            sql.append(" FROM ").append(DataBaseNames.AUTO_MONITOR_ITEM).append(" AMIS ");
            sql.append(" JOIN ").append(DataBaseNames.ROUTE).append(" WORK_N ON ");
            sql.append(" AMIS.WORKFLOW_RRN = WORK_N.SYS_RRN ");
            sql.append(" JOIN (SELECT WORKFLOW_RRN,MAX(WORKFLOW_VERSION) AS MAX_VERSION FROM ");
            sql.append(DataBaseNames.AUTO_MONITOR_ITEM).append(" GROUP BY WORKFLOW_RRN) FLOW_VERSION ON ");
            sql.append(" AMIS.WORKFLOW_RRN=FLOW_VERSION.WORKFLOW_RRN AND AMIS.WORKFLOW_VERSION=FLOW_VERSION.MAX_VERSION ");

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

            if (StringUtils.isNotBlank(conditions.getItemType())) {
                sql.append(" AND AMIS.ITEM_TYPE LIKE ? ");
                argList.add(conditions.getItemType());
            }

            if (StringUtils.isNotBlank(conditions.getProductId())) {
                sql.append(" AND AMIS.PRODUCT_ID LIKE ? ");
                argList.add(conditions.getProductId());
            }

            if (StringUtils.isNotBlank(conditions.getMainEqptType())) {
                sql.append(" AND AMIS.MAIN_EQUIPMENT_TYPE = ? ");
                argList.add(conditions.getMainEqptType());
            }
            return sql.toString();
        }

    }