package com.dao;

import com.core.*;
import java.util.*;
import com.actionForm.ManagerForm;
import java.sql.*;
import java.*;

public class ManagerDAO {
    private ConnDB conn = new ConnDB();
    //查詢數據
    public Collection query(String queryif) {
        ManagerForm managerForm = null;
      Collection managercoll = new ArrayList();
        String sql = "";
        if (queryif == null || queryif == "" || queryif == "all") {	//當參數queryif的值為null、all或空時查詢全部數據
            sql = "select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id";
            }else{
                sql="select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id where m.name='"+queryif+"'";			//此處需要應用左連接
        }
        ResultSet rs = conn.executeQuery(sql);					//執行SQL語句
        try {											//捕捉異常信息
            while (rs.next()) {
                managerForm = new ManagerForm();
                managerForm.setId(Integer.valueOf(rs.getString(1)));
                managerForm.setName(rs.getString(2));
                managerForm.setPwd(rs.getString(3));
                managerForm.setSysset(rs.getInt(4));
                managerForm.setReaderset(rs.getInt(5));
                managerForm.setBookset(rs.getInt(6));
                managerForm.setBorrowback(rs.getInt(7));
                managerForm.setSysquery(rs.getInt(8));
                managercoll.add(managerForm);			//將查詢結果保存到Collection集合中
            }
        } catch (SQLException e) {}
        return managercoll;								//返回查詢結果
    }

