Deduct food recipes from the main inventroy?

[savedlema]

Hi all!

I'm stuck somewhere. I have a table for Inventory (which contains the available amount of each food component)
What I want to do: If I select a certain food (say salad) I want to be able to deduct the components (amount) of 'salad' from the inventory table.

Consider this:
Salad is made up of 20mil of water, 50 of oil and 100gm of onion (just an example)
When I give someone a Salad, I want to be able to deduct 20mil of water, 50 of oil and 100gm of onion from the inventory table. (So that I know how much of each item in my inventory is left)

The code is to allow these recipes of Salad to be changed/deleted/some others added into it. (Flexibility). I've found this difficult since in order to allow this flexibility, I can't just write that deduction in code as something that won't change forever.(More recipes can be added in the future)

Does anyone has an idea on how to do this?

I use MySQL with VB.NET

[savedlema]

What I have so far:

My tables are:
1.Inventory (item,amount)
2. Menu (menu_name)
3. Salad (recipe, amount)

Other tables will be added for each menu, just like I did for salad. (I may have table for Rice, Soda e.t.c)

Now, when I click Salad, since it has two values (recipe for it and amount), how then can I subtract these (recipe & amount) from the (amount) in the inventory table?

I find it challenging.

[Begginnerdev]

You will have to get the current value, subtract, then update to the newest value.

This is assuming your amount colum in as decimal value.

For Example:

       Try
            Dim da As New OleDbDataAdapter(New OleDbCommand("SELECT * FROM Inventory WHERE Item='" & sItemType & "'", myConnection))

            'The select gets the table structure.
            Dim ds As New DataSet

            da.Fill(ds, "Inventory")

            If Not IsNothing(ds.Tables("Inventory")) And ds.Tables("Inventory").Rows.Count > 0 Then
                Dim dblCurrentAmount As Double = CDbl(ds.Tables("Inventory").Rows(0)("Amount")) - dblAmountToSubtract

                If dblCurrentAmount < 0 Then
                    MsgBox("You have used more than what is in stock!" & vbCrLf & "This operation will not be completed", MsgBoxStyle.Exclamation, "ERROR!")
                    GoTo DISPOSE
                ElseIf dblCurrentAmount = 0 Then
                    MsgBox("You have depleted your stock for this item!" & vbCrLf & "Please restock this item!", MsgBoxStyle.Exclamation, "Oops!")
                End If

                ds.Tables("Inventory").Rows(0)("Amount") = dblCurrentAmount

                da.UpdateCommand = New OleDb.OleDbCommandBuilder(da).GetUpdateCommand
                da.Update(ds, "Inventory")

                MsgBox("Item Updated!", MsgBoxStyle.Information, "Complete!")
            End If
DISPOSE:
            da = Nothing
            ds = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try 

[savedlema]

Thanks Begginerdev!

Something:Please can you explain to me what will be done in line 2 of your solution?

Dim da As New OleDbDataAdapter(New OleDbCommand("SELECT * FROM Inventory WHERE Item='" & sItemType & "'", myConnection))

What will this WHERE Item='" & sItemType & "'", myConnection do? I did not understand this. (especially the "sItemType")

Thank you very much.

[Begginnerdev]

The select statment retreives the record from your database table.

For example:

Inventory
Item  | Amount
garlic,10.21
onion,50.13
water,200.23


"SELECT * FROM Inventory WHERE Item='garlic'" 'Would return garlic,10.21

As for the myConnection, you will need a connection to the database to retreive the data from the database.

In this case, my connection would look as follows:

Dim myConnection As New OleDBConnection("MyConnectionStringHere")

For connection strings, see this website.

I hope this clears things up for you!

[savedlema]

Thank you. But, I think we are using different connectors? This is how I had to change your code to suite the connector I use:

     con.Open()

            Try
                Dim da As New MySqlDataAdapter(New MySqlCommand("SELECT * FROM Inventory WHERE Item='" & Me.ComboBox1.Text & "'", con))
                'The select gets the table structure.

                Dim ds As New DataSet
                da.Fill(ds, "Inventory")
                If Not IsNothing(ds.Tables("Inventory")) And ds.Tables("Inventory").Rows.Count > 0 Then
                    Dim dblCurrentAmount As Double = CDbl(ds.Tables("Inventory").Rows(0)("Amount")) -dblAmountToSubtract '(This dblAmountToSubtract is not declared, what should it be set to?)
                    If dblCurrentAmount < 0 Then
                        MsgBox("You have used more than what is in stock!" & vbCrLf & "This operation will not be completed", MsgBoxStyle.Exclamation, "ERROR!")
                        GoTo DISPOSE
                    ElseIf dblCurrentAmount = 0 Then
                        MsgBox("You have depleted your stock for this item!" & vbCrLf & "Please restock this item!", MsgBoxStyle.Exclamation, "Oops!")
                    End If

                    ds.Tables("Inventory").Rows(0)("Amount") = dblCurrentAmount
                    da.UpdateCommand = New OleDb.OleDbCommandBuilder(da).GetUpdateCommand '(How should I change this line?)
                    da.Update(ds, "Inventory")
                    MsgBox("Item Updated!", MsgBoxStyle.Information, "Complete!")
                End If
    DISPOSE:
                da = Nothing
                ds = Nothing
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            con.Close()

