2,754
edits
Programmer:Stock Item: Difference between revisions
→Default Item Code Format
mNo edit summary |
|||
| (45 intermediate revisions by the same user not shown) | |||
Line 1:
==Technical Specification==
# Default ItemCode size = 30
# Item must have at least one '''UOM'''
# Must have '''base UOM''', which the UOM rate is 1.
#
# Though AutoCount Accounting supports more than one UOM with the rate of 1, there can only be one '''base uom'''.
# '''Rename''' ItemCode at ItemDataAccess or when Edit the item is not allow.
Line 10:
==References of AutoCount Accounting version 1.8==
{{BaseReferenceAC18}}
'''BCE.AutoCount.StockMaint.dll'''
==Load data with API==
===Single Record===
Load one item by specifying "ItemCode" and "UOM".
<syntaxhighlight lang="csharp">
public void ExampleOfSingleItemRecord(string itemCode, string uom, BCE.Data.DBSetting dbset)
{
BCE.AutoCount.Data.ItemRecord itemRec = BCE.AutoCount.Data.CommonRecordUtils.GetItem(dbset, itemCode, uom);
if (itemRec != null)
{
string description = itemRec.Description;
string desc2 = itemRec.Desc2;
string furtherDesc = itemRec.FurtherDescription;
string defaultSaleUOM = itemRec.SalesUOM;
string defaultPurchaseUOM = itemRec.PurchaseUOM;
decimal sellingPrice = itemRec.Price;
decimal standardCost = itemRec.Cost;
bool isStockControl = itemRec.StockControl;
bool isActive = itemRec.IsActive;
//Data that is of UDF object requires to perform conversion to appropriate datatype
string state = itemRec.UDF["State"].ToString();
decimal commissionRate = BCE.Data.Convert.ToDecimal(itemRec.UDF["COMMRATE"]);
//itemRec.UDF of boolean type returns "T" or "F";
//Must use TextToBoolean(object) method to convert the UDF of boolean type to bool.
bool calcCommission = BCE.Data.Convert.TextToBoolean(itemRec.UDF["CALCCOMM"]);
}
}
</syntaxhighlight>
===Multiple items with Filter===
====Load Items on selection of '''Item Groups'''====
Load multiple items that filter item of selected Item Groups.
Below coding requires to add directive of:-
'''using System.Data;'''
'''using System.Linq;'''
<syntaxhighlight lang="csharp">
public DataTable GetItemDataByItemGroup(string[] itemGroups, BCE.Data.DBSetting dbset)
{
//Build Sql List of Item Group
string itemGroupInSql = BuildItemGroupInSql(itemGroups);
//Construct Sql Select string with selection of Item Groups
string sqlSelectItemInItemGroup = string.Format("SELECT ItemCode FROM Item WHERE IsActive='T' AND ItemGroup IN ({0})", itemGroupInSql);
//Get Item Code List in a table from Sql Server
DataTable tblItemCode = dbset.GetDataTable(sqlSelectItemInItemGroup, false);
//Convert Table of ItemCode to an Array of string
string[] itemArray = tblItemCode.AsEnumerable().Select(r => r.Field<string>("ItemCode")).ToArray();
//Create ItemDataAccess object,
//and call LoadAllItem(string[]) method to filter ItemCode
BCE.AutoCount.Stock.Item.ItemDataAccess cmd = BCE.AutoCount.Stock.Item.ItemDataAccess.Create(dbset);
BCE.AutoCount.Stock.Item.ItemEntities items = cmd.LoadAllItem(itemArray);
//items.ItemTable consists of UOM in single table
return items.ItemTable;
}
</syntaxhighlight>
<syntaxhighlight lang="csharp">
private string BuildItemGroupInSql(string[] itemGroups)
{
//Generate and return a Sql string of ItemGroup
//If ItemGroups string array is null or empty, the return is "SELECT Item FROM LIST('')"
return string.Format("SELECT Item FROM LIST('{0}')",
itemGroups.Aggregate("",
(g1, g2) => string.IsNullOrEmpty(g2)
? g1
: string.IsNullOrEmpty(g1) ? g2 : string.Format("{0},{1}", g1, g2)
));
}
</syntaxhighlight>
*To call the method of '''GetItemDataByItemGroup'''
<syntaxhighlight lang="csharp">
GetItemDataByItemGroup(new string[] { "FINISHED", "RAW" }, mydbset)
</syntaxhighlight>
<br/><br/>
====Load Items that was last updated on specific date====
Load multiple items that filter item that has been updated since a specific date.
Below coding requires to add directive of:-
'''using System.Data;'''
'''using System.Linq;'''
<syntaxhighlight lang="csharp">
public DataTable GetModifiedItemData(DateTime filterModifiedDate, BCE.Data.DBSetting dbset)
{
//Get ItemCode List that is edited (modified) after a specific date
DataTable tblItemCode = dbset.GetDataTable("SELECT ItemCode FROM Item WHERE IsActive='T' AND LastModified>=?", false, filterModifiedDate);
string[] itemArray = tblItemCode.AsEnumerable().Select(r => r.Field<string>("ItemCode")).ToArray();
//Create ItemDataAccess object,
//and call LoadAllItem(string[]) method to filter ItemCode
BCE.AutoCount.Stock.Item.ItemDataAccess cmd = BCE.AutoCount.Stock.Item.ItemDataAccess.Create(dbset);
BCE.AutoCount.Stock.Item.ItemEntities items = cmd.LoadAllItem(itemArray);
//items.ItemTable consists of UOM in single table
return items.ItemTable;
}
</syntaxhighlight>
<br/>
===Stock Balance (Quantity & Costing) Table and with filter location===
<syntaxhighlight lang="csharp">
public System.Data.DataTable GetItemLocationBalanceData(DateTime filterOnDate, BCE.Data.DBSetting dbset)
{
BCE.AutoCount.Stock.StockBalance.StockBalanceHelper sbHelper = new BCE.AutoCount.Stock.StockBalance.StockBalanceHelper(dbset);
//Filter the stock balance to show location from "PJ" to "PJ"
sbHelper.Criteria.LocationFilter.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
sbHelper.Criteria.LocationFilter.From = "PJ";
sbHelper.Criteria.LocationFilter.To = "PJ";
sbHelper.Inquire(filterOnDate);
//sbHelper.ResultDataSet returns 2 tables
//1. Master (Item Balance)
//2. ItemSerialNo (Serial Number)
//System.Data.DataSet dsBalanceWithSerial = sbHelper.ResultDataSet;
return sbHelper.ResultTable;
}
</syntaxhighlight>
*On AutoCount Accounting 1.8.31.188, Master table consists of below columns
*:Location, ItemCode, UOM, BatchNo, '''Balance''', StandardCost, TotalCost, Rate, Shelf, ItemGroup,
*:ItemType, Description, Desc2, ReportUOM, LocationDesc, LocationDesc2, CostingMethodString, ExpiryDate, ManufacturedDate,
*:AverageCost, '''SmallestBalQty'''
*'''Balance''' is the balance quantity of the UOM of this (current) row.
*'''SmallestBalQty''' is the total quantity of all UOMs in '''smallest uom''' (base uom in Item Maintenance).
*Get Stock Item Costing...[[Programmer:Get Stock Cost|Click here]].
==Stock Item API Usage==
===New===
* It is advisable that '''Item Group''' is assigned to an item.
====Method 1 : Simple assign value to new Item====
<syntaxhighlight lang="csharp">
public void NewStockItem(BCE.Data.DBSetting dbSetting)
Line 26 ⟶ 156:
//Since this is a new item, so there is no transactions to be recalculated.
bool recalculate = false;
BCE.AutoCount.Stock.Item.ItemDataAccess cmd = BCE.AutoCount.Stock.Item.ItemDataAccess.Create(dbSetting);
BCE.AutoCount.Stock.Item.ItemEntity itemEntity = cmd.NewItem();
itemEntity.ItemCode = "
//ItemGroup must already exist in "Item Group Maintenance"
itemEntity.ItemGroup = "FINISHED";
itemEntity.Description = "Finished Goods 002";
//0 : Fixed Cost
//1 : Weighted Average
//2 : FIFO
//3 : LIFO
//If not
itemEntity.CostingMethod = 2;
itemEntity.BaseUomRecord.Uom = “UNIT”;
itemEntity.BaseUomRecord.StandardCost = 80;
itemEntity.BaseUomRecord.StandardSellingPrice = 100;
cmd.SaveData(itemEntity, ref recalculate);
}
</syntaxhighlight>
====Method 2 : Auto generate ItemCode====
* This example shows get auto generate ItemCode and multiple UOMs
<syntaxhighlight lang="csharp">
public void NewStockItem(BCE.Data.DBSetting dbSetting)
{
//If true, cost of this item will be recalculated upon saving
//Since this is a new item, so there is no transactions to be recalculated.
bool recalculate = false;
BCE.AutoCount.Stock.Item.ItemDataAccess cmd = BCE.AutoCount.Stock.Item.ItemDataAccess.Create(dbSetting);
BCE.AutoCount.Stock.Item.ItemEntity itemEntity = cmd.NewItem();
BCE.AutoCount.Stock.Item.ItemUomEntity uomEntity;
//Refer to "Auto Generate ItemCode for New Item with ItemCodeHelper" in this page
itemEntity.ItemCode = GetAutoItemCodeByItemGroup("FINISHED", dbSetting)
//ItemGroup must already exist in "Item Group Maintenance"
itemEntity.ItemGroup = "FINISHED";
itemEntity.Description = "Finished Goods 001";
itemEntity.CostingMethod = 1;
Line 46 ⟶ 206:
//UOM
//While creating new item, system will auto initiate a new
//The value of this UOM, is taken from "Default UOM",
//which it is maintained in Tools | Option, under Stock | General Stock Setting
//Next line will remove the new auto created uom, before adding my uom(s).
itemEntity.DeleteItemUom(0);
//
//The first UOM that is rate 1 will be auto assign to BaseUOM of this item
//Assuming from here is a loop to create UOMs
uomEntity = itemEntity.NewUom("UNIT", 1);
uomEntity.StandardCost = 50;
uomEntity.StandardSellingPrice = 100;
uomEntity = itemEntity.NewUom("PCS", 1);
uomEntity.StandardCost = 52;
uomEntity.StandardSellingPrice = 100;
uomEntity = itemEntity.NewUom("BOX", 6);
uomEntity.StandardCost = 150;
uomEntity.StandardSellingPrice = 300;
//End of adding UOM
//The result of the above uom:
//Total of 3 uoms added to this item
//There are two uom with Rate = 1
//"UNIT" is the base uom
cmd.SaveData(itemEntity, ref recalculate);
}
</syntaxhighlight>
===Edit===
<syntaxhighlight lang="csharp">
Line 82 ⟶ 259:
cmd.SaveData(itemEntity, ref recalculate);
}
</syntaxhighlight>
Line 126 ⟶ 295:
}
</syntaxhighlight>
===Delete===
<syntaxhighlight lang="csharp">
public void DeleteStock(string itemCode, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Stock.Item.ItemDataAccess cmd = BCE.AutoCount.Stock.Item.ItemDataAccess.Create(dbSetting);
cmd.Delete(itemCode);
}
</syntaxhighlight>
<br/>
==Auto Generate ItemCode for New Item with '''ItemCodeHelper'''==
* Auto generate item code depends on a valid '''Item Code Format''' setting.
Line 155 ⟶ 335:
}
</syntaxhighlight>
===Default Item Code Format===
Return the text formatting of ItemCode when the formatting is required by auto generate ItemCode
<syntaxhighlight lang="csharp">
private string GetDefaultItemCodeFormat(BCE.Data.DBSetting dbSetting)
| |||