# DiffBatchQueryDAOImpl.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.MapUtils;
    import com.mycim.framework.utils.lang.math.NumberUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.wip.dao.DiffBatchQueryDAO;
    import com.mycim.server.wip.dao.mapper.BatchLotStoreMapper;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.wip.BatchLotStore;
    import com.mycim.valueobject.wip.EqpRcpDiffBatchSetupInfo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;
    import org.springframework.util.CollectionUtils;

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

    /**
     * @author finatice.yang
     * @date 2021/6/30
     **/
    @Repository
    public class DiffBatchQueryDAOImpl implements DiffBatchQueryDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        private StringBuilder getBatchLotStoreBaseSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("BASE.BATCH_ID,BASE.LOT_ID,BASE.LOT_RRN,BASE.MONITOR_LOT_ID,BASE.MONITOR_LOT_RRN, ");
            sql.append("BASE.SEQUENCE,BASE.EQPT_ID,BASE.EQPT_RRN,BASE.RECIPE_ID,BASE.RECIPE_RRN ");
            sql.append("FROM batch_lot_store base ");
            return sql;
        }

        @Override
        public BatchLotStore getBatchLotStoreByToRrn(Long lotRrn) {
            StringBuilder sql = getBatchLotStoreBaseSql();
            sql.append(" WHERE BASE.LOT_RRN = ?");
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{lotRrn}, new BatchLotStoreMapper());
        }

        @Override
        public List<BatchLotStore> getBatchLotStore(String batchId) {
            StringBuilder sql = getBatchLotStoreBaseSql();
            sql.append(" WHERE BASE.BATCH_ID = ? order by BASE.sequence");
            return jdbcTemplate.query(sql.toString(), new Object[]{batchId}, new BatchLotStoreMapper());
        }

        @Override
        public List<BatchLotStore> getBatchLotStore(long monitorRrn) {
            StringBuilder sql = getBatchLotStoreBaseSql();
            sql.append(" WHERE BASE.MONITOR_LOT_RRN = ? order by BASE.sequence");
            return jdbcTemplate.query(sql.toString(), new Object[]{monitorRrn}, new BatchLotStoreMapper());
        }

        @Override
        public boolean checkLotInBatch(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(LOT_RRN) FROM ");
            sql.append(DataBaseNames.BATCH_LOT_STORE).append(" WHERE LOT_RRN = ? OR MONITOR_LOT_RRN = ?");

            Long count = jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{lotRrn,lotRrn}, Long.class);
            return Objects.nonNull(count) && count.longValue() > 0;
        }

        @Override
        public boolean checkLotIsBatchMonitorLot(Long monitorLotRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(LOT_RRN) FROM ");
            sql.append(DataBaseNames.BATCH_LOT_STORE).append(" WHERE MONITOR_LOT_RRN = ?");
            Long count = jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{monitorLotRrn}, Long.class);
            return Objects.nonNull(count) && count.longValue() > 0;
        }

        @Override
        public String getBatchIdByLotRrn(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT BATCH_ID FROM ");
            sql.append(DataBaseNames.BATCH_LOT_STORE).append(" WHERE LOT_RRN = ? OR MONITOR_LOT_RRN = ?");

            List<String> results = jdbcTemplate.query(sql.toString(), new Object[]{lotRrn, lotRrn}, String.class);
            String batchId = StringUtils.EMPTY;
            if (!CollectionUtils.isEmpty(results) && results.size() > 0) {
                batchId = results.get(0);
            }
            return batchId;
        }

        @Override
        public int getPrefixBatchIdCount(String batchId) {
            String sql = "SELECT count(DISTINCT batch_id) from BATCH_LOT_STORE_H where instr(batch_id,?)>0 ";
            Object[] args = new Object[]{batchId};
            return jdbcTemplate.queryForObjectWithNull(sql, args, int.class);
        }

        @Override
        public List<Map<String, Object>> getBatchLotStoreHistory(BatchLotStore storeCondition, Boolean showAll) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("B.BATCH_ID,B.SEQUENCE,B.TRANS_RRN,B.LOT_STEP_SEQUENCE,B.DE_TRANS_RRN,");
            sql.append("B.DE_LOT_STEP_SEQUENCE,B.LOT_ID,B.LOT_RRN,B.MONITOR_LOT_ID,B.MONITOR_LOT_RRN,");
            sql.append("B.TRANS_USER_ID,B.TRANS_TIME,B.DE_TRANS_USER_ID,B.DE_TRANS_TIME ");
            sql.append(" FROM BATCH_LOT_STORE_H B WHERE 1=1 ");

            List<Object> argList = new ArrayList<>();
            if (StringUtils.isNotBlank(storeCondition.getBatchId())) {
                sql.append(" AND B.BATCH_ID = ? ");
                argList.add(storeCondition.getBatchId());
            }
            if (StringUtils.isNotBlank(storeCondition.getEqptId())) {
                sql.append(" AND B.EQPT_ID = ? ");
                argList.add(storeCondition.getEqptId());
            }
            if (storeCondition.getLotRrn() != null && storeCondition.getLotRrn().longValue() > 0) {
                sql.append(" AND B.LOT_RRN = ? ");
                argList.add(storeCondition.getLotRrn());
            }
            if (!showAll) {
                sql.append(" AND B.DE_TRANS_RRN IS NULL ");
            }
            sql.append(" ORDER BY B.BATCH_ID,B.SEQUENCE");

            List<Map<String, Object>> list = jdbcTemplate
                    .query(sql.toString(), argList.toArray(), new RowMapper<Map<String, Object>>() {

                        @Override
                        public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                            Map<String, Object> map = MapUtils.newHashMap();
                            map.put("batchId", rs.getString("BATCH_ID"));
                            map.put("lotId", rs.getString("LOT_ID"));
                            map.put("lotRrn", rs.getLong("LOT_RRN"));
                            map.put("transRrn", rs.getLong("TRANS_RRN"));
                            map.put("lotStepSequence", rs.getLong("LOT_STEP_SEQUENCE"));
                            map.put("monitorLotId", rs.getString("MONITOR_LOT_ID"));
                            map.put("monitorLotRrn", rs.getLong("MONITOR_LOT_RRN"));
                            map.put("deTransRrn", rs.getLong("DE_TRANS_RRN"));
                            map.put("deLotStepSequence", rs.getLong("DE_LOT_STEP_SEQUENCE"));
                            map.put("batchCreateTime",
                                    DateUtils.formatDate(rs.getTimestamp("TRANS_TIME"), DateUtils.DATE_FORMAT4DATE));

                            String batchDeleteTime = rs.getString("DE_TRANS_TIME") == null ? "" : DateUtils
                                    .formatDate(rs.getTimestamp("DE_TRANS_TIME"), DateUtils.DATE_FORMAT4DATE);
                            map.put("batchDeleteTime", batchDeleteTime);
                            if (StringUtils.isBlank(batchDeleteTime)) {
                                map.put("inLineBatch", NumberUtils.INTEGER_ONE.toString());
                            }
                            return map;
                        }

                    });
            return list;
        }

        @Override
        public List<EqpRcpDiffBatchSetupInfo> getEqpRecipeDiffBatchSet(long equipmentRrn) {
            String sql = "SELECT EQPT_ID,EQPT_RRN,RECIPE_ID,RECIPE_RRN,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<EqpRcpDiffBatchSetupInfo>) (rs, rowNum) -> {
                        EqpRcpDiffBatchSetupInfo setupInfo = new EqpRcpDiffBatchSetupInfo();
                        setupInfo.setEqptId(rs.getString("EQPT_ID"));
                        setupInfo.setEqptRrn(rs.getLong("EQPT_RRN"));
                        setupInfo.setRecipeId(rs.getString("RECIPE_ID"));
                        setupInfo.setRecipeRrn(rs.getLong("RECIPE_RRN"));
                        setupInfo.setMonitorProductId(rs.getString("MONITOR_PRODUCT_ID"));
                        setupInfo.setMonitorProductRrn(rs.getLong("MONITOR_PRODUCT_RRN"));
                        setupInfo.setMonitorLimit(rs.getInt("MONITOR_LOWER_LIMIT"));
                        setupInfo.setEdcFlag(rs.getBoolean("EDC_FLAG"));
                        return setupInfo;
                    });
        }

        @Override
        public Page getEqpRcpBatchSetupHistory(Map<String, Object> condition, Page page) {
            String sql = "SELECT D.TRANS_ID,D.TRANS_TIME,D.TRANS_USER_ID,D.EQPT_ID,D.RECIPE_ID,D.MONITOR_PRODUCT_ID," +
                    " D.MONITOR_LOWER_LIMIT,D.EDC_FLAG  FROM DIFF_EQPT_BATCH_SET_H D  WHERE 1=1 ";

            List<Object> args = new ArrayList<Object>();
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "eqptRrn"))) {
                sql += " AND D.EQPT_RRN = ?";
                args.add(MapUtils.getLong(condition, "eqptRrn"));
            }
            sql += " ORDER BY D.TRANS_TIME DESC";
            return jdbcTemplate.queryForPage(page, sql, args.toArray(), new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = MapUtils.newHashMap();
                    map.put("transId", rs.getString("TRANS_ID"));
                    map.put("transTime", rs.getTimestamp("TRANS_TIME") == null ? "" : DateUtils
                            .formatDate(rs.getTimestamp("TRANS_TIME"), DateUtils.DATE_FORMAT4DATE));

                    map.put("transPerformedBy", rs.getString("TRANS_USER_ID"));
                    map.put("eqptId", rs.getString("EQPT_ID"));
                    map.put("recipeId", rs.getString("RECIPE_ID"));
                    map.put("productId", rs.getString("MONITOR_PRODUCT_ID"));
                    map.put("limit", rs.getLong("MONITOR_LOWER_LIMIT"));
                    map.put("edcFlag", rs.getString("EDC_FLAG"));
                    return map;
                }

            });
        }

    }