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:lib";
             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 void AddRecord(String ReaId, String LibId, String BarCode, String LoanTime, String RevertibleTime, String Operator)
    {
        try {
            String strSQL = "insert Record values ( '" + ReaId + "', '" +
                            LibId + "', '" + BarCode + "', '" + LoanTime + "', '', '" +
                           RevertibleTime + "', '" + Operator + "')";
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
        }
    }

    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 + "'";
        else
            strSQL = "select * from Record where ReaId = '" + ReaId + "' and BarCode = '" + BarCode + "'";
        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(5));
                rd.setLoanTime(rs.getString(6));
                rd.setReturnTime(rs.getString(7));
                rd.setRevertibleTime(rs.getString(8));
                rd.setOperator(rs.getString(9));
                vt.add(rd);
            }
            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(String 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 {
    }
}