Java tutorial
/******************************************************************************* * Copyright (c) 2010 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.analysis; import com.sfs.Formatter; import com.sfs.beans.BuilderBean; import com.sfs.whichdoctor.beans.AgedDebtorsAnalysisBean; import com.sfs.whichdoctor.beans.AgedDebtorsGrouping; import com.sfs.whichdoctor.beans.AgedDebtorsPeriod; import com.sfs.whichdoctor.beans.AgedDebtorsRecord; import com.sfs.whichdoctor.beans.PersonBean; import com.sfs.whichdoctor.beans.OrganisationBean; import com.sfs.whichdoctor.beans.ReceiptBean; import com.sfs.whichdoctor.dao.BaseDAOImpl; import com.sfs.whichdoctor.dao.PersonDAO; import com.sfs.whichdoctor.dao.WhichDoctorDaoException; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.TreeMap; import javax.annotation.Resource; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.RowMapper; /** * The Class AgedDebtorsAnalysisDAOImpl. */ public class AgedDebtorsAnalysisDAOImpl extends BaseDAOImpl implements AgedDebtorsAnalysisDAO { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(AgedDebtorsAnalysisDAOImpl.class); /** The types string array. */ private String[] types = new String[] { "debit", "credit", "receipt", "refund" }; /** The person dao. */ @Resource private PersonDAO personDAO; /** * Perform an aged debtors analysis. * * @param agedDebtorsAnalysis the aged debtors analysis * @return the aged debtors analysis bean * @throws WhichDoctorAnalysisDaoException the which doctor analysis dao exception */ public final AgedDebtorsAnalysisBean analyse(final AgedDebtorsAnalysisBean agedDebtorsAnalysis) throws WhichDoctorAnalysisDaoException { AgedDebtorsAnalysisBean analysis = new AgedDebtorsAnalysisBean(); if (agedDebtorsAnalysis == null) { throw new WhichDoctorAnalysisDaoException( "Valid parameters for the aged debtors analysis are required"); } // Set the periods analysis.setParameters(agedDebtorsAnalysis.getAsAtDate(), agedDebtorsAnalysis.getPeriodLength(), agedDebtorsAnalysis.getNumberOfPeriods(), agedDebtorsAnalysis.getShowZeroBalances()); analysis.setGroupBy(agedDebtorsAnalysis.getGroupBy()); // Set the people and organisations Collection<PersonBean> people = new ArrayList<PersonBean>(); Collection<OrganisationBean> organisations = new ArrayList<OrganisationBean>(); BuilderBean personDetails = new BuilderBean(); personDetails.setParameter("MEMBERSHIP", true); for (Integer guid : agedDebtorsAnalysis.getPeople().keySet()) { PersonBean person = agedDebtorsAnalysis.getPeople().get(guid); // If the group by value is set load the membership details for the person if (StringUtils.isNotBlank(analysis.getGroupBy())) { try { person = this.personDAO.loadGUID(guid, personDetails); } catch (WhichDoctorDaoException wde) { dataLogger.error("Error loading person (" + guid + "): " + wde.getMessage()); } } people.add(person); } for (Integer guid : agedDebtorsAnalysis.getOrganisations().keySet()) { OrganisationBean org = agedDebtorsAnalysis.getOrganisations().get(guid); organisations.add(org); } analysis.setPeople(people); analysis.setOrganisations(organisations); analysis = loadGroupings(analysis); // Calculate the overall balance analysis.setTotal(calculateBalance(analysis.getGroupings())); return analysis; } /** * Load groupings. * * @param analysis the analysis * @return the aged debtors analysis bean */ private AgedDebtorsAnalysisBean loadGroupings(final AgedDebtorsAnalysisBean analysis) { HashMap<Integer, AgedDebtorsRecord> records = buildRecordMap(analysis); HashMap<Integer, ReceiptBean> lastReceipts = loadLastReceipts(analysis); TreeMap<String, AgedDebtorsGrouping> groupings = new TreeMap<String, AgedDebtorsGrouping>(); // If there are no records then there is no need to perform any searches if (records.size() > 0) { // Loop through these for the different periods and in total for (Integer periodId : analysis.getPeriods().keySet()) { AgedDebtorsPeriod period = analysis.getPeriods().get(periodId); for (String type : types) { dataLogger.debug("Performing lookup for " + type); Collection<AgedDebtorsRecord> results = performLookup(analysis, period, type); for (AgedDebtorsRecord result : results) { int guid = result.getPersonGUID(); if (result.getOrganisationGUID() > 0) { guid = result.getOrganisationGUID(); } String groupName = analysis.getGroupName(guid); AgedDebtorsGrouping grouping = new AgedDebtorsGrouping(); grouping.setName(groupName); if (groupings.containsKey(groupName)) { grouping = groupings.get(groupName); } dataLogger.debug("Processing result for GUID: " + guid + ", " + Formatter.toCurrency(result.getOutstandingDebitValue())); AgedDebtorsRecord record = records.get(guid); AgedDebtorsPeriod periodResult = new AgedDebtorsPeriod(period); if (record.getPeriodBreakdown().containsKey(periodId)) { periodResult = record.getPeriodBreakdown().get(periodId); } if (StringUtils.equalsIgnoreCase(type, "debit")) { periodResult.setOutstandingDebitValue(result.getOutstandingDebitValue()); // Update the running total for the record record.setOutstandingDebitValue( record.getOutstandingDebitValue() + result.getOutstandingDebitValue()); // Update the running total for the period period.setOutstandingDebitValue( period.getOutstandingDebitValue() + result.getOutstandingDebitValue()); } if (StringUtils.equalsIgnoreCase(type, "credit")) { periodResult.setUnallocatedCreditValue(result.getOutstandingDebitValue()); // Update the running total for the record record.setUnallocatedCreditValue( record.getUnallocatedCreditValue() + result.getOutstandingDebitValue()); // Update the running total for the period period.setUnallocatedCreditValue( period.getUnallocatedCreditValue() + result.getOutstandingDebitValue()); } if (StringUtils.equalsIgnoreCase(type, "refund")) { periodResult.setUnallocatedRefundValue(result.getOutstandingDebitValue()); // Update the running total for the record record.setUnallocatedRefundValue( record.getUnallocatedRefundValue() + result.getOutstandingDebitValue()); // Update the running total for the period period.setUnallocatedRefundValue( period.getUnallocatedRefundValue() + result.getOutstandingDebitValue()); } if (StringUtils.equalsIgnoreCase(type, "receipt")) { periodResult.setUnallocatedReceiptValue(result.getOutstandingDebitValue()); // Update the running total for the record record.setUnallocatedReceiptValue( record.getUnallocatedReceiptValue() + result.getOutstandingDebitValue()); // Update the running total for the period period.setUnallocatedReceiptValue( period.getUnallocatedReceiptValue() + result.getOutstandingDebitValue()); } // Set the last receipt if one exists for this record if (lastReceipts.containsKey(guid)) { record.setLastReceipt(lastReceipts.get(guid)); } record.getPeriodBreakdown().put(periodId, periodResult); grouping.getRecords().put(analysis.getOrderKey(guid), record); groupings.put(groupName, grouping); } } analysis.getPeriods().put(periodId, period); } } analysis.setGroupings(processGroups(groupings, analysis.getShowZeroBalances())); return analysis; } /** * Calculate the overall balance. * * @param groups the groups * @return the double */ private double calculateBalance(final TreeMap<String, AgedDebtorsGrouping> groups) { double balance = 0; for (String orderKey : groups.keySet()) { AgedDebtorsGrouping group = groups.get(orderKey); balance += group.getTotal(); } return balance; } /** * Load receipt map, where integer = guid of person/organisation. * * @param analysis the analysis * @return the hash map of receipts */ @SuppressWarnings("unchecked") private HashMap<Integer, ReceiptBean> loadLastReceipts(final AgedDebtorsAnalysisBean analysis) { HashMap<Integer, ReceiptBean> lastReceipts = new HashMap<Integer, ReceiptBean>(); StringBuffer sql = new StringBuffer(); sql.append(this.getSQL().getValue("agedDebtors/lastReceipts")); sql.append(" WHERE "); sql.append(buildSqlWhere(analysis, "fs1.PersonId", "fs1.OrganisationId", false)); sql.append(" GROUP BY fs1.PersonId"); if (dataLogger.isDebugEnabled()) { dataLogger.debug("SQL: " + sql.toString()); } Collection<ReceiptBean> receipts = new ArrayList<ReceiptBean>(); try { receipts = this.getJdbcTemplateReader().query(sql.toString(), new Object[] {}, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { ReceiptBean receipt = new ReceiptBean(); receipt.setGUID(rs.getInt("Id")); receipt.setAbbreviation(rs.getString("Abbreviation")); receipt.setNumber(rs.getString("Number")); receipt.setPersonId(rs.getInt("PersonId")); receipt.setOrganisationId(rs.getInt("OrganisationId")); receipt.setNetValue(rs.getDouble("NetValue")); try { receipt.setIssued(rs.getDate("Issued")); } catch (SQLException sqe) { dataLogger.error("Error loading issued date: " + sqe.getMessage()); } return receipt; } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for this search: " + ie.getMessage()); } for (ReceiptBean receipt : receipts) { if (receipt.getPersonId() > 0) { lastReceipts.put(receipt.getPersonId(), receipt); } if (receipt.getOrganisationId() > 0) { lastReceipts.put(receipt.getOrganisationId(), receipt); } } return lastReceipts; } /** * Post process the aged debtors groupings. * * @param groupings the groupings * @param showZeroBalances the show zero balances * @return the tree map */ private TreeMap<String, AgedDebtorsGrouping> processGroups(final TreeMap<String, AgedDebtorsGrouping> groupings, final boolean showZeroBalances) { TreeMap<String, AgedDebtorsGrouping> processedGroupings = new TreeMap<String, AgedDebtorsGrouping>(); for (String groupName : groupings.keySet()) { AgedDebtorsGrouping group = groupings.get(groupName); TreeMap<String, AgedDebtorsRecord> records = new TreeMap<String, AgedDebtorsRecord>(); for (String orderKey : group.getRecords().keySet()) { AgedDebtorsRecord record = group.getRecords().get(orderKey); if (showZeroBalances || record.getTotal() != 0) { records.put(orderKey, record); for (int id : record.getPeriodBreakdown().keySet()) { AgedDebtorsPeriod period = record.getPeriodBreakdown().get(id); AgedDebtorsPeriod groupPeriod = group.getPeriodBreakdown(period); // Update the running totals for the period of the grouping groupPeriod.setOutstandingDebitValue( groupPeriod.getOutstandingDebitValue() + period.getOutstandingDebitValue()); groupPeriod.setUnallocatedRefundValue( groupPeriod.getUnallocatedRefundValue() + period.getUnallocatedRefundValue()); groupPeriod.setUnallocatedCreditValue( groupPeriod.getUnallocatedCreditValue() + period.getUnallocatedCreditValue()); groupPeriod.setUnallocatedReceiptValue( groupPeriod.getUnallocatedReceiptValue() + period.getUnallocatedReceiptValue()); group.getPeriodBreakdown().put(id, groupPeriod); } // Update the running totals for the grouping group.setOutstandingDebitValue( group.getOutstandingDebitValue() + record.getOutstandingDebitValue()); group.setUnallocatedRefundValue( group.getUnallocatedRefundValue() + record.getUnallocatedRefundValue()); group.setUnallocatedCreditValue( group.getUnallocatedCreditValue() + record.getUnallocatedCreditValue()); group.setUnallocatedReceiptValue( group.getUnallocatedReceiptValue() + record.getUnallocatedReceiptValue()); } } group.setRecords(records); processedGroupings.put(groupName, group); } return processedGroupings; } /** * Perform a lookup for a period based on the type of financial transaction. * * @param analysis the analysis * @param period the period * @param type the type * @return the collection */ @SuppressWarnings("unchecked") private Collection<AgedDebtorsRecord> performLookup(final AgedDebtorsAnalysisBean analysis, final AgedDebtorsPeriod period, final String type) { Collection<AgedDebtorsRecord> records = new ArrayList<AgedDebtorsRecord>(); String sqlEntry = "agedDebtors/debit"; String personField = "invoice.PersonId"; String organisationField = "invoice.OrganisationId"; if (StringUtils.equalsIgnoreCase(type, "credit")) { sqlEntry = "agedDebtors/credit"; personField = "credit.PersonId"; organisationField = "credit.OrganisationId"; } if (StringUtils.equalsIgnoreCase(type, "refund")) { sqlEntry = "agedDebtors/refund"; personField = "credit.PersonId"; organisationField = "credit.OrganisationId"; } if (StringUtils.equalsIgnoreCase(type, "receipt")) { sqlEntry = "agedDebtors/receipt"; personField = "payment.PersonId"; organisationField = "payment.OrganisationId"; } StringBuffer sql = new StringBuffer(); sql.append(this.getSQL().getValue(sqlEntry)); sql.append(buildSqlWhere(analysis, personField, organisationField, true)); sql.append(" GROUP BY "); sql.append(personField); sql.append(", "); sql.append(organisationField); if (dataLogger.isDebugEnabled()) { dataLogger.debug("SQL: " + sql.toString()); dataLogger.debug("Start date: " + period.getStartDate()); dataLogger.debug("End date: " + period.getEndDate()); } try { records = this.getJdbcTemplateReader().query(sql.toString(), new Object[] { period.getEndDate(), period.getStartDate() }, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { AgedDebtorsRecord record = new AgedDebtorsRecord(); record.setPersonGUID(rs.getInt("PersonId")); record.setOrganisationGUID(rs.getInt("OrganisationId")); record.setOutstandingDebitValue(rs.getDouble("Value")); return record; } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for this search: " + ie.getMessage()); } return records; } /** * Builds the record map. * * @param analysis the analysis * @return the tree map */ private HashMap<Integer, AgedDebtorsRecord> buildRecordMap(final AgedDebtorsAnalysisBean analysis) { HashMap<Integer, AgedDebtorsRecord> records = new HashMap<Integer, AgedDebtorsRecord>(); for (Integer guid : analysis.getPeople().keySet()) { AgedDebtorsRecord record = new AgedDebtorsRecord(); record.setPerson(analysis.getPeople().get(guid)); records.put(guid, record); } for (Integer guid : analysis.getOrganisations().keySet()) { AgedDebtorsRecord record = new AgedDebtorsRecord(); record.setOrganisation(analysis.getOrganisations().get(guid)); records.put(guid, record); } return records; } /** * Builds the sql where statement. * * @param analysis the analysis * @param peopleField the people field * @param organisationField the organisation field * @param prependAnd the flag to prepend the AND to the SQL where statement * @return the string */ private String buildSqlWhere(final AgedDebtorsAnalysisBean analysis, final String peopleField, final String organisationField, final boolean prependAnd) { StringBuffer sql = new StringBuffer(); for (Integer guid : analysis.getPeople().keySet()) { if (sql.length() > 0) { sql.append(" OR "); } sql.append(peopleField); sql.append(" = "); sql.append(guid); } for (Integer guid : analysis.getOrganisations().keySet()) { if (sql.length() > 0) { sql.append(" OR "); } sql.append(organisationField); sql.append(" = "); sql.append(guid); } if (prependAnd && sql.length() > 0) { sql.insert(0, " AND ("); sql.append(")"); } return sql.toString(); } }