Report Script: Create a Packing List in report script: Difference between revisions
Content added Content deleted
mNo edit summary |
|||
(10 intermediate revisions by one other user not shown) | |||
Line 1: | Line 1: | ||
==Introduction== |
==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/> |
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 area and customer. |
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== |
==Product== |
||
AutoCount Accounting 1.8 / 1.9<br/> |
AutoCount Accounting 1.8 / 1.9<br/> |
||
Applicable to AutoCount Accounting 2.0 |
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 |
{{SourceDownload|link=https://drive.google.com/open?id=11uJzctlWBNC6YQ1Z8rdWgNPgAxZ4N_ZO|remark=(AutoCount Accounting 1.8 / 1.9)|Download Report Template|Packing in Delivery Detail Listing.}} |
||
[[File:Prog PickList.PackUOM.png|link=]]<br /> |
[[File:Prog PickList.PackUOM.png|link=]]<br /> |
||
{| class="wikitable" |
|||
!colspan=2|UOM Table |
|||
|- |
|||
! UOM !! Rate |
|||
|- |
|||
| UNIT || 1 |
|||
|- |
|||
| BOX || 2 |
|||
|- |
|||
| CTN || 6 |
|||
|} |
|||
==Report Script== |
==Report Script== |
||
Show the packing from any UOM |
|||
===Open Report Designer and Load Report Template=== |
|||
#At main window, go to Sales | Delivery Order |
#At main window, go to Sales | Delivery Order |
||
#On the main menu, click Report | Design Detail Listing Report |
#On the main menu, click Report | Design Detail Listing Report |
||
#Select "Picking List - Delivery Order" report template |
#Select "Picking List - Delivery Order" report template<br/> |
||
===Add Calculated Field=== |
|||
#Insert a '''Calculated Field''' |
#Insert a '''Calculated Field''' |
||
#:While the view is at '''Designer''', |
#:While the view is at '''Designer''', |
||
Line 22: | Line 39: | ||
#:Locate & Select the newly added calculated field |
#:Locate & Select the newly added calculated field |
||
#:In '''Property Grid''', rename '''(Name)''' to '''calcPacking''' |
#:In '''Property Grid''', rename '''(Name)''' to '''calcPacking''' |
||
===Add Script Event=== |
|||
#Create new event of '''GetValue''' to '''calcPacking''' |
#Create new event of '''GetValue''' to '''calcPacking''' |
||
#:Click [+] to expand '''Scripts''' |
#:Click [+] to expand '''Scripts''' |
||
# |
#Click the box on the right of "Get a Value" |
||
#:Then click the arrow button, and click '''(New)''' |
#:Then click the arrow button, and click '''(New)''' |
||
# |
#Report Designer will switch to Scripts editor, |
||
#:and '''calcPacking_GetValue''' event is created.<br/><br/> |
#:and '''calcPacking_GetValue''' event is created.<br/><br/> |
||
===Add using directive=== |
|||
#Add directive at the top of '''Scripts''', if has not already added. |
#Add directive at the top of '''Scripts''', if has not already added. |
||
#*'''using System.Data;''' |
#*'''using System.Data;''' |
||
#*'''using System.Linq;''' |
#*'''using System.Linq;''' |
||
===Insert scripts into '''calcPacking_GetValue'''=== |
|||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
//Main Entry |
//Main Entry |
||
private void calcPacking_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e) |
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")); |
DataTable tblUOM = GetUOMFromItem(GetCurrentColumnValue("ItemCode")); |
||
if (tblUOM == null) |
if (tblUOM == null) |
||
return; |
return; |
||
// |
//Calculate the quantity of smallest UOM that has 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 smallestQty = GetSmallestQty(GetCurrentColumnValue("ItemCode"), GetCurrentColumnValue("UOMRate")); |
||
decimal uomRate; |
decimal uomRate; |
||
string result = ""; |
string result = ""; |
||
//Loop all UOMs in the UOM Table |
|||
foreach (DataRow row in tblUOM.Rows) |
foreach (DataRow row in tblUOM.Rows) |
||
{ |
{ |
||
Line 57: | Line 74: | ||
break; |
break; |
||
//Get the rate of current UOM in tblUOM (DataTable) |
|||
uomRate = BCE.Data.Convert.ToDecimal(row["Rate"]); |
uomRate = BCE.Data.Convert.ToDecimal(row["Rate"]); |
||
//Store each formatted UOM Packing |
|||
result = FormatCurrentUomResult(result, |
result = FormatCurrentUomResult(result, |
||
uomRate > 0 ? Math.Floor(smallestQty / uomRate) : 0, |
uomRate > 0 ? Math.Floor(smallestQty / uomRate) : 0, |
||
Line 65: | Line 85: | ||
smallestQty = smallestQty % uomRate; |
smallestQty = smallestQty % uomRate; |
||
} |
} |
||
//Assign the "Packing" to calcPacking |
|||
e.Value = result; |
e.Value = result; |
||
} |
} |
||
</syntaxhighlight> |
</syntaxhighlight> |
||
⚫ | |||
⚫ | |||
⚫ | |||
====Function of GetSmallestQty==== |
|||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
//ItemCode and UOMRate are the grouping in this report |
//ItemCode and UOMRate are the grouping in this report |
||
private decimal GetSmallestQty(object oItemCode, object oUomRate) |
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) |
|||
⚫ | |||
) |
|||
.GroupBy(r => new { |
|||
ItemCode = r.Field<string>("ItemCode"), |
|||
UOMRate = r.Field<decimal>("UOMRate") |
|||
}) |
|||
⚫ | |||
.FirstOrDefault(); //Return first record (expected), or 0 (if no result). |
|||
} |
} |
||
</syntaxhighlight> |
</syntaxhighlight> |
||
====Function of GetUOMFromItem==== |
|||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
//Get the table of available UOM of one Item |
//Get the table of available UOM of one Item |
||
Line 98: | Line 120: | ||
} |
} |
||
</syntaxhighlight> |
</syntaxhighlight> |
||
====Function of FormatCurrentUomResult==== |
|||
<syntaxhighlight lang="csharp"> |
<syntaxhighlight lang="csharp"> |
||
private string FormatCurrentUomResult(string result, decimal qty, string uom) |
private string FormatCurrentUomResult(string result, decimal qty, string uom) |
||
Line 109: | Line 132: | ||
} |
} |
||
</syntaxhighlight> |
</syntaxhighlight> |
||
<br/> |
|||
===Add label to display "Packing"=== |
|||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
<br/> |
<br/> |
||
Line 114: | Line 144: | ||
[[Category:Tutorials]] |
[[Category:Tutorials]] |
||
[[Category:ReportScript]] |
[[Category:ReportScript]] |
||
{{NavigateDeveloper}} |
{{NavigateDeveloper}} |
Latest revision as of 07:43, 14 April 2020
Introduction
This tutorial shows how to add a text which displays packing that is based on largest UOM to smallest UOM with report scripting.
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
Applicable to AutoCount Accounting 2.0 (require to manual modify some code)
Download Report Template (AutoCount Accounting 1.8 / 1.9)
Packing in Delivery Detail Listing. |
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
Add Calculated Field
- Insert a Calculated Field
- While the view is at Designer,
- Right click 1 Main Data: Delivery Order Master (aka. Master table)
- and Add Calculated Field
- Set the property of newly added Calculated Field
- 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.
- and calcPacking_GetValue event is created.
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
//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 of smallest UOM that has 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 <= 0)
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,
row["UOM"].ToString());
//Calculate the remaining Smallest Qty for next UOM
smallestQty = smallestQty % uomRate;
}
//Assign the "Packing" to calcPacking
e.Value = result;
}
- Add function of GetSmallestQty
- Add function of GetUOMFromItem
- Add function of FormatCurrentUomResult
Function of GetSmallestQty
//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)
).Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate"));
}
Function of GetUOMFromItem
//Get the table of available UOM of one Item
private DataTable GetUOMFromItem(object oItemCode)
{
if (oItemCode == null)
return null;
return __report.DBSetting.GetDataTable(
"SELECT UOM, Rate FROM ItemUOM WHERE ItemCode=? ORDER BY Rate DESC", false, oItemCode.ToString());
}
Function of FormatCurrentUomResult
private string FormatCurrentUomResult(string result, decimal qty, string uom)
{
//Format the display text of qty and uom
if (qty == 0)
return result;
string formatQtyUom = string.Format("{0} {1}", qty, uom);
return string.IsNullOrEmpty(result) ? formatQtyUom
: string.Format("{0}, {1}", result, formatQtyUom);
}
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
Go to top
|
Resources For AutoCount Software Developers
|