package 畢業設計;
import java.sql.*;
import java.util.*;
public class BookConDB {
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement pst;
    public BookConDB() {
        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 void AddBook(String Id, String BarCode, String Name, String Author,
                        String Page, String Publish, String Isbn, String Price,
                        String Sort, String IntoTime, String Location,
                        String Synopsis) {
        try {
            String strSQL = "insert Book values ('" + Id + "', '" + BarCode +
                            "', '" +
                            Name + "', '" + Author + "','" + Page + "', '" +
                            Publish +
                            "', '" + Isbn + "', '" + Price + "', '" + Sort +
                            "', '" +
                            IntoTime + "', '" + Location + "', '在庫', 0, '" +
                            Synopsis + "')";
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
        }
    }

    public Vector SearchAll() {
        Vector vt = new Vector();
        try {
            String str = "select * from Book";
            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 Book order by LoanCount 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 SearchBook(String Id, String BarCode, String Name,
                             String Author,
                             String Publish, String Sort, boolean Isnot) {
        Vector vt = new Vector();
        String str = "";
        if (Isnot) {
            for (int i = 0; i < 6; i++) {
                String tem = "";
                if (i == 0) {
                    if (!Id.equals("")) {
                        tem = " and Id = '" + Id + "'";
                    }
                } else if (i == 1) {
                    if (!BarCode.equals("")) {
                        tem = " and BarCode = '" + BarCode + "'";
                    }
                } else if (i == 2) {
                    if (!Name.equals("")) {
                        tem = " and Name = '" + Name + "'";
                    }
                } else if (i == 3) {
                    if (!Author.equals("")) {
                        tem = " and Author = '" + Author + "'";
                    }
                } else if (i == 4) {
                    if (!Publish.equals("")) {
                        tem = " and Publish = '" + Publish + "'";
                    }
                } else if (i == 5) {
                    if (!Sort.equals("")) {
                        tem = " and Sort = '" + Sort + "'";
                    }
                }
                if (!tem.equals("")) {
                    str = str + tem;
                }
            }
        } else {
            for (int i = 0; i < 6; i++) {
                String tem = "";
                if (i == 0) {
                    if (!Id.equals("")) {
                        tem = " and Id like '%" + Id + "%'";
                    }
                } else if (i == 1) {
                    if (!BarCode.equals("")) {
                        tem = " and BarCode like '%" + BarCode + "%'";
                    }
                } else if (i == 2) {
                    if (!Name.equals("")) {
                        tem = " and Name like '%" + Name + "%'";
                    }
                } else if (i == 3) {
                    if (!Author.equals("")) {
                        tem = " and Author like '%" + Author + "%'";
                    }
                } else if (i == 4) {
                    if (!Publish.equals("")) {
                        tem = " and Publish like '%" + Publish + "%'";
                    }
                } else if (i == 5) {
                    if (!Sort.equals("")) {
                        tem = " and Sort like '%" + Sort + "%'";
                    }
                }
                if (!tem.equals("")) {
                    str = str + tem;
                }
            }
        }
        try {
            String strSQL = "select * from Book 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 SearchLRBook(String Id, String BarCode, boolean Isnot) {
        Vector vt = new Vector();
        String strSQL = "";
        if (Isnot) {
            strSQL = "select * from Book where Id = '" + Id + "'";
        } else {
            strSQL = "select * from Book where BarCode = '" + BarCode + "'";
        }
        try {
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                Book bk = new Book();
                bk.setId(rs.getString(1));
                bk.setBarCode(rs.getString(2));
                bk.setName(rs.getString(3));
                bk.setAuthor(rs.getString(4));
                bk.setPage(rs.getString(5));
                bk.setPublish(rs.getString(6));
                bk.setIsbn(rs.getString(7));
                bk.setPrice(rs.getString(8));
                bk.setSort(rs.getString(9));
                bk.setIntoTime(rs.getString(10));
                bk.setLocation(rs.getString(11));
                bk.setIsin(rs.getString(12));
                bk.setLoanCount(rs.getString(13));
                bk.setSynopsis(rs.getString(14));
                vt.add(bk);
            }
            rs.close();
        } catch (SQLException ex) {
        }
        return vt;
    }

    public Vector SearchUpdateBook(String content, String sort, boolean isnot) {
        Vector vt = new Vector();
        String strSQL = "";
        if (isnot) {
            if (sort.equals("圖書編號")) {
                strSQL = "select * from Book where Id = '" + content + "'";
            } else if (sort.equals("圖書條形碼")) {
                strSQL = "select * from Book where BarCode = '" + content + "'";
            } else if (sort.equals("圖書名稱")) {
                strSQL = "select * from Book where Name = '" + content + "'";
            } else if (sort.equals("圖書作者")) {
                strSQL = "select * from Book where Author = '" + content + "'";
            } else if (sort.equals("圖書出版社")) {
                strSQL = "select * from Book where Publish = '" + content + "'";
            } else if (sort.equals("圖書類別")) {
                strSQL = "select * from Book where Sort = '" + content + "'";
            }
        } else {
            if (sort.equals("圖書編號")) {
                strSQL = "select * from Book where Id like '%" + content + "%'";
            } else if (sort.equals("圖書條形碼")) {
                strSQL = "select * from Book where BarCode like '%" + content +
                         "%'";
            } else if (sort.equals("圖書名稱")) {
                strSQL = "select * from Book where Name like '%" + content +
                         "%'";
            } else if (sort.equals("圖書作者")) {
                strSQL = "select * from Book where Author like '%" + content +
                         "%'";
            } else if (sort.equals("圖書出版社")) {
                strSQL = "select * from Book where Publish like '%" + content +
                         "%'";
            } else if (sort.equals("圖書類別")) {
                strSQL = "select * from Book where Sort like '%" + content +
                         "%'";
            }
        }
        try {
            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 boolean UpdateBook(String Id, String BarCode, String Name,
                              String Author,
                              String Page, String Publish, String Isbn,
                              String Price,
                              String Sort, String IntoTime, String Location,
                              String Isin,
                              String LoanCount, String Synopsis) {
        boolean Success = true;
        try {
            String strSQL = "update Book set BarCode = '" + BarCode +
                            "', Name = '" +
                            Name + "', Author = '" + Author + "', Page = '" +
                            Page +
                            "', Publish = '" + Publish + "', Isbn = '" + Isbn +
                            "', Price = '" + Price + "', Sort = '" + Sort +
                            "', IntoTime = '"
                            + IntoTime + "', Location = '" + Location +
                            "', Isin = '" +
                            Isin + "', LoanCount = '" + LoanCount +
                            "', Synopsis = '" +
                            Synopsis + "' where Id = '" + Id + "'";
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
            Success = false;
        }
        return Success;
    }

    public void UpdateBorrowBook(String Id, String BarCode) {
        String strSQL;
        if (Id.equals(""))
            strSQL =
                    "update Book set Isin = '不在庫', LoanCount = LoanCount + 1 where BarCode = '" +
                    BarCode + "'";
        else {
            strSQL =
                    "update Book set Isin = '不在庫', LoanCount = LoanCount + 1 where Id = '" +
                    Id + "'";
        }
        try {
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
        }
    }

    public void UpdateReturnBook(String Id, String BarCode) {
        String strSQL;
        if (Id.equals(""))
            strSQL = "update Book set Isim = '在庫' where BarCode = '" +
                     BarCode + "'";
        else {
            strSQL = "update Book set Isim = '在庫' where Id = '" + Id + "'";
        }
        try {
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
        }
    }

    public void DeleteBook(String Id) {
        try {
            String strSQL = "delete from Book where Id = '" + Id + "'";
            pst = con.prepareStatement(strSQL);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException ex) {
        }
    }

    public void CloseBookDB() {
        try {
            st.close();
            con.close();
        } catch (SQLException ex) {
        }
    }

    private void jbInit() throws Exception {
    }
}