Programmer:Item Opening Maintenance v2

From AutoCount Resource Center

Technical Specification

  1. Quantity of the Item Opening must not be zero
    • When adding a new entry to Item Opening, zero qty entry will be removed before saving to database
    • Positive (+ve) & negative (-ve) quantity are allowed
  2. Best to filter the loading of Item Opening
    • Avoid loading entire data of Item Opening, when there might be more than 100k records in Item Opening of different UOM, location, batches and etc.
    • Filters that can be applied to reduce the loading of Item Opening data
      • ItemCode
      • ItemGroup
      • ItemType
      • ItemBrand (Module: Advanced Item)
      • ItemCategory (Module: Advanced Item)
      • ItemClass (Module: Advanced Item)
      • Location
    • The filtering will have impact on adding new record of an ItemCode that is not in the range of the filtered range.
      If the newly added ItemCode is not in the filter range, system throw exception message of "Item Code is not in Filter range."
  3. The value of Seq cannot repeat the same, when the value of below columns are the same, or already exist in the record of Item Opening:
    • ItemCode
    • UOM
    • Location
    • BatchNo
    If there is any occasion when Seq is repeated for the same ItemCode, UOM, Location and BatchNo, below error message is thrown:
    Sequence for Item 'FG00001', UOM 'UNIT', Location 'HQ', BatchNo ' ' is not correct.
  4. Seq will affect how the cost of FIFO and LIFO is calculated.

References of AutoCount Accounting version 2.0

AutoCount.Accounting.dll
AutoCount.Accounting.UI.dll
AutoCount.dll
AutoCount.MainEntry.dll
AutoCount.UI.dll
AutoCount.StockMaint.dll


Load Item Opening

Load Item Opening with ItemCode Filtering

public DataTable GetStockOpening(AutoCount.Authentication.UserSession userSession)
{
    AutoCount.Stock.Item.ItemOpeningDataAccess cmd = AutoCount.Stock.Item.ItemOpeningDataAccess.Create(userSession, userSession.DBSetting);
    AutoCount.Stock.Item.ItemOpeningCriteria crit = new ItemOpeningCriteria(userSession.DBSetting);

    crit.ItemCodeFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.ItemCodeFilter.From = "FG00001";
    crit.ItemCodeFilter.To = "FG00003";

    DataTable dtOpening = cmd.LoadAutoGenerateTable(crit);
    return dtOpening;
}
  • This example shows the loading of ItemCode from "FG00001" to "FG00003".

Load Item Opening with Location Filtering

public DataTable GetStockOpening(AutoCount.Authentication.UserSession userSession)
{
    AutoCount.Stock.Item.ItemOpeningDataAccess cmd = AutoCount.Stock.Item.ItemOpeningDataAccess.Create(userSession, userSession.DBSetting);
    AutoCount.Stock.Item.ItemOpeningCriteria crit = new ItemOpeningCriteria(userSession.DBSetting);

    crit.LocationFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    DataTable dtOpening = cmd.LoadAutoGenerateTable(crit);
    return dtOpening;
}
  • This example shows the loading of items opening at location "HQ".

Load Item Opening with the combination of filters

public DataTable GetStockOpening(AutoCount.Authentication.UserSession userSession)
{
    AutoCount.Stock.Item.ItemOpeningDataAccess cmd = AutoCount.Stock.Item.ItemOpeningDataAccess.Create(userSession, userSession.DBSetting);
    AutoCount.Stock.Item.ItemOpeningCriteria crit = new ItemOpeningCriteria(userSession.DBSetting);

    //Filter ItemCode
    crit.ItemCodeFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.ItemCodeFilter.From = "FG00001";
    crit.ItemCodeFilter.To = "FG00003";

    //Filter Location
    crit.LocationFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    DataTable dtOpening = cmd.LoadAutoGenerateTable(crit);
    return dtOpening;
}

Add New Entry to Item Opening

Add New Item Opening with the method of NewItemOpening()

public void NewStockOpening(AutoCount.Authentication.UserSession userSession)
{
    AutoCount.Stock.Item.ItemOpeningDataAccess cmd = AutoCount.Stock.Item.ItemOpeningDataAccess.Create(userSession, userSession.DBSetting);

    //Create and define ItemOpeningCriteria
    AutoCount.Stock.Item.ItemOpeningCriteria crit = new ItemOpeningCriteria(userSession.DBSetting);

    //Filter ItemCode by each item
    crit.ItemCodeFilter.Type = AutoCount.SearchFilter.FilterType.ByIndividual;
    crit.ItemCodeFilter.Add("FG00001");
    crit.ItemCodeFilter.Add("FG00003");

    //Filter only location to "HQ"
    crit.LocationFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    //Load records of existing Items
    AutoCount.Stock.Item.ItemOpeningEntities entities = cmd.LoadItemOpening(crit);

    //Create new Item Opening Entry
    AutoCount.Stock.Item.ItemOpeningEntity entity1 = entities.NewItemOpening();
    entity1.ItemCode = "FG00001";
    entity1.Uom = "UNIT";
    entity1.Location = "HQ";
    entity1.Qty = 1;
    entity1.Cost = 5.01M;

    //Create new Item Opening Entry
    AutoCount.Stock.Item.ItemOpeningEntity entity2 = entities.NewItemOpening();
    entity1.ItemCode = "FG00003";
    entity1.Uom = "UNIT";
    entity1.Location = "HQ";
    entity1.Qty = 2;
    entity1.Cost = 6M;

    try
    {
        cmd.Save(entities);
        AutoCount.AppMessage.ShowMessage("Item Opening Saved.");
    }
    catch (AutoCount.AppException ex)
    {
        AutoCount.AppMessage.ShowErrorMessage(ex.Message);
    }
}

