MOBILE_API/DB/SPs/PROC_SCHEDULAR_PUSH_TXN_COMMON.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[PROC_SCHEDULAR_PUSH_TXN_COMMON]    Script Date: 9/27/2019 1:30:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----EXEC [PROC_SCHEDULAR_PUSH_TXN_COMMON] @FLAG = 'PUSH-LIST',@PAYOUTPARTNERID=590814

CREATE PROC [dbo].[PROC_SCHEDULAR_PUSH_TXN_COMMON]
@FLAG				VARCHAR(20),
@PAYOUTPARTNERID	BIGINT,
@TRANID				BIGINT = NULL,
@PartnerPin			VARCHAR(20) = NULL,
@PartnerId			VARCHAR(30) = NULL,
@ControlNo			VARCHAR(20) = NULL,
@message			NVARCHAR(500)= NULL

AS
SET NOCOUNT ON;

IF @FLAG = 'PUSH-LIST'
BEGIN
	---- 590814:NI BANK,585209:THUNES 
	SELECT TOP 20 TranId = R.id, BaseUrl = p.BaseUrl,[Authorization] = p.AuthKey,ControlNo = ControlNo,PayoutPartner = @PAYOUTPARTNERID,DecryptedPIN = dbo.FNADecryptString(controlNo)
	FROM remitTran(NOLOCK) R
	INNER JOIN serviceTypeMaster M(NOLOCK) ON M.typeTitle = R.paymentMethod
	INNER JOIN TblPartnerwiseCountry(NOLOCK) P ON P.AgentId = R.pSuperAgent AND P.PaymentMethod = M.serviceTypeId
	WHERE R.pSuperAgent = @PAYOUTPARTNERID
	AND tranStatus = 'PAYMENT' AND payStatus = 'UNPAID'
	and r.pSuperAgent <> 590814
	--and  R.controlNo IN('QIJJKIIMOQO','QIONPLPKKMP','QIRPJMRMROP')
	--AND  1=2
	RETURN
END
ELSE IF @FLAG = 'POST'
BEGIN
	IF ISNULL(@PartnerPin,'') = ''
		SET @PartnerPin = DBO.FNADecryptString(@ControlNo)

	UPDATE remitTran SET 
		payStatus		= 'Post',
		postedBy		= 'system',
		postedDate		= GETDATE(),
		postedDateLocal	= GETUTCDATE(),
		controlNo		= dbo.FNAEncryptString(@PartnerPin),
		controlNo2		= @ControlNo,
		contNo			= @PartnerId 
	WHERE ID = @TRANID AND pSuperAgent = @PAYOUTPARTNERID
	AND tranStatus = 'PAYMENT' AND payStatus = 'UNPAID'

	EXEC proc_errorHandler '0','SUCCESS',NULL 
	RETURN
END
ELSE IF @FLAG = 'SYNC-LIST'
BEGIN
	
	SELECT TOP 50 TranId = R.id, BaseUrl = p.BaseUrl,[Authorization] = p.AuthKey
		,ControlNo = CASE WHEN @PAYOUTPARTNERID =585209 THEN DBO.FNADecryptString(controlNo2) ELSE DBO.FNADecryptString(ControlNo) END
		,[PayoutPartnerId] = @PAYOUTPARTNERID
	FROM remitTran(NOLOCK) R
	INNER JOIN serviceTypeMaster M(NOLOCK) ON M.typeTitle = R.paymentMethod
	INNER JOIN TblPartnerwiseCountry(NOLOCK) P ON P.AgentId = R.pSuperAgent AND P.PaymentMethod = M.serviceTypeId
	WHERE R.pSuperAgent = @PAYOUTPARTNERID
	AND tranStatus = 'PAYMENT' AND payStatus = 'POST'
	ORDER BY NEWID()
	RETURN
END
ELSE IF @FLAG = 'PAID'
BEGIN
	UPDATE remitTran SET 
		tranStatus		= 'Paid',
		payStatus		= 'Paid',
		paidBy			= 'system',
		paidDate		= GETDATE(),
		paidDateLocal	= GETUTCDATE()
	WHERE ID = @TRANID AND pSuperAgent = @PAYOUTPARTNERID
	AND tranStatus = 'PAYMENT' AND payStatus = 'POST'

	IF @PAYOUTPARTNERID = 590814
	BEGIN
		DECLARE @CustomerEmail VARCHAR(100),@Mobile varchar(20),@SMSBody VARCHAR(90),@refund char(1)
		SELECT @CustomerEmail = createdBy,@ControlNo = DBO.FNADecryptString(controlNo) FROM remitTran(NOLOCK)
		WHERE ID = @TRANID AND pSuperAgent = @PAYOUTPARTNERID

		SELECT @Mobile = mobile FROM customerMaster(NOLOCK) WHERE email = @CustomerEmail
		SET @Mobile = CASE WHEN LEFT(@Mobile,3) = '+82' THEN REPLACE(@Mobile,'+82','0') ELSE @Mobile END
		SET @Mobile = CASE WHEN LEFT(@Mobile,2) = '82' THEN REPLACE(@Mobile,'82','0') ELSE @Mobile END

		IF LEN(@Mobile) = 11
		BEGIN
			SET @SMSBody = 'Your GME transaction '+@ControlNo+' is successfully credited to beneficiary account.'
			EXEC proc_CallToSendSMS @FLAG = 'I',@SMSBody= @SMSBody ,@MobileNo=@Mobile
		END
	END

	EXEC proc_errorHandler '0','SUCCESS',NULL 
	RETURN
END
ELSE IF @FLAG = 'CANCEL'
BEGIN

	DECLARE @srouteId CHAR(1),@controlNoEncrypted VARCHAR(20)

	--IF @PROVIDER ='contact'
	BEGIN
		SELECT @controlNo = DBO.FNADecryptString(CONTROLNO),@srouteId = sRouteId,@controlNoEncrypted=controlNo
		FROM remitTran(NOLOCK) WHERE id = @TRANID AND pSuperAgent  = @PAYOUTPARTNERID

		INSERT INTO tranModifyLog(tranId,controlNo,message,MsgType,createdBy,createdDate)
		SELECT @tranId,@controlNoEncrypted,@message,'API Response','system',GETDATE()

		--IF @srouteId = 'W'
		--BEGIN
		--	IF NOT EXISTS(SELECT 'A' FROM FastMoneyPro_Account.DBO.tran_master(NOLOCK) WHERE field1= @controlNo)
		--		EXEC FastMoneyPro_Account.DBO.proc_transactionVoucherEntry @controlNo

		--	--INSERT INTO @tempTbl(errorcode, msg, id)
		--	EXEC [proc_cancelTran] @flag = 'cancel',@controlNo = @controlNo,@user = 'system',@cancelReason = @message,@refund = 'N'

		--	EXEC [proc_cancelTran] @flag = 'cancelReceipt',@tranId = @TRANID,@user = 'system'
		--END
		--ELSE
			UPDATE remitTran SET tranStatus='Hold' WHERE id = @TRANID AND pSuperAgent  = @PAYOUTPARTNERID
	END
END
GO