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

--EXEC [PROC_PROCESS_TRAN_NIBANK] @FLAG = 'TRAN-TXN',@P_CONTROLNO='QIONPLPKKMP'

CREATE PROCEDURE [dbo].[PROC_PROCESS_TRAN_NIBANK]
(
	@FLAG      		VARCHAR(50),
	@P_CONTROLNO	VARCHAR(20)	= NULL,
	@RATE			MONEY		= NULL,
	@PARTNERID		BIGINT		= NULL
)
AS

SET  NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
----------------------------------------------------------------------------------
-- Procedure Start
----------------------------------------------------------------------------------

BEGIN
	IF @FLAG = 'TRAN-TXN'
	BEGIN

		SELECT	'0' AS ErrorCode,
				'SUCCESS' AS Msg,
				dbo.FNADecryptString(RT.controlNo) AS [TxnReference], --TxnReference
				ISNULL(RT.purposeOfRemit,'Family maintenance') As [Purpose], --Purpose
				(SELECT lastName1+ISNULL(' '+lastName2,'')  FROM dbo.FNASplitName(TS.firstName)) AS [SenderLastName],		--SenderLastName
				(SELECT firstName+ISNULL(' '+middleName,'') FROM dbo.FNASplitName(TS.firstName)) AS [SenderFirstName],	--SenderFirstName
				TS.mobile AS [SenderTelNo], --SenderTelNo
				KB.BankName AS [SenderBankName],
				CM.walletAccountNo AS [SenderAccountNo],
				CASE WHEN  RI.localLastName1   IS NOT NULL THEN RI.localLastName1 ELSE TR.lastName1+ISNULL(' '+TR.lastName2,'') END AS [RecLastName],	--RecLastName
				CASE WHEN  RI.localFirstName   IS NOT NULL THEN RI.localFirstName ELSE TR.firstName  END AS [RecFirstName],	--RecLastName
				--CASE WHEN RT.controlNo IN('QIOMJNMNRON') THEN N'НЯМЖАРГАЛ' ELSE TR.firstName  END AS [RecFirstName],	--RecFirstName
				--TR.lastName1+ISNULL(' '+TR.lastName2,'') AS [RecLastName],	--RecLastName
				--TR.firstName+ISNULL(' '+TR.middleName,'') AS [RecFirstName],	--RecFirstName
				TR.mobile AS [RecTelNo],--RecTelNo
				'' AS [RecForm],--RecForm
				AM.agentCode AS [RecBankNo],--RecBankNo
				RT.accountNo AS [RecAccount],--RecAccount
				RT.payoutCurr AS [RecCurrency],--RecCurrency
				ROUND(tAmt/(sCurrCostRate+ISNULL(sCurrHoMargin,0)) ,2)  AS [FromAmount],--FromAmount
				FORMAT(RT.createdDate,'yyyy-MM-dd HH:mm:ss') AS [SendingDate] --SendingDate
		FROM  	remitTran(nolock) RT  
		INNER JOIN tranSenders(nolock) TS ON  RT.id = TS.tranid  
		INNER JOIN tranReceivers(nolock) TR ON   RT.id = TR.tranId 
		INNER JOIN receiverInformation(nolock) RI ON   RI.receiverId= TR.customerId 		 
		INNER JOIN agentMaster(nolock) AM ON   RT.pBank = AM.agentId  
		INNER JOIN customerMaster(nolock) CM ON CM.customerId = TS.customerId
		INNER JOIN KoreanBankList(nolock) KB ON KB.rowId = CM.bankName
		WHERE  	pCountry = 'MONGOLIA' AND payStatus = 'UNPAID' AND tranStatus='PAYMENT'
		AND 	controlNo= @P_CONTROLNO AND RT.pSuperAgent = 590814
		
	END
	ELSE IF @FLAG = 'UPDATE-RATE'
	BEGIN
		DECLARE @FROMDATE DATE = GETDATE(),@TODATE DATE,@DAYNAME VARCHAR(20)

		SET @DAYNAME = FORMAT(@FROMDATE, 'dddd')

		SET @TODATE = CASE	WHEN @DAYNAME = 'FRIDAY' THEN DATEADD(DAY,3,@FROMDATE)
							WHEN @DAYNAME = 'SATURDAY' THEN DATEADD(DAY,2,@FROMDATE)
							ELSE DATEADD(DAY,1,@FROMDATE)
						END

		IF EXISTS(SELECT 'A' FROM TBL_PARTNER_COST_RATE(NOLOCK) WHERE GETDATE() BETWEEN EffectiveFrom AND EffectiveTo AND ToCurr = 'MNT' AND ProviderId = @PARTNERID)
		BEGIN
			SELECT '1' ErrorCode,'Todays Rate already applied.' Msg, NULL
			RETURN
		END	

		INSERT INTO TBL_PARTNER_COST_RATE(ProviderName,CurrCostRate,FromCurr,ToCurr,EffectiveFrom,EffectiveTo,Createdby,CreatedDate,ProviderId)		
		SELECT 'NIBANK',@RATE,'USD','MNT',@FROMDATE,CAST(@TODATE AS VARCHAR)+' 19:00:00','SYSTEM',GETDATE(),@PARTNERID

		SELECT '0' ErrorCode,'New Rate Updated Successfully.' Msg, NULL
		
	END
END
----------------------------------------------------------------------------------
-- Procedure End
----------------------------------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET  NOCOUNT OFF

GO