?? 短信跟蹤系統服務程序建庫腳本.sql

?? 太陽神短信跟蹤服務系統
?? SQL
?? 第 1 頁 / 共 2 頁
字號:
12 下一頁
--短信跟蹤系統服務程序

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)
12 下一頁
?? 文件大小 173 K
?? 上傳用戶 fengkuangyidao
?? 所屬分類手機短信編程
??? 相關標簽

#短信 #服務系統
?? 快捷鍵說明

復制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號 Ctrl + =
減小字號 Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? 短信跟蹤系統服務程序建庫腳本.sql

?? 快捷鍵說明
