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

太阳神短信跟踪服务系统

	--取即将获16个骨干权数的人手机号
	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='冲剌前提醒4'),
	'冲剌前提醒4',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=(@16TotalScore*@RemindPercent) and TotalScore<@16TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='冲剌前提醒4') and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12) 

end

--瓶颈期
if((SUBSTRING(@SendRang,3,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 TB_BusinessInfo  where BusinessCode in 
	(select BusinessCode from dbo.TB_BusinessInfo_His where calcuid<=@CurrentWeek and calcuid>@CurrentWeek-26 and isAuditpass=1 and applydate<(select BeginDate from TB_CalcuID where WeekCode=@CurrentWeek-15) group by BusinessCode having sum(isnull(FactTruePayMoney,0))=0) 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='半年无业绩')

	--提取3月无业绩的人手机号
	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 TB_BusinessInfo  where BusinessCode in 
	(select BusinessCode from dbo.TB_BusinessInfo_His where calcuid<=@CurrentWeek and calcuid>(@CurrentWeek-12) and isAuditpass=1 and applydate<(select BeginDate from TB_CalcuID where WeekCode=@CurrentWeek-11) group by BusinessCode having sum(isnull(FactTruePayMoney,0))=0) 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='半年无业绩' or SendSeason='3月无业绩') 

	--提取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 TB_BusinessInfo  where BusinessCode in 
	(select BusinessCode from dbo.TB_BusinessInfo_His where calcuid<=@CurrentWeek and calcuid>(@CurrentWeek-4) and isAuditpass=1 and applydate<(select BeginDate from TB_CalcuID where WeekCode=@CurrentWeek-3) group by BusinessCode having sum(isnull(FactTruePayMoney,0))=0) 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='半年无业绩' or SendSeason='3月无业绩' or SendSeason='1月无业绩') 
end


--物流短信
--if (SUBSTRING(@SendRang,4,1)='1')
--begin
--end

--提取获骨干权数
if (SUBSTRING(@SendRang,5,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 and TotalScore<@4TotalScore 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
	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='获4个骨干权数'),
	'获4个骨干权数',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=@4TotalScore and TotalScore<@9TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='获4个骨干权数') 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='获9个骨干权数'),
	'获9个骨干权数',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=@9TotalScore and TotalScore<@16TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='获9个骨干权数') and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12) 

	--提取获16个骨干权数的人手机号
	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='获16个骨干权数'),
	'获16个骨干权数',
	'system'
	from dbo.TB_BusinessInfo where TotalScore>=@16TotalScore and Mobile not in(select Sendtomobile from TMsg_SendList where SendSeason='获16个骨干权数') and (mobile like '13%' or mobile like '013%') and (LEN(mobile)=11 or LEN(mobile)=12) 
end

--生日祝福
if (SUBSTRING(@SendRang,6,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 TB_BusinessInfo where
	(case when len(Idcode)=15 then substring(Idcode,9,4) when len(idcode)=18 then substring(Idcode,11,4) end)=
	(case when len(LTRIM(STR(MONTH(getdate()+1))))=1 then '0'+ LTRIM(STR(MONTH(getdate()+1))) when len(LTRIM(STR(MONTH(getdate()+1))))=2 then LTRIM(STR(MONTH(getdate()+1))) end) + (case when len(LTRIM(STR(DAY(getdate()+1))))=1 then '0'+ LTRIM(STR(DAY(getdate()+1))) when len(LTRIM(STR(DAY(getdate()+1))))=2 then LTRIM(STR(DAY(getdate()+1))) end)
	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,7,1)='1')
--begin
--end

--考核提醒
--if (SUBSTRING(@SendRang,8,1)='1')
--begin
--end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO