com.sfs.whichdoctor.dao.AddressVerificationDAOImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.sfs.whichdoctor.dao.AddressVerificationDAOImpl.java

Source

/*******************************************************************************
 * 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;
    }
}