轉載請注明出處
/****** 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), --發(fā)送消息
@roomid int, --房間id
@roomname nvarchar(20), --房間名稱
@online int, --在線人數(shù)
@state int, --用戶狀態(tài)
@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
--更新自己狀態(tài)
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 = '嗨！怎么取數(shù)字名字呀？不要以為帥哥美女都是電腦哦~！想抓住屬于你的情緣，昵稱不要超過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
--已經(jīng)注冊
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 = '夜涼如水，路上小心！愿今晚給你留下的不只是美好的回憶！記得常回來看看，發(fā)送520到8788重回都市情緣聊天室。'
end
else begin
set @msgbody = '夜涼如水，路上小心！愿今晚給你留下的不只是美好的回憶！記得?；貋砜纯矗l(fā)送me到' + @srcphone + '重回都市情緣聊天室。'
end
if @debug<>1 and not exists(select * from test_phone where [email protected]) and @province <> '571' begin--浙江移動不下發(fā)
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個字?；貜蚼e+性別+昵稱完成修改。'
end
else if len(@nickname)<1 begin
set @msgbody = '嗨！這名字太短了吧？不要以為帥哥美女都是電腦哦~！想抓住屬于你的情緣，昵稱不要超過6個字?；貜蚼e+性別+昵稱完成注冊。'
end
else if isnumeric(@nickname)=1 begin
set @msgbody = '嗨！怎么取數(shù)字名字呀？不要以為帥哥美女都是電腦哦~！想抓住屬于你的情緣，昵稱不要超過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 + '，你好似輕云避月，飄飄然若流風之回雪。往事如煙，悠悠回想?；貜蚼r聊天，感觸都市情緣。'
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--進入系統(tǒng)默認聊天室
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 = '您現(xiàn)在擁有自己的聊天室了.回復' + 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+房間名創(chuàng)建房間名回復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 = '您已經(jīng)進入自己的房間'
end
else begin
set @msgbody = '沒有創(chuàng)建'
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 = '您還沒有創(chuàng)建自己的房間呢，怎么就踢別人??？趕快回復mf+房間名稱創(chuàng)建自己的房間吧！體驗一下自己做管理員的滋味??！'
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 = '您已關閉群聊功能，不接收群聊信息?；貜蚼c開啟群聊功能。'
if @chat=1 begin
set @msgbody = '您已開啟群聊功能，接收群聊信息?；貜蚼c關閉群聊功能。'
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 = '迷失的感情，錯亂的糾纏，交織的愛意...我決定退隱江湖，過一段隱居生活。這期間將收不到聊天信息。發(fā)送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，聯(lián)通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
