# HG changeset patch # User Michiel Broek # Date 1548279391 -3600 # Node ID f0ec83e1e01f91b3a4befd4a3ed16e7e627289c4 # Parent 63174cff2cc1382078c75f7961d503277e43ea82 Switches yeasts inventory to indexed names and translations. Database save is using POST instead of GET. Redesigned the web page. Added total cost display which updates on inventory changes. diff -r 63174cff2cc1 -r f0ec83e1e01f README.design --- a/README.design Wed Jan 23 19:43:55 2019 +0100 +++ b/README.design Wed Jan 23 22:36:31 2019 +0100 @@ -39,7 +39,7 @@ inventory_miscs Ok. inventory_suppliers Ok. Ok. inventory_water Ok. Ok. -inventory_yeasts Ok. Ok. +inventory_yeasts Ok. Ok. Ok. Ok. Ok. Ok. profile_mash Ok. Ok. profile_profiles Ok. Ok. Ok. profile_styles Ok. Ok. diff -r 63174cff2cc1 -r f0ec83e1e01f www/import/from_brouwhulp.php --- a/www/import/from_brouwhulp.php Wed Jan 23 19:43:55 2019 +0100 +++ b/www/import/from_brouwhulp.php Wed Jan 23 22:36:31 2019 +0100 @@ -223,15 +223,53 @@ foreach ($yeasts->YEAST as $yeast) { $sql = "INSERT INTO inventory_yeasts SET name='" . mysqli_real_escape_string($db, $yeast->NAME); - $sql .= "', type='" . $yeast->TYPE; - $sql .= "', form='" . $yeast->FORM; + + if ($yeast->TYPE == 'Lager') + $sql .= "', type='0"; + else if ($yeast->TYPE == 'Ale') + $sql .= "', type='1"; + else if ($yeast->TYPE == 'Wheat') + $sql .= "', type='2"; + else if ($yeast->TYPE == 'Wine') + $sql .= "', type='3"; + else if ($yeast->TYPE == 'Champagne') + $sql .= "', type='4"; + else + echo "Unknown TYPE " . $yeast->TYPE . PHP_EOL; + + if ($yeast->FORM == 'Liquid') + $sql .= "', form='0"; + else if ($yeast->FORM == 'Dry') + $sql .= "', form='1"; + else if ($yeast->FORM == 'Slant') + $sql .= "', form='2"; + else if ($yeast->FORM == 'Culture') + $sql .= "', form='3"; + else if ($yeast->FORM == 'Frozen') + $sql .= "', form='4"; + else if ($yeast->FORM == 'Bottle') + $sql .= "', form='5"; + else + echo "Unknown FORM " . $yeast->FORM . PHP_EOL; + $sql .= "', laboratory='" . mysqli_real_escape_string($db, $yeast->LABORATORY); $sql .= "', product_id='" . mysqli_real_escape_string($db, $yeast->PRODUCT_ID); if ($yeast->MIN_TEMPERATURE) $sql .= "', min_temperature='" . floatval($yeast->MIN_TEMPERATURE); if ($yeast->MAX_TEMPERATURE) $sql .= "', max_temperature='" . floatval($yeast->MAX_TEMPERATURE); - $sql .= "', flocculation='" . $yeast->FLOCCULATION; + + if ($yeast->FLOCCULATION == 'Low') + $sql .= "', flocculation='0"; + else if ($yeast->FLOCCULATION == 'Medium') + $sql .= "', flocculation='1"; + else if ($yeast->FLOCCULATION == 'High') + $sql .= "', flocculation='2"; + else if ($yeast->FLOCCULATION == 'Very high') + $sql .= "', flocculation='3"; + else + echo "Unknown FLOCCULATION " . $yeast->FLOCCULATION . PHP_EOL; + if ($yeast->ATTENUATION) $sql .= "', attenuation='" . floatval($yeast->ATTENUATION); $sql .= "', notes='" . mysqli_real_escape_string($db, $yeast->NOTES); diff -r 63174cff2cc1 -r f0ec83e1e01f www/includes/db_inventory_yeasts.php --- a/www/includes/db_inventory_yeasts.php Wed Jan 23 19:43:55 2019 +0100 +++ b/www/includes/db_inventory_yeasts.php Wed Jan 23 22:36:31 2019 +0100 @@ -11,74 +11,71 @@ mysqli_set_charset($connect, "utf8" ); // get data and store in a json array -$query = "SELECT * FROM inventory_yeasts ORDER BY laboratory,product_id,name"; -if (isset($_GET['insert'])) { - // INSERT COMMAND - $sql = "INSERT INTO `inventory_yeasts` SET name='" . mysqli_real_escape_string($connect, $_GET['name']); - $sql .= "', type='" . $_GET['type']; - $sql .= "', form='" . $_GET['form']; - $sql .= "', laboratory='" . mysqli_real_escape_string($connect, $_GET['laboratory']); - $sql .= "', product_id='" . mysqli_real_escape_string($connect, $_GET['product_id']); - $sql .= "', min_temperature='" . $_GET['min_temperature']; - $sql .= "', max_temperature='" . $_GET['max_temperature']; - $sql .= "', flocculation='" . $_GET['flocculation']; - $sql .= "', attenuation='" . $_GET['attenuation']; - $sql .= "', notes='" . mysqli_real_escape_string($connect, $_GET['notes']); - $sql .= "', best_for='" . mysqli_real_escape_string($connect, $_GET['best_for']); - $sql .= "', max_reuse='" . $_GET['max_reuse']; - $sql .= "', inventory='" . floatval($_GET['inventory']) / 1000.0; - $sql .= "', cost='" . $_GET['cost']; - $sql .= "', production_date='" . $_GET['production_date']; - $sql .= "', tht_date='" . $_GET['tht_date']; - $sql .= "';"; +if (isset($_POST['insert']) || isset($_POST['update'])) { + if (isset($_POST['insert'])) { + $sql = "INSERT INTO `inventory_yeasts` SET "; + } + if (isset($_POST['update'])) { + $sql = "UPDATE `inventory_yeasts` SET "; + } + + $sql .= "name='" . mysqli_real_escape_string($connect, $_POST['name']); + $sql .= "', type='" . $_POST['type']; + $sql .= "', form='" . $_POST['form']; + $sql .= "', laboratory='" . mysqli_real_escape_string($connect, $_POST['laboratory']); + $sql .= "', product_id='" . mysqli_real_escape_string($connect, $_POST['product_id']); + $sql .= "', min_temperature='" . $_POST['min_temperature']; + $sql .= "', max_temperature='" . $_POST['max_temperature']; + $sql .= "', flocculation='" . $_POST['flocculation']; + $sql .= "', attenuation='" . $_POST['attenuation']; + $sql .= "', notes='" . mysqli_real_escape_string($connect, $_POST['notes']); + $sql .= "', best_for='" . mysqli_real_escape_string($connect, $_POST['best_for']); + $sql .= "', max_reuse='" . $_POST['max_reuse']; + $sql .= "', inventory='" . floatval($_POST['inventory']) / 1000.0; + $sql .= "', cost='" . $_POST['cost'] . "'"; + if ($_POST['production_date'] == '') + $sql .= ", production_date=NULL"; + else + $sql .= ", production_date='" . $_POST['production_date'] . "'"; + if ($_POST['tht_date'] == '') + $sql .= ", tht_date=NULL"; + else + $sql .= ", tht_date='" . $_POST['tht_date'] . "'"; + if (isset($_POST['insert'])) { + $sql .= ";"; + } + if (isset($_POST['update'])) { + $sql .= " WHERE record='" . $_POST['record'] . "';"; + } + syslog(LOG_NOTICE, $sql); + $result = mysqli_query($connect, $sql); if (! $result) { syslog(LOG_NOTICE, "db_inventory_yeasts: ".$sql." result: ".mysqli_error($connect)); } else { - syslog(LOG_NOTICE, "db_inventory_yeasts: inserted ".$_GET['name']); + if (isset($_POST['update'])) { + syslog(LOG_NOTICE, "db_inventory_yeasts: updated record ".$_POST['record']); + } else { + $lastid = mysqli_insert_id($connect); + syslog(LOG_NOTICE, "db_inventory_yeasts: inserted record ".$lastid); + } } echo $result; -} else if (isset($_GET['update'])) { - // UPDATE COMMAND - $sql = "UPDATE `inventory_yeasts` SET name='" . mysqli_real_escape_string($connect, $_GET['name']); - $sql .= "', type='" . $_GET['type']; - $sql .= "', form='" . $_GET['form']; - $sql .= "', laboratory='" . mysqli_real_escape_string($connect, $_GET['laboratory']); - $sql .= "', product_id='" . mysqli_real_escape_string($connect, $_GET['product_id']); - $sql .= "', min_temperature='" . $_GET['min_temperature']; - $sql .= "', max_temperature='" . $_GET['max_temperature']; - $sql .= "', flocculation='" . $_GET['flocculation']; - $sql .= "', attenuation='" . $_GET['attenuation']; - $sql .= "', notes='" . mysqli_real_escape_string($connect, $_GET['notes']); - $sql .= "', best_for='" . mysqli_real_escape_string($connect, $_GET['best_for']); - $sql .= "', max_reuse='" . $_GET['max_reuse']; - $sql .= "', inventory='" . floatval($_GET['inventory']) / 1000.0; - $sql .= "', cost='" . $_GET['cost']; - $sql .= "', production_date='" . $_GET['production_date']; - $sql .= "', tht_date='" . $_GET['tht_date']; - $sql .= "' WHERE record='" . $_GET['record'] . "';"; +} else if (isset($_POST['delete'])) { + // DELETE COMMAND + $sql = "DELETE FROM `inventory_yeasts` WHERE record='".$_POST['record']."';"; $result = mysqli_query($connect, $sql); if (! $result) { syslog(LOG_NOTICE, "db_inventory_yeasts: ".$sql." result: ".mysqli_error($connect)); } else { - syslog(LOG_NOTICE, "db_inventory_yeasts: updated record ".$_GET['record']); - } - echo $result; - -} else if (isset($_GET['delete'])) { - // DELETE COMMAND - $sql = "DELETE FROM `inventory_yeasts` WHERE record='".$_GET['record']."';"; - $result = mysqli_query($connect, $sql); - if (! $result) { - syslog(LOG_NOTICE, "db_inventory_yeasts: ".$sql." result: ".mysqli_error($connect)); - } else { - syslog(LOG_NOTICE, "db_inventory_yeasts: deleted record ".$_GET['record']); + syslog(LOG_NOTICE, "db_inventory_yeasts: deleted record ".$_POST['record']); } echo $result; } else { // SELECT COMMAND + $query = "SELECT * FROM inventory_yeasts ORDER BY laboratory,product_id,name"; $result = mysqli_query($connect, $query) or die("SQL Error 1: " . mysqli_error($connect)); while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { $yeasts[] = array( diff -r 63174cff2cc1 -r f0ec83e1e01f www/inv_yeasts.php --- a/www/inv_yeasts.php Wed Jan 23 19:43:55 2019 +0100 +++ b/www/inv_yeasts.php Wed Jan 23 22:36:31 2019 +0100 @@ -13,62 +13,71 @@