转载请注明出处
/****** Object: Trigger dbo.update_room Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[update_room]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[update_room]
GO
/****** Object: Stored Procedure dbo.chat Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chat]
GO
/****** Object: Table [dbo].[AirLinkMT] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirLinkMT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AirLinkMT]
GO
/****** Object: Table [dbo].[chat_clew] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chat_clew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[chat_clew]
GO
/****** Object: Table [dbo].[chat_log] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chat_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[chat_log]
GO
/****** Object: Table [dbo].[chat_room] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chat_room]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[chat_room]
GO
/****** Object: Table [dbo].[chat_user] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chat_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[chat_user]
GO
/****** Object: Table [dbo].[FREE_PHONE] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FREE_PHONE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FREE_PHONE]
GO
/****** Object: Table [dbo].[test_phone] Script Date: 2004-9-27 8:18:44 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test_phone]
GO
/****** Object: Table [dbo].[AirLinkMT] Script Date: 2004-9-27 8:18:45 ******/
CREATE TABLE [dbo].[AirLinkMT] (
[OuQ_Id] [int] IDENTITY (1, 1) NOT NULL ,
[OuQ_Date] [datetime] NOT NULL ,
[MsgFmt] [int] NOT NULL ,
[Province] [smallint] NOT NULL ,
[Service] [int] NULL ,
[FeeCode] [nvarchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[MsgBody] [nvarchar] (1024) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DestPhone] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FeeSevID] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FeeType] [nchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SrcPhone] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FeePhone] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Priority] [tinyint] NOT NULL ,
[MsgCode] [tinyint] NOT NULL ,
[ReportFlag] [tinyint] NOT NULL ,
[MTType] [tinyint] NOT NULL ,
[LinkID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[chat_clew] Script Date: 2004-9-27 8:18:47 ******/
CREATE TABLE [dbo].[chat_clew] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[content] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[province] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[chat_log] Script Date: 2004-9-27 8:18:48 ******/
CREATE TABLE [dbo].[chat_log] (
[chat_id] [int] IDENTITY (1, 1) NOT NULL ,
[Phone] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[srcPhone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[msgBody] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
[roomid] [int] NULL ,
[sendTime] [datetime] NOT NULL ,
[tophone] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[chat_room] Script Date: 2004-9-27 8:18:49 ******/
CREATE TABLE [dbo].[chat_room] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Phone] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[roomname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[createtime] [datetime] NOT NULL ,
[online] [int] NOT NULL ,
[welcome] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS NULL ,
[intime] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[chat_user] Script Date: 2004-9-27 8:18:51 ******/
CREATE TABLE [dbo].[chat_user] (
[userid] [int] IDENTITY (10000, 1) NOT NULL ,
[phone] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[nickname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[srcPhone] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Province] [int] NOT NULL ,
[regtime] [datetime] NOT NULL ,
[intime] [datetime] NULL ,
[intime1] [datetime] NULL ,
[State] [int] NOT NULL ,
[roomid] [int] NULL ,
[sex] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[chat] [bit] NOT NULL ,
[fraction] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[FREE_PHONE] Script Date: 2004-9-27 8:18:53 ******/
CREATE TABLE [dbo].[FREE_PHONE] (
[PhoneNumber] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FreeSrvId] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[test_phone] Script Date: 2004-9-27 8:18:54 ******/
CREATE TABLE [dbo].[test_phone] (
[phone] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AirLinkMT] WITH NOCHECK ADD
CONSTRAINT [PK_AirLinkMT] PRIMARY KEY CLUSTERED
(
[OuQ_Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[chat_clew] WITH NOCHECK ADD
CONSTRAINT [PK_chat_clew] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[chat_log] WITH NOCHECK ADD
CONSTRAINT [PK_chat_log] PRIMARY KEY CLUSTERED
(
[chat_id] DESC
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[chat_room] WITH NOCHECK ADD
CONSTRAINT [PK_chat_room2] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[chat_user] WITH NOCHECK ADD
CONSTRAINT [PK_chat_user] PRIMARY KEY CLUSTERED
(
[userid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AirLinkMT] WITH NOCHECK ADD
CONSTRAINT [DF_AirLinkMT_OuQ_Date] DEFAULT (getdate()) FOR [OuQ_Date],
CONSTRAINT [DF_AirLinkMT_OuQ_SrcPhone] DEFAULT (8888) FOR [SrcPhone],
CONSTRAINT [DF_AirLinkMT_OuQ_Priority] DEFAULT (1) FOR [Priority],
CONSTRAINT [DF_AirLinkMT_OuQ_MsgCode] DEFAULT (0) FOR [MsgCode],
CONSTRAINT [DF_AirLinkMT_ReportFlag] DEFAULT (1) FOR [ReportFlag],
CONSTRAINT [DF_AirLinkMT_MTType] DEFAULT (2) FOR [MTType],
CONSTRAINT [DF_AirLinkMT_LinkID] DEFAULT (0) FOR [LinkID]
GO
ALTER TABLE [dbo].[chat_log] WITH NOCHECK ADD
CONSTRAINT [DF_chat_log_sendTime] DEFAULT (getdate()) FOR [sendTime]
GO
ALTER TABLE [dbo].[chat_room] WITH NOCHECK ADD
CONSTRAINT [DF_chat_room2_createtime] DEFAULT (getdate()) FOR [createtime],
CONSTRAINT [DF_chat_room_online] DEFAULT (0) FOR [online],
CONSTRAINT [DF_chat_room2_intime] DEFAULT (getdate()) FOR [intime]
GO
ALTER TABLE [dbo].[chat_user] WITH NOCHECK ADD
CONSTRAINT [DF_chat_user_intime] DEFAULT (getdate()) FOR [regtime],
CONSTRAINT [DF_chat_user_intime_1] DEFAULT (getdate()) FOR [intime],
CONSTRAINT [DF_chat_user_intime1] DEFAULT (getdate()) FOR [intime1],
CONSTRAINT [DF_chat_user_state] DEFAULT (0) FOR [State],
CONSTRAINT [DF_chat_user_chat] DEFAULT (0) FOR [chat],
CONSTRAINT [DF_chat_user_fraction] DEFAULT (0) FOR [fraction],
CONSTRAINT [IX_chat_user] UNIQUE NONCLUSTERED
(
[nickname]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.chat Script Date: 2004-9-27 8:18:56 ******/
CREATE proc [dbo].[chat]
--chat '13588144652','MY','278810','571','1'
@phone nvarchar(11),
@Content nvarchar(512),
@srcPhone nvarchar(20), --端口
@Province nvarchar(20), --省份
@debug int, --调试
@LinkID nvarchar(20)='0'
as
declare
@msgBody nvarchar(512), --发送消息
@roomid int, --房间ID
@roomname nvarchar(20), --房间名称
@online int, --在线人数
@state int, --用户状态
@userid int, --用户ID
@nickname nvarchar(20), --用户昵称
@tonickname nvarchar(20), --对方昵称
@sex nvarchar(2), --性别
@chat bit, --是否接受群聊
@clew nvarchar(512), --提示语
@tophone nvarchar(11), --接收号码
@welcome nvarchar(512), --欢迎词
@FeeCode int,
@FeeType int,
@FeeSevID nvarchar(20),
@MtTemp int
--错误检查
if @Content = '' or @Content is null or len(@phone)<>11 begin
return
end
--后面补上10
if len(@srcPhone)<6 begin
set @srcPhone = left(@srcPhone,4) + '10'
end
--3天没消息自动关闭群聊
update chat_user set chat=0 where chat=1 and datediff(d,intime,getdate())>2
--10天没消息自动离线
update chat_user set state=0,roomid=null where state=1 and datediff(d,intime,getdate())>5
--更新自己状态
update chat_user set intime=getdate(),intime1=getdate(),fraction=fraction+1 where phone=@phone
--屏蔽手机号码
if charindex('13',@Content)>0 and isnumeric(substring(@Content,charindex('13',@Content),7))=1 and not exists(select * from test_phone where phone=@phone) begin
return
end
--容错处理
if upper(left(@Content,1)) = 'M' begin
set @Content = replace(@Content,',','')
set @Content = replace(@Content,',','')
set @Content = replace(@Content,' ','')
set @Content = replace(@Content,'(','')
set @Content = replace(@Content,')','')
set @Content = replace(@Content,'.','')
set @Content = replace(@Content,'+','')
end
if exists(select * from free_phone where phonenumber=@phone and freesrvid='520LT') begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='2371' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'LTS'
end
else if @Province='2571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'YXG'
end
--未注册
if not exists(select * from chat_user where phone=@phone) and upper(@Content)<>'QXLT' begin
if upper(@Content) <> 'ME' and @Province = '571' begin
set @msgBody = '请回复ME完成注册'
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,'BZ',@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
return
end
if upper(left(@Content,2)) = 'ME' and len(@Content)>2 begin
set @nickname = substring (@Content,3,len(@Content))
set @sex = substring(@nickname,1,1)
if @sex not in ('男','女') begin
set @sex = '女'
end
else begin
set @nickname = substring (@nickname,2,len(@nickname))
end
if len(@nickname)>6 begin
set @msgBody = '嗨！这名字好长啊！不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME+性别+昵称完成注册。'
end
else if len(@nickname)<1 begin
set @msgBody = '嗨！这名字太短了吧？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要少于1个字。回复ME+性别+昵称完成注册。'
end
else if isnumeric(@nickname)=1 begin
set @msgBody = '嗨！怎么取数字名字呀？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME+性别+昵称完成注册。'
end
else if exists(select * from chat_user where nickname=@nickname) begin
set @msgBody = '哇！聪慧而灵秀的名字啊，可惜有人抢先一步了。等待你的，是不是心动的邂逅呢？快快回复ME+性别+昵称换一个靓名吧。'
end
else begin
insert into chat_user (phone,nickname,srcphone,province,sex) values(@phone,@nickname,left(@srcphone,6),@province,@sex)
set @roomid = rand() * 8 + 1
select @roomname=roomname from chat_room where id=@roomid
update chat_user set state=1,roomid=@roomid,chat=1 where phone=@phone
set @msgBody = @nickname + ",欢迎来到欲望都市之'" + @roomname + "'聊天室,回复想要说的话既可与大家聊天,回复MR看房间,回复MK找朋友,改昵称回复ME+性别+昵称"
end
end
else begin
select top 1 @userid=userid from chat_user order by userid desc
insert into chat_user (phone,nickname,srcphone,province,sex) values(@phone,ltrim(str(@userid+1)),left(@srcphone,6),@province,'女')
select @roomid=roomid,@nickname=nickname from chat_user where phone=@phone
set @roomid = rand() * 8 + 1
select @roomname=roomname from chat_room where id=@roomid
update chat_user set state=1,roomid=@roomid where phone=@phone
set @msgBody = "欢迎来到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友，改昵称回复ME+性别+昵称"
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into chat_log (phone,srcphone,msgbody,roomid) values(@phone,@srcPhone,@nickname+'来到聊天室',@roomid)
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
--已经注册
else begin
if upper(left(ltrim(@Content),4)) in ('QXLT') begin
set @srcPhone = left(@srcPhone,6)
delete chat_user where phone=@phone
delete chat_room where phone=@phone
if @Province = '2571' begin
set @msgBody = '夜凉如水，路上小心！愿今晚给你留下的不只是美好的回忆！记得常回来看看，发送520到8788重回都市情缘聊天室。'
end
else begin
set @msgBody = '夜凉如水，路上小心！愿今晚给你留下的不只是美好的回忆！记得常回来看看，发送ME到' + @srcPhone + '重回都市情缘聊天室。'
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @Province <> '571' begin--浙江移动不下发
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
return
end
--ME MK MR MC MQ MS
if upper(left(@Content,2)) = 'ME' begin
set @srcPhone = left(@srcPhone,6)
if len(@Content)>2 begin--改昵称
set @nickname = substring (@Content,3,len(@Content))
set @sex = substring(@nickname,1,1)
if @sex not in ('男','女') begin
set @sex = '女'
end
else begin
set @nickname = substring (@nickname,2,len(@nickname))
end
if len(@nickname)>6 begin
set @msgBody = '嗨！这名字好长啊！不要以为帅哥美女都是电脑哦~！想找到你的梦中情人，昵称不要超过6个字。回复ME+性别+昵称完成修改。'
end
else if len(@nickname)<1 begin
set @msgBody = '嗨！这名字太短了吧？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME+性别+昵称完成注册。'
end
else if isnumeric(@nickname)=1 begin
set @msgBody = '嗨！怎么取数字名字呀？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME+性别+昵称完成注册。'
end
else begin
if exists(select * from chat_user where phone<>@phone and nickname=@nickname) begin
set @msgBody = '哇！聪慧而灵秀的名字啊，可惜有人抢先一步了。等待你的，是不是心动的邂逅呢？快快回复ME+性别+昵称换一个靓名吧。'
end
else begin
update chat_user set nickname=@nickname,state=1,sex=@sex,chat=1 where phone=@phone
if @sex = '男' begin
set @msgBody = @nickname + '，哇，帅哥来了，让众美女眼前一亮！体验激情约会，突破就在今晚。回复MR聊天，寻找你的梦中情人。'
end
else begin
set @msgBody = @nickname + '，你好似轻云避月，飘飘然若流风之回雪。往事如烟，悠悠回想。回复MR聊天，感触都市情缘。'
end
end
end
end
else begin
select @roomid=roomid,@nickname=nickname from chat_user where phone=@phone
set @roomid = rand() * 8 + 1
select @roomname=roomname from chat_room where id=@roomid
update chat_user set state=1,roomid=@roomid where phone=@phone
set @msgBody = @nickname + "，欢迎回到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友，改昵称回复ME+性别+昵称"
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if upper(left(@Content,3)) = 'MKG' begin
set @Content = replace(@Content,'+','')
set @srcPhone = left(@srcPhone,6)
if isnumeric(substring(@Content,4,len(@Content))) = 1 begin
select @roomid = substring(@Content,4,len(@Content))
end
else begin
select @roomid=roomid from chat_user where phone=@phone
end
set @msgBody = '房间里有：'
declare yb cursor
for select top 8 nickname from chat_user where roomid=@roomid and fraction > 0 and phone<>@phone and sex = '男' order by isnumeric(nickname),newid()
open yb
fetch next from yb into @nickname
while (@@fetch_status=0)
begin
set @msgBody = @msgBody + @nickname +','
fetch next from yb into @nickname
end
close yb
deallocate yb
select @nickname=nickname from chat_user where phone=@phone
set @msgBody = @msgBody + '正在等你,尽情挥洒去吧!回复MS+昵称邀请对方'
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if upper(left(@Content,3)) = 'MKM' begin
set @Content = replace(@Content,'+','')
set @srcPhone = left(@srcPhone,6)
if isnumeric(substring(@Content,4,len(@Content))) = 1 begin
select @roomid = substring(@Content,4,len(@Content))
end
else begin
select @roomid=roomid from chat_user where phone=@phone
end
set @msgBody = '房间里有:'
declare yb cursor
for select top 8 nickname from chat_user where roomid=@roomid and fraction > 0 and phone<>@phone and sex = '女' order by isnumeric(nickname),newid()
open yb
fetch next from yb into @nickname
while (@@fetch_status=0)
begin
set @msgBody = @msgBody + @nickname +','
fetch next from yb into @nickname
end
close yb
deallocate yb
select @nickname=nickname from chat_user where phone=@phone
set @msgBody = @msgBody + '正在等你,尽情挥洒去吧!回复MS+昵称邀请对方'
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if upper(left(@Content,2)) = 'MK' begin
set @Content = replace(@Content,'+','')
set @srcPhone = left(@srcPhone,6)
if isnumeric(substring(@Content,3,len(@Content))) = 1 begin
select @roomid = substring(@Content,3,len(@Content))
end
else begin
select @roomid=roomid from chat_user where phone=@phone
end
set @msgBody = '房间里有:'
declare yb cursor
for select top 5 nickname,sex from chat_user where roomid=@roomid and fraction > 0 and phone<>@phone order by isnumeric(nickname),newid()
open yb
fetch next from yb into @nickname,@sex
while (@@fetch_status=0)
begin
set @msgBody = @msgBody + @nickname + '(' + @sex +'),'
fetch next from yb into @nickname,@sex
end
close yb
deallocate yb
select @nickname=nickname from chat_user where phone=@phone
set @msgBody = @msgBody + '正在等你,尽情挥洒去吧!回复MS+昵称邀请对方'
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if @Content in ('1','2','3','4','5','6','7','8','9') begin--进入系统默认聊天室
set @srcPhone = left(@srcPhone,6)
if exists(select * from chat_room where id=@Content) begin
update chat_user set roomid=@Content,state=1 where phone=@phone
select @welcome=welcome from chat_room where id=@Content
if @welcome is null begin
set @welcome = ''
end
set @msgBody = @welcome
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else if isnumeric(@Content) = 1 and @Content not in ('1','2','3','4','5','6','7','8','9') and len(@Content) < 4 begin --进入自建聊天室
set @srcPhone = left(@srcPhone,6)
if exists(select * from chat_room where id=@Content) begin
update chat_user set roomid=@Content,state=1 where phone=@phone
select @welcome=welcome from chat_room where id=@Content
if @welcome is null begin
set @welcome = ''
end
set @msgBody = @welcome
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else if upper(left(@Content,2)) = 'MF' begin
set @srcPhone = left(@srcPhone,6)
set @roomname = left(substring(@Content,3,len(@Content)),12)
if exists(select * from chat_user where phone=@phone and state=1) begin
if exists(select * from chat_room where phone=@phone) begin
update chat_room set roomname=@roomname where phone=@phone
select @roomid=id from chat_room where phone=@phone
set @msgBody = '聊天室名称修改成功.回复' + ltrim(str(@roomid)) + '进入自己的房间回复MG+欢迎词修改自己房间的欢迎词'
end
else begin
insert into chat_room (phone,roomname) values(@phone,@roomname)
select @roomid=id from chat_room where phone=@phone
set @msgBody = '您现在拥有自己的聊天室了.回复' + ltrim(str(@roomid)) + '进入自己的房间回复MF+房间名修改房间名回复MG+欢迎词修改自己房间的欢迎词'
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else if upper(left(@Content,2)) = 'MG' begin
set @Content = replace(@Content,'+','')
set @srcPhone = left(@srcPhone,6)
set @welcome = left(substring(@Content,3,len(@Content)),65)
if exists(select * from chat_user where phone=@phone) begin
if exists(select * from chat_room where phone=@phone) begin
update chat_room set welcome=@welcome where phone=@phone
select @roomid=id from chat_room where phone=@phone
set @msgBody = '房间欢迎词修改成功.回复' + ltrim(str(@roomid)) + '进入自己的房间'
end
else begin
set @msgBody = '回复MF+房间名创建房间名回复MG+欢迎词修改自己房间的欢迎词'
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else if upper(left(@Content,2)) = 'MS' begin
set @Content = replace(@Content,'+','')
set @srcPhone = left(@srcPhone,6)
set @nickname = substring(@Content,3,len(@Content))
if exists(select * from chat_user where nickname=@nickname and state=1) begin
select @userid=userid,@tophone=phone from chat_user where nickname=@nickname
set @msgBody = '直接回复聊天内容即可与' + @nickname + '私聊，MC关闭/开启群聊，免别人打扰。'
set @SrcPhone = left(@SrcPhone,6) + ltrim(str(@userid))
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else if upper(@Content) = 'MR' begin
set @srcPhone = left(@srcPhone,6)
set @msgBody = ''
declare yb cursor
for select top 5 id,roomname,online from chat_room order by online desc
open yb
fetch next from yb into @roomid,@roomname,@online
while (@@fetch_status=0)
begin
set @msgBody = @msgBody + ltrim(str(@roomid))+ '.' + @roomname + '(' + ltrim(str(@online)) + '人)' + char(13)
fetch next from yb into @roomid,@roomname,@online
end
close yb
deallocate yb
set @msgBody = @msgBody + '回复房间编号进入.'
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if upper(@Content) = 'MY' begin
set @srcPhone = left(@srcPhone,6)
if exists(select * from chat_room where phone=@phone) begin
update chat_user set roomid=(select id from chat_room where phone=@phone) where phone=@phone
set @msgBody = '您已经进入自己的房间'
end
else begin
set @msgBody = '没有创建'
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if upper(left(@Content,2)) = 'MT' begin
set @Content = replace(@Content,'+','')
if exists(select * from chat_room where phone = @phone) begin
if isnumeric(substring(@Content,3,len(@Content))) = 1 and exists(select * from chat_user where userid = substring(@Content,3,len(@Content)))begin
select @userid=userid,@SrcPhone=srcphone,@province=province,@tophone=phone,@nickname=nickname,@roomid=roomid from chat_user where userid=substring(@Content,3,len(@Content))
end
else if exists(select * from chat_user where nickname = substring(@Content,3,len(@Content))) begin
select @userid=userid,@SrcPhone=srcphone,@province=province,@tophone=phone,@nickname=nickname,@roomid=roomid from chat_user where nickname=substring(@Content,3,len(@Content))
end
if exists(select * from chat_room where id=@roomid and phone=@phone) begin
set @roomid = rand() * 7 + 1
select @roomname=roomname from chat_room where id=@roomid
update chat_user set roomid=@roomid where phone=@tophone
set @msgBody = @nickname + "，欢迎来到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友，改昵称回复ME+性别+昵称"
if not exists(select * from test_phone where phone=@tophone) begin
if exists(select * from free_phone where phonenumber=@tophone and freesrvid='520LT') begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='2371' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'LTS'
end
else if @Province='2571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'YXG'
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
end
end
end
else begin
set @msgBody = '您的房间里有这个人吗？我怎么没有找到呢？'
select @SrcPhone=srcphone,@province=province from chat_user where phone=@phone
if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @msgBody is not null begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else begin
set @msgBody = '您还没有创建自己的房间呢，怎么就踢别人啊？赶快回复MF+房间名称创建自己的房间吧！体验一下自己做管理员的滋味！！'
if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @msgBody is not null begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
else if upper(left(@Content,2)) = 'MC' or left(@Content,4) = '0000' begin
update chat_user set chat=1^chat where phone=@phone
select @chat=chat from chat_user where phone=@phone
set @msgBody = '您已关闭群聊功能，不接收群聊信息。回复MC开启群聊功能。'
if @chat=1 begin
set @msgBody = '您已开启群聊功能，接收群聊信息。回复MC关闭群聊功能。'
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
else if upper(left(@Content,2)) = 'MQ' begin--离开
set @srcPhone = left(@srcPhone,6)
update chat_user set state=0,roomid=null where phone=@phone
set @msgBody = '迷失的感情，错乱的纠缠，交织的爱意...我决定退隐江湖，过一段隐居生活。这期间将收不到聊天信息。发送ME到' + @srcPhone + '重拾都市情缘'
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
/*
--查看用户信息
else if upper(left(@Content,2)) = 'MM' begin
set @msgBody = null
if isnumeric(substring(@Content,3,len(@Content))) = 1 begin
select @msgBody = phone from chat_user where userid=substring(@Content,3,len(@Content))
end
else begin
select @msgBody = phone from chat_user where nickname=substring(@Content,3,len(@Content))
end
if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @msgBody is not null begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
*/
else if len(@SrcPhone)>6 begin--私聊
set @userid = substring(@SrcPhone,6,len(@SrcPhone))
if exists(select * from chat_user where state=1 and userid=@userid) begin
set @tophone = (select phone from chat_user where state=1 and userid=@userid)
select @nickname=nickname,@userid=userid,@sex=sex,@roomid=roomid from chat_user where phone = @phone
if @sex<>null begin
set @msgBody = "'" +@nickname + "'(" + @sex + ')对你说:' + @Content
end
else begin
set @msgBody = "'" + @nickname + "'悄悄的对你说:" + @Content
end
set @srcPhone = left(@srcphone,6) + ltrim(str(@userid))
if len(@msgBody)>0 begin
select @srcphone=srcphone,@province=province from chat_user where phone=@tophone
set @SrcPhone = @srcPhone + ltrim(str(@userid))
if exists(select * from chat_clew where (len(content)<(68-len(@msgBody))) and (province=@province or province is null)) begin
select top 1 @clew=content from chat_clew where (len(content)<(68-len(@msgBody))) and (province=@province or province is null) order by newid()
set @msgBody = @msgBody + @clew
end
if not exists(select * from test_phone where phone=@tophone) begin
if exists(select * from free_phone where phonenumber=@tophone and freesrvid='520LT') begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='2371' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'LTS'
end
else if @Province='2571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'YXG'
end
if @debug<>1 and not exists(select * from test_phone where phone=@tophone) begin
while (len( @msgBody ) > 0)
begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,left(@msgBody,70),@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
set @msgBody = substring( @msgBody ,71,len(@msgBody))
end
end
end
select @tonickname = nickname from chat_user where phone=@tophone
set @msgBody = "'" +@nickname + "'(" + @sex + ")对'" + @tonickname + "'说:" + @Content
if @msgBody <> null begin
insert into chat_log (phone,srcphone,msgbody,roomid,tophone) values(@phone,@srcPhone,@msgBody,@roomid,@tophone)
end
end
end
end
else begin
select @roomid=roomid,@nickname=nickname,@sex=sex,@state=state from chat_user where phone=@phone
if @roomid<>null and @state=1 and exists(select * from chat_user where roomid=@roomid and phone<>@phone) begin
set @msgBody = + '"' + @nickname + '(' + @sex + ')"对大家说:' + @Content
if @msgBody <> null begin
insert into chat_log (phone,srcphone,msgbody,roomid) values(@phone,@srcPhone,@msgBody,@roomid)
end
declare yb cursor
for select phone from chat_user where roomid=@roomid and phone<>@phone and state=1 and chat=1
open yb
fetch next from yb into @tophone
while (@@fetch_status=0)
begin
select @srcphone=srcphone,@province=province from chat_user where phone=@tophone
if not exists(select * from test_phone where phone=@tophone) begin
if exists(select * from free_phone where phonenumber=@tophone and freesrvid='520LT') begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='2371' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'LTS'
end
else if @Province='2571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520LT'
end
else if @Province='571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'YXG'
end
if @debug<>1 and not exists(select * from test_phone where phone=@tophone) begin
declare @tempBody nvarchar(512)
--插入广告
set @tempBody = @msgBody
if exists(select * from chat_clew where (len(content)<(68-len(@tempBody))) and (province=@province or province is null)) begin
select top 1 @clew=content from chat_clew where (len(content)<(68-len(@tempBody))) and (province=@province or province is null) order by newid()
set @tempBody = @tempBody + @clew
end
while (len( @tempBody ) > 0)
begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,left(@tempBody,70),@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
set @tempBody = substring( @tempBody ,71,len(@tempBody))
end
end
end
fetch next from yb into @tophone
end
close yb
deallocate yb
end
if @roomid=null begin
set @msgBody = '回复编号进入房间' + char(13)
declare yb cursor
for select top 4 id,roomname,online from chat_room order by newid()
open yb
fetch next from yb into @roomid,@roomname,@online
while (@@fetch_status=0)
begin
set @msgBody = @msgBody + ltrim(str(@roomid))+ '.' + @roomname + '(' + ltrim(str(@online)) + '人)' + char(13)
fetch next from yb into @roomid,@roomname,@online
end
close yb
deallocate yb
if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
end
end
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Trigger dbo.update_room Script Date: 2004-9-27 8:18:56 ******/
CREATE TRIGGER [update_room] ON [dbo].[chat_user]
FOR INSERT, UPDATE, DELETE
AS
update r set r.online=(select count(chat_user.phone) from chat_user where chat_user.roomid=r.id group by chat_user.roomid) from chat_room r,chat_user u where r.id=u.roomid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec sp_addextendedproperty N'MS_Description', N'服务号（移动2788，联通8788）', N'user', N'dbo', N'table', N'chat_user', N'column', N'srcPhone'
GO
exec sp_addextendedproperty N'MS_Description', N'免费的服务类别', N'user', N'dbo', N'table', N'FREE_PHONE', N'column', N'FreeSrvId'
GO
exec sp_addextendedproperty N'MS_Description', N'免费电话号码', N'user', N'dbo', N'table', N'FREE_PHONE', N'column', N'PhoneNumber'
GO
相关文章：http://blog.csdn.net/iuhxq/archive/2004/09/24/115990.aspx
本文地址：http://com.8s8s.com/it/it20434.htm