package 畢業(yè)設計;
import java.sql.*;
import javax.swing.JOptionPane;
import java.util.Vector;
public class UserConDB {
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private PreparedStatement pst;
    public UserConDB() {
        //**************************連接數據庫************************************
         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 出錯");
        }
    }

    //***************************驗證密碼***********************************
     public boolean VerifyPassword(String Id, String Password) {
         boolean psw = false;
         try {
             String str = "select * from Operator where Id = '" + Id +
                          "' and Password = '" + Password + "'";
             rs = st.executeQuery(str);
             if (rs.next()) {
                 psw = true;
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return psw;
     }

    //*******************************設置所有用戶為不在使用**********************
     public void setIsUsing() {
         boolean psw = false;
         try {
             String str =
                     "Update Operator set IsUsing = '0' where IsUsing = '1'";
             pst = con.prepareStatement(str);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //******************************設置當前用戶為正在使用者************************
     public void UpdateIsUsing(String Id) {
         boolean psw = false;
         try {
             String str = "Update Operator set IsUsing = '1' where Id = '" + Id +
                          "'";
             pst = con.prepareStatement(str);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //**************************更改密碼************************************
     public void UpdatePassword(String newPassword) {
         boolean psw = false;
         try {
             String str = "Update Operator set Password = '" + newPassword +
                          "' where IsUsing = '1'";
             pst = con.prepareStatement(str);
             pst.executeUpdate();
             pst.close();
         } catch (SQLException ex) {
         }
     }

    //***************************查詢所有用戶***********************************
     public Vector SearchAll() {
         Vector vt = new Vector();
         try {
             String str = "select * from Operator";
             rs = st.executeQuery(str);
             while (rs.next()) {
                 Vector tempvt = new Vector();
                 for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                     if (i != 4)
                         tempvt.add(rs.getString(i));
                 }
                 vt.add(tempvt);
             }
             rs.close();
         } catch (SQLException ex) {
         }
         return vt;
     }

    //****************************查詢要更新的用戶**********************************
     public Vector SearchUser(String content, String sort, boolean isnot) {
         Vector vt = new Vector();
         if (isnot) {
             if (sort.equals("卡號")) {
                 try {
                     String str = "select * from Operator where Id = '" +
                                  content +
                                  "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (sort.equals("姓名")) {
                 try {
                     String str = "select * from Operator where Name = '" +
                                  content +
                                  "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (sort.equals("權限")) {
                 try {
                     String str = "select * from Operator where Popedom = '" +
                                  content + "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             }
         } else {
             if (sort.equals("卡號")) {
                 try {
                     String str = "select * from Operator where Id like '%" +
                                  content +
                                  "%'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (sort.equals("姓名")) {
                 try {
                     String str = "select * from Operator where Name like '%" +
                                  content +
                                  "%'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (sort.equals("權限")) {
                 try {
                     String str =
                             "select * from Operator where Popedom like '%" +
                             content + "%'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             }
         }
         return vt;
     }

    //*****************************查詢用戶*********************************
     public Vector SearchUser(String Id, String Name, String Popedom,
                              boolean isnot) {
         Vector vt = new Vector();
         if (isnot) {
             if (!Id.equals("") && Name.equals("") && Popedom.equals("")) {
                 try {
                     String str = "select * from Operator where Id = '" + Id +
                                  "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (Id.equals("") && !Name.equals("") && Popedom.equals("")) {
                 try {
                     String str = "select * from Operator where Name = '" +
                                  Name +
                                  "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (Id.equals("") && Name.equals("") && !Popedom.equals("")) {
                 try {
                     String str = "select * from Operator where Popedom = '" +
                                  Popedom + "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();
                 } catch (SQLException ex) {
                 }
             } else if (!Id.equals("") && !Name.equals("") && Popedom.equals("")) {
                 try {
                     String str = "select * from Operator where Id = '" + Id +
                                  "' and Name = '" + Name + "'";
                     rs = st.executeQuery(str);
                     while (rs.next()) {
                         Vector tempvt = new Vector();
                         for (int i = 1; i <= rs.getMetaData().getColumnCount();
                                      i++) {
                             if (i != 4)
                                 tempvt.add(rs.getString(i));
                         }
                         vt.add(tempvt);
                     }
                     rs.close();