changeset
Finished brews import
Wed, 21 Nov 2018 19:58:47 +0100
- author
- Michiel Broek <mbroek@mbse.eu>
- date
- Wed, 21 Nov 2018 19:58:47 +0100
- changeset 108
- a466373bc23f
- parent 107
- 8b4fdadf320c
- child 109
- 3c334f3ca7f9
Finished brews import
| README.design | file | annotate | diff | comparison | revisions | |
| www/import/from_brouwhulp.php | file | annotate | diff | comparison | revisions |
--- a/README.design Tue Nov 20 14:39:15 2018 +0100 +++ b/README.design Wed Nov 21 19:58:47 2018 +0100 @@ -107,6 +107,6 @@ | | +-------------------- rapport/etiketten | +--------------------------------- Log/rapport +------------------------------------------- Log/rapport - Stage: Plan Wait Brew Primary Secondary Tertiary Package Carbonation Mature Taste Closed + Stage: Plan Wait Brew Primary Secondary Tertiary Package Carbonation Mature Taste Ready Closed
--- a/www/import/from_brouwhulp.php Tue Nov 20 14:39:15 2018 +0100 +++ b/www/import/from_brouwhulp.php Wed Nov 21 19:58:47 2018 +0100 @@ -961,6 +961,7 @@ printf("Error: %s\n", mysqli_error($db)); } + date_default_timezone_set('Europe/Amsterdam'); $recipes = simplexml_load_file($brouwhulp . '/brews.xml'); foreach ($recipes->RECIPE as $recipe) { @@ -971,7 +972,8 @@ $pCara = 0; $pSugar = 0; $svg = 77; - $colorw = 0; + $colorw = 0; + $stage = "Plan"; // Default value. $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid')); $psql = "INSERT INTO prod_main SET uuid='" . $uuid; // So we can link the records. @@ -979,7 +981,6 @@ $psql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME); $rsql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME); - $rsql .= "', locked='0"; if ($recipe->NOTES) $rsql .= "', notes='" . mysqli_real_escape_string($db, $recipe->NOTES); @@ -1097,11 +1098,54 @@ $rsql .= "', est_abv='" . floatval($abv); $color = kw_to_ebc(mysqli_real_escape_string($db, $recipe->COLOR_METHOD), $colorw); $rsql .= "', est_color='" . floatval($color); - $rsql .= "';"; - if (! $rresult = mysqli_query($db, $rsql)) { - printf("Error: %s\n", mysqli_error($db)); + $psql .= "', code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE); + + /* + * Update external logfiles + */ + $sql = "UPDATE log_brews SET product_uuid='".$uuid."', product_name='".mysqli_real_escape_string($db, $recipe->NAME); + $sql .= "' WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';"; + if (! $result = mysqli_query($db, $sql)) { + printf("Error: %s\n", mysqli_error($db)); + } + $count = mysqli_affected_rows($db); + if ($count > 0) { + $psql .= "', log_brew='1"; + } + $sql = "UPDATE log_fermentation SET product_uuid='".$uuid."', product_name='".mysqli_real_escape_string($db, $recipe->NAME); + $sql .= "' WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';"; + if (! $result = mysqli_query($db, $sql)) { + printf("Error: %s\n", mysqli_error($db)); + } + $count = mysqli_affected_rows($db); + if ($count > 0) { + $psql .= "', log_fermentation='1"; } - $psql .= "', code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE); + + /* + * 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)); + } + 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)); + } + } + $psql .= "', log_fermentation='1"; + } if ($recipe->EQUIPMENT) { $psql .= "', eq_name='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->NAME); @@ -1112,12 +1156,14 @@ $psql .= "', eq_tun_specific_heat='" . floatval($recipe->EQUIPMENT->TUN_SPECIFIC_HEAT); $psql .= "', eq_tun_material='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->TUN_MATERIAL); $psql .= "', eq_tun_height='" . floatval($recipe->EQUIPMENT->TUN_HEIGHT); - //$psql .= "', eq_top_up_water='" . floatval($recipe->EQUIPMENT->); + if ($recipe->EQUIPMENT->TOP_UP_WATER) + $psql .= "', eq_top_up_water='" . floatval($recipe->EQUIPMENT->TOP_UP_WATER); $psql .= "', eq_trub_chiller_loss='" . floatval($recipe->EQUIPMENT->TRUB_CHILLER_LOSS); $psql .= "', eq_evap_rate='" . floatval($recipe->EQUIPMENT->EVAP_RATE); $psql .= "', eq_boil_time='" . floatval($recipe->EQUIPMENT->BOIL_TIME); - // eq_calc_boil_volume CALC_BOIL_VOLUME - //$psql .= "', eq_top_up_kettle='" . floatval($recipe->EQUIPMENT->); + ($recipe->EQUIPMENT->CALC_BOIL_VOLUME == 'TRUE') ? $psql .= "', eq_calc_boil_volume='1" : $psql .= "', eq_calc_boil_volume='0"; + if ($recipe->EQUIPMENT->TOP_UP_KETTLE) + $psql .= "', eq_top_up_kettle='" . floatval($recipe->EQUIPMENT->TOP_UP_KETTLE); $psql .= "', eq_hop_utilization='" . floatval($recipe->EQUIPMENT->HOP_UTILIZATION); $psql .= "', eq_lauter_volume='" . floatval($recipe->EQUIPMENT->LAUTER_VOLUME); $psql .= "', eq_lauter_height='" . floatval($recipe->EQUIPMENT->LAUTER_HEIGHT); @@ -1128,10 +1174,19 @@ $psql .= "', eq_efficiency='" . floatval($recipe->EQUIPMENT->EFFICIENCY); } + if (($recipe->DATE) && (! $recipe->TIME_STARTED) && (! $recipe->TIME_ENDED)) { + /* We have a plan date but haven't brewed yet, use current date */ + $psql .= "', birth='" . date("Y-m-d"); + $stage = "Wait"; + } + if (($recipe->DATE) && ($recipe->TIME_STARTED) && ($recipe->TIME_ENDED)) { /* We have brew data */ - $date_start = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2).' '.$recipe->TIME_STARTED; - $date_end = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2).' '.$recipe->TIME_ENDED; + $stage = "Primary"; // Need to think about during a brew... + $brewdate = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2); + $psql .= "', birth='" . $brewdate; + $date_start = $brewdate.' '.$recipe->TIME_STARTED; + $date_end = $brewdate.' '.$recipe->TIME_ENDED; $psql .= "', brew_date_start='" . $date_start; if ($recipe->PH_ADJUSTED) @@ -1171,53 +1226,102 @@ $psql .= "', brew_aeration_time='" . floatval($recipe->AERATION_TIME); } $psql .= "', brew_date_end='" . $date_end; - echo $date_start . PHP_EOL; } - /* - * AGE = onduidelijk - * INVENTORY_REDUCED - * LOCKED - * ABV - */ + + if ($recipe->PRIMARY_AGE) { + /* 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"); + $psql .= "', primary_start_temp='" . floatval($recipe->START_TEMP_PRIMARY); + $psql .= "', primary_max_temp='" . floatval($recipe->MAX_TEMP_PRIMARY); + $psql .= "', primary_end_temp='" . floatval($recipe->END_TEMP_PRIMARY); + $psql .= "', primary_end_sg='" . floatval($recipe->SG_END_PRIMARY); + $psql .= "', primary_end_date='" . $pdate->format("Y-m-d"); + $stage = "Secondary"; + + if ($recipe->SECONDARY_AGE) { + $sdate = new DateTime($brewdate); + $sdate->modify("+".floatval($recipe->SECONDARY_AGE)." days"); + $psql .= "', secondary_temp='" . floatval($recipe->SECONDARY_TEMP); + $psql .= "', secondary_end_date='" . $sdate->format("Y-m-d"); + $stage = "Tertiary"; - /* - * FERMENTATION_STAGES - * PRIMARY_AGE - * PRIMARY_TEMP - * SECONDARY_AGE - * SECONDARY_TEMP - * TERTIARY_AGE - * TERTIARY_TEMP - * SG_END_PRIMARY - * START_TEMP_PRIMARY - * MAX_TEMP_PRIMARY - * END_TEMP_PRIMARY - */ + if ($recipe->TERTIARY_TEMP) { + $psql .= "', tertiary_temp='" . floatval($recipe->TERTIARY_TEMP); + } + } + } + + if ($recipe->DATE_BOTTLING && ($recipe->AMOUNT_BOTTLING || $recipe->AMOUNT_KEGGED) && + ($recipe->AMOUNT_PRIMING || $recipe->AMOUNT_PRIMING_KEGS)) { + $bdate = substr($recipe->DATE_BOTTLING, 6, 4).substr($recipe->DATE_BOTTLING,2,4).substr($recipe->DATE_BOTTLING,0,2); + $psql .= "', package_date='" . $bdate; + $stage = "Package"; + $dStart = new DateTime($bdate); + $dEnd = new DateTime(''); + $dDiff = $dStart->diff($dEnd); + $age = floatval($dDiff->days); + if ($age == 0) // Package day + $stage = "Package"; + else if ($age < 14) // Carbonation period + $stage = "Carbonation"; + else if ($age < 42) // Mature, fixed 6 weeks + $stage = "Mature"; + else // Ready for tasting. + $stage = "Taste"; - /* - * DATE_BOTTLING - * AMOUNT_BOTTLING - * CARBONATION - * FORCED_CARB_KEGS - * AMOUNT_PRIMING - * AMOUNT_PRIMING_KEGS - * KEG_PRESSURE - * PRIMING_SUGAR_BOTTLES - * PRIMING_SUGAR_KEGS - */ - /* - * TASTE - * TASTE_NOTES - * TASTING_RATE - * TASTE_DATE - * TASTE_COLOR - * TASTE_TRANSPARENCY - * TASTE_HEAD - * TASTE_AROMA - * TASTE_TASTE - * TASTE_MOUTHFEEL - * TASTE_AFTERTASTE - */ + if ($recipe->AMOUNT_BOTTLING && $recipe->AMOUNT_PRIMING) { + $psql .= "', bottle_amount='" . floatval($recipe->AMOUNT_BOTTLING); + $psql .= "', bottle_carbonation='" . floatval($recipe->CARBONATION); + $psql .= "', bottle_priming_sugar='" . mysqli_real_escape_string($db, $recipe->PRIMING_SUGAR_BOTTLES); + $psql .= "', bottle_priming_amount='" . floatval($recipe->AMOUNT_PRIMING); + $psql .= "', bottle_carbonation_temp='" . floatval($recipe->CARBONATION_TEMP); + } + if ($recipe->AMOUNT_KEGGED && $recipe->AMOUNT_PRIMING_KEGS) { + $psql .= "', keg_amount='" . floatval($recipe->AMOUNT_KEGGED); + $psql .= "', keg_carbonation='" . floatval($recipe->CARBONATION); // Lijkt wel hetzelfde als bottles + $psql .= "', keg_priming_sugar='" . mysqli_real_escape_string($db, $recipe->PRIMING_SUGAR_KEGS); + $psql .= "', keg_priming_amount='" . floatval($recipe->AMOUNT_PRIMING_KEGS); + $psql .= "', keg_carbonation_temp='" . floatval($recipe->KEG_CARB_TEMP); + ($recipe->FORCED_CARB_KEGS == 'TRUE') ? $psql .= "', keg_forced_carb='1" : $psql .= "', keg_forced_carb='0"; + $psql .= "', keg_pressure='" . floatval($recipe->KEG_PRESSURE); + $psql .= "', keg_priming_factor='" . floatval($recipe->KEG_PRIMING_FACTOR); + } + } + + if ($recipe->TASTE_NOTES && $recipe->TASTING_RATE && $recipe->TASTE_DATE) { + $stage = "Ready"; // Ready if tasted. + $psql .= "', taste_notes='" . mysqli_real_escape_string($db, $recipe->TASTE_NOTES); + $psql .= "', 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); + $psql .= "', taste_date='" . $tdate; + $psql .= "', taste_color='" . mysqli_real_escape_string($db, $recipe->TASTE_COLOR); + $psql .= "', taste_transparency='" . mysqli_real_escape_string($db, $recipe->TASTE_TRANSPARENCY); + $psql .= "', taste_head='" . mysqli_real_escape_string($db, $recipe->TASTE_HEAD); + $psql .= "', taste_aroma='" . mysqli_real_escape_string($db, $recipe->TASTE_AROMA); + $psql .= "', taste_taste='" . mysqli_real_escape_string($db, $recipe->TASTE_TASTE); + $psql .= "', taste_mouthfeel='" . mysqli_real_escape_string($db, $recipe->TASTE_MOUTHFEEL); + $psql .= "', taste_aftertaste='" . mysqli_real_escape_string($db, $recipe->TASTE_AFTERTASTE); + } + + ($recipe->INVENTORY_REDUCED == 'TRUE') ? $psql .= "', inventory_reduced='1" : $psql .= "', inventory_reduced='0"; + if (($recipe->LOCKED == 'TRUE') && ($stage == 'Ready')) { + $psql .= "', locked='1"; + $rsql .= "', locked='1"; + $stage = "Closed"; + } else { + $psql .= "', locked='0"; + $rsql .= "', locked='0"; + } + + $psql .= "', stage='" . $stage; +// echo ' '.$brewdate.' '.$recipe->NR_RECIPE.' '.$stage . PHP_EOL; + + $rsql .= "';"; + if (! $rresult = mysqli_query($db, $rsql)) { + printf("Error: %s\n", mysqli_error($db)); + } + $psql .= "';"; if (! $presult = mysqli_query($db, $psql)) { printf("Error: %s\n", mysqli_error($db));
