# PodDAOImpl.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.PodDAO;
    import com.mycim.server.carrier.dao.mapper.PodMapper;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.TransactionNames;
    import com.mycim.valueobject.ems.POD;
    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 PodDAOImpl implements PodDAO {

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

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Autowired
        CarrierDAO carrierDAO;

        @Override
        public POD getPod(long podRrn) {
            POD pod = 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" +
                    ".pod_map_rrn, p.load_position, " + "p.pollution_level, p.pod_code, getinstanceid(e" +
                    ".allowable_events_rrn) as " + "allowable_events_id, " + "n.object_subtype as pod_type, getinstanceid" +
                    "(e.maintenance_engineer_rrn) as " + "engineer_group_id " + "from pod 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.pod_rrn " + "and n.instance_rrn = e" +
                    ".entity_rrn and n.instance_rrn = ?";

            Object[] params = new Object[]{podRrn};

            pod = jdbcTemplate.queryForObject(sql, params, new PodMapper());
            return pod;
        }

        @Override
        public Map<String, Object> qryPodInfoDetailById(String podId, String namedSpace) {
            Map<String, Object> dataMap = new HashMap();
            dataMap.put("podId", podId);
            dataMap.put("namedSpace", namedSpace);
            String sql = this.getPodListSql(dataMap);
            dataMap = jdbcTemplate.queryForObject(sql, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> dataMap = new HashMap();
                    dataMap.put("podId", rs.getString("instance_id"));
                    dataMap.put("podRrn", rs.getLong("instance_rrn"));
                    dataMap.put("podDesc", rs.getString("instance_desc"));
                    dataMap.put("podInstanceStatus", rs.getString("instance_status"));
                    dataMap.put("podStatus", rs.getString("current_status"));
                    dataMap.put("podPollution", rs.getString("pollution_level"));
                    dataMap.put("podColor", rs.getString("pod_code"));
                    dataMap.put("podAllowable", rs.getString("allowable_events_id"));
                    dataMap.put("podType", rs.getString("pod_type"));
                    dataMap.put("podEngineer", rs.getString("engineer_group_id"));
                    String createTime = rs.getString("TRANS_START_TIMESTAMP");
                    if (StringUtils.isNotBlank(createTime)) {
                        createTime = (createTime.split("\\."))[0];
                    }
                    dataMap.put("podCreateTime", createTime);
                    dataMap.put("podCreateUser", rs.getString("create_user"));
                    dataMap.put("podCategory", rs.getString("category_data"));
                    dataMap.put("podComment", rs.getString("pod_comment"));
                    return dataMap;
                }
            });
            return dataMap;
        }

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

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

            sql.append(" (POD_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, POD_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(" EQPT_RRN, JOB_RRN, RUN_RRN, POD_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_POD,TRANS_RRN)");
            sql.append(" (SELECT POD_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, POD_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(
                    " EQPT_RRN, JOB_RRN, RUN_RRN, POD_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,STATUS,?,?,?,?,?," + "?,?,?," +
                            "? ");
            sql.append(" FROM POD WHERE POD_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,
                                        podRrn});
        }

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

            insertPodH(pod.getInstanceRrn(), StringUtils.EMPTY, transRrn);
        }

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

            insertPodH(pcdAssembly.getPodRrn(), transRrn, lotId);
        }

        @Override
        public void deletePod(POD pod, Long transRrn) {
            long instanceRrn = pod.getInstanceRrn();
            insertPodH(instanceRrn, StringUtils.EMPTY, transRrn);
            String sql = "DELETE FROM " + DataBaseNames.POD + " WHERE POD_RRN = " + instanceRrn;
            jdbcTemplate.update(sql);
        }

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

            String sql = "INSERT INTO " + DataBaseNames.POD + " (POD_RRN,SLOT_COUNT,AVAILABLE_SLOT_COUNT," + "POD_CODE," +
                    "MCS_MOVE_STATUS,MCS_LAST_MOVE_TIME," + "MCS_LAST_OPERATOR_RRN,EQPT_RRN,JOB_RRN,RUN_RRN," +
                    "POD_MAP_RRN,LOAD_POSITION,POLLUTION_LEVEL,STATUS)" + " VALUES (" + instanceRrn + "," +
                    MiscUtils.parseSQL(obj) + ")";
            jdbcTemplate.update(sql);

            insertPodH(instanceRrn, "", transRrn);
        }

        @Override
        public void updatePod(POD pod, Long transRrn) {
            String sql = "UPDATE NAMED_OBJECT SET INSTANCE_DESC = ?,OBJECT_SUBTYPE = ?,LAST_UPDATE_TIMESTAMP = " +
                    "sysdate WHERE INSTANCE_RRN = ?";
            jdbcTemplate.update(sql, new Object[]{pod.getInstanceDesc(), pod.getObjectSubtype(), pod.getInstanceRrn()});
            String sql2 = "UPDATE POD SET POD_CODE=?,POLLUTION_LEVEL=? WHERE POD_RRN = ?";
            jdbcTemplate.update(sql2, new Object[]{pod.getCarrierCode(), pod.getPollutionLevel(), pod.getInstanceRrn()});

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

            insertPodH(pod.getInstanceRrn(), StringUtils.EMPTY, transRrn);
        }

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

        @Override
        public List<Map> getPodListByPage(Map<String, Object> argMap, int startRow, int currentTotalRow) {
            String rownumSql = "select * from (select rownum as rowno, rowt.* from (" + getPodListSql(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("podId", rs.getString("instance_id"));
                    dataMap.put("podRrn", rs.getLong("instance_rrn"));
                    dataMap.put("podDesc", rs.getString("instance_desc"));
                    dataMap.put("podInstanceStatus", rs.getString("instance_status"));
                    dataMap.put("podStatus", rs.getString("current_status"));
                    dataMap.put("podPollution", rs.getString("pollution_level"));
                    dataMap.put("podColor", rs.getString("pod_code"));
                    dataMap.put("podAllowable", rs.getString("allowable_events_id"));
                    dataMap.put("podType", rs.getString("pod_type"));
                    dataMap.put("podEngineer", rs.getString("engineer_group_id"));
                    String createTime = rs.getString("TRANS_START_TIMESTAMP");
                    if (StringUtils.isNotBlank(createTime)) {
                        createTime = (createTime.split("\\."))[0];
                    }
                    dataMap.put("podCreateTime", createTime);
                    dataMap.put("podCreateUser", rs.getString("create_user"));
                    dataMap.put("podCategory", rs.getString("category_data"));
                    dataMap.put("podCleanOverDate", rs.getString("clean_over_date"));
                    dataMap.put("podRemainCleanDay", rs.getString("remain_clean_day"));

                    dataMap.put("podSlotCount", rs.getString("slot_count"));
                    dataMap.put("podLoadPosition", rs.getString("load_position"));
                    dataMap.put("podCategoryCode", rs.getString("category_code"));
                    return dataMap;
                }
            });
        }

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

            String paramSql1 = "";
            if (StringUtils.isNotBlank(MapUtils.getString(argMap, "podId"))) {
                paramSql1 += " and n.instance_id like '" + MapUtils.getString(argMap, "podId") + "' ";
            }
            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 pod_info.clean_over_date, pod_info.remain_clean_day, " + "create_trans_info" +
                    ".TRANS_PERFORMED_BY || ' ' || user_info.instance_desc as " + "create_user, to_char(new_comment" +
                    ".entity_comment) as pod_comment," + "create_trans_info.TRANS_START_TIMESTAMP, ref_category" +
                    ".category_data, ref_category" + ".category_code, " + "pod_info.instance_rrn, pod_info.instance_id, " +
                    "pod_info.instance_desc, pod_info" + ".instance_status, pod_info.current_status," + "pod_info" +
                    ".pollution_level, pod_info.pod_code, pod_info.allowable_events_id, pod_info" + ".pod_type, pod_info" +
                    ".engineer_group_id, " + "pod_info.load_position, pod_info.slot_count, " +
                    " least(COALESCE(cst_clean_over_date, door_clean_over_date, pod_info.clean_over_date), "+
                    " COALESCE(pod_info.clean_over_date, cst_clean_over_date, door_clean_over_date), "+
                    "  COALESCE(door_clean_over_date,  pod_info.clean_over_date,cst_clean_over_date))  as clean_leastDate "+
                    "from (select n" +
                    ".instance_rrn, noe.flag_type, n.instance_id, n.instance_desc, n" + ".instance_status, es" +
                    ".current_status, p.pollution_level, p.pod_code, " + "getinstanceid(e.allowable_events_rrn) as " +
                    "allowable_events_id, n.object_subtype as " + "pod_type, pc.clean_over_date, " + " to_char(TRUNC" +
                    "(clean_over_date - sysdate, 2),'fm9999999990.00') as remain_clean_day, " + "getinstanceid(e" +
                    ".maintenance_engineer_rrn) as engineer_group_id, p.load_position, p" + ".slot_count, " +
                    "  (select temp_pc1.CLEAN_OVER_DATE from PCD_CLEAN temp_pc1 where temp_pc1.INSTANCE_RRN = asbly.CARRIER_RRN) as cst_clean_over_date, "+
                    " (select temp_pc2.CLEAN_OVER_DATE from PCD_CLEAN temp_pc2  where temp_pc2.INSTANCE_RRN = asbly.DOOR_RRN)  as door_clean_over_date "+
                    "from  pod " +
                    "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 left join PCD_ASSEMBLY asbly on asbly.POD_RRN = n.INSTANCE_RRN  " +
                    "where n" +
                    ".instance_rrn = p.pod_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.POD_KEY + "' " + paramSql1 + ") pod_info left join " + "(select TL" +
                    ".TRANS_START_TIMESTAMP, TL.TRANS_PERFORMED_BY, ph.pod_rrn  from " + "transaction_log tl, pod_h ph, " +
                    "(select MAX(TL.TRANS_START_TIMESTAMP) AS TRANS_START_TIMESTAMP, ph.pod_rrn from " +
                    "pod_h ph, transaction_log tl " + "where ph.trans_rrn = tl.trans_rrn and tl.trans_id = '" +
                    TransactionNames.CREATE_KEY + "' GROUP BY ph.pod_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 ph.trans_rrn = tl.trans_rrn and ph.pod_rrn = max_create_time_info.pod_rrn) " +
                    "create_trans_info on pod_info.instance_rrn = create_trans_info.pod_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 pod_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 = pod_info.instance_rrn "
                    // + "order by create_trans_info.trans_start_timestamp desc";
                    + "order by clean_leastDate, pod_info.instance_id asc ";
            return sql;
        }

        private void insertPodH(Long podRrn, Long transRrn, String lotId) {
            Map basicInfo = carrierDAO.getBasicHistInfo(podRrn);

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

            sql.append(" (POD_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, POD_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(" EQPT_RRN, JOB_RRN, RUN_RRN, POD_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_POD,TRANS_RRN)");
            sql.append(" (SELECT POD_RRN, SLOT_COUNT, AVAILABLE_SLOT_COUNT, POD_CODE, ");
            sql.append(" MCS_MOVE_STATUS, MCS_LAST_MOVE_TIME, MCS_LAST_OPERATOR_RRN, ");
            sql.append(
                    " EQPT_RRN, JOB_RRN, RUN_RRN, POD_MAP_RRN, LOAD_POSITION,POLLUTION_LEVEL,STATUS,?,?,?,?,?," + "?,?,?," +
                            "? ");
            sql.append(" FROM POD WHERE POD_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, podRrn});
        }

        @Override
        public String generatePodId(String prefix,int width) {
            String  sql = " select INSTANCE_ID from  ";
            sql+= " (select nob.INSTANCE_ID from pod  left join NAMED_OBJECT nob on  nob.INSTANCE_RRN = POD_RRN ";
            sql+=" where nob.INSTANCE_ID like ? and trim(translate(nvl(replace(nob.INSTANCE_ID,?,''),'x'),'0123456789',' ')) is null ";
            sql+=" order by to_number(replace(nob.INSTANCE_ID, ?, '')) desc  )";
            sql+=" where ROWNUM = 1 ";
            String likeParam = prefix+StringUtils.repeat("_",width);
            String podId =jdbcTemplate.queryForObjectWithNull(sql,new Object[]{likeParam,prefix,prefix},String.class);
            if(StringUtils.isEmpty(podId)){//从1开始
                return prefix+StringUtils.repeat("0",width-1)+"1";
            }
            //累加操作 //数据转换 在sql已经检查了
            long value = Long.parseLong(podId.replace(prefix,""));
            value++;
            if(value> Long.parseLong(StringUtils.repeat("9",width))){
                return "-1";
            }
            return prefix+StringUtils.leftPad(String.valueOf(value),width,"0");
        }

        @Override
        public void updatePodType(POD pod, 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[]{pod.getObjectSubtype() ,date ,pod.getInstanceRrn()});
            insertPodH(pod.getInstanceRrn(), comments, transRrn);
        }

    }