isag_ismg.sql

电信ISMG网关程序,为电信综合业务网关,20090102发布.小

ALTER TABLE [dbo].[notifyManagementInfo] ADD 
	CONSTRAINT [DF_notifyManagementInfo_moDate] DEFAULT (getdate()) FOR [moDate]
GO

ALTER TABLE [dbo].[notifySmsDeliveryReceipt] ADD 
	CONSTRAINT [DF_notifySmsDeliveryReceipt_moDate] DEFAULT (getdate()) FOR [moDate]
GO

ALTER TABLE [dbo].[notifySmsReception] ADD 
	CONSTRAINT [DF_notifySmsReception_moDate] DEFAULT (getdate()) FOR [moDate]
GO

ALTER TABLE [dbo].[orderRelationUpdateNotifyReq] ADD 
	CONSTRAINT [DF_orderRelationUpdateNotifyReq_moDate] DEFAULT (getdate()) FOR [moDate]
GO

ALTER TABLE [dbo].[sendPush] ADD 
	CONSTRAINT [DF_sendWappush_mtDate] DEFAULT (getdate()) FOR [mtDate],
	CONSTRAINT [DF_sendWappush_mtHashCode] DEFAULT (0) FOR [mtHashCode],
	CONSTRAINT [DF_sendWappush_mtSendCount] DEFAULT (0) FOR [mtSendCount]
GO

ALTER TABLE [dbo].[sendPushHistory] ADD 
	CONSTRAINT [DF_sendPushHistory_mtDate_1] DEFAULT (getdate()) FOR [mtDate],
	CONSTRAINT [DF_sendPushHistory_mtHashCode_1] DEFAULT (0) FOR [mtHashCode],
	CONSTRAINT [DF_sendPushHistory_mtSendCount_1] DEFAULT (0) FOR [mtSendCount]
GO

ALTER TABLE [dbo].[sendSms] ADD 
	CONSTRAINT [DF_sendSms_mtDate] DEFAULT (getdate()) FOR [mtDate],
	CONSTRAINT [DF_sendSms_mtHashCode] DEFAULT (0) FOR [mtHashCode],
	CONSTRAINT [DF_sendSms_mtSendCount] DEFAULT (0) FOR [mtSendCount]
GO

ALTER TABLE [dbo].[sendSmsHistory] ADD 
	CONSTRAINT [DF_sendSmsHistroy_mtDate] DEFAULT (getdate()) FOR [mtDate],
	CONSTRAINT [DF_sendSmsHistroy_mtHashCode] DEFAULT (0) FOR [mtHashCode],
	CONSTRAINT [DF_sendSmsHistroy_mtSendCount] DEFAULT (0) FOR [mtSendCount]
GO

ALTER TABLE [dbo].[serviceConsumeNotifyReq] ADD 
	CONSTRAINT [DF_serviceConsumeNotifyReq_moDate] DEFAULT (getdate()) FOR [moDate]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC sp_notifyManagementInfo
  @streamingNo	varchar(50),
  @ID		varchar(50),
  @IDType	int,
  @status	int
AS

  INSERT INTO notifyManagementInfo
     (streamingNo, ID, IDType, status)
  VALUES
     (@streamingNo, @ID, @IDType, @status)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC  sp_notifySmsDeliveryReceipt
  @spRevId		varchar(50),
  @spRevpassword	varchar(50),
  @spId			varchar(50),
  @SAN			varchar(50),
  @transactionId	varchar(50),
  @linkId		varchar(50),
  @correlator		varchar(50),
  @address		varchar(50),
  @deliveryStatus	varchar(50)
AS

  INSERT INTO notifySmsDeliveryReceipt
     (spRevId, spRevpassword, spId, SAN, transactionId, linkId, correlator, address, deliveryStatus)
  VALUES
     (@spRevId, @spRevpassword, @spId, @SAN, @transactionId, @linkId, @correlator, @address, @deliveryStatus)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  PROC sp_notifySmsReception
  @spRevId			varchar(50),
  @spRevpassword		varchar(50),
  @spId				varchar(50),
  @SAN				varchar(50),
  @transactionId		varchar(50),
  @linkId			varchar(50),
  @registrationIdentifier	varchar(50),
  @message			varchar(160),
  @senderAddress		varchar(50),
  @smsServiceActivationNumber	varchar(50)
AS

  INSERT INTO notifySmsReception
     (spRevId, spRevpassword, spId, SAN, transactionId, linkId, registrationIdentifier,
      message, senderAddress, smsServiceActivationNumber)
  VALUES
     (@spRevId, @spRevpassword, @spId, @SAN, @transactionId, @linkId, @registrationIdentifier,
      @message, @senderAddress, @smsServiceActivationNumber)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC sp_orderRelationUpdateNotifyReq
  @streamingNo	varchar(100),
  @productID	varchar(50),
  @packageID	varchar(50),
  @userID	varchar(50),
  @userIDType	int,
  @OPType	int
AS 

  INSERT INTO orderRelationUpdateNotifyReq
     (streamingNo, productID, packageID, userID, userIDType, OPType) 
  VALUES
     (@streamingNo, @productID, @packageID, @userID, @userIDType, @OPType) 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROC sp_sendHistory
  @mtSendType		varchar(10),
  @RecordID		int,
  @mtResult		varchar(50),
  @mtMessageId		varchar(50),
  @mtText		varchar(500)
