calldao.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]
	*/
/*
 * CallDAO.java
 *
 * Created on 24 March 2003, 04:39
 */

package crms.dao;

import java.sql.*;
import java.util.*;
import java.text.*;
import org.apache.log4j.Logger;
import crms.vo.*;
import crms.util.*;
import crms.module.CallModule;
import java.net.*;
import org.w3c.dom.*;

/**
 *
 * @author  dmurphy
 */
public class CallDAO extends AbstractDAO {

	static Logger logger = Logger.getLogger(CallDAO.class);
	
	public static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	PermissionDAO pm = DAOFactory.getInstance().getPermissionDAO();
	
	/** Creates a new instance of CallDAO */
	public CallDAO() {
	}

	/**
	 * @param subMode
	 * @param data
	 * @return StringBuffer containing resulting XML
	 */	
	public List getCallList(String user, boolean limit) {
		
		String sql = "SELECT *, comp.\"CompanyName\" AS \"CompanyName\" \n";
//		sql += "\"Call\".\"Owner\",\n";
//		sql += "\"Call\".\"Date\",\n";
//		sql += "\"Call\".\"Number\",\n";
//		sql += "\"Call\".\"Note\",\n";
//		sql += "\"Call\".\"Creator\",\n";
//		sql += "\"Call\".\"CallID\"\n";
		
		sql += "FROM \"Call\"\n";
		sql += "LEFT JOIN \"Company\" AS \"comp\" on \"Call\".\"CompanyID\" = comp.\"CompanyID\"\n";
		sql += "WHERE (\"Owner\" = " + quoteSingle(user) + "\n";
		//if (!limit) {
		 //   sql += "   OR \"Creator\" = " + quoteSingle(user);
		//}
		sql += ")\n";
		//sql += pm.getPermissionForReadSQL("\"Call\".\"CallID\"", EntityType.CALL, user );
		sql += "AND \"Call\".\"Deleted\" = false\n";
		sql += "ORDER BY \"Call\".\"Date\" DESC";

		if (limit) {
		 sql += " LIMIT 5";			
		}
		sql += "\n";

		ArrayList callList = (ArrayList) executeQuery(sql);
		
		logger.debug("Call list has " + callList.size() + " calls.");
		return callList;
		
	}

	public List getCallNotifications() {
		String sql = "SELECT \"Call\".*, comp.\"CompanyName\" AS \"CompanyName\"\n";
		sql += "FROM \"Call\"\n";
		sql += "LEFT JOIN \"Company\" AS \"comp\" on \"Call\".\"CompanyID\" = comp.\"CompanyID\"\n";
		sql += "WHERE \"Call\".\"Deleted\" = false\n";
		sql += "AND (\n";
		sql += "   ( (flags & " + Call.CALL_FLAG_EMAIL + ") > 0 AND (flags & " + Call.CALL_FLAG_EMAIL_SENT + ") = 0)\n";
		sql += "   OR ( (flags & " + Call.CALL_FLAG_SMS + ") > 0 AND (flags & " + Call.CALL_FLAG_SMS_SENT + ") = 0)";
		sql += ")\n";

		ArrayList callList = (ArrayList) executeQuery(sql);
		return callList;
	}


	/**
	 * @param subMode
	 * @param data
	 * @return StringBuffer containing resulting XML
	 */	
	public List getCallListForCompany(int companyID, String user,boolean limit) {
		
			String sql = "SELECT *\n";
//			sql += "\"Call\".\"Owner\",\n";
//			sql += "\"Call\".\"Date\",\n";
//			sql += "\"Call\".\"Number\",\n";
//			sql += "\"Call\".\"Note\",\n";
//			sql += "\"Call\".\"Creator\",\n";
//			sql += "\"Call\".\"CallID\"\n";
		
			sql += "FROM \"Call\"\n";
			sql += "WHERE (\"CompanyID\" = " + companyID + "\n";
			sql += ")\n";
			sql += pm.getPermissionForReadSQL("\"Call\".\"CallID\"", EntityType.CALL, user );
			sql += "AND \"Deleted\" = false\n";
			sql += "ORDER BY \"Call\".\"Date\" DESC";

			if (limit) {
			 sql += " LIMIT 5";			
			}
			sql += "\n";

			ArrayList callList = (ArrayList) executeQuery(sql);
		
			logger.debug("Call list has " + callList.size() + " calls.");
			return callList;
		
	}


