reminderdao.java

发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！

            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        
        return newReminder;
    }    

    public Reminder updateReminder(Reminder reminder, String user) {
        return updateReminder(reminder, user, null);
    }   
    
    public Reminder updateReminder(Reminder reminder, String user, Connection con) {
        
        String sql = "UPDATE \"Reminder\"\n";

        try {
            sql += "set \"ContactID\" = " + reminder.getContactID() + ",\n";
            sql += "    \"Creator\" = " + quoteSingle(reminder.getCreator().trim()) + ",\n";
            sql += "    \"ReminderDate\" =  " + quoteSingle(Reminder.fullFormat.format(reminder.getReminderDate())) + ",\n";
            sql += "    \"ReminderNext\" =  " + quoteSingle(Reminder.fullFormat.format(reminder.getReminderNext())) + ",\n";
            sql += "    \"Frequency\" = " + reminder.getFrequency() + ",\n";
            sql += "    \"ReminderMessage\" = " + quoteSingle(URLEncoder.encode(reminder.getReminderMessage(), "UTF-8")) + ",\n";
            sql += "    \"Owner\" = " + quoteSingle(reminder.getOwner().trim()) + ",\n";
            sql += "    \"OwnerHasRead\" = " + reminder.getOwnerHasRead() + ",\n";
            sql += "    \"ReminderRate\" = " + reminder.getReminderRate() + ",\n";
            sql += "    \"EmailReminder\" = " + reminder.isEmailReminder() + ",\n";

            /*if (reminder.getEmailReminderTo() != null) {
                sql += "    \"EmailReminderTo\" = " + quoteSingle(reminder.getEmailReminderTo().trim()) + ",\n";
            } else {
                sql += "    \"EmailReminderTo\" = null,\n";
            }*/
            
            sql += "    \"SMSReminder\" = " + reminder.isSMSReminder() + "\n";
            
/*            if (reminder.getSMSReminderTo() != null) {
                sql += "    \"SMSReminderTo\" = " + quoteSingle(reminder.getSMSReminderTo().trim()) + "\n";
            } else {
                sql += "    \"SMSReminderTo\" = null\n";
            }*/
            
            /*sql += "    \"Approved\" = " + reminder.isApproved() + ",\n";
            
            if (reminder.getApprovedBy() != null) {
                sql += "    \"ApprovedBy\" = " + quoteSingle(reminder.getApprovedBy().trim()) + "\n";
            } else {
                sql += "    \"ApprovedBy\" = null\n";
            }*/
            
            sql += "WHERE \"Reminder\".\"ReminderID\"=" + reminder.getReminderID();
            
            logger.debug(sql);
        
            int rows = updateSQL(sql, con);

            if (rows != 1) {
             throw new RuntimeException("Invalid number of rows updated: " + rows + " executing sql: " + sql);
            }
        
            return getReminder(reminder.getReminderID(), user);
        }
        catch (Exception ex) {
         throw new RuntimeException("Exception executing sql: " + sql, ex);   
        }
        
    }
    
    public void deleteReminder(int reminderID) {
        
        String sql = "Update \"Reminder\"\n";
        sql += "set \"Deleted\" = true\n";
        sql += "Where \"ReminderID\" = " + reminderID;

        try {
            int rows = updateSQL(sql);
            if (rows != 1) {
                throw new RuntimeException("Invalid rows (" + rows + ") updated executing: " + sql);
            }
        }
        catch (Exception ex) {
            throw new RuntimeException("Exception executing: " + sql, ex);
        }
    }
    
    static SimpleDateFormat smsDf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
    
    public int getSMSCountForMonth(String userID) {
        
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_MONTH, 1);
        cal.set(Calendar.HOUR, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        
        java.util.Date start = cal.getTime();
        
        cal.add(Calendar.MONTH, 1);
        java.util.Date end = cal.getTime();
        
        String sql = "select count(*)\n";
        sql += "FROM \"Reminder\"\n";
        sql += "WHERE \"Deleted\" = false\n";
        sql += "  AND \"SMSReminder\" = true\n";
        sql += "  AND \"ReminderDate\" >= " + quoteSingle(smsDf.format(start)) + "\n";
        sql += "  AND \"ReminderDate\" < " + quoteSingle(smsDf.format(end)) + "\n";
        
        logger.debug("Executing sql:\n" + sql);

        Connection con = null;
        ResultSet rs = null;
        Statement stmt = null;
        int count = 0;

        try {
            con = getFactory().getConnection();

            stmt = con.createStatement();

            rs = stmt.executeQuery(sql);

            if (rs.next()) {
                count = rs.getInt(1);
            }
        }
        catch (SQLException ex) {
            logger.error(ex);
        }
        finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (stmt != null) {
                    stmt.close();
                }

                if (con != null) {
                    con.close();
                }
            }
            catch (SQLException ex) {
                logger.error(ex);
            }
        }
        
        return count;
        
    }
    
    public Object createFromResultSet(ResultSet rs) throws SQLException, UnsupportedEncodingException{
        Reminder reminder = new Reminder();
        
        reminder.setContactID(rs.getInt("ContactID"));
        reminder.setCreator(rs.getString("Creator"));
        Timestamp ts = rs.getTimestamp("ReminderDate");
        reminder.setReminderDate(new java.util.Date(ts.getTime()));

        ts = rs.getTimestamp("ReminderNext");
        reminder.setReminderNext(new java.util.Date(ts.getTime()));
        
        String message = rs.getString("ReminderMessage");
        if (message != null) {
            reminder.setReminderMessage(URLDecoder.decode(message, "UTF-8"));
        }
        
        reminder.setOwner(rs.getString("Owner"));
        reminder.setOwnerHasRead(rs.getBoolean("OwnerHasRead"));
        reminder.setReminderRate(rs.getInt("ReminderRate"));
        logger.debug("Retrieved ReminderRate = " + reminder.getReminderRate());
        reminder.setReminderID((rs.getInt("ReminderID")));
        reminder.setEmailReminder(rs.getBoolean("EmailReminder"));
        //reminder.setEmailReminderTo(rs.getString("EmailReminderTo"));
        reminder.setSMSReminder(rs.getBoolean("SMSReminder"));
        reminder.setFrequency(rs.getInt("Frequency"));
        //reminder.setSMSReminderTo(rs.getString("SMSReminderTo"));
        /*reminder.setApproved(rs.getBoolean("Approved"));
        reminder.setApprovedBy(rs.getString("ApprovedBy"));*/
        reminder.setDeleted(rs.getBoolean("Deleted"));
        return reminder;
    }
    
    public void addReminderLogEntry(MessageLogEntry entry, Connection con) {
     
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        
        String sql = "";
        sql = "INSERT into \"ReminderLog\"\n";
        sql += "(\"StaffMember\", \"SMSRecipient\", \"EmailRecipient\", \"MessageDateTime\", \"MessageSent\", \"IsASMS\", \"IsEmail\")\n";
        sql += "Values\n";
        sql += "('" + entry.getStaffMember() + "',\n";
        sql += " '" + entry.getSMSRecipient() + "',\n";
        sql += " '" + entry.getEmailRecipient() + "',\n";
        sql += " '" + df.format(entry.getLogDate()) + "',\n";
        sql += " '" + encode(entry.getMessage()) + "',\n";
        sql += " " + entry.isSMS() + ",\n";
        sql += " " + entry.isEmail() + ")";
        
        try {
            int rows = updateSQL(sql, con);
            if (rows != 1) {
                throw new RuntimeException("Invalid rows updated executing: " + sql);   
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Exception excuting sql: " + sql);    
        }
    }
    
}