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

From AutoCount Resource Center
Jump to navigation Jump to search
m
 
(9 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 2.0)|Download Report Template|Packing in Delivery Detail Listing.}}
+
{{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"
 
{| class="wikitable"
Line 20: Line 24:
 
| CTN || 6
 
| CTN || 6
 
|}
 
|}
 
  
 
==Report Script==
 
==Report Script==
===Show the packing from any UOM===
+
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<br/><br/>
+
#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 34: 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"
+
#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,
+
#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'''
+
 
#Add function of '''GetSmallestQty'''
+
===Insert scripts into '''calcPacking_GetValue'''===
#Add function of '''GetUOMFromItem'''
 
#Add function of '''FormatCurrentUomResult'''<br/><br/>
 
#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
 
 
<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;
  
//Get total smallest UOM Qty of same ItemCode and UOMRate  
+
//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 69: 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 77: Line 85:
 
smallestQty = smallestQty % uomRate;
 
smallestQty = smallestQty % uomRate;
 
}
 
}
 +
 +
//Assign the "Packing" to calcPacking
 
e.Value = result;
 
e.Value = result;
 
}
 
}
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 +
#Add function of '''GetSmallestQty'''
 +
#Add function of '''GetUOMFromItem'''
 +
#Add function of '''FormatCurrentUomResult'''
 +
====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)
+
    if (oItemCode == null)
return 0;
+
        return 0;
  
return (Report.DataSource as DataSet).Tables["Master"].AsEnumerable()
+
    return (Report.DataSource as DataSet).Tables["Master"].AsEnumerable()
.Where(r =>
+
        .Where(r =>
r.Field<string>("ItemCode") == oItemCode.ToString() &&
+
            r.Field<string>("ItemCode") == oItemCode.ToString() &&
r.Field<decimal>("UOMRate") == BCE.Data.Convert.ToDecimal(oUomRate)
+
            r.Field<decimal>("UOMRate") == BCE.Data.Convert.ToDecimal(oUomRate)
)
+
        ).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(); //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 110: 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 121: Line 132:
 
}
 
}
 
</syntaxhighlight>
 
</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/>
 
<br/>
Line 126: 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)

VSProjectIcon.png Download Report Template (AutoCount Accounting 1.8 / 1.9)

Packing in Delivery Detail Listing.

Prog PickList.PackUOM.png

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

  1. At main window, go to Sales | Delivery Order
  2. On the main menu, click Report | Design Detail Listing Report
  3. Select "Picking List - Delivery Order" report template

Add Calculated Field

  1. 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
  2. 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

  1. Create new event of GetValue to calcPacking
    Click [+] to expand Scripts
  2. Click the box on the right of "Get a Value"
    Then click the arrow button, and click (New)
  3. Report Designer will switch to Scripts editor,
    and calcPacking_GetValue event is created.

Add using directive

  1. 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;
}
  1. Add function of GetSmallestQty
  2. Add function of GetUOMFromItem
  3. 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"

  1. 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
  2. Click Preview tab to see the result


Go to menu

IconAC81.png Go to top
ProgrammerGo.jpg Resources For AutoCount Software Developers