UserManager.java

package com.riskIt.controller;

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

import tada.TaDaMethod;

import com.riskIt.db.DatabaseConnection;
import com.riskIt.util.AccessorMethods;
import com.riskIt.util.RiskItConstants;
import com.riskIt.data.Invitation;
import com.riskIt.controller.AgentManager;

public class UserManager 
{
	private AccessorMethods acc;
	private AgentManager agent;
	
	public UserManager()
	{
		acc = new AccessorMethods();
		agent = new AgentManager();
	}
	
	/**
	 * This method filters data from "user-record" table that matches same 
	 * zipcode and displays the data.
	 * @param zip - holds the value of zip-code to be compared with and filtered
	 * @return count - number of people living in this zip-code
	 * @author Poornima Tantry
	 */
	public int filterZipcode(String zip)
	{
		int count = 0;
		Statement stat = null;
		ResultSet result = null;
		
		if(zip.length() == 0 || zip.equals("null") )
			zip = null;
		
		String cmd_zipSearch = "SELECT * from userrecord where zip = '" + zip + "'";
		
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
					
			result = stat.executeQuery(cmd_zipSearch);
			
			System.out.println("List of customers for zipcode : " + zip);
			System.out.printf("%20s |%20s |" ,"NAME", "SSN");
			System.out.println("");
			
			//display data from result set
			while(result.next())
			{
				++count;
				System.out.printf("%s |%s |", result.getString("name"), result.getString("ssn"));
				System.out.println("");
			}
			
			if(count == 0)
				System.out.println("There are no customers enrolled in this zipcode");
			else
				System.out.println("No. of customers in zipcode : " + zip + " is " + count);
			result.close();
		}
		catch(Exception e)
		{
			System.out.println("Exception in filterZipcode");
			e.printStackTrace();
		}
		return count;
		
	}
	
	/**
	 * This method accepts name of the state and calculates number of 
	 * people employment within the state as well as number of people unemployed 
	 * within the state and gives the percentage of unemployment rate..
	 * @param stateName - holds value of the state
	 * @return ratePercent - return the unemployment rate for the given state
	 * @author Poornima Tantry
	 */
	
	@TaDaMethod(variablesToTrack= {"countOfAllEmployees", "countOfAllEmployees",
			"countOfUnemployed", "countOfUnemployed", "countOfUnemployed", "countOfUnemployed", "countOfUnemployed"}
	, correspondingDatabaseAttribute = {"ziptable.zip", "userrecord.zip",
			"employmentStat.ssn", "userrecord.ssn", "ziptable.statename", "ziptable.zip", "userrecord.zip"})
			
	public double calculateUnEmploymentRate(String stateName)
	{
		int countOfAllEmployees = 0;
		int countOfUnemployed = 0;
		double ratePercent = 0.0;
		
		Statement stat = null;
		ResultSet result = null;
		
		//validate input
		stateName = acc.checkLetter(stateName);
		
		if(stateName.equals(""))
			System.out.println("You have entered a incorrect value.");
		else
		{
			try
			{
				Connection conn = DatabaseConnection.getConnection();
				stat = conn.createStatement();
					
				String cmd_getAllUnemployed = "SELECT count(*) as totalUnemploy "
											+ " FROM ziptable, employmentStat, userrecord "
											+ " WHERE employmentStat.ssn = userrecord.ssn " 
											+ "and employmentstat.unemploymentreason <> 'null' "
											+ "and ziptable.statename = '" + stateName + "' "
											+ "and ziptable.zip = userrecord.zip";
				
				String cmd_getTotalCount = "SELECT count(*) as totalCount FROM  ziptable, userrecord "
											+ " WHERE ziptable.statename = '" + stateName + "' and ziptable.zip = userrecord.zip";
				
				result = stat.executeQuery(cmd_getTotalCount);		
				
				if(result.next())
					countOfAllEmployees = result.getInt("totalCount");			
				result.close();
			
				System.out.println("Total number of people working for the state of "
									+ stateName + " is " + countOfAllEmployees);
				
				//check if total count is 0, 
				//then set it to 1, to avoid division by zero
				if(countOfAllEmployees == 0)
					countOfAllEmployees = 1;
				
				result = stat.executeQuery(cmd_getAllUnemployed);		
				
				if(result.next())
					countOfUnemployed = result.getInt("totalUnemploy");		
				result.close();		
				
				System.out.println("Total number of unemployed people for the state of "
						+ stateName + " is " + countOfUnemployed);	
				
				//calculate the unemployment rate
				ratePercent = ((double)countOfUnemployed/countOfAllEmployees)*100;
				
				//get the precision to 2 digits
				ratePercent = ratePercent * 100;
				ratePercent = Math.round(ratePercent);
				ratePercent = ratePercent/100;
			
				System.out.printf("The unemployment rate for the state of %s is %3.2f", stateName,ratePercent);
				System.out.println();
				stat.close();
			}
			catch(Exception e)
			{
				System.out.println("Catch in calculateUnemployment");
				e.printStackTrace();
			}
		}
		return ratePercent;
	}
	
	/**
	 * This method is used to obtain set of results that matches the 
	 * given education level and display the customer's list 
	 * @param edu - holds the value of education to be compared with
	 * @return count - number of people holding this education
	 * @author Poornima Tantry
	 */
	public int filterEducation(String edu)
	{
		int count = 0;
		Statement stat = null;
		ResultSet result = null;
		
		if(edu.length() == 0)
			edu = null;
		if(edu.equals("null"))
			edu = null;
		
		String cmd_eduSearch = "SELECT userrecord.ssn, userrecord.name, " 
								+ " education.education "
								+ " from userrecord, education where "
								+ " userrecord.ssn = education.ssn and "
								+ " education.education = ' " + edu + "'";
		
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(cmd_eduSearch);
			
			System.out.println("List of customers for given education : " + edu);
			System.out.printf("%20s |%20s |" ,"NAME", "SSN");
			System.out.println("");
			
			//display data from result set
			while(result.next())
			{
				++count;
				System.out.printf("%s |%s |", result.getString("name"), result.getString("ssn"));
				System.out.println("");
			}
			
			if(count == 0)
				System.out.println("There are no customers enrolled with this education");
			else
				System.out.println("No. of customers with education : " + edu + " is " + count);
			result.close();
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in filterEducation");
			e.printStackTrace();
		}
		
		return count;
	}
	
	
	/**
	 * This method displays customer's list that is a match with resepct to the 
	 * specified occupation
	 * @param occupation - holds the value of the occupation name to be compared with
	 * @return count - count of number of people with the given occupation
	 * @author Poornima Tantry
	 */
	
	public int filterOccupation(String occupation)
	{
		
		int count = 0;
		Statement stat = null;
		ResultSet result = null;
		
		if(occupation.length() == 0)
			occupation = null;
		if(occupation.equals("null"))
			occupation = null;
		
		String cmd_OccupationSearch = "SELECT userrecord.ssn, userrecord.name, " 
									+ " job.occupationcode "
									+ " from userrecord, job, occupation where "
									+ " userrecord.ssn = job.ssn and "
									+ " job.occupationcode = occupation.occupationcode and "
									+ " occupation.occupation = ' " + occupation + "'";
		
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
			
			result = stat.executeQuery(cmd_OccupationSearch);
			
			System.out.println("List of customers for given Occupation : " + occupation);
			System.out.printf("%20s |%20s |" ,"NAME", "SSN");
			System.out.println("");
			
			//display data from result set
			while(result.next())
			{
				++count;
				System.out.printf("%s -- %s ", result.getString("name"), result.getString("ssn"));
				System.out.println("");
			}
			
			if(count == 0)
				System.out.println("There are no customers enrolled with this occupation");
			else
				System.out.println("No. of customers with occupation : " + occupation + " is " + count);
			result.close();
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in filterOccupation");
			e.printStackTrace();
		}
		
		return count;
	}
	
	
	/**
	 * This method filters customers based on the marital status and prints the results.
	 * @param status - holds value of the marital status to be compared with
	 * @return count - number of people with the given marital status
	 * @author Poornima Tantry
	 */
	
	public int filterMaritalStatus(String status)
	{
		int count = 0;
		Statement stat = null;
		ResultSet result = null;
		
		if(status.length() == 0)
			status = null;
		if(status.equals("null"))
			status = null;
		
		String cmd_statusSearch = "SELECT * from userrecord where marital = ' " + status + "'";
		
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(cmd_statusSearch);
			
			System.out.println("List of customers for given Marital Status : " + status);
			System.out.printf("%20s |%20s |" ,"NAME", "SSN");
			System.out.println("");
			
			//display data from result set
			while(result.next())
			{
				++count;
				System.out.printf("%s -- %s ", result.getString("name"), result.getString("ssn"));
				System.out.println("");
			}
			
			if(count == 0)
				System.out.println("There are no customers enrolled with this status");
			else
				System.out.println("No. of customers with marital status : " + status + " is " + count);
			result.close();
		}
		catch(Exception e)
		{
			System.out.println("Exception in filterMaritalStatus");
			e.printStackTrace();
		}
		
		return count;
	}
	
	/**
	 * This method accepts a estimated range of income and then finds how
	 * many people are there in this range of income and lists them
	 * @param getIncome - holds estimated income value to be compared with
	 * @return count - number of people in this income range
	 * @author Poornima Tantry
	 */
	
	@TaDaMethod(variablesToTrack = {"dataIncome", "dataIncome", "dataIncome", "dataIncome", "dataIncome"},
			correspondingDatabaseAttribute = {"job.workweeks", "job.weekwage", "investment.capitalgains", 
			"investment.capitallosses", "investment.stockdividends"})
	public int filterEstimatedIncome(String getIncome)
	{
		double income = 0, dataIncome = 0;	
		int count = 0;
		Statement stat = null;
		ResultSet result = null;		
		
		
		income = acc.checkLetterDigit(getIncome, RiskItConstants.INVALID_VALUE);
		
		String cmd_filterIncome = "SELECT  userrecord.name, 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";
			
		try
		{
			Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(cmd_filterIncome);
			
			System.out.println("List of customers for given Estimated Income : " + income);
			System.out.printf("%20s |%20s |" ,"NAME", "SSN");
			System.out.println("");
			
			//display data from result set
			while(result.next())
			{		
				dataIncome = acc.calculateIncome(result.getInt("workweeks"),
												 result.getInt("weekwage"),
												 result.getInt("capitalGains"),
												 result.getInt("capitallosses"),
												 result.getInt("stockdividends"));
				if(Math.floor(dataIncome) == Math.floor(income))
				{
					++count;
					System.out.printf("%s -- %s ", result.getString("name"), result.getString("ssn"));
					System.out.println("");
				}
			}
			
			if(count == 0)
				System.out.println("There are no customers enrolled with this estimated income");
			else
				System.out.println("No. of customers with estimated income : " + income + " is " + count);
			result.close();
		}
		catch(Exception e)
		{
			System.out.println("Exception in filterEstimatedIncome");
			e.printStackTrace();
		}
		return count;
	}	
	
	/**
	 * This method accepts any number of inputs and produces a filtered result of customers
	 * @param prop - arraylist containing properties of user input that will used as filter 
	 * @return count - number of customers present that match the given criteria
	 * @author Poornima Tantry
	 */
	public int browseUserProperties(ArrayList<String> prop)
	{
		int count = 0;
		Statement stat = null;
		ResultSet result = null;
		
		String cmd_generalSearch = "Select * from userrecord where ";
		String cmd_userSQL = "";
		
		boolean checkDataFlag = false;
		for(int i=0; i < prop.size(); ++i)
		{
			if(prop.get(i).length() > 0)
				checkDataFlag = true;
		}
		int index =0;
		
		//check to see which data are available to build the SQL query
		
		if(checkDataFlag)
		{
			//name
			if(prop.get(index).length() > 0)
				cmd_userSQL = cmd_userSQL + "name = '" + prop.get(index) + "'";
			++index;
			
			
			//ssn
			if(prop.get(index).length() > 0)
			{
				String andJoin = "";
				if(cmd_userSQL.length() > 0)
					andJoin = " and ";
				cmd_userSQL = cmd_userSQL + andJoin + " ssn = " + prop.get(index) ;
				
			}
			++index;
			
			//age
			if(prop.get(index).length() > 0)
			{
				String andJoin = "";
				if(cmd_userSQL.length() > 0)
					andJoin = " and ";
				cmd_userSQL = cmd_userSQL + andJoin + " age = " + prop.get(index) ;
				
			}
			++index;
			
			//sex
			if(prop.get(index).length() > 0)
			{
				String andJoin = "";
				if(cmd_userSQL.length() > 0)
					andJoin = " and ";
				
				cmd_userSQL = cmd_userSQL + andJoin + " sex = ' " + prop.get(index) + "'";
				
			}
			++index;
			
			//marital-status
			if(prop.get(index).length() > 0)
			{
				String andJoin = "";
				if(cmd_userSQL.length() > 0)
					andJoin = " and ";
				
				cmd_userSQL = cmd_userSQL + andJoin + " marital = ' " + prop.get(index) + "'";
				
			}
			++index;
			
			//race
			if(prop.get(index).length() > 0)
			{
				String andJoin = "";
				if(cmd_userSQL.length() > 0)
					andJoin = " and ";
				
				cmd_userSQL = cmd_userSQL + andJoin + " race = ' " + prop.get(index) + "'";			
			}
			++index;
			
			//country
			if(prop.get(index).length() > 0)
			{
				String andJoin = "";
				if(cmd_userSQL.length() > 0)
					andJoin = " and ";
				cmd_userSQL = cmd_userSQL + andJoin + " birthcountry = ' " + prop.get(index) + "'";
				
			}
			++index;	
			
			cmd_generalSearch = cmd_generalSearch +  cmd_userSQL;
				
			try
			{
				Connection conn = DatabaseConnection.getConnection();
				stat = conn.createStatement();
				result = stat.executeQuery(cmd_generalSearch);						
				
				while(result.next())
				{
					++count;
					
					System.out.println(   result.getString("name")+ "->" 
										+ result.getInt("ssn") + "->" 
										+ result.getString("age") + "->"
										+ result.getString("sex") + "->"
										+ result.getString("marital") + "->"
										+ result.getString("race") + "->"
										+ result.getString("birthcountry"));				
				}
				result.close();
			}					
			catch(Exception e)
			{
				System.out.println("Exception in browseUserProperties");
				e.printStackTrace();
			}
		}
		else
			System.out.println("There is no data to filter out customers");
		
		return count;
	}
	
	/**
	 * This method calculates a quote for a customer, when they choose to find
	 * quotes by themselves. This method will accept the net worth of the property
	 *  with all expenses and and deductibles and gives a value.
	 * @param ssn - SSN of the customer
	 * @param deductible - deductible that customer wishes to pay
	 * @param netWorth - worth of all of property
	 * @param userList - list of all customers, if available , to match data
	 * @return calc_invite - user object with all calculated values
	 * @author Poornima Tantry
	 */
	
	public Invitation calculateQuoteForCustomer(long ssn, double deductible, 
											double netWorth, 
											ArrayList<Invitation> userList)
	{
		double quote = 0;
		Invitation inv = new Invitation();
		Invitation calc_invite = new Invitation();
		int deduct_list[] = {100, 250, 500,1000, 1500, 2000, 2500, 5000, 10000};
		boolean checkFlag = false;
		
		//check to see if customer is already present in lsit
		for(int i=0; i < userList.size(); ++i)
		{
			System.out.println("flow:10");
			inv = userList.get(i);
			if(inv.getUserSSN() == ssn)
			{
				System.out.println("flow:11");
				checkFlag = true;
				break;
			}
		}
		
		if(checkFlag)
		{
			System.out.println("flow:12");
			System.out.println("You have received $" + inv.getPremium() +
					" for the property value of " + inv.getPropertyValue() +
					" and deductible of $" + inv.getDeductible() + "\n\n");
		}
		System.out.println("For the expenses that you have provided, the quote will be ...");
			
		for(int i=0; i < deduct_list.length; ++i)
		{
			System.out.println("flow:13");
			//set all the values
			calc_invite.setUserSSN(ssn);
			calc_invite.setDeductible(deduct_list[i]);
			calc_invite.setPropertyValue(netWorth);
			
			//assume the property exposure unit is 30% of the property value
			calc_invite.setExpoUnit(netWorth * RiskItConstants.THIRTY_PERCENT/100);
			
			//assume loss coverage is 50% of the total expense and property value
			calc_invite.setlossCoverage(netWorth * RiskItConstants.FIFTY_PERCENT/100);
			
			//calculate the score of customer
			double score = agent.calculateScore(ssn);
			calc_invite.setScore(score);
			
			//calculate the final quote
			quote = agent.calculateTotalValue(calc_invite);
			calc_invite.setPremium(quote);
			
			System.out.println("The quote for deductible of $" + deduct_list[i] + " is : $" + quote + " per month.");
		}
		System.out.println("---------------------------------------------------");
		return calc_invite;
	}
	
	public double updatewagetable(int industrycode,int occupcode){
		int sum = 0;		
		int count_industrycode = 0;
		int count = 0;
		double average = 0;
		int meanweekwage = 0;
		Statement stat = null;
		ResultSet result = null;
		
		try
        {
        	
            Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
            stat=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
          
            String select_occupcode = "select  wage.occupationcode,wage.industrycode,weekwage from wage,job where wage.occupationcode = job.occupationcode and wage.industrycode = job.industrycode and wage.occupationcode = "  + occupcode + " and wage.industrycode = " + industrycode+ " group by wage.occupationcode,wage.industrycode,weekwage";
            String select_industrycode = "select  industrycode from wage where industrycode = " + industrycode +"";         
            String query = "select meanweekwage,weekwage from job,wage where wage.occupationcode = job.occupationcode and wage.industrycode = job.industrycode and wage.industrycode = " + industrycode +" and wage.occupationcode =  " + occupcode +" group by wage.occupationcode,wage.industrycode,weekwage,meanweekwage";    
            String updatetable = "select industrycode, occupationcode,meanweekwage from wage where industrycode = " + industrycode +" and occupationcode = " + occupcode+"";
            
            
           // Total number of occupation code
            result = stat.executeQuery(select_occupcode);
            while(result.next())
        	      count++;
                  //System.out.println(count);
                            
           result = stat.executeQuery(select_industrycode);
          //Total number of industry code
           while(result.next())
        	    count_industrycode++;
                       
          result = stat.executeQuery(query);
          while(result.next())
               sum = sum + (result.getInt("weekwage"));            
               average = (double)sum/(double)count;             
               System.out.println("Industrycode" + "  OccupationCode" +"     Sum" +"      Average");
               System.out.println( industrycode + "              " + "  " + occupcode + "           " + sum +"       " +  average);
       
          if (result.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
              System.out.println("ResultSet non-updatable.");
          } else {
             System.out.println("ResultSet updatable.");
          }
          result = stat.executeQuery(updatetable);
          result.beforeFirst();         
         while( result.next()){      
               result.updateDouble("meanweekwage",average);    	
               result.updateRow();       
        }
        
      acc.printRs(result);
       // result.close();
      
        System.out.println("-------------------------------------------------------------------");               
         System.out.println("meanweekwage is updated for industry code: " + industrycode +  " and  Occupation code: " + occupcode + " Old value for meanweekwage is:  " + meanweekwage + " and new value for meanweekwage is:  " + (int)average );   
        stat.close();
           
        }
        catch(Exception e){
         
            e.printStackTrace();
        }
    
		return average;
	}
	
	public double updatetable(long ssn,int industrycode,int occupationcode){
		int sum = 0;		
		int count_industrycode = 0;
		int count = 0;
		int countwage = 0;
		double average = 0;
		Statement stat = null;
		ResultSet result = null;
		try
        {
			Connection conn = DatabaseConnection.getConnection();
			stat = conn.createStatement();
            stat=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
          
            String select_occupcode = "select  wage.occupationcode,wage.industrycode,weekwage from wage,job where wage.occupationcode = job.occupationcode and wage.industrycode = job.industrycode and wage.industrycode = " + industrycode+" and wage.occupationcode = " + occupationcode+"  group by wage.occupationcode,wage.industrycode,weekwage";
            String select_industrycode = "select  industrycode from wage ";         
            String query = "select meanweekwage,weekwage from job,wage where wage.occupationcode = job.occupationcode and wage.industrycode = job.industrycode and wage.industrycode = " + industrycode+" and wage.occupationcode = " + occupationcode+"   group by wage.occupationcode,wage.industrycode,weekwage,meanweekwage";    
            String updatetable = "select industrycode, occupationcode,meanweekwage from wage where industrycode = " + industrycode+" and occupationcode = " + occupationcode+" ";
            String wage = "select wage.industrycode,wage.occupationcode,job.ssn,weekwage,unemploymentreason from job,wage,employmentstat where job.ssn = employmentstat.ssn and wage.industrycode = job.industrycode and wage.occupationcode = job.occupationcode  and job.ssn = " +ssn+""; //group by job.industrycode,job.occupationcode,job.ssn,weekwage,unemploymentreason";
            
           // Total number of occupation code
            result = stat.executeQuery(select_occupcode);
            while(result.next())
        	   count++;
               System.out.println( "Total number of occupation code and industrycode " + count);
               result.close();             
           result = stat.executeQuery(select_industrycode);
         //Total number of industry code
           while(result.next())
        	    count_industrycode++;
                      
            result = stat.executeQuery(query);
            while(result.next())
        	  sum = sum + (result.getInt("weekwage"));     
          double oldaverage = (double)sum/(double)(count-1);
              average = (double)sum/(double)(count);    
              
              System.out.println("ssn "+  ssn + "  Sum  " + sum +" Average " +  average  ) ;            
              result = stat.executeQuery(wage);
            
            while(result.next()){
               countwage++;
               int weekwage = result.getInt("weekwage");
               String reason = result.getString("unemploymentreason");
               //int ssn = result.getInt(3);
               System.out.println("Number of row  "  + countwage + "  ssn  "  +  ssn + "  weekwage  " +  weekwage+ "   unemploymentreason  " + reason);
            }
            	 
              
             if (result.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
                 System.out.println("ResultSet non-updatable.");
             } else {
               System.out.println("ResultSet updatable.");
             }
             
            result = stat.executeQuery(updatetable);
            result.beforeFirst();         
            while( result.next()){      
             result.updateDouble("meanweekwage",average);   	
             result.updateRow();       
           }
        
        acc.printRs(result);
      
        System.out.println("-------------------------------------------------------------------");               
        System.out.println("meanweekwage is updated for  ssn  = " + ssn + " industry code: " + industrycode +  " and  Occupation code: " + occupationcode + " Old value for meanweekwage is:  " + (int)oldaverage + " and new value for meanweekwage is:  " + (int)average );   
        stat.close();
      
       }
        catch(Exception e){
         
            e.printStackTrace();
        }
		
		return average;
	}
	
	public double userinformation(long ssn,int industrycode,int occupationcode){
		int sum = 0;		
		int count_industrycode = 0;
		int count = 0;
		int countwage = 0;
		double average = 0;
		Statement stat = null;
		ResultSet result = null;
		 try
	        {
			    Connection conn = DatabaseConnection.getConnection();				
	            stat = conn.createStatement();
	            stat=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
	                                      ResultSet.CONCUR_UPDATABLE);
	          
	            String select_occupcode = "select  wage.occupationcode,wage.industrycode,weekwage from wage,job where wage.occupationcode = job.occupationcode and wage.industrycode = job.industrycode and wage.industrycode = " + industrycode+" and wage.occupationcode = " + occupationcode+"  group by wage.occupationcode,wage.industrycode,weekwage";
	            String select_industrycode = "select  industrycode from wage ";         
	            String query = "select meanweekwage,weekwage from job,wage where wage.occupationcode = job.occupationcode and wage.industrycode = job.industrycode and wage.industrycode = " + industrycode+" and wage.occupationcode = " + occupationcode+"   group by wage.occupationcode,wage.industrycode,weekwage,meanweekwage";    
	            String updatetable = "select ssn,workclass,industrycode, occupationcode,unionmember,employersize,weekwage,selfemployed,workweeks from job where industrycode = " + industrycode+" and occupationcode = " + occupationcode+" and job.ssn = " + ssn+ "";
	            String wage = "select job.industrycode,job.occupationcode,job.ssn,weekwage,unemploymentreason from job,wage,employmentstat where job.ssn = employmentstat.ssn and wage.industrycode = job.industrycode and wage.occupationcode = job.occupationcode  and job.ssn = " +ssn+""; //group by job.industrycode,job.occupationcode,job.ssn,weekwage,unemploymentreason";
	            
	           // Total number of occupation code
	            result = stat.executeQuery(select_occupcode);
	            while(result.next())
	        	      count++;
	                  System.out.println( "Total number of occupation code and industrycode " + count);
	                              
	           result = stat.executeQuery(select_industrycode);
	          //Total number of industry code
	           while(result.next())
	        	     count_industrycode++;
	                          
	           result = stat.executeQuery(query);
	           while(result.next())
	        	    sum = sum + (result.getInt("weekwage"));            
	                average = (double)sum/(double)count;             
	                System.out.println("ssn "+  ssn + "  Sum  " + sum +" Average " +  average  ) ;           
	           result = stat.executeQuery(wage);
	           while(result.next()){
	            	 countwage++;
	            	 int weekwage = result.getInt("weekwage");
	            	 String reason = result.getString("unemploymentreason");
	            	 //int ssn = result.getInt(3);
	            	 System.out.println("Number of row  "  + countwage + "  ssn  "  +  ssn + "  weekwage  " +  weekwage+ "   unemploymentreason  " + reason);
	          }
	            	               
	          if (result.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
	              System.out.println("ResultSet non-updatable.");
	          } else {
	              System.out.println("ResultSet updatable.");
	          }
	          result = stat.executeQuery(updatetable);
	          result.beforeFirst();  
	          while(result.next()){       	  
	        	  int weekwage =( result.getInt("weekwage"));
	        	  // System.out.println( " ," + weekwage);
	        	  result.updateInt("weekwage",weekwage);         	
	              result.updateRow();
	          }
	               
	        acc.printResult(result);
	        
	        System.out.println("-------------------------------------------------------------------");               
	        stat.close();
	       
	        }
	        catch(Exception e){
	        
	            e.printStackTrace();
	        }
			
		return average;
	}
	
	
	public int updatestability(String zipcode,int industrycode,int occupationcode){
		int count = 0;
		int numcount = 0;
		int stability = 0;
		Statement stat = null;
		ResultSet result = null;
		try{
			Connection conn = DatabaseConnection.getConnection();
            stat = conn.createStatement();
            stat=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
               
            String table = "SELECT  distinct userrecord.ssn,ziptable.zip  FROM  ziptable, userrecord,job "
				+ " WHERE ziptable.zip = '" + zipcode + "' and industrycode = " + industrycode + " and occupationcode = " + occupationcode + " and  ziptable.zip = userrecord.zip and job.ssn = userrecord.ssn";
            String cmd_getAllUnemployed = "SELECT distinct employmentStat.ssn,ziptable.zip, employmentStat.unemploymentreason "
				+ " FROM ziptable, employmentStat, userrecord ,job"
				+ " WHERE job.ssn = employmentstat.ssn " 
				+ "and employmentstat.unemploymentreason <> 'null' "
				+ "and ziptable.zip = '" + zipcode + "' "
				+ "and industrycode = " + industrycode + ""
				+ "and occupationcode = " + occupationcode + ""
				+ "and ziptable.zip = userrecord.zip";
           
			String industry = "select industrycode,industry,stability from industry where industrycode = " + industrycode +"";		
            String occupation = "select occupationcode ,occupation,stability from occupation where occupationcode = " + occupationcode + "";
            
            result = stat.executeQuery(table);
            while(result.next()){			
			     int ssn = result.getInt(1);						
			     //System.out.println(ssn + "," + zip  );			
		}
            result = stat.executeQuery(table);
            while(result.next())
            	numcount++;
                System.out.println("Total number of people working in zipcode " + zipcode + " is " + numcount );    
		
		
		    result = stat.executeQuery(cmd_getAllUnemployed);
		    while(result.next()){			
			      String reason = result.getString(3);
			      int ssn = result.getInt(1);
			      //System.out.println( ssn + "," + zip  + "," + reason );
			      //System.out.println("=============");
	   	}
		    result = stat.executeQuery(cmd_getAllUnemployed);
		    while(result.next())
			     count++;
		         System.out.println("Total number of unemployed  people in zipcode " + zipcode +  " is " + count);
		         System.out.println("=======================================");
		         int  sum = count + numcount;
		
		  if (result.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
            System.out.println("ResultSet non-updatable.");
          } else {
            System.out.println("ResultSet updatable.");
          }
			System.out.println("=======================================");
			
		   result = stat.executeQuery(industry);
		   result.beforeFirst();
		   while(result.next()){
			   if(sum == 0){
					  stability = 0;
				   }else{
				   
		             stability = (numcount*10/(sum));
				   }
	            
	             // int indu_code = result.getInt(1);
	             // String indus = result.getString(2);
	             //System.out.println(indu_code + "," + indus + "," + stability);
	             result.updateInt("stability",stability);   	
                 result.updateRow();
		}
		acc.printIndustry(result);
		System.out.println("=======================================");
		  result = stat.executeQuery(occupation);
		  result.beforeFirst();
		  while(result.next()){
			  if(sum == 0){
				  stability = 0;
			   }else{
			   
	             stability = (numcount*10/(sum));
			   }
	             //stability = (numcount*10/(count+numcount));
	           //int occupcode = result.getInt(1);
	           //String occup = result.getString(2);
	           // System.out.println(occupcode + "," +  occup +  "," + stability);
	           result.updateInt("stability",stability);   	
               result.updateRow();
		}
		acc.printOccupation(result);
		System.out.println("=======================================");
		
		stat.close();
       
		}catch(SQLException e){
			e.printStackTrace();
		
		}
		return stability;
	}
	 public int FindTopOccupationCode( String statename) {
		 Statement stat = null;
		 ResultSet result = null;
	     

	      try{
	    	  Connection conn = DatabaseConnection.getConnection();
	     	    stat = conn.createStatement();
	        result = stat.executeQuery(     
	             
	        "select occupationcode from job,userrecord,ziptable where userrecord.zip = ziptable.zip and userrecord.ssn = job.ssn and statename = '" + statename +"' group by occupationcode,employersize,statename");
	       if (!result.next())
	        {
	                //reportFailure("No rows in ResultSet");
	                //return 1;
	        }
	        
	        FindTop5Occupationcode(result);
	      } catch (SQLException e)
	        {
	           e.printStackTrace();
	      } 
	      return 5;
	   }

	 @TaDaMethod(variablesToTrack = {"code"},
			 correspondingDatabaseAttribute = {"job.occupationcode"})
	   private void FindTop5Occupationcode(ResultSet rs){
	      
	     int[] resultCount = new int[50];
	     int[] resultStates= new int[50];
	     int resultIndex = 0;
	     int stateIndex =0;
	     try{
	       do{	          
	    	  int code = rs.getInt(1);
	          stateIndex = acc.getOccupation(resultStates, code);
	          if( stateIndex < 50){	            
	            resultCount[stateIndex]++;	            
	          }
	          else{	            
	            resultCount[resultIndex]++;
	            resultStates[resultIndex] = code;
	            resultIndex++;
	          }         
	          
	       }while(rs.next());
	       FindBestoccupationcode(5, resultStates, resultCount, resultIndex, " Number of workers: ");
	       
	      } catch (SQLException e)
	        {
		   e.printStackTrace();     
	      }     
	      
	    }

	   public void FindBestoccupationcode(int numResults, int[] resultStates, int[] resultValues, int resultIndex, String text){

	       int maxValue =0;
	       int maxIndex =0;
	       int j=0;
	       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){
	            System.out.println("OccupationCode: " + resultStates[maxIndex] + text + maxValue);
	            System.out.println("");
	            maxValue = 0;
	            resultValues[maxIndex] = 0;
	          }
	       }
	      
	    }
	    public int FindTopIndustryCode(String statename) {
	    	Statement stat = null;
			 ResultSet result = null;
		      try{
		    	  Connection conn = DatabaseConnection.getConnection();
		     	  stat = conn.createStatement();
		          result = stat.executeQuery(     
		               "select industrycode from job,userrecord,ziptable where userrecord.zip = ziptable.zip and userrecord.ssn = job.ssn and statename = '" + statename +"' group by industrycode,employersize,statename");
		       if (!result.next())
		        {
		                //reportFailure("No rows in ResultSet");
		                //return;
		        }
		        
		        FindTop5Industrycode(result);
		      } catch (SQLException e)
		        {
		           e.printStackTrace();
		      } 
		      return 5;
		   }

	    @TaDaMethod(variablesToTrack = {"code"},
				 correspondingDatabaseAttribute = {"job.industrycode"})
		   private void FindTop5Industrycode(ResultSet rs){
		      
		     int[] resultCount = new int[50];
		     int[] resultStates= new int[50];
		     int resultIndex = 0;
		     int stateIndex =0;
		     try{
		       do{		          
		    	  int code = rs.getInt(1); 
		          stateIndex = acc.getIndustryCode(resultStates, code);
		          if( stateIndex < 50){	            
		            resultCount[stateIndex]++;		            
		          }
		          else{		            
		            resultCount[resultIndex]++;
		            resultStates[resultIndex] = code;
		            resultIndex++;
		          }         
		          
		       }while(rs.next());
		       FindBestIndustrycode(5, resultStates, resultCount, resultIndex, " Number of workers: ");
		       
		      } catch (SQLException e)
		        {
			   e.printStackTrace();     
		      }     
		      
		    }
    private void FindBestIndustrycode(int numResults, int[] resultStates, int[] resultValues, int resultIndex, String text){

		       int maxValue =0;
		       int maxIndex =0;
		       int j=0;
		        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){
		            System.out.println("IndustryCode: " + resultStates[maxIndex] + text + maxValue);
		            System.out.println("");
		            maxValue = 0;
		            resultValues[maxIndex] = 0;
		          }
		       }
		    }    
	    
	    
	    
	    
	    
	    
	    
	    
	    
	    
	    
}