How can I allow the end user to add products used in a BOM?

[DeX]

I currently have a rather large PHP system where salesmen enter building details into a form and it builds a bill of materials (BOM) based on their inputs. Currently if they want to add any new materials into the system I have to do it because the calculations for getting the quantities of each item often depend on the attributes of other items in the system and I don't know how to give this control to the user.

 

For example, when the quote is generated to calculate the BOM, it iterates over each material in the database and fetches the quantity calculation based on the ID. The quantity calculation is hard coded into the PHP and could be something like this:

case (self::slidingTecScrews) :
                    $quantity = 0;
                    $buffer = 1.03;
                    $screwsPerBox = 500;

                    for ($i = 0; $i < count(json_decode($this->slidingQuantities, true)); $i++)
                        $quantity += ($this->getSlidingDoorWidths($i) / 2 + 1) * $this->getSlidingDoorQuantities($i) * 5 * ceil($this->getSlidingDoorWidths($i) / 3) * $this->getSlidingDoorQuantities($i); // for all doors

                    $quantity *= $buffer; // add buffer for extra screws

                    if ($quantity > 0)
                        $quantity += $screwsPerBox - ($quantity % $screwsPerBox); // round up to nearest full box

                    $quantities[] = floor($quantity);
                    break;

In this example I get the quantity of Sliding Door Tec Screws and I run a calculation of how many I need per sliding door, then multiply it by the quantity of sliding doors to get the total. Then I add in a buffer because the crew is always dropping some on the ground, then I round it up to a full box. I have absolutely no idea how I would build an interface to allow one of the users to add in a product and assign this calculation for the quantity. For good measure here's another random quantity calculation:

case (self::whiteCaulking) :
                    $quantity = ceil((
                        $this->getTotalQuantityManDoors(true) +
                        $this->getTotalQuantityWindows(true)) *
                        .75
                    );

                    $quantities[] = $quantity;
                    break;

This one passes a variable into the quantity of doors / windows to specify whether or not it should include customer supplied doors into the calculation. Sometimes the door is added to the quote but the customer may want to supply it themselves and therefore we need to order the extra trims (and white caulk) but don't want to factor the door into the total material pricing. Here we use 3/4 of a tube of caulking per door / window.

 

Sometimes it will use the quantity of doors in the calculation but only if they are insulated, or only if they're more than 12 feet high. Sometimes it'll add a product based on every 10 square feet of wall light in a building, it could be based on almost anything.

 

Getting the quantity and per-item cost of everything in the BOM allows me to show the actual build cost for the structure and then apply a markup to get the selling price. Thanks a lot.

[mac_gyver]

feel free to use, modify, or ignore any of these thoughts -

 

1) in general, if you find yourself making variables/properties/constants and functions/methods where the name is specific to the name of a data item, so that any time you change the amount of data items, you must write new/delete variables/properties, constants, functions/methods, you have an inflexible hard-coded design, that is both taking your time writing and testing, but also maintaining. your code should instead be general purpose, where the names of things indicate their purpose or function, not the name of the data they operate on. see the next item for some examples -

 

2) $this->getTotalQuantityManDoors(true) + $this->getTotalQuantityWindows(true)). i'm betting that the code for each of these methods is identical, except for some values being used, and you have a bunch more of these type of named methods. these are operating on categories. if your product/item table has categories, you could simply use one  common getTotalQuantityByCategory(category_goes_here) method. the same is probably true for a bunch of other hard-coded functions getting quantities of items. just have a common getTotalQuantityByItem(item_id_or_name_goes_here) method.

 

3) customer supplied items. it sounds like you have a bunch of code/logic/parameters to handle this special case, repeated many times? i'm assuming prices are stored in a product/item table and are multiplied by the BOM table quantity to come up with the total price for each item? to handle customer supplied items, just add a price multiplier column to the BOM table. for items that are customer supplied, store a zero in this column. for items that are purchased for the project, store a one/use a one as the default value for the column. multiply the item price * bom quantity * this new column to come up with the total price for any item. no extra handling is needed. the BOM quantity can be used as is to calculate any dependent quantities.

 

4) i would use a data driven design, where the rules are defined in data (that can be stored/retrieved from a database table.) this will remove the values from the logic and let you have just one copy of the logic that is re-used with different sets of rules. you can have 'unit' rules, that apply to each quantity of an item/category, and group rules, that apply to the total quantity in a group (i would put rounding up/down, box quantities .. here). for each item/category, define a multiplier and an offset. as you are looping over the main items, just apply the multiplier and offset to come up with the quantity. for any group, once you have the quantity for the group, apply the group rules.

 

