package hbu.david.cmc.work;

import hbu.david.cmc.bean.*;
import hbu.david.cmc.dao.*;
import hbu.david.cmc.util.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class UserBean {

	
	/**
	 * 判斷用戶名是否存在
	 */
	public static boolean isUsernameExist(String username){
		boolean ok=false;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from userinfo where username=?";
		conn=DatabaseBean.getConnection();
		try{
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, username);
			rs=prepstmt.executeQuery();
			if(rs.next()){
				ok=true;
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return ok;
	}
	/**
	 * 注冊
	 * @param userInfo
	 * @return
	 */
	public static UserInfo addUser(UserInfo userInfo){
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="insert into userinfo(username,password,nickname,addTime) values(?,?,?,?)";
		conn=DatabaseBean.getConnection();
		if(isUsernameExist(userInfo.getUsername())){
			userInfo=null;
		}else{
			//用戶名不存在
			try {
				conn.setAutoCommit(false);
				prepstmt=conn.prepareStatement(sql);
				prepstmt.setString(1, userInfo.getUsername());
				prepstmt.setString(2, userInfo.getPassword());
				prepstmt.setString(3, userInfo.getNickName());
				prepstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
				if(1==prepstmt.executeUpdate()){
					;
				}else{
					userInfo=null;
				}
				
				conn.commit();
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
				userInfo=null;
			}finally{
				DatabaseBean.close(conn, prepstmt, null);
			}
		}
		return userInfo;
	}
	/**
	 * 登錄
	 */
	public static UserInfo Login(UserInfo userInfo){
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from userinfo where username=? and password=?";
		conn=DatabaseBean.getConnection();
		try{
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, userInfo.getUsername());
			prepstmt.setString(2, userInfo.getPassword());
			rs=prepstmt.executeQuery();
			
			if(rs.next()){
				/**
				userInfo.setId(rs.getInt("id"));
				userInfo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				userInfo.setNickName(rs.getString("nickname"));
				userInfo.setUsergroup(rs.getInt("usergroup"));
				userInfo.setUrl(rs.getString("url"));
				*/
				userInfo=getUserInfoById(rs.getInt("id"));
			}else{
				userInfo=null;
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			userInfo=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return userInfo;
	}
	
	/**
	 * 根據id獲取用戶信息
	 * 
	 */
	public static UserInfo getUserInfoById(int id){
		UserInfo userInfo=new UserInfo();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from userinfo where id=?";
		conn=DatabaseBean.getConnection();
		try{
			conn.setAutoCommit(false);
			if(id<=0){
				userInfo=null;
			}else{
				prepstmt=conn.prepareStatement(sql);
				prepstmt.setInt(1, id);
				rs=prepstmt.executeQuery();
				
				if(rs.next()){
					userInfo.setId(rs.getInt("id"));
					userInfo.setUrl(rs.getString("url"));
					userInfo.setUsername(rs.getString("username"));
					userInfo.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
					userInfo.setNickName(rs.getString("nickname"));
					userInfo.setUsergroup(rs.getInt("usergroup"));
					userInfo.setUrl(rs.getString("url"));
				}else{
					userInfo=null;
				}
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			userInfo=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return userInfo;
	} 
	/**
	 * 修改頭像
	 */
	public static boolean updateUserPhoto(String url,int userId){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="update userinfo set url=? where id=?";
		conn=DatabaseBean.getConnection();
		try{
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, url);
			prepstmt.setInt(2, userId);
			ok=(1==prepstmt.executeUpdate());
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return ok;
	}
	/**
	 * 判斷用戶密碼是否正確
	 */
	public static boolean isRightUsernamePassword(String username,String password){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from userinfo where username=? and password=?";
		try{
			conn=DatabaseBean.getConnection();
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, username);
			prepstmt.setString(2, password);
			//ok=(1==prepstmt.executeUpdate());
			
			rs=prepstmt.executeQuery();
			
			if(rs.next()){
				//查出結果說明正確
				ok=true;
			}else{
				ok=false;
			}
			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return ok;
	}
	/**
	 * 修改密碼
	 */
	public static boolean updatePassword(int userId,String password){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		//ResultSet rs=null;
		String sql="update userinfo set password=? where id=?";
		try{
			conn=DatabaseBean.getConnection();
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, password);
			prepstmt.setInt(2, userId);
			ok=(1==prepstmt.executeUpdate());			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, null);
		}
		return ok;
	}
	/**
	 * 獲取用戶數
	 */
	public static int getAllUserNum(){
		//ArrayList<Integer> arr=new ArrayList<Integer>();
		int num=0;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select count(id) from userinfo";
		try{
			conn=DatabaseBean.getConnection();
			prepstmt=conn.prepareStatement(sql);
			rs=prepstmt.executeQuery();			
			if(rs.next()){
				//查出結果說明正確
				num=rs.getInt(1);
			}			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			num=0;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return num;
	}
	/**
	 * 獲取所有用戶的ID
	 */
	public static int[] getAllUserId(){
		//ArrayList<Integer> arr=new ArrayList<Integer>();
		int[] arr =new int[getAllUserNum()];
		int i=0;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select id from userinfo";
		try{
			conn=DatabaseBean.getConnection();
			prepstmt=conn.prepareStatement(sql);
			rs=prepstmt.executeQuery();			
			while(rs.next()){
				//查出結果說明正確
				arr[i]=rs.getInt("id");
				i++;
			}			
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			arr=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return arr;
	}
	/**
	 * 隨即獲取十個用戶的信息
	 */
	public static List<UserInfo> getRandomUser(){
		ArrayList<UserInfo> userList=new ArrayList<UserInfo>();
		//獲取所有用戶的ID
		int [] idList=getAllUserId();
		if(idList.length<=10){
			for(int i=0;i<idList.length;i++){
				userList.add(getUserInfoById(idList[i]));
			}
		}
		else{
			for(int i=0;i<10;i++){
				//Random random=new Random(idList.length);
				int random=new Random().nextInt(idList.length);
				userList.add(getUserInfoById(idList[random]));
			}
		}
		return userList;
		
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//UserInfo u=getUserInfoById(6);
		//System.out.print(u.getUrl()+u.getNickName()+u.getPassword()+u.getUsergroup()+u.getUsername()+u.getAddTime());;
		System.out.print(getRandomUser());
		/*(UserInfo u=new UserInfo();
		u.setUsername("qiudawei");
		u.setPassword("851120");
		u.setNickName("滿天飛雪");
		//u=new UserBean().Login(u);
		//System.out.print(new UserBean().isUsernameExist(u));
		u=new UserBean().addUser(u);
		if(u==null){
			System.out.print(u);
		}else{
			System.out.print(u.getId()+u.getNickName()+u.getPassword()+u.getUsergroup()+u.getUsername()+u.getAddTime());;
		}
		/*UserInfo u=new UserInfo();
		u.setUsername("Dai");
		u.setPassword("asd");
		u=new UserBean().Login(u);
		System.out.print(u.getId()+u.getNickName()+u.getPassword()+u.getUsergroup()+u.getUsername()+u.getAddTime());;
		*/
	}

}