www/includes/db_product.php

Thu, 29 Nov 2018 23:21:42 +0100

author
Michiel Broek <mbroek@mbse.eu>
date
Thu, 29 Nov 2018 23:21:42 +0100
changeset 115
ad31f4bd4036
parent 114
4935e86b2775
child 119
ae5e8d740173
permissions
-rw-r--r--

Inventory database script added missing fields. Product database script added JOIN with the prod_recipes table.

<?php

require($_SERVER['DOCUMENT_ROOT']."/config.php");
require($_SERVER['DOCUMENT_ROOT']."/version.php");

#Connect to the database
$connect = mysqli_connect(DBASE_HOST, DBASE_USER, DBASE_PASS, DBASE_NAME);
if (! $connect) {
	die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}
mysqli_set_charset($connect, "utf8" );

$escapers = array("\\", "/", "\"", "\n", "\r", "\t", "\x08", "\x0c");
$replacements = array("\\\\", "\\/", "\\\"", "\\n", "\\r", "\\t", "\\f", "\\b");


if (isset($_POST['insert']) || isset($_POST['update'])) {
	if (isset($_POST['insert'])) {
		$sql  = "INSERT INTO `prod_main` SET ";
	}
	if (isset($_POST['update'])) {
		$sql  = "UPDATE `prod_main` SET ";
	}
	// Basic settings
	$sql .=    "uuid='" . $_POST['uuid'];
	$sql .= "', name='" . mysqli_real_escape_string($connect, $_POST['name']);
	$sql .= "', birth='" . $_POST['birth'];
	$sql .= "', stage='" . $_POST['stage'];
	$sql .= "', notes='" . mysqli_real_escape_string($connect, $_POST['notes']);
	($_POST['log_brew'] == 'true') ? $sql .= "', log_brew='1" : $sql .= "', log_brew='0";
	($_POST['log_fermentation'] == 'true') ? $sql .= "', log_fermentation='1" : $sql .= "', log_fermentation='0";
	($_POST['inventory_reduced'] == 'true') ? $sql .= "', inventory_reduced='1" : $sql .= "', inventory_reduced='0";
	($_POST['locked'] == 'true') ? $sql .= "', locked='1" : $sql .= "', locked='0";
	// Equipment
	$sql .= "', eq_name='" . mysqli_real_escape_string($connect, $_POST['eq_name']);
	$sql .= "', eq_boil_size='" . $_POST['eq_boil_size'];
	$sql .= "', eq_batch_size='" . $_POST['eq_batch_size'];
	$sql .= "', eq_tun_volume='" . $_POST['eq_tun_volume'];
	$sql .= "', eq_tun_weight='" . $_POST['eq_tun_weight'];
	$material = mysqli_real_escape_string($connect, $_GET['tun_material']);
	$sql .= "', eq_tun_material='" . $material;
	if ($material == "RVS") {
		$sql .= "', eq_tun_specific_heat='0.11";
	} else if ($material == "Aluminium") {
		$sql .= "', eq_tun_specific_heat='0.22";
	} else if ($material == "Kunststof") {
		$sql .= "', eq_tun_specific_heat='0.46";
	} else if ($material == "Koper") {
		$sql .= "', eq_tun_specific_heat='0.092";
	}
	$sql .= "', eq_tun_height='" . $_POST['eq_tun_height'];
	$sql .= "', eq_top_up_water='" . $_POST['eq_top_up_water'];
	$sql .= "', eq_trub_chiller_loss='" . $_POST['eq_trub_chiller_loss'];
	$sql .= "', eq_evap_rate='" . $_POST['eq_evap_rate'];
	$sql .= "', eq_boil_time='" . $_POST['eq_boil_time'];
	$sql .= "', eq_calc_boil_volume='" . $_POST['eq_calc_boil_volume'];
	$sql .= "', eq_top_up_kettle='" . $_POST['eq_top_up_kettle'];
	$sql .= "', eq_hop_utilization='" . $_POST['eq_hop_utilization'];
	$sql .= "', eq_lauter_volume='" . $_POST['eq_lauter_volume'];
	$sql .= "', eq_lauter_height='" . $_POST['eq_lauter_height'];
	$sql .= "', eq_lauter_deadspace='" . $_POST['eq_lauter_deadspace'];
	$sql .= "', eq_kettle_volume='" . $_POST['eq_kettle_volume'];
	$sql .= "', eq_kettle_height='" . $_POST['eq_kettle_height'];
	$sql .= "', eq_mash_volume='" . $_POST['eq_mash_volume'];
	$sql .= "', eq_efficiency='" . $_POST['eq_efficiency'];
	$sql .= "', eq_top_up_water='" . $_POST['eq_top_up_water'];

	syslog(LOG_NOTICE, $sql);
	if (isset($_POST['insert'])) {
		$sql .= "';";
	}
	if (isset($_POST['update'])) {
		$sql .= "' WHERE record='" . $_POST['record'] . "';";
	}


//	$result = mysqli_query($connect, $sql);
//	if (! $result) {
//		syslog(LOG_NOTICE, "db_product: result: ".mysqli_error($connect));
//	} else {
//		if (isset($_POST['update'])) {
//			syslog(LOG_NOTICE, "db_product: updated record ".$_POST['record']);
//		} else {
//			$lastid = mysqli_insert_id($connect);
//			syslog(LOG_NOTICE, "db_product: inserted record ".$lastid);
//		}
//	}
//	echo $result;

} else if (isset($_POST['delete'])) {
	// DELETE COMMAND
	$sql = "DELETE FROM `prod_main` WHERE record='".$_POST['record']."';";
	syslog(LOG_NOTICE, $sql);
//	$result = mysqli_query($connect, $sql);
//	if (! $result) {
//		syslog(LOG_NOTICE, "db_product: result: ".mysqli_error($connect));
//	} else {
//		syslog(LOG_NOTICE, "db_product: deleted record ".$_POST['record']);
//	}
//	echo $result;

} else {
//	syslog(LOG_NOTICE, "db_product: select");
//	if (isset($_GET['select'])) {
//		syslog(LOG_NOTICE, "db_product: select isset ".$_GET['select']);
//	}
	// SELECT COMMAND
	if (isset($_GET['select']) && ($_GET['select'] == "inprod")) {
		$query = "SELECT record,name,code,birth,stage FROM prod_main WHERE stage != 'Closed' ORDER BY birth,code;";
		$result = mysqli_query($connect, $query) or die("SQL Error 1: " . mysqli_error($connect));
		while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
			$brews[] = array(
				'record' => $row['record'],
				'name' => $row['name'],
				'code' => $row['code'],
				'birth' => $row['birth'],
				'stage' => $row['stage']
			);
		}
		echo json_encode($brews);
		return;
	}

	/*
	 * Default, select all
	 */
	if (isset($_GET['record'])) {
		syslog(LOG_NOTICE, "Request record GET " . $_GET['record']);
	} else if (isset($_POST['record'])) {
		syslog(LOG_NOTICE, "Request record POST " . $_POST['record']);
	} else {
		syslog(LOG_NOTICE, "No record requested");
	}
	$query = "SELECT * FROM prod_main LEFT JOIN prod_recipes ON prod_main.uuid = prod_recipes.uuid ORDER BY birth,code;";
	$result = mysqli_query($connect, $query) or die("SQL Error 1: " . mysqli_error($connect));
	$brews = '[';
	$comma = FALSE;
	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
		// Manual encode to JSON.
		if ($comma)
			$brews .= ',';
		$comma = TRUE;
		$brew  = '{"record":' . $row['record'];
		$brew .= ',"uuid":"' . str_replace($escapers, $replacements, $row['uuid']);
		$brew .= '","name":"' . str_replace($escapers, $replacements, $row['name']);
		$brew .= '","code":"' . str_replace($escapers, $replacements, $row['code']);
		$brew .= '","birth":"' . str_replace($escapers, $replacements, $row['birth']);
		$brew .= '","stage":"' . str_replace($escapers, $replacements, $row['stage']);
		$brew .= '","notes":"' . str_replace($escapers, $replacements, $row['notes']);
		$brew .= '","log_brew":' . $row['log_brew'];
		$brew .= ',"log_fermentation":' . $row['log_fermentation'];
		$brew .= ',"inventory_reduced":' . $row['inventory_reduced'];
		$brew .= ',"locked":' . $row['locked'];
		$brew .= ',"eq_name":"' . str_replace($escapers, $replacements, $row['eq_name']);
		$brew .= '","eq_notes":"' . str_replace($escapers, $replacements, $row['eq_notes']);
		$brew .= '","eq_boil_size":' . floatval($row['eq_boil_size']);
		$brew .= ',"eq_batch_size":' . floatval($row['eq_batch_size']);
		$brew .= ',"eq_tun_volume":' . floatval($row['eq_tun_volume']);
		$brew .= ',"eq_tun_weight":' . floatval($row['eq_tun_weight']);
		$brew .= ',"eq_tun_specific_heat":' . floatval($row['eq_tun_specific_heat']);
		$brew .= ',"eq_tun_material":"' . $row['eq_tun_material'];
		$brew .= '","eq_tun_height":' . floatval($row['eq_tun_height']);
		$brew .= ',"eq_top_up_water":' . floatval($row['eq_top_up_water']);
		$brew .= ',"eq_trub_chiller_loss":' . floatval($row['eq_trub_chiller_loss']);
		$brew .= ',"eq_evap_rate":' . floatval($row['eq_evap_rate']);
		$brew .= ',"eq_boil_time":' . floatval($row['eq_boil_time']);
		$brew .= ',"eq_calc_boil_volume":' . $row['eq_calc_boil_volume'];
		$brew .= ',"eq_calc_boil_volume":' . floatval($row['eq_calc_boil_volume']);
		$brew .= ',"eq_top_up_kettle":' . floatval($row['eq_top_up_kettle']);
		$brew .= ',"eq_hop_utilization":' . floatval($row['eq_hop_utilization']);
		$brew .= ',"eq_lauter_volume":' . floatval($row['eq_lauter_volume']);
		$brew .= ',"eq_lauter_height":' . floatval($row['eq_lauter_height']);
		$brew .= ',"eq_lauter_deadspace":' . floatval($row['eq_lauter_deadspace']);
		$brew .= ',"eq_kettle_volume":' . floatval($row['eq_kettle_volume']);
		$brew .= ',"eq_kettle_height":' . floatval($row['eq_kettle_height']);
		$brew .= ',"eq_mash_volume":' . floatval($row['eq_mash_volume']);
		$brew .= ',"eq_efficiency":' . floatval($row['eq_efficiency']);
		$brew .= ',"eq_top_up_water":' . floatval($row['eq_top_up_water']);
		$brew .= ',"brew_date_start":"' . $row['brew_date_start'];
		$brew .= '","brew_mash_ph":' . floatval($row['brew_mash_ph']);
		$brew .= ',"brew_mash_sg":' . floatval($row['brew_mash_sg']);
		$brew .= ',"brew_sparge_temperature":' . floatval($row['brew_sparge_temperature']);
		$brew .= ',"brew_sparge_volume":' . floatval($row['brew_sparge_volume']);
		$brew .= ',"brew_preboil_volume":' . floatval($row['brew_preboil_volume']);
		$brew .= ',"brew_preboil_sg":' . floatval($row['brew_preboil_sg']);
		$brew .= ',"brew_preboil_ph":' . floatval($row['brew_preboil_ph']);
		$brew .= ',"brew_aboil_volume":' . floatval($row['brew_aboil_volume']);
		$brew .= ',"brew_aboil_sg":' . floatval($row['brew_aboil_sg']);
		$brew .= ',"brew_aboil_ph":' . floatval($row['brew_aboil_ph']);
		$brew .= ',"brew_aboil_efficiency":' . floatval($row['brew_aboil_efficiency']);
		$brew .= ',"brew_cooling_method":"' . $row['brew_cooling_method'];
		$brew .= '","brew_cooling_time":' . floatval($row['brew_cooling_time']);
		$brew .= ',"brew_cooling_to":' . floatval($row['brew_cooling_to']);
		$brew .= ',"brew_whirlpool9":' . floatval($row['brew_whirlpool9']);
		$brew .= ',"brew_whirlpool7":' . floatval($row['brew_whirlpool7']);
		$brew .= ',"brew_whirlpool6":' . floatval($row['brew_whirlpool6']);
		$brew .= ',"brew_whirlpool2":' . floatval($row['brew_whirlpool2']);
		$brew .= ',"brew_fermenter_volume":' . floatval($row['brew_fermenter_volume']);
		$brew .= ',"brew_fermenter_extrawater":' . floatval($row['brew_fermenter_extrawater']);
		$brew .= ',"brew_aeration_time":' . floatval($row['brew_aeration_time']);
		$brew .= ',"brew_aeration_speed":' . floatval($row['brew_aeration_speed']);
		$brew .= ',"brew_aeration_type":' . floatval($row['brew_aeration_type']);
		$brew .= ',"brew_fermenter_sg":' . floatval($row['brew_fermenter_sg']);
		$brew .= ',"brew_fermenter_ibu":' . floatval($row['brew_fermenter_ibu']);
		$brew .= ',"brew_date_end":"' . $row['brew_date_end'];
		$brew .= '","brew_log_available":' . $row['brew_log_available'];
		$brew .= ',"primary_start_temp":' . floatval($row['primary_start_temp']);
		$brew .= ',"primary_max_temp":' . floatval($row['primary_max_temp']);
		$brew .= ',"primary_end_temp":' . floatval($row['primary_end_temp']);
		$brew .= ',"primary_end_sg":' . floatval($row['primary_end_sg']);
		$brew .= ',"primary_end_date":"' . $row['primary_end_date'];
		$brew .= '","secondary_temp":' . floatval($row['secondary_temp']);
		$brew .= ',"secondary_end_date":"' . $row['secondary_end_date'];
		$brew .= '","tertiary_temp":' . floatval($row['tertiary_temp']);
		$brew .= ',"package_date":"' . $row['package_date'];
		$brew .= '","bottle_amount":' . floatval($row['bottle_amount']);
		$brew .= ',"bottle_carbonation":' . floatval($row['bottle_carbonation']);
		$brew .= ',"bottle_priming_sugar":"' . str_replace($escapers, $replacements, $row['bottle_priming_sugar']);
		$brew .= '","bottle_priming_amount":' . floatval($row['bottle_priming_amount']);
		$brew .= ',"bottle_carbonation_temp":' . floatval($row['bottle_carbonation_temp']);
		$brew .= ',"keg_amount":' . floatval($row['keg_amount']);
		$brew .= ',"keg_carbonation":' . floatval($row['keg_carbonation']);
		$brew .= ',"keg_priming_sugar":"' . str_replace($escapers, $replacements, $row['keg_priming_sugar']);
		$brew .= '","keg_priming_amount":' . floatval($row['keg_priming_amount']);
		$brew .= ',"keg_carbonation_temp":' . floatval($row['keg_carbonation_temp']);
		$brew .= ',"keg_forced_carb":' . floatval($row['keg_forced_carb']);
		$brew .= ',"keg_pressure":' . floatval($row['keg_pressure']);
		$brew .= ',"keg_priming_factor":' . floatval($row['keg_priming_factor']);
		$brew .= ',"taste_notes":"' . str_replace($escapers, $replacements, $row['taste_notes']);
		$brew .= '","taste_rate":' . floatval($row['taste_rate']);
		$brew .= ',"taste_date":"' . str_replace($escapers, $replacements, $row['taste_date']);
		$brew .= '","taste_color":"' . str_replace($escapers, $replacements, $row['taste_color']);
		$brew .= '","taste_transparency":"' . str_replace($escapers, $replacements, $row['taste_transparency']);
		$brew .= '","taste_head":"' . str_replace($escapers, $replacements, $row['taste_head']);
		$brew .= '","taste_aroma":"' . str_replace($escapers, $replacements, $row['taste_aroma']);
		$brew .= '","taste_taste":"' . str_replace($escapers, $replacements, $row['taste_taste']);
		$brew .= '","taste_mouthfeel":"' . str_replace($escapers, $replacements, $row['taste_mouthfeel']);
		$brew .= '","taste_aftertaste":"' . str_replace($escapers, $replacements, $row['taste_aftertaste']);
		$brew .= '","st_guide":"' . str_replace($escapers, $replacements, $row['st_guide']);
		$brew .= '","st_letter":"' . str_replace($escapers, $replacements, $row['st_letter']);
		$brew .= '","st_name":"'  . str_replace($escapers, $replacements, $row['st_name']);
		$brew .= '","st_type":"' . str_replace($escapers, $replacements, $row['st_type']);
		$brew .= '","st_category":"' . str_replace($escapers, $replacements, $row['st_category']);
		$brew .= '","st_category_number":' . floatval($row['st_category_number']);
		$brew .= ',"st_og_min":' . floatval($row['st_og_min']);
		$brew .= ',"st_og_max":' . floatval($row['st_og_max']);
		$brew .= ',"st_fg_min":' . floatval($row['st_fg_min']);
		$brew .= ',"st_fg_max":' . floatval($row['st_fg_max']);
		$brew .= ',"st_ibu_min":' . floatval($row['st_ibu_min']);
		$brew .= ',"st_ibu_max":' . floatval($row['st_ibu_max']);
		$brew .= ',"st_color_min":' . floatval($row['st_color_min']);
		$brew .= ',"st_color_max":' . floatval($row['st_color_max']);
		$brew .= ',"st_carb_min":' . floatval($row['st_carb_min']);
		$brew .= ',"st_carb_max":' . floatval($row['st_carb_max']);
		$brew .= ',"st_abv_min":' . floatval($row['st_abv_min']);
		$brew .= ',"st_abv_max":' . floatval($row['st_abv_max']);
		$brew .= ',"r_notes":"' . str_replace($escapers, $replacements, $row['notes']);
		$brew .= '","type":"' . $row['type'];
		$brew .= '","batch_size":' . floatval($row['batch_size']);
		$brew .= ',"boil_size":' . floatval($row['boil_size']);
		$brew .= ',"boil_time":' . floatval($row['boil_time']);
		$brew .= ',"efficiency":' . floatval($row['efficiency']);
		$brew .= ',"est_og":' . floatval($row['est_og']);
		$brew .= ',"est_fg":' . floatval($row['est_fg']);
		$brew .= ',"est_abv":' . floatval($row['est_abv']);
		$brew .= ',"est_carb":' . floatval($row['est_carb']);
		$brew .= ',"est_color":' . floatval($row['est_color']);
		$brew .= ',"color_method":"' . $row['color_method'];
		$brew .= '","est_ibu":' . floatval($row['est_ibu']);
		$brew .= ',"ibu_method":"' . $row['ibu_method'];
		$brew .= '","mash_sparge_temp":' . $row['mash_sparge_temp'];
		$brew .= ',"mash_ph":' . $row['mash_ph'];
		$brew .= ',"mash_name":"' . $row['mash_name'];
		$brew .= '","fermentables":' . $row['json_fermentables'];
		$brew .= ',"hops":' . $row['json_hops'];
		$brew .= ',"miscs":' . $row['json_miscs'];
		$brew .= ',"yeasts":' . $row['json_yeasts'];
		$brew .= ',"waters":' . $row['json_waters'];
		$brew .= ',"mashs":' . $row['json_mashs'];
		$brew .= '}';
//		syslog(LOG_NOTICE, $brew);
		$brews .= $brew;
//		syslog(LOG_NOTICE, 'Size: '.strlen($brews).PHP_EOL);
	}
	$brews .= ']';
//	syslog(LOG_NOTICE, $brews);
	header("Content-type: application/json");
	echo $brews;
}
?>

mercurial