TestCalculateByState.java
package com.riskIt.test;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Random;
import com.riskIt.controller.CalculateByState;
import com.riskIt.interfaces.CalculateByStateInterface;
import com.riskIt.util.Factory;
import junit.framework.Assert;
import junit.framework.TestCase;
/**
* TestCalculateByState.java
* Purpose: Tests the CalculateByState class
* @author Bryan Angone
* @version 1.0 7-12-09
*/
public class TestCalculateByState extends TestCase {
private Statement statement;
private ResultSet results;
Random generator = new Random();
public TestCalculateByState(){
super();
}
public void testGetStateList(){
int count = 0;
String stateLable = null;
String stateLable2 = null;
try{
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT DISTINCT STATENAME FROM ziptable");
while(results.next()){
if(results.getString("STATENAME") == null){
continue;
}
count++;
if(count <= 1){
stateLable = results.getString("STATENAME");
stateLable2 = results.getString("STATENAME");
}
int randomIndex = generator.nextInt(20);
if(randomIndex == 2 || randomIndex == 6 || randomIndex == 10 || randomIndex == 14 || randomIndex == 18){
stateLable = results.getString("STATENAME");
}
if(randomIndex == 3 || randomIndex == 7 || randomIndex == 11 || randomIndex == 15 || randomIndex == 19){
stateLable2 = results.getString("STATENAME");
}
}
} 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();
}
}
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
ArrayList<String> stateList = calc.getStateList();
Assert.assertEquals(count, stateList.size());
Assert.assertNotSame(count +1, stateList.size());
Assert.assertTrue(stateList.contains(stateLable.trim()));
Assert.assertTrue(stateList.contains(stateLable2.trim()));
stateLable = "";
Assert.assertFalse(stateList.contains(stateLable));
stateLable = "Random Name";
Assert.assertFalse(stateList.contains(stateLable));
Assert.assertTrue(stateList.contains(stateLable2.trim()));
// Take Out if DB Changes
stateLable = "IL";
Assert.assertTrue(stateList.contains(stateLable.trim()));
}
public void testIsInStateList(){
int count = 0;
String stateLable = null;
String stateLable2 = null;
try{
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT DISTINCT STATENAME FROM ziptable");
while(results.next()){
if(results.getString("STATENAME") == null){
continue;
}
count++;
if(count <= 1){
stateLable = results.getString("STATENAME");
stateLable2 = results.getString("STATENAME");
}
int randomIndex = generator.nextInt(20);
if(randomIndex == 2 || randomIndex == 6 || randomIndex == 10 || randomIndex == 14 || randomIndex == 18){
stateLable = results.getString("STATENAME");
}
if(randomIndex == 3 || randomIndex == 7 || randomIndex == 11 || randomIndex == 15 || randomIndex == 19){
stateLable2 = results.getString("STATENAME");
}
}
} 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();
}
}
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue(calc.isInStateList(stateLable));
Assert.assertTrue(calc.isInStateList(stateLable2));
Assert.assertFalse(calc.isInStateList(null));
Assert.assertFalse(calc.isInStateList(""));
Assert.assertFalse(calc.isInStateList("Fake Name"));
Assert.assertTrue(calc.isInStateList("Il"));
Assert.assertTrue(calc.isInStateList(" " + "iL" + " "));
Assert.assertFalse(calc.isInStateList(" XX " + " IL" + " XX "));
}
public void testCalculateIncomeByState(){
double sum = 0;
double count = 0;
double average = 0;
String stateToTest = "IL";
HashMap<Integer, Integer> ssnSum = new HashMap<Integer, Integer>();
try{
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT job.SSN, job.WEEKWAGE, job.WORKWEEKS FROM (job JOIN (userrecord JOIN ziptable on userrecord.ZIP = ziptable.ZIP) on job.SSN = userrecord.SSN) WHERE ziptable.STATENAME = '" + stateToTest + "'");
while(results.next()){
if(results.getString("SSN") == null || results.getString("WEEKWAGE") == null || results.getString("WORKWEEKS") == null ){
continue;
}
if(results.getInt("WEEKWAGE") > 0 && results.getInt("WORKWEEKS") > 0){
ssnSum.put(results.getInt("SSN"), (results.getInt("WEEKWAGE") * results.getInt("WORKWEEKS")));
}
}
results = statement.executeQuery("SELECT SSN, ZIP FROM userRecord WHERE ZIP IS NOT NULL");
while(results.next()){
if(results.getString("SSN") == null || results.getString("ZIP") == null){
continue;
}
if(ssnSum.containsKey(results.getInt("SSN")) ){
sum = sum + ssnSum.get(results.getInt("SSN"));
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();
}
}
if(count == 0 || sum == 0){
average = 0;
} else {
average = sum / count;
BigDecimal bd = new BigDecimal(average);
bd = bd.setScale(2,BigDecimal.ROUND_HALF_EVEN);
average = bd.doubleValue();
}
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue(average == calc.calculateIncomeByState(stateToTest));
Assert.assertFalse(average == calc.calculateIncomeByState(null));
Assert.assertFalse(average == calc.calculateIncomeByState("Non Valid State"));
Assert.assertFalse((average +.01) == calc.calculateIncomeByState(stateToTest));
Assert.assertTrue(average == calc.calculateIncomeByState(stateToTest));
Assert.assertTrue(average == calc.calculateIncomeByState(" " + stateToTest + " "));
// Only Works with original unaltered DB
// Assert.assertTrue(36150.74 == calc.calculateIncomeByState(stateToTest));
}
public void testCalculateWeeklyWageByState(){
{
double sum = 0;
double count = 0;
double average = 0;
String stateToTest = "IL";
HashMap<Integer, Integer> ssnSum = new HashMap<Integer, Integer>();
try{
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT job.SSN, job.WEEKWAGE FROM (job JOIN (userrecord JOIN ziptable on userrecord.ZIP = ziptable.ZIP) on job.SSN = userrecord.SSN) WHERE ziptable.STATENAME = '" + stateToTest + "'");
while(results.next()){
if(results.getString("SSN") == null || results.getString("WEEKWAGE") == null){
continue;
}
if(results.getInt("WEEKWAGE") > 0){
ssnSum.put(results.getInt("SSN"), results.getInt("WEEKWAGE"));
}
}
results = statement.executeQuery("SELECT SSN, ZIP from userRecord WHERE ZIP IS NOT NULL");
while(results.next()){
if(results.getString("SSN") == null || results.getString("ZIP") == null){
continue;
}
if(ssnSum.containsKey(results.getInt("SSN")) ){
sum = sum + ssnSum.get(results.getInt("SSN"));
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();
}
}
if(count == 0 || sum == 0){
average = 0;
} else {
average = sum / count;
BigDecimal bd = new BigDecimal(average);
bd = bd.setScale(2,BigDecimal.ROUND_HALF_EVEN);
average = bd.doubleValue();
}
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue(average == calc.calculateWeeklyWageByState(stateToTest));
Assert.assertFalse(average == calc.calculateWeeklyWageByState(null));
Assert.assertFalse(average == calc.calculateWeeklyWageByState("Non Valid State"));
Assert.assertFalse((average +.01) == calc.calculateWeeklyWageByState(stateToTest));
Assert.assertTrue(average == calc.calculateWeeklyWageByState(stateToTest));
Assert.assertTrue(average == calc.calculateWeeklyWageByState(" " + stateToTest + " "));
// Only Works with original unaltered DB
// Assert.assertTrue(779.25 == calc.calculateWeeklyWageByState(stateToTest));
}
}
public void testCalculateIncomeByAllStates(){
double sum = 0;
double count = 0;
double average = 0;
HashMap<Integer, Integer> ssnSum = new HashMap<Integer, Integer>();
HashMap<String, String> zipStateHM = new HashMap<String, String>();
try{
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT SSN, WEEKWAGE, WORKWEEKS FROM job");
while(results.next()){
if(results.getString("SSN") == null || results.getString("WEEKWAGE") == null || results.getString("WORKWEEKS") == null ){
continue;
}
if(results.getInt("WEEKWAGE") > 0 && results.getInt("WORKWEEKS") > 0){
ssnSum.put(results.getInt("SSN"), (results.getInt("WEEKWAGE") * results.getInt("WORKWEEKS")));
}
}
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"));
}
results = statement.executeQuery("SELECT SSN, ZIP FROM userRecord WHERE ZIP IS NOT NULL");
while(results.next()){
if(results.getString("SSN") == null || results.getString("ZIP") == null){
continue;
}
if(ssnSum.containsKey(results.getInt("SSN")) && zipStateHM.get(results.getString("ZIP")) != null ){
sum = sum + ssnSum.get(results.getInt("SSN"));
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();
}
}
if(count == 0 || sum == 0){
average = 0;
} else {
average = sum / count;
BigDecimal bd = new BigDecimal(average);
bd = bd.setScale(2,BigDecimal.ROUND_HALF_EVEN);
average = bd.doubleValue();
}
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue(average == calc.calculateIncomeByAllStates());
Assert.assertFalse((average +.01) == calc.calculateIncomeByAllStates());
// Only Works with original unaltered DB
// Assert.assertTrue(40269.43 == calc.calculateIncomeByAllStates());
}
public void testCalculagteWeeklyWageByAllState(){
double sum = 0;
double count = 0;
double average = 0;
HashMap<Integer, Integer> ssnSum = new HashMap<Integer, Integer>();
HashMap<String, String> zipStateHM = new HashMap<String, String>();
try{
statement = Factory.getConnection().createStatement();
results = statement.executeQuery("SELECT SSN, WEEKWAGE FROM job");
while(results.next()){
if(results.getString("SSN") == null || results.getString("WEEKWAGE") == null){
continue;
}
if(results.getInt("WEEKWAGE") > 0){
ssnSum.put(results.getInt("SSN"), results.getInt("WEEKWAGE"));
}
}
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"));
}
results = statement.executeQuery("SELECT SSN, ZIP FROM userRecord");
while(results.next()){
if(results.getString("SSN") == null || results.getString("ZIP") == null){
continue;
}
if(ssnSum.containsKey(results.getInt("SSN")) && zipStateHM.get(results.getString("ZIP")) != null ){
sum = sum + ssnSum.get(results.getInt("SSN"));
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();
}
}
if(count == 0 || sum == 0){
average = 0;
} else {
average = sum / count;
BigDecimal bd = new BigDecimal(average);
bd = bd.setScale(2,BigDecimal.ROUND_HALF_EVEN);
average = bd.doubleValue();
}
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue(average == calc.calculagteWeeklyWageByAllState());
Assert.assertFalse((average +.01) == calc.calculagteWeeklyWageByAllState());
// Only Works with original unaltered DB
// Assert.assertTrue(862.72 == calc.calculagteWeeklyWageByAllState());
}
public void testCalculateIncomeByState2(){
try{
statement = Factory.getConnection().createStatement();
statement.execute("INSERT INTO userRecord (NAME, ZIP, SSN, RACE) VALUES (' Test Name 1','99994', 999999980, ' Test Race 1')");
statement.execute("INSERT INTO job (SSN, WEEKWAGE, WORKWEEKS) VALUES (999999980, 500, 40)");
statement.execute("INSERT INTO userRecord (NAME, ZIP, SSN, RACE) VALUES (' Test Name 2','99994', 999999981, ' Test Race 1')");
statement.execute("INSERT INTO job (SSN, WEEKWAGE, WORKWEEKS) VALUES (999999981, 1500, 20)");
statement.execute("INSERT INTO ziptable (ZIP, STATENAME) VALUES ('99994', 'XX')");
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue((((500 * 40) + (1500 * 20)) / 2) == calc.calculateIncomeByState("XX"));
statement.execute("DELETE from ziptable WHERE ZIP = '99994' AND STATENAME = 'XX'");
statement.execute("DELETE from job WHERE SSN = 999999980");
statement.execute("DELETE from userRecord WHERE SSN = 999999980");
statement.execute("DELETE from job WHERE SSN = 999999981");
statement.execute("DELETE from userRecord WHERE SSN = 999999981");
} 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();
}
}
}
public void testCalculateWeeklyWageByState2(){
try{
statement = Factory.getConnection().createStatement();
statement.execute("INSERT INTO userRecord (NAME, ZIP, SSN, RACE) VALUES (' Test Name 1','99994', 999999980, ' Test Race 1')");
statement.execute("INSERT INTO job (SSN, WEEKWAGE, WORKWEEKS) VALUES (999999980, 500, 40)");
statement.execute("INSERT INTO userRecord (NAME, ZIP, SSN, RACE) VALUES (' Test Name 2','99994', 999999981, ' Test Race 1')");
statement.execute("INSERT INTO job (SSN, WEEKWAGE, WORKWEEKS) VALUES (999999981, 1500, 20)");
statement.execute("INSERT INTO ziptable (ZIP, STATENAME) VALUES ('99994', 'XX')");
CalculateByStateInterface calc = null;
Assert.assertTrue(calc == null);
calc = Factory.getCalculateByState();
Assert.assertTrue(calc != null);
Assert.assertTrue(calc.getClass().equals(CalculateByState.class));
Assert.assertTrue(((500 + 1500 ) / 2) == calc.calculateWeeklyWageByState("XX"));
statement.execute("DELETE from ziptable WHERE ZIP = '99994' AND STATENAME = 'XX'");
statement.execute("DELETE from job WHERE SSN = 999999980");
statement.execute("DELETE from userRecord WHERE SSN = 999999980");
statement.execute("DELETE from job WHERE SSN = 999999981");
statement.execute("DELETE from userRecord WHERE SSN = 999999981");
} 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();
}
}
}
}