MOBILE_API/DB/SPs/ws_proc_AccountDepositMarkeDownloaded.sql

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROC [dbo].[ws_proc_AccountDepositMarkeDownloaded] (	 
	@ACCESSCODE			VARCHAR(50),
	@USERNAME			VARCHAR(50),
	@PASSWORD			VARCHAR(50),
	@AGENT_SESSION_ID	VARCHAR(50),
	@DOWNLOAD_TOKENID	VARCHAR(50)
)
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY


	DECLARE @errCode INT
     DECLARE @count INT 
	 DECLARE @autMsg	VARCHAR(500)


	EXEC ws_proc_checkAuthntication @USERNAME,@PASSWORD,@ACCESSCODE,@errCode OUT, @autMsg OUT
	--EXEC ws_proc_checkAuthntication @USERNAME, @PASSWORD, @ACCESSCODE, @errCode OUT
	
DECLARE @errorTable TABLE (
	 AGENT_SESSION_ID VARCHAR(150)
	,CONTROLNO INT
	,SENDAGENT INT
	,SENDERNAME VARCHAR(200)
	,SENDERADDRESS VARCHAR(10)
	,SENDER_MOBILE VARCHAR(10)
	,SENDERCITY VARCHAR(10)
	,SENDERCOUNTRY VARCHAR(10)
	,RECEIVERNAME VARCHAR(10)
	,RECEIVERADDRESS varchar(10)
	,RECEIVERPHONE VARCHAR(10)
	,RECEIVERCITY VARCHAR(10)
	,RECEIVERCOUNTRY VARCHAR(10)
	,TRANSFERAMOUNT MONEY
	,SCURRCOSTRATE FLOAT
	,RCURRCOSTRATE FLOAT
	,PAYOUTAMT MONEY
	,PAYOUTCURRENCY VARCHAR(3)
	,PAYMENTTYPE VARCHAR(10)
	,BANKNAME VARCHAR(10)
	,BANKBRANCH VARCHAR(10)
	,BANKACCOUNTNO VARCHAR(10)
	,BANKCODE VARCHAR(10)
	,BANKBRANCHCODE varchar(10)
	,TRNDATE varchar(10)
	,DOWNLOAD_TOKENID INT
	,ISLOCAL VARCHAR(5)
	,TRANID VARCHAR(50)
	,RECEIVERMOBILE VARCHAR(50)
)

INSERT INTO @errorTable(AGENT_SESSION_ID) SELECT @AGENT_SESSION_ID

	IF (@errCode = 1)
	BEGIN
		SELECT
			'1002' CODE
			,ISNULL(@autMsg,'Authentication Fail') MESSAGE
			,* 
		FROM @errorTable
		RETURN
	END
	--IF (@errCode = 1)
	--BEGIN
	--	SELECT '1002' CODE, 'Authentication Fail' MESSAGE,@AGENT_SESSION_ID AGENT_SESSION_ID
	--	RETURN
	--END
	IF EXISTS(SELECT 'A' FROM applicationUsers WITH (NOLOCK) WHERE 
			userName = @USERNAME AND forceChangePwd = 'Y')
	BEGIN
		SELECT '1002' CODE
			,'You logged on first time,must first change your password and try again!' MESSAGE
			,@AGENT_SESSION_ID AGENT_SESSION_ID
		RETURN
	END
	------------------VALIDATION-------------------------------
	IF @AGENT_SESSION_ID IS NULL
	BEGIN
		SELECT 
			 '1001' CODE
			,'AGENT SESSION ID Field is Required' MESSAGE
			,@AGENT_SESSION_ID AGENT_SESSION_ID
		RETURN;
	END
	IF @DOWNLOAD_TOKENID IS NULL
	BEGIN
		SELECT 
			 '1001' CODE
			,'DOWNLOAD TOKENID Field is Required' MESSAGE
			,@AGENT_SESSION_ID AGENT_SESSION_ID
		RETURN;
	END
	
	DECLARE		
		@pAgent INT
		,@tranId INT
		,@tranStatus VARCHAR(50)
		,@pAgentName varchar(200)

	-- PICK AGENTID ,COUNTRY FROM USER
