# PmsScheduleDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    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.math.NumberUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.pms.dao.PmsScheduleDao;
    import com.mycim.server.pms.dao.mapper.PmScheduleRowMapper;
    import com.mycim.server.pms.dao.mapper.PmsPmScheduleRowMapper;
    import com.mycim.valueobject.ObjectList;
    import com.mycim.valueobject.SystemConstant;
    import com.mycim.valueobject.bas.TransactionLog;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.LinkTypeList;
    import com.mycim.valueobject.consts.TransactionNames;
    import com.mycim.valueobject.ems.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

    import java.math.BigDecimal;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    import java.time.*;
    import java.time.format.DateTimeFormatter;
    import java.util.*;
    import java.util.stream.Collectors;

    @Repository
    public class PmsScheduleDaoImpl implements PmsScheduleDao {

        private static final String DATE_FORMAT = "YYYY/MM/DD HH24:MI:SS";

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public List<PmsSchedule> qryEqptPMInfo(PmsSchedule schedule) {
            StringBuilder sb = buildPMSqrySql(schedule);
            List<PmsSchedule> list = jdbcTemplate.query(sb.toString(), new PmsPmScheduleRowMapper());
            list = list.stream().peek(pms -> pms.setToleranceStatusBase(StringUtils.equalsIgnoreCase(PmControlTypeEnum.PM_COUNT.getControlType(), pms.getControlType())
                                                                                && StringUtils.equalsIgnoreCase(pms.getItemStatus(), SystemConstant.Str.ON)?
                                               this.getRunCountForPMCount(pms):0)).collect(Collectors.toList());
            return list;
        }

        @Override
        public PmsSchedule qryEqptPMInfo(long pmRrn) {
            PmsSchedule schedule = new PmsSchedule();
            schedule.setPmRrn(pmRrn);
            StringBuilder sb = buildPMSqrySql(schedule);
            schedule = jdbcTemplate.queryForObjectWithNull(sb.toString(), new PmsPmScheduleRowMapper());
            if (schedule == null) {
                return null;
            }
            schedule.setToleranceStatusBase(StringUtils.equalsIgnoreCase(PmControlTypeEnum.PM_COUNT.getControlType(), schedule.getControlType())
                                                    && StringUtils.equalsIgnoreCase(schedule.getItemStatus(), SystemConstant.Str.ON)?
                                               this.getRunCountForPMCount(schedule):0);
            return schedule;
        }

        @Override
        public String deletePmSchedules(long instanceRrn) {
            return "DELETE FROM " + DataBaseNames.PM_SCHEDULE + " WHERE ENTITY_RRN = " + instanceRrn;
        }

        @Override
        public List<PmSchedule> getPmSchedulesOfEntity(long entityRrn) {
            String sql = "SELECT SEQUENCE_NUMBER,SCHEDULE_ID,SCHEDULE_DESCRIPTION," + "CHECKLIST_RRN,FREQUENCE,UNIT," +
                    "NEXT_EVENT_TIME," + "ESTIMATED_DURATION,BASED_ON_PM_FLAG,ALARM_ID_WHEN_DUE," + "ALARM_ENABLE_FLAG," +
                    "REPEAT_IN_FREQUENCE,REPEAT_IN_UNIT," + "NOTIFY_BEFORE,NOTIFY_ALARM_ID,CANCEL_IF_SCHEDULE_ID," +
                    "CANCEL_WITHIN,OFF_SET,ALARM_REPEAT,ACTION_FLAG_1,ACTION_FLAG_2 FROM " + DataBaseNames.PM_SCHEDULE +
                    " WHERE ENTITY_RRN = ? ORDER BY SEQUENCE_NUMBER";
            return jdbcTemplate.query(sql, new Object[]{entityRrn}, (RowMapper<PmSchedule>) (rs, rowNum) -> {
                PmSchedule pmSchedule = new PmSchedule();
                pmSchedule.setSequenceNumber(rs.getLong("SEQUENCE_NUMBER"));
                pmSchedule.setScheduleId(rs.getString("SCHEDULE_ID"));
                String des = rs.getString("SCHEDULE_DESCRIPTION");
                pmSchedule.setScheduleDescription(StringUtils.isEmpty(des) ? "" : des);
                pmSchedule.setChecklistRrn(rs.getLong("CHECKLIST_RRN"));
                pmSchedule.setFrequence(rs.getString("FREQUENCE"));
                pmSchedule.setUnit(rs.getString("UNIT"));
                pmSchedule.setNextEventTime(DateUtils.formatDate(rs.getTimestamp("NEXT_EVENT_TIME")));
                pmSchedule.setEstimatedDuration(rs.getLong("ESTIMATED_DURATION"));
                pmSchedule.setBasedOnPmFlag(rs.getString("BASED_ON_PM_FLAG"));
                pmSchedule.setAlarmIdWhenDue(rs.getString("ALARM_ID_WHEN_DUE"));
                pmSchedule.setAlarmEnableFlag(rs.getString("ALARM_ENABLE_FLAG"));
                pmSchedule.setRepeatInFrequence(rs.getLong("REPEAT_IN_FREQUENCE"));
                pmSchedule.setRepeatInUnit(rs.getString("REPEAT_IN_UNIT"));
                pmSchedule.setNotifyBefore(rs.getLong("NOTIFY_BEFORE"));
                pmSchedule.setNotifyAlarmId(rs.getString("NOTIFY_ALARM_ID"));
                pmSchedule.setCancelIfScheduleId(rs.getString("CANCEL_IF_SCHEDULE_ID"));
                pmSchedule.setCancelWithin(rs.getLong("CANCEL_WITHIN"));
                pmSchedule.setOffset(rs.getLong("OFF_SET"));
                pmSchedule.setAlarmIdWhenRepeat(rs.getString("ALARM_REPEAT"));
                pmSchedule.setNextEventTime4Show(pmSchedule.getNextEventTime());

                /*
                 * if ((pmSchedule.getNextEventTime() != null) && (MiscUtils.parseSQL(pmSchedule
                 * .getNextEventTime()) >
                 * 0)) { pmSchedule.setNextEventTime4Show( MiscUtils.timestampToString( new
                 * Timestamp(MiscUtils.parseSQL( pmSchedule.getNextEventTime())))); }
                 */

                if ((pmSchedule.getEstimatedDuration() != null) &&
                        (MiscUtils.parseSQL(pmSchedule.getEstimatedDuration()) > 0)) {
                    pmSchedule.setEstimatedDuration4Show(
                            DateUtils.formatDateForSeconds(MiscUtils.parseSQL(pmSchedule.getEstimatedDuration())));
                }

                pmSchedule.setActionFlag1(rs.getString("ACTION_FLAG_1"));
                pmSchedule.setActionFlag2(rs.getString("ACTION_FLAG_2"));
                return pmSchedule;
            });

        }

        @Override
        public List<Map> getPmListByCheckList(long checklistRrn) {
            String sql = "SELECT DISTINCT N.INSTANCE_RRN,N.INSTANCE_ID,N.INSTANCE_DESC,PC.SEQUENCE_NUMBER, PC" +
                    ".SCHEDULE_ID, PC.SCHEDULE_DESCRIPTION " + " FROM PM_SCHEDULE PC,NAMED_OBJECT N " + " WHERE PC" +
                    ".CHECKLIST_RRN=? " + " AND PC.ENTITY_RRN=N.INSTANCE_RRN" + " AND N.OBJECT='" + DataBaseNames.ENTITY +
                    "' ORDER BY N.INSTANCE_ID, PC.SEQUENCE_NUMBER";

            return jdbcTemplate.query(sql, (RowMapper<Map>) (rs, rowNum) -> {
                Map m = new HashMap();
                m.put("equipmentId", rs.getString("INSTANCE_ID"));
                m.put("equipmentDesc", rs.getString("INSTANCE_DESC"));
                m.put("equipmentRrn", rs.getLong("INSTANCE_RRN"));
                m.put("sequenceNumber", rs.getLong("SEQUENCE_NUMBER"));
                m.put("scheduleId", rs.getString("SCHEDULE_ID"));
                m.put("scheduleDesc", rs.getString("SCHEDULE_DESCRIPTION"));
                return m;
            }, checklistRrn);

        }

        @Override
        public void insertPmSchedule(PmSchedule pmSchedule) {
            Object[] obj = {pmSchedule.getScheduleId(), pmSchedule.getScheduleDescription(), pmSchedule.getChecklistRrn()
                    , pmSchedule.getFrequence(), pmSchedule.getUnit(), pmSchedule.getEstimatedDuration(),
                    pmSchedule.getBasedOnPmFlag(), pmSchedule.getAlarmIdWhenDue(), pmSchedule.getAlarmEnableFlag(),
                    pmSchedule.getRepeatInFrequence(), pmSchedule.getRepeatInUnit(), pmSchedule.getNotifyBefore(),
                    pmSchedule.getNotifyAlarmId(), pmSchedule.getCancelIfScheduleId(), pmSchedule.getCancelWithin()};
            long instanceRrn = pmSchedule.getEntityRrn();
            long seqNo = getPmScheduleSeqNo(pmSchedule);
            String sql = "INSERT INTO " + DataBaseNames.PM_SCHEDULE + " (ENTITY_RRN,SEQUENCE_NUMBER,SCHEDULE_ID," +
                    "SCHEDULE_DESCRIPTION," + "CHECKLIST_RRN,FREQUENCE,UNIT," + "ESTIMATED_DURATION,BASED_ON_PM_FLAG," +
                    "ALARM_ID_WHEN_DUE," + "ALARM_ENABLE_FLAG,REPEAT_IN_FREQUENCE,REPEAT_IN_UNIT," + "NOTIFY_BEFORE," +
                    "NOTIFY_ALARM_ID,CANCEL_IF_SCHEDULE_ID," + "CANCEL_WITHIN,NEXT_EVENT_TIME,OFF_SET,ALARM_REPEAT," +
                    "ACTION_FLAG_1,ACTION_FLAG_2)" + " SELECT " + instanceRrn + "," + seqNo + "," +
                    MiscUtils.parseSQL(obj) + ",to_timestamp('" + pmSchedule.getNextEventTime() + "', '" + DATE_FORMAT +
                    "'), " + pmSchedule.getOffset() + ",'" + pmSchedule.getAlarmIdWhenRepeat() + "','" +
                    pmSchedule.getActionFlag1() + "','" + pmSchedule.getActionFlag2() + "' FROM " + DataBaseNames.ENTITY +
                    " where ENTITY_RRN = " + instanceRrn;
            jdbcTemplate.execute(sql);
            pmSchedule.setSequenceNumber(seqNo);
        }

        @Override
        public void insertPmScheduleHistory(PmSchedule pmSchedule, long transRrn) {
            String sql_h = "INSERT INTO " + DataBaseNames.PM_SCHEDULE_HISTORY + " (ENTITY_RRN,SEQUENCE_NUMBER," +
                    "SCHEDULE_ID,SCHEDULE_DESCRIPTION," + "CHECKLIST_RRN,FREQUENCE,UNIT," + "ESTIMATED_DURATION," +
                    "BASED_ON_PM_FLAG,ALARM_ID_WHEN_DUE," + "ALARM_ENABLE_FLAG,REPEAT_IN_FREQUENCE,REPEAT_IN_UNIT," +
                    "NOTIFY_BEFORE,NOTIFY_ALARM_ID,CANCEL_IF_SCHEDULE_ID," + "CANCEL_WITHIN,NEXT_EVENT_TIME,OFF_SET," +
                    "ALARM_REPEAT,ACTION_FLAG_1,ACTION_FLAG_2," + "TRANS_RRN)" + " SELECT ENTITY_RRN,SEQUENCE_NUMBER," +
                    "SCHEDULE_ID,SCHEDULE_DESCRIPTION," + "CHECKLIST_RRN,FREQUENCE,UNIT," + "ESTIMATED_DURATION," +
                    "BASED_ON_PM_FLAG,ALARM_ID_WHEN_DUE," + "ALARM_ENABLE_FLAG,REPEAT_IN_FREQUENCE,REPEAT_IN_UNIT," +
                    "NOTIFY_BEFORE,NOTIFY_ALARM_ID,CANCEL_IF_SCHEDULE_ID," + "CANCEL_WITHIN,NEXT_EVENT_TIME,OFF_SET," +
                    "ALARM_REPEAT,ACTION_FLAG_1,ACTION_FLAG_2,? " + "FROM " + DataBaseNames.PM_SCHEDULE + " where " +
                    "ENTITY_RRN = ? and SEQUENCE_NUMBER = ? ";

            jdbcTemplate.update(sql_h, transRrn, pmSchedule.getEntityRrn(), pmSchedule.getSequenceNumber());
        }

        @Override
        public PmSchedule getPmSchedule(PmSchedule pmSchedule) {
            String sql = "SELECT SEQUENCE_NUMBER,SCHEDULE_ID,SCHEDULE_DESCRIPTION," + "CHECKLIST_RRN,FREQUENCE,UNIT," +
                    "NEXT_EVENT_TIME," + "ESTIMATED_DURATION,BASED_ON_PM_FLAG,ALARM_ID_WHEN_DUE," + "ALARM_ENABLE_FLAG," +
                    "REPEAT_IN_FREQUENCE,REPEAT_IN_UNIT," + "NOTIFY_BEFORE,NOTIFY_ALARM_ID,CANCEL_IF_SCHEDULE_ID," +
                    "CANCEL_WITHIN,OFF_SET,ALARM_REPEAT,ACTION_FLAG_1,ACTION_FLAG_2 " + " FROM " +
                    DataBaseNames.PM_SCHEDULE + " WHERE ENTITY_RRN = " + pmSchedule.getEntityRrn() +
                    " AND SEQUENCE_NUMBER = " + pmSchedule.getSequenceNumber();

            return jdbcTemplate.queryForObjectWithNull(sql, (RowMapper<PmSchedule>) (rs, rowNum) -> {
                pmSchedule.setSequenceNumber(rs.getLong("SEQUENCE_NUMBER"));
                pmSchedule.setScheduleId(rs.getString("SCHEDULE_ID"));
                pmSchedule.setScheduleDescription(rs.getString("SCHEDULE_DESCRIPTION"));
                pmSchedule.setChecklistRrn(rs.getLong("CHECKLIST_RRN"));
                pmSchedule.setFrequence(rs.getString("FREQUENCE"));
                pmSchedule.setUnit(rs.getString("UNIT"));
                pmSchedule.setNextEventTime(DateUtils.formatDate(rs.getTimestamp("NEXT_EVENT_TIME")));
                pmSchedule.setEstimatedDuration(rs.getLong("ESTIMATED_DURATION"));
                pmSchedule.setBasedOnPmFlag(rs.getString("BASED_ON_PM_FLAG"));
                pmSchedule.setAlarmIdWhenDue(rs.getString("ALARM_ID_WHEN_DUE"));
                pmSchedule.setAlarmEnableFlag(rs.getString("ALARM_ENABLE_FLAG"));
                pmSchedule.setRepeatInFrequence(rs.getLong("REPEAT_IN_FREQUENCE"));
                pmSchedule.setRepeatInUnit(rs.getString("REPEAT_IN_UNIT"));
                pmSchedule.setNotifyBefore(rs.getLong("NOTIFY_BEFORE"));
                pmSchedule.setNotifyAlarmId(rs.getString("NOTIFY_ALARM_ID"));
                pmSchedule.setCancelIfScheduleId(rs.getString("CANCEL_IF_SCHEDULE_ID"));
                pmSchedule.setCancelWithin(rs.getLong("CANCEL_WITHIN"));
                pmSchedule.setOffset(rs.getLong("OFF_SET"));
                pmSchedule.setAlarmIdWhenRepeat(rs.getString("ALARM_REPEAT"));
                pmSchedule.setActionFlag1(rs.getString("ACTION_FLAG_1"));
                pmSchedule.setActionFlag2(rs.getString("ACTION_FLAG_2"));
                return pmSchedule;
            });
        }

        @Override
        public void updatePmSchedule(PmSchedule pmSchedule) {
            String sql = "UPDATE " + DataBaseNames.PM_SCHEDULE + " SET SCHEDULE_ID = " +
                    MiscUtils.parseSQL(pmSchedule.getScheduleId()) + "," + " SCHEDULE_DESCRIPTION = " +
                    MiscUtils.parseSQL(pmSchedule.getScheduleDescription()) + "," + " CHECKLIST_RRN = " +
                    MiscUtils.parseSQL(pmSchedule.getChecklistRrn()) + "," + " FREQUENCE = " +
                    MiscUtils.parseSQL(pmSchedule.getFrequence()) + "," + " UNIT = " +
                    MiscUtils.parseSQL(pmSchedule.getUnit()) + "," + " NEXT_EVENT_TIME = to_timestamp('" +
                    pmSchedule.getNextEventTime() + "', '" + DateUtils.DATE_FORMAT24 + "')" + "," +
                    " ESTIMATED_DURATION = " + MiscUtils.parseSQL(pmSchedule.getEstimatedDuration()) + "," +
                    " BASED_ON_PM_FLAG = " + MiscUtils.parseSQL(pmSchedule.getBasedOnPmFlag()) + "," +
                    " ALARM_ID_WHEN_DUE = " + MiscUtils.parseSQL(pmSchedule.getAlarmIdWhenDue()) + "," +
                    " ALARM_ENABLE_FLAG = " + MiscUtils.parseSQL(pmSchedule.getAlarmEnableFlag()) + "," +
                    " REPEAT_IN_FREQUENCE = " + MiscUtils.parseSQL(pmSchedule.getRepeatInFrequence()) + "," +
                    " REPEAT_IN_UNIT = " + MiscUtils.parseSQL(pmSchedule.getRepeatInUnit()) + "," + " NOTIFY_BEFORE = " +
                    MiscUtils.parseSQL(pmSchedule.getNotifyBefore()) + "," + " NOTIFY_ALARM_ID = " +
                    MiscUtils.parseSQL(pmSchedule.getNotifyAlarmId()) + "," + " CANCEL_IF_SCHEDULE_ID = " +
                    MiscUtils.parseSQL(pmSchedule.getCancelIfScheduleId()) + "," + " CANCEL_WITHIN = " +
                    MiscUtils.parseSQL(pmSchedule.getCancelWithin()) + "," + " OFF_SET = " +
                    MiscUtils.parseSQL(pmSchedule.getOffset()) + "," + " ALARM_REPEAT = " +
                    MiscUtils.parseSQL(pmSchedule.getAlarmIdWhenRepeat()) + ",ACTION_FLAG_1=" +
                    MiscUtils.parseSQL(pmSchedule.getActionFlag1()) + ",ACTION_FLAG_2=" +
                    MiscUtils.parseSQL(pmSchedule.getActionFlag2()) + " WHERE ENTITY_RRN = " + pmSchedule.getEntityRrn() +
                    " AND " + "SEQUENCE_NUMBER = " + MiscUtils.parseSQL(pmSchedule.getSequenceNumber());
            jdbcTemplate.update(sql);
        }

        @Override
        public void deletePmSchedule(PmSchedule pmSchedule) {
            String sql =
                    "DELETE FROM " + DataBaseNames.PM_SCHEDULE + " WHERE ENTITY_RRN = " + pmSchedule.getEntityRrn() + " " +
                            "AND SEQUENCE_NUMBER = " + pmSchedule.getSequenceNumber();
            jdbcTemplate.update(sql);
        }

        @Override
        public PmSchedule getActualEventTime(Entity entity, Long sequenceNumber) {
            String sql = "SELECT to_date('1970-01-01 08:00:00', '" + DateUtils.DATE_FORMAT24 + "')" + " + Q" +
                    ".next_fire_time / 1000) ACTUAL_EVENT_TIME " + " FROM PM_SCHEDULE P, NAMED_OBJECT N, QRTZ_TRIGGERS Q " +
                    " WHERE P.ENTITY_RRN = N.INSTANCE_RRN AND P.SEQUENCE_NUMBER=" + sequenceNumber + " AND P.ENTITY_RRN" +
                    "=" + entity.getInstanceRrn() + " AND Q.TRIGGER_NAME = P.ENTITY_RRN || N.INSTANCE_ID || P" +
                    ".SEQUENCE_NUMBER";

            return jdbcTemplate.queryForObjectWithNull(sql, (RowMapper<PmSchedule>) (rs, rowNum) -> {
                PmSchedule pmSchedule = new PmSchedule();
                pmSchedule.setActualEventTime(DateUtils.formatDate(rs.getTimestamp("ACTUAL_EVENT_TIME")));
                return pmSchedule;
            });
        }

        @Override
        public void delaySchedule(String jobName, String jobGroup, long time) {
            String sql = " UPDATE QRTZ_TRIGGERS SET NEXT_FIRE_TIME = ? WHERE JOB_NAME= ? AND JOB_GROUP =?";

            Object[] args = new Object[]{time, jobName, jobGroup};

            jdbcTemplate.update(sql, args);
        }

        @Override
        public Page qryPmsEqptHist(Map<String, Object> condition, Page page) {
            StringBuilder sql = new StringBuilder("SELECT * FROM (");
            sql.append("(SELECT DISTINCT L.TRANS_RRN,L.TRANS_ID,L.TRANS_START_TIMESTAMP,L.TRANS_END_TIMESTAMP,L" +
                               ".TRANS_PERFORMED_BY ||' '|| NO.INSTANCE_DESC TRANS_PERFORMED_BY,L.COMMENTS,H.TOTAL_COUNT,H.ALARM_COUNT,");
            sql.append(
                    "H.OBJECT_ID,H.CHAMBER_ID,H.PM_TYPE,H.PM_ITEM_DESC,H.CYCLE_TIME,H.DURATION_TIME,H" + ".NEXT_PM_TIME,H" +
                            ".TOLERANCE_TIME,H.ITEM_STATUS,H.OBJECT_TYPE,H.PM_ID," + " GETINSTANCEID(H.CHECKLIST_RRN) " +
                            "CHECKLIST_ID, 0 CHECKLIST_JOB_RRN ,H.TRIGGER_CODE,H.TRIGGER_CODE_SPEC,H.TRIGGER_READING,H.TOLERANCE_OTHER ");
            sql.append(" FROM PMS_PM_SCHEDULE_H H LEFT JOIN TRANSACTION_LOG L ");
            sql.append(" ON H.TRANS_RRN = L.TRANS_RRN LEFT JOIN NAMED_OBJECT NO ON NO.INSTANCE_ID = L.TRANS_PERFORMED_BY AND NO.OBJECT='USER')");
            sql.append(" UNION ");
            sql.append(" (SELECT L.TRANS_RRN,L.TRANS_ID,L.TRANS_START_TIMESTAMP,L.TRANS_END_TIMESTAMP,L" +
                               ".TRANS_PERFORMED_BY ||' '|| NO.INSTANCE_DESC TRANS_PERFORMED_BY,");
            sql.append(" L.COMMENTS,PPS.TOTAL_COUNT,PPS.ALARM_COUNT,PPS.OBJECT_ID,PPS.CHAMBER_ID,CLJH.PM_TYPE,");
            sql.append(" PPS.PM_ITEM_DESC,PPS.CYCLE_TIME,PPS.DURATION_TIME,PPS.NEXT_PM_TIME,PPS.TOLERANCE_TIME,PPS" +
                               ".ITEM_STATUS,");
            sql.append(" PPS.OBJECT_TYPE,PPS.PM_ID,GETINSTANCEID(cljh.CHECKLIST_RRN) CHECKLIST_ID, CLJH" +
                               ".CHECKLIST_JOB_RRN ,PPS.TRIGGER_CODE,PPS.TRIGGER_CODE_SPEC,PPS.TRIGGER_READING,PPS.TOLERANCE_OTHER ");
            sql.append(" FROM TRANSACTION_LOG L LEFT JOIN NAMED_OBJECT NO ON NO.INSTANCE_ID = L.TRANS_PERFORMED_BY AND NO.OBJECT='USER' RIGHT JOIN ( CHECKLIST_JOB_H CLJH LEFT JOIN PMS_PM_SCHEDULE PPS ");
            sql.append(" ON CLJH.ENTITY_RRN = PPS.OBJECT_RRN ");
            sql.append(" AND CLJH.CHECKLIST_RRN = PPS.CHECKLIST_RRN ");
            sql.append(" AND CLJH.PM_TYPE = PPS.PM_TYPE ");
            sql.append(" AND CLJH.PM_RRN = PPS.PM_RRN ) ");
            sql.append(" ON L.TRANS_RRN = CLJH.TRANS_RRN AND L.TRANS_ID IN ('" + TransactionNames.CHECKLIST_COMPLETED + "', '"+ TransactionNames.CHECKLIST_ERR_SAVE + "')) ");
            sql.append(") TR WHERE 1=1 ");
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "startDate"))) {
                sql.append(" AND TR.TRANS_START_TIMESTAMP >=to_timestamp('")
                   .append(MapUtils.getString(condition, "startDate")).append(" 00:00:00','")
                   .append(DateUtils.DATE_FORMAT24).append("') ");
            }

            if (StringUtils.isNotBlank(MapUtils.getString(condition, "endDate"))) {
                sql.append(" AND TR.TRANS_START_TIMESTAMP <=to_timestamp('")
                   .append(MapUtils.getString(condition, "endDate")).append(" 23:59:59','").append(DateUtils.DATE_FORMAT24)
                   .append("') ");
            }

            if (StringUtils.isNotBlank(MapUtils.getString(condition, "eqptId"))) {
                sql.append(" AND TR.OBJECT_ID LIKE '").append(MapUtils.getString(condition, "eqptId").replace("*", "%"))
                   .append("' ");
            }

            if (StringUtils.isNotBlank(MapUtils.getString(condition, "chamberId"))) {
                sql.append(" AND TR.CHAMBER_ID ='").append(MapUtils.getString(condition, "chamberId")).append("' ");
            }

            if (StringUtils.isNotBlank(MapUtils.getString(condition, "pmType"))) {
                sql.append(" AND TR.PM_TYPE ='").append(MapUtils.getString(condition, "pmType")).append("' ");
            }

            sql.append("ORDER BY TR.TRANS_START_TIMESTAMP DESC ");

            return jdbcTemplate.queryForPage(page, sql.toString(), null, (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                Map<String, Object> map = new HashMap<>();
                String transId = rs.getString("TRANS_ID");
                boolean isCheckListFlag = transId.contains("CHECKLIST");
                map.put("transRrn", rs.getLong("TRANS_RRN"));
                map.put("transId", transId);
                map.put("transStartTimestamp", DateUtils.formatDate(rs.getTimestamp("TRANS_START_TIMESTAMP")));
                map.put("transPerformedBy", rs.getString("TRANS_PERFORMED_BY"));
                map.put("transEndTimstamp", DateUtils.formatDate(rs.getTimestamp("TRANS_END_TIMESTAMP")));
                map.put("comments", rs.getString("COMMENTS"));
                map.put("pmId", rs.getString("PM_ID"));
                map.put("objectId", rs.getString("OBJECT_ID"));
                map.put("chamberId", rs.getString("CHAMBER_ID"));
                map.put("pmType", rs.getString("PM_TYPE"));
                map.put("totalCount", rs.getString("TOTAL_COUNT"));
                map.put("alarmCount", rs.getString("ALARM_COUNT"));
                map.put("pmItemDesc", isCheckListFlag ? "" : rs.getString("PM_ITEM_DESC"));
                map.put("cycleTime", isCheckListFlag ? "" : rs.getDouble("CYCLE_TIME"));
                map.put("durationTime", isCheckListFlag ? "" : rs.getDouble("DURATION_TIME"));
                map.put("checklistJobId", rs.getString("CHECKLIST_ID"));
                map.put("checklistJobRrn", rs.getLong("CHECKLIST_JOB_RRN"));
                if (TransactionNames.PMS_START.equalsIgnoreCase(transId) ||
                        TransactionNames.PMS_END.equalsIgnoreCase(transId)) {
                    double cycleTime = rs.getDouble("CYCLE_TIME");
                    Timestamp startTime = rs.getTimestamp("TRANS_START_TIMESTAMP");
                    long times = startTime.getTime() + (int) cycleTime * 24 * 60 * 60 * 1000;
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat(DateUtils.DATE_FORMAT);
                    Date date = new Date(times);
                    map.put("nextPmTime", simpleDateFormat.format(date));
                } else {
                    map.put("nextPmTime", isCheckListFlag ? "" : DateUtils.formatDate(rs.getTimestamp("NEXT_PM_TIME")));
                }
                map.put("toleranceTime", isCheckListFlag ? "" : rs.getDouble("TOLERANCE_TIME"));
                map.put("itemStatus", isCheckListFlag ? "" : rs.getString("ITEM_STATUS"));
                map.put("objectType", isCheckListFlag ? "" : rs.getString("OBJECT_TYPE"));
                String chamberId = (String) map.get("chamberId");
                if ("#".equals(chamberId)) {
                    map.put("chamberId", "");
                }

                map.put("triggerCode", rs.getString("TRIGGER_CODE"));
                map.put("triggerCodeSpec", rs.getString("TRIGGER_CODE_SPEC"));
                map.put("triggerReading", rs.getString("TRIGGER_READING"));
                map.put("toleranceTrigger", rs.getString("TOLERANCE_OTHER"));
                return map;
            });
        }

        @Override
        public Page qryEqptPMInfo(PmsSchedule schedule, Page page) {
            List args = new ArrayList();
            StringBuilder userLocation = new StringBuilder("#");

            List<Map<String, Object>> list = getWorkAreaByUser(schedule.getCreateUserRrn(), schedule.getNamedSpace());
            for (Map<String, Object> map : list) {
                userLocation.append(map.get("areaKey"));
                userLocation.append("#");
            }
            schedule.setUserLocation(userLocation.toString());
            StringBuilder sb = buildPMSqrySql(schedule);
            page = jdbcTemplate.queryForPage(page, sb.toString(), args.toArray(), new PmsPmScheduleRowMapper());
            List<PmsSchedule> resultList = (List<PmsSchedule>) page.getResults();
            resultList = resultList.stream().peek(pms -> pms.setToleranceStatusBase(
                    StringUtils.equalsIgnoreCase(PmControlTypeEnum.PM_COUNT.getControlType(), pms.getControlType())
                            && StringUtils.equalsIgnoreCase(pms.getItemStatus(), SystemConstant.Str.ON)?
                            this.getRunCountForPMCount(pms):0)).collect(Collectors.toList());
            page.setResults(resultList);
            return page;
        }

        @Override
        public void addPmSchedule(PmsSchedule schedule) {
            List<Object> args = new ArrayList<>();
            String sql = " INSERT INTO PMS_PM_SCHEDULE ( ";
            String sqlSetBodyK = "";
            String sqlSetBodyV = "";
            sqlSetBodyK += "PM_RRN,";
            sqlSetBodyV += "?,";
            args.add(schedule.getPmRrn());

            sqlSetBodyK += "OBJECT_RRN,";
            sqlSetBodyV += "?,";
            args.add(schedule.getObjectRrn());

            if (schedule.getPmId() != null) {
                sqlSetBodyK += "PM_ID,";
                sqlSetBodyV += "?,";
                args.add(schedule.getPmId());
            }

            if (schedule.getObjectType() != null) {
                sqlSetBodyK += "OBJECT_TYPE,";
                sqlSetBodyV += "?,";
                args.add(schedule.getObjectType());
            }

            if (schedule.getObjectId() != null) {
                sqlSetBodyK += "OBJECT_ID,";
                sqlSetBodyV += "?,";
                args.add(schedule.getObjectId());
            }

            if (schedule.getChamberId() != null) {
                sqlSetBodyK += "CHAMBER_ID,";
                sqlSetBodyV += "?,";
                args.add(schedule.getChamberId());
            }

            if (schedule.getPmType() != null) {
                sqlSetBodyK += "PM_TYPE,";
                sqlSetBodyV += "?,";
                args.add(schedule.getPmType());
            }

            if (schedule.getPmItemDesc() != null) {
                sqlSetBodyK += "PM_ITEM_DESC,";
                sqlSetBodyV += "?,";
                args.add(schedule.getPmItemDesc());
            }

            if (schedule.getCycleTime() != null) {
                sqlSetBodyK += "CYCLE_TIME,";
                sqlSetBodyV += "?,";
                args.add(schedule.getCycleTime());
            }

            if (schedule.getDurationTime() != null) {
                sqlSetBodyK += "DURATION_TIME,";
                sqlSetBodyV += "?,";
                args.add(schedule.getDurationTime());
            }

            if (schedule.getNextPmTime() != null) {
                sqlSetBodyK += "NEXT_PM_TIME,";
                sqlSetBodyV += "?,";
                args.add(schedule.getNextPmTime());
            }

            if (schedule.getToleranceTime() != null) {
                sqlSetBodyK += "TOLERANCE_TIME,";
                sqlSetBodyV += "?,";
                args.add(schedule.getToleranceTime());
            }

            if (schedule.getCycleCount() != null) {
                sqlSetBodyK += "CYCLE_COUNT,";
                sqlSetBodyV += "?,";
                args.add(schedule.getCycleCount());
            }

            if (schedule.getDurationCount() != null) {
                sqlSetBodyK += "DURATION_COUNT,";
                sqlSetBodyV += "?,";
                args.add(schedule.getDurationCount());
            }

            if (schedule.getCurrentPmCount() != null) {
                sqlSetBodyK += "CURRENT_PM_COUNT,";
                sqlSetBodyV += "?,";
                args.add(schedule.getCurrentPmCount());
            }

            if (schedule.getToleranceCount() != null) {
                sqlSetBodyK += "TOLERANCE_COUNT,";
                sqlSetBodyV += "?,";
                args.add(schedule.getToleranceCount());
            }

            if (schedule.getCycleOther() != null) {
                sqlSetBodyK += "CYCLE_OTHER,";
                sqlSetBodyV += "?,";
                args.add(schedule.getCycleOther());
            }

            if (schedule.getDurationOther() != null) {
                sqlSetBodyK += "DURATION_OTHER,";
                sqlSetBodyV += "?,";
                args.add(schedule.getDurationOther());
            }

            if (schedule.getCurrentPmOther() != null) {
                sqlSetBodyK += "CURRENT_PM_OTHER,";
                sqlSetBodyV += "?,";
                args.add(schedule.getCurrentPmOther());
            }

            if (schedule.getToleranceOther() != null) {
                sqlSetBodyK += "TOLERANCE_OTHER,";
                sqlSetBodyV += "?,";
                args.add(schedule.getToleranceOther());
            }

            if (schedule.getItemStatus() != null) {
                sqlSetBodyK += "ITEM_STATUS,";
                sqlSetBodyV += "?,";
                args.add(schedule.getItemStatus());
            }

            if (schedule.getAutoEqpStatus() != null) {
                sqlSetBodyK += "AUTO_EQP_STATUS,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAutoEqpStatus());
            }

            if (StringUtils.isNotBlank(schedule.getTriggerCode())){
                sqlSetBodyK += "TRIGGER_CODE,";
                sqlSetBodyV += "?,";
                args.add(schedule.getTriggerCode());
            }

            if (StringUtils.isNotBlank(schedule.getTriggerCodeSpec())){
                sqlSetBodyK += "TRIGGER_CODE_SPEC,";
                sqlSetBodyV += "?,";
                args.add(NumberUtils.toDouble(schedule.getTriggerCodeSpec()));
            }

            if (schedule.getCreateUserRrn() != null) {
                sqlSetBodyK += "CREATE_USER_RRN,";
                sqlSetBodyV += "?,";
                args.add(schedule.getCreateUserRrn());
            }

            //if (schedule.getCreateDate() != null) {
            sqlSetBodyK += "CREATE_DATE,";
            sqlSetBodyV += " sysdate,";
            //}

            if (schedule.getAttributeData1() != null) {
                sqlSetBodyK += "ATTRIBUTE_DATA_1,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAttributeData1());
            }

            if (schedule.getAttributeData2() != null) {
                sqlSetBodyK += "ATTRIBUTE_DATA_2,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAttributeData2());
            }

            if (schedule.getAttributeData3() != null) {
                sqlSetBodyK += "ATTRIBUTE_DATA_3,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAttributeData3());
            }

            if (schedule.getAttributeData4() != null) {
                sqlSetBodyK += "ATTRIBUTE_DATA_4,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAttributeData4());
            }

            if (schedule.getAttributeData5() != null) {
                sqlSetBodyK += "ATTRIBUTE_DATA_5,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAttributeData5());
            }
            if (schedule.getChamberRrn() != null && schedule.getChamberRrn() > 0) {
                sqlSetBodyK += "CHAMBER_RRN,";
                sqlSetBodyV += "?,";
                args.add(schedule.getChamberRrn());
            }
            if (schedule.getControlType() != null) {
                sqlSetBodyK += "control_type,";
                sqlSetBodyV += "?,";
                args.add(schedule.getControlType());
            }
            if (schedule.getTotalCount() != null) {
                sqlSetBodyK += "total_Count,";
                sqlSetBodyV += "?,";
                args.add(schedule.getTotalCount());
            }
            if (schedule.getAlarmCount() != null) {
                sqlSetBodyK += "Alarm_Count,";
                sqlSetBodyV += "?,";
                args.add(schedule.getAlarmCount());
            }
            if (schedule.getChecklistRrn() != null && schedule.getChecklistRrn() > 0) {
                sqlSetBodyK += "CHECKLIST_RRN,";
                sqlSetBodyV += "?,";
                args.add(schedule.getChecklistRrn());
            }
            if (schedule.getPmTimeType() != null) {
                sqlSetBodyK += "PM_TIME_TYPE,";
                sqlSetBodyV += "?,";
                args.add(schedule.getPmTimeType());
            }
            sqlSetBodyK = sqlSetBodyK.substring(0, sqlSetBodyK.length() - 1);
            sqlSetBodyV = sqlSetBodyV.substring(0, sqlSetBodyV.length() - 1);
            sql += sqlSetBodyK;
            sql += ")VALUES(";
            sql += sqlSetBodyV;
            sql += ")";
            jdbcTemplate.update(sql, args.toArray());
        }

        @Override
        public void addPmScheduleH(PmsSchedule schedule) {
            String pmRrnStr = schedule.getPmRrnStr();
            String[] pmrrns;
            if (StringUtils.isBlank(pmRrnStr)) {
                pmrrns = new String[]{schedule.getPmRrn() + ""};
            } else {
                pmrrns = pmRrnStr.split(",");
            }
            StringBuilder sql = new StringBuilder(
                    "INSERT INTO PMS_PM_SCHEDULE_H SELECT " + schedule.getTransRrn() + ",L.* FROM PMS_PM_SCHEDULE L WHERE" +
                            " L.PM_RRN IN (");
            buildPmRrnsSql(pmrrns, sql);
            jdbcTemplate.update(sql.toString());
        }

        @Override
        public void modifyPmSchedule(PmsSchedule schedule) {
            SimpleDateFormat sdf = new SimpleDateFormat(DateUtils.DATE_FORMAT);
            List<Object> args = new ArrayList<>();
            String sql = " UPDATE PMS_PM_SCHEDULE SET ";
            String sqlSetBody = "";
            StringBuilder sqlPk = new StringBuilder(" WHERE pm_rrn = ? ");
            if (schedule.getObjectRrn() != null && schedule.getObjectRrn() > 0) {
                sqlSetBody += "OBJECT_RRN = ? ,";
                if (schedule.getObjectRrn() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getObjectRrn());
                }
            }

            if (schedule.getObjectId() != null) {
                sqlSetBody += "OBJECT_ID = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getObjectId())) {
                    args.add(null);
                } else {
                    args.add(schedule.getObjectId());
                }
            }

            if (schedule.getChamberId() != null && !StringUtils.isEmpty(schedule.getChamberId())) {
                sqlSetBody += "CHAMBER_ID = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getChamberId())) {
                    args.add(null);
                } else {
                    args.add(schedule.getChamberId());
                }
            }

            if (schedule.getPmType() != null) {
                sqlSetBody += "PM_TYPE = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getPmType())) {
                    args.add(null);
                } else {
                    args.add(schedule.getPmType());
                }
            }

            if (schedule.getPmItemDesc() != null) {
                sqlSetBody += "PM_ITEM_DESC = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getPmItemDesc())) {
                    args.add(null);
                } else {
                    args.add(schedule.getPmItemDesc());
                }
            }

            if (schedule.getCycleTime() != null) {
                sqlSetBody += "CYCLE_TIME = ? ,";
                if (schedule.getCycleTime() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getCycleTime());
                }
            }

            if (schedule.getDurationTime() != null) {
                sqlSetBody += "DURATION_TIME = ? ,";
                if (schedule.getDurationTime() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getDurationTime());
                }
            }

            if (schedule.getNextPmTime() != null) {
                sqlSetBody += "NEXT_PM_TIME = ? ,";
                args.add(schedule.getNextPmTime());
            }

            if (schedule.getToleranceTime() != null) {
                sqlSetBody += "TOLERANCE_TIME = ? ,";
                if (schedule.getToleranceTime() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getToleranceTime());
                }
            }

            if (schedule.getCycleCount() != null) {
                sqlSetBody += "CYCLE_COUNT = ? ,";
                if (schedule.getCycleCount().longValue() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getCycleCount());
                }
            }

            if (schedule.getDurationCount() != null) {
                sqlSetBody += "DURATION_COUNT = ? ,";
                if (schedule.getDurationCount().longValue() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getDurationCount());
                }
            }

            if (schedule.getCurrentPmCount() != null) {
                sqlSetBody += "CURRENT_PM_COUNT = ? ,";
                if (schedule.getCurrentPmCount().longValue() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getCurrentPmCount());
                }
            }

            if (schedule.getToleranceCount() != null) {
                sqlSetBody += "TOLERANCE_COUNT = ? ,";
                if (schedule.getToleranceCount().longValue() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getToleranceCount());
                }
            }

            if (schedule.getCycleOther() != null) {
                sqlSetBody += "CYCLE_OTHER = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getCycleOther())) {
                    args.add(null);
                } else {
                    args.add(schedule.getCycleOther());
                }
            }

            if (schedule.getDurationOther() != null) {
                sqlSetBody += "DURATION_OTHER = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getDurationOther())) {
                    args.add(null);
                } else {
                    args.add(schedule.getDurationOther());
                }
            }

            if (schedule.getCurrentPmOther() != null) {
                sqlSetBody += "CURRENT_PM_OTHER = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getCurrentPmOther())) {
                    args.add(null);
                } else {
                    args.add(schedule.getCurrentPmOther());
                }
            }

            if (schedule.getToleranceOther() != null) {
                sqlSetBody += "TOLERANCE_OTHER = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getToleranceOther())) {
                    args.add(null);
                } else {
                    args.add(schedule.getToleranceOther());
                }
            }

            if (schedule.getItemStatus() != null) {
                sqlSetBody += "ITEM_STATUS = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getItemStatus())) {
                    args.add(null);
                } else {
                    args.add(schedule.getItemStatus());
                }
            }

            if (schedule.getAutoEqpStatus() != null) {
                sqlSetBody += "AUTO_EQP_STATUS= ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getAutoEqpStatus())) {
                    args.add(null);
                } else {
                    args.add(schedule.getAutoEqpStatus());
                }
            }
            if (schedule.getTriggerCode() != null){
                sqlSetBody += "TRIGGER_CODE = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getTriggerCode())) {
                    args.add(null);
                } else {
                    args.add(schedule.getTriggerCode());
                }
            }

            if (schedule.getTriggerCodeSpec() != null){
                sqlSetBody += "TRIGGER_CODE_SPEC = ?,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getTriggerCodeSpec())) {
                    args.add(null);
                } else {
                    args.add(schedule.getTriggerCodeSpec());
                }
            }

            if (schedule.getTriggerReading() != null){
                sqlSetBody += "TRIGGER_READING = ?,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getTriggerReading())) {
                    args.add(null);
                } else {
                    args.add(schedule.getTriggerReading());
                }
            }

            if (schedule.getChamberRrn() != null && schedule.getChamberRrn() > 0) {
                sqlSetBody += "CHAMBER_RRN = ? ,";
                if (schedule.getChamberRrn() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getChamberRrn());
                }
            }
            if (schedule.getControlType() != null) {
                sqlSetBody += "control_type = ?,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getControlType())) {
                    args.add(null);
                } else {
                    args.add(schedule.getControlType());
                }
            }
            if (schedule.getTotalCount() != null) {
                sqlSetBody += "total_Count = ?,";
                if (schedule.getTotalCount() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getTotalCount());
                }
            }
            if (schedule.getAlarmCount() != null) {
                sqlSetBody += "Alarm_Count = ?,";
                if (schedule.getAlarmCount() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getAlarmCount());
                }
            }

            if (schedule.getChecklistRrn() != null) {
                sqlSetBody += "CHECKLIST_RRN = ?,";
                if (schedule.getChecklistRrn() <= 0) {
                    args.add(null);
                } else {
                    args.add(schedule.getChecklistRrn());
                }
            }

            if (schedule.getPmTimeType() != null) {
                sqlSetBody += "PM_TIME_TYPE = ?,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getPmTimeType())) {
                    args.add(null);
                } else {
                    args.add(schedule.getPmTimeType());
                }
            }

            if (schedule.getPmLinks() != null) {
                sqlSetBody += "PM_LINKS = ?,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getPmLinks())) {
                    args.add(null);
                } else {
                    args.add(schedule.getPmLinks());
                }
            }

            if (schedule.getCreateDate() != null) {
                sqlSetBody += "CREATE_DATE = to_timestamp(? , '" + DATE_FORMAT + "'),";
                args.add(sdf.format(schedule.getCreateDate()));
            }

            if (schedule.getObjectId() != null) {
                sqlSetBody += "PM_ID = ? ,";
                if (StringUtils.UPDATE_COLUMN_NULL_FLAG.equals(schedule.getPmId())) {
                    args.add(null);
                } else {
                    args.add(schedule.getPmId());
                }
            }

            String pmRrnStr = schedule.getPmRrnStr();
            String[] pmrrns = null;
            if (StringUtils.isBlank(pmRrnStr)) {
                //pmrrns = new String[]{schedule.getPmRrn()+""};
                args.add(schedule.getPmRrn());
            } else {
                pmrrns = pmRrnStr.split(",");
                sqlPk = new StringBuilder(" WHERE PM_RRN IN (");
                buildPmRrnsSql(pmrrns, sqlPk);
            }
            sqlSetBody = sqlSetBody.substring(0, sqlSetBody.length() - 1);
            sql += sqlSetBody;
            sql += sqlPk;
            jdbcTemplate.update(sql, args.toArray());
        }

        @Override
        public boolean checkIsParentEqpt(long entityRrn) {
            long count;
            boolean check = false;
            String sql = "select count(*) from entity t where t.parent_entity_rrn=" + entityRrn;
            count = jdbcTemplate.queryForObjectWithNull(sql, long.class);
            if (count > 0) {
                check = true;
            }
            return check;
        }

        @Override
        public void deletePmSchedule(PmsSchedule schedule) {
            String pmRrnStr = schedule.getPmRrnStr();
            String[] pmrrns;
            if (StringUtils.isBlank(pmRrnStr)) {
                pmrrns = new String[]{schedule.getPmRrn() + ""};
            } else {
                pmrrns = pmRrnStr.split(",");
            }
            StringBuilder sql = new StringBuilder("DELETE FROM PMS_PM_SCHEDULE WHERE PM_RRN IN (");
            buildPmRrnsSql(pmrrns, sql);
            jdbcTemplate.update(sql.toString());
        }

        @Override
        public PmSchedule updateNextPMTime(PmSchedule pmSchedule) {
            String sql = "UPDATE " + DataBaseNames.PM_SCHEDULE + " SET NEXT_EVENT_TIME=?" + " WHERE ENTITY_RRN =? " + " " +
                    "AND SEQUENCE_NUMBER=? ";

            String unit = pmSchedule.getUnit();
            int frequence = Integer.parseInt(pmSchedule.getFrequence());
            Calendar calendar = Calendar.getInstance();
            if ("daily".equalsIgnoreCase(unit) || "day".equalsIgnoreCase(unit) || "D".equalsIgnoreCase(unit)) {
                calendar.add(Calendar.DATE, frequence);
            } else if ("monthly".equalsIgnoreCase(unit) || "month".equalsIgnoreCase(unit) || "M".equalsIgnoreCase(unit)) {
                calendar.add(Calendar.MONTH, frequence);
            } else if ("weekly".equalsIgnoreCase(unit) || "week".equalsIgnoreCase(unit) || "W".equalsIgnoreCase(unit)) {
                calendar.add(Calendar.WEEK_OF_YEAR, frequence);

            } else if ("hour".equalsIgnoreCase(unit) || "hourly".equalsIgnoreCase(unit) || "h".equalsIgnoreCase(unit)) {
                calendar.add(Calendar.HOUR, frequence);

            } else if ("year".equalsIgnoreCase(unit) || "yearly".equalsIgnoreCase(unit) || "y".equalsIgnoreCase(unit)) {
                calendar.add(Calendar.YEAR, frequence);
            } else if ("minute".equalsIgnoreCase(unit) || "minutely".equalsIgnoreCase(unit) || "I".equalsIgnoreCase(unit)) {
                calendar.add(Calendar.MINUTE, frequence);
            }

            String newDate = DateUtils.getNowTime();
            pmSchedule.setNextEventTime(newDate);

            jdbcTemplate.update(sql, new Timestamp(calendar.getTimeInMillis()), pmSchedule.getEntityRrn(),
                                pmSchedule.getSequenceNumber());
            return pmSchedule;
        }

        @Override
        public Page getPmSchedulesByPageQuery(Map condition, Page page) {
            String sql = "SELECT INSTANCE_DESC, ENTITY_RRN, SEQUENCE_NUMBER, SCHEDULE_ID, SCHEDULE_DESCRIPTION, " +
                    "CHECKLIST_RRN, FREQUENCE, UNIT, NEXT_EVENT_TIME, " + "ESTIMATED_DURATION, BASED_ON_PM_FLAG, " +
                    "ALARM_ID_WHEN_DUE, " + "ALARM_ENABLE_FLAG, REPEAT_IN_FREQUENCE, REPEAT_IN_UNIT, " + "NOTIFY_BEFORE, " +
                    "NOTIFY_ALARM_ID, CANCEL_IF_SCHEDULE_ID, " + "CANCEL_WITHIN, OFF_SET, ALARM_REPEAT, " + " to_date" +
                    "('1970-01-01 08:00:00', '" + DateUtils.DATE_FORMAT24 + "')" + " + Q.next_fire_time / 1000) " +
                    "ACTUAL_EVENT_TIME " + "FROM PM_SCHEDULE P, NAMED_OBJECT N, QRTZ_TRIGGERS Q " + "WHERE P.ENTITY_RRN =" +
                    " N.INSTANCE_RRN " + "AND Q.TRIGGER_NAME=P.ENTITY_RRN||N.INSTANCE_ID||P.SEQUENCE_NUMBER ";

            if (MapUtils.getLong(condition, "entityRrn") != null) {
                sql += " AND ENTITY_RRN = " + MapUtils.getLong(condition, "entityRrn");
            }

            if (StringUtils.isNotEmpty(MapUtils.getString(condition, "pmScheduleDateFrom"))) {
                sql += " AND to_char(NEXT_EVENT_TIME,'" + DateUtils.DATE_FORMAT4DAYD + "') >= to_char(to_timestamp('" +
                        MapUtils.getString(condition, "pmScheduleDateFrom") + "','" + DateUtils.DATE_FORMAT4D + "'),'" +
                        DateUtils.DATE_FORMAT4DAYD + "') ";
            }

            if (StringUtils.isNotEmpty(MapUtils.getString(condition, "pmScheduleDateTo"))) {
                sql += " AND to_char(NEXT_EVENT_TIME,'" + DateUtils.DATE_FORMAT4DAYD + "') <= to_char(to_timestamp('" +
                        MapUtils.getString(condition, "pmScheduleDateTo") + "','" + DateUtils.DATE_FORMAT4D + "'),'" +
                        DateUtils.DATE_FORMAT4DAYD + "') ";
            }

            if (StringUtils.isNotEmpty(MapUtils.getString(condition, "eventType"))) {
                String eventType = MapUtils.getString(condition, "eventType");
                sql += " AND P.CHECKLIST_RRN in (SELECT INSTANCE_RRN FROM NAMED_OBJECT " + "WHERE OBJECT='CHECKLIST' AND " +
                        "OBJECT_TYPE='EVENT_TYPE' AND OBJECT_SUBTYPE='" + eventType + "') ";
            }

            sql += " ORDER BY NEXT_EVENT_TIME ";

            page = jdbcTemplate.queryForPage(page, sql, null, new PmScheduleRowMapper());
            List<PmsSchedule> resultList = (List<PmsSchedule>) page.getResults();
            resultList = resultList.stream().peek(pms -> pms.setToleranceStatusBase(
                    StringUtils.equalsIgnoreCase(PmControlTypeEnum.PM_COUNT.getControlType(), pms.getControlType())?
                            this.getRunCountForPMCount(pms):0))
                                   .collect(Collectors.toList());
            page.setResults(resultList);
            return page;
        }

        @Override
        public Map getPMScheduleByMonth(String equipmentId, int year, int month, long ownerEntityRrn) {
            Map<String, Object> pmMap = new HashMap<>();
            // 取得月份的开始时间和结束时间
            GregorianCalendar startGc = new GregorianCalendar(year, month - 1, 1, 0, 0, 0);
            GregorianCalendar endGc = new GregorianCalendar(year, startGc.get(Calendar.MONTH) + 1, 1, 0, 0, 0);

            long startTime = startGc.getTime().getTime();
            long endTime = endGc.getTime().getTime();
            String sql = "";

            sql = "SELECT T.*, I.INSTANCE_ID CHECKLIST_ID, I.INSTANCE_DESC CHECKLIST_DESC " + " FROM " + " (SELECT O" +
                    ".INSTANCE_ID EQUIPMENT_ID, O.INSTANCE_DESC EQUIPMENT_DESC, O.NAMED_SPACE, " + " P.CHECKLIST_RRN, P" +
                    ".SCHEDULE_ID, P.SCHEDULE_DESCRIPTION, P.ENTITY_RRN, " + " P.FREQUENCE, P.UNIT, P.NEXT_EVENT_TIME,P" +
                    ".SEQUENCE_NUMBER " + " FROM " + " PM_SCHEDULE P, NAMED_OBJECT O " + " WHERE P.ENTITY_RRN = O" +
                    ".INSTANCE_RRN " + " AND O.INSTANCE_ID = ? AND P.NEXT_EVENT_TIME < ?) T, " + " NAMED_OBJECT I " + " " +
                    "WHERE T.CHECKLIST_RRN = I.INSTANCE_RRN";
            if (ownerEntityRrn != 0) {
                sql = " SELECT DISTINCT T.*, I.INSTANCE_ID CHECKLIST_ID, I.INSTANCE_DESC CHECKLIST_DESC " + " FROM " +
                        "(SELECT O.INSTANCE_ID EQUIPMENT_ID,O.INSTANCE_DESC EQUIPMENT_DESC, " + " O.NAMED_SPACE, " + " P" +
                        ".CHECKLIST_RRN, " + " P.SCHEDULE_ID, " + " P.SCHEDULE_DESCRIPTION, " + " P.ENTITY_RRN, " + " P" +
                        ".FREQUENCE, " + " P.UNIT, " + " P.NEXT_EVENT_TIME, " + " P.SEQUENCE_NUMBER " + " FROM " +
                        "PM_SCHEDULE P, NAMED_OBJECT O,ENTITY EN,RELATION R " + " WHERE P.ENTITY_RRN = O.INSTANCE_RRN " +
                        " AND EN.ENTITY_RRN=P.ENTITY_RRN " + " AND EN.MAINTENANCE_ENGINEER_RRN=R.TO_RRN " +
                        " AND O.INSTANCE_ID = ? " + " AND P.NEXT_EVENT_TIME < ? " + " AND R.FROM_RRN=?) T, " +
                        " NAMED_OBJECT I " + " WHERE T.CHECKLIST_RRN = I.INSTANCE_RRN ";

            }

            List<Object> args = new ArrayList<>();
            args.add(equipmentId);
            args.add(new Date(endGc.getTime().getTime()));
            if (ownerEntityRrn != 0) {
                args.add(ownerEntityRrn);
            }
            Map<String, Object> eqpDescMap = new HashMap<>();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, args.toArray());
            int i = 0;
            while (rs.next()) {
                Timestamp nextDate = rs.getTimestamp("NEXT_EVENT_TIME");
                long nextTime = nextDate.getTime();
                int freq = rs.getInt("FREQUENCE");
                String unit = rs.getString("UNIT");
                // 判断下一次开始时间是否大于结束时间，是则结束循环

                while (nextTime < endTime) {
                    // 如果大于开始时间，则记录此PM Schedule的信息
                    i++;
                    if (nextTime >= startTime) {

                        GregorianCalendar nextCalendar = new GregorianCalendar();
                        nextCalendar.setTime(new Date(nextTime));
                        // String nextDay = String.valueOf(nextCalendar.get(Calendar.DATE));
                        int nextDay = nextCalendar.get(Calendar.DATE);
                        PmSchedule pms = new PmSchedule();
                        pms.setEntityRrn(rs.getLong("ENTITY_RRN"));
                        pms.setChecklistRrn(rs.getLong("CHECKLIST_RRN"));
                        pms.setChecklistId(rs.getString("CHECKLIST_ID"));
                        pms.setChecklistDescription(rs.getString("CHECKLIST_DESC"));
                        pms.setSequenceNumber(rs.getLong("SEQUENCE_NUMBER"));
                        if (pmMap.get(String.valueOf(nextDay)) != null) {
                            List<PmSchedule> pmList = (List) pmMap.get(String.valueOf(nextDay));
                            pmList.add(pms);
                        } else {
                            List<PmSchedule> pmList = new ArrayList<>();
                            pmList.add(pms);
                            pmMap.put(String.valueOf(nextDay), pmList);
                        }
                    }
                    nextTime = addTimeByUnit(nextTime, unit, freq);
                    if (freq == 0) {
                        break;
                    }
                }
            }
            return pmMap;
        }

        @Override
        public PmSchedule selectPMScheduleDetail(long checklistRrn, long entityRrn, long sequenceNum) {
            String sql = "SELECT T.*, I.INSTANCE_ID CHECKLIST_ID, I.INSTANCE_DESC CHECKLIST_DESC " + " FROM " + " (SELECT" +
                    " O.INSTANCE_ID EQUIPMENT_ID, O.INSTANCE_DESC EQUIPMENT_DESC, O.NAMED_SPACE, " + " P.CHECKLIST_RRN, P" +
                    ".SCHEDULE_ID, P.SCHEDULE_DESCRIPTION, P.ENTITY_RRN, P" + ".notify_alarm_id," + " P.FREQUENCE, P" +
                    ".UNIT, P.NEXT_EVENT_TIME ,P.SEQUENCE_NUMBER,P.alarm_id_when_due, " + " P.repeat_in_frequence,P" +
                    ".based_on_pm_flag,P.repeat_in_unit,P.ALARM_REPEAT FROM " + " PM_SCHEDULE P, NAMED_OBJECT O " + " " +
                    "WHERE P.ENTITY_RRN = ? AND P.ENTITY_RRN = O.INSTANCE_RRN) T, " + " NAMED_OBJECT I " + " WHERE T" +
                    ".SEQUENCE_NUMBER = ? AND T.CHECKLIST_RRN = ? AND T.CHECKLIST_RRN = I" + ".INSTANCE_RRN";

            return jdbcTemplate.queryForObject(sql, (RowMapper<PmSchedule>) (rs, rowNum) -> {
                PmSchedule pms = new PmSchedule();
                pms.setEquipmentId(rs.getString("EQUIPMENT_ID"));
                pms.setEquipmentDesc(rs.getString("EQUIPMENT_DESC"));
                pms.setEquipmentNamedSpace(rs.getString("NAMED_SPACE"));
                pms.setScheduleId(rs.getString("SCHEDULE_ID"));
                pms.setScheduleDescription(rs.getString("SCHEDULE_DESCRIPTION"));
                pms.setChecklistId(rs.getString("CHECKLIST_ID"));
                pms.setChecklistDescription(rs.getString("CHECKLIST_DESC"));
                pms.setFrequence(rs.getString("FREQUENCE"));
                pms.setUnit(rs.getString("UNIT"));
                pms.setSequenceNumber(rs.getLong("SEQUENCE_NUMBER"));
                pms.setEntityRrn(rs.getLong("ENTITY_RRN"));
                pms.setNextEventTime(DateUtils.formatDate(rs.getTimestamp("NEXT_EVENT_TIME")));
                pms.setAlarmIdWhenDue(rs.getString("alarm_id_when_due"));
                pms.setNotifyAlarmId(rs.getString("notify_alarm_id"));
                pms.setRepeatInFrequence(rs.getLong("repeat_in_frequence"));
                pms.setBasedOnPmFlag(rs.getString("based_on_pm_flag"));
                pms.setChecklistRrn(checklistRrn);
                pms.setRepeatInUnit(rs.getString("repeat_in_unit"));
                pms.setAlarmIdWhenRepeat(rs.getString("ALARM_REPEAT"));
                return pms;
            }, entityRrn, sequenceNum, checklistRrn);

        }

        @Override
        public void updatePmScheduleNext(PmSchedule pms) {
            String unit = pms.getUnit();
            int freq = Integer.parseInt(pms.getFrequence());
            long newEventTime = 0;

            String nextEventStr = DateUtils
                    .formatDate(DateUtils.stringToTimestamp(pms.getNextEventTime()), DateUtils.DATE_FORMAT4DATE);
            Timestamp eventTime = Timestamp.valueOf(nextEventStr);

            newEventTime = addTimeByUnit(eventTime.getTime(), unit, freq);

            String nextEventTime = DateUtils.formatDate(new Date(newEventTime), DateUtils.DATE_FORMAT4DATE);

            String sql = "UPDATE PM_SCHEDULE SET NEXT_EVENT_TIME = to_timestamp(?, '" + DateUtils.DATE_FORMAT24 + "') " +
                    "WHERE ENTITY_RRN = ? AND SEQUENCE_NUMBER = ? ";

            jdbcTemplate.update(sql, nextEventTime, pms.getEntityRrn(), pms.getSequenceNumber());
        }

        @Override
        public void updatePmScheduleNextTime(Map jobData) {
            PmSchedule pmSchedule = (PmSchedule) jobData.get("pmSchedule");
            String entityRrn = pmSchedule.getEntityRrn() + "";
            String seq = String.valueOf(pmSchedule.getSequenceNumber());

            String sql = "select t.next_event_time,frequence,unit,based_on_pm_flag from pm_schedule " + "t where t" +
                    ".entity_rrn=? and t.sequence_number=? ";

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, entityRrn, seq);

            Timestamp time = null;
            int frequence;
            String unit;
            String baseFlag = "";
            if (rs.next()) {
                time = rs.getTimestamp("next_event_time");
                frequence = rs.getInt("frequence");
                unit = rs.getString("unit");
                baseFlag = rs.getString("based_on_pm_flag");
                long nextTime = this.getNextTime(time.getTime(), frequence, unit);
                time.setTime(nextTime);
            }

            if (time != null && !baseFlag.equalsIgnoreCase("1") && !baseFlag.equalsIgnoreCase("Y")) {

                String updateSql = "update  pm_schedule t set t.next_event_time=" + " to_timestamp('" + DateUtils.
                                                                                                                         formatDate(
                                                                                                                                 time) +
                        "','" + DateUtils.DATE_FORMAT24 + "')" + " where t.entity_rrn=" + entityRrn +
                        " and t.sequence_number=" + seq;
                jdbcTemplate.update(updateSql);
            }
        }

        @Override
        public Map<String, Object> getTrigger(String jobName, String jobGroup) {
            HashMap<String, Object> map = new HashMap<>();
            String sql = "SELECT T.* FROM QRTZ_TRIGGERS T WHERE T.JOB_NAME=? AND T.JOB_GROUP=? ";

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, jobName, jobGroup);

            if (rs.next()) {
                // map.put("startTime",new Long(rs.getLong("START_TIME")));
                map.put("firstFireTime", rs.getLong("start_time"));
                map.put("startTime", rs.getLong("PREV_FIRE_TIME"));
                map.put("nextTime", rs.getLong("NEXT_FIRE_TIME"));
            }
            return map;
        }

        @Override
        public Integer getRunCountForPMCount(PmsSchedule pms) {
            if (StringUtils.equalsIgnoreCase(PmControlTypeEnum.PM_COUNT.getControlType(), pms.getControlType())) {
                List<Object> args = new ArrayList<>();
                 String parentEntityRrnSql = "SELECT NO.INSTANCE_ID PARENT_ENTITY_ID FROM ENTITY E,NAMED_OBJECT NO WHERE E.ENTITY_RRN = ? AND E.PARENT_ENTITY_RRN=NO.INSTANCE_RRN";
                 String parentEntityId = jdbcTemplate
                         .queryForObjectWithNull(parentEntityRrnSql, new Object[]{pms.getObjectRrn()}, String.class);

                SimpleDateFormat sdf = new SimpleDateFormat(DateUtils.DATE_FORMAT);
                String sql;
                String fromSql = " FROM EQUIPMENT_RUN_HISTORY WHERE RUN_TYPE = ? AND EQUIPMENT_RUN_TIME BETWEEN "
                        + "TO_TIMESTAMP (?, 'YYYY/MM/DD HH24:MI:SS')  AND TO_TIMESTAMP (?, 'YYYY/MM/DD HH24:MI:SS')  AND EQUIPMENT_ID = ? ";
                args.add(TransactionNames.MOVEOUT_KEY);
                args.add(sdf.format(pms.getCreateDate()));
                args.add(sdf.format(new Date()));
                if (StringUtils.isNotBlank(parentEntityId)) {//对于腔体设备，EQUIPMENT_ID 放的是主设备的ID
                    fromSql += " AND PHYSICAL_RECIPE_ID LIKE ? ";
                    args.add(parentEntityId);
                     //获取腔体后缀
                     String eqptId = pms.getObjectId();
                     String eqptSufix = eqptId.substring(eqptId.lastIndexOf("_") + 1);
                    args.add("%-%" + eqptSufix + "%");
                } else {
                    args.add(pms.getObjectId());
                }
                if (StringUtils.equalsIgnoreCase(pms.getPmType(), PmControlTypeEnum.BY_WAFER.getControlType())) {
                    sql = "SELECT SUM(QTY1) " + fromSql;
                } else if (StringUtils.equalsIgnoreCase(pms.getPmType(), PmControlTypeEnum.BY_LOT.getControlType())) {
                    sql = "SELECT COUNT(LOT_ID) " + fromSql;
                } else {
                    return 0;
                }
                return jdbcTemplate.queryForObject(sql, args.toArray(), Integer.class);
            }
            return 0;
        }

        @Override
        public List qryChecklistJobDataHist(Long transRrn) {
            String sql = "SELECT DISTINCT L.TRANS_RRN,L.TRANS_ID,L.TRANS_START_TIMESTAMP," + " L.TRANS_END_TIMESTAMP,L" +
                    ".TRANS_PERFORMED_BY,H.CHECKLIST_ITEM_SEQUENCE," + " CI.CHECKLIST_ITEM_ID, CI.CHECKLIST_ITEM_DESC, " +
                    " H.DATA_VALUE,H.OPERATOR_RRN,H.COMMENTS,H.STANDARD,PPS.OBJECT_ID,PPS.PM_ID FROM CHECKLIST_JOB_DATA_H H," +
                    " TRANSACTION_LOG L, CHECKLIST_JOB_H CJ, CHECKLIST_JOB_ITEM CI, PMS_PM_SCHEDULE PPS " +
                    " WHERE H.TRANS_RRN = L.TRANS_RRN(+) " + " AND H.CHECKLIST_JOB_RRN = CJ.CHECKLIST_JOB_RRN " +
                    " AND CJ.CHECKLIST_RRN = CI.CHECKLIST_RRN " + " AND H.CHECKLIST_JOB_RRN = CI.CHECKLIST_JOB_RRN " +
                    " AND PPS.OBJECT_RRN = CJ.ENTITY_RRN AND CJ.CHECKLIST_RRN = PPS.CHECKLIST_RRN AND CJ.PM_TYPE = PPS.PM_TYPE"+
                    " AND H.CHECKLIST_ITEM_SEQUENCE = CI.CHECKLIST_SEQUENCE " + "AND CJ.PM_RRN = PPS.PM_RRN AND H.TRANS_RRN = ? " +
                    " ORDER BY CHECKLIST_ITEM_SEQUENCE ";

            return jdbcTemplate.query(sql, new Object[]{transRrn}, (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                Map<String, Object> map = new HashMap<>();
                map.put("transRrn", rs.getLong("TRANS_RRN"));
                map.put("transId", rs.getString("TRANS_ID"));
                map.put("transStartTimestamp", DateUtils.formatDate(rs.getTimestamp("TRANS_START_TIMESTAMP")));
                map.put("transPerformedBy", rs.getString("TRANS_PERFORMED_BY"));
                map.put("checklistItemId", rs.getString("CHECKLIST_ITEM_ID"));
                map.put("checklistItemDesc", rs.getString("CHECKLIST_ITEM_DESC"));
                map.put("dataValue", rs.getString("DATA_VALUE"));
                map.put("checklistItemSequence", rs.getLong("CHECKLIST_ITEM_SEQUENCE"));
                map.put("operatorRrn", rs.getString("OPERATOR_RRN"));
                map.put("comments", rs.getString("COMMENTS"));
                map.put("standard", rs.getString("STANDARD"));
                map.put("objectId", rs.getString("OBJECT_ID"));
                map.put("pmId", rs.getString("PM_ID"));
                return map;
            });
        }

        @Override
        public void addTriggerMaintainHistory(EquipmentTriggerMaintainQuery etmq, TransactionLog tl) {
            List<Object> args = new ArrayList<>();
            args.add(tl.getTransRrn());
            args.add(tl.getTransSequence());
            args.add(tl.getTransId());
            args.add(etmq.getUserRrn());
            args.add(tl.getTransStartTimestamp());
            args.add(etmq.getEquipmentRrn());
            args.add(etmq.getTriggerCode());

            String sql = "INSERT INTO " + DataBaseNames.EQPT_TRIGGER_MAINTAIN_H +
                    " (TRANS_RRN,TRANS_SEQUENCE,TRANS_ID,EQUIPMENT_RRN,EQUIPMENT_ID,CHAMBER_ID,TRIGGER_CODE,TRIGGER_CODE_DESC,UOM," +
                    "TRIGGER_CODE_SPEC,TRIGGER_READING,TRIGGER_LEVEL,TRANS_USER_RRN,TRANS_TIME) " +
                    "SELECT ?, ?, ?, M.EQUIPMENT_RRN,M.EQUIPMENT_ID,M.CHAMBER_ID,M.TRIGGER_CODE,M.TRIGGER_CODE_DESC,M.UOM," +
                    "M.TRIGGER_CODE_SPEC,M.TRIGGER_READING, M.TRIGGER_LEVEL ,?,?  FROM " +
                    DataBaseNames.EQPT_TRIGGER_MAINTAIN + " M WHERE M.EQUIPMENT_RRN = ? AND M.TRIGGER_CODE = ?";
            jdbcTemplate.update(sql, args.toArray());
        }

        @Override
        public void addTriggerMaintain(EquipmentTriggerMaintainQuery etmq, TransactionLog tl) {
            List<Object> args = new ArrayList<>();
            args.add(etmq.getEquipmentRrn());
            args.add(etmq.getEquipmentId());
            args.add(etmq.getChamberId());
            args.add(etmq.getTriggerCode());
            args.add(etmq.getTriggerCodeSpec());
            args.add(etmq.getUserRrn());
            args.add(tl.getTransStartTimestamp());
            args.add(etmq.getTriggerLevel());
            args.add(etmq.getTriggerCodeDesc());
            args.add(etmq.getUOM());

            String sql = "INSERT INTO " + DataBaseNames.EQPT_TRIGGER_MAINTAIN +
                    " (EQUIPMENT_RRN,EQUIPMENT_ID,CHAMBER_ID,TRIGGER_CODE,TRIGGER_CODE_SPEC,"
                    + "LAST_TRIGGER_USER_RRN,LAST_TRIGGER_TIME,TRIGGER_LEVEL,TRIGGER_CODE_DESC, UOM) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
            jdbcTemplate.update(sql, args.toArray());
        }

        @Override
        public void modifyTriggerMaintain(EquipmentTriggerMaintainQuery etmq, TransactionLog tl) {
            List<Object> args = new ArrayList<>();
            args.add(etmq.getTriggerCodeSpec());
            args.add(etmq.getUserRrn());
            args.add(tl.getTransStartTimestamp());
            args.add(etmq.getTriggerReading());
            args.add(etmq.getEquipmentRrn());
            args.add(etmq.getTriggerCode());

            String sql = "UPDATE " + DataBaseNames.EQPT_TRIGGER_MAINTAIN + " TM " +
                    "SET TM.TRIGGER_CODE_SPEC = ?, TM.LAST_TRIGGER_USER_RRN = ?, TM.LAST_TRIGGER_TIME = ?, TM.TRIGGER_READING = ? " +
                    "WHERE TM.EQUIPMENT_RRN = ? AND TM.TRIGGER_CODE = ?";
            jdbcTemplate.update(sql, args.toArray());
        }

        @Override
        public void deleteTriggerMaintain(EquipmentTriggerMaintainQuery etmq) {
            String sql = "DELETE FROM " + DataBaseNames.EQPT_TRIGGER_MAINTAIN + " WHERE EQUIPMENT_RRN = ? AND TRIGGER_CODE = ?";
            jdbcTemplate.update(sql, etmq.getEquipmentRrn(), etmq.getTriggerCode());
        }

        @Override
        public EquipmentTriggerMaintain getTriggerMaintain(EquipmentTriggerMaintainQuery etmq) {
            String sql = "SELECT ETM.EQUIPMENT_ID,ETM.EQUIPMENT_RRN,ETM.CHAMBER_ID,ETM.TRIGGER_CODE,ETM.TRIGGER_CODE_SPEC,ETM.TRIGGER_READING, "
                    + "ETM.TRIGGER_CODE_DESC,ETM.UOM, "
                    + "ETM.LAST_TRIGGER_USER_RRN,ETM.LAST_TRIGGER_TIME "
                    + ",E.EQUIPMENT_LOCATION "
                    + ",(SELECT COUNT(1) FROM PMS_PM_SCHEDULE PPS WHERE PPS.OBJECT_ID=ETM.EQUIPMENT_ID AND PPS.TRIGGER_CODE = ETM.TRIGGER_CODE AND PPS.ITEM_STATUS=?) CITE_COUNT  "
                    + " FROM " + DataBaseNames.EQPT_TRIGGER_MAINTAIN + " ETM"
                    + " LEFT JOIN ENTITY_EXT E ON E.EQPT_RRN  = ETM.EQUIPMENT_RRN"
                    + " WHERE ETM.EQUIPMENT_ID = ? AND ETM.TRIGGER_CODE = ?";
            Object[] args = new Object[]{SystemConstant.Str.ON, etmq.getEquipmentId(), etmq.getTriggerCode()};
            return jdbcTemplate.queryForObjectWithNull(sql, args, (RowMapper<EquipmentTriggerMaintain>) (rs, rowNum) -> {
                EquipmentTriggerMaintain etm = buildEquipmentTriggerMaintain(rs);
                etm.setCiteCount(rs.getInt("CITE_COUNT"));
                return etm;
            });
        }

        @Override
        public List<EquipmentTriggerMaintain> queryTriggerCodeByEquipment(EquipmentTriggerMaintainQuery maintainQuery) {
            List<Object> args = new ArrayList<>();
            String sql = buildEquipmentTriggerMaintainSql(args, maintainQuery);
            return jdbcTemplate.query(sql, args.toArray(),
                                      (RowMapper<EquipmentTriggerMaintain>) (rs, rowNum) -> buildEquipmentTriggerMaintain(rs));
        }

        @Override
        public Page queryMaintainByPage(Page page, EquipmentTriggerMaintainQuery etmq) {
            List<Object> args = new ArrayList<>();
            String sql = buildEquipmentTriggerMaintainSql(args, etmq);
            return jdbcTemplate.queryForPage(page, sql, args.toArray(),
                                             (RowMapper<EquipmentTriggerMaintain>) (rs, rowNum) -> buildEquipmentTriggerMaintain(rs));
        }

        private String buildEquipmentTriggerMaintainSql(List<Object> args, EquipmentTriggerMaintainQuery etmq) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("TM.EQUIPMENT_ID,TM.EQUIPMENT_RRN,TM.CHAMBER_ID,TM.TRIGGER_CODE,TM.TRIGGER_CODE_SPEC,TM.TRIGGER_READING,");
            sql.append("NO.INSTANCE_ID || ' ' || UP.USER_NAME LAST_TRIGGER_USER_RRN,TM.LAST_TRIGGER_TIME,TM.TRIGGER_LEVEL ");
            sql.append(",TM.TRIGGER_CODE_DESC,TM.UOM,");
            sql.append("E.EQUIPMENT_LOCATION");
            sql.append(" FROM ");
            sql.append(DataBaseNames.EQPT_TRIGGER_MAINTAIN);
            sql.append(" TM ");
            sql.append(" LEFT JOIN NAMED_OBJECT NO ON NO.INSTANCE_RRN=TM.LAST_TRIGGER_USER_RRN ");
            sql.append(" LEFT JOIN USER_PROFILE UP ON UP.USER_RRN=TM.LAST_TRIGGER_USER_RRN ");
            sql.append(" LEFT JOIN ENTITY_EXT E ON E.EQPT_RRN  = TM.EQUIPMENT_RRN ");
            sql.append("WHERE 1=1 ");

            if (StringUtils.isNotBlank(etmq.getEquipmentId())) {
                sql.append("AND TM.EQUIPMENT_ID LIKE ? ");
                args.add(StringUtils.conditionSqlToLike(etmq.getEquipmentId()));
            }
            if (StringUtils.isNotBlank(etmq.getTriggerCode())) {
                sql.append("AND TM.TRIGGER_CODE LIKE ? ");
                args.add(SystemConstant.Str.PERCENTAGE + etmq.getTriggerCode() + SystemConstant.Str.PERCENTAGE);
            }

            if (StringUtils.isNotBlank(etmq.getTriggerLevel())) {
                sql.append("AND TM.TRIGGER_LEVEL = ? ");
                args.add(etmq.getTriggerLevel());
            }

            if (StringUtils.isNotBlank(etmq.getStartDate())) {
                sql.append("AND TM.LAST_TRIGGER_TIME >= TO_DATE(?, ?) ");
                args.add(etmq.getStartDate());
                args.add(DateUtils.DATE_FORMAT24);
            }
            if (StringUtils.isNotBlank(etmq.getEndDate())) {
                sql.append("AND TM.LAST_TRIGGER_TIME <= TO_DATE(?, ?) ");
                args.add(etmq.getEndDate());
                args.add(DateUtils.DATE_FORMAT24);
            }
            sql.append("ORDER BY TM.LAST_TRIGGER_TIME DESC ");
            return sql.toString();
        }

        @Override
        public Page queryMaintainHistoryByPage(Page page, EquipmentTriggerMaintainQuery etmq) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("TL.TRANS_ID, TL.TRANS_START_TIMESTAMP, TL.TRANS_PERFORMED_BY || ' ' || UP.USER_NAME TRANS_PERFORMED_BY, ");
            sql.append("TMH.EQUIPMENT_ID, TMH.CHAMBER_ID, TMH.TRIGGER_CODE, TMH.TRIGGER_CODE_SPEC, TMH.TRIGGER_READING, ");
            sql.append("TL.COMMENTS, TMH.TRIGGER_LEVEL ");
            sql.append("FROM ");
            sql.append(DataBaseNames.EQPT_TRIGGER_MAINTAIN_H);
            sql.append(" TMH ");
            sql.append(" LEFT JOIN USER_PROFILE UP ON UP.USER_RRN=TMH.TRANS_USER_RRN ");
            sql.append("INNER JOIN TRANSACTION_LOG TL ON TMH.TRANS_RRN = TL.TRANS_RRN ");
            sql.append("WHERE 1=1 ");

            List<Object> args = new ArrayList<>();
            if (StringUtils.isNotBlank(etmq.getEquipmentId())) {
                sql.append("AND TMH.EQUIPMENT_ID LIKE ? ");
                args.add(StringUtils.conditionSqlToLike(etmq.getEquipmentId()));
            }
            if (StringUtils.isNotBlank(etmq.getStartDate())) {
                sql.append("AND TL.TRANS_START_TIMESTAMP >= TO_DATE(?, ?) ");
                args.add(etmq.getStartDate() + SystemConstant.Str.TIME_START);
                args.add(DateUtils.DATE_FORMAT24);
            }
            if (StringUtils.isNotBlank(etmq.getEndDate())) {
                sql.append("AND TL.TRANS_START_TIMESTAMP <= TO_DATE(?, ?) ");
                args.add(etmq.getEndDate() + SystemConstant.Str.TIME_END);
                args.add(DateUtils.DATE_FORMAT24);
            }

            sql.append("ORDER BY TL.TRANS_START_TIMESTAMP DESC, TMH.TRANS_SEQUENCE DESC ");
            return jdbcTemplate.queryForPage(page, sql.toString(), args.toArray(),
                                             (RowMapper<EquipmentTriggerMaintainHistory>) (rs, rowNum) -> buildEquipmentTriggerMaintainHistory(rs));
        }

        @Override
        public void updateTriggerReadingByPms(PmsSchedule ps) {
            String sql = " UPDATE PMS_PM_SCHEDULE SET TRIGGER_READING = ?  WHERE PM_RRN = ? ";
            jdbcTemplate.update(sql, ps.getTriggerReading(), ps.getPmRrn());
        }

        private EquipmentTriggerMaintain buildEquipmentTriggerMaintain(ResultSet rs) throws SQLException {
            EquipmentTriggerMaintain etm = new EquipmentTriggerMaintain();
            etm.setEquipmentId(rs.getString("EQUIPMENT_ID"));
            etm.setEquipmentRrn(rs.getLong("EQUIPMENT_RRN"));
            etm.setChamberId(rs.getString("CHAMBER_ID"));
            etm.setTriggerCode(rs.getString("TRIGGER_CODE"));
            etm.setTriggerCodeSpec(rs.getString("TRIGGER_CODE_SPEC"));
            etm.setTriggerReading(rs.getString("TRIGGER_READING"));
            // etm.setTriggerLevel(rs.getString("TRIGGER_LEVEL"));
            etm.setLastTriggerUser(rs.getString("LAST_TRIGGER_USER_RRN"));
            etm.setLastTriggerDate(rs.getTimestamp("LAST_TRIGGER_TIME"));

            etm.setTriggerCodeDesc(rs.getString("TRIGGER_CODE_DESC"));
            etm.setUOM(rs.getString("UOM"));
            etm.setAreaId(rs.getString("EQUIPMENT_LOCATION"));
            return etm;
        }

        private EquipmentTriggerMaintainHistory buildEquipmentTriggerMaintainHistory(ResultSet rs) throws SQLException {
            EquipmentTriggerMaintainHistory etmh = new EquipmentTriggerMaintainHistory();
            etmh.setEquipmentId(rs.getString("EQUIPMENT_ID"));
            etmh.setChamberId(rs.getString("CHAMBER_ID"));
            etmh.setTriggerCode(rs.getString("TRIGGER_CODE"));
            etmh.setTriggerCodeSpec(rs.getString("TRIGGER_CODE_SPEC"));
            etmh.setTriggerReading(rs.getString("TRIGGER_READING"));
            etmh.setTriggerLevel(rs.getString("TRIGGER_LEVEL"));
            etmh.setComments(rs.getString("COMMENTS"));
            etmh.setTransUser(rs.getString("TRANS_PERFORMED_BY"));
            etmh.setTransDate(rs.getTimestamp("TRANS_START_TIMESTAMP"));
            etmh.setTransId(rs.getString("TRANS_ID"));
            return etmh;
        }

        /**
         * @param startTime :开始时间
         * @param frequence :间隔
         * @param unit      :间隔单位Y,M,W,D,H,I,S分别表示年，月，周，日，天，分，秒
         * @return : long ：startTime+ frequence*unit 得到开始时间在第一次间隔以后的时间
         * @author Bright
         */
        private long getNextTime(long startTime, int frequence, String unit) {
            java.util.Calendar calendar = java.util.Calendar.getInstance();
            calendar.setTimeInMillis(startTime);
            if (unit.equalsIgnoreCase("Y")) {
                calendar.add(java.util.Calendar.YEAR, frequence);
            } else if (unit.equalsIgnoreCase("M")) {
                calendar.add(java.util.Calendar.MONTH, frequence);
            } else if (unit.equalsIgnoreCase("W")) {
                calendar.add(java.util.Calendar.WEEK_OF_YEAR, frequence);
            } else if (unit.equalsIgnoreCase("D")) {
                calendar.add(java.util.Calendar.DATE, frequence);
            } else if (unit.equalsIgnoreCase("H")) {
                calendar.add(java.util.Calendar.HOUR, frequence);
            } else if (unit.equalsIgnoreCase("I")) {
                calendar.add(java.util.Calendar.MINUTE, frequence);
            } else if (unit.equalsIgnoreCase("S")) {
                calendar.add(java.util.Calendar.SECOND, frequence);
            }
            return calendar.getTimeInMillis();
        }

        private long addTimeByUnit(long time, String unit, long freq) {
            if ("D".equalsIgnoreCase(unit)) {
                // DAY
                time = time + freq * 24 * 60 * 60 * 1000;
            } else if ("W".equalsIgnoreCase(unit)) {
                // WEEK
                time = time + freq * 7 * 24 * 60 * 60 * 1000;
            } else if ("M".equalsIgnoreCase(unit)) {
                // MONTH
                GregorianCalendar nextGC = new GregorianCalendar();
                nextGC.setTime(new Date(time));
                nextGC.set(Calendar.MONTH, nextGC.get(Calendar.MONTH) + (int) freq);
                time = (nextGC.getTime()).getTime();
                // time = nextGC.getTimeInMillis();
            } else if ("Y".equalsIgnoreCase(unit)) {
                // YEAR
                time = time + freq * 365 * 24 * 60 * 60 * 1000;
            } else if ("H".equalsIgnoreCase(unit)) {
                // HOUR
                time = time + freq * 60 * 60 * 1000;
            } else {
                // Minute
                time = time + freq * 60 * 1000;
            }
            return time;
        }

        private List<Map<String, Object>> getWorkAreaByUser(long userRrn, String entityNamedSpace) {
            List<Object> argList = new ArrayList<>();

            argList.add(ObjectList.ENTITY_KEY);
            argList.add(ObjectList.EQUIPMENT_KEY);
            argList.add(entityNamedSpace);
            argList.add(LinkTypeList.STATION_EQPT_KEY);
            argList.add(LinkTypeList.STATION_USER_KEY);
            argList.add(LinkTypeList.USER_USERGROUP_KEY);
            argList.add(userRrn);

            String sql = "SELECT " + "KEY_1_VALUE, DATA_1_VALUE FROM NAMED_OBJECT A, ENTITY_EXT E, " + "(SELECT FD" +
                    ".KEY_1_VALUE, FD.DATA_1_VALUE FROM NAMED_OBJECT N, REFERENCE_FILE_DETAIL " + "FD " + "WHERE N" +
                    ".INSTANCE_ID = '$EQPT_LOCATION' AND N.INSTANCE_RRN = FD.REFERENCE_FILE_RRN) " + "USE_WA " + "WHERE A" +
                    ".OBJECT = ? AND A.OBJECT_TYPE = ? AND NAMED_SPACE = ? " + "AND A.INSTANCE_RRN = E.EQPT_RRN AND E" +
                    ".EQUIPMENT_LOCATION = USE_WA.KEY_1_VALUE " + "AND A.INSTANCE_RRN IN (SELECT TO_RRN FROM RELATION " +
                    "WHERE LINK_TYPE = ? " + "AND FROM_RRN IN (SELECT FROM_RRN FROM RELATION WHERE LINK_TYPE = ? " + "AND" +
                    " TO_RRN IN (SELECT TO_RRN from RELATION WHERE LINK_TYPE = ? AND FROM_RRN = ?))) " + "GROUP BY USE_WA" +
                    ".KEY_1_VALUE, USE_WA.DATA_1_VALUE ";
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, argList.toArray());
            List<Map<String, Object>> list = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("areaKey", rs.getString("KEY_1_VALUE"));
                map.put("areaData", rs.getString("DATA_1_VALUE"));
                list.add(map);
            }
            return list;
        }

        private long getPmScheduleSeqNo(PmSchedule pmSchedule) {
            String sql = " SELECT NVL(MAX(SEQUENCE_NUMBER),0)+1 as SEQ FROM " + DataBaseNames.PM_SCHEDULE + " where " +
                    "ENTITY_RRN = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, long.class, pmSchedule.getEntityRrn());
        }

        private StringBuilder buildPMSqrySql(PmsSchedule schedule) {
            StringBuilder sb = new StringBuilder(
                    "SELECT DISTINCT * FROM (SELECT K.*,CASE WHEN CHAMBER_RRN IS NULL OR CHAMBER_RRN <=0 THEN " +
                            "OBJECT_STATUS ELSE CHAMBER_STATUS END AS EQPT_STATUS ");
            sb.append("FROM (SELECT P.PM_RRN,PM_ID,OBJECT_RRN,OBJECT_TYPE,OBJECT_ID,CHAMBER_ID,PM_TYPE," + "PM_ITEM_DESC," +
                              "CYCLE_TIME,DURATION_TIME, NEXT_PM_TIME,");
            sb.append("TOTAL_COUNT,ALARM_COUNT,CONTROL_TYPE,CHECKLIST_RRN," + "GETINSTANCEID(CHECKLIST_RRN) CHECKLIST_ID," +
                              "PM_TIME_TYPE,PM_LINKS,");
            sb.append("TOLERANCE_TIME,CYCLE_COUNT,DURATION_COUNT,CURRENT_PM_COUNT,TOLERANCE_COUNT,CYCLE_OTHER," +
                              "DURATION_OTHER,CURRENT_PM_OTHER,R.STATION_IDS,");
            sb.append("TOLERANCE_OTHER,ITEM_STATUS,AUTO_EQP_STATUS,P.CREATE_USER_RRN,CREATE_DATE,P.ATTRIBUTE_DATA_1,P" +
                              ".ATTRIBUTE_DATA_2,P.ATTRIBUTE_DATA_3,P.ATTRIBUTE_DATA_4,");
            sb.append("P.ATTRIBUTE_DATA_5,E.CURRENT_STATUS AS OBJECT_STATUS,C.CURRENT_STATUS AS CHAMBER_STATUS,P" +
                              ".CHAMBER_RRN,X.EQUIPMENT_LOCATION,GETINSTANCEID(P.CREATE_USER_RRN) AS PM_CREATE_USER,");
            sb.append("PL.trans_id,P.TRIGGER_CODE,P.TRIGGER_CODE_SPEC,P.TRIGGER_READING");
            if (StringUtils.isNotBlank(schedule.getUserLocation())) {
                sb.append(",'").append(schedule.getUserLocation()).append("' AS USER_LOCATION ");
            } else {
                sb.append(",'' AS USER_LOCATION ");
            }

            sb.append(" FROM PMS_PM_SCHEDULE P LEFT JOIN (SELECT CURRENT_STATUS,ENTITY_RRN FROM " + "ENTITY_STATUS L" + " " +
                              "WHERE STATUS_GROUP_ID='EQPT_STATUS1') E ON P.OBJECT_RRN=E.ENTITY_RRN LEFT JOIN ");
            sb.append(
                    "(SELECT CURRENT_STATUS,ENTITY_RRN FROM ENTITY_STATUS L WHERE " + "STATUS_GROUP_ID='EQPT_STATUS1') C " +
                            "ON P.CHAMBER_RRN = C.ENTITY_RRN LEFT JOIN ENTITY_EXT" + " X ON P.OBJECT_RRN = X.EQPT_RRN " +
                            "LEFT JOIN ");
            sb.append("(SELECT R.TO_RRN, (listagg(getinstanceid(R.FROM_RRN), ',') WITHIN GROUP (ORDER BY R.TO_RRN)) AS " +
                              "STATION_IDS FROM RELATION R WHERE R.LINK_TYPE = 'STATION_TO_EQUIPMENT' " + "GROUP BY R" +
                              ".TO_RRN) R ON P.OBJECT_RRN = R.TO_RRN ");
            sb.append("LEFT JOIN (SELECT PHT.TRANS_ID,PHT.PM_RRN FROM (SELECT  tl.trans_id,pms.pm_rrn " +
                              "FROM PMS_PM_SCHEDULE pms,PMS_PM_SCHEDULE_H pmsh,transaction_log tl " +
                              "where pms.pm_rrn = pmsh.PM_rrn and pmsh.trans_rrn=tl.trans_rrn ORDER BY tl.trans_start_timestamp desc) PHT WHERE ROWNUM=1" +
                              ") PL ON PL.pm_rrn = P.PM_RRN ");
            if (StringUtils.isNotBlank(schedule.getStation())) {
                sb.append("WHERE R.STATION_IDS LIKE '").append(schedule.getStation()).append("'");
            }
            sb.append(")K WHERE 1=1 ");

            if (StringUtils.isNotBlank(schedule.getStartDate())) {
                sb.append(" AND K.NEXT_PM_TIME >=to_timestamp('").append(schedule.getStartDate()).append(" 00:00:00','")
                  .append(DateUtils.DATE_FORMAT24).append("') ");
            }

            if (StringUtils.isNotBlank(schedule.getEndDate())) {
                sb.append(" AND K.NEXT_PM_TIME <=to_timestamp('").append(schedule.getEndDate()).append(" 23:59:59','")
                  .append(DateUtils.DATE_FORMAT24).append("') ");
            }

            if (StringUtils.isNotBlank(schedule.getPmId())) {
                sb.append(" AND K.PM_ID = '").append(schedule.getPmId()).append("'");
            }

            if (StringUtils.isNotBlank(schedule.getTriggerCode())) {
                sb.append(" AND K.TRIGGER_CODE = '").append(schedule.getTriggerCode()).append("'");
            }

            if (StringUtils.isNotBlank(schedule.getObjectId())) {
                sb.append(" AND K.OBJECT_ID LIKE '").append(schedule.getObjectId()).append("'");
            }

            if (schedule.getObjectRrn() != null && schedule.getObjectRrn() > 0) {
                sb.append(" AND K.OBJECT_RRN = ").append(schedule.getObjectRrn());
            }

            if (StringUtils.isNotBlank(schedule.getChamberId())) {
                sb.append(" AND K.CHAMBER_ID = '").append(schedule.getChamberId()).append("'");
            }

            if (StringUtils.isNotBlank(schedule.getPmType())) {
                sb.append(" AND K.PM_TYPE = '").append(schedule.getPmType()).append("'");
            }
            sb.append(") M WHERE 1=1 ");

            if (StringUtils.isNotBlank(schedule.getEqptStatus()) &&
                    !StringUtils.equalsIgnoreCase(schedule.getEqptStatus(), "ALL")) {
                sb.append(" AND M.EQPT_STATUS = '").append(schedule.getEqptStatus()).append("'");
            }

            if (StringUtils.isNotBlank(schedule.getEquipmentLocation()) &&
                    !StringUtils.equalsIgnoreCase(schedule.getEquipmentLocation(), "ALL")) {
                sb.append(" AND M.EQUIPMENT_LOCATION = '").append(schedule.getEquipmentLocation()).append("'");
            }

            if (StringUtils.isNotBlank(schedule.getItemStatus())) {
                sb.append(" AND M.ITEM_STATUS = '").append(schedule.getItemStatus()).append("'");
            }
            if (StringUtils.isNotBlank(schedule.getControlType())) {
                sb.append(" AND M.CONTROL_TYPE = '").append(schedule.getControlType()).append("'");
            }
            if (schedule.getChecklistRrn() != null && schedule.getChecklistRrn() != 0L) {
                sb.append(" AND M.CHECKLIST_RRN = '").append(schedule.getChecklistRrn()).append("'");
            }

            String[] pmrrns;
            String pmRrnStr = schedule.getPmRrnStr();
            if (StringUtils.isBlank(pmRrnStr) && schedule.getPmRrn() != null) {
                pmRrnStr = schedule.getPmRrn() + "";
            }
            if (StringUtils.isNotBlank(pmRrnStr)) {
                pmrrns = pmRrnStr.split(",");
                sb.append(" AND M.PM_RRN IN (");
                buildPmRrnsSql(pmrrns, sb);
            }
            return sb;
        }

        private void buildPmRrnsSql(String[] pmrrns, StringBuilder sql) {
            for (int i = 0; i < pmrrns.length; i++) {
                if (i > 0) {
                    sql.append(",").append(Long.parseLong(pmrrns[i]));
                } else {
                    sql.append(Long.parseLong(pmrrns[i]));
                }
            }
            sql.append(")");
        }

    }