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.
254 lines
13 KiB
254 lines
13 KiB
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- ======================================================================
|
|
-- Description: Only for "Easy Remit Customer Block Page" in Core System
|
|
-- ======================================================================
|
|
CREATE PROCEDURE [dbo].[PROC_EASYREMITFORBLOCK]
|
|
(
|
|
@flag VARCHAR(50),
|
|
@id BIGINT = NULL,
|
|
@block CHAR = NULL,
|
|
@searchBy VARCHAR(MAX) = NULL,
|
|
@searchValue VARCHAR(MAX) = NULL,
|
|
@email VARCHAR(150) = NULL,
|
|
@name VARCHAR(200) = NULL,
|
|
@walletAccountNo VARCHAR(100) = NULL,
|
|
@mobile VARCHAR(100) = NULL,
|
|
@startDate DATETIME = NULL,
|
|
@closeDate DATETIME = NULL,
|
|
@sortBy VARCHAR(100) = NULL,
|
|
@sortOrder VARCHAR(5) = NULL,
|
|
@pageSize INT = NULL,
|
|
@pageNumber INT = NULL,
|
|
@user VARCHAR(50) = NULL
|
|
)
|
|
|
|
AS
|
|
BEGIN
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
|
|
DECLARE
|
|
@table VARCHAR(MAX) = NULL
|
|
,@sqlFilter VARCHAR(MAX) = NULL
|
|
,@selectFieldList VARCHAR(MAX) = NULL
|
|
,@extraFieldList VARCHAR(MAX) = NULL
|
|
,@noPaging CHAR(1) = NULL
|
|
|
|
IF @FLAG = 'customerList'
|
|
BEGIN
|
|
|
|
IF @searchBy = 'email'
|
|
BEGIN
|
|
SET @email = @searchValue
|
|
END
|
|
ELSE IF @searchBy = 'fullName'
|
|
BEGIN
|
|
SET @name = @searchValue
|
|
END
|
|
ELSE IF @searchBy = 'walletAccountNo'
|
|
BEGIN
|
|
SET @walletAccountNo = @searchValue
|
|
END
|
|
ELSE IF @searchBy = 'mobile'
|
|
BEGIN
|
|
SET @mobile = @searchValue
|
|
END
|
|
|
|
|
|
SET @table =
|
|
N'(SELECT cm.customerId as ''customerId'',
|
|
cm.email as ''ID'',
|
|
cm.fullName as ''Name'',
|
|
com.countryName as ''Nationality'',
|
|
sd.detailTitle as ''ID_Type'',
|
|
cm.idNumber as ''ID_Number'',
|
|
cm.mobile as ''Mobile_Number'',
|
|
cm.walletAccountNo as ''Virtual_Account_Number'',
|
|
cm.approvedBy as ''Approved_By'',
|
|
cm.approvedDate as ''Approved_Date'',
|
|
cm.createdDate
|
|
FROM dbo.customerMaster cm(nolock)
|
|
LEFT JOIN dbo.staticDataValue sd(nolock) ON sd.valueId = cm.idType
|
|
INNER JOIN dbo.countryMaster com(nolock) ON com.countryId = cm.nativeCountry
|
|
WHERE cm.customerType =11048 and cm.approvedDate is not null '
|
|
|
|
IF @startDate IS NOT NULL
|
|
SET @table = @table + ' AND cm.approvedDate >= ''' + CONVERT(VARCHAR, @startDate, 101) + ' 00:00:00''';
|
|
|
|
IF @closeDate IS NOT NULL
|
|
SET @table = @table + ' AND cm.approvedDate <= ''' + CONVERT(VARCHAR, @closeDate, 101) + ' 23:59:59''';
|
|
|
|
SET @table = @table + ')x'
|
|
|
|
SET @sqlFilter = ''
|
|
|
|
IF @email IS NOT NULL
|
|
SET @sqlFilter += ' AND x.ID like ''' + @email + '%''';
|
|
|
|
IF @name IS NOT NULL
|
|
SET @sqlFilter += ' AND x.Name like ''' + @name + '%''';
|
|
|
|
IF @walletAccountNo IS NOT NULL
|
|
SET @sqlFilter += ' AND x.Virtual_Account_Number = ''' + @walletAccountNo + '''';
|
|
|
|
IF @mobile IS NOT NULL
|
|
SET @sqlFilter = @sqlFilter + ' AND REPLACE(x.Mobile_Number, ''-'', '''') =''' + Replace(@mobile, '-', '') + '''';
|
|
|
|
SET @selectFieldList = 'customerId, ID, Name, Nationality, ID_Type, ID_Number, Mobile_Number, Virtual_Account_Number, Approved_By, Approved_Date'
|
|
|
|
IF @sortBy IS NULL
|
|
SET @sortBy = 'createdDate'
|
|
|
|
IF @sortOrder IS NULL
|
|
SET @sortOrder = 'DESC'
|
|
|
|
|
|
EXEC dbo.Proc_paging
|
|
@table,
|
|
@sqlFilter,
|
|
@selectFieldList,
|
|
@extraFieldList,
|
|
@sortBy,
|
|
@sortOrder,
|
|
@pageSize,
|
|
@pageNumber,
|
|
@noPaging
|
|
|
|
RETURN
|
|
END
|
|
|
|
IF @FLAG = 'searchFilter'
|
|
BEGIN
|
|
|
|
SELECT '' [VALUE], 'SELECT' [TEXT]
|
|
UNION ALL
|
|
SELECT 'email', 'ID'
|
|
UNION ALL
|
|
SELECT 'fullName', 'Name'
|
|
UNION ALL
|
|
SELECT 'walletAccountNo', 'Virtual Account Number'
|
|
UNION ALL
|
|
SELECT 'mobile', 'Mobile Number'
|
|
|
|
END
|
|
|
|
IF @flag = 'checkCustomerInfo'
|
|
BEGIN
|
|
|
|
IF NOT EXISTS (SELECT 'A' FROM dbo.customerMaster(nolock) WHERE customerId = @id)
|
|
BEGIN
|
|
SELECT '1' ErrorCode, 'Requested customer ID is not exist.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 'A' FROM dbo.customerMaster(nolock) WHERE customerId=@id AND isActive='N' AND islocked='Y' AND isBlockWSB='Y')
|
|
BEGIN
|
|
SELECT '2' ErrorCode, 'Requested customer ID is already blocked.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
IF NOT EXISTS (SELECT 'A' FROM dbo.customerMaster(nolock)
|
|
WHERE customerId=@id AND isActive='Y' AND (islocked='N' OR islocked is null) AND (isBlockWSB='N' OR isBlockWSB is null))
|
|
BEGIN
|
|
SELECT '3' ErrorCode, 'Requested customer ID is already unblocked.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
SELECT '0' ErrorCode, 'Requested customer ID is fine.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'callCustomerInfo'
|
|
BEGIN
|
|
|
|
IF NOT EXISTS (SELECT COUNT(1) FROM dbo.customerMaster WHERE customerId = @id)
|
|
BEGIN
|
|
SELECT '1' ErrorCode, 'Requested customer ID is not exist.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
DECLARE @tlgSeqNo VARCHAR(10), @NewValue VARCHAR(10)
|
|
|
|
SET @NewValue= next value FOR dbo.wsb_sequence SET @tlgSeqNo=(SELECT CONVERT(VARCHAR(6), Getdate(), 12)) + @NewValue
|
|
|
|
SELECT dataLen =400,
|
|
orgtId='GME',
|
|
srvcDs='VT0401',
|
|
trxTypeCd='0200',
|
|
rspsCd='0000',
|
|
tlgSeqNo=@tlgSeqNo,
|
|
tlgSendHur=(SELECT Format(Getdate(), 'yyyyMMddHHmmss')),
|
|
filler=NULL,
|
|
username = Isnull(cm.email, ''),
|
|
[password] = dbo.Fnadecryptstring(cm.customerpassword),
|
|
[channel] = 'registration',
|
|
[account] = Isnull(cm.membershipid, ''),
|
|
walletaccountno,
|
|
bankaccountno,
|
|
fullname,
|
|
customerbankname,
|
|
bankCode =NULL,
|
|
cm.idtype,
|
|
Replace(cm.idnumber, ' ', '') AS [idNumber], CONVERT(VARCHAR(6), cm.dob, 12) AS [dobYMD],
|
|
CASE WHEN cm.gender = '97' THEN '7' WHEN cm.gender = '98' THEN '8' END AS [genderCode],
|
|
CASE WHEN cm.nativecountry = '238' THEN '1' WHEN cm.nativecountry = '113' THEN '2' WHEN cm.nativecountry = '45' THEN '3' ELSE '4' END AS [nativeCountryCode],customerid
|
|
FROM dbo.customermaster cm(nolock)
|
|
WHERE cm.customerid = @Id
|
|
|
|
SELECT '0' ErrorCode, 'Confirmed to calling requested customer ID.' Msg, @Id id
|
|
RETURN
|
|
END
|
|
|
|
IF @flag = 'requestConfirm'
|
|
BEGIN
|
|
|
|
IF NOT EXISTS (SELECT COUNT(1) FROM dbo.customerMaster(nolock) WHERE customerId = @id)
|
|
BEGIN
|
|
SELECT '1' ErrorCode, 'Requested customer ID is not exist.' Msg, NULL
|
|
RETURN
|
|
END
|
|
|
|
IF @block='Y'
|
|
BEGIN
|
|
UPDATE customerMaster
|
|
SET isActive = 'N',
|
|
islocked = 'Y',
|
|
isBlockWSB = 'Y'
|
|
WHERE customerId = @id;
|
|
|
|
SELECT '0' ErrorCode, 'Confirmed to update requested customer ID to blocked.' Msg, @Id id
|
|
RETURN
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE customerMaster
|
|
SET isActive = 'Y',
|
|
islocked = 'N',
|
|
isBlockWSB = 'N'
|
|
WHERE customerId = @id;
|
|
|
|
SELECT '0' ErrorCode, 'Confirmed to update requested customer ID to unblocked.' Msg, @Id id
|
|
RETURN
|
|
END
|
|
END
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
|
|
DECLARE @errorMessage VARCHAR(max)
|
|
|
|
SET @errorMessage = Error_message()
|
|
|
|
EXEC Proc_errorhandler 1, @errorMessage, NULL
|
|
|
|
END CATCH
|
|
END
|