# PiLotInqDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.utils.lang.collections.CollectionUtils;
    import com.mycim.server.wip.dao.PiLotInqDAO;
    import com.mycim.server.wip.dao.mapper.*;
    import com.mycim.valueobject.ems.pilot.*;
    import com.mycim.valueobject.ems.pilot.dto.PiLotRcpGrpQueryDTO;
    import com.mycim.valueobject.ems.pilot.dto.PiLotSetupQueryDTO;
    import com.mycim.valueobject.ems.pilot.dto.PiLotViewQueryDTO;
    import org.springframework.stereotype.Repository;

    import java.util.List;

    /**
     * @author songpy
     * @version 1.0.0
     * @date 2021/8/18
     **/
    @Repository
    public class PiLotInqDAOImpl implements PiLotInqDAO {

        final JdbcTemplate jdbcTemplate;

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

        @Override
        public Page getPiLotSetups(Page page, PiLotSetupQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotSetupBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            sqlBuilder.append(" ORDER BY PI_LOT_RRN desc ");
            return jdbcTemplate.queryForPage(page, sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                             new PiLotSetupRowMapper());
        }

        @Override
        public IdleType getPiLotSetupIdle(Long piLotSetupRrn) {
            String sql = "SELECT MAX_RUN_TIME,IDLE_TIME,RESET_COMMENT,SUB_TYPE FROM PI_LOT_SETUP_IDLE_TIME where " +
                    "PI_LOT_RRN = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new IdleTypeRowMapper(), piLotSetupRrn);
        }

        @Override
        public PmType getPiLotSetupPM(Long piLotSetupRrn) {
            String sql = "SELECT BUFFER_TIME,CONSTRAINT_RECIPE_RRN,CONSTRAINT_RECIPE_ID FROM PI_LOT_SETUP_PM where " +
                    "PI_LOT_RRN = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new PmTypeRowMapper(), piLotSetupRrn);
        }

        @Override
        public CountType getPiLotSetupCount(Long piLotSetupRrn) {
            String sql = "SELECT COUNT_TYPE,COUNT_SPEC,TIME_POINT TRIGGER_RECIPE_RRN,TRIGGER_RECIPE_ID," +
                    "CONSTRAINT_RECIPE_RRN,CONSTRAINT_RECIPE_ID FROM PI_LOT_SETUP_COUNT WHERE PI_LOT_RRN= ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new CountTypeRowMapper(), piLotSetupRrn);
        }

        @Override
        public RcpGrpType getPiLotSetupRcpGrp(Long piLotSetupRrn) {
            String sql = "SELECT FROM_RCP_GRP_ID,FROM_RCP_GRP_RRN,TO_RCP_GRP_ID,TO_RCP_GRP_RRN FROM PI_LOT_SETUP_RCP_GRP " +
                    "WHERE PI_LOT_RRN= ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new RcpGrpTypeRowMapper(), piLotSetupRrn);
        }

        @Override
        public PiLotSetup getPiLotSetup(PiLotSetupQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotSetupBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.queryForObjectWithNull(sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                                       new PiLotSetupRowMapper());
        }

        @Override
        public PiLotView getPiLotView(PiLotViewQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotViewBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.queryForObjectWithNull(sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                                       new PiLotViewRowMapper());
        }

        @Override
        public Page getPiLotViews(Page page, PiLotViewQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotViewBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            // sqlBuilder.append(" ORDER BY view_id desc ");
            sqlBuilder.append(" ORDER BY update_time desc ");
            return jdbcTemplate.queryForPage(page, sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                             new PiLotViewRowMapper());
        }

        @Override
        public Integer getCountPiLotViewBySetupRrn(Long setupRrn) {
            String sql = "SELECT count(*) FROM PI_LOT_VIEW WHERE PI_LOT_RRN= ?";
            return jdbcTemplate.queryForObjectWithNull(sql, Integer.class, setupRrn);
        }

        @Override
        public List<PiLotView> getPiLotViews(PiLotViewQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotViewBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.query(sqlBuilder.toString(), queryDTO.getParamList().toArray(), new PiLotViewRowMapper());
        }

        @Override
        public Page getPiLotRcpGrps(Page page, PiLotRcpGrpQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotRcpGrpBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.queryForPage(page, sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                             new PiLotRcpGrpRowMapper());
        }

        @Override
        public PiLotRcpGrp getPiLotRcpGrp(PiLotRcpGrpQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotRcpGrpBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.queryForObjectWithNull(sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                                       new PiLotRcpGrpRowMapper());
        }

        @Override
        public Page getPiLotRcps(Page page, PiLotRcpGrpQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotRcpBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.queryForPage(page, sqlBuilder.toString(), queryDTO.getParamList().toArray(),
                                             new PiLotRcpRowMapper());
        }

        @Override
        public Page querySetupHistorys(Page page, PiLotSetupQueryDTO queryDTO) {
            String sql = "SELECT h.trans_rrn,h.trans_id,h.pi_lot_rrn,h.pi_lot_id,h.pi_lot_type,h.status,h.enable_flag,h" +
                    ".eqpt_id,h.eqpt_rrn,h.wafer_qty,h.wafer_rule_id,h.retry_count,h.trans_user_rrn,h.trans_user_id,h" +
                    ".trans_time, h.PARALLEL_RUN_TYPE,h1" +
                    ".IDLE_TIME,h1.MAX_RUN_TIME,h1.RESET_COMMENT,h1.SUB_TYPE,h2.BUFFER_TIME,h2.CONSTRAINT_RECIPE_ID " +
                    "PM_CONSTRAINT_RECIPE_ID,h3.FROM_RCP_GRP_ID,h3.FROM_RCP_GRP_RRN,h3.TO_RCP_GRP_ID,h3.to_rcp_grp_rrn,h4" +
                    ".COUNT_TYPE,h4.COUNT_SPEC,h4.TIME_POINT,h4.TRIGGER_RECIPE_ID,h4.CONSTRAINT_RECIPE_ID FROM " +
                    "PI_LOT_SETUP_H h LEFT JOIN PI_LOT_SETUP_IDLE_TIME_H h1 ON h.TRANS_RRN=h1.TRANS_RRN LEFT JOIN " +
                    "PI_LOT_SETUP_PM_H h2 ON h.TRANS_RRN=h2.TRANS_RRN LEFT JOIN PI_LOT_SETUP_RCP_GRP_H h3 ON h" +
                    ".TRANS_RRN=h3.TRANS_RRN LEFT JOIN PI_LOT_SETUP_COUNT_H h4 ON h.TRANS_RRN=h4.TRANS_RRN WHERE h" +
                    ".PI_LOT_RRN= ? ORDER BY h.TRANS_TIME DESC";
            return jdbcTemplate.queryForPage(page, sql, new Object[]{queryDTO.getPiLotRrn()}, new PiLotSetupHRowMapper());
        }

        @Override
        public Page queryViewHistorys(Page page, PiLotViewQueryDTO queryDTO) {
            String sql = "SELECT trans_rrn,trans_id,view_rrn,view_id,pi_lot_rrn,pi_lot_id,pi_lot_type,status,eqpt_id," +
                    "eqpt_rrn,wafer_qty,retry_count,lot_id,lot_rrn,child_lot_id,child_lot_rrn,flow_complete,CAN_SPLIT," +
                    "trans_user_rrn,trans_user_id,trans_time,start_route_id,start_route_rrn,start_step_id,start_step_rrn," +
                    "end_route_id," +
                    "end_route_rrn,end_step_id,end_step_rrn,single_lot,parallel_run_type,comments FROM PI_LOT_VIEW_H where view_rrn = ? ORDER BY TRANS_TIME DESC";
            return jdbcTemplate.queryForPage(page, sql, new Object[]{queryDTO.getViewRrn()}, new PiLotViewHRowMapper());
        }

        @Override
        public List<PiLotSetup> getPilotSetups(PiLotSetupQueryDTO queryDTO) {
            StringBuilder sqlBuilder = new StringBuilder(getPiLotSetupBaseSql());
            sqlBuilder.append(queryDTO.toSqlString());
            return jdbcTemplate.query(sqlBuilder.toString(), queryDTO.getParamList().toArray(), new PiLotSetupRowMapper());
        }

        private String getPiLotSetupBaseSql() {
            return "SELECT*FROM (" +
                    "SELECT s.PI_LOT_RRN,s.PI_LOT_ID,s.PI_LOT_TYPE,s.STATUS,s.ENABLE_FLAG,s.EQPT_ID,s.EQPT_RRN,s" +
                    ".WAFER_QTY,s.WAFER_RULE_ID,s.RETRY_COUNT,s.CREATE_TIME,s.UPDATE_TIME,s.CREATE_USER_RRN,s" +
                    ".UPDATE_USER_RRN,s.LOCK_VERSION,s.PARALLEL_RUN_TYPE,v.single_lot,v.LOT_ID,v.LOT_RRN,v.VIEW_RRN,(" +
                    "SELECT MAX(trans_time) FROM PI_LOT_VIEW_H h WHERE h.pi_lot_rrn=s.PI_LOT_RRN AND h.STATUS='CLOSE') AS" +
                    " close_time FROM PI_LOT_SETUP s LEFT JOIN pi_lot_view v ON s.PI_LOT_RRN=v.PI_LOT_RRN AND v.STATUS " +
                    "!='CLOSE') WHERE 1= 1";
        }

        private String getPiLotViewBaseSql() {
            return "SELECT view_rrn,view_id,pi_lot_rrn,pi_lot_id,pi_lot_type,status,eqpt_id,eqpt_rrn,wafer_qty," +
                    "retry_count,lot_id,lot_rrn,child_lot_id,child_lot_rrn,flow_complete,can_split,start_route_id," +
                    "start_route_rrn,start_step_id,start_step_rrn,end_route_id,end_route_rrn,end_step_id,end_step_rrn," +
                    "create_time,update_time,create_user_rrn,update_user_rrn,lock_version,single_lot,parallel_run_type FROM PI_LOT_VIEW WHERE 1= 1";
        }

        private String getPiLotRcpGrpBaseSql() {
            return "SELECT rcp_grp_rrn,rcp_grp_id,create_time,update_time,create_user_rrn,update_user_rrn,lock_version," +
                    "rcp_grp_desc FROM PI_LOT_RECIPE_GROUP WHERE 1=1 ";
        }

        private String getPiLotRcpBaseSql() {
            return "SELECT rcp_grp_rrn,rcp_grp_id,recipe_id,recipe_rrn FROM PI_LOT_RECIPE_GROUP_MAPPING WHERE 1=1";
        }

    }