package db;

import java.sql.*;

import bean.*;

import java.util.*;

public class AdminOperation {
	// Connection對象
	private Connection connection = null;

	// 查詢的結(jié)果集
	private ResultSet resultSet = null;

	// PreparedStatement對象
	private java.sql.PreparedStatement preparedStatement = null;

	private boolean flag = false;

	// CallableStatement對象
	private java.sql.CallableStatement callableStatement = null;

	private int pagecount = 0;

	private int pagedata = 0;

	/**
	 * 取得pagecount的值
	 * 
	 * @return
	 */
	public int getSearchCountPage() {
		return this.pagecount;
	}

	/**
	 * 取得pagedata的值
	 * 
	 * @return
	 */
	public int getSearchCountdata() {
		return this.pagedata;
	}

	/**
	 * 配置數(shù)據(jù)庫的連接信息，建立與數(shù)據(jù)庫的連接
	 */
	public AdminOperation() {
		try {
			// 加載數(shù)據(jù)庫的驅(qū)動程序類
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			// 驅(qū)動于數(shù)據(jù)庫的連接
			connection = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=MyShop",
							"sa", "");
		} catch (SQLException ex) {

		} catch (ClassNotFoundException ex) {

		}

	}

	/**
	 * 驗證用戶的登陸信息
	 * 
	 * @param ab
	 * @return
	 */
	public boolean checkAdminLogin(AdminBean ab) {

		boolean result = false;
		try {
			// 查詢Admin表的sql語句
			String sql = "select count(*) from Admin where UserName=? and UserPwd=?";
			// 創(chuàng)建PreparedStatement對象
			preparedStatement = connection.prepareStatement(sql);
			// 設(shè)置sql語句的第一個參數(shù)為用戶名
			preparedStatement.setString(1, ab.getUserName());
			// 設(shè)置sql語句的第二個參數(shù)為用戶密碼
			preparedStatement.setString(2, ab.getUserPwd());
			// 執(zhí)行查詢操作
			resultSet = preparedStatement.executeQuery();
			// 如果查詢結(jié)果不為空就說明該用戶為合法用戶
			if (resultSet.next()) {
				if (resultSet.getInt(1) > 0) {
					result = true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 分頁返回所有的用戶
	 * 
	 * @param count
	 * @param page
	 * @return
	 */
	public ArrayList getUsersList(int count, int page) {
		ArrayList list = new ArrayList();
		try {
			// 調(diào)用存儲過程，創(chuàng)建CallableStatement對象
			callableStatement = connection
					.prepareCall("{call proc_users_page (?,?,?)}");
			// 設(shè)置callableStatement對象的第一個參數(shù)的值
			callableStatement.setInt(1, page);
			// 設(shè)置callableStatement對象的第二個參數(shù)的值
			callableStatement.setInt(2, count);
			// 設(shè)置callableStatement對象輸出值為整形
			callableStatement.registerOutParameter(3, Types.INTEGER);
			// 執(zhí)行查詢
			resultSet = callableStatement.executeQuery();
			// 將查詢到結(jié)果組裝成UserBean對象放到list中
			while (resultSet.next()) {
				UserBean ub = new UserBean();
				ub.setUserId(resultSet.getInt(1));
				ub.setUserName(resultSet.getString(2));
				ub.setUserPwd(resultSet.getString(3));
				ub.setRealName(resultSet.getString(4));
				ub.setUserSex(resultSet.getInt(5));
				ub.setUserPhone(resultSet.getString(6));
				ub.setUserMail(resultSet.getString(7));
				ub.setUserCity(resultSet.getInt(8));
				ub.setUserAdds(resultSet.getString(9));
				ub.setUserCode(resultSet.getInt(10));
				ub.setUserWork(resultSet.getString(11));
				ub.setUserCard(resultSet.getString(12));
				ub.setUserStrp(resultSet.getInt(13));
				ub.setUserAge(resultSet.getInt(14));
				list.add(ub);
			}
			pagecount = callableStatement.getInt(3);
		} catch (SQLException ex) {
			System.out.println("服務(wù)器異常發(fā)生在 getUsersList()");
			ex.printStackTrace();
		}
		return list;
	}

	/**
	 * 插入新的管理員信息
	 * 
	 * @param admin
	 * @return
	 */
	public boolean insertAdmin(AdminBean admin) {
		try {
			// 創(chuàng)建插入管理員信息的PreparedStatemnet對象
			preparedStatement = connection
					.prepareStatement("insert into admin (userName,UserPwd) values(?,?)");
			// 設(shè)置preparedStatement對象的第一個參數(shù)
			preparedStatement.setString(1, admin.getUserName());
			// 設(shè)置preparedStatement對象的第二個參數(shù)
			preparedStatement.setString(2, admin.getUserPwd());
			// 執(zhí)行更新操作
			int flag = preparedStatement.executeUpdate();
			if (flag == 0) {
				return false;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		return true;

	}

	/**
	 * 取得等級信息
	 * 
	 * @param stepId
	 * @return
	 */
	public String getStep(int stepId) {
		try {
			// 查詢Step信息的preparedStatement語句
			preparedStatement = connection
					.prepareStatement("select Display from Step where StepId=?");
			// 設(shè)置perparedStatment語句的第一個參數(shù)
			preparedStatement.setInt(1, stepId);
			// 執(zhí)行查詢
			resultSet = preparedStatement.executeQuery();
			// 將結(jié)果集指針后移
			resultSet.next();
			// 取得display字段的值
			return resultSet.getString("Display");
		} catch (SQLException ex) {

			return null;
		}

	}

	/**
	 * 取得書籍等級
	 * 
	 * @param stepId
	 * @return
	 */
	public String getBookStep(int stepId) {
		try {
			// 創(chuàng)建從BookStep取得BookStep信息的Statement對象
			preparedStatement = connection
					.prepareStatement("select Display from BookStep where BookStepId=?");
			// 設(shè)置preparedStatement對象的第一個參數(shù)
			preparedStatement.setInt(1, stepId);
			// 執(zhí)行數(shù)據(jù)庫查詢操作
			ResultSet res = preparedStatement.executeQuery();
			// 將結(jié)果集指針后移
			res.next();
			// 取得結(jié)果集中的display字段的值
			return res.getString("Display");
		} catch (SQLException ex) {

			return null;
		}

	}

	/**
	 * 插入BookType信息
	 * 
	 * @param btb
	 * @return
	 */
	public boolean insertBookType(BookTypeBean btb) {
		try {
			// 往Type表中插入一條記錄的Statement對象
			preparedStatement = connection
					.prepareStatement("insert into Type (Display) values('"
							+ btb.getDisplay() + "')");
			// 執(zhí)行更新操作
			int flag = preparedStatement.executeUpdate();
			if (flag == 0) {
				return false;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 刪除BookType信息
	 * 
	 * @param BookTypeId
	 * @return
	 */
	public boolean deleteBookType(int BookTypeId) {
		try {
			// 創(chuàng)建從Type表中刪除記錄的Statement對象
			preparedStatement = connection
					.prepareStatement("delete Type where TypeId=?");
			// 設(shè)置Statement對象的第一個參數(shù)
			preparedStatement.setInt(1, BookTypeId);
			// 執(zhí)行刪除操作
			int flag = preparedStatement.executeUpdate();
			if (flag == 0) {
				return false;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	/**