AutoCount Resource Center

Report Script: Create a Packing List in report script: Difference between revisions

Page Discussion

Report Script: Create a Packing List in report script (view source)

Revision as of 07:43, 14 April 2020

878 bytes added ,  4 years ago
→‎Introduction
No edit summary
 
(17 intermediate revisions by one other user not shown)
Line 1:
==Introduction==
This tutorial shows how to add a text which displays packing that is based on largest UOM to smallest UOM with report scripting.<br/>
Does not take into consideration of multi-location, area and customer.
 
==Tasks in this tutorial==
*Add Calculate Field in Report
*Construct a text that join UOMs
 
==Product==
AutoCount Accounting 1.8 / 1.9<br/>
Applicable to AutoCount Accounting 2.0 (require to manual modify some code)
 
{{SourceDownload|link=https://drive.google.com/open?id=1u10Aq8PnC4hYMbOYs_uMnXb3U_i4xrNq11uJzctlWBNC6YQ1Z8rdWgNPgAxZ4N_ZO|remark=(AutoCount Accounting 21.08 / 1.9)|Download Report Template|GetPacking Fieldin thatDelivery is not inDetail reportListing.}}
[[File:Prog PickList.PackUOM.png|link=]]<br />
{| class="wikitable"
!colspan=2|UOM Table
|-
! UOM !! Rate
|-
| UNIT || 1
|-
| BOX || 2
|-
| CTN || 6
|}
 
==Report Script==
===Show the packing from any UOM===
===Open Report Designer and Load Report Template===
#At main window, go to Sales | Delivery Order
#On the main menu, click Report | Design Detail Listing Report
#Select "Picking List - Delivery Order" report template<br/><br/>
===Add Calculated Field===
#Insert a '''Calculated Field'''
#:While the view is at '''Designer''',
Line 20 ⟶ 39:
#:Locate & Select the newly added calculated field
#:In '''Property Grid''', rename '''(Name)''' to '''calcPacking'''
===Add Script Event===
#Create new event of '''GetValue''' to '''calcPacking'''
#:Click [+] to expand '''Scripts'''
#:Click the box on the right of "Get a Value"
#:Then click the arrow button, and click '''(New)'''
#:Report Designer will switch to Scripts editor,
#:and '''calcPacking_GetValue''' event is created.<br/><br/>
===Add using directive===
#Add directive at the top of '''Scripts''', if has not already added.
#*'''using System.Data;'''
#*'''using System.Linq;'''
 
#===Insert scripts into '''calcPacking_GetValue'''===
#Add function of '''GetSmallestQty'''▼
#Add function of '''GetUOMFromItem'''▼
#Add function of '''FormatCurrentUomResult'''<br/><br/>▼
#Add a new Label onto Detail band of Detail Report
which the DataMember is "AR Invoice Master.AR Invoice Detail"
Bind the calcProjDesc to this new label▼
Drag calcProjDesc from AR Invoice Detail (table) in Field List onto the designer
Click Preview tab to see the result▼
<syntaxhighlight lang="csharp">
//Main Entry
private void calcPacking_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
//Get all UOM of current Item, and store in DataTable
DataTable tblUOM = GetUOMFromItem(GetCurrentColumnValue("ItemCode"));
if (tblUOM == null)
return;
 
//Calculate the quantity //Get totalof smallest UOM Qtythat ofhas the same "ItemCode" and "UOMRate "
//It is based on the design of this report that uses "Group Header" to group "ItemCode" and "UOMRate"
decimal smallestQty = GetSmallestQty(GetCurrentColumnValue("ItemCode"), GetCurrentColumnValue("UOMRate"));
 
decimal uomRate;
string result = "";
 
//Loop all UOMs in the UOM Table
foreach (DataRow row in tblUOM.Rows)
{
if (smallestQty <= 10)
break;
 
//Get the rate of current UOM in tblUOM (DataTable)
uomRate = BCE.Data.Convert.ToDecimal(row["Rate"]);
 
//Store each formatted UOM Packing
result = FormatCurrentUomResult(result,
uomRate > 0 ? Math.Floor(smallestQty / uomRate) : 0,
Line 63 ⟶ 85:
smallestQty = smallestQty % uomRate;
}
 
//Assign the "Packing" to calcPacking
e.Value = result;
}
</syntaxhighlight>
 
▲#Add function of '''GetSmallestQty'''
▲#Add function of '''GetUOMFromItem'''
▲#Add function of '''FormatCurrentUomResult'''<br/><br/>
====Function of GetSmallestQty====
<syntaxhighlight lang="csharp">
//ItemCode and UOMRate are the grouping in this report
private decimal GetSmallestQty(object oItemCode, object oUomRate)
{
if (oItemCode == null)
return 0;
 
return (Report.DataSource as DataSet).Tables["Master"].AsEnumerable()
.Where(r =>
r.Field<string>("ItemCode") == oItemCode.ToString() &&
r.Field<decimal>("UOMRate") == BCE.Data.Convert.ToDecimal(oUomRate)
.Select(g => g ).Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate")));▼
)
.GroupBy(r => new {
ItemCode = r.Field<string>("ItemCode"),
UOMRate = r.Field<decimal>("UOMRate")
})
▲ .Select(g => g.Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate")))
.FirstOrDefault();
}
</syntaxhighlight>
====Function of GetUOMFromItem====
<syntaxhighlight lang="csharp">
//Get the table of available UOM of one Item
private DataTable GetUOMFromItem(object oItemCode)
{
Line 95 ⟶ 120:
}
</syntaxhighlight>
====Function of FormatCurrentUomResult====
<syntaxhighlight lang="csharp">
private string FormatCurrentUomResult(string result, decimal qty, string uom)
Line 106 ⟶ 132:
}
</syntaxhighlight>
 
<br/>
===Add label to display "Packing"===
#Add a new Label into existing Group Header
▲#:Bind the calcProjDesc to this new label
#:Drag calcPacking from '''1 Main Data: Delivery Order Master''' in Field List onto the new label
▲#Click Preview tab to see the result
 
<br/>
Line 111 ⟶ 144:
[[Category:Tutorials]]
[[Category:ReportScript]]
 
{{NavigateDeveloper}}
Jae Sen
authors, Administrators
1,155

edits

Retrieved from "https://wiki.autocountsoft.com/wiki/Special:MobileDiff/9694...14690"