MOBILE_API/DB/SPs/mobile_proc_resend.sql

USE [FastMoneyPro_Remit]
GO
	
/****** Object:  StoredProcedure [dbo].[mobile_proc_resend]    Script Date: 7/4/2019 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--mobile_proc_resend @Flag='tranDetail',@TranId='100514281'
-- EXEC mobile_proc_resend @Flag = 'tranList', @CustomerId = '88',@FromDate = null,@ToDate = null

ALTER PROCEDURE mobile_proc_resend(
	@Flag		VARCHAR(100)	= NULL,
	@CustomerId BIGINT			= NULL,
	@TranId		BIGINT			= NULL,
	@FromDate	VARCHAR(50)		= NULL,
	@ToDate		VARCHAR(50)		= NULL
)
AS
SET NOCOUNT ON;
BEGIN
	IF @Flag='tranList'
	BEGIN
		DECLARE @sql VARCHAR(MAX)

		IF @FromDate IS NULL
		BEGIN
			SET @FromDate	=	DATEADD(MONTH,-6,GETDATE())
			SET @ToDate		=	GETDATE()
		END			
		
		SET @sql=
		'SELECT  TOP 1000
			 ReceiverName   = rt.ReceiverName
			,SenderName		= rt.senderName
			,TranId         = rt.id
			,ControlNo      = dbo.FNADecryptString(rt.controlNo)
			,CollAmount     = Cast(rt.cAmt as decimal) 
			,CollCurr       = rt.collCurr
			,PayoutAmt      = rt.pAmt 
			,PCurr          = rt.payoutCurr
			,PayStatus      = CASE WHEN rt.tranStatus=''Cancel'' then ''CANCEL'' else rt.payStatus end
			,PayoutMode     = rt.paymentMethod
			,SendDate       = FORMAT(rt.createdDate,''yyyy-MM-dd'')
			,PaidDate       = FORMAT(rt.paidDate,''yyyy-MM-dd'')
			,BankName		= ISNULL(rt.pBankName,''ANYWHERE'')
		FROM dbo.remitTran(NOLOCK) rt
		INNER JOIN tranSenders ts(NOLOCK) ON rt.id=ts.tranId
		WHERE ts.customerId='''+CAST(@CustomerId AS VARCHAR)+''''
		
		SET @sql = @sql + ' AND rt.createdDate BETWEEN '''+@FromDate+''' AND '''+ @ToDate+''''		
		SET @sql = @sql+ ' ORDER BY rt.createdDate DESC'
		
		--PRINT(@sql)
		EXEC(@sql)
	END

	IF @Flag='tranDetail'
	BEGIN
				
		   DECLARE @paymentMethod varchar(50) = NULL,@serviceType int = NULL,@pCountry INT = NULL, @pAgent INT = NULL, @pBank INT =NULL
		   DECLARE @PayOutPartner BIGINT,@vPayoutPartner BIGINT,@vGmeCode VARCHAR(20)

		   SELECT	@paymentMethod	=	paymentMethod,
					@pCountry		=	pcm.countryId,
					@pAgent			=	CASE WHEN rt.pCountry='NEPAL' THEN '1036' ELSE pSuperAgent END,
					@pBank			=	rt.pBank ,
					@PayOutPartner	=	CASE WHEN rt.pCountry = 'Nepal' THEN '1036' ELSE rt.pSuperAgent END
		   FROM FastMoneyPro_Remit.dbo.remittran(NOLOCK) rt 
		   INNER JOIN countryMaster(NOLOCK) pcm ON rt.pCountry = pcm.CountryName 
		   WHERE rt.id = @TranId

		   SELECT @serviceType = serviceTypeId 
		   FROM serviceTypeMaster(NOLOCK) 
		   WHERE typeTitle = @paymentMethod
			
			IF @pCountry IN(33,36,42,142,169,174) --Myanmar,Cambodia,Sri Lanka,Mongolia,Pakistan,Philippines
			BEGIN
				SELECT @vPayoutPartner = AgentId FROM TblPartnerwiseCountry(nolock) 
				WHERE CountryId = @pCountry AND (PaymentMethod = @serviceType OR PaymentMethod IS NULL)
				AND IsActive = 1
				
				IF @vPayoutPartner <> @PayOutPartner
				BEGIN
					SET @PayOutPartner = @vPayoutPartner
					SELECT @vGmeCode = swiftCode FROM agentMaster(NOLOCK) WHERE agentId = @pBank

					SELECT @pBank = agentId,@PayOutPartner = @PayOutPartner FROM agentMaster(NOLOCK) 
					WHERE swiftCode = @vGmeCode AND parentId = @vPayoutPartner
					AND agentType = '2903' AND isActive='Y' AND agentRole = CAST(@serviceType AS VARCHAR)
					AND agentCountryId = @pCountry
				END
			END

			IF NOT EXISTS(SELECT 'x' FROM TblPartnerwiseCountry(nolock) WHERE CountryId = @pCountry AND AgentId = @PayOutPartner 
			AND (PaymentMethod = @serviceType OR PaymentMethod IS NULL))
			BEGIN
				SELECT '1' ErrorCode, 'Service Not allowed.' Msg, NULL Id
				RETURN
			END	  
		 
			IF @pBank IS NOT NULL
			BEGIN
				IF NOT EXISTS(SELECT 's'  FROM agentMaster(NOLOCK) WHERE agentId = @pBank AND isActive = 'Y' AND ISNULL(isDeleted,'N') = 'N')
				BEGIN
					SELECT '1' ErrorCode, 'Service Not allowed.' Msg, NULL Id
					RETURN
				END
			END

		EXEC proc_errorHandler '0','Success',null

		SELECT 
			 pAgent			=	@pBank
			,sCountry		=	scm.countryId
			,pCountry		=	pcm.countryId
			,pCountryName	=	rt.pCountry
			,sCurrency		=	collCurr
			,pCurrency		=	payoutCurr
			,cAmount		=	cAmt
			,pAmount		=	pAmt
			,serviceType	=	stm.serviceTypeId
			,calcBy			=	'P'	
			,PayOutPartner	=	@PayOutPartner		
		FROM dbo.remitTran(NOLOCK) rt 
		INNER JOIN countryMaster scm (NOLOCK) ON  rt.sCountry = scm.countryName
		INNER JOIN countryMaster pcm (NOLOCK) ON  rt.pCountry = pcm.countryName
		INNER JOIN serviceTypeMaster stm(NOLOCK) ON stm.typeTitle = rt.paymentMethod
		WHERE rt.id = @TranId

		SELECT 	TOP 1
			 [User]				=	rt.createdBy
			,SenderId			=	ts.customerId
			,ReceiverId			=	tr.customerId
			,DeliveryMethodId	=	stm.serviceTypeId
			,PBranch			=	pBankBranch
			,PAgent				=	@pBank
			,PCurr				=	rt.payoutCurr
			,CollCurr			=	rt.collCurr
			,CollAmt			=	rt.cAmt
			,PayoutAmt			=	rt.pAmt
			,TransferAmt		=	rt.tAmt
			,RelWithSender		=	sdvr.valueId
			,PurposeOfRemit		=	sdvp.valueId
			,SourceOfFund		=	sdvsof.valueId
			,RState				=	rt.pState
			,RLocation			=	rt.pLocation
			,PayOutPartner		=	@PayOutPartner			 
			,ReceiverAccountNo	=	rt.accountNo
		FROM dbo.remitTran(NOLOCK) rt 
		INNER JOIN tranSenders ts(NOLOCK) ON ts.tranId = rt.id
		INNER JOIN tranReceivers tr(NOLOCK) ON tr.tranId = rt.id
		INNER JOIN serviceTypeMaster stm(NOLOCK) ON stm.typeTitle = rt.paymentMethod
		LEFT JOIN staticDataValue sdvp(NOLOCK) ON sdvp.detailTitle = rt.purposeOfRemit
		LEFT JOIN staticDataValue sdvr(NOLOCK) ON sdvr.detailTitle = rt.relWithSender
		LEFT JOIN staticDataValue sdvsof(NOLOCK) ON sdvsof.detailTitle = rt.sourceOfFund
		WHERE rt.id = @TranId 
			
		SELECT 
			 rFullName		=	receiverName
			,rAddress		=	tr.address
			,rCountry		=	tr.country
			,rMobile		=	tr.mobile
			,rCountryCode	=	cm.countryCode
			,deliveryMethod	=	rt.paymentMethod
			,pAgentName		=	pBankName 
		FROM remittran (NOLOCK)rt	
		INNER JOIN  tranReceivers (NOLOCK) tr ON rt.id = tr.tranId 
		INNER JOIN dbo.countryMaster (NOLOCK) cm ON cm.countryName = rt.pCountry
		WHERE rt.id	= @TranId	
	END
END