/ ****** Object: Database SMS Script Date: 2005-2-25 12:35:12 ****** / if EXISTS (Select Name from Master.dbo.sysDatabases Where Name = N'sms') DROP DATABASE [SMS] Go
Create Database [SMS] ON (Name = n'sms_data ', filename = n'd: / program files / microsoft sql server / mssql / data / sms_data.mdf', size = 749, filegrowth = 10%) log on (Name = N'sms_log ', filename = n'd: / program files / microsoft SQL Server / MSSQL / DATA / SMS_LOG.LDF', SIZE = 34, FileGrowTH = 10%) Collate Chinese_PRC_CI_ASGO
Exec sp_dboption n'sms ', n'autoclose', N'false'go
EXEC SP_DBOPTION N'SMS ', N'BULKCOPY', N'FALSE'GO
Exec sp_dboption n'sms ', n'trunc. log', n'true'go
EXEC SP_DBOPTION N'SMS ', N'TORN Page Detection', N'true'go
Exec sp_dboption n'sms ', n'read only', n'false'go
EXEC SP_DBOPTION N'SMS ', N'DBO USE', N'FALSe'go
Exec sp_dboption n'sms ', n'single', n'false'go
Exec sp_dboption n'sms ', N'Autoshrink', N'False'go
Exec sp_dboption n'sms ', n'ansi null default', N'false'go
Exec sp_dboption n'sms ', N'Recursive Triggers', N'False'go
Exec sp_dboption n'sms ', n'ansi nulls', n'false'go
Exec sp_dboption n'sms ', n'concat null yields null', N'False'go
EXEC SP_DBOPTION N'SMS ', N'Cursor Close On Commit', N'False'go
Exec sp_dboption n'sms ', N'Default to Local Cursor', N'False'go
Exec sp_dboption n'sms ', n'quoted identifier', n'false'goexec sp_dboption n'sms ', n'si warnings', N'false'go
EXEC SP_DBOPTION N'SMS ', N'AUTO CREATE STATISTICS', N'TRUE'GO
Exec sp_dboption n'sms ', N'RUTO UPDATE STATISTISTICS', N'TRUE'GO
Use [SMS] Go
/ ****** Object: Trigger dbo.update_room script date: 2005-2-25 12:35:17 ****** / if exists (select * from dbo.sysObjects where id = Object_id (n ') DBO]. [update_room] ') And ObjectProperty (ID, N'ISTRIGER') = 1) DROP TRIGGER [DBO]. [Update_room] GO
/ ****** Object: Stored Procedure DBO.CHAT Script Date: 2005-2-25 12:35:17 ****** / 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: Stored Procedure DBo.Bh Script Date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from DBO.SYSOBJECTS WHERE ID = Object_ID (N ' [dbo]. [bh] ') And ObjectProperty (ID, n'isprocedure') = 1) Drop Procedure [dbo]. [bh] GO
/ ****** Object: Stored Procedure DBO.MW Script Date: 2005-2-25 12:35:17 ****** / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n ' [dbo] ') And ObjectProperty (id, n'isprocedure') = 1) Drop Procedure [dbo]. [MW] Go
/ ****** Object: stored procedure dbo.fun_sevid script date: 2005-2-25 12:35:17 ****** / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n ' [dbo]. [fun_sevid] ') And ObjectProperty (ID, n'isprocedure') = 1) Drop Procedure [DBO]. [Fun_Sevid] Go
/ ****** Object: Stored Procedure DBO.TEST Script Date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from DBO.SYSOBJECTS WHERE ID = Object_ID (N ' [DBO]. [TEST] ') And ObjectProperty (ID, N'isprocedure') = 1) DROP Procedure [DBO]. [TEST] Go / ****** Object: Table [DBO]. [AirLinkmt] Script Date: 2005-2-25 12:35:17 ****** / 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]. [Birthday] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysobjects where id = Object_id (N '[dbo]. [Birthday]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [Birthday] GO
/ ****** Object: Table [DBO]. [Chat_clew] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysobjects where id = Object_id (N '[dbo]. [Chat_clew]') And ObjectProperty (ID, n'susertable ') = 1) Drop Table [dbo]. [Chat_clew] Go
/ ****** Object: table [dbo]. [Chat_friend] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from DBO.SYSOBJECTS WHERE ID = Object_ID (N '[dbo]. [Chat_friend]') And ObjectProperty (ID, n'susertable ') = 1) Drop Table [dbo]. [Chat_friend] Go
/ ****** Object: table [dbo]. [Chat_log] script date: 2005-2-25 12:35:17 ****** / if EXISTS (Select * from dbo.sysObjects where id = Object_id (N '[dbo]. [Chat_log]') And ObjectProperty (ID, n'susertable ') = 1) Drop Table [dbo]. [Chat_log] GO
/ ****** Object: table [dbo]. [Chat_room] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysObjects where id = Object_id (N '[dboom]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [Chat_room] go / ****** Object: Table [dbo]. Chat_user] script date: 2005-2-25 12:35:17 ****** / 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]. [Cs] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysObjects where id = Object_id (N '[DBO]. [Cs]') And ObjectProperty (ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [CS] GO
/ ****** Object: Table [DBO]. [Free_phone] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysObjects where id = Object_id (N '[DBO]. [Free_phone]') And ObjectProperty (ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [Free_phone] Go
/ ****** Object: table [dbo]. [Test_phone] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysobjects where id = Object_id (N '[dbo]. [Test_Phone]') And ObjectProperty (ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [TEST_PHONE] GO
/ ****** Object: table [dbo]. [User_2571] script date: 2005-2-25 12:35:17 ****** / if EXISTS (Select * from dbo.sysObjects where id = Object_ID (N '[DBO]. [User_2571]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [User_2571] GO
/ ****** Object: table [dbo]. [User_571] script date: 2005-2-25 12:35:17 ****** / if exists (select * from dbo.sysObjects where id = Object_id (N '[DBO]. [User_571]') And ObjectProperty (ID, N'ISUSERTABLE ') = 1) DROP TABLE [DBO]. [User_571] go / ****** Object: Table [DBO]. Weather] script date: 2005-2-25 12:35:17 ****** / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]') And ObjectProperty ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [Weather] Go
/ ****** Object: table [dbo]. [Xh] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysobjects where id = Object_id (N '[DBO]. [XH]') And ObjectProperty (ID, N'ISUSERTABLE ') = 1) Drop Table [DBO]. [XH] GO
/ ****** Object: table [dbo]. [Xz] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysObjects where id = Object_id (N '[dbo]. [Xz]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [Xz] GO
/ ****** Object: table [dbo]. [Xz_user] script date: 2005-2-25 12:35:17 ****** / if EXISTS (SELECT * from dbo.sysobjects where id = Object_ID (N '[dbo]. [Xz_user]') And ObjectProperty (ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [XZ_USER] GO
/ ****** Object: Table [DBO]. [AirLinkmt] Script date: 2005-2-25 12:35:21 ****** / Create Table [DBO]. [AirLinkmt] ([OQ_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]. [Birthday] script date: 2005-2-25 12:35:23 ****** / CR Eate Table [DBO]. [Birthday] ([Content] [VARCHAR] (456) Collate Chinese_PRC_CI_AS NULL, [BH] [NVARCHAR] (4) Collate Chinese_PRC_CI_AS NULL) ON [PRIMARY] GO
/ ****** Object: table [dbo]. [Chat_clew] script date: 2005-2-25 12:35:24 ****** / 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_friend] script date: 2005-2-25 12:35:25 ****** / CREATE TABLE [DBO]. [Chat_friend] ([ID] [INT] Identity (1, 1) Not NULL, [Userid] [INT] NOT NULL, [FriendId] [INT] NOT NULL, [FLAG] [INT] NOT NULL) ON [PRIMARY] GO
/ ****** Object: Table [DBO]. [Chat_log] script date: 2005-2-25 12:35:26 ****** / 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: 2005-2-25 12:35:28 ****** / 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: 2005-2-25 12:35:29 ****** / CREATE TABLE [DBO]. [Chat_user] ([userid] [int] NULL, [MTCODE] [INT] 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, [Provination] [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, [Page] [INT] NOT NULL) ON [PRIMARY] GO
/ ****** Object: table [dbo]. [Cs] script date: 2005-2-25 12:35:31 ****** / CREATE TABLE [DBO]. [Cs] ([ID] [INT] Identity (1, 1) Not NULL, [CS] [NVARCHAR] (50) Collate Chinese_PRC_CI_AS NULL, [CS1] [NVARCHAR] (50) Collate Chinese_PRC_CI_AS NULL) ON [PRIMARY] GO / ****** Object: table [dbo]. [Free_phone] script date: 2005-2-25 12:35:32 ****** / 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: 2005-2-25 12:35:34 ****** / CREATE TABLE [DBO]. [TEST_PHONE] ([Phone] [nvarchar] (11) collate chinese_prc_ci_as not null) ON [primary] Go
/ ****** Object: table [dbo]. [User_2571] script date: 2005-2-25 12:35:35 ****** / CREATE TABLE [DBO]. [USER_2571] ([Phone] [nvarchar] (11) collate chinese_prc_ci_as not null, [serviceid] [nvarchar] (10) collate chinese_prc_ci_as null, [INTime] [smallDatetime] not null) on [primary] GO
/ ****** Object: table [dbo]. [User_571] script date: 2005-2-25 12:35:36 ****** / Create Table [DBO]. [User_571] ([phone] [nvarchar] (11) COLLATE Chinese_PRC_CI_AS NOT NULL, [ServiceId] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL, [intime] [smalldatetime] NOT NULL, [state] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] Go
/ ****** Object: table [dbo]. [Weather] script date: 2005-2-25 12:35:37 ****** / CREATE TABLE [DBO]. [Weather] ([Content] [varchar] (512) Collate chinese_prc_ci_as null, [province] [nvarchar] (50) collate chinese_prc_ci_as null) on [primary] GO
/ ****** Object: table [dbo]. [Xh] script date: 2005-2-25 12:35:38 ****** / CREATE TABLE [DBO]. [XH] ([ID] [int] Identity (1, 1) not null, [content] [nvarchar] (512) collate chinese_prc_ci_as null, [xtype] [nvarcha] (50) collate chinese_prc_ci_as null, [id1] [int] null) on [primary] GO / ****** Object: table [dbo]. [Xz] script date: 2005-2-25 12:35:39 ****** / CREATE TABLE [DBO]. [Xz] ([ID] ] [INT] Identity (1, 1) Not NULL, [Id1] [INT] NOT NULL, [Content] [NVARCHAR] (3500) Collate Chinese_PRC_CI_AS NOT NULL, [NVARCHAR] [NVARCHAR] (50) Collate Chinese_PRC_CI_AS NULL) ON [Primary] Go
/ ****** Object: table [dbo]. [Xz_user] script date: 2005-2-25 12:35:41 ****** / CREATE TABLE [DBO]. [Xz_user] ([ID] [int] Identity (1, 1) Not null, [phone] [nvarchar] (11) collate chinese_prc_ci_as not null, [srcphone] [nvarchar] (10) collate chinese_prc_ci_as not null, [province] [nvarchar] (4) Collate CHINESE_PRC_CI_AS NOT NULL, [Id1] [INT] NOT NULL, [STATUS] [NVARCHAR] (50) Collate Chinese_PRC_CI_AS NULL) ON [PRIMARY] GO
Alter Table [DBO]. [AirLinkmt] with nocheck address [pk_airlinkmt] primary key clustered ([uq_id]) on [primary] Go
Alter Table [DBO]. [Chat_clew] with nocheck address [pk_chat_clew] primary key clustered ([ID]) on [primary] Go
Alter Table [DBO]. [Chat_friend] with nocheck add constraint [pk_friend] Primary Key Clustered ([ID]) on [primary] GO
Alter Table [DBO]. [Chat_log] with nocheck address [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] GOALTER TABLE [dbo]. [Cs] WITH NOCHECK ADD CONSTRAINT [PK_cs] PRIMARY KEY CLUSTERED ([id ]) ON [PRIMARY] GO
Alter Table [DBO]. [Xz] with nocheck add constraint [pk_xz] primary key clustered ([ID]) on [primary] GO
Alter Table [DBO]. [Xz_user] with nocheck add constraint [pk_xz_user] Primary Key Clustered ([ID]) 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_friend] with nocheck add constraint [df_Friend_Flag] default (0) for [flag] 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 [DF_chat_user_page] DEFAULT (0) FOR [page], CONSTRAINT [IX_chat_user] UNIQUE NONCLUSTERED ([nickname]) ON [PRIMARY] GOALTER TABLE [dbo]. [user_2571] WITH NOCHECK ADD CONSTRAINT [DF_user_2571_intime] DEFAULT (getdate ()) FOR [intime] Go
Alter Table [DBO]. [User_571] with nocheck add constraint [df_sccerr_intime] default (getdate ()) for [Intime], consTRAINT [DF_USER_571_STATE_1] Default ('a') for [State] Go
ALTER TABLE [DBO]. [Xz] with nocheck add constraint [df_xz_id1] default (0) for [id1], consT [DF_XZ_CONTENT] default ('') for [content] Go
ALTER TABLE [dbo]. [Xz_user] WITH NOCHECK ADD CONSTRAINT [DF_xz_user_srcphone] DEFAULT (278810) FOR [srcphone], CONSTRAINT [DF_xz_user_province] DEFAULT (571) FOR [province], CONSTRAINT [DF_xz_user_id1] DEFAULT (0) FOR [id1] Go
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ ****** Object: stored procedure dbo.fun_sevid script date: 2005-2-25 12:35:42 ****** / CREATE PROC [DBO]. [Function] @Phone NVARCHAR (11), @Province nvarchar (4), @ FeeCode int output, @ FeeType int output, @ FeeSevID nvarchar (20) outputasif exists (select * from free_phone where phonenumber = @ phone) begin set @FeeCode = 0 set @FeeType = 1 set @FeeSevID = '520500'endelse if @ province =' 2371 'begin set @feecode = 0 set @feeetype = 1 set @FeeeEthPE =' lts'endelse if @ province = '2571' begin set @feecode = 0 set @feetype = 1 set @Feesevid = '520500'endelse if @ province =' 571 'begin set @Feecode = 0 set @feeeType = 1 set @Feesevid =' yxg'endgoset quoted_identifier off goset Ansi_nulls on Go
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ ****** Object: stored procedure dbo.test script date: 2005-2-25 12:35:43 ****** / CREATE PROC [DBO]. [Test] @Phone NVARCHAR (11), @action nvarchar (10) = 'add'asif not exists (select top 1 * from test_phone where phone = @ phone) and len (@phone) = 11 begin insert into test_phone values (@phone) endelse begin delete from test_phone where phone = @ phoneendgo
Set quoted_identifier off goset ANSI_NULLS ON GO
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ ****** Object: Stored Procedure DBo.Bh Script Date: 2005-2-25 12:35:43 ****** / CREATE PROC [DBO]. [BH]
@Phone nvarchar (11), @content nvarchar (512), @ srcphone nvarchar (20), @PROVINCE NVARCHAR (20), @ Debug INT = 0, @ linkid nvarchar (20) = '0'ASDeclare @ msgbody nvarchar (512 ), @ nickname nvarchar (512), @ nickname nvarchar (20), @ roomID int, @ i int, @ id, @ sr, @ feecode int, @ feetype int, @feesevid nvarchar (20), @ mtTemp INT
set @ srcphone = left (@ srcphone, 6) ltrim (str ( '2')) exec fun_sevid @ phone, @ province, @ FeeCode output, @ FeeType output, @ FeeSevID output if isnumeric (@content) = 1 begin select @ msgbody = content from birthday where bh = @ concegbody = 'Please reply to your birthday. For example: You are from May 6th to reply 0506 end
select @nickname = nickname, @ roomid = roomid from chat_user where phone = @ phone insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, @ nickname ':' @ msgBody, @ roomid)
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 (), @, @, @ Msgbody, 70), @ phone, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) set @msgbody = Substring (@msgbody, 71, len (@msgbody)) endGo
Set quoted_identifier off goset ANSI_NULLS ON GO
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ ****** Object: Stored Procedure DBO.MW Script Date: 2005-2-25 12:35:43 ****** / CREATE PROC [DBO]. [MW]
@Phone nvarchar (11), @content nvarchar (512), @ srcphone nvarchar (20), @PROVINCE NVARCHAR (20), @ Debug INT = 0, @ linkid nvarchar (20) = '0'ASDeclare @ msgbody nvarchar (512 ), @ nickname nvarchar (512), @ nickname nvarchar (20), @ roomID int, @ i int, @ id, @ sr, @ feecode int, @ feetype int, @feesevid nvarchar (20), @ mtTemp INT
set @ i = 1exec fun_sevid @ phone, @ province, @ FeeCode output, @ FeeType output, @ FeeSevID outputset @ srcphone = left (@ srcphone, 6) ltrim (str ( '1')) if not exists (select * from xz_user where phone = @ phone) begin insert into xz_user (phone, srcphone, province) values (@ phone, @ srcphone, @ province) endif isnumeric (@Content) = 1 begin if len (@Content) = 8 begin set @sr = (Substring (@ Content, 5, 4)) IF @SR> = 121 and @SR <= 218 begin set @ i = 12 end else if @SR> = 219 and @SR <= 320 begin set @ i = 13 Else if @SR> = 321 and @SR <= 420 begin set @ i = 2 end else if @SR> = 421 and @SR <= 521 begin set @ i = 3 end else @SR> = 522 and @ SR <= 621 begin set @ i = 4 end else if @SR> = 622 and @SR <= 722 begin set @ i = 5 end else if @SR> = 723 and @SR <= 823 begin set @ i = 6 Else if @SR> = 824 and @SR <= 923 begin set @ i = 7 end else if @SR> = 924 and @SR <= 1023 begin set @ i = 8 end else if @SR> = 1024 and @ SR <= 1122 Begin Set @ i = 8 END ELSE IF @SR> = 1123 and @SR <= 1221 Begin Set @ i = 10 END ELSE IF @SR> = 1222 or @SR <= 120 Begin S et @ i = 11 end select @msgBody = content, @ id = id from xz where id = @ i update xz_user set id1 = @ i where phone = @ phone select @nickname = nickname, @ roomid = roomid from chat_user where phone = @
phone insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, @ nickname ':' @ msgBody, @ roomid) 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), @ Phone, @ SrcPhone, @ phone, 1, 0, 1, 2, @ lineid) set @msgbody = substring (@msgbody, 71, len (@msgbody)) End
Else if @Content = '0' begin select @ i = id1 from xz where id = (select id1 from xz_user where phone = @ phone) select @msgbody = content, @ id = id from xz where id = @ i update xz_user set id1 = @ id where phone = @ phone select @nickname = nickname, @ roomid = roomid from chat_user where phone = @ phone insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, @ nickname ' : ' @ msgBody, @ roomid) 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, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1 , 2, @Linkid = Substring (@msgbody, 71, len (@msgbody)) End Else Begin Declare Yb Cursor for select Content, ID from xz where xz.id1 = (select id1 from xz_user where phone = @Phone) Order by id open yb fetch next from yb INTO @ msgbody, @ ID while (@@ fetch_status = 0) begin if @ i = @ content begin select @nickname = nickname, @ roomid = roomid from chat_user where phone = @ phone insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, @ nickname ':' @ msgBody, @ roomid ) Update xz_user set id1 = @ id where phone = @ phone 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), @ phone, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) set @msgbody = substring (@msgbody, 71, len (@msgBody)) end end set @ i = @ i 1 fetch next from yb into @ msgBody, @ id end close yb deallocate ybend endelse begin select @msgBody = content from xz where id = 1 update xz_user set id1 = 1 where phone = @ phone select @nickname = nickname, @ roomid = roomid from chat_user where phone = @ phone insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, @ nickname ':' @ msgBody , @ roomid) 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), @ phone, @Feeevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) set @msgbody = substring (@msgbody, 71, len (@msgbody) EndendGo
Set quoted_identifier off goset ANSI_NULLS ON GO
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ ****** Object: stored procedure dbo.chat script date: 2005-2-25 12:35:44 ****** / CREATE PROC [DBO]. [Chat] - chat '135 ** ****** ',' my ',' 278810 ',' 571 ',' 1 '- # instruction # Userid # mtcode # phone # nickname # roomid # @ phone nvarchar (11), @ Content NVARCHAR (512 ), @ srcphone nvarchar (20), - port @Province NVARCHAR (20), - province @debug int = 0, - Debug @Linkid nvarchar (20) = '0'ASDeclare @ msgbody nvarchar (512), - - Send a message @Roomid int, - Room ID @ Roomname Nvarchar (20), - Room Name @Online Int, - Online Number @State Int, - User Status @Userid Int, - User ID @ Mtcode Int, - Dry port @nickname nvarchar (20), - User nickname @seickname nvarchar (20), - 对 昵 @sex nvarchar (2), - Gender @Chat Bit, - Accepted group chat @clew nvarchar (512), - Tips @tophone nvarchar (11), - Receive number @welcome nvarchar (512), - Welcome words @TempBody nVARCHAR (512), @ page int, - Page @PageCount Int, - Page @i int, @ feEcode int, @ feetype int, @feesevid nvarchar (20), @ mttemp int
- Error check if @ phone = '13300000000' or @content = 'or @Content is Null or Len (@phone) <> 11 Begin Returnend
- Back to 10IF LEN (@srcphone) <= 6 Begin Set @SRCPHONE = Left (@ srcphone, 4) '10'end - Shield Mobile Number IF (Charindex ('13', @ Content)> 0 OR Charindex ('057', @ Content)> 0 or Charindex ('one or three', @ Content)> 0) And not exists (select * from test_phone where phone = @ phone) Begin Returnend
--3 days no news automatically close group chat - chat_user set chat = 0 Where chat = 1 And Datediff (d, intime, getdate ())> 2
--10 days no news automatic offline - ipdate chat_user set state = 0, roomid = null where state = 1 and datediff (d, intime, getdate ())> 9
- Update your state update chat_user set intime = getdate (), intime1 = getdate (), fraction = fraction 1 where phone = @ phone-- fault tolerance SET @Content = Replace (@content, '#', '') IF Upper (@ 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, ' ', '') set @content = replace (@content, '.', '') Set @content = replace (@ Content, '.', '') End
IF Upper (@content) = 'test' begin exec test @Phone Returne
Exec fun_sevid @ phone, @ province, @ feecode output, @ feetype output, @feesevid output
- Unregistered 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 = 'a love encounter, experience the passion appointment, please reply ME join Dating park Tel:.' 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 @ province = '2571' Begin Insert INTO USER_2571 (Phone, ServiceID) VALUES (@ phone, '520500') end if Upper (@ 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 ('male,' female ' Begin Select Top 1 @ sex = sex from chat_user orthder by newid () --set @sex = 'female' end else begin set @nickname = substring (@ nickname, 2, len (@nickname)) end if len (@ Nickname> 6 begin set @msgbody = 'Hey! This name is long! Don't think that the handsome girl is a computer ~! I want to catch your love, and the nickname should not exceed 6 words. Reply to ME nickname complete registration. 'End else if len (@nickname) <1 begin set @msgbody =' Hey! Is this too short? Don't think that the handsome girl is a computer ~! I want to catch your love belonging, and the nickname is not less than 1 word. Reply to ME Gender Nickname Complete Registration. 'End else if isnumeric (@nickname) = 1 begin set @msgbody ='! How do you take a digital name? Don't think that the handsome girl is a computer ~! I want to catch your love, and the nickname should not exceed 6 words. Reply to ME Gender Nickname Complete Registration.
'End else if exists (select * from chat_user where nickname = @ nickname) begin set @msgbody =' Wow! Intelligent and bright name, unfortunately someone will first step. Waiting for you, is it a heartbeat? Quickly reply to ME Gender nickname to change a name.
'End else begin insert into chat_user (phone, nickname, srcphone, province, sex) values (@ phone, @ nickname, left (@ srcphone, 6), @ province, @ sex) update chat_user set mtcode = userid where phone = @ phone set @roomid = 4 select @ roomname = roomname from chat_room where id = @ roomid update chat_user set state = 1, roomid = @ roomid where phone = @ phone if @Province = '2571' begin set @msgBody = @nickname " Welcome to the reader club '" @ROOMNAME " chat room, you can reply to you, you can chat with you, reply to MR to see the room, reply to MK to find friends, change nickname Reply ME Gender nickname "Else Begin set @ Msgbody = @nickname ", welcome to '" @ROMNAME "chat room, reply to what you want to say, you can chat with you, reply to MR to see the room, reply to MK to find friends, change nickname Reply ME Gender nickname" END end end else begin insert into chat_user (phone, nickname, srcphone, province, sex) values (@ phone, 'temp', left (@ srcphone, 6), @ province, 'F') update chat_user set mtcode = userid, nickname = userid where phone = @ phone select @ roomid = roomid, @ nickname = nickname from chat_user where phone = @ phone set @roomid = 4 select @ roomname = roomname from chat_room where id = @ roomid update chat_user set state = 1, ro = @ roomid where phone = @ phone if @Province = '2571' begin set @msgbody = "Welcome to the '" @Roomname "chat room of the reader club, meet a love, pursue a romantic! Reply "" ME Gender Nickname "" Take a nickname for yourself, such as the handsome guy of the name Wei Xiaobao "Else Begin Set @msgbody =" Welcome to the City Love '" @ROMNAME "' chat room, In a love, pursue a romantic! Reply "" ME Gender Nickname "
"Take a nickname for yourself, such as the handsome guy of the name Wei Xiaobao" EndInsert Into chat_log (Phone, Srcphone, Msgbody, Roomid) VALUES (@ phone, @ srcphone, @ nickname 'Register to chat room "@ roomID ) INSERT INTO CHAT_LOG (Phone, Srcphone, Msgbody, Roomid) Values (@ phone, @ srcphone, '# me', @ Roomid) 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) Endend
- Else Begin if EXISTS (SELECT TOP 1 * from chat_user where phone = @ phone and state = 0) Begin set @Roomid = rand () * 8 1 Update chat_user set roomid = @ roomid, state = 1 where phone @ Phone end select top 1 @ roomid = roomid from chat_user where phone = @ phone if Upper (@ Content, 1)) = 'm' or upper (@content) in ('xz', 'XH', ' BH ',' TQ ',' GG ') or @srcphone in (' 2788101 ',' 8788101 ',' 2788102 ',' 8788102 ') begin select @nickname = nickname from chat_user where phone = @ phone insert into chat_log (phone , SrcPhone, Msgbody, Roomid Values (@ phone, @ srcphone, @ Nickname ':' @ Content, @ Roomid) End if Upper (Ltrim (@content), 4)) in ('QXLT') Begin Set @srcPhone = left (@ srcPhone, 6) select @ nickname = nickname, @ userid = userid from chat_user where phone = @ phone delete chat_friend where userid = @ userid delete chat_user where phone = @ phone delete chat_room where phone = @ phone if @ Province = '2571' begin delete user_2571 where phone = @ phone and serviceid = '520500' set @msgbody = 'night cool as water, be careful! I hope that I will give you a good memory tonight! Remember to come back and see, send 520 to 8788 to return to the reader club chat room.
'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 ELSE BEGIN DELETE USER_571 WHERE Phone = @ phone and serviceid = 'yxg' end insert INTO CHAT_LOG (Phone, Srcphone, Msgbody, Roomid) VALUES (@ phone, @ srcphone, str (@phone) 'Cancel chat room business ----------------- -------------------- ', @ roomid) Insert Into chat_log (phone, srone, msgbody, roomid) Values (@ phone, @ srcphone,' # qlt ', @Roomid) RETURN END --ME MK MR MC MQ MS if Upper (@ Content, 2)) = 'me' begin set @srcphone = left (@ srcphone, 6) if Len (@content> 2 Begin- - change nickname set @nickname = substring (@ Content, 3, len (@content)) set @sex = substring (@ nickname, 1, 1) if @sex not in ('male,' female ') Begin Select Top 1 @ sex = sex from chat_user order by newid () --set @sex = 'female' end else begin set @nickname = substring (@ nickname, 2, l En (@nickname)) end if len (@nickname)> 6 begin set @msgbody = '! This name is long! Don't think that the handsome girl is a computer ~! I want to find your dream lover, and the nickname should not exceed 6 words. Reply to ME Gender Nickname Complete Modification. 'End else if len (@nickname) <1 begin set @msgbody =' Hey! Is this too short? Don't think that the handsome girl is a computer ~! I want to catch your love, and the nickname should not exceed 6 words. Reply to ME Gender Nickname Complete Registration. 'End else if isnumeric (@nickname) = 1 begin set @msgbody ='! How do you take a digital name? Don't think that the handsome girl is a computer ~! I want to catch your love, and the nickname should not exceed 6 words. Reply to ME Gender Nickname Complete Registration.
'End else beginning_user where phone <> @ phone and nickname = @ nickname) Begin set @msgbody =' Wow! Intelligent and bright name, unfortunately someone will first step. Waiting for you, is it a heartbeat? Quickly reply to ME Gender nickname to change a name. 'End else begin update chat_user set nickname = @ nickname, state = 1, sex = @sex where phone = @ phone if @sex =' male 'begin set @msgbody = @nickname ', wow, handsome guy, let Beauty is bright! Experience the passionate date, breakthrough is tonight. Reply to MR chat, look for your dream lover. 'End else begin set @msgbody = @nickname ', you are like a light cloud, if you are floating, if you are flowing back to snow. The past is like smoke, and I will recall. Reply to MR chat, feel the urban love. '
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 = @neickname ", welcome to the city love '" @roomname "chat room, meet a love, pursue a romantic! Reply MR Look at the room, reply to MK to find a friend, change nickname Reply ME Gender nickname "End Insert Into chat_log (Phone, Srcphone, Msgbody, Roomid) Values (@ phone, @ srcphone, '# me', @ Roomid) 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 Else IF Upper (@ Content, 2)) = 'MB' Begin Declare @FriendIdIdId Int, @ FriendName Nvarchar (20), @ FriendMtcode Nvarchar (20), @ Friendsrcphone NVARCHAR (10) Set @content = r Eplace (@content, ' ', '') set @srcphone = left @FriendName = SUBSTRING (@ Content, 3, Len (@content)) --SELECT @nickname = Nickname from chat_user Where phone = @ phone --insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, @ nickname ':' @ Content, @ roomid) if exists (select * from chat_user where nickname = @
friendname) begin select @ friendid = userid, @ friendmtcode = mtcode, @ tophone = phone, @ friendsrcphone = srcphone from chat_user where nickname = @ friendname select @ userid = userid, @ mtcode = mtcode, @ nickname = nickname from chat_user where phone = @Phone if @ phone = @ Tophone Begin Set @msgbody = 'Can't add yourself as a friend. '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, @ fesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @LINKID) End End Else if EXISTS (SELECT TOP 1 * from chat_friend where friendsid = @ Friendid) Begin set @msgbody = 'friend:' @FriendName 'already exists.
Reply to chat with each other 'Insert Into chat_log (Phone, srcphone, msgbody, roomid) VALUES (@ phone, @ srcphone, @ msgbody, @ roomid) set @srcphone = left (@ srcphone, 6) LTRIM (STR (@ friendmtcode)) 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_friend (userid, friendidid) Values (@ userid, @ friendid) set @msgbody = 'has successfully added' @FriendName 'as a friend. Reply to chat with each other 'Insert Into chat_log (Phone, srcphone, msgbody, roomid) VALUES (@ phone, @ srcphone, @ msgbody, @ roomid) set @srcphone = left (@ srcphone, 6) LTRIM (STR (@ friendmtcode)) 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 set @msgbody = @nickname 'adds you as friend.
Reply to chat with each other 'set @srcphone = left (@ Friendsrcphone, 6) LTRIM (Str (@mtcode) Insert Into chat_log (Phone, srcphone, msgbody, roomid) Values (@ phone, @ srcphone, @ msgbody, @roomid) 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 end else if upper (left (@ Content, 2)) = 'MH' begin select @ userid = userid from chat_user where phone = @ phone declare yb cursor for select nickname from chat_user where userid in (select top 8 friendid from chat_friend where Userid = @ Userid ORDER BY NEWID ()) Open YB Fetch Next from Yb Into @nickname if @@ fetch_status = 0 Begin Set @msgbody = 'You have no friends, reply to MB each other nickname Add Friends' end else begin set @msgbody = 'Your friend:' While (@@ fetch_status = 0) begin set @msgbody = @msgbody @nickname ',' fetch next from yb @neickname end set @msgbody = @msgbody 'is waiting for you, enjoy Go! Reply MS nickname invitation to each other 'end close yb deallocate yb --insert Into chat_log (Phone, srone, msgbody, roomid) Values (@ phone, @ srcphone, @ msgbody, @ roomid) 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, @ ms Gbody, @ phone, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) end end else if Upper (@content) = 'mm' begin update chat_user set sex = 'female' Where Phone = @ phone set @msgbody = "Beautiful MM, welcome you, meet a love, pursue a romantic! Reply to MR Look at the room, reply MK to find a friend "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 Else if Upper (@content) = 'gg' begin update chat_user SET SEX = 'male' Where phone = @ phone set @msgbody = "Handsome guy, welcome you, meet a love, pursue a romantic! Reply MR look at the room, reply MK to find a friend" 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 (@ Content, 4, Len (@Content)))) = 1 begin select @roomID = SUBSTR ing (@ Content, 4, len (@Content)) end else begin select @ roomid = roomid from chat_user where phone = @ phone end select @ pagecount = count (*) / 8 from chat_user where state = 1 and roomid = @ roomid And Fraction> 0 and sex = 'male' and phone <> @ phone from chat_user where phone = @PHONE IF @Page> @ PageCount Begin Set @ Page = 0 End Update chat_user set Page @ Page
1 where phone = @ phone set @ i = 0 set @msgbody = 'room has:' declare YB CURSOR for SELECT NICKNAME from Chat_User Where State = 1 and roomid = @ Roomid and Fraction> 0 and sex = 'Male' and Phone <> @PHONE ORDER BY INTIME DESC OPEN YB FETCH NEXT from Yb Into @nickname While (@@ fetch_status = 0 and @i <8 * (@ Page 1)) Begin if @i> = 8 * @ Page Begin Set @ Msgbody = @msgbody @nickname ',' end fetch next from yb @nickname set @ i = @ i 1 end close yb deallocate yb set @msgbody = @msgbody 'is waiting for you, replying to MS nickname invitation each other, Reply MKG See the next page '- Isert Into chat_log (Phone, Srcphone, Msgbody, Roomid, Tophone) VALUES (@ phone, @ srcphone, @ msgbody, @ roomid, @ phone) 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 (), @ Province, 1, @ feecode, @ msgbody, @ phone, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) End Else if Upper Left (@ content, 3)) = 'mkm' begin set @content = replace (@content, ' ', '') set @srcphone = left (@ srcphone, 6) if isnumeric (@ Content, 4, Len (@content))) = 1 Begin Select @Roomid = Substring (@ Content, 4, Len (@content)) Else Begin Select @ roomid = roomid from chat_user where phone = @ Phone
End select @ pagecount = count (*) / 8 from chat_user where state = 1 and roomid = @ roomid and sex = 'female' and fraction> 0 and phone <> @ phone select @ PAGE = Page from chat_user where phone = @ phone IF @Page> = @ PageCount Begin Set @ Page = 0 End Update chat @ = @ page 1 where phone = @PAGE SET @ i = 0 set @msgbody = 'Room with:' Declare Yb Cursor for Select Nickname from Chat_user where state = 1 and roomid = @ roomid and fraction> 0 and sex = 'female' and phone <> @ phone order by intime desc open yb fetch next from yb @nickname while (@@ fetch_status = 0 and @i < 8 * (@ Page 1)) Begin if @i> = 8 * @ Page Begin set @msgbody = @msgbody @nickname ',' end fetch get from yb @nickname set @ i = @ i 1 end Close Yb Deallocate YB Set @msgbody = @msgbody 'Are waiting for you, replying to MS nickname Invitation Office, Reply MKM to see next page' --ensert Into chat_log (Phone, srcphone, msgbody, roomid, tophone) VALUES (@phone, @ srcphone, @ msgbody, @ Roomid, @ phone) 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 Else if Upper (@ Content, 2)) = 'mk' begin set @content = Replace @Content, ' ', '') set @
Srcphone = left (@ srcphone, 6) if isnumeric (@ Content, 3, Len (@content))) = 1 Begin Select @Roomid = Substring (@ Content, 3, Len (@content) ELSE BEGIN SELECT @ roomid = roomid from chat_user where phone = @ phone end select @ pagecount = count (*) / 5 from chat_user where state = 1 and roomid = @ roomid and fraction> 0 and phone <> @ phone select @ page = page from chat_user Where Phone = @PHONE @Page> = @ PageCount Begin Set @ Page = 0 End Update chat_USER SET Page = @PAGE 1 WHERE phone = @MsGbody = 'Room:' Declare YB cursor for select nickname, sex from chat_user where state = 1 and roomid = @ roomid and fraction> 0 and phone <> @ phone order by intime desc open yb fetch next from yb into @ nickname, @ sex while (@@ fetch_status = 0 And @i <5 * (@ Page 1)) Begin if @i> = 5 * @ Page Begin set @msgbody = @msgbody @nickname '(' @sex '),' end fetch next from yb INTO @ nickname, @ sex set @ i = @ i 1 end close yb deallocate yb set @msgbody = @msgbody 'is waiting for you, reply to MS nickname invitation to each other, reply MK to see the next page' - insert into chat_log (phone, srcphone, msgbody, roomid, tophone) values (@ phone, @ srcPhone, @ msgBody, @ roomid, @ phone) 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 Else if Upper (@ Content, 2)) = ' Ma 'Begin Set @content = Replace (@content,' ',' ') set @srcphone = left @nickname = substring (@ Content, 3, Len (@content)) if EXISTS Select * from chat_user where nickname = @ nickname) Begin print 't' end end else if Upper (@ Content, 2)) = 'tq' begin set @srcphone = left (@ srcphone, 6) set @msgbody = ' Didn't find this city. Please reply to the TQ city query. For example: TQ Hangzhou 'IF LEN (@content)> 2 Begin Set @tempBody = Substring (@ Content, 3, Len (@content)) if not exists (SELECT TOP 1 * from Weather WHERE PROVINS = @ Tempbody) Beginselect Top 1 @tempBody = cs1 from cs where cs = @ tempBody end select top 1 @ msgBody = content from weather where province = @ tempBody end else begin set @msgBody = 'reply to query city TQ city city forecasts can be: Ningbo, Hangzhou, Jinhua, Quzhou Lishui Wenzhou Zhoushan Taizhou Huzhou Jiaxing Shaoxing. For example: TQ Hangzhou 'End - Irt Into chat_log (Phone, Srcphone, Msgbody, Roomid, Tophone) VALUES (@ phone, @ srcphone, @ Content, @ Roomid, @ phone)
- Insert an advertisement set @tempBody = @msgbody if exists (selection <(68-len (@tempBody))))) Begin Select Top 1, BEGIN SELECT TOP 1 @ Clew = Content from chat_clew where (lin (limited) <(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 (), @ Province, 1, @ feecode, left (@ Tempbody, 70), @ phone, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) set @tempbody = Substring (@tempbody, 71, len (@tempbody)) end end else if Upper (@ Content, 2)) = 'xh' begin select @ roomid = roomid from chat_user where phone = @ phone if len (@srcphone )> 6 begin select @tophone = phone, @ province = province from chat_user where mtcode = substring (@ SrcPhone, 6, len (@SrcPhone)) end else begin set @tophone = @phone end exec fun_sevid @ tophone, @ province, @FeeCode output, @ FeeType output, @ FeeSevID output select @ srcPhone = ltrim (str (srcphone)) ltrim (str (mtcode)) from chat_user where phone = @ tophone select top 1 @Content = content from xh where xtype = ' Playful humor 'Order by newid () Insert Into chat_log (Phone, Srcphone, Msgbody, Roomid, Tophone) VALUES (@phon e, @ srcPhone, @ content, @ roomid, @ tophone) 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
ELSE IF @SRCPHONE IN ('2788101') or Upper (@ Content, 2)) = 'XZ' Begin Exec MW @ phone, @ Content, @ srcphone, @ province end else @srcphone in ('2788102') OR Upper (@ Content, 2)) = 'BH' Begin Exec BH @ phone, @ Content, @ srcphone, @ province end else @content in ('1', ' 2 ',' 3 ',' 4 ',' 5 ',' 6 ',' 7 ',' 8 ',' 9 ') Begin - Enter System Default Chat Room 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 - ISERT INTO chat_log (Phone, srcphone, msgbody, roomid) VALUES (@ phone, @ srcphone, '#' ltrim (Str (@content), @ roomid) 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, MTTYP E, LINKID) VALUES (Getdate (), 1, @ province, 1, @ feecode, @ fesevid, @ 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 - enter the self-built chat room 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 Else if Upper (@ Content, 2)) = 'mf' begin set @srcphone = left (@ srcphone, 6) set @ Roomname = left (@ 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 @Roomi D = id from chat_room where phone = @ phone set @msgbody = 'chat room name modification success. Reply' ltrim (Str (@Roomid) 'Enter your room Reply MG Welcome word to modify your room Welcome word' end Else Begin Insert Into chat_room (@ phone, @ roomname) select @ roomid = id from chat_room where phone = @ phone set @msgbody = 'You now have your own chat room. Reply' ltrim (STR (@Roomid)) 'Enter your own room Reply MF Room name Modified room name Reply MG Welcome word to modify your room Welcome word' 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 (@ 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 @msgbody = @ phone set @msgbody = 'room welcomes word modification success. Reply' ltrim (Str (@Roomid) 'Enter your own room' end Else Begin Set @msgbody = 'Reply MF Room Name Create Room Name Reply MG Welcome Word Modify HydroShole Welcome Word' 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 (@content) = 'my' begin - enter your room 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 = 'You have Enter your own room 'end else begin set @msgbody =' did not create '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 Else if Upper (@ Content, 2)) = ' MT 'Begin - kicks out someone else set @content = replace from its own room (@content,' ',' ') if exists (Substring) Begin if isnumeric (Substring (@Content, 3, len (@content)) = 1 and exists (SELECT * from chat_user where mtcode = substring (@ Content, 3, Len (@content))) Begin select @ mtcode = mtcode, @ srcphone = srcphone, @ province = province, @ tophone = phone, @ nickname = nickname, @ roomid = roomid from chat_user where mtcode = substring (@ Content, 3, len (@Content)) end else if exists (select * from chat_user where nickname = substring (@Content , 3, len (@content)) Begin Select @ mtcode = mtcode, @ srcphone = srcphone, @ province = province, @ Tophone = phone, @ nickname = nickname, @ roomid = roomid from Chat_user where nickname = substring (@ Content, 3, Len (@content)) end if @ 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 ", welcome to the Sex and the city '" @roomname "' chat rooms, meet unexpectedly encounter A love,
Pursue a romantic! Reply MR to see the room, reply MK find a friend, changing nickname reply ME sex nickname "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 @fecode = 0 set @feetype = 1 set @Fesevid = 'yxg 'End @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 Else begin set @msgbody = "Is there this person in your room? Why didn't I find it? '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 @MsGbody = 'You have not created your own room, how can you play others? ? Hurry to reply to the MF room name to create your own room! Experience the taste of your administrator! ! '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, @, @ feecode, @ msgbody, @ phone, @feesevid, @ feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) end end * / else if Upper (@ 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 @ mtcode = mtcode, @ tophone = phone from Chat_user where nickname = @ nickname if @ phone = @ Tophone begin set @msgbody = 'can't chat with yourself. Still find someone to talk. 'End else begin set @msgbody =' Direct reply to chat content, chat with ' @nickname ' private chat, MC close / open group chat, free of others to bother.
'End set @SrcPhone = left (@ SrcPhone, 6) ltrim (str (@mtcode)) 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 , @Feeevid, @feetype, @ srcphone, @ phone, 1, 0, 1, 2, @ linkid) 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 (Strim (Str (@Online)) 'people)' char (13) fetch next from yb Into @Roomid @ roomname, @ online end @MsGbody = @msgbody 'Reply room number entry.' IF @Debug <> 1 and not exists (SELECT * from test_phone where phone = @ phone) begininsert 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, 2)) = 'MC' Begin if Charindex ('Open', @ Content)> 0 Begin update chat_user set chat = 1 Where phone = @ phone end else if charIndex ('Off ', @ Content)> 0 begin update chat_user set chat = 0 where phone = @ phone end else begin update chat_user set chat = 1 ^ chat where phone = @ phone endselect @ chat = chat from chat_user where phone = @ phone set @msgBody = 'You have closed the group chat function, do not receive group chat information. Reply to the MC to open the group chat function. 'If @ chat = 1 begin set @msgbody =' You have already turned on group chat function, receive group chat information. Reply to the MC Close the group chat function. 'End --ensert inTo chat_log (Phone, Srcphone, Msgbody, Roomid) VALUES (@ phone, @ srcphone,' # mc ', @ Roomid) 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 Else if Upper (@ Content, 2)) = ' MQ 'Begin - Leave Set @srcphone = Left (@ srcphone, 6) Update chat_user set state = 0, roomid = null where phone = @ phone set @msgbody =' lost emotions, misfortune, intertwined love. .. I decided to retreat, and lived a hidden life. Call information will not be received during this period. Send me to ' @SrcPhone ' regaining urban love
select top 1 @ userid = userid, @ mtcode = mtcode, @ nickname = nickname, @ roomid = roomid from chat_user where phone = @ phone insert into chat_log (phone, srcphone, msgbody, roomid) values (@ phone, @ srcPhone, ' #Mq # ' LTRIM (Str (@Userid)), @ Roomid) - # 指) # Userid # mtcode # phone # nickname # roomid # INSERT INTO Chat_log (Phone, Srcphone, Msgbody, Roomid) Values (@ phone, @ srcPhone, @ nickname 'leave the IM', @ roomid) 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 / * - View User Information Else if Upper (@ Content, 2)) = 'MM' Begin Set @msgbody = NULL ISNUMERIC (Substring (@ Content, 3, Len (@content))) = 1 Begin Select @msgbody = Phone from chat_user where mtcode = substring (@ Content, 3, Len (@content)) Else Begin select @msgbody = pho ne 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 - private chat set @mtcode = substring (@ srcphone, 6, len (@srcphone)) if exists (SELECT * from chat_user where state = 1 and mtcode = @ mtcode ) begin set @tophone = (select phone from chat_user where state = 1 and mtcode = @ mtcode) select @ nickname = nickname, @ mtcode = mtcode, @ sex = sex, @ roomid = roomid from chat_user where phone = @phone if @ Phone = @ Tophone begin set @msgbody = '"' @nickname '"' 'self-speaking self-words:' @content end else begin set @msgbody = " @ nickname " '(" @ SEX ') STRCPHONE = Left (@ srcphone, 6) LTRIM (@mtcode) IF LEN (@msgbody)> 0 Begin
select @ srcphone = srcphone, @ province = province from chat_user where phone = @ tophone set @SrcPhone = @srcPhone ltrim (str (@mtcode)) if exists (select * from chat_clew where (len (content) <(68-len (@msgbody))))) Begin Select Top 1 @ clew = content from chat_clew where (len) <(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 exec fun_sevid @ tophone, @ province, @ FeeCode output, @ FeeType output, @ FeeSevID output if @debug <> 1 and not exists (select * from test_phone where phone = @ tophone) and @Province <> '2571' begin-- Unicom shield 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 select @TonickName = Nickname from chat_user where phone = @ Tophone if @ phone = @Tophone Begin Set @msgbody = '"' @nickname '"' 'Self-speech:' @content end else begin set @msgbody = "" @ nickname "'(" @sex ") Sike '" @TonickName "' quietly said:" @Content End if @msgbody <> Null Begin INSER t into chat_log (phone, srcphone, msgbody, roomid, tophone) values (@ phone, @ srcPhone, @ msgBody, @ roomid, @ tophone) end end end end else begin if not exists (select top 1 * from chat_room, chat_user where Chat_user.phone=@phone and chat_room.id = chat_user.roomid) Begin Return End 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) beginset @msgBody = ' "' @nickname '(' @sex ') "said:' @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
EXEC FUN_SEVID @ Tophone, @ province, @ feecode output, @feetyvid output, @feesevid output if @debug <> 1 and not exists (select * from test_phone where phone = @ Tophone) Begin
- Insert an advertisement set @tempBody = @msgbody if exists (select top 1 * from chat_user where phone = @ Tophone and fraction <5) and let (@tempbody) <60 begin set @tempbody = @tempbody '(Tips: Reply MQ leaving chat room) 'end else if exists (Select * from chat_clew where (lin (limited) <(68-len (@tempbody))))))) Begin Select Top 1 @ @ @TempBody)) Clew = content from chat_clew where (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 (), @ Province, 1, @ feecode, @ 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 = 'into the room number reply' 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 (Strim (Str (@Online)) 'people)' char (13) fetch next from yb @ Roomid, @ roomname, @Online End Close Yb Deallocate Yb if @debug <> 1 and not exists (SELECT * from test_phone where pho ne = @ 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 coverndgoset quote_identifier off goset ANSI_NULLS ON GO
Set quoted_identifier on goset ANSI_NULLS ON GO
/ ****** Object: Trigger dbo.update_room script date: 2005-2-25 12:35:45 ****** / CREATE TRIGGER [UPDATE_ROOM] on dbo.chat_user for insert, Update, Delete Asupdate 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.roomidgo
Set quoted_identifier off goset ANSI_NULLS ON GO
EXEC SP_ADDEXTENDEDPROPERTY N'MS_DESBRIPTION ', N' Service Number (Mobile 2788, Unicom 8788) ', N'User', N'dbo ', N'TABLE', N'Chat_User ', N'Column', N'srcPhone '
Go
exec sp_addextendedproperty N'MS_Description ', N' free service category ', N'user', N'dbo ', N'table', N'FREE_PHONE ', N'column', N'FreeSrvId'GOexec sp_addextendedproperty N'MS_Description ' , N 'Free Phone Number', N'User ', N'dbo', N'Table ', N'Free_Phone', N'Column ', N'PhoneNumber'
Go
