15/11 – 30/11 java 進行曲

import java.io.*;
import java.sql.*;
import java.awt.*;
import java.util.*;
import java.text.*;
import java.lang.*;


class PowerMarket
{
 private BufferedReader in;
 private Connection con;
 private Statement stmt;
 private ResultSet rs;
 private int dream;
 private String choose;
 private PowerUser user;
 private PreparedStatement pstmt;
 private String street;
 private String city;
 private String country;
 private float pay2 = 0;
 private final int size = 1000;
 SimpleDateFormat fhour;
 SimpleDateFormat fmin;


 /**********************************************************************************************************************
  * Name   : main class
  * Parameter : None
  * Usage  : new an object when the program start.  Caught any Exception.
  * Return  : None
 **********************************************************************************************************************/
 public static void main(String[] args)
 {
  PowerMarket dbm;


  try
  {
   dbm = new PowerMarket();
   dbm.close();
  } catch (ClassNotFoundException e)
  {
   System.err.println(“Caught ClassNotFoundException: ” +
     e.getMessage());
  } catch (SQLException e)
  {
   System.err.println(“Caught SQLException: “);
   do
   {
    System.err.println(“Message: ” + e.getMessage());
    System.err.println(“SQL State: ” + e.getSQLState());
    System.err.println(“Error Code: ” + e.getErrorCode());
    e = e.getNextException();
   } while (e != null);
  } catch (FileNotFoundException e)
  {
   System.err.println(“Caught FileNotFoundException: ” +
     e.getMessage());
  } catch (IOException e)
  {
   System.err.println(“Caught IOException: ” + e.getMessage());
  }
 }


 public void close() throws SQLException
 {
  con.close();
  stmt.close();
 }


 /**********************************************************************************************************************
  * Name   : Constructor
  * Parameter : None
  * Usage  : Connect to our database and go to login page.
  * Return  : None
 **********************************************************************************************************************/
 public PowerMarket()
  throws ClassNotFoundException, IOException, SQLException
  {
   Class.forName(“oracle.jdbc.driver.OracleDriver”);
   in = new BufferedReader(new InputStreamReader(System.in));
   con = DriverManager.getConnection(“jdbc:oracle:thin:@db00.cse.cuhk.edu.hk:1521:db00″,”db095″,”mclee!”);
   stmt = con.createStatement();
   fhour = new SimpleDateFormat(“HH”);
   fmin = new SimpleDateFormat(“mm”);
   trimDown();
   welcome();
  }


 /**********************************************************************************************************************
  * Name   : alter
  * Parameter : None
  * Usage  : This is Alter User Page.  It lets user to choose the function of Alter User Page and link to other page.
  * Return  : None
 **********************************************************************************************************************/
 public void alter() throws SQLException, IOException, NumberFormatException
 {
  System.out.println(” Alter Pagenn”);
  System.out.println(“What do you want, ” + user.getUserName() + “?”);
  System.out.println(“1. Show my infoamtion”);
  System.out.println(“2. Update my infomation”);
  System.out.println(“3. Go to Query Auction Page”);
  System.out.println(“4. Go to Create Auction Page”);
  System.out.println(“5. Go to Statistic Page”);
  System.out.println(“6. Logout”);
  System.out.println(“7. Sleep”);
  try{
   dream = Integer.parseInt(in.readLine().trim());
   blank();
  }
  catch(NumberFormatException e){
   System.err.println(“You should enter either 1, 2, 3, 4 or 5”);
   alter();
   return;
  }
  switch(dream){
   case 1:
    pstmt = con.prepareStatement(“SELECT DISTINCT u.name, u.email, u.credit_card, u.phone from users u  where u.email = ?”);
    pstmt.setString(1, user.getUserEmail());
    rs = pstmt.executeQuery();
    if(rs.next()){
     userHeader(rs);
     alter();
    }
    pstmt.close();
    break;
   case 2:
    updateUser();
    break;
   case 3:
    query();
    break;
   case 4:
    creAuct();
    break;
   case 5:
    stat();
    break;
   case 6:
    welcome();
    break;
   case 7:
    System.out.println(“Bye Bye, have a good sleep”);
    break;
   default:
    System.out.println(“Sorry, we don’t have so many function”);
    alter();
  }
 }


