sp.txt

通过网关发手机短信

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter  proc [dbo].[CK_Flow_Sms] 

@ObjType int, 				
@FTaskID int,
@FDataBase Nvarchar(50)
 
AS

begin
  declare @Table nvarchar (20)
  declare @TableName nvarchar (50) 
  declare @Fdocentry  int
  declare @FStep  int
  declare @FlowName nvarchar (50) 
  declare  @Content  nvarchar (200)
  declare @Sql nvarchar (4000)

Select @TableName =Case @ObjType When  23 then  N'报价单'
                                When 17 then N'订单'
                                When 15 then N'交货单'
                                When 13 then N'发票'
                                When 14 then N'贷项凭证'
                                When 16 then N'退货'
                                When 22 then N'采购订单'
                                When 20 then N'采购交货'
                                When 18 then N'采购发票'
                                When 19  then N'采购贷项凭证'
                                When 21  then  N'采购退货'
                                When 67  then  N'库存转储'
end ,
  @Table =Case @ObjType When  23 then  N'OQUT'
                                When 17 then N'ORDR'
                                When 15 then N'ODLN'
                                When 13 then N'OINV'
                                When 14 then N'ORIN'
                                When 16 then N'ORDN'
                                When 22 then N'OPOR'
                                When 20 then N'OPDN'
                                When 18 then N'OPCH'
                                When 19  then N'ORPC'
                                When 21  then  N'ORPD'
                                When 67  then  N'OWTR'
end 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

select Top 1 @Fdocentry= FDocKey,@FlowName=FCheckFlowName,@FStep=FNextStep
from ck_flow_task 
where FtaskId=@FTaskID
select @Sql= N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  if exists(select top 1 1   from sysobjects where  name=N''FTemp'')  drop table  FTemp '
select @Sql=@Sql + N' Select Top 1 CardCode,CardName,DocNum,U_Opname,DocTotal  into FTemp From ' +@FDataBase+N'..odrf'
             +N' where docentry='    +cast(@Fdocentry  as nvarchar)
 
exec(@Sql)

if exists(select top 1 1   from sysobjects where  name=N'FTemp') 
begin

select @Content=N'审批流程 '+''''+@FlowName+''''+N'：由' +isnull(U_Opname,N'') +N'做的,合作伙伴 '+isnull(CardCode,N'')+N' '+ isnull(CardName,N'') +
  N',金额：'+ cast( DocTotal as nvarchar)+N' 单号:' + cast(DocNum as nvarchar)  +N'的' + isnull(@TableName,N'') +N'要求你审批.请回复'+cast(@FTaskID  as nvarchar) +N'SP 加字母Y或N 进行审批'
from  FTemp

   drop table FTemp
end 

declare @j int
declare @s nvarchar(100)
select @j=0
while  @j< len(@Content)/69 +1
begin
  select @s=substring(@Content,@j*69+1,@j*69+69)


   insert T_SMSSendList( S_ReceiveCardNo ,S_SendContent )

   select U.Fphone ,@s
   from  userinfo U ,ck_flow_step  S
   where  CharIndex(U.FUserCode, S.FCheckers) > 0 
   and S.FstepId= @FStep
   and isnull(@s,'')<>''
   and isnull(Fphone,'')<>''

   select @j=@j+1
end


end