here is a pseudo code example for the white caulking - 

case (self::whiteCaulking) :

    $unit_rules = array();
    // note: this example is for categories, not individual items, though you could mix them easily by adding an indicator to the defining array if the $key it a category or an item and call the appropriate method to get the correct quantity
    $unit_rules['ManDoors'] = array('mult'=>.75,'offset'=>0);
    $unit_rules['Windows'] = array('mult'=>.75,'offset'=>0);
    
    $group_rules = array();
    $group_rules[] = array('operation'=>'function','name'=>'ceil');
    
    $quantity = 0;
    // apply any unit rules
    foreach($unit_rules as $key->$rule)
    {
        $quanity += ($this->getTotalQuantityByCategory($key) * $rule['mult']) + $rule['offset'];
    }
    
    // apply any group rules
    foreach($group_rules as $rule)
    {
        switch($rule['operation'])
        {
            case 'function':
                $quantity = $rule['name']($quantity);
            break;
            
        }
    }
    
    $quantities[] = $quantity;
break;

i didn't work out how this would apply to the slidingTecScrews example, but it should be possible to make this work for any case you have now.

 

you will find that the actual php logic for each case statement is or can be made to be the same, just with different defining arrays. when you get to that point, you would just set up the two arrays of data for each case, then call a common function/method to calculate the quantity and return it to the calling code.

 

once you get to this point, you can eliminate all the switch/case logic and just store/retrieve all the rules in a database table and loop over them in turn to calculate the bom quantities.

Edited September 22, 2016 by mac_gyver

[DeX]

Very cool, thank you! When the user is adding the new item and trying to input how the quantity calculation will work, how could the interface work for that? How could the specify that 3/4 of a tube of this new product needs to be added any time another product (doors / windows) is added? And to make it more complicated, what about all the confusion for how the user would enter the calculation for the Sliding Tec Screws? Would I just ask them for the multiplier, offset and rules for each product?

[mac_gyver]

creating a new entry would be fairly straight forward -

 

1) enter the name and select the category for the new item. inserting this into the product/item table would assign an item id to it. the item id would be used in any related data and logic.

 

2) if the new item is used by other unit items or categories of items, you would need a way of searching/listing and selecting (checkboxes) which of the existing items/categories it goes with. the category id or item id of the selected item would become the $unit_rules key/index. you would enter any multiplier/offset or other values you determine you may need. any group rules you have define would have a way of selecting just the possible choices. if at all possible, i would avoid allowing any arbitrary logic/tests to be entered.

 

i'll have to give some thought to the Sliding Tec Screws case.

[mac_gyver]

the easy answer to the Sliding Tec Screws, is, to just enter the number of screws per door as the multiplier for each door entry.

[DeX]

the easy answer to the Sliding Tec Screws, is, to just enter the number of screws per door as the multiplier for each door entry.

The quantity of screws is based on the width of the sliding doors though, because there is a screw every so many square feet I believe. And then another set around the edge, something along those lines, I know the calculation I posted is the calculation we're using in production right now.

[DeX]

 

case (self::whiteCaulking) :

    $unit_rules = array();
    // note: this example is for categories, not individual items, though you could mix them easily by adding an indicator to the defining array if the $key it a category or an item and call the appropriate method to get the correct quantity
    $unit_rules['ManDoors'] = array('mult'=>.75,'offset'=>0);
    $unit_rules['Windows'] = array('mult'=>.75,'offset'=>0);
    
    $group_rules = array();
    $group_rules[] = array('operation'=>'function','name'=>'ceil');
    
    $quantity = 0;
    // apply any unit rules
    foreach($unit_rules as $key->$rule)
    {
        $quanity += ($this->getTotalQuantityByCategory($key) * $rule['mult']) + $rule['offset'];
    }
    
    // apply any group rules
    foreach($group_rules as $rule)
    {
        switch($rule['operation'])
        {
            case 'function':
                $quantity = $rule['name']($quantity);
            break;
            
        }
    }
    
    $quantities[] = $quantity;
break;

 

Now that I have a chance to go through this very slowly and understand it I have a few questions about the flow of the logic.

1. The switch statement checks if we're looking for caulking and then enters into this logic to get the quantity.

2. What do you mean about this example being for categories and not for items? It seems like it would work for white caulking as it is.

3. I see you've added a multiplier and an offset for 2 other items (doors / windows), I understand that.

4. I see you add a group rule of rounding up on the screws to be used later, I get that.

