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.
304 lines
11 KiB
304 lines
11 KiB
ALTER PROC PROC_CUSTOMERMODIFYLOG
|
|
(
|
|
@flag VARCHAR(30),
|
|
@email VARCHAR(100) = NULL,
|
|
@idNumber VARCHAR(20) = NULL,
|
|
@bank VARCHAR(5) = NULL,
|
|
@accNumber VARCHAR(30) = NULL,
|
|
@customerId BIGINT = NULL,
|
|
@mobileNumber varchar(20) = NULL,
|
|
@user VARCHAR(100) = NULL,
|
|
@idType varchar(40) = null, --new added by dhan
|
|
@dob varchar(40) = null,
|
|
@issueDate varchar(40) = null,
|
|
@expiryDate varchar(40) = NULL, --new added by dhan
|
|
@address varchar(200) = NULL,
|
|
@city VARCHAR(100) = NULL,
|
|
@nativeCountry VARCHAR(100) = NULL,
|
|
@verifyDoc1 VARCHAR(300) = NULL,
|
|
@verifyDoc2 VARCHAR(300) = NULL,
|
|
@verifyDoc3 VARCHAR(300) = NULL,
|
|
@verifyDoc4 VARCHAR(300) = NULL,
|
|
@fullName VARCHAR(50) = NULL,
|
|
@searchKey varchar(20) = NULL,
|
|
@searchValue varchar(100) = NULL,
|
|
@startDate VARCHAR(30) = NULL,
|
|
@endDate VARCHAR(30) = NULL,
|
|
@sortBy VARCHAR(50) = NULL,
|
|
@sortOrder VARCHAR(5) = NULL,
|
|
@orderBy VARCHAR(100) = NULL,
|
|
@pageSize VARCHAR(50) = NULL,
|
|
@pageNumber VARCHAR(50) = NULL,
|
|
@customerEmail VARCHAR(100) = NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN
|
|
IF @flag = 'i'
|
|
BEGIN
|
|
DECLARE @oldEmail VARCHAR(100), @oldIdNumber VARCHAR(20), @oldBank VARCHAR(5), @oldAccNumber VARCHAR(30), @oldMobileNumber VARCHAR(20),
|
|
@oldDob varchar(40) = null, --new added by dhan
|
|
@oldIssueDate varchar(40) = null,
|
|
@oldExpiryDate varchar(40) = NULL, --new added by dhan
|
|
@oldAddress varchar(200) = NULL,
|
|
@oldCity VARCHAR(200) = NULL,
|
|
@oldNativeCountry varchar(200) = NULL,
|
|
@oldVerifyDoc1 VARCHAR(300) = NULL,
|
|
@oldVerifyDoc2 VARCHAR(300) = NULL,
|
|
@oldVerifyDoc3 VARCHAR(300) = NULL,
|
|
@oldVerifyDoc4 VARCHAR(300) = NULL,
|
|
@oldFullName VARCHAR(50) = NULL,
|
|
@oldIdType VARCHAR(50) = NULL,
|
|
@oldCustomerEmail VARCHAR(100) = NULL
|
|
|
|
|
|
SELECT @oldEmail = email,
|
|
@oldIdNumber = idNumber,
|
|
@oldBank = bankName,
|
|
@oldAccNumber = bankAccountNo,
|
|
@oldMobileNumber = mobile,
|
|
@oldDob = CONVERT(VARCHAR(10), dob,101),
|
|
@oldIssueDate = CONVERT(VARCHAR(10), idIssueDate,101),
|
|
@oldExpiryDate = CONVERT(VARCHAR(10), idExpiryDate,101),
|
|
@oldAddress = [address],
|
|
@oldCity = city,
|
|
@oldNativeCountry = nativeCountry,
|
|
@oldVerifyDoc1 = verifyDoc1,
|
|
@oldVerifyDoc2 = verifyDoc2,
|
|
@oldVerifyDoc3 = verifyDoc3,
|
|
@oldVerifyDoc4 = selfieDoc,
|
|
@oldFullName = firstName,
|
|
@oldIdType = idType,
|
|
@oldCustomerEmail= customerEmail
|
|
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @customerId
|
|
|
|
IF @oldEmail is null
|
|
RETURN
|
|
|
|
IF @oldEmail <> @email
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'email', @oldEmail, @user, GETDATE()
|
|
END
|
|
IF @oldIdNumber <> @idNumber
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'idNumber', @oldIdNumber, @user, GETDATE()
|
|
END
|
|
IF @oldIdType <> @idType
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'idType', @oldIdType, @user, GETDATE()
|
|
END
|
|
IF @oldBank <> @bank
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'bankName', @oldBank, @user, GETDATE()
|
|
END
|
|
IF @oldAccNumber <> @accNumber
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'bankAccountNo', @oldAccNumber, @user, GETDATE()
|
|
END
|
|
IF @oldMobileNumber <> @mobileNumber
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'mobile', @oldMobileNumber, @user, GETDATE()
|
|
END
|
|
IF @oldIssueDate <> Convert(VARCHAR(10),@issueDate,101)
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'idIssueDate', ISNULL(@oldIssueDate,''), @user, GETDATE()
|
|
END
|
|
IF @idType <> '8008'
|
|
BEGIN
|
|
--IF @oldIssueDate <> Convert(VARCHAR(10),@issueDate,101)
|
|
--BEGIN
|
|
-- INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
-- SELECT @customerId, 'idIssueDate', ISNULL(@oldIssueDate,''), @user, GETDATE()
|
|
-- END
|
|
|
|
IF @oldExpiryDate <> Convert(VARCHAR(10),@expiryDate,101)
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'idExpiryDate', ISNULL(@oldExpiryDate,''), @user, GETDATE()
|
|
END
|
|
END
|
|
|
|
IF @oldDob <> Convert(VARCHAR(10),@dob,101)
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'dob', ISNULL(@oldDob,''), @user, GETDATE()
|
|
END
|
|
|
|
IF @oldAddress <> @address
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'address', @oldAddress, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldCity <> @city
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'city', @oldCity, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldNativeCountry <> @nativeCountry
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'nativeCountry', @oldNativeCountry, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldVerifyDoc1 <> @verifyDoc1
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'national/alien card front', @oldVerifyDoc1, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldVerifyDoc2 <> @verifyDoc2
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'national/alien card back', @oldVerifyDoc2, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldVerifyDoc3 <> @verifyDoc3
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'passbook', @oldVerifyDoc3, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldVerifyDoc4 <> @verifyDoc4
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'selfieDoc', @oldVerifyDoc4, @user, GETDATE()
|
|
END
|
|
|
|
IF @oldFullName <> @fullName
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'fullName', @oldFullName, @user, GETDATE()
|
|
END
|
|
IF @oldCustomerEmail <> @customerEmail
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'customer email', @oldCustomerEmail, @user, GETDATE()
|
|
END
|
|
|
|
END
|
|
ELSE IF @flag = 'i-new' --if update is not full(if only few fields are allowed to modify)
|
|
BEGIN
|
|
|
|
SELECT @oldEmail = email,
|
|
@oldMobileNumber = mobile
|
|
FROM customerMaster (NOLOCK)
|
|
WHERE customerId = @customerId
|
|
|
|
IF @oldEmail is null
|
|
RETURN
|
|
|
|
IF @oldEmail <> @email
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'email', @oldEmail, @user, GETDATE()
|
|
END
|
|
IF @oldMobileNumber <> @mobileNumber
|
|
BEGIN
|
|
INSERT INTO TBLCUSTOMERMODIFYLOGS(customerId, columnName, newValue, modifiedBy, modifiedDate)
|
|
SELECT @customerId, 'mobile', @oldMobileNumber, @user, GETDATE()
|
|
END
|
|
END
|
|
ELSE IF @flag = 'modify-log'
|
|
BEGIN
|
|
DECLARE @table VARCHAR(MAX);
|
|
IF LOWER(@searchKey) = 'email'
|
|
BEGIN
|
|
SELECT @customerId = customerId FROM customermaster cm(NOLOCK)
|
|
WHERE email=@searchValue
|
|
IF NOT EXISTS(SELECT 'x' FROM dbo.customerMaster CM(NOLOCK) WHERE customerId = @customerId)
|
|
BEGIN
|
|
SELECT '1' errorCode, @searchValue + ' does not exists.' Msg, NULL Id
|
|
SELECT '1' errorCode, @searchValue + ' does not exists.' Msg, NULL Id
|
|
RETURN;
|
|
END
|
|
END
|
|
ELSE IF(@searchKey) = 'idnumber'
|
|
BEGIN
|
|
SELECT @customerId = customerId FROM customermaster cm(NOLOCK)
|
|
WHERE idnumber = @searchValue
|
|
IF NOT EXISTS(SELECT 'x' FROM dbo.customerMaster CM(NOLOCK) WHERE customerId = @customerId)
|
|
BEGIN
|
|
SELECT '1' errorCode, 'Id Number ' + @searchValue + ' does not exists.' Msg, NULL Id
|
|
SELECT '1' errorCode, 'Id Number ' + @searchValue + ' does not exists.' Msg, NULL Id
|
|
RETURN;
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @customerId = null;
|
|
END
|
|
|
|
SET @table =
|
|
'SELECT
|
|
rowId,
|
|
[Customer Email ID] = cm.email,
|
|
[Field Modified] = tcml.columnName,
|
|
[Previous Value] = case when tcml.columnName = ''nativeCountry'' then (select ctm.countryName from countryMaster ctm(nolock) where ctm.countryId=tcml.newValue)
|
|
when tcml.columnName = ''bankName'' then (select bl.BankName from koreanbanklist bl(nolock) where bl.bankCode = tcml.newValue)
|
|
when tcml.columnName = ''idType'' then (select sdv.detailTitle from staticDataValue sdv(nolock) where sdv.valueId = tcml.newValue)
|
|
else tcml.newValue
|
|
end,
|
|
[Modified Date] = CONVERT(VARCHAR(10),tcml.modifiedDate,102),
|
|
Modifier = tcml.modifiedBy
|
|
|
|
FROM TBLCUSTOMERMODIFYLOGS tcml(NOLOCK)
|
|
INNER JOIN customerMaster cm(NOLOCK) ON tcml.customerId = cm.customerId
|
|
WHERE tcml.customerId = '+convert(varchar,ISNULL(CAST(@customerId AS VARCHAR(MAX)),'cm.customerId'))+''
|
|
|
|
--used this for fetching previous value but in uat invalid datetime type is causing issue
|
|
--PreviousValue = case when tcml.columnName in (''idExpiryDate'',''dob'',''idIssueDate'') then CONVERT(VARCHAR(10),Cast(tcml.newValue as date),102)
|
|
-- else tcml.newValue
|
|
-- end,
|
|
|
|
IF @startDate IS NOT NULL and @endDate is not NULL
|
|
BEGIN
|
|
SET @table = @table + ' AND tcml.modifiedDate BETWEEN ''' + @startDate + ''' AND ''' + @endDate + ' 23:59:59'''
|
|
END
|
|
|
|
DECLARE @sql AS VARCHAR(MAX);
|
|
SET @sql='
|
|
SELECT COUNT(''x'') AS TXNCOUNT,'+@pageSize+' PAGESIZE,'+@pageNumber+' PAGENUMBER FROM ('+ @table+') AS tmp;
|
|
SELECT * FROM
|
|
(
|
|
SELECT ROW_NUMBER() OVER (ORDER BY [rowId] DESC) AS [S.N.],*
|
|
FROM
|
|
(
|
|
'+ @table +'
|
|
)A
|
|
)B WHERE 1 = 1 AND B.[S.N.] BETWEEN (('+@pageNumber+' - 1) * '+@pageSize+' + 1) AND '+@pageNumber+' * '+@pageSize+'
|
|
'
|
|
PRINT(@sql)
|
|
EXEC(@sql)
|
|
|
|
EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL
|
|
|
|
SELECT 'From Date' head, @startDate value union all
|
|
SELECT 'To Date' head, @endDate value UNION all
|
|
SELECT 'Search By' head, @searchKey UNION ALL
|
|
SELECT 'Search Value' head, @searchValue
|
|
|
|
SELECT 'Customer Modification Log Report' title
|
|
|
|
END
|
|
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|