/**********************************
 * FileName:BookBorrowFrame.java
 * Function:根據書本ID借閱圖書
 * Time:2004 1.11
 *********************************/

import java.io.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.border.*;
import javax.swing.table.*;
import java.sql.*; 

class BookBorrowFrame extends Frame implements ActionListener 
{   
	Panel topPanel = new Panel ();
	Label lbBookID = new Label ("書本ID");
	Label lbUserID = new Label ("讀者ID");
	Label lbResult = new Label ("結果顯示");
	
	TextField tfUserID = new TextField(4);
	TextField tfBookID = new TextField (4);
	TextField tfUserName = new TextField (8);
	TextField tfResult = new TextField (20);
	Button butQuery = new Button("查詢");
	Button butBorrow = new Button ("借閱");
	Button butClear = new Button ("清空");
	Button butExit = new Button ("退出");
	JTable tbBBResult;
	JScrollPane tbPane1;
	private Vector vect = new Vector ();
	String labels[] = {"書本ID","讀者ID","讀者姓名","借閱日期"};
    ConDB conUserQuery= new ConDB();

	BookBorrowFrame()
	{
		setTitle("圖書借閱窗口");
		BorderLayout gbLayout = new BorderLayout();
		setLayout(gbLayout);
		tbBBResult = new JTable(tm);
	    tbBBResult.setToolTipText("圖書借閱記錄");
	    tbBBResult.setCellSelectionEnabled(false);
	    tbBBResult.setShowVerticalLines(true);
	    tbBBResult.setShowHorizontalLines(true);
	    tbPane1 = new  JScrollPane(tbBBResult);
		

	    topPanel.add(lbBookID);
		topPanel.add(tfBookID);
	    topPanel.add(butQuery);
		topPanel.add(butBorrow);
		topPanel.add(butClear);
	    topPanel.add(butExit);
		topPanel.add(lbUserID);
		topPanel.add(tfUserID);
		topPanel.add(lbResult);
		topPanel.add(tfResult);
		tfResult.setEditable (false);
		
	  
	    add("North",topPanel);
	    add("Center",tbPane1);
		butQuery.addActionListener (this);
		butBorrow.addActionListener (this);
		butClear.addActionListener (this);
	    butExit.addActionListener (this);
	    this.setVisible (true);
	    this.setSize(900,600);
	    this.setLocation (100,100);	
		
	    addWindowListener(new WindowAdapter() 
	   {
		  public void windowClosing(WindowEvent e) 
        	{
               System.exit (0);
        	}
      	 });
		openDB();
	 
	}//end of new UserQueryFrame()
	
	
	//設定數據
        //聲明一個類AbstractTableModel對象
        //實現AbstractTableModel對象tm中的方法
      AbstractTableModel tm= new AbstractTableModel() 
		{
			//取得表格列數
			public int getColumnCount() { return labels.length; }
            //取得表格行數
			public int getRowCount() { return vect.size();}
            //取得單元格中的屬性值
			public Object getValueAt(int arow, int acol) 
			{ 
				Vector row = (Vector)vect.elementAt(arow);
	            return row.elementAt(acol);
			 }
			//設置表格列名
		   public  String getColumnName(int column)
			{
			   if (labels[column] != null) 
			   {
            	  return labels[column];
        		}
			
			   else 
			  {
				   return "";
        		
			   }
		    }
			
			//數據模型不可編輯,該方法設置為空
			public void setValueAt(Object value,int arow,int acol){  }
			
		    //取得列所屬對象類
			public  Class getColumnClass(int column)
			{
      	        int type;
      	        try {
      	            type = conUserQuery.rs.getMetaData().getColumnType(column+1);
      	        }
      	        catch (SQLException e) {
      	            return super.getColumnClass(column);
      	        }
      
      	        switch(type) {
      	        case Types.CHAR:
      	        case Types.VARCHAR:
      	        case Types.LONGVARCHAR:
      	            return String.class;
      
      	        case Types.BIT:
      	            return Boolean.class;
      
      	        case Types.TINYINT:
      	        case Types.SMALLINT:
      	        case Types.INTEGER:
      	            return Integer.class;
      
      	        case Types.BIGINT:
      	            return Long.class;
      
      	        case Types.FLOAT:
      	        case Types.DOUBLE:
      	            return Double.class;
      
      	        case Types.DATE:
      	            return java.sql.Date.class;
      
      	        default:
      	            return Object.class;
      	        }
      	    }
		  //設置單元格不可編輯,為缺省實現
		  public  boolean isCellEditable(int row,int column)
		  {
			return false;
		  }

			
		};//end of new AbstractTableModel()  
	