5. Then you execute the unit rules, this part I kind of understand. You're passing in 'ManDoors' as the key for the calculation, would it be correct to assume I should pass in the database ID of the product instead? Is that better practice?

6. In the same section, you're adding the offset, should you be multiplying by it instead? Is this a typo?

7. If I want to apply the calculation only if the door is insulated, would I just use a second offset? Would I just keep adding offsets for any additional attributes I wanted to check for like this? For example, a height labour charge is added if the building is over 16 feet high because the crew has to rent a scissor lift to do the work and that cost has to get passed to the customer. So I'm assuming I would have an offset for whether or not the building is over 16 feet.

8. Do I hard code what these offsets are or can the client add an additional offset for a new attribute when adding items?

 

This is great, thank you. If I can allow customers to add their own products I can set this up as a portal where clients can sign themselves up without any involvement from me and I can charge them a monthly fee for access so if you would prefer to speak on the phone and I pay you for an hour of your time, that would be fine by me. It would be nice to have it here for others to see too though.

[mac_gyver]

2. What do you mean about this example being for categories and not for items? It seems like it would work for white caulking as it is.

 

 

see post #2 in the thread, point #2, about how i came up with categories from your existing code.

 

this example is for your hard-coded rules for 'ManDoors' and 'Windows', converted into general purpose, data driven code.  'ManDoors' and 'Windows' are not specific items, they are at best categories that items are organized under.

 

5. Then you execute the unit rules, this part I kind of understand. You're passing in 'ManDoors' as the key for the calculation, would it be correct to assume I should pass in the database ID of the product instead? Is that better practice?

 

 

in general, you would use id's. since your current code doesn't have id's for categories, only names as part of method calls, i used the only information that i had available in the example.

 

6. In the same section, you're adding the offset, should you be multiplying by it instead? Is this a typo?

 

 

the offset is there for when you want to offset the quantity by a value, not dependent on how may 'used on' items there are.

 

7. If I want to apply the calculation only if the door is insulated, would I just use a second offset? Would I just keep adding offsets for any additional attributes I wanted to check for like this?

 

 

 

different variations of something, insulated vs non-insulated, are/should be different items/part numbers. each different item would have their own unit rule. since ManDoors seems to be a category, you would have categories for insulated ManDoors and un-insulated ManDoors.

 

for the example given, you would add as many rules are there are cases -

 

for white caulking rules - 

   $unit_rules['ManDoors'] = array('mult'=>.75,'offset'=>0);
   $unit_rules['Windows'] = array('mult'=>.75,'offset'=>0);
   $unit_rules['ManDoors-insulated'] = array('mult'=>1.2,'offset'=>0);
   $unit_rules['Windows-insulated'] = array('mult'=>.8,'offset'=>0);

 

For example, a height labour charge is added if the building is over 16 feet high because the crew has to rent a scissor lift to do the work and that cost has to get passed to the customer. So I'm assuming I would have an offset for whether or not the building is over 16 feet.

 

 

 

for any cases like this, i would add a line item to the BOM, 'building height adder' that the rules in the the scissor lift quantity calculation would use -  $unit_rules[building_height_adder_item_id_goes_here] = array('mult'=>0,'offset'=>1);. then if the building height adder is on the BOM, the scissor lift quantity calculation would result in 1 scissors lift rental.

 

8. Do I hard code what these offsets are or can the client add an additional offset for a new attribute when adding items?

 

 

see the commentary in post #2. the whole point of what i posted is to move the values that are hard-coded in the program logic into a data structure, making this a data-driven design. once you get the hard-coded values out of the code, you can define them in a database table and let new rules get added or existing rules get edited by providing a user interface for the database table.

Edited September 28, 2016 by mac_gyver

[mac_gyver]

The quantity of screws is based on the width of the sliding doors though, because there is a screw every so many square feet I believe. And then another set around the edge, something along those lines, I know the calculation I posted is the calculation we're using in production right now.

 

to make this general purpose (what if a different door frame material requires more or less screws), the easiest method is to simply enter the number of screws as the multiplier in the rule for each different door item id, not for the category of sliding doors.

[DeX]

creating a new entry would be fairly straight forward -

 

1) enter the name and select the category for the new item. inserting this into the product/item table would assign an item id to it. the item id would be used in any related data and logic.

 

2) if the new item is used by other unit items or categories of items, you would need a way of searching/listing and selecting (checkboxes) which of the existing items/categories it goes with. the category id or item id of the selected item would become the $unit_rules key/index. you would enter any multiplier/offset or other values you determine you may need. any group rules you have define would have a way of selecting just the possible choices. if at all possible, i would avoid allowing any arbitrary logic/tests to be entered.

 

