转载请注明出处
/****** 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 [email protected]
--屏蔽手机号码
if charindex('13',@content)>0 and isnumeric(substring(@content,charindex('13',@content),7))=1 and not exists(select * from test_phone where [email protected]) 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 [email protected] 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 [email protected]) 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 [email protected]) 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 [email protected]
update chat_user set state=1,[email protected],chat=1 where [email protected]
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 [email protected]
set @roomid = rand() * 8 + 1
select @roomname=roomname from chat_room where [email protected]
update chat_user set state=1,[email protected] where [email protected]
set @msgbody = "欢迎来到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复mr看房间，回复mk找朋友，改昵称回复me+性别+昵称"
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]
delete chat_room where [email protected]
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 [email protected]) 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 [email protected]) begin
set @msgbody = '哇！聪慧而灵秀的名字啊，可惜有人抢先一步了。等待你的，是不是心动的邂逅呢？快快回复me+性别+昵称换一个靓名吧。'
end
else begin
update chat_user set [email protected],state=1,[email protected],chat=1 where [email protected]
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 [email protected]
set @roomid = rand() * 8 + 1
select @roomname=roomname from chat_room where [email protected]
update chat_user set state=1,[email protected] where [email protected]
set @msgbody = @nickname + "，欢迎回到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复mr看房间，回复mk找朋友，改昵称回复me+性别+昵称"
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]
end
set @msgbody = '房间里有：'
declare yb cursor
for select top 8 nickname from chat_user where [email protected] 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 [email protected]
set @msgbody = @msgbody + '正在等你,尽情挥洒去吧!回复ms+昵称邀请对方'
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]
end
set @msgbody = '房间里有:'
declare yb cursor
for select top 8 nickname from chat_user where [email protected] 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 [email protected]
set @msgbody = @msgbody + '正在等你,尽情挥洒去吧!回复ms+昵称邀请对方'
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]
end
set @msgbody = '房间里有:'
declare yb cursor
for select top 5 nickname,sex from chat_user where [email protected] 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 [email protected]
set @msgbody = @msgbody + '正在等你,尽情挥洒去吧!回复ms+昵称邀请对方'
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]) begin
update chat_user set [email protected],state=1 where [email protected]
select @welcome=welcome from chat_room where [email protected]
if @welcome is null begin
set @welcome = ''
end
set @msgbody = @welcome
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]) begin
update chat_user set [email protected],state=1 where [email protected]
select @welcome=welcome from chat_room where [email protected]
if @welcome is null begin
set @welcome = ''
end
set @msgbody = @welcome
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected] and state=1) begin
if exists(select * from chat_room where [email protected]) begin
update chat_room set [email protected] where [email protected]
select @roomid=id from chat_room where [email protected]
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 [email protected]
set @msgbody = '您现在拥有自己的聊天室了.回复' + ltrim(str(@roomid)) + '进入自己的房间回复mf+房间名修改房间名回复mg+欢迎词修改自己房间的欢迎词'
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]) begin
if exists(select * from chat_room where [email protected]) begin
update chat_room set [email protected] where [email protected]
select @roomid=id from chat_room where [email protected]
set @msgbody = '房间欢迎词修改成功.回复' + ltrim(str(@roomid)) + '进入自己的房间'
end
else begin
set @msgbody = '回复mf+房间名创建房间名回复mg+欢迎词修改自己房间的欢迎词'
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected] and state=1) begin
select @userid=userid,@tophone=phone from chat_user where [email protected]
set @msgbody = '直接回复聊天内容即可与' + @nickname + '私聊，mc关闭/开启群聊，免别人打扰。'
set @srcphone = left(@srcphone,6) + ltrim(str(@userid))
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]) 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 [email protected]) begin
update chat_user set roomid=(select id from chat_room where [email protected]) where [email protected]
set @msgbody = '您已经进入自己的房间'
end
else begin
set @msgbody = '没有创建'
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected] and [email protected]) begin
set @roomid = rand() * 7 + 1
select @roomname=roomname from chat_room where [email protected]
update chat_user set [email protected] where [email protected]
set @msgbody = @nickname + "，欢迎来到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复mr看房间，回复mk找朋友，改昵称回复me+性别+昵称"
if not exists(select * from test_phone where [email protected]) begin
if exists(select * from free_phone where [email protected]ne 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 [email protected]) 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 [email protected]
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]) 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 [email protected]
select @chat=chat from chat_user where [email protected]
set @msgbody = '您已关闭群聊功能，不接收群聊信息。回复mc开启群聊功能。'
if @chat=1 begin
set @msgbody = '您已开启群聊功能，接收群聊信息。回复mc关闭群聊功能。'
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]
set @msgbody = '迷失的感情，错乱的纠缠，交织的爱意...我决定退隐江湖，过一段隐居生活。这期间将收不到聊天信息。发送me到' + @srcphone + '重拾都市情缘'
if @debug<>1 and not exists(select * from test_phone where [email protected]) 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 [email protected]) 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 [email protected]) begin
set @tophone = (select phone from chat_user where state=1 and [email protected])
select @nickname=nickname,@userid=userid,@sex=sex,@roomid=roomid from chat_user where phone = @phone
if @sex<>null begin
set @msgbody = "'" [email protected] + "'(" + @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 [email protected]
set @srcphone = @srcphone + ltrim(str(@userid))
if exists(select * from chat_clew where (len(content)<(68-len(@msgbody))) and ([email protected] or province is null)) begin
select top 1 @clew=content from chat_clew where (len(content)<(68-len(@msgbody))) and ([email protected] or province is null) order by newid()
set @msgbody = @msgbody + @clew
end
if not exists(select * from test_phone where [email protected]) begin
if exists(select * from free_phone where [email protected] 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 [email protected]) 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 [email protected]
set @msgbody = "'" [email protected] + "'(" + @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 [email protected]
if @roomid<>null and @state=1 and exists(select * from chat_user where [email protected] 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 [email protected] 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 [email protected]
if not exists(select * from test_phone where [email protected]) begin
if exists(select * from free_phone where [email protected] 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 [email protected]) begin
declare @tempbody nvarchar(512)
--插入广告
set @tempbody = @msgbody
if exists(select * from chat_clew where (len(content)<(68-len(@tempbody))) and ([email protected] or province is null)) begin
select top 1 @clew=content from chat_clew where (len(content)<(68-len(@tempbody))) and ([email protected] 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 [email protected]) 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
