reminderdao.java

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

    /* CRMS, customer relationship management system
    Copyright (C) 2003  Service To Youth Council

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

    For further information contact the SYC ICT department on [e-mail]
    98 Kermode Street
    North Adelaide
    South Australia
    SA 5006 
    [phone]
    */
/*
 * ReminderDAO.java
 *
 * Created on 28 March 2003, 00:19
 */

package crms.dao;

import crms.vo.*;
import crms.util.*;
import org.apache.log4j.Logger;

import java.text.*;
import java.util.*;
import java.sql.*;
import java.net.*;

import java.io.*;


/**
 *
 * @author  dmurphy
 */
public class ReminderDAO extends AbstractDAO {
    
    static Logger logger = Logger.getLogger(ReminderDAO.class);

    public static SimpleDateFormat df = new SimpleDateFormat("d MMMM, yyyy");
    public static SimpleDateFormat tf = new SimpleDateFormat("h:mm a");
    
    //PermissionDAO pm = DAOFactory.getInstance().getPermissionDAO();
    LDAPDAO ldapDAO = LDAPDAOFactory.getInstance().getLDAPDAO();
    
    /** Creates a new instance of ReminderDAO */
    public ReminderDAO() {
    }

	public List getRemindersForUser(String user) {
		return getRemindersForUser(user, false);
	}
    
    public List getRemindersForUser(String user, boolean limited) {
        
        String sql = "SELECT \"Reminder\".\"*\"\n";
        sql += "FROM \"Reminder\"\n";
        sql += "WHERE ( (\"Owner\" = " + quoteSingle(user) + ")\n";
		if (!limited) {
			sql += "OR (\"Creator\" = " + quoteSingle(user) + ")\n";
		}

        /*if (!limited && ldapDAO.isUserInGroup(user, "crmsapprove")) {
            sql += "OR (\"Approved\" = false)\n";    
        } */
        sql += ")\n";
        //sql += pm.getPermissionForReadSQL("\"Reminder\".\"ReminderID\"", EntityType.REMINDER, user);
        sql += " AND \"Deleted\" = false\n";
        
        sql += " ORDER BY \"Reminder\".\"ReminderDate\" DESC";
		if (limited) sql += " LIMIT 5";
            
        
        ArrayList result = (ArrayList) executeQuery(sql);
        return result;
    }

   
	/** Return the limited reminders for a user.
	 * @deprecated Please use getRemindersForUser(user, true)
	 */
    public List getLimitedRemindersForUser(String user) {
		return getRemindersForUser(user, true);
    }

    /**
     * <p>Obtain a list of reminders that need to be issued either by SMS
     * or by email, for this minute. This will included reminders with a time
     * less than the current time, to cover possiblities where machine load
     * or other circumstances might stop messages being delivered properly.
     * </p>
     * <p>Only SMS's that have been approved may be sent.</p>
     *
     * @param now Date representing time to search for.
     * @return List of ReminderVO objects.
     */
    
    public List getRemindersForMinute(java.util.Date now) {
        String sql = "SELECT \"Reminder\".\"*\"\n";
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        sql += "FROM \"Reminder\"\n";
        sql += "WHERE \"ReminderNext\" <= '" + df.format(now) + "'\n";
        sql += "AND \"Frequency\" >= 0\n";
        sql += "AND \"Deleted\" = false\n";
        sql += "AND (\"SMSReminder\" = true\n";
        // sql += " AND \"Approved\" = true)\n";
        sql += " OR \"EmailReminder\" = true)\n";
        sql += " ORDER BY \"ReminderRate\"";
        
        ArrayList result = (ArrayList) executeQuery(sql);
        return result;
    }
    
    public Reminder getReminder(int reminderID, String user) {
        
        String sql = "SELECT \"Reminder\".\"*\"\n";
        sql += "FROM \"Reminder\"\n";
        sql += "WHERE \"Reminder\".\"ReminderID\" = " + reminderID + "\n";
        //sql += pm.getPermissionForReadSQL("\"Reminder\".\"ReminderID\"", EntityType.REMINDER, user, true);

		// added to follow the logic in the getRemindersForUser (otherwise a permission denied error occurs)
		// commented out because untested
        /*if (ldapDAO.isUserInGroup(user, "crmsapprove")) {
            sql += "OR (\"Approved\" = false)\n";    
        } */
        
        sql += " ORDER BY \"Reminder\".\"ReminderDate\" DESC";
            
        
        ArrayList result = (ArrayList) executeQuery(sql);
        if (result != null && result.size() > 0) {
            return (Reminder) result.get(0);
        }
        return null;
    }
    
    
    public Reminder createReminder(Reminder newReminder) throws Exception {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            con = getFactory().getConnection();

            // There's three queries that need to happen atomically here,
            // so we'll make it transactional by setting autoCommit to false
            // and then doing a con.commit at the end.

            con.setAutoCommit(false);

            stmt = con.createStatement();

            String sql = "INSERT INTO \"Reminder\"\n";
            sql += "(\"ContactID\", \"Creator\", \"ReminderDate\", \"ReminderNext\", \"Frequency\", \"ReminderMessage\", \"Owner\", \"OwnerHasRead\", \"ReminderRate\",\"EmailReminder\",\"SMSReminder\", \"Deleted\")\n";
            sql += "Values\n";
            sql += "(" + newReminder.getContactID();
            sql += ", " + quoteSingle(newReminder.getCreator().trim());
            sql += ", " + quoteSingle(Reminder.fullFormat.format(newReminder.getReminderDate()));
            sql += ", " + quoteSingle(Reminder.fullFormat.format(newReminder.getReminderNext()));
            sql += ", " + newReminder.getFrequency();
            sql += ", " + quoteSingle(URLEncoder.encode(newReminder.getReminderMessage(), "UTF-8"));
            sql += ", " + quoteSingle(newReminder.getOwner().trim());
            sql += ", " + false;
            sql += ", " + newReminder.getReminderRate();
            sql += ", " + newReminder.isEmailReminder();
            sql += ", " + newReminder.isSMSReminder();
            
            sql += ", false";
            sql += ")\n";

            logger.debug("Executing sql:\n" + sql);

            int rows = stmt.executeUpdate(sql);

            if (rows != 1) {
                con.rollback();
                logger.error("Incorrect number of rows inserted executing:");
                logger.error(sql);
                throw new Exception("Incorrect number of rows inserted!");
            } else {
                logger.debug("1 row inserted okay");
            }

            // Get the ID of the row just inserted

            sql = "select max(\"ReminderID\")\n";
            sql += "  from \"Reminder\"\n";

            logger.debug("Executing sql:\n" + sql);

            rs = stmt.executeQuery(sql);
            int reminderID = -1;

            if (rs.next()) {
                reminderID = rs.getInt(1);
                newReminder.setReminderID(reminderID);
            }

            con.commit();
        }
        catch (Exception ex) {
            logger.error(ex);
            throw new Exception(ex);
        }
        finally {