// A. Toy, A10 for CISC474, Spring 2006
// LoginExpert class
// Modified: 5/8/06

package com.gmscheduler.model;
import java.sql.*;
import java.util.*;

public class LoginExpert
{
   // Create connection and statement objects
   Connection myCon;
   Statement myStmt;

   String user;			//string passed from the JSP
   String password;		//string passed from the JSP
   boolean success = false;	//true if user/password combination is correct

   // Create a new user
   public boolean createUser(String u, String p)
   {
      try
      {
         connecter();

         // Check if the user already exists 
         // Create the SELECT command as a String
         final String select_sql = "SELECT * from Users where Username = '" 
            + u + "'";
         ResultSet result = myStmt.executeQuery(select_sql);
         if (result.next())	//user already exists
            return false;
         else			//create user
         {
            // Create the INSERT command as a String
            final String insert_sql = "INSERT into Users (Username, Password) values ('"
               + u + "','" + p + "')";

            // Perform the insertion
            myStmt.execute(insert_sql);
         }
         closer();
      }
   
      catch (SQLException sqlEx)
      {
         System.err.println(sqlEx);
      }

      return true;
   }

   // Return a list of items that match the passed category
   public boolean login(String u, String p)
   {
      user = u;
      password = p;
      Query();
      return(success);
   }

   // Query the database for the specified items
   void Query()
   {          
      try
      {
         connecter();
         select();
         closer();
      }
   
      catch (SQLException sqlEx)
      {
         System.err.println(sqlEx);
      }
   }   // end Query()
      
   // connecter method
   //   Register the database driver and create the database
   //   connection and query statement objects
   void connecter() throws SQLException
   {
      try
      {
         // Register the MySQL database driver
         Class.forName("org.gjt.mm.mysql.Driver").newInstance();
         
         // Create the connection object
         final String URL="jdbc:mysql://jaguar.cis.udel.edu:8099/toy";
         final String username="toy"; 
         final String password="h221dc"; 
         
         myCon = DriverManager.getConnection(URL, username, password);
         
         // Create the statement object for the query
         myStmt = myCon.createStatement();
         
      } // end of try clause
      
      catch (SQLException sqlEx)
      {
         throw sqlEx;
      }
      
      catch (Exception e)
      {
         System.err.println(e);
      }
      
   } // end of the connecter method
   
   // select method - create the SQL command string,
   // execute it, and display the results
   void select() throws SQLException
   {
      // Create the SELECT command as a String
      final String select_sql = "SELECT * from Users";

      // Perform the query
      ResultSet result = myStmt.executeQuery(select_sql);
      
      // add results to list if category matches
      while (result.next())
      {
         // username/password combination correct
         if ((result.getString("Username").compareTo(user) == 0) 
            &&(result.getString("Password").compareTo(password) == 0))
            success = true;
         //else password incorrect
      }
   } // select() method
   
   // closer method - close the database connection
   void closer() throws SQLException
   {
      myCon.close();
   }    
}
