用存储过程写的聊天室程序-CSDN博客

本文链接：https://blog.csdn.net/iuhxq/article/details/117733

/****** 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_AS
GO

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'
GO

exec sp_dboption N'sms', N'ANSI warnings', N'false'
GO

exec sp_dboption N'sms', N'auto create statistics', N'true'
GO

exec sp_dboption N'sms', N'auto update statistics', 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'IsTrigger') = 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].[MW]') 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'IsUserTable') = 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'IsUserTable') = 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'IsUserTable') = 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'IsUserTable') = 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'[dbo].[chat_room]') and OBJECTPROPERTY(id, N'IsUserTable') = 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'IsUserTable') = 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'IsUserTable') = 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'IsUserTable') = 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'IsUserTable') = 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].[weather]') and OBJECTPROPERTY(id, N'IsUserTable') = 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'IsUserTable') = 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'IsUserTable') = 1)
drop table [dbo].[xz_user]
GO

/****** Object: Table [dbo].[AirLinkMT] Script Date: 2005-2-25 12:35:21 ******/
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].[birthday] Script Date: 2005-2-25 12:35:23 ******/
CREATE 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] IDENTITY (10000, 1) NOT 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 ,
[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 ,
[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] [nvarchar] (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 ,
[bz] [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 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_friend] WITH NOCHECK ADD
CONSTRAINT [PK_friend] 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].[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]
GO

ALTER 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],
CONSTRAINT [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
GO
SET 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) output
as
if exists(select * from free_phone where phonenumber=@phone) begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = '520500'
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 = '520500'
end
else if @Province='571' begin
set @FeeCode = 0
set @FeeType = 1
set @FeeSevID = 'YXG'
end
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET 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'
as
if not exists(select top 1 * from test_phone where phone=@phone) and len(@phone)=11 begin
insert into test_phone values(@phone)
end
else begin
delete from test_phone where phone=@phone
end
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET 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'
as
declare
@msgBody nvarchar(512),
@tempBody nvarchar(512),
@nickname nvarchar(20),
@roomid int,
@i int,
@id int,

@sr int,
@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=@content
end
else begin
  set @msgbody='请回复你的生日。例如：你是5月6号出生回复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(),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
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object: Stored Procedure dbo.MW Script Date: 2005-2-25 12:35:43 ******/
CREATE proc [dbo].[MW]

@sr int,
@FeeCode int,
@FeeType int,
@FeeSevID nvarchar(20),
@MtTemp int

set @i=1
exec fun_sevid @phone,@province,@FeeCode output,@FeeType output,@FeeSevID output
set @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)
end
if 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
  end
  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 if @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
  end
  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
   set @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,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   set @msgBody = substring( @msgBody ,71,len(@msgBody))
  end

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(@msgBody,70),@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   set @msgBody = substring( @msgBody ,71,len(@msgBody))
  end
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 yb

end
end
else 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,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
set @msgBody = substring( @msgBody ,71,len(@msgBody))
end
end
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET 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'
--#指令#userid#mtcode#phone#nickname#roomid#
@phone nvarchar(11),
@Content nvarchar(512),
@srcPhone nvarchar(20), --端口
@Province nvarchar(20), --省份
@debug int=0,  --调试
@LinkID nvarchar(20)='0'
as
declare
@msgBody nvarchar(512), --发送消息
@roomid int,  --房间ID
@roomname nvarchar(20), --房间名称
@online  int,  --在线人数
@state  int,  --用户状态
@userid  int,  --用户ID
@mtcode int,  --下发端口
@nickname nvarchar(20), --用户昵称
@tonickname nvarchar(20), --对方昵称
@sex  nvarchar(2), --性别
@chat  bit,  --是否接受群聊
@clew  nvarchar(512), --提示语
@tophone nvarchar(11), --接收号码
@welcome nvarchar(512), --欢迎词
@tempBody nvarchar(512),
@page  int,  --分页
@pagecount int,  --分页
@i  int,

