MOBILE_API/DB/SPs/proc_SendTransactionOnBehalf.sql

USE FastMoneyPro_Remit
GO

ALTER PROC proc_SendTransactionOnBehalf (
 @flag			VARCHAR(20)
 ,@searchData	VARCHAR(50)	=	NULL
 ,@searchValue	VARCHAR(50)	=	NULL
 ,@countryId	VARCHAR(10)	=	NULL
 ,@reciverID	VARCHAR(20) =	NULL
 ,@CustomerID	VARCHAR(20) =	NULL
 ,@locationId	VARCHAR(10) =	NULL
 ,@tranid		BIGINT		=	NULL
) 
AS 
SET  NOCOUNT ON
 IF @flag = 's'
 BEGIN 
	
	DECLARE @availableBalance MONEY

	IF @searchData NOT IN ('Email','Id Numbert')
	BEGIN
		SELECT errorCode = 1, msg = 'Enter search value'
		RETURN
	END
	/*Check Easy remit customer block or not*/

		DECLARE @custId VARCHAR(20)
		SELECT TOP 1 @custId=customerId from customerMaster(nolock) where email=@searchValue and customerType='11068' ---change customer type in live
		IF EXISTS (SELECT 'X' FROM CUSTOMER_BLOCK_LIST(nolock) where customerid=@custId and isBlock='Y')
		BEGIN
				EXEC proc_errorHandler  1,'You account is blocked.Please contact GME Support!' ,null
				RETURN;
		END

	IF @searchData = 'email'
	BEGIN

		 SELECT @availableBalance = ISNULL(cm.availablebalance,0)
		 FROM customermaster cm  (NOLOCK) 
		 WHERE cm.email = @searchValue AND CM.approvedDate IS NOT NULL AND isActive = 'Y'

		 IF @availableBalance IS NULL
		 BEGIN
			  SELECT errorCode=1  ,msg='Customer is not approved/exists in system.'
			  RETURN
		 END

		 IF @availableBalance = 0
		 BEGIN
			  SELECT errorCode=1  ,msg='You do not have sufficient balance'
			  RETURN
		 END

		 UPDATE  customermaster SET sourceOfFund='Salary / Wages' WHERE sourceOfFund IS NULL AND email = @searchValue

		SELECT  
			customerid,errorCode=0,
			fullName			= ISNULL(cm.firstname,'')
			, valueId			= idType 
			, idName			= sd.detailTitle
			, svalidDate		= idExpiryDate
			, smobile			= cm.mobile
			, senderAddress		= cm.[address]
			, nativecountryid	= cm.nativecountry 
			, nativecountry		= ctm.countryName
			, semail			= cm.email
			, sCity				= Cm.city
			, IdIssuedDate		= cm.idissuedate
			, availablebalance	= ISNULL(cm.availablebalance,0)
			, sourceOfFund		= cm.sourceOfFund
			, sourceOfFundid	= sof.valueId
			, cm.walletAccountNo
			, SenderIdNo		= cm.idNumber
			, SenderBirthDate	= cm.dob
		FROM customermaster cm  (nolocK)
		INNER JOIN staticDataValue sd (NOLOCK) ON sd.valueId = cm.idType AND sd.typeID=1300 AND ISNULL(sd.IS_DELETE ,'N')<>'Y'
		INNER JOIN countrymaster  ctm (nolocK) ON ctm.countryid=cm.nativecountry
		LEFT JOIN dbo.staticDataValue sof (NOLOCK) ON sof.detailTitle = cm.sourceOfFund AND sof.typeID = 3900 AND ISNULL(sof.IS_DELETE ,'N')<>'Y'
		WHERE cm.email = @searchValue AND cm.isActive = 'Y'
	END 
	ELSE 
	BEGIN

		SELECT @availableBalance = ISNULL(cm.availablebalance,0)
		 FROM customermaster cm  (NOLOCK) 
		 WHERE cm.idNumber = @searchValue AND CM.approvedDate IS NOT NULL AND isActive = 'Y'

		 IF @availableBalance IS NULL
		 BEGIN
			  SELECT errorCode=1  ,msg='Customer is not approved/exists in system.'
			  RETURN
		 END

		 IF @availableBalance = 0
		 BEGIN
			  SELECT errorCode=1  ,msg='You do not have sufficient balance'
			  RETURN
		 END

		SELECT  
			customerid,errorCode=0,
			fullName			= ISNULL(cm.firstname,'')
			, valueId			= idType 
			, idName			= sd.detailTitle
			, svalidDate		= idExpiryDate
			, smobile			= cm.mobile
			, senderAddress		= cm.[address]
			, nativecountryid	= cm.nativecountry 
			, nativecountry		= ctm.countryName
			, semail			= cm.email
			, sCity				= Cm.city
			, IdIssuedDate		= cm.idissuedate
			, availablebalance	= ISNULL(cm.availablebalance,0)
			, sourceOfFund		= cm.sourceOfFund
			, sourceOfFundid	= sof.valueId
			, cm.walletAccountNo
			, SenderIdNo		= cm.idNumber
			, SenderBirthDate	= cm.dob
		FROM customermaster cm  (nolocK)
		INNER JOIN staticDataValue sd (NOLOCK) ON sd.valueId = cm.idType AND sd.typeID=1300 AND ISNULL(sd.IS_DELETE ,'N')<>'Y'
		INNER JOIN countrymaster  ctm (nolocK) ON ctm.countryid=cm.nativecountry
		LEFT JOIN dbo.staticDataValue sof (NOLOCK) ON sof.detailTitle = cm.sourceOfFund AND sof.typeID = 3900 AND ISNULL(sof.IS_DELETE ,'N')<>'Y'
		WHERE cm.idNumber = @searchValue AND cm.isActive = 'Y'
   END 
 END
 ELSE IF @flag = 'ddlReceiver'
 BEGIN
	SELECT [Key]=receiverId , Value= firstName  + ' '+ ISNULL(middleName,'') + ' '+  ISNULL(lastName1,'') +''+ ISNULL(lastName2,' ')  
	FROM  receiverInformation (nolocK) 
	WHERE customerId = @CustomerID AND isActive = 1
 END
