Report Script: Filter Overdue Letter with specific age that is due: Difference between revisions
(Created page with "==Introduction== ==Task in this tutorial== * ==Product== AutoCount Accounting 1.8 / 1.9<br/> Applicable to AutoCount Accounting 2.0 (require to manual modify some code) {{...") |
No edit summary |
||
Line 1: | Line 1: | ||
==Introduction== |
==Introduction== |
||
A requirement to show '''Overdue Letter''' that is 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. |
|||
Therefore, in this tutorial will delete the record in the DataTable that is not in the overdue age range. |
|||
==Task in this tutorial== |
==Task in this tutorial== |
||
*Remove detail record, and show documents overdue age between 30-59. |
|||
* |
|||
*Calculate the total sum of amount due. |
|||
Line 10: | Line 15: | ||
{{SourceDownload|link=|remark=(AutoCount Accounting 1.8 / 1.9)|Download Report Template|Coming soon...}} |
{{SourceDownload|link=|remark=(AutoCount Accounting 1.8 / 1.9)|Download Report Template|Coming soon...}} |
||
[[File: |
[[File:ReportScript.OverdueLetter.png|link=]]<br /> |
||
==Report Script== |
==Report Script== |
||
Line 19: | Line 24: | ||
===Add function of FilterDueAge=== |
===Add function of FilterDueAge=== |
||
'''FilterDueAge''' removes detail table record that is not in the filter range of '''Age'''. |
|||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
private void FilterDueAge(int fromAge, int toAge) |
private void FilterDueAge(int fromAge, int toAge) |
||
Line 40: | Line 45: | ||
===Add function of RemoveEmptyMaster=== |
===Add function of RemoveEmptyMaster=== |
||
'''RemoveEmptyMaster''' function removes master record that has no '''Overdue''' document in detail. |
|||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
private void RemoveEmptyMaster() |
private void RemoveEmptyMaster() |
||
{ |
{ |
||
//Get Master and Detail tables |
|||
DataTable dtMaster = (__report.DataSource as DataSet).Tables["Master"]; |
DataTable dtMaster = (__report.DataSource as DataSet).Tables["Master"]; |
||
DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"]; |
DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"]; |
||
string debtorCode; |
string debtorCode; |
||
//Delete master record that has no detail (overdue document) |
|||
foreach (DataRow rowM in dtMaster.Rows) |
foreach (DataRow rowM in dtMaster.Rows) |
||
{ |
{ |
||
debtorCode = rowM["AccNo"].ToString(); |
debtorCode = rowM["AccNo"].ToString(); |
||
//Find DebtorCode in Detail Table |
|||
DataRow[] selectRows = dtDetail.Select(string.Format("DebtorCode='{0}'", debtorCode)); |
DataRow[] selectRows = dtDetail.Select(string.Format("DebtorCode='{0}'", debtorCode)); |
||
if (selectRows.Length == 0) |
if (selectRows.Length == 0) |
||
Line 59: | Line 70: | ||
</syntaxhighlight> |
</syntaxhighlight> |
||
===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. |
|||
<syntaxhighlight lang="csharp" highlight="7"> |
|||
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); |
|||
} |
|||
</syntaxhighlight> |
|||
Revision as of 09:36, 24 October 2018
Introduction
A requirement to show Overdue Letter that is 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);
}
Go to top
|
Resources For AutoCount Software Developers
|