Requirements19_24.java
package com.riskIt.controller;
import java.sql.*;
import java.util.HashMap;
import java.util.ArrayList;
import tada.TaDaMethod;
import com.riskIt.util.UtilitiesReq19_24;
import com.riskIt.interfaces.CalculateByOccupationOrIndustryCodeInterface;
import com.riskIt.db.DatabaseConnection;
import com.riskIt.util.Factory;
/**
* Requirements19_24.java
* Purpose: Processing of the requirements ( main menu options) 19-24.
*
* @author Selma Tiganj
* @version 1.0 7-15-09
*/
public class Requirements19_24 {
private Connection _conn;
private Statement s;
private static final int ErrorValue = 35565;
private static final int maxEduDegrees = 17;
public Requirements19_24()
{
_conn = null;
s = null;
}
// ########################### FIND TOP STATES ###################################
public ArrayList<String> FindTopStatesByCategory(String selection, String category ) {
//Select top 5 states that have the most workers based on occupation category
ResultSet rs = null;
ArrayList<String> findBestStatesResult = new ArrayList<String>();
int i =0;
ArrayList<Integer> code = new ArrayList<Integer>();
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery(
"SELECT " + selection +"code FROM " + selection + " WHERE " + selection +"="+ category );
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet");
return findBestStatesResult;
}
do{
code.add(rs.getInt(1));
i++;
}while(rs.next());
rs.close();
s.close();
findBestStatesResult = FindTopStatesByCode( selection, code);
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
return findBestStatesResult;
}
@TaDaMethod(variablesToTrack = {"ssn"}, correspondingDatabaseAttribute = {"job.ssn"} )
public ArrayList<String> FindTopStatesByCode(String selection, ArrayList<Integer> code) {
//Select top 5 states that have the most workers based on occupation/industry code
ResultSet rs = null;
ResultSet rs1 = null;
int ssn = 0;
String zip = "";
String state = "";
double[] resultCount = new double[50];
String[] resultStates= new String[50];
int resultIndex = 0;
int stateIndex =0;
ArrayList<String> findBestStatesResult = new ArrayList<String>();
String sqlQuery ="SELECT ssn FROM job WHERE " + selection +"code IN (";
for(int i=0; i<code.size(); i++){
sqlQuery+=code.get(i);
if(i<code.size()-1)
sqlQuery+=", ";
}
sqlQuery+=")";
HashMap<String, String> zipToStateMap = new HashMap<String, String>();
HashMap<Integer, String> ssnToZipMap = new HashMap<Integer, String>();
initializeSsnToZipMap(ssnToZipMap);
initializeZipToStateMap(zipToStateMap);
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery(sqlQuery);
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No SSN for code: " + code);
return findBestStatesResult;
}
do{
//System.out.println("SSN: " + rs.getInt(1));
ssn = rs.getInt(1);
zip = ssnToZipMap.get(ssn);
if(zip==null)
continue;
state = zipToStateMap.get(zip);
if(state==null)
continue;
stateIndex = getStateIndex(resultStates, state);
if( stateIndex < 50){
//System.out.println("Increasing count for state: " + resultStates[stateIndex] + " with index: " + stateIndex);
resultCount[stateIndex]++;
}
else{
//System.out.println("stateIndex: " + stateIndex);
resultCount[resultIndex]++;
resultStates[resultIndex] = state;
resultIndex++;
}
}while(rs.next());
findBestStatesResult = FindBestStates(5, resultStates, resultCount, resultIndex, " Number of workers: ");
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
return findBestStatesResult;
}
private ArrayList<String> FindBestStates(int numResults, String[] resultStates, double[] resultValues, int resultIndex, String text){
double maxValue =0;
int maxIndex =0;
int j=0;
ArrayList<String> findBestStatesResult = new ArrayList<String>();
System.out.println("\nThe best states are: ");
for(int i=0; i<numResults; i++){
for(j=0; j<resultIndex; j++){
if(maxValue < resultValues[j]){
maxValue = resultValues[j];
maxIndex=j;
}
}
if(maxValue != 0){
findBestStatesResult.add("State: " + resultStates[maxIndex] + text + maxValue);
System.out.println("State: " + resultStates[maxIndex] + text + maxValue);
System.out.println("");
maxValue = 0;
resultValues[maxIndex] = 0;
}
}
System.out.println("\n");
return findBestStatesResult;
}
private int getStateIndex(String[] states, String state)
{
int i=0;
//System.out.println("State passed to getStateIndex: " + state);
for(i=0; i<50; i++)
{
//System.out.println("State[" +i+ "] passed to getStateIndex: " + states[i]);
if(states[i]==null || state == null)
continue;
if(states[i].equals(state))
return i;
}
return i;
}
// #########################RECOMMEND BEST STATE TO WORK ######################
@TaDaMethod(variablesToTrack = {"code"}, correspondingDatabaseAttribute = {"job.occupationcode"} )
public ArrayList<String> RecommendBestStateToWork(int ssn){
ResultSet rs = null;
String[] resultStates= new String[50];
double[] averageWage = new double[50];
int size=0;
ArrayList<String> findBestStatesResult = new ArrayList<String>();
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery(
"SELECT occupationcode FROM job WHERE ssn=" + ssn);
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. Occupation code not found for SSN " + ssn);
return findBestStatesResult;
}
int code = rs.getInt(1);
size = CalculateAverageWageForOccupationForAllStates(code, resultStates, averageWage);
System.out.println("");
rs.close();
s.close();
findBestStatesResult = FindBestStates(3, resultStates, averageWage, size, " Average weekly wage for the user's occupation: ");
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
return findBestStatesResult;
}
@TaDaMethod(variablesToTrack = {"ssn"}, correspondingDatabaseAttribute = {"job.ssn"} )
public int CalculateAverageWageForOccupationForAllStates(int occupationCode, String[] resultStates, double[] averageWage){
ResultSet rs = null;
int ssn = 0;
String zip = "";
String state = "";
int[] resultCount = new int[50];
int stateIndex=0;
int size =0;
HashMap<String, String> zipToStateMap = new HashMap<String, String>();
HashMap<Integer, String> ssnToZipMap = new HashMap<Integer, String>();
HashMap<Integer, Integer> ssnToWeekWageMap = new HashMap<Integer, Integer>();
initializeSsnToWeekWageMap(ssnToWeekWageMap);
initializeSsnToZipMap(ssnToZipMap);
initializeZipToStateMap(zipToStateMap);
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery("SELECT ssn FROM job WHERE occupationcode ="+occupationCode);
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No SSN for occupation code: " + occupationCode);
return 0;
}
do{
//System.out.println("State: " + rs1.getString(1));
ssn = rs.getInt(1);
zip = ssnToZipMap.get(ssn);
if(zip==null)
continue;
state = zipToStateMap.get(zip);
if(state==null)
continue;
stateIndex = getStateIndex(resultStates, state);
if( stateIndex < 50){
resultCount[stateIndex]++;
averageWage[stateIndex]+= ssnToWeekWageMap.get(ssn);
}
else{
resultCount[size]++;
resultStates[size] = state;
averageWage[size]+= ssnToWeekWageMap.get(ssn);
size++;
}
}while(rs.next());
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
for(int i=0; i<size; i++){
averageWage[i] = averageWage[i]/resultCount[i];
averageWage[i] = Factory.getRoundMethod(averageWage[i]);
}
return size;
}
// ############################## COMPUTE AVERAGE EDUCATION ###################
public String ComputeAverageEducationString(String selection, int code)
{
String education = "";
int level;
level = ComputeAverageEducation(selection,code);
if(level != ErrorValue){
HashMap<Integer, String> levelToEducationMap = new HashMap<Integer, String>();
initializeLevelToEducationHashMap(levelToEducationMap);
education = levelToEducationMap.get((int)level);
System.out.println("Average education level for "+ selection + " code " + code + ": " + education);
System.out.println("");
System.out.println("");
}
else{
System.out.println("Error when calculating average education level for "+ selection + " code " + code);
System.out.println("");
System.out.println("");
}
return(education);
}
@TaDaMethod(variablesToTrack = {"ssn"}, correspondingDatabaseAttribute = {"job.ssn"} )
public int ComputeAverageEducation(String selection, int code)
{
String education = "";
double averageLevel = 0;
double total =0;
double count = 0;
int ssn=0;
ResultSet rs = null;
int level =0;
HashMap<Integer, String> ssnToEducationMap = new HashMap<Integer, String>();
HashMap<String, Integer> educationTolevelsMap = new HashMap<String, Integer>();
HashMap<Integer, String> levelToEducationMap = new HashMap<Integer, String>();
initializeLevelToEducationHashMap(levelToEducationMap);
initializeEducationToLevelHashMap(educationTolevelsMap);
initializeSsnToEducationMap(ssnToEducationMap);
try {
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery(
"SELECT ssn FROM job WHERE " + selection +"code=" + code);
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No SSN found for " + selection + "code " + code);
return ErrorValue;
}
do{
ssn = rs.getInt(1);
if(ssnToEducationMap.get(ssn)!=null){
education = ssnToEducationMap.get(ssn);
if(educationTolevelsMap.get(education)!=null){
level = educationTolevelsMap.get(education);
if(level !=0){
total+= level;
count ++;
}
}
}
}while(rs.next());
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
averageLevel = total/count;
return (int)averageLevel;
}
// ############################## FIND OCCUPATION/INDUSTRY WITH HIGHEST/LOWES EDUCATION ###################
//@TaDaMethod(variablesToTrack = {"codeList", "categoryList"}, correspondingDatabaseAttribute = {"job.occupationcode"} )
public ArrayList<String> FindCccupationIndustryWithHighestLowestEducation(String selection)
{
String maxCategory = "";
String minCategory = "";
int level = 0;
int maxCode = 0;
int minCode = 0;
int maxLevel = 0;
int minLevel = 15;
ResultSet rs = null;
double MaxAverageIncomeBasedOnCode = 0;
double MinAverageIncomeBasedOnCode = 0;
int[] codeList = new int[500];
String[] categoryList= new String[500];
ArrayList<String> result = new ArrayList<String>();
HashMap<Integer, String> levelToEducationMap = new HashMap<Integer, String>();
initializeLevelToEducationHashMap(levelToEducationMap);
try {
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery(
"SELECT " + selection + "code," + selection + " FROM " + selection);
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. Code not found.");
return result;
}
int i = 0;
do{
codeList[i] = rs.getInt(1);
categoryList[i] = rs.getString(2);
i++;
}while(rs.next());
for(int j=0; j<i; j++){
// 0 code is not valid industry or occupation. Skipp 0
if( (codeList[j] == 0) || (codeList ==null))
continue;
level = ComputeAverageEducation(selection, codeList[j]);
if( maxLevel <level){
maxLevel = level;
maxCode = codeList[j];
maxCategory = categoryList[j];
}
if(minLevel>level && level>0){
minLevel = level;
minCode = codeList[j];
minCategory = categoryList[j];
}
}
result.add(levelToEducationMap.get(maxLevel));
System.out.println("The " + selection + " " + maxCategory+ "\n has the highest education level: " + levelToEducationMap.get(maxLevel));
result.add(levelToEducationMap.get(minLevel));
System.out.println("The " + selection + " " + minCategory + "\n has the lowest education level: " + levelToEducationMap.get(minLevel));
System.out.println("");
System.out.println("");
CalculateByOccupationOrIndustryCodeInterface calc = Factory.getCalculateByOccupationOrIndustryCode();
if(selection.equals("industry")){
MaxAverageIncomeBasedOnCode = calc.calculateIncomeByIndustryCode(maxCode);
MinAverageIncomeBasedOnCode = calc.calculateIncomeByIndustryCode(minCode);
}
else if(selection.equals("occupation")){
MaxAverageIncomeBasedOnCode = calc.calculateIncomeByOccupationCode(maxCode);
MinAverageIncomeBasedOnCode = calc.calculateIncomeByOccupationCode(minCode);
}
else{
System.out.println("Invalid option: " +selection);
return result;
}
double ammount = 0;
ammount = UtilitiesReq19_24.CalculateIncomePercentageDifference(MaxAverageIncomeBasedOnCode, MinAverageIncomeBasedOnCode);
ammount = Factory.getRoundMethod(ammount);
if(MaxAverageIncomeBasedOnCode == MinAverageIncomeBasedOnCode){
result.add("The average income for "+ selection +" with highest education level and lowest education level is equal");
System.out.println("The average income for "+ selection +" with highest education level and lowest education level is equal");
}else if(MaxAverageIncomeBasedOnCode > MinAverageIncomeBasedOnCode){
result.add("The average income for "+ selection +" with highest education level is " + ammount + "% higher than for " +selection+ " with lowest education level");
System.out.println("The average income for "+ selection +" with highest education level is " + ammount + "% higher than for " +selection+ " with lowest education level");
}else{
result.add("The average income for "+ selection +" with highest education level is " + ammount + "% lower than for " +selection+ " with lowest education level");
System.out.println("The average income for "+ selection +" with highest education level is " + ammount + "% lower than for " +selection+ " with lowest education level");
}
System.out.println("");
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
return result;
}
// #########################CALCULATE LIKELINESS TO MOVE FACTOR ######################
@TaDaMethod(variablesToTrack = {"code", "wage", "zip", "state"}, correspondingDatabaseAttribute = {"job.occupationcode", "job.weekwage", "userrecord.zip", "ziptable.statename"} )
public String CalculateLikelinessToMoveFactor(int ssn){
ResultSet rs = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
ResultSet rs3 = null;
String[] resultStates= new String[50];
double[] averageWage = new double[50];
int size=0;
String state="";
double wage =0;
int betterStatesCount=0;
String returnValue="";
try{
System.out.println("flow:14");
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery(
"SELECT occupationcode FROM job WHERE ssn=" + ssn);
if (!rs.next())
{
System.out.println("flow:15");
UtilitiesReq19_24.reportFailure("No rows in ResultSet. Occupation code not found for SSN " + ssn);
return ("Error");
}
int code = rs.getInt(1);
System.out.println("");
rs1 = s.executeQuery(
"SELECT weekwage FROM job WHERE ssn=" + ssn);
if (!rs1.next())
{
System.out.println("flow:16");
UtilitiesReq19_24.reportFailure("No rows in ResultSet. Occupation code not found for SSN " + ssn);
return ("Error");
}
wage = rs1.getInt(1);
rs2 = s.executeQuery(
"SELECT zip FROM userrecord WHERE ssn=" + ssn);
if (!rs2.next())
{
System.out.println("flow:17");
UtilitiesReq19_24.reportFailure("No rows in ResultSet. Occupation code not found for SSN " + ssn);
return ("Error");
}
String zip = rs2.getString(1);
if (zip==null)
{
System.out.println("flow:18");
UtilitiesReq19_24.reportFailure("No rows in ResultSet. Zip not found for SSN " + ssn);
return ("Error");
}
rs3 = s.executeQuery(
"SELECT statename FROM ziptable WHERE zip ='" + zip +"'");
if (!rs3.next())
{
System.out.println("flow:19");
UtilitiesReq19_24.reportFailure("No rows in ResultSet. State not found for SSN " + ssn);
return ("Error");
}
state = rs3.getString(1);
rs.close();
rs1.close();
rs2.close();
rs3.close();
s.close();
size = CalculateAverageWageForOccupationForAllStates(code, resultStates, averageWage);
for(int i=0; i<size; i++){
System.out.println("flow:20");
if((wage < averageWage[i]) && !(state.equals(resultStates[i]))){
System.out.println("flow:21");
betterStatesCount++;
}
}
if(betterStatesCount > size/2){
System.out.println("flow:22");
System.out.println("The likeliness to move factor is high as more than 50% of states have better average wage for this person's occupation");
returnValue= "high";
}
else{
System.out.println("flow:23");
System.out.println("The likeliness to move factor is low as more than 50% of states have lower or equal average wage for this person's occupation");
returnValue= "low";
}
} catch (SQLException e)
{
System.out.println("flow:24");
UtilitiesReq19_24.printExceptionText(e);
}
return returnValue;
}
// ############################## INITIALIZATION ###################################
@TaDaMethod(variablesToTrack = {"educationArray"}, correspondingDatabaseAttribute = {"education.educationmap"} )
private void getEducationResultSet(String[] educationArray ){
ResultSet rs = null;
try {
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
//_conn.setAutoCommit(false);
int i =0;
s.execute(
"create table educationmap(LEVEL int NOT NULL, EDUCATION char(50), PRIMARY KEY (LEVEL))");
s.execute("insert into educationmap values (0, ' Children')");
s.execute("insert into educationmap values (1, ' Less than 1st grade')");
s.execute("insert into educationmap values (2, ' 1st 2nd 3rd or 4th grade')");
s.execute("insert into educationmap values (3, ' 5th or 6th grade')");
s.execute("insert into educationmap values (4, ' 7th and 8th grade')");
s.execute("insert into educationmap values (5, ' 9th grade')");
s.execute("insert into educationmap values (6, ' 10th grade')");
s.execute("insert into educationmap values (7, ' 11th grade')");
s.execute("insert into educationmap values (8, ' 12th grade no diploma')");
s.execute("insert into educationmap values (9, ' High school graduate')");
s.execute("insert into educationmap values (10, ' Some college but no degree')");
s.execute("insert into educationmap values (11, ' Associates degree-occup /vocational')");
s.execute("insert into educationmap values (12, ' Associates degree-academic program')");
s.execute("insert into educationmap values (13, ' Prof school degree (MD DDS DVM LLB JD)')");
s.execute("insert into educationmap values (14, ' Bachelors degree(BA AB BS)')");
s.execute("insert into educationmap values (15, ' Masters degree(MA MS MEng MEd MSW MBA)')");
s.execute("insert into educationmap values (16, ' Doctorate degree(PhD EdD)')");
rs = s.executeQuery("SELECT level, education FROM educationmap");
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No data found in internal educationmap table.");
return;
}
do{
educationArray[i++] = rs.getString(2);
}while(rs.next());
s.execute("drop table educationmap");
rs.close();
//s.comit();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
return;
}
private void initializeLevelToEducationHashMap(HashMap<Integer, String> levelToEducationMap){
String[] resultSet = new String[maxEduDegrees];
int i =0;
getEducationResultSet(resultSet);
do{
levelToEducationMap.put(i, resultSet[i]);
i++;
}while(i<maxEduDegrees);
}
private void initializeEducationToLevelHashMap(HashMap<String, Integer> educationTolevelsMap){
String[] resultSet = new String[maxEduDegrees];
int i =0;
getEducationResultSet(resultSet);
do{
educationTolevelsMap.put(resultSet[i], i);
i++;
}while(i<maxEduDegrees);
}
@TaDaMethod(variablesToTrack = {"a", "b"}, correspondingDatabaseAttribute = {"ssn.education", "education.education"} )
private void initializeSsnToEducationMap(HashMap<Integer, String> ssnToEducationMap){
ResultSet rs = null;
try {
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery("SELECT ssn, education FROM education");
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No SSN found for table education");
return;
}
do{
int a = rs.getInt(1);
String b = rs.getString(2);
ssnToEducationMap.put(a, b);
}while(rs.next());
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
}
@TaDaMethod(variablesToTrack = {"a", "b"}, correspondingDatabaseAttribute = {"ssn.userrecord", "zip.userrecord"} )
private void initializeSsnToZipMap(HashMap<Integer, String> ssnToZipMap){
ResultSet rs = null;
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery("SELECT ssn, zip FROM userrecord");
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No SSN, zip found for table userrecord");
return;
}
do{
int a = rs.getInt(1);
String b = rs.getString(2);
ssnToZipMap.put(a, b);
}while(rs.next());
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
}
@TaDaMethod(variablesToTrack = {"a", "b"}, correspondingDatabaseAttribute = {"ssn.job", "weekwage,job"} )
private void initializeSsnToWeekWageMap(HashMap<Integer, Integer> ssnToWeekWageMap){
ResultSet rs = null;
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery("SELECT ssn, weekwage FROM job");
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No SSN, weekwage found for table job");
return;
}
do{
int a = rs.getInt(1);
int b = rs.getInt(2);
ssnToWeekWageMap.put(a, b);
}while(rs.next());
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
}
@TaDaMethod(variablesToTrack = {"a", "b"}, correspondingDatabaseAttribute = {"zip.ziptable", "statename.ziptable"} )
private void initializeZipToStateMap(HashMap<String, String> zipToStateMap){
ResultSet rs = null;
try{
_conn = DatabaseConnection.getConnection();
s = _conn.createStatement();
rs = s.executeQuery("SELECT zip, statename FROM ziptable");
if (!rs.next())
{
UtilitiesReq19_24.reportFailure("No rows in ResultSet. No zip, statename found for table job");
return;
}
do{
String a = rs.getString(1);
String b = rs.getString(2);
zipToStateMap.put(a, b);
}while(rs.next());
rs.close();
s.close();
} catch (SQLException e)
{
UtilitiesReq19_24.printExceptionText(e);
}
}
}