CalculateByState.java
package com.riskIt.controller;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import tada.TaDaMethod;
import com.riskIt.interfaces.CalculateByStateInterface;
import com.riskIt.interfaces.CalcImplInterface;
import com.riskIt.interfaces.TypeWageDataStructureInterface;
import com.riskIt.util.Factory;
/**
* CalculateByState.java
* Purpose: Calculation Class to calculate average weekly wage and average
* income by State. Used for requirements 4 and 6.
*
* @author Bryan Angone
* @version 1.0 7-12-09
*/
/**
* Object to perform all calculations necessary to calculate
* average income and average weekly wage by all or by a given
* race state contained in the database. Uses Hash Maps and
* offloads processing to the JVM to overcome inherent
* inefficiencies in the Derby database thus significantly improving
* performance.
*/
public class CalculateByState implements CalculateByStateInterface {
ResultSet results;
Statement statement;
/**
* Returns a list with all the state codes contained in the database.
* <p>
* @return An ArrayList<String> of all state codes.
*/
@TaDaMethod(variablesToTrack ={"stateList"},
correspondingDatabaseAttribute = {"ziptable.statename"})
public ArrayList<String> getStateList() {
ArrayList<String> stateList = new ArrayList<String>();
try {
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT DISTINCT STATENAME FROM ziptable");
while (results.next()) {
if(results.getString("STATENAME") != null){
stateList.add(results.getString(1).trim());
}
}
} catch (SQLException e) {
while (e != null) {
System.err.println("\n----- SQLException -----");
System.err.println(" SQL State: " + e.getSQLState());
System.err.println(" Error Code: " + e.getErrorCode());
System.err.println(" Message: " + e.getMessage());
// for stack traces, refer to derby.log or uncomment this:
// e.printStackTrace(System.err);
e = e.getNextException();
}
}
Collections.sort(stateList);
return stateList;
}
/**
* Checks if a state code is contained in the database
* <p>
* @param A String of a state code to test
* @return Boolean true / false answer
*/
public boolean isInStateList(String state) {
ArrayList<String> stateList = getStateList();
if(state == null){
state = "";
} else {
state = state.trim();
}
for (String i : stateList) {
if (i.trim().equalsIgnoreCase(state)) {
return true;
}
}
return false;
}
/**
* Calculates average income for a state code
* <p>
* @param A String of a state code to request
* Warning: This method does not check if the
* state string submitted is contained in
* the database as this is an expensive call to
* the db. To check if the string is contained
* in the db use the method :
* isInStateList(String)
* @return A Double rounded to two decimal places using the
* ROUND_HALF_EVEN method of the BigDecimal class.
*/
public double calculateIncomeByState(String state) {
return calculateIncome(state, Factory.getYearlyImpl());
}
/**
* Calculates average weekly wage for a state code
* <p>
* @param A String of a state code to request
* Warning: This method does not check if the
* state string submitted is contained in
* the database as this is an expensive call to
* the db. To check if the string is contained
* in the db use the method :
* isInStateList(String)
* @return A Double rounded to two decimal places using the
* ROUND_HALF_EVEN method of the BigDecimal class.
*/
public double calculateWeeklyWageByState(String state) {
return calculateIncome(state, Factory.getWeeklyImpl());
}
/**
* Calculates average income for all states in the database
* <p>
* @return A Double rounded to two decimal places using the
* ROUND_HALF_EVEN method of the BigDecimal class.
*/
public double calculateIncomeByAllStates() {;
return calculateIncome("all", Factory.getYearlyImpl());
}
/**
* Calculates average weekly wage for all states in the database
* <p>
* @return A Double rounded to two decimal places using the
* ROUND_HALF_EVEN method of the BigDecimal class.
*/
public double calculagteWeeklyWageByAllState() {
return calculateIncome("all", Factory.getWeeklyImpl());
}
@TaDaMethod(variablesToTrack ={"ssnZipHM", "ssnZipHM", "zipStateHM", "zipStateHM", "ssnWageHM", "ssnWageHM",
"ssnWorkWeeks", "ssnWorkWeeks"},
correspondingDatabaseAttribute = {"userrecord,zip", "userrecord.ssn",
"ziptable.zip", "ziptable.statename",
"job.ssn", "job.WEEKWAGE",
"job.ssn", "job.WORKWEEKS"})
private double calculateIncome(String stateIn, CalcImplInterface type) {
double returnValue = 0;
HashMap<Integer, String> ssnZipHM = new HashMap<Integer, String>();
HashMap<String, String> zipStateHM = new HashMap<String, String>();
HashMap<Integer, Integer> ssnWageHM = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> ssnWorkWeeks = new HashMap<Integer, Integer>();
String stateCode = null;
int weekWage = 0;
int workWeeks = 0;
ArrayList<TypeWageDataStructureInterface> stateWageAL = new ArrayList<TypeWageDataStructureInterface>();
double sum = 0;
double count = 0;
if(stateIn == null){
stateIn = "bad_value_return_0";
}else {
stateIn = stateIn.trim();
}
try {
statement = Factory.getConnection().createStatement();
// Get the SSN and Zip from user record and put them in a hashMap
results = statement.executeQuery("SELECT SSN, ZIP from userrecord WHERE ZIP IS NOT NULL");
while (results.next()) {
ssnZipHM.put(results.getInt("SSN"), results.getString("ZIP"));
}
// Get the ZIP and State Code from Zip Table and put them in a hash
// map
results = statement.executeQuery("SELECT ZIP, STATENAME from ziptable");
while (results.next()) {
if(results.getString("ZIP") == null || results.getString("STATENAME") == null){
continue;
}
zipStateHM.put(results.getString("ZIP"), results.getString("STATENAME"));
}
// Get the SSN and Wage from job table and put in a hash map
results = statement.executeQuery("SELECT SSN, WEEKWAGE, WORKWEEKS from job");
while (results.next()) {
if(results.getInt("WEEKWAGE") > 0){
ssnWageHM.put(results.getInt("SSN"), results.getInt("WEEKWAGE"));
}
if(results.getInt("WORKWEEKS") > 0){
ssnWorkWeeks.put(results.getInt("SSN"), results.getInt("WORKWEEKS"));
}
}
// Iterate through all the SSN's from main user record
// First find the zip for that SSN
for (Iterator<Integer> i = ssnZipHM.keySet().iterator(); i.hasNext();) {
int keySSN = (Integer) i.next();
String zip = (String) ssnZipHM.get(keySSN);
// Then find the state code for that zip
if (zip != null && !(zip.equals(""))) {
stateCode = (String) zipStateHM.get(zip);
if (stateCode != null) {
stateCode = stateCode.trim();
}
} else {
stateCode = null;
}
// Then find weekly wage from job table based on ssn
// If Null or can not part to an int then Week Wage = 0;
if (ssnWageHM.get(keySSN) == null) {
weekWage = 0;
} else {
weekWage = (Integer) ssnWageHM.get(keySSN);
}
if (ssnWorkWeeks.get(keySSN) == null) {
workWeeks = 0;
} else {
workWeeks = (Integer) ssnWorkWeeks.get(keySSN);
}
// Then build an new Array List inserting new StateWageClass
// object
type.addToArrayList(stateWageAL, stateCode, weekWage, workWeeks);
}
// Now go through the stateWageAL Array List and look for the state
// selected or all states for ALL
for (TypeWageDataStructureInterface i : stateWageAL) {
double result = type.calculateAverage(stateIn, i);
if(result > 0){
sum = sum + result;
count++;
}
}
// Handle Divide by 0 for invalid state codes or states with no wage
// data
if (count == 0) {
returnValue = 0;
} else {
returnValue = sum / count;
}
} catch (SQLException e) {
{
while (e != null) {
System.err.println("\n----- SQLException -----");
System.err.println(" SQL State: " + e.getSQLState());
System.err.println(" Error Code: " + e.getErrorCode());
System.err.println(" Message: " + e.getMessage());
// for stack traces, refer to derby.log or uncomment this:
// e.printStackTrace(System.err);
e = e.getNextException();
}
throw new IllegalArgumentException();
}
}
return Factory.getRoundMethod(returnValue);
}
}