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.
1049 lines
49 KiB
1049 lines
49 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_customerDocumentType] Script Date: 2/6/2024 11:07:20 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <12/27/2018>
|
|
-- Description: <>
|
|
-- Aug04 -> add CdId
|
|
-- =============================================
|
|
ALTER PROCEDURE [dbo].[proc_customerDocumentType]
|
|
-- Add the parameters for the stored procedure here
|
|
@cdId INT = NULL
|
|
,@SN INT = NULL
|
|
,@fileName VARCHAR(200) = NULL
|
|
,@fileDescription VARCHAR(200) = NULL
|
|
,@fileType VARCHAR(20) = NULL
|
|
,@createdDate VARCHAR(30) = NULL
|
|
,@documentType INT = NULL
|
|
,@createdBy VARCHAR(30) = NULL
|
|
,@flag VARCHAR(30)
|
|
,@customerId INT = NULL
|
|
,@user VARCHAR(30) = NULL
|
|
,@agentId INT = NULL
|
|
,@branchId INT = NULL
|
|
,@sortBy VARCHAR(50) = NULL
|
|
,@sortOrder VARCHAR(5) = NULL
|
|
,@pageSize INT = NULL
|
|
,@pageNumber INT = NULL
|
|
,@searchCriteria VARCHAR(30) = NULL
|
|
,@searchValue VARCHAR(50) = NULL
|
|
,@approvedBy VARCHAR(30) = NULL
|
|
,@membershipId VARCHAR(30) = NULL
|
|
,@signatureType VARCHAR(100) = NULL
|
|
,@signatureUploadId VARCHAR(30) = NULL
|
|
,@sessionId VARCHAR(200) = NULL
|
|
,@rowIdField VARCHAR(20) = NULL
|
|
,@referralCode VARCHAR(20) = NULL
|
|
,@fromDate VARCHAR(10) = NULL
|
|
,@toDate VARCHAR(10) = NULL
|
|
,@isCustomerId CHAR(1) = NULL
|
|
,@url VARCHAR(500) = NULL
|
|
,@fileBytes VARCHAR(500) = NULL
|
|
,@idType VARCHAR(20) = NULL
|
|
AS
|
|
DECLARE @table NVARCHAR(MAX)
|
|
,@sql_filter VARCHAR(MAX)
|
|
,@extra_field_list VARCHAR(MAX)
|
|
,@select_field_list VARCHAR(MAX);
|
|
|
|
BEGIN
|
|
--------------------------------------------------
|
|
--> JME-566 - exclude Compliance Document
|
|
--> Aug 05- > add colum cicd flag@getCustomerCompliance
|
|
--> Aug 06- > add colum cicd flag@getDocByCustomerId
|
|
--------------------------------------------------
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT For Grid statements.
|
|
-- #101- Mobile Changes
|
|
-- #829 - image crop , getById
|
|
-- #750 - Deposit slip doc show in customer document , @flag = 's'
|
|
-- #11796 - revise trust dock , @flag = 'i-ekyc'
|
|
SET NOCOUNT ON;
|
|
|
|
IF @flag = 's'
|
|
BEGIN
|
|
IF @sortBy = 'SN'
|
|
SET @sortBy = NULL;
|
|
|
|
IF @sortBy IS NULL
|
|
OR @sortBy = ''
|
|
SET @sortBy = 'createdDate';
|
|
|
|
IF @sortOrder IS NULL
|
|
OR @sortOrder = ''
|
|
SET @sortOrder = 'DESC';
|
|
SET @table =
|
|
'
|
|
(
|
|
SELECT cdid,
|
|
customerId,
|
|
[fileName],
|
|
fileDescription,
|
|
CASE WHEN fileType = ''signature'' THEN ''image''
|
|
ELSE fileType END fileType,
|
|
documentType,
|
|
CD.sessionId,
|
|
cd.createdBy,
|
|
cd.createdDate,
|
|
ISNULL(sv.detailTitle,CASE WHEN documentType = 0 THEN fileDescription ELSE ''image'' END) documentTypeName
|
|
FROM dbo.customerDocument cd (NOLOCK)
|
|
LEFT JOIN dbo.staticDataValue sv (NOLOCK) ON sv.valueId=cd.documentType
|
|
where ISNULL(isDeleted,''N'') = ''N''
|
|
--AND ISNULL(cd.fileDescription,'''') <> ''Compliance Document''
|
|
--WHERE (fileType <> ''signature'' OR documenttype <> ''0'')
|
|
|
|
UNION ALL
|
|
|
|
SELECT cdId,
|
|
customerId,
|
|
[fileName],
|
|
''Deposit Slip'' as filedescription,
|
|
fileType = ds.filedescription,
|
|
ds.fileType AS [documentType],
|
|
'''' as sessionId,
|
|
ds.createdBy,
|
|
ds.createdDate,
|
|
''Deposit Slip'' AS documenttypeName
|
|
--ISNULL(sv.detailTitle,CASE WHEN documentType = 0 THEN fileDescription ELSE ''image'' END) documentTypeName
|
|
FROM depositslip ds (NOLOCK)
|
|
--LEFT JOIN dbo.staticDataValue sv (NOLOCK) ON sv.valueId= ds.documentType
|
|
where ds.[status] = 1
|
|
)x'
|
|
;
|
|
SET @sql_filter = '';
|
|
|
|
IF @customerId IS NOT NULL
|
|
BEGIN
|
|
SET @sql_filter += ' AND customerId=' + CAST(@customerId AS VARCHAR(10)) + '';
|
|
END
|
|
|
|
IF @fileType IS NOT NULL
|
|
AND @fileType != '0'
|
|
BEGIN
|
|
DECLARE @fileFilterName VARCHAR(150) = NULL
|
|
|
|
SELECT @fileFilterName = LTRIM(RTRIM(detailTitle))
|
|
FROM dbo.staticDataValue
|
|
WHERE valueId = @fileType
|
|
|
|
SET @fileFilterName = REPLACE(@fileFilterName, ' ', '_');
|
|
SET @sql_filter += ' AND fileName LIKE ''%' + @fileFilterName + '%''';
|
|
END
|
|
|
|
IF @fileDescription IS NOT NULL
|
|
BEGIN
|
|
SET @sql_filter += ' AND fileDescription=''' + @fileDescription + '''';
|
|
END
|
|
|
|
IF @createdDate IS NOT NULL
|
|
BEGIN
|
|
SET @sql_filter += ' AND createdDate between ''' + CONVERT(VARCHAR, @createdDate, 101) + ''' AND ''' + CONVERT(VARCHAR, @createdDate, 101) + ' 23:59:59''';
|
|
END
|
|
|
|
PRINT 'Filter Data:' + @sql_filter
|
|
|
|
SET @select_field_list = 'cdId,customerId,sessionId,[fileName],fileDescription,fileType,documentType,createdBy,createdDate,documentTypeName';
|
|
|
|
EXEC dbo.proc_paging @table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 's-idPicture'
|
|
BEGIN
|
|
IF @sortBy = 'SN'
|
|
SET @sortBy = NULL;
|
|
|
|
IF @sortBy IS NULL
|
|
OR @sortBy = ''
|
|
SET @sortBy = 'createdDate';
|
|
|
|
IF @sortOrder IS NULL
|
|
OR @sortOrder = ''
|
|
SET @sortOrder = 'DESC';
|
|
SET @table = '
|
|
(
|
|
SELECT cdid,
|
|
customerId,
|
|
[fileName],
|
|
fileDescription,
|
|
CASE WHEN fileType = ''signature'' THEN ''image''
|
|
ELSE fileType END fileType,
|
|
documentType,
|
|
CD.sessionId,
|
|
cd.createdBy,
|
|
cd.createdDate,
|
|
ISNULL(sv.detailTitle,''signature'') documentTypeName
|
|
FROM dbo.customerDocument cd (NOLOCK)
|
|
LEFT JOIN dbo.staticDataValue sv (NOLOCK) ON sv.valueId=cd.documentType
|
|
where customerId = ''0'')x';
|
|
SET @sql_filter = '';
|
|
|
|
IF @referralCode IS NOT NULL
|
|
BEGIN
|
|
SET @sql_filter += ' AND createdby=''' + CAST(@referralCode AS VARCHAR(10)) + '''';
|
|
END
|
|
|
|
IF @fromDate IS NOT NULL
|
|
BEGIN
|
|
SET @sql_filter += ' AND createdDate between ''' + CONVERT(VARCHAR, @fromDate, 101) + ''' AND ''' + CONVERT(VARCHAR, @fromDate, 101) + ' 23:59:59''';
|
|
END
|
|
|
|
PRINT 'Filter Data:' + @sql_filter
|
|
|
|
SET @select_field_list = 'cdId,customerId,sessionId,[fileName],fileDescription,fileType,documentType,createdBy,createdDate,documentTypeName';
|
|
|
|
EXEC dbo.proc_paging @table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 's-id'
|
|
BEGIN
|
|
IF @sortBy = 'SN'
|
|
SET @sortBy = NULL;
|
|
|
|
IF @sortBy IS NULL
|
|
OR @sortBy = ''
|
|
SET @sortBy = 'createdDate';
|
|
|
|
--select * from customerDocumentTmp
|
|
IF @sortOrder IS NULL
|
|
OR @sortOrder = ''
|
|
SET @sortOrder = 'DESC';
|
|
SET @table = '
|
|
(
|
|
SELECT rowId,
|
|
cd.customerId,
|
|
membershipid,
|
|
cd.[fileName],
|
|
cd.documentType,
|
|
cd.createdBy,
|
|
cd.createdDate,
|
|
ISNULL(sv.detailTitle,''signature'') documentTypeName
|
|
FROM dbo.customerDocumentTmp cd (NOLOCK)
|
|
LEFT JOIN dbo.customerMaster cm(NOLOCK) ON cm.customerId = cd.customerId
|
|
LEFT JOIN dbo.staticDataValue sv (NOLOCK) ON sv.valueId=cd.documentType
|
|
where ISNULL(cd.isDeleted, 0) = 0
|
|
AND cd.approvedBy IS NULL )x';
|
|
SET @sql_filter = '';
|
|
|
|
IF ISNULL(@customerId, '') <> ''
|
|
BEGIN
|
|
SET @sql_filter += ' AND customerId = ''' + CAST(@customerId AS VARCHAR) + '''';
|
|
END
|
|
|
|
IF ISNULL(@fileType, '') <> ''
|
|
BEGIN
|
|
SET @sql_filter += ' AND documentType = ''' + CAST(@fileType AS VARCHAR) + '''';
|
|
END
|
|
|
|
IF ISNULL(@createdDate, '') <> ''
|
|
BEGIN
|
|
SET @sql_filter += ' AND CAST(createdDate AS DATE) = ''' + @createdDate + '''';
|
|
END
|
|
|
|
PRINT 'Filter Data:' + @sql_filter
|
|
|
|
SET @select_field_list = 'rowId,customerId,membershipid,[fileName],documentType,createdBy,createdDate,documentTypeName';
|
|
|
|
EXEC dbo.proc_paging @table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'getDocByCustomerId'
|
|
BEGIN
|
|
SELECT [fileName]
|
|
,fileType
|
|
,documentType
|
|
,documentName
|
|
,cdid
|
|
,CREATEDDATE
|
|
,idType
|
|
FROM (
|
|
SELECT ROW_NUMBER() OVER (
|
|
PARTITION BY 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,'DOC') idType
|
|
FROM dbo.customerDocument(NOLOCK) cm
|
|
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'
|
|
WHERE customerId = @customerId
|
|
AND ISNULL(isdeleted, 'N') = 'N'
|
|
) x
|
|
WHERE x.sn = 1
|
|
ORDER BY x.cdId
|
|
END
|
|
|
|
IF @flag = 'getSignatureByCustomerId'
|
|
BEGIN
|
|
SELECT cm.fileName
|
|
,cm.fileType
|
|
,cm.documentType
|
|
,CASE
|
|
WHEN CM.documentType = '0'
|
|
THEN 'Customer Signature'
|
|
ELSE Sv.detailTitle
|
|
END documentName
|
|
FROM dbo.customerDocument(NOLOCK) cm
|
|
LEFT JOIN dbo.staticDataValue(NOLOCK) Sv ON Sv.valueId = cm.documentType
|
|
WHERE customerId = @customerId
|
|
AND cm.fileDescription = @fileDescription
|
|
AND cm.sessionId = @sessionId
|
|
END
|
|
|
|
IF @flag = 'getById'
|
|
BEGIN
|
|
IF @cdId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1
|
|
,'Customer Document Id Is Required.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @fileDescription = 'Deposit Slip'
|
|
BEGIN
|
|
SELECT CONVERT(VARCHAR, cm.createdDate, 23) AS createdDate
|
|
,cm.customerId
|
|
,ds.fileName
|
|
,ds.createdBy
|
|
,cm.membershipId
|
|
,'Deposit Slip' AS fileDescription
|
|
,'Deposit Slip' AS documentName
|
|
FROM dbo.depositSlip ds(NOLOCK)
|
|
LEFT JOIN dbo.customerMaster cm(NOLOCK) ON cm.customerId = ds.customerId
|
|
--LEFT JOIN dbo.staticDataValue(NOLOCK) Sv ON Sv.valueId = cm.documentType
|
|
WHERE cdId = @cdId;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT CONVERT(VARCHAR, cm.createdDate, 23) AS createdDate
|
|
,cm.customerId
|
|
,cd.fileName
|
|
,cd.createdBy
|
|
,cm.membershipId
|
|
,cd.filedescription
|
|
,Sv.detailTitle documentName
|
|
FROM dbo.customerDocument cd(NOLOCK)
|
|
LEFT JOIN dbo.customerMaster cm(NOLOCK) ON cm.customerId = cd.customerId
|
|
LEFT JOIN dbo.staticDataValue(NOLOCK) Sv ON Sv.valueId = cm.documentType
|
|
WHERE cdId = @cdId;
|
|
END
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'getById-tmp'
|
|
BEGIN
|
|
IF @cdId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1
|
|
,'Customer Document Id Is Required.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
DECLARE @isMoved VARCHAR(1) = 'N'
|
|
DECLARE @filenametmp VARCHAR(200);
|
|
DECLARE @createdBytmp VARCHAR(200);
|
|
|
|
SELECT @filenametmp = [filename]
|
|
,@createdBytmp = customerId
|
|
FROM customerDocumentTmp
|
|
WHERE rowId = @cdId;
|
|
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM customerDocument(NOLOCK)
|
|
WHERE customerId = @createdBytmp
|
|
AND fileName = @filenametmp
|
|
)
|
|
BEGIN
|
|
SET @isMoved = 'Y'
|
|
END
|
|
|
|
SELECT cm.createdDate
|
|
,cm.customerId
|
|
,cd.fileName
|
|
,cd.createdBy
|
|
,cm.membershipId
|
|
,filedescription = ''
|
|
,cm.fullName
|
|
,cm.username
|
|
,idType = SV.detailTitle
|
|
,idDetailDate = CAST(CAST(idIssueDate AS DATE) AS VARCHAR) + ' - ' + CAST(CAST(idExpiryDate AS DATE) AS VARCHAR)
|
|
,@isMoved AS isMoved
|
|
,cm.idNumber
|
|
FROM dbo.customerDocumentTmp cd(NOLOCK)
|
|
LEFT JOIN dbo.customerMaster cm(NOLOCK) ON cm.customerId = cd.customerId
|
|
LEFT JOIN staticDataValue SV(NOLOCK) ON SV.valueId = cm.idType
|
|
WHERE rowId = @cdId;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
-- Insert statements for procedure here
|
|
IF @flag = 'i'
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,[fileName]
|
|
,fileDescription
|
|
,fileType
|
|
,documentType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,approvedBy
|
|
,approvedDate
|
|
,agentId
|
|
,branchId
|
|
,sessionId
|
|
,isOnlineDoc
|
|
,idtype
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,-- customerId - int
|
|
@fileName
|
|
,-- fileName - varchar(50)
|
|
@fileDescription
|
|
,-- fileDescription - varchar(100)
|
|
@fileType
|
|
,-- fileType - varchar(10)
|
|
@documentType
|
|
,NULL
|
|
,-- isDeleted - char(1)
|
|
@user
|
|
,-- createdBy - varchar(30)
|
|
GETDATE()
|
|
,-- createdDate - datetime
|
|
@user
|
|
,-- modifiedBy - varchar(30)
|
|
GETDATE()
|
|
,-- modifiedDate - datetime
|
|
''
|
|
,-- approvedBy - varchar(30)
|
|
GETDATE()
|
|
,-- approvedDate - datetime
|
|
@agentId
|
|
,-- agentId - int
|
|
@branchId
|
|
,-- branchId - int
|
|
@rowIdField
|
|
,ISNULL(@isCustomerId,'N')
|
|
,ISNULL(@idType,'')
|
|
);
|
|
|
|
SET @cdId = @@IDENTITY
|
|
|
|
DECLARE @documentCount INT
|
|
DECLARE @documentCount1 INT
|
|
DECLARE @verificationCode VARCHAR(100)
|
|
|
|
SELECT @verificationCode = verificationCode FROM dbo.customerMaster WHERE customerId = @customerId
|
|
|
|
SELECT @documentCount = COUNT('X')
|
|
FROM dbo.customerDocument cd
|
|
INNER JOIN dbo.customerMaster cm ON cm.customerId = cd.customerId
|
|
WHERE cd.customerId = @customerId AND cm.createdFrom IN( 'M','O') AND cd.documentType IN ('11394')
|
|
|
|
SELECT @documentCount1 = COUNT('X')
|
|
FROM dbo.customerDocument cd
|
|
INNER JOIN dbo.customerMaster cm ON cm.customerId = cd.customerId
|
|
WHERE cd.customerId = @customerId AND cm.createdFrom IN( 'M','O') AND cd.documentType IN ('11396')
|
|
|
|
IF @documentCount >= 1 AND @documentCount1 >= 1
|
|
BEGIN
|
|
IF(@verificationCode <> 'COMPLETED')
|
|
BEGIN
|
|
UPDATE customerMaster
|
|
SET verificationCode = 'PROCESSING'
|
|
WHERE customerId = @customerId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE customerMaster
|
|
SET verificationCode = @verificationCode
|
|
WHERE customerId = @customerId
|
|
END
|
|
END
|
|
|
|
UPDATE dbo.customerDocument
|
|
SET sessionId = 'IME' + RIGHT('0000000' + CAST(@cdId AS VARCHAR), 6)
|
|
WHERE cdId = @cdId
|
|
END
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'com-i'
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,[fileName]
|
|
,fileDescription
|
|
,fileType
|
|
,documentType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,approvedBy
|
|
,approvedDate
|
|
,agentId
|
|
,branchId
|
|
,sessionId
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,-- customerId - int
|
|
@fileName
|
|
,-- fileName - varchar(50)
|
|
@fileDescription
|
|
,-- fileDescription - varchar(100)
|
|
@fileType
|
|
,-- fileType - varchar(10)
|
|
@documentType
|
|
,NULL
|
|
,-- isDeleted - char(1)
|
|
@user
|
|
,-- createdBy - varchar(30)
|
|
GETDATE()
|
|
,-- createdDate - datetime
|
|
@user
|
|
,-- modifiedBy - varchar(30)
|
|
GETDATE()
|
|
,-- modifiedDate - datetime
|
|
''
|
|
,-- approvedBy - varchar(30)
|
|
GETDATE()
|
|
,-- approvedDate - datetime
|
|
@agentId
|
|
,-- agentId - int
|
|
@branchId
|
|
,-- branchId - int
|
|
@rowIdField
|
|
);
|
|
END
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'u'
|
|
BEGIN
|
|
IF @cdId IS NULL
|
|
BEGIN
|
|
EXEC proc_errorHandler 1
|
|
,'Customer Document Number Is Required.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
UPDATE dbo.customerDocument
|
|
SET [fileName] = CASE
|
|
WHEN @fileName IS NOT NULL
|
|
THEN @fileName
|
|
ELSE [fileName]
|
|
END
|
|
,fileDescription = CASE
|
|
WHEN @fileDescription IS NOT NULL
|
|
THEN @fileDescription
|
|
ELSE fileDescription
|
|
END
|
|
,fileType = CASE
|
|
WHEN @fileType IS NOT NULL
|
|
THEN @fileType
|
|
ELSE fileType
|
|
END
|
|
,documentType = CASE
|
|
WHEN @documentType IS NOT NULL
|
|
THEN @documentType
|
|
ELSE documentType
|
|
END
|
|
,modifiedBy = CASE
|
|
WHEN @user IS NOT NULL
|
|
THEN @user
|
|
ELSE modifiedBy
|
|
END
|
|
,approvedBy = CASE
|
|
WHEN @approvedBy IS NOT NULL
|
|
THEN @approvedBy
|
|
ELSE approvedBy
|
|
END
|
|
,modifiedDate = GETDATE()
|
|
WHERE cdId = @cdId;
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'AddSignature'
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,fileName
|
|
,fileDescription
|
|
,fileType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,documentType
|
|
,sessionId
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,@fileName
|
|
,ISNULL(@signatureType, 'Customer Signature')
|
|
,'signature'
|
|
,'N'
|
|
,@user
|
|
,GETDATE()
|
|
,'0'
|
|
,@signatureUploadId
|
|
)
|
|
|
|
SET @cdId = @@IDENTITY
|
|
|
|
IF @signatureType = 'Counter (Customer-Signature)'
|
|
UPDATE customerMaster
|
|
SET isVerifiedByCustomer = 1
|
|
WHERE customerId = @customerId
|
|
|
|
IF @signatureType = 'Counter (Customer-Signature-Receiver)'
|
|
UPDATE receiverInformation
|
|
SET isVerified = 0
|
|
WHERE receiverId = @signatureUploadId
|
|
|
|
IF @signatureType = 'Counter (Customer-Signature-txn)'
|
|
UPDATE remittrantemp
|
|
SET payStatus = 'Unpaid'
|
|
WHERE controlno = dbo.fnaencryptstring(@signatureUploadId)
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,@cdId;
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @flag = 'customer-upload-path'
|
|
BEGIN
|
|
SELECT CAST(CUSTOMERID AS VARCHAR) + '|' + CAST(MEMBERSHIPID AS VARCHAR) + '|' + CAST(CAST(CREATEDDATE AS DATE) AS VARCHAR) ID
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @customerId
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'customer-path-check'
|
|
BEGIN
|
|
IF ISNULL(@isCustomerId, 'N') = 'Y'
|
|
SELECT @membershipId = membershipId
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @membershipId
|
|
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM customerMaster CM(NOLOCK)
|
|
INNER JOIN customerDocument CD(NOLOCK) ON CD.customerId = CM.customerId
|
|
WHERE CM.membershipId = @membershipId
|
|
AND CD.fileType = @fileType
|
|
AND CD.sessionId = @rowIdField
|
|
AND fileDescription = 'bene-reg-form'
|
|
)
|
|
BEGIN
|
|
SELECT '1' ID
|
|
|
|
RETURN
|
|
END
|
|
|
|
SELECT '0|' + CAST(CM.CUSTOMERID AS VARCHAR) + '|' + CAST(MEMBERSHIPID AS VARCHAR) + '|' + CAST(CAST(CM.CREATEDDATE AS DATE) AS VARCHAR)
|
|
FROM customerMaster CM(NOLOCK)
|
|
WHERE membershipId = @membershipId
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'customer-path-check-new'
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT *
|
|
FROM customerMaster CM(NOLOCK)
|
|
INNER JOIN customerDocument CD(NOLOCK) ON CD.customerId = CM.customerId
|
|
WHERE CM.membershipId = @membershipId
|
|
AND CD.fileType = @fileType
|
|
AND CD.sessionId = @rowIdField
|
|
AND fileDescription = 'customer-letter-save'
|
|
)
|
|
BEGIN
|
|
SELECT '1' ID
|
|
|
|
RETURN
|
|
END
|
|
|
|
SELECT '0|' + CAST(CUSTOMERID AS VARCHAR) + '|' + CAST(MEMBERSHIPID AS VARCHAR) + '|' + CAST(CAST(CREATEDDATE AS DATE) AS VARCHAR) ID
|
|
FROM customerMaster(NOLOCK)
|
|
WHERE customerId = @rowIdField
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'Add-file-bene'
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,fileName
|
|
,fileDescription
|
|
,fileType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,documentType
|
|
,sessionId
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,@fileName
|
|
,'bene-reg-form'
|
|
,@fileType
|
|
,'N'
|
|
,@user
|
|
,GETDATE()
|
|
,11056
|
|
,@rowIdField
|
|
)
|
|
|
|
SET @cdId = @@IDENTITY
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,@cdId;
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @flag = 'Add-file-save'
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,fileName
|
|
,fileDescription
|
|
,fileType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,documentType
|
|
,sessionId
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,@fileName
|
|
,'bene-reg-form'
|
|
,@fileType
|
|
,'N'
|
|
,@user
|
|
,GETDATE()
|
|
,11056
|
|
,@rowIdField
|
|
)
|
|
|
|
SET @cdId = @@IDENTITY
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,@cdId;
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @flag = 'Add-letter-save'
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,fileName
|
|
,fileDescription
|
|
,fileType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,documentType
|
|
,sessionId
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,@fileName
|
|
,'customer-letter-save'
|
|
,@fileType
|
|
,'N'
|
|
,@user
|
|
,GETDATE()
|
|
,'0'
|
|
,@rowIdField
|
|
)
|
|
|
|
SET @cdId = @@IDENTITY
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,@cdId;
|
|
|
|
RETURN;
|
|
END
|
|
END
|
|
|
|
IF @flag = 'delete'
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM customerDocument
|
|
WHERE cdId = @cdId
|
|
)
|
|
BEGIN
|
|
UPDATE customerDocument
|
|
SET isDeleted = 'Y'
|
|
WHERE cdId = @cdId
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record deleted successfully'
|
|
,@cdId
|
|
END
|
|
END
|
|
|
|
IF @flag = 'getCustomerCompliance'
|
|
BEGIN
|
|
SELECT CM.CREATEDDATE
|
|
,cm.fileName
|
|
,cm.fileType
|
|
,cm.documentType
|
|
,CC.MEMBERSHIPID
|
|
,CAST(CAST(CC.CREATEDDATE AS DATE) AS VARCHAR) CUSTOMERDATE
|
|
,CC.CUSTOMERID
|
|
,cm.cdId
|
|
FROM dbo.customerDocument(NOLOCK) cm
|
|
INNER JOIN CUSTOMERMASTER CC(NOLOCK) ON CC.CUSTOMERID = CM.CUSTOMERID
|
|
LEFT JOIN dbo.staticDataValue(NOLOCK) Sv ON Sv.valueId = cm.documentType
|
|
WHERE CC.customerId = @cdId
|
|
AND CM.SESSIONID = @rowIdField
|
|
AND CM.fileDescription = 'Compliance Document'
|
|
AND ISNULL(CM.isdeleted, 'N') = 'N'
|
|
END
|
|
|
|
IF @flag = 'depositslip-list'
|
|
BEGIN
|
|
IF @sortBy = 'SN'
|
|
SET @sortBy = NULL;
|
|
|
|
IF @sortBy IS NULL
|
|
OR @sortBy = ''
|
|
SET @sortBy = 'createdDate';
|
|
|
|
--select * from customerDocumentTmp
|
|
IF @sortOrder IS NULL
|
|
OR @sortOrder = ''
|
|
SET @sortOrder = 'DESC';
|
|
SET @table = '
|
|
(
|
|
SELECT rowId,
|
|
customerId,
|
|
[fileName],
|
|
fileType,
|
|
ds.createdBy,
|
|
ds.createdDate,
|
|
ISNULL(sv.detailTitle,''signature'') documentTypeName
|
|
FROM dbo.depositSlip ds (NOLOCK)
|
|
LEFT JOIN dbo.staticDataValue sv (NOLOCK) ON sv.valueId=ds.documentType
|
|
where ISNULL(isDeleted, 0) = 0
|
|
AND approvedBy IS NULL )x';
|
|
SET @sql_filter = '';
|
|
|
|
IF ISNULL(@customerId, '') <> ''
|
|
BEGIN
|
|
SET @sql_filter += ' AND customerId = ''' + CAST(@customerId AS VARCHAR) + '''';
|
|
END
|
|
|
|
IF ISNULL(@fileType, '') <> ''
|
|
BEGIN
|
|
SET @sql_filter += ' AND fileType = ''' + CAST(@fileType AS VARCHAR) + '''';
|
|
END
|
|
|
|
IF ISNULL(@createdDate, '') <> ''
|
|
BEGIN
|
|
SET @sql_filter += ' AND CAST(createdDate AS DATE) = ''' + @createdDate + '''';
|
|
END
|
|
|
|
PRINT 'Filter Data:' + @sql_filter
|
|
|
|
SET @select_field_list = 'rowId,customerId,[fileName],fileType,createdBy,createdDate,documentTypeName';
|
|
|
|
EXEC dbo.proc_paging @table
|
|
,@sql_filter
|
|
,@select_field_list
|
|
,@extra_field_list
|
|
,@sortBy
|
|
,@sortOrder
|
|
,@pageSize
|
|
,@pageNumber;
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
IF @flag = 'i-ekyc'
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT TOP 1 1
|
|
FROM customerDocument(NOLOCK)
|
|
WHERE documentFolder = @URL
|
|
AND isOnlineDoc = 1
|
|
)
|
|
BEGIN
|
|
SELECT @cdId = cdId
|
|
FROM customerDocument(NOLOCK)
|
|
WHERE documentFolder = @url
|
|
|
|
IF @cdId IS NOT NULL
|
|
BEGIN
|
|
EXEC proc_customerDocumentType @flag = 'u'
|
|
,@cdId = @cdId
|
|
,@fileName = @fileName
|
|
,@fileDescription = @fileDescription
|
|
,@fileType = @fileType
|
|
,@documentType = @documentType
|
|
,@user = @user
|
|
,@approvedBy = @approvedBy
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
IF ISNULL(@fileName, '') <> ''
|
|
BEGIN
|
|
INSERT INTO dbo.customerDocument (
|
|
customerId
|
|
,[fileName]
|
|
,fileDescription
|
|
,fileType
|
|
,documentType
|
|
,isDeleted
|
|
,createdBy
|
|
,createdDate
|
|
,modifiedBy
|
|
,modifiedDate
|
|
,approvedBy
|
|
,approvedDate
|
|
,agentId
|
|
,branchId
|
|
,sessionId
|
|
,documentFolder
|
|
,isProfilePic
|
|
,isOnlineDoc
|
|
)
|
|
VALUES (
|
|
@customerId
|
|
,@fileName
|
|
,@fileDescription
|
|
,@fileType
|
|
,@documentType
|
|
,NULL
|
|
,@user
|
|
,GETDATE()
|
|
,@user
|
|
,GETDATE()
|
|
,''
|
|
,GETDATE()
|
|
,@agentId
|
|
,@branchId
|
|
,@rowIdField
|
|
,@url
|
|
,CASE
|
|
WHEN @documentType = '11440'
|
|
THEN 1
|
|
ELSE 0
|
|
END
|
|
,1
|
|
);
|
|
|
|
SET @cdId = @@IDENTITY
|
|
|
|
UPDATE dbo.customerDocument
|
|
SET sessionId = 'JME' + RIGHT('0000000' + CAST(@cdId AS VARCHAR), 6)
|
|
WHERE cdId = @cdId END
|
|
|
|
EXEC proc_errorHandler 0
|
|
,'Record updated successfully.'
|
|
,NULL;
|
|
|
|
RETURN;
|
|
END
|
|
END;
|
|
END;
|
|
--EXEC proc_customerDocumentType @flag='getCustomerCompliance' ,@user ='admin' ,@cdId ='48645' ,@rowIdField ='10127799'
|
|
--EXEC proc_customerDocumentType @flag='getCustomerCompliance' ,@user ='admin' ,@cdId ='46441'
|
|
|