# HG changeset patch
# User Michiel Broek <mbroek@mbse.eu>
# Date 1542655316 -3600
# Node ID c64d979613abace1425c2cdc3ddb29ada447455d
# Parent  d06ddc4d1af04a2aab278894465a4143757e1560
Prepare for production brews databases

diff -r d06ddc4d1af0 -r c64d979613ab README.design
--- a/README.design	Mon Nov 19 13:44:07 2018 +0100
+++ b/README.design	Mon Nov 19 20:21:56 2018 +0100
@@ -107,4 +107,6 @@
 	                          |         |            +--------------------	rapport/etiketten
 	                          |         +---------------------------------	Log/rapport
 	                          +-------------------------------------------	Log/rapport
+	Stage:	Plan Wait Brew Primary Secondary Tertiary Package Carbonation Mature Taste Closed
 
+
diff -r d06ddc4d1af0 -r c64d979613ab www/import/from_brouwhulp.php
--- a/www/import/from_brouwhulp.php	Mon Nov 19 13:44:07 2018 +0100
+++ b/www/import/from_brouwhulp.php	Mon Nov 19 20:21:56 2018 +0100
@@ -16,7 +16,12 @@
 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;
@@ -441,10 +446,341 @@
 
 
 
+/*
+ * Return recipe ingredients as json functions.
+ */
+function recipe_style($recipe)
+{
+	global	$db;
 
-function do_recipes($brews)
+	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":' . $percent;
+		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 $brouwhulp, $db;
+	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)
+			$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;
@@ -452,21 +788,13 @@
 	$len_waters = 0;
 	$len_mash = 0;
 
-	if ($brews) {
-		echo "  Start adding brews to the database\n";
-		$sql = "TRUNCATE TABLE production;";
-	} else {
-		echo "  Start adding recipes to the database\n";
-		$sql = "TRUNCATE TABLE recipes;";
-	}
+	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));
 	}
 
-	if ($brews)
-		$recipes = simplexml_load_file($brouwhulp . '/brews.xml');
-	else
-		$recipes = simplexml_load_file($brouwhulp . '/recipes.xml');
+	$recipes = simplexml_load_file($brouwhulp . '/recipes.xml');
 
 	foreach ($recipes->RECIPE as $recipe) {
 		$f_sugars = 0;
@@ -479,10 +807,7 @@
 		$colorw = 0;
 		$uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid'));
 
-		if ($brews)
-			$sql  = "INSERT INTO brews SET uuid='" . $uuid;
-		else
-			$sql  = "INSERT INTO recipes SET uuid='" . $uuid;
+		$sql  = "INSERT INTO recipes SET uuid='" . $uuid;
 		$sql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME);
 		$sql .= "', locked='0";
 		if ($recipe->NOTES)
@@ -519,143 +844,14 @@
 			$sql .= "', est_carb='" . floatval($recipe->CARBONATION);
 
 		if ($recipe->STYLE) {
-			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);
+			$sql .= recipe_style($recipe);
 		}
 
 		/*
 		 * Put the fermentables in a json array
 		 */
 		if ($recipe->FERMENTABLES) {
-			$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":' . $percent;
-				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 .= ']';
-//			echo $fermentables . PHP_EOL;
+			$fermentables = recipe_fermentables($recipe);
 			$sql .= "', json_fermentables='" . $fermentables;
 			if (strlen($fermentables) > $len_fermentables)
 				$len_fermentables = strlen($fermentables);
@@ -665,43 +861,7 @@
 		 * Put the hops in a json array
 		 */
 		if ($recipe->HOPS) {
-			$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 .= ']';
-//			echo $hops . PHP_EOL;
+			$hops = recipe_hops($recipe);
 			$sql .= "', json_hops='" . $hops;
 			if (strlen($hops) > $len_hops)
 				$len_hops = strlen($hops);
@@ -711,37 +871,7 @@
 		 * Put the miscs in a json array
 		 */
 		if ($recipe->MISCS) {
-			$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 .= ']';
-//			echo $miscs . PHP_EOL;
+			$miscs = recipe_miscs($recipe);
 			$sql .= "', json_miscs='" . $miscs;
 			if (strlen($miscs) > $len_miscs)
 				$len_miscs = strlen($miscs);
@@ -751,39 +881,7 @@
 		 * Put the yeasts in a json array
 		 */
 		if ($recipe->YEASTS) {
-			$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)
-					$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 .= ']';
-//			echo $yeasts . PHP_EOL;
+			$yeasts = recipe_yeasts($recipe);
 			$sql .= "', json_yeasts='" . $yeasts;
 			if (strlen($yeasts) > $len_yeasts)
 				$len_yeasts = strlen($yeasts);
@@ -793,37 +891,7 @@
 		 * Put the waters in a json array
 		 */
 		if ($recipe->WATERS) {
-			$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 .= ']';
-//			echo $waters . PHP_EOL;
+			$waters = recipe_waters($recipe, $db);
 			$sql .= "', json_waters='" . $waters;
 			if (strlen($waters) > $len_waters)
 				$len_waters = strlen($waters);
@@ -839,29 +907,7 @@
 				$sql .= "',mash_name='" . mysqli_real_escape_string($db, $recipe->MASH->NAME);
 
 			if ($recipe->MASH->MASH_STEPS) {
-				$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 .= ']';
-//				echo $steps . PHP_EOL;
+				$steps = recipe_mash_steps($recipe);
 				$sql .= "', json_mashs='" . $steps;
 				if (strlen($steps) > $len_mash)
 					$len_mash = strlen($steps);
@@ -895,6 +941,201 @@
 
 
 
+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));
+	}
+
+        $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;
+                $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid'));
+
+		$psql  = "INSERT INTO prod_main SET uuid='" . $uuid;		// So we can link the records.
+		$rsql  = "INSERT INTO prod_recipes SET uuid='" . $uuid;
+
+		$psql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME);
+		$rsql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME);
+		$rsql .= "', locked='0";
+
+                if ($recipe->NOTES)
+                        $rsql .= "', notes='" . mysqli_real_escape_string($db, $recipe->NOTES);
+                else
+                        $rsql .= "', notes='";
+                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);
+                $rsql .= "';";
+                if (! $rresult = mysqli_query($db, $rsql)) {
+                        printf("Error: %s\n", mysqli_error($db));
+		}
+
+		if ($recipe->EQUIPMENT) {
+			$psql .= "', eq_name='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->NAME);
+			$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);
+			//$psql .= "', eq_top_up_water='" . floatval($recipe->EQUIPMENT->);
+			$psql .= "', eq_trub_chiller_loss='" . floatval($recipe->EQUIPMENT->TRUB_CHILLER_LOSS);
+			$psql .= "', eq_evap_rate='" . floatval($recipe->EQUIPMENT->EVAP_RATE);
+			$psql .= "', eq_boil_time='" . floatval($recipe->EQUIPMENT->BOIL_TIME);
+			// eq_calc_boil_volume  CALC_BOIL_VOLUME
+			//$psql .= "', eq_top_up_kettle='" . floatval($recipe->EQUIPMENT->);
+			$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);
+		}
+
+		$psql .= "';";
+		if (! $presult = mysqli_query($db, $psql)) {
+			printf("Error: %s\n", mysqli_error($db));
+		}
+        }
+}
+
+
+
 do_fermentables();
 do_hops();
 do_yeasts();
@@ -903,8 +1144,8 @@
 do_equipments();
 do_styles();
 do_mash();
-do_recipes(0);
-//do_recipes(1);
+do_recipes();
+do_brews();
 
 
 mysqli_close($db);