/* * ------------------------------------------------------- * Copyright (c) 2004 Appriss. All Rights Reserved. * ------------------------------------------------------- * $Header$ * $Log$ */ package com.appriss.vinehistory.report.mostwanted; import com.appriss.UnknownEnvironmentException; import com.appriss.vinehistory.DLog; import com.appriss.vinehistory.Tools; import java.io.IOException; import java.security.GeneralSecurityException; import java.sql.*; import java.util.*; /** * Helper class that makes it easier for JSPs to display most wanted person * information. * @author Norris Shelton * @version $Revision$ */ public class MostWantedHelper { /** The query used to retrieve a specific most wanted person via sid. */ private static final String RETRIEVE_MOST_WANTED_PERSON = "SELECT * FROM most_wanted_all_vw WHERE most_wanted_sid = ?"; //fixme /** * The query used to retrieve most wanted persons for a specific * site/agency. */ private static final String RETRIEVE_MOST_WANTED_BY_SITE_AGENCY = "SELECT * " + " FROM most_wanted_all_vw m " + //" photo_storage p " //" WHERE m." + " where site_id = ? " + " AND agency = ? "; /* * Methods used to indicate which most wanted person(s) to retrieve */ /** The sid of the most wanted person to retrieve. */ private String sid = ""; /** * Sets the sid of the most wanted person to retrieve. Setting the sid * causes the most wanted person to be retrieved from the database. */ public void setSid(String sid) { this.sid = sid; mostWantedPerson = getMostWantedPersonFromDatabase(); } /** The agency,site ID of the most wanted persons to retrieve. */ private String agencySite = ""; /** Sets the site ID of the most wanted persons to retrieve. */ public void setAgencySite(String site) { this.agencySite = site; } /* * Getter methods to retrieve the information from the DB. */ /** The error message, if any. */ private String errorMessage = ""; /** * Gets the error message, if any. * @return */ public String getErrorMessage() { return errorMessage; } /** The most wanted person that was previously specified via setSid(); */ Map mostWantedPerson = null; /** * Retrieves the most wanted person that was previously specified via * setSid(). */ public Map getMostWantedPerson() { return mostWantedPerson; } /** * Most wanted persons that were previously specified via setSiteId() and * setAgency(). */ List mostWantedBySiteAgency = null; /** * Retrieves the most wanted persons that was previously specified via * setSiteId() and setAgency(). */ public List getMostWantedBySiteAgency() { if (mostWantedBySiteAgency == null) { mostWantedBySiteAgency = getMostWantedBySiteAgencyFromDatabase(); } return mostWantedBySiteAgency; } /** * Retrieves the number of records that are contained in the results of the * most wanted by site agency query. * @return */ public int getMostWantedBySiteAgencySize() { if (mostWantedBySiteAgency == null) { mostWantedBySiteAgency = getMostWantedBySiteAgencyFromDatabase(); } return mostWantedBySiteAgency.size(); } /* * Helper methods. These generally retrieve information from the database. */ /** * Retrieves the most wanted person from the database. * @return Map of most wanted person values. */ private Map getMostWantedPersonFromDatabase() { Map data = new HashMap(); if (!"".equals(sid)) { Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; String methodString = "MostWantedHelper.getMostWantedPersonFromDatabase() "; try { conn = Tools.getConnection(); DLog.Debug(methodString + "executing query: " + RETRIEVE_MOST_WANTED_PERSON); stmt = conn.prepareCall(RETRIEVE_MOST_WANTED_PERSON); stmt.setInt(1, Integer.parseInt(sid)); rs = stmt.executeQuery(); while (rs.next()) { data = populateMostWantedPerson(rs); } } catch (ClassNotFoundException e) { DLog.Error( methodString + "caught ClassNotFoundException " + e.toString()); errorMessage = methodString + "caught ClassNotFoundException"; } catch (GeneralSecurityException e) { DLog.Error(methodString + "caught GeneralSecurityException " + e.toString()); errorMessage = methodString + "caught GeneralSecurityException"; } catch (IOException e) { DLog.Error(methodString + "caught IOException " + e.toString()); errorMessage = methodString + "caught IOException"; } catch (UnknownEnvironmentException e) { DLog.Error(methodString + "caught UnknownEnvironmentException " + e.toString()); errorMessage = methodString + "caught UnknownEnvironmentException"; } catch (SQLException e) { DLog.Error( methodString + "caught SQLException " + e.toString()); errorMessage = methodString + "caught SQLException " + e.toString(); } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { /* do nothing */ } try { if (conn != null) { conn.close(); } } catch (SQLException e) { /* do nothing */ } } } else { errorMessage = "No Most Wanted Person was specified"; } return data; } /** * Retrieves the most wanted persom that was previously specified via * setSiteId() and setAgency() from the database. * @return */ private List getMostWantedBySiteAgencyFromDatabase() { List data = new LinkedList(); if (!"".equals(agencySite)) { Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; String methodString = "MostWantedHelper.getMostWantedBySiteAgency()"; try { conn = Tools.getConnection(); DLog.Debug(methodString + "executing query: " + RETRIEVE_MOST_WANTED_BY_SITE_AGENCY); stmt = conn.prepareCall(RETRIEVE_MOST_WANTED_BY_SITE_AGENCY); stmt.setInt(1, Integer.parseInt(Tools.getSiteFromAgencySite(agencySite))); stmt.setInt(2, Integer.parseInt(Tools.getAgencyFromAgencySite(agencySite))); rs = stmt.executeQuery(); while (rs.next()) { data.add(populateMostWantedPerson(rs)); } } catch (ClassNotFoundException e) { DLog.Error( methodString + "caught ClassNotFoundException " + e.toString()); errorMessage = methodString + "caught ClassNotFoundException"; } catch (GeneralSecurityException e) { DLog.Error(methodString + "caught GeneralSecurityException " + e.toString()); errorMessage = methodString + "caught GeneralSecurityException"; } catch (IOException e) { DLog.Error(methodString + "caught IOException " + e.toString()); errorMessage = methodString + "caught IOException"; } catch (UnknownEnvironmentException e) { DLog.Error(methodString + "caught UnknownEnvironmentException " + e.toString()); errorMessage = methodString + "caught UnknownEnvironmentException"; } catch (SQLException e) { DLog.Error( methodString + "caught SQLException " + e.toString()); errorMessage = methodString + "caught SQLException"; } finally { try { if (rs != null) { rs.close(); } } catch (SQLException e) { /* do nothing */ } try { if (conn != null) { conn.close(); } } catch (SQLException e) { /* do nothing */ } } } else { errorMessage = "No Agency,Site Specified"; } return data; } /** * Populates a map of most wanted fields for each result record. * @param rs * @return Map of most wanted person fields * @throws SQLException */ private Map populateMostWantedPerson(ResultSet rs) throws SQLException { Map mostWantedPerson = new HashMap(); mostWantedPerson.put("tableName", rs.getString("table_name")); mostWantedPerson.put("sid", rs.getString("most_wanted_sid")); mostWantedPerson.put("stateCode", rs.getString("state_cd")); mostWantedPerson.put("siteID", rs.getString("site_id")); mostWantedPerson.put("agencyID", rs.getString("agency")); mostWantedPerson.put("agency", rs.getString("agency_description")); mostWantedPerson.put("lastName", rs.getString("lname")); mostWantedPerson.put("firstName", rs.getString("fname")); mostWantedPerson.put("middleName", rs.getString("mname")); mostWantedPerson.put("SSN", rs.getString("ssn_dash")); mostWantedPerson.put("DOB", rs.getString("date_of_birth")); mostWantedPerson.put("race", rs.getString("race")); mostWantedPerson.put("gender", rs.getString("gender")); // split the height into feet and inches String height = rs.getString("hgt"); mostWantedPerson.put("height", rs.getString("hgt")); if (height != null && height.length() == 3) { mostWantedPerson.put("heightFeet", height.substring(0,1)); mostWantedPerson.put("heightInches", height.substring(1)); } mostWantedPerson.put("weight", rs.getString("wgt")); mostWantedPerson.put("hairColor", rs.getString("hair")); mostWantedPerson.put("eyeColor", rs.getString("eye")); mostWantedPerson.put("build", rs.getString("build_txt")); mostWantedPerson.put("complexion", rs.getString("complexion_txt")); mostWantedPerson.put("scarsAndMarks", rs.getString("marks_scars_tattoos")); mostWantedPerson.put("aliases", rs.getString("alias_names")); mostWantedPerson.put("wantedFor", rs.getString("offense_description")); mostWantedPerson.put("additionalInformation", rs.getString("notes")); mostWantedPerson.put("contactAgency", rs.getString("contact_agency")); mostWantedPerson.put("contactPerson", rs.getString("contact_person")); mostWantedPerson.put("contactPhone", rs.getString("contact_phone")); mostWantedPerson.put("contactEmail", rs.getString("contact_email")); // note this is named as frontPhoto so that the existing views can be re-used. mostWantedPerson.put("frontPhoto", rs.getString("photo_count")); mostWantedPerson.put("status", "MOSTWANTED"); return mostWantedPerson; } }