# HG changeset patch # User Michiel Broek # Date 1557586541 -7200 # Node ID 36c72e368948fa0a7c5f2c3a47d50d1ab974fb8b # Parent 64d6f3c857d7b33b042f60afa4bbd77e1a5f12f0 Reduce inventory is now done in a cron job at moments when there are ingredients used. So the inventory should be uptodate at all times. diff -r 64d6f3c857d7 -r 36c72e368948 www/Makefile --- a/www/Makefile Fri May 10 12:05:03 2019 +0200 +++ b/www/Makefile Sat May 11 16:55:41 2019 +0200 @@ -13,7 +13,7 @@ mon_brewer.php mon_fermenter.php mon_node.php \ prod_archive_code.php prod_archive_date.php prod_archive_name.php prod_beerxml.php \ prod_duplicate.php prod_edit.php prod_export.php prod_forum.php prod_impbrew.php \ - prod_inprod.php prod_new.php prod_print.php prod_reduce.php prod_torecipe.php \ + prod_inprod.php prod_new.php prod_print.php prod_torecipe.php \ profile_fermentation.php profile_mash.php profile_setup.php profile_styles.php \ profile_water.php \ rec_beerxml.php rec_duplicate.php rec_edit.php rec_export.php rec_import.php \ diff -r 64d6f3c857d7 -r 36c72e368948 www/crontasks.php --- a/www/crontasks.php Fri May 10 12:05:03 2019 +0200 +++ b/www/crontasks.php Sat May 11 16:55:41 2019 +0200 @@ -12,6 +12,527 @@ mysqli_set_charset($connect, "utf8" ); syslog(LOG_NOTICE, "crontasks.php started"); + + +/* + * Upgrade inventory_reduced value from old boolean to tiny integer value. + */ +$query = "UPDATE products SET inventory_reduced=stage WHERE inventory_reduced = 1"; +$result = mysqli_query($connect, $query); +$changed = mysqli_affected_rows($connect); +if ($changed > 0) { + syslog(LOG_NOTICE, "Updated ".$changed." products to new inventory_reduced value"); +} + + + +/* + * 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"; $result = mysqli_query($connect, $query); $changed = mysqli_affected_rows($connect); @@ -34,6 +555,9 @@ } +/* + * Check fementation logs. + */ $query = "SELECT record,code,name,log_brew,log_fermentation FROM products;"; $result = mysqli_query($connect, $query); while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { diff -r 64d6f3c857d7 -r 36c72e368948 www/js/prod_edit.js --- a/www/js/prod_edit.js Fri May 10 12:05:03 2019 +0200 +++ b/www/js/prod_edit.js Sat May 11 16:55:41 2019 +0200 @@ -1899,16 +1899,12 @@ $("#primary_end_brix").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); $("#primary_end_date").jqxDateTimeInput({ disabled: true }); } - if (dataRecord.stage < 6) { // Not yet packaged - $("#inventory_reduced").jqxCheckBox({ disabled : true }); - } else { + if (dataRecord.stage >= 6) { // Packaged $("#secondary_temp").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); $("#secondary_end_date").jqxDateTimeInput({ disabled: true }); $("#tertiary_temp").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); $("#fg").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); $("#final_brix").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); - if ($('#inventory_reduced').jqxCheckBox('checked')) - $("#inventory_reduced").jqxCheckBox({ disabled : true }); $("#package_date").jqxDateTimeInput({ disabled: true }); $("#bottle_amount").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); $("#keg_amount").jqxNumberInput({ spinButtons: false, readOnly: true, width: 90 }); @@ -2854,7 +2850,6 @@ $("#birth").val(dataRecord.birth); $("#stage").val(StageData[dataRecord.stage].nl); $("#notes").val(dataRecord.notes); - $("#inventory_reduced").val(dataRecord.inventory_reduced); $("#locked").val(dataRecord.locked); $("#eq_name").val(dataRecord.eq_name); $("#eq_notes").val(dataRecord.eq_notes); @@ -4032,29 +4027,6 @@ $("#birth").jqxInput({ theme: theme, width: 120, height: 23 }); $("#stage").jqxTooltip({ content: 'De productie fase van dit product.' }); $("#stage").jqxInput({ theme: theme, width: 100, height: 23 }); - $("#inventory_reduced").jqxCheckBox({ theme: theme, width: 120, height: 23 }); - $('#inventory_reduced').on('checked', function (event) { - if (dataRecord.inventory_reduced == 0) { - saveRecord(); - var data = "reduce=1&uuid="+dataRecord.uuid+"&record="+my_record; - var url = "prod_reduce.php"; - $.ajax({ - dataType: 'json', - url: url, - cache: false, - data: data, - type: "POST", - success: function (data, status, xhr) { - console.log("success"); - window.location.href = my_return; - }, - error: function(jqXHR, textStatus, errorThrown) { - console.log("error"); - window.location.href = my_return; - } - }); - } - }); $("#locked").jqxCheckBox({ theme: theme, width: 120, height: 23, disabled : true }); $('#locked').on('checked', function (event) { if (dataRecord.stage >= 10) { diff -r 64d6f3c857d7 -r 36c72e368948 www/prod_edit.php --- a/www/prod_edit.php Fri May 10 12:05:03 2019 +0200 +++ b/www/prod_edit.php Sat May 11 16:55:41 2019 +0200 @@ -45,8 +45,7 @@
Brouwzaal rendement:
- Ingredienten afgeboekt: -
+ Brouw volume: diff -r 64d6f3c857d7 -r 36c72e368948 www/prod_reduce.php --- a/www/prod_reduce.php Fri May 10 12:05:03 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,209 +0,0 @@ -= " . $fermentables[$i]['f_amount']; - $sql2 .= " LIMIT 1;"; -// syslog(LOG_NOTICE, $sql2); - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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, "Reduced fermentable `".$fermentables[$i]['f_name']."' from `".$fermentables[$i]['f_supplier']."' failed"); - } -// syslog(LOG_NOTICE, "affected rows: ".$ar); - } - } - - $hops = json_decode($row['json_hops'], true); - for ($i = 0; $i < count($hops); $i++) { - - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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, "Reduced 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++) { - - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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, "Reduced misc `".$miscs[$i]['m_name']."' failed"); - } - } - } - - $yeasts = json_decode($row['json_yeasts'], true); - for ($i = 0; $i < count($yeasts); $i++) { - - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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, "Reduced yeast `".$yeasts[$i]['y_product_id'].' '.$yeasts[$i]['y_name']."' from `".$yeasts[$i]['y_laboratory']."' 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;"; -// syslog(LOG_NOTICE, $sql2); - $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;"; -// syslog(LOG_NOTICE, $sql2); - $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, "Reduced water `".$row['w1_name']."' failed or 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;"; -// syslog(LOG_NOTICE, $sql2); - $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;"; -// syslog(LOG_NOTICE, $sql2); - $result2 = mysqli_query($connect, $sql2); - $ar = mysqli_affected_rows($connect); - if ($ar == 1) { - syslog(LOG_NOTICE, "Reduced water `".$row['w2_name']."' to 0 liters"); - } - } - } - - syslog(LOG_NOTICE, "Finished reducing inventory from ". $row['code'].' '.$row['name']); - mysqli_free_result($result1); - - $sql1 = "UPDATE products SET inventory_reduced=1 WHERE uuid = '" . $_POST['uuid'] . "';"; - //syslog(LOG_NOTICE, $sql1); - $result1 = mysqli_query($connect, $sql1); - $ar = mysqli_affected_rows($connect); -} - -exit(0);