i'll have to give some thought to the Sliding Tec Screws case.

 

Well today is the day I actually start implementing this change in the code and I'm scanning which items I can apply this to and which I can't. I keep focusing on the ones that check to see if the item (door or window) is customer supplied or not and how that would translate. You mentioned above that I could use a multiplier of 1 or 0 to accomplish this but would this be better than separating the customer supplied doors and non-customer supplied doors into 2 separate items?

 

Here is one I'm specifically dealing with right now:

                    case (self::sideWallBeam) :
                        for ($i = 0; $i < count(json_decode($this->overheadQuantities, true)); $i++)
                            if (strtolower($this->getOverheadDoorLocations($i)) == strtolower("Side Wall"))
                                $quantities[] = $this->getOverheadDoorQuantities($i);

                        for ($i = 0; $i < count(json_decode($this->slidingQuantities, true)); $i++)
                            if (strtolower($this->getSlidingDoorLocations($i)) == strtolower("Side Wall"))
                                $quantities[] = $this->getSlidingDoorQuantities($i);
                        break;

The salesman enters two 10x12 overhead doors, another 3 10x10 overhead doors and finally a single 10x12 sliding door when doing the quote. For each group of overhead doors, I check to see if that door location is side-wall or end-wall (chosen by salesman) and then add side-wall beams accordingly. My main confusion here resides with the number of options available when adding these doors. They can be customer supplied, end-wall, side-wall and also insulated / non-insulated. Various items depend on each of these attributes when determining if they are required for building the building. Thanks again.

[DeX]

Another slight addendum, what about for items that do not rely on quantities of other items but rely solely on inputs from the salesman? I see the rules in these examples are for multipliers and offsets used in conjunction with quantities of other existing items but what about things like gable vents which have a quantity input by the sales person on the quote? The quantity of these items is simply the exact number input by the salesman. Nothing more.

 

Most importantly I don't see how the interface would work for this. The input fields are hard coded on the input page, I don't have a list in the database. So for the interface to allow the user to add a new product into the system and base it on a salesman input, that would be quite difficult. I would need to shift all inputs to the database and categorize / order them for how they would display relative to one another.

[mac_gyver]

the data-driven rule based logic is only to replace the hard-coded logic you have now, for determining the derived quantity of dependent items, based on the primary/direct entered item. if you don't have any hard-coded logic calculating the quantity now, there would not be any rules needed. you probably don't have the item in your switch/case statement either.

 

you have a direct bom, of the items that are selected by people and an indirect/derived bom, of items that this thread is trying to calculate. it's only the indirect/derived items that will have rules defined for them.

Edited November 3, 2016 by mac_gyver

[DeX]

the data-driven rule based logic is only to replace the hard-coded logic you have now, for determining the derived quantity of dependent items, based on the primary/direct entered item. if you don't have any hard-coded logic calculating the quantity now, there would not be any rules needed. you probably don't have the item in your switch/case statement either.

 

you have a direct bom, of the items that are selected by people and an indirect/derived bom, of items that this thread is trying to calculate. it's only the indirect/derived items that will have rules defined for them.

Okay so in order to allow the user to enter a new item and give them the ability to add it to the direct BOM or the indirect BOM, I would need to give them a list of available inputs in order to tie the multiplier to for the quantity calculations. That means I would need to build the quote inputs from the database. I would also need to give them the ability to add a new input in case they're adding a direct BOM material which relies on an input that doesn't exist yet.

 

Am I correct? I'm very excited to get this going, just want to make sure I'm attacking it correctly before I begin. Thanks a lot.

[DeX]

 

 

for any cases like this, i would add a line item to the BOM, 'building height adder' that the rules in the the scissor lift quantity calculation would use -  $unit_rules[building_height_adder_item_id_goes_here] = array('mult'=>0,'offset'=>1);. then if the building height adder is on the BOM, the scissor lift quantity calculation would result in 1 scissors lift rental.

 

 Two questions about this.

 

1. You had the rule ID as building_height_adder_item_id but should this be the actual item's ID instead? Should it be the database ID of the scissor lift and not an arbitrary height adder item id? I'm probably not explaining this properly but I was under the assumption that that spot is reserved for all item ID in the database and not the ID of something else so if we're trying to calculate quantities of scissor lifts, it would be the scissor lift ID in that spot.

 

