package net.chat;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.text.SimpleDateFormat;

public class ChatRoomList {
	
	/**
	 * 計算聊天室中的在線人數
	 * */
	public int countUser(String chatRoom)
	throws SQLException,ClassNotFoundException
	{
		BaseConn conn = null;
		try
		{
			conn = new BaseConn();
			String sql="select count(*) As intCount from onlineUser where chatRoom=?";
			PreparedStatement ps = conn.preparedStatement(sql);
			ps.setString(1,chatRoom);
			ResultSet rs = conn.executeQuery();
			if(rs.next())
				return rs.getInt("intCount");
			else
				return 0;
		}catch(SQLException ex)
		{
			ex.printStackTrace();
			throw ex;
		}catch(ClassNotFoundException ex)
		{
			ex.printStackTrace();
			throw ex;
		}finally
		{
			conn.closeDB();
		}
	}
	
	/**
	 * 有用戶登錄進入聊天室時，在onlineUser表中添加一個用戶
	 * 首先判斷該用戶是否已經在聊天室，如果不在聊天室就把該用戶添加到聊天室在線用
      戶表中
	 * */
	public boolean addOnlineUser(String userName,String chatRoom)
	throws SQLException,ClassNotFoundException
	{
		BaseConn conn = null;
		try
		{
			conn = new BaseConn();
			
			//先判斷該用戶是不是已經在該聊天室
			String sql="select * from onlineUser where nickName=? and chatRoom=?";
			PreparedStatement ps = conn.preparedStatement(sql);
			ps.setString(1,userName);
			ps.setString(2,chatRoom);
			ResultSet rs = conn.executeQuery();
			if(rs.next())
			{
				return true;
			}
			else
			{
				//如果聊天室不存在該用戶，將訪用戶添加到聊天室的在線用戶表中
				sql="insert into onlineUser(nickName,chatRoom) values(?,?)";
				ps = conn.preparedStatement(sql);
				ps.setString(1,userName);
				ps.setString(2,chatRoom);
				conn.executeUpdate();
				ps.close();
              //獲取信息時間，并將時間定義為year-Month-day Hour:minutes:seconds
				SimpleDateFormat cal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    String time = cal.format(new java.util.Date());
			    //在msgInfo表中添加一條系統公告，歡迎新用戶進入聊天室
				sql = "insert into msgInfo(chatRoom,msgFrom,msgTo,chatTime,msgContent)values(?,?,?,?,?)";
				ps = conn.preparedStatement(sql);
				ps.setString(1,chatRoom);
				ps.setString(2,"system notice");
				ps.setString(3,"all people");
				ps.setString(4,time);
				ps.setString(5,"<font color=green>"+userName+"</font>enter our chatroom.let us welcome him or her");
				conn.executeUpdate();
				return true;
			}
		}catch(SQLException ex)
		{
			ex.printStackTrace();
			throw ex;
		}catch(ClassNotFoundException ex)
		{
			ex.printStackTrace();
			throw ex;
		}finally
		{
			conn.closeDB();
		}
	}
}