    /*********************************************************/
    //查詢權限信息
    public ManagerForm query_p(String str) {
        ManagerForm managerForm1 = null;
        String sql = "select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id where m.name='"+str+"'";

        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                managerForm1 = new ManagerForm();
                managerForm1.setId(Integer.valueOf(rs.getString(1)));
                managerForm1.setName(rs.getString(2));
                managerForm1.setPwd(rs.getString(3));
                managerForm1.setSysset(rs.getInt(4));
                managerForm1.setReaderset(rs.getInt(5));
                managerForm1.setBookset(rs.getInt(6));
                managerForm1.setBorrowback(rs.getInt(7));
                managerForm1.setSysquery(rs.getInt(8));
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
        }finally{
        	conn.close();
        }
        return managerForm1;
    }


    //管理員身份驗證
    public int checkManager(ManagerForm managerForm) {
        int flag = 0;
        ChStr chStr=new ChStr();
        String sql = "SELECT * FROM tb_manager where name='" +
        chStr.filterStr(managerForm.getName()) + "'";
        ResultSet rs = conn.executeQuery(sql);
        try {
            if (rs.next()) {
                String pwd = chStr.filterStr(managerForm.getPwd());		//獲取輸入的密碼并過濾輸入字符串中的危險字符
                if (pwd.equals(rs.getString(3))) {
                    flag = 1;
                } else {
                    flag = 0;
                }
            }else{
                flag = 0;
            }
        } catch (SQLException ex) {
            flag = 0;
        }finally{
        	conn.close();
        }
        return flag;
    }

    //修改時應用的查詢方法
    public ManagerForm query_update(ManagerForm managerForm) {
        ManagerForm managerForm1 = null;
        String sql = "select m.*,p.sysset,p.readerset,p.bookset,p.borrowback,p.sysquery from tb_manager m left join tb_purview p on m.id=p.id where m.id=" +
                     managerForm.getId() + "";

        ResultSet rs = conn.executeQuery(sql);	//執行查詢語句
        try {
            while (rs.next()) {
                managerForm1 = new ManagerForm();
                managerForm1.setId(Integer.valueOf(rs.getString(1)));
                managerForm1.setName(rs.getString(2));
                managerForm1.setPwd(rs.getString(3));
                managerForm1.setSysset(rs.getInt(4));
                managerForm1.setReaderset(rs.getInt(5));
                managerForm1.setBookset(rs.getInt(6));
                managerForm1.setBorrowback(rs.getInt(7));
                managerForm1.setSysquery(rs.getInt(8));
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();	//輸出異常信息
        }finally{
        	conn.close();	//關閉數據庫連接
        }
        return managerForm1;
    }
    //更改口令時應用的查詢方法
    public ManagerForm query_pwd(ManagerForm managerForm) {
        ManagerForm managerForm1 = null;
        String sql = "SELECT * FROM tb_manager WHERE name='" +managerForm.getName() + "'";
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                managerForm1 = new ManagerForm();
                managerForm1.setId(Integer.valueOf(rs.getString(1)));
                managerForm1.setName(rs.getString(2));
                managerForm1.setPwd(rs.getString(3));
            }
        } catch (SQLException ex) {
        	ex.printStackTrace();
        }finally{
        	conn.close();
        }
        return managerForm1;
    }
    //添加數據
    public int insert(ManagerForm managerForm) {
        String sql1="SELECT * FROM tb_manager WHERE name='"+managerForm.getName()+"'";
        ResultSet rs = conn.executeQuery(sql1);
        String sql = "";
        int falg = 0;
            try {
                if (rs.next()) {
                    falg=2;
                } else {
                    sql = "INSERT INTO tb_manager (name,pwd) values('" +
                                 managerForm.getName() + "','" +
                                 managerForm.getPwd() +
                                 "')";
                    falg = conn.executeUpdate(sql);
                    System.out.println("添加管理員信息的SQL：" + sql);
                }
            } catch (SQLException ex) {
                falg=0;
            }finally{
            	conn.close();
            }
        return falg;
    }

    //設置管理員權限
    public int update(ManagerForm managerForm) {
        String sql1="SELECT * FROM tb_purview WHERE id="+managerForm.getId()+"";
        ResultSet rs=conn.executeQuery(sql1);	//查詢要設置權限的管理員的權限信息
        String sql="";
        int falg=0;
        try {					//捕捉異常信息
            if (rs.next()) {	//當已經設置權限時，執行更新語句
                sql = "Update tb_purview set sysset=" + managerForm.getSysset() +
                             ",readerset=" + managerForm.getReaderset() + ",bookset="+managerForm.getBookset()+",borrowback="+managerForm.getBorrowback()+",sysquery="+managerForm.getSysquery()+" where id=" +
                     managerForm.getId() + "";
            }else{	//未設置權限時，執行插入語句
                sql="INSERT INTO tb_purview values("+managerForm.getId()+","+managerForm.getSysset()+","+managerForm.getReaderset()+","+managerForm.getBookset()+","+managerForm.getBorrowback()+","+managerForm.getSysquery()+")";
            }
            falg = conn.executeUpdate(sql);
            System.out.println("修改數據時的SQL：" + sql);
        } catch (SQLException ex) {
            falg=0;			//表示設置管理員權限失敗
        }finally{
        	conn.close();	//關閉數據庫連接
        }
        return falg;
    }
    //修改管理員密碼
public int updatePwd(ManagerForm managerForm){
    String sql="UPDATE tb_manager SET pwd='"+managerForm.getPwd()+"' where name='"+managerForm.getName()+"'";
    int ret=conn.executeUpdate(sql);
    System.out.println("修改管理員密碼時的SQL："+sql);
    conn.close();
    return ret;
}

//刪除數據
    public int delete(ManagerForm managerForm) {
    	int flag=0;
    	try{		//捕捉異常信息
        String sql = "DELETE FROM tb_manager where id=" + managerForm.getId() +"";
        flag = conn.executeUpdate(sql);	//執行刪除管理員信息的語句
        if (flag !=0){
            String sql1 = "DELETE FROM tb_purview where id=" + managerForm.getId() +"";
            conn.executeUpdate(sql1);	//執行刪除權限信息的語句
        }}catch(Exception e){
        	System.out.println("刪除管理員信息時產生的錯誤："+e.getMessage());	//輸出錯誤信息
        }finally{
        	conn.close();	//關閉數據庫連接
        }
        return flag;
    }
}