2. In the last line you state it will show as 1 (because of the offset) if the scissor lift is in the BOM. I'm confused by this because I thought all items would always be in the BOM for every building, the rules would just get a quantity of 0 or more based on the rules associated with that item ID. If the item is not needed for that building (insulation in a non-insulated building), then it calculates a quantity of 0 based on the rules. Is there another part where I'm only adding materials to the BOM if required, based on additional rules?

[mac_gyver]

re: post #13. i would say the answer is yes. but you should work out how you are going to do this for each case.

 

some example cases -

 

1) adding a new direct item that uses no indirect items - enter the information about the new direct item and save it, ignoring anything else that might be present on the page to support example cases #2 - #4.

 

for example, an entry floor mat that will just be laid on the floor.

 

2) adding a new direct item that uses an existing indirect item - enter and save the new direct item information per example #1, but make use of a search/select interface on the page to choose from existing indirect items.

 

for example, an entry floor mat that will use double-sided sticky tape to hold it in place. after entering and saving the new floor mat information, display any existing indirect items that reference the chosen direct item (there will be none since this direct item has just been inserted/created) and use the search/select interface to display existing tapes/adhesives (whatever you are using to categorize items.) and select (checkboxes or perhaps just by entering the unit multiplier/offset data) which indirect item(s) are to be used with this direct item. after entering any multiplier values, save the new rules for this indirect item to the existing rules that may already exist for it.

 

3) adding or editing indirect items for an existing direct item. same as the second part for example case #2. rather than entering and saving the information for a new direct item, just select an existing direct item. the edit page would display the direct item that has been selected, any existing indirect items that reference the chosen direct item (so you can edit the rules, including deleting rules) and the search/select interface to let you add more indirect items for the chosen direct item.

 

4) adding a new indirect item. near the search/select interface, if there isn't a suitable existing indirect item, have entry form fields to add a new indirect item, that once added can be selected for the current chosen direct item.

[mac_gyver]

1. You had the rule ID as building_height_adder_item_id but should this be the actual item's ID instead? Should it be the database ID of the scissor lift and not an arbitrary height adder item id? I'm probably not explaining this properly but I was under the assumption that that spot is reserved for all item ID in the database and not the ID of something else so if we're trying to calculate quantities of scissor lifts, it would be the scissor lift ID in that spot.

 

 

the id in the $unit_rules[...] would be the item id of a direct item. since i don't know how you are entering/specifying what would trigger the scissor lift due to the building height, i suggested a general purpose way of having a "building height adder" item, which would be a direct item that a person can pick and add to the direct bom. if you already have an item that will always be present on a bom that does this, you would use it's id in the rule.

 

2. In the last line you state it will show as 1 (because of the offset) if the scissor lift is in the BOM. I'm confused by this because I thought all items would always be in the BOM for every building, the rules would just get a quantity of 0 or more based on the rules associated with that item ID. If the item is not needed for that building (insulation in a non-insulated building), then it calculates a quantity of 0 based on the rules. Is there another part where I'm only adding materials to the BOM if required, based on additional rules?

 

 

 

not sure if i follow, but for at least the direct bom, these are items that are selected by a human. they should only be stored in the database bom table if they have been selected for a project/building. if you are initially inserting ALL available items into the bom table, this is creating more work, increasing data storage requirements, probably takes more code, and may be where some of your performance problems are at.

[DeX]

re: post #13. i would say the answer is yes. but you should work out how you are going to do this for each case.

 

some example cases -

 

1) adding a new direct item that uses no indirect items - enter the information about the new direct item and save it, ignoring anything else that might be present on the page to support example cases #2 - #4.

 

for example, an entry floor mat that will just be laid on the floor.

 

2) adding a new direct item that uses an existing indirect item - enter and save the new direct item information per example #1, but make use of a search/select interface on the page to choose from existing indirect items.

 

for example, an entry floor mat that will use double-sided sticky tape to hold it in place. after entering and saving the new floor mat information, display any existing indirect items that reference the chosen direct item (there will be none since this direct item has just been inserted/created) and use the search/select interface to display existing tapes/adhesives (whatever you are using to categorize items.) and select (checkboxes or perhaps just by entering the unit multiplier/offset data) which indirect item(s) are to be used with this direct item. after entering any multiplier values, save the new rules for this indirect item to the existing rules that may already exist for it.

 

3) adding or editing indirect items for an existing direct item. same as the second part for example case #2. rather than entering and saving the information for a new direct item, just select an existing direct item. the edit page would display the direct item that has been selected, any existing indirect items that reference the chosen direct item (so you can edit the rules, including deleting rules) and the search/select interface to let you add more indirect items for the chosen direct item.

 

