companydao.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]
	*/
/*
 * CompanyDAO.java
 *
 * Created on 26 March 2003, 00:46
 */

package crms.dao;

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



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

	PermissionDAO pm = DAOFactory.getInstance().getPermissionDAO();
	
	/** Creates a new instance of CompanyDAO */
	
	public CompanyDAO() {
	}
	
	
	public List getCompanyList(String user) {
		
		/*String sql = "SELECT *\n";
		sql += "FROM \"Company\"\n";
		sql += "WHERE \"Deleted\" = false\n";
		sql += pm.getPermissionForReadSQL("\"Company\".\"CompanyID\"", EntityType.COMPANY, user);
		sql += "\nORDER BY lower(\"CompanyName\")\n";*/

		// fill in the incomplete check - tnichols 20031103
		// note this is very ugly and I think that there has to be a better way!
		String sql = "SELECT c.\"CompanyName\", c.\"Notes\", c.\"CompanyID\", c.\"Deleted\", (\n";
		sql += "    ((length(d1.\"ContactValue\") = 0)\n";
		sql += "    AND (length(d2.\"ContactValue\") = 0))\n";
		sql += "    OR c.\"Incomplete\") as \"Incomplete\"\n";
		sql += "FROM \"Company\" as c\n";
		sql += "LEFT JOIN \"DataStore\" AS d1 on c.\"CompanyID\" = d1.\"ContactNumber\" and d1.\"ContactValueType\" = 'address'\n";
		sql += "LEFT JOIN \"DataStore\" AS d2 on c.\"CompanyID\" = d2.\"ContactNumber\" and d2.\"ContactValueType\" = 'phone'\n";
		sql += "WHERE c.\"Deleted\" = false\n";
		//sql += pm.getPermissionForReadSQL("c.\"CompanyID\"", EntityType.COMPANY, user);
		sql += "\nORDER BY lower(\"CompanyName\")\n";

		
		
		ArrayList result = (ArrayList) executeQuery(sql);
		return result;
	}
	
	public boolean companyExists(String companyName) throws Exception {
		String sql = "SELECT 1\n";
		sql += "FROM \"Company\"\n";
		sql += "WHERE lower(\"CompanyName\") = lower(" + quoteSingle(companyName) + ")";

		logger.debug("exists SQL: " + sql);

		return entityExists(sql);
	}

	public boolean companyExists(int companyID) throws Exception {
		String sql = "SELECT 1\n";
		sql += "FROM \"Company\"\n";
		sql += "WHERE \"CompanyID\" = " + companyID;

		return entityExists(sql);
	}
	
	public Company getCompany(String companyName) {
		
		String sql = "SELECT *\n";
		sql += "FROM \"Company\"\n";
		sql += "WHERE lower(\"CompanyName\") = lower(" + quoteSingle(companyName) + ")";
		
		ArrayList result = (ArrayList) executeQuery(sql);

		// check to see if it actually exists
		if (result.size() == 0) return null;

		Company company = (Company) result.get(0);
		getCompanyMetaData(company);
		
		return company;
		
	}
	
	public Company getCompany(int companyID) {
		Company company = null;
		
		String sql = "SELECT *\n";
		sql += "FROM \"Company\"\n";
		sql += "WHERE \"CompanyID\" = " + companyID;
		
		ArrayList result = (ArrayList) executeQuery(sql);
		if (result.size() == 0) {
			company = null;
		} else {
			company = (Company) result.get(0);
			getCompanyMetaData(company);
		}

		return company;
		
	}
	
	public Object createFromResultSet(ResultSet rs) throws SQLException, UnsupportedEncodingException {
		try {
			if (rs.findColumn("CompanyName") > 0) {
	 
				Company company = new Company();
		
				company.setCompanyName(rs.getString("CompanyName").trim());
				company.setCompanyID(rs.getInt("CompanyID"));
				company.setNotes(URLDecoder.decode(rs.getString("Notes"), "UTF-8").trim());
				company.setIncomplete(rs.getBoolean("Incomplete"));
				company.setDeleted(rs.getBoolean("Deleted"));

				// hax0r for industry details
				String type = (String)getMetaData(ContactType.ORGANISATION.getCode(), company.getCompanyID(), ContactValueType.COMP_TYPE.getCode());
				company.setCompanyType((CompanyIdentifiersTypeCode)CompanyIdentifiersTypeCode.decode(type, CompanyIdentifiersTypeCode.class));
		
				return company;
			}
		} catch (SQLException e) {
			// CompanyName is specific to Company, as we handling Location also, do that here
			// perhaps Location should be split into LocationDAO
			Location location = new Location();

			location.setID(rs.getInt("LocationID"));
			location.setCompanyID(rs.getInt("CompanyID"));
			location.setTitle(rs.getString("Title"));
			location.setAddress(rs.getString("Address1"));
			location.setState(rs.getString("State"));
			location.setSuburb(rs.getString("Suburb"));
			location.setCountry(rs.getString("Country"));
			location.setPostCode(rs.getString("Postcode"));
			location.setDeleted(rs.getBoolean("Deleted"));

			return location;
		}

		return null;
	}
	
	public Company insertCompany(Company company) throws Exception {
		
		Connection con = null;
		String sql = "";
		Statement stmt = null;

		try {

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

			sql += "INSERT into \"Company\"\n";
			sql += "(\"CompanyName\",\"Notes\",\"RecordOwner\", \"Deleted\", \"Incomplete\")\n";
			sql += "VALUES\n";
			sql += "(" + quoteSingle(company.getCompanyName().trim()) + ",";
			sql += quoteSingle(encode(company.getNotes().trim())) + ",";
			sql += quoteSingle(company.getOwner().trim())+",";
			sql += "false,";
			sql += company.getIncomplete();
			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(\"CompanyID\")\n";
			sql += "  FROM \"Company\"";
			logger.debug(sql);
			
			stmt = con.createStatement();
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next()) {
				company.setCompanyID(rs.getInt(1));
			} else {
				company.setCompanyID(1);
			}
			rs.close();
			
			updateCompanyMetaData(con, company);
			
			con.commit();

		}
		catch( Exception ex) {
			con.rollback();
			throw new RuntimeException(ex);
		}
		finally {
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();

			}
		}
		return company;
			
	}
	
	public void updateCompany(Company company) throws Exception {
		Connection con = null;
		String sql = "";
		
		try {

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

			sql += "UPDATE \"Company\"\n";
			sql += "   SET \"CompanyName\" = " + quoteSingle(company.getCompanyName()) + ",\n";
			sql += "	   \"Notes\" = " + quoteSingle(encode(company.getNotes())) + ",\n";
			sql += "	   \"Incomplete\" = " + company.getIncomplete() + "\n";
			// lubo: at this stage we should not be updating the owner id
			//sql += "	   \"RecordOwner\" = " + quoteSingle(encode(company.getOwner())) + "\n";
			sql += "WHERE \"CompanyID\" = " + company.getCompanyID();

			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);
			}
			
			updateCompanyMetaData(con, company);
			
			con.commit();
		}
		catch( Exception ex) {
			con.rollback();
			throw new RuntimeException(ex);
		}
		finally {
			if (con != null) {
				con.close();
			}
		}
		
	}
	
	public void deleteCompany(int companyID) {
		String sql = "Update \"Company\"\n";
		sql += "set \"Deleted\" = true\n";
		sql += "Where \"CompanyID\" = " + companyID;

		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 List searchCompany(Company criteria, Location location) {
		
		boolean used = false;
		
		String sql = "SELECT *\n"; //, d1.\"ContactValue\" as \"Industry\"\n";
		sql += "FROM \"Company\"\n";
		//sql += "LEFT JOIN \"DataStore\" as d1 on \"Company\".\"CompanyID\" = d1.\"ContactNumber\" and d1.\"ContactValueType\" = 'comp-type'\n";