usermanager.java

淘客在线客服管理系统（TaokeOCS V3.2）

package msg;
import msg.*;
import java.io.*;
import java.util.*;
import java.lang.*;
import java.sql.*;
import java.net.*;
import java.text.SimpleDateFormat;

public class UserManager{
	String sql="";
	ResultSet rs=null;
    DbConn db=null;
	public UserManager(){
		try{
			db=new msg.DbConn();
		}catch(Exception e){}
	}
	/**
	  *根据站点url获取站点客服人员的属性(以XML方式)
	  *生成的内容只针对IE浏览器（netscape浏览器请参看方法getSiteUserInXml_）
	  *调用者：webTalk.jsp
	  *@param url 网站地址
	  *@return String 站点客服人员的属性
	  */
		public String getSiteUserInXml(String url){			 
		String msgToBeSent="<xml ondatasetcomplete=\\\"inithosts();\\\" id=\\\"hostids\\\">"+
					       "<?xml version=\\\"1.0\\\" encoding=\\\"utf-8\\\"?> <Hosts  divid=\\\"\\\" type=\\\"0\\\">";
		try{			
			sql="select id,type,voiphide,mbhide,voipenabled,mbenabled,state,name from user,site "+
			    "where site.domainIds=user.domainId and domain=\""+url+"\" "+
			    "group by type,id";
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();
			String type=null;
			while(rs!=null && rs.next()){  
				//<YdGroup name=><Host voiphide= mbhide= voipenabled= mbenabled= state= name=/></YdGroup>
				if(type==null ){
					type=rs.getString("type");			 
 					msgToBeSent +="<YdGroup name=\\\""+rs.getString("type")+"\\\">";
				}	
				if(type!=null && !type.equals(rs.getString("type")) ){
					type=rs.getString("type");			 
 					msgToBeSent +="</YdGroup><YdGroup name=\\\""+rs.getString("type")+"\\\">";
				}	
				msgToBeSent +="<Host voiphide=\\\""+ rs.getString("voiphide")+"\\\" mbhide=\\\""+ rs.getString("mbhide")+
							  "\\\" voipenabled=\\\""+ rs.getString("voipenabled")+"\\\" mbenabled=\\\""+ rs.getString("mbenabled")+
							  "\\\" state=\\\""+ rs.getString("state")+"\\\" name=\\\""+ rs.getString("name")+
							  "\\\" id=\\\""+ rs.getString("id")+"\\\"/>";					 		
			}				
 			if(db.getConnection()!=null && !db.isClosed())
				db.closeConnection();	
		}catch(Exception e){
			closeDbConnection();
			Log.log("Class:UserManager method: getSiteUserInXml() error:"+e.getMessage()+" sql:"+sql);
			return "";
		}	
		 
		msgToBeSent +="</YdGroup></Hosts></xml>";
		return msgToBeSent;	
	}
	/**
	  *根据站点url获取站点客服人员的属性(以XML方式)
	  *生成的内容只针对netscape浏览器（netscape浏览器请参看方法getSiteUserInXml）
	  *调用者：webTalk.jsp
	  *@param url 网站地址
	  *@return String 站点客服人员的属性
	  */ 
	public String getSiteUserInXml_(String url){			 
		//String msgToBeSent="<xml  id=\"hostids\">"+
		//			       "<?xml version=\"1.0\" encoding=\"utf-8\"?> <Hosts  divid=\"\" type=\"0\">";
	String msgToBeSent="<Hosts  divid=\"\" type=\"0\">";
		try{			
			sql="select id,type,voiphide,mbhide,voipenabled,mbenabled,state,name from user,site "+
			    "where site.domainIds=user.domainId and domain=\""+url+"\" "+
			    "group by type,id";
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();
			String type=null;
			while(rs!=null && rs.next()){  
				//<YdGroup name=><Host voiphide= mbhide= voipenabled= mbenabled= state= name=/></YdGroup>
				if(type==null ){
					type=rs.getString("type");			 
 					msgToBeSent +="<YdGroup name=\""+rs.getString("type")+"\">";
				}	
				if(type!=null && !type.equals(rs.getString("type")) ){
					type=rs.getString("type");			 
 					msgToBeSent +="</YdGroup><YdGroup name=\""+rs.getString("type")+"\">";
				}	
				msgToBeSent +="<Host voiphide=\""+ rs.getString("voiphide")+"\" mbhide=\""+ rs.getString("mbhide")+
							  "\" voipenabled=\""+ rs.getString("voipenabled")+"\" mbenabled=\""+ rs.getString("mbenabled")+
							  "\" state=\""+ rs.getString("state")+"\" name=\""+ rs.getString("name")+
							  "\" id=\""+ rs.getString("id")+"\"/>";					 		
			}				
 			if(db.getConnection()!=null && !db.isClosed())
				db.closeConnection();	
		}catch(Exception e){
			closeDbConnection();
			Log.log("Class:UserManager method: getSiteUserInXml_() error:"+e.getMessage()+" sql:"+sql);
			return "";
		}	
		 
		msgToBeSent +="</YdGroup></Hosts>";//</xml>";
		return msgToBeSent;	
	}

