package com.wang.db;

import java.sql.*;
import com.wang.bean.*;
import java.util.*;

public class AdminDB
{
    private Connection conn = null;
    private ResultSet res = null;
    private java.sql.PreparedStatement prepar = null;
    private boolean flag = false;
    private java.sql.CallableStatement proc = null;
    private int pagecount = 0;
    private int pagedata = 0;
    public int getSearchCountPage()
    {
        return this.pagecount;
    }

    public int getSearchCountdata()
    {
        return this.pagedata;
    }

    public AdminDB()
    {
        try
        {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=MyShop", "sa", "");
            System.out.println("連接成功");
        }
        catch (SQLException ex)
        {
            //System.out.println(ex.getMessage() + "1路徑錯誤");
        }
        catch (ClassNotFoundException ex)
        {
            // System.out.println(ex.getMessage() + "驅動錯誤");
        }

    }

    public boolean checkAdminLogin(AdminBean ab)
    { // 驗證登陸

        boolean result = false;
        try
        {
            String sql = "select count(*) from Admin where UserName=? and UserPwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, ab.getUserName());
            prepar.setString(2, ab.getUserPwd());
            res = prepar.executeQuery();
            if (res.next())
            {
                if (res.getInt(1) > 0)
                {
                    result = true;
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return result;
    }

    public ArrayList getUsersList(int count, int page)
    {
        ArrayList list = new ArrayList();
        try
        {
            proc = conn.prepareCall("{call proc_users_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next())
            {
                userBean ub = new userBean();
                ub.setUserId(res.getInt(1));
                ub.setUserName(res.getString(2));
                ub.setUserPwd(res.getString(3));
                ub.setRealName(res.getString(4));
                ub.setUserSex(res.getInt(5));
                ub.setUserPhone(res.getString(6));
                ub.setUserMail(res.getString(7));
                ub.setUserCity(res.getInt(8));
                ub.setUserAdds(res.getString(9));
                ub.setUserCode(res.getInt(10));
                ub.setUserWork(res.getString(11));
                ub.setUserCard(res.getString(12));
                ub.setUserStrp(res.getInt(13));
                ub.setUserAge(res.getInt(14));
                list.add(ub);
            }
            pagecount = proc.getInt(3);
        }
        catch (SQLException ex)
        {
            System.out.println("服務器異常發生在 getUsersList()");
            ex.printStackTrace();
        }
        return list;
    }

    public boolean insertAdmin(AdminBean admin)
    {
        try
        {
            prepar = conn.prepareStatement("insert into admin (userName,UserPwd) values(?,?)");
            prepar.setString(1, admin.getUserName());
            prepar.setString(2, admin.getUserPwd());
            int flag = prepar.executeUpdate();
            if (flag == 0)
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
        return true;

    }

    public String getStep(int stepId)
    {
        try
        {

            prepar = conn.prepareStatement("select Display from Step where StepId=?");
            prepar.setInt(1, stepId);
            res = prepar.executeQuery();
            res.next();
            return res.getString("Display");
        }
        catch (SQLException ex)
        {

            return null;
        }

    }

    public String getBookStep(int stepId)
    {
        try
        {

            prepar = conn.prepareStatement("select Display from BookStep where BookStepId=?");
            prepar.setInt(1, stepId);
            ResultSet res = prepar.executeQuery();
            res.next();
            return res.getString("Display");
        }
        catch (SQLException ex)
        {

            return null;
        }

    }

    public boolean insertBookType(BookTypeBean btb)
    {
        try
        {
            prepar = conn.prepareStatement("insert into Type (Display) values('" + btb.getDisplay() + "')");

            int flag = prepar.executeUpdate();
            if (flag == 0)
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBookType(int BookTypeId)
    {
        try
        {
            prepar = conn.prepareStatement("delete Type where TypeId=?");
            prepar.setInt(1, BookTypeId);
            int flag = prepar.executeUpdate();
            if (flag == 0)
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean updateBookType(BookTypeBean btb)
    {
        try
        {
            prepar = conn.prepareStatement("update Type set Display=? where TypeId=?");
            prepar.setString(1, btb.getDisplay());
            prepar.setInt(2, btb.getTypeId());
            int flag = prepar.executeUpdate();
            if (flag == 0)
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean insertBooks(BookBean bb)
    {
        try
        {
            prepar = conn.prepareStatement("insert into information (BookName,BookPenster,BookCompany,BookSynopsis,BookStorage,BookSell,BookDate,BookPrice,BookType,bookImage,BookStep) values(?,?,?,?,?,?,?,?,?,?,?)");
            prepar.setString(1, bb.getBookName());
            prepar.setString(2, bb.getBookPenster());
            prepar.setString(3, bb.getBookCompany());
            prepar.setString(4, bb.getBookSynopsis());
            prepar.setInt(5, bb.getBookStorage());
            prepar.setInt(6, bb.getBookSell());
            prepar.setDate(7, new java.sql.Date(new java.util.Date().getTime()));
            prepar.setInt(8, bb.getBookPrice());
            prepar.setInt(9, Integer.parseInt(bb.getBookType()));
            prepar.setString(10, bb.getBookImage());
            prepar.setInt(11, bb.getBookStep());
            int flag = prepar.executeUpdate();
            if (flag == 0)
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public BookBean getBookBean(int bookId)
    {
        BookBean bbean = new BookBean();
        String sql = "select * from information where BookId=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, bookId);
            res = prepar.executeQuery();
            while (res.next())
            {
                bbean.setBookCompany(res.getString("BookCompany"));
                bbean.setBookCount(1);
                bbean.setBookData(res.getDate("BookDate"));
                bbean.setBookId(bookId);
                bbean.setBookImage(res.getString("BookImage"));
                bbean.setBookName(res.getString("BookName"));
                bbean.setBookPenster(res.getString("BookPenster"));
                bbean.setBookPrice(res.getInt("BookPrice"));
                bbean.setBookSell(res.getInt("BookSell"));
                bbean.setBookStep(res.getInt("BookStep"));
                bbean.setBookStorage(res.getInt("BookStorage"));
                bbean.setBookSynopsis(res.getString("BookSynopsis"));
                bbean.setBookType(this.queryType("" + res.getInt("BookType")));
                bbean.setBookAllPrice(bbean.getBookPrice());
            }
        }

        catch (SQLException ex)
        {
            ex.printStackTrace();
            return null;
        }
        return bbean;
    }

    public String queryType(String typeId)
    {
        try
        {

            java.sql.PreparedStatement prepar1 = conn.prepareStatement("select Display from Type where TypeId=?");
            prepar1.setInt(1, Integer.parseInt(typeId));
            ResultSet res1 = prepar1.executeQuery();
            res1.next();
            return res1.getString("Display");
        }
        catch (SQLException ex)
        {

            return null;
        }
    }

    public ArrayList getBooksList(int count, int page)
    {
        ArrayList list = new ArrayList();
        try
        {
            proc = conn.prepareCall("{call proc_books_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next())
            {
                BookBean kb = new BookBean();
                kb.setBookId(res.getInt(1));
                kb.setBookName(res.getString(2));
                kb.setBookPenster(res.getString(3));
                kb.setBookCompany(res.getString(4));
                kb.setBookSynopsis(res.getString(5));
                kb.setBookStorage(res.getInt(6));
                kb.setBookSell(res.getInt(7));
                kb.setBookData(res.getDate(8));
                kb.setBookPrice(res.getInt(9));
                kb.setBookType(res.getInt(10) + "");
                kb.setBookImage(res.getString(11));
                kb.setBookStep(res.getInt(12));
                list.add(kb);
            }
            pagecount = proc.getInt(3);
        }
        catch (SQLException ex)
        {
            System.out.println("服務器異常發生在 getBooksList()");
            ex.printStackTrace();
        }
        return list;
    }

    public ArrayList getOrderList(int count, int page)
    {
        ArrayList list = new ArrayList();
        try
        {
            proc = conn.prepareCall("{call proc_order_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next())
            {
                OrderFormBean ob = new OrderFormBean();
                ob.setOrderId(res.getInt(1));
                ob.setUserId(res.getInt(2));
                ob.setBookId(res.getInt(3));
                ob.setAmount(res.getInt(4));
                ob.setOrderData(res.getString(5));
                ob.setMoney(res.getInt(6));
                list.add(ob);
            }
            pagecount = proc.getInt(3);
        }
        catch (SQLException ex)
        {
            System.out.println("服務器異常發生在 getBooksList()");
            ex.printStackTrace();
        }
        return list;
    }

    public boolean chengeAdminPwd(AdminBean admin)
    {
        try
        {
            prepar = conn.prepareStatement("update admin set UserPwd=? where userId=?");
            prepar.setString(1,admin.getUserPwd());
            prepar.setInt(2,admin.getUserId());
            int flag = prepar.executeUpdate();
            if (flag == 0)
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public void Close()
    {
        try
        {
            if (res != null)
            {
                res.close();
            }
            if (prepar != null)
            {
                prepar.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        System.out.println("關閉成功");
    }

}