@FeeCode int,
@FeeType int,
@FeeSevID nvarchar(20),
@MtTemp int

--错误检查
if @phone='13300000000' or @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
--屏蔽手机号码
if (charindex('13',@Content)>0 or charindex('057',@Content)>0 or charindex('一三',@Content)>0) and not exists(select * from test_phone where phone=@phone) begin
return
end

--3天没消息自动关闭群聊
-- 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())>9

--更新自己状态
update chat_user set intime=getdate(),intime1=getdate(),fraction=fraction+1 where phone=@phone

--容错处理
set @Content = replace(@Content,'#','')
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,'+','')
set @Content = replace(@Content,'。','')
set @Content = replace(@Content,'.','')
end

if upper(@content)='TEST' begin
exec test @phone
return
end

exec fun_sevid @phone,@province,@FeeCode output,@FeeType output,@FeeSevID output

--未注册
if not exists(select * from chat_user where phone=@phone) and upper(@Content)<>'QXLT' begin
if upper(@Content) <> 'ME' and @Province = '571' begin
   set @msgBody = '邂逅一段爱情,体验激情约会,请回复ME加入交友乐园.咨询电话:'
   insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,'BZ',@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   return
end
if @province='2571' begin
  insert into user_2571 (phone,serviceid) values(@phone,'520500')
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
   select top 1 @sex=sex from chat_user order by newid()
   --set @sex = '女'
  end
  else begin
   set @nickname = substring (@nickname,2,len(@nickname))
  end
  if len(@nickname)>6 begin
   set @msgBody = '嗨！这名字好长啊！不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME昵称完成注册。'
  end
  else if len(@nickname)<1 begin
   set @msgBody = '嗨！这名字太短了吧？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要少于1个字。回复ME性别昵称完成注册。'
  end
  else if isnumeric(@nickname)=1 begin
   set @msgBody = '嗨！怎么取数字名字呀？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME性别昵称完成注册。'
  end
  else if exists(select * from chat_user where nickname=@nickname) begin
   set @msgBody = '哇！聪慧而灵秀的名字啊，可惜有人抢先一步了。等待你的，是不是心动的邂逅呢？快快回复ME性别昵称换一个靓名吧。'
  end
  else begin
   insert into chat_user (phone,nickname,srcphone,province,sex) values(@phone,@nickname,left(@srcphone,6),@province,@sex)
   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 + ",欢迎来到读者俱乐部'" + @roomname + "'聊天室,回复想要说的话既可与大家聊天,回复MR看房间,回复MK找朋友,改昵称回复ME性别昵称"
   end
   else begin
    set @msgBody = @nickname + ",欢迎来到'" + @roomname + "'聊天室,回复想要说的话既可与大家聊天,回复MR看房间,回复MK找朋友,改昵称回复ME性别昵称"
   end
  end
end
else begin
  insert into chat_user (phone,nickname,srcphone,province,sex) values(@phone,'temp',left(@srcphone,6),@province,'女')
  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,roomid=@roomid where phone=@phone
  if @Province = '2571' begin
   set @msgBody = "欢迎来到读者俱乐部之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复""ME性别昵称""为自己取个昵称，如取名韦小宝的帅哥回复ME男韦小宝"
  end
  else begin
   set @msgBody = "欢迎来到都市情缘之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复""ME性别昵称""为自己取个昵称，如取名韦小宝的帅哥回复ME男韦小宝"
  end
end

insert into chat_log (phone,srcphone,msgbody,roomid) values(@phone,@srcPhone,@nickname+'注册到聊天室',@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)
end
end

--已经注册
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(left(@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(left(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 = '夜凉如水，路上小心！愿今晚给你留下的不只是美好的回忆！记得常回来看看，发送520到8788重回读者俱乐部聊天室。'
   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)+'取消了聊天室业务---------------------------------------',@roomid)
  insert into chat_log (phone,srcphone,msgbody,roomid) values(@phone,@srcPhone,'#QXLT',@roomid)
  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
    select top 1 @sex=sex from chat_user order by newid()
    --set @sex = '女'
   end
   else begin
    set @nickname = substring (@nickname,2,len(@nickname))
   end
   if len(@nickname)>6 begin
    set @msgBody = '嗨！这名字好长啊！不要以为帅哥美女都是电脑哦~！想找到你的梦中情人，昵称不要超过6个字。回复ME性别昵称完成修改。'
   end
   else if len(@nickname)<1 begin
    set @msgBody = '嗨！这名字太短了吧？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME性别昵称完成注册。'
   end
   else if isnumeric(@nickname)=1 begin
    set @msgBody = '嗨！怎么取数字名字呀？不要以为帅哥美女都是电脑哦~！想抓住属于你的情缘，昵称不要超过6个字。回复ME性别昵称完成注册。'
   end
   else begin

    if exists(select * from chat_user where phone<>@phone and nickname=@nickname) begin
     set @msgBody = '哇！聪慧而灵秀的名字啊，可惜有人抢先一步了。等待你的，是不是心动的邂逅呢？快快回复ME性别昵称换一个靓名吧。'
    end
    else begin
     update chat_user set nickname=@nickname,state=1,sex=@sex where phone=@phone
     if @sex = '男' begin
      set @msgBody = @nickname + '，哇，帅哥来了，让众美女眼前一亮！体验激情约会，突破就在今晚。回复MR聊天，寻找你的梦中情人。'
     end
     else begin
      set @msgBody = @nickname + '，你好似轻云避月，飘飘然若流风之回雪。往事如烟，悠悠回想。回复MR聊天，感触都市情缘。'

     end
    end
   end
  end
  else begin
   select @roomid=roomid,@nickname=nickname from chat_user where phone=@phone
   set @roomid = rand() * 8 + 1
   select @roomname=roomname from chat_room where id=@roomid
   update chat_user set state=1,roomid=@roomid where phone=@phone
   set @msgBody = @nickname + "，欢迎回到都市情缘之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友，改昵称回复ME性别昵称"
  end
  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

end
else if upper(left(@Content,2)) = 'MB' begin
  declare @friendid int,@friendname nvarchar(20),@friendmtcode nvarchar(20),@friendsrcphone nvarchar(10)
  set @Content = replace(@Content,'+','')
  set @srcPhone = left(@srcPhone,6)
  set @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 = '不能加自己为好友。'
    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 exists(select top 1 * from chat_friend where friendid=@friendid) begin
    set @msgBody = '好友:' + @friendname + '已经存在。回复即可与对方聊天'
    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
   else begin
    insert into chat_friend (userid,friendid) values(@userid,@friendid)
    set @msgBody = '已成功将' + @friendname + '添加为好友。回复即可与对方聊天'
    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 + '把您加为好友了。回复即可与对方聊天'
    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 = '您还没有好友,回复MB对方昵称添加好友'
  end
  else begin
   set @msgBody = '您的好友为:'
   while (@@fetch_status=0)
   begin
    set @msgBody = @msgBody + @nickname +','
    fetch next from yb into @nickname
   end
   set @msgBody = @msgBody + '正在等你,尽情挥洒去吧!回复MS昵称邀请对方'
  end
  close yb
  deallocate yb
  --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,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
  end
end
else if upper(@Content) = 'MM' begin
  update chat_user set sex='女' where phone=@phone
  set @msgBody = "漂亮MM，欢迎您，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友"
  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) = 'GG' begin
  update chat_user set sex='男' where phone=@phone
  set @msgBody = "帅哥，欢迎您，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友"
  if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
   insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
  end
end
else if upper(left(@Content,3)) = 'MKG' begin
  set @Content = replace(@Content,'+','')
  set @srcPhone = left(@srcPhone,6)
  if isnumeric(substring(@Content,4,len(@Content))) = 1 begin
   select @roomid = substring(@Content,4,len(@Content))
  end
  else begin
   select @roomid=roomid from chat_user where phone=@phone
  end
  select @pagecount=count(*)/8 from chat_user where state=1 and roomid=@roomid and fraction>0 and sex='男' and phone<>@phone
  select @page=page 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 = '房间里有:'
  declare yb cursor
  for select nickname from chat_user where state=1 and roomid=@roomid and fraction>0 and sex='男' 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 into @nickname
   set @i=@i+1
  end
  close yb
  deallocate yb
  set @msgBody = @msgBody + '正在等你,回复MS昵称邀请对方,回复MKG看下一页'
  --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
end
else if upper(left(@Content,3)) = 'MKM' begin
  set @Content = replace(@Content,'+','')
  set @srcPhone = left(@srcPhone,6)
  if isnumeric(substring(@Content,4,len(@Content))) = 1 begin
   select @roomid = substring(@Content,4,len(@Content))
  end
  else begin
   select @roomid=roomid from chat_user where phone=@phone

  end
  select @pagecount=count(*)/8 from chat_user where state=1 and roomid=@roomid and sex='女' 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_user set page=@page+1 where phone=@phone
  set @i=0
  set @msgBody = '房间里有:'
  declare yb cursor
  for select nickname from chat_user where state=1 and roomid=@roomid and fraction>0 and sex='女' 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 into @nickname
   set @i=@i+1
  end
  close yb
  deallocate yb
  set @msgBody = @msgBody + '正在等你,回复MS昵称邀请对方,回复MKM看下一页'
  --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
end
else if upper(left(@Content,2)) = 'MK' begin
  set @Content = replace(@Content,'+','')
  set @srcPhone = left(@srcPhone,6)
  if isnumeric(substring(@Content,3,len(@Content))) = 1 begin
   select @roomid = substring(@Content,3,len(@Content))
  end
  else begin
   select @roomid=roomid from chat_user where phone=@phone
  end
  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
  if @page>=@pagecount begin
   set @page=0
  end
  update chat_user set page=@page+1 where phone=@phone
  set @i=0
  set @msgBody = '房间里有:'
  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 + '正在等你,回复MS昵称邀请对方,回复MK看下一页'
  --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
end
else if upper(left(@Content,2)) = 'MA' 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) begin
   print 't'
  end
