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.
1081 lines
42 KiB
1081 lines
42 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[JsonRx_Proc_UserRegistration] Script Date: 11/10/2023 9:51:30 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROCEDURE [dbo].[JsonRx_Proc_UserRegistration] (
|
|
@language VARCHAR(100) = 'en'
|
|
,@customerId VARCHAR(100) = NULL
|
|
,@username VARCHAR(100) = NULL
|
|
,@flag VARCHAR(100) = NULL
|
|
,@password VARCHAR(100) = NULL
|
|
,@clientId VARCHAR(100) = NULL
|
|
,@IMEI VARCHAR(100) = NULL
|
|
,@appVersion VARCHAR(100) = NULL
|
|
,@phoneBrand VARCHAR(100) = NULL
|
|
,@phoneOs VARCHAR(100) = NULL
|
|
,@osVersion VARCHAR(100) = NULL
|
|
,@deviceId VARCHAR(100) = NULL
|
|
,@dob VARCHAR(100) = NULL
|
|
,@mobile VARCHAR(100) = NULL
|
|
,@nativeCountry VARCHAR(5) = NULL
|
|
,@referenceId BIGINT = NULL
|
|
,@randomPassword VARCHAR(20) = NULL
|
|
,@user VARCHAR(20) = NULL
|
|
,@registrationType VARCHAR(30) = NULL
|
|
)
|
|
AS
|
|
-----------------------------------------------------------------
|
|
--#101 Mobile app changes -> isForcedPwdChange=1 @flag='pwd-reset'
|
|
--#387 Mobile app changes -> Added new flag = 'pwd-reset-core' , 'pin-reset-core' , Reset pin & Password From Core
|
|
--#101 Push notificaiton for New Customer Register
|
|
-- #1030 , @flag = 'agree' , added isAgreeDate
|
|
-----------------------------------------------------------------
|
|
BEGIN TRY
|
|
DECLARE @dobDB VARCHAR(200)
|
|
,@code VARCHAR(100)
|
|
,@_errorMsg VARCHAR(300)
|
|
,@verifiedDate DATETIME
|
|
,@customerIdNo VARCHAR(50)
|
|
|
|
IF @flag = 'sign-up'
|
|
BEGIN
|
|
--user already registered
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMaster AS CM(NOLOCK)
|
|
WHERE CM.email = @username
|
|
)
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '1002') Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
|
|
--user already registered
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMasterTemp AS CM(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '1002') Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
|
|
--Username already taken
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.mobile_userRegistration(NOLOCK) AS MUR
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '1001') Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
|
|
BEGIN TRAN
|
|
|
|
INSERT INTO dbo.CustomerMasterTemp (
|
|
username
|
|
,customerPassword
|
|
,createdBy
|
|
,createdDate
|
|
,dob
|
|
,email
|
|
,mobile
|
|
,isActive
|
|
)
|
|
SELECT @username
|
|
,dbo.FNAEncryptString(@password)
|
|
,@username
|
|
,GETDATE()
|
|
,@dob
|
|
,@username
|
|
,CASE
|
|
WHEN ISNUMERIC(@username) = 1
|
|
THEN @username
|
|
ELSE ''
|
|
END
|
|
,'Y'
|
|
|
|
SET @customerId = SCOPE_IDENTITY()
|
|
|
|
INSERT INTO dbo.mobile_userRegistration (
|
|
clientId
|
|
,username
|
|
,createdDate
|
|
,IMEI
|
|
,appVersion
|
|
,phoneBrand
|
|
,phoneOs
|
|
,osVersion
|
|
,deviceId
|
|
,customerId
|
|
)
|
|
SELECT @clientId
|
|
,@username
|
|
,GETDATE()
|
|
,@IMEI
|
|
,@appVersion
|
|
,@phoneBrand
|
|
,@phoneOs
|
|
,@osVersion
|
|
,@deviceId
|
|
,@customerId
|
|
|
|
COMMIT TRAN
|
|
|
|
IF @@TRANCOUNT = 0
|
|
BEGIN
|
|
--successful registered
|
|
SELECT 0 ErrorCode
|
|
,dbo.GetMessage(@language, '1000') Msg
|
|
,NULL Id
|
|
,rowId
|
|
,PdfName
|
|
,AgreePdfPath
|
|
FROM customerAgreeDocumentTbl
|
|
WHERE targetObj = 'UAT'
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
ELSE IF @flag = 'sign-up-V2'
|
|
BEGIN
|
|
--user already registered
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMaster AS CM(NOLOCK)
|
|
WHERE CM.email = @username
|
|
)
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '1002') Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
|
|
--DECLARE @MOBILE_OTP VARCHAR(30) = NULL
|
|
--SELECT @MOBILE_OTP = MOBILE_NUMBER
|
|
--FROM TBL_MOBILE_OTP_REQUEST (NOLOCK)
|
|
--WHERE ROW_ID = @referenceId
|
|
--IF @MOBILE_OTP IS NULL
|
|
--BEGIN
|
|
-- SELECT 1 ErrorCode,dbo.GetMessage(@language,'1003') Msg, NULL Id
|
|
-- RETURN
|
|
--END
|
|
--IF @MOBILE_OTP <> @mobile
|
|
--BEGIN
|
|
-- SELECT 1 ErrorCode,dbo.GetMessage(@language,'1004') Msg, NULL Id
|
|
-- RETURN
|
|
--END
|
|
--user already registered
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMasterTemp AS CM(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '1002') Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
|
|
--Username already taken
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.mobile_userRegistration(NOLOCK) AS MUR
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '1001') Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
|
|
BEGIN TRAN
|
|
|
|
INSERT INTO dbo.CustomerMasterTemp (
|
|
username
|
|
,customerPassword
|
|
,createdBy
|
|
,createdDate
|
|
,email
|
|
,mobile
|
|
,nativeCountry
|
|
,isActive
|
|
)
|
|
SELECT @username
|
|
,dbo.FNAEncryptString(@password)
|
|
,@username
|
|
,GETDATE()
|
|
,@username
|
|
,@mobile
|
|
,@nativeCountry
|
|
,'Y'
|
|
|
|
SET @customerId = SCOPE_IDENTITY()
|
|
|
|
INSERT INTO dbo.mobile_userRegistration (
|
|
clientId
|
|
,username
|
|
,createdDate
|
|
,IMEI
|
|
,appVersion
|
|
,phoneBrand
|
|
,phoneOs
|
|
,osVersion
|
|
,deviceId
|
|
,customerId
|
|
)
|
|
SELECT @clientId
|
|
,@username
|
|
,GETDATE()
|
|
,@IMEI
|
|
,@appVersion
|
|
,@phoneBrand
|
|
,@phoneOs
|
|
,@osVersion
|
|
,@deviceId
|
|
,@customerId
|
|
|
|
COMMIT TRAN
|
|
|
|
IF @@TRANCOUNT = 0
|
|
BEGIN
|
|
--successful registered
|
|
SELECT 0 ErrorCode
|
|
,dbo.GetMessage(@language, '1000') Msg
|
|
,NULL Id
|
|
,rowId
|
|
,PdfName
|
|
,AgreePdfPath
|
|
FROM customerAgreeDocumentTbl
|
|
WHERE targetObj = 'STAGING'
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
ELSE IF @flag = 'pwd-reset'
|
|
BEGIN
|
|
DECLARE @isExist BIT = 0
|
|
|
|
IF (@userName IS NULL OR @userName='')
|
|
BEGIN
|
|
SELECT @_errorMsg = 'Email address is required.'
|
|
|
|
SELECT '3' ErrorCode
|
|
,@_errorMsg Msg
|
|
,NULL ID
|
|
,@username Extra
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.CustomerMasterTemp(NOLOCK) cm
|
|
WHERE (cm.username = @userName)
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SET @isExist = 1;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.CustomerMasterTemp(NOLOCK) cm
|
|
WHERE (cm.username = @userName)
|
|
AND (cm.dob = @dob)
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Dob is invalid.'
|
|
,NULL ID
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMaster(NOLOCK) cm
|
|
WHERE cm.email = @userName
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SET @isExist = 1;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.CustomerMaster(NOLOCK) cm
|
|
WHERE (cm.username = @userName)
|
|
AND (cm.dob = @dob)
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Dob is invalid.'
|
|
,NULL ID
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @isExist = 0
|
|
BEGIN
|
|
SELECT @_errorMsg = 'User with contact Info ' + @username + ' does not exists. If you are a new user, then sign up and proceed further.'
|
|
|
|
SELECT '3' ErrorCode
|
|
,@_errorMsg Msg
|
|
,NULL ID
|
|
,@username Extra
|
|
|
|
RETURN
|
|
END
|
|
|
|
SELECT @dobDB = dob
|
|
,@mobile = mobile
|
|
,@customerId = customerId
|
|
,@verifiedDate = verifiedDate
|
|
,@customerIdNo = idNumber
|
|
,@mobile = mobile
|
|
,@_errorMsg= fullname
|
|
FROM customerMaster WITH (NOLOCK)
|
|
WHERE email = @userName
|
|
|
|
--AND dob = @dob
|
|
SET @code = @randomPassword
|
|
|
|
BEGIN TRAN
|
|
|
|
UPDATE dbo.customerMaster
|
|
SET customerPassword = dbo.FNAEncryptString(@code)
|
|
WHERE email = @username
|
|
AND dob = @dob
|
|
|
|
UPDATE dbo.mobile_userRegistration
|
|
SET isForcePassChange = 1
|
|
WHERE username = @username
|
|
|
|
DECLARE @smsMsg VARCHAR(100) = 'Your new password is ' + @code
|
|
|
|
--IF ISNUMERIC(@username) = 1
|
|
--BEGIN
|
|
-- PRINT 'a'
|
|
-- --exec proc_CallToSendSMS @FLAG = 'I',@SMSBody=@smsMsg,@MobileNo=@mobile
|
|
--END
|
|
COMMIT TRAN
|
|
|
|
IF @@TRANCOUNT = 0
|
|
BEGIN
|
|
SELECT '0' ErrorCode
|
|
,'Sucess' Msg
|
|
,@customerId Id
|
|
,@code Extra
|
|
,@_errorMsg Extra2
|
|
,@mobile Extra3
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '2' ErrorCode
|
|
,'Could not reset the password. Please contact IME London head office.'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
ELSE IF @flag = 'pwd-reset-core'
|
|
BEGIN
|
|
DECLARE @email NVARCHAR(50)
|
|
,@customerName NVARCHAR(50)
|
|
,@oldpassword NVARCHAR(50)
|
|
,@amendmentId NVARCHAR(MAX)
|
|
|
|
SELECT @email = email
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @customerId;
|
|
|
|
SELECT @customerName = fullName
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @customerId;
|
|
|
|
SELECT @oldpassword = customerPassword
|
|
FROM customerMaster
|
|
WHERE customerId = @customerId;
|
|
|
|
DECLARE @isExistCore BIT = 0
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.CustomerMasterTemp(NOLOCK) cm
|
|
WHERE (cm.username = @email)
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SET @isExistCore = 1;
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMaster(NOLOCK) cm
|
|
WHERE cm.email = @email
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SET @isExistCore = 1;
|
|
END
|
|
|
|
IF @isExistCore = 0
|
|
BEGIN
|
|
SELECT @_errorMsg = 'User with contact Info ' + @email + ' does not exists. If you are a new user, then sign up and proceed further.'
|
|
|
|
SELECT '1' ErrorCode
|
|
,@_errorMsg Msg
|
|
,@email ID
|
|
|
|
RETURN
|
|
END
|
|
|
|
SET @code = @randomPassword
|
|
SET @amendmentId = NEWID()
|
|
|
|
BEGIN TRAN
|
|
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS (
|
|
customerId
|
|
,columnName
|
|
,oldValue
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,newValue
|
|
,amendmentId
|
|
,agentId
|
|
)
|
|
SELECT @customerId
|
|
,'customerPassword'
|
|
,@oldpassword
|
|
,@user
|
|
,GETDATE()
|
|
,dbo.FNAEncryptString(@code)
|
|
,@amendmentId
|
|
,'0'
|
|
|
|
UPDATE dbo.customerMaster
|
|
SET customerPassword = dbo.FNAEncryptString(@code)
|
|
WHERE email = @email -- AND dob = @dob
|
|
|
|
UPDATE dbo.mobile_userRegistration
|
|
SET isForcePassChange = 1
|
|
WHERE username = @email
|
|
|
|
COMMIT TRAN
|
|
|
|
IF @@TRANCOUNT = 0
|
|
BEGIN
|
|
SELECT '0' ErrorCode
|
|
,'Success' Msg
|
|
,@email Id
|
|
,@code Extra
|
|
,@customerName Extra2
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Could not reset the password. Please contact JME head office.'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
ELSE IF @flag = 'pin-reset-core'
|
|
BEGIN
|
|
DECLARE @randompin INT
|
|
,@customeremail NVARCHAR(50)
|
|
,@customer NVARCHAR(50)
|
|
,@amendmentIdPin NVARCHAR(MAX)
|
|
,@oldpin NVARCHAR(8)
|
|
|
|
SELECT @oldpin = dbo.FNAEncryptString(txnpin)
|
|
FROM customerMaster
|
|
WHERE customerid = @customerId
|
|
|
|
SET @randompin = DBO.FNAGetRandomTransactionPinNo(6)
|
|
SET @amendmentIdPin = NEWID()
|
|
|
|
SELECT @customeremail = email
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @customerId;
|
|
|
|
SELECT @customer = fullName
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @customerId;
|
|
|
|
DECLARE @isExistPin BIT = 0
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.CustomerMasterTemp(NOLOCK) cm
|
|
WHERE (cm.username = @customeremail)
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SET @isExistPin = 1;
|
|
END
|
|
|
|
IF EXISTS (
|
|
SELECT 'x'
|
|
FROM dbo.customerMaster(NOLOCK) cm
|
|
WHERE cm.email = @customeremail
|
|
AND ISNULL(cm.isDeleted, 'N') = 'N'
|
|
)
|
|
BEGIN
|
|
SET @isExistPin = 1;
|
|
END
|
|
|
|
IF @isExistPin = 0
|
|
BEGIN
|
|
SELECT @_errorMsg = 'User with contact Info ' + @customeremail + ' does not exists. If you are a new user, then sign up and proceed further.'
|
|
|
|
SELECT '1' ErrorCode
|
|
,@_errorMsg Msg
|
|
,@email ID
|
|
|
|
RETURN
|
|
END
|
|
|
|
BEGIN TRAN
|
|
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS (
|
|
customerId
|
|
,columnName
|
|
,oldValue
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,newValue
|
|
,amendmentId
|
|
,agentId
|
|
)
|
|
SELECT @customerId
|
|
,'txpin'
|
|
,@oldpin
|
|
,@user
|
|
,GETDATE()
|
|
,dbo.FnaencryptString(@randompin)
|
|
,@amendmentIdPin
|
|
,'0'
|
|
|
|
UPDATE dbo.customerMaster
|
|
SET txnpin = @randompin
|
|
WHERE email = @customeremail
|
|
|
|
UPDATE dbo.mobile_userRegistration
|
|
SET isForcePinChange = 1
|
|
WHERE username = @customeremail
|
|
|
|
COMMIT TRAN
|
|
|
|
IF @@TRANCOUNT = 0
|
|
BEGIN
|
|
SELECT '0' ErrorCode
|
|
,'Success' Msg
|
|
,@customeremail Id
|
|
,@randompin Extra
|
|
,@customer Extra2
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '1' ErrorCode
|
|
,'Could not reset the pin. Please contact JME head office.'
|
|
,NULL
|
|
|
|
RETURN
|
|
END
|
|
END
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT <> 0
|
|
ROLLBACK TRAN
|
|
|
|
--Execption
|
|
SELECT 1 ErrorCode
|
|
,dbo.GetMessage(@language, '9999') + CONVERT(VARCHAR, ERROR_LINE()) + ERROR_MESSAGE() Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END CATCH
|
|
|
|
-- 이용자 정보 동의 확인 값 추가 Info Agree Insert
|
|
IF @flag = 'agree'
|
|
BEGIN
|
|
DECLARE @fcmid VARCHAR(250)
|
|
,@fullname VARCHAR(100)
|
|
,@Trusdocid VARCHAR(50)
|
|
,@regType VARCHAR(20)
|
|
|
|
SELECT @fcmid = deviceid
|
|
,@customerId = customerId
|
|
FROM mobile_userRegistration
|
|
WHERE username = @username;
|
|
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM CustomerMasterTemp(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT @dobDB = CONVERT(VARCHAR(10), dob, 121)
|
|
,@fullname = fullName
|
|
FROM customerMasterTemp WITH (NOLOCK)
|
|
WHERE email = @username
|
|
|
|
UPDATE customerMasterTemp
|
|
SET agreeYn = '1'
|
|
,isAgreeDate = GETDATE()
|
|
,mobileverifieddate = GETDATE()
|
|
,mobileverifiedby = @username
|
|
,mobileVerificationType = 'verified'
|
|
WHERE username = @username
|
|
|
|
UPDATE customerMaster
|
|
SET RegistrationType = @registrationType
|
|
,mobileverifieddate = GETDATE()
|
|
,mobileverifiedby = @username
|
|
,mobileVerificationType = 'verified'
|
|
WHERE username = @userName
|
|
AND customerId = @customerId;
|
|
|
|
SELECT 0 ErrorCode
|
|
,@fullname Msg
|
|
,@customerId Id
|
|
,@fcmid Extra
|
|
,@dobDB Extra2
|
|
,@Trusdocid Extra3
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF EXISTS (
|
|
SELECT 'X'
|
|
FROM CustomerMaster(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT @dobDB = CONVERT(VARCHAR(10), dob, 121)
|
|
,@fullname = fullName
|
|
,@Trusdocid = ''
|
|
,@regType = RegistrationType
|
|
FROM customerMaster WITH (NOLOCK)
|
|
WHERE email = @username
|
|
|
|
UPDATE customerMaster
|
|
SET agreeYn = '1'
|
|
,isAgreeDate = GETDATE()
|
|
,mobileverifieddate = GETDATE()
|
|
,mobileverifiedby = @username
|
|
,mobileVerificationType = 'verified'
|
|
,verificationCode='PROCESSING'
|
|
WHERE username = @username
|
|
|
|
INSERT INTO pushNotificationHistroy (
|
|
customerId
|
|
,body
|
|
,title
|
|
,createDate
|
|
,imageURL
|
|
,sentId
|
|
,Type
|
|
,isReservation
|
|
,isRead
|
|
,isSend
|
|
,category
|
|
,isClickable
|
|
)
|
|
SELECT @customerId
|
|
,'Thank you for completing your profile. You can now start sending transactions'
|
|
,'IME LONDON Registration'
|
|
,getdate()
|
|
,''
|
|
,@customerId
|
|
,0
|
|
,0
|
|
,0
|
|
,0
|
|
,'INFO'
|
|
,'N'
|
|
|
|
SELECT 0 ErrorCode
|
|
,@fullname Msg
|
|
,@customerId Id
|
|
,@fcmid Extra
|
|
,@dobDB Extra2
|
|
,@Trusdocid Extra3
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,'Failed' Msg
|
|
,NULL Id
|
|
END
|
|
END
|
|
|
|
IF @flag = 'agree-manual'
|
|
BEGIN
|
|
DECLARE @RegistrationTypeold VARCHAR(20);
|
|
DECLARE @isAgreeDate DATETIME;
|
|
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM CustomerMaster(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT @RegistrationTypeold = RegistrationType
|
|
,@customerId = customerid
|
|
,@isAgreeDate = isAgreeDate
|
|
FROM CustomerMaster(NOLOCK)
|
|
WHERE username = @username
|
|
|
|
--UPDATE customerMaster
|
|
--SET RegistrationType = 'MKYC'
|
|
-- ,modifiedDate = GETDATE()
|
|
-- ,modifiedBy = @username
|
|
-- ,isAgreeDate=null
|
|
-- ,agreeYn=0
|
|
-- ,HasDeclare=0
|
|
--WHERE username = @username
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS (
|
|
customerId
|
|
,columnName
|
|
,oldValue
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,newValue
|
|
,amendmentId
|
|
,agentId
|
|
)
|
|
SELECT @customerId
|
|
,'RegistrationType'
|
|
,@RegistrationTypeold + '|' + CAST(@isAgreeDate AS VARCHAR)
|
|
,@userName
|
|
,GETDATE()
|
|
,'MYKC'
|
|
,''
|
|
,''
|
|
|
|
SELECT 0 ErrorCode
|
|
,'Success' Msg
|
|
,NULL Id
|
|
,@fcmid Extra
|
|
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,'Failed' Msg
|
|
,NULL Id
|
|
END
|
|
END
|
|
|
|
IF @flag = 'save-kyc-option'
|
|
BEGIN
|
|
DECLARE @KYCOptionold VARCHAR(30);
|
|
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM CustomerMaster(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
SELECT @customerId = customerid
|
|
FROM CustomerMaster(NOLOCK)
|
|
WHERE username = @username
|
|
|
|
SELECT TOP 1 @KYCOptionold = isnull(newValue, oldValue)
|
|
FROM TBLCUSTOMERMODIFYLOGS
|
|
WHERE columnName = 'KYCOption'
|
|
AND customerId = @customerId
|
|
ORDER BY customerId DESC
|
|
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS (
|
|
customerId
|
|
,columnName
|
|
,oldValue
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,newValue
|
|
)
|
|
SELECT @customerId
|
|
,'KYCOption'
|
|
,ISNULL(@KYCOptionold, '') + '|' + @RegistrationTypeold
|
|
,@userName
|
|
,GETDATE()
|
|
,@clientid
|
|
|
|
IF @clientid = 'KYC_LATER'
|
|
BEGIN
|
|
UPDATE customerMaster
|
|
SET RegistrationType = @registrationType
|
|
,mobileverifieddate = GETDATE()
|
|
,mobileverifiedby = @username
|
|
,mobileVerificationType = 'verified'
|
|
,HasDeclare = 1
|
|
,agreeYn = 1
|
|
,LawsonCardNo='KYC_LATER'
|
|
WHERE customerId = @customerId;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE customerMaster SET LawsonCardNo='KYC_NOW' WHERE customerId = @customerId;
|
|
END
|
|
|
|
SELECT 0 ErrorCode
|
|
,'Success' Msg
|
|
,NULL Id
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF EXISTS (
|
|
SELECT 'X'
|
|
FROM CustomerMastertemp(NOLOCK)
|
|
WHERE username = @username
|
|
)
|
|
BEGIN
|
|
PRINT 'CustomerMastertemp'
|
|
|
|
SELECT @customerId = customerid
|
|
FROM CustomerMastertemp(NOLOCK)
|
|
WHERE username = @username
|
|
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS (
|
|
customerId
|
|
,columnName
|
|
,oldValue
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,newValue
|
|
,amendmentId
|
|
,agentId
|
|
)
|
|
SELECT @customerId
|
|
,'KYCOption'
|
|
,''
|
|
,@userName
|
|
,GETDATE()
|
|
,@clientid + + '|' + @registrationType
|
|
,''
|
|
,''
|
|
|
|
IF @clientid = 'KYC_LATER'
|
|
BEGIN
|
|
INSERT INTO dbo.customerMaster (
|
|
fullName
|
|
,firstName
|
|
,middleName
|
|
,lastName1
|
|
,mobile
|
|
,gender
|
|
,dob
|
|
,occupation
|
|
,nativeCountry
|
|
,country
|
|
,bankName
|
|
,bankAccountNo
|
|
,idType
|
|
,idNumber
|
|
,homePhone
|
|
,idIssueDate
|
|
,idExpiryDate
|
|
,verifyDoc1
|
|
,verifyDoc2
|
|
,verifyDoc3
|
|
,SelfieDoc
|
|
,referelCode
|
|
,createdBy
|
|
,createdDate
|
|
,isActive
|
|
,onlineUser
|
|
,customerPassword
|
|
,[address]
|
|
,city
|
|
,state2
|
|
,customerType
|
|
,ADDITIONALADDRESS
|
|
,monthlyIncome
|
|
,organizationType
|
|
,username
|
|
,anotherIdType
|
|
,employeeBusinessType
|
|
,documentType
|
|
,remittanceallowed
|
|
,txnPin
|
|
,nameOfEmployeer
|
|
,isExistingCustomer
|
|
,HasDeclare
|
|
,createdFrom
|
|
,email
|
|
,customerEmail
|
|
,isEmailVerified
|
|
,mobileUser
|
|
,RegistrationType
|
|
,lawsoncardno
|
|
,zipCode
|
|
|
|
)
|
|
SELECT fullName
|
|
,CMT.firstName
|
|
,CMT.middleName
|
|
,cmt.lastName1
|
|
,mobile
|
|
,gender
|
|
,CASE ISDATE(dob)
|
|
WHEN 1
|
|
THEN dob
|
|
ELSE ''
|
|
END
|
|
,CMT.occupation
|
|
,nativeCountry
|
|
,233
|
|
,bankName
|
|
,bankAccountNo
|
|
,idType
|
|
,idNumber
|
|
,CMT.homePhone
|
|
,CMT.idIssueDate
|
|
,idExpiryDate
|
|
,verifyDoc1
|
|
,verifyDoc2
|
|
,verifyDoc3
|
|
,CMT.selfie
|
|
,CMT.referelCode
|
|
,CMT.createdBy
|
|
,createdDate
|
|
,'Y'
|
|
,'Y'
|
|
,customerPassword
|
|
,[address]
|
|
,city
|
|
,state2
|
|
,4700
|
|
,ADDITIONALADDRESS
|
|
,monthlyIncome
|
|
,organizationType
|
|
,username
|
|
,anotherIdType
|
|
,employeeBusinessType
|
|
,documentType
|
|
,1
|
|
,NULL
|
|
,nameOfEmployeer
|
|
,0
|
|
,0
|
|
,'M'
|
|
,email
|
|
,customerEmail
|
|
,isEmailVerified
|
|
,mobileUser
|
|
,RegistrationType
|
|
,@clientid
|
|
--,CASE @clientid WHEN 'KYC_LATER' THEN GETDATE() ELSE NULL END
|
|
--,CASE @clientid WHEN 'KYC_LATER' THEN 'verified' ELSE NULL END
|
|
--,@username
|
|
,zipCode
|
|
FROM dbo.CustomerMasterTemp AS CMT(NOLOCK)
|
|
WHERE CMT.username = @userName
|
|
|
|
SET @customerId = @@IDENTITY
|
|
|
|
UPDATE dbo.mobile_userRegistration
|
|
SET customerId = @customerId
|
|
WHERE username = @userName
|
|
|
|
DELETE
|
|
FROM customerMasterTemp
|
|
WHERE username = @userName
|
|
|
|
DECLARE @MEMBESHIP_ID VARCHAR(50) = NULL
|
|
|
|
EXEC PROC_GENERATE_MEMBERSHIP_ID @CUSTOMERID = 0
|
|
,@USER = 'mobile'
|
|
,@loginBranchId = 0
|
|
,@MEMBESHIP_ID = @MEMBESHIP_ID OUT
|
|
|
|
UPDATE dbo.customerMaster
|
|
SET MEMBERSHIPID = @MEMBESHIP_ID
|
|
,mobileverifieddate = GETDATE()
|
|
,mobileverifiedby = @username
|
|
,mobileVerificationType = 'verified'
|
|
,HasDeclare = 1
|
|
,agreeYn = 1
|
|
--,LawsonCardNo = ISNULL(@clientid, '')
|
|
WHERE customerId = @customerId
|
|
|
|
INSERT INTO pushNotificationHistroy (
|
|
customerId
|
|
,body
|
|
,title
|
|
,createDate
|
|
,imageURL
|
|
,sentId
|
|
,Type
|
|
,isReservation
|
|
,isRead
|
|
,isSend
|
|
,category
|
|
,isClickable
|
|
)
|
|
SELECT @customerId
|
|
,'Congratulations, You are now ready to send money from IME London Mobile App'
|
|
,'IME LONDON Registration'
|
|
,getdate()
|
|
,''
|
|
,@customerId
|
|
,0
|
|
,0
|
|
,0
|
|
,0
|
|
,'INFO'
|
|
,'N'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE CustomerMasterTemp SET RegistrationType='KYC_NOW' WHERE customerId = @customerId;
|
|
END
|
|
|
|
SELECT 0 ErrorCode
|
|
,'Success' Msg
|
|
,NULL Id
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT 1 ErrorCode
|
|
,'Failed' Msg
|
|
,NULL Id
|
|
END
|
|
END
|