Tool System Design

[CoryG89]

Okay, so I think we should open a new issue to discuss here how we are going to handle the tool system for the application as we have a run into some issues with our previous plans for it. I read back through original slideshow that was submitted as a senior design proposal, and after reading it I think we should discuss how we're going to do it. I also looked at the job site evaluation form they used last time and I think that we should probably do something simple like that when it comes to to our jobsite evaluation form.

I am thinking of giving them a list of names of the tools that we currently have in the database and an input for each one to give a quantity for that tool while, also having an input where a tool that is not currently in the database could be requested. The job site evaluator would not know how many of each tool is in the database, they should only be concerned with how many tools it takes to do the job they are evaluating.

If we did it this way, then in an ideal case, there would be the right tools and sufficient quantities in the database to cover all of the requests and every job site would then be allotted the quantities of tools requested.

In a less than ideal case, there will be the right tools needed currently in the database or there will be an insufficient quantity of one or more tools needed to fulfill all of the job site requests. If that does happen, then because we have a max request amount for certain tools which cannot be exceeded, it could be such that the total quantity of a particular tool which needed to fulfill all the requests is either greater than the max request amount, or it could be such that it is less than or equal to the max request amount.

If the total quantity needed for a each tool (not covered by the tools currently in the database) is less than or equal to the max request amount then everything is fine. However, if the total quantity needed for some tools is greater than the max request amount then before the tool report can be printed it must then be decided which job site that requested said tool will get less than they had originally asked for.

I think we've been ignoring this last step needed and should consider how it should be handled.

[CoryG89]

Also, Justin have you had a chance to think about what you said about simplifying how tools are stored in the database. How were you thinking about changing it? I think it should be simplified and that some of this information should be stored with the job site evaluations documents. Here is an example of one of the tool documents from my test database:

{
    "name": "pick axe",
    "totalAvailable": "4",
    "numberInUse": "4",
    "numberRequested": "2",
    "maxRequest": "on",
    "maxRequestValue": "8",
    "numberRemaining": 0,
    "_id": {
        "$oid": "5278ada2c84e15e008000001"
    }
}

I think the only ones we really need are a quantity and the fields concerning the max request amount. What I was thinking is that we can do away with the numberInUse, numberRequested, and numberRemaining fields. This information will be stored in each of the job site evaluation documents and aggregated to calculate the total number requested for a particular tool before the tool report is generated. For example what I propose is to have a tool document that looks something like this:

{
    "name": "pick axe",
    "totalAvailable": "4",
    "maxRequest": "on",
    "maxRequestValue": "8",
    "_id": {
        "$oid": "5278ada2c84e15e008000001"
    }
}

That would be the minimal changes, which would also mean minimal changes in our current code to get it working. You could change totalAvailable to simply be quantity to be more semantic but this works just as well in my opinion.

Before the tool report is generated, using aggregation, the total amount for each of the tools requested in all of the job site evaluations will be calculated. For each of the totals requested for each tool, we check to see if the totalAvailable quantity in the database for that tool is greater than or equal to the total requested for that tool. If it is then then that tool is covered and does not have to go on the tool report.

However, if totalAvailable is less than the calculated total needed for this tool then we take the difference by subtracting the total request amount for that tool by its totalAvailable quantity to get the difference. At this point there are three possibilities:

