短信跟踪系统服务程序建库脚本.sql

太阳神短信跟踪服务系统

--短信跟踪系统服务程序

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TMsg_SendList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TMsg_SendList]
GO

CREATE TABLE [dbo].[TMsg_SendList] (
	[Id] [int] IDENTITY (1, 1) NOT NULL ,   
	[SendtoMobile] [varchar] (1200) NOT NULL ,  --发送到手机号(多个号码以","分隔 最多100个号码)
	[MobileType] [int] NULL ,                   --电话类型 1,手机，2小灵通
	[SendTime] [datetime] NOT NULL ,            --发送时间
	[SendContent] [varchar] (140) NOT NULL ,    --发送内容(最大字符数量70个中文字符或140个英文字符)
	[SendSeason] [varchar] (50) NULL ,          --发送理由
	[FromOperator] [varchar] (30) NULL ,        --发送操作员
	[SendState] [int] NOT NULL default (3)      --发送状态(3新增,0已发送,1TCP方式登录,2HTTP方式登录,3新增,-1EPID错误,)[详见短信状态信息表]
	primary key(id)
) ON [PRIMARY]
GO


--短信息内容表
drop table TMsg_msgContent

CREATE TABLE [dbo].[TMsg_msgContent] (
	[Id] [int] IDENTITY (1, 1) NOT NULL ,
	[msgType] [varchar] (50) NOT NULL ,
	[msgContent] [varchar] (300) NOT NULL 
) ON [PRIMARY]
GO

insert into TMsg_msgContent(msgType,msgContent) values('新加入','欢迎加入太阳神健康事业,正确的选择是成功的基础。我们会和您一起努力！真诚的祝愿我们合作愉快、事业发达！深圳太阳神')

insert into TMsg_msgContent(msgType,msgContent) values('生日祝福','您好，今天是您的生日，太阳神全体员工祝您生日快乐，永远健康!')
insert into TMsg_msgContent(msgType,msgContent) values('节日祝福','您好，太阳神全体员工祝您节日快乐，永远健康!')


--发送规则（短信息参数设置）
drop table TMsg_SendRule

CREATE TABLE [dbo].[TMsg_SendRule] (
	[ID] [int] IDENTITY (1, 1) NOT NULL,   --参数名
	[RuleName] [varchar] (100) NOT NULL,    --参数值
	[RuleValue] [varchar] (100) NULL 
) ON [PRIMARY]
GO


--select len(msgContent) from TMsg_msgContent
select * from TMsg_msgContent


--短信息发送状态表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TMsg_SendState]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TMsg_SendState]
GO

CREATE TABLE [dbo].[TMsg_SendState] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[StateCode] [int] NOT NULL ,
	[StateValue] [varchar] (50) NOT NULL 
	primary key(StateCode)
) ON [PRIMARY]
GO

insert into TMsg_SendState(StateCode,StateValue) values(3, '新增')
insert into TMsg_SendState(StateCode,StateValue) values(0, '正常')
insert into TMsg_SendState(StateCode,StateValue) values(1, 'TCP方式登录')
insert into TMsg_SendState(StateCode,StateValue) values(2, 'HTTP方式登录')
insert into TMsg_SendState(StateCode,StateValue) values(-1, 'EPID错误')
insert into TMsg_SendState(StateCode,StateValue) values(-2, '无该用户')
insert into TMsg_SendState(StateCode,StateValue) values(-3, '注册码错')
insert into TMsg_SendState(StateCode,StateValue) values(-4, '用户被停用')
insert into TMsg_SendState(StateCode,StateValue) values(-5, '未注册成功')
insert into TMsg_SendState(StateCode,StateValue) values(-6, '超出使用日期')
insert into TMsg_SendState(StateCode,StateValue) values(-7, '费用不足')
insert into TMsg_SendState(StateCode,StateValue) values(-8, '源手机错误')
insert into TMsg_SendState(StateCode,StateValue) values(-9, '目的手机错误')
insert into TMsg_SendState(StateCode,StateValue) values(-10, '信息内容错误')
insert into TMsg_SendState(StateCode,StateValue) values(-11, '连接失败')
insert into TMsg_SendState(StateCode,StateValue) values(-12, '系统内部错误或者无效的客户状态')
insert into TMsg_SendState(StateCode,StateValue) values(-13, '客户权限不对')
insert into TMsg_SendState(StateCode,StateValue) values(-14, '不是从指定的IP处登录')
insert into TMsg_SendState(StateCode,StateValue) values(-15, '帐号已经登录(TCP)')
insert into TMsg_SendState(StateCode,StateValue) values(-16, '内部通讯错误')
insert into TMsg_SendState(StateCode,StateValue) values(-17, '无可用的MT通道')
insert into TMsg_SendState(StateCode,StateValue) values(-18, '不支持该功能')
insert into TMsg_SendState(StateCode,StateValue) values(-20, '未知错误')



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PMsg_GetMobile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PMsg_GetMobile]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-------脚本名称：提取满足条件的手机号--------------------------------
-------描    述：from from TB_BusinessInfo to TMsg_SendList
-------作    者：熊正浩
-------创建时间：2006-02-22
-------修改时间：2006-05-30
-------版    本：v2.0

