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

From AutoCount Resource Center
Jump to navigation Jump to search
Line 43: Line 43:
 
return;
 
return;
  
decimal smallestQty = GetSmallestQty(GetCurrentColumnValue("Qty"), GetCurrentColumnValue("Rate"));
+
        //Get total smallest UOM Qty of same ItemCode and UOMRate
 +
decimal smallestQty = GetSmallestQty(GetCurrentColumnValue("ItemCode"), GetCurrentColumnValue("UOMRate"));
  
 
decimal uomRate;
 
decimal uomRate;
Line 64: Line 65:
 
</syntaxhighlight>
 
</syntaxhighlight>
 
<syntaxhighlight lang="csharp">
 
<syntaxhighlight lang="csharp">
private decimal GetSmallestQty(object oQty, object oRate)
+
//ItemCode and UOMRate are the grouping in this report
 +
private decimal GetSmallestQty(object oItemCode, object oUomRate)
 
{
 
{
return BCE.Data.Convert.ToDecimal(oQty) * BCE.Data.Convert.ToDecimal(oRate);
+
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")
 +
})
 +
.Select(g => g.Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate")))
 +
.FirstOrDefault();
 
}
 
}
 
</syntaxhighlight>
 
</syntaxhighlight>
Line 82: Line 97:
 
{
 
{
 
//Format the display text of qty and uom
 
//Format the display text of qty and uom
 +
if (qty == 0)
 +
return result;
 
string formatQtyUom = string.Format("{0} {1}", qty, uom);
 
string formatQtyUom = string.Format("{0} {1}", qty, uom);
 
return string.IsNullOrEmpty(result) ? formatQtyUom
 
return string.IsNullOrEmpty(result) ? formatQtyUom
: string.Format("{0} X {1}", result, formatQtyUom);
+
: string.Format("{0}, {1}", result, formatQtyUom);
 
}
 
}
 
</syntaxhighlight>
 
</syntaxhighlight>

Revision as of 09:17, 31 July 2018

Introduction

This tutorial shows how to add a text which displays packing that is based on largest UOM to smallest UOM with report scripting.

Product

AutoCount Accounting 1.8 / 1.9

VSProjectIcon.png Download Report Template (AutoCount Accounting 2.0)

Get Field that is not in report.

Prog PickList.PackUOM.png

Report Script

Show the packing from any UOM

  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

  4. 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
  5. Set the property of newly added Calculated Field
    Locate & Select the newly added calculated field
    In Property Grid, rename (Name) to calcPacking
  6. 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.

  7. Add directive using System.Data; at the top of Scripts, if has not added.
  8. Insert scripts into calcPacking_GetValue
  9. Add function of GetSmallestQty
  10. Add function of GetUOMFromItem
  11. Add function of FormatCurrentUomResult

  12. 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

private void calcPacking_GetValue(object sender, DevExpress.XtraReports.UI.GetValueEventArgs e)
{
	DataTable tblUOM = GetUOMFromItem(GetCurrentColumnValue("ItemCode"));
	if (tblUOM == null)
		return;

        //Get total smallest UOM Qty of same ItemCode and UOMRate 
	decimal smallestQty = GetSmallestQty(GetCurrentColumnValue("ItemCode"), GetCurrentColumnValue("UOMRate"));

	decimal uomRate;
	string result = "";
	foreach (DataRow row in tblUOM.Rows)
	{
		if (smallestQty < 1)
			break;

		uomRate = BCE.Data.Convert.ToDecimal(row["Rate"]);
		result = FormatCurrentUomResult(result,
			uomRate > 0 ? Math.Floor(smallestQty / uomRate) : 0,
			row["UOM"].ToString());

		//Calculate the remaining Smallest Qty for next UOM
		smallestQty = smallestQty % uomRate;
	}
	e.Value = result;
}
//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)
		)
		.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();
}
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());
}
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);
}


Go to menu

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