日期:2014-05-17 浏览次数:20541 次
USE DP6_Siemens
GO
ALTER PROCEDURE [dbo].[Siemens_ReRemind_XS]
@BeginTime VARCHAR(10) ,
@EndTime VARCHAR(10) ,
@Corpid INT
AS
BEGIN
CREATE TABLE #aa(memberId INT ,stordid VARCHAR(500))
INSERT INTO #aa
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
(SELECT CAST(StoreId AS NVARCHAR(max))+N'店,' FROM dbo.DP_MemberStore WHERE MemberId=AA.MemberId FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA
GROUP BY AA.MemberId
) B
--要发出的信息
INSERT INTO DP_SmsOutQueue(CorpId , Mobile , [Content] , SendChannel , AttemptTimes , Operator)
SELECT @Corpid AS Corpid ,
dbo.GetMobileByMemberId(a.MemberId) AS Mobile ,
'西门子dp平台提醒:你好,你还有'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店未上报上月的进货额,请在今天内上报,多谢合作!未上报门店为:'+CAST(ISNULL(a.CC,0)AS VARCHAR(max))+ '。' AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),@Corpid) AS SendChannel ,
0 AS AttemptTimes ,
'Siemens_ReRemind_XS' AS Operator
--发出信息的参数
--发出信息对应的会员ID
FROM (SELECT tt.MemberId,
--未上报XS会员对应为上报的门店数
COUNT (DISTINCT CASE WHEN tt.MemberId like '%1%' THEN tt.storeId end ) AS AA,
--列出未上报的门店ID
#aa.stordid AS CC
FROM dbo.DP_MemberStore tt
LEFT JOIN #aa ON tt.MemberId = #aa.memberId
LEFT JOIN dbo.DP_Members pp ON tt.CorpId = pp.CorpId AND tt.MemberId = pp.MemberId
WHERE tt.CorpId=@Corpid AND pp.Status=2 AND pp.RoleId=7
--排除上报了XS任务的会员