diff -r 159d7a89fcef -r 2c9cfe2f0860 www/includes/db_product.php --- a/www/includes/db_product.php Mon Dec 24 15:52:11 2018 +0100 +++ b/www/includes/db_product.php Mon Dec 24 23:10:52 2018 +0100 @@ -12,25 +12,55 @@ $escapers = array("\\", "/", "\"", "\n", "\r", "\t", "\x08", "\x0c"); $replacements = array("\\\\", "\\/", "\\\"", "\\n", "\\r", "\\t", "\\f", "\\b"); - +$rescapers = array("'"); +$rreplacements = array("\\'"); +$disallowed = array('visibleindex','uniqueid','boundindex','uid','h_weight','m_weight'); if (isset($_POST['insert']) || isset($_POST['update'])) { if (isset($_POST['insert'])) { - $sql = "INSERT INTO `prod_main` SET "; + $sql = "INSERT INTO `products` SET "; } if (isset($_POST['update'])) { - $sql = "UPDATE `prod_main` SET "; + $sql = "UPDATE `products` SET "; } + + $stage = $_POST['stage']; + if ($stage == 'Plan') + $stageno = 0; + else if ($stage == 'Wait') + $stageno = 1; + else if ($stage == 'Brew') + $stageno = 2; + else if ($stage == 'Primary') + $stageno = 3; + else if ($stage == 'Secondary') + $stageno = 4; + else if ($stage == 'Tertiary') + $stageno = 5; + else if ($stage == 'Package') + $stageno = 6; + else if ($stage == 'Carbonation') + $stageno = 7; + else if ($stage == 'Mature') + $stageno = 8; + else if ($stage == 'Taste') + $stageno = 9; + else if ($stage == 'Ready') + $stageno = 10; + else if ($stage == 'Closed') + $stageno = 11; + // Basic settings - $sql .= "puuid='" . $_POST['puuid']; - $sql .= "', pname='" . mysqli_real_escape_string($connect, $_POST['pname']); + $sql .= "uuid='" . $_POST['uuid']; + $sql .= "', name='" . mysqli_real_escape_string($connect, $_POST['name']); + $sql .= "', code='" . mysqli_real_escape_string($connect, $_POST['code']); $sql .= "', birth='" . $_POST['birth']; $sql .= "', stage='" . $_POST['stage']; - $sql .= "', pnotes='" . mysqli_real_escape_string($connect, $_POST['pnotes']); + $sql .= "', notes='" . mysqli_real_escape_string($connect, $_POST['notes']); ($_POST['log_brew'] == 'true') ? $sql .= "', log_brew='1" : $sql .= "', log_brew='0"; ($_POST['log_fermentation'] == 'true') ? $sql .= "', log_fermentation='1" : $sql .= "', log_fermentation='0"; ($_POST['inventory_reduced'] == 'true') ? $sql .= "', inventory_reduced='1" : $sql .= "', inventory_reduced='0"; - ($_POST['plocked'] == 'true') ? $sql .= "', plocked='1" : $sql .= "', plocked='0"; + ($_POST['locked'] == 'true') ? $sql .= "', locked='1" : $sql .= "', locked='0"; // Equipment $sql .= "', eq_name='" . mysqli_real_escape_string($connect, $_POST['eq_name']); $sql .= "', eq_boil_size='" . $_POST['eq_boil_size']; @@ -57,6 +87,7 @@ $sql .= "', eq_mash_max='" . $_POST['eq_mash_max']; $sql .= "', eq_efficiency='" . $_POST['eq_efficiency']; + if ($stageno >= 2) { // brew_date_start // brew_mash_ph // brew_mash_sg @@ -86,14 +117,26 @@ // brew_fermenter_ibu // brew_date_end // brew_log_available + } + + if ($stageno >= 3) { // primary_start_temp // primary_max_temp // primary_end_temp // primary_end_sg - // primary_end_date + // primary_end_date + } + + if ($stageno >= 4) { // secondary_temp - // secondary_end_date - // tertiary_temp + // secondary_end_date + } + + if ($stageno >= 5) { + // tertiary_temp + } + + if ($stageno >= 6) { // package_date // bottle_amount // bottle_carbonation @@ -107,7 +150,10 @@ // keg_carbonation_temp // keg_forced_carb // keg_pressure - // keg_priming_factor + // keg_priming_factor + } + + if ($stageno >= 9) { // taste_notes // taste_rate // taste_date @@ -117,8 +163,133 @@ // taste_aroma // taste_taste // taste_mouthfeel - // taste_aftertaste + // taste_aftertaste + } + + /* + * Recipe part + */ +// $sql .= "', st_name='" . mysqli_real_escape_string($connect, $_POST['st_name']); +// $sql .= "', st_letter='" . mysqli_real_escape_string($connect, $_POST['st_letter']); +// $sql .= "', st_guide='" . mysqli_real_escape_string($connect, $_POST['st_guide']); +// $sql .= "', st_type='" . mysqli_real_escape_string($connect, $_POST['st_type']); +// $sql .= "', st_category='" . mysqli_real_escape_string($connect, $_POST['st_category']); +// $sql .= "', st_category_number='" . $_POST['st_category_number']; + $sql .= "', st_og_min='" . $_POST['st_og_min']; + $sql .= "', st_og_max='" . $_POST['st_og_max']; + $sql .= "', st_fg_min='" . $_POST['st_fg_min']; + $sql .= "', st_fg_max='" . $_POST['st_fg_max']; + $sql .= "', st_ibu_min='" . $_POST['st_ibu_min']; + $sql .= "', st_ibu_max='" . $_POST['st_ibu_max']; + $sql .= "', st_color_min='" . $_POST['st_color_min']; + $sql .= "', st_color_max='" . $_POST['st_color_max']; + $sql .= "', st_carb_min='" . $_POST['st_carb_min']; + $sql .= "', st_carb_max='" . $_POST['st_carb_max']; + $sql .= "', st_abv_min='" . $_POST['st_abv_min']; + $sql .= "', st_abv_max='" . $_POST['st_abv_max']; + $sql .= "', type='" . $_POST['type']; + $sql .= "', batch_size='" . $_POST['batch_size']; + $sql .= "', boil_size='" . $_POST['boil_size']; + $sql .= "', boil_time='" . $_POST['boil_time']; + $sql .= "', efficiency='" . $_POST['efficiency']; + $sql .= "', est_og='" . $_POST['est_og']; + $sql .= "', est_fg='" . $_POST['est_fg']; + $sql .= "', est_abv='" . $_POST['est_abv']; + $sql .= "', est_carb='" . $_POST['est_carb']; + $sql .= "', est_color='" . $_POST['est_color']; + $sql .= "', color_method='" . $_POST['color_method']; + $sql .= "', est_ibu='" . $_POST['est_ibu']; + $sql .= "', ibu_method='" . $_POST['ibu_method']; + $sql .= "', sparge_temp='" . $_POST['sparge_temp']; + $sql .= "', sparge_ph='" . $_POST['sparge_ph']; + $sql .= "', sparge_volume='" . $_POST['sparge_volume']; +// $sql .= "', sparge_acid_type='" . $_POST['sparge_acid_type']; +// $sql .= "', sparge_acid_perc='" . $_POST['sparge_acid_perc']; +// $sql .= "', sparge_acid_amount='" . $_POST['sparge_acid_amount']; + $sql .= "', mash_ph='" . $_POST['mash_ph']; + $sql .= "', mash_name='" . $_POST['mash_name']; + $sql .= "', calc_acid='" . $_POST['calc_acid']; + if (isset($_POST['w1_name'])) { + $sql .= "', w1_name='" . mysqli_real_escape_string($connect, $_POST['w1_name']); + $sql .= "', w1_amount='" . $_POST['w1_amount']; + $sql .= "', w1_calcium='" . $_POST['w1_calcium']; + $sql .= "', w1_sulfate='" . $_POST['w1_sulfate']; + $sql .= "', w1_chloride='" . $_POST['w1_chloride']; + $sql .= "', w1_sodium='" . $_POST['w1_sodium']; + $sql .= "', w1_magnesium='" . $_POST['w1_magnesium']; + $sql .= "', w1_total_alkalinity='" . $_POST['w1_total_alkalinity']; + $sql .= "', w1_ph='" . $_POST['w1_ph']; + $sql .= "', w1_cost='" . $_POST['w1_cost']; + } + if (isset($_POST['w2_name'])) { + $sql .= "', w2_name='" . mysqli_real_escape_string($connect, $_POST['w2_name']); + $sql .= "', w2_amount='" . $_POST['w2_amount']; + $sql .= "', w2_calcium='" . $_POST['w2_calcium']; + $sql .= "', w2_sulfate='" . $_POST['w2_sulfate']; + $sql .= "', w2_chloride='" . $_POST['w2_chloride']; + $sql .= "', w2_sodium='" . $_POST['w2_sodium']; + $sql .= "', w2_magnesium='" . $_POST['w2_magnesium']; + $sql .= "', w2_total_alkalinity='" . $_POST['w2_total_alkalinity']; + $sql .= "', w2_ph='" . $_POST['w2_ph']; + $sql .= "', w2_cost='" . $_POST['w2_cost']; + } syslog(LOG_NOTICE, $sql); + + if (isset($_POST['fermentables'])) { + $array = $_POST['fermentables']; + foreach($array as $key => $item){ + foreach ($disallowed as $disallowed_key) { + unset($array[$key]["$disallowed_key"]); + } + } + syslog(LOG_NOTICE, "json_fermentables=: ".str_replace($rescapers,$rreplacements,json_encode($array))); + $sql .= "', json_fermentables='" . str_replace($rescapers,$rreplacements,json_encode($array)); + } + + if (isset($_POST['hops'])) { + $array = $_POST['hops']; + foreach($array as $key => $item){ + foreach ($disallowed as $disallowed_key) { + unset($array[$key]["$disallowed_key"]); + } + } + syslog(LOG_NOTICE, "json_hops: ".str_replace($rescapers,$rreplacements,json_encode($array))); + $sql .= "', json_hops='" . str_replace($rescapers,$rreplacements,json_encode($array)); + } + + if (isset($_POST['miscs'])) { + $array = $_POST['miscs']; + foreach($array as $key => $item){ + foreach ($disallowed as $disallowed_key) { + unset($array[$key]["$disallowed_key"]); + } + } + syslog(LOG_NOTICE, "json_miscs: ".str_replace($rescapers,$rreplacements,json_encode($array))); + $sql .= "', json_miscs='" . str_replace($rescapers,$rreplacements,json_encode($array)); + } + + if (isset($_POST['yeasts'])) { + $array = $_POST['yeasts']; + foreach($array as $key => $item){ + foreach ($disallowed as $disallowed_key) { + unset($array[$key]["$disallowed_key"]); + } + } + syslog(LOG_NOTICE, "json_yeasts: ". str_replace($rescapers,$rreplacements,json_encode($array))); + $sql .= "', json_yeasts='" . str_replace($rescapers,$rreplacements,json_encode($array)); + } + + if (isset($_POST['mashs'])) { + $array = $_POST['mashs']; + foreach($array as $key => $item){ + foreach ($disallowed as $disallowed_key) { + unset($array[$key]["$disallowed_key"]); + } + } + syslog(LOG_NOTICE, "json_mashs: ".str_replace($rescapers,$rreplacements,json_encode($array))); + $sql .= "', json_mashs='" . str_replace($rescapers,$rreplacements,json_encode($array)); + } + if (isset($_POST['insert'])) { $sql .= "';"; } @@ -144,17 +315,9 @@ } else if (isset($_POST['delete'])) { /* - * DELETE command, first delete the recipe and then the product. + * DELETE command. */ - $sql = "DELETE FROM `prod_recipes` WHERE uuid='".$_POST['uuid']."';"; - $result = mysqli_query($connect, $sql); - if (! $result) { - syslog(LOG_NOTICE, "db_product: ".$sql." result: ".mysqli_error($connect)); - } else { - syslog(LOG_NOTICE, "db_product: deleted recipe uuid ".$_POST['uuid']); - } - - $sql = "DELETE FROM `prod_main` WHERE puuid='".$_POST['uuid']."';"; + $sql = "DELETE FROM `products` WHERE uuid='".$_POST['uuid']."';"; $result = mysqli_query($connect, $sql); if (! $result) { syslog(LOG_NOTICE, "db_product: ".$sql." result: ".mysqli_error($connect)); @@ -168,12 +331,12 @@ * SELECT, produce a list of products that are not yet Closed. */ if (isset($_GET['select']) && ($_GET['select'] == "inprod")) { - $query = "SELECT record,pname,code,birth,stage FROM prod_main WHERE stage != 'Closed' ORDER BY birth,code;"; + $query = "SELECT record,name,code,birth,stage FROM products WHERE stage != 'Closed' ORDER BY birth,code;"; $result = mysqli_query($connect, $query) or die("SQL Error 1: " . mysqli_error($connect)); while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { $brews[] = array( 'record' => $row['record'], - 'pname' => $row['pname'], + 'name' => $row['name'], 'code' => $row['code'], 'birth' => $row['birth'], 'stage' => $row['stage'] @@ -187,10 +350,9 @@ * Default, select all or a given record. */ if (isset($_GET['record'])) { - $query = "SELECT * FROM prod_main LEFT JOIN prod_recipes ON puuid = uuid WHERE prod_main.record='"; - $query .= $_GET['record'] . "';"; + $query = "SELECT * FROM products WHERE record='" . $_GET['record'] . "';"; } else { - $query = "SELECT * FROM prod_main LEFT JOIN prod_recipes ON puuid = uuid ORDER BY birth,code;"; + $query = "SELECT * FROM products ORDER BY birth,code;"; } $result = mysqli_query($connect, $query) or die("SQL Error 1: " . mysqli_error($connect)); $brews = '['; @@ -201,16 +363,16 @@ $brews .= ','; $comma = TRUE; $brew = '{"record":' . $row['record']; - $brew .= ',"puuid":"' . str_replace($escapers, $replacements, $row['puuid']); - $brew .= '","pname":"' . str_replace($escapers, $replacements, $row['pname']); + $brew .= ',"uuid":"' . str_replace($escapers, $replacements, $row['uuid']); + $brew .= '","name":"' . str_replace($escapers, $replacements, $row['name']); $brew .= '","code":"' . str_replace($escapers, $replacements, $row['code']); $brew .= '","birth":"' . str_replace($escapers, $replacements, $row['birth']); $brew .= '","stage":"' . str_replace($escapers, $replacements, $row['stage']); - $brew .= '","pnotes":"' . str_replace($escapers, $replacements, $row['pnotes']); + $brew .= '","notes":"' . str_replace($escapers, $replacements, $row['notes']); $brew .= '","log_brew":' . $row['log_brew']; $brew .= ',"log_fermentation":' . $row['log_fermentation']; $brew .= ',"inventory_reduced":' . $row['inventory_reduced']; - $brew .= ',"plocked":' . $row['plocked']; + $brew .= ',"locked":' . $row['locked']; $brew .= ',"eq_name":"' . str_replace($escapers, $replacements, $row['eq_name']); $brew .= '","eq_notes":"' . str_replace($escapers, $replacements, $row['eq_notes']); $brew .= '","eq_boil_size":' . floatval($row['eq_boil_size']); @@ -316,8 +478,7 @@ $brew .= ',"st_carb_max":' . floatval($row['st_carb_max']); $brew .= ',"st_abv_min":' . floatval($row['st_abv_min']); $brew .= ',"st_abv_max":' . floatval($row['st_abv_max']); - $brew .= ',"notes":"' . str_replace($escapers, $replacements, $row['notes']); - $brew .= '","type":"' . $row['type']; + $brew .= ',"type":"' . $row['type']; $brew .= '","batch_size":' . floatval($row['batch_size']); $brew .= ',"boil_size":' . floatval($row['boil_size']); $brew .= ',"boil_time":' . floatval($row['boil_time']); @@ -365,6 +526,7 @@ $brew .= ',"yeasts":' . $row['json_yeasts']; $brew .= ',"mashs":' . $row['json_mashs']; $brew .= '}'; +// syslog(LOG_NOTICE, $brew); $brews .= $brew; } $brews .= ']';