Report Script: Filter Overdue Letter with specific age that is due: Difference between revisions
No edit summary |
No edit summary |
||
Line 1:
==Introduction==
A requirement to show '''Overdue Letter''' that is based on specific age range in was brought to my attention.
I decided to write this tutorial to show how we can manipulate the DataTable in the report.<br/><br/>
Attempted to apply DataView.RowFilter on the detail table that is in a DataRelation was unsuccessful.
|
Revision as of 10:04, 24 October 2018
Introduction
A requirement to show Overdue Letter that is based on specific age range in was brought to my attention.
I decided to write this tutorial to show how we can manipulate the DataTable in the report.
Attempted to apply DataView.RowFilter on the detail table that is in a DataRelation was unsuccessful.
Therefore, in this tutorial will delete the record in the DataTable that is not in the overdue age range.
Task in this tutorial
- Remove detail record, and show documents overdue age between 30-59.
- Calculate the total sum of amount due.
Product
AutoCount Accounting 1.8 / 1.9
Applicable to AutoCount Accounting 2.0 (require to manual modify some code)
[ Download Report Template] (AutoCount Accounting 1.8 / 1.9)
Coming soon... |
Report Script
Add using directive
- Add directive at the top of Scripts, if has not already added.
- using System.Data;
Add function of FilterDueAge
FilterDueAge removes detail table record that is not in the filter range of Age.
private void FilterDueAge(int fromAge, int toAge)
{
//Get table of documents that are due (Detail table)
DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"];
//Find document record that the age is not in the age range
DataRow[] rows = dtDetail.Select(string.Format("Age < {0} OR Age > {1}", fromAge, toAge));
//Delete document record
foreach (DataRow row in rows)
{ row.Delete(); }
//Above execution may render to debtor has no overdue document,
//below function is to remove debtor record that has no overdue document.
RemoveEmptyMaster();
}
Add function of RemoveEmptyMaster
RemoveEmptyMaster function removes master record that has no Overdue document in detail.
private void RemoveEmptyMaster()
{
//Get Master and Detail tables
DataTable dtMaster = (__report.DataSource as DataSet).Tables["Master"];
DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"];
string debtorCode;
//Delete master record that has no detail (overdue document)
foreach (DataRow rowM in dtMaster.Rows)
{
debtorCode = rowM["AccNo"].ToString();
//Find DebtorCode in Detail Table
DataRow[] selectRows = dtDetail.Select(string.Format("DebtorCode='{0}'", debtorCode));
if (selectRows.Length == 0)
{
rowM.Delete();
}
}
}
Add function call in Report_BeforePrint event
Before this report is processed, add the function call in the event that is triggered at the beginning.
- Assign value of from age and to age for the function
- If the report is for overdue age that is greater than 60, assign value of 60 and 999.
private void Report_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
__report = Report as BCE.AutoCount.Report.BaseReport;
//Filter document that the age is between 30 and 59.
//The result of Overdue Letter will show document that the due age is 30 to 59.
FilterDueAge(30, 59);
}
Add/Replace a label to show the letter overdue message with Total Amount Due
- Add/Replace a label, and amend the (Name) to "xrLabelOverdueTitle"
- Type in the text "Our record shows that an amount of {0} is now overdue. A list of overdue invoices is shown as below:"
- {0} is a parameter that will be replaced with the value of Total Amount Due in string.Format(...).
- In Property Grid, Click [+] button of Scripts to reveal the events
- Find Before Print, and click into the text box on the right
- Then click the arrow button, and click (New)
- Report Designer will switch to Scripts editor,
- and xrLabelOverdueTitle_BeforePrint event is created.
Calculate Total Overdue Amount after records in detail table is updated
This sample code, a method Compute(...) of DataTable object is used to calculate the sum of AmountDue.
private void xrLabelOverdueTitle_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
object oAccNo = GetCurrentColumnValue("AccNo");
if (oAccNo == null) return;
//Temporary store the original text of this label
string orgText = (sender as XRLabel).Text;
//Calculate the current debtor total overdue amount
object oTotalAmtDue = (__report.DataSource as DataSet).Tables["Detail"]
.Compute("SUM(AmountDue)", string.Format("DebtorCode='{0}'", oAccNo.ToString()));
decimal totalAmtDue = oTotalAmtDue == null ? 0 : BCE.Data.Convert.ToDecimal(oTotalAmtDue);
//Assign 'total amount due' to this label, while format the decimal digit with currency formatting
(sender as XRLabel).Text = string.Format(orgText, __report.DecimalSetting.FormatCurrency(totalAmtDue));
}
Go to top
|
Resources For AutoCount Software Developers
|