触发手机短信订阅功能

首先需要买一个短信猫,一般都会提供解决方案

 

代码

USE   [ WXT_SYS_BASIC ]
GO
/* ***** Object:  StoredProcedure [dbo].[MsgSed]    Script Date: 08/19/2010 17:28:53 ***** */
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
ALTER   proc   [ dbo ] . [ MsgSed ]
as
declare  cur  cursor
read_only
for   select   [ uid ]   from  UserMessage 

declare   @uid   int
open  cur

fetch   next   from  cur  into   @uid
while  ( @@fetch_status   = 0 )
begin
declare   @Phone   nvarchar ( 100 )
declare   @ToDayAccpetCount   nvarchar ( 100 )
declare   @ToDateFinCount   nvarchar ( 100 )
declare   @ToPrice   nvarchar ( 100 )
declare   @Content   nvarchar ( Max )
declare   @SaleContent   nvarchar ( Max )
declare   @SaleToDayAccpetCount   nvarchar ( 100 )
declare   @SaleToDayBackCount   nvarchar ( 100 )
declare   @SalePrice   nvarchar ( 100 )
set   @Phone = ''
set   @ToPrice = ' 0 '
set   @SalePrice = ' 0 '
select   @Phone = uphone  from  UserMessage  where  uid = @uid
select   @ToDayAccpetCount = COUNT ( * )   from  InService  where  uid = @uid   and   DATEADD ( day , DATEDIFF ( day , 0 ,wxDate), 0 ) = DATEADD ( day , DATEDIFF ( day , 0 , GETDATE ()), 0 ) 
select   @ToDateFinCount = COUNT ( * )  from  InService  where  uid = @uid   and   DATEADD ( day , DATEDIFF ( day , 0 ,wxwDate), 0 ) = DATEADD ( day , DATEDIFF ( day , 0 , GETDATE ()), 0 ) 
select   @ToPrice = fabPrice - fabCost   from  InService  where  uid = @uid   and   DATEADD ( day , DATEDIFF ( day , 0 ,wxwDate), 0 ) = DATEADD ( day , DATEDIFF ( day , 0 , GETDATE ()), 0 ) 

select   @SaleToDayAccpetCount = COUNT ( * )  from  SalesInfo  where  uid = @uid   and   DATEADD ( day , DATEDIFF ( day , 0 ,salesDate), 0 ) = DATEADD ( day , DATEDIFF ( day , 0 , GETDATE ()), 0 ) 
select   @SaleToDayBackCount = COUNT ( * )  from  SalesInfo  where  uid = @uid   and   DATEADD ( day , DATEDIFF ( day , 0 ,salesBackDate), 0 ) = DATEADD ( day , DATEDIFF ( day , 0 , GETDATE ()), 0 )  and  salesBackType = ' 已退款 '
select   @SalePrice = salesPrice - salesCost  from  SalesInfo  where  uid = @uid   and   DATEADD ( day , DATEDIFF ( day , 0 ,salesDate), 0 ) = DATEADD ( day , DATEDIFF ( day , 0 , GETDATE ()), 0 )  and  salesBackType != ' 已退款 '

set   @Phone = @Phone + ' @sms.xunsai.net '
set   @Content = ' 【XXX提醒您：今日受理 ' + @ToDayAccpetCount + ' 人，维修完成 ' + @ToDateFinCount + ' 人，今日维修毛利 ' + @ToPrice + ' 元；请勿回复本短信，欢迎您继续使用XXX】 '
set   @SaleContent = ' 【XXX提醒您：今日销售 ' + @SaleToDayAccpetCount + ' 单，退款 ' + @SaleToDayBackCount + ' 单，销售毛利 ' + @SalePrice + ' 元，请勿回复本短信，欢迎您继续使用XXX】 '

execute  msdb.dbo.sp_send_dbmail  @profile_name = ' SMS ' ,
@recipients = @Phone ,
@subject = ' MSG ' ,
@body = @Content
execute  msdb.dbo.sp_send_dbmail  @profile_name = ' SMS ' ,
@recipients = @Phone ,
@subject = ' MSG ' ,
@body = @SaleContent
fetch   next   from  cur  into   @uid
end
close  cur
deallocate  cur

 

 最后做下计划任务就完成了

转载于:https://www.cnblogs.com/dthom/articles/1803743.html