package databaseoperate;

import baseclass.Worker;
import java.sql.SQLException;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
import baseclass.User;
import baseclass.Books;
import java.util.Vector;


/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class DatabaseQuery {
    public DatabaseQuery() {
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static ResultSet rSet = null;    // 定義數(shù)據(jù)庫查詢的結(jié)果集
    public static ResultSet rSet1=null;
    public static Vector vertor=new Vector();

    public static boolean workerListQuery(Worker worker)
    {
        try
        {
            String sql="select * from WorkerList where workerID='"+worker.getWorkerID()+"'";
            rSet=DatabaseConnection.statement.executeQuery(sql);
            if(rSet.next()==false)
            {
                JOptionPane.showOptionDialog(null, "the ID is wrong!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;
            }

            String password=rSet.getString("workerPWD");
            //System.out.println("asdf");
            int type=rSet.getInt("workerType");

            //////////////////////////////////////////////////////
            //check the popedom!
            if(type!=worker.getWorkerType())
            {
                JOptionPane.showOptionDialog(null, "you don't have the popedom!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;

            }
            //////////////////////////////////////////////////////
            //check the password
            if(!worker.getWorkerPWD().equals(password))
            {
                JOptionPane.showOptionDialog(null, "the password is wrong!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;
            }

        }
        catch(SQLException ex)
        {                                   // 捕捉異常
            System.out.println("\nERROR:----- SQLException -----\n");
            while (ex != null)
            {
                System.out.println("Message:   " + ex.getMessage());
                System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
            }
        }
        return true;
    }


    /////////////////////////////////////////////////////////////////////////
    //query UserList check whether the register ID has already existed
     /////////////////////////////////////////////////////////////////////////
    public static boolean userListQuery(User user)
    {
        try
        {
            String sql="select * from UserList where userID='"+user.getUserID()+"'";
            rSet=DatabaseConnection.statement.executeQuery(sql);
            if(rSet.next()==true)
            {
                JOptionPane.showOptionDialog(null, "the ID exist!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;
            }

        }
        catch(SQLException ex)
        {                                   // 捕捉異常
            System.out.println("\nERROR:----- SQLException -----\n");
            while (ex != null)
            {
                System.out.println("Message:   " + ex.getMessage());
                System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
            }
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////
    //query UserList whether the ID is wrong
     /////////////////////////////////////////////////////////////////////////
     public static boolean userListQueryForWhetherWright(User user)
     {
         try
        {
            String sql="select * from UserList where userID='"+user.getUserID()+"'";
            rSet=DatabaseConnection.statement.executeQuery(sql);
            if(rSet.next()!=true)
            {
                JOptionPane.showOptionDialog(null, "the ID is wrong!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;
            }
            if(!user.getUserPWD().equals(rSet.getString("userPWD")))
            {
                JOptionPane.showOptionDialog(null, "the Password is wrong!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;
            }

        }

         catch(SQLException ex)
        {                                   // 捕捉異常
            System.out.println("\nERROR:----- SQLException -----\n");
            while (ex != null)
            {
                System.out.println("Message:   " + ex.getMessage());
                System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
            }
        }
        return true;

     }

    /////////////////////////////////////////////////////////////////////////
    //query UserList to gain the information of User with the input ID
     /////////////////////////////////////////////////////////////////////////
     public static void userListQueryForInformation(User user)
     {
         try
        {
            String sql="select * from UserList where userID='"+user.getUserID()+"'";
            rSet=DatabaseConnection.statement.executeQuery(sql);
            if(rSet.next()==true)
            {
                user.setUserType(rSet.getString("userType"));
                user.setUserName(rSet.getString("userName"));
                user.setUserEmail(rSet.getString("userEmail"));
                user.setNumOfBook(rSet.getInt("numOfBook"));
                user.setSumOfBook(rSet.getInt("sumOfBook"));
                user.setUserSex(rSet.getString("userSex"));
                user.setUserDept(rSet.getString("userDept"));

                rSet.close();                                       // 關(guān)閉結(jié)果集


            }
        }

         catch(SQLException ex)
        {                                   // 捕捉異常
            System.out.println("\nERROR:----- SQLException -----\n");
            while (ex != null)
            {
                System.out.println("Message:   " + ex.getMessage());
                System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
            }
        }
        //return user;

     }


    ////////////////////////////////////////////////////////////////////////////
    //BooksList query
     ////////////////////////////////////////////////////////////////////////////
    public static boolean booksListQuery(Books books)
    {
        try
        {
            String sql="select * from BooksList where ISBN='"+books.getISBN()+"'";
            rSet=DatabaseConnection.statement.executeQuery(sql);
            //System.out.println(books.getBookName()+books.getBookType());
            if(rSet.next()==true)
            {
                JOptionPane.showOptionDialog(null, "the Book exist,can't add the same book in two month!",
                                      "WARNING",
                                      JOptionPane.OK_CANCEL_OPTION,
                                      JOptionPane.WARNING_MESSAGE, null, null, null);
                return false;
            }

        }
        catch(SQLException ex)
        {                                   // 捕捉異常
            System.out.println("\nERROR:----- SQLException -----\n");
            while (ex != null)
            {
                System.out.println("Message:   " + ex.getMessage());
                System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
            }
        }
        return true;

    }

    ////////////////////////////////////////////////////////////////////////////
    // query BooksList to whether the book exist with the input index
     ////////////////////////////////////////////////////////////////////////////
     public static boolean bookInformationQueryForWhetherExistWithIndex(Books book)
    {
        try
        {
            String sql="select * from BookInformation where bookIndex="+book.book.getBookIndex();
            rSet=DatabaseConnection.statement.executeQuery(sql);
            if(rSet.next()==false)
            {
                rSet.close();
                return false;
            }
        }
        catch(SQLException ex)
        {                                   // 捕捉異常
            System.out.println("\nERROR:----- SQLException -----\n");
            while (ex != null)
            {
                System.out.println("Message:   " + ex.getMessage());
                System.out.println("SQLState:  " + ex.getSQLState());
                System.out.println("ErrorCode: " + ex.getErrorCode());
                ex = ex.getNextException();
            }
        }

        return true;
    }

    /////////////////////////////////////////////////////////////////////////
    //query bookInformation to gain the information of one book with the input Index
     /////////////////////////////////////////////////////////////////////////
     public static Vector bookInformationQueryForInformationWithIndex(Books books,Vector vector)
     {
         Books bookOne=new Books();