# HG changeset patch
# User Michiel Broek <mbroek@mbse.eu>
# Date 1542826727 -3600
# Node ID a466373bc23f8afdc7d5fd07940cb6935c809c5d
# Parent  8b4fdadf320c623558f1ddbe6220fa5abf901f65
Finished brews import

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