DB_Script/mobile_proc_online_customerMaster_V1.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[mobile_proc_online_customerMaster_V1]    Script Date: 1/31/2024 1:32:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[mobile_proc_online_customerMaster_V1] (
	--EXEC mobile_proc_online_customerMaster_V1 @flag='detail',@customerId='1'
	@flag VARCHAR(20)
	,@customerId VARCHAR(100) = NULL
	,@username VARCHAR(100) = NULL
	,@postalCode VARCHAR(100) = NULL
	,@address1 NVARCHAR(200) = NULL
	,@address2 NVARCHAR(200) = NULL
	,@city VARCHAR(100) = NULL
	,@idType VARCHAR(100) = NULL
	,@idTypeNumber VARCHAR(100) = NULL
	,@idIssuingCountry VARCHAR(5) = NULL
	,@idStartDate VARCHAR(20) = NULL
	,@idEndDate VARCHAR(20) = NULL
	)
AS
	;

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN
	IF @flag = 'detail'
	BEGIN
		--ID TYPE
		SELECT valueId AS id
			,detailTitle AS [text]
			,isBackRequired = CASE sv.valueId
				WHEN '10997'
					THEN 0
				ELSE 1
				END
		INTO #ID_TYPE
		FROM countryIdType CID WITH (NOLOCK)
		INNER JOIN staticDataValue SV WITH (NOLOCK) ON CID.IdTypeId = SV.valueId
		WHERE ISNULL(SV.ISActive, 'Y') = 'Y'
			AND ISNULL(isDeleted, 'N') <> 'Y'
			AND COUNTRYID = 233

		IF EXISTS (
				SELECT 'x'
				FROM dbo.customerMasterTemp(NOLOCK)
				WHERE customerId = @customerId
				)
		BEGIN
			SELECT TOP 1 cmt.firstName
				,fullName
				,gender = CASE 
					WHEN gender = 97
						THEN 'M'
					WHEN gender = 98
						THEN 'F'
					ELSE NULL
					END
				,idType = CASE 
					WHEN ID.ID = '11402'
						THEN CMT.otherIdNumber
					ELSE CAST(ID.text AS VARCHAR)
					END
				,idTypeValue = ID.ID 
				,CONVERT(VARCHAR(10), dob, 103) AS dob
				,email AS email
				,mobile
				,city
				,ISNULL(zipCode, postalCode)
				,address address1
				,additionalAddress address2
				
				,cm.countryName 
				,idNumber 
				,cmt.idIssueDate
				,cmt.idExpiryDate
				,idIssueCountry
				,occupation
				,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
				,SelfieDoc
					
			FROM dbo.customerMasterTEMP(NOLOCK) cmt
			LEFT JOIN #ID_TYPE ID ON ID.id = cmt.idType
			LEFT JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryId = cmt.Country
			WHERE customerId = @customerId

			Exec proc_customerDocumentType @flag='getDocByCustomerId' ,@customerId = @customerId
		END
		ELSE
		BEGIN
			SELECT TOP 1 cmt.firstName
				,fullName
				,gender = CASE 
					WHEN gender = 97
						THEN 'M'
					WHEN gender = 98
						THEN 'F'
					ELSE NULL
					END
				,idType = CASE 
					WHEN ID.ID = '11402'
						THEN CMT.otherIdNumber
					ELSE CAST(ID.text AS VARCHAR)
					END
			    ,idTypeValue = ID.ID 
				,CONVERT(VARCHAR(10), dob, 103) AS dob
				,email AS email
				,mobile
				,city
			     ,zipCode
				,address  address1
				,additionalAddress address2
				
				,cm.countryName 
				,occ.detailTitle occupation
				,idNumber 
				,CONVERT(VARCHAR(10), cmt.idIssueDate, 103) as  idIssueDate
				,CONVERT(VARCHAR(10), cmt.idExpiryDate, 103) as idExpiryDate
				,cmt.idType
				,icm.countryName as ICountryName
				,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
					,'0' errorCode 
					,'Success' msg
					,SelfieDoc
			FROM dbo.customerMaster(NOLOCK) cmt
			LEFT JOIN #ID_TYPE ID ON ID.id = cmt.idType
			LEFT JOIN staticDataValue occ ON cmt.occupation = occ.valueId and occ.typeid='2000'
			LEFT JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryId = cmt.Country
			LEFT JOIN dbo.countryMaster(NOLOCK) icm On icm.countryId= cmt.idIssueCountry
			WHERE customerId = @customerId

		--	Exec proc_customerDocumentType @flag='getDocByCustomerId' ,@customerId=@customerId
	SELECT [fileName]
			,fileType
			,documentType
			,documentName
			,cdid
			,CREATEDDATE
			,idType
			,typeid
		FROM (
			SELECT ROW_NUMBER() OVER (
					PARTITION BY CM.DOCUMENTTYPE ORDER BY CM.CREATEDDATE DESC
					) SN
				, convert(varchar, CM.CREATEDDATE, 103) CREATEDDATE
				,cm.fileName
				,ISNULL(cm.fileType, Sv.detailTitle) fileType
				,cm.documentType
				,ISNULL(Sv.detailTitle, 'doc') documentName
				,cdid
				,ISNULL(Si.detailTitle,SA.detailTitle) idType
				, ISNULL(si.typeid,SA.typeid) typeid
			FROM dbo.customerDocument(NOLOCK) cm
			INNER JOIN customerMaster (NOLOCK) c on cm.customerId= c.customerId
			LEFT JOIN dbo.staticDataValue(NOLOCK) Sv ON Sv.valueId = cm.documentType
			LEFT JOIN dbo.staticDataValue(NOLOCK)  Si ON Si.valueId = cm.IDType-- and Si.typeid='1300'
			LEFT JOIN dbo.staticDataValue(NOLOCK)  SA ON SA.valueId = c.documentType-- and Si.typeid='1300'
			WHERE c.customerId = @customerId
				AND ISNULL(c.isdeleted, 'N') = 'N'
				AND sv.valueid not in('11440','11443')
			) x
		WHERE x.sn = 1
		ORDER BY x.cdId
			
		END
	END

	IF @flag = 'update-id-Kyc'
	BEGIN
		IF EXISTS (
				SELECT 'X'
				FROM dbo.customerMasterTemp(NOLOCK)
				WHERE customerId = @customerId
				)
		BEGIN
			UPDATE dbo.customerMasterTemp
			SET idType = ISNULL(@idType, idType)
				,idNumber = ISNULL(@idTypeNumber, idNumber)
				,idExpiryDate =ISNULL(CONVERT(VARCHAR(10), CONVERT(DATE, @idEndDate, 103), 23), idExpiryDate)
				,idIssueDate =ISNULL(CONVERT(VARCHAR(10), CONVERT(DATE,@idStartDate, 103), 23), idIssueDate)    --idIssueDate = ISNULL(@idStartDate, idIssueDate)
				,idIssueCountry = ISNULL(@idIssuingCountry, idIssueCountry)
			WHERE customerId = @customerId
		END
		ELSE
		BEGIN
			UPDATE dbo.customerMaster
			SET idType = ISNULL(@idType, idType)
				,idNumber = ISNULL(@idTypeNumber, idNumber)
				,idExpiryDate =ISNULL(CONVERT(VARCHAR(10), CONVERT(DATE, @idEndDate, 103), 23), idExpiryDate)
				,idIssueDate =ISNULL(CONVERT(VARCHAR(10), CONVERT(DATE,@idStartDate, 103), 23), idIssueDate)
				--,idExpiryDate = ISNULL(@idEndDate, idExpiryDate)
				--,idIssueDate = ISNULL(@idStartDate, idIssueDate)
				,idIssueCountry = ISNULL(@idIssuingCountry, idIssueCountry)
			WHERE customerId = @customerId
		END
		IF @@ROWCOUNT >0
		BEGIN
			SELECT '0' ErrorCode
			,'Customer update successfully.' Msg
			,@customerId id
		END
		ELSE
				BEGIN
				SELECT '1' ErrorCode
				,'Customer update failed.' Msg
				,@customerId id
				END
			
		
	END
	IF @flag = 'update-address'
	BEGIN
	IF EXISTS (
				SELECT 'X'
				FROM dbo.customerMasterTemp(NOLOCK)
				WHERE customerId = @customerId
				)
		BEGIN

			UPDATE dbo.customerMasterTemp
			SET zipCode = ISNULL(@postalCode, zipCode)
				,address = ISNULL(@address1, address)
				,ADDITIONALADDRESS = ISNULL(@address2, ADDITIONALADDRESS)
				,city = ISNULL(@city, city)
			WHERE customerId = @customerId
		END

		ELSE
		BEGIN
			UPDATE dbo.customerMaster
			SET zipCode = ISNULL(@postalCode, zipCode)
				,address = ISNULL(@address1, address)
				,ADDITIONALADDRESS = ISNULL(@address2, ADDITIONALADDRESS)
				,city = ISNULL(@city, city)
			WHERE customerId = @customerId
		END
			IF @@ROWCOUNT >0
		BEGIN
			SELECT '0' ErrorCode
			,'Customer update successfully.' Msg
			,@customerId id
		END
		ELSE
				BEGIN
				SELECT '1' ErrorCode
				,'Customer update failed.' Msg
				,@customerId id
				END
	END
END