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.
118 lines
4.4 KiB
118 lines
4.4 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_CustomerTxnStatement] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
DROP PROCEDURE [dbo].[proc_CustomerTxnStatement]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_CustomerTxnStatement] Script Date: 7/4/2019 11:35:48 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE PROC [dbo].[proc_CustomerTxnStatement]
|
|
@fromDate VARCHAR(10) = null,
|
|
@toDate VARCHAR(20) = null,
|
|
@IdNumber VARCHAR(20) = null,
|
|
@User VARCHAR(50) ,
|
|
@flag VARCHAR(10) = null,
|
|
@chargeAmt MONEY = NULL,
|
|
@refundAmt MONEY = NULL
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
IF @flag IS NULL
|
|
BEGIN
|
|
DECLARE @customerName VARCHAR(100), @customerId BIGINT
|
|
|
|
SELECT @customerName = firstName, @customerId = customerId FROM customermaster (NOLOCK) WHERE REPLACE(idnumber, '-', '') = REPLACE(@IdNumber, '-', '')
|
|
|
|
SELECT senderName = ISNULL(@customerName, r.senderName)
|
|
,s.idNumber,createdDate = CONVERT(VARCHAR,r.createdDate,111)
|
|
,controlNo = dbo.FNADecryptString(r.controlNo),r.receiverName,r.cAmt,r.pAmt,R.payoutCurr
|
|
FROM remitTran r(NOLOCK)
|
|
INNER JOIN tranSenders s (NOLOCK) ON s.tranId = r.id
|
|
WHERE s.customerId = @customerId
|
|
AND r.transtatus <> 'Cancel'
|
|
AND r.approvedDate BETWEEN @fromDate AND @toDate + ' 23:59:59'
|
|
ORDER BY CONVERT(VARCHAR,r.createdDate,111)
|
|
END
|
|
ELSE IF @flag = 'search'
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM customerMaster (NOLOCK) WHERE walletAccountNo = @IdNumber)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid customer requested for refund', NULL
|
|
RETURN
|
|
END
|
|
|
|
SELECT errorCode = 0, customerName = firstName, idNumber, availableBalance = ISNULL(availableBalance, 0)
|
|
FROM customerMaster (NOLOCK) WHERE walletAccountNo = @IdNumber
|
|
END
|
|
ELSE IF @flag = 'refund'
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM customerMaster (NOLOCK) WHERE walletAccountNo = @IdNumber)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid customer requested for refund', NULL
|
|
RETURN
|
|
END
|
|
declare @availableBalance money,@rowId int,@Narration varchar(max)
|
|
SELECT @availableBalance = ISNULL(availableBalance, 0)
|
|
,@Narration='being amount refunded to primary ac : '+ walletAccountNo+' / '+bankAccountNo
|
|
FROM customerMaster (NOLOCK) WHERE walletAccountNo = @IdNumber
|
|
|
|
if not exists(select 'a' from TblVirtualBankDepositDetail(nolock) where virtualAccountNo = @IdNumber)
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Balance not found for refund', NULL
|
|
RETURN
|
|
END
|
|
IF ISNULL(@refundAmt,0) <= 0
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid amount requested for refund', NULL
|
|
RETURN
|
|
END
|
|
IF ISNULL(@availableBalance,0) - ISNULL(@refundAmt,0)< 0
|
|
BEGIN
|
|
EXEC proc_errorHandler 1, 'Invalid amount requested for refund', NULL
|
|
RETURN
|
|
END
|
|
|
|
DELETE FROM FastMoneyPro_Account.dbo.temp_tran WHERE SESSIONID = @IdNumber
|
|
|
|
BEGIN TRANSACTION
|
|
insert into TblVirtualBankDepositDetail(processId,obpId,customerName,virtualAccountNo,amount,receivedOn,partnerServiceKey
|
|
,institution,depositor,no,logDate)
|
|
select top 1 0,obpId,customerName,virtualAccountNo,- @refundAmt,getdate(),'000'
|
|
,institution,'Refund Deposit',no,getdate() from TblVirtualBankDepositDetail (nolock)
|
|
where virtualAccountNo= @IdNumber
|
|
|
|
SET @rowId = @@IDENTITY
|
|
|
|
UPDATE customerMaster SET availableBalance=availableBalance- @refundAmt WHERE walletAccountNo = @IdNumber
|
|
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'100241011536','cr',(@refundAmt - ISNULL(@chargeAmt,0)),@IdNumber,'Refund Deposit',@IdNumber,@rowId union all
|
|
SELECT @User,@IdNumber,'dr',@refundAmt,@IdNumber,'Refund Deposit',@IdNumber,@rowId
|
|
IF ISNULL(@chargeAmt,0) >0
|
|
BEGIN
|
|
INSERT INTO FastMoneyPro_Account.dbo.temp_tran(entry_user_id,acct_num,part_tran_type,tran_amt,field1,field2
|
|
,sessionID,refrence)
|
|
SELECT @User,'910141097092','cr',ISNULL(@chargeAmt,0),@IdNumber,'Refund Deposit',@IdNumber,@rowId
|
|
END
|
|
|
|
|
|
COMMIT TRANSACTION
|
|
DECLARE @vVoucherType CHAR(1) = 'Y'
|
|
SELECT @fromDate = convert(varchar,getdate(),101)
|
|
|
|
SET @vVoucherType = CASE WHEN YEAR(@fromDate) = 2018 THEN 'J' ELSE @vVoucherType END
|
|
|
|
exec FastMoneyPro_Account.dbo.[spa_saveTempTrn] @flag='i',@sessionID= @IdNumber,@date=@fromDate,@narration=@Narration,@company_id=1,@v_type= @vVoucherType,@user=@user
|
|
END
|
|
ELSE IF @FLAG = 'AcStmt' ----Customer Account Statement
|
|
BEGIN
|
|
SELECT @customerName = EMAIL FROM customermaster (NOLOCK) WHERE REPLACE(idnumber, '-', '') = REPLACE(@IdNumber, '-', '')
|
|
EXEC [mobile_proc_WalletStatement] @UserID = @customerName,@startDate=@fromDate,@endDate=@toDate
|
|
END
|
|
GO
|