companydao.java

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

		
		if (criteria != null) {
			
			if (criteria.getCompanyName() != null && !criteria.getCompanyName().trim().equals("")) {
				sql += "WHERE ";
				sql += "LOWER(\"CompanyName\") like LOWER('%" + escape(criteria.getCompanyName()) + "%')";
				used = true;
			}
			
			if (location.getState() != null && location.getState().trim().length() > 0) {
				if (used) {
					sql += "\nAND ";
				} else {
					sql += "WHERE ";
				}
				sql += "EXISTS (select 1";
				sql += "\n		FROM \"Location\"";
				sql += "\n		WHERE \"CompanyID\"=\"Company\".\"CompanyID\"";
				sql += "\n		  AND LOWER(\"State\") = LOWER(" + quoteSingle(location.getState()) + ")";
				sql += ")";
				used=true;
			}

			if (location.getPostCode() != null && !location.getPostCode().trim().equals("")) {
				if (used) {
					sql += "\nAND ";
				} else {
					sql += "WHERE ";
				}
				sql += "EXISTS (select 1";
				sql += "\n		FROM \"Location\"";
				sql += "\n		WHERE \"CompanyID\"= \"Company\".\"CompanyID\"";
				sql += "\n		  AND LOWER(\"Postcode\") = LOWER(" + quoteSingle(location.getPostCode()) + ")";
				sql += ")";
				
				used=true;
			}

			if (criteria.getCompanyType() != null) {
				if (used) {
					sql += "\nAND ";
				} else {
					sql += "WHERE ";
				}
				sql += "EXISTS (select 1";
				sql += "\n		FROM \"DataStore\"";
				sql += "\n		WHERE \"ContactType\"= 'org'";
				sql += "\n		AND \"ContactNumber\"= \"Company\".\"CompanyID\"";
				sql += "\n		AND \"ContactValueType\"= 'comp-type'";
				sql += "\n		AND \"ContactValue\" = " + quoteSingle(criteria.getCompanyType().getCode());
				sql += ")";
				
				used=true;
			}

			if (location.getSuburb() != null && !location.getSuburb().trim().equals("")) {
				if (used) {
					sql += "\nAND ";
				} else {
					sql += "WHERE ";
				}
					
				sql += "EXISTS (select 1";
				sql += "\n		FROM \"Location\"";
				sql += "\n		WHERE \"CompanyID\"=\"Company\".\"CompanyID\"";
				sql += "\n		  AND LOWER(\"Suburb\") = LOWER(" + quoteSingle(location.getSuburb()) + ")";
				sql += ")";
				
				used=true;
			}
			
			
		}
		if (used) {
			sql += "\nAND ";
		} else {
			sql += "WHERE ";
		}
		sql += "\"Deleted\" = false\n";
		used=true;

		sql += "ORDER BY \"CompanyName\"";

		logger.debug(sql);
		
		ArrayList results = (ArrayList) executeQuery(sql);
		return results;
		
	}


	
	
	private void updateCompanyMetaData(Connection con, Company company) throws Exception {
		String id = String.valueOf(company.getCompanyID());
		
		updateMetaData(con, id, ContactType.ORGANISATION, company.getPhone(), ContactValueType.PHONE);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getFax(), ContactValueType.FAX);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getAddress(), ContactValueType.ADDRESS);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getSuburb(), ContactValueType.SUBURB);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getState(), ContactValueType.STATE);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getCountry(), ContactValueType.COUNTRY);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getPostcode(), ContactValueType.POSTCODE);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getCompanySize(), ContactValueType.COMP_SIZE);
		updateMetaData(con, id, ContactType.ORGANISATION, company.getCompanyType(), ContactValueType.COMP_TYPE);
		updateMetaData(con, id, ContactType.ORGANISATION.getCode(), company.getABN(), ContactValueType.ABN.getCode());
		updateMetaData(con, id, ContactType.ORGANISATION.getCode(), company.getACN(), ContactValueType.ACN.getCode());
		updateMetaData(con, id, ContactType.ORGANISATION.getCode(), company.getURL(), ContactValueType.CONT_WWW.getCode());
		updateMetaData(con, id, ContactType.ORGANISATION.getCode(), company.getEmail(), ContactValueType.CONT_EMAIL.getCode());

		// store the company locations
		updateLocations(company, company.getLocations());
	}
	
	public void getCompanyMetaData(Company company) {
		
		HashMap metaData = getMetaData(ContactType.ORGANISATION.getCode(), company.getCompanyID());
		
		company.setPhone((String)metaData.get(ContactValueType.PHONE.getCode()));
		company.setFax((String)metaData.get(ContactValueType.FAX.getCode()));
		company.setAddress((String)metaData.get(ContactValueType.ADDRESS.getCode()));
		company.setSuburb((String)metaData.get(ContactValueType.SUBURB.getCode()));
		company.setState((String)metaData.get(ContactValueType.STATE.getCode()));
		company.setCountry((String)metaData.get(ContactValueType.COUNTRY.getCode()));
		company.setPostcode((String)metaData.get(ContactValueType.POSTCODE.getCode()));		

		company.setABN(getMetaDataList(ContactType.ORGANISATION.getCode(), String.valueOf(company.getCompanyID()), ContactValueType.ABN.getCode()));
		company.setACN(getMetaDataList(ContactType.ORGANISATION.getCode(), String.valueOf(company.getCompanyID()), ContactValueType.ACN.getCode()));
		company.setURL(getMetaDataList(ContactType.ORGANISATION.getCode(), String.valueOf(company.getCompanyID()), ContactValueType.CONT_WWW.getCode()));
		company.setEmail(getMetaDataList(ContactType.ORGANISATION.getCode(), String.valueOf(company.getCompanyID()), ContactValueType.CONT_EMAIL.getCode()));

		String type = (String)metaData.get(ContactValueType.COMP_TYPE.getCode());
		company.setCompanyType((CompanyIdentifiersTypeCode)CompanyIdentifiersTypeCode.decode(type, CompanyIdentifiersTypeCode.class));

		String size = (String)metaData.get(ContactValueType.COMP_SIZE.getCode());
		company.setCompanySize((CompanySizeCode)CompanySizeCode.decode(size, CompanySizeCode.class));

		company.setLocations(getLocations(company));
	}

	/*public boolean locationExists(Location location) {
		String sql = "SELECT 1\n";
		sql += "FROM \"Location\"\n";
		sql += "WHERE lower(\"CompanyName\") = lower(" + quoteSingle(companyName) + ")";

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

		return entityExists(sql);
	}*/

	public ArrayList getLocations(Company company) {
		String sql = "SELECT *\n";
		sql += "FROM \"Location\"\n";
		sql += "WHERE \"Deleted\" = false\n";
		sql += "AND \"CompanyID\" = " + company.getCompanyID() + "\n";
		sql += "\nORDER BY lower(\"Title\")\n";

		ArrayList result = (ArrayList) executeQuery(sql);

		Iterator i = result.iterator();
		while (i.hasNext()) {
			Location location = (Location)i.next();
			location.setPhone(getMetaDataList(ContactType.LOCATION.getCode(), Integer.toString(location.getID()), "phone"));
			location.setFax(getMetaDataList(ContactType.LOCATION.getCode(), Integer.toString(location.getID()), "fax"));
			location.setEmail(getMetaDataList(ContactType.LOCATION.getCode(), Integer.toString(location.getID()), "email"));
			location.setURL(getMetaDataList(ContactType.LOCATION.getCode(), Integer.toString(location.getID()), "url"));
		}
		return result;
	}
	

	/** Update the location list in the database for a particular company.
	 * updating locations is slightly tricky as we must first ensure that the list of locations
	 * is in the database, and remove and locations that have been deleted
	 */
	public void updateLocations(Company company, ArrayList locations) throws Exception {
		Connection con = null;
		String sql = null;
		Statement stmt = null;

		try {

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

			// start off by inserting/updating the locations list
			Location location = null;

			Iterator i = null;
			if (locations != null) i = locations.iterator();

			while (locations != null && i.hasNext()) {
				location = (Location)i.next();

				// existing locations have valid ID
				if (location.getID() > 0) {
					sql = "UPDATE \"Location\"\n";
					sql += "SET \"CompanyID\" = " + company.getCompanyID() + ",\n";
					sql += "\"Title\" = " + quoteSingle(location.getTitle()) + ",\n";
					sql += "\"State\" = " + quoteSingle(location.getState()) + ",\n";
					sql += "\"Address1\" = " + quoteSingle(location.getAddress()) + ",\n";
					sql += "\"Suburb\" = " + quoteSingle(location.getSuburb()) + ",\n";
					sql += "\"Country\" = " + quoteSingle(location.getCountry()) + ",\n";
					sql += "\"Postcode\" = " + quoteSingle(location.getPostCode());
					sql += "WHERE \"LocationID\" = " + location.getID();

					int result = updateSQL(sql, con);
					if (result != 1) {
						throw new RuntimeException("Failed to update location - number of changed rows: " + result);
					}
				} else {
					sql = "INSERT into \"Location\"\n";
					sql += "(\"CompanyID\",\"Title\",\"Address1\", \"State\", \"Suburb\", \"Country\", \"Postcode\", \"Deleted\")\n";
					sql += "VALUES\n";
					sql += "(" + company.getCompanyID() + ",";
					sql += quoteSingle(location.getTitle().trim()) + ",";
					sql += quoteSingle(location.getAddress().trim()) + ",";
					sql += quoteSingle(location.getState().trim())+",";
					sql += quoteSingle(location.getSuburb().trim())+",";
					sql += quoteSingle(location.getCountry().trim())+",";
					sql += quoteSingle(location.getPostCode().trim())+",";
					sql += "false";
					sql += ")\n";

					int result = updateSQL(sql, con);
					if (result != 1) {
						throw new RuntimeException("Failed to insert location");
					}

					sql = "SELECT max(\"LocationID\") from \"Location\"";
					logger.debug(sql);
			
					stmt = con.createStatement();
					
					ResultSet rs = stmt.executeQuery(sql);
			
					if (rs.next()) {
						location.setID(rs.getInt(1));
					} else {
						location.setID(1);
					}
		
				} // end if (insert/update location)

				updateMetaData(con, Integer.toString(location.getID()), ContactType.LOCATION.getCode(), location.getPhone(), "phone");
				updateMetaData(con, Integer.toString(location.getID()), ContactType.LOCATION.getCode(), location.getFax(), "fax");
				updateMetaData(con, Integer.toString(location.getID()), ContactType.LOCATION.getCode(), location.getEmail(), "email");
				updateMetaData(con, Integer.toString(location.getID()), ContactType.LOCATION.getCode(), location.getURL(), "url");

			} // end for

			// get the list of locations that exist and remove those not found
			List db_locations = getLocations(company);
			Location compare = null;

			Iterator l = (db_locations != null) ?db_locations.iterator() : null;
			boolean flag;
			while (l != null && l.hasNext()) {
				flag = false; 
				compare = (Location)l.next();

				Iterator x = locations.iterator();
				while (x != null && x.hasNext()) {
					Location loc = (Location)x.next();
					if (compare.getID() == loc.getID()) {
						flag = true;
						// object not found
					}
				}
				if (flag == false) {
					deleteLocation(compare);
				}
			}

			con.commit();

		} catch( Exception ex) {
			if (con != null) {
				con.rollback();
			}
			throw new RuntimeException(ex);
		} finally {
			if (con != null) {
				con.close();
			}
		} // end try
	}
					  
	public void deleteLocation(Location location) {
		String sql = "Update \"Location\"\n";
		sql += "set \"Deleted\" = true\n";
		sql += "Where \"LocationID\" = " + location.getID();

		logger.debug("Delete location: " + sql);

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