2,756
edits
Report Script: Filter Overdue Letter with specific age that is due: Difference between revisions
Report Script: Filter Overdue Letter with specific age that is due (view source)
Revision as of 09:34, 26 October 2018
, 5 years agono edit summary
No edit summary |
mNo edit summary |
||
| (7 intermediate revisions by the same user not shown) | |||
Line 1:
==Introduction==
A requirement to show '''Overdue Letter''' that is based on specific age range
I decided to write this tutorial to show how we can manipulate
Attempted to apply DataView.RowFilter on the detail table that is in a DataRelation was unsuccessful.<br/>
Therefore, in this tutorial will delete the record in the DataTable that is not in the overdue age range.
Line 8:
*Remove detail record, and show documents overdue age between 30-59.
*Calculate the total sum of amount due.
==Product==
Line 14 ⟶ 13:
Applicable to AutoCount Accounting 2.0 (require to manual modify some code)
{{SourceDownload|link=https://drive.google.com/open?id=11uJzctlWBNC6YQ1Z8rdWgNPgAxZ4N_ZO|remark=(AutoCount Accounting 1.8 / 1.9)|Download Report Template|
[[File:ReportScript.OverdueLetter.png|link=]]<br />
Line 24 ⟶ 23:
===Add function of FilterDueAge===
'''FilterDueAge''' removes detail table record that is not in the filter range of '''Age'''.<br/>
And remove debtor that has no overdue document that is shown as highlighted in following code.
<syntaxhighlight lang="csharp">▼
<syntaxhighlight lang="csharp" highlight="15">
private void FilterDueAge(int fromAge, int toAge)
{
Line 85 ⟶ 86:
</syntaxhighlight>
<br/>
===Add/Replace a label to show the letter overdue message with ''Total Amount Due''===
[[File:ReportScript.OverdueLetter2.png|link=]]
<br/><br/>
#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===
Apply a method '''Compute(...)''' of DataTable object is used to calculate the sum of AmountDue.<br/>
*Add a local variable '''myOverdueMsg'''
<syntaxhighlight lang="csharp">
private string myOverdueMsg = null;
</syntaxhighlight>
*Insert script to '''xrLabelOverdueTitle_BeforePrint''' event
▲<syntaxhighlight lang="csharp">
private void xrLabelOverdueTitle_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
object oAccNo = GetCurrentColumnValue("AccNo");
if (oAccNo == null)
{
(sender as XRLabel).Text = "";
return;
}
//Store the original text of this label
if (myOverdueMsg == null)
{
myOverdueMsg = (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(myOverdueMsg, __report.DecimalSetting.FormatCurrency(totalAmtDue));
}
</syntaxhighlight>
| |||