/**********************************
 * FileName:BookReturnFrame.java
 * Function:跟據書本歸還圖書
 * 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 BookReturnFrame extends Frame implements ActionListener 
{   
	Panel topPanel = new Panel ();
	Label lbBookID = new Label ("書本ID");
	Label lbResult  = new Label ("結果提示");
	TextField tfBookID = new TextField (20);
	TextField tfResult = new TextField (50);
	Button butQuery = new Button("查詢");
	Button butReturn = 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();

	BookReturnFrame()
	{
		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設置
		topPanel.setLayout(new GridLayout(1,3));
	    topPanel.add(lbBookID);
	    topPanel.add(tfBookID);
	    topPanel.add(butQuery);
		topPanel.add(butReturn);
		topPanel.add(butClear);
	    topPanel.add(butExit); 
		topPanel.add(lbResult);
		topPanel.add(tfResult);
		tfResult.setEditable (false);
	   
	    add("North",topPanel);
	    add("Center",tbPane1);
		butQuery.addActionListener (this);
		butReturn .addActionListener (this);
		butClear.addActionListener (this);
	    butExit.addActionListener (this);
	    this.setVisible (true);
	    this.setSize(900,600);
	    this.setResizable (false);
		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 void fireTableStructureChanged(){};
		    //取得列所屬對象類
			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 bID = Integer.parseInt (bookID);
			ResultSetMetaData rsmd = conUserQuery.rs.getMetaData();
	        conUserQuery.stm = conUserQuery.con.createStatement();
			conUserQuery.rs = conUserQuery.stm.executeQuery("select * from borrow where bookid ='"+bID+"'");
		  
		   //初始化向量對象
		   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("書ID為空或輸入格式有誤");
		}
		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 deleteData()
   {
	   String bookID = tfBookID.getText ();
	   try
	   {
		   int bID = Integer.parseInt (bookID);
		   String sql ="delete from borrow where bookid ='"+bID+"'";
		   conUserQuery.stm = conUserQuery.con.createStatement();
		   if(conUserQuery.stm.executeUpdate(sql) == 1)
		   {
			   tfResult.setText ("歸還成功");
		   }
		  
	   }
	   catch(NumberFormatException e)
	   {
		   tfResult.setText("輸入格式有誤或書本ID不對");
	   }
	   catch(SQLException e)
	   {
		   System.out.print("Error in Delete Data." + e);
   		   System.exit(1);
	   }
   }//end of deleteData()
	
	public void actionPerformed(ActionEvent e)
	{
		
		if(e.getActionCommand () == "查詢")
			borrowQuery();
		if(e.getActionCommand () == "歸還")
		    deleteData();
		if(e.getActionCommand ()=="清空")
		{
			tfBookID.setText ("");
			tfResult.setText ("");
		}
		if(e.getActionCommand () == "退出")
			dispose ();
	   
	}
}