Mercurial > bms / file revision

www/crontasks.php@544d7d0183b2

www/crontasks.php

Sun, 02 Jun 2019 12:48:54 +0200

author
Michiel Broek <mbroek@mbse.eu>
date
Sun, 02 Jun 2019 12:48:54 +0200
changeset 392
544d7d0183b2
parent 379
2aa6addc1853
child 393
5020feba78ec
permissions
-rw-r--r--

Added 15 fields to the recipes table. Added 18 fields to the products table. These are calculated values that are now stored in the database so export programs can use these values without calculating them again. Product and recipe print have water and mash schedule added. Product print has brewday results added if the brewday is over. The ingredients layout changed in the product and recipe prints.

<?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