www/import/from_brouwhulp.php

<?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";


?>