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.search.sql; import com.sfs.DataFilter; import com.sfs.beans.BuilderBean; import com.sfs.beans.UserBean; import com.sfs.whichdoctor.beans.DebitBean; import com.sfs.whichdoctor.beans.PaymentBean; import com.sfs.whichdoctor.beans.ReceiptBean; import com.sfs.whichdoctor.beans.SearchBean; import com.sfs.whichdoctor.beans.TagBean; import com.sfs.whichdoctor.dao.ReceiptDAO; import com.sfs.whichdoctor.search.TagSearchDAO; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Map; import javax.annotation.Resource; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; /** * The Class ReceiptSqlHandler. * * @author David Harrison */ public class ReceiptSqlHandler extends SqlHandlerBase { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(ReceiptSqlHandler.class); /** The Constant LIMIT. */ private static final int LIMIT = 50; /** The tag search dao. */ @Resource private TagSearchDAO tagSearchDAO; /** The receipt dao. */ @Resource private ReceiptDAO receiptDAO; /** * Instantiates a new receipt sql handler. */ public ReceiptSqlHandler() { super(); this.setType("receipt"); this.setIdentifierColumn("receipt.GUID"); this.setDefaultOrder("ReceiptNo"); } /** * Initiate a SearchBean for the search type. * * @param user the user * * @return configured SearchBean */ public final SearchBean initiate(final UserBean user) { SearchBean search = new SearchBean(); ReceiptBean searchCriteria = new ReceiptBean(); ReceiptBean searchConstraints = new ReceiptBean(); searchCriteria.setCancelled(false); searchConstraints.setCancelled(true); /** * If user is not an Administrator or in the Finances group then they * shouldn't be able to view secured receipts. **/ if (user != null && !user.isFinancialUser()) { searchCriteria.setSecurity("Standard"); } search.setRequestedPage(1); search.setLimit(LIMIT); search.setOrderColumn("receipt.ReceiptNo"); search.setOrderColumn2(""); search.setOrderColumn3(""); search.setOrderAscending(true); search.setType("receipt"); search.setSearchCriteria(searchCriteria); search.setSearchConstraints(searchConstraints); return search; } /** * Gets the group by. * * @return the group by */ public final String getGroupBy() { return " GROUP BY receipt.ReceiptId "; } /** * Gets the count sql. * * @return the count sql */ public final String getCountSql() { return "SELECT count(DISTINCT receipt.GUID) " + this.getSQL().getValue("receipt/search"); } /** * Gets the select sql. * * @return the select sql */ public final String getSelectSql() { return "SELECT DISTINCT receipt.GUID, sum(payment.Value) AS TotalValue " + getSQL().getValue("receipt/search"); } /** * Load the identified objects and return results as a Collection of * Objects. * * @param uniqueIds the unique ids * @param loadDetails the load details * * @return a Collection of Objects */ public final Collection<Object> load(final Collection<Integer> uniqueIds, final BuilderBean loadDetails) { Collection<Object> results = new ArrayList<Object>(); if (uniqueIds != null) { for (Integer uniqueId : uniqueIds) { try { ReceiptBean receipt = this.receiptDAO.loadGUID(uniqueId, loadDetails); results.add(receipt); } catch (Exception e) { dataLogger.error("Error loading receipt (" + uniqueId + ") for search: " + e.getMessage()); } } } return results; } /** * Construct the SQL string, description and parameters. * * @param objCriteria Object containing search criteria values * @param objConstraints Object containing search constraint values * * @return Map containing a String[] { sql, description } => * Collection< Object > parameters * * @throws IllegalArgumentException the illegal argument exception */ public final Map<String[], Collection<Object>> construct(final Object objCriteria, final Object objConstraints) throws IllegalArgumentException { ReceiptBean searchCriteria = null; ReceiptBean searchConstraints = null; if (objCriteria instanceof ReceiptBean) { searchCriteria = (ReceiptBean) objCriteria; } if (objConstraints instanceof ReceiptBean) { searchConstraints = (ReceiptBean) objConstraints; } if (searchCriteria == null) { throw new IllegalArgumentException("The search criteria must be " + "a valid ReceiptBean"); } if (searchConstraints == null) { throw new IllegalArgumentException("The search constraints must be " + "a valid ReceiptBean"); } StringBuffer sqlWHERE = new StringBuffer(); StringBuffer description = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (searchCriteria.getTags() != null) { try { for (TagBean tag : searchCriteria.getTags()) { Map<String[], Collection<Object>> results = this.tagSearchDAO.construct(tag, new TagBean()); for (String[] index : results.keySet()) { String tagWHERE = index[0]; String tagDescription = index[1]; Collection<Object> tagParameters = results.get(index); if (tagWHERE.compareTo("") != 0) { /* * A WHERE condition is defined, add to the SQL * WHERE clause */ sqlWHERE.append(" " + this.getSQL().getValue("receipt/searchTags") + " WHERE " + tagWHERE + ")"); /* Add to the description and process the arrays */ description.append(tagDescription); if (tagParameters != null) { parameters.addAll(tagParameters); } } } } } catch (Exception e) { dataLogger.error("Error setting tag search options: " + e.getMessage()); } } if (searchCriteria.getBasicSearch() != null) { if (searchCriteria.getBasicSearch().compareTo("") != 0) { String searchString = searchCriteria.getBasicSearch().trim(); int basicSearch = 0; try { basicSearch = Integer.parseInt(searchCriteria.getBasicSearch()); } catch (NumberFormatException nfe) { dataLogger.debug("Error parsing BasicSearch: " + nfe.getMessage()); } if (basicSearch == 0) { // If the search string has : in the first ten // characters the financial abbreviation is probably // included - strip it out for search purposes if (searchString.indexOf(": ") > 3 && searchString.indexOf(": ") < 10) { searchString = searchString.substring(3, searchString.length()); } dataLogger.debug("Basic search string: " + searchString); String field = "%" + searchString + "%"; sqlWHERE.append(" AND (concat(receipt.ReceiptNo, ': ', " + "receipt.Description) LIKE ?)"); description.append(" and a receipt description like '" + searchCriteria.getBasicSearch() + "'"); parameters.add(field); } else { sqlWHERE.append(" AND (receipt.ReceiptNo LIKE ?)"); description.append(" and a receipt number of '" + DataFilter.getHtml(searchCriteria.getBasicSearch()) + "'"); parameters.add("%" + searchCriteria.getBasicSearch()); } } } if (searchCriteria.getNumber() != null) { boolean SearchConstraints = false; if (searchCriteria.getNumber().compareTo("") != 0) { if (searchConstraints.getNumber() != null) { if (searchConstraints.getNumber().compareTo("") != 0) { SearchConstraints = true; } } if (SearchConstraints) { if (searchConstraints.getNumber().compareTo("-") == 0) { // Less than Receipt specified sqlWHERE.append(" AND receipt.ReceiptNo <= ?"); description.append(" and a receipt number less than '" + searchCriteria.getNumber() + "'"); parameters.add(searchCriteria.getNumber()); } else if (searchConstraints.getNumber().compareTo("+") == 0) { // Greater then Receipt specified sqlWHERE.append(" AND receipt.ReceiptNo >= ?"); description .append(" and a receipt number greater than '" + searchCriteria.getNumber() + "'"); parameters.add(searchCriteria.getNumber()); } else { // Range between a and b - find whether greater than or // less than... int intA = 0; int intB = 0; try { intA = Integer.parseInt(searchCriteria.getNumber()); } catch (NumberFormatException nfe) { dataLogger.debug("Error parsing ReceiptNo: " + nfe.getMessage()); } try { intB = Integer.parseInt(searchConstraints.getNumber()); } catch (NumberFormatException nfe) { dataLogger.debug("Error parsing ReceiptNo: " + nfe.getMessage()); } if (intA == intB) { // criteria A and B are the same sqlWHERE.append(" AND receipt.ReceiptNo LIKE ?"); description.append(" and a receipt number of '" + searchCriteria.getNumber() + "'"); parameters.add("%" + searchCriteria.getNumber()); } if (intA < intB) { // criteria A is less than B sqlWHERE.append(" AND receipt.ReceiptNo BETWEEN ? AND ?"); description.append(" and a receipt number between '" + searchCriteria.getNumber() + "' and '" + searchConstraints.getNumber() + "'"); parameters.add(searchCriteria.getNumber()); parameters.add(searchConstraints.getNumber()); } if (intA > intB) { // Criteria A is greater than B sqlWHERE.append(" AND receipt.ReceipttNo BETWEEN ? AND ?"); description.append(" and a receipt number between '" + searchConstraints.getNumber() + "' and '" + searchCriteria.getNumber() + "'"); parameters.add(searchConstraints.getNumber()); parameters.add(searchCriteria.getNumber()); } } } else { sqlWHERE.append(" AND receipt.ReceiptNo LIKE ?"); description.append(" and a receipt number of '" + searchCriteria.getNumber() + "'"); parameters.add("%" + searchCriteria.getNumber()); } } } if (searchCriteria.getGUIDList() != null) { final StringBuffer guidWHERE = new StringBuffer(); for (String guid : searchCriteria.getGUIDList()) { if (StringUtils.isNotBlank(guid)) { guidWHERE.append(" OR receipt.GUID = ?"); parameters.add(guid); } } if (guidWHERE.length() > 0) { // Append the guidWHERE buffer to the sqlWHERE buffer sqlWHERE.append(" AND ("); // Append the guidWHERE but strip the first OR statement sqlWHERE.append(guidWHERE.toString().substring(4)); sqlWHERE.append(")"); description.append(" and has a GUID in the supplied list"); } } if (searchCriteria.getIdentifierList() != null) { final StringBuffer identifierWHERE = new StringBuffer(); for (String identifier : searchCriteria.getIdentifierList()) { if (StringUtils.isNotBlank(identifier)) { identifierWHERE.append(" OR receipt.ReceiptNo LIKE ?"); parameters.add("%" + identifier); } } if (identifierWHERE.length() > 0) { // Append the identifierWHERE buffer to the sqlWHERE buffer sqlWHERE.append(" AND ("); // Append the identifierWHERE but strip the first OR statement sqlWHERE.append(identifierWHERE.toString().substring(4)); sqlWHERE.append(")"); description.append(" and has a receipt number in the supplied list"); } } if (searchCriteria.getBatchReferenceInt() > 0) { boolean SearchConstraints = false; if (searchConstraints.getBatchReferenceLimiter() != null) { if (searchConstraints.getBatchReferenceLimiter().compareTo("+") == 0) { // Greater then Receipt specified sqlWHERE.append(" AND receipt.BatchReference >= ?"); description.append( " and a batch number greater than '" + searchCriteria.getBatchReference() + "'"); parameters.add(searchCriteria.getBatchReference()); SearchConstraints = true; } if (searchConstraints.getBatchReferenceLimiter().compareTo("-") == 0) { // Less than Receipt specified sqlWHERE.append(" AND receipt.BatchReference <= ?"); description .append(" and a batch number less than '" + searchCriteria.getBatchReference() + "'"); parameters.add(searchCriteria.getBatchReference()); SearchConstraints = true; } } if (searchConstraints.getBatchReferenceInt() > 0) { // Range between a and b - find whether greater than or less // than. if (searchCriteria.getBatchReferenceInt() == searchConstraints.getBatchReferenceInt()) { // criteria A and B are the same sqlWHERE.append(" AND receipt.BatchReference = ?"); description .append(" and a batch number equal to '" + searchCriteria.getBatchReferenceInt() + "'"); parameters.add(searchCriteria.getBatchReferenceInt()); SearchConstraints = true; } if (searchCriteria.getBatchReferenceInt() < searchConstraints.getBatchReferenceInt()) { // criteria A is less than B sqlWHERE.append(" AND receipt.BatchReference BETWEEN ? AND ?"); description.append(" and a batch number between '" + searchCriteria.getBatchReferenceInt() + "' and '" + searchConstraints.getBatchReferenceInt() + "'"); parameters.add(searchCriteria.getBatchReferenceInt()); parameters.add(searchConstraints.getBatchReferenceInt()); SearchConstraints = true; } if (searchCriteria.getBatchReferenceInt() > searchConstraints.getBatchReferenceInt()) { // Criteria A is greater than B sqlWHERE.append(" AND receipt.BatchReference BETWEEN ? AND ?"); description.append(" and a batch number between '" + searchConstraints.getBatchReferenceInt() + "' and '" + searchCriteria.getBatchReferenceInt() + "'"); parameters.add(searchConstraints.getBatchReferenceInt()); parameters.add(searchCriteria.getBatchReferenceInt()); SearchConstraints = true; } } if (!SearchConstraints) { sqlWHERE.append(" AND receipt.BatchReference = ?"); description.append(" and a batch number equal to '" + searchCriteria.getBatchReferenceInt() + "'"); parameters.add(searchCriteria.getBatchReferenceInt()); } } if (searchCriteria.getProcessType() != null) { if (searchCriteria.getProcessType().compareTo("") != 0) { sqlWHERE.append(" AND processtype.Class LIKE ?"); description.append(" and a process type like '" + searchCriteria.getProcessType() + "'"); parameters.add(searchCriteria.getProcessType()); } } if (searchCriteria.getPerson() != null) { if (searchCriteria.getPerson().getId() != 0) { sqlWHERE.append(" AND receipt.PersonId = ?"); description.append(" and a person GUID equal to '" + searchCriteria.getPerson() + "'"); parameters.add(searchCriteria.getPerson()); } } if (searchCriteria.getOrganisation() != null) { if (searchCriteria.getOrganisation().getId() != 0) { sqlWHERE.append(" AND receipt.OrganisationId = ?"); description.append(" and an organisation GUID equal to '" + searchCriteria.getOrganisation() + "'"); parameters.add(searchCriteria.getOrganisation()); } } if (searchCriteria.getCancelled()) { if (searchConstraints.getCancelled()) { // Only cancelled receipts sqlWHERE.append(" AND receipt.Cancelled = true"); description.append(" and the receipt is cancelled"); } } else { if (!searchConstraints.getCancelled()) { // Only non-cancelled receipts sqlWHERE.append(" AND receipt.Cancelled = false"); description.append(" and the receipt is not cancelled"); } } if (searchCriteria.getDescription() != null) { if (searchCriteria.getDescription().compareTo("") != 0) { if (searchCriteria.getDescription().indexOf("\"") > -1) { // Description contains "" so treat as a specific search sqlWHERE.append(" AND receipt.Description LIKE ?"); description.append(" and a receipt description like '" + searchCriteria.getDescription() + "'"); parameters.add(StringUtils.replace(searchCriteria.getDescription(), "\"", "")); } else { sqlWHERE.append(" AND receipt.Description LIKE ?"); description.append(" and a receipt description like '" + searchCriteria.getDescription() + "'"); parameters.add("%" + searchCriteria.getDescription() + "%"); } } } if (searchCriteria.getTypeName() != null) { if (searchCriteria.getTypeName().compareTo("") != 0) { sqlWHERE.append(" AND financialtype.Name LIKE ?"); description.append(" and a receipt type like '" + searchCriteria.getTypeName() + "'"); parameters.add(searchCriteria.getTypeName()); } } if (searchCriteria.getClassName() != null) { if (searchCriteria.getClassName().compareTo("") != 0) { sqlWHERE.append(" AND financialtype.Class LIKE ?"); description.append(" and a receipt class like '" + searchCriteria.getClassName() + "'"); parameters.add(searchCriteria.getClassName()); } } if (searchCriteria.getSecurity() != null) { if (searchCriteria.getSecurity().compareTo("") != 0) { sqlWHERE.append(" AND financialtype.Security = ?"); description.append(" and a security setting of '" + searchCriteria.getSecurity() + "'"); parameters.add(searchCriteria.getSecurity()); } } if (searchCriteria.getPayments() != null) { for (PaymentBean payment : searchCriteria.getPayments()) { StringBuffer paymentSearch = new StringBuffer(); if (payment.getDebit() != null) { final DebitBean debit = payment.getDebit(); if (debit.getGUID() > 0) { paymentSearch.append(" AND payment.InvoiceId = ?"); description .append(" and has a payment associated with " + "debit guid: " + debit.getGUID()); parameters.add(debit.getGUID()); } } if (paymentSearch.length() > 0) { sqlWHERE.append( " " + getSQL().getValue("receipt/searchPayment") + paymentSearch.toString() + ")"); } } } // Other searches: cancelled, date issued..... if (searchCriteria.getIssued() != null) { if (searchConstraints.getIssued() != null) { int larger = searchCriteria.getIssued().compareTo(searchConstraints.getIssued()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getIssued()); String fieldB = this.getDf().format(searchConstraints.getIssued()); sqlWHERE.append(" AND receipt.Issued BETWEEN ? AND ?"); description.append(" and issued between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getIssued()); String fieldB = this.getDf().format(searchConstraints.getIssued()); sqlWHERE.append(" AND receipt.Issued BETWEEN ? AND ?"); description.append(" and issued between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getIssued()); sqlWHERE.append(" AND receipt.Issued = ?"); description.append(" and issued on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getIssued()); sqlWHERE.append(" AND receipt.Issued = ?"); description.append(" and issued on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getCreatedDate() != null) { if (searchConstraints.getCreatedDate() != null) { int larger = searchCriteria.getCreatedDate().compareTo(searchConstraints.getCreatedDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getCreatedDate()); String fieldB = this.getDf().format(searchConstraints.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate BETWEEN ? AND ?"); description.append(" and created between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getCreatedDate()); String fieldB = this.getDf().format(searchConstraints.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate BETWEEN ? AND ?"); description.append(" and created between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate = ?"); description.append(" and created on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate = ?"); description.append(" and created on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getModifiedDate() != null) { if (searchConstraints.getModifiedDate() != null) { int larger = searchCriteria.getModifiedDate().compareTo(searchConstraints.getModifiedDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getModifiedDate()); String fieldB = this.getDf().format(searchConstraints.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate BETWEEN ? AND ?"); description.append(" and modified between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getModifiedDate()); String fieldB = this.getDf().format(searchConstraints.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate BETWEEN ? AND ?"); description.append(" and modified between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate = ?"); description.append(" and modified on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate = ?"); description.append(" and modified on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getIncludeGUIDList() != null) { final StringBuffer guidWHERE = new StringBuffer(); for (String guid : searchCriteria.getIncludeGUIDList()) { if (StringUtils.isNotBlank(guid)) { guidWHERE.append(" OR receipt.GUID = ?"); parameters.add(guid); } } if (guidWHERE.length() > 0) { // Append the guidWHERE buffer to the sqlWHERE buffer sqlWHERE.append(" OR ("); // Append the guidWHERE but strip the first OR statement sqlWHERE.append(guidWHERE.toString().substring(4)); sqlWHERE.append(")"); description.append(" and has a GUID in the supplied list"); } } String[] index = new String[] { sqlWHERE.toString(), DataFilter.getHtml(description.toString()) }; Map<String[], Collection<Object>> results = new HashMap<String[], Collection<Object>>(); results.put(index, parameters); return results; } }