Java tutorial
/******************************************************************************* * Copyright (c) 2009 David Harrison. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Public License v3.0 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/gpl-3.0.html * * Contributors: * David Harrison - initial API and implementation ******************************************************************************/ package com.sfs.whichdoctor.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.TreeMap; import javax.annotation.Resource; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.WordUtils; import org.apache.log4j.Logger; import org.springframework.dao.DataAccessException; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.RowMapper; import com.sfs.beans.ObjectTypeBean; import com.sfs.beans.PrivilegesBean; import com.sfs.beans.UserBean; import com.sfs.dao.PrivilegesDAO; import com.sfs.dao.SFSDaoException; import com.sfs.whichdoctor.beans.AddressBean; import com.sfs.whichdoctor.beans.AddressVerificationBean; import com.sfs.whichdoctor.beans.OrganisationBean; import com.sfs.whichdoctor.beans.PersonBean; /** * The Class AddressVerificationDAOImpl. */ public class AddressVerificationDAOImpl extends BaseDAOImpl implements AddressVerificationDAO { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(AddressVerificationDAOImpl.class); /** The address dao. */ @Resource private AddressDAO addressDAO; /** The person dao. */ @Resource private PersonDAO personDAO; /** The privileges dao. */ @Resource private PrivilegesDAO privilegesDAO; /** The organisation dao. */ @Resource private OrganisationDAO organisationDAO; /** The Constant PROCESSED. */ private final static String PENDING = "Pending"; /** The Constant PROCESSED. */ private final static String PROCESSED = "Processed"; /** The Constant PROCESSING. */ private final static String PROCESSING = "Processing"; /** The Constant ERROR_PROCESSING. */ private final static String PROCESS_ERROR = "Error processing"; /** * Load the address verification bean based on the address guid. * * @param guid the guid * @return the address verification bean * @throws WhichDoctorDaoException the which doctor dao exception */ @SuppressWarnings("unchecked") public final AddressVerificationBean load(final int addressVerificationId) throws WhichDoctorDaoException { AddressVerificationBean addressVerification = null; String loadGUID = this.getSQL().getValue("addressVerification/loadId"); try { addressVerification = (AddressVerificationBean) this.getJdbcTemplateReader().queryForObject(loadGUID, new Object[] { addressVerificationId }, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadAddressVerification(rs); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug("Could not find an address verification record for " + " the AddressVerificationId " + addressVerificationId + ": " + ie.getMessage()); } return addressVerification; } /** * Load the address verification beans associated with the supplied address guid. * * @param guid the guid * @return the address verification bean * @throws WhichDoctorDaoException the which doctor dao exception */ @SuppressWarnings("unchecked") public final Collection<AddressVerificationBean> loadGUID(final int guid) throws WhichDoctorDaoException { Collection<AddressVerificationBean> addressVerifications = new ArrayList<AddressVerificationBean>(); String loadGUID = this.getSQL().getValue("addressVerification/loadGUID"); try { addressVerifications = this.getJdbcTemplateReader().query(loadGUID, new Object[] { guid }, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadAddressVerification(rs); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug( "Could not find an address verification record for GUID (" + guid + "): " + ie.getMessage()); } return addressVerifications; } /** * Load a collection of address verification beans that are pending verification * which are associated with the supplied address guid. * * @param guid the guid * @return the address verification beans * @throws WhichDoctorDaoException the which doctor dao exception */ @SuppressWarnings("unchecked") public final Collection<AddressVerificationBean> loadPendingForGUID(final int guid) throws WhichDoctorDaoException { Collection<AddressVerificationBean> addressVerifications = new ArrayList<AddressVerificationBean>(); String loadGUID = this.getSQL().getValue("addressVerification/loadGUID") + " AND processstatus.Class = ?"; try { addressVerifications = this.getJdbcTemplateReader().query(loadGUID, new Object[] { guid, PENDING }, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadAddressVerification(rs); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug( "Could not find an address verification record for GUID (" + guid + "): " + ie.getMessage()); } return addressVerifications; } /** * Load a collection of address verification beans that are capable of being * processed by WhichDoctor and are pending processing. * * @param count the requested number * @return the pending address verification beans * @throws WhichDoctorDaoException the which doctor dao exception */ @SuppressWarnings("unchecked") public final Collection<AddressVerificationBean> loadPending(final int count) throws WhichDoctorDaoException { Collection<AddressVerificationBean> addressVerifications = new ArrayList<AddressVerificationBean>(); Collection<Object> parameters = new ArrayList<Object>(); parameters.add(PENDING); StringBuffer sqlWHERE = new StringBuffer(); Collection<String> codes = this.getProcessableReturnCodes(); for (String code : codes) { if (sqlWHERE.length() > 0) { sqlWHERE.append(" OR "); } sqlWHERE.append("address_verification.ReturnCode LIKE ?"); parameters.add(code + "%"); } if (sqlWHERE.length() > 0) { sqlWHERE.insert(0, " AND ("); sqlWHERE.append(")"); } parameters.add(count); sqlWHERE.append(" LIMIT ?"); String loadPending = this.getSQL().getValue("addressVerification/loadPending") + sqlWHERE.toString(); try { addressVerifications = this.getJdbcTemplateReader().query(loadPending, parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadAddressVerification(rs); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug("Could not find any pending addresses: " + ie.getMessage()); } // Update the status of these pending records for (AddressVerificationBean av : addressVerifications) { // Update the process status to 'Processing' for these records updateProcessStatus(av.getAddressVerificationId(), PROCESSING, ""); } return addressVerifications; } /** * Reconcile the list of pending address verification beans. * * @param addressVerifications the address verifications * @throws WhichDoctorDaoException the which doctor dao exception */ public void reconcilePending(Collection<AddressVerificationBean> addressVerifications) throws WhichDoctorDaoException { if (addressVerifications != null) { for (AddressVerificationBean av : addressVerifications) { AddressVerificationBean vr = this.load(av.getAddressVerificationId()); if (StringUtils.equalsIgnoreCase(vr.getProcessStatus(), PROCESSING)) { // Change back to pending updateProcessStatus(vr.getAddressVerificationId(), PENDING, ""); } } } } /** * Process the address verification. * * @param addressVerification the address verification * @return true, if successful * @throws WhichDoctorDaoException the which doctor dao exception */ public boolean process(final AddressVerificationBean av) throws WhichDoctorDaoException { boolean success = false; if (av.getOutputAddress() == null) { throw new WhichDoctorDaoException("No valid output address exists"); } if (StringUtils.isBlank(av.getReturnCode())) { throw new WhichDoctorDaoException("This address verification record is yet" + "to be verified"); } UserBean systemUser = getSystemUser("Address", "Verification"); PrivilegesBean privileges = new PrivilegesBean(); try { privileges = this.privilegesDAO.load(); } catch (SFSDaoException sfe) { dataLogger.error("Error loading privileges: " + sfe.getMessage()); throw new WhichDoctorDaoException("Error loading privileges: " + sfe.getMessage()); } Collection<String> processableReturnCodes = getProcessableReturnCodes(); boolean canProcess = false; for (String code : processableReturnCodes) { if (StringUtils.startsWithIgnoreCase(av.getReturnCode(), code)) { canProcess = true; } } if (canProcess) { // Load the current address AddressBean address = null; try { address = this.addressDAO.loadGUID(av.getAddressGUID()); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading related address: " + wde.getMessage()); } if (address != null) { if (address.compare(av.getOutputAddress(), false)) { // The address is different - update the address and status AddressBean updated = mergeAddress(address, av.getOutputAddress()); if (StringUtils.isBlank(updated.getState())) { // Check to see if a state is available updated.setState(addressDAO.getState(updated.getCity(), updated.getCountry())); } updated.setVerificationStatus("Verified address"); try { updated.setLogMessage("Automatic address verification update"); int updateId = addressDAO.modify(updated, systemUser, privileges); if (updateId > 0) { // Address successfully updated updateProcessStatus(av.getAddressVerificationId(), PROCESSED, ""); } else { // The address was not updated dataLogger.error("The address could not be updated"); updateProcessStatus(av.getAddressVerificationId(), PROCESS_ERROR, "The address could not be updated"); } } catch (Exception e) { // Error updating the address dataLogger.error("Error updating verified address: " + e.getMessage(), e); updateProcessStatus(av.getAddressVerificationId(), PROCESS_ERROR, "Error updating verified address: " + e.getMessage()); } } else { // The address is not different - update the status updateProcessStatus(av.getAddressVerificationId(), PROCESSED, "The verified address was the same" + " as the current address"); } } else { // The address does not exist record the error and update the status updateProcessStatus(av.getAddressVerificationId(), PROCESS_ERROR, "The address does not exist"); } } return success; } /** * Creates the address verification bean. * * @param address the address * @return the int * @throws WhichDoctorDaoException the which doctor dao exception */ public final int create(final AddressBean address) throws WhichDoctorDaoException { if (address == null) { throw new WhichDoctorDaoException("The address cannot be null"); } if (address.getGUID() < 1) { throw new WhichDoctorDaoException("The address requires a valid GUID"); } if (StringUtils.isBlank(address.getCountry())) { throw new WhichDoctorDaoException("A valid country is required"); } if (StringUtils.isBlank(address.getAddressField(0))) { throw new WhichDoctorDaoException("An address requires at least one line"); } // The message for any unprocessed records String processedMessage = "Address verification request superceeded by " + "a WhichDoctor change"; // Load the parent person or organisation int personIdentifier = 0; String personName = ""; String organisationName = ""; boolean parentFound = false; try { PersonBean person = this.personDAO.loadGUID(address.getReferenceGUID()); if (person != null) { personIdentifier = person.getPersonIdentifier(); personName = person.getPreferredName() + " " + person.getLastName(); parentFound = true; } } catch (WhichDoctorDaoException wde) { dataLogger.info("No person found for the address: " + wde.getMessage()); } if (!parentFound) { try { OrganisationBean org = this.organisationDAO.loadGUID(address.getReferenceGUID()); if (org != null) { organisationName = org.getName(); parentFound = true; } } catch (WhichDoctorDaoException wde) { dataLogger.info("No organisation found for the address: " + wde.getMessage()); } } if (!parentFound) { throw new WhichDoctorDaoException( "No valid person or organisation is " + "associated with this address"); } /* Identify the pending and processed ProcessStatusId */ int pendingProcessStatusId = 0; int processedProcessStatusId = 0; try { ObjectTypeBean object = this.getObjectTypeDAO().load("Address Verification Process Status", "", PENDING); pendingProcessStatusId = object.getObjectTypeId(); } catch (SFSDaoException sfe) { dataLogger.error("Error loading address verification process status: " + sfe.getMessage()); throw new WhichDoctorDaoException("A valid address verification process status is required"); } try { ObjectTypeBean object = this.getObjectTypeDAO().load("Address Verification Process Status", "", PROCESSED); processedProcessStatusId = object.getObjectTypeId(); } catch (SFSDaoException sfe) { dataLogger.error("Error loading address verification process status: " + sfe.getMessage()); throw new WhichDoctorDaoException("A valid address verification process status is required"); } int addressVerificationId = 0; int createCount = 0; // Create the new record Timestamp sqlTimeStamp = new Timestamp(Calendar.getInstance().getTimeInMillis()); TreeMap<Integer, String> addressMap = new TreeMap<Integer, String>(); addressMap.put(0, address.getAddressField(0)); addressMap.put(1, address.getAddressField(1)); addressMap.put(2, address.getAddressField(2)); addressMap.put(3, address.getAddressField(3)); addressMap.put(4, address.getAddressField(4)); addressMap.put(5, address.getAddressField(5)); // Remove the suburb and city values from the map addressMap.put(address.getAddressFieldCount() - 1, ""); addressMap.put(address.getAddressFieldCount() - 2, ""); try { createCount = this.getJdbcTemplateWriter().update(this.getSQL().getValue("addressVerification/create"), new Object[] { address.getGUID(), address.getReferenceGUID(), personIdentifier, personName, organisationName, pendingProcessStatusId, sqlTimeStamp, addressMap.get(0), addressMap.get(1), addressMap.get(2), addressMap.get(3), addressMap.get(4), addressMap.get(5), address.getSuburb(), address.getCity(), address.getStateAbbreviation(), address.getCountry(), address.getPostCode() }); } catch (DataAccessException de) { dataLogger.error("Error creating address verification record: " + de.getMessage()); throw new WhichDoctorDaoException( "Error creating address verification " + "record: " + de.getMessage()); } if (createCount > 0) { // Find the maximum address verification id addressVerificationId = this.getJdbcTemplateReader() .queryForInt(this.getSQL().getValue("addressVerification/findMax")); // Set the processed flag to true for any pending address // verification records for this address guid this.getJdbcTemplateWriter().update(this.getSQL().getValue("addressVerification/updateProcess"), new Object[] { processedProcessStatusId, processedMessage, address.getGUID(), addressVerificationId, pendingProcessStatusId }); // Update the address verification status this.addressDAO.updateVerificationStatus("Pending verification", address.getGUID()); } return addressVerificationId; } /** * Deletes the address verification bean. * * @param addressVerification the address verification * @return the boolean * @throws WhichDoctorDaoException the which doctor dao exception */ public final boolean delete(final AddressVerificationBean addressVerification) throws WhichDoctorDaoException { if (addressVerification == null) { throw new WhichDoctorDaoException("The address verification cannot be null"); } if (addressVerification.getAddressVerificationId() < 1) { throw new WhichDoctorDaoException("A valid ID is required"); } boolean success = false; int deleteCount = 0; try { deleteCount = this.getJdbcTemplateWriter().update(this.getSQL().getValue("addressVerification/delete"), new Object[] { addressVerification.getAddressVerificationId() }); } catch (DataAccessException de) { dataLogger.error("Error deleting address verification record: " + de.getMessage()); throw new WhichDoctorDaoException( "Error deleting address verification " + "record: " + de.getMessage()); } if (deleteCount > 0) { success = true; // Check the verification status of the address compared to // what is waiting to be verified try { AddressBean address = this.addressDAO.loadGUID(addressVerification.getAddressGUID()); Collection<AddressVerificationBean> pending = loadPendingForGUID( addressVerification.getAddressGUID()); if (StringUtils.startsWithIgnoreCase(address.getVerificationStatus(), PENDING)) { if (pending.size() == 0) { // There are no pending records, set status to unverified this.addressDAO.updateVerificationStatus("Unverified address", addressVerification.getAddressGUID()); } } } catch (WhichDoctorDaoException wde) { dataLogger.error("Error checking current verification of the address: " + wde.getMessage()); } } return success; } /** * Gets the return codes that can be processed. * * @return the processable return codes */ public final Collection<String> getProcessableReturnCodes() { Collection<String> codes = new ArrayList<String>(); Collection<ObjectTypeBean> returnCodes = new ArrayList<ObjectTypeBean>(); try { returnCodes = this.getObjectTypeDAO().load("QAS Return Code"); } catch (SFSDaoException sfde) { dataLogger.error("Error loading QAS return codes: " + sfde.getMessage()); } for (ObjectTypeBean returnCode : returnCodes) { if (StringUtils.equalsIgnoreCase(returnCode.getSecurity(), "Yes")) { codes.add(returnCode.getClassName()); } } return codes; } /** * Update the process status. * * @param addressVerificationId the address verification id * @param status the status * @param message the message */ private void updateProcessStatus(final int addressVerificationId, final String status, final String message) { // Load the process status int processStatusId = 0; try { ObjectTypeBean objectType = this.getObjectTypeDAO().load("Address Verification Process Status", "", status); if (objectType != null) { processStatusId = objectType.getObjectTypeId(); } } catch (SFSDaoException sfe) { dataLogger.error("Error loading process status: " + sfe.getMessage()); } try { this.getJdbcTemplateWriter().update(this.getSQL().getValue("addressVerification/processStatus"), new Object[] { processStatusId, message, addressVerificationId }); } catch (DataAccessException de) { dataLogger.error("Error updating the address verification status: " + de.getMessage()); } } /** * Load the address verification bean. * * @param rs the result set * @return the address verification bean * @throws SQLException the sQL exception */ private AddressVerificationBean loadAddressVerification(final ResultSet rs) throws SQLException { AddressVerificationBean addressVerification = new AddressVerificationBean(); addressVerification.setAddressVerificationId(rs.getInt("AddressVerificationId")); addressVerification.setAddressGUID(rs.getInt("GUID")); addressVerification.setReferenceGUID(rs.getInt("ReferenceGUID")); addressVerification.setPersonIdentifier(rs.getInt("PersonIdentifier")); addressVerification.setPersonName(rs.getString("PersonName")); addressVerification.setOrganisationName(rs.getString("OrganisationName")); addressVerification.setProcessStatus(rs.getString("ProcessStatus")); addressVerification.setProcessingException(rs.getString("ProcessingException")); addressVerification.setReturnCode(rs.getString("ReturnCode")); addressVerification.setReturnCodeExtension(rs.getString("ReturnCodeExtension")); try { addressVerification.setCreatedDate(rs.getTimestamp("Created")); } catch (SQLException sqe) { dataLogger.debug("Error parsing CreatedDate: " + sqe.getMessage()); } AddressBean inAddress = new AddressBean(); inAddress.setGUID(rs.getInt("GUID")); inAddress.setActive(true); inAddress.setAddressField(rs.getString("IN_Address1")); inAddress.setAddressField(rs.getString("IN_Address2")); inAddress.setAddressField(rs.getString("IN_Address3")); inAddress.setAddressField(rs.getString("IN_Address4")); inAddress.setAddressField(rs.getString("IN_Address5")); inAddress.setAddressField(rs.getString("IN_Address6")); inAddress.setAddressField(rs.getString("IN_Suburb")); inAddress.setAddressField(rs.getString("IN_City")); String inState = rs.getString("IN_State"); inAddress.setState(addressDAO.getStateFromAbbreviation(inState)); inAddress.setStateAbbreviation(inState); String inCountry = rs.getString("IN_Country"); inAddress.setCountry(addressDAO.getCountryFromAbbreviation(inCountry)); inAddress.setCountryAbbreviation(inCountry); inAddress.setPostCode(rs.getString("IN_Postcode")); addressVerification.setInputAddress(inAddress); AddressBean outAddress = new AddressBean(); outAddress.setGUID(rs.getInt("GUID")); outAddress.setActive(true); outAddress.setAddressField(capitalise(rs.getString("OUT_Address1"))); outAddress.setAddressField(capitalise(rs.getString("OUT_Address2"))); outAddress.setAddressField(capitalise(rs.getString("OUT_Address3"))); outAddress.setAddressField(capitalise(rs.getString("OUT_Address4"))); outAddress.setAddressField(capitalise(rs.getString("OUT_Address5"))); outAddress.setAddressField(capitalise(rs.getString("OUT_Address6"))); outAddress.setAddressField(capitalise(rs.getString("OUT_Suburb"))); outAddress.setAddressField(capitalise(rs.getString("OUT_City"))); String outState = rs.getString("OUT_State"); outAddress.setState(addressDAO.getStateFromAbbreviation(outState)); outAddress.setStateAbbreviation(outState); String outCountry = rs.getString("OUT_Country"); outAddress.setCountry(addressDAO.getCountryFromAbbreviation(outCountry)); outAddress.setCountryAbbreviation(outCountry); outAddress.setPostCode(rs.getString("OUT_Postcode")); addressVerification.setOutputAddress(outAddress); return addressVerification; } /** * Merge the verified address with the current address. * * @param existing the existing address * @param updated the updated (verified) address * @return the address bean */ private AddressBean mergeAddress(final AddressBean existing, final AddressBean updated) { // Reset the existing address fields existing.reset(); for (int i = 0; i < updated.getAddressFieldCount(); i++) { existing.setAddressField(updated.getAddressField(i)); } existing.setState(updated.getState()); existing.setCountry(updated.getCountry()); existing.setPostCode(updated.getPostCode()); return existing; } /** * Capitalise the supplied field if all caps. * * @param s the string * @return the string */ private static String capitalise(final String s) { String value = s; if (StringUtils.isNotBlank(s) && isAllUpper(s)) { value = WordUtils.capitalizeFully(s); } return value; } /** * Checks if the string is all upper case. * * @param s the string * @return true, if is all upper */ private static boolean isAllUpper(String s) { for (char c : s.toCharArray()) { if (Character.isLetter(c) && Character.isLowerCase(c)) { return false; } } return true; } }