# PcdDAOImpl.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.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.carrier.dao.PcdDAO;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.ems.PcdAssembly;
    import com.mycim.valueobject.ems.PcdClean;
    import com.mycim.valueobject.wip.TransReason;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

    @Repository
    public class PcdDAOImpl implements PcdDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public PcdAssembly getPcdAssembly(Long carrierRrn, Long podRrn, Long doorRrn) {
            String sql = "SELECT CARRIER_RRN,getinstanceid(CARRIER_RRN) as CARRIER_ID,POD_RRN,getinstanceid(POD_RRN)" +
                    " as POR_ID," + "DOOR_RRN,getinstanceid(DOOR_RRN) as DOOR_ID,CREATE_USER,CREATE_TIME FROM " +
                    "PCD_ASSEMBLY " + "WHERE 1=1";
            if (carrierRrn != null && carrierRrn.longValue() > 0) {
                sql += " AND CARRIER_RRN=" + carrierRrn;
            }
            if (podRrn != null && podRrn.longValue() > 0) {
                sql += " AND POD_RRN=" + podRrn;
            }
            if (doorRrn != null && doorRrn.longValue() > 0) {
                sql += " AND DOOR_RRN=" + doorRrn;
            }
            List<PcdAssembly> list = jdbcTemplate.query(sql, new RowMapper<PcdAssembly>() {

                @Override
                public PcdAssembly mapRow(ResultSet rs, int rowNum) throws SQLException {
                    PcdAssembly pcd = new PcdAssembly();
                    pcd.setCarrierId(rs.getString("CARRIER_ID"));
                    pcd.setCarrierRrn(rs.getLong("CARRIER_RRN"));
                    pcd.setPodId(rs.getString("POR_ID"));
                    pcd.setPodRrn(rs.getLong("POD_RRN"));
                    pcd.setDoorId(rs.getString("DOOR_ID"));
                    pcd.setDoorRrn(rs.getLong("DOOR_RRN"));
                    pcd.setCreatedUserId(rs.getString("CREATE_USER"));
                    pcd.setCreatedTime(rs.getString("CREATE_TIME"));
                    pcd.setCreatedTimestamp(rs.getTimestamp("CREATE_TIME"));
                    return pcd;
                }
            });
            if (list != null && list.size() > 0) {
                return list.get(0);
            } else {
                return null;
            }
        }

        @Override
        public PcdClean getPcdCleanInfo(Long pcdRrn) {
            String sql = "SELECT getinstanceid(C.INSTANCE_RRN) AS PCD_ID, C.INSTANCE_RRN, C.IN_CLEAN_DATE, C" +
                    ".CLEAN_OVER_DATE, C.CLEAN_CYCLE, C.PCD_TYPE, C.OUT_CLEAN_DATE, " + "C.CLEAN_COUNT, C.EQPT_ID FROM " +
                    "PCD_CLEAN C WHERE C.INSTANCE_RRN = ?";

            return jdbcTemplate.queryForObject(sql, new Object[]{pcdRrn}, new RowMapper<PcdClean>() {

                @Override
                public PcdClean mapRow(ResultSet rs, int rowNum) throws SQLException {
                    PcdClean cleanInfo = new PcdClean();
                    cleanInfo.setInstanceId(rs.getString("PCD_ID"));
                    cleanInfo.setInstanceRrn(rs.getLong("INSTANCE_RRN"));
                    cleanInfo.setInCleanDate(rs.getTimestamp("IN_CLEAN_DATE"));
                    cleanInfo.setCleanOverDate(rs.getTimestamp("CLEAN_OVER_DATE"));
                    cleanInfo.setCleanCycle(rs.getDouble("CLEAN_CYCLE"));
                    cleanInfo.setPcdType(rs.getString("PCD_TYPE"));
                    cleanInfo.setOutCleanDate(rs.getTimestamp("OUT_CLEAN_DATE"));
                    cleanInfo.setCleanCount(rs.getInt("CLEAN_COUNT"));
                    cleanInfo.setCleanEqptId(rs.getString("EQPT_ID"));
                    if (cleanInfo.getInCleanDate() != null) {
                        long now = DateUtils.parse(DateUtils.getNowTime()).getTime();
                        long inClean = cleanInfo.getInCleanDate().getTime();
                        cleanInfo.setCleaningMinutes((now - inClean) / 1000 / 60);
                    }
                    return cleanInfo;
                }
            });
        }

        @Override
        public Map getPcdDefaultCleanCycleInfo(String category, String type) {
            String sql = "SELECT PD.CATEGORY, PD.TYPE, PD.DEFAULT_CLEAN_CYCLE, PD.OPERATION_DATE, PD.OPERATION_USER " +
                    "FROM  PCD_DEFALULT_CLEAN_CYCLE PD WHERE PD.CATEGORY = ? AND PD.TYPE = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{category, type}, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap();
                    map.put("category", rs.getString("CATEGORY"));
                    map.put("type", rs.getString("TYPE"));
                    map.put("defaultCleanCycle", rs.getDouble("DEFAULT_CLEAN_CYCLE"));
                    map.put("operationDate", rs.getTimestamp("OPERATION_DATE"));
                    map.put("operationUser", rs.getString("OPERATION_USER"));
                    return map;
                }
            });
        }

        @Override
        public String getCarrierIdSerial(String prefix) {
            String sql = "SELECT nvl(MAX(SUFFIX),'0000') as a  FROM (select SUBSTR(n.instance_id, 3) " + "SUFFIX from " +
                    "named_object n, carrier c where n.instance_rrn=c.carrier_rrn and n" + ".instance_id like ?) t_0  " +
                    "WHERE REGEXP_LIKE (SUFFIX , '^[0-9]{4}$')";
            return jdbcTemplate.queryForObject(sql, new Object[]{"%" + prefix + "%"}, String.class);
        }

        @Override
        public String getCarrierIdSerial(String prefix, int serialLength) {
            String zeros = "";
            for (int i = 0; i < serialLength; i++) {
                zeros += "0";
            }
            int prefixLen = prefix != null ? prefix.length() : 0;
            String sql = "SELECT nvl(MAX(SUFFIX),'" + zeros + "') as a  FROM (select SUBSTR(n.instance_id, ?) " + "SUFFIX" +
                    " from named_object n, carrier c where n.instance_rrn=c.carrier_rrn and n" + ".instance_id like ?) " +
                    "t_0  WHERE REGEXP_LIKE ( SUFFIX , '^[0-9]{" + serialLength + "}$')";
            return jdbcTemplate.queryForObject(sql, new Object[]{prefixLen + 1, "%" + prefix + "%"}, String.class);
        }

        @Override
        public int deletePcdCleanInfo(Long pcdRrn) {
            String sql = "delete from pcd_clean pc where pc.instance_rrn = ?";
            int successCount = jdbcTemplate.update(sql, new Object[]{pcdRrn});
            return successCount;
        }

        @Override
        public int insertPcdCleanH(Long transRrn, PcdClean cleanInfo) {
            StringBuffer sql = new StringBuffer("INSERT INTO PCD_CLEAN_H (TRANS_RRN, INSTANCE_RRN");
            StringBuffer values = new StringBuffer(" VALUES (?, ?");
            List<Object> args = new ArrayList();
            args.add(transRrn);
            args.add(cleanInfo.getInstanceRrn());
            if (cleanInfo.getInCleanDate() != null) {
                sql.append(", IN_CLEAN_DATE");
                values.append(", ?");
                args.add(cleanInfo.getInCleanDate());
            }
            if (cleanInfo.getCleanOverDate() != null) {
                sql.append(", CLEAN_OVER_DATE");
                values.append(", ?");
                args.add(cleanInfo.getCleanOverDate());
            }
            if (cleanInfo.getCleanCycle() > 0) {
                sql.append(", CLEAN_CYCLE");
                values.append(", ?");
                args.add(cleanInfo.getCleanCycle());
            }
            if (StringUtils.isNotBlank(cleanInfo.getPcdType())) {
                sql.append(", PCD_TYPE");
                values.append(", ?");
                args.add(cleanInfo.getPcdType());
            }
            if (cleanInfo.getOutCleanDate() != null) {
                sql.append(", OUT_CLEAN_DATE");
                values.append(", ?");
                args.add(cleanInfo.getOutCleanDate());
            }

            sql.append(", EQPT_ID ");
            values.append(", ?");
            args.add(cleanInfo.getCleanEqptId());

            sql.append(")");
            values.append(")");
            sql.append(values);

            int successCount = jdbcTemplate.update(sql.toString(), args.toArray());
            return successCount;
        }

        @Override
        public void insertPcdNamedObjectExt(Long pcdRrn, String flagType, String dmm_type) {
            String sql = "insert into named_object_ext (instance_rrn, flag_type, dmm_type) values (?, ?, ?)";
            jdbcTemplate.update(sql, new Object[]{pcdRrn, flagType, dmm_type});
        }

        @Override
        public int insertPcdCleanInfo(PcdClean cleanInfo) {
            String sql = "INSERT INTO PCD_CLEAN (INSTANCE_RRN, CLEAN_OVER_DATE, CLEAN_CYCLE, PCD_TYPE, " + "CLEAN_COUNT) " +
                    "VALUES (?, ?, ?, ?, ?)";
            Object[] args = new Object[]{cleanInfo.getInstanceRrn(), cleanInfo.getCleanOverDate(),
                    cleanInfo.getCleanCycle(), cleanInfo.getPcdType(), cleanInfo.getCleanCount()};
            int successCount = jdbcTemplate.update(sql, args);
            return successCount;
        }

        @Override
        public void updatePcdNamedObjectExt(Long pcdRrn, String flagType) {
            String sql = "update named_object_ext set flag_type = ? where instance_rrn = ?";
            jdbcTemplate.update(sql, new Object[]{flagType, pcdRrn});
        }

        @Override
        public int countPcdCategoryByRrn(Long pcdRrn) {
            String sql = "select count(n.flag_type) from named_object_ext n where n.instance_rrn = ?";
            return jdbcTemplate.queryForObject(sql, new Object[]{pcdRrn}, int.class);
        }

        @Override
        public int updatePcdCleanInfo(PcdClean cleanInfo) {
            StringBuffer sql = new StringBuffer("UPDATE PCD_CLEAN SET INSTANCE_RRN=?");
            List<Object> args = new ArrayList<Object>();
            args.add(cleanInfo.getInstanceRrn());
            if (cleanInfo.getInCleanDate() != null) {
                sql.append(", IN_CLEAN_DATE=?");
                args.add(cleanInfo.getInCleanDate());
            }
            if (cleanInfo.getCleanOverDate() != null) {
                sql.append(", CLEAN_OVER_DATE=?");
                args.add(cleanInfo.getCleanOverDate());
            }
            if (cleanInfo.getCleanCycle() > 0) {
                sql.append(", CLEAN_CYCLE=?");
                args.add(cleanInfo.getCleanCycle());
            }
            if (StringUtils.isNotBlank(cleanInfo.getPcdType())) {
                sql.append(", PCD_TYPE=?");
                args.add(cleanInfo.getPcdType());
            }
            if (cleanInfo.getOutCleanDate() != null) {
                sql.append(", OUT_CLEAN_DATE=? ");
                args.add(cleanInfo.getOutCleanDate());
            } else {
                sql.append(", OUT_CLEAN_DATE=? ");
                args.add(null);
            }
            if (cleanInfo.getCleanCount() >= 0) {
                sql.append(", CLEAN_COUNT=? ");
                args.add(cleanInfo.getCleanCount());
            }

            sql.append(", EQPT_ID=? ");
            args.add(cleanInfo.getCleanEqptId());

            sql.append(" WHERE INSTANCE_RRN=?");
            args.add(cleanInfo.getInstanceRrn());
            int successCount = jdbcTemplate.update(sql.toString(), args.toArray());
            return successCount;
        }

        @Override
        public void deletePcdAssembly(Long carrierRrn) {
            String sql = "DELETE FROM PCD_ASSEMBLY WHERE CARRIER_RRN=?";
            Object[] args = new Object[]{carrierRrn};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void insertPcdAssemblyH(Long carrierRrn, String user, Long transRrn) {
            String sql = "INSERT INTO PCD_ASSEMBLY_H (TRANS_RRN,CARRIER_RRN,POD_RRN,DOOR_RRN,CREATE_USER," +
                    "CREATE_TIME,UPDATE_USER,UPDATE_TIME) " + " SELECT ?,CARRIER_RRN,POD_RRN,DOOR_RRN,CREATE_USER," +
                    "CREATE_TIME,?,sysdate FROM " + "PCD_ASSEMBLY WHERE CARRIER_RRN = ?";
            Object[] args = new Object[]{transRrn, user, carrierRrn};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void insertPcdAssemblyH(Long carrierRrn, Long podRrn, Long doorRrn, String user, Long transRrn) {
            String sql = "INSERT INTO PCD_ASSEMBLY_H (TRANS_RRN,CARRIER_RRN,POD_RRN,DOOR_RRN,CREATE_USER," +
                    "CREATE_TIME,UPDATE_USER,UPDATE_TIME) VALUES (?,?,?,?,?,sysdate,?,sysdate)";
            Object[] args = new Object[]{transRrn, carrierRrn, podRrn, doorRrn, user, user};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void insertPcdAssembly(Long carrierRrn, Long podRrn, Long doorRrn, String user) {
            String sql = "INSERT INTO PCD_ASSEMBLY (CARRIER_RRN,POD_RRN,DOOR_RRN,CREATE_USER,CREATE_TIME) " + "VALUES (?," +
                    "?,?,?,sysdate)";
            Object[] args = new Object[]{carrierRrn, podRrn, doorRrn, user};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public boolean isAssembledPCD(Long instanceRrn) {
            String sql = "SELECT COUNT(*)  FROM PCD_ASSEMBLY  WHERE CARRIER_RRN = ? OR POD_RRN = ? OR DOOR_RRN " + "= ?";
            long count = jdbcTemplate
                    .queryForObjectWithNull(sql, new Object[]{instanceRrn, instanceRrn, instanceRrn}, Long.class);
            return count > 0 ? true : false;
        }

        @Override
        public List<Map> getHoldReasons(long instanceRrn) {
            String sql = "SELECT REASON_CATEGORY,REASON_CODE,REASON," + "SEQUENCE_NUMBER,HOLD_PASSWORD,INSTANCE_ID," +
                    "HOLD_TIMESTAMP,B.HOLD_BY" + " FROM " + DataBaseNames.TRANS_REASON + " A," +
                    DataBaseNames.MULTIPLE_HOLD + " B," + DataBaseNames.NAMEDOBJECT + " C" +
                    " WHERE A.INSTANCE_RRN = B.INSTANCE_RRN" + " AND A.TRANS_RRN = B.TRANS_RRN" +
                    " AND A.INSTANCE_RRN = ?" + " AND B.HOLD_BY = C.INSTANCE_RRN";
            return jdbcTemplate.query(sql, new Object[]{instanceRrn}, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map holdReason = new HashMap();
                    holdReason.put("instanceRrn", (new Long(instanceRrn)).toString());
                    holdReason.put("reasonCategory", rs.getString(1));
                    holdReason.put("reasonCode", rs.getString(2));
                    holdReason.put("reason", rs.getString(3));
                    holdReason.put("sequenceNumber", rs.getString(4));
                    holdReason.put("holdPassword", rs.getString(5));
                    String holdName = getTransByName(rs.getString(6));
                    if (holdName != null) {
                        holdReason.put("holdBy", rs.getString(6) + " " + holdName);
                    } else {
                        holdReason.put("holdBy", rs.getString(6));
                    }
                    holdReason.put("holdByRrn", rs.getString("HOLD_BY"));
                    if (rs.getTimestamp(7) != null) {
                        SimpleDateFormat formatter = new SimpleDateFormat(DateUtils.DATE_FORMAT);
                        holdReason.put("holdDate", formatter.format(rs.getTimestamp(7)));
                    } else {
                        holdReason.put("holdDate", "");
                    }
                    return holdReason;
                }
            });
        }

        @Override
        public PcdAssembly getPcdAssembly(Long carrierRrn) {
            String sql = "SELECT CARRIER_RRN,getinstanceid(CARRIER_RRN) as CARRIER_ID,POD_RRN,getinstanceid(POD_RRN)" +
                    " as POR_ID," + " DOOR_RRN,getinstanceid(DOOR_RRN) as DOOR_ID,CREATE_USER,CREATE_TIME FROM " +
                    "PCD_ASSEMBLY " + "WHERE 1=1 " + " AND CARRIER_RRN=" + carrierRrn;
            List<PcdAssembly> list = jdbcTemplate.query(sql, new RowMapper<PcdAssembly>() {

                @Override
                public PcdAssembly mapRow(ResultSet rs, int rowNum) throws SQLException {
                    PcdAssembly pcd = new PcdAssembly();
                    pcd.setCarrierId(rs.getString("CARRIER_ID"));
                    pcd.setCarrierRrn(rs.getLong("CARRIER_RRN"));
                    pcd.setPodId(rs.getString("POR_ID"));
                    pcd.setPodRrn(rs.getLong("POD_RRN"));
                    pcd.setDoorId(rs.getString("DOOR_ID"));
                    pcd.setDoorRrn(rs.getLong("DOOR_RRN"));
                    pcd.setCreatedUserId(rs.getString("CREATE_USER"));
                    pcd.setCreatedTime(rs.getString("CREATE_TIME"));
                    pcd.setCreatedTimestamp(rs.getTimestamp("CREATE_TIME"));
                    return pcd;
                }
            });
            if (list != null && list.size() > 0) {
                return list.get(0);
            } else {
                return null;
            }
        }

        @Override
        public void insertMultipleHold(long instanceRrn, long holdBy, long transRrn) {
            String sql = "INSERT INTO " + DataBaseNames.MULTIPLE_HOLD + "(INSTANCE_RRN,OBJECT,SEQUENCE_NUMBER," +
                    "HOLD_BY,HOLD_TIMESTAMP,TRANS_RRN) VALUES(?,?," + "NVL((SELECT MAX(NVL(SEQUENCE_NUMBER,0))+1 FROM " +
                    DataBaseNames.MULTIPLE_HOLD + " WHERE INSTANCE_RRN = ?),1),?,?,?)";
            jdbcTemplate.update(sql, instanceRrn, ObjectList.CARRIER_KEY, instanceRrn, holdBy,
                                new java.sql.Timestamp(System.currentTimeMillis()), transRrn);
        }

        @Override
        public void insertTransReason(long transRrn, long instanceRrn, TransReason transReason, int reasonCodeSequence) {

            String sql = "INSERT INTO " + DataBaseNames.TRANS_REASON + "(TRANS_RRN,INSTANCE_RRN,REASON_CODE_SEQUENCE," +
                    "REASON_CATEGORY," + "REASON_CODE,REASON,TRANS_QTY1,TRANS_QTY2,RESPONSIBILITY,ACCOUNT_CODE) " +
                    "VALUES" + "(?,?,?,?,?,?,?,?,?,?)";

            if (transReason.getTransQty1() == null) {
                transReason.setTransQty1(0.0);
            }

            if (transReason.getTransQty2() == null) {
                transReason.setTransQty2(0.0);
            }

            Object[] args = {transRrn, instanceRrn, reasonCodeSequence, transReason.getReasonCategory(),
                    transReason.getReasonCode(), transReason.getReason(), transReason.getTransQty1(),
                    transReason.getTransQty2(), transReason.getResponsibility(), transReason.getAccountCode()};

            jdbcTemplate.update(sql, args);
        }

        @Override
        public void deleteMultipleHold(long instanceRrn, long sequence) {
            String sql = "DELETE FROM " + DataBaseNames.MULTIPLE_HOLD + " WHERE INSTANCE_RRN = ? AND SEQUENCE_NUMBER = ?";

            jdbcTemplate.update(sql, instanceRrn, sequence);

        }

        @Override
        public List<Map> getHoldReasonCodes(String holdReasonGroupID, List<String> holdRoles, String referenceFileId) {
            StringBuffer sql = new StringBuffer(
                    "select rfd.key_1_value, rfd.data_1_value,rfd.data_2_value,rfd.data_3_value from " +
                            "reference_file_detail rfd, " + "named_object no " + "where rfd.reference_file_rrn=no" +
                            ".instance_rrn and no.instance_id=? and ( rfd" + ".data_3_value=? or rfd.data_3_value like " +
                            "'%ALL%' )" + " and rfd.data_5_value = '1' ");

            List args = new ArrayList();
            args.add(referenceFileId);
            args.add(holdReasonGroupID);
            //        if (CollectionUtils.isNotEmpty(holdRoles)) {
            //            sql.append(" AND (");
            //            for (Iterator iterator = holdRoles.iterator(); iterator.hasNext(); ) {
            //                String holdRole = (String) iterator.next();
            //                sql.append(" rfd.data_2_value LIKE ? ");
            //                args.add("%" + holdRole + "%");
            //                if (iterator.hasNext()) {
            //                    sql.append(" OR ");
            //                }
            //            }
            //            sql.append(" )");
            //        }
            sql.append(" order by rfd.data_1_value ");
            List<Map> holdReasons = jdbcTemplate.query(sql.toString(), args.toArray(), new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map holdReason = null;
                    if (isMatchDept(holdRoles, rs.getString("data_2_value"))) {
                        holdReason = new HashMap();
                        holdReason.put("holdCode", rs.getString("key_1_value"));
                        holdReason.put("holdDesc", rs.getString("data_1_value"));
                        holdReason.put("holdUsersGroup", rs.getString("data_3_value"));
                    }
                    return holdReason;
                }
            });
            return holdReasons;
        }

        @Override
        public List<Map> getReleaseGroup(String holdCode, String classTableValue) {
            String sql = "select rfd.data_4_value from reference_file_detail rfd, named_object no " + "where rfd" +
                    ".reference_file_rrn=no.instance_rrn and no.instance_id=? and rfd" + ".key_1_value=?";
            List args = new ArrayList();
            args.add(classTableValue);
            args.add(holdCode);
            List<Map> releaseGroup = jdbcTemplate.query(sql.toString(), args.toArray(), new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map map = new HashMap();
                    map.put("releaseGroup", rs.getString("data_4_value"));
                    return map;
                }
            });
            return releaseGroup;
        }

        @Override
        public List<Map> getReleaseReasonCodes(String releaseReasonGroupID, List releaseRoles, String referenceFileId) {

            StringBuffer sql = new StringBuffer(
                    "select rfd.key_1_value, rfd.data_1_value,rfd.data_2_value,rfd.data_3_value from " +
                            "reference_file_detail rfd, " + "named_object no " + "where rfd.reference_file_rrn=no" +
                            ".instance_rrn and no.instance_id=? and ( rfd" + ".data_2_value=? or rfd.data_2_value like " +
                            "'%ALL%' )" + " and rfd.data_5_value='1' ");

            List args = new ArrayList();
            args.add(referenceFileId);
            args.add(releaseReasonGroupID);

            //        if (CollectionUtils.isNotEmpty(releaseRoles)) {
            //            sql.append(" AND (");
            //            for (Iterator iterator = releaseRoles.iterator(); iterator.hasNext(); ) {
            //                String holdRole = (String) iterator.next();
            //                sql.append(" rfd.data_3_value LIKE ? ");
            //                args.add("%" + holdRole + "%");
            //                if (iterator.hasNext()) {
            //                    sql.append(" OR ");
            //                }
            //            }
            //            sql.append(" )");
            //        }

            sql.append(" order by rfd.data_2_value ");
            List<Map> releaseReasons = jdbcTemplate.query(sql.toString(), args.toArray(), new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map releaseReason = null;
                    if (isMatchDept(releaseRoles, rs.getString("data_3_value"))) {
                        releaseReason = new HashMap();
                        releaseReason.put("releaseCode", rs.getString("key_1_value"));
                        releaseReason.put("releaseDesc", rs.getString("data_1_value"));
                    }
                    return releaseReason;
                }
            });
            return releaseReasons;
        }

        @Override
        public List<Map<String, Object>> getAllPcdDefaultCleanCycle() {
            String sql = "SELECT PD.CATEGORY, PD.TYPE, PD.DEFAULT_CLEAN_CYCLE, PD.OPERATION_DATE, PD.OPERATION_USER " +
                    "FROM  PCD_DEFALULT_CLEAN_CYCLE PD ";

            List<Map<String, Object>> list = jdbcTemplate.query(sql.toString(), new RowMapper<Map<String, Object>>() {
                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap<String, Object>();
                    map.put("category", rs.getString("CATEGORY"));
                    map.put("type", rs.getString("TYPE"));
                    map.put("defaultCleanCycle", rs.getDouble("DEFAULT_CLEAN_CYCLE"));
                    map.put("operationDate", rs.getTimestamp("OPERATION_DATE"));
                    map.put("operationUser", rs.getString("OPERATION_USER"));
                    return map;
                }
            });
            return list;
        }

        @Override
        public void batchInsertOrUpdatePcdDefaultCleanCycle(List<Map<String, Object>> defaultCleanCycleList) {
            String sql = "MERGE INTO PCD_DEFALULT_CLEAN_CYCLE PD_1 " + "USING (SELECT ? AS CATEGORY, ? AS TYPE, ? AS " +
                    "DEFAULT_CLEAN_CYCLE, " + "? AS OPERATION_USER, SYSDATE AS OPERATION_DATE FROM DUAL) PD_2 " + "ON " +
                    "(PD_1.CATEGORY = PD_2.CATEGORY AND PD_1.TYPE = PD_2.TYPE) " + "WHEN MATCHED THEN " + "UPDATE SET " +
                    "PD_1.DEFAULT_CLEAN_CYCLE = PD_2.DEFAULT_CLEAN_CYCLE " + "WHEN NOT MATCHED THEN " + "INSERT " +
                    "(CATEGORY, TYPE, DEFAULT_CLEAN_CYCLE, OPERATION_USER, OPERATION_DATE) " + "VALUES " + "(PD_2" +
                    ".CATEGORY, PD_2.TYPE, PD_2.DEFAULT_CLEAN_CYCLE, PD_2.OPERATION_USER, PD_2" + ".OPERATION_DATE)";

            List<Object[]> batchArgs = new ArrayList<Object[]>();
            for (int i = 0; i < defaultCleanCycleList.size(); i++) {
                Map<String, Object> map = defaultCleanCycleList.get(i);
                batchArgs.add(new Object[]{MapUtils.getString(map, "categoryCode"), MapUtils.getString(map,
                                                                                                       "typeCode"),
                        MapUtils.getDoubleValue(
                        map, "defaultCleanCycle"), MapUtils.getString(map, "operationUser")});
            }
            jdbcTemplate.batchUpdate(sql, batchArgs);

        }

        @Override
        public int addPcdDefaultCleanCycleHistory(long transRrn, String targetCategoryDesc, String targetTypeDesc,
                                                  double targetDefaultCycleD, String user) {
            StringBuffer sql = new StringBuffer("INSERT INTO ");
            sql.append("PCD_DEFALULT_CLEAN_CYCLE_H (TRANS_RRN, CATEGORY, TYPE, DEFAULT_CLEAN_CYCLE, " + "OPERATION_USER, " +
                               "OPERATION_DATE) ");
            sql.append("VALUES (?, ?, ?, ?, ?, sysdate) ");

            int result = jdbcTemplate.update(sql.toString(),
                                             new Object[]{transRrn, targetCategoryDesc, targetTypeDesc,
                                                     targetDefaultCycleD, user});
            return result;
        }

        @Override
        public List<PcdClean> getPcdInfoByCleanEqpt(String eqptId, String entityNamedSpace) {
            String sql = "select pc.instance_rrn, getinstanceid(pc.instance_rrn) as pcd_id, pc.in_clean_date, pc" +
                    ".clean_over_date, " + "pc.clean_cycle, pc.pcd_type, pc.out_clean_date, pc.clean_count, pc.eqpt_id " +
                    "from pcd_clean pc, named_object n " +
                    "where pc.instance_rrn = n.instance_rrn and n.named_space = ? and pc.eqpt_id = ?";
            return jdbcTemplate.query(sql, new Object[]{entityNamedSpace, eqptId}, new RowMapper<PcdClean>() {

                @Override
                public PcdClean mapRow(ResultSet rs, int rowNum) throws SQLException {
                    PcdClean pc = new PcdClean();
                    pc.setInstanceRrn(rs.getLong("instance_rrn"));
                    pc.setInstanceId(rs.getString("pcd_id"));
                    pc.setInCleanDate(rs.getTimestamp("in_clean_date"));
                    pc.setCleanOverDate(rs.getTimestamp("clean_over_date"));
                    pc.setCleanCycle(rs.getDouble("clean_cycle"));
                    pc.setPcdType(rs.getString("pcd_type"));
                    pc.setOutCleanDate(rs.getTimestamp("out_clean_date"));
                    pc.setCleanCount(rs.getInt("clean_count"));
                    pc.setCleanEqptId(rs.getString("eqpt_id"));
                    if (pc.getInCleanDate() != null) {
                        long now = DateUtils.parse(DateUtils.getNowTime()).getTime();
                        long inClean = pc.getInCleanDate().getTime();
                        pc.setCleaningMinutes((now - inClean) / 1000 / 60);
                    }
                    return pc;
                }
            });
        }

        @Override
        public List<PcdClean> qryPCDCleanInfo(String targetStatus) {
            String sql = "select DISTINCT pc.instance_rrn, pc.in_clean_date,pc.clean_over_date,pc.clean_cycle,pc" +
                    ".pcd_type,pc.out_clean_date," + " pc.clean_count,m.event_rrn,n.instance_id event_id from pcd_clean " +
                    "pc,entity e, " + "event_model m,relation r, entity_status s,named_object n," + " NAMED_OBJECT N2, " +
                    "REFERENCE_FILE_DETAIL RE" + " where pc.instance_rrn = e.entity_rrn and pc.instance_rrn = s" +
                    ".entity_rrn and e" + ".allowable_events_rrn = r.from_rrn" + " and r.link_type = " +
                    "'ALLOWABLEEVENT_TO_EVENT' and r.to_rrn = m.event_rrn and n" + ".instance_rrn = m.event_rrn" + " AND " +
                    "n.instance_id=RE.DATA_1_VALUE and (m.valid_status=s.current_status or " + " m.valid_status is null) " +
                    "and m.target_status=?" + " AND N2.INSTANCE_RRN=RE.REFERENCE_FILE_RRN AND N2" +
                    ".INSTANCE_ID='$$AUTO_CHANGE_STATUS'" + " AND " + "N2.OBJECT='REFERENCEFILE' AND PC" +
                    ".CLEAN_OVER_DATE<=sysdate";

            List<PcdClean> pcdCleanInfo = jdbcTemplate.query(sql, new Object[]{targetStatus}, new RowMapper<PcdClean>() {
                @Override
                public PcdClean mapRow(ResultSet rs, int rowNum) throws SQLException {
                    PcdClean pcd = new PcdClean();

                    pcd.setInstanceRrn(rs.getLong("instance_rrn"));
                    pcd.setInCleanDate(rs.getTimestamp("in_clean_date"));
                    pcd.setCleanOverDate(rs.getTimestamp("clean_over_date"));
                    pcd.setCleanCycle(rs.getDouble("clean_cycle"));
                    pcd.setPcdType(rs.getString("pcd_type"));
                    pcd.setOutCleanDate(rs.getTimestamp("out_clean_date"));
                    pcd.setCleanCount(rs.getInt("clean_count"));
                    pcd.setEventRrn(rs.getLong("event_rrn"));
                    pcd.setEventId(rs.getString("event_id"));
                    if (pcd.getInCleanDate() != null) {
                        long now = DateUtils.parse(DateUtils.getNowTime()).getTime();
                        long inClean = pcd.getInCleanDate().getTime();
                        pcd.setCleaningMinutes((now - inClean) / 1000 / 60);
                    }

                    return pcd;
                }
            });

            return pcdCleanInfo;
        }

        private String getTransByName(String transBy) {
            String sql =
                    "select t.user_name from user_profile t,named_object o " + " where t.user_rrn=o.instance_rrn " + " " +
                            "and o.instance_id='" + transBy + "' and o.object='USER'";
            return jdbcTemplate.queryForObject(sql, String.class);
        }

        private boolean isMatchDept(List holdRoles, String data_2_value) {

            String[] datas = StringUtils.split(data_2_value, ",");
            for (int i = 0; i < datas.length; i++) {
                if (holdRoles.contains(datas[i]) || StringUtils.equals(datas[i], "ALL")) {
                    return true;
                }
            }
            return false;
        }

        @Override
        public void updateCleanOverTimeByRrn(Long podRrn, Timestamp timestamp) {
            String sql = "UPDATE PCD_CLEAN t " +
                    "SET t.CLEAN_OVER_DATE =? " +
                    "WHERE t.INSTANCE_RRN = ? ";
            jdbcTemplate.update(sql,new Object[]{timestamp,podRrn});
        }

    }