Adding two entries of same ItemCode to Item Opening

Following example shows the increase of Seq when 2nd entry of Item Opening has the same ItemCode, UOM, Location and BatchNo from the 1st entry. 

public void NewTwoEntriesOfSameItem(AutoCount.Authentication.UserSession userSession)
{
    AutoCount.Stock.Item.ItemOpeningDataAccess cmd = AutoCount.Stock.Item.ItemOpeningDataAccess.Create(userSession, userSession.DBSetting);

    //Create and define ItemOpeningCriteria
    AutoCount.Stock.Item.ItemOpeningCriteria crit = new ItemOpeningCriteria(userSession.DBSetting);

    //Filter ItemCode by each item
    crit.ItemCodeFilter.Type = AutoCount.SearchFilter.FilterType.ByIndividual;
    crit.ItemCodeFilter.Add("FG00001");

    //Filter only location to "HQ"
    crit.LocationFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    //Load records of existing Items
    AutoCount.Stock.Item.ItemOpeningEntities entities = cmd.LoadItemOpening(crit);

    //Create new Item Opening Entry
    AutoCount.Stock.Item.ItemOpeningEntity entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 2;
    entity.Cost = 5.01M;
    entity.Seq = 0;

    //Increase the Seq of 2nd Entry of same item
    entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 5;
    entity.Cost = 5.02M;
    entity.Seq = 1;

    try
    {
        cmd.Save(entities);
        AutoCount.AppMessage.ShowMessage("Item Opening Saved.");
    }
    catch (AutoCount.AppException ex)
    {
        AutoCount.AppMessage.ShowErrorMessage(ex.Message);
    }
}

Delete Item Opening entry

When delete existing item opening entry, "ItemOpeningKey" is required.
This example uses a function to find the ItemOpeningKey.
Highlighted shows the function to get the list of keys and deletion of existing keys.

Get "ItemOpeningKey" can also be applied to check the existing record when it can avoid the error of
"Sequence for Item 'FG00001', UOM 'UNIT', Location 'HQ', BatchNo ' ' is not correct."

public void CheckNewEntryBeforeAdding(AutoCount.Authentication.UserSession userSession)
{
    AutoCount.Stock.Item.ItemOpeningDataAccess cmd = AutoCount.Stock.Item.ItemOpeningDataAccess.Create(userSession, userSession.DBSetting);

    //Create and define ItemOpeningCriteria
    AutoCount.Stock.Item.ItemOpeningCriteria crit = new ItemOpeningCriteria(userSession.DBSetting);

    //Filter ItemCode by each item
    crit.ItemCodeFilter.Type = AutoCount.SearchFilter.FilterType.ByIndividual;
    crit.ItemCodeFilter.Add("FG00001");

    //Filter only location to "HQ"
    crit.LocationFilter.Type = AutoCount.SearchFilter.FilterType.ByRange;
    crit.LocationFilter.From = "HQ";
    crit.LocationFilter.To = "HQ";

    //Load records of existing Items
    AutoCount.Stock.Item.ItemOpeningEntities entities = cmd.LoadItemOpening(crit);

    //Delete any existing entries that has same ItemCode, UOM, Location and BatchNo,
    //before adding new entries
    List<long> listKeys = GetExistItemOpeningKey(entities.ItemOpeningTable, "FG00001", "UNIT", "HQ", null);
    listKeys.ForEach(key => entities.DeleteItemOpening(key));

    //Create new Item Opening Entry
    AutoCount.Stock.Item.ItemOpeningEntity entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 2;
    entity.Cost = 5.01M;
    entity.Seq = 0;

    //Increase the Seq of 2nd Entry of same item
    entity = entities.NewItemOpening();
    entity.ItemCode = "FG00001";
    entity.Uom = "UNIT";
    entity.Location = "HQ";
    entity.Qty = 5;
    entity.Cost = 5.02M;
    entity.Seq = 1;

    try
    {
        cmd.Save(entities);
        AutoCount.AppMessage.ShowMessage("Item Opening Saved.");
    }
    catch (AutoCount.AppException ex)
    {
        AutoCount.AppMessage.ShowErrorMessage(ex.Message);
    }
}

Function to find a list of keys that is/are matching. 

public List<long> GetExistItemOpeningKey(DataTable table, string itemCode, string uom, string location, string batchNo)
{
    return table.AsEnumerable().Where(r =>
        r.RowState != DataRowState.Deleted &&
        r.Field<string>("ItemCode") == itemCode &&
        r.Field<string>("UOM") == uom &&
        r.Field<string>("Location") == location &&
        r.Field<string>("BatchNo") == batchNo
    ).Select(r => r.Field<long>("ItemOpeningKey"))
    .ToList();
}

Another function to specially handle Deleting matching Item Opening 

public void DeleteAllMatchingItemOpening(IEnumerable<DataRow> itemOpenings, string itemCode, string uom, string location, string batchNo, Action<long> deleteOpening)
{
    itemOpenings.Where(r => r.RowState != DataRowState.Deleted &&
        r.Field<string>("ItemCode") == itemCode &&
        r.Field<string>("UOM") == uom &&
        r.Field<string>("Location") == location &&
        r.Field<string>("BatchNo") == batchNo
    ).Select(r => r.Field<long>("ItemOpeningKey"))
    .ToList().ForEach(key => deleteOpening(key));
}

To apply this function code; 

DeleteAllMatchingItemOpening(entities.ItemOpeningTable.AsEnumerable(), "FG00001", "UNIT", "HQ", null, entities.DeleteItemOpening);


See Also

Go to menu

Go to top
Resources For AutoCount Software Developers


Retrieved from "https://wiki.autocountsoft.com/wiki/Programmer:Item_Opening_Maintenance_v2?oldid=10319"