# AlarmDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.MiscUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.alarm.dao.AlarmDao;
    import com.mycim.server.alarm.dao.mapper.EmailMessageMapper;
    import com.mycim.valueobject.alm.Alarm;
    import com.mycim.valueobject.alm.EenMessage;
    import com.mycim.valueobject.alm.EmailMessage;
    import com.mycim.valueobject.alm.TriggeredAlarm;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.LinkTypeList;
    import com.mycim.valueobject.security.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

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

    @Repository
    public class AlarmDaoImpl implements AlarmDao {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public EenMessage getEenMessage(EenMessage eenMessage, long instanceRrn) {
            String sql =
                    "SELECT MESSAGE_RRN,MESSAGE_SUBJECT,MESSAGE_TEXT" + " FROM EEN_MESSAGE " + " WHERE MESSAGE_RRN " + "=" +
                            instanceRrn;
            return jdbcTemplate.queryForObject(sql, new RowMapper<EenMessage>() {

                @Override
                public EenMessage mapRow(ResultSet rs, int rowNum) throws SQLException {
                    // construct the value object
                    eenMessage.setInstanceRrn(instanceRrn);
                    eenMessage.setMessageSubject(rs.getString("MESSAGE_SUBJECT"));
                    eenMessage.setMessageText(rs.getString("MESSAGE_TEXT"));
                    return eenMessage;
                }
            });
        }

        @Override
        public boolean isAlarmID(String alarmType, String alarmID) {
            String sql = "SELECT COUNT(*) FROM " + DataBaseNames.ALARM + " WHERE ALARM_TYPE = '" + alarmType + "' and " +
                    "ALARM_ID = '" + alarmID + "'";
            long number = jdbcTemplate.queryForObject(sql, int.class);
            if (number > 0) {
                return true;
            } else if (number == 0) {
                return false;
            } else {
                return false;
            }
        }

        @Override
        public Alarm getAlarm(Alarm alarm) {
            String sql = "SELECT ALARM_DESC,ALARM_CATEGORY_RRN,ENABLE_FLAG FROM " + DataBaseNames.ALARM + " WHERE " +
                    "ALARM_TYPE = ? and EQUIPMENT_MODEL_RRN = ?" + " and ALARM_ID = ?";

            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{alarm.getAlarmType(), alarm.getEqptModelRrn(),
                                                                         alarm.getAlarmId()}, new RowMapper<Alarm>() {

                @Override
                public Alarm mapRow(ResultSet rs, int rowNum) throws SQLException {
                    alarm.setAlarmDesc(rs.getString("ALARM_DESC"));
                    alarm.setAlarmCategoryRrn(rs.getLong("ALARM_CATEGORY_RRN"));
                    alarm.setEnableFlag(rs.getString("ENABLE_FLAG"));
                    return alarm;
                }
            });
        }

        @Override
        public void insertAlarm(Alarm alarm) {
            Object[] obj = {alarm.getAlarmType(), alarm.getAlarmId(), alarm.getAlarmDesc(), alarm.getAlarmCategoryRrn(),
                            alarm.getEnableFlag()};

            String sql = "INSERT INTO " + DataBaseNames.ALARM + " (EQUIPMENT_MODEL_RRN,ALARM_TYPE,ALARM_ID,ALARM_DESC," +
                    "ALARM_CATEGORY_RRN,ENABLE_FLAG)" + " VALUES (" + MiscUtils.parseSQL(alarm.getEqptModelRrn()) + ", " +
                    MiscUtils.parseSQL(obj) + ")";
            jdbcTemplate.update(sql);
        }

        @Override
        public void updateAlarm(Alarm alarm) {
            String sql = "UPDATE " + DataBaseNames.ALARM + " SET ALARM_DESC = " + MiscUtils.parseSQL(alarm.getAlarmDesc()) +
                    "," + " ALARM_CATEGORY_RRN = " + MiscUtils.parseSQL(alarm.getAlarmCategoryRrn()) + "," +
                    " ENABLE_FLAG = " + StringUtils.wrapWithApostropheIfMissing(alarm.getEnableFlag()) +
                    " WHERE ALARM_TYPE = " + StringUtils.wrapWithApostropheIfMissing(alarm.getAlarmType()) +
                    " and EQUIPMENT_MODEL_RRN = " + MiscUtils.parseSQL(alarm.getEqptModelRrn()) + " and ALARM_ID = " +
                    StringUtils.wrapWithApostropheIfMissing(alarm.getAlarmId());
            jdbcTemplate.update(sql);
        }

        @Override
        public void deleteAlarm(Alarm alarm) {
            String sql = "DELETE FROM " + DataBaseNames.ALARM + " WHERE ALARM_TYPE = " +
                    MiscUtils.parseSQL(alarm.getAlarmType()) + " and EQUIPMENT_MODEL_RRN = " +
                    MiscUtils.parseSQL(alarm.getEqptModelRrn()) + " and ALARM_ID = " +
                    StringUtils.wrapWithApostropheIfMissing(alarm.getAlarmId());
            jdbcTemplate.update(sql);
        }

        @Override
        public List<EmailMessage> getAlarmEmailMessages(String status, int pageSize, String type) {
            List<Object> params = new ArrayList<>();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT t0.* FROM (SELECT t.* FROM alarm_email_message t where t.status = ?");
            params.add(status);

            if (StringUtils.isNotBlank(type)) {
                sql.append(" and t.EMAIL_TYPE = ? ");
                params.add(type);
            }

            sql.append("order by t.created_date) t0  where rownum <= ?");
            params.add(pageSize);

            return jdbcTemplate.query(sql.toString(), params.toArray(), new EmailMessageMapper());
        }

        @Override
        public List<User> getUserInfo4AlarmByDepartment(String department) {
            String sql = "select user_rrn,user_name,email_address from user_profile where shift_id in" + " (";
            String[] departments = StringUtils.split(department, EmailMessage.USERGROUP_SEPARATOR);
            for (int i = 0; i < departments.length; i++) {
                if (i == 0) {
                    sql += "?";
                } else {
                    sql += ",?";
                }
            }
            sql += ")and email_address is not null ";
            return jdbcTemplate.query(sql, departments, (RowMapper<User>) (rs, rowNum) -> {
                User user = new User();
                user.setUserName(rs.getString("user_name"));
                user.setEmailAddress(rs.getString("email_address"));
                return user;
            });
        }

        @Override
        public List<User> getEmailAddressByUserGroups(String toUserGroupRrns) {
            String sql = "SELECT distinct t.user_rrn,t.user_name,t.email_address FROM user_profile t,relation r " +
                    "where r.from_rrn=t.user_rrn and r.link_type='" + LinkTypeList.USER_USERGROUP_KEY + "' and t" +
                    ".email_address is not null and r.to_rrn in (" + toUserGroupRrns + ") order by t.user_name ";
            return jdbcTemplate.query(sql, (RowMapper<User>) (rs, rowNum) -> {
                User user = new User();
                user.setUserName(rs.getString("user_name"));
                user.setEmailAddress(rs.getString("email_address"));
                return user;
            });
        }

        @Override
        public void deleteEmailMessage(EmailMessage emailMessage) {
            String sql = "UPDATE alarm_email_message t SET t.status='SENT',t.last_updated_date=sysdate WHERE t" +
                    ".message_rrn=?";
            jdbcTemplate.update(sql, emailMessage.getMessageRrn());
        }

        @Override
        public void markedSendMailError(EmailMessage emailMessage) {
            String sql = "UPDATE alarm_email_message  SET status='SENT_ERROR',last_updated_date=sysdate " + "WHERE " +
                    "message_rrn=?";
            jdbcTemplate.update(sql, emailMessage.getMessageRrn());
        }

        @Override
        public void deleteOldEmailMessages() {
            String sql = "DELETE FROM alarm_email_message  WHERE (status='SENT' or status='SENT_ERROR') and " + " " +
                    "last_updated_date <  sysdate - 7";
            jdbcTemplate.update(sql);
        }

        @Override
        public void insertAlarmEmailMessag(EmailMessage emailMessage) {
            String sql = " INSERT INTO " + DataBaseNames.ALARM_EMAIL_MESSAGE + " (MESSAGE_RRN,EMAIL_TYPE, FROM_USER, " +
                    "TO_USERS,CC_USERS, SUBJECT, CONTENT, " + "CREATED_DATE, CREATED_USER, LAST_UPDATED_DATE, " +
                    "LAST_UPDATED_USER, " + " STATUS,ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3, ATTRIBUTE_DATA4, " +
                    "ATTRIBUTE_DATA5)\n" + " VALUES (MSG_SEQ.nextval, ?, ?, ?, ? ,?, ?, sysdate, ?, sysdate, ?, ?, ?, " +
                    "?, ?, ?, " + "?)";

            jdbcTemplate.update(sql, emailMessage.getEmailType(), emailMessage.getFromUser(), emailMessage.getToUsers(),
                                emailMessage.getCcUsers(), emailMessage.getSubject(), emailMessage.getContent(),
                                emailMessage.getCreatedUser().getInstanceRrn(),
                                emailMessage.getLastUpdatedUser().getInstanceRrn(), "WAITING",
                                emailMessage.getAttributeData1(), emailMessage.getAttributeData2(),
                                emailMessage.getAttributeData3(), emailMessage.getAttributeData4(),
                                emailMessage.getAttributeData5());
        }

        @Override
        public void insertAlarmActionHistory(TriggeredAlarm alarmInfo) {

            Object[] obj = {alarmInfo.getReportAlarmRrn(), alarmInfo.getAlarmStatus(), alarmInfo.getAlarm().getAlarmId(),
                            alarmInfo.getAlarmText(), alarmInfo.getAlarm().getAlarmType(), alarmInfo.getSeverity(),
                            alarmInfo.getPriority(), alarmInfo.getLotRrn(), alarmInfo.getChamberRrn(),
                            alarmInfo.getEqptRrn(), alarmInfo.getJobRrn(), alarmInfo.getRunRrn(),
                            alarmInfo.getTriggeredBy(), alarmInfo.getTriggerExecutionRrn(), alarmInfo.getAcknowledgedFlag(),
                            alarmInfo.getAcknowledgedBy(), alarmInfo.getAcknowledgedExecutionRrn(),
                            alarmInfo.getAcknowledgedComments(), alarmInfo.getClearedFlag(), alarmInfo.getClearedBy(),
                            alarmInfo.getClearExecutionRrn(), alarmInfo.getClearedComments(),
                            alarmInfo.getRemindExecutionRrn(), alarmInfo.getManualTriggerFlag()};

            // insert into ALARM_ACTTION_H table
            String sqlh = "INSERT INTO " + DataBaseNames.ALARM_ACTION_HISTORY + " (REPORT_ALARM_RRN,ALARM_STATUS," +
                    "ALARM_ID,ALARM_TEXT,ALARM_TYPE,SEVERITY,PRIORITY, " + " LOT_RRN,CHAMBER_RRN,EQPT_RRN,JOB_RRN," +
                    "RUN_RRN,TRIGGERED_BY," + "TRIGGERED_EXECUTION_RRN,ACKNOWLEDGED_FLAG," + " ACKNOWLEDGED_BY," +
                    "ACKNOLEDGED_EXECUTION_RRN,ACKNOWLEDGED_COMMENTS," + " CLEARED_FLAG,CLEARED_BY,CLEARED_EXECUTION_RRN," +
                    "CLEARED_COMMENTS," + " REMIND_EXECUTION_RRN,MANUALLY_TRIGGER_FLAG,TRIGGERED_TIME,ACKNOWLEDGED_TIME," +
                    "CLEARED_TIME)" + " VALUES (" + MiscUtils.parseSQL(obj) + "," + " to_date('" +
                    processTime(alarmInfo.getTriggeredTime()) + "', '" + DateUtils.DATE_FORMAT24 + "')," + " to_date('" +
                    processTime(alarmInfo.getAcknowledgedTime()) + "', '" + DateUtils.DATE_FORMAT24 + "')," + " to_date('" +
                    processTime(alarmInfo.getClearedTime()) + "', '" + DateUtils.DATE_FORMAT24 + "') ) ";

            jdbcTemplate.update(sqlh);
        }

        @Override
        public Map<String, Object> getTriggerAlarmWithWorkFlowInfo(Long entityRrn, String alarmId) {
            String sql_1 = "SELECT MAINTENANCE_ENGINEER_RRN from ENTITY " + " WHERE ENTITY_RRN=" + entityRrn;

            String sql_2 = "SELECT T.ALARM_DESC ,T.ALARM_ID ,C.TRIGGER_WFL_RRN ,C.ALARM_CATEGORY_RRN FROM ALARM T ," +
                    "ALARM_CATEGORY C" + " WHERE T.ALARM_CATEGORY_RRN=C.ALARM_CATEGORY_RRN" + " AND T.ALARM_ID='" +
                    alarmId + "'" + " AND T.ALARM_TYPE='SYSTEM'";

            Map<String, Object> map = new HashMap<>();
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql_1);
            if (rs.next()) {
                map.put("toUserRrn", rs.getLong("MAINTENANCE_ENGINEER_RRN"));
            } else {
                map.put("toUserRrn", 0L);
            }

            rs = jdbcTemplate.queryForRowSet(sql_2);
            long alarmCategoryRrn = 0;
            if (rs.next()) {
                map.put("alarmId", alarmId);
                map.put("alarmText", rs.getString("ALARM_DESC"));
                map.put("workflowRrn", rs.getLong("TRIGGER_WFL_RRN"));
                alarmCategoryRrn = rs.getLong("ALARM_CATEGORY_RRN");
            }
            Alarm alarm = new Alarm();
            alarm.setAlarmId(alarmId);
            alarm.setAlarmType("SYSTEM");

            alarm = getAlarm(alarm);

            map.put("alarm", alarm);

            return map;
        }

        private String processTime(Timestamp timestamp) {
            String time = DateUtils.formatDate(timestamp);
            time = StringUtils.isEmpty(time) ? "" : time;
            return time;
        }

    }