You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
273 lines
15 KiB
273 lines
15 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[mobile_proc_online_customerMaster_V1] Script Date: 11/20/2023 1:53:59 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, 120) 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, 120) AS dob
|
|
,email AS email
|
|
,mobile
|
|
,city
|
|
,zipCode
|
|
,address address1
|
|
,additionalAddress address2
|
|
|
|
,cm.countryName
|
|
,occ.detailTitle occupation
|
|
,idNumber
|
|
,CONVERT(VARCHAR(10), cmt.idIssueDate, 120) as idIssueDate
|
|
,CONVERT(VARCHAR(10), cmt.idExpiryDate, 120) 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(@idEndDate, idExpiryDate)
|
|
,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(@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
|
|
|