# DoorDAOImpl.java

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

    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.MapUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.carrier.dao.CarrierDAO;
    import com.mycim.server.carrier.dao.DoorDAO;
    import com.mycim.server.carrier.dao.mapper.DoorMapper;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.TransactionNames;
    import com.mycim.valueobject.ems.Door;
    import com.mycim.valueobject.ems.PcdAssembly;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

    @Repository
    public class DoorDAOImpl implements DoorDAO {

        private final static Long DEFAULT_SLOT_COUNT = new Long(25);

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Autowired
        CarrierDAO carrierDAO;

        @Override
        public Door getDoor(long doorRrn) {
            Door door = null;
            // get the value object by object rrn
            String sql = "select n.instance_rrn, noe.flag_type, n.named_space, n.object_type, n.object, n" +
                    ".instance_id, n.instance_desc, n.instance_status, " + "es.current_status, p.mcs_move_status, p" +
                    ".mcs_last_move_time, p" + ".mcs_last_operator_rrn, p" + ".eqpt_rrn, p.job_rrn, p.run_rrn, " + "p" +
                    ".carrier_map_rrn, p.load_position, " + "p.pollution_level, p.door_code, getinstanceid(e" +
                    ".allowable_events_rrn) as " + "allowable_events_id, " + "n.object_subtype as door_type, " +
                    "getinstanceid(e.maintenance_engineer_rrn) as " + "engineer_group_id " + "from door p, entity e, " +
                    "named_object n left join named_object_ext noe on n" + ".instance_rrn = noe.instance_rrn left join " +
                    "entity_status es on n.instance_rrn = es" + ".entity_rrn " + " where n.instance_rrn = p.door_rrn" +
                    " and n.instance_rrn = e.entity_rrn and n.instance_rrn = ?";

            Object[] params = new Object[]{doorRrn};
            door = jdbcTemplate.queryForObject(sql, params, new DoorMapper());
            return door;
        }

        @Override
        public Map<String, Object> qrysDoorInfoDetailById(String doorId, String namedSpace) {
            Map<String, Object> dataMap = new HashMap();
            dataMap.put("doorId", doorId);
            dataMap.put("namedSpace", namedSpace);
            String sql = this.getDoorListSql(dataMap);
            dataMap = jdbcTemplate.queryForObject(sql, new Object[]{}, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> dataMap = new HashMap();
                    dataMap.put("doorId", rs.getString("instance_id"));
                    dataMap.put("doorRrn", rs.getLong("instance_rrn"));
                    dataMap.put("doorDesc", rs.getString("instance_desc"));
                    dataMap.put("doorInstanceStatus", rs.getString("instance_status"));
                    dataMap.put("doorStatus", rs.getString("current_status"));
                    dataMap.put("doorPollution", rs.getString("pollution_level"));
                    dataMap.put("doorColor", rs.getString("door_code"));
                    dataMap.put("doorAllowable", rs.getString("allowable_events_id"));
                    dataMap.put("doorType", rs.getString("door_type"));
                    dataMap.put("doorEngineer", rs.getString("engineer_group_id"));
                    String createTime = rs.getString("TRANS_START_TIMESTAMP");
                    if (StringUtils.isNotBlank(createTime)) {
                        createTime = (createTime.split("\\."))[0];
                    }
                    dataMap.put("doorCreateTime", createTime);
                    dataMap.put("doorCreateUser", rs.getString("create_user"));
                    dataMap.put("doorCategory", rs.getString("category_data"));
                    dataMap.put("doorComment", rs.getString("door_comment"));
                    return dataMap;
                }
            });
            return dataMap;
        }

        @Override
        public void insertDoorH(Long doorRrn, String comments, Long transRrn) {
            Map basicInfo = carrierDAO.getBasicHistInfo(doorRrn);

            StringBuilder sql = new StringBuilder("INSERT INTO DOOR_H ");

            sql.append(" (DOOR_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, DOOR_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(" EQPT_RRN, JOB_RRN, RUN_RRN, CARRIER_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,status,");
            sql.append(
                    " FLAG_TYPE,OBJECT_SUBTYPE,DESCription,ALLOWABLE_EVENTS_RRN,MAINTENANCE_ENGINEER_RRN," + "COMMENTS,");
            sql.append(" LOT_ID,TARGET_DOOR,TRANS_RRN)");
            sql.append(" (SELECT DOOR_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, DOOR_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(
                    " EQPT_RRN, JOB_RRN, RUN_RRN, CARRIER_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,STATUS,?,?,?," + "?,?,?," +
                            "?,?,? ");
            sql.append(" FROM DOOR WHERE DOOR_RRN = ?) ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{MapUtils.getString(basicInfo, "category"), MapUtils.getString(basicInfo,
                                                                                                           "objectType"),
                                        MapUtils.getString(
                                        basicInfo, "instanceDesc"), MapUtils.getLong(basicInfo,
                                                                                     "allowableRrn"), MapUtils.getLong(
                                        basicInfo, "engineerRrn"), comments, MapUtils.getString(basicInfo,
                                                                                                "lotId"), "", transRrn,
                                        doorRrn});
        }

        @Override
        public void updateDoorByDeassembly(Long transRrn, Door door) {
            String sql = "update door set mcs_move_status=?, mcs_last_move_time=?, mcs_last_operator_rrn=?, " + "eqpt_rrn" +
                    "=?, job_rrn=?, run_rrn=?, load_position=?, pollution_level=?, " + "status=? where door_rrn = ?";
            jdbcTemplate.update(sql,
                                new Object[]{door.getMcsMoveStatus(), door.getMcsLastMoveTime(),
                                        door.getMcsLastOperatorRrn(), door.getEqptRrn(), door.getJobRrn(),
                                        door.getRunRrn(), door.getLoadPosition(), door.getPollutionLevel(),
                                        door.getDoorStatus(), door.getInstanceRrn()});

            insertDoorH(door.getInstanceRrn(), StringUtils.EMPTY, transRrn);
        }

        @Override
        public void updateDoorStatus(PcdAssembly pcdAssembly, Long transRrn, String status, String lotId) {
            String sql = " UPDATE DOOR SET STATUS = ? WHERE DOOR_RRN=?";
            jdbcTemplate.update(sql, new Object[]{status, pcdAssembly.getDoorRrn()});

            insertDoorH(pcdAssembly.getDoorRrn(), transRrn, lotId);
        }

        @Override
        public int getDoorTotalCount(Map<String, Object> argMap) {
            String countSql = "select count(*) from (" + getDoorListSql(argMap) + ") countt";
            int count = this.jdbcTemplate.queryForObject(countSql, int.class);
            return count;
        }

        @Override
        public List<Map> getDoorListByPage(Map<String, Object> argMap, int startRow, int currentTotalRow) {
            String rownumSql = "select * from (select rownum as rowno, rowt.* from (" + getDoorListSql(argMap) +
                    ")rowt )rowtt where rowno <= ? ";
            String pageSql = "select * from (" + rownumSql + ") paget where rowno > ? ";

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

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> dataMap = new HashMap<String, Object>();
                    dataMap.put("doorId", rs.getString("instance_id"));
                    dataMap.put("doorRrn", rs.getLong("instance_rrn"));
                    dataMap.put("doorDesc", rs.getString("instance_desc"));
                    dataMap.put("doorInstanceStatus", rs.getString("instance_status"));
                    dataMap.put("doorStatus", rs.getString("current_status"));
                    dataMap.put("doorPollution", rs.getString("pollution_level"));
                    dataMap.put("doorColor", rs.getString("door_code"));
                    dataMap.put("doorAllowable", rs.getString("allowable_events_id"));
                    dataMap.put("doorType", rs.getString("door_type"));
                    dataMap.put("doorEngineer", rs.getString("engineer_group_id"));
                    String createTime = rs.getString("TRANS_START_TIMESTAMP");
                    if (StringUtils.isNotBlank(createTime)) {
                        createTime = (createTime.split("\\."))[0];
                    }
                    dataMap.put("doorCreateTime", createTime);
                    dataMap.put("doorCreateUser", rs.getString("create_user"));
                    dataMap.put("doorCategory", rs.getString("category_data"));
                    dataMap.put("doorCleanOverDate", rs.getString("clean_over_date"));
                    dataMap.put("doorRemainCleanDay", rs.getString("remain_clean_day"));

                    dataMap.put("doorSlotCount", rs.getString("slot_count"));
                    dataMap.put("doorLoadPosition", rs.getString("load_position"));
                    dataMap.put("doorCategoryCode", rs.getString("category_code"));
                    return dataMap;
                }
            });
        }

        @Override
        public void updateDoor(Door door, Long transRrn) {
            String sql = "UPDATE NAMED_OBJECT SET INSTANCE_DESC = ?,OBJECT_SUBTYPE = ?,LAST_UPDATE_TIMESTAMP = " +
                    "sysdate WHERE INSTANCE_RRN = ?";
            jdbcTemplate.update(sql, new Object[]{door.getInstanceDesc(), door.getObjectSubtype(), door.getInstanceRrn()});
            String sql2 = "UPDATE DOOR SET DOOR_CODE=?,POLLUTION_LEVEL=? WHERE DOOR_RRN = ?";
            jdbcTemplate.update(sql2, new Object[]{door.getCarrierCode(), door.getPollutionLevel(), door.getInstanceRrn()});

            String sql3 = "UPDATE ENTITY SET ALLOWABLE_EVENTS_RRN=?,MAINTENANCE_ENGINEER_RRN=? WHERE ENTITY_RRN" + " =?";
            jdbcTemplate.update(sql3,
                                new Object[]{door.getAllowableEventsRrn(), door.getMaintenanceEngineerRrn(),
                                        door.getInstanceRrn()});

            insertDoorH(door.getInstanceRrn(), StringUtils.EMPTY, transRrn);
        }

        @Override
        public void insertDoor(Door door, Long transRrn) {
            long instanceRrn = door.getInstanceRrn();
            Object[] obj = {(door.getSlotCount() == null) ? DEFAULT_SLOT_COUNT : door
                    .getSlotCount(), door.getAvailableSlotCount(), door.getCarrierCode(), door.getMcsMoveStatus(),
                    door.getMcsLastMoveTime(), door.getMcsLastOperatorRrn(), door.getEqptRrn(), door.getJobRrn(),
                    door.getRunRrn(), door.getCarrierMapRrn(), door.getLoadPosition(), door.getPollutionLevel(),
                    door.getDoorStatus()};

            String sql = "INSERT INTO " + DataBaseNames.DOOR + " (DOOR_RRN,SLOT_COUNT,AVAILABLE_SLOT_COUNT," +
                    "DOOR_CODE,MCS_MOVE_STATUS,MCS_LAST_MOVE_TIME," + "MCS_LAST_OPERATOR_RRN,EQPT_RRN,JOB_RRN,RUN_RRN," +
                    "CARRIER_MAP_RRN,LOAD_POSITION,POLLUTION_LEVEL,STATUS)" + " VALUES (" + instanceRrn + "," +
                    MiscUtils.parseSQL(obj) + ")";
            jdbcTemplate.update(sql);

            insertDoorH(instanceRrn, StringUtils.EMPTY, transRrn);
        }

        @Override
        public void deleteDoor(Door door, Long transRrn) {
            long instanceRrn = door.getInstanceRrn();
            insertDoorH(instanceRrn, StringUtils.EMPTY, transRrn);
            String sql = "DELETE FROM " + DataBaseNames.DOOR + " WHERE DOOR_RRN = " + instanceRrn;
            jdbcTemplate.update(sql);
        }

        private String getDoorListSql(Map<String, Object> argMap) {

            String paramSql1 = "";
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "doorId"))) {
                paramSql1 += " and n.instance_id like '" + MapUtils.getString(argMap, "doorId") + "' ";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "status"))) {
                paramSql1 += " and es.current_status = '" + MapUtils.getString(argMap, "status") + "' ";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "type"))) {
                paramSql1 += " and n.object_subtype = '" + MapUtils.getString(argMap, "type") + "' ";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "category"))) {
                paramSql1 += " and noe.flag_type = '" + MapUtils.getString(argMap, "category") + "' ";
            }

            String namedSpace = MapUtils.getString(argMap, "namedSpace").toUpperCase();

            String sql = "select distinct door_info.clean_over_date, door_info.remain_clean_day, " + "create_trans_info" +
                    ".trans_start_timestamp, to_char(new_comment.entity_comment) as " + "door_comment, ref_category" +
                    ".category_data, ref_category.category_code, " + "(create_trans_info.trans_performed_by || ' ' || " +
                    "user_info.instance_desc) as " + "create_user, door_info.instance_rrn, " + "door_info.instance_id, " +
                    "door_info.instance_desc, door_info.instance_status, " + "door_info.current_status, " + "door_info" +
                    ".pollution_level, door_info.door_code, door_info.allowable_events_id, " + "door_info.door_type, " +
                    "door_info.engineer_group_id," + "door_info.load_position, door_info.slot_count " + "from (select n" +
                    ".instance_rrn, noe.flag_type, n.instance_id, n.instance_desc, n" + ".instance_status, es" +
                    ".current_status, " + "p.pollution_level, p.door_code, pc.clean_over_date, " + " to_char(TRUNC" +
                    "(clean_over_date - sysdate, 2),'fm9999999990.00') as remain_clean_day, " + "getinstanceid(e" +
                    ".allowable_events_rrn) as allowable_events_id, n.object_subtype as " + "door_type, " +
                    "getinstanceid(e.maintenance_engineer_rrn) as engineer_group_id, p.load_position, p" + ".slot_count " +
                    "from door p, entity e, entity_status es, " +
                    " named_object n left join NAMED_OBJECT_EXT NOE on n.instance_rrn = noe.instance_rrn " +
                    " left join pcd_clean pc on n.instance_rrn = pc.instance_rrn " +
                    "where n.instance_rrn = p.door_rrn and n.instance_rrn = e.entity_rrn and n" +
                    ".instance_rrn = es.entity_rrn " + "and n.named_space = '" + namedSpace + "' " + "and n.object = '" +
                    ObjectList.ENTITY_KEY + "' and n.object_type = '" + ObjectList.DOOR_KEY + "' " + paramSql1 +
                    ") door_info left join " +
                    "(select TL.TRANS_START_TIMESTAMP, TL.TRANS_PERFORMED_BY, dh.door_rrn  from " +
                    "transaction_log tl, door_h dh, " +
                    "(select MAX(TL.TRANS_START_TIMESTAMP) AS TRANS_START_TIMESTAMP, dh.door_rrn from " +
                    "door_h dh, transaction_log tl " + "where dh.trans_rrn = tl.trans_rrn and tl.trans_id = '" +
                    TransactionNames.CREATE_KEY + "' GROUP BY dh.door_rrn) max_create_time_info " +
                    "where tl.trans_start_timestamp = max_create_time_info.TRANS_START_TIMESTAMP and tl" + ".trans_id = '" +
                    TransactionNames.CREATE_KEY + "' " +
                    "and dh.trans_rrn = tl.trans_rrn and dh.door_rrn = max_create_time_info.door_rrn) " +
                    "create_trans_info on door_info.instance_rrn = create_trans_info.door_rrn left join " + "(select " +
                    "instance_id, instance_desc from named_object where " + "object = '" + ObjectList.USER_KEY +
                    "') user_info on create_trans_info.trans_performed_by = user_info.instance_id left " + "join " +
                    "(select refd.key_1_value as category_code, refd.data_1_value as category_data from " +
                    "reference_file_detail refd, named_object n " +
                    "where n.instance_id = '$PCD_CATEGORY' and n.named_space = 'MYCIM2' and n" +
                    ".instance_rrn = refd.reference_file_rrn) ref_category on door_info.flag_type = " +
                    "ref_category.category_code left join " + "(select e1.entity_rrn, e1.create_time, e1.entity_comment " +
                    "from entity_comment_h e1, (select e.entity_rrn, max(e.create_time) as " +
                    "max_create_time from entity_comment_h e group by e.entity_rrn) e2 " +
                    "where e1.entity_rrn = e2.entity_rrn and e1.create_time = e2.max_create_time) " +
                    "new_comment on new_comment.entity_rrn = door_info.instance_rrn "
                    // + "order by create_trans_info.trans_start_timestamp desc";
                    + "order by door_info.instance_id asc ";

            return sql;
        }

        private void insertDoorH(Long doorRrn, Long transRrn, String lotId) {
            Map basicInfo = carrierDAO.getBasicHistInfo(doorRrn);
            StringBuilder sql = new StringBuilder("INSERT INTO DOOR_H ");

            sql.append(" (DOOR_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, DOOR_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(" EQPT_RRN, JOB_RRN, RUN_RRN, CARRIER_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,status,");
            sql.append(
                    " FLAG_TYPE,OBJECT_SUBTYPE,DESCription,ALLOWABLE_EVENTS_RRN,MAINTENANCE_ENGINEER_RRN," + "COMMENTS,");
            sql.append(" LOT_ID,TARGET_DOOR,TRANS_RRN)");
            sql.append(" (SELECT DOOR_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, DOOR_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(
                    " EQPT_RRN, JOB_RRN, RUN_RRN, CARRIER_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,STATUS,?,?,?," + "?,?,?," +
                            "?,?,? ");
            sql.append(" FROM DOOR WHERE DOOR_RRN = ?) ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{MapUtils.getString(basicInfo, "category"), MapUtils.getString(basicInfo,
                                                                                                           "objectType"),
                                        MapUtils.getString(
                                        basicInfo, "instanceDesc"), MapUtils.getLong(basicInfo,
                                                                                     "allowableRrn"), MapUtils.getLong(
                                        basicInfo, "allowableRrn"), "", (StringUtils.isNotBlank(lotId) ? lotId : MapUtils
                                        .getString(basicInfo, "lotId")), "", transRrn, doorRrn});
        }

        @Override
        public void updateDoorType(Door door, String comments, Long transRrn) {
            Date date = DateUtils.parse(DateUtils.getNowTime());
            String sql = "UPDATE NAMED_OBJECT SET OBJECT_SUBTYPE = ?,LAST_UPDATE_TIMESTAMP = ? WHERE INSTANCE_RRN = ?";
            jdbcTemplate.update(sql, new Object[]{door.getObjectSubtype() ,date , door.getInstanceRrn()});

            insertDoorH(door.getInstanceRrn(), comments, transRrn);
        }

    }