ELSE IF @flag = 'state'
BEGIN
	IF @countryId = '151'
	BEGIN
		SELECT [Key]	= Replace(stateName,char(9),'')
			, [Value]	= Replace(stateName,CHAR(9),'')  
		FROM dbo.countriesStates rcs WITH(NOLOCK)
		INNER JOIN dbo.countryMaster cm WITH(NOLOCK) ON cm.countryCode = rcs.countryCode  
		WHERE countryId = @countryId
		ORDER BY stateName ASC
	END
	ELSE
	BEGIN
		IF NOT EXISTS(SELECT 'A' FROM tblServicewiseLocation (NOLOCK) WHERE countryId = @countryId)
		BEGIN
			SELECT [Value] = 'Any State', [Key] = '0' 
			RETURN
		END
		SELECT [Value] = location
				,[Key] = rowId 
		FROM tblServicewiseLocation (NOLOCK) 
		WHERE countryId = @countryId
		AND isActive = 1
	END
END
ELSE IF @flag = 'substate'
BEGIN
	IF @countryId = '151'
	BEGIN
		SELECT [Key]	= REPLACE(stateName,char(9),'')
			, [Value]	= REPLACE(stateName,CHAR(9),'')  
		FROM dbo.countriesStates rcs WITH(NOLOCK)
		INNER JOIN  countryMaster CM (NOLOCK) ON CM.countryName = rcs.countryName
		WHERE CM.countryId = @countryId

		RETURN
	END	
	ELSE
	BEGIN
	DECLARE @payoutPartner VARCHAR(20)
		SELECT @payoutPartner = partnerId FROM tblServicewiseLocation (NOLOCK) WHERE ROWID = @locationId
		--TRANGLO SDN. BHD. and country Indonesia have direct sub location defined
		IF @payoutPartner = '224388' AND @countryId = '105'
		BEGIN
			SELECT [Key] = rowId, [Value] = subLocation 
			FROM tblSubLocation (NOLOCK) 
			WHERE locationId = 0
			AND isActive = 1
			AND partnerId = @payoutPartner
			ORDER BY subLocation ASC			
		
			RETURN
		END
		IF @payoutPartner = '393901' AND @countryId = '36' -- For truemoney
		BEGIN
			SELECT [Key] = rowId, [Value] = subLocation 
			FROM tblSubLocation (NOLOCK) 
			WHERE locationId = 0
			AND isActive = 1
			AND partnerId = @payoutPartner
			ORDER BY subLocation ASC			
		
			RETURN
		END
		IF NOT EXISTS(SELECT 'A' FROM tblSubLocation (NOLOCK) WHERE locationId = @locationId   AND partnerId<>'392577')
		BEGIN
			SELECT [Value] = 'Any location',[Key] = '0' 
			RETURN
		END

		SELECT [Key] = rowId, [Value] = subLocation 
		FROM tblSubLocation (NOLOCK) 
		WHERE locationId = @locationId  
		AND isActive = 1 AND partnerId<>'392577'
		ORDER BY subLocation ASC
		RETURN
	END
	RETURN
 END
 ELSE IF @flag='r'
 BEGIN
	 IF @reciverID IS NULL
	 BEGIN
		SELECT errorCode=1, msg='Select Receiver Value'
		RETURN
	 END
	  SELECT errorCode=0
				, ri.firstName 
				, ri.middleName
				, ISNULL(lastName1,' ') + ISNULL(lastName2,' ') AS lastName 
				, ri.city
				, ri.[address]
				, mobile
				, cm.countryId
				, cm.countryName
				, receiverId
				, relationship
				, Relationid =sd.valueid
				, [state]= ISNULL([state] ,'Any State')
				, StateId = CASE WHEN RI.COUNTRY = 'NEPAL' THEN cs.rowId ELSE TL.rowId END
				, locationid=partnersublocationid
				, district
				, purposeOfRemit
				, purposeOfRemitid =por.valueId
			FROM receiverInformation ri (nolocK)
			LEFT JOIN countriesStates cs (NOLOCK) ON cs.stateName = ri.state 
			LEFT JOIN tblServicewiseLocation TL (NOLOCK) ON TL.location = ri.state
			LEFT JOIN tblSubLocation TS(NOLOCK) ON TS.subLocation = ri.district
			LEFT JOIN staticDataValue sd (NOLOCK) ON sd.detailTitle = ri.relationship AND sd.typeid= 2100 AND ISNULL(sd.IS_DELETE ,'N')<>'Y'
			LEFT JOIN staticDataValue por (NOLOCK) ON por.detailTitle=ri.purposeOfRemit AND por.typeid= 3800 AND ISNULL(por.IS_DELETE ,'N')<>'Y'
			lEFT JOIN countryMaster cm(NOLOCK) ON cm.countryName = ri.country
			WHERE ri.receiverId=@reciverID
	RETURN
 END
 ELSE IF @flag ='controlno'
 BEGIN
	 SELECT  dbo.FNADecryptString(controlNo) AS controlNo  FROM vwRemitTran(NOLOCK) WHERE holdTranId = @tranid OR id = @tranid
	 RETURN
END