package com.wang.bean;

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

/*
 數據庫業務Bean
 */

public class DataBean
{

    private Connection conn = null;
    private ResultSet res = null;
    private java.sql.PreparedStatement prepar = null;
    private java.sql.CallableStatement proc = null;
    public static int PAGECOUNT; //查詢后返回的總頁數 因為java的函數不能傳引用所以需要用靜態變量來獲得
    public DataBean()
    {
        try
        {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager.getConnection(
                "jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=MyData",
                "sa", "123");
        }
        catch (SQLException ex)
        {
            System.out.println(ex.getMessage() + "1路徑錯誤");
        }
        catch (ClassNotFoundException ex)
        {
            System.out.println(ex.getMessage() + "驅動錯誤");
        }

    }

    public UserBean checkUsersLogin(String userName, String userPwd) //登陸驗證
    {
        UserBean ub = null;
        if (!checkParameter(userName + userPwd))
        {
            userName = "null";
            userPwd = "null";
        }
        try
        {
            String sql =
                "select count(*) from admin where userName=? and userPwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, userName);
            prepar.setString(2, userPwd);
            res = prepar.executeQuery();
            if (res.next())
            {
                if (res.getInt(1) > 0)
                {
                    ub = this.getUser(userName);
                }
                else
                {
                    ub = null;
                }
            }
        }
        catch (Exception e)
        {
            ub = null;
            e.printStackTrace();
        }
        return ub;
    }

    public UserBean getUser(String userName) //提取登陸用戶信息
    {
        UserBean ub = new UserBean();
        String sql = "select * from admin where userName=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, userName);
            res = prepar.executeQuery();
            while (res.next())
            {
                ub.setUserName(res.getString("userName"));
                ub.setUserPwd(res.getString("userPwd"));
                ub.setUserId(res.getLong("userId"));
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return ub;
    }

    public boolean checkParameter(String para) //過濾非法字符
    {
        int flag = 0;
        flag += para.indexOf("'") + 1;
        flag += para.indexOf(";") + 1;
        flag += para.indexOf("1=1") + 1;
        flag += para.indexOf("|") + 1;
        flag += para.indexOf("<") + 1;
        flag += para.indexOf(">") + 1;
        if (flag != 0)
        {
            System.out.println("提交了非法字符!!!");
            return false;
        }
        return true;
    }

    public ArrayList selectCDBean(String selectValue, int page, int count) //查詢分頁
    {
        ArrayList list = new ArrayList();
        if (!checkParameter(selectValue))
        {
            selectValue = "";
        }
        try
        {
            proc = conn.prepareCall("{call proc_page(?,?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.setString(3, selectValue);
            proc.registerOutParameter(4, Types.INTEGER); //OUTPUT參數 返回結構共多少頁
            res = proc.executeQuery(); //接收存儲過程的結果集
            while (res.next()) //提取結果集的每條記錄
            {
                CDBean cb = new CDBean();
                cb.setCdAlbum(res.getString("CDalbum"));
                cb.setCdCompany(res.getString("CDcompany"));
                cb.setCdName(res.getString("CDname"));
                cb.setCdId(res.getLong("CDid"));
                cb.setCdType(getCDType(res.getInt("CDtypeId")));
                list.add(cb);
            }
            PAGECOUNT = proc.getInt(4);
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        return list;

    }

    public String getCDType(int cdtypeId)
    {
        try
        {

            java.sql.PreparedStatement prepar1 = conn.prepareStatement(
                "select display from CDtype where CDtypeId=?");
            prepar1.setLong(1, cdtypeId);
            ResultSet res1 = prepar1.executeQuery();
            res1.next();
            return res1.getString("display");
        }
        catch (SQLException ex)
        {

            return null;
        }

    }

    public boolean setCDBean(CDBean cb)
    {
        if (!checkParameter(cb.getCdName() + cb.getCdCompany() + cb.getCdAlbum() +
                            cb.getCdType()))
        {
            return false;
        }

        boolean flag = false;
        String sql =
            "update CDinfo set CDname=?,CDcompany=?,CDalbum=?,CDtypeId=? where CDid=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, cb.getCdName());
            prepar.setString(2, cb.getCdCompany());
            prepar.setString(3, cb.getCdAlbum());
            prepar.setInt(4, Integer.parseInt(cb.getCdType()));
            prepar.setLong(5, cb.getCdId());
            int result = prepar.executeUpdate();
            if (result > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }
        }
        catch (Exception ex)
        {
            flag = false;
            ex.printStackTrace();
        }
        return flag;
    }

    public CDBean getCDBean(long id)
    {

        CDBean cb = new CDBean();
        String sql = "select * from CDinfo where CDid=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setLong(1, id);
            res = prepar.executeQuery();
            while (res.next())
            {
                cb.setCdAlbum(res.getString("CDalbum"));
                cb.setCdCompany(res.getString("CDcompany"));
                cb.setCdName(res.getString("CDname"));
                cb.setCdId(res.getLong("CDid"));
                cb.setCdType(getCDType(res.getInt("CDtypeId")));

            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        return cb;
    }

    public boolean deleteCDBean(long id)
    {
        boolean flag = false;
        String sql = "delete from CDinfo where CDid=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setLong(1, id);
            int result = prepar.executeUpdate();
            if (result > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }
        }
        catch (Exception ex)
        {
            flag = false;
            ex.printStackTrace();
        }
        return flag;
    }

    public boolean addCDBean(CDBean cb)
    {
        boolean flag = false;
        if (!checkParameter(cb.getCdName() + cb.getCdCompany() + cb.getCdAlbum() +
                            cb.getCdType()))
        {
            return false;
        }
        String sql = "insert into CDinfo values(?,?,?,?)";
        try
        {
            this.prepar = conn.prepareStatement(sql);
            prepar.setString(1, cb.getCdName());
            prepar.setString(2, cb.getCdCompany());
            prepar.setString(3, cb.getCdAlbum());
            prepar.setInt(4, Integer.parseInt(cb.getCdType()));
            int result = prepar.executeUpdate();
            if (result > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }

        }
        catch (Exception ex)
        {
            flag = false;
            ex.printStackTrace();
        }
        return flag;
    }

    public boolean setUserBean(UserBean ub)
    {
        boolean flag = false;
        String sql = "update admin set userPwd=? where userId=?";
        try
        {
            if (!checkParameter(ub.getUserPwd()))
            {
                return false;
            }
            this.prepar = conn.prepareStatement(sql);
            prepar.setString(1, ub.getUserPwd());
            prepar.setLong(2, ub.getUserId());
            int result = prepar.executeUpdate();
            if (result > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }
        }
        catch (Exception ex)
        {
            flag = false;
            ex.printStackTrace();
        }
        return flag;
    }

    public boolean addUserBean(UserBean ub)
    {
        boolean flag = false;
        String sql = "insert into admin values(?,?)";
        if (!checkParameter(ub.getUserPwd() + ub.getUserName()))
        {
            return false;
        }
        if (hasUser(ub.getUserName()))
        {
            return false;
        }
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, ub.getUserName());
            prepar.setString(2, ub.getUserPwd());
            int result = prepar.executeUpdate();
            if (result > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }

        }
        catch (Exception ex)
        {
            flag = false;
            ex.printStackTrace();
        }

        return flag;
    }

    public boolean hasUser(String userName)
    {
        boolean flag = true;
        String sql = "select count(*) from admin where userName=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, userName);
            res = prepar.executeQuery();
            res.next();
            int result = res.getInt(1);
            if (result > 0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            flag = true;
        }

        return flag;
    }

}