package 畢業設計;
import java.sql.*;
import javax.swing.JOptionPane;
import java.util.Vector;
public class ReaderConDB {
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement pst;
    public ReaderConDB() {
        //***************************連接數據庫***********************************
         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 {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //***************************查詢所有讀者***********************************
     public Vector SearchAll() {
         Vector vt = new Vector();
         try {
             String str = "select * from Reader";
             rs = st.executeQuery(str);
             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;
     }

    //****************************查詢前50名讀者**********************************
     public Vector SearchTop() {
         Vector vt = new Vector();
         int k = 1;
         try {
             String str =
                     "select TOP 50 * from Reader order by BorrowCount DESC";
             rs = st.executeQuery(str);
             while (rs.next()) {
                 Vector tempvt = new Vector();
                 tempvt.add(String.valueOf(k++));
                 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 SearchUpdateReader(String content, String sort,
                                      boolean isnot) {
         Vector vt = new Vector();
         String str = "";
         if (isnot) {
             if (sort.equals("讀者卡號")) {
                 str = "select * from Reader where Id = '" + content + "'";
             } else if (sort.equals("讀者姓名")) {
                 str = "select * from Reader where Name = '" + content + "'";
             } else if (sort.equals("讀者職業")) {
                 str = "select * from Reader where Vocation = '" + content +
                       "'";
             }
         } else {
             if (sort.equals("讀者卡號")) {
                 str = "select * from Reader where Id like '%" + content + "%'";
             } else if (sort.equals("讀者姓名")) {
                 str = "select * from Reader where Name like '%" + content +
                       "%'";
             } else if (sort.equals("讀者職業")) {
                 str = "select * from Reader where Vocation like '%" + content +
                       "%'";
             }
         }
         try {
             rs = st.executeQuery(str);
             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 SearchReader(String Id, String Name, String Vocation,
                                boolean Isnot) {
         Vector vt = new Vector();
         String str = "";
         if (Isnot) {
             for (int i = 0; i < 3; i++) {
                 String tem = "";
                 if (i == 0) {
                     if (!Id.equals("")) {
                         tem = " and Id = '" + Id + "'";
                     }
                 } else if (i == 1) {
                     if (!Name.equals("")) {
                         tem = " and Name = '" + Name + "'";
                     }
                 } else if (i == 2) {
                     if (!Vocation.equals("")) {
                         tem = " and Vocation = '" + Vocation + "'";
                     }
                 }
                 if (!tem.equals("")) {
                     str = str + tem;
                 }
             }
         } else {
             for (int i = 0; i < 3; i++) {
                 String tem = "";
                 if (i == 0) {
                     if (!Id.equals("")) {
                         tem = " and Id like '%" + Id + "%'";
                     }
                 } else if (i == 1) {
                     if (!Name.equals("")) {
                         tem = " and Name like '%" + Name + "%'";
                     }
                 } else if (i == 2) {
                     if (!Vocation.equals("")) {
                         tem = " and Vocation like '%" + Vocation + "%'";
                     }
                 }
                 if (!tem.equals("")) {
                     str = str + tem;
                 }
             }
         }
         try {
             String strSQL = "select * from Reader where 1=1" + str;
             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 SearchLRReader(String Id) {
         Vector vt = new Vector();
         try {
             String strSQL = "select * from Reader where Id = '" + Id + "'";
             rs = st.executeQuery(strSQL);
             while (rs.next()) {
                 Reader rd = new Reader();
                 rd.setId(rs.getString(1));
                 rd.setName(rs.getString(2));
                 rd.setSex(rs.getString(3));
                 rd.setBirthTime(rs.getString(4));
                 rd.setVocation(rs.getString(5));
                 rd.setIdentityCard(rs.getString(6));
                 rd.setTel(rs.getString(7));
                 rd.setLoanableNum(rs.getString(8));
                 rd.setAlreadyNum(rs.getString(9));
                 rd.setTransactTime(rs.getString(10));
                 rd.setEndTime(rs.getString(11));
                 rd.setBorrowCount(rs.getString(12));
                 rd.setAddress(rs.getString(13));
                 rd.setRemark(rs.getString(14));
                 vt.add(rd);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

    //****************************借書時更新讀者信息****************************
     public void UpdateBorrowBook(String Id) {
         try {
             String strSQL = "update Reader set LoanableNum = LoanableNum - 1, AlreadyNum = AlreadyNum + 1, BorrowCount = BorrowCount + 1 where Id = '" +
                             Id + "'";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //***************************還書時更新讀者信息****************************
     public void UpdateReturnBook(String Id) {
         try {
             String strSQL = "update Reader set LoanableNum = LoanableNum + 1, AlreadyNum = AlreadyNum - 1 where Id = '" +
                             Id + "'";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //****************************更新讀者信息**********************************
     public boolean UpdateReader(String Id, String Name, String Sex,
                                 String BirthTime,
                                 String Vocation, String IdentityCard,
                                 String Tel,
                                 String LoanableNum, String AlreadyNum,
                                 String TransactTime,
                                 String EndTime, String BorrowCount,
                                 String Address, String Remark) {
         boolean Success = true;
         try {
             String strSQL = "update Reader set Name = '" + Name + "', Sex = '" +
                             Sex +
                             "', BirthTime = '" + BirthTime + "', Vocation = '" +
                             Vocation +
                             "', IdentityCard = '" + IdentityCard +
                             "', Tel = '" +
                             Tel +
                             "', LoanableNum = '" + LoanableNum +
                             "', AlreadyNum = '" +
                             AlreadyNum + "', TransactTime = '" + TransactTime +
                             "', EndTime = '" + EndTime + "', BorrowCount = '" +
                             BorrowCount + "', Address = '" + Address +
                             "', Remark = '" +
                             Remark + "' where Id = '" + Id + "'";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
             Success = false;
         }
         return Success;
     }

    //***************************添加讀者***********************************
     public void AddReader(String Id, String Name, String Sex, String BirthTime,
                           String Vocation, String TransactTime, String EndTime,
                           String IdentityCard, String Tel, String LoanableNum,
                           String Address, String Remark) {
         try {
             String strSQL = "insert Reader values ('" + Id + "', '" + Name +
                             "', '" + Sex +
                             "', '" + BirthTime + "','" + Vocation + "', '" +
                             IdentityCard +
                             "', '" + Tel + "', " + LoanableNum + ", 0, '" +
                             TransactTime +
                             "', '" + EndTime + "', 0, '" + Address + "', '" +
                             Remark + "')";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //****************************刪除讀者**********************************
     public void DeleteReader(String Id) {
         try {
             String strSQL = "delete from Reader where Id = '" + Id + "'";
             pst = con.prepareStatement(strSQL);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

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

    private void jbInit() throws Exception {
    }
}