www/crontasks.php

Sat, 11 May 2019 16:55:41 +0200

author
Michiel Broek <mbroek@mbse.eu>
date
Sat, 11 May 2019 16:55:41 +0200
changeset 356
36c72e368948
parent 354
2e372eeba04b
child 379
2aa6addc1853
permissions
-rw-r--r--

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.

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