package databaseoperate;

import baseclass.WarningFrame;
import java.util.*;
import java.net.URL;
import java.sql.*;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import baseclass.UserBook;
import javax.swing.JOptionPane;
import librarysystem.PayFrame;

public class DatabaseProcedure
{
  public static void borrowBook(UserBook userBook)
  {
    try
    {
      ResultSet tSet=null;
      String sql="{call borrowBook(?,?,?)}";
      CallableStatement cstmt=DatabaseConnection.connection.prepareCall(sql);

      cstmt.setString(1,userBook.getUserID());
      cstmt.setString(2,userBook.getUserPWD());
      cstmt.setInt(3,userBook.getBookIndex());

      cstmt.executeQuery();
      tSet=cstmt.getResultSet();

      ResultSetMetaData rsmd=tSet.getMetaData();

      tSet.next();
      String abc;

      int whetherValidUser=tSet.getInt("userID");
      int whetherRightPWD=tSet.getInt("pwd");
      int whetherToBorrow=tSet.getInt("num");
      int whetherHaveBook=tSet.getInt("bookIndex");
      int overDays= tSet.getInt("days");
      int whetherOverDate=tSet.getInt("overBook");


      if(whetherValidUser==0)
          WarningFrame.warningFrame("You are not the valid user!");
      else if(whetherRightPWD==0)
          WarningFrame.warningFrame("The password is wrong!");
      else if(whetherOverDate==0)
          WarningFrame.warningFrame("You have book over time!");
      else if(whetherToBorrow==0)
          WarningFrame.warningFrame("The number of book you borrowed reach the top!");
      else if(whetherHaveBook==0)
          WarningFrame.warningFrame("book index is wrong!");
      else if(whetherHaveBook==2)
          WarningFrame.warningFrame("This book has already been ordered!");
      else if(whetherHaveBook==3)
          WarningFrame.warningFrame("This book has already been borrowed!");
      else
          WarningFrame.warningFrame("Borrow successful!");


    }
    catch(SQLException ex)
    {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
    }
  }

  /////////////////////////////////////////////////////////////////////////////
  //call procedure returnBook for returning books
  /////////////////////////////////////////////////////////////////////////////

  public static void returnBook(UserBook userBook,int check)
  {
      try
    {
        ResultSet tSet = null;
        String sql = "{call returnBook(?,?,?,?)}";
        CallableStatement cstmt = DatabaseConnection.connection.prepareCall(sql);
        cstmt.setString(1, userBook.getUserID());
        cstmt.setString(2, userBook.getUserPWD());
        cstmt.setInt(3, userBook.getBookIndex());
        cstmt.setInt(4,check);

        cstmt.executeQuery();
        tSet = cstmt.getResultSet();

        ResultSetMetaData rsmd = tSet.getMetaData();

        tSet.next();
        int whetherValidUser=tSet.getInt("userID");
        int whetherRightPWD=tSet.getInt("pwd");
        int whetherToBorrow=tSet.getInt("num");
        int whetherHaveBook=tSet.getInt("bookIndex");
        int overDays= tSet.getInt("days");
        int whetherOverDate=tSet.getInt("overBook");

        if(whetherValidUser==0)
            WarningFrame.warningFrame("You are not the valid user!");
        else if(whetherRightPWD==0)
            WarningFrame.warningFrame("The password is wrong!");
        else if(whetherHaveBook==0)
            WarningFrame.warningFrame("book index is wrong!");
        else if(whetherToBorrow==0)
            WarningFrame.warningFrame("You haven't borrowed this book!");

        else if(overDays!=0)
        {
            PayFrame payFrame=new PayFrame(overDays);
            payFrame.userBook=userBook;
            payFrame.setVisible(true);
        }
        else
            WarningFrame.warningFrame("Return successful!");
    }
    catch(SQLException ex)
    {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
    }

  }

  /////////////////////////////////////////////////////////////////////////////
  //call procedure orderBook for returning books
  /////////////////////////////////////////////////////////////////////////////

  public static void orderBook(UserBook userBook)
  {
      try
    {
        ResultSet tSet = null;
        String sql = "{call orderBook(?,?,?)}";
        CallableStatement cstmt = DatabaseConnection.connection.prepareCall(sql);
        cstmt.setString(1, userBook.getUserID());
        cstmt.setString(2, userBook.getUserPWD());
        cstmt.setInt(3, userBook.getBookIndex());

        cstmt.executeQuery();
        tSet = cstmt.getResultSet();

        ResultSetMetaData rsmd = tSet.getMetaData();

        tSet.next();
        int whetherValidUser=tSet.getInt("userID");
        int whetherRightPWD=tSet.getInt("pwd");
        int whetherToBorrow=tSet.getInt("num");
        int whetherHaveBook=tSet.getInt("bookIndex");
        int overDays= tSet.getInt("days");
        int whetherOverDate=tSet.getInt("overBook");

        if(whetherValidUser==0)
            WarningFrame.warningFrame("You are not the valid user!");
        else if(whetherRightPWD==0)
            WarningFrame.warningFrame("The password is wrong!");
        else if(whetherOverDate==0)
            WarningFrame.warningFrame("You have book over time!");
        else if(whetherHaveBook==0)
            WarningFrame.warningFrame("book index is wrong!");

        else if(whetherToBorrow==0)
            WarningFrame.warningFrame("The book is at library!");
        else if(whetherToBorrow==2)
            WarningFrame.warningFrame("This book has already been ordered!");
        else
            WarningFrame.warningFrame("Order successful!");

    }
    catch(SQLException ex)
    {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
    }

  }

  /////////////////////////////////////////////////////////////////////////////
  //call procedure reborrowBook for returning books
  /////////////////////////////////////////////////////////////////////////////


  public static void reborrowBook(UserBook userBook)
  {
      try
    {
        ResultSet tSet = null;
        String sql = "{call reborrowBook(?,?,?)}";
        CallableStatement cstmt = DatabaseConnection.connection.prepareCall(sql);
        cstmt.setString(1, userBook.getUserID());
        cstmt.setString(2, userBook.getUserPWD());
        cstmt.setInt(3, userBook.getBookIndex());

        cstmt.executeQuery();
        tSet = cstmt.getResultSet();

        ResultSetMetaData rsmd = tSet.getMetaData();

        tSet.next();
        int whetherValidUser=tSet.getInt("userID");
        int whetherRightPWD=tSet.getInt("pwd");
        int whetherToBorrow=tSet.getInt("num");
        int whetherHaveBook=tSet.getInt("bookIndex");
        int overDays= tSet.getInt("days");
        int whetherOverDate=tSet.getInt("overBook");

        if(whetherValidUser==0)
            WarningFrame.warningFrame("You are not the valid user!");
        else if(whetherRightPWD==0)
            WarningFrame.warningFrame("The password is wrong!");
        else if(whetherOverDate==0)
            WarningFrame.warningFrame("You have book over time!");
        else if(whetherHaveBook==0)
            WarningFrame.warningFrame("book index is wrong!");
        else if(overDays==0)
            WarningFrame.warningFrame("You haven't borrow this book！");
        else if(overDays==2)
            WarningFrame.warningFrame("未到續借時間！");
        else
            WarningFrame.warningFrame("續借成功！");

    }
    catch(SQLException ex)
    {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
    }

  }




}