hi there, here i am going to create duplicate of bankaccountstatement report, which has the requirement of like this
In this description should come from customer payment journal and the credit and debit values should be from bankAccountTrans.AmountCur in which -ve and +ve values get divided into credit and the debit. Balance is the current balance here, in that credit - Prev banalce = current balance and debit + Prevbalance = current balance should be there, i have store current balance value as sthe Prevbalance in the code,
but i am unable to pick up the values, please help me out. here is my code, pls give some suggestions.
[
SRSReportQueryAttribute(queryStr(TSBankAccountStatementNew)),
SRSReportParameterAttribute(classstr(TSBankAccountStatementNewContract))
]
class TSBankAccountStatementNewDP extends SrsReportDataProviderPreProcessTempDB
{
AmountCur currentBalance;
TransDate fromDate;
TransDate toDate;
CompanyBankAccount bankAccount;
TSBankAccountStatementTmpNew bankAccountStatementTmp;
AmountCur PrevBalance;
//real currentBalance;
/// <summary>
/// Calculates the opening balance from fromDate.
/// </summary>
/// <returns>
/// The opening balance.
/// </returns>
private AmountCur calculateOpeningBalanceForBankAccount()
{
BankAccountTrans bankAccountTrans;
select sum(AmountCur), sum(AmountCorrect) from bankAccountTrans
where bankAccountTrans.AccountId == bankAccount
&& bankAccountTrans.TransDate < fromDate;
return bankAccountTrans.AmountCur + bankAccountTrans.AmountCorrect;
}
/// <summary>
/// Adds the records to the <c>BankAccountStatementTmp</c> table.
/// </summary>
private void createTSBankAccountStatementTmpNew()
{
BankAccountTable bankAccountTable;
BankAccountTrans bankAccountTrans;
LedgerJournalTrans ledgerJournalTrans;
insert_recordset bankAccountStatementTmp
(
FromDate,
ToDate,
CurrentBalance,
BankTransType,
TransDate,
Voucher,
DepositNum,
PaymReference,
PaymentMode,
AmountCur,
AmountCorrect,
AmountMST,
AmountMSTSecond,
AccountID,
Name,
CurrencyCode
//TSDescription
)
select
fromDate,
toDate,
currentBalance,
BankTransType,
TransDate,
Voucher,
DepositNum,
PaymReference,
PaymentMode,
AmountCur,
AmountCorrect,
AmountMST,
AmountReportingCurrency
from bankAccountTrans
where bankAccountTrans.AccountId == bankAccount
&& bankAccountTrans.TransDate >= fromDate
&& bankAccountTrans.TransDate <= toDate
join
AccountID,
Name,
CurrencyCode
from bankAccountTable
where bankAccountTable.AccountID == bankAccount;
// join
// Txt from ledgerJournalTrans
// where //bankAccountStatementTmp.TSDescription == ledgerJournalTrans.Txt;
//LedgerJournalTrans.PaymentAccount == bankAccount;
//select * from ledgerJournalTable where LedgerJournalTable.BankAccountId == BankAccountTable.AccountID;
//bankAccountStatementTmp.TSDescription = ledgerJournalTable.Name;
//bankAccountStatementTmp.insert();
select count(RecId) from bankAccountStatementTmp;
if (bankAccountStatementTmp.RecId == 0)
{
bankAccountTable = BankAccountTable::find(bankAccount);
bankAccountStatementTmp.AccountID = bankAccountTable.AccountID;
bankAccountStatementTmp.Name = bankAccountTable.Name;
bankAccountStatementTmp.CurrencyCode = bankAccountTable.CurrencyCode;
bankAccountStatementTmp.CurrentBalance = currentBalance;
bankAccountStatementTmp.insert();
}
else
{
if(bankAccountStatementTmp)
{
ttsbegin;
while select forupdate bankAccountStatementTmp
{
select * from bankAccountTrans where BankAccountTrans.AccountId == BankAccountTable.AccountID;
if(bankAccountStatementTmp.AmountCur>=0)
{
bankAccountStatementTmp.TSDebit = bankAccountTrans.AmountCur;
}
else
{
bankAccountStatementTmp.TSCredit = bankAccountTrans.AmountCur;
}
}
PrevBalance = bankAccountStatementTmp.TSCorrectedAmountCur;
if(bankAccountStatementTmp.TSCredit)
{
bankAccountStatementTmp.TSCorrectedAmountCur = PrevBalance - bankAccountStatementTmp.TSCredit;
}
else
{
bankAccountStatementTmp.TSCorrectedAmountCur = PrevBalance + bankAccountStatementTmp.TSDebit;
}
}
}
}
}
/// <summary>
/// Retrieves the temporary table for SQL Server Reporting Services reports.
/// </summary>
/// <returns>
/// The <c>BankAccountStatementTmp</c> temporary table.
/// </returns>
[SrsReportDataSetAttribute(tablestr(TSBankAccountStatementTmpNew))]
public TSBankAccountStatementTmpNew getTSBankAccountStatementTmpNew()
{
select bankAccountStatementTmp;
return bankAccountStatementTmp;
}
/// <summary>
/// Processes the business logic that is used to populate a <c>BankAccountStatementTmp</c> temporary
/// table.
/// </summary>
public void processReport()
{
TSBankAccountStatementNewContract contract = this.parmDataContract() as TSBankAccountStatementNewContract;
fromDate = contract.parmFromDate();
toDate = contract.parmToDate() ? contract.parmToDate() : dateMax();
bankAccount = contract.parmBankAccount();
currentBalance = this.calculateOpeningBalanceForBankAccount();
this.createTSBankAccountStatementTmpNew();
}
}
