contactdao.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]
	*/
/*
 * ContactDAO.java
 *
 * Created on 26 March 2003, 01:01
 */

package crms.dao;

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

/**
 *
 * @author  dmurphy
 */
public class ContactDAO extends AbstractDAO {
	static Logger logger = Logger.getLogger(ContactDAO.class);
	
	PermissionDAO pm = DAOFactory.getInstance().getPermissionDAO();
	
	/** Creates a new instance of ContactDAO */
	public ContactDAO() {
	}
	
	public List getContactList(String filter, String user) {
		
		// Get int representation of company id passed in
		int companyFilterID = Integer.parseInt(filter);
		
		logger.debug("Filter=[" + companyFilterID + "]");
		
		String sql = "SELECT \"Contacts\".\"FirstName\",\n";
		sql += "\"Contacts\".\"LastName\",\n";
		sql += "\"Contacts\".\"ContactID\",\n";
		sql += "\"Contacts\".\"CompanyID\",\n";
		sql += "\"Contacts\".\"Owner\",\n";
		sql += "\"Contacts\".\"Notes\"\n";
		sql += "FROM \"Contacts\"\n";
		
		sql += "WHERE \"Deleted\" = false\n";
		
		if (companyFilterID == Company.NO_COMPANY.getCompanyID()) {
			// SQL Filter for contacts with No Company ID '0'
			sql += "AND \"Contacts\".\"CompanyID\" = " + companyFilterID + "\n";
		}
		else if (companyFilterID != Company.ALL_COMPANIES.getCompanyID()) {
			// SQL Filter for contacts with specific company ID
			sql += "AND \"Contacts\".\"CompanyID\" = " + companyFilterID + "\n";
		}
		else {
			// They've chosen contacts from all companies, so no SQL filter
		}

		sql += pm.getPermissionForReadSQL("\"Contacts\".\"ContactID\"", EntityType.CONTACT, user, true );

		sql += "\nORDER by lower(\"Contacts\".\"FirstName\"), lower(\"Contacts\".\"LastName\")\n";
			
		ArrayList result = (ArrayList) executeQuery(sql);
		return result;
	}

	
	public boolean contactExists(int companyID) throws Exception {
		String sql = "SELECT 1\n";
		sql += "FROM \"Contact\"\n";
		sql += "WHERE \"ContactID\" = " + companyID;

		return entityExists(sql);
	}
	
	public Contact getContact(int contactID) {
		
		String sql = "SELECT *\n";
		sql += "FROM \"Contacts\"\n";
		sql += "WHERE \"ContactID\" = " + contactID;

		ArrayList result = (ArrayList) executeQuery(sql);
		if (result.size() == 0) {
			return null;
		}
		Contact contact = (Contact) result.get(0);
		
		getContactMetaData(contact);
		logger.debug("Contact work suburb is now: " + contact.getWorkSuburb());
		
		return contact;
		
	}
	
