# ElogSheetQueryDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.server.wip.dao.ElogSheetQueryDAO;
    import com.mycim.server.wip.dao.mapper.ElogSheetMapper;
    import com.mycim.server.wip.dao.mapper.ElogSubSheetMapper;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.wip.ElogSheet;
    import com.mycim.valueobject.wip.ElogSubSheet;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.RowMapper;
    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/7/20
     **/
    @Repository
    public class ElogSheetQueryDAOImpl implements ElogSheetQueryDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public ElogSheet getElogSheetEvent(Long elogSheetRrn) {
            StringBuilder sql = getElogSheetEventSql();
            sql.append(" WHERE elog_sheet_rrn = ?");

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{elogSheetRrn}, new ElogSheetMapper());
        }

        @Override
        public List<ElogSheet> getElogSheetEventList(ElogSheet queryInfo) {
            StringBuilder sql = getElogSheetEventSql();
            sql.append(" WHERE 1=1");

            List<Object> argList = new ArrayList<>();
            if (queryInfo.getEquipmentRrn() != null && queryInfo.getEquipmentRrn().longValue() > 0) {
                sql.append(" AND equipment_rrn = ?");
                argList.add(queryInfo.getEquipmentRrn());
            }
            if (queryInfo.getLotRrn() != null && queryInfo.getLotRrn().longValue() > 0) {
                sql.append(" AND lot_rrn = ?");
                argList.add(queryInfo.getLotRrn());
            }

            return jdbcTemplate.query(sql.toString(), new ElogSheetMapper(), argList.toArray());
        }

        @Override
        public ElogSubSheet getElogSubSheetEventBySubSheetRrn(Long elogSubSheetRrn) {
            StringBuilder sql = getSubSheetEventSql();
            sql.append(" WHERE elog_sub_sheet_rrn = ?");
            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new Object[]{elogSubSheetRrn}, new ElogSubSheetMapper());
        }

        @Override
        public List<ElogSubSheet> getElogSubSheetEventList(Long elogSheetRrn) {
            StringBuilder sql = getSubSheetEventSql();
            sql.append(" WHERE elog_sheet_rrn = ?");
            return jdbcTemplate.query(sql.toString(), new ElogSubSheetMapper(), new Object[]{elogSheetRrn});
        }

        @Override
        public List<ElogSubSheet> getElogSubSheetEventList(ElogSubSheet queryInfo) {

            StringBuilder sql = getSubSheetEventSql();
            sql.append(" WHERE 1=1");

            List<Object> argList = new ArrayList<>();
            if (queryInfo.getEquipmentRrn() != null && queryInfo.getEquipmentRrn().longValue() > 0) {
                sql.append(" AND equipment_rrn = ?");
                argList.add(queryInfo.getEquipmentRrn());
            }

            return jdbcTemplate.query(sql.toString(), new ElogSubSheetMapper(), argList.toArray());
        }

        @Override
        public List<Map<String, Object>> getElogChamberSheet(String lotRrn, String eqptId) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" b.CHAMBER_TYPE, b.EQUIPMENT_ID, b.LOT_ID, ESS.UNIT_ID  FROM ( SELECT CHAMBER_TYPE, ");
            sql.append(" EQUIPMENT_ID, LOT_ID, ELOG_SHEET_RRN  FROM ( SELECT ESC.CHAMBER_TYPE, ESC.EQUIPMENT_ID, ESC.LOT_ID, ");
            sql.append(" ESC.ELOG_SHEET_RRN  FROM ELOG_SHEET_CHAMBER ESC  WHERE ESC.LOT_ID = ?  AND ESC.EQUIPMENT_ID = ?  ");
            sql.append(" ORDER BY esc.PROCESS_END_TIME DESC  )  WHERE rownum = 1  ) b LEFT JOIN  ELOG_SUB_SHEET ESS  ON ");
            sql.append(" b.LOT_ID = ESS.LOT_ID  AND b.EQUIPMENT_ID = ESS.EQUIPMENT_ID AND b.ELOG_SHEET_RRN = ESS.ELOG_SHEET_RRN ");

            List args = new ArrayList();
            args.add(lotRrn);
            args.add(eqptId);

            return jdbcTemplate.query(sql.toString(),  args.toArray(),
                                      (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                                          HashMap<String, Object> elogSheetInfo = MapUtils.newHashMap();
                                          elogSheetInfo.put("chamberType",rs.getString("CHAMBER_TYPE"));
                                          elogSheetInfo.put("lotId",rs.getString("LOT_ID"));
                                          elogSheetInfo.put("eqptId",rs.getString("EQUIPMENT_ID"));
                                          elogSheetInfo.put("unitId",rs.getString("UNIT_ID"));
                                          return elogSheetInfo;
                                      });
        }

        private StringBuilder getElogSheetEventSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("elog_sheet_rrn,equipment_id,equipment_rrn,equipment_group_id,chamber_type,lot_rrn,lot_id,");
            sql.append("step_sequence,lot_type,qty,chamber_qty,batch_id,recipe_id,RECIPE_PHYSICAL_ID,hot_flag,priority,");
            sql.append("carrier_id,product_id,product_version,process_id,process_version,route_id,step_id,");
            sql.append("flow_seq,stage_id,reticle_group_id,reticle_id,process_location,pollution_level,customer_id,");
            sql.append("workarea,process_start_user_id,process_start_time,process_end_user_id,process_end_time");
            sql.append(" FROM ").append(DataBaseNames.ELOG_SHEET_EVENT);
            return sql;
        }

        private StringBuilder getSubSheetEventSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("elog_sub_sheet_rrn,elog_sheet_rrn,equipment_id,equipment_rrn,chamber_type,chamber_flow,lot_rrn,");
            sql.append("lot_id,step_sequence,slot,batch_id,recipe_id,RECIPE_PHYSICAL_ID,unit_id,unit_qty,carrier_id,");
            sql.append("process_start_user_id,process_start_time,process_end_user_id,process_end_time");
            sql.append(" FROM ").append(DataBaseNames.ELOG_SUB_SHEET_EVENT);
            return sql;
        }

    }