从主库存表中扣除食物配方量？ [英] Deduct food recipe amounts from the main Inventory table?

问题描述

大家好！



我被困在某个地方。我有一个库存表（其中包含每种食品成分的可用量）

我想做什么：如果我选择某种食物（比如沙拉）我希望能够扣除这些成分（数量）来自库存表的'沙拉'。



考虑一下：

沙拉由20mil的水组成，50油和100克洋葱（只是一个例子）

当我给某人一份沙拉时，我希望能够从库存表中扣除20毫升水，50克油和100克洋葱。 （这样我就知道库存中的每件物品剩下多少）



代码是允许更改/删除沙拉的这些食谱/添加其他一些食谱进去。 （灵活性）。我发现这很困难，因为为了实现这种灵活性，我不能只在代码中写下那些不会永远改变的东西。（将来可以添加更多的食谱）



有没有人知道如何做到这一点？



我在VB.NET中使用MySQL



提前致谢！



PS：到目前为止我有什么：

我的表是：

1.库存（项目，金额）

2.菜单（menu_name）

3.沙拉（食谱，金额）

将为每个菜单添加其他表格，就像我为沙拉做的那样。

现在，当我点击Salad时，因为它有两个值（配方和数量），那么怎么样呢我从库存表中的（金额）中减去这些（食谱和金额）？



我发现它具有挑战性。

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

Thanks in advance!

P.S: 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.
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.

推荐答案

我的工作方案有点类似于这个



当然必须有一张桌子哪个w每个项目的病假食谱

1.我们可以在库存表中说明原材料



2.Menu表存储沙拉等最终产品



3.并且应该有另一张桌子将存储每个菜单所需的物品数量（使用超过两张桌子的外键）



如果你有这个结构，你的食谱可以灵活。每次你必须使用内部联接查询来查明你已经消耗了多少股票



________________________________________________________________

让我们说库存表有主要关键PKInvid。

和菜单表（最终产品沙拉）有主键PKMenuId

第三个表将具有这样的结构



CREATE TABLE [dbo]。[Recipe]

（

[PKRecipeid] [int] IDENTITY（1,1）NOT NULL， - 主键

[FK_InvID] INT， - 外键参考库存表（原材料）

[FK_MenuID] INT， - 外键参考菜单表

[数量]浮动， - 需要库存数量



）



此表可以保存信息

FK_MenuID沙拉，FK_InvID水的数量为20

FK_MenuID沙拉，FK_InvID的油，数量为50

FK_MenuID沙拉，FK_InvID水的数量为500



现在订购两个沙拉，你可以使用下面的查询

申报@Order INT - 2个订单

DECLARE @Menu INT - for Salads



最后你必须将这些信息存储在表变量中并写一个while循环来逐行更新每个数量



声明@tab AS TABLE

（

TID身份（1,1）

FK_MenuID INT，

QTY Float

）

INSERT INTO @Tab

选择FK_InvID，Qty * @Order FROM Recipe其中FK_MenuID = @ MenuID

DECLARE @RowCOunt INT，@ i INT = 1

SET @RowCOunt =（选择Count（*）form @tab）



WHILE @ i< @RowCOunt

BEGIN

SET @QTY =（选择QTY FROM @ tab WHERE TID = @i）

SET @FK_MenuID =（SELECT FK_MenuID来自@tab WHERE TID = @i）

UPDATE Invenotry SET QTY = QTY - @QTY，其中MenuID = @FK_MenuID

SET @ i = @ i + 1

END

I have worked on scenario somewhat similar to this

Surely there must be a Table which will store recipes for Each Item
1.Lets Says in Inventory Table stores Raw Material

2.Menu table stores the End product like Salad

3.and there should be another table will store Items needed for each menu with quantity (using foreign key over above two tables)

if you have this structure, your recipes can be flexible. and every time you have to use inner join query to find out how much stock you have consumed

________________________________________________________________
Let’s Say Inventory Table have Primary Key PKInvid.
And Menu table (end Product Salad) have Primary Key PKMenuId
The third table will have structure like this

CREATE TABLE [dbo].[Recipe]
(
[PKRecipeid] [int] IDENTITY(1,1) NOT NULL, -- Primary Key
[FK_InvID] INT, -- Foreign Key refering to Inventory Table (raw material)
[FK_MenuID] INT, -- Foreign Key Refering to Menu table
[Qty] Float , -- Inventory Quntity needed

)

This table can hold information like for
FK_MenuID Salad, FK_InvID of water with Qty as 20
FK_MenuID Salad, FK_InvID of Oil with Qty as 50
FK_MenuID Salad, FK_InvID of water with Qty as 500

Now there is order for two salads, you can use query like below
Declare @Order INT -– for 2 orders
DECLARE @Menu INT – for Salads

Finally you have to store this information in table variable and write a while loop to update each quantity row by row

Declare @tab AS TABLE
(
TID Identity(1,1)
FK_MenuID INT,
QTY Float
)
INSERT INTO @Tab
Select FK_InvID, Qty * @Order FROM Recipe where FK_MenuID = @MenuID
DECLARE @RowCOunt INT,@i INT =1
SET @RowCOunt =(select Count(*) form @tab)

WHILE @i < @RowCOunt
BEGIN
SET @QTY = (SELECT QTY FROM @tab WHERE TID = @i)
SET @FK_MenuID = (SELECT FK_MenuID FROM @tab WHERE TID = @i)
UPDATE Invenotry SET QTY =QTY - @QTY where MenuID = @FK_MenuID
SET @i=@i+1
END

这篇关于从主库存表中扣除食物配方量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！