NOW:
1. dblAmountToSubtract is not declared, what should this be set to?
2. How should I change this lineda.UpdateCommand = New OleDb.OleDbCommandBuilder(da).GetUpdateCommand to suit my connector?

I appreciate your support.

[Begginnerdev]

dblAmountToSubtract will be a variable declared as double that will hold the value to subtract from your total.

For Example:

Dim dblAmountToSubtract As Double = Cdbl(txtAmountUsed.Text)
'This pulls the amount from a text box on a form.

For the command, you can simply replace:

da.UpdateCommand = New OleDb.OleDbCommandBuilder(da).GetUpdateCommand

With

da.UpdateCommand = New MySQLCommandBuilder(da).GetUpdateCommand

[savedlema]

Thank you, now it works; it subtract & update one record in the inventory table. BUT,
What I want to do is, consider the inventory table in your solution :

Inventory table:
   Item   | Amount
garlic,     10.21
onion,      50.13
water,      200.23

Now, let's say to make 1 Salad you will use 5of garlic, 5 of onion and 5 of water. So, when I click on "Salad", I want all these (5garlic,5onion,5water) to be deducted from their store in the inventory table. (Not deduct just one)

This composition for Salad (and whatever will be added) can be changed by a user, say, he can add 5 of salt to it.

I will appreciate more help on that.

[GeekPlease]

You must have settings in your program where you can update the recipe of each food. You can't expect your program to know that thing. Hope this helps

[ddanbe]

I hope these are just example ingredients for a salad. Water in a salad? :-)
Another note:
The offial symbol for milliliters is not mil but ml
The offial symbol for grams is not gm but g

[savedlema]

Yes ddanbe, these are just examples, if it works I can substitute that with something useful. No problem with measurements units too for now, I just want it to do the subtraction and I will care about that in the future. Any idea with my case?

[savedlema]

Note: It seems like you guys did not understand my scenario clearly. I will add this note;

Now, let's say to make 1 Salad you will use 5of garlic, 5 of onion and 5 of water. So, when I click on "Salad", I want all these (5garlic,5onion,5water) to be deducted from their store in the inventory table. (Not deduct just one)
This composition for Salad (and whatever will be added) can be changed by a user, say, he can add 5 of salt to it.

I will appreciate more help on that.

[Begginnerdev]

Place the code in a sub procedure and pass in the item type for the action:

Private Sub DeductFromTotal(ByVal sItemType As String,ByVal dblAmountToSubtract As Double)
    Try
        Dim da As New MySqlDataAdapter(New MySqlCommand("SELECT * FROM Inventory WHERE Item='" & Me.ComboBox1.Text & "'", New MySQLConnection("ConnectionStringHere")) 
        'You don't have to open the connection before using it in the adapter.'
        'The select gets the table structure.'

        Dim ds As New DataSet
        da.Fill(ds, "Inventory")
        If Not IsNothing(ds.Tables("Inventory")) And ds.Tables("Inventory").Rows.Count > 0 Then
            Dim dblCurrentAmount As Double = CDbl(ds.Tables("Inventory").Rows(0)("Amount")) - dblAmountToSubtract '(This dblAmountToSubtract is not declared, what should it be set to?)
            If dblCurrentAmount < 0 Then
                MsgBox("You have used more than what is in stock!" & vbCrLf & "This operation will not be completed", MsgBoxStyle.Exclamation, "ERROR!")
                GoTo DISPOSE
            ElseIf dblCurrentAmount = 0 Then
                MsgBox("You have depleted your stock for this item!" & vbCrLf & "Please restock this item!", MsgBoxStyle.Exclamation, "Oops!")
            End If

            ds.Tables("Inventory").Rows(0)("Amount") = dblCurrentAmount
            da.UpdateCommand = New MySQLCommandBuilder(da).GetUpdateCommand
            da.Update(ds, "Inventory")
            MsgBox("Item Updated!", MsgBoxStyle.Information, "Complete!")
        End If
DISPOSE:
            da = Nothing
            ds = Nothing
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try      
End Sub

Then call it from code (A button click would work)

Private Sub btnSalad_Click(sender As Object, e As EventArgs) Handles btnSalad.Click
    Try
        'Get list of ingredients from table
            For Each s As String In lstIngredients 'List(Of String)
                DeductFromTotal(s,Cdbl(txtIngredient1.Text))
            Next
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

This begs the question: How are you binding an ingredient list to the item type?

Are you doing something like this?

Salad,Onion
Salad,Water
Salad,Garlic

The Copy and past of the update code may have caused some unwanted characters. (Due to View Source)

[savedlema]

Thanks.
To answer some questions/clarify more:

• I purchase food raw materials (such as Rice, Oil, Salt, Sugar, Soda, Coffee, Maize etc), I record this in the inventory table

• I record to the system (table menu), types of foods I will be making (we call it menu), menu can be something like (Rice with Fish, Salad, Coffee, Soda, Pizza etc)

• I also record to the system (table recipe) what raw materials makes up each of the menu I specified above, and what amount of the raw material. (look like thise Rice & Fish :20units of Rice, 1 fish, 2 units of salt), this for every other menu/food listed in paragraph 2 above.

• When I sell the food/menu, I want the appropriate amount of its recipes to be substracted from the total that is available in the main Inventory table. So, if I sold 1 Rice&Fish, I want the system to substract 20units of rice, 1fish,2Units of salt from the inventory table. (This is where I get into trouble)

In my trying, I decided to create three tables (Inventory,Menu & Recipe), I am attaching a snapshot of how I designed the tables, please have a look. (Please note that in table recipe, menuID & InvID are foreign key)

Now, FROM THE TABLES (see attached)
If I sell (Click) “Salad” I will run the querry

"SELECT InvID,quantity FROM Recipe WHERE menuID = '" & Me.ComboBoxMenu.Text & "'" '(selected here in this combobox is e.g. "Salad") 

(This give me: Water (20), Salt (30)), according to the values I had specified in the other tables.

Now my problem is, how can I deduct this (Water (20), Salt (30)) from their respective amounts in the Inventory table?

• How can I read what is in the inventory table and substract the two-column results of a querry?
• Can looping be used to do this? This confuses me and I would appreciate some help.

*Sorry if I did not explain myself clearly, and thanks for continued support.

[Begginnerdev]

You could take that string returned, split and parse it to what you need.

For example:

Private Function ParseIngredients(ByVal strIngredients As String) As Dictionary(Of String, Double)
    Try
        strIngredients = "(Water (20), Salt(30))" 'When passing the value in delete this, this is just for example.
        Dim dic As New Dictionary(Of String, Double)

        Dim lstIngredients As List(Of String) = strIngredients.Split(",").ToList()
        'lstIngredients(0) = "(Water (20)"
        'lstIngredients(1) = "Salt (30))"

        For Each s As String In lstIngredients

            Dim Key As String = String.Empty
            Dim Value As Double = 0.0
            s = s.Replace("(", "") 'Will return "Water 20)"
            s = s.Replace(")", "") 'Will return "Water 20"

            Key = s.Split(" ").ToList(0) ' Will return Water
            Value = Cdbl(s.Split(" ").ToList(1)) 'Will return 20.0 as Double
            'The key will be set to Water
            'The value will be set to 20

            'Add to dictionary
            dic.Add(Key, Value)
        Next

        Return dic
    Catch ex As Exception
        MsgBox(ex.ToString)
        Return New Dictionary(Of String, Double)
    End Try
End Function

Just pass in the ingredients string and accept the output dictionary.

Iterating through the dictionary can be done as follows:

For Each kvp As KeyValuePair(Of String,Double) In ParseIngredients(IngredientsStringHere)
    DeductFromTotal(kvp.Key,kvp.Value)
Next

I hope this helps!

[savedlema]

Begginerdev, Thank you again for taking your time to help.

But I must admit, almost everything in that function is new to me!

Just pass in the ingredients string and accept the output dictionary.>
Iterating through the dictionary can be done as follows:

Can you please instruct how to do what you said in the quote? I don't mean to be lazy but, I think may be if you instruct me how get the code work (call the function) will help me to learn this 'Dictionary' thing.
Or, if you think you have links where I can read and get things work that way, I would appreciate too. (I would love both, help me iterate through the dictionary, call the function & give any links if any.)

Sorry, new to that, but I can learn.

[tinstaafl]

Dictionaries are simpler than they sound. Basically a dictionary is a list that instead of accessing individual items by their index number each index is given a name. Thus, just as, a list (MyList(3)) returns the value associated with that index, a dictionary (MyDictionary("vegetable oil")) returns the value associated with that index. In a dictionary each index is called the key and the value associated with it is called, appropriately enough, the value. Therefore each value added to a dictionary must have a key for the dictionary to access it. This is called a KeyValuePair.

On a different note. If your database is only holding the Inventory you might find it easier to use xml, and a datatable directly. The datatable class can load and save directly to/from xml, and would greatly simplify your code.

[Begginnerdev]

Not knowing =/= Laziness.

It is a good thing that you are willing to learn!

[savedlema]

Solved. I solved it by just few lines of a SELECT statement that has other SELECT in it.

Thank you all, you are great!

[tinstaafl]

Please remember to mark this solved thanks