1. This tool has maxRequest set to off in which case, this difference value is what goes on the tool report to be ordered.
2. This tool has maxRequest set toonbut the difference calculated for this tool is less than themaxRequestValue` in which cause the difference value is still what goes on the tool report to be ordered.
3. This tool has maxRequest set to on and the difference value calculated is greater than the maxRequestValue.

In the case of the 3rd option, we need to figure out how to handle this. Do we just put the maxRequestAmount on the report and then automatically reduce the amount allotted to each job site as evenly as possible? Or should we just put the maxRequestAmount on the tool report and leave the amount they originally requested intact?

[justin3rd5Auburn]

Cory it seems the only real question you have is number 3 in your comment. It really isn't the reports job to change the request amounts. We aren't even dealing with the allocation of tools just how many can be requested. The Job Site evaluation only contains the request not the allocated value. Maybe this should be a new feature we look into. I know Leslie told us not to put a limit on the requests made in the evaluation only limit the amount in the report.

As far the simplification of the tools forms and database I've already done that almost exactly like you have stated above. Calculating the number to request for the report, the number needed for the tool review page, and the number needed for the staff home page is my question. I really don't know of a way to do other than actually doing the calculation every time by aggregation.

The tool report already calculates the value as you stated above except that the number requested is set to 0 because I don't know how to calculate it yet.

[CoryG89]

I don't understand what numbers you're talking about calculating on the tool review page or staff home page, if we're not going to deal with allocation, as far as I see it we have a list of tools that we have, the quantities of those tools that we have, and the total amount requested for each tool. What else needs to be calculated?

[CoryG89]

I was thinking that we were going to be basically assigning tools to job sites when the max request amount was exceeded, but yeah I think you're right, but I still don't understand what else needs to go on the staff home page or tool review form that isn't already there though. The number that goes on the report can be calculated as I outlined above, but I don't know what you're wanting to put on the other pages. Is it that you're wanting to the same value that is calculated for the report also to be on the tool review page and on the staff home page with the tool table?

[justin3rd5Auburn]

The only thing on the evaluation is the number requested by the staff member doing the evaluation. It's an I would like to have this many if possible. Then we know how's my we actually have from the tools collection. The max request value or "request limit" if you will is only valid for Home Depot. No where else is this a valid value nor does it matter.

On the tool review for we will show the number we actually have "totalAmount" and the "number needed" or the sum of all the requests for that tool across all evaluations. We will show the max request limit here but it has nothing to do with the calculation.

This is the same number that will be shown on the staff home page for every tool. It will be one of the columns.

For the report we calculate exactly what you said.

[CoryG89]

Yeah, I guess you can do that the staff home page and the tool review page as well, it's going to be complicated though and IMO probably more so than its worth. On the staff home page you're basically going to have to run part of the same calculation that we do for the tool report, and on the tool review page you'd have to do the part of the same calculation but only for one tool. They can always just print out the tool report when they're ready, but anything you show on the staff home page or the tool review page isn't going to be the final value anyway. Jobsite requests / evaluations could still be coming in at anytime and the value that there seeing may have changed before their browser even loads the page.

I guess it won't be hard to do after we figure out the aggregation needed to do it for the tool report though. I don't think it will be that hard. I am going to finish up the job site evaluation form after I get done with my databases homework here shortly. I am going to be using the tools collection but I am just depending on the name field so your changes to the tool schema shouldn't effect it.

[justin3rd5Auburn]

i know how to do the calculation. They can't just look at the tool report because that will only show the value that will be requested from home depot. I want to show them the total number needed so they can plan for the next year or buy (she said they have some money) if they need to.

[CoryG89]

I'm confused, not sure if we're talking about the same thing or not. So when I think about the different values there will be for each tool basically it would be something like this:

• totalAvailable - The quantity of a particular tool that Big Event staff currently has to use.
• totalRequested - The total quantity of requests for a particular tool summed across all job site evaluations.
• totalNeeded - Can be calculated as totalAvailable - totalRequested
• maxRequestLimit - Maximum amount of this type of tool that can be requested from supplier.
• supplyRequest - Can be calculated as min(totalNeeded, maxRequestLimit).

So given the above definitions: totalAvailable is the same as currently stored in the tools collection. totalRequested for each tool will be calculated via a MongoDB aggregation operation. Once totalRequested has been calculated for each tool, then we can easily calculate totalNeeded for each tool.

If totalNeeded for a particular tool is calculated to be greater than 0, then that tool will need to go on the tool report, otherwise there is no need for it to go on the tool report.

The actual amount of that tool that should go on the tool report to be supplied by home depot is supplyRequest which may be limited by the maxRequestLimit.

What I meant in my previous comments was that in order to calculate supplyRequest which is needed to go on the tool report you also have to calculate the other values. Therefore, if you so desired you could easily put any of the other values on the report as well like totalRequested and totalNeeded.

Is it totalNeeded or one of these values I listed which you are wanting to put on the staff and tool review pages or is it some other value that I'm not thinking of?

[justin3rd5Auburn]

ok so I changed the naming scheme so it makes more sense:

inventory - the number currently in inventory maxRequest - boolean value that tells if if they have set a limit for home depot maxRequestValue - the request limit for home depot

these are the only stored values for tools other than a name.

for a tool review page we will calculate

total needed - the total number needed from all job site evals. The only calculation is to add the value from each eval.

For the staff homepage we will calculate the same value for each tool instead of one and display it in the table

For the tool report we calculate

Amount to request form home depot: if numberNeeded (calculated as above) > inventory then requestNumber = numberNeeded - inventory. then if maxRequest is on requestNumber = min(requestNumber, maxRequestValue) else just use requestNumber.

[CoryG89]

Yeah, okay then, we're on the same page. Just different names.

I think I just got why you don't want the other stuff on the tool report too. Because its a document that goes to the supplier, they shouldn't know how much inventory of each tool the big event staff currently has, or how many was requested.

I wasn't thinking about that, I was just thinking about putting all of the data we're able to calculate on the report so that it could be used by the staff, but that's not what we need for this report.

Maybe if we have time we should also have another PDF report which has all the calculated data for the tools though, one that is not meant to go to the supplier.

[justin3rd5Auburn]

i mean we can its almost a copy and paste. I can see where it will be useful, but its defiantly an if we have time project.