www/import/from_brouwhulp.php@71f1ca48c551
www/import/from_brouwhulp.php
Sat, 08 Dec 2018 17:11:38 +0100
- author
- Michiel Broek <mbroek@mbse.eu>
- date
- Sat, 08 Dec 2018 17:11:38 +0100
- changeset 131
- 71f1ca48c551
- parent 126
- 9999b3ce98cb
- child 136
- bcc4583fd013
- permissions
- -rw-r--r--
When importing recipes with liquid yeast divide the cost by the pack volume. Don't allow alpha editing of hops in a recipe. Fix hop use time when the hop is used when not boiling and set the fixed time.
<?php /* * Import inventory from brouwhulp */ require("../config.php"); require("../version.php"); require("../includes/formulas.php"); echo "Start adding data from brouwhulp\n"; $db = mysqli_connect(DBASE_HOST, DBASE_USER, DBASE_PASS, DBASE_NAME); if (! $db) { die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); } mysqli_set_charset($db, "utf8" ); $brouwhulp = '/home/mbroek/Documents/brouwhulp'; $pCara = 0; $pSugar = 0; $efficiency = 75; $batch_size = 20; $colorw = 0; $f_sugars = 0; function get_miscs_cost($miscname) { global $db; if ($result = mysqli_query($db, "SELECT cost FROM inventory_miscs WHERE name='$miscname'")) { if ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { return floatval($row['cost']); } } return 0.0; } function do_fermentables() { global $brouwhulp, $db; echo " Start adding fermentables to the database\n"; $sql = "TRUNCATE TABLE inventory_fermentables;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $myfermentables = simplexml_load_file($brouwhulp . '/fermentables.xml'); foreach ($myfermentables->FERMENTABLE as $fermentable) { $sql = "INSERT INTO inventory_fermentables SET name='" . mysqli_real_escape_string($db, $fermentable->NAME); $sql .= "', type='" . $fermentable->TYPE; if ($fermentable->YIELD) $sql .= "', yield='" . floatval($fermentable->YIELD); if ($fermentable->COLOR) { $srm = floatval($fermentable->COLOR); $ebc = srm_to_ebc($srm); $sql .= "', color='" . $ebc; } ($fermentable->ADD_AFTER_BOIL == "TRUE") ? $sql .= "', add_after_boil='1" : $sql .= "', add_after_boil='0"; $sql .= "', origin='" . mysqli_real_escape_string($db, $fermentable->ORIGIN); $sql .= "', supplier='" . mysqli_real_escape_string($db, $fermentable->SUPPLIER); $sql .= "', notes='" . mysqli_real_escape_string($db, $fermentable->NOTES); if ($fermentable->COARSE_FINE_DIFF) $sql .= "', coarse_fine_diff='" . floatval($fermentable->COARSE_FINE_DIFF); if ($fermentable->MOISTURE) $sql .= "', moisture='" . floatval($fermentable->MOISTURE); if ($fermentable->DIASTATIC_POWER) $sql .= "', diastatic_power='" . floatval($fermentable->DIASTATIC_POWER); if ($fermentable->PROTEIN) $sql .= "', protein='" . floatval($fermentable->PROTEIN); if ($fermentable->MAX_IN_BATCH) $sql .= "', max_in_batch='" . floatval($fermentable->MAX_IN_BATCH); ($fermentable->RECOMMEND_MASH == "TRUE") ? $sql .= "', recommend_mash='1" : $sql .= "', recommend_mash='0"; if ($fermentable->IBU_GAL_PER_LB) $sql .= "', ibu_gal_per_lb='" . floatval($fermentable->IBU_GAL_PER_LB); ($fermentable->ALWAYS_ON_STOCK == "TRUE") ? $sql .= "', always_on_stock='1" : $sql .= "', always_on_stock='0"; if ($fermentable->INVENTORY) $sql .= "', inventory='" . floatval($fermentable->INVENTORY); if ($fermentable->COST) $sql .= "', cost='" . floatval($fermentable->COST); /* * These are not beerxml standard: */ if ($fermentable->DI_pH) $sql .= "', di_ph='" . floatval($fermentable->DI_pH); if ($fermentable->{'ACID_TO_pH_5.7'}) $sql .= "', acid_to_ph_57='" . floatval($fermentable->{'ACID_TO_pH_5.7'}); $sql .= "', graintype='" . $fermentable->GRAINTYPE; $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_hops() { global $brouwhulp, $db; echo " Start adding hops to the database\n"; $sql = "TRUNCATE TABLE inventory_hops;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $myhops = simplexml_load_file($brouwhulp . '/hops.xml'); foreach ($myhops->HOP as $hop) { $sql = "INSERT INTO inventory_hops SET name='" . mysqli_real_escape_string($db, $hop->NAME); if ($hop->ALPHA) $sql .= "', alpha='" . floatval($hop->ALPHA); if ($hop->BETA) $sql .= "', beta='" . floatval($hop->BETA); if ($hop->HUMULENE) $sql .= "', humulene='" . floatval($hop->HUMULENE); if ($hop->CARYOPHYLLENE) $sql .= "', caryophyllene='" . floatval($hop->CARYOPHYLLENE); if ($hop->COHUMULONE) $sql .= "', cohumulone='" . floatval($hop->COHUMULONE); if ($hop->MYCRENE) $sql .= "', myrcene='" . floatval($hop->MYCRENE); if ($hop->HSI) $sql .= "', hsi='" . floatval($hop->HSI); $sql .= "', useat='Boil"; // brouwhulp says Aroma always :) $sql .= "', type='" . $hop->TYPE; $sql .= "', form='" . $hop->FORM; $sql .= "', notes='" . mysqli_real_escape_string($db, $hop->NOTES); $sql .= "', origin='" . mysqli_real_escape_string($db, $hop->ORIGIN); $sql .= "', substitutes='" . mysqli_real_escape_string($db, $hop->SUBSTITUTES); ($hop->ALWAYS_ON_STOCK == 'TRUE') ? $sql .= "', always_on_stock='1" : $sql .= "', always_on_stock='0"; if ($hop->INVENTORY) $sql .= "', inventory='" . floatval($hop->INVENTORY) / 1000.0; if ($hop->COST) $sql .= "', cost='" . floatval($hop->COST); if ($hop->TOTAL_OIL) $sql .= "', total_oil='" . floatval($hop->TOTAL_OIL); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_yeasts() { global $brouwhulp, $db; echo " Start adding yeasts to the database\n"; $sql = "TRUNCATE TABLE inventory_yeasts;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $yeasts = simplexml_load_file($brouwhulp . '/yeasts.xml'); 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; $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->ATTENUATION) $sql .= "', attenuation='" . floatval($yeast->ATTENUATION); $sql .= "', notes='" . mysqli_real_escape_string($db, $yeast->NOTES); $sql .= "', best_for='" . mysqli_real_escape_string($db, $yeast->BEST_FOR); if ($yeast->MAX_REUSE) $sql .= "', max_reuse='" . $yeast->MAX_REUSE; if ($yeast->INVENTORY) $sql .= "', inventory='" . floatval($yeast->INVENTORY) / 1000.0; if ($yeast->COST) $sql .= "', cost='" . floatval($yeast->COST) * 1000.0; if ($yeast->CULTURE_DATE) { $date = substr($yeast->CULTURE_DATE, 6, 4) . '-' . substr($yeast->CULTURE_DATE, 3, 2) . '-' . substr($yeast->CULTURE_DATE, 0, 2); $sql .= "', production_date='" . $date; } $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_waters() { global $brouwhulp, $db; echo " Start adding waters to the database\n"; $sql = "TRUNCATE TABLE profile_water;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $sql = "TRUNCATE TABLE inventory_waters;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $waters = simplexml_load_file($brouwhulp . '/waters.xml'); foreach ($waters->WATER as $water) { if (($water->NAME == "Gedemineraliseerd water") || ($water->NAME == "Cristalline") || ($water->NAME == "Montille (Delhaize)") || ($water->NAME == "Spa Reine") || ($water->NAME == "Velsen") || ($water->NAME == "Zutphen") || ($water->NAME == "Aurele Bronwater (Colruyt)")) { $sql = "INSERT INTO inventory_waters SET name='" . mysqli_real_escape_string($db, $water->NAME); } else { $sql = "INSERT INTO profile_water SET name='" . mysqli_real_escape_string($db, $water->NAME); } if ($water->NOTES) $sql .= "', notes='" . mysqli_real_escape_string($db, $water->NOTES); $sql .= "', calcium='" . floatval($water->CALCIUM); $sql .= "', bicarbonate='" . floatval($water->BICARBONATE); $sql .= "', sulfate='" . floatval($water->SULFATE); $sql .= "', chloride='" . floatval($water->CHLORIDE); $sql .= "', sodium='" . floatval($water->SODIUM); $sql .= "', magnesium='" . floatval($water->MAGNESIUM); $sql .= "', ph='" . floatval($water->PH); if ($water->TOTAL_ALKALINITY) $sql .= "', total_alkalinity='" . floatval($water->TOTAL_ALKALINITY); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_miscs() { global $brouwhulp, $db; echo " Start adding miscs to the database\n"; $sql = "TRUNCATE TABLE inventory_miscs;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $miscs = simplexml_load_file($brouwhulp . '/miscs.xml'); foreach ($miscs->MISC as $misc) { $sql = "INSERT INTO inventory_miscs SET name='" . mysqli_real_escape_string($db, $misc->NAME); if ($misc->NOTES) $sql .= "', notes='" . mysqli_real_escape_string($db, $misc->NOTES); $sql .= "', type='" . $misc->TYPE; $sql .= "', use_use='" . $misc->USE; $sql .= "', time='" . $misc->TIME; ($misc->AMOUNT_IS_WEIGHT == 'TRUE') ? $sql .= "', amount_is_weight='1" : $sql .= "', amount_is_weight='0"; if ($misc->USE_FOR) $sql .= "', use_for='" . mysqli_real_escape_string($db, $misc->USE_FOR); if ($misc->ALWAYS_ON_STOCK) ($misc->ALWAYS_ON_STOCK == 'TRUE') ? $sql .= "', always_on_stock='1" : $sql .= "', always_on_stock='0"; if ($misc->INVENTORY) $sql .= "', inventory='" . floatval($misc->INVENTORY) / 1000.0; if ($misc->COST) $sql .= "', cost='" . floatval($misc->COST); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_equipments() { global $brouwhulp, $db; echo " Start adding equipments to the database\n"; $sql = "TRUNCATE TABLE inventory_equipments;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $equipments = simplexml_load_file($brouwhulp . '/equipments.xml'); foreach ($equipments->EQUIPMENT as $equipment) { $sql = "INSERT INTO inventory_equipments SET name='" . mysqli_real_escape_string($db, $equipment->NAME); $sql .= "', boil_size='" . floatval($equipment->BOIL_SIZE); $sql .= "', batch_size='" . floatval($equipment->BATCH_SIZE); $sql .= "', tun_volume='" . floatval($equipment->TUN_VOLUME); $sql .= "', tun_weight='" . floatval($equipment->TUN_WEIGHT); $sql .= "', tun_specific_heat='" . floatval($equipment->TUN_SPECIFIC_HEAT); $sql .= "', tun_material='" . mysqli_real_escape_string($db, $equipment->TUN_MATERIAL); $sql .= "', tun_height='" . floatval($equipment->TUN_HEIGHT); $sql .= "', top_up_water='" . floatval($equipment->TOP_UP_WATER); $sql .= "', trub_chiller_loss='" . floatval($equipment->TRUB_CHILLER_LOSS); /* * Brouwhulp uses a percentage for the evaporation rate. This is wrong * but was made so because the beerxml standard requires this. What we * do is calculate the actual evaporation and store that. * This is what we use. Brouwhulp calculates this on the fly. */ $sql .= "', evap_rate='" . (floatval($equipment->EVAP_RATE) * floatval($equipment->BOIL_SIZE)) / 100.0; $sql .= "', boil_time='" . floatval($equipment->BOIL_TIME); ($equipment->CALC_BOIL_VOLUME == 'TRUE') ? $sql .= "', calc_boil_volume='1" : $sql .= "', calc_boil_volume='0"; $sql .= "', top_up_kettle='" . floatval($equipment->TOP_UP_KETTLE); $sql .= "', hop_utilization='" . floatval($equipment->HOP_UTILIZATION); if ($equipment->NOTES) $sql .= "', notes='" . mysqli_real_escape_string($db, $equipment->NOTES); $sql .= "', lauter_volume='" . floatval($equipment->LAUTER_VOLUME); $sql .= "', lauter_height='" . floatval($equipment->LAUTER_HEIGHT); $sql .= "', lauter_deadspace='" . floatval($equipment->LAUTER_DEADSPACE); $sql .= "', kettle_volume='" . floatval($equipment->KETTLE_VOLUME); $sql .= "', kettle_height='" . floatval($equipment->KETTLE_HEIGHT); $sql .= "', mash_volume='" . floatval($equipment->MASH_VOLUME); $sql .= "', efficiency='" . floatval($equipment->EFFICIENCY); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_styles() { global $brouwhulp, $db; echo " Start adding styles to the database\n"; $sql = "TRUNCATE TABLE profile_styles;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $styles = simplexml_load_file($brouwhulp . '/styles.xml'); foreach ($styles->STYLE as $style) { $sql = "INSERT INTO profile_styles SET name='" . mysqli_real_escape_string($db, $style->NAME); if ($style->NOTES) $sql .= "', notes='" . mysqli_real_escape_string($db, $style->NOTES); if ($style->CATEGORY) $sql .= "', category='" . mysqli_real_escape_string($db, $style->CATEGORY); if ($style->CATEGORY_NUMBER) $sql .= "', category_number='" . $style->CATEGORY_NUMBER; if ($style->STYLE_LETTER) $sql .= "', style_letter='" . mysqli_real_escape_string($db, $style->STYLE_LETTER); if ($style->STYLE_GUIDE) $sql .= "', style_guide='" . mysqli_real_escape_string($db, $style->STYLE_GUIDE); $sql .= "', type='" . $style->TYPE; $sql .= "', og_min='" . floatval($style->OG_MIN); $sql .= "', og_max='" . floatval($style->OG_MAX); $sql .= "', fg_min='" . floatval($style->FG_MIN); $sql .= "', fg_max='" . floatval($style->FG_MAX); $sql .= "', ibu_min='" . floatval($style->IBU_MIN); $sql .= "', ibu_max='" . floatval($style->IBU_MAX); $sql .= "', color_min='" . floatval($style->COLOR_MIN); $sql .= "', color_max='" . floatval($style->COLOR_MAX); $sql .= "', carb_min='" . floatval($style->CARB_MIN); $sql .= "', carb_max='" . floatval($style->CARB_MAX); $sql .= "', abv_min='" . floatval($style->ABV_MIN); $sql .= "', abv_max='" . floatval($style->ABV_MAX); if ($style->PROFILE) $sql .= "', profile='" . mysqli_real_escape_string($db, $style->PROFILE); if ($style->INGREDIENTS) $sql .= "', ingredients='" . mysqli_real_escape_string($db, $style->INGREDIENTS); if ($style->EXAMPLES) $sql .= "', examples='" . mysqli_real_escape_string($db, $style->EXAMPLES); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } function do_mash() { global $brouwhulp, $db; echo " Start adding mash profiles to the database\n"; $sql = "TRUNCATE TABLE profile_mash;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $mashes = simplexml_load_file($brouwhulp . '/mashs.xml'); foreach ($mashes->MASH as $mash) { $sql = "INSERT INTO profile_mash SET name='" . mysqli_real_escape_string($db, $mash->NAME); if ($mash->NOTES) $sql .= "', notes='" . mysqli_real_escape_string($db, $mash->NOTES); /* * Put the steps in a json array */ if ($mash->MASH_STEPS) { $steps = "["; $comma = FALSE; foreach ($mash->MASH_STEPS->MASH_STEP as $step) { if ($comma) $steps .= ','; $comma = TRUE; $steps .= '{"step_name":"' . mysqli_real_escape_string($db, $step->NAME) . '"'; if ($step->TYPE) { $steps .= ',"step_type":"' . $step->TYPE . '"'; } if ($step->STEP_TEMP) { $steps .= ',"step_temp":"' . floatval($step->STEP_TEMP) . '"'; } if ($step->STEP_TIME) { $steps .= ',"step_time":"' . floatval($step->STEP_TIME) . '"'; } if ($step->RAMP_TIME) { $steps .= ',"ramp_time":"' . floatval($step->RAMP_TIME) . '"'; } if ($step->END_TEMP) { $steps .= ',"end_temp":"' . floatval($step->END_TEMP) . '"'; } $steps .= "}"; } $steps .= ']'; $sql .= "', steps='" . $steps; } $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } } /* * Return recipe ingredients as json functions. */ function recipe_style($recipe) { global $db; if ($recipe->STYLE->NAME) $sql = "', st_name='" . mysqli_real_escape_string($db, $recipe->STYLE->NAME); if ($recipe->STYLE->STYLE_LETTER) $sql .= "', st_letter='" . mysqli_real_escape_string($db, $recipe->STYLE->STYLE_LETTER); if ($recipe->STYLE->STYLE_GUIDE) { if ($recipe->STYLE->STYLE_GUIDE == "Biertypengids Derek Walsh") { $sql .= "', st_guide='BKG 2015"; } else if ($recipe->STYLE->STYLE_GUIDE == "BKG Biertypen") { $sql .= "', st_guide='BKG 2015"; } else { $sql .= "', st_guide='" . mysqli_real_escape_string($db, $recipe->STYLE->STYLE_GUIDE); } } if ($recipe->STYLE->CATEGORY) $sql .= "', st_category='" . mysqli_real_escape_string($db, $recipe->STYLE->CATEGORY); if ($recipe->STYLE->CATEGORY_NUMBER) $sql .= "', st_category_number='" . floatval($recipe->STYLE->CATEGORY_NUMBER); if ($recipe->STYLE->TYPE) $sql .= "', st_type='" . mysqli_real_escape_string($db, $recipe->STYLE->TYPE); if ($recipe->STYLE->OG_MIN) $sql .= "', st_og_min='" . floatval($recipe->STYLE->OG_MIN); if ($recipe->STYLE->OG_MAX) $sql .= "', st_og_max='" . floatval($recipe->STYLE->OG_MAX); if ($recipe->STYLE->FG_MIN) $sql .= "', st_fg_min='" . floatval($recipe->STYLE->FG_MIN); if ($recipe->STYLE->FG_MAX) $sql .= "', st_fg_max='" . floatval($recipe->STYLE->FG_MAX); if ($recipe->STYLE->IBU_MIN) $sql .= "', st_ibu_min='" . floatval($recipe->STYLE->IBU_MIN); if ($recipe->STYLE->IBU_MAX) $sql .= "', st_ibu_max='" . floatval($recipe->STYLE->IBU_MAX); if ($recipe->STYLE->COLOR_MIN) { $srm = floatval($recipe->STYLE->COLOR_MIN); $sql .= "', st_color_min='" . srm_to_ebc($srm); } if ($recipe->STYLE->COLOR_MAX) { $srm = floatval($recipe->STYLE->COLOR_MAX); $sql .= "', st_color_max='" . srm_to_ebc($srm); } if ($recipe->STYLE->CARB_MIN) $sql .= "', st_carb_min='" . floatval($recipe->STYLE->CARB_MIN); if ($recipe->STYLE->CARB_MAX) $sql .= "', st_carb_max='" . floatval($recipe->STYLE->CARB_MAX); if ($recipe->STYLE->ABV_MIN) $sql .= "', st_abv_min='" . floatval($recipe->STYLE->ABV_MIN); if ($recipe->STYLE->ABV_MAX) $sql .= "', st_abv_max='" . floatval($recipe->STYLE->ABV_MAX); return $sql; } function recipe_fermentables($recipe) { global $db; global $pCara; global $pSugar; global $batch_size; global $efficiency; global $colorw; global $f_sugars; $fermentables = "["; $comma = FALSE; foreach ($recipe->FERMENTABLES->FERMENTABLE as $fermentable) { if ($comma) $fermentables .= ','; $comma = TRUE; $fermentables .= '{"f_name":"' . mysqli_real_escape_string($db, $fermentable->NAME) . '"'; $fermentables .= ',"f_origin":"' . mysqli_real_escape_string($db, $fermentable->ORIGIN) . '"'; $fermentables .= ',"f_supplier":"' . mysqli_real_escape_string($db, $fermentable->SUPPLIER) . '"'; $famount = floatval($fermentable->AMOUNT); $fermentables .= ',"f_amount":' . $famount; $fermentables .= ',"f_cost":' . floatval($fermentable->COST); $ftype = mysqli_real_escape_string($db, $fermentable->TYPE); $fermentables .= ',"f_type":"' . $ftype . '"'; $fyield = floatval($fermentable->YIELD); $fermentables .= ',"f_yield":' . $fyield; if ($fermentable->COLOR) { $srm = floatval($fermentable->COLOR); $ebc = srm_to_ebc($srm); } else { $srm = 0; $ebc = 0; } $colorw += ($famount * $srm / $batch_size) * 8.34436; /* Kleurwerking */ $fermentables .= ',"f_color":' . $ebc; if ($fermentable->COARSE_FINE_DIFF) $fermentables .= ',"f_coarse_fine_diff":' . floatval($fermentable->COARSE_FINE_DIFF); else $fermentables .= ',"f_coarse_fine_diff":0'; if ($fermentable->MOISTURE) $fmoisture = floatval($fermentable->MOISTURE); else $fmoisture = 0; $fermentables .= ',"f_moisture":' . $fmoisture; if ($fermentable->DIASTATIC_POWER) $fermentables .= ',"f_diastatic_power":' . floatval($fermentable->DIASTATIC_POWER); else $fermentables .= ',"f_diastatic_power":0'; if ($fermentable->PROTEIN) $fermentables .= ',"f_protein":' . floatval($fermentable->PROTEIN); else $fermentables .= ',"f_protein":0'; if ($fermentable->MAX_IN_BATCH) $fermentables .= ',"f_max_in_batch":' . floatval($fermentable->MAX_IN_BATCH); else $fermentables .= ',"f_max_in_batch":100.0'; $fgraintype = mysqli_real_escape_string($db, $fermentable->GRAINTYPE); if ($fermentable->GRAINTYPE) $fgraintype = mysqli_real_escape_string($db, $fermentable->GRAINTYPE); else $fgraintype = "Base"; $fermentables .= ',"f_graintype":"' . $fgraintype . '"'; if ($fermentable->ADDED) $fadded = mysqli_real_escape_string($db, $fermentable->ADDED); else $fadded = "Mash"; $fermentables .= ',"f_added":"'.$fadded.'"'; ($fermentable->ADD_AFTER_BOIL== "TRUE") ? $fermentables .= ',"f_add_after_boil":true' : $fermentables .= ',"f_add_after_boil":false'; ($fermentable->RECOMMEND_MASH== "TRUE") ? $fermentables .= ',"f_recommend_mash":true' : $fermentables .= ',"f_recommend_mash":false'; if ($fermentable->DISSOLVED_PROTEIN) $fermentables .= ',"f_dissolved_protein":' . floatval($fermentable->DISSOLVED_PROTEIN); else $fermentables .= ',"f_dissolved_protein":0'; ($fermentable->ADJUST_TO_TOTAL_100 == "TRUE") ? $fermentables .= ',"f_adjust_to_total_100":true' : $fermentables .= ',"f_adjust_to_total_100":false'; $percent = floatval($fermentable->PERCENTAGE); $fermentables .= ',"f_percentage":' . round($percent * 10) / 10.0; if ($fermentable->DI_pH) $fermentables .= ',"f_di_ph":' . floatval($fermentable->DI_pH); else $fermentables .= ',"f_di_ph":0.0'; $fermentables .= "}"; /* Sugars */ $d = $famount * ($fyield / 100) * (1 - $fmoisture / 100); if ($fadded == "Mash") $d = floatval($efficiency) / 100 * $d; $f_sugars += $d; if ($fgraintype == "Crystal") $pCara += $percent; if ($ftype == "Sugar") $pSugar += $percent; } $fermentables .= ']'; return $fermentables; } function recipe_hops($recipe) { global $db; $hops = "["; $comma = FALSE; foreach ($recipe->HOPS->HOP as $hop) { if ($comma) $hops .= ','; $comma = TRUE; $hops .= '{"h_name":"' . mysqli_real_escape_string($db, $hop->NAME) . '"'; $hops .= ',"h_amount":' . floatval($hop->AMOUNT); $hops .= ',"h_cost":' . floatval($hop->COST); $hops .= ',"h_type":"' . mysqli_real_escape_string($db, $hop->TYPE) . '"'; $hops .= ',"h_form":"' . mysqli_real_escape_string($db, $hop->FORM) . '"'; $hops .= ',"h_useat":"' . mysqli_real_escape_string($db, $hop->USE) . '"'; if ($hop->TIME) $hops .= ',"h_time":' . floatval($hop->TIME); else $hops .= ',"h_time":0'; $hops .= ',"h_alpha":' . floatval($hop->ALPHA); $hops .= ',"h_beta":' . floatval($hop->BETA); $hops .= ',"h_hsi":' . floatval($hop->HSI); $hops .= ',"h_humulene":' . floatval($hop->HUMULENE); $hops .= ',"h_carophyllene":' . floatval($hop->CAROPHYLLENE); $hops .= ',"h_cohumulone":' . floatval($hop->COHUMULONE); $hops .= ',"h_myrcene":' . floatval($hop->MYRCENE); if ($hop->TOTAL_OIL) $hops .= ',"h_total_oil":' . floatval($hop->TOTAL_OIL); else $hops .= ',"h_total_oil":0'; if ($hop->ORIGIN) $hops .= ',"h_origin":"' . mysqli_real_escape_string($db, $hop->ORIGIN) . '"'; else $hops .= ',"h_origin":""'; $hops .= "}"; } $hops .= ']'; return $hops; } function recipe_miscs($recipe) { global $db; $miscs = "["; $comma = FALSE; foreach ($recipe->MISCS->MISC as $misc) { if ($comma) $miscs .= ','; $comma = TRUE; $mname = mysqli_real_escape_string($db, $misc->NAME); $miscs .= '{"m_name":"' . $mname . '"'; $miscs .= ',"m_amount":' . floatval($misc->AMOUNT); if ($misc->COST) { $miscs .= ',"m_cost":' . floatval($misc->COST); } else { /* Brouwhulp bug, added water agents have no cost field. */ if ($misc->TYPE == "Water agent") { $miscs .= ',"m_cost":'. get_miscs_cost($mname); } else { $miscs .= ',"m_cost":0'; } } $miscs .= ',"m_type":"' . mysqli_real_escape_string($db, $misc->TYPE) . '"'; $miscs .= ',"m_use_use":"' . mysqli_real_escape_string($db, $misc->USE) . '"'; ($misc->AMOUNT_IS_WEIGHT== "TRUE") ? $miscs .= ',"m_amount_is_weight":true' : $miscs.= ',"m_amount_is_weight":false'; if ($misc->TIME) $miscs .= ',"m_time":' . floatval($misc->TIME); else $miscs .= ',"m_time":0'; $miscs .= "}"; } $miscs .= ']'; return $miscs; } function recipe_yeasts($recipe) { global $db; $yeasts = "["; $comma = FALSE; foreach ($recipe->YEASTS->YEAST as $yeast) { if ($comma) $yeasts .= ','; $comma = TRUE; $yeasts .= '{"y_name":"' . mysqli_real_escape_string($db, $yeast->NAME) . '"'; $yeasts .= ',"y_amount":' . floatval($yeast->AMOUNT); if ($yeast->COST) { if (($yeast->FORM == "Liquid") && ($yeast->AMOUNT == "0.0588")) { // Packs $yeasts .= ',"y_cost":' . floatval($yeast->COST) / 58.8; } else { $yeasts .= ',"y_cost":' . floatval($yeast->COST); } } else $yeasts .= ',"y_cost":0'; $yeasts .= ',"y_laboratory":"' . mysqli_real_escape_string($db, $yeast->LABORATORY) . '"'; $yeasts .= ',"y_product_id":"' . mysqli_real_escape_string($db, $yeast->PRODUCT_ID) . '"'; $yeasts .= ',"y_type":"' . mysqli_real_escape_string($db, $yeast->TYPE) . '"'; $yeasts .= ',"y_form":"' . mysqli_real_escape_string($db, $yeast->FORM) . '"'; ($yeast->AMOUNT_IS_WEIGHT== "TRUE") ? $yeasts .= ',"y_amount_is_weight":true' : $yeasts.= ',"y_amount_is_weight":false'; if ($yeast->ADD_TO_SECONDARY=="FALSE") { $yeasts .= ',"y_use":"Primary"'; $svg = floatval($yeast->ATTENUATION); } else if ($yeast->PRODUCT_ID=="F2") { $yeasts .= ',"y_use":"Bottle"'; } else { $yeasts .= ',"y_use":"Secondary"'; } $yeasts .= ',"y_min_temperature":' . floatval($yeast->MIN_TEMPERATURE); $yeasts .= ',"y_max_temperature":' . floatval($yeast->MAX_TEMPERATURE); $yeasts .= ',"y_attenuation":' . floatval($yeast->ATTENUATION); $yeasts .= "}"; } $yeasts .= ']'; return $yeasts; } function recipe_waters($recipe, $db) { $waters = "["; $comma = FALSE; foreach ($recipe->WATERS->WATER as $water) { if ($comma) $waters .= ','; $comma = TRUE; $waters .= '{"w_name":"' . mysqli_real_escape_string($db, $water->NAME) . '"'; $waters .= ',"w_amount":' . floatval($water->AMOUNT); if ($water->CALCIUM) $waters .= ',"w_calcium":' . floatval($water->CALCIUM); if ($water->SULFATE) $waters .= ',"w_sulfate":' . floatval($water->SULFATE); if ($water->CHLORIDE) $waters .= ',"w_chloride":' . floatval($water->CHLORIDE); if ($water->SODIUM) $waters .= ',"w_sodium":' . floatval($water->SODIUM); if ($water->MAGNESIUM) $waters .= ',"w_magnesium":' . floatval($water->MAGNESIUM); if ($water->PH) $waters .= ',"w_ph":' . floatval($water->PH); if ($water->TOTAL_ALKALINITY) $waters .= ',"w_total_alkalinity":' . floatval($water->TOTAL_ALKALINITY); if ($water->COST) $waters .= ',"w_cost":' . floatval($water->COST); else $waters .= ',"w_cost":0.00'; $waters .= "}"; } $waters .= ']'; return $waters; } function recipe_mash_steps($recipe) { global $db; $steps = '['; $comma = FALSE; foreach ($recipe->MASH->MASH_STEPS->MASH_STEP as $step) { if ($comma) $steps .= ','; $comma = TRUE; $steps .= '{"step_name":"' . mysqli_real_escape_string($db, $step->NAME) . '"'; if ($step->TYPE) $steps .= ',"step_type":"' . $step->TYPE . '"'; if ($step->INFUSE_AMOUNT) $steps .= ',"step_infuse_amount":"' . floatval($step->INFUSE_AMOUNT) . '"'; if ($step->STEP_TEMP) $steps .= ',"step_temp":"' . floatval($step->STEP_TEMP) . '"'; if ($step->STEP_TIME) $steps .= ',"step_time":"' . floatval($step->STEP_TIME) . '"'; if ($step->RAMP_TIME) $steps .= ',"ramp_time":"' . floatval($step->RAMP_TIME) . '"'; if ($step->END_TEMP) $steps .= ',"end_temp":"' . floatval($step->END_TEMP) . '"'; $steps .= "}"; } $steps .= ']'; return $steps; } function do_recipes() { global $brouwhulp, $db, $efficiency, $batch_size, $pCara, $pSugar, $colorw, $f_sugars; $len_fermentables = 0; $len_hops = 0; $len_miscs = 0; $len_yeasts = 0; $len_waters = 0; $len_mash = 0; echo " Start adding recipes to the database\n"; $sql = "TRUNCATE TABLE recipes;"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } $recipes = simplexml_load_file($brouwhulp . '/recipes.xml'); foreach ($recipes->RECIPE as $recipe) { $f_sugars = 0; $efficiency = 75; $batch_size = 20; $boil_size = 22; $pCara = 0; $pSugar = 0; $svg = 77; $colorw = 0; $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid')); $sql = "INSERT INTO recipes SET uuid='" . $uuid; $sql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME); $sql .= "', locked='0"; if ($recipe->NOTES) $sql .= "', notes='" . mysqli_real_escape_string($db, $recipe->NOTES); else $sql .= "', notes='"; if ($recipe->TYPE) $sql .= "', type='" . mysqli_real_escape_string($db, $recipe->TYPE); else $sql .= "', type='"; if ($recipe->BATCH_SIZE) $batch_size = floatval($recipe->BATCH_SIZE); $sql .= "', batch_size='" . $batch_size; if ($recipe->BOIL_SIZE) $boil_size = floatval($recipe->BOIL_SIZE); $sql .= "', boil_size='" . $boil_size; if ($recipe->BOIL_TIME) $sql .= "', boil_time='" . floatval($recipe->BOIL_TIME); else $sql .= "', boil_time='90"; if ($recipe->EFFICIENCY) $efficiency = floatval($recipe->EFFICIENCY); $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); if ($recipe->CARBONATION) $sql .= "', est_carb='" . floatval($recipe->CARBONATION); if ($recipe->STYLE) { $sql .= recipe_style($recipe); } /* * Put the fermentables in a json array */ if ($recipe->FERMENTABLES) { $fermentables = recipe_fermentables($recipe); $sql .= "', json_fermentables='" . $fermentables; if (strlen($fermentables) > $len_fermentables) $len_fermentables = strlen($fermentables); } /* * Put the hops in a json array */ if ($recipe->HOPS) { $hops = recipe_hops($recipe); $sql .= "', json_hops='" . $hops; if (strlen($hops) > $len_hops) $len_hops = strlen($hops); } /* * Put the miscs in a json array */ if ($recipe->MISCS) { $miscs = recipe_miscs($recipe); $sql .= "', json_miscs='" . $miscs; if (strlen($miscs) > $len_miscs) $len_miscs = strlen($miscs); } /* * Put the yeasts in a json array */ if ($recipe->YEASTS) { $yeasts = recipe_yeasts($recipe); $sql .= "', json_yeasts='" . $yeasts; if (strlen($yeasts) > $len_yeasts) $len_yeasts = strlen($yeasts); } /* * Put the waters in a json array */ if ($recipe->WATERS) { $waters = recipe_waters($recipe, $db); $sql .= "', json_waters='" . $waters; if (strlen($waters) > $len_waters) $len_waters = strlen($waters); } /* * Put the mash in a json array */ if ($recipe->MASH) { $sql .= "',mash_sparge_temp='" . floatval($recipe->MASH->SPARGE_TEMP); $sql .= "',mash_ph='" . floatval($recipe->MASH->PH); if ($recipe->MASH->NAME) $sql .= "',mash_name='" . mysqli_real_escape_string($db, $recipe->MASH->NAME); if ($recipe->MASH->MASH_STEPS) { $steps = recipe_mash_steps($recipe); $sql .= "', json_mashs='" . $steps; if (strlen($steps) > $len_mash) $len_mash = strlen($steps); } } /* * Added the calculated values * OG, FG, color, IBU */ $og = estimate_sg($f_sugars, $batch_size); $sql .= "', est_og='" . floatval($og); $fg = estimate_fg($pSugar, $pCara, 0, 0, 0, $svg, $og); $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); $sql .= "', est_color='" . floatval($color); $sql .= "';"; if (! $result = mysqli_query($db, $sql)) { printf("Error: %s\n", mysqli_error($db)); } } // echo "Fermentables: " . $len_fermentables . PHP_EOL; // echo "Hops: " . $len_hops . PHP_EOL; // echo "Miscs: " . $len_miscs . PHP_EOL; // echo "Yeasts: " . $len_yeasts . PHP_EOL; // echo "Waters: " . $len_waters . PHP_EOL; // echo "Mash: " . $len_mash . PHP_EOL; } function do_brews() { global $brouwhulp, $db, $efficiency, $batch_size, $pCara, $pSugar, $colorw, $f_sugars; $len_fermentables = 0; $len_hops = 0; $len_miscs = 0; $len_yeasts = 0; $len_waters = 0; $len_mash = 0; echo " Start adding brews to the database\n"; $psql = "TRUNCATE TABLE prod_main;"; if (! $presult = mysqli_query($db, $psql)) { printf("Error: %s\n", mysqli_error($db)); } $rsql = "TRUNCATE TABLE prod_recipes;"; if (! $rresult = mysqli_query($db, $rsql)) { 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) { $f_sugars = 0; $efficiency = 75; $batch_size = 20; $boil_size = 22; $pCara = 0; $pSugar = 0; $svg = 77; $colorw = 0; $stage = "Plan"; // Default value. $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid')); $psql = "INSERT INTO prod_main SET puuid='" . $uuid; // So we can link the records. $rsql = "INSERT INTO prod_recipes SET uuid='" . $uuid; $psql .= "', pname='" . mysqli_real_escape_string($db, $recipe->NAME); $rsql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME); if ($recipe->NOTES) { $rsql .= "', notes='" . mysqli_real_escape_string($db, $recipe->NOTES); // Duplicate the notes $psql .= "', pnotes='" . mysqli_real_escape_string($db, $recipe->NOTES); } else { $rsql .= "', notes='"; $psql .= "', pnotes='"; } if ($recipe->TYPE) $rsql .= "', type='" . mysqli_real_escape_string($db, $recipe->TYPE); else $rsql .= "', type='"; if ($recipe->BATCH_SIZE) $batch_size = floatval($recipe->BATCH_SIZE); $rsql .= "', batch_size='" . $batch_size; if ($recipe->BOIL_SIZE) $boil_size = floatval($recipe->BOIL_SIZE); $rsql .= "', boil_size='" . $boil_size; if ($recipe->BOIL_TIME) $rsql .= "', boil_time='" . floatval($recipe->BOIL_TIME); else $rsql .= "', boil_time='90"; if ($recipe->EFFICIENCY) $efficiency = floatval($recipe->EFFICIENCY); $rsql .= "', 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) $rsql .= "', color_method='" . mysqli_real_escape_string($db, $recipe->COLOR_METHOD); if ($recipe->IBU) $rsql .= "', est_ibu='" . floatval($recipe->IBU); if ($recipe->IBU_METHOD) $rsql .= "', ibu_method='" . mysqli_real_escape_string($db, $recipe->IBU_METHOD); if ($recipe->CARBONATION) $rsql .= "', est_carb='" . floatval($recipe->CARBONATION); if ($recipe->STYLE) { $rsql .= recipe_style($recipe); } /* * Put the fermentables in a json array */ if ($recipe->FERMENTABLES) { $fermentables = recipe_fermentables($recipe); $rsql .= "', json_fermentables='" . $fermentables; if (strlen($fermentables) > $len_fermentables) $len_fermentables = strlen($fermentables); } /* * Put the hops in a json array */ if ($recipe->HOPS) { $hops = recipe_hops($recipe); $rsql .= "', json_hops='" . $hops; if (strlen($hops) > $len_hops) $len_hops = strlen($hops); } /* * Put the miscs in a json array */ if ($recipe->MISCS) { $miscs = recipe_miscs($recipe); $rsql .= "', json_miscs='" . $miscs; if (strlen($miscs) > $len_miscs) $len_miscs = strlen($miscs); } /* * Put the yeasts in a json array */ if ($recipe->YEASTS) { $yeasts = recipe_yeasts($recipe); $rsql .= "', json_yeasts='" . $yeasts; if (strlen($yeasts) > $len_yeasts) $len_yeasts = strlen($yeasts); } /* * Put the waters in a json array */ if ($recipe->WATERS) { $waters = recipe_waters($recipe, $db); $rsql .= "', json_waters='" . $waters; if (strlen($waters) > $len_waters) $len_waters = strlen($waters); } /* * Put the mash in a json array */ if ($recipe->MASH) { $rsql .= "',mash_sparge_temp='" . floatval($recipe->MASH->SPARGE_TEMP); $rsql .= "',mash_ph='" . floatval($recipe->MASH->PH); if ($recipe->MASH->NAME) $rsql .= "',mash_name='" . mysqli_real_escape_string($db, $recipe->MASH->NAME); if ($recipe->MASH->MASH_STEPS) { $steps = recipe_mash_steps($recipe); $rsql .= "', json_mashs='" . $steps; if (strlen($steps) > $len_mash) $len_mash = strlen($steps); } } /* * Added the calculated values * OG, FG, color, IBU */ $og = estimate_sg($f_sugars, $batch_size); $rsql .= "', est_og='" . floatval($og); $fg = estimate_fg($pSugar, $pCara, 0, 0, 0, $svg, $og); $rsql .= "', est_fg='" . floatval($fg); $abv = abvol($og, $fg); $rsql .= "', est_abv='" . floatval($abv); $color = kw_to_ebc(mysqli_real_escape_string($db, $recipe->COLOR_METHOD), $colorw); $rsql .= "', est_color='" . floatval($color); $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"; } else { // See if there really are no records $sql = "SELECT product_code FROM log_fermentation WHERE product_code='".$recipe->NR_RECIPE."';"; $count = mysqli_affected_rows($db); if ($count > 0) { $psql .= "', 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)); } 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); $psql .= "', eq_notes='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->NOTES); $psql .= "', eq_boil_size='" . floatval($recipe->EQUIPMENT->BOIL_SIZE); $psql .= "', eq_batch_size='" . floatval($recipe->EQUIPMENT->BATCH_SIZE); $psql .= "', eq_tun_volume='" . floatval($recipe->EQUIPMENT->TUN_VOLUME); $psql .= "', eq_tun_weight='" . floatval($recipe->EQUIPMENT->TUN_WEIGHT); $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); 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); /* * Brouwhulp uses a percentage for the evaporation rate. This is wrong * but was made so because the beerxml standard requires this. What we * do is calculate the actual evaporation and store that. * This is what we use. Brouwhulp calculates this on the fly. */ $psql .= "', eq_evap_rate='" . (floatval($recipe->EQUIPMENT->EVAP_RATE) * floatval($recipe->EQUIPMENT->BOIL_SIZE)) / 100; $psql .= "', eq_boil_time='" . floatval($recipe->EQUIPMENT->BOIL_TIME); ($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); $psql .= "', eq_lauter_deadspace='" . floatval($recipe->EQUIPMENT->LAUTER_DEADSPACE); $psql .= "', eq_kettle_volume='" . floatval($recipe->EQUIPMENT->KETTLE_VOLUME); $psql .= "', eq_kettle_height='" . floatval($recipe->EQUIPMENT->KETTLE_HEIGHT); $psql .= "', eq_mash_volume='" . floatval($recipe->EQUIPMENT->MASH_VOLUME); $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 */ $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) $psql .= "', brew_mash_ph='" . floatval($recipe->PH_ADJUSTED); if (floatval($recipe->SG_END_MASH) != 1.0) $psql .= "', brew_mash_sg='" . floatval($recipe->SG_END_MASH); if ($recipe->MASH->SPARGE_TEMP) $psql .= "', brew_sparge_temperature='" . floatval($recipe->MASH->SPARGE_TEMP); if ($recipe->MASH->PH) $psql .= "', brew_sparge_ph='" . floatval($recipe->MASH->PH); if ($recipe->VOLUME_HLT) $psql .= "', brew_sparge_volume='" . floatval($recipe->VOLUME_HLT); if ($recipe->VOLUME_BEFORE_BOIL) $psql .= "', brew_preboil_volume='" . floatval($recipe->VOLUME_BEFORE_BOIL); if (floatval($recipe->OG_BEFORE_BOIL) != 1.0) $psql .= "', brew_preboil_sg='" . floatval($recipe->OG_BEFORE_BOIL); if ($recipe->PH_BEFORE_BOIL) $psql .= "', brew_preboil_ph='" . floatval($recipe->PH_BEFORE_BOIL); if ($recipe->VOLUME_AFTER_BOIL) $psql .= "', brew_aboil_volume='" . floatval($recipe->VOLUME_AFTER_BOIL); $psql .= "', brew_aboil_sg='" . floatval($recipe->OG); if ($recipe->PH_AFTER_BOIL) $psql .= "', brew_aboil_ph='" . floatval($recipe->PH_AFTER_BOIL); if ($recipe->ACTUAL_EFFICIENCY) $psql .= "', brew_aboil_efficiency='" . floatval($recipe->ACTUAL_EFFICIENCY); $psql .= "', brew_whirlpool2='" . floatval($recipe->WHIRLPOOL_TIME); $psql .= "', brew_cooling_method='" . mysqli_real_escape_string($db, $recipe->COOLING_METHOD); $psql .= "', brew_cooling_time='" . floatval($recipe->COOLING_TIME); $psql .= "', brew_cooling_to='" . floatval($recipe->COOLING_TO); if ($recipe->VOLUME_FERMENTER) $psql .= "', brew_fermenter_volume='" . floatval($recipe->VOLUME_FERMENTER); if ($recipe->EQUIPMENT->TOP_UP_WATER_BREWDAY) $psql .= "', brew_fermenter_extrawater='" . floatval($recipe->EQUIPMENT->TOP_UP_WATER_BREWDAY); $psql .= "', brew_fermenter_sg='" . floatval($recipe->OG_FERMENTER); $psql .= "', brew_fermenter_ibu='" . floatval($recipe->IBU); $psql .= "', brew_aeration_type='" . mysqli_real_escape_string($db, $recipe->AERATION_TYPE); if ($recipe->AERATION_TYPE != "None") { $psql .= "', brew_aeration_speed='" . floatval($recipe->AERATION_SPEED); $psql .= "', brew_aeration_time='" . floatval($recipe->AERATION_TIME); } $psql .= "', brew_date_end='" . $date_end; } 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"; 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"; 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 .= "', plocked='1"; $rsql .= "', locked='1"; $stage = "Closed"; } else { $psql .= "', plocked='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)); } } } do_fermentables(); do_hops(); do_yeasts(); do_miscs(); do_waters(); do_equipments(); do_styles(); do_mash(); do_recipes(); do_brews(); mysqli_close($db); echo "Finished adding data\n"; ?>
