/* -- John Wilkinson (MS50)
   -- Databases (COMS 30103) - Dr. J.Gallagher
   -- Video Shop - Assignment 2
   ------------------------------------------------------------------------
   -- This is a simple user interface for the Video shop transactions
   ------------------------------------------------------------------------ */


import java.sql.*;
import java.io.*;

public class videoshop
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    String user, pass;
      user = readEntry("userid : ");
      pass = readEntry("password : ");
    Connection conn =
      DriverManager.getConnection (
        "jdbc:oracle:thin:@tao:1521:msc98",user, pass);
      conn.setAutoCommit(false);

    // Display menu
   
        System.out.println ("***************************");
        System.out.println ("*                         *");
        System.out.println ("*     VIDEO RENTALS       *");
        System.out.println ("*                         *");
        System.out.println ("***************************");
        System.out.println (" ");
        System.out.println (" ");
        System.out.println (" 1. Rent a video:");
        System.out.println (" 2. Return a video:");
        System.out.println (" 3. Report overdue videos:");
        
        String choice;
        int nchoice = 0;
        do {
                System.out.println (" ");
                choice = readEntry (" Please enter a choice (0 to stop): ");
                try{
                        nchoice = Integer.parseInt(choice);
                        if (nchoice == 1) rentVideo(conn);
                        else 
                        if (nchoice == 2) returnVideo(conn);
                        else 
                        if (nchoice == 3) reportOverdueVideos(conn);
                        else
                        if (nchoice != 0)
                        System.out.println (" Invalid choice. Try again.");}
                        catch (NumberFormatException e) {
                                System.out.println("Invalid number.");}
        } while (nchoice != 0);
        conn.close();
   }


// Procedure to rent a video
// Assumes a PL/SQL stored procedure called rentvideo(?,?)
// First parameter is a video title
// Second parameter is a member number
// Results are returned via a relation called messages

static void rentVideo(Connection conn)
    throws SQLException
    {
        String videotitle;
        String membernum;
        System.out.println ("**************************");
        System.out.println ("      Rent a video        ");
        System.out.println ("**************************");
        System.out.println (" ");
        videotitle = readEntry("Enter a video title: ");
        membernum = readEntry("Enter a member number: ");
        System.out.println (" Executing transaction...");
        try{
        CallableStatement rentproc = 
                conn.prepareCall ("begin rentvideo(?,?); end;");
        rentproc.setString(1,videotitle);
        rentproc.setString(2,membernum);
        rentproc.execute ();
        rentproc.close();}
                catch (SQLException e) {
                while (e != null) {
                        System.out.println("Error Message: "+e.getMessage());
                        e = e.getNextException();
                        }
                }
        System.out.println (" ");
        dumpMessages(conn);
    }

// Procedure to return a video
// Assumes a PL/SQL stored procedure called returnvideo(?,?)
// First parameter is a video title
// Second parameter is a member number
// Results are returned via a relation called messages

static void returnVideo(Connection conn)
    throws SQLException
    {
        String videotitle;
        String membernum;
        System.out.println ("**************************");
        System.out.println ("      Return a video      ");
        System.out.println ("**************************");
        System.out.println (" ");
        videotitle = readEntry("Enter a video title: ");
        membernum = readEntry("Enter a member number: ");
        System.out.println (" Executing transaction...");
        try{
        CallableStatement returnproc = 
                conn.prepareCall ("begin returnvideo(?,?); end;");
        returnproc.setString(1,videotitle);
        returnproc.setString(2,membernum);
        returnproc.execute ();
        returnproc.close();}
                catch (SQLException e) {
                while (e != null) {
                        System.out.println("Error Message: "+e.getMessage());
                        e = e.getNextException();
                        }
                }
        System.out.println (" ");
        dumpMessages(conn);
    }

// Procedure to report overdue videos (at a given date)
// Assumes a PL/SQL stored procedure called overduevideos(?)
// First parameter is a date
// Results are returned via a relation called messages

static void reportOverdueVideos(Connection conn)
    throws SQLException
    {
        String duedate;
        System.out.println ("**************************");
        System.out.println ("   Report overdue videos  ");
        System.out.println ("**************************");
        System.out.println (" ");
        duedate = readEntry("Enter a date (DD-MM-YYYY): ");
        System.out.println (" Executing transaction...");
        try{
        CallableStatement overdueproc = 
                conn.prepareCall ("begin reportOverdueVideos(?); end;");
        overdueproc.setString(1,duedate);
        overdueproc.execute ();
        overdueproc.close();}
                catch (SQLException e) {
                while (e != null) {
                        System.out.println("Error Message: "+e.getMessage());
                        e = e.getNextException();
                        }
                }
        System.out.println (" ");
        dumpMessages(conn);
    }

  // Utility function to dump the contents of the message table and
  // clear it
  static void dumpMessages (Connection conn)
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from message_s");
    while (rset.next ())
      System.out.println (rset.getString (1));
    stmt.execute ("delete from message_s");
    stmt.execute ("commit");
    stmt.close();
  }


  // readEntry  -- to read username and password etc.
 static String readEntry(String prompt) 
        {
        try {
                StringBuffer buffer = new StringBuffer();
                System.out.print(prompt);
                System.out.flush();
                int c = System.in.read();
                while (c != '\n' && c != -1) {
                        buffer.append((char)c);
                        c = System.in.read();
                }
                return buffer.toString().trim();
        }
        catch (IOException e) {
                return "";
        }
        }
}