4) adding a new indirect item. near the search/select interface, if there isn't a suitable existing indirect item, have entry form fields to add a new indirect item, that once added can be selected for the current chosen direct item.

 

Wow this is great, thank you.

 

1. This makes total sense, and to build on your point, would I assign the quantity of floor mats to one of the user inputs? I'm assuming that's the very definition of a direct input, is that it uses only the direct inputs and not any other indirect BOM items.

 

2. I'm not quite sure what you mean by search / select interface, do you mean a drop down box of items from the database? I already have all items in the database so the drop down box is what I had initially thought I would use. Then the user would add a multiplier, an offset and can then add a second or third for the same item if that item is used with multiple other indirect BOM items. Look at me, I'm learning so much from you! It appears that you're associating the tape to the mat in the user interface from the mat side, is this correct? What I mean is it appears you're editing the mat details and adding all of the indirect BOM items that are included when a mat is added. Up until this point I had assumed you would go into the tape and add all direct / indirect BOM items that would have tape associated with them which is essentially backwards from how it appears you're doing it. Am I confused?

 

3. Again it appears you're editing the items that get included when editing a top level direct item. This would mean you go into MAT and edit all the lower level indirect items it uses. Is this more correct than going into TAPE and editing all the higher level direct items that would include tape? I want to make sure I don't have it backwards, same confusion as point 2.

 

4. Are you saying I should allow the user to add a new indirect item while adding a different indirect item? Up until now I was assuming I would only add a single indirect item at a time and then add another indirect item that used that first item. I think it would clean up the interface a lot but want to be sure I'm not removing some functionality that would help with something else.

Oh wait, maybe I understand it now that I read it again. Are you talking about when setting a rule on an item that uses an indirect item, this is how the indirect item entry would work? I see that, so the user wants to create a rule using an item that doesn't exist yet so they would need some sort of interface to add the indirect item and then use it in the rule they were creating for an existing item.

 

the id in the $unit_rules[...] would be the item id of a direct item. since i don't know how you are entering/specifying what would trigger the scissor lift due to the building height, i suggested a general purpose way of having a "building height adder" item, which would be a direct item that a person can pick and add to the direct bom. if you already have an item that will always be present on a bom that does this, you would use it's id in the rule.

 

 

not sure if i follow, but for at least the direct bom, these are items that are selected by a human. they should only be stored in the database bom table if they have been selected for a project/building. if you are initially inserting ALL available items into the bom table, this is creating more work, increasing data storage requirements, probably takes more code, and may be where some of your performance problems are at.

 

1. Aha, I get it now! I had thought I was using the current item ID here that we were currently editing. No that's not right, you're saying I should be using the ID of the item that this item relies on and for which the rule is being created. For example when editing tape, you would put the mat ID here and set your multiplier and offset to calculate the quantity of tape per mat. Thanks.

 

2. So if the user wants to add a floor mat for every 30 feet of building length, how do I populate the drop down list of all direct inputs they can set the rule on? They would need to choose building_length from the drop down and then it would set the rule on that input. Do I just hard code the options they would have in this drop down? I was thinking of putting them all into the database so that the drop down would be populated automatically, then they can also add new inputs on which new direct items can rely.

 

Thanks a lot for this, this will probably help a lot of people searching for this solution in the future.

[mac_gyver]

^^^ i'll have to digest that before replying fully.

 

direct (parent) items are the things that are specifically and intentionally picked by the sales engineer, i.e. quantity 3, steel man door, quantity 10, 3 x 5 sash window.... picking these results in a direct bom that consists mainly of the item id and its quantity, that's stored with respect to a project/building id.

 