AS
  IF @mtSendType = 'SMS'
  BEGIN 
    INSERT INTO sendSmsHistory
       (
        mtDate, mtHashCode, mtSendCount, SH_productId, SH_SAN, SH_transactionId, SH_transEnd, 
        SH_linkID, SH_OA, SH_FA, addresses, senderName, CI_description, CI_currency, CI_amount,
        CI_code, message, SR_endpoint, SR_interfaceName, SR_correlator, mtResult,mtMessageId,mtText
       ) 
    SELECT 
        mtDate, mtHashCode, mtSendCount, SH_productId, SH_SAN, SH_transactionId, SH_transEnd, 
        SH_linkID, SH_OA, SH_FA, addresses, senderName, CI_description, CI_currency, CI_amount,
        CI_code, message, SR_endpoint, SR_interfaceName, SR_correlator, @mtResult,@mtMessageId,@mtText
    FROM  sendSms WHERE RecordID = @RecordID

    DELETE FROM sendSms WHERE RecordID = @RecordID
  END 

  IF @mtSendType = 'WAPPUSH'
  BEGIN 
    INSERT INTO sendPushHistory
       (        
        mtDate, mtHashCode, mtSendCount, SH_productId, SH_SAN, SH_transactionId, SH_transEnd, 
        SH_linkID, SH_OA, SH_FA, addresses, senderAddress, targetURL, subject, 
        SR_endpoint, SR_interfaceName, SR_correlator, mtResult,mtMessageId,mtText
       ) 
    SELECT 
        mtDate, mtHashCode, mtSendCount, SH_productId, SH_SAN, SH_transactionId, SH_transEnd, 
        SH_linkID, SH_OA, SH_FA, addresses, senderAddress, targetURL, subject, 
        SR_endpoint, SR_interfaceName, SR_correlator, @mtResult,@mtMessageId,@mtText
    FROM  sendPush WHERE RecordID = @RecordID

    DELETE FROM sendPush WHERE RecordID = @RecordID
  END 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC sp_sendLoading
  @mtSendType		varchar(10),
  @mtSendCount		int,
  @mtRetrySendTime	int,--重发分钟
  @mtAutoClearError	Char(1),
  @mtHashCode		bigint /*临时会话代码*/
AS
  IF @mtSendType = 'SMS'
  BEGIN    
    /*自动清理发送失败的记录*/
    IF @mtAutoClearError = '1'
    BEGIN
      DELETE FROM sendSms WHERE mtSendCount >= @mtSendCount
    END

    /*取发信数据*/
    IF @mtSendCount < 1 SET @mtSendCount = 1
    SELECT TOP 300 * FROM sendSms WHERE  
       ((mtSendCount < @mtSendCount) OR (mtDate < DATEADD(minute,-@mtRetrySendTime,GetDate()))) AND (mtHashCode <> @mtHashCode)
  END

  IF @mtSendType = 'WAPPUSH'
  BEGIN    
    /*自动清理发送失败的记录*/
    IF @mtAutoClearError = '1'
    BEGIN
      DELETE FROM sendPush WHERE mtSendCount >= @mtSendCount
    END

    /*取发信数据*/
    IF @mtSendCount < 1 SET @mtSendCount = 1
    SELECT TOP 300 * FROM sendPush WHERE  
       ((mtSendCount < @mtSendCount) OR (mtDate < DATEADD(minute,-@mtRetrySendTime,GetDate()))) AND (mtHashCode <> @mtHashCode)
  END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROC sp_sendUpdate
  @mtSendType		varchar(10),
  @RecordID		int,
  @mtHashCode		bigint, /*临时会话代码*/
  @IsBlackMobile 	char(1),/*0 正常  1 目的号码是黑名单*/
  @HasBadword    	char(1) /*0 正常  1 短信内容有非法字存在,被过滤*/
AS
  IF @mtSendType = 'SMS'
  BEGIN
    IF @IsBlackMobile  = '1' 
    BEGIN
      /*删除目的号码是黑名单*/
      DELETE FROM sendSms  WHERE RecordID = @RecordID
    END ELSE
    BEGIN
      UPDATE sendSms SET mtHashCode = @mtHashCode WHERE RecordID = @RecordID
    END
  END

  IF @mtSendType = 'WAPPUSH'
  BEGIN
    IF @IsBlackMobile  = '1' 
    BEGIN
      /*删除目的号码是黑名单*/
      DELETE FROM sendPush  WHERE RecordID = @RecordID
    END ELSE
    BEGIN
      UPDATE sendPush SET mtHashCode = @mtHashCode WHERE RecordID = @RecordID
    END
  END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC sp_serviceConsumeNotifyReq
  @streamingNo	varchar(50),
  @userID	varchar(50),
  @userIDType	int,
  @productID	varchar(50),
  @linkID	varchar(50),
  @featureStr	varchar(50)
AS 

  INSERT INTO serviceConsumeNotifyReq
     (streamingNo, userID, userIDType, productID, linkID, featureStr)
  VALUES
     (@streamingNo, @userID, @userIDType, @productID, @linkID, @featureStr)

 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO