?? 短信跟蹤系統服務程序建庫腳本.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
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -