Mercurial > bms / file revision

www/crontasks.php@3be8c2278fd7

www/crontasks.php

Sat, 11 May 2019 23:13:40 +0200

author
Michiel Broek <mbroek@mbse.eu>
date
Sat, 11 May 2019 23:13:40 +0200
changeset 358
3be8c2278fd7
parent 356
36c72e368948
child 379
2aa6addc1853
permissions
-rw-r--r--

Added flags for each ingredient group for the supplies state. On the main screen show the state of the supplies if the product stage is not yet packaged. Calculate the state of the supplies for fermentables, hops, yeasts and miscs. In the grids don't show the inventory values after they are used and reduced. On the fermentables tab show the weight of the grains to mash.

<?php

require_once('config.php');

$escapers = array("\\", "/", "\"", "\n", "\r", "\t", "\x08", "\x0c");
$replacements = array("\\\\", "\\/", "\\\"", "\\n", "\\r", "\\t", "\\f", "\\b");

$connect = mysqli_connect(DBASE_HOST, DBASE_USER, DBASE_PASS, DBASE_NAME);
if (! $connect) {
	die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}
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);
if ($changed > 0) {
	syslog(LOG_NOTICE, "Updated ".$changed." products to stage 7 (Carbonation)");
}

$query = "UPDATE products SET stage=8 WHERE stage = 7 AND DATEDIFF(CURDATE(), package_date) > 13";
$result = mysqli_query($connect, $query);
$changed = mysqli_affected_rows($connect);
if ($changed > 0) {
	syslog(LOG_NOTICE, "Updated ".$changed." products to stage 8 (Mature)");
}

$query = "UPDATE products SET stage=9 WHERE stage = 8 AND DATEDIFF(CURDATE(), package_date) > 41";
$result = mysqli_query($connect, $query);
$changed = mysqli_affected_rows($connect);
if ($changed > 0) {
	syslog(LOG_NOTICE, "Updated ".$changed." products to stage 9 (Taste)");
}


/*
 * 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)) {
	$logfile = "log/fermentation/" . $row['code'] . " " . $row['name'] . ".log";
	if (file_exists($logfile))
		$ok = 1;
	else
		$ok = 0;
	if ($ok != $row['log_fermentation']) {
		$query = "UPDATE products SET log_fermentation='" . $ok . "' WHERE record='" . $row['record'] . "';";
		syslog(LOG_NOTICE, $query);
		$result1 = mysqli_query($connect, $query);
	}
}

Mercurial Repository: bms

mercurial