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.analysis; import com.sfs.whichdoctor.beans.DebitBean; import com.sfs.whichdoctor.beans.OrganisationBean; import com.sfs.whichdoctor.beans.PaymentBean; import com.sfs.whichdoctor.beans.PersonBean; import com.sfs.whichdoctor.beans.ReceiptBean; import com.sfs.whichdoctor.beans.RevenueAnalysisBean; import com.sfs.whichdoctor.beans.RevenueBean; import com.sfs.whichdoctor.dao.BaseDAOImpl; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.TreeMap; import org.apache.log4j.Logger; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.RowMapper; /** * The Class RevenueAnalysisDAOImpl. */ public class RevenueAnalysisDAOImpl extends BaseDAOImpl implements RevenueAnalysisDAO { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(RevenueAnalysisDAOImpl.class); /** * Batch analysis. * * @param search the search * * @return the revenue analysis bean * * @throws WhichDoctorAnalysisDaoException the which doctor analysis dao * exception */ @SuppressWarnings("unchecked") public final RevenueAnalysisBean batchAnalysis(final RevenueAnalysisBean search) throws WhichDoctorAnalysisDaoException { /* Zero out values in revenue analysis bean */ search.setValue(0); search.setNetValue(0); /* Set ordering system of returned results */ String sqlORDER = " ORDER BY receipt.BatchReference, receipt.ReceiptNo"; StringBuffer sqlWHERE = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (search.getSQLWhereStatement() != null) { if (search.getSQLWhereStatement().compareTo("") != 0) { sqlWHERE.append(" AND "); sqlWHERE.append(search.getSQLWhereStatement()); } } if (search.getSearchParameters() != null) { parameters = search.getSearchParameters(); } /* BUILD SQL Statement */ final StringBuffer searchSQL = new StringBuffer(); searchSQL.append(this.getSQL().getValue("revenue")); searchSQL.append(sqlWHERE.toString()); searchSQL.append(" GROUP BY payment.PaymentId, receipt.BatchReference "); searchSQL.append(sqlORDER); dataLogger.info("SQL Query: " + searchSQL.toString()); Collection<RevenueBean> results = new ArrayList<RevenueBean>(); try { results = this.getJdbcTemplateReader().query(searchSQL.toString(), parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadBatchRevenue(rs); } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for this search: " + ie.getMessage()); } final TreeMap<Object, ArrayList<RevenueBean>> batchMap = new TreeMap<Object, ArrayList<RevenueBean>>(); for (RevenueBean revenue : results) { ArrayList<RevenueBean> revenueList = new ArrayList<RevenueBean>(); if (batchMap.containsKey(revenue.getBatchReference())) { revenueList = batchMap.get(revenue.getBatchReference()); } revenueList.add(revenue); batchMap.put(revenue.getBatchReference(), revenueList); } final RevenueAnalysisBean summary = consolidateSummary(batchMap); search.setValue(summary.getValue()); search.setNetValue(summary.getNetValue()); search.setGSTValues(summary.getGSTValues()); search.setRevenue(summary.getRevenue()); return search; } /** * Stream analysis. * * @param search the search * * @return the revenue analysis bean * * @throws WhichDoctorAnalysisDaoException the which doctor analysis dao * exception */ @SuppressWarnings("unchecked") public final RevenueAnalysisBean streamAnalysis(final RevenueAnalysisBean search) throws WhichDoctorAnalysisDaoException { /* Zero out values in revenueanalysis bean */ search.setValue(0); search.setNetValue(0); /* Set ordering system of returned results */ String sqlORDER = " ORDER BY RevenueType, receipt.ReceiptNo"; final StringBuffer sqlWHERE = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (search.getSQLWhereStatement() != null) { if (search.getSQLWhereStatement().compareTo("") != 0) { sqlWHERE.append(" AND "); sqlWHERE.append(search.getSQLWhereStatement()); } } if (search.getSearchParameters() != null) { parameters = search.getSearchParameters(); } // BUILD SQL Statement final StringBuffer searchSQL = new StringBuffer(); searchSQL.append(this.getSQL().getValue("revenue")); searchSQL.append(sqlWHERE.toString()); searchSQL.append(" GROUP BY payment.PaymentId, RevenueType "); searchSQL.append(sqlORDER); dataLogger.info("SQL Query: " + searchSQL.toString()); Collection<RevenueBean> results = new ArrayList<RevenueBean>(); try { results = this.getJdbcTemplateReader().query(searchSQL.toString(), parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadStreamRevenue(rs); } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for search: " + ie.getMessage()); } TreeMap<Object, ArrayList<RevenueBean>> revenueTypeMap = new TreeMap<Object, ArrayList<RevenueBean>>(); for (RevenueBean revenue : results) { if (dataLogger.isDebugEnabled()) { dataLogger.debug("Net value: " + revenue.getNetValue()); dataLogger.debug("Value: " + revenue.getValue()); } ArrayList<RevenueBean> revenueList = new ArrayList<RevenueBean>(); if (revenueTypeMap.containsKey(revenue.getRevenueType())) { revenueList = revenueTypeMap.get(revenue.getRevenueType()); } revenueList.add(revenue); revenueTypeMap.put(revenue.getRevenueType(), revenueList); } final RevenueAnalysisBean summary = consolidateSummary(revenueTypeMap); search.setValue(summary.getValue()); search.setNetValue(summary.getNetValue()); search.setGSTValues(summary.getGSTValues()); search.setRevenue(summary.getRevenue()); return search; } /** * Consolidate summary. * * @param revenueMap the revenue map * * @return the tree map< revenue bean, collection< revenue bean>> */ private RevenueAnalysisBean consolidateSummary(final TreeMap<Object, ArrayList<RevenueBean>> revenueMap) { final RevenueAnalysisBean result = new RevenueAnalysisBean(); final Collection<RevenueBean> summary = new ArrayList<RevenueBean>(); for (Object key : revenueMap.keySet()) { RevenueBean summaryRevenue = new RevenueBean(); for (RevenueBean revenue : revenueMap.get(key)) { summaryRevenue.setBatchReference(revenue.getBatchReference()); summaryRevenue.setBatchNo(revenue.getBatchNo()); summaryRevenue.setRevenueType(revenue.getRevenueType()); summaryRevenue.setRevenueClass(revenue.getRevenueClass()); final double summaryValue = summaryRevenue.getValue(); final double summaryNetValue = summaryRevenue.getNetValue(); /* Update the summary revenue totals for this batch */ summaryRevenue.setValue(summaryValue + revenue.getValue()); summaryRevenue.setNetValue(summaryNetValue + revenue.getNetValue()); for (Double gstRate : revenue.getGSTValues().keySet()) { final double gstValue = revenue.getGSTValues().get(gstRate); double gstSubtotal = 0; if (summaryRevenue.getGSTValues().containsKey(gstRate)) { gstSubtotal = summaryRevenue.getGSTValues().get(gstRate); } summaryRevenue.setGSTValue(gstRate, gstSubtotal + gstValue); } /* Add receipts/payments to this revenue batch */ TreeMap<Integer, ReceiptBean> receipts = summaryRevenue.getReceipts(); if (receipts == null) { receipts = new TreeMap<Integer, ReceiptBean>(); } for (Integer receiptId : revenue.getReceipts().keySet()) { ReceiptBean receipt = revenue.getReceipts().get(receiptId); if (receipts.containsKey(receiptId)) { ReceiptBean summaryReceipt = receipts.get(receiptId); ArrayList<PaymentBean> payments = (ArrayList<PaymentBean>) summaryReceipt.getPayments(); if (payments == null) { payments = new ArrayList<PaymentBean>(); } if (receipt.getPayments() != null) { for (PaymentBean payment : receipt.getPayments()) { payments.add(payment); } } summaryReceipt.setPayments(payments); receipts.put(receiptId, summaryReceipt); } else { receipts.put(receiptId, receipt); } } } if (dataLogger.isDebugEnabled()) { dataLogger.debug("Summary value: " + summaryRevenue.getValue()); dataLogger.debug("Summary net value: " + summaryRevenue.getNetValue()); } summary.add(summaryRevenue); } // Calculate the totals for the revenue analysis for (RevenueBean summaryRevenue : summary) { /* Update the overall running revenue totals */ result.setValue(result.getValue() + summaryRevenue.getValue()); result.setNetValue(result.getNetValue() + summaryRevenue.getNetValue()); /* Update the GST totals */ for (double gstRate : summaryRevenue.getGSTValues().keySet()) { final double gstValue = summaryRevenue.getGSTValues().get(gstRate); double currentGSTValue = 0; if (result.getGSTValues().containsKey(gstRate)) { currentGSTValue = result.getGSTValues().get(gstRate); } result.setGSTValue(gstRate, currentGSTValue + gstValue); } } // Ensure the revenue beans have the same GST fields result.setRevenue(processGSTRates(summary)); if (dataLogger.isDebugEnabled()) { dataLogger.debug("Total calculated value: " + result.getValue()); dataLogger.debug("Total calculated net value: " + result.getNetValue()); } return result; } /** * Load batch revenue. * * @param rs the rs * @return the revenue bean * @throws SQLException the sQL exception */ private RevenueBean loadBatchRevenue(final ResultSet rs) throws SQLException { final RevenueBean revenue = new RevenueBean(); revenue.setBatchReference(rs.getInt("BatchReference")); revenue.setBatchNo(rs.getString("BatchNo")); if (revenue.getBatchNo() == null) { revenue.setBatchNo(rs.getString("BatchReference")); } if (revenue.getBatchNo().compareTo("") == 0) { revenue.setBatchNo(rs.getString("BatchReference")); } revenue.setValue(rs.getDouble("RevenueValue")); revenue.setNetValue(rs.getDouble("RevenueNetValue")); final double gstRate = rs.getDouble("InvoiceGSTRate"); revenue.setGSTValue(gstRate, revenue.getGSTValue()); /** Temporary placeholder for Receipt details */ ReceiptBean receipt = loadReceipt(rs); PaymentBean payment = new PaymentBean(); payment.setIncomeStream(rs.getString("RevenueType")); payment.setValue(rs.getDouble("RevenueValue")); payment.setNetValue(rs.getDouble("RevenueNetValue")); /* Load invoice details */ if (rs.getInt("InvoiceGUID") > 0) { payment.setDebit(loadDebit(rs)); } Collection<PaymentBean> payments = new ArrayList<PaymentBean>(); payments.add(payment); receipt.setPayments(payments); TreeMap<Integer, ReceiptBean> receipts = new TreeMap<Integer, ReceiptBean>(); receipts.put(new Integer(receipt.getId()), receipt); revenue.setReceipts(receipts); return revenue; } /** * Load stream revenue. * * @param rs the rs * @return the revenue bean * @throws SQLException the sQL exception */ private RevenueBean loadStreamRevenue(final ResultSet rs) throws SQLException { final RevenueBean revenue = new RevenueBean(); revenue.setRevenueType(rs.getString("RevenueType")); revenue.setRevenueClass(rs.getString("RevenueClass")); revenue.setValue(rs.getDouble("RevenueValue")); revenue.setNetValue(rs.getDouble("RevenueNetValue")); final double gstRate = rs.getDouble("InvoiceGSTRate"); revenue.setGSTValue(gstRate, revenue.getGSTValue()); /** Temporary placeholder for Receipt details */ final ReceiptBean receipt = loadReceipt(rs); PaymentBean payment = new PaymentBean(); payment.setIncomeStream(rs.getString("RevenueType")); payment.setValue(rs.getDouble("RevenueValue")); payment.setNetValue(rs.getDouble("RevenueNetValue")); /* Load invoice details */ if (rs.getInt("InvoiceGUID") > 0) { payment.setDebit(loadDebit(rs)); } final Collection<PaymentBean> payments = new ArrayList<PaymentBean>(); payments.add(payment); receipt.setPayments(payments); TreeMap<Integer, ReceiptBean> receipts = new TreeMap<Integer, ReceiptBean>(); receipts.put(new Integer(receipt.getId()), receipt); revenue.setReceipts(receipts); return revenue; } /** * Load debit. * * @param rs the rs * * @return the debit bean */ private DebitBean loadDebit(final ResultSet rs) { DebitBean debit = null; try { debit = new DebitBean(); debit.setGUID(rs.getInt("InvoiceGUID")); debit.setNumber(rs.getString("InvoiceNo")); debit.setAbbreviation(rs.getString("InvoiceAbbreviation")); debit.setDescription(rs.getString("InvoiceDescription")); try { debit.setIssued(rs.getDate("InvoiceIssued")); } catch (SQLException e) { debit.setIssued(null); } if (rs.getInt("InvoicePersonGUID") > 0) { // Member exists so load details PersonBean person = new PersonBean(); person.setGUID(rs.getInt("InvoicePersonGUID")); person.setPersonIdentifier(rs.getInt("InvoicePersonIdentifier")); person.setPreferredName(rs.getString("InvoicePreferredName")); person.setLastName(rs.getString("InvoiceLastName")); debit.setPerson(person); } if (rs.getInt("InvoiceOrganisationGUID") > 0) { // Organisation exists so load details OrganisationBean organisation = new OrganisationBean(); organisation.setGUID(rs.getInt("InvoiceOrganisationGUID")); organisation.setName(rs.getString("InvoiceOrganisation")); debit.setOrganisation(organisation); } debit.setValue(rs.getDouble("InvoiceValue")); debit.setNetValue(rs.getDouble("InvoiceNetValue")); debit.setGSTRate(rs.getDouble("InvoiceGSTRate")); debit.setCancelled(rs.getBoolean("InvoiceCancelled")); } catch (SQLException sqe) { dataLogger.error("Error loading debit details: " + sqe.getMessage()); } return debit; } /** * Load receipt. * * @param rs the rs * * @return the receipt bean */ private ReceiptBean loadReceipt(final ResultSet rs) { ReceiptBean receipt = null; try { receipt = new ReceiptBean(); // receipt not already loaded so enter details... receipt.setId(rs.getInt("ReceiptId")); receipt.setAbbreviation(rs.getString("ReceiptAbbreviation")); receipt.setNumber(rs.getString("ReceiptNo")); receipt.setBatchReference(rs.getInt("BatchReference")); receipt.setProcessAbbreviation(rs.getString("ReceiptProcessType")); receipt.setDescription(rs.getString("ReceiptDescription")); try { receipt.setIssued(rs.getDate("ReceiptIssued")); } catch (SQLException e) { receipt.setIssued(null); } receipt.setTypeName(rs.getString("ReceiptType")); receipt.setClassName(rs.getString("ReceiptClass")); // Add member/organisation if it exists.... if (rs.getInt("ReceiptPersonGUID") > 0) { receipt.setPerson(loadPerson(rs)); } if (rs.getInt("ReceiptOrganisationGUID") > 0) { receipt.setOrganisation(loadOrganisation(rs)); } } catch (SQLException sqe) { dataLogger.error("Error loading receipt details: " + sqe.getMessage()); } return receipt; } /** * Load person. * * @param rs the rs * * @return the person bean */ private PersonBean loadPerson(final ResultSet rs) { PersonBean person = null; try { // Person exists so load details person = new PersonBean(); person.setGUID(rs.getInt("ReceiptPersonGUID")); person.setPersonIdentifier(rs.getInt("ReceiptPersonIdentifier")); person.setPreferredName(rs.getString("ReceiptPreferredName")); person.setLastName(rs.getString("ReceiptLastName")); } catch (SQLException sqe) { dataLogger.error("Error loading person details: " + sqe.getMessage()); } return person; } /** * Load organisation. * * @param rs the rs * * @return the organisation bean */ private OrganisationBean loadOrganisation(final ResultSet rs) { OrganisationBean organisation = null; try { // Organisation exists so load details organisation = new OrganisationBean(); organisation.setGUID(rs.getInt("ReceiptOrganisationGUID")); organisation.setName(rs.getString("ReceiptOrganisation")); } catch (Exception e) { dataLogger.error("Error loading organisation details: " + e.getMessage()); } return organisation; } /** * Process the gst rates for a summary. * * @param summary the summary * @return the collection */ private final Collection<RevenueBean> processGSTRates(final Collection<RevenueBean> summary) { final Collection<RevenueBean> results = new ArrayList<RevenueBean>(); final TreeMap<Double, Double> gstRates = new TreeMap<Double, Double>(); // Construct a map with all of the GST rates that are used for (RevenueBean revenue : summary) { for (Double gstRate : revenue.getGSTValues().keySet()) { gstRates.put(gstRate, 0.0); } } // Build a new results collection with the correct number of GST rates for (RevenueBean revenue : summary) { for (Double gstRate : gstRates.keySet()) { // If the GST rate is not part of the revenue analysis add it with 0 value if (!revenue.getGSTValues().containsKey(gstRate)) { revenue.setGSTValue(gstRate, 0.0); } } results.add(revenue); } return results; } }