日期:2014-05-17 浏览次数:20558 次
USE DP6_Siemens
GO
ALTER PROCEDURE [dbo].[Siemens_Confirm_XS]
@BEGINTIME DATETIME ,
@ENDTIME DATETIME,
@CorpId INT
AS
BEGIN
--要发出的信息内容
INSERT INTO DP_SMSOUTQUEUE(CORPID , MOBILE , CONTENT , SendChannel , AttemptTimes , Operator)
SELECT CORPID=@CorpId ,
dbo.GetMobileByMemberId(a.MemberId) AS MOBILE ,
+'西门子dp平台核实:你好,你昨日共上报'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店的月进货额,总进货额'+CAST(ISNULL(a.BB,0)AS VARCHAR(10))+'元,已上报门店为:'+CAST(ISNULL(a.CC,0)AS VARCHAR(10))
AS CONTENT ,
dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),1) AS SENDCHANNEL ,
0 AS ATTEMPTTIMES ,
'Siemens_Confirm_XS' AS OPERATOR
--内容的值
FROM ( SELECT dce.MemberId ,
COUNT(DISTINCT CASE dce.Instruction
WHEN 'XS' THEN dce.StoreId
END) AA,
SUM(CASE dce.Instruction
WHEN 'XS' THEN CAST(dce.Param1 AS INT)
END) BB,
(SELECT NIMEI FROM (
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
(SELECT StoreId+' ' FROM dbo.DP_MemberS