changeset
Moved the reduce inventory code from the crontask to the db_productsphp file so the reduce is done only after a save of a record.
Sun, 02 Jun 2019 19:56:44 +0200
- author
- Michiel Broek <mbroek@mbse.eu>
- date
- Sun, 02 Jun 2019 19:56:44 +0200
- changeset 393
- 5020feba78ec
- parent 392
- 544d7d0183b2
- child 394
- ccd85f0ed96c
Moved the reduce inventory code from the crontask to the db_productsphp file so the reduce is done only after a save of a record.
| README.design | file | annotate | diff | comparison | revisions | |
| www/crontasks.php | file | annotate | diff | comparison | revisions | |
| www/includes/db_product.php | file | annotate | diff | comparison | revisions |
--- a/README.design Sun Jun 02 12:48:54 2019 +0200 +++ b/README.design Sun Jun 02 19:56:44 2019 +0200 @@ -22,3 +22,5 @@ Koolzuurdruk tabel inbouwen. +Fix grid popup edit updates in products and recipes. +Also miscs during fermentation cannot have a time value.
--- a/www/crontasks.php Sun Jun 02 12:48:54 2019 +0200 +++ b/www/crontasks.php Sun Jun 02 19:56:44 2019 +0200 @@ -27,510 +27,6 @@ /* - * Automatic reduce inventory depending on the production stage. - * Stage 3+, primary, reduce sugars(0-mash, 1-boil), hops(0-mash, 1-fwh, 2-boil, 3-aroma, 4-whirlpool), miscs(0-starter, 1-mash, 2-boil) - * Stage 4+, Secondary, reduce sugars(2-fermention), yeasts(0-Primary), miscs(3-primary) - * Stage 5+, Tertiary, reduce yeasts(1-Secondary) - * Stage 6+, packaged, reduce sugars(3-lagering), hops(5-dry-hop), yeasts(2-Tertiary), miscs(4-secondary) - * Stage 7+, carbonatiom, reduce sugars(4-bottle), yeasts(3-Bottle), miscs(5-bottling) - */ -$query = "SELECT * FROM products WHERE inventory_reduced < stage"; -$result = mysqli_query($connect, $query); -while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { - - $savethis = 0; - - /* - * If the brew is done, reduce the used ingredients. - */ - if (($row['stage'] >= 3) && ($row['inventory_reduced'] < 3)) { - syslog(LOG_NOTICE, "Reduce brew inventory from " . $row['code'] . " " . $row['name']); - - $fermentables = json_decode($row['json_fermentables'], true); - for ($i = 0; $i < count($fermentables); $i++) { - if ($fermentables[$i]['f_added'] <= 1) { // Mash, Boil - $sql2 = "UPDATE inventory_fermentables SET inventory = inventory - " . $fermentables[$i]['f_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_origin']); - $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_supplier']); - $sql2 .= "' AND inventory >= " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' with ".$fermentables[$i]['f_amount']." kg"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_fermentables SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_origin']); - $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_supplier']); - $sql2 .= "' AND inventory < " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' to 0 kg"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' failed"); - } - } - } - } - - $hops = json_decode($row['json_hops'], true); - for ($i = 0; $i < count($hops); $i++) { - if ($hops[$i]['h_useat'] <= 4) { // Mash, FWH, Boil, Flameout, Whirlpool - $sql2 = "UPDATE inventory_hops SET inventory = inventory - " . $hops[$i]['h_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $hops[$i]['h_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $hops[$i]['h_origin']); - $sql2 .= "' AND form=" . $hops[$i]['h_form']; - $sql2 .= " AND inventory >= " . $hops[$i]['h_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced hop `".$hops[$i]['h_name']."' from `".$hops[$i]['h_origin']."' with ".$hops[$i]['h_amount']." kg"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_hops SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $hops[$i]['h_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $hops[$i]['h_origin']); - $sql2 .= "' AND form=" . $hops[$i]['h_form']; - $sql2 .= " AND inventory < " . $hops[$i]['h_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced hop `".$hops[$i]['h_name']."' from `".$hops[$i]['h_origin']."' to 0 kg"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce hop `".$hops[$i]['h_name']."' from `".$hops[$i]['h_origin']."' failed"); - } - } - } - } - - $miscs = json_decode($row['json_miscs'], true); - for ($i = 0; $i < count($miscs); $i++) { - if ($miscs[$i]['m_use_use'] <= 2) { // Starter, Mash, Boil - $sql2 = "UPDATE inventory_miscs SET inventory = inventory - " . $miscs[$i]['m_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory >= " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' with ".$miscs[$i]['m_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_miscs SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory < " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce misc `".$miscs[$i]['m_name']."' failed"); - } - } - } - } - - if ($row['w1_name'] != '') { - $sql2 = "UPDATE inventory_waters SET inventory = inventory - ".$row['w1_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $row['w1_name']); - $sql2 .= "' AND unlimited_stock=0 AND inventory >= ".$row['w1_amount']." LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced water `".$row['w1_name']."' with ".$row['w1_amount']." liter"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_waters SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $row['w1_name']); - $sql2 .= "' AND unlimited_stock=0 AND inventory < ".$row['w1_amount']." LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced water `".$row['w1_name']."' to 0 liters"); - } else { - syslog(LOG_NOTICE, "Reduce water `".$row['w1_name']."' not reduced is maybe tapwater"); - } - } - } - if ($row['w2_name'] != '') { - $sql2 = "UPDATE inventory_waters SET inventory = inventory - ".$row['w2_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $row['w2_name']); - $sql2 .= "' AND unlimited_stock=0 AND inventory >= ".$row['w2_amount']." LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced water `".$row['w2_name']."' with ".$row['w2_amount']." liter"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_waters SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $row['w2_name']); - $sql2 .= "' AND unlimited_stock=0 AND inventory < ".$row['w2_amount']." LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced water `".$row['w2_name']."' to 0 liters"); - } else { - syslog(LOG_NOTICE, "Reduce water `".$row['w2_name']."' failed"); - } - } - } - - $row['inventory_reduced'] = '3'; - $savethis = 1; - } - - - /* - * After the Primary fermentation - */ - if (($row['stage'] >= 4) && ($row['inventory_reduced'] < 4)) { - syslog(LOG_NOTICE, "Reduce Primary inventory from " . $row['code'] . " " . $row['name']); - - $fermentables = json_decode($row['json_fermentables'], true); - for ($i = 0; $i < count($fermentables); $i++) { - if ($fermentables[$i]['f_added'] == 2) { // Fermentation - $sql2 = "UPDATE inventory_fermentables SET inventory = inventory - " . $fermentables[$i]['f_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_origin']); - $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_supplier']); - $sql2 .= "' AND inventory >= " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' with ".$fermentables[$i]['f_amount']." kg"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_fermentables SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_origin']); - $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_supplier']); - $sql2 .= "' AND inventory < " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' to 0 kg"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' failed"); - } - } - } - } - - $miscs = json_decode($row['json_miscs'], true); - for ($i = 0; $i < count($miscs); $i++) { - if ($miscs[$i]['m_use_use'] == 3) { // Fermentation - $sql2 = "UPDATE inventory_miscs SET inventory = inventory - " . $miscs[$i]['m_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory >= " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' with ".$miscs[$i]['m_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_miscs SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory < " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce misc `".$miscs[$i]['m_name']."' failed"); - } - } - } - } - - $yeasts = json_decode($row['json_yeasts'], true); - for ($i = 0; $i < count($yeasts); $i++) { - if ($yeasts[$i]['y_use'] == 0) { // Primary - $sql2 = "UPDATE inventory_yeasts SET inventory = inventory - " . $yeasts[$i]['y_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory >= " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' with ".$yeasts[$i]['y_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_yeasts SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory < " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' failed"); - } - } - } - } - - $row['inventory_reduced'] = '4'; - $savethis = 1; - } - - - /* - * After the Seconday fermentation - */ - if (($row['stage'] >= 5) && ($row['inventory_reduced'] < 5)) { - syslog(LOG_NOTICE, "Reduce Secondary inventory from " . $row['code'] . " " . $row['name']); - - $yeasts = json_decode($row['json_yeasts'], true); - for ($i = 0; $i < count($yeasts); $i++) { - if ($yeasts[$i]['y_use'] == 1) { // Secondary - $sql2 = "UPDATE inventory_yeasts SET inventory = inventory - " . $yeasts[$i]['y_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory >= " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' with ".$yeasts[$i]['y_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_yeasts SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory < " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' failed"); - } - } - } - } - $row['inventory_reduced'] = '5'; - $savethis = 1; - } - - - /* - * After the Tertiary fermentation - */ - if (($row['stage'] >= 6) && ($row['inventory_reduced'] < 6)) { - syslog(LOG_NOTICE, "Reduce Tertiary inventory from " . $row['code'] . " " . $row['name']); - - $fermentables = json_decode($row['json_fermentables'], true); - for ($i = 0; $i < count($fermentables); $i++) { - if ($fermentables[$i]['f_added'] == 3) { // Lagering - $sql2 = "UPDATE inventory_fermentables SET inventory = inventory - " . $fermentables[$i]['f_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_origin']); - $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_supplier']); - $sql2 .= "' AND inventory >= " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' with ".$fermentables[$i]['f_amount']." kg"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_fermentables SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_origin']); - $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $fermentables[$i]['f_supplier']); - $sql2 .= "' AND inventory < " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' to 0 kg"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' failed"); - } - } - } - } - - $hops = json_decode($row['json_hops'], true); - for ($i = 0; $i < count($hops); $i++) { - if ($hops[$i]['h_useat'] == 5) { // Dry hop - $sql2 = "UPDATE inventory_hops SET inventory = inventory - " . $hops[$i]['h_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $hops[$i]['h_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $hops[$i]['h_origin']); - $sql2 .= "' AND form=" . $hops[$i]['h_form']; - $sql2 .= " AND inventory >= " . $hops[$i]['h_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced hop `".$hops[$i]['h_name']."' from `".$hops[$i]['h_origin']."' with ".$hops[$i]['h_amount']." kg"); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_hops SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $hops[$i]['h_name']); - $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $hops[$i]['h_origin']); - $sql2 .= "' AND form=" . $hops[$i]['h_form']; - $sql2 .= " AND inventory < " . $hops[$i]['h_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced hop `".$hops[$i]['h_name']."' from `".$hops[$i]['h_origin']."' to 0 kg"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce hop `".$hops[$i]['h_name']."' from `".$hops[$i]['h_origin']."' failed"); - } - } - } - } - - $yeasts = json_decode($row['json_yeasts'], true); - for ($i = 0; $i < count($yeasts); $i++) { - if ($yeasts[$i]['y_use'] == 2) { // Tertiary - $sql2 = "UPDATE inventory_yeasts SET inventory = inventory - " . $yeasts[$i]['y_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory >= " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' with ".$yeasts[$i]['y_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_yeasts SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory < " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' failed"); - } - } - } - } - - $miscs = json_decode($row['json_miscs'], true); - for ($i = 0; $i < count($miscs); $i++) { - if ($miscs[$i]['m_use_use'] == 4) { // Secondary or Tertiary - $sql2 = "UPDATE inventory_miscs SET inventory = inventory - " . $miscs[$i]['m_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory >= " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' with ".$miscs[$i]['m_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_miscs SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory < " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce misc `".$miscs[$i]['m_name']."' failed"); - } - } - } - } - - $row['inventory_reduced'] = '6'; - $savethis = 1; - } - - - /* - * After packaging - * reduce sugars(4-bottle), yeasts(3-Bottle), miscs(5-bottling) - */ - if (($row['stage'] >= 7) && ($row['inventory_reduced'] < 7)) { - syslog(LOG_NOTICE, "Reduce Packaging inventory from " . $row['code'] . " " . $row['name']); - - // Bottle sugar, how? - - $yeasts = json_decode($row['json_yeasts'], true); - for ($i = 0; $i < count($yeasts); $i++) { - if ($yeasts[$i]['y_use'] == 3) { // Bottle - $sql2 = "UPDATE inventory_yeasts SET inventory = inventory - " . $yeasts[$i]['y_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory >= " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' with ".$yeasts[$i]['y_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_yeasts SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_name']); - $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_laboratory']); - $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $yeasts[$i]['y_product_id']); - $sql2 .= "' AND form=" . $yeasts[$i]['y_form']; - $sql2 .= " AND inventory < " . $yeasts[$i]['y_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' failed"); - } - } - } - } - - $miscs = json_decode($row['json_miscs'], true); - for ($i = 0; $i < count($miscs); $i++) { - if ($miscs[$i]['m_use_use'] == 5) { // Bottle - $sql2 = "UPDATE inventory_miscs SET inventory = inventory - " . $miscs[$i]['m_amount']; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory >= " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' with ".$miscs[$i]['m_amount']); - } else if ($ar == 0) { - $sql2 = "UPDATE inventory_miscs SET inventory = 0"; - $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $miscs[$i]['m_name']); - $sql2 .= "' AND inventory < " . $miscs[$i]['m_amount'] . " LIMIT 1;"; - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced misc `".$miscs[$i]['m_name']."' to 0"); - } else if ($ar == 0) { - syslog(LOG_NOTICE, "Reduce misc `".$miscs[$i]['m_name']."' failed"); - } - } - } - } - - $row['inventory_reduced'] = $row['stage']; - $savethis = 1; - } - - /* - * Save only if something was reduced. - */ - if ($savethis == 1) { - $sql2 = "UPDATE products SET inventory_reduced=".$row['inventory_reduced']." WHERE uuid = '" . $row['uuid'] . "';"; - syslog(LOG_NOTICE, $sql2); - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar != 1) { - syslog(LOG_NOTICE, $sql2." error, affected rows: ".$ar); - } - } -} - - - -/* * Update stages after packaging depending on the age. */ $query = "UPDATE products SET stage=7 WHERE stage = 6 AND DATEDIFF(CURDATE(), package_date) > 0";
--- a/www/includes/db_product.php Sun Jun 02 12:48:54 2019 +0200 +++ b/www/includes/db_product.php Sun Jun 02 19:56:44 2019 +0200 @@ -421,6 +421,7 @@ } else { if (isset($_POST['update'])) { syslog(LOG_NOTICE, "db_product: updated record ".$_POST['record']); + inventory_reduce(); } else { $lastid = mysqli_insert_id($connect); syslog(LOG_NOTICE, "db_product: inserted record ".$lastid); @@ -861,4 +862,384 @@ header("Content-type: application/json"); echo $brews; } + + + +function reduce_fermentables($item) { + + global $connect; + + $amount = round($item['f_amount'] * 1000) / 1000; + $sql2 = "UPDATE inventory_fermentables SET inventory = inventory - " . $amount; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['f_name']); + $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $item['f_origin']); + $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $item['f_supplier']); + $sql2 .= "' AND inventory >= " . $amount; + $sql2 .= " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced fermentable `".$item['f_name']."' from `".$item['f_supplier']."' with ".$amount." kg"); + } else if ($ar == 0) { + $sql2 = "UPDATE inventory_fermentables SET inventory = 0"; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['f_name']); + $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $item['f_origin']); + $sql2 .= "' AND supplier='" . mysqli_real_escape_string($connect, $item['f_supplier']); + $sql2 .= "' AND inventory < " . $amount; + $sql2 .= " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced fermentable `".$item['f_name']."' from `".$item['f_supplier']."' to 0 kg"); + } else if ($ar == 0) { + syslog(LOG_NOTICE, "Reduce fermentable `".$item['f_name']."' from `".$item['f_supplier']."' failed"); + } + } +} + + + +function reduce_hops($item) { + + global $connect; + + $amount = round($item['h_amount'] * 1000) / 1000; + $sql2 = "UPDATE inventory_hops SET inventory = inventory - " . $amount; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['h_name']); + $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $item['h_origin']); + $sql2 .= "' AND form=" . $item['h_form']; + $sql2 .= " AND inventory >= " . $amount . " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced hop `".$item['h_name']."' from `".$item['h_origin']."' with ".$amount." kg"); + } else if ($ar == 0) { + $sql2 = "UPDATE inventory_hops SET inventory = 0"; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['h_name']); + $sql2 .= "' AND origin='" . mysqli_real_escape_string($connect, $item['h_origin']); + $sql2 .= "' AND form=" . $item['h_form']; + $sql2 .= " AND inventory < " . $amount . " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced hop `".$item['h_name']."' from `".$item['h_origin']."' to 0 kg"); + } else if ($ar == 0) { + syslog(LOG_NOTICE, "Reduce hop `".$item['h_name']."' from `".$item['h_origin']."' failed"); + } + } +} + + + +function reduce_miscs($item) { + + global $connect; + + $amount = round($item['m_amount'] * 100000) / 100000; + $sql2 = "UPDATE inventory_miscs SET inventory = inventory - " . $amount; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['m_name']); + $sql2 .= "' AND inventory >= " . $amount . " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced misc `".$item['m_name']."' with ".$amount); + } else if ($ar == 0) { + $sql2 = "UPDATE inventory_miscs SET inventory = 0"; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['m_name']); + $sql2 .= "' AND inventory < " . $amount . " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced misc `".$item['m_name']."' to 0"); + } else if ($ar == 0) { + syslog(LOG_NOTICE, "Reduce misc `".$item['m_name']."' failed"); + } + } +} + + + +function reduce_yeast($item) { + + global $connect; + + $amount = round($item['y_amount'] * 10000) / 10000; + $sql2 = "UPDATE inventory_yeasts SET inventory = inventory - " . $amount; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['y_name']); + $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $item['y_laboratory']); + $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $item['y_product_id']); + $sql2 .= "' AND form=" . $item['y_form']; + $sql2 .= " AND inventory >= " . $amount . " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced yeast `".$item['y_product_id'].' '.$item['y_name']."' from `".$item['y_laboratory']."' with ".$amount); + } else if ($ar == 0) { + $sql2 = "UPDATE inventory_yeasts SET inventory = 0"; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $item['y_name']); + $sql2 .= "' AND laboratory='" . mysqli_real_escape_string($connect, $item['y_laboratory']); + $sql2 .= "' AND product_id='" . mysqli_real_escape_string($connect, $item['y_product_id']); + $sql2 .= "' AND form=" . $item['y_form']; + $sql2 .= " AND inventory < " . $amount . " LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced yeast `".$item['y_product_id'].' '.$item['y_name']."' from `".$item['y_laboratory']."' to 0"); + } else if ($ar == 0) { + syslog(LOG_NOTICE, "Reduce yeast `".$item['y_product_id'].' '.$item['y_name']."' from `".$item['y_laboratory']."' failed"); + } + } +} + + + +/* + * Reduce inventory depending on the production stage. + * Stage 3+, after brew, reduce sugars(0-mash, 1-boil), hops(0-mash, 1-fwh, 2-boil, 3-aroma, 4-whirlpool), miscs(0-starter, 1-mash, 2-boil) + * Stage 4+, after primary, reduce sugars(2-fermention), yeasts(0-Primary), miscs(3-primary) + * Stage 5+, after secondary, reduce yeasts(1-Secondary) + * Stage 6+, after tertiary, reduce sugars(3-lagering), hops(5-dry-hop), yeasts(2-Tertiary), miscs(4-secondary) + * Stage 7+, after packaging, reduce sugars(4-bottle), yeasts(3-Bottle), miscs(5-bottling) + */ +function inventory_reduce() { + + global $connect; + + $savethis = 0; + $stage = $_POST['stage']; + $inventory_reduced = $_POST['inventory_reduced']; + syslog(LOG_NOTICE, "inventory_reduce() stage: ".$stage." inventory_reduced: ".$inventory_reduced); + + /* + * If the brew is done, reduce the used ingredients. + */ + if (($stage >= 3) && ($inventory_reduced < 3)) { + syslog(LOG_NOTICE, "Reduce brew inventory from " . $_POST['code'] . " " . $_POST['name']); + + if (isset($_POST['fermentables'])) { + $array = $_POST['fermentables']; + foreach($array as $key => $item) { + if ($item['f_added'] <= 1) { // Mash, Boil + reduce_fermentables($item); + } + } + } + + if (isset($_POST['hops'])) { + $array = $_POST['hops']; + foreach($array as $key => $item) { + if ($item['h_useat'] <= 4) { // Mash, FWH, Boil, Flameout, Whirlpool + reduce_hops($item); + } + } + } + + if (isset($_POST['miscs'])) { + $array = $_POST['miscs']; + foreach($array as $key => $item) { + if ($item['m_use_use'] <= 2) { // Starter, Mash, Boil + reduce_miscs($item); + } + } + } + + if ($_POST['w1_name'] != '') { + $sql2 = "UPDATE inventory_waters SET inventory = inventory - ".$_POST['w1_amount']; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $_POST['w1_name']); + $sql2 .= "' AND unlimited_stock=0 AND inventory >= ".$_POST['w1_amount']." LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced water `".$_POST['w1_name']."' with ".$_POST['w1_amount']." liter"); + } else if ($ar == 0) { + $sql2 = "UPDATE inventory_waters SET inventory = 0"; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $_POST['w1_name']); + $sql2 .= "' AND unlimited_stock=0 AND inventory < ".$_POST['w1_amount']." LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced water `".$_POST['w1_name']."' to 0 liters"); + } else { + syslog(LOG_NOTICE, "Reduce water `".$_POST['w1_name']."' not reduced is maybe tapwater"); + } + } + } + if ($_POST['w2_name'] != '') { + $sql2 = "UPDATE inventory_waters SET inventory = inventory - ".$_POST['w2_amount']; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $_POST['w2_name']); + $sql2 .= "' AND unlimited_stock=0 AND inventory >= ".$_POST['w2_amount']." LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced water `".$_POST['w2_name']."' with ".$_POST['w2_amount']." liter"); + } else if ($ar == 0) { + $sql2 = "UPDATE inventory_waters SET inventory = 0"; + $sql2 .= " WHERE name='" . mysqli_real_escape_string($connect, $_POST['w2_name']); + $sql2 .= "' AND unlimited_stock=0 AND inventory < ".$_POST['w2_amount']." LIMIT 1;"; + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar == 1) { + syslog(LOG_NOTICE, "Reduced water `".$_POST['w2_name']."' to 0 liters"); + } else { + syslog(LOG_NOTICE, "Reduce water `".$_POST['w2_name']."' failed"); + } + } + } + + $inventory_reduced = 3; + $savethis = 1; + } + + /* + * After the Primary fermentation + */ + if (($stage >= 4) && ($inventory_reduced < 4)) { + syslog(LOG_NOTICE, "Reduce Primary inventory from " . $_POST['code'] . " " . $_POST['name']); + + if (isset($_POST['fermentables'])) { + $array = $_POST['fermentables']; + foreach($array as $key => $item) { + if ($item['f_added'] == 2) { // Fermentation + reduce_fermentables($item); + } + } + } + + if (isset($_POST['miscs'])) { + $array = $_POST['miscs']; + foreach($array as $key => $item) { + if ($item['m_use_use'] == 3) { // Fermentation + reduce_miscs($item); + } + } + } + + if (isset($_POST['yeasts'])) { + $array = $_POST['yeasts']; + foreach($array as $key => $item) { + if ($item['y_use'] == 0) { // Primary + reduce_yeast($item); + } + } + } + + $inventory_reduced = 4; + $savethis = 1; + } + + + /* + * After the Seconday fermentation + */ + if (($stage >= 5) && ($inventory_reduced < 5)) { + syslog(LOG_NOTICE, "Reduce Secondary inventory from " . $_POST['code'] . " " . $_POST['name']); + + if (isset($_POST['yeasts'])) { + $array = $_POST['yeasts']; + foreach($array as $key => $item) { + if ($item['y_use'] == 1) { // Secondary + reduce_yeast($item); + } + } + } + + $inventory_reduced = 5; + $savethis = 1; + } + + + /* + * After the Tertiary fermentation + */ + if (($stage >= 6) && ($inventory_reduced < 6)) { + syslog(LOG_NOTICE, "Reduce Tertiary inventory from " . $_POST['code'] . " " . $_POST['name']); + + if (isset($_POST['fermentables'])) { + $array = $_POST['fermentables']; + foreach($array as $key => $item) { + if ($item['f_added'] == 3) { // Lagering + reduce_fermentables($item); + } + } + } + + if (isset($_POST['hops'])) { + $array = $_POST['hops']; + foreach($array as $key => $item) { + if ($item['h_useat'] == 5) { // Dry hop + reduce_hops($item); + } + } + } + + if (isset($_POST['yeasts'])) { + $array = $_POST['yeasts']; + foreach($array as $key => $item) { + if ($item['y_use'] == 2) { // Tertiary + reduce_yeast($item); + } + } + } + + if (isset($_POST['miscs'])) { + $array = $_POST['miscs']; + foreach($array as $key => $item) { + if ($item['m_use_use'] == 4) { // Secondary or Tertiary + reduce_miscs($item); + } + } + } + + $inventory_reduced = 6; + $savethis = 1; + } + + /* + * After packaging + * reduce sugars(4-bottle), yeasts(3-Bottle), miscs(5-bottling) + */ + if (($stage >= 6) && ($inventory_reduced < 7)) { + syslog(LOG_NOTICE, "Reduce Packaging inventory from " . $_POST['code'] . " " . $_POST['name']); + + // Bottle sugar, how? + + if (isset($_POST['yeasts'])) { + $array = $_POST['yeasts']; + foreach($array as $key => $item) { + if ($item['y_use'] == 3) { // Bottle + reduce_yeast($item); + } + } + } + + if (isset($_POST['miscs'])) { + $array = $_POST['miscs']; + foreach($array as $key => $item) { + if ($item['m_use_use'] == 5) { // Bottle + reduce_miscs($item); + } + } + } + + if ($stage < 7) + $stage = 7; + $inventory_reduced = $stage; + $savethis = 1; + } + + /* + * Save only if something was reduced. + */ + if ($savethis == 1) { + $sql2 = "UPDATE products SET stage=".$stage.", inventory_reduced=".$inventory_reduced." WHERE record='".$_POST['record']."';"; + syslog(LOG_NOTICE, $sql2); + $result2 = mysqli_query($connect, $sql2); + $ar = mysqli_affected_rows($connect); + if ($ar != 1) { + syslog(LOG_NOTICE, $sql2." error, affected rows: ".$ar); + } + } +} + + ?>
