--- 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);
+ }
+ }
+}
+
+
?>