ZipcodeManager.java

package com.riskIt.controller;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import tada.TaDaMethod;

import com.riskIt.db.DatabaseConnection;
import com.riskIt.util.*;
import com.riskIt.data.*;

/**
 * This class mainly works on methods that deals with zipcode properties
 * @author Poornima Tantry
 *
 */
public class ZipcodeManager 
{
	private AccessorMethods acc;
	
	public ZipcodeManager()
	{
		acc = new AccessorMethods();
	}
	/**
	 * This method gets all persons living in same zipcode and then calculated range 
	 * @param zipcode - zipcode to be compared with
	 * @param c - connection from the calling method
	 * @author Poornima Tantry
	 */
	@TaDaMethod(variablesToTrack = {"a", "b", "c", "d", "e", "f"},
			correspondingDatabaseAttribute = {"job.workweeks", "job.weekwage ", "investment.capitalgains",  
			"investment.capitallosses", "investment.stockdividends", "userrecord.ssn"})
	public ArrayList<ScoreData> getAllZipcodes(String zipcode)
	{
		String cmds_getAllZipcode = "";
		ResultSet result = null;
		ArrayList<ScoreData> sData = new ArrayList<ScoreData>();
		
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			Statement s = conn.createStatement();
			
			//get all zipcode similar to the given person
			if(zipcode != null)
			{
				cmds_getAllZipcode = "SELECT userrecord.zip, userrecord.ssn, investment.capitalgains, " 
									+ "investment.capitallosses, investment.stockdividends, " 
									+ "job.workweeks, job.weekwage " 
									+ "FROM  investment, userrecord, job WHERE "
									+ " investment.ssn = userrecord.ssn and "
									+ " job.ssn = userrecord.ssn and "
									+ "userrecord.zip = '" + zipcode + "'" ;
				
				// find all the persons with same zipcode		
				result = s.executeQuery(cmds_getAllZipcode);
		
				while(result.next())
				{		
					int a = result.getInt("workweeks"),
					b = result.getInt("weekwage"),
					c = result.getInt("capitalGains"),
					d = result.getInt("capitallosses"),
					e = result.getInt("stockdividends"),
					f = result.getInt("ssn");
					//calculate the range for each person in resultSet
					sData = acc.calculateRange(	a,
												b,
												c,
												d,
												e,
												(long)f, sData);				
				}		
				result.close();
			}
			s.close();
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in getAllZipcode");
			e.printStackTrace();
		}	
		return sData;
	}
	
	/**
	 * This method gets the name of state by first searching the zipcode
	 * from the userrecord
	 * @param inv - customer's information unit
	 * @return - customer object with updated values
	 * @author Poornima Tantry
	 */
	@TaDaMethod(variablesToTrack = {"zipcode", "age"},
			correspondingDatabaseAttribute = {"userrecord.zip", "userrecord.age"})
	public Invitation getOneZipcode(Invitation inv)
	{
		String stateName = "";
		int age = 0;
		String cmd_zipcode = "select zip, age from userrecord where ssn = " + inv.getUserSSN();
		String zipcode = "";
		
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			Statement s = conn.createStatement();
			
			ResultSet result = s.executeQuery(cmd_zipcode);
			if(result.next())
			{
				zipcode = result.getString("zip");
				age = result.getInt("age");
			}
			//set data in the object
			inv.setZipcode(zipcode);
			inv.setAge(age);
			
			result.close();
			
			//get the state name from zipcode
			String cmd_state = "select * from ziptable where zip = '" + zipcode + "'";
			
			result = s.executeQuery(cmd_state);
			if(result.next())
				stateName = result.getString("statename");
			inv.setStateName(stateName);
			result.close();
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in getOneZipcode");
			e.printStackTrace();
		}
		return inv;
	}
	
}