package 畢業設計;
import java.sql.*;
import java.util.*;
public class RecordConDB {
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement pst;
    public RecordConDB() {
        //****************************連接數據庫**********************************
         try {
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         } catch (ClassNotFoundException ex) {
             System.out.println("Driver 出錯");
         }
        try {
            String url = "jdbc:odbc:chenhaiLibrary";
            con = DriverManager.getConnection(url);
            st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);
        } catch (SQLException ex1) {
            System.out.println("lib 出錯");
        }
        try {
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //*****************************添加借書記錄*********************************
     public boolean AddRecord(String ReaId, String LibId, String BarCode,
                              String LoanTime, String RevertibleTime,
                              String Operator) {
         boolean Success = true;
         try {
             String strSQL = "insert Record values ( '" + ReaId + "', '" +
                             LibId + "', '" + BarCode + "', '" + LoanTime +
                             "', '', '" +
                             RevertibleTime + "', '" + Operator + "')";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
             Success = false;
         }
         return Success;
     }

    //****************************查詢記錄**********************************
     public Vector SearchRecord(String LibId, String BarCode, String ReaId) {
         Vector vt = new Vector();
         String strSQL = "";
         if (BarCode.equals(""))
             strSQL = "select * from Record where ReaId = '" + ReaId +
                      "' and LibId = '" + LibId + "' and ReturnTime = ''";
         else
             strSQL = "select * from Record where ReaId = '" + ReaId +
                      "' and BarCode = '" + BarCode + "' and ReturnTime = ''";
         try {
             rs = st.executeQuery(strSQL);
             while (rs.next()) {
                 Record rd = new Record();
                 rd.setId(rs.getString(1));
                 rd.setReaId(rs.getString(2));
                 rd.setLibId(rs.getString(3));
                 rd.setBarCode(rs.getString(4));
                 rd.setLoanTime(rs.getString(5));
                 rd.setReturnTime(rs.getString(6));
                 rd.setRevertibleTime(rs.getString(7));
                 rd.setOperator(rs.getString(8));
                 vt.add(rd);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

    //************************查詢借書記錄**************************************
     public Vector SearchLoanRecord() {
         Vector vt = new Vector();
         try {
             String strSQL = "select * from Record where ReturnTime = ''";
             rs = st.executeQuery(strSQL);
             while (rs.next()) {
                 Vector tempvt = new Vector();
                 for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                     tempvt.add(rs.getString(i));
                 }
                 vt.add(tempvt);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

    //**************************查詢讀者已借書記錄******************************
     public Vector SearchAlreadyRecord(String ReaId) {
         Vector vt = new Vector();
         try {
             String strSQL = "select * from Record where ReaId = '" + ReaId +
                             "' and ReturnTime = ''";
             rs = st.executeQuery(strSQL);
             while (rs.next()) {
                 Vector tempvt = new Vector();
                 for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                     tempvt.add(rs.getString(i));
                 }
                 vt.add(tempvt);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

    //*************************還書時更新記錄*************************************
     public void ReturnBookRecord(int Id, String ReturnTime) {
         try {
             String strSQL = "update Record set ReturnTime = '" + ReturnTime +
                             "' where Id = " + Id;
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //*****************************關閉數據庫*********************************
     public void CloseRecordDB() {
         try {
             st.close();
             con.close();
         } catch (SQLException ex) {
         }
     }

    private void jbInit() throws Exception {
    }
}