DB_Script/mobile_proc_customerMaster.sql

USE [FastMoneyPro_Remit]
GO

/****** Object:  StoredProcedure [dbo].[mobile_proc_customerMaster]    Script Date: 11/8/2023 8:07:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--EXEC [mobile_proc_customerMaster] @flag='refresh-customer-info',@userName='android-demo-jme@yopmail.com'
ALTER PROCEDURE [dbo].[mobile_proc_customerMaster] @flag VARCHAR(30)
	,@userName VARCHAR(100) = NULL
	,@firstName VARCHAR(50) = NULL
	,@middleName VARCHAR(50) = NULL
	,@lastName VARCHAR(50) = NULL
	,@nickName VARCHAR(100) = NULL
	,@mobileNumber VARCHAR(30) = NULL
	,@email VARCHAR(100) = NULL
	,@gender VARCHAR(15) = NULL
	,@dateOfBirth DATETIME = NULL
	,@nativeCountry VARCHAR(100) = NULL
	,@country VARCHAR(100) = NULL
	,@address VARCHAR(150) = NULL
	,@city VARCHAR(50) = NULL
	,@province VARCHAR(50) = NULL
	,@occupation VARCHAR(100) = NULL
	,@primaryBankName VARCHAR(200) = NULL
	,@primaryAccountNumber VARCHAR(100) = NULL
	,@verificationIdType VARCHAR(50) = NULL
	,@verificationIdNumber VARCHAR(50) = NULL
	,@issueDate DATETIME = NULL
	,@expiryDate DATETIME = NULL
	,@regIdcardFrontUrl VARCHAR(200) = NULL
	,@regIdcardBackUrl VARCHAR(200) = NULL
	,@passbookUrl VARCHAR(200) = NULL
	,@passportUrl VARCHAR(200) = NULL
	,@selfieUrl VARCHAR(300) = NULL
	-- CDD parameters
	,@cddCode VARCHAR(100) = NULL
	,@sourceOfFund VARCHAR(500) = NULL
	,@referralCode VARCHAR(100) = NULL
	,@sourceCustomerId INT = NULL
	,@appVersion VARCHAR(100) = NULL
	,@phoneBrand VARCHAR(100) = NULL
	,@phoneOS VARCHAR(100) = NULL
	,@fcmId VARCHAR(MAX) = NULL
	,@osVersion VARCHAR(100) = NULL
	,@fullName VARCHAR(200) = NULL
	,@passportNumber VARCHAR(100) = NULL
	,@anotherIDType VARCHAR(20) = NULL
	,@anotherIDNumber VARCHAR(20) = NULL
	,@branch VARCHAR(20) = NULL
	,@type INT = NULL
	,@url VARCHAR(200) = NULL
	,@userId INT = NULL
	,@fileType VARCHAR(25) = NULL
	,@fileName VARCHAR(200) = NULL
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
	------------------------------------------------------------------------------------------------------------
	-- select column [mobile_userRegistration].isForcePassChange
	-- #387 - Added parameter @IsForcePinChangeMob
	-- #101 - Mobile Changes
	-- #647 - change pin and password for existing customer from mobile reg
	-- #659 change in @flag='refresh-customer-info' for full name
	-- #1069 in @flag = 'refresh-customer-info' for id expiry date and status
	--#lawson card
	-- #1135 _ Notification count
	-- #1003 - Reward Points , @flag = 'get-invite-details'
	-- #1298 - changes in reward points response 
	-- #1309 - Changes in @flag = 'get-invite-details' and @flag='get-reward-fee'
	-- #1594 -  @flag = 'get-loyalty-points'
	-- #1601 - Push 500 reward points to customers , @flag = 'get-loyalty-points'
	-- Trust doc -- add ResidenceType, IsNFC
	-- #18600 added new @flag = 'get-payment-method' for exrate
	-- #19922 change in flag= 'get-invite-details' to retrieve membershipId and available reward points
	------------------------------------------------------------------------------------------------------------
	DECLARE @errorMsg VARCHAR(MAX)
		,@customerId BIGINT
		,@yearlyLimit MONEY = '3500000.00'
		,@totalSend MONEY
		,@totalSendText VARCHAR(200)
		,@YearStart DATE
		,@YearEnd DATETIME
		,@cust BIGINT = NULL;
	DECLARE @IsActive CHAR(1)
	DECLARE @redirectTo VARCHAR(100) = ''

	SET @country = '118';
	SET @firstName = UPPER(@firstName)
	SET @middleName = UPPER(@middleName)
	SET @lastName = UPPER(@lastName)
	SET @fullName = @firstName + ISNULL(' ' + @middleName, '') + ISNULL(' ' + @lastName, '')

	IF @flag = 'i'
	BEGIN
		--SELECT @customerId = customerId FROM customerMasterTemp with (nolock)  
		--WHERE email = @username OR mobile = @username
		--CHECK FOR customer IN temp table
		SELECT @customerId = customerId
		FROM customerMasterTemp WITH (NOLOCK)
		WHERE username = @userName

		IF @customerId IS NULL
		BEGIN
			IF EXISTS (
					SELECT TOP 1 'A'
					FROM customerMaster(NOLOCK)
					WHERE userName = @userName
					)
			BEGIN
				SELECT @errorMsg = 'It looks like you are already registered in JME system!'

				EXEC proc_errorHandler 1
					,@errorMsg
					,@userName

				RETURN
			END
		END

		SELECT @email = @userName

		BEGIN TRANSACTION

		UPDATE dbo.customerMasterTemp
		SET firstName = @fullName
			,fullName = @fullName
			,nickName = @nickName
			,mobile = @mobileNumber
			,homePhone = @verificationIdNumber
			,
			--email=@email,
			gender = CASE 
				WHEN UPPER(@gender) = 'M'
					THEN '97'
				WHEN UPPER(@gender) = 'F'
					THEN '98'
				ELSE '99'
				END
			,
			--dob=@dateOfBirth,
			nativeCountry = @nativeCountry
			,country = @country
			,[address] = @address
			,city = @province
			,state2 = @province
			,occupation = @occupation
			,bankName = @primaryBankName
			,bankAccountNo = @primaryAccountNumber
			,idNumber = @verificationIdNumber
			,idType = @verificationIdType
			,idIssueDate = @issueDate
			,createdDate = GETDATE()
			,sourceOfFund = CASE 
				WHEN @sourceOfFund IS NOT NULL
					THEN (
							SELECT sd.detailTitle
							FROM dbo.staticDataValue sd(NOLOCK)
							WHERE sd.valueId = @sourceOfFund
							)
				ELSE sourceOfFund
				END
			,idExpiryDate = @expiryDate
			,verifyDoc1 = @regIdcardFrontUrl
			,verifyDoc2 = @regIdcardBackUrl
			,verifyDoc3 = @passbookUrl
			,verifyDoc4 = @passportUrl
			,selfie = @selfieUrl
			,referelCode = @referralCode
		WHERE userName = @username

		INSERT INTO dbo.customerMaster (
			fullName
			,firstName
			,mobile
			,email
			,gender
			,dob
			,occupation
			,nativeCountry
			,country
			,bankName
			,bankAccountNo
			,idType
			,idNumber
			,homePhone
			,idIssueDate
			,idExpiryDate
			,sourceOfFund
			,verifyDoc1
			,verifyDoc2
			,verifyDoc3
			,SelfieDoc
			,referelCode
			,createdBy
			,createdDate
			,isActive
			,onlineUser
			,customerPassword
			,[address]
			,city
			,state2
			,customerType
			,agreeYn
			)
		SELECT fullName
			,CMT.firstName
			,mobile
			,email
			,gender
			,dob
			,CMT.occupation
			,nativeCountry
			,country
			,bankName
			,bankAccountNo
			,idType
			,idNumber
			,CMT.homePhone
			,CMT.idIssueDate
			,idExpiryDate
			,sourceOfFund
			,verifyDoc1
			,verifyDoc2
			,verifyDoc3
			,CMT.selfie
			,CMT.referelCode
			,CMT.createdBy
			,GETDATE()
			,'Y'
			,'Y'
			,customerPassword
			,[address]
			,state2
			,state2
			,4701
			,CMT.agreeYn
		FROM dbo.CustomerMasterTemp AS CMT(NOLOCK)
		WHERE CMT.customerId = @customerId

		SET @cust = @@IDENTITY

		UPDATE dbo.mobile_userRegistration
		SET customerId = @cust
		WHERE username = @username

		DELETE
		FROM customerMasterTemp
		WHERE username = @username

		COMMIT TRANSACTION

		IF @@TRANCOUNT = 0
		BEGIN
			SELECT 0 AS ERRORCODE
				,'KYC Submitted successfully' AS MSG
				,@userName AS ID
				,@cust AS EXTRA

			--SELECT 
			--	 errorCode='0'
			--	,userId=@userName
			--	,firstName=ISNULL(cm.firstName, '')
			--	,middleName=ISNULL(cm.middleName, '')
			--	,lastName=ISNULL(cm.lastName1,'')
			--	,fullname=ISNULL(cm.firstName, '') + ISNULL(' ' + cm.middleName, '') + ISNULL(' ' + cm.lastName1, '') 
			--	,mobileNumber=ISNULL(mobile,'') 
			--	,email=ISNULL(email,'') 
			--	,gender=ISNULL(sv.detailTitle,'')  
			--	,dateOfBirth=CONVERT(VARCHAR(10),dob,120)
			--	,nativeCountry=ISNULL(com1.countryName,'') 
			--	,country=ISNULL(com.countryName,'') 
			--	,address=ISNULL(address,'') 
			--	,city=ISNULL(city,'') 
			--	,province=ISNULL(cm.state2,'')
			--	,provinceId= cm.state2
			--	,occupation=ISNULL(sdv.detailTitle,'') 
			--	,primaryBankName=ISNULL(bl.BankName,'')
			--	,primaryAccountNumber=ISNULL(cm.bankAccountNo,'') 
			--	,verificationIdType=ISNULL(dv.detailTitle,'') 
			--	,verificationIdNumber=ISNULL(cm.idNumber,'') 
			--	,issueDate=CONVERT(VARCHAR(10),cm.idIssueDate,120) 
			--	,expiryDate=CONVERT(VARCHAR(10),cm.idExpiryDate,120) 
			--	,sourceOfFund=ISNULL(cm.sourceOfFund,'')
			--	,regIdcardFrontUrl=ISNULL(cm.verifyDoc1,'') 
			--	,regIdcardBackUrl=ISNULL(cm.verifyDoc2,'') 
			--	,passbookUrl=ISNULL(cm.verifyDoc3,'') 
			--	,selfieUrl=ISNULL(cm.SelfieDoc,'')
			--	,passportUrl=''
			--FROM dbo.customerMaster cm WITH(NOLOCK)
			--LEFT JOIN dbo.vwBankLists bl WITH(NOLOCK)ON cm.bankName=bl.rowId
			--LEFT JOIN dbo.staticDataValue sdv WITH(NOLOCK)ON cm.occupation=sdv.valueId
			--LEFT JOIN dbo.staticDataValue sv WITH(NOLOCK)ON cm.gender=sv.valueId
			--LEFT JOIN dbo.countryMaster com WITH(NOLOCK)ON cm.country=com.countryId
			--LEFT JOIN dbo.countryMaster com1 WITH(NOLOCK)ON cm.nativeCountry=com1.countryId
			--LEFT JOIN dbo.staticDataValue dv WITH(NOLOCK)ON cm.idType=dv.valueId
			--WHERE customerId=@cust
			RETURN
		END

		EXEC proc_errorHandler 1
			,'Failed to Submit KYC'
			,@userName

		-- SELECT errorCode='1'
		--,userId=''
		--,firstName=''
		--,middleName=''
		--,lastName=''
		--,fullname=''
		--,mobileNumber=''
		--,email=''
		--,gender=''
		--,dateOfBirth=''
		--,nativeCountry=''
		--,country=''
		--,address=''
		--,city=''
		--,province=''
		--,provinceId=''
		--,occupation=''
		--,primaryBankName=''
		--,primaryAccountNumber=''
		--,verificationIdType='' 
		--,verificationIdNumber='' 
		--,issueDate=''
		--,expiryDate=''
		--,sourceOfFund=''
		--,regIdcardFrontUrl='' 
		--,regIdcardBackUrl=''
		--,passbookUrl=''
		--,passportUrl=''
		--,selfieUrl=''
		RETURN
	END

	IF @flag = 'i-V2'
	BEGIN
		--CHECK FOR customer IN temp table  
		SELECT @customerId = customerId
		FROM customerMasterTemp WITH (NOLOCK)
		WHERE username = @userName

		--PRINT @userName
		--PRINT @customerId
		IF @customerId IS NULL
		BEGIN
			IF EXISTS (
					SELECT TOP 1 'A'
					FROM customerMaster(NOLOCK)
					WHERE EMAIL = @userName
					)
			BEGIN
				SELECT @errorMsg = 'It looks like you are already registered in JME system!'

				EXEC proc_errorHandler 1
					,@errorMsg
					,@userName

				RETURN
			END
		END

		BEGIN TRANSACTION

		UPDATE dbo.customerMasterTemp
		SET firstName = @fullName
			,fullName = @fullName
			,nickName = @nickName
			,homePhone = @verificationIdNumber
			,email = @userName
			,customerEmail = @email
			,gender = CASE 
				WHEN UPPER(@gender) = 'M'
					THEN '97'
				WHEN UPPER(@gender) = 'F'
					THEN '98'
				ELSE '99'
				END
			,dob = @dateOfBirth
			,country = @country
			,[address] = @address
			,city = @city
			,state2 = @province
			,occupation = @occupation
			,bankName = @primaryBankName
			,bankAccountNo = @primaryAccountNumber
			,idNumber = @verificationIdNumber
			,idType = @verificationIdType
			,idIssueDate = @issueDate
			,createdDate = GETDATE()
			,sourceOfFund = CASE 
				WHEN @sourceOfFund IS NOT NULL
					THEN (
							SELECT TOP 1 sd.detailTitle
							FROM dbo.staticDataValue sd(NOLOCK)
							WHERE sd.valueId = @sourceOfFund
							)
				ELSE sourceOfFund
				END
			,idExpiryDate = @expiryDate
			,verifyDoc1 = @regIdcardFrontUrl
			,verifyDoc2 = @regIdcardBackUrl
			,verifyDoc3 = @passbookUrl
			,verifyDoc4 = @passportUrl
			,selfie = @selfieUrl
			,referelCode = @referralCode
		WHERE customerId = @customerId

		INSERT INTO dbo.customerMaster (
			fullName
			,firstName
			,mobile
			,email
			,customerEmail
			,gender
			,dob
			,occupation
			,nativeCountry
			,country
			,bankName
			,bankAccountNo
			,idType
			,idNumber
			,homePhone
			,idIssueDate
			,idExpiryDate
			,sourceOfFund
			,verifyDoc1
			,verifyDoc2
			,verifyDoc3
			,SelfieDoc
			,referelCode
			,createdBy
			,createdDate
			,isActive
			,onlineUser
			,customerPassword
			,[address]
			,city
			,state2
			,customerType
			)
		SELECT fullName
			,CMT.firstName
			,mobile
			,email
			,customerEmail
			,gender
			,dob
			,CMT.occupation
			,nativeCountry
			,country
			,bankName
			,bankAccountNo
			,idType
			,idNumber
			,CMT.homePhone
			,CMT.idIssueDate
			,idExpiryDate
			,sourceOfFund
			,verifyDoc1
			,verifyDoc2
			,verifyDoc3
			,CMT.selfie
			,CMT.referelCode
			,CMT.createdBy
			,GETDATE()
			,'Y'
			,'Y'
			,customerPassword
			,[address]
			,city
			,state2
			,4701
		FROM dbo.CustomerMasterTemp AS CMT(NOLOCK)
		WHERE CMT.customerId = @customerId

		SET @cust = @@IDENTITY

		UPDATE dbo.mobile_userRegistration
		SET customerId = @cust
		WHERE username = @username

		DELETE
		FROM customerMasterTemp
		WHERE username = @username

		COMMIT TRANSACTION

		IF @@TRANCOUNT = 0
		BEGIN
			SELECT 0 AS ERRORCODE
				,'KYC Submitted successfully' AS MSG
				,@userName AS ID
				,@cust AS EXTRA

			RETURN
		END

		EXEC proc_errorHandler 1
			,'Failed to Submit KYC'
			,@userName

		RETURN
	END

	IF @flag = 'u'
	BEGIN
		IF (YEAR(GETDATE()) - YEAR(@dateOfBirth) < 16)
		BEGIN
			EXEC proc_errorHandler 1
				,'Customer Not Eligible'
				,@userName

			RETURN
		END

		----OR cm.mobile=@userName not aplicable 
		IF NOT EXISTS (
				SELECT TOP 1 'x'
				FROM dbo.customerMaster(NOLOCK) cm
				WHERE cm.email = @userName
				)
		BEGIN
			SELECT @errorMsg = 'Customer with userId ' + @userName + ' does not exists.'

			EXEC proc_errorHandler 1
				,@errorMsg
				,@userName

			RETURN
		END

		SELECT @customerId = cm.customerId
		FROM dbo.customerMaster(NOLOCK) cm
		WHERE cm.email = @userName

		--OR cm.mobile=@userName
		BEGIN TRANSACTION

		UPDATE dbo.customerMaster
		SET firstName = ISNULL(@fullName, fullName)
			,fullName = ISNULL(@fullName, fullName)
			,
			----nickName=ISNULL(@nickName,nickName),
			mobile = ISNULL(@mobileNumber, mobile)
			,email = ISNULL(@email, email)
			,gender = ISNULL(@gender, gender)
			,dob = ISNULL(@dateOfBirth, dob)
			,nativeCountry = ISNULL(@nativeCountry, nativeCountry)
			,country = ISNULL(@country, country)
			,[address] = ISNULL(@address, [address])
			,city = ISNULL(@city, city)
			,state2 = CASE 
				WHEN @province IS NOT NULL
					THEN (
							SELECT TOP 1 cim.cityName
							FROM dbo.CityMaster cim(NOLOCK)
							WHERE cim.cityId = @province
							)
				ELSE state2
				END
			,occupation = ISNULL(@occupation, occupation)
			,bankName = ISNULL(@primaryBankName, bankName)
			,bankAccountNo = ISNULL(@primaryAccountNumber, bankAccountNo)
			,idNumber = ISNULL(@verificationIdNumber, idNumber)
			,idType = ISNULL(@verificationIdType, idType)
			,idIssueDate = ISNULL(@issueDate, idIssueDate)
			,sourceOfFund = CASE 
				WHEN @sourceOfFund IS NOT NULL
					THEN (
							SELECT TOP 1 sd.detailTitle
							FROM dbo.staticDataValue sd(NOLOCK)
							WHERE sd.valueId = @sourceOfFund
							)
				ELSE sourceOfFund
				END
			,idExpiryDate = ISNULL(@expiryDate, idExpiryDate)
			,verifyDoc1 = ISNULL(@regIdcardFrontUrl, verifyDoc1)
			,verifyDoc2 = ISNULL(@regIdcardBackUrl, verifyDoc2)
			,verifyDoc3 = ISNULL(@passbookUrl, verifyDoc3)
			,SelfieDoc = ISNULL(@selfieUrl, SelfieDoc)
		FROM customermaster(NOLOCK) cust
		WHERE cust.customerId = @customerId

		IF @@TRANCOUNT > 0
			COMMIT TRANSACTION

		SELECT errorCode = '0'
			,userId = @userName
			,firstName = ISNULL(cm.firstName, '')
			,middleName = ISNULL(cm.middleName, '')
			,lastName = ISNULL(cm.lastName1, '')
			,fullname = ISNULL(cm.firstName, '') + ISNULL(' ' + cm.middleName, '') + ISNULL(' ' + cm.lastName1, '')
			,nickName = ''
			,mobileNumber = ISNULL(mobile, '')
			,email = ISNULL(email, '')
			,gender = ISNULL(sv.detailTitle, '')
			,dateOfBirth = CONVERT(VARCHAR(10), dob, 120)
			,nativeCountry = ISNULL(com1.countryName, '')
			,country = ISNULL(com.countryName, '')
			,[address] = ISNULL([address], '')
			,city = ISNULL(city, '')
			,province = ISNULL(cm.state2, '')
			,provinceId = ISNULL(cim.cityId, '')
			,occupation = ISNULL(sdv.detailTitle, '')
			,primaryBankName = 'Fast Remit Bank' --bl.BankName
			,primaryAccountNumber = ISNULL(cm.bankAccountNo, '')
			,verificationIdType = ISNULL(dv.detailTitle, '')
			,verificationIdNumber = ISNULL(cm.idNumber, '')
			,issueDate = CONVERT(VARCHAR(10), cm.idIssueDate, 120)
			,expiryDate = CONVERT(VARCHAR(10), cm.idExpiryDate, 120)
			,sourceOfFund = ISNULL(cm.sourceOfFund, '')
			,regIdcardFrontUrl = ISNULL(cm.verifyDoc1, '')
			,regIdcardBackUrl = ISNULL(cm.verifyDoc2, '')
			,passbookUrl = ISNULL(cm.verifyDoc3, '')
			,passportUrl = ''
			,selfieUrl = ISNULL(cm.SelfieDoc, '')
		FROM (
			SELECT TOP 1 *
			FROM dbo.customerMaster(NOLOCK) cm
			WHERE cm.customerId = @customerId
			) cm
		LEFT JOIN dbo.vwBankLists(NOLOCK) bl ON cm.bankName = bl.bankCode
		LEFT JOIN mobile_userRegistration(NOLOCK) v ON cm.customerId = v.customerId
		LEFT JOIN dbo.staticDataValue(NOLOCK) sdv ON cm.occupation = sdv.valueId
		LEFT JOIN dbo.staticDataValue(NOLOCK) sv ON cm.gender = sv.valueId
		LEFT JOIN dbo.countryMaster(NOLOCK) com ON cm.country = com.countryId
		LEFT JOIN dbo.staticDataValue(NOLOCK) dv ON cm.idType = dv.valueId
		LEFT JOIN dbo.countryMaster(NOLOCK) com1 ON cm.nativeCountry = com1.countryId
		LEFT JOIN dbo.CityMaster cim(NOLOCK) ON LTRIM(RTRIM(cim.cityName)) = LTRIM(RTRIM(cm.state2))

		--WHERE cm.customerId = @customerId
		RETURN
	END

	IF @flag = 's'
	BEGIN
		IF NOT EXISTS (
				SELECT TOP 1 'x'
				FROM dbo.customerMaster(NOLOCK) cm
				WHERE cm.email = @userName
					OR cm.mobile = @userName
				)
		BEGIN
			SELECT @errorMsg = 'Customer with userId ' + @userName + ' does not exists.'

			EXEC proc_errorHandler 1
				,@errorMsg
				,@userName

			RETURN
		END

		SELECT @customerId = cm.customerId
		FROM dbo.customerMaster(NOLOCK) cm
		WHERE cm.email = @userName
			OR cm.mobile = @userName

		SELECT errorCode = '0'
			,userId = @userName
			,firstName = ISNULL(cm.firstName, '')
			,middleName = ''
			,lastName = ''
			,fullname = ISNULL(cm.fullName, '')
			,nickName = ''
			,mobileNumber = ISNULL(mobile, '')
			,email = ISNULL(email, '')
			,gender = ISNULL(sv.detailTitle, '')
			,dateOfBirth = CONVERT(VARCHAR(10), dob, 120)
			,nativeCountry = ISNULL(com1.countryName, '')
			,country = 'South Korea'
			,address = ISNULL(address, '')
			,city = ISNULL(city, '')
			,province = ISNULL(cm.state2, '')
			,provinceId = ISNULL(cim.cityId, '')
			,occupation = ISNULL(sdv.detailTitle, '')
			,primaryBankName = 'Fast Remit Bank' --ISNULL(bl.BankName,'')
			,primaryAccountNumber = ISNULL(cm.bankAccountNo, '')
			,verificationIdType = ISNULL(dv.detailTitle, '')
			,verificationIdNumber = ISNULL(cm.idNumber, '')
			,issueDate = CONVERT(VARCHAR(10), cm.idIssueDate, 120)
			,expiryDate = CONVERT(VARCHAR(10), cm.idExpiryDate, 120)
			,sourceOfFund = ISNULL(cm.sourceOfFund, '')
			,regIdcardFrontUrl = ISNULL(cm.verifyDoc1, '')
			,regIdcardBackUrl = ISNULL(cm.verifyDoc2, '')
			,passbookUrl = ISNULL(cm.verifyDoc3, '')
			,selfieUrl = ISNULL(cm.SelfieDoc, '')
		FROM (
			SELECT TOP 1 *
			FROM dbo.customerMaster cm WITH (NOLOCK)
			WHERE customerId = @customerId
			) cm
		LEFT JOIN dbo.vwBankLists bl WITH (NOLOCK) ON cm.bankName = bl.bankCode
		LEFT JOIN dbo.staticDataValue sdv WITH (NOLOCK) ON cm.occupation = sdv.valueId
		LEFT JOIN dbo.staticDataValue sv WITH (NOLOCK) ON cm.gender = sv.valueId
		LEFT JOIN dbo.countryMaster com1 WITH (NOLOCK) ON cm.nativeCountry = com1.countryId
		LEFT JOIN dbo.staticDataValue dv WITH (NOLOCK) ON cm.idType = dv.valueId
		LEFT JOIN dbo.CityMaster cim(NOLOCK) ON cim.cityName = cm.state2

		--WHERE customerId=@customerId
		RETURN
	END

	IF @flag = 'getUser' --customer due deligience(static data values)
	BEGIN
		-- OR cm.mobile=@userName USE IN FUTURE
		IF EXISTS (
				SELECT TOP 1 'x'
				FROM dbo.customerMaster(NOLOCK) cm
				WHERE cm.email = @userName
				)
		BEGIN
			--DECLARE @yearlyLimit VARCHAR(100)=''
			--DECLARE @YearStart DATE, @YearEnd DATETIME
			SELECT @YearStart = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
				,@YearEnd = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, - 1) + ' 23:59:59'

			--deCLARE @CUSTID BIGINT
			SELECT @customerId = customerId
			FROM dbo.customerMaster(NOLOCK) cm
			WHERE cm.email = @userName

			--OR cm.mobile=@userName  USE IN FUTURE
			SELECT @totalSend = SUM(ROUND(R.tAmt / (R.sCurrCostRate + R.sCurrHoMargin), 2, 0))
			FROM REMITTRAN R(NOLOCK)
			INNER JOIN TRANSENDERS T(NOLOCK) ON T.TRANID = R.ID
				AND T.CUSTOMERID = @customerId
				AND R.TRANSTATUS <> 'Cancel'
				AND R.approvedDate BETWEEN @YearStart
					AND @YearEnd

			SELECT @yearlyLimit = amount
			FROM dbo.csDetail CD(NOLOCK)
			INNER JOIN csMaster CM(NOLOCK) ON CM.csMasterId = CD.csMasterId
				AND CD.period = 365
				AND CD.condition = 4600
				AND ISNULL(CD.isActive, 'Y') = 'Y'
				AND ISNULL(CD.isDeleted, 'N') = 'N'
				AND ISNULL(CD.isEnable, 'Y') = 'Y'
				AND ISNULL(CM.isActive, 'Y') = 'Y'
				AND ISNULL(CM.isDeleted, 'N') = 'N'

			SET @yearlyLimit = (@yearlyLimit - ISNULL(@totalSend, 0))

			SELECT @customerId = cm.customerId
			FROM dbo.customerMaster(NOLOCK) cm
			WHERE cm.email = @userName

			--OR cm.mobile=@userName ## not applicable 
			SELECT errorCode = '0'
				,userId = @userName
				,firstName = ISNULL(cm.firstName, '')
				,middleName = ISNULL(cm.middleName, '')
				,lastName = ISNULL(cm.lastName1, '')
				,nickName = ''
				,email = ISNULL(cm.email, '')
				,mobileNumber = ISNULL(cm.mobile, '')
				,verificationCode = ISNULL(ur.OTP, '')
				,VerificationCodeExpiryDate = ''
				,createdDate = CONVERT(VARCHAR(10), ur.createdDate, 120)
				,userRoles = ''
				,rewardPoint = CAST(ISNULL(cm.bonusPoint, 0) AS DECIMAL)
				,isActive = CASE 
					WHEN ISNULL(cm.isActive, 'Y') = 'Y'
						THEN 1
					ELSE 0
					END
				,hasKYC = CASE 
					WHEN ISNULL(cm.createdDate, '') <> ''
						THEN 1
					ELSE 0
					END
				,isVerified = CASE 
					WHEN cm.approvedDate IS NOT NULL
						THEN 1
					ELSE 0
					END
				,forgetCode = ISNULL(ur.passRecoveryCode, '')
				,ForgetCodeExpiryDate = ''
				,primaryBankName = 'Fast Remit Bank' --CASE WHEN cm.customerType='11048' THEN N'WSB (050)' ELSE 'Kwangju Bank (034)' END
				,walletNumber = ISNULL(cm.walletAccountNo, '')
				,availableBalance = @yearlyLimit --   CAST([dbo].FNAGetCustomerACBal(@userName) AS DECIMAL) --change this with yearly limit after fix in mobile
				,dpUrl = ''
				,ISNULL(ur.cmRegistrationId, '') cmRegistrationId
				,yearlyLimit = FORMAT(@yearlyLimit, '0,00') --@yearlyLimit
			FROM (
				SELECT TOP 1 *
				FROM dbo.customerMaster cm WITH (NOLOCK)
				WHERE customerId = @customerId
				) cm
			LEFT JOIN dbo.mobile_userRegistration(NOLOCK) ur ON ur.customerId = cm.customerId
			LEFT JOIN dbo.vwBankLists bl WITH (NOLOCK) ON bl.bankCode = cm.bankName

			--WHERE cm.customerId=@customerId
			RETURN
		END
		ELSE
		BEGIN
			SELECT @errorMsg = 'Customer with userId ' + @userName + ' does not exists.'

			EXEC proc_errorHandler 1
				,@errorMsg
				,@userName

			RETURN
		END
	END

	IF @flag = 'refresh-customer-info'
	BEGIN
		DECLARE @isExistingCustomer BIT
			,@hasUpdatedDefaultCredentials INT
			,@createdFrom CHAR(1)
			,@isForcedPwdChange INT
			,@isForcePassChangeMob INT
			,@isForcePinChangeMob INT
			,@registrationType VARCHAR(100)
		DECLARE @notificationCount INT = 0
			,@showInviteCode VARCHAR(1) = 'Y'

		--SELECT @yearlyLimit = amount
		--FROM dbo.csDetail CD(NOLOCK)
		--INNER JOIN csMaster CM(NOLOCK) ON CM.csMasterId = CD.csMasterId
		--	AND CD.period = 365
		--	AND CD.condition = 4600
		--	AND ISNULL(CD.isActive, 'Y') = 'Y'
		--	AND ISNULL(CD.isDeleted, 'N') = 'N'
		--	AND ISNULL(CD.isEnable, 'Y') = 'Y'
		--	AND ISNULL(CM.isActive, 'Y') = 'Y'
		--	AND ISNULL(CM.isDeleted, 'N') = 'N'
		PRINT 'A';
		IF EXISTS (
				SELECT TOP 1 'A'
				FROM CustomerMasterTemp(NOLOCK)
				WHERE username = @userName
					AND isActive = 'Y'
				)
		BEGIN
			SET @isExistingCustomer = 1
			
			IF EXISTS (
					SELECT *
					FROM CustomerMasterTemp CT(NOLOCK)
					WHERE username = @userName
						AND OldCustomerId IS NULL
					)
			BEGIN
				SET @isExistingCustomer = 0
			END

			UPDATE mobile_userRegistration
			SET appVersion = @appVersion
				,phoneBrand = @phoneBrand
				,phoneOS = @phoneOS
				,deviceId = ISNULL(@fcmId, deviceId)
				,osVersion = @osVersion
				
			WHERE customerId = (
					SELECT TOP 1 customerId
					FROM CustomerMasterTemp(NOLOCK)
					WHERE username = @userName
						AND isActive = 'Y'
					)

			SELECT '0' ErrorCode
				,@userName userId
				,cust.customerId SenderId
				,ISNULL(cust.fullName, @userName) firstName			
				,ISNULL(cust.email, '') email
				,ISNULL(cust.customerEmail, '') AS customerEmail
				,ISNULL(cust.mobile, '') phone
				,CONVERT(VARCHAR(10), cust.dob, 120) dob
				--,cust.idType AS idType
				--,cust.idNumber AS idNumber
				,'' AS countryCode
				,CAST(ISNULL(cust.bonusPoint, 0) AS DECIMAL) rewardPoint
				,CASE 
					WHEN ISNULL(cust.isActive, 'N') = 'Y'
						THEN 1
					ELSE 0
					END active
				,KYC=0 
				,verified=0 
				,'' walletNumber
				,0 availableBalance
				,'Fast Remit Bank' primaryBankName
				,ISNULL(dpUrl, '') dpUrl
				,ISNULL(ur.cmRegistrationId, '') cmRegistrationId
				,ISNULL(co.countryName, '') country
				,'' city
				,'' [address]
				,'' [province]
				,'' [provinceId]
				,CASE 
					WHEN cust.referelCode IS NOT NULL
						THEN 1
					ELSE 0
					END isReferred
				,'' sourceId
				,yearlyLimit = FORMAT(@yearlyLimit, '0,00')
				,PennyTestStatus = '2'
				,'' accessTokenRegTime
				,'' accessTokenExpTime
				,redirectTo = ISNULL(cust.registrationType,'EKYC')
				,cust.membershipId AS referelCode				
				, 'true' agreeYn
				,isExistingCustomer = @isExistingCustomer
				,hasUpdatedDefaultCredentials = '2'
				,hasVerifiedOTP = CASE 
					WHEN ISNULL(isEmailVerified, 0) = 0
						THEN 'False'
					ELSE 'True'
					END
				,IsBiometricLogin = 0
				,BiometricLoginType = ''			
				,@notificationCount notificationCount							
				,CONVERT(VARCHAR(10), cust.idExpiryDate, 121) IDExpiryDate
				,CASE 
					WHEN GETDATE() > cust.idExpiryDate
						THEN 'Expired'
					ELSE 'Valid'
					END idStatus
				,showInviteCode = ISNULL(@showInviteCode, 'N')
				,RewardPoints = 0
				,ISNULL(ur.ResidenceType, 'FOREIGNER') ResidenceType
				,ISNULL(ur.UseNFC, 'Y') UseNFC
				,cust.SelfieDoc
				,KycVerified= 0
				,KycStatus= ISNULL(verificationCode,'NOT_COMPLETED')
				,KycStatusMsg= CASE verificationCode WHEN 'NOT_COMPLETED'
								THEN 'Not Completed' WHEN 'PROCESSING' THEN  'ID Document Submission is in Processing' 
								WHEN 'COMPLETED' THEN 'KYC Completed' ELSE 'NOT COMPLETED' END
			FROM (
				SELECT TOP 1 *
				FROM CustomerMasterTemp(NOLOCK) cust
				WHERE cust.username = @userName
				) cust
			LEFT JOIN mobile_userRegistration(NOLOCK) ur ON cust.customerId = ur.customerId
			LEFT JOIN countryMaster co(NOLOCK) ON cust.country = co.countryId
			LEFT JOIN dbo.CityMaster cm(NOLOCK) ON cust.state2 = cm.cityName
			WHERE ur.username = @userName
			RETURN
		END

		DECLARE @customerStatus VARCHAR(5)
			,@ekycPinUpdated BIT
			,@deviceType VARCHAR(20)
		SELECT @customerId = cm.customerId
			,@IsActive = isActive
			,@createdFrom = ISNULL(createdFrom, 'O')
			,@isForcedPwdChange = ISNULL(isForcedPwdChange, 2)
			,@isExistingCustomer = ISNULL(isexistingcustomer, 0)
			,@isForcePassChangeMob = ISNULL(ur.isForcePassChange, 0)
			,@isForcePinChangeMob = ISNULL(ur.isForcePinChange, 0)
			,@registrationType = ISNULL(cm.registrationType, '')
			,@customerStatus = cm.customerStatus
			,@ekycPinUpdated = ISNULL(ur.ekycPinUpdated, 0)
			,@deviceType = ur.DeviceType
		FROM dbo.customerMaster(NOLOCK) cm
		INNER JOIN mobile_userRegistration(NOLOCK) ur ON cm.customerId = ur.customerId
		WHERE cm.username = @userName

		PRINT @customerId;
		--notification count
		SELECT @notificationCount = count(*)
		FROM pushNotificationHistroy
		WHERE isRead = 0
			AND type = 0
			AND customerId = @customerId

		DECLARE @rewardPoints INT = 0;

		SET @rewardPoints = CAST(DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@customerId) AS INT);

		-- IF EXISTS (
		-- SELECT 'x'
		-- FROM IntroducerCommissionSetup
		-- WHERE IntroducerId = @customerId
		-- )
		-- BEGIN
		-- SET @showInviteCode = 'N'
		-- END

		--SELECT @customerStatus = customerstatus
		--FROM customerMaster
		--WHERE customerid = @customerId

		--IF @isForcedPwdChange <> 0
		--BEGIN
		--	IF @createdFrom = 'C'
		--		AND @customerStatus = 'OTC'
		--		SET @hasUpdatedDefaultCredentials = 2

		--	IF @createdFrom = 'C'
		--		AND @customerStatus IS NULL
		--		SET @hasUpdatedDefaultCredentials = 0

		--	IF @createdFrom = 'M'
		--		AND @isExistingCustomer = 1
		--		SET @hasUpdatedDefaultCredentials = 0

		--	IF @createdFrom = 'M'
		--		AND @isExistingCustomer = 0
		--		SET @hasUpdatedDefaultCredentials = 1 --change Pin

		--	IF @createdFrom = 'A'
		--		SET @hasUpdatedDefaultCredentials = 2 --change pin & password

		--	IF @createdFrom = 'O'
		--		SET @hasUpdatedDefaultCredentials = 0
		--END
		--ELSE
		--	SET @hasUpdatedDefaultCredentials = 0

		--IF (
		--		@hasUpdatedDefaultCredentials = 0
		--		AND @isForcePassChangeMob = 1
		--		)
		--BEGIN
		--	SET @hasUpdatedDefaultCredentials = 3 -- change password only
		--END
		--ELSE IF (
		--		@hasUpdatedDefaultCredentials = 0
		--		AND @isForcePinChangeMob = 1
		--		)
		--BEGIN
		--	SET @hasUpdatedDefaultCredentials = 4 -- change pin only
		--END
		--ELSE
		--BEGIN
		--	IF @createdFrom = 'M'
		--		AND @isExistingCustomer = 0
		--		AND ISNULL(@registrationType, '') = 'EKYC'
		--		AND @ekycPinUpdated = 0 --AND ISNULL(@deviceType,'IOS')<>'Android'
		--		SET @hasUpdatedDefaultCredentials = 5 --change Pin
		--END

		--OR cm.mobile=@userName future use
		DECLARE @hasPennyTestDone VARCHAR(1) = '0'

		SELECT @hasPennyTestDone = '1'

		IF @customerId IS NULL
		BEGIN
			EXEC proc_errorHandler 1
				,'Unauthorized access found, Please contact JME support'
				,@userName

			RETURN
		END

		--IF EXISTS (
		--		SELECT TOP 1 'x'
		--		FROM dbo.customerMaster(NOLOCK)
		--		WHERE approvedDate < '2018-12-19'
		--			AND customerId = @customerId
		--		)
		--BEGIN
		--	SET @hasPennyTestDone = '2'
		--END

		SET @hasPennyTestDone = '2'

		IF ISNULL(@IsActive, 'N') <> 'Y'
		BEGIN
			EXEC proc_errorHandler 1
				,'Your account has blocked, Please contact JME support'
				,@userName

			RETURN
		END

		SELECT @YearStart = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
			,@YearEnd = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, - 1) + ' 23:59:59'

		--SELECT @totalSend = SUM(R.tAmt) --SUM(ROUND(R.tAmt/(R.sCurrCostRate+R.sCurrHoMargin), 2, 0))
		--FROM REMITTRAN R(NOLOCK)
		--INNER JOIN TRANSENDERS T(NOLOCK) ON T.TRANID = R.ID
		--	AND T.CUSTOMERID = @customerId
		--	AND R.TRANSTATUS <> 'Cancel'
		--	AND R.approvedDate BETWEEN @YearStart
		--		AND @YearEnd
		SET @yearlyLimit = (@yearlyLimit - ISNULL(@totalSend, 0))

		UPDATE mobile_userRegistration
		SET appVersion = @appVersion
			,phoneBrand = @phoneBrand
			,phoneOS = @phoneOS
			,deviceId = ISNULL(@fcmId, deviceId)
			,osVersion = @osVersion
		WHERE customerId = @customerId

		SELECT '0' ErrorCode
			,@userName userId
			,cust.customerId SenderId
			,ISNULL(cust.fullName, '') firstName			
			,ISNULL(cust.email, '') email
			,ISNULL(cust.customerEmail, '') AS customerEmail
			,ISNULL(cust.mobile, '') phone
			,CONVERT(VARCHAR(10), cust.dob, 120) dob
			--,cust.idType AS idType
			--,cust.idNumber AS idNumber
			,cm1.countryCode AS countryCode
			,CAST(ISNULL(@rewardPoints, 0) AS DECIMAL) rewardPoint
			,CASE 
				WHEN ISNULL(cust.isActive, 'N') = 'Y'
					THEN 1
				ELSE 0
				END active
			,CASE  lawsoncardno WHEN 'KYC_LATER' THEN 1 ELSE  ISNULL(HasDeclare, 0) END  kyc
			,CASE 
			      WHEN ISNULL(HasDeclare, 0) =0 THEN 0
				WHEN  ISNULL(mobileApprovedDate,mobileVerifiedDate) IS NOT NULL
					THEN 1
				ELSE 0
				END verified
				,walletAccountNo walletNumber
			,0 availableBalance
			--,ISNULL(bl.BankName,'') primaryBankName
			,cust.username
			,primaryBankName = 'Fast Remit Bank' --CASE WHEN cust.customerType='11048' THEN N'저축은행 (050)' ELSE 'Kwangju Bank (034)' END
			,'' dpUrl
			,ISNULL(ur.cmRegistrationId, '') cmRegistrationId
			,ISNULL(co.countryName, '') country
			,ISNULL(cust.city, '') city
			,ISNULL(cust.[address], '') [address]
			,ISNULL(cust.state2, '') [province]
			,ISNULL(cm.cityId, '') [provinceId]
			,CASE 
				WHEN cust.referelCode IS NOT NULL
					THEN 1
				ELSE 0
				END isReferred
			,ISNULL(sdv.valueId, '') sourceId
			,yearlyLimit = FORMAT(@yearlyLimit, '0,00')
			,PennyTestStatus = @hasPennyTestDone -----0 not started, 1 requested , 2 completed
			,kcm.accessTokenRegTime
			,kcm.accessTokenExpTime
			,redirectTo = ISNULL(cust.RegistrationType, 'EKYC') --@redirectTo
			,cust.membershipId AS referelCode
			,agreeYn = CASE  lawsoncardno WHEN 'KYC_LATER' THEN 'true' ELSE  ISNULL(cust.agreeYn, 'false') END
			,isExistingCustomer = ISNULL(isExistingCustomer, 1)
			,hasUpdatedDefaultCredentials = ISNULL(@hasUpdatedDefaultCredentials, 0)
			,hasVerifiedOTP = ISNULL(isEmailVerified, 0)
			,IsBiometricLogin = ISNULL(IsBiometricLogin, 0)
			,BiometricLoginType = ISNULL(BiometricLoginType, '')
			--,CASE  ur.isForcePassChange WHEN '1' THEN 'Y' ELSE 'N' END IsForcedPwdChange
			,@notificationCount notificationCount
			,CONVERT(VARCHAR(10), cust.idExpiryDate, 121) IDExpiryDate
			,CASE 
				WHEN GETDATE() > cust.idExpiryDate
					THEN 'Expired'
				ELSE 'Valid'
				END idStatus
			,showInviteCode = ISNULL(@showInviteCode, 'N')
			,RewardPoints = ISNULL(@rewardPoints, 0)
			,ISNULL(ur.ResidenceType, 'FOREIGNER') ResidenceType
			,ISNULL(ur.UseNFC, 'Y') UseNFC
			,REPLACE(cust.SelfieDoc, '\\', '/') SelfieDoc
	    	,KycVerified= ISNULL(isVerifiedByCustomer,0)
			,KycStatus= ISNULL(verificationCode,'NOT_COMPLETED')
			,KycStatusMsg= CASE verificationCode WHEN 'NOT_COMPLETED'
			                THEN 'Not Completed' WHEN 'PROCESSING' THEN  'ID Document Submission is in Processing' 
							WHEN 'COMPLETED' THEN 'KYC Completed' ELSE 'NOT COMPLETED' END
		FROM (
			SELECT TOP 1 *
			FROM customerMaster(NOLOCK) cust
			WHERE cust.customerId = @customerId
			) cust
		LEFT JOIN dbo.countryMaster AS CM1 ON cm1.countryId = cust.nativeCountry
		LEFT JOIN mobile_userRegistration(NOLOCK) ur ON cust.customerId = ur.customerId
		LEFT JOIN dbo.vwBankLists(NOLOCK) bl ON cust.bankName = bl.rowId
		LEFT JOIN countryMaster co(NOLOCK) ON cust.country = co.countryId
		LEFT JOIN staticDatavalue sdv(NOLOCK) ON cust.sourceOfFund = sdv.detailTitle
			AND sdv.typeID = '3900'
			AND ISNULL(sdv.IS_DELETE, 'N') = 'N'
		LEFT JOIN dbo.CityMaster cm(NOLOCK) ON cust.state2 = cm.cityName
		LEFT JOIN dbo.KFTC_CUSTOMER_MASTER(NOLOCK) kcm ON cust.customerId = kcm.customerId
		WHERE cust.customerId = @customerId AND ISNULL(sdv.IS_DELETE,'N')='N'	
		RETURN
	END

	IF @flag = 'get-invite-details'
	BEGIN
		--	PRINT @sourceCustomerId;
		DECLARE @totalCustomerCount INT
			,@registrationMoney MONEY
			,@transactionMoney MONEY

		IF EXISTS (
				SELECT 'X'
				FROM CustomerMaster(NOLOCK)
				WHERE CUSTOMERID = @sourceCustomerId
				)
		BEGIN
			

			IF OBJECT_ID('tempdb..#Reward') IS NOT NULL
				DROP TABLE #Reward

			CREATE TABLE #Reward (
				DESTINATION_CUSTOMERID INT
				,REWARD_TYPE VARCHAR(50)
				,REWARD_AMOUNT INT
				,CUSTOMER_NAME VARCHAR(200)
				,CREATED_DATE VARCHAR(10)
				,REWARD_POINTS VARCHAR(100)
				,REFERRAL_CODE VARCHAR(200)
				)

			INSERT INTO #Reward (
				DESTINATION_CUSTOMERID
				,REWARD_TYPE
				,REWARD_AMOUNT
				,CREATED_DATE
				,REWARD_POINTS
				,REFERRAL_CODE
				)
			SELECT destinationCustomerId
				,CASE 
					WHEN codeType = 'REGISTRATION'
						THEN 'Successful Registration'
					WHEN codeType = 'FIRST_TXN'
						THEN 'First Transaction Done'
					WHEN codeType = 'REDEEM'
						THEN 'Points Used'
					WHEN codeType = 'MULTI_TXN'
						THEN 'JME Reward - Multi Transaction'
					END
				,CASE 
					WHEN trantype = 'DR'
						THEN - CAST(ISNULL(AMOUNT, 0) AS INT)
					ELSE CAST(ISNULL(AMOUNT, 0) AS INT)
					END
				,CONVERT(VARCHAR(10), cp.createdDate, 121)
				,CAST(dbo.[FNA_GET_AVAILABLE_BALANCE_POINTS](@sourceCustomerId) AS INT) AS [rewardMoney]
				--,(SELECT membershipId FROM customerMaster WHERE customerId = @sourceCustomerId) AS [referralCode]
				,    ISNULL((SELECT membershipId FROM customerMaster WHERE customerId = @sourceCustomerId), '') AS referralCode

			FROM Customer_Promotion CP(NOLOCK)
			WHERE sourceCustomerId = @sourceCustomerId
				AND [STATUS] = 1

			UPDATE R
			SET CUSTOMER_NAME = ISNULL(fullName, '')
			FROM #Reward R
			INNER JOIN customerMaster CM(NOLOCK) ON R.DESTINATION_CUSTOMERID = customerId

		

			SELECT @totalCustomerCount = COUNT(destinationCustomerId)
			FROM Customer_Promotion CP(NOLOCK)
			WHERE 1 = 1
				AND sourceCustomerId = @sourceCustomerId
				AND codeType = 'REGISTRATION'
				AND [status] = '1'

			--SELECT @registrationMoney = ISNULL(SUM(amount), 0)
			--FROM Customer_Promotion CP(NOLOCK)
			--WHERE 1 = 1
			--	AND sourceCustomerId = @sourceCustomerId
			--	AND rewardType = 'REGISTRATION'
			--	AND [status] = '1'
			--	AND tranType = 'CR'
			--SELECT @transactionMoney = ISNULL(SUM(amount), 0)
			--FROM Customer_Promotion CP(NOLOCK)
			--WHERE 1 = 1
			--	AND sourceCustomerId = @sourceCustomerId
			--	AND rewardType = 'TRANSACTION'
			--	-- AND [status]  = '1' Total
			--	AND tranType = 'CR'
			-- select * from customer_promotion
			SELECT DISTINCT @totalCustomerCount AS [totalReferral]
				,CAST(dbo.[FNA_GET_AVAILABLE_BALANCE_POINTS](@sourceCustomerId) AS INT) AS [rewardMoney]
				--,(isnull(@registrationMoney, 0) + isnull(@transactionMoney, 0)) AS [rewardMoney]
				,cm.membershipId AS [inviteCode]
				--,'Earned ' + CAST(@registrationMoney AS VARCHAR) + ' JPY for Regisration. ' + CAST(@transactionMoney AS VARCHAR) + ' for first Txn' AS [inviteMessage]
				,customerId AS sourceCustomerId
			FROM CUSTOMERMASTER CM(NOLOCK)
			LEFT JOIN Customer_Promotion CP(NOLOCK) ON CM.customerId = CP.sourceCustomerId
			WHERE 1 = 1
				AND CM.customerId = @sourceCustomerId

		SELECT *
			FROM #Reward

			SELECT @totalCustomerCount = COUNT(destinationCustomerId)
			FROM Customer_Promotion CP(NOLOCK)
			WHERE 1 = 1
				AND sourceCustomerId = @sourceCustomerId
				AND codeType = 'REGISTRATION'
				AND [status] = '1'

			--SELECT @registrationMoney = ISNULL(SUM(amount), 0)
			--FROM Customer_Promotion CP(NOLOCK)
			--WHERE 1 = 1
			--	AND sourceCustomerId = @sourceCustomerId
			--	AND rewardType = 'REGISTRATION'
			--	AND [status] = '1'
			--	AND tranType = 'CR'
			--SELECT @transactionMoney = ISNULL(SUM(amount), 0)
			--FROM Customer_Promotion CP(NOLOCK)
			--WHERE 1 = 1
			--	AND sourceCustomerId = @sourceCustomerId
			--	AND rewardType = 'TRANSACTION'
			--	-- AND [status]  = '1' Total
			--	AND tranType = 'CR'
			-- select * from customer_promotion
			SELECT DISTINCT @totalCustomerCount AS [totalReferral]
				,CAST(dbo.[FNA_GET_AVAILABLE_BALANCE_POINTS](@sourceCustomerId) AS INT) AS [rewardMoney]
				--,(isnull(@registrationMoney, 0) + isnull(@transactionMoney, 0)) AS [rewardMoney]
				,cm.membershipId AS [inviteCode]
				--,'Earned ' + CAST(@registrationMoney AS VARCHAR) + ' JPY for Regisration. ' + CAST(@transactionMoney AS VARCHAR) + ' for first Txn' AS [inviteMessage]
				,customerId AS sourceCustomerId
			FROM CUSTOMERMASTER CM(NOLOCK)
			LEFT JOIN Customer_Promotion CP(NOLOCK) ON CM.customerId = CP.sourceCustomerId
			WHERE 1 = 1
				AND CM.customerId = @sourceCustomerId

					
		END
		ELSE
		BEGIN
			SELECT @errorMsg = 'Customer does not exists.'

			EXEC proc_errorHandler 1
				,@errorMsg
				,@sourceCustomerId

			RETURN
		END
	END

	IF @flag = 'get-reward-fee'
	BEGIN
		DECLARE @showRewardPoints CHAR(1) = 'N'

		IF EXISTS (
				SELECT 'x'
				FROM Customer_Promotion(NOLOCK)
				WHERE sourceCustomerId = @sourceCustomerId
				)
		BEGIN
			SET @showRewardPoints = 'Y'
		END

		SELECT CAST(DBO.FNA_GET_AVAILABLE_BALANCE_POINTS(@sourceCustomerId) AS INT) AS [point]
			,showRewardPoint = ISNULL(@showRewardPoints, 'N')
	END

	IF @flag = 'get-loyalty-points'
	BEGIN
		DECLARE @tranCount INT = 0;
		DECLARE @enable CHAR(1) = 'Y';
		DECLARE @totalCount INT
			,@isFreeSc CHAR(1) = 'N'

		EXEC PROC_Customer_Loyalty @flag = 'get-tranCount'
			,@customerId = @sourceCustomerId
			,@tranCount = @tranCount OUT

		SELECT @totalCount = (
				SELECT TOP 1 txnCount
				FROM schemeSetup(NOLOCK)
				WHERE isActive = 'Y'
				ORDER BY createdDate DESC
				)

		IF @tranCount >= @totalCount
		BEGIN
			SET @isFreeSc = 'Y'
		END

		IF @totalCount IS NULL
		BEGIN
			SET @totalCount = 5
			SET @enable = 'N';
		END

		SELECT ISNULL(@tranCount, 0) AS tranCount
			,ISNULL(@totalCount, 0) AS totalCount
			,@isFreeSc AS isFreeSc
			,requiredCount = CASE 
				WHEN (ISNULL(@totalCount, 0) - ISNULL(@tranCount, 0)) > 0
					THEN (ISNULL(@totalCount, 0) - ISNULL(@tranCount, 0))
				ELSE 0
				END
			,@enable IsActive
	END

	IF @flag = 'save-profile'
	BEGIN
	  DECLARE @fileDescription VARCHAR(10) = NULL 
	  SET @fileDescription = 'Selfie'
	  SELECT @customerId = customerId FROM customerMaster (NOLOCK) WHERE username = @userName

	  IF EXISTS (SELECT TOP 1 1 FROM customerDocument (NOLOCK) WHERE customerId = @customerId AND fileDescription = @fileDescription)
	  BEGIN
		UPDATE customerDocument SET fileName = @fileName , fileType = @filetype , modifiedBy = @userName , modifiedDate = GETDATE() 
		WHERE customerId = @customerId and fileDescription = @fileDescription
	  END
	  ELSE
	  BEGIN
		 INSERT INTO customerDocument ( 
				customerId
				,[fileName]
				,fileDescription
				,fileType
				,createdBy
				,createdDate 
				,approvedBy
				,approvedDate
				,isProfilePic
				,documentType
			)
			SELECT 
				@customerId
				,@fileName
				,@fileDescription
				,@fileType
				,@userName
				,GETDATE()
				,@userName
				,GETDATE()
				,'1'
				,'11440'  --  Customer Selfie
	  END

		IF EXISTS (
				SELECT TOP 1 1
				FROM customerMaster(NOLOCK)
				WHERE username = @username
				)
		BEGIN
			UPDATE customerMaster
			SET SelfieDoc = @url
			WHERE username = @username

			SELECT '0' ErrorCode
				,'Customer profile updated successfully' Msg
				,@username Id
				,@fileName Extra

		END
		ELSE
		BEGIN
			SELECT '1' ErrorCode
				,'Username not found.' Msg
				,@username Id

			RETURN
		END
	END


	IF @flag = 'get-membershipId'
	BEGIN
		--IF EXISTS (
		--		SELECT TOP 1 1
		--		FROM customerMaster(NOLOCK)
		--		WHERE customerid = @customerId
		--		)
		--BEGIN
		SELECT membershipId,CONVERT(VARCHAR(10), createdDate,121) createdDate
		FROM customerMaster
		WHERE customerId = @userId
			--	RETURN ;
			--END
			--SELECT NULL membershipId
	END
	IF @flag = 'get-ResidenceType'
	BEGIN
		
		select  CASE  WHEN  ResidenceType='RESIDENCE' THEN 'RESIDENT' WHEN ( (ResidenceType ='0' OR  ResidenceType IS NULL) AND idType='11168' ) THEN 'FOREIGNER' 
		 WHEN ( (ResidenceType ='0' OR  ResidenceType IS NULL) AND idType='11079' ) THEN 'RESIDENT'
		ELSE ISNULL(ResidenceType,'')  END  ResidenceType , idType FROM mobile_userRegistration  mu inner join
		customerMaster cm on mu.customerId= cm.customerId
		WHERE cm.username = @userName
		UNION ALL 
		select  CASE  WHEN  ResidenceType='RESIDENCE' THEN 'RESIDENT' WHEN ( (ResidenceType ='0' OR  ResidenceType IS NULL) AND idType='11168' ) THEN 'FOREIGNER' 
		 WHEN ( (ResidenceType ='0' OR  ResidenceType IS NULL) AND idType='11079' ) THEN 'RESIDENT'
		ELSE ISNULL(ResidenceType,'')  END  ResidenceType , idType FROM mobile_userRegistration  mu inner join
		CustomerMasterTemp cm on mu.customerId= cm.customerId
		WHERE cm.username = @userName
			--	RETURN ;
			--END
			--SELECT NULL membershipId
	END

	IF @flag = 'get-payment-method'
	BEGIN
		SELECT CAST(detailDesc AS DECIMAL(7,2) ) detailDesc ,
		REPLACE(REPLACE(detailTitle, CHAR(13), ''), CHAR(10), '') detailTitle , CASE detailTitle WHEN 'ONLINE' THEN 'Online Banking(Best Rate)'   WHEN 'DEBIT_CARD' THEN  'DEBIT CARD' ELSE 'E-Banking (Good rate)' END AS DisplayText
		FROM staticDataValue WHERE typeId = '8109'
		RETURN
	END

	IF @flag = 'get-profile-details'
	BEGIN
		SELECT TOP 1 cmt.firstName
				,fullName
				,gender = CASE 
					WHEN gender = 97
						THEN 'Male'
					WHEN gender = 98
						THEN 'Female'
					ELSE 'OTHERS'
					END				
				,CONVERT(VARCHAR(10), dob, 120) AS dob
				,email AS email
				,city				
				,address
				,ADDITIONALADDRESS
				,nativeCountry = cm.countryName							
				,idNumber AS passportNumber
				,CONVERT(VARCHAR(10), idIssueDate, 120) AS passportIssueDate
				,CONVERT(VARCHAR(10), idExpiryDate, 120) AS passportExpiryDate
				,idIssueCountry				
				,mobile			
				,referelCode
				,customerId AS userId
				,zipCode
			FROM dbo.customerMaster(NOLOCK) cmt			
			LEFT JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryId = cmt.nativeCountry
			WHERE username = @email

		RETURN
	END
END TRY

BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION

	DECLARE @errorMessage VARCHAR(MAX)

	SET @errorMessage = ERROR_MESSAGE()

	SELECT '1' ErrorCode
		,@errorMessage Msg
		,NULL ID
END CATCH
GO