package db;

import java.sql.*;

import bean.*;

import java.util.ArrayList;
import java.text.SimpleDateFormat;
import java.util.Calendar;

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

	// ResultSet對象
	private ResultSet resultSet = null;

	private java.sql.PreparedStatement preparedStatement = null;

	private boolean flag = false;

	private java.sql.CallableStatement callableStatement = null;

	private int pagecount = 0;

	private int pagedata = 0;

	/**
	 * 構造函數，構造與數據庫的連接信息
	 */
	public CustomerOperation() {
		try {
			// 加載SQL Server的數據庫驅動程序類
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			// 創建Connection對象
			connection = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=MyShop",
							"sa", "");
		} catch (SQLException ex) {

		} catch (ClassNotFoundException ex) {

		}

	}

	/**
	 * 驗證登陸用戶的合法性
	 * 
	 * @param userName
	 * @param userPwd
	 * @return
	 */
	public UserBean checkUsersLogin(String userName, String userPwd) {
		UserBean useBean;
		flag = false;
		try {
			// 從users表中查詢特定用戶的SQL語句
			String sql = "select count(*) from users where userName=? and userPwd=?";
			// 創建PreparedStatement對象
			preparedStatement = connection.prepareStatement(sql);
			// 為PreparedStatement對象設置必要的參數
			preparedStatement.setString(1, userName);
			preparedStatement.setString(2, userPwd);
			// 執行數據庫查詢操作
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				// 判斷查詢結果是否為空
				if (resultSet.getInt(1) > 0) {
					useBean = this.getUser(userName);
				} else {
					useBean = null;
				}
			} else {
				useBean = null;
			}
		} catch (Exception e) {
			useBean = null;
			e.printStackTrace();
		}
		return useBean;
	}

	/**
	 * 根據用戶名查詢其全部的用戶信息
	 * 
	 * @param userName
	 * @return
	 */
	public UserBean getUser(String userName) {
		UserBean useBean = new UserBean();
		// 查詢特定用戶信息的SQL語句
		String sql = "select * from users where userName=?";
		try {
			// 創建Statement對象
			preparedStatement = connection.prepareStatement(sql);
			// 設置Statement對象的相關參數
			preparedStatement.setString(1, userName);
			// 執行查詢語句
			resultSet = preparedStatement.executeQuery();
			// 對查詢到的結果集進行處理
			while (resultSet.next()) {
				useBean.setRealName(resultSet.getString("realName"));
				useBean.setUserAdds(resultSet.getString("UserAdds"));
				useBean.setUserAge(resultSet.getInt("UserAge"));
				useBean.setUserCard(resultSet.getString("Usercard"));
				useBean.setUserCity(resultSet.getInt("UserCity"));
				useBean.setUserCode(resultSet.getInt("UserCode"));
				useBean.setUserId(resultSet.getInt("userId"));
				useBean.setUserMail(resultSet.getString("UserMail"));
				useBean.setUserName(resultSet.getString("userName"));
				useBean.setUserPhone(resultSet.getString("UserPhone"));
				useBean.setUserPwd(resultSet.getString("UserPwd"));
				useBean.setUserSex(resultSet.getInt("UserSex"));
				useBean.setUserStrp(resultSet.getInt("UserStep"));
				useBean.setUserWork(resultSet.getString("UserWork"));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return useBean;
	}

	/**
	 * 根據cityId信息查詢城市名
	 * 
	 * @param cityId
	 * @return
	 */
	public String getCity(int cityId) {
		try {
			// 創建Statement對象
			preparedStatement = connection
					.prepareStatement("select Display from City where CityId=?");
			// 設置其對應的參數
			preparedStatement.setInt(1, cityId);
			// 執行查詢
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getString("Display");
		} catch (SQLException ex) {

			return null;
		}

	}

	/**
	 * 根據stepId取得相應等級信息
	 * 
	 * @param stepId
	 * @return
	 */
	public String getStep(int stepId) {
		try {
			// 創建Statement對象
			preparedStatement = connection
					.prepareStatement("select Display from Step where StepId=?");
			// 設置其對應的參數
			preparedStatement.setInt(1, stepId);
			// 執行查詢
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getString("Display");
		} catch (SQLException ex) {

			return null;
		}

	}

	public int getUserOrderCount(int userId) {
		// 查詢某個用戶定單數量的查詢語句
		String sql = "select count(*) from OrderForm where userId=?";
		try {
			// 創建Statement對象
			preparedStatement = connection.prepareStatement(sql);
			// 設置其對應的參數
			preparedStatement.setInt(1, userId);
			// 執行查詢
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return 0;
	}

	/**
	 * 根據userId取得其用戶信息
	 * 
	 * @param userId
	 * @return
	 */
	public UserBean getUser(int userId) {
		UserBean useBean = new UserBean();
		// 查詢特定用戶信息的sql語句
		String sql = "select * from users where userId=?";
		try {
			// 創建Statement對象
			preparedStatement = connection.prepareStatement(sql);
			// 設置Statement對象相應的參數
			preparedStatement.setInt(1, userId);
			// 執行查詢
			resultSet = preparedStatement.executeQuery();
			// 對查詢結果的處理
			while (resultSet.next()) {
				useBean.setRealName(resultSet.getString("realName"));
				useBean.setUserAdds(resultSet.getString("UserAdds"));
				useBean.setUserAge(resultSet.getInt("UserAge"));
				useBean.setUserCard(resultSet.getString("Usercard"));
				useBean.setUserCity(resultSet.getInt("UserCity"));
				useBean.setUserCode(resultSet.getInt("UserCode"));
				useBean.setUserId(resultSet.getInt("userId"));
				useBean.setUserMail(resultSet.getString("UserMail"));
				useBean.setUserName(resultSet.getString("userName"));
				useBean.setUserPhone(resultSet.getString("UserPhone"));
				useBean.setUserPwd(resultSet.getString("UserPwd"));
				useBean.setUserSex(resultSet.getInt("UserSex"));
				useBean.setUserStrp(resultSet.getInt("UserStep"));
				useBean.setUserWork(resultSet.getString("UserWork"));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return useBean;
	}

	/**
	 * 插入新用戶操作
	 * 
	 * @param useBean
	 * @return
	 */
	public boolean insertUser(UserBean useBean) {
		boolean bool = false;
		// 判斷該用戶是否已經存在
		if (!this.hasUser(useBean.getUserName())) {

			bool = true;
			// 插入新用戶的SQL語句
			String sql = "insert into users (userName,UserPwd,realName,UserSex,UserPhone,UserMail,UserCity,UserAdds,UserCode,UserWork,Usercard,UserAge) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			try {
				// 創建Statement對象
				preparedStatement = connection.prepareStatement(sql);
				// 設置Statement對象的相應參數
				preparedStatement.setString(1, useBean.getUserName());
				preparedStatement.setString(2, useBean.getUserPwd());
				preparedStatement.setString(3, useBean.getRealName());
				preparedStatement.setInt(4, useBean.getUserSex());
				preparedStatement.setString(5, useBean.getUserPhone());
				preparedStatement.setString(6, useBean.getUserMail());
				preparedStatement.setInt(7, useBean.getUserCity());
				preparedStatement.setString(8, useBean.getUserAdds());
				preparedStatement.setInt(9, useBean.getUserCode());
				preparedStatement.setString(10, useBean.getUserWork());
				preparedStatement.setString(11, useBean.getUserCard());
				preparedStatement.setInt(12, useBean.getUserAge());
				// 執行更新操作
				int flag = preparedStatement.executeUpdate();
				if (flag == 0) {
					bool = false;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
				bool = false;
			}
		}
		return bool;
	}

	/**
	 * 更新特定用戶信息
	 * 
	 * @param useBean
	 * @return
	 */
	public boolean updateUser(UserBean useBean) {
		boolean bool = false;

		bool = true;
		// 更新用戶信息的sql語句
		String sql = "update users set userName=?,UserPwd=?,realName=?,UserSex=?,UserPhone=?,UserMail=?,UserCity=?,UserAdds=?,UserCode=?,UserWork=?,Usercard=?,UserAge=? where userId=?";
		try {
			// 創建Statement對象
			preparedStatement = connection.prepareStatement(sql);
			// 設置其相應參數
			preparedStatement.setString(1, useBean.getUserName());
			preparedStatement.setString(2, useBean.getUserPwd());
			preparedStatement.setString(3, useBean.getRealName());
			preparedStatement.setInt(4, useBean.getUserSex());
			preparedStatement.setString(5, useBean.getUserPhone());
			preparedStatement.setString(6, useBean.getUserMail());
			preparedStatement.setInt(7, useBean.getUserCity());