 /**********************************************************************************************************************
  * Name   : updateUser
  * Parameter : None
  * Usage  : This is one of the function of ALter User page.  It lets user to choose which personal details to change.
  * Return  : None
 **********************************************************************************************************************/
 public void updateUser() throws IOException, SQLException{
  int dream2;
  String temp = “”;
  System.out.println(“Which column you want to update?”);
  System.out.println(“1. Name”);
  System.out.println(“2. Credit Card”);
  System.out.println(“3. Phone”);
  System.out.println(“4. Address”);
  System.out.println(“5. Interest”);
  try{
   dream2 = Integer.parseInt((temp = in.readLine().trim()));
  }
  catch(NumberFormatException e){
   if(temp.equals(“”)){
    alter();
    return;
   }
   System.err.println(“You should enter either 1, 2, 3, 4, 5, 6, 7, 8”);
   updateUser();
   return;
  }
  pstmt = con.prepareStatement(“SELECT * from users where email = ?”);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  rs.next();
  switch(dream2){
   case 1:
    System.out.println(“You don’t like your name ‘” + user.getUserName() + “‘.  So what name would you like to use? (leave blank to quit)”);
    while((temp = in.readLine().trim()).equals(“”)){
     updateUser();
     return;
    }
    pstmt =  con.prepareStatement(“UPDATE users set name = ? WHERE email = ?”);
    pstmt.setString(1, temp);
    pstmt.setString(2, user.getUserEmail());
    pstmt.executeUpdate();
    user.setUserName(temp);
    System.out.println(“nUpdate Successn”);
    updateUser();
    break;
   case 2:
    System.out.println(“Woo, you have so many credit card. Your original credit card number is ” + rs.getString(3) + “. What is you new credit card number? (leave blank to quit”);
    while(!checkCreditCardNumber(temp = in.readLine().trim())){
     if(temp.equals(“”)){
      updateUser();
      return;
     }
     System.out.println(“Tell me the truth.  What is your new credit card number?”);
    }
    pstmt =  con.prepareStatement(“UPDATE users set credit_card = ? WHERE email = ?”);
    pstmt.setString(1, temp);
    pstmt.setString(2, user.getUserEmail());
    pstmt.executeUpdate();
    System.out.println(“nupdate successn”);
    updateUser();
    break;
   case 3:
    if(rs.getString(4)==null){
     System.out.println(“Finally you have a phone now. What is your new phone number?”);
    }
    else{
     System.out.println(“Your original phone number is ” + rs.getString(4) + “. What is your new phone number?”);
    }
    if(!checkPhoneNumber(temp = in.readLine().trim())){
     do{
      if(temp.equals(“”)){
       break;
      }
      else{
       System.out.println(“Don’t fool me, the number is fake.  What is your new phone number?”);
      }
     }while(!checkPhoneNumber(temp = in.readLine().trim()));
    }
    pstmt =  con.prepareStatement(“UPDATE users set phone = ? WHERE email = ?”);
    pstmt.setString(1, temp);
    pstmt.setString(2, user.getUserEmail());
    pstmt.executeUpdate();
    System.out.println(“Update Success”);
    updateUser();
    break;
   case 4:
    pstmt = con.prepareStatement(“SELECT street, city, country from address where email = ? “);
    pstmt.setString(1, user.getUserEmail());
    rs = pstmt.executeQuery();
    if(rs.next()){
     addressHeader(rs);
     updateAddress();
    }
    else{
     while(true){
      System.out.println(“You don’t have an address. Do you want to add an address?(y/n)”);
      String yn = in.readLine().trim();
      if(yn.equals(“y”)){
       int num;
       if(addAddress()){
        updateAddress();
       }
       else{
        updateUser();
       }
       break;
      }
      else if(yn.equals(“n”)){
       updateUser();
       break;
      }
      else{
       System.out.println(“You should enter y or n”);
      }
     }
    }
    break;
   case 5:
    updateInterest();
    break;
   case 6:
    alter();
    break;
   default:
    System.out.println(“Sorry, we do not have so many function.”);
    updateUser();
  }
 }


 /**********************************************************************************************************************
  * Name   : query
  * Parameter : None
  * Usage  : This is Query Auction Page.  It lets user to choose the function on Query Auction to run,
        link to other pages, logout and exit.
  * Return  : None
 **********************************************************************************************************************/
 public void query() throws SQLException,IOException,NumberFormatException
 {
  System.out.println(” Query Pagen”);
  System.out.println(“How do you check the auction?”);
  System.out.println(“1. By field ID”);
  System.out.println(“2. By current between a range”);
  System.out.println(“3. By specific no. of days remain opening”);
  System.out.println(“4. By a list of item names”);
  System.out.println(“5. I am Seller”);
  System.out.println(“6. I am Watcher or a Bidder”);
  System.out.println(“7. By email of seller”);
  System.out.println(“8. By email of watcher or bidder”);
  System.out.println(“9. By item to search other item in the category”);
  System.out.println(“10.I like it”);
  System.out.println(“11.Bid”);
  System.out.println(“12.Watch”);
  System.out.println(“13.Go to Alter Existing User Page”);
  System.out.println(“14.Go to Create Auction Page”);
  System.out.println(“15.Go to Statistics Page”);
  System.out.println(“16.Logout”);
  System.out.println(“17.Quit”);


  try{
   dream = Integer.parseInt(in.readLine().trim());
   blank();
  }
  catch(NumberFormatException e){
   blank();
   System.out.println(“You must enter either 1,2,3,4,5,6,7,8,9,10 of 11”);
   System.out.println(“Press ENTER to continue”);
   in.readLine();
   blank();
   query();
   return;
  }
  switch(dream){
   case 1:
    String id;
    do{
     System.out.println(“What auction id do you want to search?”);
     id = in.readLine().trim();
     if(id.equals(“”)){
      break;
     }
    }while(!checkID(id));
    if(!id.equals(“”)){
     rs = stmt.executeQuery(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), to_char(a.close, ‘YYYYMMDD’), a.seller from auction a, open o where a.id = o.id and a.id = ‘” + id + “‘ order by a.id desc”);
     if(rs.next()){
      openHeader(rs);
      rs.close();
     }
     else{
      blank();
      System.out.println(“The auction belongs to this ID is closed.  Please come earlier”);
      System.out.println(“Press ENTER to continue”);
      in.readLine().trim();
      blank();
     }
    }
    query();
    break;
   case 2:
    String upCurrent;
    String lowCurrent;
    while(true){
     System.out.println(“What is the upper bound and lower bound of the current you want to search?”);
     upCurrent = in.readLine().trim();
     lowCurrent = in.readLine().trim();
     if(upCurrent.equals(“”) && lowCurrent.equals(“”)){
      blank();
      break;
     }
     if((upCurrent.equals(“”) && !lowCurrent.equals(“”)) || (!upCurrent.equals(“”) && lowCurrent.equals(“”))){
      System.out.println(“You must fill a pair of price”);
     }
     else if(checkCurrent(upCurrent) && checkCurrent(lowCurrent)){
      if(Float.parseFloat(upCurrent) < Float.parseFloat(lowCurrent)){
       String temp = lowCurrent;
       lowCurrent = upCurrent;
       upCurrent = temp;
      }
      break;
     }
    }
    if(!lowCurrent.equals(“”)){
     rs = stmt.executeQuery(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o where a.id = o.id and o.current_price < ” + upCurrent + ” and o.current_price > ” + lowCurrent + “order by a.id desc”);
     if(!rs.next()){
      blank();
      System.out.println(“There are no auction which value is in between the values you specified”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
      blank();
     }
     else{
      openHeader(rs);
      rs.close();
     }
    }
    query();
    break;
   case 3:
    while(true){
     System.out.println(“Please enter the no. of days”);
     String temp = in.readLine().trim();
     try{
      dream = Integer.parseInt(temp);
      break;
     }catch(NumberFormatException e){
      if(temp.equals(“”)){
       blank();
       query();
       return;
      }
      System.out.println(“you should enter a number”);
     }
    }
    pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o ,time t where a.id = o.id and o.open < t.day and a.close > t.day + ? order by a.id desc”);
    pstmt.setString(1,Integer.toString(dream));
    rs = pstmt.executeQuery();
    if(!rs.next()){//no auction chosen
      blank();
      System.out.println(“There are no auction which dead after the number of days specified”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
      blank();
    }
    else{
     openHeader(rs);//have auction chosen
     rs.close();
    }
    System.out.println();
    query();//back to query page
    break;
   case 4:
    System.out.println(“Please enter the name of the item you want”);
    String istring;
    istring = (“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller FROM auction a, open o where a.id = o.id  and a.id IN (“);
    String temp;
    while(!(temp = in.readLine().trim()).equals(“”)){
     String temp2 = (“”);
     for(int i = 0; i<temp.length() ; i++){
      temp2 += temp.charAt(i);
      if((temp.charAt(i)) == (”’)){
       temp2 += temp.charAt(i);
      }
     }  
     istring += (“SELECT DISTINCT id from consist where item = ‘” + temp2 +”‘ INTERSECT ” );
    }
    istring += (“SELECT DISTINCT id from consist) order by a.id desc”);
    rs = stmt.executeQuery(istring);
    if(rs.next()){
     openHeader(rs);
    }
    else{
      blank();
      System.out.println(“No auction selected”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
      blank();
    }
    query();
    break;
   case 5:
    pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o where a.id = o.id and a.seller = ? order by a.id desc”);
    pstmt.setString(1, user.getUserEmail());
    rs = pstmt.executeQuery();
    if(!rs.next())
    {
      blank();
      System.out.println(“Sorry, you sell nothing now”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
    }
    else{
     openHeader(rs);
     rs.close();
    }
    query();
    break;


   case 6:
    pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o, watch w, bid b where a.id = o.id and ((o.id = w.id and w.email = ?) or (o.id = b.id and b.email = ?))”);
    pstmt.setString(1, user.getUserEmail());
    pstmt.setString(2, user.getUserEmail());
    rs = pstmt.executeQuery();
    if(!rs.next()){
     System.out.println(“Sorry, you are not a watcher or a bidder”);
    }
    else{
     openHeader(rs);
     rs.close();
    }
    query();break;


   case 7:
    String semail;
    do{
     System.out.println(“Please enter the email of the seller you want to search”);
     semail = in.readLine().trim();
     if(semail.equals(“”)){
      break;
     }
    }while(!checkSEmail(semail));
    if(!semail.equals(“”)){
    pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o where a.id = o.id and a.seller = ? order by a.id desc”);
     pstmt.setString(1, semail);
     rs = pstmt.executeQuery();
     if(rs.next()){
      openHeader(rs);
      rs.close();
     }
     else{
      blank();
      System.out.println(“The seller don’t sell any auction now”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
     }
    }
    query();break;


   case 8:
    String wbemail;
    do{
     System.out.println(“Please enter the email of the watcher or bidder you want to search”);
     wbemail = in.readLine().trim();
     if(wbemail.equals(“”)){
      blank();
      break;
     }
    }while(!checkWBEmail(wbemail));
    if(!wbemail.equals(“”)){
    pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o, watch w, bid b where a.id = o.id and ((o.id = w.id and w.email = ?) or (o.id = b.id and b.email = ?))”);
     pstmt.setString(1, wbemail);
     pstmt.setString(2, wbemail);
     rs = pstmt.executeQuery();
     if(rs.next()){
      openHeader(rs);
      rs.close();
     }
     else{
      blank();
      System.out.println(“The watcher or bidder don’t bid or watch any item now.”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
     }
    }
    query();
    break;
   case 9:
    String category;
    do{
     System.out.println(“What category are you interested in ?”);
     category = in.readLine().trim();
     if(category.equals(“”)){
      blank();
      break;
     }
    }while(!checkCategory(category));
    if(!category.equals(“”)){
     pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o, consist c, classify y where a.id = o.id and a.id = c.id and c.item = y.name and y.class = ? order by a.id desc”);
     pstmt.setString(1, category);
     rs = pstmt.executeQuery();
     if(rs.next()){
      openHeader(rs);
      rs.close();
     }
     else{
      blank();
      System.out.println(“This category don’t have any open item”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
     }
    }
    query();
    break;


   case 10:
     pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller from auction a, open o, consist c, classify y, interest n where a.id = o.id and a.id = c.id and c.item = y.name and y.class = n.interest and n.email = ? order by a.id desc”);
    pstmt.setString(1, user.getUserEmail());
    rs = pstmt.executeQuery();
    if(!rs.next()){
      blank();
     System.out.println(“You get no interest on our auction”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
    }
    else{
     openHeader(rs);
     rs.close();
    }
    query();
    break;
   case 11:
    pstmt = con.prepareStatement(“SELECT hour FROM time where hour > 8 and hour < 17”);
    rs = pstmt.executeQuery();
    if(rs.next()){
     bid();
    }
    else{
     blank();
     System.out.println(“Time is up.  Put down your hands.”);
     System.out.println(“Press ENTER to continue”);
     in.readLine();
     blank();
     query();
    }
    break;
   case 12:
    watch();
    break;
   case 13:
    alter();
    break;
   case 14:
    creAuct();
    break;
   case 15:
    stat();
    break;
   case 16:
    System.out.println(“Logout successful”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
    welcome();
    break;
   case 17:
    System.out.println(“Bye bye, have a nice weekend”);
    break;
   default:
    System.out.println(“We don’t have so many function”);
    query();
  }
 }


 /**********************************************************************************************************************
  * Name   : creAuct
  * Parameter : None
  * Usage  : This is Create Auction Page.  It lets user to choose the function on Create Auction Page to run,
        link to other pages, logout and exit.
  * Return  : None
 **********************************************************************************************************************/
 public void creAuct() throws SQLException, IOException, NumberFormatException
 {
  blank();
  System.out.println(”  Create Auction Pagenn”);
  System.out.println(“What do you want, ” + user.getUserName() + “n”);
  System.out.println(“1. Create an open auction”);
  System.out.println(“2. Go to Alter Existing User Page”);
  System.out.println(“3. Go to Query auction Page”);
  System.out.println(“4. Go to Statistics Page”);
  System.out.println(“5. Logout”);
  System.out.println(“6. Sleep”);
  try{
   dream = Integer.parseInt(in.readLine().trim());
   blank();
  }
  catch(NumberFormatException e){
   System.err.println(“You should enter either 1, 2, 3, 4, 5 or 6”);
   creAuct();
   return;
  }
  switch(dream){
   case 1:
    createAuction();
    break;
   case 2:
    alter();
    break;
   case 3:
    query();
    break;
   case 4:
    stat();
    break;
   case 5:
    System.out.println(“Logout Successful.”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
    welcome();
    break;
   case 6:
    System.out.println(“Have a good Sleep”);
    break;
   default:
    blank();
    System.out.println(“You should enter either 1, 2, 3, 4, 5 or 6”);
      System.out.println(“Press ENTER to continue”);
      in.readLine();
  }
 }


 /**********************************************************************************************************************
  * Name   : createAuction
  * Parameter : None
  * Usage  : This is a function of Create Auction Page.  It lets user to create a new auction and several new items.
  * Return  : None
 **********************************************************************************************************************/
 public void createAuction() throws SQLException, IOException, NumberFormatException
 {
  String id;
  int j = 0;
  int noOfNewItem = 0;
  int noOfNewCategory = 0;
  String temp;
  String[] item = new String[size];
  String[] newItem = new String[size];
  String[] newDescription = new String[size];
  int[] newQuantity = new int[size];
  String[] newLocation = new String[size];
  String[] newShipping = new String[size];
  String[][] newCategory = new String[size][size];
  String openDate;
  String closeDate;
  String st2;
  float price;
  ResultSet rs2;
  PreparedStatement pstmt2;


  rs = stmt.executeQuery(“SELECT DISTINCT id from auction order by id desc”);//list all the ID that can bid by current user
  if(rs.next()){
   if(Integer.parseInt(rs.getString(1)) < 10000){
    id = “10000”;
   }
   else{
    id = Integer.toString(Integer.parseInt(rs.getString(1)) + 1);
   }
   String st = “SELECT DISTINCT name from item”;
   String ste = ” order by name”;
   while(true){
    rs.close();
    pstmt =  con.prepareStatement(st + ste);
    rs = pstmt.executeQuery();
    rs.next();
    itemHeader(rs);
    System.out.println(“What item would you like to include?(Leave blank to quit)”);
    while(!(temp = in.readLine().trim()).equals(“”)){
     try{
      dream = Integer.parseInt(temp);
      break;
     }
     catch(NumberFormatException e){
      System.out.println(“You should enter a number”);
     }
    }
    if(temp.equals(“”)){
     creAuct();
     return;
    }
    rs.close();
    rs = stmt.executeQuery(st);
    int counter;
    boolean bol = true;
    for(counter = 0;rs.next();counter++);
    if(dream == counter + 2){
     break;
    }
    else if(dream > counter + 2){
     System.out.println(“We don’t have so much item you can choose”);
     bol = false;
    }
    else if(dream == counter + 1){
     System.out.println(“Please enter the name of your item”);
     while(true){
      if((temp = in.readLine().trim()).length() > 64){
       System.out.println(“Auction name to too long”);
      }
      else if(temp.equals(“”)){
       System.out.println(“You must enter the name of the item”);
      }
      else{
       pstmt2 = con.prepareStatement(“SELECT name FROM item WHERE name = ?”);
       pstmt2.setString(1, temp);
       rs = pstmt2.executeQuery();
       if(!rs.next()){
       item[j] = temp;
       newItem[noOfNewItem] = temp;
       break;
       }
       else{
        System.out.println(“We have this item already”);
      }
      }
     }
     System.out.println(“Please enter the description of your item”);
     while((newDescription[noOfNewItem] = in.readLine().trim()).length() > 1024){
      System.out.println(“Auction description is too long”);
     }
     System.out.println(“Please enter the Quantity of your item”);
     while(true){
      try{
       newQuantity[noOfNewItem] = Integer.parseInt(in.readLine().trim());
       if(newQuantity[noOfNewItem] > 99){
        System.out.println(“We don’t support so much items”);
       }
       else{
        break;
       }
      }
      catch(NumberFormatException e){
       System.out.println(“You must enter a number”);
      }
     }
     System.out.println(“Please enter the Location of the item”);
     while((newLocation[noOfNewItem] = in.readLine().trim()).length() > 64){
      System.out.println(“Location name is too long”);
     }
     System.out.println(“Please enter the shipping of the item”);
     while(true){
      if((newShipping[noOfNewItem] = in.readLine().trim()).length() > 64){
       System.out.println(“Your shipping is too long”);
      }
      else if(newShipping[noOfNewItem].equals(“”)){
       System.out.println(“You must enter the shipping of the item”);
      }
      else{
       break;
      }
     }
     st2 = “SELECT DISTINCT name, description from category”;
     int total;
     while(true){
      rs.close();
      pstmt = con.prepareStatement(st2 + ste);
      rs = pstmt.executeQuery();
      if(!rs.next()){
       break;
      }
      blank();
      total = categoryHeader(rs);
      System.out.println(“Which category would you like to classify this item?”);
      while(true){
       try{
        temp = in.readLine().trim();
        if(temp.equals(“”)){
         break;
        }
        else{
         dream = Integer.parseInt(temp);
         if(dream > total){
          System.out.println(“We don’t have so much category”);
         }
         else{
          break;
         }
        }
       }catch(NumberFormatException e){
        System.out.println(“You must enter a number”);
       }
      }
      if(temp.equals(“”)){
       if(noOfNewCategory != 0){
        break;
       }
      }
      else{
       rs.close();
       rs = stmt.executeQuery(st2 + ste);
       for(int i = 0; i < dream && rs.next(); i++);
       newCategory[noOfNewItem][noOfNewCategory++] = rs.getString(1);
       counter = 0;
       temp = “”;
       while(counter < (rs.getString(1)).length()){
        temp += rs.getString(1).charAt(counter);
        if(rs.getString(1).charAt(counter) == ”’){
         temp += rs.getString(1).charAt(counter);
        }
        counter++;
       }
       st2 += (” MINUS SELECT name, description from category where name = ‘” + temp + “‘”);
      }
     }
     rs.close();
    noOfNewItem++;
    }
    else{
     rs.close();
     rs = stmt.executeQuery(st);
     for(int i = 0;i<dream && rs.next();i++);
     item[j] = rs.getString(1);
    }
    if(bol){
     blank();
     System.out.println(“name”);
     System.out.println(“=========================================”);
     for(int k = 0;k <= j;k++){
      System.out.println(item[k]);
     }
     while(true){
      System.out.println(“Do you want to add these item to your auction?(y/n)”);
      String yn = in.readLine().trim();
      if(yn.equals(“n”)){
       break;
      }
      else if(yn.equals(“y”)){
       temp = “”;
       counter = 0;
       while(counter < item[j].length()){
        temp += item[j].charAt(counter);
        if(item[j].charAt(counter) == ”’){
         temp += item[j].charAt(counter);
        }
        counter++;
       }
       st += (” MINUS (SELECT name FROM item where name = ‘” + temp + “‘)”);
       j++;
       blank();
       System.out.println(“nOk, item added.nPress ENTER to continue”);
       in.readLine().trim();
       break;
      }
       blank();
      System.out.println(“nYou should enter y or nnPress ENTER to continue”);
      in.readLine().trim();
     }
    }
   }
   if(j!=0){
    do{
     blank();
     System.out.println(“name”);
     System.out.println(“=========================================”);
     for(int k = 0;k < j;k++){
      System.out.println(item[k]);
     }
     System.out.println(“What is the initial price of the auction?”);
    }while(!checkCurrent(temp = in.readLine().trim()));
    price = Float.parseFloat(temp);
    do{
     System.out.println(“What is the open date of the auction?(YYYYMMDD)”);
    }while(!checkDate2(temp = in.readLine().trim(),”o”));
    openDate = temp;
    do{
     System.out.println(“What is the close date of the auction?(YYYYMMDD)”);
    }while(!checkDate2(temp = in.readLine().trim(),openDate));
    closeDate = temp;
    pstmt = con.prepareStatement(“INSERT INTO item VALUES(?,?,?,?,?)”);
    for(int i = 0; i < noOfNewItem ; i++){
     pstmt.setString(1, newItem[i]);
     pstmt.setString(2, newDescription[i]);
     pstmt.setString(3, Integer.toString(newQuantity[i]));
     pstmt.setString(4, newLocation[i]);
     pstmt.setString(5, newShipping[i]);
     pstmt.executeUpdate();
    }
    pstmt.close();
    pstmt = con.prepareStatement(“INSERT INTO classify VALUES(?,?)”);
    for(int i = 0; i < noOfNewItem ; i++){
     for(int k = 0; k < noOfNewCategory ; k++){
      pstmt.setString(1, newItem[i]);
      pstmt.setString(2, newCategory[i][k]);
      pstmt.executeUpdate();
     }
    }
    pstmt.close();
    pstmt = con.prepareStatement(“INSERT INTO auction VALUES (?, TO_DATE(?,’YYYYMMDD’), ?)”);
    pstmt.setString(1, id);
    pstmt.setString(2, closeDate);
    pstmt.setString(3, user.getUserEmail());
    pstmt.executeUpdate();
    pstmt.close();
    for(int i = 0; i < j; i++){
     pstmt = con.prepareStatement(“INSERT INTO consist VALUES (?, ?)”);
     pstmt.setString(1, id);
     pstmt.setString(2, item[i]);
     pstmt.executeUpdate();
    }
    pstmt.close();
    pstmt = con.prepareStatement(“INSERT INTO open VALUES(?,?,?, TO_DATE(?,’YYYYMMDD’))”);
    pstmt.setString(1, id);
    pstmt.setString(2, Float.toString(price));
    pstmt.setString(3, “”);
    pstmt.setString(4, openDate);
    pstmt.executeUpdate();
    pstmt.close();
    blank();
    System.out.println(“nAuction Added Successfullyn”);
    System.out.println(“Press Enter to continue”);
    in.readLine();
   }
   creAuct();
  }
  else{
   System.out.println(“No item no fun. Bye”);
  }
 }


 /**********************************************************************************************************************
  * Name   : checkCurrent
  * Parameter : None
  * Usage  : Check the input format of the price.  Also check if the price is inbetween 0 and 100000 and convert
        the price into 2 decimal places.
  * Return  : boolean
        – true if the format of the price and the price fall on the boundery is correct
 **********************************************************************************************************************/
 public boolean checkCurrent(String temp) throws SQLException, NumberFormatException
 {
  int counter;
  float price;
  if(temp.equals(“”)){
   return false;
  }
  for(counter = 0; counter < temp.length() && temp.charAt(counter) != ‘.’;counter++);
  if(counter != temp.length()){
   for(int i = 0;i<2 && counter < temp.length();i++, counter++);
   temp = temp.substring(0, ++counter);
  }
  try{
   price = Float.parseFloat(temp);
   if(price >= 100000){
    System.out.println(“The price is too high”);
    return false;
   }
   else{
    return true;
   }
  }
  catch(NumberFormatException e){
   System.out.println(“Price should be a number”);
   return false;
  }
 }


 /**********************************************************************************************************************
  * Name   : checkDate2
  * Parameter : None
  * Usage  : Check the input format of 2 day.  Also, it compare two days
  * Return  : boolean
        – true if the format of the date and the first day is before the second day
 **********************************************************************************************************************/
 public boolean checkDate2(String day, String c) throws SQLException
 {
  if(!checkDate(day)){
   return false;
  }
  if(c.equals(“o”)){
   rs = stmt.executeQuery(“SELECT TO_CHAR(day, ‘YYYYMMDD’) from time”);
   rs.next();
   if(rs.getString(1).compareTo(day) < 1){
    return true;
   }
   else{
    System.out.println(“The open date should be today or days after tomorrow”);
    return false;
   }
  }
  else if(checkDate(c)){
   if(c.compareTo(day) < 0){
    return true;
   }
   else{
    System.out.println(“You should give some time for bidder to bid your auction”);
    return false;
   }
  }
  return false;
 }


 /**********************************************************************************************************************
  * Name   : itemHeader
  * Parameter : None
  * Usage  : It prints the item in certain format
  * Return  : void
 **********************************************************************************************************************/
 public void itemHeader(ResultSet rs) throws IOException, SQLException
 {
  int j = 1;
  blank();
  System.out.println(“item”);
  System.out.println(“No. Name”);
  System.out.println(“=============================================================================================================”);
  do{
   System.out.println(j++ + “. ” + rs.getString(1));
  }while(rs.next());
  System.out.println(j++ + “. others”);
  System.out.println(j + “. Enough”);
 }


 /**********************************************************************************************************************
  * Name   : trimDown
  * Parameter : None
  * Usage  : It is a function of Administrative Page.  It saves spaces and covert the out-dated open auction
        into a closed one.
  * Return  : void
 **********************************************************************************************************************/
 public void trimDown() throws SQLException
 {
  int counter;
  pstmt = con.prepareStatement(“SELECT DISTINCT o.id, o.current_price, b.email FROM auction a, open o, bid b, time t where o.id = a.id and b.id = a.id and o.current_price = b.price and a.close < t.day order by o.id desc”);//list all the ID that can bid by current user
  rs = pstmt.executeQuery();
  String temp;
  for(int i = 0; rs.next(); i++){
   counter = 0;
   temp = “”;
   while(counter < rs.getString(3).length()){
    temp += (rs.getString(3)).charAt(counter);
    if((rs.getString(3).charAt(counter++)) == ”’){
     temp += (rs.getString(3)).charAt(counter);
    }
   }
   stmt.executeUpdate(“DELETE from watch where id = ‘” + rs.getString(1) + “‘”);//delete auction from open
   stmt.executeUpdate(“DELETE from bid where id = ‘” + rs.getString(1) + “‘”);//delete auction from open
   stmt.executeUpdate(“DELETE from open where id = ‘” + rs.getString(1) + “‘”);//delete auction from open
   stmt.executeUpdate(“INSERT INTO closed VALUES(‘” + rs.getString(1) + “‘ , ‘” + rs.getString(2) + “‘ , ‘” + temp + “‘)”);//delete auction from open
  }
  rs.close();
  pstmt.close();
 }


 /**********************************************************************************************************************
  * Name   : anom
  * Parameter : None
  * Usage  : It is a function of Administrative Page.  It checks and prints all anomalous activities.
  * Return  : void
 **********************************************************************************************************************/
 public void anom() throws SQLException
 {
  String[] seller = new String[size];
  PreparedStatement pstmt = con.prepareStatement(“SELECT DISTINCT seller from auction”);
  rs = pstmt.executeQuery();
  int noOfSeller = 0;
  for(int i = 0; rs.next(); i++, noOfSeller++){
   seller[i] = rs.getString(1);
  }
  pstmt.close();
  rs.close();
  String bidder[] = new String[size];
  pstmt = con.prepareStatement(“SELECT DISTINCT email from bid”);
  rs = pstmt.executeQuery();
  int noOfBidder = 0;
  for(int i = 0; rs.next(); i++, noOfBidder++){
   bidder[i] = rs.getString(1);
  }
  pstmt.close();
  pstmt = con.prepareStatement(“SELECT DISTINCT id from auction where seller = ? MINUS SELECT DISTINCT id from bid where email= ?”);
  for(int i = 0 ; i < noOfSeller ; i++){
   for(int j = 0 ; j < noOfBidder ; j++){
    pstmt.setString(1, seller[i]);
    pstmt.setString(2, bidder[j]);
    rs = pstmt.executeQuery();
    if(!rs.next()){
     System.out.println(bidder[j] + ” bid all item sold by ” + seller[i]);
    }
   }
   rs.close();
  }
 }


 /**********************************************************************************************************************
  * Name   : watch
  * Parameter : None
  * Usage  : It is a function of Query Auction Page.  It converts current users into watcher.
  * Return  : void
 **********************************************************************************************************************/
 public void watch() throws SQLException, IOException
 {
  String id;
  pstmt = con.prepareStatement(“SELECT id from OPEN MINUS (SELECT id FROM auction WHERE seller = ? UNION SELECT id FROM watch WHERE email = ?) order by id desc”);
  pstmt.setString(1, user.getUserEmail());
  pstmt.setString(2, user.getUserEmail());
  rs = pstmt.executeQuery();
  if(rs.next()){
   blank();
   System.out.println(“Open auction”);
   System.out.println(“IDn==============”);
   do{
    System.out.println(rs.getString(1));
   }while(rs.next());
   System.out.println(“Which auction you want to watch?”);
   while(!checkID(id = in.readLine().trim())){
    if(id.equals(“”)){
     query();
     return;
    }
   }
   pstmt.close();
   try{
    pstmt = con.prepareStatement(“INSERT INTO watch VALUES(?,?)”);
    pstmt.setString(1, id);
    pstmt.setString(2, user.getUserEmail());
    rs = pstmt.executeQuery();
    blank();
    System.out.println(“Watch successfully”);
    System.out.println(“Press ENTER to continue”);
    in.readLine().trim();
    watch();
   }
   catch(SQLException e){
    blank();
    System.out.println(“You are watching this auction already or you are seller”);
    System.out.println(“Press ENTER to continue”);
    in.readLine();
    watch();
   }
   rs.close();
   pstmt.close();
  }
  else{
   System.out.println(“You are watching everything now”);
   pstmt.close();
   rs.close();
   query();
  }
 }


 /**********************************************************************************************************************
  * Name   : bid
  * Parameter : None
  * Usage  : It is a function of Query Auction Page.  It lets current user to bid open auctions.
  * Return  : void
 **********************************************************************************************************************/
 public void bid() throws SQLException, IOException
 {
  pstmt = con.prepareStatement(“SELECT o.id from OPEN o, time t where o.open <= t.day and NOT EXISTS (SELECT a.id from auction a, bid b where a.id = b.id and a.id = o.id and o.current_price = b.price and (a.seller = ? or b.email = ?))”);//list all the ID that can bid by current user
  pstmt.setString(1, user.getUserEmail());
  pstmt.setString(2, user.getUserEmail());
  rs = pstmt.executeQuery();
  String id;
  if(rs.next()){// if there is an auction that can bid by current user
   pstmt.close();
   rs.close();
   pstmt = con.prepareStatement(“SELECT o.id from OPEN o, time t where o.open <= t.day and NOT EXISTS (SELECT a.id from auction a, bid b where a.id = b.id and a.id = o.id and o.current_price = b.price and (a.seller = ? or b.email = ?))”);//list all the ID that can bid by current user
   pstmt.setString(1, user.getUserEmail());
   pstmt.setString(2, user.getUserEmail());
   rs = pstmt.executeQuery();
   System.out.println(“Bid”);
   System.out.println(“ID”);
   System.out.println(“=======”);
   for(int i = 0 ; rs.next() ; i++){
    System.out.println(rs.getString(1));
   }
   rs.close();
   pstmt.close();
   System.out.println(“Please enter the id of item you want to bid(leave blank to back)”);
  }
  else{
   System.out.println(“There are no auction you can bid”);
   query();
   return;
  }
  while(true){
   id = in.readLine().trim();
   if(id.equals(“”)){//check if the current user enter nothing
    query();
    return;
   }
   else if(checkID(id)){
    pstmt = con.prepareStatement(“SELECT o.id from OPEN o, time t where o.id = ? and o.open <= t.day and NOT EXISTS (SELECT a.id from auction a, bid b where a.id = b.id and a.id = o.id and o.current_price = b.price and (a.seller = ? or b.email = ?)) ORDER BY o.id desc”);//list all the ID that can bid by current user
    pstmt.setString(1, id);
    pstmt.setString(2, user.getUserEmail());
    pstmt.setString(3, user.getUserEmail());
    rs = pstmt.executeQuery();
    if(rs.next()){
     break;
    }
    else{
     blank();
     System.out.println(“You cannot bid this auction.”);
     System.out.println(“Press ENTER to continue”);
     in.readLine().trim();
     bid();
     return;
    }
   }
   else{
    System.out.println(“Please enter the id of item you want to bid”);
   }
  }
  rs.close();
  pstmt.close();
  pstmt = con.prepareStatement(“SELECT DISTINCT price from bid where ID = ?”);//for showing current price of the auction
  pstmt.setString(1, id);
  rs = pstmt.executeQuery();
  String yn;
  if(rs.next()){//if the auction has been bade by someone
   String pay = “”;
   while(true){
    do{
      rs.close();
      pstmt.close();
      pstmt = con.prepareStatement(“SELECT o.current_price from open o where o.id = ? and NOT EXISTS(SELECT b.price from bid b where b.id = o.id and o.current_price = b.price and b.email = ?)”);//for showing current price of the auction
      pstmt.setString(1, id);
      pstmt.setString(2, user.getUserEmail());
      rs = pstmt.executeQuery();
     if(rs.next()){
      System.out.println(“The current price is ” + rs.getString(1) + “. How much you can pay? (leave blank to quit)”);
      pay = in.readLine().trim();
      if(pay.equals(“”)){//check if the current user enter nothing
       bid();
       return;
      }
     }
     else{
      System.out.println(“You have just bade the auction”);
     }
    }while(!comparePrice(pay, rs.getString(1)));
    while (true){
     System.out.println(“Are you sure to use $” + pay2 + ” to bid this auction ” + id + “?(y/n)”);
     yn = in.readLine().trim();
     if(yn.equals(“y”)){
      pstmt = con.prepareStatement(“SELECT o.current_price from OPEN o where o.id = ? and NOT EXISTS(SELECT a.id from auction a where a.id = o.id and a.seller = ?) ORDER BY o.id asc”);//check the id entered by current user can or cannot be bade
      pstmt.setString(1, id);
      pstmt.setString(2, user.getUserEmail());
      rs = pstmt.executeQuery();
      if(rs.next()){
       if(pay2 > Float.parseFloat(rs.getString(1))){
        pstmt.close();
        rs.close();
        pstmt = con.prepareStatement(“SELECT email from bid where id = ? and email = ?”);
        pstmt.setString(1, id);
        pstmt.setString(2, user.getUserEmail());
        rs = pstmt.executeQuery();
        if(rs.next()){
         pstmt.close();
         pstmt = con.prepareStatement(“UPDATE bid set price = ? where id =  ? and email = ?”);
         pstmt.setString(2, id);
         pstmt.setString(3, user.getUserEmail());
         pstmt.setString(1, Float.toString(pay2));
        }
        else{
         pstmt.close();
         pstmt = con.prepareStatement(“INSERT INTO bid VALUES (?, ?, ?)”);
         pstmt.setString(1, id);
         pstmt.setString(2, user.getUserEmail());
         pstmt.setString(3, Float.toString(pay2));
        }
        pstmt.executeUpdate();
        pstmt.close();
        pstmt = con.prepareStatement(“UPDATE open set current_price = ? where id = ?”);
        pstmt.setString(2, id);
        pstmt.setString(1, Float.toString(pay2));
        pstmt.executeUpdate();
        System.out.println(“Bid Success!!!”);
        bid();
        return;
       }
       else{
        System.out.println(“Sorry, you are too late”);
        break;
       }
      }
      else{
       System.out.println(“You are too late”);
       bid();
       return;
      }
     }
     else if(yn.equals(“n”)){
      bid();
      return;
     }
     else{
      System.out.println(“You must enter y or n”);
     }
    }
   }
  }
  else{
   rs.close();
   pstmt.close();
   rs = stmt.executeQuery(“SELECT initial_price from open where id = ‘” + id + “‘”);
   rs.next();
   while(true){
    System.out.println(“The auction has not been bade yet. Are you sure to use $” + rs.getString(1) + ” to bid this auction?”);
    yn = in.readLine().trim();
    if(yn.equals(“y”)){
     try{
      pstmt = con.prepareStatement(“INSERT INTO bid VALUES (?, ?, ?)”);
      pstmt.setString(1, id);
      pstmt.setString(2, user.getUserEmail());
      pstmt.setString(3, rs.getString(1));
      pstmt.executeUpdate();
      pstmt.close();
      pstmt = con.prepareStatement(“UPDATE open set current_price = ? where id = ?”);
      pstmt.setString(2, id);
      pstmt.setString(1, rs.getString(1));
      pstmt.executeUpdate();
      System.out.println(“Bid Success!!!”);
      bid();
      break;
     }catch(SQLException e){
      System.out.println(“Sorry, you are too late”);
      rs.close();
      bid();
      return;
     }
    }
    else if(yn.equals(“n”)){
     rs.close();
     bid();
     return;
    }
    else{
     System.out.println(“You must enter y or n”);
    }
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : comparePrice
  * Parameter : String pay, String current
  * Usage  : It checks the format of 2 price.  It converts the prices into 2 decmial places. 
        It checks if the prices fall in between 0 and 100000.  Also it compare two inputs of price.
  * Return  : boolean
        – true if the format, range is correct and the first price is greater than the second price.
 **********************************************************************************************************************/
 public boolean comparePrice(String pay, String current) throws NumberFormatException
 {
  try{
   int counter;
   pay2 = Float.parseFloat(pay);
   for(counter = 0;counter<pay.length() && pay.charAt(counter) != ‘.’;counter++);
   if(counter != pay.length()){
    int counter2;
    counter++;
    for(counter2 = 0;counter2<2 && counter<pay.length();counter2++, counter++);
    pay = pay.substring(0, counter);
    pay2 = Float.parseFloat(pay);
   }
  }
  catch(NumberFormatException e){
   if(pay.equals(“”)){
    return false;
   }
   System.out.println(“Money must be a number”);
   return false;
  }
  if(Float.parseFloat(current) >= pay2){
   System.out.println(“If you want to get this auction, you must pay more”);
   return false;
  }
  else if(pay2 >= 100000){
   System.out.println(“You are a rich!!!  You should use the money to buy our system rather than bid this auction”);
   return false;
  }
  else{
   return true;
  }
 }


 /**********************************************************************************************************************
  * Name   : checkCreditCardNumber
  * Parameter : String credit
  * Usage  : It checks the format of the input credit card number.
  * Return  : boolean
        – true if the credit card number format is correct
 **********************************************************************************************************************/
 public boolean checkCreditCardNumber(String credit) throws SQLException
 {
  if(credit.length() == 19 && Character.isDigit(credit.charAt(0))
    && Character.isDigit(credit.charAt(1))
    && Character.isDigit(credit.charAt(2))
    && Character.isDigit(credit.charAt(3))
    && credit.charAt(4) == ‘-‘
    && Character.isDigit(credit.charAt(5))
    && Character.isDigit(credit.charAt(6))
    && Character.isDigit(credit.charAt(7))
    && Character.isDigit(credit.charAt(8))
    && credit.charAt(9) == ‘-‘
    && Character.isDigit(credit.charAt(10))
    && Character.isDigit(credit.charAt(11))
    && Character.isDigit(credit.charAt(12))
    && Character.isDigit(credit.charAt(13))
    && credit.charAt(14) == ‘-‘
    && Character.isDigit(credit.charAt(15))
    && Character.isDigit(credit.charAt(16))
    && Character.isDigit(credit.charAt(17))
    && Character.isDigit(credit.charAt(18))){
   return true;
  }
  else if (credit.equals(“”)){
   return false;
  }
  else{
   System.out.println(“credit card number should be in format 0000-0000-0000-0000”);
   return false;
  }


 }


 /**********************************************************************************************************************
  * Name   : checkPhoneNumber
  * Parameter : String number
  * Usage  : It checks the format of the input phone number.
  * Return  : boolean
        – true if the phone number format is correct
 **********************************************************************************************************************/
 public boolean checkPhoneNumber(String number){
  if(number.length() == 8 && Character.isDigit(number.charAt(0))
    && Character.isDigit(number.charAt(1))
    && Character.isDigit(number.charAt(2))
    && Character.isDigit(number.charAt(3))
    && Character.isDigit(number.charAt(4))
    && Character.isDigit(number.charAt(5))
    && Character.isDigit(number.charAt(6))
    && Character.isDigit(number.charAt(7))){
   return true;
  }
  else if (number.equals(“”)){
   return false;
  }
  else{
   System.out.println(“phone number should be in format 00000000”);
   return false;
  }
 }


 /**********************************************************************************************************************
  * Name   : checkID
  * Parameter : String id
  * Usage  : It checks the format of the input ID.
  * Return  : boolean
        – true if the ID format is correct
 **********************************************************************************************************************/
 public boolean checkID(String id) throws SQLException
 {
  if(id.length()!=5){
   if(!(id.equals(“”))){
   System.out.println(“ID must be a a five digit number”);
   }
   return false;
  }
  int k;
  for(k=0;k<id.length();k++){
   if(!Character.isDigit(id.charAt(k))){
    break;
   }
  }
  if(k != id.length() || id.length()!= 5){
   System.out.println(“Press ENTER to continue”);
   return false;
  }
  else{
   rs = stmt.executeQuery(“SELECT id from auction where id = ‘” + id + “‘”);
   if(!rs.next()){
    System.out.println(“The auction id you specified is not available.”);
    return false;
   }
   else{
    return true;
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : checkSEmail
  * Parameter : String email
  * Usage  : It checks if the input email address is belong to any seller.
  * Return  : boolean
        – true if the email address is belong to any seller
 **********************************************************************************************************************/
 public boolean checkSEmail(String email) throws SQLException
 {
  pstmt = con.prepareStatement(“SELECT seller FROM auction where seller = ?”);
  pstmt.setString(1, email);
  rs = pstmt.executeQuery();
  if(!rs.next()){
   System.out.println(“There is no seller that have thesame email specified by you”);
   return false;
  }
  else{
   return true;
  }
 }


 /**********************************************************************************************************************
  * Name   : checkSWEmail
  * Parameter : String email
  * Usage  : It checks if the input email address is belong to any watcher or bidder.
  * Return  : boolean
        – true if the email address is belong to any watcher or bidder
 **********************************************************************************************************************/
 public boolean checkWBEmail(String email) throws SQLException
 {
  pstmt = con.prepareStatement(“SELECT w.email FROM watch w,bid b where w.email = ? or b.email = ?”);
  pstmt.setString(1, email);
  pstmt.setString(2, email);
  rs = pstmt.executeQuery();
  if(!rs.next()){
   System.out.println(“We do not have any watcher or bidder who have the email you specified.”);
   return false;
  }
  else{
   return true;
  } 
 }


 /**********************************************************************************************************************
  * Name   : checkItem
  * Parameter : String item
  * Usage  : It checks if the input item is avaiable or not.
  * Return  : boolean
        – true if the input item is avaiable
 **********************************************************************************************************************/
 public boolean checkItem(String item) throws SQLException
 {
  pstmt = con.prepareStatement(“SELECT name FROM category where name = ?”);
  pstmt.setString(1, item);
  rs = pstmt.executeQuery();
  if(!rs.next()){
   System.out.println(“The category you specified is not available.”);
   return false;
  }
  else{
   return true;
  }
 }


 /**********************************************************************************************************************
  * Name   : checkCategory
  * Parameter : String category
  * Usage  : It checks if the input category is avaiable or not.
  * Return  : boolean
        – true if the input category is avaiable
 **********************************************************************************************************************/
 public boolean checkCategory(String category) throws SQLException
 {
  pstmt = con.prepareStatement(“SELECT name from category where name = ?”);
  pstmt.setString(1, category);
  rs = pstmt.executeQuery();
  if(!rs.next()){
   System.out.println(“The catagory you specified is not available.”);
   return false;
  }
  else{
   return true;
  }
 }


 /**********************************************************************************************************************
  * Name   : addressHeader
  * Parameter : ResultSet rs
  * Usage  : It prints the address of users
  * Return  : void
 **********************************************************************************************************************/
 public void addressHeader(ResultSet rs) throws IOException, SQLException
 {
  int j = 1;
  System.out.println(”    Your Address”);
  System.out.println(“No. Address”);
  System.out.println(“==================================================================================================================================”);
  do{
   System.out.print(j + “. “);
   for(int i = 1;i<4;i++){
    System.out.print(rs.getString(i) + ” “);
   }
   System.out.println();
   j++;
  }while(rs.next());
 }


 /**********************************************************************************************************************
  * Name   : updateInterest
  * Parameter : none
  * Usage  : It is a function of Alter User Page.  It updated the interest of the user.
  * Return  : void
 **********************************************************************************************************************/
 public void updateInterest() throws SQLException, NumberFormatException, IOException
 {
  pstmt = con.prepareStatement(“SELECT DISTINCT c.name, c.description from category c,interest i where c.name = i.interest and i.email = ?”);
  pstmt.setString(1,user.getUserEmail());
  rs = pstmt.executeQuery();
  if(rs.next()){
   categoryHeader(rs);
   pstmt = con.prepareStatement(“SELECT DISTINCT c.name, c.description from category c,interest i where c.name = i.interest MINUS (SELECT DISTINCT c2.name, c2.description from category c2,interest i2 where c2.name = i2.interest and i2.email = ?)”);
   pstmt.setString(1,user.getUserEmail());
   rs = pstmt.executeQuery();
   if(rs.next()){
    System.out.println(“What do you want?”);
    System.out.println(“1. Add interest”);
    System.out.println(“2. Delete interest”);
    String temp = “”;
    try{
     dream = Integer.parseInt(temp = in.readLine().trim());
    }
    catch(NumberFormatException e){
     if(!(temp.equals(“”))){
     System.out.println(“You should enter either 1 or 2”);
     }
     updateUser();
     return;
    }
    rs.close();
    switch(dream){
     case 1:
      addInterest();
      break;
     case 2:
      deleteInterest();
      break;
     default:
      System.out.println(“We don’t have so much category”);
      updateUser();
    }
   }
   else{
    System.out.println(“You have added all interests in our category before.  You want to delete some now?(y/n)”);
    String yn;
    while(true){
     yn = in.readLine().trim();
     if(yn.equals(“y”)){
      deleteInterest();
      break;
     }
     else if(yn.equals(“n”)){
      updateUser();
      break;
     }
     else{
      System.out.println(“You should enter y or n”);
     }
    }
   }
  }
  else{
   System.out.println(“You don’t get interest in our category before.  Are you get interets now?(y/n)”);
   String yn;
   while(true){
    yn = in.readLine().trim();
    if(yn.equals(“y”)){
     addInterest();
     break;
    }
    else if(yn.equals(“n”)){
     updateUser();
     break;
    }
    else{
     System.out.println(“You should enter y or n”);
    }
   }
  }
 } 


 /**********************************************************************************************************************
  * Name   : addInterest
  * Parameter : none
  * Usage  : It is a function of Alter User Page.  It adds the interest of the user.
  * Return  : void
 **********************************************************************************************************************/
 public void addInterest() throws SQLException, IOException{
  pstmt = con.prepareStatement(“SELECT DISTINCT c.name, c.description from category c,interest i where c.name = i.interest MINUS(SELECT c2.name, c2.description from interest i2, category c2 where i2.interest = c2.name and i2.email = ?)”);
  pstmt.setString(1,user.getUserEmail());
  rs = pstmt.executeQuery();
  if(rs.next()){
   categoryHeader(rs);
  }
  else{
   System.out.println(“No category find”);
   updateUser();
   return;
  }
  System.out.println(“What is your new interest?(Leave blank to exit)”);
  try{
   String temp;
   if((temp = in.readLine().trim()).equals(“”)){
    updateInterest();
    return;
   }
   dream = Integer.parseInt(temp);
  }
  catch(NumberFormatException e){
   System.out.println(“You should input a number”);
   updateUser();
   return;
  }
  pstmt.close();
  rs.close();
  pstmt = con.prepareStatement(“SELECT DISTINCT c.name, c.description from category c,interest i where c.name = i.interest MINUS(SELECT c2.name, c2.description from interest i2, category c2 where i2.interest = c2.name and i2.email = ?)”);
  pstmt.setString(1,user.getUserEmail());
  rs = pstmt.executeQuery();
  for(int i = 0; i < dream; i++){
   if(!rs.next()){
    System.out.println(“There are no so much category you can choose”);
    updateUser();
    return;
   }
  }
  System.out.println(“Name Descriptionn==================================================”);
  for(int i = 1; i < 3; i++){
   System.out.print(rs.getString(i) + ”  “);
  }
  System.out.println(“nnYou like this one?(y/n)”);
  while(true){
   String yn = in.readLine().trim();
   if(yn.equals(“y”)){
    String interest = rs.getString(1);
    pstmt.close();
    pstmt = con.prepareStatement(“INSERT INTO interest VALUES (?, ?)”);
    pstmt.setString(1,user.getUserEmail());
    pstmt.setString(2, interest);
    try{
     pstmt.executeUpdate();
     processSQLWarning(pstmt.getWarnings());
     System.out.println(“Add Successful”);
    }
    catch(SQLException e){
     System.out.println(“We have already known that you get interest in it”);
    }
    updateInterest();
    break;
   }
   else if(yn.equals(“n”)){
    updateUser();
    break;
   }
   else{
    System.out.println(“You should enter y or n”);
   }
  }
  pstmt.close();
  rs.close();
 }


 /**********************************************************************************************************************
  * Name   : deleteInterest
  * Parameter : none
  * Usage  : It is a function of Alter User Page.  It deletes the interest of the user.
  * Return  : void
 **********************************************************************************************************************/
 public void deleteInterest() throws IOException, NumberFormatException, SQLException
 {
  pstmt = con.prepareStatement(“SELECT DISTINCT i.interest, c.description from interest i, category c where i.interest = c.name and i.email = ? “);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  rs.next();
  categoryHeader(rs);
  System.out.println(“Which interest you want to delete?”);
  try{
   String temp;
   if((temp = in.readLine().trim()).equals(“”)){
    updateInterest();
    return;
   }
   dream = Integer.parseInt(temp);
   if(dream < 1){
    updateInterest();
    return;
   }
  }
  catch(NumberFormatException e){
   System.out.println(“Input should be a number”);
   updateInterest();
   return;
  }
  pstmt.close();
  rs.close();
  pstmt = con.prepareStatement(“SELECT DISTINCT i.interest, c.description from interest i, category c where i.interest = c.name and i.email = ? “);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  int i;
  for(i = 0;i<dream && rs.next();i++);
  if(i!=dream){
   System.out.println(“You don’t have so many interest”);
   updateInterest();
   return;
  }
  System.out.println(“interset description”);
  System.out.println(“=========================================”);
  for( i = 1;i<3;i++){
   System.out.print(rs.getString(i) + ” “);
  }
  String interest = rs.getString(1);
  System.out.println();
  while(true){
   System.out.println(“delete this interest?(y/n)”);
   String yn = in.readLine().trim();
   if(yn.equals(“n”)){
    updateInterest();
    return;
   }
   else if(yn.equals(“y”)){
    try{
     pstmt = con.prepareStatement(“DELETE interest where email = ? and interest = ?”);
     pstmt.setString(1, user.getUserEmail());
     pstmt.setString(2, interest);
     pstmt.executeUpdate();
     pstmt.close();
     System.out.println(“delete successful”);
     updateInterest();
     return;
    }
    catch(SQLException e){
     System.out.println(“Sorry, you are too late”);
     updateInterest();
     return;
    }
   }
   else{
    System.out.println(“You must enter y or n”);
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : categoryHeader
  * Parameter : none
  * Usage  : It prints the category name of the system.
  * Return  : void
 **********************************************************************************************************************/
 public int categoryHeader(ResultSet rs) throws SQLException
 {
  int j = 1;
  System.out.println(“category page”);
  System.out.println(“Name description”);
  System.out.println(“===================================================================================================================================”);
  do{
   System.out.print(j + “. “);
   for(int i = 1;i<3;i++){
    System.out.print(rs.getString(i) + ”     “);
   }
   System.out.println();
   j++;
  }while(rs.next());
  return j – 1;
 }


 /**********************************************************************************************************************
  * Name   : updateAddress
  * Parameter : none
  * Usage  : It is a function of alter user page.  It updates the inforamtion of the current user.
  * Return  : void
 **********************************************************************************************************************/
 public void updateAddress() throws SQLException, IOException
 {
  System.out.println();
  System.out.println(“What do you want to do?”);
  System.out.println(“1. Add an address”);
  System.out.println(“2. Update an address”);
  System.out.println(“3. Delete an address”);
  System.out.println(“4. Back”);
  System.out.println(“5. Logout”);
  System.out.println(“6. Sleep”);
  try{
   dream = Integer.parseInt(in.readLine().trim());
  }
  catch(NumberFormatException e){
   System.err.println(“You should enter either 1, 2 or 3”);
   updateAddress();
   return;
  }
  switch(dream){
   case 1:
    addAddress();
    updateAddress();
    break;
   case 2:
    changeAddress();
    break;
   case 3:
    deleteAddress();
    break;
   case 4:
    updateUser();
    break;
   case 5:
    System.out.println(“Logout Successful”);
    welcome();
    break;
   case 6:
    System.out.println(“Have a good sleep”);
    break;
   default:
    System.out.println(“You must enter 1, 2, 3, 4, 5 or 6”);
    updateUser();
  }
 }


 /**********************************************************************************************************************
  * Name   : deleteAddress
  * Parameter : none
  * Usage  : It is a function of alter user page.  It deletes the inforamtion of the current user.
  * Return  : void
 **********************************************************************************************************************/
 public void deleteAddress() throws IOException, NumberFormatException, SQLException
 {
  pstmt = con.prepareStatement(“SELECT street, city, country from address where email = ? “);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  rs.next();
  addressHeader(rs);
  System.out.println(“Which address you want to delete?”);
  try{
   String temp;
   if((temp = in.readLine().trim()).equals(“”)){
    updateUser();
    return;
   }
   dream = Integer.parseInt(temp);
  }
  catch(NumberFormatException e){
   System.out.println(“Input should be a number”);
   updateUser();
   return;
  }
  pstmt = con.prepareStatement(“SELECT street, city, country from address where email = ? “);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  for(int i = 0;i<dream;i++){
   if(!rs.next()){
    System.out.println(“You don’t have so many address”);
    updateUser();
    return;
   }
  }
  while(true){
   System.out.println(“Address”);
   System.out.println(“=========================================”);
   for(int i = 1;i<4;i++){
    System.out.print(rs.getString(i) + “, “);
   }
   street = rs.getString(1);
   city = rs.getString(2);
   country = rs.getString(3);
   System.out.println();
   while(true){
    System.out.println(“Is this address?(y/n)”);
    String yn = in.readLine().trim();
    if(yn.equals(“n”)){
     updateUser();
     return;
    }
    else if(yn.equals(“y”)){
     try{
      pstmt = con.prepareStatement(“DELETE address where email = ? and street = ? and city = ? and country = ?”);
      pstmt.setString(1, user.getUserEmail());
      pstmt.setString(2, street);
      pstmt.setString(3, city);
      pstmt.setString(4, country);
      pstmt.executeUpdate();
      pstmt.close();
      System.out.println(“delete successful”);
      updateUser();
      return;
     }
     catch(SQLException e){
      System.out.println(“Sorry, you are too late”);
      updateUser();
      return;
     }
    }
    else{
     System.out.println(“You must enter y or n”);
    }
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : changeAddress
  * Parameter : none
  * Usage  : It is a function of alter user page.  It changes the adderss inforamtion of the current user.
  * Return  : void
 **********************************************************************************************************************/
 public void changeAddress() throws IOException, SQLException
 {
  String yn;
  pstmt = con.prepareStatement(“SELECT street, city, country from address where email = ? “);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  rs.next();
  addressHeader(rs);
  System.out.println(“Which address you want to change?(Leave blank to back)”);
  try{
   String temp;
   if((temp = in.readLine().trim()).equals(“”)){
    updateUser();
    return;
   }
   dream = Integer.parseInt(temp);
  }
  catch(NumberFormatException e){
   System.out.println(“Input should be a number”);
   updateUser();
   return;
  }
  pstmt.close();
  rs.close();
  pstmt = con.prepareStatement(“SELECT street, city, country from address where email = ? “);
  pstmt.setString(1, user.getUserEmail());
  rs = pstmt.executeQuery();
  rs.next();
  try{
   for(int i = 1;i<dream;i++, rs.next());
  }
  catch(SQLException e){
   System.out.println(“You don’t have so many address”);
   updateUser();
   return;
  }
  while(true){
   System.out.println(“Address”);
   System.out.println(“=========================================”);
   for(int i = 1;i<4;i++){
    System.out.print(rs.getString(i) + “, “);
   }
   System.out.println();
   System.out.println(“Is this address?(y/n)”);
   yn = in.readLine().trim();
   if(yn.equals(“n”)){
    updateUser();
    break;
   }
   else if(yn.equals(“y”)){
    String street2 = rs.getString(1);
    String city2 = rs.getString(2);
    String country2 = rs.getString(3);
    pstmt = con.prepareStatement(“Update address set street = ? , city = ? , country = ? where email = ? and street = ? and city = ? and country = ? “);
    pstmt.setString(4, user.getUserEmail());
    pstmt.setString(5, street2);
    pstmt.setString(6, city2);
    pstmt.setString(7, country2);
    System.out.print(“street:”);
    street = in.readLine().trim();
    pstmt.setString(1,street);
    System.out.print(“city:”);
    city = in.readLine().trim();
    pstmt.setString(2,city);
    System.out.print(“country:”);
    country = in.readLine().trim();
    pstmt.setString(3, country);
    if(street.equals(“”) || city.equals(“”) || country.equals(“”)){
     System.out.println(“Your address is not complete”);
     updateUser();
    }
    else{
     try{
      rs = pstmt.executeQuery();
      System.out.println(“Update Success”);
     }
     catch(SQLException e){
      System.err.println(“We have this address already”);
     }
    }
    updateAddress();
    break;
   }
   else{
    System.out.println(“You should enter either y or n”);
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : addAddress
  * Parameter : none
  * Usage  : It is a function of alter user page.  It adds the adderss inforamtion of the current user.
  * Return  : void
 **********************************************************************************************************************/
 public boolean addAddress() throws SQLException, IOException{
  pstmt = con.prepareStatement(“INSERT INTO address VALUES (?, ?, ?, ?)”);
  pstmt.setString(1,user.getUserEmail());
  System.out.print(“Street: “);
  while((street = in.readLine().trim()).length() > 64){
   System.out.println(“Street name too long”);
  }
  pstmt.setString(2,street);
  System.out.print(“City: “);
  while((city = in.readLine().trim()).length() > 32){
   System.out.println(“City name too long”);
  }
  pstmt.setString(3,city);
  System.out.print(“Country: “);
  while((country = in.readLine().trim()).length() > 32){
   System.out.println(“Coutry name too long”);
  }
  pstmt.setString(4,country);
  if(street.equals(“”) || city.equals(“”) || country.equals(“”)){
   System.out.println(“This is not a complete address”);
   return false;
  }
  else{
   try{
    pstmt.executeUpdate();
    processSQLWarning(pstmt.getWarnings());
   }
   catch(SQLException e){
    System.out.println(“I have this address already”);
    return false;
   }
   System.out.println(“Address added successfully”);
   return true;
  }
 }


 /**********************************************************************************************************************
  * Name   : blank
  * Parameter : none
  * Usage  : It turns over the pages.
  * Return  : void
 **********************************************************************************************************************/
 public void blank(){
  for(int i = 0 ; i < 60 ; i++){
   System.out.println();
  }
 }


 /**********************************************************************************************************************
  * Name   : userHeader
  * Parameter : ResultSet rs
  * Usage  : It prints the user details.
  * Return  : void
 **********************************************************************************************************************/
 public void userHeader(ResultSet rs) throws SQLException, IOException
 {
  PreparedStatement pstmt2;
  ResultSet rs2;
  blank();
  int nocount = 0;
  do{
   System.out.println(“n************************************************************************************************************************************”);
   System.out.println(“Name  : ” + rs.getString(1));
   System.out.println(“Email  : ” + rs.getString(2));
   System.out.println(“Credit Card : ” + rs.getString(3));
   System.out.println(“Phone  : ” + rs.getString(4));
   pstmt2 = con.prepareStatement(“SELECT street, city, country FROM address WHERE email = ? ORDER BY country asc”);
   pstmt2.setString(1, rs.getString(2));
   rs2 = pstmt2.executeQuery();
   for(int i = 1 ; rs2.next() ; i++){
    System.out.println(“Address ” + i + ” : ” + rs2.getString(1) + “, ” + rs2.getString(2) + “, ” + rs2.getString(3));
   }
   pstmt2.close();
   rs2.close();
   pstmt2 = con.prepareStatement(“SELECT interest FROM interest WHERE email = ? ORDER BY interest asc”);
   pstmt2.setString(1, rs.getString(2));
   rs2 = pstmt2.executeQuery();
   for(int i = 1 ; rs2.next() ; i++){
    System.out.println(“Interest ” + i + ” : ” + rs2.getString(1));
   }
   pstmt2.close();
   rs2.close();
   System.out.println(“*************************************************************************************************************************************n”);
   if((++nocount%2) == 0){
    System.out.println(“Press Enter to continue”);
    in.readLine();
    blank();
   }
  }while(rs.next());
   if((nocount%2) == 1){
    System.out.println(“Press Enter to continue”);
    in.readLine();
    blank();
   }
 }


 /**********************************************************************************************************************
  * Name   : openHeader
  * Parameter : ResultSet rs
  * Usage  : It prints the details of open auction.
  * Return  : void
 **********************************************************************************************************************/
 public void openHeader(ResultSet rs) throws IOException, SQLException
 {
  ResultSet rs2, rs3;
  PreparedStatement pstmt2, pstmt3;
  String date;
  int nocount = 0;
  blank();
  do{
   System.out.println(“n************************************************************************************************************************************”);
   System.out.println(“Auction id  : ” + rs.getString(1));
   System.out.println(“Initial Price  : ” + rs.getString(2));
   System.out.println(“CurrentPrice  : ” + rs.getString(3));
   System.out.println(“Open date  : ” + rs.getString(4));
   System.out.println(“Close date  : ” + rs.getString(5));
   System.out.println(“Seller   : ” + rs.getString(6));
   pstmt2 = con.prepareStatement(“SELECT i.name, i.description, i.quantity, i.location, i.shipping FROM item i, consist c WHERE c.item = i.name and c.id = ? ORDER BY i.name asc”);
   pstmt2.setString(1, rs.getString(1));
   rs2 = pstmt2.executeQuery();
   for(int i = 1 ; rs2.next() ; i++){
    System.out.println(“ITEM ” + i + ” name  : ” + rs2.getString(1));
    System.out.println(“ITEM ” + i + ” description : ” + rs2.getString(2));
    System.out.println(“ITEM ” + i + ” quantity  : ” + rs2.getString(3));
    System.out.println(“ITEM ” + i + ” location  : ” + rs2.getString(4));
    System.out.println(“ITEM ” + i + ” shipping  : ” + rs2.getString(5));
     pstmt3 = con.prepareStatement(“SELECT class FROM classify WHERE name = ? ORDER BY name asc”);
     pstmt3.setString(1, rs2.getString(1));
     rs3 = pstmt3.executeQuery();
     for(int j = 1 ; rs3.next() ; j++){
      System.out.println(“ITEM ” + i + ” Classification ” + j + ” : ” + rs3.getString(1));
     }
     pstmt3.close();
     rs3.close();
   }
   rs2.close();
   pstmt2.close();
   System.out.println(“*************************************************************************************************************************************n”);
   if((++nocount%2) == 0){
    System.out.println(“Press Enter to continue”);
    in.readLine();
    blank();
   }
  }while(rs.next());
  if((nocount%2) == 1){
   System.out.println(“Press Enter to continue”);
   in.readLine();
   blank();
  }
  rs.close();
 }
 
 /**********************************************************************************************************************
  * Name   : closeHeader
  * Parameter : ResultSet rs
  * Usage  : It prints the details of close auction.
  * Return  : void
 **********************************************************************************************************************/
 public void closeHeader(ResultSet rs) throws IOException, SQLException
 {
  ResultSet rs2, rs3;
  PreparedStatement pstmt2, pstmt3;
  String date;
  int nocount = 0;
  blank();
  do{
   System.out.println(“n******************************************************** ” + rs.getString(6) + ” *******************************************************************”);
   System.out.println(“Auction id  : ” + rs.getString(1));
   System.out.println(“Current Price  : ” + rs.getString(2));
   System.out.println(“Close date  : ” + rs.getString(3));
   System.out.println(“Seller   : ” + rs.getString(4));
   System.out.println(“Buyer   : ” + rs.getString(5));
   pstmt2 = con.prepareStatement(“SELECT i.name, i.description, i.quantity, i.location, i.shipping FROM item i, consist c WHERE c.item = i.name and c.id = ? ORDER BY i.name asc”);
   pstmt2.setString(1, rs.getString(1));
   rs2 = pstmt2.executeQuery();
   for(int i = 1 ; rs2.next() ; i++){
    System.out.println(“ITEM ” + i + ” name  : ” + rs2.getString(1));
    System.out.println(“ITEM ” + i + ” description : ” + rs2.getString(2));
    System.out.println(“ITEM ” + i + ” quantity  : ” + rs2.getString(3));
    System.out.println(“ITEM ” + i + ” location  : ” + rs2.getString(4));
    System.out.println(“ITEM ” + i + ” shipping  : ” + rs2.getString(5));
     pstmt3 = con.prepareStatement(“SELECT class FROM classify WHERE name = ? ORDER BY name asc”);
     pstmt3.setString(1, rs2.getString(1));
     rs3 = pstmt3.executeQuery();
     for(int j = 1 ; rs3.next() ; j++){
      System.out.println(“ITEM ” + i + ” Classification ” + j + ” : ” + rs3.getString(1));
     }
     pstmt3.close();
     rs3.close();
   }
   rs2.close();
   pstmt2.close();
   System.out.println(“*************************************************************************************************************************************n”);
   if((++nocount%2) == 0){
    System.out.println(“Press Enter to continue”);
    in.readLine();
    blank();
   }
  }while(rs.next());
  if(nocount%2 == 1){
  System.out.println(“Press Enter to continue”);
  in.readLine();
  blank();
  }
  rs.close();
 }


 /* What’s this? */
 private void processSQLWarning(SQLWarning warning)
 {
  if (warning != null)
  {
   System.err.println(“SQLWarning: “);
   do
   {
    System.err.println(“Message: ” + warning.getMessage());
    System.err.println(“SQL State: ” + warning.getSQLState());
    System.err.println(“Error Code: ” + warning.getErrorCode());
    warning = warning.getNextWarning();
   } while (warning != null);
  }
 }
 
 /**********************************************************************************************************************
  * Name   : welcome
  * Parameter : None
  * Usage  : It is a Login Page.  It lets current user to choose the function on login page.
  * Return  : void
 **********************************************************************************************************************/
 public void welcome() throws SQLException,IOException{
  blank();
  Calendar now = Calendar.getInstance();
  pstmt = con.prepareStatement(“UPDATE time SET hour = ?”);
  pstmt.setString(1, fhour.format(now.getTime()));
  pstmt.executeUpdate();
  pstmt.close();
  pstmt = con.prepareStatement(“SELECT TO_CHAR(day, ‘YYYYMMDD’), hour from time”);
  rs = pstmt.executeQuery();
  rs.next();
  System.out.println(” Today is ” + rs.getString(1));
  System.out.println(” Time now is ” + rs.getString(2) + “:” + fmin.format(now.getTime()));
  System.out.println();
  System.out.println(“What do u want, master?”);
  System.out.println(“1.login”);
  System.out.println(“2.Create account”);
  System.out.println(“3.Set current date”);
  System.out.println(“4.Sleep”);
  try{
   dream = Integer.parseInt(in.readLine().trim());
  }
  catch(NumberFormatException e){
   System.out.println(“You should enter either 1,2 or 3”);
   welcome();
   return;
  }
  switch(dream){
   case 1:
    login();
    break;
   case 2:
    creater();
    break;
   case 3:
    setDate();
    break;
   case 4:
    System.out.println(“Have a nice weekend!”);
    break;
   default:
    System.out.println(“Sorry, your choice is out of service”);
    welcome();
    break;
  }
 }


 /**********************************************************************************************************************
  * Name   : setDate
  * Parameter : None
  * Usage  : It is a function of Login Page.  It sets the current date.
  * Return  : void
 **********************************************************************************************************************/
 public void setDate() throws SQLException, IOException
 {
  String day;
  rs = stmt.executeQuery(“SELECT TO_CHAR(day, ‘YYYYMMDD’) FROM time”);
  rs.next();
  blank();
  System.out.println(“Welcome to overtime tunnel!!  Current date is ” + rs.getString(1) + “.  When do you want to go(YYYYMMDD)?”);
  while(true){
   while(!checkDate(day = in.readLine().trim())){
    if(day.equals(“”)){
     welcome();
     return;
    }
   }
   if((rs.getString(1)).compareTo(day)<0){
    try{
     pstmt = con.prepareStatement(“UPDATE time SET day = TO_DATE(?,’YYYYMMDD’)”);
     pstmt.setString(1, day);
     pstmt.executeUpdate();
     blank();
     System.out.println(“nUpdate finished!!n”);
     System.out.println(“Press ENTER to continue”);
     in.readLine();
     trimDown();
     welcome();
     break;
    }catch(SQLException e){
     blank();
     System.out.println(“Do you come from Mars?nLook at the calender and reply me again”);
     System.out.println(“Press ENTER to continue”);
     in.readLine();
     blank();
    }
   }
   else{
    System.out.println(“You can’t go back to the past”);
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : checkDate
  * Parameter : String nday
  * Usage  : It checks the correctness of date format.
  * Return  : boolean
        – true if the date format is correct
 **********************************************************************************************************************/
 public boolean checkDate(String nday){
  if(nday.length() == 0){
   return false;
  }
  if(!(nday.length() == 8 && Character.isDigit(nday.charAt(0))
     && Character.isDigit(nday.charAt(1))
     && Character.isDigit(nday.charAt(2))
     && Character.isDigit(nday.charAt(3))
     && Character.isDigit(nday.charAt(4))
     && Character.isDigit(nday.charAt(5))
     && Character.isDigit(nday.charAt(6))
     && Character.isDigit(nday.charAt(7)))){
   System.out.println(“Date format should be in YYYYMMDD”);
   return false;
  }
  int year = Integer.parseInt(nday.substring(0,4));
  int month = Integer.parseInt(nday.substring(4,6));
  int day = Integer.parseInt(nday.substring(6,8));
  boolean bol = true;
  if(month == 2){
   if(day == 29){
    if((year%4) != 0){
     bol = false;
    }
    else if(year%100 == 0 && year%400 != 0){
     bol = false;
    }
   }
   else if(day > 29){
    bol = false;
   }
  }
  else if(month == 4 || month == 6 || month == 9 || month == 11){
   if(day > 30){
    bol = false;
   }
  }
  else if(month > 12){
   bol = false;
  }
  else if(day > 31){
   bol = false;
  }
  if(!bol){
   System.out.println(“Do you come from Mars?nLook at the calender and reply me again”);
  }
  return bol;
 }


 /**********************************************************************************************************************
  * Name   : login
  * Parameter : None
  * Usage  : It is a function of Login Page.  It lets current user to login
  * Return  : boolean
        – true if the date format is correct
 **********************************************************************************************************************/
 public void login() throws SQLException,IOException{
  blank();
  System.out.println(“Please enter your login name”);
  String name = in.readLine().trim();
  String email;
  if(name.equals(“admin”)){
   System.out.println(“what’s your email?”);
   email = in.readLine().trim();
   if (email.equals(“root”)){
    System.out.println(“Hello evil leo!!!!!”);
    admin();
   }
   else{
    System.out.println(“Don’t fool me, I am evil leo fans!!!”);
    welcome();
   }
  }
  else{
   pstmt = con.prepareStatement(“SELECT email FROM users where name = ?”);
   pstmt.setString(1, name);
   rs = pstmt.executeQuery();
   processSQLWarning(stmt.getWarnings());
   boolean bol = false;
   if (!rs.next()){
    System.out.println(“Name not found”);
    welcome();
   }
   else{
    System.out.println(“Hi, ” + name + “, what’s your email?”);
    email = in.readLine().trim();
    do{ 
     if (email.equals(rs.getString(1))){
      bol = true;
      user = new PowerUser();
      user.setUserName(name);
      user.setUserEmail(email);
      blank();
      query();
      break;
     }
    }while(rs.next());
    if(!bol){
     System.out.println(“Email does not match.”);
     welcome();
    }
   }
  }


 }


 /**********************************************************************************************************************
  * Name   : admin
  * Parameter : None
  * Usage  : This is Administrative Page.  It lets administrator to choose the function on Administrative Auction to run,
        link to other pages, logout and exit.
  * Return  : None
 **********************************************************************************************************************/
 public void admin() throws NumberFormatException, IOException, SQLException
 {
  blank();
  System.out.println(” This is Administrative Page”);
  System.out.println(“What do you want, evil leo?”);
  System.out.println(“1. Trim Down Storage Space Usage”);
  System.out.println(“2. Mine Anomalous Activities”);
  System.out.println(“3. Logout”);
  System.out.println(“4. Sleep”);
  try{
   dream = Integer.parseInt(in.readLine().trim());
  }catch(NumberFormatException e){
   System.out.println(“You should enter 1, 2, 3 or 4”);
   admin();
   return;
  }
  switch(dream){
   case 1:
    System.out.println(“This is a rubbish function.  If I were you, I won’t ask my students to do that.”);
    trimDown();
    blank();
    System.out.println(“Trim Down Successful”);
    System.out.println(“Press ENTER to continue”);
    in.readLine();
    admin();
    break;
   case 2:
    anom();
    admin();
    break;
   case 3:
    blank();
    System.out.println(“nLogout Successfuln”);
    System.out.println(“Press ENTER to continue”);
    in.readLine();
    welcome();
    break;
   case 4:
    System.out.println(“nHave a good sleepn”);
    break;
   default:
    System.out.println(“We do not have so many function”);
    admin();
  }
 }


 /**********************************************************************************************************************
  * Name   : stat
  * Parameter : None
  * Usage  : This is Statistics Page.  It lets user to choose the function on Statistics Page to run,
        link to other pages, logout and exit.
  * Return  : None
 **********************************************************************************************************************/
 public void stat() throws NumberFormatException, IOException, SQLException
 {
  System.out.println(”  Statistics Pagenn”);
  System.out.println(“What do you want, ” + user.getUserName() + “?n”);
  System.out.println(“1. Show the top three hot auctions”);
  System.out.println(“2. Show the top three highest bids in every country”);
  System.out.println(“3. Show the top three generous buyers”);
  System.out.println(“4. Show the top three successful seller”);
  System.out.println(“5. Go to Alter Existing User Page”);
  System.out.println(“6. Go to Query Auction Page”);
  System.out.println(“7. Go to Create Auction Page”);
  System.out.println(“8. Logout”);
  System.out.println(“9. Sleep”);
  try{
   dream = Integer.parseInt(in.readLine().trim());
   blank();
  }catch(NumberFormatException e){
   System.out.println(“You should enter either 1, 2, 3, 4, 5, 6, 7, 8 or 9”);
   stat();
   return;
  }
  switch(dream){
   case 1:
    hotAuctStat();
    stat();
    break;
   case 2:
    highBidStat();
    stat();
    break;
   case 3:
    genBuyStat();
    stat();
    break;
   case 4:
    succSellStat();
    stat();
    break;
   case 5:
    alter();
    break;
   case 6:
    query();
    break;
   case 7:
    createAuction();
    break;
   case 8:
    System.out.println(“nLogout Successfuln”);
    welcome();
    break;
   case 9:
    System.out.println(“nHave a good sleepn”);
    break;
   default:
    System.out.println(“We do not have so many function”);
    stat();
  }
 }


 /**********************************************************************************************************************
  * Name   : hotAuctStat
  * Parameter : None
  * Usage  : It is a function of Statistics Page.  It shows those open auctions with number of bids as least as
        high as the third highest
  * Return  : void
 **********************************************************************************************************************/
 public void hotAuctStat() throws SQLException, IOException
 {
  PreparedStatement pstmt = con.prepareStatement(“SELECT DISTINCT b.id, count(b.email) FROM bid b, open o where o.id = b.id group by b.id order by count(b.email) desc”);
  rs = pstmt.executeQuery();
  int top = 0;
  int counter = 0;
  int counter2 = 0;
  String[] topID = new String[size];
  if(rs.next()){
   for(counter2 = 0;rs.next(); counter2++){
    if(top != Integer.parseInt(rs.getString(2))){
     if(++counter == 4){
      break;
     }
     top = Integer.parseInt(rs.getString(2));
     }
     topID[counter2] = rs.getString(1);
   }
   pstmt.close();
   rs.close();
   pstmt = con.prepareStatement(“SELECT DISTINCT a.id, o.initial_price, o.current_price, TO_CHAR(o.open, ‘YYYYMMDD’), TO_CHAR(a.close, ‘YYYYMMDD’), a.seller FROM auction a, open o where a.id = o.id and a.id = ? order by a.id desc”);
   for(int i = 0; i<counter2;i++){
    pstmt.setString(1, topID[i]);
    rs = pstmt.executeQuery();
    rs.next();
    openHeader(rs);
    rs.close();
   }
  }
 }


 /**********************************************************************************************************************
  * Name   : highBidStat
  * Parameter : None
  * Usage  : It is a function of Statistics Page.  It shows
  * those closed auction with current (final) price at least as high as the third highest price.
  * Return  : void
 **********************************************************************************************************************/
 public void highBidStat() throws SQLException, IOException
 {
  String country[] = new String[size];
  int counter = 0, counter2 = 0;
  float top = 0;
  String[] topID = new String[size];
  rs = stmt.executeQuery(“SELECT DISTINCT country FROM address order by country asc”);
  for(counter = 0;rs.next();counter++){
   country[counter] = rs.getString(1);
  }
  for(int i = 0; i < counter ; i++){
   pstmt = con.prepareStatement(“SELECT DISTINCT s.id, s.final FROM closed s, auction a, address d where s.id = a.id and d.email = s.buyer and d.country = ? order by s.final desc”);
   pstmt.setString(1, country[i]);
   rs = pstmt.executeQuery();
   int counter3 = 0;
   for(counter2 = 0;rs.next(); counter2++){
    if(top != Float.parseFloat(rs.getString(2))){
     if(++counter3 == 4){
      break;
     }
     top = Float.parseFloat(rs.getString(2));
     }
     topID[counter2] = rs.getString(1);
   }
   pstmt.close();
   pstmt = con.prepareStatement(“SELECT DISTINCT s.id, s.final, TO_CHAR(a.close, ‘YYYYMMDD’), a.seller, s.buyer, d.country FROM closed s, auction a, address d where s.id = a.id and s.buyer = d.email and s.id = ? order by s.id asc”);
   for(int j = 0 ; j < counter2 ; j++){
    pstmt.setString(1, topID[j]);
    rs = pstmt.executeQuery();
    rs.next();
    closeHeader(rs);
    rs.close();
    }
   }
  }


 /**********************************************************************************************************************
  * Name   : highBidStat
  * Parameter : None
  * Usage  : It is a function of Statistics Page.  It shows those closed auction with current (final) price at least as high as the third highest price.
  * Return  : void
 **********************************************************************************************************************/
 public void genBuyStat() throws SQLException, IOException
 {
  PreparedStatement pstmt = con.prepareStatement(“SELECT DISTINCT buyer, SUM(final) FROM closed GROUP BY buyer ORDER BY SUM(final) desc”);
  rs = pstmt.executeQuery();
  int counter = 0;
  String[] buyer = new String[size];
  String[] sum = new String[size];
  String top = “”;
   int counter2 = 0;
   for(counter = 0 ; rs.next() && counter2 < 3;){
    buyer[counter] = rs.getString(1);
    sum[counter] = rs.getString(2);
    if(!(top.equals(sum[counter++]))){
     counter2++;
    }
   }
   rs.close();
   pstmt.close();
   for(int i = 0 ; i< counter ; i++){
    pstmt = con.prepareStatement(“SELECT DISTINCT name, email, credit_card, phone FROM users WHERE email = ?”);
    pstmt.setString(1, buyer[i]);
    rs = pstmt.executeQuery();
    rs.next();
    userHeader(rs);
    rs.close();
   }
  }


 /**********************************************************************************************************************
  * Name   : succSellStat
  * Parameter : None
  * Usage  : It is a function of Statistics Page.  It shows hte sum of current (final) prices as least as high
        as the third highest.
  * Return  : void
 **********************************************************************************************************************/
 public void succSellStat() throws SQLException, IOException
 {
  PreparedStatement pstmt = con.prepareStatement(“SELECT DISTINCT a.seller, SUM(s.final) FROM auction a ,closed s where a.id = s.id GROUP BY a.seller ORDER BY SUM(s.final) desc”);
  rs = pstmt.executeQuery();
  int counter = 0;
  String[] seller = new String[size];
  String[] sum = new String[size];
  String top = “”;
   int counter2 = 0;
   for(counter = 0 ; rs.next() && counter2 < 3;){
    seller[counter] = rs.getString(1);
    sum[counter] = rs.getString(2);
    if(!(top.equals(sum[counter++]))){
     counter2++;
    }
   }
   rs.close();
   pstmt.close();
   for(int i = 0 ; i< counter ; i++){
    pstmt = con.prepareStatement(“SELECT DISTINCT name, email, credit_card, phone FROM users WHERE email = ?”);
    pstmt.setString(1, seller[i]);
    rs = pstmt.executeQuery();
    if(rs.next()){
     userHeader(rs);
    }
    rs.close();
   }
  }


 /**********************************************************************************************************************
  * Name   : creater
  * Parameter : None
  * Usage  : It is a function of Login Page.  It lets users to create a new account in the system.
  * Return  : void
 **********************************************************************************************************************/
 public void creater() throws SQLException,IOException{
  blank();
  PreparedStatement pstmt = con.prepareStatement(“INSERT INTO users VALUES (?, ?, ?, ?)”);
  processSQLWarning(con.getWarnings());
  System.out.println(“What’s your name? (leave blank to quit)”);
  String name = in.readLine().trim();
  if(name.equals(“”)){
   welcome();
   return;
  }
  while (name.equals(“admin”)){
   System.out.println(“Your should not use “admin” as your name”);
   System.out.println(“What’s your name?”);
   name = in.readLine().trim();
  }
  pstmt.setString(1, name);
  System.out.println(“What’s your email?(leave blank to quit)”);
  String email = “”;
  String temp = “”;
  while (true){
   while((email = in.readLine().trim()).length() > 64){
    System.out.println(“Your email is too long”);
   }
   while (email.equals(“root”)){
    System.out.println(“Your should not use “root” as your email address”);
    System.out.println(“What’s your email?(leave blank to quit)”);
    email = in.readLine().trim();
   }
   PreparedStatement pstmt2 = con.prepareStatement(“SELECT email from users where email = ?”);
   pstmt2.setString(1, email);
   rs = pstmt2.executeQuery();
   if(rs.next()){
    System.out.println(“Email exists”);
    System.out.println(“What’s your email?(leave blank to quit”);
   }
   else break;
  }
  if(email.equals(“”)){
   welcome();
   return;
  }
  pstmt.setString(2, email);
  temp = “”;
  System.out.println(“What is your credit card number?(leave blank to quit)”);
  while(!checkCreditCardNumber(temp = in.readLine().trim())){
   if (temp.equals(“”)){
    break;
   }
   System.out.println(“Tell me the truth. What is your credit card number?”);
  }
  if (temp.equals(“”)){
   welcome();
   return;
  }
  pstmt.setString(3, temp);
  temp = “”;
  System.out.println(“What is your phone number?”);
  while(!checkPhoneNumber(temp = in.readLine().trim())){
   if(temp.equals(“”)){
    break;
   }
   System.out.println(“What is your phone number?”);
  }
  pstmt.setString(4, temp);
  pstmt.executeUpdate();
  processSQLWarning(pstmt.getWarnings());
  pstmt.close();
  user = new PowerUser();
  user.setUserEmail(email);
  user.setUserName(name);
  pstmt = con.prepareStatement(“INSERT INTO address VALUES (?, ?, ?, ?)”);
  processSQLWarning(con.getWarnings());
  pstmt.setString(1,email);
  String yn, street = “”, city = “”,country = “”;
  while(true){
   while (true){
    System.out.println(“Do u want to add an address?(y/n)”);
    yn = in.readLine().trim();
    if(!yn.equals(“y”) && !yn.equals(“n”)){
     System.out.println(“I am serious!!!! Please input y or n”);
    }
    else{
     break;
    }
   }
   if(yn.equals(“n”)){
    break;
   }
   else{
    addAddress();
   }
  }
  pstmt.close();
  System.out.println(“New User added successfully!”);
  rs.close();
  query();
 }
}
/* vi: set sw=4 ts=4: */


Advertisements