changeset
Import products uses indexes.
Sat, 02 Feb 2019 20:49:41 +0100
- author
- Michiel Broek <mbroek@mbse.eu>
- date
- Sat, 02 Feb 2019 20:49:41 +0100
- changeset 234
- b8b5efa495bb
- parent 233
- 14a84d42031d
- child 235
- 0a063ac5d7f5
Import products uses indexes.
| www/import/from_brouwhulp.php | file | annotate | diff | comparison | revisions |
--- a/www/import/from_brouwhulp.php Sat Feb 02 17:07:22 2019 +0100 +++ b/www/import/from_brouwhulp.php Sat Feb 02 20:49:41 2019 +0100 @@ -1175,13 +1175,17 @@ /* Don't use $recipe->EST_OG but recalculate it */ /* Don't use $recipe->EST_FG but recalculate it */ /* Don't use $recipe->EST_COLOR but recalculate it */ - if ($recipe->COLOR_METHOD == 'Morey') + $color_method = 0; + if ($recipe->COLOR_METHOD == 'Morey') { + $color_method = 0; $sql .= "', color_method='0"; - else if ($recipe->COLOR_METHOD == 'Mosher') + } else if ($recipe->COLOR_METHOD == 'Mosher') { + $color_method = 1; $sql .= "', color_method='1"; - else if ($recipe->COLOR_METHOD == 'Daniels') + } else if ($recipe->COLOR_METHOD == 'Daniels') { + $color_method = 2; $sql .= "', color_method='2"; - else + } else echo "Unknown COLOR_METHO " . $recipe->COLOR_METHOD . PHP_EOL; if ($recipe->IBU) @@ -1301,7 +1305,7 @@ $sql .= "', est_fg='" . floatval($fg); $abv = abvol($og, $fg); $sql .= "', est_abv='" . floatval($abv); - $color = kw_to_ebc(mysqli_real_escape_string($db, $recipe->COLOR_METHOD), $colorw); + $color = kw_to_ebc($color_method, $colorw); $sql .= "', est_color='" . floatval($color); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { @@ -1324,7 +1328,7 @@ $len_hops = 0; $len_miscs = 0; $len_yeasts = 0; - $len_mash = 0; + $len_mash = 0; echo " Start adding brews to the database\n"; $sql = "TRUNCATE TABLE products;"; @@ -1344,8 +1348,7 @@ $pSugar = 0; $svg = 77; $colorw = 0; - $stageno = 0; - $stage = "Plan"; // Default value. + $stage = 0; $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid')); $sql = "INSERT INTO products SET uuid='" . $uuid; @@ -1356,10 +1359,14 @@ } else { $sql .= "', notes='"; } - if ($recipe->TYPE) - $sql .= "', type='" . mysqli_real_escape_string($db, $recipe->TYPE); - else - $sql .= "', type='"; + + if ($recipe->TYPE == 'Extract') + $sql .= "', type='0"; + else if ($recipe->TYPE == 'Partial Mash') + $sql .= "', type='1"; + else if ($recipe->TYPE == 'All Grain') + $sql .= "', type='2"; + if ($recipe->BATCH_SIZE) $batch_size = floatval($recipe->BATCH_SIZE); $sql .= "', batch_size='" . $batch_size; @@ -1375,13 +1382,31 @@ $sql .= "', efficiency='" . $efficiency; /* Don't use $recipe->EST_OG but recalculate it */ /* Don't use $recipe->EST_FG but recalculate it */ - /* Don't use $recipe->EST_COLOR but recalculate it */ - if ($recipe->COLOR_METHOD) - $sql .= "', color_method='" . mysqli_real_escape_string($db, $recipe->COLOR_METHOD); - if ($recipe->IBU) - $sql .= "', est_ibu='" . floatval($recipe->IBU); - if ($recipe->IBU_METHOD) - $sql .= "', ibu_method='" . mysqli_real_escape_string($db, $recipe->IBU_METHOD); + /* Don't use $recipe->EST_COLOR but recalculate it */ + $color_method = 0; + if ($recipe->COLOR_METHOD == 'Morey') { + $color_method = 0; + $sql .= "', color_method='0"; + } else if ($recipe->COLOR_METHOD == 'Mosher') { + $color_method = 1; + $sql .= "', color_method='1"; + } else if ($recipe->COLOR_METHOD == 'Daniels') { + $color_method = 2; + $sql .= "', color_method='2"; + } else + echo "Unknown COLOR_METHO " . $recipe->COLOR_METHOD . PHP_EOL; + + if ($recipe->IBU) + $sql .= "', est_ibu='" . floatval($recipe->IBU); + if ($recipe->IBU_METHOD == 'Tinseth') + $sql .= "', ibu_method='0"; + else if ($recipe->IBU_METHOD == 'Rager') + $sql .= "', ibu_method='1"; + else if ($recipe->IBU_METHOD == 'Daniels') + $sql .= "', ibu_method='2"; + else + echo "Unknown IBU_METHOD " . $recipe->IBU_METHOD . PHP_EOL; + if ($recipe->CARBONATION) $sql .= "', est_carb='" . floatval($recipe->CARBONATION); @@ -1396,13 +1421,13 @@ $sql .= "', mash_ph='" . floatval($recipe->TARGET_PH); } if ($recipe->SPARGE_ACID_TYPE && ($recipe->SPARGE_ACID_TYPE == "Lactic")) { - $sql .= "', sparge_acid_type='Melkzuur"; + $sql .= "', sparge_acid_type='0"; } else if ($recipe->SPARGE_ACID_TYPE && ($recipe->SPARGE_ACID_TYPE == "Hydrochloric")) { - $sql .= "', sparge_acid_type='Zoutzuur"; + $sql .= "', sparge_acid_type='1"; } else if ($recipe->SPARGE_ACID_TYPE && ($recipe->SPARGE_ACID_TYPE == "Phosphoric")) { - $sql .= "', sparge_acid_type='Fosforzuur"; + $sql .= "', sparge_acid_type='2"; } else if ($recipe->SPARGE_ACID_TYPE && ($recipe->SPARGE_ACID_TYPE == "Sulfuric")) { - $sql .= "', sparge_acid_type='Zwavelzuur"; + $sql .= "', sparge_acid_type='3"; } if ($recipe->ACID_SPARGE_PERC) { $sql .= "', sparge_acid_perc='" . floatval($recipe->ACID_SPARGE_PERC); @@ -1489,7 +1514,7 @@ $sql .= "', est_fg='" . floatval($fg); $abv = abvol($og, $fg); $sql .= "', est_abv='" . floatval($abv); - $color = kw_to_ebc(mysqli_real_escape_string($db, $recipe->COLOR_METHOD), $colorw); + $color = kw_to_ebc($color_method, $colorw); $sql .= "', est_color='" . floatval($color); $sql .= "', code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE); @@ -1505,46 +1530,30 @@ if ($count > 0) { $sql .= "', log_brew='1"; } - $lsql = "UPDATE log_fermentation SET product_uuid='".$uuid."', product_name='".mysqli_real_escape_string($db, $recipe->NAME); - $lsql .= "' WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';"; - if (! $result = mysqli_query($db, $lsql)) { - printf("Error: %s\n", mysqli_error($db)); - } - $count = mysqli_affected_rows($db); - if ($count > 0) { + + $filename = '../log/fermentation/'.$recipe->NR_RECIPE.' '.$recipe->NAME.'.log'; + if (file_exists($filename)) { $sql .= "', log_fermentation='1"; } else { - // See if there really are no records - $lsql = "SELECT product_code FROM log_fermentation WHERE product_code='".$recipe->NR_RECIPE."';"; - $count = mysqli_affected_rows($db); - if ($count > 0) { + if ($recipe->FERM_MEASUREMENTS) { + /* + * There is internal logdata in the xml, create a logfile + */ + $fp = fopen($filename, "w+"); + foreach ($recipe->FERM_MEASUREMENTS->FERM_MEASUREMENT as $measurement) { + $dt = substr($measurement->DATE_TIME, 6, 4).substr($measurement->DATE_TIME,2,4).substr($measurement->DATE_TIME,0,2); + $dt .= ' '.substr($measurement->DATE_TIME, 11,8); + $line = $dt.',NONE,PRIMARY,'; + $line .= sprintf("%.1f",floatval($measurement->TEMP_SENSOR_1)) .','; + $line .= sprintf("%.1f",floatval($measurement->TEMP_SENSOR_2)) .','; + $line .= 'NA,NA,NA,NA,NA,NA,NA,NA,' . PHP_EOL; + fwrite($fp, $line); + } + fclose($fp); $sql .= "', log_fermentation='1"; - } - } - - /* - * If no external fermentation log is found, check for an internal one. - */ - if (($count == 0) && $recipe->FERM_MEASUREMENTS) { - $lsql = "DELETE FROM log_fermentation WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';"; - if (! $lresult = mysqli_query($db, $lsql)) { - printf("Error: %s\n", mysqli_error($db)); + } else { + $sql .= "', log_fermentation='0"; } - foreach ($recipe->FERM_MEASUREMENTS->FERM_MEASUREMENT as $measurement) { - $dt = substr($measurement->DATE_TIME, 6, 4).substr($measurement->DATE_TIME,2,4).substr($measurement->DATE_TIME,0,2); - $dt .= ' '.substr($measurement->DATE_TIME, 11,8); - $lsql = "INSERT INTO log_fermentation SET product_uuid='".$uuid; - $lsql .= "', product_code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE); - $lsql .= "', product_name='" . mysqli_real_escape_string($db, $recipe->NAME); - $lsql .= "', datetime='" . $dt; - $lsql .= "', temperature_air='" . floatval($measurement->TEMP_SENSOR_1); - $lsql .= "', temperature_beer='" . floatval($measurement->TEMP_SENSOR_2); - $lsql .= "';"; - if (! $lresult = mysqli_query($db, $lsql)) { - printf("Error: %s\n", mysqli_error($db)); - } - } - $sql .= "', log_fermentation='1"; } if ($recipe->EQUIPMENT) { @@ -1555,7 +1564,7 @@ $sql .= "', eq_tun_volume='" . floatval($recipe->EQUIPMENT->TUN_VOLUME); $sql .= "', eq_tun_weight='" . floatval($recipe->EQUIPMENT->TUN_WEIGHT); $sql .= "', eq_tun_specific_heat='" . floatval($recipe->EQUIPMENT->TUN_SPECIFIC_HEAT); - $sql .= "', eq_tun_material='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->TUN_MATERIAL); + $sql .= "', eq_tun_material='" . $recipe->EQUIPMENT->TUN_MATERIAL; $sql .= "', eq_tun_height='" . floatval($recipe->EQUIPMENT->TUN_HEIGHT); if ($recipe->EQUIPMENT->TOP_UP_WATER) $sql .= "', eq_top_up_water='" . floatval($recipe->EQUIPMENT->TOP_UP_WATER); @@ -1585,14 +1594,12 @@ if (($recipe->DATE) && (! $recipe->TIME_STARTED) && (! $recipe->TIME_ENDED)) { /* We have a plan date but haven't brewed yet, use current date */ $sql .= "', birth='" . date("Y-m-d"); - $stageno= 1; - $stage = "Wait"; + $stage = 1; } if (($recipe->DATE) && ($recipe->TIME_STARTED) && ($recipe->TIME_ENDED)) { /* We have brew data */ - $stageno = 3; - $stage = "Primary"; // Need to think about during a brew... + $stage = 3; // Need to think about during a brew... $brewdate = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2); $sql .= "', birth='" . $brewdate; $date_start = $brewdate.' '.$recipe->TIME_STARTED; @@ -1623,7 +1630,19 @@ if ($recipe->ACTUAL_EFFICIENCY) $sql .= "', brew_aboil_efficiency='" . floatval($recipe->ACTUAL_EFFICIENCY); $sql .= "', brew_whirlpool2='" . floatval($recipe->WHIRLPOOL_TIME); - $sql .= "', brew_cooling_method='" . mysqli_real_escape_string($db, $recipe->COOLING_METHOD); + + if ($recipe->COOLING_METHOD == '-') + $sql .= "', brew_cooling_method='0"; + else if ($recipe->COOLING_METHOD == 'Emersion chiller') + $sql .= "', brew_cooling_method='1"; + else if ($recipe->COOLING_METHOD == 'Counterflow chiller') + $sql .= "', brew_cooling_method='2"; + else if ($recipe->COOLING_METHOD == 'Au bain marie') + $sql .= "', brew_cooling_method='3"; + else if ($recipe->COOLING_METHOD == 'Natural') + $sql .= "', brew_cooling_method='4"; + else + echo "Unknown COOLING_METHOD " . $recipe->COOLING_METHOD . PHP_EOL; $sql .= "', brew_cooling_time='" . floatval($recipe->COOLING_TIME); $sql .= "', brew_cooling_to='" . floatval($recipe->COOLING_TO); if ($recipe->VOLUME_FERMENTER) @@ -1632,18 +1651,27 @@ $sql .= "', brew_fermenter_extrawater='" . floatval($recipe->EQUIPMENT->TOP_UP_WATER_BREWDAY); $sql .= "', brew_fermenter_sg='" . floatval($recipe->OG_FERMENTER); $sql .= "', brew_fermenter_ibu='" . floatval($recipe->IBU); - $sql .= "', brew_aeration_type='" . mysqli_real_escape_string($db, $recipe->AERATION_TYPE); - if ($recipe->AERATION_TYPE != "None") { + + if ($recipe->AERATION_TYPE == 'None') + $sql .= "', brew_aeration_type='0"; + else if ($recipe->AERATION_TYPE == 'Air') { + $sql .= "', brew_aeration_type='1"; $sql .= "', brew_aeration_speed='" . floatval($recipe->AERATION_SPEED); $sql .= "', brew_aeration_time='" . floatval($recipe->AERATION_TIME); - } + } else if ($recipe->AERATION_TYPE == 'Oxygen') { + $sql .= "', brew_aeration_type='2"; + $sql .= "', brew_aeration_speed='" . floatval($recipe->AERATION_SPEED); + $sql .= "', brew_aeration_time='" . floatval($recipe->AERATION_TIME); + } else + echo "Unknown AERATION_TYPE " . $recipe->AERATION_TYPE . PHP_EOL; + $sql .= "', brew_date_end='" . $date_end; } $sql .= "', og='" . floatval($recipe->OG); $sql .= "', fg='" . floatval($recipe->FG); - if ($recipe->PRIMARY_AGE && ($stageno >= 3)) { + if ($recipe->PRIMARY_AGE && ($stage >= 3)) { /* PRIMARY_TEMP is the average of START_TEMP_PRIMARY MAX_TEMP_PRIMARY END_TEMP_PRIMARY */ $pdate = new DateTime($brewdate); $pdate->modify("+".floatval($recipe->PRIMARY_AGE)." days"); @@ -1652,60 +1680,54 @@ $sql .= "', primary_end_temp='" . floatval($recipe->END_TEMP_PRIMARY); $sql .= "', primary_end_sg='" . floatval($recipe->SG_END_PRIMARY); $sql .= "', primary_end_date='" . $pdate->format("Y-m-d"); - $stageno = 4; - $stage = "Secondary"; + $stage = 4; // Secondary - if ($recipe->SECONDARY_AGE && ($stageno >= 4)) { + if ($recipe->SECONDARY_AGE && ($stage >= 4)) { $sdate = new DateTime($brewdate); $sdate->modify("+".floatval($recipe->SECONDARY_AGE)." days"); $sql .= "', secondary_temp='" . floatval($recipe->SECONDARY_TEMP); $sql .= "', secondary_end_date='" . $sdate->format("Y-m-d"); - $stageno = 5; - $stage = "Tertiary"; + $stage = 5; // Tertiary - if ($recipe->TERTIARY_TEMP && ($stageno >= 5)) { + if ($recipe->TERTIARY_TEMP && ($stage >= 5)) { $sql .= "', tertiary_temp='" . floatval($recipe->TERTIARY_TEMP); } } } if ($recipe->DATE_BOTTLING && ($recipe->AMOUNT_BOTTLING || $recipe->AMOUNT_KEGGED) && - ($recipe->AMOUNT_PRIMING || $recipe->AMOUNT_PRIMING_KEGS) && ($stageno >= 5)) { + ($recipe->AMOUNT_PRIMING || $recipe->AMOUNT_PRIMING_KEGS) && ($stage >= 5)) { $bdate = substr($recipe->DATE_BOTTLING, 6, 4).substr($recipe->DATE_BOTTLING,2,4).substr($recipe->DATE_BOTTLING,0,2); $sql .= "', package_date='" . $bdate; - $stage = "Package"; - $stageno = 6; + $stage = 6; // Package $dStart = new DateTime($bdate); $dEnd = new DateTime(''); $dDiff = $dStart->diff($dEnd); $age = floatval($dDiff->days); if ($age == 0) { // Package day - $stage = "Package"; - $stageno = 6; + $stage = 6; } else if ($age < 14) { // Carbonation period - $stage = "Carbonation"; - $stageno = 7; + $stage = 7; } else if ($age < 42) { // Mature, fixed 6 weeks - $stage = "Mature"; - $stageno = 8; + $stage = 8; } else { // Ready for tasting. - $stage = "Taste"; - $stageno = 9; + $stage = 9; } if ($recipe->AMOUNT_BOTTLING && $recipe->AMOUNT_PRIMING) { $sql .= "', bottle_amount='" . floatval($recipe->AMOUNT_BOTTLING); $sql .= "', bottle_carbonation='" . floatval($recipe->CARBONATION); + if ($recipe->PRIMING_SUGAR_BOTTLES == "Saccharose") - $sql .= "', bottle_priming_sugar='Kristalsuiker"; + $sql .= "', bottle_priming_sugar='0"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "Glucose or dextrose") - $sql .= "', bottle_priming_sugar='Glucose/dextrose"; + $sql .= "', bottle_priming_sugar='1"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "Honey") - $sql .= "', bottle_priming_sugar='Honing"; + $sql .= "', bottle_priming_sugar='2"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "DME") - $sql .= "', bottle_priming_sugar='Moutextract"; + $sql .= "', bottle_priming_sugar='3"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "Molassis") - $sql .= "', bottle_priming_sugar='Melasse"; + $sql .= "', bottle_priming_sugar='4"; $sql .= "', bottle_priming_amount='" . floatval($recipe->AMOUNT_PRIMING); $sql .= "', bottle_carbonation_temp='" . floatval($recipe->CARBONATION_TEMP); } @@ -1713,15 +1735,15 @@ $sql .= "', keg_amount='" . floatval($recipe->AMOUNT_KEGGED); $sql .= "', keg_carbonation='" . floatval($recipe->CARBONATION); // Lijkt wel hetzelfde als bottles if ($recipe->PRIMING_SUGAR_BOTTLES == "Saccharose") - $sql .= "', keg_priming_sugar='Kristalsuiker"; + $sql .= "', keg_priming_sugar='0"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "Glucose or dextrose") - $sql .= "', keg_priming_sugar='Glucose/dextrose"; + $sql .= "', keg_priming_sugar='1"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "Honey") - $sql .= "', keg_priming_sugar='Honing"; + $sql .= "', keg_priming_sugar='2"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "DME") - $sql .= "', keg_priming_sugar='Moutextract"; + $sql .= "', keg_priming_sugar='3"; else if ($recipe->PRIMING_SUGAR_BOTTLES == "Molassis") - $sql .= "', keg_priming_sugar='Melasse"; + $sql .= "', keg_priming_sugar='4"; $sql .= "', keg_priming_amount='" . floatval($recipe->AMOUNT_PRIMING_KEGS); $sql .= "', keg_carbonation_temp='" . floatval($recipe->KEG_CARB_TEMP); ($recipe->FORCED_CARB_KEGS == 'TRUE') ? $sql .= "', keg_forced_carb='1" : $sql .= "', keg_forced_carb='0"; @@ -1729,9 +1751,8 @@ } } - if ($recipe->TASTE_NOTES && $recipe->TASTING_RATE && $recipe->TASTE_DATE && ($stageno >= 9)) { - $stage = "Ready"; // Ready if tasted. - $stageno = 10; + if ($recipe->TASTE_NOTES && $recipe->TASTING_RATE && $recipe->TASTE_DATE && ($stage >= 9)) { + $stage = 10; // Ready $sql .= "', taste_notes='" . mysqli_real_escape_string($db, $recipe->TASTE_NOTES); $sql .= "', taste_rate='" . floatval($recipe->TASTING_RATE); $tdate = substr($recipe->TASTE_DATE, 6, 4).substr($recipe->TASTE_DATE,2,4).substr($recipe->TASTE_DATE,0,2); @@ -1746,10 +1767,9 @@ } ($recipe->INVENTORY_REDUCED == 'TRUE') ? $sql .= "', inventory_reduced='1" : $sql .= "', inventory_reduced='0"; - if (($recipe->LOCKED == 'TRUE') && ($stage == 'Ready')) { + if (($recipe->LOCKED == 'TRUE') && ($stage == 10)) { $sql .= "', locked='1"; - $stage = "Closed"; - $stageno = 11; + $stage = 11; } else { $sql .= "', locked='0"; }
