IDC资讯中心
转载请注明出处
/****** 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, nistrigger) = 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, nisprocedure) = 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, nisusertable) = 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, nisusertable) = 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, nisusertable) = 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, nisusertable) = 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, nisusertable) = 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, nisusertable) = 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, nisusertable) = 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 nms_description, n服务号（移动2788，联通8788）, nuser, ndbo, ntable, nchat_user, ncolumn, nsrcphone
go
exec sp_addextendedproperty nms_description, n免费的服务类别, nuser, ndbo, ntable, nfree_phone, ncolumn, nfreesrvid
go
exec sp_addextendedproperty nms_description, n免费电话号码, nuser, ndbo, ntable, nfree_phone, ncolumn, nphonenumber
go
相关文章：http://blog.csdn.net/iuhxq/archive/2004/09/24/115990.aspx