	public Call createCall(Call newCall) 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 \"Call\"\n";
			sql += "(\"ContactID\", \"Creator\", \"Date\", \"Note\", \"Number\", \"Owner\", \"OwnerHasRead\",\"FirstName\",\"LastName\",\"Deleted\",\"CompanyID\", \"flags\")\n";
			sql += "Values\n";
			sql += "(" + newCall.getContactID();
			sql += ", " + quoteSingle(newCall.getCreator());
			sql += ", " + quoteSingle(df.format(newCall.getDate()));
			sql += ", " + quoteSingle(URLEncoder.encode(newCall.getNote(),"UTF-8"));
			sql += ", " + quoteSingle(newCall.getNumber());
			sql += ", " + quoteSingle(newCall.getOwner());
			sql += ", " + false;
			sql += ", " + quoteSingle(encode(newCall.getFromFirstName()));
			sql += ", " + quoteSingle(encode(newCall.getFromLastName()));
			sql += ", false";
			sql += ", " + newCall.getCompanyID();
			sql += ", " + newCall.getFlags();
			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(\"CallID\")\n";
			sql += "  from \"Call\"\n";

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

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

			if (rs.next()) {
				callID = rs.getInt(1);
				newCall.setCallID(callID);
			}

			con.commit();
		}
		catch (Exception ex) {
			logger.error(ex);
			throw new Exception(ex);
		}
		finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();
			}
		}
		
		return newCall;
	}

	
	
	public Call updateCall(Call call) throws Exception {
		String sql = "UPDATE \"Call\"\n";
		sql += "set \"ContactID\" = " + call.getContactID() + ",\n";
		sql += "\"Creator\" = " + quoteSingle(call.getCreator())+ ",\n";
		sql += "\"Date\" = " + quoteSingle(df.format(call.getDate())) + ",\n";
		sql += "\"Note\" = " + quoteSingle(encode(call.getNote())) + ",\n";
		sql += "\"Number\" = " + quoteSingle(call.getNumber()) + ",\n";
		sql += "\"Owner\" = " + quoteSingle(call.getOwner()) + ", \n";
		sql += "\"OwnerHasRead\" = " + call.getOwnerHasRead() + ",\n";
		sql += "\"FirstName\" = " + quoteSingle(encode(call.getFromFirstName())) + ",\n";
		sql += "\"LastName\" = " + quoteSingle(encode(call.getFromLastName())) + ",\n";
		sql += "\"CompanyID\" = " + call.getCompanyID() + ",\n";
		sql += "\"flags\" = " + call.getFlags() + "\n";
		sql += "WHERE \"CallID\" = " + call.getCallID() + "\n";

		int rows = updateSQL(sql);
		if (rows != 1) {
			logger.error("Incorrect number of rows inserted executing:");
			logger.error(sql);
			throw new Exception("Incorrect number of rows inserted!");
		}
		return call;
	}
	
	public Call getCall(String callID, String user) {
		String sql = "SELECT *\n";
		sql += "FROM \"Call\"\n";
		sql += "WHERE (\"Owner\"=" + quoteSingle(user) + "\n";
		sql += "	OR \"Creator\" = " + quoteSingle(user) + ")\n";
		sql += "  AND \"CallID\" = " + callID + "\n";
		
		ArrayList result = (ArrayList) executeQuery(sql);
		if (result.size() > 0)
			return (Call) result.get(0);
		else
			return null;
	}

	public Object createFromResultSet(ResultSet rs) {
		Call call = new Call();
		try {
			call.setCallID(rs.getInt("CallID"));
			call.setContactID(rs.getInt("ContactID"));
			String owner = rs.getString("Owner");
			if (owner != null) {
				call.setOwner(owner.trim());
			}
			Timestamp ts = rs.getTimestamp("Date");
			java.util.Date theDate = new java.util.Date(ts.getTime());
			call.setDate(theDate);
			call.setNumber(rs.getString("Number").trim());
			call.setNote(URLDecoder.decode(rs.getString("Note"), "UTF-8").trim());
			call.setCreator(rs.getString("Creator").trim());
			boolean hasRead = rs.getBoolean("OwnerHasRead");
			call.setOwnerHasRead(hasRead);
			System.out.println("OwnerHasRead = " + call.getOwnerHasRead());
			call.setFromFirstName(decode(rs.getString("FirstName")));
			call.setFromLastName(decode(rs.getString("LastName")));
			call.setDeleted(rs.getBoolean("Deleted"));
			call.setCompanyID(rs.getInt("CompanyID"));
			call.setFlags(rs.getInt("flags"));

			// special instance
			try {
				String companyName = rs.getString("CompanyName");
				Company company = new Company();
				company.setCompanyID(call.getCompanyID());
				company.setCompanyName(companyName);
				call.setCompany(company);
				call.setCompanyID(rs.getInt("CompanyID"));
			} catch (Exception ex) {
				// do nothing, we don't care
			}
		}
		catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException(ex);
		}
		
		return call;
	}
	
	public List searchCalls(CallSearch search, String user) {
		
		boolean used = false;
		
		String sql = "SELECT *\n";
		sql += "FROM \"Call\"\n";
		
		if (search.getDate() != null) {
			sql += prepend(used);
			sql += "\"Date\" >= " + quoteSingle(df.format(search.getDate())) + "\n";
			used = true;
		}
		if (search.getDateTo() != null) {
			sql += prepend(used);
			sql += "\"Date\" <= " + quoteSingle(df.format(search.getDateTo())) + "\n";
			used = true;
		}
		
		if (search.getFromFirstName() != null && !search.getFromFirstName().equals("")) {
			sql += prepend(used);
			sql += "\"FirstName\" like '" + encode(search.getFromFirstName()) + "%'\n";
			used = true;
		}

		if (search.getFromLastName() != null && !search.getFromLastName().equals("")) {
			sql += prepend(used);
			sql += "\"LastName\" like '" + encode(search.getFromLastName()) + "%'\n";
			used = true;
		}
		
		if (search.getNote() != null && !search.getNote().equals("")) {
			sql += prepend(used);
			sql += "\"Note\" like '%" +	search.getNote() + "%'\n";
			used = true;
		}
		
		if (search.getOwner() != null && !search.getOwner().equals("")) {
			sql += prepend(used);
			sql += "\"Owner\" = " + quoteSingle(search.getOwner()) + "\n";   
			used = true;
		}
		
		if (search.getNumber() != null && !search.getNumber().equals("")) {
			sql += prepend(used);
			sql += "\"Number\" = " + quoteSingle(search.getNumber()) + "\n";   
			used = true;
		}
		
		if (search.getCompanyID() > 0 ) {
			sql += prepend(used);
			sql += "\"CompamyID\" = " + quoteSingle(search.getNumber()) + "\n";   
			used = true;
		}
		if (search.getFlags() > 0) {
			sql += prepend(used); used = true;
			sql += "\"flags\" | " + search.getFlags() + " > 0";
		}
		
		sql += pm.getPermissionForReadSQL("\"Call\".\"CallID\"", EntityType.CALL, user, used );
		sql += "ORDER BY \"Call\".\"Date\" DESC";

		ArrayList callList = (ArrayList) executeQuery(sql);
		
		logger.debug("Call list has " + callList.size() + " calls.");
		return callList;
		
	}
	
	public void deleteCall(int callID) {
		
		String sql = "Update \"Call\"\n";
		sql += "set \"Deleted\" = true\n";
		sql += "Where \"CallID\" = " + callID;

		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);
		}
	}
	
	public String prepend(boolean clauseUsed) {
		if (clauseUsed) {
			return "AND ";   
		} else {
			return "WHERE ";   
		}
	}
}