package 畢業(yè)設(shè)計;
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;
    }

    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("讀者職業(yè)")) {
                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("讀者職業(yè)")) {
                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 {
    }
}