Report Script: Create a Packing List in report script

From AutoCount Resource Center
Revision as of 03:28, 1 August 2018 by DanielY (talk | contribs)
Jump to navigation Jump to search

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 area and customer.

Product

AutoCount Accounting 1.8 / 1.9
Applicable to AutoCount Accounting 2.0

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

  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 at the top of Scripts, if has not already added.
    • using System.Data;
    • using System.Linq;
  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 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
  13. Click Preview tab to see the result
//Main Entry
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 <= 0)
			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)
        ).Sum(r => r.Field<decimal>("Qty") * r.Field<decimal>("Rate"));
}
//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());
}
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