package 畢業(yè)設(shè)計;
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: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 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();
       try {
           String str = "select TOP 50 * from Book order by LoanCount DESC";
           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 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 void 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)
   {
       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) {
       }
   }
   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 {
    }
}