# UnitQueryDAOImpl.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.MiscUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.CollectionUtils;
    import com.mycim.server.wip.dao.UnitQueryDAO;
    import com.mycim.server.wip.dao.mapper.BoxForDieMapper;
    import com.mycim.server.wip.dao.mapper.UnitMapRowMapper;
    import com.mycim.server.wip.dao.mapper.UnitMapper;
    import com.mycim.server.wip.dao.mapper.UnitTimeLimitStatusMapper;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.TimeLimitEnum;
    import com.mycim.valueobject.prp.UnitRecycledInfo;
    import com.mycim.valueobject.wip.*;
    import org.apache.commons.beanutils.BeanUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

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

    /**
     * @author yanbing.chen
     * @version 6.0.0
     * @date 2019/9/4
     **/
    @Repository
    public class UnitQueryDAOImpl implements UnitQueryDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public Unit getUnit(long unitRrn) {
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE," + "CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS,BEFORE_STATUS," + "DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG,QTY,PROCESS_START_TIME,PROCESS_END_TIME," + "UNIT_MAP_RRN,RECIPE_ID,USAGE_LIMIT," +
                    "USAGE_COUNT,TOTAL_DIE_NUM,T7CODE,UNIT_ALIAS_1," + "UNIT_ALIAS_2,ITEM_ID,MARK_NUMBER," +
                    "MARK_NUMBER_COMMENT,incoming_type,AUTO_MONITOR_USED_FLAG,'' LOT_ID,CUSTOMER_T7CODE, MATERIAL_ID ";
            sql += " FROM UNIT";
            sql += " WHERE UNIT_RRN=? ";
            Object[] args = new Object[]{new Long(unitRrn)};
            return jdbcTemplate.queryForObjectWithNull(sql, args, new UnitMapper());
        }

        @Override
        public List<Unit> getUnitList(Long lotRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" U.UNIT_RRN, U.FACILITY_RRN, U.UNIT_ID, U.STEP_SEQUENCE, ");
            sql.append(" U.LOT_RRN, U.LOT_STEP_SEQUENCE, U.CARRIER_RRN, U.POSITION_IN_CARRIER, ");
            sql.append(" U.UNIT_STATUS, U.PROCESS_STATUS, U.BEFORE_STATUS, U.DUMMY_FLAG, U.DEFECTIVE_FLAG, U" +
                               ".BONUS_FLAG, ");
            sql.append(" U.QTY, U.PROCESS_START_TIME, U.PROCESS_END_TIME, U.UNIT_MAP_RRN, U.RECIPE_ID, ");
            sql.append(" U.USAGE_LIMIT, U.USAGE_COUNT,U.UNIT_ALIAS_1,U.T7CODE,U.UNIT_ALIAS_2,U.ITEM_ID, ");
            sql.append(" U.MARK_NUMBER,U.MARK_NUMBER_COMMENT,u.incoming_type,AUTO_MONITOR_USED_FLAG,L.LOT_ID,U.CUSTOMER_T7CODE ");
            sql.append(" ,U.MATERIAL_ID ");
            sql.append(" FROM ").append(DataBaseNames.UNIT).append(" U, ");
            sql.append(DataBaseNames.LOT).append(" L ");
            sql.append(" WHERE L.LOT_RRN=U.LOT_RRN AND  U.LOT_RRN = ? ORDER BY U.POSITION_IN_CARRIER DESC");

            return jdbcTemplate.query(sql.toString(), new Object[]{lotRrn}, new UnitMapper());
        }

        @Override
        public List<Map> getUnits(Long lotRrn) {
            List<Map> units = new ArrayList();
            if (lotRrn != null) {
                String sql = "SELECT u.UNIT_RRN,u.POSITION_IN_CARRIER,u.UNIT_ID,u.UNIT_STATUS,l.lot_id,u.lot_rrn," + "l" +
                        ".carrier_rrn,c.instance_id,r.sum_loop_count,r.loop_count  FROM " + "UNIT u left join lot l on u" +
                        ".lot_rrn = l.lot_rrn " + "left join  named_object C on l.carrier_rrn = C.instance_rrn " + "left " +
                        "join unit_recycled r  on u.UNIT_RRN = r.UNIT_RRN " + " WHERE u.LOT_RRN =? " + "ORDER BY " +
                        "POSITION_IN_CARRIER ";

                return jdbcTemplate.query(sql, new Object[]{lotRrn.longValue()}, new RowMapper<Map>() {

                    long seq = 1;

                    @Override
                    public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Map unit = new HashMap();
                        unit.put("unitRrn", new Long(rs.getLong("UNIT_RRN")));
                        unit.put("postionInCarrier", new Integer(rs.getInt("POSITION_IN_CARRIER")));
                        unit.put("position", new Integer(rs.getInt("POSITION_IN_CARRIER")));
                        unit.put("unitId", rs.getString("UNIT_ID"));
                        unit.put("seq", new Long(seq++));
                        unit.put("unitStatus", rs.getString("UNIT_STATUS"));
                        unit.put("lotId", rs.getString("lot_id"));
                        unit.put("lotRrn", rs.getString("lot_rrn"));
                        unit.put("carrierRrn", rs.getString("carrier_rrn"));
                        unit.put("carrierId", rs.getString("instance_id"));
                        unit.put("loopCount", rs.getInt("loop_count"));
                        unit.put("sumLoopCount", rs.getInt("sum_loop_count"));
                        return unit;
                    }
                });
            }
            return units;
        }

        @Override
        public List<Map> getUnitsByParentLotRrn(long lotRrn) {

            String sql = "SELECT U.UNIT_RRN,U.POSITION_IN_CARRIER,U.UNIT_ID,U.UNIT_STATUS,L.LOT_ID,U.LOT_RRN," +
                    "L.CARRIER_RRN,C.INSTANCE_ID,R.SUM_LOOP_COUNT,R.LOOP_COUNT FROM UNIT U LEFT JOIN LOT L ON " +
                    "U.LOT_RRN = L.LOT_RRN LEFT JOIN  NAMED_OBJECT C ON L.CARRIER_RRN = C.INSTANCE_RRN LEFT " +
                    "JOIN UNIT_RECYCLED R  ON U.UNIT_RRN = R.UNIT_RRN  WHERE U.LOT_RRN IN " +
                    "(SELECT LOT_RRN FROM LOT WHERE LOT_RRN = ? OR BASED_LOT_RRN =?) ORDER BY POSITION_IN_CARRIER ";

            List<Map> units = jdbcTemplate.query(sql, new Object[]{lotRrn, lotRrn}, new RowMapper<Map>() {

                long seq = 1;

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map unit = new HashMap();
                    unit.put("unitRrn", new Long(rs.getLong("UNIT_RRN")));
                    unit.put("postionInCarrier", new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.put("position", new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.put("unitId", rs.getString("UNIT_ID"));
                    unit.put("seq", new Long(seq++));
                    unit.put("unitStatus", rs.getString("UNIT_STATUS"));
                    unit.put("lotId", rs.getString("LOT_ID"));
                    unit.put("lotRrn", rs.getString("LOT_RRN"));
                    unit.put("carrierRrn", rs.getString("CARRIER_RRN"));
                    unit.put("carrierId", rs.getString("INSTANCE_ID"));
                    unit.put("loopCount", rs.getInt("LOOP_COUNT"));
                    unit.put("sumLoopCount", rs.getInt("SUM_LOOP_COUNT"));
                    return unit;
                }
            });
            return units == null ? new ArrayList<>() : units;
        }

        @Override
        public List<Unit> getUnitHistoryListByCarrierMapRrn(long oldCarrierMapRrn) {
            String sql = "SELECT CMH.UNIT_RRN, CMH.POSITION_IN_CARRIER, U.UNIT_ID, U.LOT_RRN, CMH.CARRIER_RRN "
                    + " FROM CARRIER_MAPPING_H CMH "
                    + " LEFT JOIN UNIT U ON CMH.UNIT_RRN = U.UNIT_RRN "
                    + " WHERE CMH.CARRIER_MAP_RRN = ? "
                    + " ORDER BY CMH.POSITION_IN_CARRIER";

            List<Unit> units = jdbcTemplate.query(sql, new Object[]{oldCarrierMapRrn}, (RowMapper<Unit>) (rs, rowNum) -> {
                Unit unit = new Unit();
                unit.setUnitRrn(rs.getLong("UNIT_RRN"));
                unit.setUnitId(rs.getString("UNIT_ID"));
                unit.setLotRrn(rs.getLong("LOT_RRN"));
                // unit.setLotId(rs.getString("LOT_ID"));
                unit.setPositionInCarrier(rs.getInt("POSITION_IN_CARRIER"));
                return unit;
            });
            return units == null ? new ArrayList<>() : units;
        }

        @Override
        public List<Map> getUnitListHistory(long transRrn) {
            String sql = "SELECT U.UNIT_ID,UH.*, TL.TRANS_ID, TL.TRANS_START_TIMESTAMP,TL.TRANS_PERFORMED_BY, TL.COMMENTS" +
                    " " + " FROM UNIT U,UNIT_MARK_NUMBER_MAPPING_H UH, TRANSACTION_LOG TL " + "WHERE U.UNIT_RRN = UH.UNIT_RRN AND UH" +
                    ".TRANS_RRN=TL.TRANS_RRN AND UH.TRANS_RRN = ?";
            return jdbcTemplate.query(sql, new Object[]{transRrn}, (rs, rowNum) -> {
                Map unit = new HashMap();
                unit.put("lotRrn", rs.getString("LOT_RRN"));
                unit.put("unitRrn", rs.getString("UNIT_RRN"));
                unit.put("unitId", rs.getString("UNIT_ID"));
                unit.put("markNumber", rs.getString("MARK_NUMBER"));
                unit.put("markNumberComment", rs.getString("MARK_NUMBER_COMMENT"));
                unit.put("transId", rs.getString("TRANS_ID"));
                unit.put("transStartTimestamp", rs.getString("TRANS_START_TIMESTAMP"));
                unit.put("transPerformedBy", rs.getString("TRANS_PERFORMED_BY"));
                unit.put("comments", rs.getString("COMMENTS"));
                return unit;
            });
        }

        @Override
        public List<Map> getUnitListByLot(long lotRrn, Boolean filterStatus) {
            this.updateCarrierMapRrn(lotRrn);

            String sql = "SELECT U.LOT_RRN,U.UNIT_RRN,NVL(U.UNIT_ID,'N/A'),M.POSITION_IN_CARRIER" + " ,M.AVAILABLE_FLAG,U" +
                    ".DUMMY_FLAG,U.UNIT_STATUS,L.lot_id,L.product_rrn,U.recipe_id," + " U.T7CODE,U.UNIT_ALIAS_1,U" +
                    ".UNIT_ALIAS_2,U.ITEM_ID,U.MARK_NUMBER,U" + ".MARK_NUMBER_COMMENT," + " U.SOURCE_WAFER_ID,U" +
                    ".SOURCE_WAFER_DIE_QTY " + " FROM (" + DataBaseNames.CARRIER_MAPPING + " M left join " +
                    DataBaseNames.UNIT + " U on M.UNIT_RRN=U.UNIT_RRN)," + DataBaseNames.LOT + " L," +
                    DataBaseNames.CARRIER + " C " + " WHERE L.LOT_RRN= " + lotRrn + " AND L.CARRIER_RRN=C.CARRIER_RRN";
            if (filterStatus) {
                sql = sql + " AND U.UNIT_STATUS !=" + StringUtils.wrapWithApostropheIfMissing(UnitStatus.LOST) +
                        " AND U.UNIT_STATUS !=" + StringUtils.wrapWithApostropheIfMissing(UnitStatus.SCRAPPED);
            }
            sql = sql + " AND C.CARRIER_MAP_RRN = M.CARRIER_MAP_RRN" + " AND L.LOT_RRN = U.LOT_RRN" + " ORDER BY M" +
                    ".POSITION_IN_CARRIER";

            return jdbcTemplate.query(sql, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map unit = new HashMap();
                    unit = new HashMap<>();
                    unit.put("lotRrn", rs.getString(1));
                    unit.put("unitRrn", rs.getString(2));
                    unit.put("unitId", rs.getString(3));
                    unit.put("position", rs.getString(4));
                    // unit.put("available", rs.getString(5));
                    // unit.put("dummyflag", rs.getString(6));
                    unit.put("unitstatus", rs.getString(7));
                    unit.put("lotid", rs.getString(8));
                    if (StringUtils.isNotEmpty(rs.getString("T7CODE"))) {
                        unit.put("t7code", rs.getString("T7CODE"));
                    } else {
                        unit.put("t7code", "");
                    }

                    if (StringUtils.isNotEmpty(rs.getString(9))) {
                        // unit.put("productRrn", rs.getString(9));
                    }
                    if (StringUtils.isNotEmpty(rs.getString("recipe_id"))) {
                        unit.put("ppid", rs.getString("recipe_id"));
                        unit.put("recipeId", rs.getString("recipe_id"));
                    } else {
                        unit.put("ppid", "");
                        unit.put("recipeId", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("UNIT_ALIAS_1"))) {
                        unit.put("unitAlias1", rs.getString("UNIT_ALIAS_1"));
                    } else {
                        unit.put("unitAlias1", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("UNIT_ALIAS_2"))) {
                        unit.put("unitAlias2", rs.getString("UNIT_ALIAS_2"));
                    } else {
                        unit.put("unitAlias2", "");
                    }

                    if (StringUtils.isNotEmpty(rs.getString("ITEM_ID"))) {
                        unit.put("itemId", rs.getString("ITEM_ID"));
                    } else {
                        unit.put("itemId", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("MARK_NUMBER"))) {
                        unit.put("markNumber", rs.getString("MARK_NUMBER"));
                    } else {
                        unit.put("markNumber", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("MARK_NUMBER_COMMENT"))) {
                        unit.put("markNumberComment", rs.getString("MARK_NUMBER_COMMENT"));
                    } else {
                        unit.put("markNumberComment", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("SOURCE_WAFER_ID"))) {
                        unit.put("sourceWaferId", rs.getString("SOURCE_WAFER_ID"));
                    } else {
                        unit.put("sourceWaferId", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("SOURCE_WAFER_DIE_QTY"))) {
                        unit.put("sourceWaferDieQty", rs.getString("SOURCE_WAFER_DIE_QTY"));
                    } else {
                        unit.put("sourceWaferDieQty", "");
                    }

                    return unit;
                }
            });
        }

        @Override
        public Unit getUnit(long facilityRrn, String unitId) {
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE,CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS," + "BEFORE_STATUS,DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG," + "QTY,PROCESS_START_TIME,PROCESS_END_TIME,UNIT_MAP_RRN,usage_limit,usage_count FROM " +
                    DataBaseNames.UNIT + " WHERE FACILITY_RRN = ? and UNIT_ID = ?";
            List units = jdbcTemplate.query(sql, new Object[]{facilityRrn, unitId}, new RowMapper() {

                // TODO:mapper重构
                @Override
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Unit unit = new Unit();
                    unit.setFacilityRrn(new Long(rs.getLong("FACILITY_RRN")));
                    unit.setUnitId(rs.getString("UNIT_ID"));
                    unit.setStepSequence(new Long(rs.getLong("STEP_SEQUENCE")));
                    unit.setLotRrn(new Long(rs.getLong("LOT_RRN")));
                    unit.setLotStepSequence(new Long(rs.getLong("LOT_STEP_SEQUENCE")));
                    unit.setCarrierRrn(new Long(rs.getLong("CARRIER_RRN")));
                    unit.setPositionInCarrier(new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.setUnitStatus(rs.getString("UNIT_STATUS"));
                    unit.setProcessStatus(rs.getString("PROCESS_STATUS"));
                    unit.setBeforeStatus(rs.getString("BEFORE_STATUS"));
                    unit.setDummyFlag(rs.getString("DUMMY_FLAG"));
                    unit.setDefectiveFlag(rs.getString("DEFECTIVE_FLAG"));
                    unit.setBonusFlag(rs.getString("BONUS_FLAG"));
                    unit.setQty(new Double(rs.getDouble("QTY")));
                    unit.setProcessStartTime(rs.getDate("PROCESS_START_TIME"));
                    unit.setProcessEndTime(rs.getDate("PROCESS_END_TIME"));
                    unit.setUnitMapRrn(new Long(rs.getLong("UNIT_MAP_RRN")));
                    unit.setUnitRrn(rs.getLong("UNIT_RRN"));
                    unit.setUsageCount(rs.getInt("usage_count"));
                    unit.setUsageLimit(rs.getInt("usage_limit"));
                    return unit;
                }
            });
            return (units.size() > 0 ? (Unit) units.get(0) : null);
        }

        @Override
        public List<Map<String, Object>> getUsefulUnits(long lotRrn) {
            String sql = "SELECT UNIT_RRN,POSITION_IN_CARRIER,UNIT_ID,UNIT_STATUS,TOTAL_DIE_NUM " + " FROM UNIT " + " " +
                    "WHERE UNIT_STATUS != ? AND UNIT_STATUS != ? AND LOT_RRN= ?" + " ORDER BY POSITION_IN_CARRIER ASC";
            return jdbcTemplate.query(sql, new Object[]{UnitStatus.LOST, UnitStatus.SCRAPPED, lotRrn}, new RowMapper() {

                @Override
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap<String, Object>();
                    map.put("unitRrn", rs.getLong("UNIT_RRN"));
                    map.put("positionInCarrier", rs.getInt("POSITION_IN_CARRIER"));
                    map.put("unitId", rs.getString("UNIT_ID"));
                    map.put("unitStatus", rs.getString("UNIT_STATUS"));
                    map.put("totalDieNum", rs.getLong("TOTAL_DIE_NUM"));
                    return map;
                }
            });
        }

        @Override
        public int getLotWafeCount(long lotRrn) {
            String sql = "SELECT COUNT(L.UNIT_RRN) FROM UNIT L WHERE L.LOT_RRN=?";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lotRrn}, int.class);
        }

        @Override
        public List<Map<String,Long>> getLotRrnFromUnitStepRangeHistory(String unitId) {
            String sql = "select distinct lot_rrn from unit_step_range_h where unit_rrn=(select unit_rrn " + "from unit " +
                    "where unit_id=?) ";
            SqlRowSet rowSet= jdbcTemplate.queryForRowSet(sql, unitId);
            List<Map<String,Long>> result =new ArrayList<>();
            while (rowSet.next()){
                Map<String,Long> item = new HashMap<>();
                item.put("LOT_RRN",rowSet.getLong("lot_rrn"));
                result.add(item);
            }
            return result;
        }

        @Override
        public List<Map> getUnitListByCarrier(long carrierRrn) {
            String sql = "SELECT U.LOT_RRN,U.UNIT_RRN,NVL(U.UNIT_ID,'N/A') UNIT_ID,M.POSITION_IN_CARRIER"
                    + " ,M.AVAILABLE_FLAG,U.DUMMY_FLAG,U.UNIT_STATUS,L.LOT_ID,U.LOT_STEP_SEQUENCE,L"
                    + ".PRODUCT_RRN,U.RECIPE_ID,U.T7CODE,U.UNIT_ALIAS_1,U.UNIT_ALIAS_2,U.ITEM_ID "
                    + " FROM " + DataBaseNames.UNIT
                    + " U left join " + DataBaseNames.CARRIER_MAPPING + " M on U.UNIT_RRN = M.UNIT_RRN,"
                    + DataBaseNames.LOT + " L," + DataBaseNames.CARRIER
                    + " C  WHERE C.CARRIER_RRN= ? AND C.CARRIER_MAP_RRN = M.CARRIER_MAP_RRN"
                    + " AND U.LOT_RRN=L.LOT_RRN AND U.UNIT_STATUS != ? AND U.UNIT_STATUS !=?"
                    + "  ORDER BY M.POSITION_IN_CARRIER";

            return jdbcTemplate.query(sql, new Object[]{carrierRrn,UnitStatus.LOST,UnitStatus.SCRAPPED }, (RowMapper<Map>) (rs, rowNum) -> {
                Map unit = new HashMap<>();
                unit.put("lotRrn", rs.getString("LOT_RRN"));
                unit.put("unitRrn", rs.getString("UNIT_RRN"));
                unit.put("unitId", rs.getString("UNIT_ID"));
                unit.put("position", rs.getString("POSITION_IN_CARRIER"));
                unit.put("unitstatus", rs.getString("UNIT_STATUS"));
                unit.put("lotid", rs.getString("LOT_ID"));
                unit.put("lotId", rs.getString("LOT_ID"));
                unit.put("t7code", StringUtils.defaultIfBlank(rs.getString("T7CODE"), StringUtils.EMPTY));
                unit.put("ppid", StringUtils.defaultIfBlank(rs.getString("RECIPE_ID"), StringUtils.EMPTY));
                unit.put("recipeId", StringUtils.defaultIfBlank(rs.getString("RECIPE_ID"), StringUtils.EMPTY));
                unit.put("unitAlias1", StringUtils.defaultIfBlank(rs.getString("UNIT_ALIAS_1"), StringUtils.EMPTY));
                unit.put("unitAlias2", StringUtils.defaultIfBlank(rs.getString("UNIT_ALIAS_2"), StringUtils.EMPTY));
                unit.put("itemId", StringUtils.defaultIfBlank(rs.getString("ITEM_ID"), StringUtils.EMPTY));
                return unit;
            });
        }

        @Override
        public Long getNumberOfUnitInCarrier(Long carrierRrn) {
            StringBuilder sql = new StringBuilder("SELECT COUNT(UNIT_RRN) FROM ");
            sql.append(DataBaseNames.CARRIER_MAPPING);
            sql.append(" WHERE CARRIER_MAP_RRN IN ")
               .append(" (SELECT CARRIER_MAP_RRN FROM CARRIER WHERE CARRIER_RRN = ?) ");

            return jdbcTemplate.queryForObject(sql.toString(), new Object[]{carrierRrn}, Long.class);
        }

        @Override
        public List<BoxForDie> getBoxsByLotRrn(Long lotRrn) {
            String sql = "SELECT * FROM BOX_FOR_DIE WHERE LOT_RRN=" + lotRrn;
            return jdbcTemplate.query(sql, new Object[]{}, new BoxForDieMapper());
        }

        @Override
        public List<Map<String, Object>> getUnitsByLot(Lot lot) {

            String sql =
                    "SELECT UNIT_RRN,POSITION_IN_CARRIER,UNIT_ID,UNIT_STATUS,DUMMY_FLAG FROM " + DataBaseNames.UNIT + " " +
                            "WHERE LOT_RRN=" + lot.getLotRrn() + " ORDER BY POSITION_IN_CARRIER ASC";
            return jdbcTemplate.query(sql, new Object[]{}, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> m = new HashMap();
                    m.put("UNIT_RRN", new Long(rs.getLong("UNIT_RRN")));
                    m.put("POSITION_IN_CARRIER", new Long(rs.getLong("POSITION_IN_CARRIER")));
                    m.put("UNIT_ID", rs.getString("UNIT_ID"));
                    m.put("UNIT_STATUS", rs.getString("UNIT_STATUS"));
                    m.put("DUMMY_FLAG", rs.getString("DUMMY_FLAG"));
                    return m;
                }
            });
        }

        @Override
        public List<Map<String, Long>> getReworkUnitSeq(Long lotRrn, String processStepVersion) {
            String sql = "select unit_rrn,nvl(max(rework_count), 0) as num from rework_unit_info_history" +
                    " where unit_rrn in (select u.unit_rrn from unit u where u.lot_rrn= ? )" +
                    " and process_step_version like ? group by unit_rrn order by unit_rrn";
            List<Map<String, Long>> list = jdbcTemplate
                    .query(sql, new Object[]{lotRrn, processStepVersion + "%"}, new RowMapper<Map<String, Long>>() {

                        @Override
                        public Map<String, Long> mapRow(ResultSet rs, int rowNum) throws SQLException {
                            Map<String, Long> map = new HashMap<String, Long>();
                            map.put("unitRrn", rs.getLong("unit_Rrn"));
                            map.put("num", rs.getLong("num"));
                            return map;
                        }

                    });
            return list;
        }

        @Override
        public Long getMaxReworkCountByUnit(Long lotRrn, String processStepVersion) {
            String sql = "select max(num) as num from (select nvl(max(rework_count), 0) as num from " +
                    " rework_unit_info_history where unit_rrn in (select u.unit_rrn from unit u where u.lot_rrn= ? )" +
                    " and process_step_version like ? group by unit_rrn order by unit_rrn) ";

            return jdbcTemplate.queryForObject(sql, new Object[]{lotRrn, processStepVersion + "%"}, Long.class);
        }

        @Override
        public Long getMaxReworkCountByUnit(Long transRrn) {
            String sql = "SELECT MAX(NUM) AS NUM FROM (SELECT NVL(MAX(REWORK_COUNT), 0) AS NUM FROM " +
                    " REWORK_UNIT_INFO_HISTORY RH  WHERE RH.TRANS_RRN = ? GROUP BY UNIT_RRN ORDER BY UNIT_RRN)";

            return jdbcTemplate.queryForObject(sql, new Object[]{transRrn}, Long.class);
        }

        @Override
        public List<Unit> getUnitListByT7Code(String t7Code) {
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE,CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS," + "BEFORE_STATUS,DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG," + "QTY,PROCESS_START_TIME,PROCESS_END_TIME,UNIT_MAP_RRN,USAGE_LIMIT," + "USAGE_COUNT," +
                    "T7CODE,UNIT_ALIAS_1,UNIT_ALIAS_2 ,ITEM_ID FROM " + DataBaseNames.UNIT + " WHERE T7CODE = ?  ";

            List<Unit> list = jdbcTemplate.query(sql, new Object[]{t7Code}, new RowMapper<Unit>() {

                @Override
                public Unit mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Unit unit = new Unit();

                    unit.setFacilityRrn(new Long(rs.getLong("FACILITY_RRN")));
                    unit.setUnitId(rs.getString("UNIT_ID"));
                    unit.setStepSequence(new Long(rs.getLong("STEP_SEQUENCE")));
                    unit.setLotRrn(new Long(rs.getLong("LOT_RRN")));
                    unit.setLotStepSequence(new Long(rs.getLong("LOT_STEP_SEQUENCE")));
                    unit.setCarrierRrn(new Long(rs.getLong("CARRIER_RRN")));
                    unit.setPositionInCarrier(new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.setUnitStatus(rs.getString("UNIT_STATUS"));
                    unit.setProcessStatus(rs.getString("PROCESS_STATUS"));
                    unit.setBeforeStatus(rs.getString("BEFORE_STATUS"));
                    unit.setDummyFlag(rs.getString("DUMMY_FLAG"));
                    unit.setDefectiveFlag(rs.getString("DEFECTIVE_FLAG"));
                    unit.setBonusFlag(rs.getString("BONUS_FLAG"));
                    unit.setQty(new Double(rs.getDouble("QTY")));
                    unit.setProcessStartTime(rs.getDate("PROCESS_START_TIME"));
                    unit.setProcessEndTime(rs.getDate("PROCESS_END_TIME"));
                    unit.setUnitMapRrn(new Long(rs.getLong("UNIT_MAP_RRN")));
                    unit.setUnitRrn(rs.getLong("UNIT_RRN"));
                    unit.setUsageCount(rs.getInt("usage_count"));
                    unit.setUsageLimit(rs.getInt("usage_limit"));
                    unit.setT7code(rs.getString("T7CODE"));
                    unit.setUnitAlias1(rs.getString("unit_alias_1"));
                    unit.setUnitAlias2(rs.getString("unit_alias_2"));
                    unit.setItemId(rs.getString("ITEM_ID"));
                    return unit;
                }
            });
            return list;
        }

        @Override
        public Unit getUnit4Alias(Long unitRrn, String unitId, String unitAlias) {
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE,CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS," + "BEFORE_STATUS,DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG," + "QTY,PROCESS_START_TIME,PROCESS_END_TIME,UNIT_MAP_RRN," + "USAGE_LIMIT,USAGE_COUNT," +
                    "UNIT_ALIAS_1,UNIT_ALIAS_2,ITEM_ID FROM " + DataBaseNames.UNIT + " WHERE UNIT_RRN<>? AND ";

            if (StringUtils.equals(unitAlias, "UNIT_ALIAS_1")) {
                sql += "(UNIT_ID = ? OR UNIT_ALIAS_1 =?)";
            } else {
                sql += "(UNIT_ID = ? OR UNIT_ALIAS_2 =?)";
            }

            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{unitRrn, unitId, unitId}, new RowMapper<Unit>() {

                @Override
                public Unit mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Unit unit = new Unit();

                    unit.setFacilityRrn(new Long(rs.getLong("FACILITY_RRN")));
                    unit.setUnitId(rs.getString("UNIT_ID"));
                    unit.setStepSequence(new Long(rs.getLong("STEP_SEQUENCE")));
                    unit.setLotRrn(new Long(rs.getLong("LOT_RRN")));
                    unit.setLotStepSequence(new Long(rs.getLong("LOT_STEP_SEQUENCE")));
                    unit.setCarrierRrn(new Long(rs.getLong("CARRIER_RRN")));
                    unit.setPositionInCarrier(new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.setUnitStatus(rs.getString("UNIT_STATUS"));
                    unit.setProcessStatus(rs.getString("PROCESS_STATUS"));
                    unit.setBeforeStatus(rs.getString("BEFORE_STATUS"));
                    unit.setDummyFlag(rs.getString("DUMMY_FLAG"));
                    unit.setDefectiveFlag(rs.getString("DEFECTIVE_FLAG"));
                    unit.setBonusFlag(rs.getString("BONUS_FLAG"));
                    unit.setQty(new Double(rs.getDouble("QTY")));
                    unit.setProcessStartTime(rs.getDate("PROCESS_START_TIME"));
                    unit.setProcessEndTime(rs.getDate("PROCESS_END_TIME"));
                    unit.setUnitMapRrn(new Long(rs.getLong("UNIT_MAP_RRN")));
                    unit.setUnitRrn(rs.getLong("UNIT_RRN"));
                    unit.setUsageCount(rs.getInt("usage_count"));
                    unit.setUsageLimit(rs.getInt("usage_limit"));
                    unit.setUnitAlias1(rs.getString("unit_alias_1"));
                    unit.setUnitAlias2(rs.getString("unit_alias_2"));
                    unit.setItemId(rs.getString("ITEM_ID"));

                    return unit;
                }

            });
        }

        @Override
        public List<UnitTimelimitStatus> getUnitTimeLimitStatusForNormal() {
            List args = new ArrayList();
            StringBuilder sb = new StringBuilder();
            sb.append(" SELECT ");
            sb.append(" L.LOT_RRN,U.UNIT_ID,UTS.UNIT_RRN, TIMELIMIT_ID, TIMELIMIT_TYPE, ");
            sb.append(" START_PRODUCT_RRN, START_PROCESS_RRN, START_PROCESS_VERSION, START_PROCESS_VERSION ," +
                              "START_ROUTE_RRN, START_OPERATION_RRN, ");
            sb.append(" END_PRODUCT_RRN, END_PROCESS_RRN, END_ROUTE_RRN, END_OPERATION_RRN, ");
            sb.append(" TIMELIMIT, START_TIME, STATUS, ADDITIONAL_TIMELIMIT, TIMELIMIT_RRN, TIME_TYPE, " + "LIMIT_TYPE, ");
            sb.append(
                    " START_ROUTE_SEQ, START_OPERATION_SEQ, END_ROUTE_SEQ, END_OPERATION_SEQ, UTS.STEP_SEQUENCE, " + "UTS" +
                            ".MODULE" + " ");
            sb.append(" FROM " + DataBaseNames.UNIT_TIMELIMIT_STATUS + " uts,");
            sb.append(DataBaseNames.UNIT + "  u ,");
            sb.append(DataBaseNames.LOT + "  l ");
            sb.append(" WHERE UTS.UNIT_RRN=U.UNIT_RRN AND U.LOT_RRN=L.LOT_RRN ");

            sb.append(" AND UTS.STATUS=? ");
            args.add(TimeLimitEnum.NORMAL_STATUS.getValue());

            sb.append(" ORDER BY TIME_TYPE, START_TIME ");

            return jdbcTemplate.query(sb.toString(), args.toArray(), new UnitTimeLimitStatusMapper());
        }

        @Override
        public List<UnitTimelimitStatus> getUnitTimeLimitStatusByLotRrn(Long lotRrn, String targetStatus) {
            List args = new ArrayList<>();
            args.add(lotRrn);
            StringBuilder sb = new StringBuilder();
            sb.append(" SELECT ");
            sb.append(" L.LOT_RRN,U.UNIT_ID,UTS.UNIT_RRN, TIMELIMIT_ID, TIMELIMIT_TYPE, ");
            sb.append(" START_PRODUCT_RRN, START_PROCESS_RRN, START_PROCESS_VERSION, START_PROCESS_VERSION ," +
                              "START_ROUTE_RRN, START_OPERATION_RRN, ");
            sb.append(" END_PRODUCT_RRN, END_PROCESS_RRN, END_ROUTE_RRN, END_OPERATION_RRN, ");
            sb.append(" TIMELIMIT, START_TIME, STATUS, ADDITIONAL_TIMELIMIT, TIMELIMIT_RRN, TIME_TYPE, " + "LIMIT_TYPE, ");
            sb.append(
                    " START_ROUTE_SEQ, START_OPERATION_SEQ, END_ROUTE_SEQ, END_OPERATION_SEQ, UTS.STEP_SEQUENCE, " + "UTS" +
                            ".MODULE" + " ");
            sb.append(" FROM " + DataBaseNames.UNIT_TIMELIMIT_STATUS + " uts,");
            sb.append(DataBaseNames.UNIT + "  u ,");
            sb.append(DataBaseNames.LOT + "  l ");
            sb.append(" WHERE UTS.UNIT_RRN=U.UNIT_RRN AND U.LOT_RRN=L.LOT_RRN ");
            sb.append(" AND L.LOT_RRN=? ");

            if (StringUtils.isNotBlank(targetStatus)) {
                sb.append(" AND UTS.STATUS=? ");
                args.add(targetStatus);
            }

            sb.append(" ORDER BY TIME_TYPE, START_TIME ");

            return jdbcTemplate.query(sb.toString(), args.toArray(), new UnitTimeLimitStatusMapper());
        }

        @Override
        public List<Map<String, Object>> getUnitListFromCarrierMapping(Long lotRrn) {
            String sql = "select t.unit_rrn,t.position_in_carrier,u.unit_id,u.qty " + "from carrier_mapping t,unit u " +
                    "where t.carrier_rrn=(select carrier_rrn from lot where lot_rrn=?) " + "and t.unit_rrn=u.unit_rrn " +
                    "and t.carrier_map_rrn=(select carrier_map_rrn from carrier where carrier_rrn =" +
                    "(select carrier_rrn from lot where lot_rrn=?)) " + "order by t.position_in_carrier";
            Object[] args = new Object[]{new Long(lotRrn), new Long(lotRrn)};
            return jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> unit = new HashMap();
                    unit.put("unit_rrn", new Long(rs.getLong(1)));
                    unit.put("unit_id", rs.getString(3));
                    unit.put("unit_qty", new Long(rs.getLong(4)));
                    unit.put("postionInCarrier", new Long(rs.getLong(2)));
                    unit.put("unitId", rs.getString("unit_id"));
                    return unit;
                }
            });
        }

        @Override
        public List<UnitRecycledInfo> getUnitRecycledInfoByLotRrn(Long lotRrn) {

            String sql =
                    "SELECT U.LOT_RRN, UR.PROCESS_RRN, UR.START_ROUTE_RRN, UR.START_OPERATION_RRN, UR" + ".END_ROUTE_RRN," +
                            " UR.END_OPERATION_RRN, UR.LOOP_COUNT, UR.SUM_LOOP_COUNT, UR.PROCESS_VERSION, U" + ".UNIT_ID" +
                            " FROM UNIT_RECYCLED UR,UNIT U,LOT L " +
                            " WHERE UR.UNIT_RRN =U.UNIT_RRN AND U.LOT_RRN=L.LOT_RRN AND L.LOT_RRN=? ";

            List<UnitRecycledInfo> unitRecycledInfos = jdbcTemplate
                    .query(sql, new Object[]{lotRrn}, new RowMapper<UnitRecycledInfo>() {

                        @Override
                        public UnitRecycledInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                            UnitRecycledInfo unitRecycledInfo = new UnitRecycledInfo();

                            unitRecycledInfo.setLotRrn(rs.getLong("lot_rrn"));
                            unitRecycledInfo.setProcessRrn(rs.getLong("process_rrn"));
                            unitRecycledInfo.setStartRouteRrn(rs.getLong("start_route_rrn"));
                            unitRecycledInfo.setStartOperationRrn(rs.getLong("start_operation_rrn"));
                            unitRecycledInfo.setEndRouteRrn(rs.getLong("end_route_rrn"));
                            unitRecycledInfo.setEndOperationRrn(rs.getLong("end_operation_rrn"));
                            unitRecycledInfo.setLoopCount(rs.getInt("loop_count"));
                            unitRecycledInfo.setSumLoopCount(rs.getInt("SUM_LOOP_COUNT"));

                            unitRecycledInfo.setProcessVersion(rs.getInt("PROCESS_VERSION"));
                            unitRecycledInfo.setUnitId(rs.getString("UNIT_ID"));
                            return unitRecycledInfo;
                        }
                    });
            return unitRecycledInfos;

        }

        @Override
        public List<Unit> getAllUnits(Long lotRrn) {
            String sql_select =
                    "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE," + "LOT_RRN,LOT_STEP_SEQUENCE," + "CARRIER_RRN," +
                            "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS," + "BEFORE_STATUS,DUMMY_FLAG," +
                            "DEFECTIVE_FLAG,BONUS_FLAG," + "QTY,PROCESS_START_TIME,PROCESS_END_TIME,UNIT_MAP_RRN," +
                            "RECIPE_ID," + "usage_limit,USAGE_COUNT,item_id,unit_alias_1,unit_alias_2, MATERIAL_ID," +
                            "T7CODE,MARK_NUMBER,MARK_NUMBER_COMMENT,incoming_type, AUTO_MONITOR_USED_FLAG ,'' LOT_ID,CUSTOMER_T7CODE " +
                            " FROM " + DataBaseNames.UNIT + " WHERE lot_rrn = ? ORDER" + " BY POSITION_IN_CARRIER";
            Object[] args = new Object[1];
            if (lotRrn == null) {
                args[0] = 0;
            } else {
                args[0] = lotRrn.longValue();
            }
            return jdbcTemplate.query(sql_select, args, new UnitMapper());
        }

        @Override
        public List<Map> getUnitListLot(Long lotRrn) {
            String sql = "SELECT U.LOT_RRN,U.UNIT_RRN,NVL(U.UNIT_ID,'N/A'),M.POSITION_IN_CARRIER" + " ,M.AVAILABLE_FLAG,U" +
                    ".DUMMY_FLAG,U.UNIT_STATUS,L.lot_id,L.product_rrn,U.recipe_id" + " FROM " + DataBaseNames.UNIT + " U " +
                    "left join " + DataBaseNames.CARRIER_MAPPING + " M on U.UNIT_RRN  = M.UNIT_RRN," + DataBaseNames.LOT +
                    " L," + DataBaseNames.CARRIER + " C " + " WHERE L.LOT_RRN= " + lotRrn +
                    " AND L.CARRIER_RRN=C.CARRIER_RRN AND C.CARRIER_MAP_RRN = M.CARRIER_MAP_RRN" +
                    " AND L.LOT_RRN = U.LOT_RRN" + " ORDER BY M.POSITION_IN_CARRIER";

            return jdbcTemplate.query(sql, new Object[]{}, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map unit = new HashMap<>();
                    unit.put("lotRrn", rs.getString(1));
                    unit.put("unitRrn", rs.getString(2));
                    unit.put("unitId", rs.getString(3));
                    unit.put("position", rs.getString(4));
                    unit.put("available", rs.getString(5));
                    unit.put("dummyflag", rs.getString(6));
                    unit.put("unitstatus", rs.getString(7));
                    unit.put("lotid", rs.getString(8));

                    if (StringUtils.isNotEmpty(rs.getString(9))) {
                        unit.put("productRrn", rs.getString(9));
                    }
                    if (StringUtils.isNotEmpty(rs.getString("recipe_id"))) {
                        unit.put("ppid", rs.getString("recipe_id"));
                        unit.put("recipeId", rs.getString("recipe_id"));
                    } else {
                        unit.put("ppid", "");
                        unit.put("recipeId", "");
                    }
                    return unit;
                }
            });
        }

        @Override
        public String getUnitIdByRrn(Long unitRrn) {
            String sql = "SELECT UNIT_ID FROM " + DataBaseNames.UNIT + " WHERE UNIT_RRN=?";
            return jdbcTemplate.queryForObject(sql, new Object[]{MiscUtils.parseSQL(unitRrn)}, String.class);
        }

        @Override
        public String getUnitIdBySlotandLotRrnandCsdRrn(long lotRrn, long carrierRrn, Integer slot) {
            long facilityRrn = LocalContext.getFacilityRrn();
            StringBuilder sb = new StringBuilder();
            sb.append(" select t.unit_id ");
            sb.append(" from carrier_mapping c,unit t ");
            sb.append(" where c.unit_rrn = t.unit_rrn ");
            sb.append(" and t.lot_rrn =? ");
            sb.append(" and c.carrier_rrn = ? ");
            sb.append(" and t.facility_rrn = ? ");
            sb.append(" and c.position_in_carrier = ? ");
            return jdbcTemplate
                    .queryForObjectWithNull(sb.toString(), new Object[]{lotRrn, carrierRrn, facilityRrn, slot.intValue()},
                                            String.class);
        }

        @Override
        public List<Map<String, Object>> getUnitListByCarrierMapRrn(Long carrierMapRrn) {
            String sql = "SELECT U.LOT_RRN,U.UNIT_RRN,NVL(U.UNIT_ID,'N/A'),M.POSITION_IN_CARRIER" + " ,M.AVAILABLE_FLAG,U" +
                    ".DUMMY_FLAG,U.UNIT_STATUS,L.lot_id,U.LOT_STEP_SEQUENCE,L" + ".PRODUCT_RRN," + " U.recipe_id,U" +
                    ".T7CODE,U.UNIT_ALIAS_1,U.UNIT_ALIAS_2,U.ITEM_ID " + " FROM (" + DataBaseNames.CARRIER_MAPPING + " M " +
                    "LEFT JOIN " + DataBaseNames.UNIT + " U ON M.UNIT_RRN = U.UNIT_RRN) ," + DataBaseNames.LOT + " L," +
                    DataBaseNames.CARRIER + " C " + " WHERE M.carrier_map_rrn= ? AND C.CARRIER_RRN = M.CARRIER_RRN" +
                    " AND U.LOT_RRN=L.LOT_RRN" + " ORDER BY M.POSITION_IN_CARRIER";
            return jdbcTemplate.query(sql, new UnitMapRowMapper(), carrierMapRrn);
        }

        private void updateCarrierMapRrn(long lotRrn) {
            String sql = "UPDATE CARRIER " + " SET CARRIER_MAP_RRN = (SELECT MAX(CARRIER_MAP_RRN)" + " FROM " +
                    "CARRIER_MAPPING" + " WHERE UNIT_RRN IN" + " (SELECT UNIT_RRN" + " FROM UNIT" + " WHERE LOT_RRN = ?))" +
                    " WHERE CARRIER_RRN =" + " (SELECT CARRIER_RRN FROM LOT WHERE LOT_RRN = ?)" + " AND " +
                    "(CARRIER_MAP_RRN IS NULL OR CARRIER_RRN = 0)";
            Object[] args = new Object[]{new Long(lotRrn), new Long(lotRrn)};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public List<Map> getUnpackUnitListByLot(long lotRrn) {

            String sql = "SELECT u.UNIT_RRN,u.POSITION_IN_CARRIER,u.UNIT_ID,u.UNIT_STATUS,l.lot_id,u.lot_rrn," + "U" +
                    ".SOURCE_WAFER_ID,U.SOURCE_WAFER_DIE_QTY, " + " l.carrier_rrn,c.instance_id,r.sum_loop_count,r" +
                    ".loop_count " + " FROM " + DataBaseNames.UNIT + " u left join lot l on u.lot_rrn = l.lot_rrn left " +
                    "join named_object c on l" + ".carrier_rrn = c.instance_rrn left join unit_recycled r" + " on u" +
                    ".UNIT_RRN = r.UNIT_RRN WHERE u.LOT_RRN =? " + " AND u.UNIT_RRN NOT IN (SELECT P.UNIT_RRN FROM " +
                    "ERP_PACKAGE_UNIT P WHERE P.LOT_RRN=?) " + " ORDER BY POSITION_IN_CARRIER ";
            return jdbcTemplate.query(sql, new Object[]{lotRrn, lotRrn}, new RowMapper<Map>() {

                long seq = 1;

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map unit = new HashMap();
                    unit.put("unitRrn", new Long(rs.getLong("UNIT_RRN")));
                    unit.put("postionInCarrier", new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.put("position", new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.put("unitId", rs.getString("UNIT_ID"));
                    unit.put("seq", new Long(seq++));
                    unit.put("unitStatus", rs.getString("UNIT_STATUS"));
                    unit.put("lotid", rs.getString("lot_id"));
                    unit.put("lotRrn", rs.getString("lot_rrn"));
                    unit.put("carrierRrn", rs.getString("carrier_rrn"));
                    unit.put("carrierId", rs.getString("instance_id"));
                    unit.put("loopCount", rs.getInt("loop_count"));
                    unit.put("sumLoopCount", rs.getInt("sum_loop_count"));
                    if (StringUtils.isNotEmpty(rs.getString("SOURCE_WAFER_ID"))) {
                        unit.put("sourceWaferId", rs.getString("SOURCE_WAFER_ID"));
                    } else {
                        unit.put("sourceWaferId", "");
                    }
                    if (StringUtils.isNotEmpty(rs.getString("SOURCE_WAFER_DIE_QTY"))) {
                        unit.put("sourceWaferDieQty", rs.getString("SOURCE_WAFER_DIE_QTY"));
                    } else {
                        unit.put("sourceWaferDieQty", "");
                    }
                    return unit;
                }
            });
        }

        @Override
        public List<Unit> getUnitList(List<Long> lotRrnList) {
            if (CollectionUtils.isEmpty(lotRrnList)) {
                return new ArrayList<Unit>();
            }
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE," + "CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS,BEFORE_STATUS," + "DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG,QTY,PROCESS_START_TIME,PROCESS_END_TIME," + "UNIT_MAP_RRN,RECIPE_ID,USAGE_LIMIT," +
                    "USAGE_COUNT,TOTAL_DIE_NUM,T7CODE,UNIT_ALIAS_1," + "UNIT_ALIAS_2,ITEM_ID,MARK_NUMBER," +
                    "MARK_NUMBER_COMMENT,incoming_type,AUTO_MONITOR_USED_FLAG,'' LOT_ID,CUSTOMER_T7CODE,MATERIAL_ID ";
            sql += " FROM UNIT";
            sql += " WHERE LOT_RRN in (:lotRrns) ";
            Map<String, Object> args = new HashMap<String, Object>();
            args.put("lotRrns", lotRrnList);
            return jdbcTemplate.query(sql, args, new UnitMapper());
        }

        public List<Unit> getUnitListByCustomerT7Code(String customerT7code) {
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE,CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS," + "BEFORE_STATUS,DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG," + "QTY,PROCESS_START_TIME,PROCESS_END_TIME,UNIT_MAP_RRN,USAGE_LIMIT," + "USAGE_COUNT," +
                    "T7CODE,UNIT_ALIAS_1,UNIT_ALIAS_2 ,ITEM_ID,CUSTOMER_T7CODE FROM " + DataBaseNames.UNIT + " WHERE CUSTOMER_T7CODE = ?  ";

            List<Unit> list = jdbcTemplate.query(sql, new Object[]{customerT7code}, new RowMapper<Unit>() {

                @Override
                public Unit mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Unit unit = new Unit();

                    unit.setFacilityRrn(new Long(rs.getLong("FACILITY_RRN")));
                    unit.setUnitId(rs.getString("UNIT_ID"));
                    unit.setStepSequence(new Long(rs.getLong("STEP_SEQUENCE")));
                    unit.setLotRrn(new Long(rs.getLong("LOT_RRN")));
                    unit.setLotStepSequence(new Long(rs.getLong("LOT_STEP_SEQUENCE")));
                    unit.setCarrierRrn(new Long(rs.getLong("CARRIER_RRN")));
                    unit.setPositionInCarrier(new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.setUnitStatus(rs.getString("UNIT_STATUS"));
                    unit.setProcessStatus(rs.getString("PROCESS_STATUS"));
                    unit.setBeforeStatus(rs.getString("BEFORE_STATUS"));
                    unit.setDummyFlag(rs.getString("DUMMY_FLAG"));
                    unit.setDefectiveFlag(rs.getString("DEFECTIVE_FLAG"));
                    unit.setBonusFlag(rs.getString("BONUS_FLAG"));
                    unit.setQty(new Double(rs.getDouble("QTY")));
                    unit.setProcessStartTime(rs.getDate("PROCESS_START_TIME"));
                    unit.setProcessEndTime(rs.getDate("PROCESS_END_TIME"));
                    unit.setUnitMapRrn(new Long(rs.getLong("UNIT_MAP_RRN")));
                    unit.setUnitRrn(rs.getLong("UNIT_RRN"));
                    unit.setUsageCount(rs.getInt("usage_count"));
                    unit.setUsageLimit(rs.getInt("usage_limit"));
                    unit.setT7code(rs.getString("T7CODE"));
                    unit.setUnitAlias1(rs.getString("unit_alias_1"));
                    unit.setUnitAlias2(rs.getString("unit_alias_2"));
                    unit.setItemId(rs.getString("ITEM_ID"));
                    unit.setCustomerT7Code(rs.getString("CUSTOMER_T7CODE"));
                    return unit;
                }
            });
            return list;
        }

        @Override
        public List<Unit> getUnitListByMaterialId(String materialId) {
            String sql = "SELECT UNIT_RRN,FACILITY_RRN,UNIT_ID,STEP_SEQUENCE,LOT_RRN,LOT_STEP_SEQUENCE,CARRIER_RRN," +
                    "POSITION_IN_CARRIER,UNIT_STATUS,PROCESS_STATUS," + "BEFORE_STATUS,DUMMY_FLAG,DEFECTIVE_FLAG," +
                    "BONUS_FLAG," + "QTY,PROCESS_START_TIME,PROCESS_END_TIME,UNIT_MAP_RRN,USAGE_LIMIT," + "USAGE_COUNT," +
                    "T7CODE,UNIT_ALIAS_1,UNIT_ALIAS_2 ,ITEM_ID,MATERIAL_ID FROM " + DataBaseNames.UNIT + " WHERE MATERIAL_ID = ?  ";

            List<Unit> list = jdbcTemplate.query(sql, new Object[]{materialId}, new RowMapper<Unit>() {

                @Override
                public Unit mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Unit unit = new Unit();

                    unit.setFacilityRrn(new Long(rs.getLong("FACILITY_RRN")));
                    unit.setUnitId(rs.getString("UNIT_ID"));
                    unit.setStepSequence(new Long(rs.getLong("STEP_SEQUENCE")));
                    unit.setLotRrn(new Long(rs.getLong("LOT_RRN")));
                    unit.setLotStepSequence(new Long(rs.getLong("LOT_STEP_SEQUENCE")));
                    unit.setCarrierRrn(new Long(rs.getLong("CARRIER_RRN")));
                    unit.setPositionInCarrier(new Integer(rs.getInt("POSITION_IN_CARRIER")));
                    unit.setUnitStatus(rs.getString("UNIT_STATUS"));
                    unit.setProcessStatus(rs.getString("PROCESS_STATUS"));
                    unit.setBeforeStatus(rs.getString("BEFORE_STATUS"));
                    unit.setDummyFlag(rs.getString("DUMMY_FLAG"));
                    unit.setDefectiveFlag(rs.getString("DEFECTIVE_FLAG"));
                    unit.setBonusFlag(rs.getString("BONUS_FLAG"));
                    unit.setQty(new Double(rs.getDouble("QTY")));
                    unit.setProcessStartTime(rs.getDate("PROCESS_START_TIME"));
                    unit.setProcessEndTime(rs.getDate("PROCESS_END_TIME"));
                    unit.setUnitMapRrn(new Long(rs.getLong("UNIT_MAP_RRN")));
                    unit.setUnitRrn(rs.getLong("UNIT_RRN"));
                    unit.setUsageCount(rs.getInt("usage_count"));
                    unit.setUsageLimit(rs.getInt("usage_limit"));
                    unit.setT7code(rs.getString("T7CODE"));
                    unit.setUnitAlias1(rs.getString("unit_alias_1"));
                    unit.setUnitAlias2(rs.getString("unit_alias_2"));
                    unit.setItemId(rs.getString("ITEM_ID"));
                    unit.setCustomerT7Code(rs.getString("MATERIAL_ID"));
                    return unit;
                }
            });
            return list;
        }

    }