	public Contact insertContact(Contact contact) throws Exception {
		
		Connection con = null;
		String sql = "";
		Statement stmt = null;

		try {

			con = getFactory().getConnection();
			con.setAutoCommit(false);

			sql += "INSERT into \"Contacts\"\n";
			sql += "(\"FirstName\",\"LastName\",\"CompanyID\",\"Notes\",\"Owner\",\"Deleted\")\n";
			sql += "VALUES\n";
			sql += "(" + quoteSingle(encode(contact.getFirstName())) + ",";
			sql += quoteSingle(encode(contact.getLastName())) + ",";
			sql += contact.getCompanyID() + ",";
			sql += quoteSingle(encode(contact.getNotes())) + ",";
			sql += quoteSingle(contact.getOwner()) + ",";
			sql += "false";
			sql += ")\n";

			logger.debug(sql);
			
			int result = updateSQL(sql,con);
//			if ( result != 1) {
//				throw new RuntimeException("Illegal number of rows modified in insert. Should be 1 was " + result);
//			}

			sql = "SELECT max(\"ContactID\")\n";
			sql += "  FROM \"Contacts\"";
			logger.debug(sql);
			
			stmt = con.createStatement();
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next()) {
				contact.setContactID(rs.getInt(1));
			} else {
				contact.setContactID(1);
			}
			rs.close();
			
			updateContactMetaData(con, contact);
			
			con.commit();
		}
		catch( Exception ex) {
			con.rollback();
			throw new RuntimeException(ex);
		}
		finally {
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();
			}
		}
		return contact;
	}
	
	public void updateContact(Contact contact) throws Exception {
		Connection con = null;
		String sql = "";
		
		try {

			con = getFactory().getConnection();
			con.setAutoCommit(false);

			sql += "UPDATE \"Contacts\"\n";
			sql += "   SET \"FirstName\" = " + quoteSingle(encode(contact.getFirstName())) + ",\n";
			sql += "	   \"LastName\" = " + quoteSingle(encode(contact.getLastName())) + ",\n";
			sql += "	   \"Notes\" = " + quoteSingle(encode(contact.getNotes())) + ",\n";
			sql += "	   \"CompanyID\" = " + contact.getCompanyID() + "\n";
			sql += "WHERE \"ContactID\" = " + contact.getContactID();

			logger.debug(sql);
			
			int result = updateSQL(sql,con);
			if ( result != 1) {
				throw new RuntimeException("Illegal number of rows modified in update. Should be 1 was " + result);
			}
			
			updateContactMetaData(con, contact);
			
			con.commit();
		}
		catch( Exception ex) {
			con.rollback();
			throw new RuntimeException(ex);
		}
		finally {
			if (con != null) {
				con.close();
			}
		}
		
	}

	public List searchContacts(Contact criteria, String user, String company) {
		
		StringBuffer sql = new StringBuffer("SELECT *, c.\"CompanyName\"\n");
		sql.append("FROM \"Contacts\"\n");
		sql.append("LEFT JOIN \"Company\" as c on \"Contacts\".\"CompanyID\" = c.\"CompanyID\"\n");
		
		boolean used = false;
		
		if (criteria.getLastName() != null && !criteria.getLastName().equals("")) {
			used = criteria(used, sql);
			sql.append("LOWER(\"LastName\") like LOWER('" + criteria.getLastName() + "%')\n");
		}
		
		if (criteria.getFirstName() != null && !criteria.getFirstName().equals("")) {
			used = criteria(used, sql);
			sql.append("LOWER(\"FirstName\") like LOWER('" + criteria.getFirstName() + "%')\n");
		}
		
		if (company != null && company.length() > 0) {
			used = criteria(used, sql);
			sql.append("\"Contacts\".\"CompanyID\" in (SELECT \"CompanyID\" from \"Company\" where lower(\"CompanyName\") like lower('%" + escape(company) + "%'))\n");
		}

		used = criteria(used, sql);
		sql.append("\"Contacts\".\"Deleted\" = false\n");


		sql.append(pm.getPermissionForReadSQL("\"Contacts\".\"ContactID\"", EntityType.CONTACT, user, used ));

		logger.debug("Executing sql: ");
		logger.debug(sql);
		
		ArrayList contacts = (ArrayList) executeQuery(sql.toString());
		
		return contacts;
	}
	
	public void deleteContact(int contactID) {
		
		String sql = "Update \"Contacts\"\n";
		sql += "set \"Deleted\" = true\n";
		sql += "Where \"ContactID\" = " + contactID;

		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);
		}
	}

	
	private void updateContactMetaData(Connection con, Contact contact) throws Exception {
		String id = String.valueOf(contact.getContactID());
		
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomePhone(), ContactValueType.HOME_PHONE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomeFax(), ContactValueType.HOME_FAX);
		
		
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomeAddress(), ContactValueType.HOME_ADDRESS);
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomeSuburb(), ContactValueType.HOME_SUBURB);
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomeState(), ContactValueType.HOME_STATE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomePostCode(), ContactValueType.HOME_POSTCODE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getHomeCountry(), ContactValueType.HOME_COUNTRY);
		
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkPhone(), ContactValueType.WORK_PHONE);
		//updateMetaData(con, id, ContactType.CONTACT, contact.getWorkOtherPhone(), ContactValueType.WORK_OTHER_PHONE);
		// TODO: use the multiple value thing
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkFax(), ContactValueType.WORK_FAX);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkMobile(), ContactValueType.WORK_MOBILE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkAddress(), ContactValueType.WORK_ADDRESS);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkSuburb(), ContactValueType.WORK_SUBURB);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkState(), ContactValueType.WORK_STATE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkPostCode(), ContactValueType.WORK_POSTCODE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWorkCountry(), ContactValueType.WORK_COUNTRY);
		
		updateMetaData(con, id, ContactType.CONTACT, contact.getGender(), ContactValueType.GENDER);
		
		updateMetaData(con, id, ContactType.CONTACT, contact.getEmail(), ContactValueType.CONT_EMAIL);
		updateMetaData(con, id, ContactType.CONTACT, contact.getWWW(), ContactValueType.CONT_WWW);
		updateMetaData(con, id, ContactType.CONTACT, contact.getContactIdentifier(), ContactValueType.ROLE);

		updateMetaData(con, id, ContactType.CONTACT, String.valueOf(contact.getLocationID()), ContactValueType.LOCATION);
		updateMetaData(con, id, ContactType.CONTACT, contact.getPosition(), ContactValueType.POSITION);
		updateMetaData(con, id, ContactType.CONTACT, contact.getTitle(), ContactValueType.TITLE);
		updateMetaData(con, id, ContactType.CONTACT, contact.getBirthDate(), ContactValueType.BIRTHDATE);
		
		/*if (contact.getBirthDate() != null) {
			String date = Contact.df.format(contact.getBirthDate());
			updateMetaData(con, id, ContactType.CONTACT, date, ContactValueType.BIRTHDATE);
		} else {
			updateMetaData(con, id, ContactType.CONTACT, (String) null, ContactValueType.BIRTHDATE);
		}*/
	}
	
	
	public void getContactMetaData(Contact contact) {
		HashMap metaData = getMetaData(ContactType.CONTACT.getCode(), contact.getContactID());
		logger.debug("MetaData hashmap is of size: " + metaData.keySet().size());
		Iterator it = metaData.keySet().iterator();
		while (it.hasNext()) {
			String key = (String) it.next();
			logger.debug("Entry: " + key + " = " + (String) metaData.get(key));
		}
	
		String locstr = (String) metaData.get(ContactValueType.LOCATION.getCode());	
		int locint = -1;
		try {
			locint = Integer.parseInt(locstr);
		} catch (Exception e) {}
		contact.setLocationID(locint);
		contact.setHomePhone((String) metaData.get(ContactValueType.HOME_PHONE.getCode()));
		contact.setHomeFax((String) metaData.get(ContactValueType.HOME_FAX.getCode()));
		contact.setHomeAddress((String) metaData.get(ContactValueType.HOME_ADDRESS.getCode()));
		contact.setHomeSuburb((String) metaData.get(ContactValueType.HOME_SUBURB.getCode()));
		contact.setHomeCountry((String) metaData.get(ContactValueType.HOME_COUNTRY.getCode()));
		contact.setHomeState((String)metaData.get(ContactValueType.HOME_STATE.getCode()));
		contact.setHomePostCode((String) metaData.get(ContactValueType.HOME_POSTCODE.getCode()));
		
		contact.setWorkPhone((String) metaData.get(ContactValueType.WORK_PHONE.getCode()));
		// TODO: use multiple values! contact.setWorkOtherPhone((String) metaData.get(ContactValueType.WORK_OTHER_PHONE.getCode()));
		contact.setWorkFax((String) metaData.get(ContactValueType.WORK_FAX.getCode()));
		contact.setWorkMobile((String) metaData.get(ContactValueType.WORK_MOBILE.getCode()));
		contact.setWorkAddress((String) metaData.get(ContactValueType.WORK_ADDRESS.getCode()));
		contact.setWorkSuburb((String) metaData.get(ContactValueType.WORK_SUBURB.getCode()));
		contact.setWorkState((String) metaData.get(ContactValueType.WORK_STATE.getCode()));
		contact.setWorkPostCode((String) metaData.get(ContactValueType.WORK_POSTCODE.getCode()));
		contact.setWorkCountry((String) metaData.get(ContactValueType.WORK_COUNTRY.getCode()));
		
		String code = (String)metaData.get(ContactValueType.GENDER.getCode());
		contact.setGender((GenderCode) GenderCode.decode(code, GenderCode.class));
		
		String role =(String)metaData.get(ContactValueType.ROLE.getCode());
		contact.setContactIdentifier((ContactIdentifiresTypeCode)ContactIdentifiresTypeCode.decode(role, ContactIdentifiresTypeCode.class));
 
		contact.setEmail((String) metaData.get(ContactValueType.CONT_EMAIL.getCode()));
		contact.setWWW((String) metaData.get(ContactValueType.CONT_WWW.getCode()));
	 
		contact.setPosition((String) metaData.get(ContactValueType.POSITION.getCode()));
		contact.setTitle((String) metaData.get(ContactValueType.TITLE.getCode()));
	 
		contact.setBirthDate((String) metaData.get(ContactValueType.BIRTHDATE.getCode()));
		/*String date = (String) metaData.get(ContactValueType.BIRTHDATE.getCode());
		if (date != null) {
			try {
				contact.setBirthDate(Contact.df.parse(date));
			}
			catch (ParseException ex) {
						 			logger.debug("getContactMetaData Exception - " + ex.getMessage());
				throw new RuntimeException(ex);
			}
		}*/
	}
	
	public Object createFromResultSet(ResultSet rs) throws SQLException, UnsupportedEncodingException {
//			sql += "(\"FirstName\",\"LastName\",\"CompanyID\",\"Notes\",\"Owner\")\n";
		Contact contact = new Contact();
		contact.setFirstName(decode(rs.getString("FirstName")));
		contact.setLastName(decode(rs.getString("LastName")));
		contact.setCompanyID(rs.getInt("CompanyID"));

		try {
			String compname = rs.getString("CompanyName");
			contact.setCompanyName(compname);
		} catch (Exception e) {
			// do nothing
		}
		contact.setContactID(rs.getInt("ContactID"));
		contact.setNotes(URLDecoder.decode(rs.getString("Notes"), "UTF-8"));
		contact.setOwner(rs.getString("Owner"));
		
		return contact;
	}	
	
	
}