这是一个自已写的采用Ajax技术的实时在线对话系统,下面是用到的表
Chat 记录聊天信息
ChatLink 记录聊天实时状态下面是用到的存储过程列表
ChatLink_GetCStatus 实时得到客户端的状态
ChatLink_GetSStatus 实时得到服户端的状态
Chat_CInsert 插入用户提交的话
Chat_SInsert 插入客服提交的话
Chat_GetCNewMsg 得到用户端的新消息
Chat_GetSNewMsg 得到客服端的新消息问题:客户端不存在丢话的问题,而服务器端经常出现自已发的话看不到的问题,客户的话不会丢,一直没有找到是那个地方出现的问题,很头痛,请各位高人帮下忙!谢谢!-----------------------------------------------------------------------
下面是第个表的结构:Chat 记录聊天信息CREATE TABLE [Chat] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL CONSTRAINT [DF_Chat_CompanyID] DEFAULT (0),
[UserID] [int] NOT NULL CONSTRAINT [DF_Chat_UserID] DEFAULT (0),
[ServerNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_ServerNo] DEFAULT (''),
[Sayer] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_Sayer] DEFAULT (''),
[Listener] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_Listener] DEFAULT (''),
[Msg] [text] COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_dsafds] DEFAULT (''),
[Time] [datetime] NOT NULL CONSTRAINT [DF_Chat_Time] DEFAULT (0),
[Status] [int] NOT NULL CONSTRAINT [DF_Chat_Status] DEFAULT (0),
CONSTRAINT [PK_Chat] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOChatLink 记录聊天实时状态CREATE TABLE [ChatLink] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL CONSTRAINT [DF_ChatLink_CompanyID] DEFAULT (0),
[UserID] [int] NOT NULL CONSTRAINT [DF_ChatLink_UserID] DEFAULT (0),
[ServerNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ChatLink_ServerNO] DEFAULT (''),
[ServerID] [int] NOT NULL CONSTRAINT [DF_ChatLink_ServerID] DEFAULT (0),
[CustomerIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ChatLink_CustomerIP] DEFAULT (''),
[ServerIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ChatLink_ServerIP] DEFAULT (''),
[CreaterTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_CreaterTime] DEFAULT (0),
[ReadyTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_ReadyTime] DEFAULT (0),
[FinishTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_FinishTime] DEFAULT (0),
[ActiveTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_ActiveTime] DEFAULT (0),
[LastChatID] [bigint] NOT NULL CONSTRAINT [DF_ChatLink_LastChatID] DEFAULT (0),
[LastSChatID] [bigint] NOT NULL CONSTRAINT [DF_ChatLink_LastSChatID] DEFAULT (0),
[Status] [int] NOT NULL CONSTRAINT [DF_ChatLink_Status] DEFAULT (0),
CONSTRAINT [PK_ChatLink] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Chat 记录聊天信息
ChatLink 记录聊天实时状态下面是用到的存储过程列表
ChatLink_GetCStatus 实时得到客户端的状态
ChatLink_GetSStatus 实时得到服户端的状态
Chat_CInsert 插入用户提交的话
Chat_SInsert 插入客服提交的话
Chat_GetCNewMsg 得到用户端的新消息
Chat_GetSNewMsg 得到客服端的新消息问题:客户端不存在丢话的问题,而服务器端经常出现自已发的话看不到的问题,客户的话不会丢,一直没有找到是那个地方出现的问题,很头痛,请各位高人帮下忙!谢谢!-----------------------------------------------------------------------
下面是第个表的结构:Chat 记录聊天信息CREATE TABLE [Chat] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL CONSTRAINT [DF_Chat_CompanyID] DEFAULT (0),
[UserID] [int] NOT NULL CONSTRAINT [DF_Chat_UserID] DEFAULT (0),
[ServerNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_ServerNo] DEFAULT (''),
[Sayer] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_Sayer] DEFAULT (''),
[Listener] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_Listener] DEFAULT (''),
[Msg] [text] COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Chat_dsafds] DEFAULT (''),
[Time] [datetime] NOT NULL CONSTRAINT [DF_Chat_Time] DEFAULT (0),
[Status] [int] NOT NULL CONSTRAINT [DF_Chat_Status] DEFAULT (0),
CONSTRAINT [PK_Chat] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOChatLink 记录聊天实时状态CREATE TABLE [ChatLink] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL CONSTRAINT [DF_ChatLink_CompanyID] DEFAULT (0),
[UserID] [int] NOT NULL CONSTRAINT [DF_ChatLink_UserID] DEFAULT (0),
[ServerNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ChatLink_ServerNO] DEFAULT (''),
[ServerID] [int] NOT NULL CONSTRAINT [DF_ChatLink_ServerID] DEFAULT (0),
[CustomerIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ChatLink_CustomerIP] DEFAULT (''),
[ServerIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_ChatLink_ServerIP] DEFAULT (''),
[CreaterTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_CreaterTime] DEFAULT (0),
[ReadyTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_ReadyTime] DEFAULT (0),
[FinishTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_FinishTime] DEFAULT (0),
[ActiveTime] [datetime] NOT NULL CONSTRAINT [DF_ChatLink_ActiveTime] DEFAULT (0),
[LastChatID] [bigint] NOT NULL CONSTRAINT [DF_ChatLink_LastChatID] DEFAULT (0),
[LastSChatID] [bigint] NOT NULL CONSTRAINT [DF_ChatLink_LastSChatID] DEFAULT (0),
[Status] [int] NOT NULL CONSTRAINT [DF_ChatLink_Status] DEFAULT (0),
CONSTRAINT [PK_ChatLink] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--【功 能】
-- 处理客户方连接情况
--【参 数】
-- 0-@ServerNo--服务单号码
-- 1-@CustomerIP--客户的IP地址
-- 2-@OperatorID--操作人员ID值
-- 3-@OperatorIP--操作人员IP地址
--【返回值】
-- 0-CreateLink(正在连立链接)
-- 1-Connection(正在等待客服人员响应)
-- 2-Ok(连接建立成功)
-- 3-NoServer(没有在线的客服人员)
-- 4-OutLine(服务人员已离线)
-- 5-DepartLine(服务已断开)
--************************************************************************************************************
CREATE PROCEDURE ChatLink_GetCStatus
@ServerNo varchar(40)='',
@CustomerIP varchar(60)='',
@OperatorID int=0,
@OperatorIP varchar(60)=''
as
Declare @ServerID int
Declare @LinkID bigint
Declare @UserID int
Declare @Status int
--初始值
Set @ServerID=0
Set @LinkID=0
Set @UserID=0
Set @Status=0--更新断开的连接
Declare OutLink CURSOR FOR
Select ID,ServerID from ChatLink where Datediff(second,ActiveTime,Getdate())>6 and Status=1
Open OutLink
FETCH NEXT FROM OutLink into @LinkID,@ServerID
WHILE @@FETCH_STATUS = 0
begin
Update Users Set ServerCount=ServerCount-1 where Lock=0 and ID=@ServerID
Update ChatLink Set Status=2 where ID=@LinkID
FETCH NEXT FROM OutLink into @LinkID,@ServerID
end
Close OutLink
DEALLOCATE OutLink
Delete From ChatLink where Datediff(second,ActiveTime,Getdate())>6 and Status=0Set @ServerID=0
Set @LinkID=0--判断数据库里面有无此服务单号,如果没有就创建一条连接记录,并返回不同的标识
Select @LinkID=ID,@ServerID=ServerID,@Status=Status From ChatLink where ServerNo=@ServerNo
if @LinkID=0
begin
select Top 1 @UserID=ID from users where status=1 and Lock=0 and charindex(',LinkChat,',','+rtrim(QX)+',')>0 order by servercount
if @UserID>0
begin
insert into ChatLink (ServerNo,CustomerIP,CreaterTime,ActiveTime)
values (@ServerNo,@CustomerIP,GetDate(),GetDate())
return 0
end
else return 3
end
else
begin
Update ChatLink Set ActiveTime=GetDate() where ServerNo=@ServerNo
if @ServerID>0
begin
if @Status=2 return 5
Select @UserID=ID from Users where Lock=0 and id=@ServerID and Status=1
if @@Rowcount<>1
return 4
else
return 2
end
else
begin
--查出服务人数最少的坐席并建立连接
select Top 1 @UserID=ID from users where status=1 and Lock=0 and charindex(',LinkChat,',','+rtrim(QX)+',')>0 order by servercount
if @UserID>0
begin
Update ChatLink Set ReadyTime=GetDate(),ServerID=@UserID,Status=1 where ServerNo=@ServerNo
Update Users Set ServerCount=ServerCount+1 where ID=@UserID
return 1
end
else return 3
end
endGOChatLink_GetSStatus 实时得到服户端的状态--************************************** 〖使用说明〗 **************************************************
--【功 能】检测客服端连接情况
--【参 数】
-- 1-@ID--连接记录号
-- 2-@ServerID--客服的ID号
-- 3-@ServerIP--客服的IP地址
-- 3-@OperatorID--操作人员ID值
-- 4-@OperatorIP--操作人员IP地址
--【返回值】
-- 1-NoLink(连接尚未连立或者不存在)
-- 2-DepartLine(连接已断开)
-- 3-Ok(连接正常)
--************************************************************************************************************
CREATE PROCEDURE ChatLink_GetSStatus
@ID bigint=0,
@ServerID int=0,
@ServerIP varchar(60)='',
@OperatorID int=0,
@OperatorIP varchar(60)=''
as
Set nocount on
Declare @Status int
Declare @LinkID int
Declare @SID int
Declare @SIP varchar(60)
--初始值
Set @Status=0
Set @LinkID=0
Set @SID=0
Set @SIP=''--更新断开的连接
Declare OutLink CURSOR FOR
Select ID,ServerID from ChatLink where Datediff(second,ActiveTime,Getdate())>6 and Status=1
Open OutLink
FETCH NEXT FROM OutLink into @LinkID,@SID
WHILE @@FETCH_STATUS = 0
begin
Update Users Set ServerCount=ServerCount-1 where Lock=0 and ID=@SID
Update ChatLink Set Status=2 where ID=@LinkID
FETCH NEXT FROM OutLink into @LinkID,@SID
end
Close OutLink
DEALLOCATE OutLink
Delete From ChatLink where Datediff(second,ActiveTime,Getdate())>6 and Status=0--判断数据库里面有无此服务单号,返回不同的值
Select @Status=Status,@SIP=ServerIP From ChatLink Where ID=@ID and ServerID=@ServerIDif @Status=0
begin
select '1' as status
return 1
endif @Status=1
begin
if @SIP='' and @ServerIP<>'' Update ChatLink Set ServerIP=@ServerIP Where ID=@ID and ServerID=@ServerID
select '3' as status
return 3
end
else
begin
select '2' as status
return 2
endGO
@ServerNo varchar(50)='',
@Msg text='',
@OperatorID int=0,
@OperatorIP varchar(50)=''
as
--审核数据
Select ID from ChatLink where ServerNo=@ServerNo and CustomerIP=@OperatorIP
if @@Rowcount<>1 return -1INSERT INTO [Chat]([ServerNo], [Msg], [Time])
VALUES(@ServerNo,@Msg,GetDate())
GO
Chat_SInsert 插入客服提交的话CREATE PROCEDURE Chat_SInsert
@ID bigint,
@ServerID int=0,
@Msg text='',
@OperatorID int=0,
@OperatorIP varchar(50)=''
as
Declare @ServerNo varchar(50)Set @ServerNo=''--审核数据
Select @ServerNo=ServerNo from ChatLink where ServerID=@ServerID and ID=@IDif @ServerNo='' return -1INSERT INTO [Chat]([userid],[ServerNo], [Msg], [Time])
VALUES(@ServerID,@ServerNo,@Msg,GetDate())
GOChat_GetCNewMsg 得到用户端的新消息--************************************** 〖使用说明〗 **************************************************
--【参 数】
-- 0-@ServerNo--服务单号码
-- 1-@CustomerIP--客户的IP地址
-- 2-@OperatorID--操作人员ID值
-- 3-@OperatorIP--操作人员IP地址
--【返回记录】
-- 0-Time(聊天记录的时间)
-- 1-Msg(聊天记录的内容)
-- 2-Sayer(发言者)
-- 3-Listener(对像)
--************************************************************************************************************
CREATE Procedure Chat_GetCNewMsg
@ServerNo varchar(40)='',
@CustomerIP varchar(60)='',
@OperatorID int=0,
@OperatorIP varchar(60)=''
as
--声明部分
Declare @LastChatID bigint
Declare @LastChatLinkID bigint
Declare @NewChatID bigint
Declare @Msg varchar(200)
Declare @Time DateTimeSet @LastChatLinkID=0
Set @NewChatID=0
Set @LastChatID=0
Set @Msg=''
Set @Time=0
--第一步:从ChatLink得到最新的LastChatID,如果得不到直接返回-1
select @LastChatLinkID=ID,@LastChatID=LastChatID from ChatLink where ServerNo=@ServerNo and CustomerIP=@CustomerIP
--第二步:根据返回的LastChatID,得到最新的消息。
if @LastChatLinkID>0
begin
Select @NewChatID=max([ID]) from Chat where ServerNo=@ServerNo and ID>@LastChatID
--第三步:改写ChatLink中的LastChatID,返回消息。
if @NewChatID>0
begin
Select [UserID],[Time],[Msg],[Sayer],[Listener] from Chat where ServerNo=@ServerNo and ID>@LastChatID and ID<=@NewChatID order by id
if @@Rowcount>=1 Update ChatLink Set LastChatID=@NewChatID where ServerNo=@ServerNo and CustomerIP=@CustomerIP
end
else
select 0 as [UserID],getdate() as [Time],'' as [Msg],'' as [Sayer],'' as [Listener]
end
else
begin
select 0 as [UserID],getdate() as [Time],'' as [Msg],'' as [Sayer],'' as [Listener]
endGOChat_GetSNewMsg 得到客服端的新消息--************************************** 〖使用说明〗 **************************************************
--【参 数】
-- 0-@ID--连接号码
-- 1-@ServerID--服务人员的ID值
-- 1-@ServerIP--服务人员的IP地址
-- 2-@OperatorID--操作人员ID值
-- 3-@OperatorIP--操作人员IP地址
--【返回记录】
-- 0-Time(聊天记录的时间)
-- 1-Msg(聊天记录的内容)
-- 2-Sayer(发言者)
-- 3-Listener(对像)
--************************************************************************************************************
CREATE Procedure Chat_GetSNewMsg
@ID bigint,
@ServerID int,
@ServerIP varchar(60)='',
@OperatorID int=0,
@OperatorIP varchar(60)=''--Declare @ID bigint
--Declare @ServerID int
--Declare @ServerIP varchar(60)--Set @ID=15
--Set @ServerID=1
--Set @ServerIP='192.168.1.212'
as
Set nocount on
--声明部分
Declare @ServerNo varchar(50)
Declare @LastSChatID bigint
Declare @TmpID bigint
Declare @NewSChatID bigint
Declare @Msg varchar(200)
Declare @Time DateTimeSet @ServerNo=''
Set @TmpID=0
Set @NewSChatID=0
Set @LastSChatID=0
Set @Msg=''
Set @Time=0
--第一步:从Users表,如果得不到直接返回-1
select @TmpID=ID from Users where Lock=0 and ID=@ServerID
Print '从Users表返回用户ID值TmpID:'+Cast(@TmpID as varchar)
Print '-------------------------------------------'
--第二步:根据返回的TmpID,得到最新的消息。
if @TmpID>0
begin
--得到服务单号,得到最新的LastSChatID
Select @ServerNo=ServerNo, @LastSChatID=LastSChatID from ChatLink where ID=@ID
Print 'TmpID大于0'
Print '@ServerNo:'+Cast(@ServerNo as varchar)
Print '@LastSChatID:'+Cast(@LastSChatID as varchar)
Print '-------------------------------------------'
Select @NewSChatID=max([ID]) from Chat where ServerNo=@ServerNo and ID>@LastSChatID
Print '根据@ServerNo返回@NewSChatID值:'+Cast(@NewSChatID as varchar)
Print '-------------------------------------------'
--第三步:改写ChatLink表中的LastSChatID,返回消息。
if @NewSChatID>0
begin
Print '@NewSChatID大于0'
Select [UserID],[Time],[Msg],[Sayer],[Listener] from Chat where ServerNo=@ServerNo and ID>@LastSChatID and ID<=@NewSChatID order by id
if @@Rowcount>=1
Begin
Print '从聊天表中得到ID值大于'+Cast(@LastSChatID as varchar)+'小于等于'+Cast(@NewSChatID as varchar)+'的记录集'+Cast(@@Rowcount as varchar)+'条'
Update ChatLink Set LastSChatID=@NewSChatID where ID=@ID
Print '更新ChatLink表的LastSChatID值:'+Cast(@NewSChatID as varchar)
End
end
else
Begin
Print '@NewSChatID小于等于0,返回空记录集'
select 0 as [UserID],getdate() as [Time],'' as [Msg],'' as [Sayer],'' as [Listener]
End
end
else
begin
Print '@TmpID小于等于0,返回空记录集'
select 0 as [UserID],getdate() as [Time],'' as [Msg],'' as [Sayer],'' as [Listener]
endGO