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.
241 lines
17 KiB
241 lines
17 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_CustomerBankVerification] Script Date: 9/17/2019 2:37:27 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
ALTER PROC [dbo].[PROC_CustomerBankVerification]
|
|
(
|
|
@flag VARCHAR(20)
|
|
,@pCustomerId BIGINT = NULL
|
|
,@pCertNumber CHAR(4) = NULL
|
|
,@pCertLimitCount INT = NULL
|
|
,@pBankCode VARCHAR(10) = NULL
|
|
,@pBankAccountNo VARCHAR(20) = NULL
|
|
,@pIdType VARCHAR(10) = NULL
|
|
,@pIdNumber VARCHAR(50) = NULL
|
|
,@pDob VARCHAR(50) = NULL
|
|
,@pGender VARCHAR(10) = NULL
|
|
,@pNativeCountry INT = NULL
|
|
,@pAction VARCHAR(10) = NULL
|
|
,@pEmail VARCHAR(100) = NULL
|
|
)
|
|
AS
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @vCustomerId BIGINT = 0
|
|
DECLARE @vTemp VARCHAR(10)
|
|
DECLARE @vAction VARCHAR(10)
|
|
DECLARE @vCertLimitCount INT
|
|
DECLARE @errorMessage VARCHAR(MAX)
|
|
DECLARE @vErrorCode INT
|
|
DECLARE @tempTbl TABLE (errorcode VARCHAR(5), msg VARCHAR(MAX), id VARCHAR(50))
|
|
BEGIN TRY
|
|
|
|
IF @flag = 'REQ'
|
|
BEGIN
|
|
|
|
---------------------------------------------------------------------
|
|
-- 기존에 등록된 계좌가 있는지 체크한다.
|
|
---------------------------------------------------------------------
|
|
SET @vTemp = 'N'
|
|
SELECT @vTemp = 'Y',
|
|
@vAction = action,
|
|
@vCertLimitCount = certLimitCount
|
|
FROM TblCustomerBankVerification(nolock)
|
|
WHERE customerId = @pCustomerId
|
|
|
|
IF (@vAction = 'SUCCESS')
|
|
BEGIN
|
|
SET @errorMessage = 'This account is already registered.'
|
|
EXEC proc_errorHandler 1, @errorMessage, @pBankAccountNo
|
|
return
|
|
END
|
|
------------------------------------------------
|
|
BEGIN TRANSACTION
|
|
------------------------------------------------
|
|
---------------------------------------------------------------------
|
|
-- 기존에 등록된 계좌가 없으면, 신규로 추가한다.
|
|
---------------------------------------------------------------------
|
|
--IF ISNULL(@vTemp,'') <> 'Y'
|
|
BEGIN
|
|
INSERT INTO TblCustomerBankVerification
|
|
(
|
|
customerId, certNumber, certLimitCount, bankCode,
|
|
bankAccountNo, idType, idNumber, dob, gender,
|
|
nativeCountry, action, reqDate
|
|
)
|
|
VALUES
|
|
(
|
|
@pCustomerId, @pCertNumber, ISNULL(@vCertLimitCount,3)-1, @pBankCode,
|
|
@pBankAccountNo, @pIdType, @pIdNumber, @pDob, @pGender,
|
|
@pNativeCountry, @pAction, GETDATE()
|
|
)
|
|
|
|
SET @errorMessage = 'Data saved successfully.'
|
|
EXEC proc_errorHandler 0, @errorMessage, @pBankAccountNo
|
|
|
|
UPDATE FastMoneyPro_Account.dbo.billSetting SET PennyCount = ISNULL(PennyCount,0)+1
|
|
|
|
--INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2,sessionID,refrence)
|
|
--SELECT 'system','100241011536','cr',1,@pIdNumber,'Penny Test',@pIdNumber,@pCustomerId
|
|
--union all
|
|
--SELECT 'system','910141047584','dr',1,@pIdNumber,'Penny Test',@pIdNumber,@pCustomerId
|
|
|
|
END
|
|
------------------------------------------------
|
|
COMMIT TRANSACTION
|
|
--DECLARE @vDate date = GETDATE(),@vRemarks varchar(200)
|
|
--SELECT @vRemarks = 'Penny Test : '+ @pBankAccountNo+'('+BankName+') ID:' + @pIdNumber FROM KoreanBankList(NOLOCK) WHERE bankCode = @pBankCode
|
|
|
|
--INSERT INTO @tempTbl(errorcode, msg, id)
|
|
--exec FastMoneyPro_Account.dbo.[spa_saveTempTrn] @flag='i',@sessionID= @pIdNumber,@date=@vDate,@narration = @vRemarks,@company_id=1,@v_type='j',@user='system'
|
|
------------------------------------------------
|
|
END
|
|
ELSE IF @flag = 'TRY'
|
|
BEGIN
|
|
IF EXISTS ( SELECT 'a' from TblCustomerBankVerification(nolock)
|
|
WHERE action = 'REQ'
|
|
AND customerId = @pCustomerId
|
|
AND certNumber = @pCertNumber)
|
|
BEGIN
|
|
------------------------------------------------
|
|
BEGIN TRANSACTION
|
|
------------------------------------------------
|
|
|
|
UPDATE TblCustomerBankVerification
|
|
SET action = 'SUCCESS',
|
|
certDate = GETDATE()
|
|
WHERE customerId = @pCustomerId
|
|
--AND action = 'REQ'
|
|
|
|
------------------------------------------------
|
|
COMMIT TRANSACTION
|
|
------------------------------------------------
|
|
|
|
SET @errorMessage = 'Bank verified successfully.'
|
|
EXEC proc_errorHandler 0, @errorMessage, @pBankAccountNo
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @errorMessage = 'Bank verification failed,Please check the statement and enter the 4 digit number display after GME.'
|
|
EXEC proc_errorHandler 1, @errorMessage, @pBankAccountNo
|
|
END
|
|
END
|
|
ELSE IF @flag = 'SUCCESS'
|
|
BEGIN
|
|
------------------------------------------------
|
|
BEGIN TRANSACTION
|
|
------------------------------------------------
|
|
|
|
UPDATE TblCustomerBankVerification
|
|
SET customerId = @pCustomerId,
|
|
action = 'SUCCESS',
|
|
certDate = GETDATE()
|
|
WHERE bankAccountNo = @pBankAccountNo
|
|
AND action = 'REQ'
|
|
|
|
SET @errorMessage = 'Customer certificate is successfully.'
|
|
EXEC proc_errorHandler 0, @errorMessage, @pBankAccountNo
|
|
|
|
------------------------------------------------
|
|
COMMIT TRANSACTION
|
|
------------------------------------------------
|
|
|
|
END
|
|
ELSE IF @flag = 'CUSTOMER-BANK-INFO'
|
|
BEGIN
|
|
IF EXISTS ( SELECT 1
|
|
FROM TblCustomerBankVerification(nolock)
|
|
WHERE customerId = @pCustomerId
|
|
AND action = 'REQ' )
|
|
BEGIN
|
|
SELECT KBL.BankName, CM.bankAccountNo, CC.certLimitCount
|
|
FROM customerMaster CM (nolock)
|
|
INNER JOIN KoreanBankList KBL (nolock)
|
|
ON CM.bankName = KBL.rowId
|
|
LEFT OUTER JOIN TblCustomerBankVerification CC (nolock)
|
|
ON CM.customerId = CC.customerId
|
|
WHERE CM.customerId = @pCustomerId
|
|
END
|
|
END
|
|
ELSE IF @flag = 'CUSTOMER-INFO'
|
|
BEGIN
|
|
IF @pEmail IS NOT NULL
|
|
SELECT @pCustomerId= customerId FROM customerMaster(NOLOCK) WHERE EMAIL = @pEmail
|
|
SET @vErrorCode = 0
|
|
|
|
|
|
SELECT @vAction = action,
|
|
@vCertLimitCount = certLimitCount
|
|
FROM TblCustomerBankVerification(nolock)
|
|
WHERE customerId = @pCustomerId
|
|
|
|
SELECT @vErrorCode = CASE WHEN @vAction = 'SUCCESS' OR @vCertLimitCount = 0 THEN 1 WHEN @vAction = 'REQ' AND @vCertLimitCount > 0 THEN 10 ELSE 0 END
|
|
|
|
SELECT @vErrorCode AS ErrorCode, @errorMessage as Msg,
|
|
CM.IdType, CM.IdNumber,Dob = FORMAT(CM.dob,'MM/dd/yyyy'), CM.Gender, CM.NativeCountry,
|
|
KBL.BankCode, KBL.BankName, CM.BankAccountNo, CM.CustomerId ,ISNULL(CM.IsKftcOnly,'N') As IsKftcOnly,customerType=CASE WHEN cm.customerType='11051' THEN 'inbound' ELSE 'Individual' END
|
|
FROM customerMaster CM (nolock)
|
|
INNER JOIN KoreanBankList KBL (nolock) ON CM.bankName = KBL.rowId
|
|
where customerId = @pCustomerId
|
|
END
|
|
ELSE IF @flag = 'PENNY-RESULT'
|
|
BEGIN
|
|
SELECT CM.customerId AS [M_CustomerId],
|
|
CM.idType, CM.idNumber, CM.dob, CM.gender, CM.nativeCountry,
|
|
KBL.bankCode, KBL.BankName, CM.bankAccountNo,
|
|
----CASE WHEN GETDATE()>'2018-12-17' THEN '1' ELSE ISNULL(CC.customerId, '0') END AS [C_CustomerId],
|
|
[C_CustomerId] = CASE WHEN CM.createdDate < '2018-12-19 11:00:00' THEN 'N' WHEN ISNULL(CC.action,'') = '' THEN '0' ELSE 'N' END,
|
|
ISNULL(CC.action, '0') AS [Action]
|
|
from customerMaster CM (nolock)
|
|
LEFT OUTER JOIN TblCustomerBankVerification CC (nolock)
|
|
ON CM.customerId = CC.customerId
|
|
INNER JOIN KoreanBankList KBL (nolock)
|
|
ON CM.bankName = KBL.rowId
|
|
WHERE CM.email = @pEmail
|
|
END
|
|
ELSE IF (@flag = 'BANK')
|
|
BEGIN
|
|
SELECT bankCode
|
|
FROM KoreanBankList(nolock)
|
|
WHERE rowId = @pBankCode
|
|
END
|
|
|
|
ELSE IF @flag = 'CUSTOMER-INFO-V4'
|
|
BEGIN
|
|
IF @pEmail IS NOT NULL
|
|
SELECT @pCustomerId= customerId ,@pBankCode=bankName,@pBankAccountNo=bankAccountNo
|
|
FROM customerMaster(NOLOCK) WHERE EMAIL = @pEmail
|
|
SET @vErrorCode = 0
|
|
|
|
|
|
SELECT @vAction = action,
|
|
@vCertLimitCount = certLimitCount
|
|
FROM TblCustomerBankVerification(nolock)
|
|
WHERE customerId = @pCustomerId --AND bankCode=@pBankCode AND bankAccountNo=@pBankAccountNo
|
|
|
|
SELECT @vErrorCode = CASE WHEN @vAction = 'SUCCESS' OR @vCertLimitCount = 0 THEN 1 WHEN @vAction = 'REQ' AND @vCertLimitCount > 0 THEN 10 ELSE 0 END
|
|
|
|
SELECT @vErrorCode AS ErrorCode, @errorMessage as Msg,
|
|
CM.IdType, CM.IdNumber,Dob = FORMAT(CM.dob,'MM/dd/yyyy'), CM.Gender, CM.NativeCountry,
|
|
KBL.BankCode, KBL.BankName, CM.BankAccountNo, CM.CustomerId ,ISNULL(CM.IsKftcOnly,'N') As IsKftcOnly,anotherIDType,anotherIDNumber
|
|
FROM customerMaster CM (nolock)
|
|
INNER JOIN KoreanBankList KBL (nolock) ON CM.bankName = KBL.rowId
|
|
where customerId = @pCustomerId AND KBL.rowId=@pBankCode AND CM.bankAccountNo=@pBankAccountNo
|
|
END
|
|
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT <> 0
|
|
ROLLBACK TRANSACTION;
|
|
|
|
--DECLARE @errorMessage VARCHAR(MAX);
|
|
--SET @errorMessage = ERROR_MESSAGE();
|
|
SET @errorMessage = 'Error Occur while requesting.'
|
|
|
|
EXEC proc_errorHandler 1, @errorMessage, @pCustomerId
|
|
END CATCH
|
|
|