package serives;

import java.sql.*;
import beans.AdminInfoBean;
import DbUtil.DbLink;
import beans.BookInfoBean;
import java.util.ArrayList;
import beans.BuyBookInfo;
import beans.UserBean;

public class AdminerServer {
    private static String sql1 = "SELECT * FROM Adminer WHERE adminId=?";
    private static String sql2 = "SELECT * FROM Adminer WHERE adminPassword=?";
    private static String sql3 = "SELECT num,name,rmb FROM BookInfo";
    private static String sql4 = "SELECT * FROM BookInfo WHERE num=?";
    private static String sql5 =
            "INSERT INTO BookInfo(num,name,rmb) VALUES(?,?,?)";
    private static String sql6 = "DELETE FROM BookInfo WHERE num=?";
    private static String sql7 =
            "SELECT username,buybook,sumrmb,buydate FROM BuyBookInfo";
    private static String sql8 =
            "SELECT UserName,PassWord,Sex,Tel,Address FROM UserInfo WHERE UserName=?";
    private static String sql9 =
            "SELECT UserName,PassWord,Sex,Tel,Address FROM UserInfo";
    private static String sql10 = "DELETE FROM UserInfo WHERE UserName=?";
    private static String sql11 = "UPDATE UserInfo SET PassWord=? WHERE UserName=?";
    public static boolean isSelectAdminName(AdminInfoBean bean) {
        boolean flag = false;
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql1);
            prs.setString(1, bean.getAdminId());
            ResultSet rs = prs.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
            rs.close();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return flag;
    }

    public static boolean isSelectAdminPassword(AdminInfoBean bean) {
        boolean flag = false;
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql2);
            prs.setString(1, bean.getAdminpassword());
            ResultSet rs = prs.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
            rs.close();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return flag;

    }

    public static ArrayList SelectBookInfo() {
        ArrayList list = new ArrayList();
        try {
            Connection con = DbLink.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                BookInfoBean bookbean = new BookInfoBean();
                bookbean.setNum(rs.getString(1));
                bookbean.setName(rs.getString(2));
                bookbean.setRmb(rs.getString(3));
                list.add(bookbean);
            }
            rs.close();
            st.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean isSelectBookNum(BookInfoBean bean) {
        boolean flag = false;
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql4);
            prs.setString(1, bean.getNum());
            ResultSet rs = prs.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
            rs.close();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return flag;
    }

    public static void insretBookInfo(BookInfoBean bean) {
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql5);
            prs.setString(1, bean.getNum());
            prs.setString(2, bean.getName());
            prs.setString(3, bean.getRmb());
            prs.executeUpdate();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static void deleteBookInfo(BookInfoBean bean) {
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql6);
            prs.setString(1, bean.getNum());
            prs.executeUpdate();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static ArrayList SelectBuyInfo() {
        ArrayList list = new ArrayList();
        try {
            Connection con = DbLink.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql7);
            while (rs.next()) {
                BuyBookInfo bean = new BuyBookInfo();
                bean.setUsername(rs.getString(1));
                bean.setBuybook(rs.getString(2));
                bean.setSumrmb(rs.getString(3));
                bean.setBuydate(rs.getString(4));
                list.add(bean);
            }
            rs.close();
            st.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static ArrayList SelecUserInfo(String name) {
        ArrayList list = new ArrayList();
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql8);
            prs.setString(1, name);
            ResultSet rs = prs.executeQuery();
            while (rs.next()) {
                UserBean bean = new UserBean();
                bean.setUsername(rs.getString(1));
                bean.setUserpassword(rs.getString(2));
                bean.setSex(rs.getString(3));
                bean.setTel(rs.getString(4));
                bean.setAddress(rs.getString(5));
                list.add(bean);
            }
            rs.close();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static ArrayList SelectUser() {
        ArrayList list = new ArrayList();
        try {
            Connection con = DbLink.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql9);
            while (rs.next()) {
                UserBean bean = new UserBean();
                bean.setUsername(rs.getString(1));
                bean.setUserpassword(rs.getString(2));
                bean.setSex(rs.getString(3));
                bean.setTel(rs.getString(4));
                bean.setAddress(rs.getString(5));
                list.add(bean);
            }
            rs.close();
            st.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
    }
    public static void deleteUser(UserBean bean) {
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql10);
            prs.setString(1, bean.getUsername());
            prs.executeUpdate();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    public static void updateUserPassWord(UserBean bean){
        try {
            Connection con = DbLink.getConnection();
            PreparedStatement prs = con.prepareStatement(sql11);
            prs.setString(1,bean.getUserpassword());
            prs.setString(2,bean.getUsername());
            prs.executeUpdate();
            prs.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

}