MOBILE_API/DB/SPs/PROC_WSB_PAYMENT_STOP.sql

CREATE PROC [dbo].[PROC_WSB_PAYMENT_STOP]  
(  
@Flag    VARCHAR(30)    = NULL  
,@rowId   BIGINT     = NULL  
,@stopType   VARCHAR(6)    = NULL  
,@requestData  NVARCHAR(MAX)   = NULL  
,@requestDate  DATETIME    = NULL  
,@responseData  NVARCHAR(MAX)   = NULL  
,@responseDate  DATETIME    = NULL  
,@dataLen   NUMERIC(4)    = NULL  
,@orgtId   VARCHAR(10)    = NULL  
,@srvcDs   VARCHAR(6)    = NULL  
,@trxTypeCd   VARCHAR(4)    = NULL  
,@rspsCd   VARCHAR(4)    = NULL  
,@tlgSeqNo   NUMERIC(10)    = NULL  
,@TlgSendHur  VARCHAR(14)    = NULL  
,@filler   NVARCHAR(MAX)   = NULL  
,@prcsSeqNo   VARCHAR(18)    = NULL  
,@vtulAccNo   VARCHAR(20)    = NULL  
,@maccNo   VARCHAR(20)    = NULL  
,@vctmAmt   NUMERIC(15)    = NULL  
,@rqmtSendOc  VARCHAR(14)    = NULL  
,@depoSendOc  VARCHAR(14)    = NULL  
,@vctmNm   NVARCHAR(20)   = NULL  
,@vctmBrthDt  VARCHAR(8)    = NULL  
,@vctmTelNo   VARCHAR(12)    = NULL  
,@vctmAddr   NVARCHAR(MAX)   = NULL  
,@vctmRqstResn  NVARCHAR(MAX)   = NULL  
,@ripsNm   NVARCHAR(MAX)   = NULL  
,@paymStopAmt  NUMERIC(15)    = NULL  
,@paymStopOc  VARCHAR(14)    = NULL  
,@filler1   NVARCHAR(MAX)   = NULL  
,@sortBy   VARCHAR(50)    = NULL   
,@sortOrder   VARCHAR(5)    = NULL  
,@pageSize   INT      = NULL    
,@pageNumber  INT      = NULL  
,@user    VARCHAR(50)    = NULL  

)  
AS   
SET NOCOUNT ON;    
SET XACT_ABORT ON;    
BEGIN TRY  
	DECLARE @table    VARCHAR(MAX)    
		   ,@select_field_list VARCHAR(MAX)    
		   ,@extra_field_list VARCHAR(MAX)    
		   ,@sql_filter   VARCHAR(MAX)    
		   ,@modType    VARCHAR(6)    
		   ,@errorMsg   VARCHAR(MAX)    
		   ,@bankName   VARCHAR(100)  
		   ,@userType   VARCHAR(5)   
		   ,@custIdNumber  VARCHAR(50)   
		   ,@customerGuid  VARCHAR(50)  
		   
	IF @flag='ilog'  
	BEGIN  
	BEGIN TRANSACTION  
 /*new tlgSeqNo generator*/  
 -- IF @trxTypeCd='0210'  
 -- BEGIN  
 -- DECLARE @NewValue VARCHAR(10)  
 -- SET @NewValue= NEXT VALUE FOR dbo.WSB_SEQUENCE  
 -- SET @tlgSeqNo=(SELECT CONVERT(VARCHAR(6), GETDATE(), 12))  +@NewValue   
  
 -- SET @TlgSendHur=(SELECT FORMAT(GETDATE(),'yyyyMMddHHmmss'))  
 --END  
   
		INSERT INTO dbo.WSB_PAYMENT_STOP  
		(  
			stopType,     	requestData,    requestDate,  	responseData,  	responseDate,     	dataLen,     	orgtId,  
			srvcDs,   		trxTypeCd,   	rspsCd,    		tlgSeqNo,   	TlgSendHur,   		filler,   		prcsSeqNo,       
			vtulAccNo,     	maccNo,    		vctmAmt,   		rqmtSendOc,   	depoSendOc,   		vctmNm,   		vctmBrthDt,  
			vctmTelNo,     	vctmAddr,   	vctmRqstResn,   ripsNm,       	paymStopAmt,     	paymStopOc,     filler1  
		)  
		VALUES  
		(    
		/*stopType : Notification: VT0301, Response: VT0302 */  
			@srvcDs,      	@requestData,   GETDATE(),    	@responseData,  CASE WHEN @responseData=N'' THEN NULL ELSE GETDATE() END ,     @dataLen,      @orgtId,   
			@srvcDs,      	@trxTypeCd,   	@rspsCd,      	@tlgSeqNo,      @TlgSendHur,      	@filler,      	@prcsSeqNo,    
			@vtulAccNo,     @maccNo,        @vctmAmt,       @rqmtSendOc,  	@depoSendOc,    	@vctmNm,      	@vctmBrthDt,   
			@vctmTelNo,  	@vctmAddr,    	@vctmRqstResn,  @ripsNm,   		@paymStopAmt,    	@paymStopOc,    @filler1    
		)  
	   
		SET @rowId = @@IDENTITY  
	  
		IF @trxTypeCd='0200'  
		BEGIN  
			UPDATE dbo.customerMaster SET isActive='N' WHERE walletAccountNo=@vtulAccNo  
	  
			/*sms send to our team */  
	  
			INSERT INTO KT_SMS.dbo.SDK_SMS_SEND 
			( 
				USER_ID, 	SCHEDULE_TYPE, 	SUBJECT, 	SMS_MSG, 	NOW_DATE, 	SEND_DATE, 	CALLBACK, 	DEST_INFO
			)  
			SELECT 'globalmoney',0,'Notice','WSB Bank Payment stop notification. Customer Wallent No '+CAST(@vtulAccNo AS VARCHAR)  
			,FORMAT(GETDATE(),'yyyyMMddHHmmss'),FORMAT(GETDATE(),'yyyyMMddHHmmss'),'1588-6864','GME^'+'01039333964'  
			UNION ALL  
			SELECT 'globalmoney',0,'Notice','WSB Bank Payment stop notification. Customer Wallent No '+CAST(@vtulAccNo AS VARCHAR)  
			,FORMAT(GETDATE(),'yyyyMMddHHmmss'),FORMAT(GETDATE(),'yyyyMMddHHmmss'),'1588-6864','GME^'+'01039333964'    
		END  
	  
		IF @@TRANCOUNT > 0              
			COMMIT TRANSACTION    
	 
		SELECT 0 as code , 'Record saved successfully' message  ,@rowId id   
  
	END  

	IF @flag='ulog'  
	BEGIN  
	BEGIN TRANSACTION  
		UPDATE  WSB_PAYMENT_STOP 
		SET     responseData   = @ResponseData  
				,responseDate   = GETDATE()  
			  --,prcsSeqNo    = @prcsSeqNo  
			  --,vtulAccNo    = @vtulAccNo  
			  --,maccNo     = @maccNo  
			  --,vctmAmt    = @vctmAmt  
			  --,rqmtSendOc    = @rqmtSendOc  
			  --,depoSendOc    = @depoSendOc  
			  --,vctmNm     = @vctmNm  
			  --,vctmBrthDt    = @vctmBrthDt  
			  --,vctmTelNo    = @vctmTelNo  
			  --,vctmAddr    = @vctmAddr  
			  --,vctmRqstResn   = @vctmRqstResn  
			  --,ripsNm     = @ripsNm  
			  --,paymStopAmt   = @paymStopAmt  
			  --,paymStopOc    = @paymStopOc  
			  --,filler1    = @filler1    
		WHERE rowId=@rowId      
    COMMIT TRANSACTION    
	
	SELECT 0 as code , 'Record saved successfully' message  ,@rowId id   
	END  
  
	IF @Flag='details'  
	BEGIN  
		SELECT 		dataLen,		orgtId,			srvcDs,			'0210' AS trxTypeCd,		ISNULL(rspsCd,'0000') AS rspsCd,  
					tlgSeqNo,		TlgSendHur,		filler,			prcsSeqNo,					vtulAccNo,  
					maccNo,			vctmAmt,		rqmtSendOc,		depoSendOc,					vctmNm,
					vctmBrthDt,		vctmTelNo,		vctmAddr,		vctmRqstResn,	 			cm.firstName AS ripsNm,    
					CAST(cm.availableBalance AS INT ) AS paymStopAmt,
					FORMAT(GETDATE(),'yyyyMMddHHmmss') as paymStopOc,	
					filler1  
		FROM 		WSB_PAYMENT_STOP (NOLOCK) ps  
		INNER JOIN 	dbo.customerMaster (NOLOCK) cm ON cm.walletAccountNo=ps.vtulAccNo  
		WHERE rowId=@rowId  
		RETURN   
	END  

	IF @flag = 'customer-list'  
	BEGIN    
		IF @sortBy IS NULL  
			SET @sortBy = 'prcsSeqNo'  
		IF @sortOrder IS NULL  
			SET @sortOrder = 'DESC'  
		
		SET @table = '(  
			SELECT  rowid,prcsSeqNo  
			,vtulAccNo  
			,maccNo  
			,vctmAmt  
			,rqmtSendOc,depoSendOc,vctmNm,vctmBrthDt  
			,vctmTelNo,vctmAddr  
			,vctmRqstResn  
			,cm.firstName ripsNm  
			,ISNULL(paymStopAmt,''0'') paymStopAmt  
			,FORMAT(GETDATE(),''yyyyMMddHHmmss'') as paymStopOc  
			,filler1  
			FROM WSB_PAYMENT_STOP (NOLOCK) wps  
			INNEr JOIN dbo.customerMaster (NOLOCK) cm ON  cm.walletAccountNo=wps.vtulAccNo  
			WHERE cm.isActive =''N'' AND wps.trxTypeCd=''0200'' AND wps.srvcDs=''VT0301'' '  
  
		SET @table=@table+')x'  
		SET @sql_filter = ''  
		IF @prcsSeqNo IS NOT NULL   
			SET @sql_filter += ' AND  prcsSeqNo like ''' + @prcsSeqNo+ '%''';  
     
		SET @select_field_list ='rowid,prcsSeqNo  
								,vtulAccNo  
								,maccNo  
								,vctmAmt  
								,rqmtSendOc,depoSendOc,vctmNm,vctmBrthDt  
								,vctmTelNo,vctmAddr  
								,vctmRqstResn, ripsNm  
								,paymStopAmt  
								, paymStopOc  
								,filler1'  
     
		EXEC dbo.proc_paging  
            @table  
            ,@sql_filter  
            ,@select_field_list  
            ,@extra_field_list  
            ,@sortBy  
            ,@sortOrder  
            ,@pageSize  
            ,@pageNumber  
  
		RETURN  
	END  
	
	IF @Flag='senddetails'  
	BEGIN  
		SELECT 		dataLen,		orgtId,			'VT0302' AS srvcDs,			'0200' AS trxTypeCd,		ISNULL(rspsCd,'0000') AS rspsCd,  
					tlgSeqNo,		TlgSendHur,		filler,						prcsSeqNo,					vtulAccNo,  
					maccNo,			vctmAmt,		rqmtSendOc,					cm.firstName AS ripsNm,		paymStopAmt,
					paymStopOc       
		FROM 		WSB_PAYMENT_STOP (NOLOCK)ps  
		INNER JOIN 	dbo.customerMaster (NOLOCK) cm ON cm.walletAccountNo=ps.vtulAccNo 
		WHERE rowId=@rowId  
		
		RETURN   
	END  
  
	IF @flag='paymentStop'  
	BEGIN  
		UPDATE dbo.customerMaster SET isActive='N',modifiedBy=@user 
		WHERE walletAccountNo=@vtulAccNo  
		SELECT 0 as code , 'Customer block successfully' message  ,@vtulAccNo id   
	END  
  
END TRY  
BEGIN CATCH  
	IF @@TRANCOUNT > 0    
		ROLLBACK TRANSACTION    
	
	DECLARE @errorMessage VARCHAR(MAX)    
	SET @errorMessage = ERROR_MESSAGE()    
	EXEC proc_errorHandler 1, @errorMessage, NULL    
END CATCH