end
else if upper(left(@content,2)) = 'TQ' begin
  set @srcphone = left(@srcphone,6)
  set @msgBody = '没有找到这个城市。请回复TQ城市查询。例如：TQ杭州'
  if len(@content)>2 begin
   set @tempBody = substring(@Content,3,len(@content))
   if not exists(select top 1 * from weather where province=@tempBody) begin

    select 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 = '回复TQ城市查询城市天气预报.城市可以是:杭州金华宁波衢州丽水温州舟山台州湖州嘉兴绍兴。例如：TQ杭州'
  end
  --insert into chat_log (phone,srcphone,msgbody,roomid,tophone) values(@phone,@srcPhone,@content,@roomid,@phone)

  --插入广告
  set @tempBody = @msgBody
  if exists(select * from chat_clew where (len(content)<(68-len(@tempBody))) and (province=@province or province is null)) begin
   select top 1 @clew=content from chat_clew where (len(content)<(68-len(@tempBody))) and (province=@province or province is null) order by newid()
   set @tempBody = @tempBody + @clew
  end

  while (len( @tempBody ) > 0)
  begin
   insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,left(@tempBody,70),@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   set @tempBody = substring( @tempBody ,71,len(@tempBody))
  end
end
else if upper(left(@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='俏皮幽默' order by newid()
  insert into chat_log (phone,srcphone,msgbody,roomid,tophone) values(@phone,@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

end
else if @srcphone in ('2788101','8788101') or upper(left(@content,2))='XZ' begin
  exec mw @phone,@content,@srcphone,@province
end
             else if @srcphone in ('2788102','8788102') or upper(left(@content,2))='BH' begin
  exec BH @phone,@content,@srcphone,@province
end
else if @Content in ('1','2','3','4','5','6','7','8','9') begin--进入系统默认聊天室
  set @srcPhone = left(@srcPhone,6)
  if exists(select * from chat_room where id=@Content) begin
   update chat_user set roomid=@Content,state=1 where phone=@phone
   select @welcome=welcome from chat_room where id=@Content
   if @welcome is null begin
    set @welcome = ''
   end
   set @msgBody = @welcome
   --insert 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,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end
  end
end
/*
else if isnumeric(@Content) = 1 and @Content not in ('1','2','3','4','5','6','7','8','9') and len(@Content) < 4 begin  --进入自建聊天室
  set @srcPhone = left(@srcPhone,6)
  if exists(select * from chat_room where id=@Content) begin
   update chat_user set roomid=@Content,state=1 where phone=@phone
   select @welcome=welcome from chat_room where id=@Content
   if @welcome is null begin
    set @welcome = ''
   end
   set @msgBody = @welcome
   if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
    insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end
  end
end
else if upper(left(@Content,2)) = 'MF' begin
  set @srcPhone = left(@srcPhone,6)
  set @roomname = left(substring(@Content,3,len(@Content)),12)
  if exists(select * from chat_user where phone=@phone and state=1) begin
   if exists(select * from chat_room where phone=@phone) begin
    update chat_room set roomname=@roomname where phone=@phone
    select @roomid=id from chat_room where phone=@phone
    set @msgBody = '聊天室名称修改成功.回复' + ltrim(str(@roomid)) + '进入自己的房间回复MG+欢迎词修改自己房间的欢迎词'
   end
   else begin
    insert into chat_room (phone,roomname) values(@phone,@roomname)
    select @roomid=id from chat_room where phone=@phone
    set @msgBody = '您现在拥有自己的聊天室了.回复' + ltrim(str(@roomid)) + '进入自己的房间回复MF+房间名修改房间名回复MG+欢迎词修改自己房间的欢迎词'
   end

   if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
    insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end

  end
end
else if upper(left(@Content,2)) = 'MG' begin
  set @Content = replace(@Content,'+','')
  set @srcPhone = left(@srcPhone,6)
  set @welcome = left(substring(@Content,3,len(@Content)),65)
  if exists(select * from chat_user where phone=@phone) begin
   if exists(select * from chat_room where phone=@phone) begin
    update chat_room set welcome=@welcome where phone=@phone
    select @roomid=id from chat_room where phone=@phone
    set @msgBody = '房间欢迎词修改成功.回复' + ltrim(str(@roomid)) + '进入自己的房间'
   end
   else begin
    set @msgBody = '回复MF+房间名创建房间名回复MG+欢迎词修改自己房间的欢迎词'
   end
   if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
    insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end
  end
end
else if upper(@Content) = 'MY' begin--进入自己的房间
  set @srcPhone = left(@srcPhone,6)
  if exists(select * from chat_room where phone=@phone) begin
   update chat_user set roomid=(select id from chat_room where phone=@phone) where phone=@phone
   set @msgBody = '您已经进入自己的房间'
  end
  else begin
   set @msgBody = '没有创建'
  end
  if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
   insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
  end
end
else if upper(left(@Content,2)) = 'MT' begin--从自己的房间踢掉别人
  set @Content = replace(@Content,'+','')
  if exists(select * from chat_room where phone = @phone) begin
   if isnumeric(substring(@Content,3,len(@Content))) = 1 and exists(select * from chat_user where 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 exists(select * from chat_room where id=@roomid and phone=@phone) begin
    set @roomid = rand() * 7 + 1
    select @roomname=roomname from chat_room where id=@roomid
    update chat_user set roomid=@roomid where phone=@tophone
    set @msgBody = @nickname + "，欢迎来到欲望都市之'" + @roomname + "'聊天室，邂遇一场爱情，追求一段浪漫！回复MR看房间，回复MK找朋友，改昵称回复ME+性别+昵称"

    if not exists(select * from test_phone where phone=@tophone) begin
     if exists(select * from free_phone where phonenumber=@tophone and freesrvid='520LT') begin
      set @FeeCode = 0
      set @FeeType = 1
      set @FeeSevID = '520LT'
     end
     else if @Province='2371' begin
      set @FeeCode = 0
      set @FeeType = 1
      set @FeeSevID = 'LTS'
     end
     else if @Province='2571' begin
      set @FeeCode = 0
      set @FeeType = 1
      set @FeeSevID = '520LT'
     end
     else if @Province='571' begin
      set @FeeCode = 0
      set @FeeType = 1
      set @FeeSevID = 'YXG'
     end

     if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin

      insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
     end
    end
   end
   else begin
    set @msgBody = '您的房间里有这个人吗？我怎么没有找到呢？'
    select @SrcPhone=srcphone,@province=province from chat_user where phone=@phone
    if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @msgBody is not null begin
     insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
    end
   end
  end
  else begin
   set @msgBody = '您还没有创建自己的房间呢，怎么就踢别人啊？赶快回复MF+房间名称创建自己的房间吧！体验一下自己做管理员的滋味！！'
   if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @msgBody is not null begin
    insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end
  end
end
*/
else if upper(left(@Content,2)) = '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 = '不能跟自己聊天呀。还是找个人聊聊吧。'
   end
   else begin
    set @msgBody = '直接回复聊天内容即可与' + @nickname + '私聊，MC关闭/开启群聊，免别人打扰。'
   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,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end
  end
end
else if upper(@Content) = 'MR' begin
  set @srcPhone = left(@srcPhone,6)
  set @msgBody = ''
  declare yb cursor
  for select top 5 id,roomname,online from chat_room order by online desc
  open yb
  fetch next from yb into @roomid,@roomname,@online
  while (@@fetch_status=0)
  begin
   set @msgBody = @msgBody + ltrim(str(@roomid))+ '.' + @roomname + '(' + ltrim(str(@online)) + '人)' + char(13)
   fetch next from yb into @roomid,@roomname,@online
  end
  close yb
  deallocate yb
  set @msgBody = @msgBody + '回复房间编号进入.'
  if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin

  end
end
else if upper(left(@Content,2)) = 'MC' begin
  if charindex('开',@Content)>0 begin
   update chat_user set chat=1 where phone=@phone
  end
  else if charindex('关',@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
  end

  select @chat=chat from chat_user where phone=@phone
  set @msgBody = '您已关闭群聊功能，不接收群聊信息。回复MC开启群聊功能。'
  if @chat=1 begin
   set @msgBody = '您已开启群聊功能，接收群聊信息。回复MC关闭群聊功能。'
  end
  --insert 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
end
else if upper(left(@Content,2)) = 'MQ' begin--离开
  set @srcPhone = left(@srcPhone,6)
  update chat_user set state=0,roomid=null where phone=@phone
  set @msgBody = '迷失的感情，错乱的纠缠，交织的爱意...我决定退隐江湖，过一段隐居生活。这期间将收不到聊天信息。发送ME到' + @srcPhone + '重拾都市情缘'

  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+'离开聊天室',@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
/*
--查看用户信息
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 mtcode=substring(@Content,3,len(@Content))
  end
  else begin
   select @msgBody = phone from chat_user where nickname=substring(@Content,3,len(@Content))
  end
  if @debug<>1 and not exists(select * from test_phone where phone=@phone) and @msgBody is not null begin
   insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
  end

end
*/
else if len(@SrcPhone)>6 begin--私聊
  set @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 + '"' + '自言自语:' + @Content
   end
   else begin
    set @msgBody = "'" +@nickname + "'(" + @sex + ')悄悄对你说:' + @Content
   end

set @srcPhone = left(@srcphone,6) + ltrim(str(@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))) and (province=@province or province is null)) begin
     select top 1 @clew=content from chat_clew where (len(content)<(68-len(@msgBody))) and (province=@province or province is null) order by newid()
     set @msgBody = @msgBody + @clew
    end

    if not exists(select * from test_phone where phone=@tophone) begin
     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--屏蔽联通
      while (len( @msgBody ) > 0)
      begin
       insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,left(@msgBody,70),@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
       set @msgBody = substring( @msgBody ,71,len(@msgBody))
      end
     end
    end
    select @tonickname = nickname from chat_user where phone=@tophone
    if @phone=@tophone begin
     set @msgBody = '"' + @nickname + '"' + '自言自语:' + @Content
    end
    else begin
     set @msgBody = "'" +@nickname + "'(" + @sex + ")对'" + @tonickname + "'悄悄说:" + @Content
    end
    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
  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) begin

   set @msgBody = + '"' + @nickname + '(' + @sex + ')"说:' + @Content

   if @msgBody <> null begin
    insert into chat_log (phone,srcphone,msgbody,roomid) values(@phone,@srcPhone,@msgBody,@roomid)
   end

   declare yb cursor
   for select phone from chat_user where roomid=@roomid and phone<>@phone and state=1 and chat=1
   open yb
   fetch next from yb into @tophone
   while (@@fetch_status=0)
   begin
    select @srcphone=srcphone,@province=province from chat_user where phone=@tophone

if not exists(select * from test_phone where phone=@tophone) begin

exec fun_sevid @tophone,@province,@FeeCode output,@FeeType output,@FeeSevID output
if @debug<>1 and not exists(select * from test_phone where phone=@tophone) begin

      --插入广告
      set @tempBody = @msgBody
      if exists(select top 1 * from chat_user where phone=@tophone and fraction<5) and len(@tempBody)<60 begin
       set @tempBody = @tempBody + '(温馨提示:回复MQ离开聊天室)'
      end
      else if exists(select * from chat_clew where (len(content)<(68-len(@tempBody))) and (province=@province or province is null)) begin
       select top 1 @clew=content from chat_clew where (len(content)<(68-len(@tempBody))) and (province=@province or province is null) order by newid()
       set @tempBody = @tempBody + @clew
      end

      while (len( @tempBody ) > 0)
      begin
       insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,left(@tempBody,70),@tophone,@FeeSevID,@FeeType,@SrcPhone,@toPhone,1,0,1,2,@LinkID)
       set @tempBody = substring( @tempBody ,71,len(@tempBody))
      end
     end
    end
    fetch next from yb into @tophone
   end
   close yb
   deallocate yb
  end
  if @roomid=null begin
   set @msgBody = '回复编号进入房间' + char(13)
   declare yb cursor
   for select top 4 id,roomname,online from chat_room order by newid()
   open yb
   fetch next from yb into @roomid,@roomname,@online
   while (@@fetch_status=0)
   begin
    set @msgBody = @msgBody + ltrim(str(@roomid))+ '.' + @roomname + '(' + ltrim(str(@online)) + '人)' + char(13)
    fetch next from yb into @roomid,@roomname,@online
   end
   close yb
   deallocate yb
   if @debug<>1 and not exists(select * from test_phone where phone=@phone) begin
    insert into AirLinkMT(OuQ_Date,MsgFmt,Province,Service,FeeCode,MsgBody,DestPhone,FeeSevID,FeeType,SrcPhone,FeePhone,Priority,MsgCode,ReportFlag,MTType,LinkID) values(getdate(),1,@Province,1,@FeeCode,@msgBody,@phone,@FeeSevID,@FeeType,@SrcPhone,@Phone,1,0,1,2,@LinkID)
   end
  end
end
end
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET 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
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

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

exec sp_addextendedproperty N'MS_Description', N'服务号（移动2788，联通8788）', N'user', N'dbo', N'table', N'chat_user', N'column', N'srcPhone'

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'