	/**
	  *以XML方式读取某网站的所有服务人员的属性
	  *根据站点url获取站点服务人员的属性，以xml方式返回，该xml被浏览者刷新服务人员信息时调用(msgManager.jsp)
 	  */	 
/**
	public String getSiteUserInXml1(String url){			 
		String msgToBeSent="<xml ondatasetcomplete=\"inithosts();\" id=\"hostids\">"+
					       "<?xml version=\"1.0\" encoding=\"utf-8\"?> <Hosts  divid=\"\" type=\"0\">";
		try{
			sql="select id,type,voiphide,mbhide,voipenabled,mbenabled,state,name from user,site "+
				"where user.domainId=site.domainIds and domain=\""+url+"\" and type<>\"visitor\" "+
				"group by type,id";
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();
			String type=null;
			while(rs!=null && rs.next()){  
				//<YdGroup name=><Host voiphide= mbhide= voipenabled= mbenabled= state= name=/></YdGroup>
				if(type==null ){
					type=rs.getString("type");			 
 					msgToBeSent +="<YdGroup name=\""+rs.getString("type")+"\">";
				}	
				if(type!=null && !type.equals(rs.getString("type")) ){
					type=rs.getString("type");			 
 					msgToBeSent +="</YdGroup><YdGroup name=\""+rs.getString("type")+"\">";
				}	
				msgToBeSent +="<Host voiphide=\""+ rs.getString("voiphide")+"\" mbhide=\""+ rs.getString("mbhide")+
							  "\" voipenabled=\""+ rs.getString("voipenabled")+"\" mbenabled=\""+ rs.getString("mbenabled")+
							  "\" state=\""+ rs.getString("state")+"\" name=\""+ rs.getString("name")+
							  "\" id=\""+ rs.getString("id")+"\"/>";					 		
			}				
 			if(db.getConnection()!=null && !db.isClosed())
				db.closeConnection();	
		}catch(Exception e){
			closeDbConnection();
			Log.log("Class:UserManager method: getSiteUserInXml1() error:"+e.getMessage()+" sql:"+sql);
			return "";
		}	
		 
		msgToBeSent +="</YdGroup></Hosts></xml>";
		return msgToBeSent;	
	}	
**/
	/**
	  *以XML方式读取某网站的所有服务人员和在线浏览者属性  由于数据库中访问者数据量很大，获取在线浏览者的属性费时太长
          * 现在已经取消获取在线浏览者的属性，改由客户端登陆后，在发送给浏览者状态改变时，由浏览者通知客户端访问者在线的
          *的消息
	  *调用者： client.jsp
	  *@param domain 网站地址
	  *@param vid 客户端id
	  *@return String 返回网站所有克客服人员的属性
	  */	 
	public String getSiteUserInXml0(String domain,String vid){
		//添加在线交谈和其他客服组			 
		String msgToBeSent="<D><GS><G i=\"TALKING\"  t=\"正在交谈\"/>";				       
		try{	
			
			//添加所有的域名组
			StringTokenizer st = new StringTokenizer(domain,","); 
			String t="";
			while(st.hasMoreTokens()){
				t=st.nextToken();
				//msgToBeSent +="<G i=\"ONLINE\"  tag=\""+t+"\" t=\"当前在线["+t+"]\"/>";
				if(t.length()>17)
					msgToBeSent +="<G i=\"ONLINE\"  tag=\""+t+"\" t=\"["+t.substring(0,16)+"..]\"/>";	
				else
					msgToBeSent +="<G i=\"ONLINE\"  tag=\""+t+"\" t=\"["+t+"]\"/>";	
			}
			//添加同事组
			msgToBeSent +="<G i=\"ONLINE\"  tag=\"mate\" t=\"我的同事\"/>";
			//添加好友组
			msgToBeSent +="<G i=\"ONLINE\"  tag=\"friend\" t=\"我的好友\"/>";
			//添加我的行业群组
			sql="select groups.groupId as groupId,groupName from mygroup,groups where "+
			    "groups.groupId=mygroup.groupId and userId=\""+vid+"\"";	
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();	
			String myGroupIds="";	
			int numberOfGroups=0;	 
			while(rs!=null && rs.next()){  					 
				msgToBeSent +="<G i=\"ONLINE\"  tag=\""+rs.getString("groupId")+"\" t=\"[群]"+
					      rs.getString("groupName")+"\"/>";		
				myGroupIds +=","+rs.getString("groupId");
				numberOfGroups++;			 		
			}	
			if(myGroupIds.startsWith(","))
				myGroupIds=myGroupIds.substring(1);
			msgToBeSent +="</GS>";				 
			///////////////////////////////////
			msgToBeSent +="<US>";	
			//读取我的同事
			String theDomain="";			
			int index=domain.indexOf(",");
			if(index>0)
				theDomain=domain.substring(0,index);
			else
				theDomain=domain;
			String domainIds=null;			 
			sql="select id,name,domainIds from user,site where user.domainId=site.domainIds and domain=\""+theDomain+
				"\" and state<>\"OFFLINE\"";
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();			 
			while(rs!=null && rs.next()){  	
				if(domainIds==null)
					domainIds=rs.getString("domainIds");				 
				if( (rs.getString("id")).equals(vid) )
					continue;
				msgToBeSent +="<U i=\""+rs.getString("id")+"\" t=\"999\" s=\"ONLINE\" v=\"\""+
					      " dm=\"mate\" n=\""+rs.getString("name")+"\"></U>";				 		
			}	

			//读取行业群组中的客户端
			if(numberOfGroups>0){
				sql="select userId,name,groupId from mygroup,user where mygroup.userId=user.id and groupId in("+
			    	    myGroupIds+") and user.state<>\"OFFLINE\"";	
				if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
					db.setConnection();  
				db.setSqlQuery(sql);
				rs=db.getResult();						 
				while(rs!=null && rs.next()){
					if( (rs.getString("userId")).equals(vid) )
						continue;
					msgToBeSent +="<U i=\""+rs.getString("userId")+"\" t=\"9\" s=\"ONLINE\" v=\"\""+
					      	      " dm=\""+rs.getString("groupId")+"\" n=\""+rs.getString("name")+"\"></U>";
				}
			}
			//获取好友
			sql="select id2,name from user,friend where user.id=friend.id2 and id1=\""+vid+"\" and user.state<>\"OFFLINE\"";	
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();						 
			while(rs!=null && rs.next()){				 
				msgToBeSent +="<U i=\""+rs.getString("id2")+"\" t=\"99\" s=\"ONLINE\" v=\"\""+
					      " dm=\"friend\" n=\""+rs.getString("name")+"\"></U>";
			}
			//msgToBeSent +="<U i=\"admin@100im_cn\" t=\"\" s=\"ONLINE\" v=\"\"  dm=\"1\"></U>";
 			
			//String[] webUrls=null;
			//st = new StringTokenizer(domain,",");		 
			//while(st.hasMoreTokens()){	
				
				sql="select id,tag,state,visit,talker,domain,lastIp,fromUrl,lastTime from visitor,site where visitor.domainId=site.domainId "+
					" and domainIds=\""+domainIds+"\" and state<>\"OFFLINE\"";
 				if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
					db.setConnection();  
				db.setSqlQuery(sql);
				rs=db.getResult();	
					
				StringTokenizer st1;long ipInt=0;ResultSet rs1;	 
				while(rs!=null && rs.next()){  					 
					msgToBeSent +="<U i=\""+rs.getString("id")+"\" t=\""+rs.getString("tag")+"\" s=\""+
								  rs.getString("state")+"\" v=\""+rs.getString("visit")+rs.getString("talker")+"\"  dm=\""+
								  rs.getString("domain")+"\">"+"<I i=\""+rs.getString("lastIp")+
								  "\" ";
					st1 = new StringTokenizer(rs.getString("lastIp"),"."); 
					ipInt=Long.parseLong(st1.nextToken())*256*256*256+Long.parseLong(st1.nextToken())*256*256+
						Long.parseLong(st1.nextToken())* 256+Long.parseLong(st1.nextToken());
					sql="select country,city from address where ip1<="+ipInt+" and ip2>="+ipInt;			 
					if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
						db.setConnection();  //local
					db.setSqlQuery(sql);
					rs1=db.getResult();
					if(rs1!=null && rs1.next()){				
						msgToBeSent +="a=\""+rs1.getString("country");
						if(rs1.getString("city")==null)
							msgToBeSent +="\" ";
						else
							msgToBeSent +=rs1.getString("city")+"\" ";
					}
					else
						msgToBeSent +="a=\"未知地域\" ";

					msgToBeSent +=" o=\"XXX\" bo=\"XXX\" l=\"XXX\" p=\"XXX\" "+
								  "u=\""+rs.getString("fromUrl")+"\" ad=\"0\"/><VS><T f=\"\" ot=\"true\" u=\""+
								  rs.getString("fromUrl")+"\" t=\""+rs.getString("lastTime")+"\"/></VS></U>";			 		
				}
				

			//}
			msgToBeSent +="</US>";		
			 
			//标记所有发给我的短信为已读
			//sql="update message set isRead=\"1\" where toId=\""+vid+"\"";
			//if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
			//	db.setConnection();  
			//db.setSqlQuery(sql);	
			//db.executeUpdate();	
		 	
		/**		<US><U i="v10386529" t="0" s="ONLINE"  v="1"  dm="www.netalker.com.cn" ><I i="61.232.3.45" a="北京市-铁通" o="winnt" bo="IE 6.0" l="zh-cn" p="1024*768" u="http://www.netalker.com.cn/" ad="0"/><VS><T  f=""  ot="true"  u="http://www.netalker.com.cn/"  t="09月26日 08:02"/></VS></U><U i="v10386528" t="0" s="ONLINE"  v="1"  dm="www.netalker.com.cn" ><I i="61.232.3.45" a="北京市-铁通" o="winnt" bo="IE 6.0" l="zh-cn" p="1024*768" u="http://www.netalker.com.cn/" ad="0"/><VS><T  f=""  ot="true"  u="http://www.netalker.com.cn/"  t="09月26日 08:02"/></VS></U></US></D></xml>	
			**/ 
			
 			if(db.getConnection()!=null && !db.isClosed())
				db.closeConnection();	
		}catch(Exception e){
			closeDbConnection();
			Log.log("Class:UserManager method: getSiteUserInXml0() error:"+e.getMessage()+" sql:"+sql);
			return "";
		}	
		msgToBeSent +="</D></xml>";	 
 		return msgToBeSent;	
	}	
	/**
	  *当一个新的浏览者来访时，判断webTalk.jsp所产生的id是否与visitor表中已经有的id相重
	  *该方法被webTalk.jsp有新来访者时调用
	  *@param id 新产生访问者id
	  *@return boolean 是否相重
	  */	 
	public boolean hasSameId(String id){
		try{
			sql="select id from visitor where id=\""+id+"\"";
			if(db==null) db=new msg.DbConn();if(db.getConnection()==null || db.isClosed())			 
				db.setConnection();  
			db.setSqlQuery(sql);
			rs=db.getResult();
			if(rs!=null && rs.last() && rs.getRow()>0){
				if(db.getConnection()!=null && !db.isClosed())
					db.closeConnection();	
				return true;
			}				 
 			if(db.getConnection()!=null && !db.isClosed())