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.
111 lines
3.9 KiB
111 lines
3.9 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_CHECK_CUSTOMER_REGISTRATION] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[PROC_CHECK_CUSTOMER_REGISTRATION]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_CHECK_CUSTOMER_REGISTRATION] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE PROC [dbo].[PROC_CHECK_CUSTOMER_REGISTRATION]
|
|
(
|
|
@flag VARCHAR(20)
|
|
,@customerName VARCHAR(150)= NULL
|
|
,@customerIdNo VARCHAR(20) = NULL
|
|
,@nativeCountryId VARCHAR(30) = NULL
|
|
,@custAdd VARCHAR(500)= NULL
|
|
,@district VARCHAR(150)= NULL
|
|
,@custCity VARCHAR(150)= NULL
|
|
,@custEmail VARCHAR(150)= NULL
|
|
,@custMobile VARCHAR(20) = NULL
|
|
,@custDOB DATETIME = NULL
|
|
,@placeOfIssue VARCHAR(80) = NULL
|
|
,@occupation VARCHAR(150)= NULL
|
|
,@relationId VARCHAR(50) = NULL
|
|
,@relativeName VARCHAR(100)= NULL
|
|
,@custGender VARCHAR(30) = NULL
|
|
,@user VARCHAR(70) = NULL
|
|
,@custIdissueDate VARCHAR(15) = NULL
|
|
,@custIdValidDate DATETIME = NULL
|
|
,@customerIdType VARCHAR(40) = NULL
|
|
,@ipAddress VARCHAR(50) = NULL
|
|
,@customerId VARCHAR(20) = NULL OUT
|
|
)
|
|
AS;
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRY
|
|
BEGIN
|
|
IF @flag = 'i'
|
|
BEGIN
|
|
IF ISNULL(@custEmail, '') = ''
|
|
BEGIN
|
|
SET @customerId = '0000'
|
|
RETURN;
|
|
END
|
|
|
|
IF EXISTS(SELECT 1 FROM CUSTOMERMASTER (NOLOCK) WHERE REPLACE(idNumber,'-','') = REPLACE(@customerIdNo, '-', ''))
|
|
BEGIN
|
|
SELECT @customerId = customerId FROM customerMaster (NOLOCK) WHERE REPLACE(idNumber,'-','') = REPLACE(@customerIdNo, '-', '')
|
|
RETURN;
|
|
END
|
|
IF EXISTS(SELECT 1 FROM CUSTOMERMASTER (NOLOCK) WHERE email = @custEmail)
|
|
BEGIN
|
|
SELECT @customerId = customerId FROM customerMaster (NOLOCK) WHERE email = @custEmail
|
|
RETURN;
|
|
END
|
|
IF EXISTS(SELECT 1 FROM CUSTOMERMASTER (NOLOCK) WHERE ISNULL(fullName, firstName) = @customerName AND dob = @custDOB)
|
|
BEGIN
|
|
SELECT @customerId = customerId FROM customerMaster (NOLOCK) WHERE ISNULL(fullName, firstName) = @customerName AND dob = @custDOB
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @newMobileNumber VARCHAR(20) = REPLACE(@custMobile, '+', '')
|
|
SET @newMobileNumber = CASE WHEN @newMobileNumber LIKE '82%' THEN STUFF(@newMobileNumber, 1, 2, '') ELSE @newMobileNumber END
|
|
|
|
SET @newMobileNumber = CASE
|
|
WHEN @newMobileNumber LIKE '0%' THEN STUFF(@newMobileNumber, 1, 1, '')
|
|
ELSE @newMobileNumber
|
|
END
|
|
SET @newMobileNumber = '%' + @newMobileNumber
|
|
|
|
IF EXISTS(SELECT 1 FROM customerMaster (NOLOCK) WHERE ISNULL(fullName, firstName) = @customerName AND mobile LIKE @newMobileNumber)
|
|
BEGIN
|
|
SELECT @customerId = customerId FROM customerMaster (NOLOCK) WHERE firstName = @customerName AND mobile LIKE @newMobileNumber
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @newPassword VARCHAR(10) = RIGHT('0000000' + CAST(CHECKSUM(NEWID()) AS VARCHAR), 7)
|
|
|
|
INSERT INTO customerMaster (
|
|
firstName, country, [address], district, city, email, homePhone, mobile
|
|
,nativeCountry, dob, placeOfIssue, occupation, relationId, relativeName, gender
|
|
, fullName, createdBy, createdDate, idIssueDate, idExpiryDate, idType, idNumber ,onlineUser
|
|
,ipAddress ,customerPassword ,customerType ,isActive , isForcedPwdChange
|
|
)
|
|
SELECT
|
|
@customerName, '118', @custAdd, @district, @custCity, @custEmail, @customerIdNo, @custMobile
|
|
, @nativeCountryId, @custDOB, @placeOfIssue, @occupation, @relationId ,@relativeName ,@custGender
|
|
, @customerName, @user, GETDATE(), @custIdissueDate, @custIdValidDate, @customerIdType, @customerIdNo, 'Y'
|
|
, @ipAddress, dbo.FNAEncryptString(@newPassword), '4700', 'Y', '1'
|
|
|
|
SET @customerId = SCOPE_IDENTITY()
|
|
END
|
|
END
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT <> 0
|
|
ROLLBACK TRANSACTION;
|
|
|
|
DECLARE @errorMessage VARCHAR(MAX);
|
|
SET @errorMessage = ERROR_MESSAGE();
|
|
|
|
EXEC proc_errorHandler 1, @errorMessage, @user;
|
|
END CATCH;
|
|
|
|
|
|
|
|
GO
|