	private void borrowQuery()
	{
		String bookID =tfBookID.getText ();
 	    
		try{
		    int id = Integer.parseInt (bookID);
			ResultSetMetaData rsmd = conUserQuery.rs.getMetaData();
	        conUserQuery.stm = conUserQuery.con.createStatement();
			conUserQuery.rs = conUserQuery.stm.executeQuery("select * from borrow where bookid ='"+id+"'");
		  
		   //初始化向量對象
		   vect.removeAllElements();
		  //更新表格內容
		  tm.fireTableStructureChanged();
         
		  while(conUserQuery.rs.next())
		  {
			Vector rec_vector = new Vector();
			for(int i =1; i<=rsmd.getColumnCount(); i++)
			{
				//從結果集中取數據放入向量rec_vector中
				rec_vector.addElement(conUserQuery.rs.getObject(i));
			}
			
			//向量rec_vector加入向量vect中
			vect.addElement(rec_vector);
		 }
		
		//更新表格,顯示向量vect的內容		
		tm.fireTableStructureChanged();
		//查詢完成后,關閉該SQL
		conUserQuery.stm.close();
		//重新執行SQL
		openDB();

   	}
	
    catch(NumberFormatException e)
	{
		tfResult.setText("輸入格式有誤,請重新輸入");
	}
		
	catch(Exception e)
	{
		  System.out.print("Error in Query Data." + e);
   		  System.exit(1); 
    }	
  }// end of private void dataQuery()


   //打開表
   private void openDB() 
   {
	   try
	   {
		   conUserQuery.stm = conUserQuery.con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
		   conUserQuery.rs = conUserQuery.stm.executeQuery("select * from borrow");
		   conUserQuery.rs.first();
 
	   }
	   catch(Exception e)
	   {
		   System.out.print("Error in open table." + e);
		   System.exit(1); 
   	
	   }
   } // end of private void openDB() 
   
   private void inserData()
   {
	   String bookID = tfBookID.getText ();
	   String userID = tfUserID.getText ();
	   String userName = tfUserName.getText ();
	   try
	   {
	       int bID = Integer.parseInt (bookID);
		   int uID = Integer.parseInt (userID);
		   conUserQuery.con.setAutoCommit(false); 
		   String stmp="{call book_borrow(?,?)}"; 
	       CallableStatement cstmt=conUserQuery.con.prepareCall(stmp); //調用存儲過程book_borrow
           int i=bID; 
           cstmt.setInt(1,i);
	       int y=uID;
	       cstmt.setInt(2,y);
	       cstmt.execute();
           conUserQuery.con.commit(); 
	       cstmt.close(); 
	       conUserQuery.con.close();
	   }
	   catch(NumberFormatException e)
	{
		tfResult.setText("輸入格式有誤,請重新輸入");
	}
		
	catch(Exception e)
	{
		 tfResult.setText("此書已借出或無此書");
    }	
	   
   }// end of inserData()
	
	public void actionPerformed(ActionEvent e)
	{
		
		if(e.getActionCommand () == "查詢")
			borrowQuery();
		if(e.getActionCommand ()== "借閱")
		{
			inserData();
		}
		if(e.getActionCommand ()=="清空")
		{	tfBookID.setText ("");
		    tfResult.setText ("");
		}
	   if(e.getActionCommand () == "退出")
			dispose ();
	}
}