SELECT 
	@pAgent		= agentId 
FROM applicationUsers WITH(NOLOCK)
WHERE userName = @USERNAME AND ISNULL(isDeleted, 'N') = 'N' AND isActive = 'Y'

SELECT @pAgentName = agentName FROM agentMaster WITH(NOLOCK) 
WHERE agentId = @pAgent AND isActive = 'Y' AND ISNULL(isDeleted, 'N') = 'N'


	DECLARE @controlNoTable TABLE(controlNo VARCHAR(50))
	INSERT INTO @controlNoTable(controlNo)
	SELECT controlNo FROM remitTran WITH(NOLOCK) 
	WHERE  pAgent	= @pAgent 
	AND payTokenId	= @DOWNLOAD_TOKENID 
	AND payStatus	= 'Unpaid' 
	AND tranStatus	= 'Payment'
	AND paymentMethod = 'Bank Deposit'
	AND lockedBy	= @USERNAME
	

	INSERT INTO @controlNoTable(controlNo)
	SELECT controlNo FROM remitTran WITH(NOLOCK) 
	WHERE  pAgent	= @pAgent 
	AND payTokenId	= @DOWNLOAD_TOKENID 
	AND payStatus	= 'Unpaid' 
	AND tranStatus	= 'Payment'
	AND paymentMethod = 'MOBILE WALLET'
	AND lockedBy	= @USERNAME

	IF NOT EXISTS(SELECT TOP 1 'X' FROM @controlNoTable)
	BEGIN
		SELECT 
			 '1004' CODE
			,'There are no Transaction with the given Token ID to be set as Downloaded' MESSAGE
			,@AGENT_SESSION_ID AGENT_SESSION_ID
		RETURN
	END
	
	
	UPDATE remitTran SET
		 payStatus			= 'Post',
		 postedBy			= @USERNAME,
		 postedDate			= GETDATE(),
		 postedDateLocal	= GETDATE()
		-- ,pAgentComm					= (SELECT amount FROM dbo.FNAGetPayComm
		--									(rt.sBranch,(SELECT countryId FROM countryMaster WITH(NOLOCK) WHERE countryName = rt.sCountry), 
		--										NULL, @psAgent, @pCountryId, null, @pBranch, rt.sAgentCommCurrency
		--										,(select serviceTypeId from servicetypemaster(nolock) where typeTitle = rt.paymentMethod)
		--										, rt.cAmt, rt.pAmt, rt.serviceCharge, NULL, NULL
		--									))
		--,pAgentCommCurrency			= rt.sAgentCommCurrency
	FROM remitTran rt
	INNER JOIN @controlNoTable cn ON rt.controlNo = cn.controlNo
	
     SET @count = @@ROWCOUNT

	SELECT
	 0 CODE
	,@AGENT_SESSION_ID AGENT_SESSION_ID
	,CAST(@count AS VARCHAR(10)) + ' transaction(s) downloaded'	MESSAGE


END TRY
BEGIN CATCH

SELECT '9001' CODE, 'Technical Error : ' + ERROR_MESSAGE() MESSAGE

INSERT INTO Logs (errorPage, errorMsg, errorDetails, createdBy, createdDate)
SELECT 'API SP Error','Technical Error : ' + ERROR_MESSAGE() MESSAGE,'ws_proc_AccountDepositMarkeDownloaded',@USERNAME, GETDATE()

END CATCH

/*

EXEC ws_proc_AccountDepositMarkeDownloaded  @ACCESSCODE='GMKOR393965',@USERNAME='IMENepal',@PASSWORD='123456',@AGENT_SESSION_ID='1231'
    ,@DOWNLOAD_TOKENID='11'

*/

GO