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.beans.BuilderBean; import com.sfs.whichdoctor.beans.FinancialSummaryBean; import com.sfs.whichdoctor.beans.PersonBean; import com.sfs.whichdoctor.beans.OrganisationBean; import com.sfs.whichdoctor.beans.TransactionSummaryBean; import com.sfs.whichdoctor.dao.BaseDAOImpl; import com.sfs.whichdoctor.dao.OrganisationDAO; import com.sfs.whichdoctor.dao.PersonDAO; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.Date; 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 FinancialSummaryAnalysisDAOImpl. */ public class FinancialSummaryAnalysisDAOImpl extends BaseDAOImpl implements FinancialSummaryAnalysisDAO { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(FinancialSummaryAnalysisDAOImpl.class); /** The sql order. */ private final String sqlORDER = " ORDER BY financial_summary.Issued, " + "financial_summary.Number"; /** The sql debit type. */ private final String sqlDebitType = " AND financialtype.Object = 'Debit' " + "AND financial_summary.Cancelled = false"; /** The sql receipt type. */ private final String sqlReceiptType = " AND financialtype.Object = 'Receipt' " + "AND financial_summary.Cancelled = false"; /** The sql credit type. */ private final String sqlCreditType = " AND financialtype.Object = 'Credit' AND " + "financialtype.Class != 'Cash' AND financial_summary.Cancelled = false"; /** The sql reimbursement type. */ private final String sqlReimbursementType = " AND financialtype.Object = 'Credit' " + "AND financialtype.Class = 'Cash' AND financial_summary.Cancelled = false"; /** The simple date format object. */ private final SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); /** The person dao. */ @Resource private PersonDAO personDAO; /** The organisation dao. */ @Resource private OrganisationDAO organisationDAO; /** * Perform a financial summary search using the given parameters. * * @param search the search * * @return the financial summary results * * @throws WhichDoctorAnalysisDaoException the which doctor analysis dao exception */ public final FinancialSummaryBean search(final FinancialSummaryBean search) throws WhichDoctorAnalysisDaoException { return search(search, new BuilderBean()); } /** * Perform a financial summary search using the given parameters. * * @param search the search * @param loadDetails the load details * * @return the financial summary results * * @throws WhichDoctorAnalysisDaoException the which doctor analysis dao exception */ public final FinancialSummaryBean search(final FinancialSummaryBean search, final BuilderBean loadDetails) throws WhichDoctorAnalysisDaoException { Collection<PersonBean> people = new ArrayList<PersonBean>(); Collection<OrganisationBean> organisations = new ArrayList<OrganisationBean>(); for (int guid : search.getPeople().keySet()) { PersonBean person = search.getPeople().get(guid); people.add(person); } for (int guid : search.getOrganisations().keySet()) { OrganisationBean organisation = search.getOrganisations().get(guid); organisations.add(organisation); } /* With PersonBean build correctly formatted SQL WHERE statement */ StringBuffer sqlWHERE = new StringBuffer(); sqlWHERE.append(buildWHERE(people, organisations)); Collection<Object> parameters = new ArrayList<Object>(); if (StringUtils.isNotBlank(search.getSecurity())) { sqlWHERE.append(" AND financialtype.Security = ?"); parameters.add(search.getSecurity()); } /* Initalize the bean to hold the results */ FinancialSummaryBean searchResults = new FinancialSummaryBean(); /* Fill with values from SearchBean */ searchResults.setRequestedPage(search.getRequestedPage()); searchResults.setLimit(search.getLimit()); searchResults.setPeople(people); searchResults.setOrganisations(organisations); searchResults.setOpeningDate(search.getOpeningDate()); searchResults.setClosingDate(search.getClosingDate()); /* If some constraints for the search exist, do search */ if (people.size() > 0 || organisations.size() > 0) { /* Set Opening Balance */ if (searchResults.getOpeningDate() != null) { try { final double balance = getBalance(sqlWHERE.toString(), parameters, searchResults.getOpeningDate(), true); searchResults.setOpeningBalance(balance); } catch (Exception e) { throw new WhichDoctorAnalysisDaoException("Could not get opening balance: " + e.getMessage()); } } else { searchResults.setOpeningBalance(0); } /* Set Closing Balance */ try { final double balance = getBalance(sqlWHERE.toString(), parameters, searchResults.getClosingDate(), false); searchResults.setClosingBalance(balance); } catch (Exception e) { throw new WhichDoctorAnalysisDaoException("Could not get closing balance: " + e.getMessage()); } // Add Opening and Closing dates to the search if they exist if (search.getOpeningDate() != null) { if (search.getClosingDate() == null) { search.setClosingDate(new Date(Calendar.getInstance().getTimeInMillis())); } int larger = search.getOpeningDate().compareTo(search.getClosingDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = df.format(search.getOpeningDate()); String fieldB = df.format(search.getClosingDate()); sqlWHERE.append(" AND financial_summary.Issued BETWEEN ? AND ?"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = df.format(search.getOpeningDate()); String fieldB = df.format(search.getClosingDate()); sqlWHERE.append(" AND financial_summary.Issued BETWEEN ? AND ?"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = df.format(search.getOpeningDate()); sqlWHERE.append(" AND financial_summary.Issued = ?"); parameters.add(field); } } if (!loadDetails.getBoolean("SHOW_CANCELLED")) { sqlWHERE.append(" AND financial_summary.Cancelled = ?"); parameters.add(false); } /* Set range of results required */ StringBuffer sqlLIMIT = new StringBuffer(); if (search.getLimit() != 0) { if (search.getRequestedPage() == 0) { search.setRequestedPage(1); } // Determine first and last record requested final int firstRecord = (search.getRequestedPage() - 1) * search.getLimit(); sqlLIMIT.append(" LIMIT "); sqlLIMIT.append(firstRecord); sqlLIMIT.append(", "); sqlLIMIT.append(search.getLimit()); } if (loadDetails.getBoolean("RESULT_COUNT")) { /* Load the result count for this search */ final String countSQL = getSQL().getValue("financialSummary/count") + sqlWHERE.toString(); dataLogger.info("SQL Query: " + countSQL); try { final int recordCount = this.getJdbcTemplateReader().queryForInt(countSQL, parameters.toArray()); searchResults.setTotalResults(recordCount); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for search: " + ie.getMessage()); } } if (loadDetails.getBoolean("LOAD_DETAILS")) { searchResults = loadTransactions(searchResults, loadDetails, sqlWHERE.toString(), sqlLIMIT.toString(), parameters); } } if (searchResults.getTransactions() == null || searchResults.getTransactions().size() == 0) { searchResults.setBalanceBroughtForward(searchResults.getClosingBalance()); } return searchResults; } /** * Load transactions. * * @param searchResults the search results * @param loadDetails the load details * @param sqlWHERE the sql where * @param sqlLIMIT the sql limit * @param parameters the parameters * * @return the financial summary results bean * * @throws WhichDoctorAnalysisDaoException the which doctor analysis dao exception */ @SuppressWarnings("unchecked") private FinancialSummaryBean loadTransactions(final FinancialSummaryBean searchResults, final BuilderBean loadDetails, final String sqlWHERE, final String sqlLIMIT, final Collection<Object> parameters) throws WhichDoctorAnalysisDaoException { Collection<TransactionSummaryBean> results = new ArrayList<TransactionSummaryBean>(); final String searchSQL = getSQL().getValue("financialSummary/find") + sqlWHERE + this.sqlORDER + sqlLIMIT; dataLogger.info("SQL Query: " + searchSQL); Collection<TransactionSummaryBean> transactions = new ArrayList<TransactionSummaryBean>(); try { transactions = this.getJdbcTemplateReader().query(searchSQL, parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return loadTransaction(rs, loadDetails); } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for search: " + ie.getMessage()); } double runningTotal = 0; boolean findRunningTotal = true; for (TransactionSummaryBean transaction : transactions) { if (findRunningTotal) { // Find running total by performing a balance search and set value. try { final double balance = getRunningTotal(sqlWHERE, parameters, transaction.getIssued(), transaction.getSummaryId()); final double total = searchResults.getOpeningBalance() + balance; searchResults.setBalanceBroughtForward(total); runningTotal = total; } catch (Exception e) { throw new WhichDoctorAnalysisDaoException("Could not get running balance: " + e.getMessage()); } findRunningTotal = false; } // Set running total by adding/subtracting net value from // runningTotal. if (!transaction.getCancelled()) { if (transaction.getDebit() != 0) { runningTotal += transaction.getDebit(); } if (transaction.getCredit() != 0) { runningTotal -= transaction.getCredit(); } } transaction.setTotal(runningTotal); results.add(transaction); } searchResults.setTransactions(results); return searchResults; } /** * Gets the balance. * * @param sqlWHERE the sql where * @param parameters the parameters * @param date the date * @param openingBalance the opening balance * * @return the balance */ private double getBalance(final String sqlWHERE, final Collection<Object> parameters, final Date date, final boolean openingBalance) { if (date == null && openingBalance) { return 0; } Collection<Object> balanceParameters = new ArrayList<Object>(); balanceParameters.addAll(parameters); String sqlDate = ""; if (date != null) { if (openingBalance) { sqlDate = " AND financial_summary.Issued < ?"; String openingDate = df.format(date); balanceParameters.add(openingDate); } else { sqlDate = " AND financial_summary.Issued <= ?"; String closingDate = df.format(date); balanceParameters.add(closingDate); } } double debits = 0; double credits = 0; double reimbursements = 0; double receipts = 0; /* Get sum of debits */ try { dataLogger.debug("SQL WHERE: " + sqlWHERE); debits = this.queryForSubTotal(sqlWHERE, sqlDate, this.sqlDebitType, balanceParameters); } catch (Exception e) { dataLogger.error("Error getting debit sub-total: " + e.getMessage()); } /* Get sum of receipts */ try { receipts = this.queryForSubTotal(sqlWHERE, sqlDate, this.sqlReceiptType, balanceParameters); } catch (Exception e) { dataLogger.error("Error getting receipt sub-total: " + e.getMessage()); } /* Get sum of credits */ try { credits = this.queryForSubTotal(sqlWHERE, sqlDate, this.sqlCreditType, balanceParameters); } catch (Exception e) { dataLogger.error("Error getting credit sub-total: " + e.getMessage()); } /* Get sum of reimbursements */ try { reimbursements = this.queryForSubTotal(sqlWHERE, sqlDate, this.sqlReimbursementType, balanceParameters); } catch (Exception e) { dataLogger.error("Error getting reimbursement sub-total: " + e.getMessage()); } dataLogger.debug("Debits total: " + debits); dataLogger.debug("Reimbursements total: " + reimbursements); dataLogger.debug("Receipts total: " + receipts); dataLogger.debug("Credits total: " + credits); return debits + reimbursements - receipts - credits; } /** * Gets the running total. * * @param sqlWHERE the sql where * @param parameters the parameters * @param date the date * @param summaryId the summary id * * @return the running total */ @SuppressWarnings("unchecked") private double getRunningTotal(final String sqlWHERE, final Collection<Object> parameters, final Date date, final int summaryId) { double runningTotal = 0; runningTotal = getBalance(sqlWHERE, parameters, date, true); // Get a list of all the transactions on the // current day in order to find balance StringBuffer findBalanceSql = new StringBuffer(); findBalanceSql.append(this.getSQL().getValue("financialSummary/find")); findBalanceSql.append(sqlWHERE); findBalanceSql.append(" AND financial_summary.Issued = ?"); findBalanceSql.append(this.sqlORDER); String field = df.format(date); parameters.add(field); dataLogger.info("SQL Query: " + findBalanceSql.toString()); Collection<TransactionSummaryBean> transactions = new ArrayList<TransactionSummaryBean>(); try { transactions = this.getJdbcTemplateReader().query(findBalanceSql.toString(), parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { TransactionSummaryBean transaction = new TransactionSummaryBean(); transaction.setSummaryId(rs.getInt("SummaryId")); transaction.setFinancialType(rs.getString("Class")); transaction.setCategory(rs.getString("Object")); transaction.setValue(rs.getDouble("Value")); transaction.setNetValue(rs.getDouble("NetValue")); transaction.setCancelled(rs.getBoolean("Cancelled")); return transaction; } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for search: " + ie.getMessage()); } for (TransactionSummaryBean transaction : transactions) { if (transaction.getSummaryId() != summaryId) { if (!transaction.getCancelled()) { if (StringUtils.equalsIgnoreCase(transaction.getCategory(), "Debit")) { runningTotal += transaction.getNetValue(); } if (StringUtils.equalsIgnoreCase(transaction.getCategory(), "Receipt")) { runningTotal -= transaction.getNetValue(); } if (StringUtils.equalsIgnoreCase(transaction.getCategory(), "Credit")) { if (StringUtils.equalsIgnoreCase(transaction.getFinancialType(), "Refund")) { runningTotal += transaction.getNetValue(); } else { runningTotal -= transaction.getNetValue(); } } } } else { return runningTotal; } } return runningTotal; } /** * Builds the where. * * @param people the people * @param organisations the organisations * * @return the string */ private String buildWHERE(final Collection<PersonBean> people, final Collection<OrganisationBean> organisations) { StringBuffer sqlWHERE = new StringBuffer(); if (people != null) { for (PersonBean person : people) { if (sqlWHERE.length() > 0) { sqlWHERE.append(" OR "); } sqlWHERE.append("financial_summary.PersonId = "); sqlWHERE.append(person.getGUID()); } } if (organisations != null) { for (OrganisationBean organisation : organisations) { if (sqlWHERE.length() > 0) { sqlWHERE.append(" OR "); } sqlWHERE.append("financial_summary.OrganisationId = "); sqlWHERE.append(organisation.getGUID()); } } if (sqlWHERE.length() > 0) { sqlWHERE.insert(0, " AND ("); sqlWHERE.append(")"); } return sqlWHERE.toString(); } /** * Query for sub total. * * @param sqlWHERE the sql where * @param sqlDate the sql date * @param sqlType the sql type * @param parameters the parameters * * @return the double */ private double queryForSubTotal(final String sqlWHERE, final String sqlDate, final String sqlType, final Collection<Object> parameters) { double subTotal = 0; dataLogger.debug("Query subtotal: " + sqlWHERE); StringBuffer searchSQL = new StringBuffer(); searchSQL.append(this.getSQL().getValue("financialSummary/total")); searchSQL.append(sqlWHERE); searchSQL.append(sqlDate); searchSQL.append(sqlType); searchSQL.append(this.sqlORDER); dataLogger.info("SQL query: " + searchSQL.toString()); try { subTotal = (Double) this.getJdbcTemplateReader().queryForObject(searchSQL.toString(), parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return new Double(rs.getDouble(1)); } }); } catch (IncorrectResultSizeDataAccessException ie) { // No results found for this search dataLogger.debug("No results found for search: " + ie.getMessage()); } return subTotal; } /** * Load transaction. * * @param rs the rs * @param loadDetails the load details * * @return the transaction summary bean * * @throws SQLException the SQL exception */ private TransactionSummaryBean loadTransaction(final ResultSet rs, final BuilderBean loadDetails) throws SQLException { TransactionSummaryBean transaction = new TransactionSummaryBean(); transaction.setSummaryId(rs.getInt("SummaryId")); transaction.setId(rs.getInt("Id")); transaction.setType(rs.getString("Name")); transaction.setFinancialType(rs.getString("Class")); transaction.setSecurity(rs.getString("Security")); transaction.setCategory(rs.getString("Object")); transaction.setAbbreviation(rs.getString("Abbreviation")); transaction.setNumber(rs.getString("Number")); transaction.setDescription(rs.getString("Description")); transaction.setValue(rs.getDouble("Value")); transaction.setNetValue(rs.getDouble("NetValue")); transaction.setCancelled(rs.getBoolean("Cancelled")); transaction.setPersonId(rs.getInt("PersonId")); transaction.setOrganisationId(rs.getInt("OrganisationId")); try { transaction.setIssued(rs.getDate("Issued")); } catch (SQLException sqe) { dataLogger.debug("Error parsing Issued: " + sqe.getMessage()); } if (loadDetails.getBoolean("LOAD_OBJECTS")) { if (transaction.getPersonId() > 0) { try { transaction.setPerson(this.personDAO.loadGUID(transaction.getPersonId())); } catch (Exception e) { dataLogger.error("Error loading person: " + e.getMessage()); } } if (transaction.getOrganisationId() > 0) { try { transaction.setOrganisation(this.organisationDAO.loadGUID(transaction.getOrganisationId())); } catch (Exception e) { dataLogger.error("Error loading organisation: " + e.getMessage()); } } } return transaction; } }