CREATE PROCEDURE  PMsg_GetMobile
--@CurrentWeek int, 
--@1TotalScore float,
--@4TotalScore float,
--@9totalScore float,
--@16totalScore float,
--@RemindPercent float,

--@SendRang char(8)  --发送范围:新加入|冲剌提醒|瓶颈期|物流|获权|生日祝福|节日祝福|考核提醒
AS

declare @currentWeek int 
declare @1TotalScore float
declare @4TotalScore float
declare @9totalScore float
declare @16totalScore float
declare @RemindPercent float
declare @SendRang char(8)

set @currentWeek=(select max(weekcode) from TB_CalcuID)
set @1TotalScore=3000
set @4TotalScore=9000
set @9TotalScore=24000
set @16TotalScore=45000
set @RemindPercent=0.15
set @SendRang='10010100'
--set @SendRang='11111111'

--提取新加入祝贺
if (SUBSTRING(@SendRang,1,1)='1')
begin
	insert into TMsg_SendList(
	SendtoMobile,
	MobileType,
	SendTime,
	SendContent,
	SendSeason,
	FromOperator
	)
	select distinct
	mobile,
	1,
	STUFF(getDate()+1,12,7,'10:00AM'),
	BusinessName + (case when sex='男' then '先生' when sex='女' then '女士' end) + 
	(select msgContent from TMsg_msgContent where msgType='新加入'),
	'新加入',
	'system'
	from dbo.TB_BusinessInfo where calcuid=0 and IsAuditPass=1 and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12)
	and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='新加入')
end

--冲剌提醒
if (SUBSTRING(@SendRang,2,1)='1')
begin
	--提取即将获1个骨干权数的人手机号
	insert into TMsg_SendList(
	SendtoMobile,
	MobileType,
	SendTime,
	SendContent,
	SendSeason,
	FromOperator
	)
	select distinct
	Mobile,
	1,
	STUFF(getDate()+1,12,7,'10:00AM'),
	BusinessName +(case when sex='男' then '先生' when sex='女' then '女士' end) +
	(select msgContent from TMsg_msgContent where msgType='冲剌前提醒1'),
	'冲剌前提醒1',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=(@1TotalScore*@RemindPercent) and TotalScore<@1TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='冲剌前提醒1') and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12) 

	--提取即将获4个骨干权数的人手机号
	insert into TMsg_SendList(
	SendtoMobile,
	MobileType,
	SendTime,
	SendContent,
	SendSeason,
	FromOperator
	)
	select distinct
	Mobile,
	1,
	STUFF(getDate()+1,12,7,'10:00AM'),
	BusinessName +(case when sex='男' then '先生' when sex='女' then '女士' end) +
	(select msgContent from TMsg_msgContent where msgType='冲剌前提醒2'),
	'冲剌前提醒2',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=(@4TotalScore*@RemindPercent) and TotalScore<@4TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='冲剌前提醒2') and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12) 

	--取即将获9个骨干权数的人手机号
	insert into TMsg_SendList(
	SendtoMobile,
	MobileType,
	SendTime,
	SendContent,
	SendSeason,
	FromOperator
	)
	select distinct
	Mobile,
	1,
	STUFF(getDate()+1,12,7,'10:00AM'),
	BusinessName +(case when sex='男' then '先生' when sex='女' then '女士' end) +
	(select msgContent from TMsg_msgContent where msgType='冲剌前提醒3'),
	'冲剌前提醒3',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=(@9TotalScore*@RemindPercent) and TotalScore<@9TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='冲剌前提醒3') and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12)