the indirect/derived/calculated (child) items are the incidental items, fasteners, sealants, lift rental, labor (i'm assuming you are handling time/labor using this same system) ... that are the result of each direct item.

 

currently, any time you change something on the direct bom, your code is looping over all the possible indirect items (in a bottom up fashion, in your original thread some time ago, it was mentioned to use a top down method, so that only things that change get recalculated) and is using values and equations hard-coded in the logic (hopefully to be converted to this data-driven rules based method) to calculate the quantity of each indirect item id. this produces an indirect bom that mainly consists of the indirect item id and its quantity.

 

entering new items, of either direct or indirect type, can be done en-mass without regard to any association. the page where you define new or edit existing rules could be with respect to either the direct item or the indirect item, and you may want to do both.

 

what i described above is with respect to the direct item. for any selected direct item, you would be able to see what existing rules there are and can edit, delete, or add new indirect items. so, for something like a window, you would see and edit any fasteners, flashing, sealant, ...

 

for an edit page with respect to the indirect items, such as white caulk, when you select that item, it would show you all the rules defined for it and what direct items it is used with and if you are using categories, like in an example earlier in this thread, you can either just list the category name(s) or retrieve and display all the actual direct items that are under any category based rule. this indirect item edit page may be more useful for reporting purposes, rather than being the primary method where you define/edit the rules.

 

lastly, i'm assuming that you have the direct bom stored in a database table. once you have these rules stored in a database table, for at least the unit_rules, you can produce the indirect bom entirely in one sql query (join the bom table with the rules table using the direct item id, sum the - bom quantity times the multiplier plus the offset, and group by the indirect item id). you may be able to also apply the group_rules in the same query, but that's more thinking than i want to do on this subject.

[DeX]

^^^ i'll have to digest that before replying fully.

 

lastly, i'm assuming that you have the direct bom stored in a database table. once you have these rules stored in a database table, for at least the unit_rules, you can produce the indirect bom entirely in one sql query (join the bom table with the rules table using the direct item id, sum the - bom quantity times the multiplier plus the offset, and group by the indirect item id). you may be able to also apply the group_rules in the same query, but that's more thinking than i want to do on this subject.

 

Much more of this is making sense now, so I'm going to start creating database tables today and setting things up to accept the new items. Looking at your last paragraph you mention having all of the direct BOM in the database already, does this mean they need to be added for each user input item already and the user is unable to add a new direct BOM and assign it to the input themselves?

 

For example, once all of this is set up, the user may want to add a welcome mat for each building and the sales guy can directly select the quantity on each building they quote, they don't depend on anything else except the direct quantity input. If the guy chooses 3, there are 3 mats. The administrator would add a new direct BOM item for the mat but how does the system know the quantity is attached to that specific input box? Normally the rules would specify the ID of another BOM item this depends on but in this case it doesn't depend on another item with ID, it depends on a specific input. How would the rule look in this case? Would I need a selector when they create the item for them to select if it references another item or a specific input?

 

I feel like this is going to click for me any minute now, I'm so close.

[DeX]

re: post #13. i would say the answer is yes. but you should work out how you are going to do this for each case.

 

 

Actually it appears you may have answered me here already. I'll give the user an option to enter either a direct item or an indirect item and that way I can use Javascript to present them with the options for creating the rules. Man, I'm really excited about this!

[DeX]

I have actually been coding this up for the last 2 months and am now starting on the rules for each product and category in the system. Right now I have a very simple price calculator with categories and products using multipliers and offsets as suggested:

 

- final price

-- lumber x 1 + 0

--- tenFoot2x4

-- metal x 1 + 0

-- labour x 1 + 0

 

So the final price is comprised of all lumber, metal and labour combined, and lumber is made up of a single product, a 10' piece of 2x4. I'm assuming for simplicity that this product is only added to the building if the building is insulated, how would I account for this? I have multipliers and offsets but what about conditions such as this? Do I add an additional rule to the product group which has a multiplier of 0 if the user input of "insulated" is false?

 

So then it would be:

- 2x4 quantity = lengthOfBuilding x 1 + 0;

After applying all these rules for the quantity I then do a group quantity calculation:

- 2x4 group quantity = 2x4 quantity x insulatedValue + 0;

 

...where insulatedValue is 1 or 0 based on whether the checkbox is checked.

[DeX]

I have been attempting to implement this for the last little while and am pretty well stuck with it. I am trying to rewrite all product quantity calculations using the multiplier / offset method posted earlier but I just can't get all of them to fall into that format. The white caulking works fine but then I have much more complex quantity calculations.

 

A simple one I'm having trouble with is 2x4x16 lumber. These are used end-to-end all the way down both sides of the building. So the quantity calculation is:

buildingLength / lengthOfBoard (16)

And then you multiple by 2 for both sides (or loop through again) and then round up. There isn't really a multiplier here since you're dividing, so how would that work?

 

A much more complicated example is for 2x8x16 lumber which is like so:

                    for ($i = 0; $i < count(json_decode($this->slidingQuantities, true)); $i++)
                        $quantity += $this->getSlidingDoorWidths($i) * 2 / 16 * $this->getSlidingDoorQuantities($i);

The salesman can add as many sliding doors to the building as he wants, so he could add 1 set of 2 10x12 doors and then another single 10x10 door to the other side. That's 3 total doors with different dimensions so the code has to loop through each group to calculate the quantity of 2x8x16. Essentially it is:

- for each group of doors entered

- individual door width times 2 (to line both sides of the door)

- divided by 16 which is the length of the board, this will add 1 board every 16 feet because they're end-to-end on both sides of the door

- multiplied by number of doors in that group because in our example the salesman added 2 doors of identical dimensions in the first group

 

This example isn't as easy as a simple multiplier / offset calculation so I'm totally lost how I would allow the user to enter this material into the system and set a quantity calculation for it themselves.

 

And if you want to see the ENTIRE quantity calculation for 2x8x16, here it is as we're using it right now. The piece I posted above is only a small part inside this:

                case (self::lumber2x8x16) :
                    $quantity = 0;

                    if ($this->getPostSize() == 
                    {
                        $quantity +=
                            ceil(
                                ceil(
                                    (
                                        $this->getBuildingWidth() /
                                        $this->getGablePostSpacing() -
                                        3
                                    ) *
                                    2
                                ) +
                                ceil(
                                (
                                    $this->getTotalQuantityWindows(true) +
                                    $this->getTotalQuantityManDoors(true)
                                ) *
                                22 /
                                16
                            ) +
                            $this->getTotalOverheadDoorWidths() /
                            16
                            );

                        for ($i = 0; $i < count(json_decode($this->overheadQuantities, true)); $i++)
                            $quantity += ($this->getBuildingHeight() - $this->getOverheadDoorHeights($i) + 2) * $this->getOverheadDoorWidths($i) * $this->getOverheadDoorQuantities($i) / 4 / 16; // for each door
                    }

                    for ($i = 0; $i < count(json_decode($this->slidingQuantities, true)); $i++)
                        $quantity += $this->getSlidingDoorWidths($i) * 2 / 16 * $this->getSlidingDoorQuantities($i);

                    for ($i = 0; $i < count(json_decode($this->biFoldQuantities, true)); $i++)
                        $quantity += $this->getBiFoldDoorWidths($i) / 16 * $this->getBiFoldDoorQuantities($i);

                    if ($this->getEveBoardLength() == 16 || $this->getEveBoardLength() == 15)
                    {
                        $count = 0;

                        if ($this->hasSoffit())
                            $quantity += ceil((($this->getLengthGableSoffit() / 12) + ($this->getLengthEveSoffit() / 12)) / $this->getEveBoardLength()) + 1;

                        $quantity += $this->getBuildingLength() * 2 / $this->getEveBoardLength();

                        foreach (json_decode($this->kickWallLengths, true) as $length)
                        {
                            if ($this->isKickWallEve($count))
                            {
                                if ($this->getKickWallHeights($count) == 4)
                                    $quantity +=
                                        ceil($this->getKickWallLengths($count) / $this->getEveBoardLength()) *
                                        ($this->isKickWallInsulated($count) ? 7 : 6);
                                else if ($this->getKickWallHeights($count) == 6)
                                    $quantity +=
                                        ceil($this->getKickWallLengths($count) / $this->getEveBoardLength()) *
                                        ($this->isKickWallInsulated($count) ? 10 : 9);
                                else if ($this->getKickWallHeights($count) == 
                                    $quantity +=
                                        ceil($this->getKickWallLengths($count) / $this->getEveBoardLength()) *
                                        ($this->isKickWallInsulated($count) ? 13 : 12);
                            }

                            $count++;
                        }
                    }

                    if ($this->getGableBoardLength() == 16 || $this->getGableBoardLength() == 15)
                    {
                        $count = 0;

                        foreach (json_decode($this->kickWallLengths, true) as $length)
                        {
                            if ($this->isKickWallGable($count))
                            {
                                if ($this->getKickWallHeights($count) == 4)
                                    $quantity +=
                                        ceil($this->getKickWallLengths($count) / $this->getGableBoardLength()) *
                                        ($this->isKickWallInsulated($count) ? 7 : 6);
                                else if ($this->getKickWallHeights($count) == 6)
                                    $quantity +=
                                        ceil($this->getKickWallLengths($count) / $this->getGableBoardLength()) *
                                        ($this->isKickWallInsulated($count) ? 10 : 9);
                                else if ($this->getKickWallHeights($count) == 
                                    $quantity +=
                                        ceil($this->getKickWallLengths($count) / $this->getGableBoardLength()) *
                                        ($this->isKickWallInsulated($count) ? 13 : 12);
                            }

                            $count++;
                        }
                    }

                    if ($this->hasPermaPosts())
                        $quantity += 4 + $this->getTotalQuantityDoors() * 2;

                    $quantities[] = $quantity < 0 ? 0 : ceil($quantity);

                    break;