2

I’ve got me a design and implementation problem extracting and formulating the results I need.
What I’m trying to do is build some reporting on forecasted stock commitments in an inventory system to help drive JIT stock procurement and manufacturing. I’m really sorry, there is a fair bit to this so if you stick with me on this explanation, I really appreciate it.

The underlying database for the inventory system I’m using is a 3rd party propriety database that I cannot alter. What I’m doing is using C# I'm executing a number of simple SQL queries via an oleDB connection to extract the data into object collections then manipulate the results via a series of linq queries and lamba functions. The data model I’m working with is as follows: Please note, this is somewhat simplified Source Datastructure

For any one product, what I need to show:

  1. The Stock on hand
  2. The list of jobs that make up forecasted demand (Needs to be separated by Job manufacture attribute)
  3. Calculate the forecasted commitments for products based on business rules
  4. Calculate the lead time of manufactured products.

Some other notes

  1. A Kitset is a product that has components (End product is manufactured).
  2. A component can itself be a kitset.
  3. A component is a product used in the manufacture of kitsets.
  4. Any product can be supplied direct to end consumer regardless of being either a kitset, a component or otherwise.

Rules to calculate product Lead Times If product is not a Kitset, use lead time from database, no further change is required Otherwise, will need to calculate based on the lead times for the component products as follows

If Kitset product, Kitset Lead Time = DB LeadTime + Maximum component Lead Time where NonDiminishing = false + the Sum of component LeadTime where NonDiminishing = True

To calculate the future commitments of products Consider this example. product Structure

Easiest to start at the bottom and work my way up.

  • Kitset Product 3, I’ve got 22 in stock, and future commitment for 30. This means I need to manufacture a further 8 units to satisfy demand.
  • Kitset Product 2, I’ve got 18 in stock and a supply commitment 16. In addition to this I also need 8 units to satisfy the manufacture requirements of Kitset Product 3 giving me a total commitment of 24 meaning I have to manufacture 6 units.
  • Kitset Product 1 follows the same sort of path. I’ve got 4 in stock, I need 6 units to satisfy the requirements of Kitset Product 2 so I need to manufacture a further 2 units.

This leads me to where I’m getting hung up. Effectively I’ve got a collection of products, each instance of a product may have a collection of products and so on. I’m really not sure how to implement this cyclic relationship.

Really appreciate any feedback you can give me.

2
  • What specifically are you struggling with? How to represent this as an object model or how to process that model? Both?
    – JimmyJames
    Feb 7, 2019 at 22:45
  • @JimmyJames. Probably both
    – Hursey
    Feb 7, 2019 at 22:47

2 Answers 2

1

After a couple days working on other issues, I had a moment of clarity.

So, turns out my problem isn't so much the cyclic relationship. It's how to recursively calculate the future stock commitments. What was bugging me, I'm able to do it manually quite easily but I wasn't able to see how to translate that process into my code.

When Calculating it manually, what I found I was doing was starting at the bottom of the product/kitset/component hierarchy and calculating from the bottom up. When coding my solution, I've been starting at the top and trying to calculate top down. The top down approach was getting me stuck in a bit of a dependency loop.

So using pretty much the same model structure as suggested by JimmyJames, add one more int property to indicate the build level. Then starting at the highest build levels, I can iterate back to the source products.

Really do appreciate everyone that looked at this for me

1

Object model:

Essentially your kitset is a tree structure. The most direct way to do this is to define something along these lines:

public class KitSet {
  public List<KitSet> GetKitsets() {
    // TODO return kitsets.  Return empty list if none.
  }

  public List<Product> GetProducts() {
    // TODO return products.  Return empty list if none.
  }

  public string ToString () {
    // TODO output the kit name
  }
}

Processing model:

Then let's say you want to print a Kitset out for debugging purposes. You can write a recursive function like so:

decimal Total(Kitset kit) {
  decimal sum = 0.0;

  foreach (Product p in kit.GetProducts) {
    sum += p.total;
  }     

  foreach (Kitset k in kit.GetKitsets) {
    sum += Total(k);
  }

  return sum;
}

I've attempted to follow C# sytax and conventions here but I may have not gotten it quite right. Please feel free to ask if you are unsure about what I've written.

4
  • Thanks for the consideration. I do think though that this model structure will likely have the same issue. My fault though, since asking the original question I've realized I've had this all wrong. My problem isn't the cyclic relation, it's recursive processing of the relationship that's my problem.
    – Hursey
    Feb 10, 2019 at 19:27
  • @Hursey So what problem are you having? If you are more specific about the challenge, I can update the recursive code example to be more relevant.
    – JimmyJames
    Feb 11, 2019 at 14:12
  • Sorry, like I said I was looking at the problem from the wrong direction. Where I was striking my problem was starting at the top level first and then recursively attempting to calculate the commitments for an unknown number of levels where I needed to know the commitments from the level below.
    – Hursey
    Feb 11, 2019 at 19:41
  • @Hursey I think you got it worked out but I modified the example to be more similar to the type of thing you are doing in case it helps. Just a note: if you find an answer helpful, you should upvote it and/or accept it.
    – JimmyJames
    Feb 11, 2019 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.