# LotInqDAOImpl.java

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

    import com.fa.sesa.threadlocal.LocalContext;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.wip.dao.LotInqDAO;
    import com.mycim.server.wip.dao.mapper.LotMapper;
    import com.mycim.valueobject.Constants;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.wip.Lot;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.util.List;

    /**
     * @author finatice.yang
     * @date 2021/12/14
     **/
    @Repository
    public class LotInqDAOImpl implements LotInqDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public String getLotId(Long lotRrn) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT LOT_ID FROM ").append(DataBaseNames.LOT);
            sql.append(" WHERE LOT_RRN = ?");
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{lotRrn}, String.class);
        }

        @Override
        public Long getLotRrn(String lotId) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT LOT_RRN FROM ").append(DataBaseNames.LOT);
            sql.append(" WHERE FACILITY_RRN = ? AND LOT_ID = ?");
            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new Object[]{LocalContext.getFacilityRrn(), lotId}, Long.class);
        }

        @Override
        public Lot getLot(Long lotRrn) {
            String sql = getLotSql() + " WHERE LOT_RRN = ?";
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{lotRrn}, new LotMapper());
        }

        @Override
        public Lot getLot(String lotId) {
            String sql = getLotSql() + " WHERE FACILITY_RRN = ? AND LOT_ID = ?";
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{LocalContext.getFacilityRrn(), lotId},
                                                       new LotMapper());
        }

        @Override
        public Lot getLotExt(Lot lot) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("lot_rrn,ATTRIBUTE_DATA1,wifer_id,wifer_type,flow_level,customer_id,customer_lot_id,");
            sql.append("customer_wafer_id,wafer_start_time,outer_order_no,inner_order_no,shipping_code,");
            sql.append("is_out_source,is_recreate_lot,project_category,reticle_group_rrn,split_merge_flag,");
            sql.append("running_hold_flag,completedss_type,completedss_level,storage,is_divide_box,out_order_type,");
            sql.append("location,plocation,location_type,plocation_type,plan_start_date,start_hold,");
            sql.append("auto_split_merg_flag,current_step_time_struct,flip_type,sys_type ");
            sql.append(" FROM ").append(DataBaseNames.LOT_EXT);
            sql.append(" WHERE LOT_RRN = ? ");
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{lot.getLotRrn()}, new RowMapper<Lot>() {
                @Override
                public Lot mapRow(ResultSet rs, int rowNum) throws SQLException {
                    lot.setPollutionLevel(rs.getString("ATTRIBUTE_DATA1"));
                    lot.setWiferId(rs.getString("WIFER_ID"));
                    lot.setFlowLevel(rs.getString("FLOW_LEVEL"));
                    lot.setCustomerId(rs.getString("CUSTOMER_ID"));
                    lot.setCustomerLotId(rs.getString("CUSTOMER_LOT_ID"));
                    lot.setOuterOrderNO(rs.getString("OUTER_ORDER_NO"));
                    lot.setInnerOrderNO(rs.getString("INNER_ORDER_NO"));
                    lot.setShippingCode(rs.getString("SHIPPING_CODE"));
                    lot.setIsOutSource(rs.getString("IS_OUT_SOURCE"));
                    lot.setIsReCreateLot(rs.getString("IS_RECREATE_LOT"));
                    lot.setProjectCategory(rs.getString("PROJECT_CATEGORY"));
                    lot.setReticleGroupRrn(rs.getLong("RETICLE_GROUP_RRN"));
                    lot.setSplitMergeFlag(rs.getString("SPLIT_MERGE_FLAG"));
                    lot.setRunningHoldFlag(rs.getString("RUNNING_HOLD_FLAG"));
                    lot.setOutOrderType(rs.getString("OUT_ORDER_TYPE"));
                    lot.setLocationRrn(rs.getLong("LOCATION"));
                    lot.setpLocationRrn(rs.getLong("PLOCATION"));
                    if (StringUtils.isNotBlank(rs.getString("PLAN_START_DATE"))) {
                        lot.setPlanStartDate(new Timestamp(
                                DateUtils.parse(rs.getString("PLAN_START_DATE"), DateUtils.DATE_FORMAT4D).getTime()));
                    }
                    lot.setStartHold(rs.getString("START_HOLD"));
                    lot.setAutoSplitMergFlag(rs.getInt("AUTO_SPLIT_MERG_FLAG"));
                    lot.setFlipType(rs.getString("FLIP_TYPE"));
                    lot.setSysType(rs.getString("SYS_TYPE"));
                    lot.setLocationType(rs.getString("LOCATION_TYPE"));
                    lot.setpLocationType(rs.getString("PLOCATION_TYPE"));
                    return lot;
                }
            });
        }

        @Override
        public List<Lot> getLotListByCarrierRrn(Long carrierRrn) {
            String sql = getLotSql() + " WHERE CARRIER_RRN = ?";
            return jdbcTemplate.query(sql, new LotMapper(), carrierRrn);
        }

        @Override
        public List<Lot> getLotListByJobRrn(Long jobRrn) {
            String sql = getLotSql() + " WHERE JOB_RRN = ?";
            return jdbcTemplate.query(sql, new LotMapper(), jobRrn);
        }

        public List<Lot> getLotListByEqptRrn(Long eqptRrn) {
            String sql = getLotSql() + " WHERE EQPT_RRN = ?";
            return jdbcTemplate.query(sql, new LotMapper(), eqptRrn);
        }

        @Override
        public String getLotStatus(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("LOT_STATUS FROM ").append(DataBaseNames.LOT);
            sql.append(" WHERE LOT_RRN = ? ");
            return jdbcTemplate.queryForObjectWithNull(sql.toString(), String.class, lotRrn);
        }

        private String getLotSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("FACILITY_RRN,LOT_ID,BASED_LOT_RRN,CREATED_PLAN_LOT_RRN,HOT_FLAG,PRIORITY,");
            sql.append("STARTED_FLAG,STARTED_TIMESTAMP,CREATE_CATEGORY,CREATED_TIMESTAMP,");
            sql.append("END_TIMESTAMP,DUE_DATE,SCHEDULE_DUE_DATE,LATE_SCHEDULED_FLAG,");
            sql.append("ESTIMATED_REMAIN_TIME,DUMMY_FLAG,LOT_TYPE,LOT_OWNER,QTY1,QTY2,DUMMY_QTY1,");
            sql.append("INPUT_QTY1,INPUT_QTY2,SUBCONTRACTOR_RRN,LOT_COMMENTS,STEP_SEQUENCE,");
            sql.append("STEP_NUMBER_IN_PROCESS,HOLD_TIMESTAMP,REWORK_TRANS_RRN,REWORK_CATEGORY,");
            sql.append("CARRIER_RRN,BEFORE_STATUS,LOT_STATUS,PROCESSING_STATUS, QUEUE_TIMESTAMP,");
            sql.append("PRODUCT_RRN, PRODUCT_VERSION,PROCESS_RRN,PROCESS_VERSION,PROCESS_STEP_VERSION,");
            sql.append("PROCESS_STEP_ID_VERSION,PREV_OPERATION_RRN,PREV_OPERATION_VERSION,OPERATION_RRN,");
            sql.append("OPERATION_VERSION,NEXT_STEP_VERSION1,NEXT_STEP_ID_VERSION1,NEXT_OPERATION_RRN1,");
            sql.append("NEXT_STEP_VERSION2,NEXT_STEP_ID_VERSION2,NEXT_OPERATION_RRN2,STAGE_ID,");
            sql.append("LAYER_ID,BOR_RRN,EQPT_RRN,RECIPE_STRING,NEXT_RECIPE_STRING1,NEXT_RECIPE_STRING2,JOB_RRN,");
            sql.append("NEXT_JOB_RRN1,NEXT_JOB_RRN2,EXECUTION_RRN,LOT_RRN,WFL_STEP_PATH,RETICLE_RRN,");
            sql.append("ROUND((GETMINREMAINTIMEBYLOTRRN(LOT_RRN, '").append(Constants.TIME_LIMIT.TIME_TYPE_MAX);
            sql.append("') / 3600), 2) AS Q_TIME,SHIPPED_QTY1,SHIPPED_QTY2,PRODUCT_LAYER,");
            sql.append("ROUTE_SEQ,OPERATION_SEQ,RECIPE_LOGICAL_RRN,RECIPE_PHYSICAL_ID,CHAMBER_TYPE,");
            sql.append("IS_SAPPHIRE,LOT_PLAN_TYPE,FLOW_SEQ,OPERATION_DESC,PROCESS_LOCATION,IS_DUMMY_LOT");
            sql.append(" FROM ").append(DataBaseNames.LOT);

            return sql.toString();
        }

    }