Mercurial > bms / file comparison

comparison: www/import/from_brouwhulp.php

www/import/from_brouwhulp.php

changeset 108
a466373bc23f
parent 107
8b4fdadf320c
child 111
8c4ba91adf58
equal deleted inserted replaced
107:8b4fdadf320c 108:a466373bc23f
959 $rsql = "TRUNCATE TABLE prod_recipes;"; 959 $rsql = "TRUNCATE TABLE prod_recipes;";
960 if (! $rresult = mysqli_query($db, $rsql)) { 960 if (! $rresult = mysqli_query($db, $rsql)) {
961 printf("Error: %s\n", mysqli_error($db)); 961 printf("Error: %s\n", mysqli_error($db));
962 } 962 }
963 963
964 date_default_timezone_set('Europe/Amsterdam');
964 $recipes = simplexml_load_file($brouwhulp . '/brews.xml'); 965 $recipes = simplexml_load_file($brouwhulp . '/brews.xml');
965 966
966 foreach ($recipes->RECIPE as $recipe) { 967 foreach ($recipes->RECIPE as $recipe) {
967 $f_sugars = 0; 968 $f_sugars = 0;
968 $efficiency = 75; 969 $efficiency = 75;
969 $batch_size = 20; 970 $batch_size = 20;
970 $boil_size = 22; 971 $boil_size = 22;
971 $pCara = 0; 972 $pCara = 0;
972 $pSugar = 0; 973 $pSugar = 0;
973 $svg = 77; 974 $svg = 77;
974 $colorw = 0; 975 $colorw = 0;
976 $stage = "Plan"; // Default value.
975 $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid')); 977 $uuid = str_replace("\n", "", file_get_contents('/proc/sys/kernel/random/uuid'));
976 978
977 $psql = "INSERT INTO prod_main SET uuid='" . $uuid; // So we can link the records. 979 $psql = "INSERT INTO prod_main SET uuid='" . $uuid; // So we can link the records.
978 $rsql = "INSERT INTO prod_recipes SET uuid='" . $uuid; 980 $rsql = "INSERT INTO prod_recipes SET uuid='" . $uuid;
979 981
980 $psql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME); 982 $psql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME);
981 $rsql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME); 983 $rsql .= "', name='" . mysqli_real_escape_string($db, $recipe->NAME);
982 $rsql .= "', locked='0";
983 984
984 if ($recipe->NOTES) 985 if ($recipe->NOTES)
985 $rsql .= "', notes='" . mysqli_real_escape_string($db, $recipe->NOTES); 986 $rsql .= "', notes='" . mysqli_real_escape_string($db, $recipe->NOTES);
986 else 987 else
987 $rsql .= "', notes='"; 988 $rsql .= "', notes='";
1095 $rsql .= "', est_fg='" . floatval($fg); 1096 $rsql .= "', est_fg='" . floatval($fg);
1096 $abv = abvol($og, $fg); 1097 $abv = abvol($og, $fg);
1097 $rsql .= "', est_abv='" . floatval($abv); 1098 $rsql .= "', est_abv='" . floatval($abv);
1098 $color = kw_to_ebc(mysqli_real_escape_string($db, $recipe->COLOR_METHOD), $colorw); 1099 $color = kw_to_ebc(mysqli_real_escape_string($db, $recipe->COLOR_METHOD), $colorw);
1099 $rsql .= "', est_color='" . floatval($color); 1100 $rsql .= "', est_color='" . floatval($color);
1100 $rsql .= "';";
1101 if (! $rresult = mysqli_query($db, $rsql)) {
1102 printf("Error: %s\n", mysqli_error($db));
1103 }
1104 $psql .= "', code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE); 1101 $psql .= "', code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE);
1102
1103 /*
1104 * Update external logfiles
1105 */
1106 $sql = "UPDATE log_brews SET product_uuid='".$uuid."', product_name='".mysqli_real_escape_string($db, $recipe->NAME);
1107 $sql .= "' WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';";
1108 if (! $result = mysqli_query($db, $sql)) {
1109 printf("Error: %s\n", mysqli_error($db));
1110 }
1111 $count = mysqli_affected_rows($db);
1112 if ($count > 0) {
1113 $psql .= "', log_brew='1";
1114 }
1115 $sql = "UPDATE log_fermentation SET product_uuid='".$uuid."', product_name='".mysqli_real_escape_string($db, $recipe->NAME);
1116 $sql .= "' WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';";
1117 if (! $result = mysqli_query($db, $sql)) {
1118 printf("Error: %s\n", mysqli_error($db));
1119 }
1120 $count = mysqli_affected_rows($db);
1121 if ($count > 0) {
1122 $psql .= "', log_fermentation='1";
1123 }
1124
1125 /*
1126 * If no external fermentation log is found, check for an internal one.
1127 */
1128 if (($count == 0) && $recipe->FERM_MEASUREMENTS) {
1129 $lsql = "DELETE FROM log_fermentation WHERE product_code='".mysqli_real_escape_string($db, $recipe->NR_RECIPE)."';";
1130 if (! $lresult = mysqli_query($db, $lsql)) {
1131 printf("Error: %s\n", mysqli_error($db));
1132 }
1133 foreach ($recipe->FERM_MEASUREMENTS->FERM_MEASUREMENT as $measurement) {
1134 $dt = substr($measurement->DATE_TIME, 6, 4).substr($measurement->DATE_TIME,2,4).substr($measurement->DATE_TIME,0,2);
1135 $dt .= ' '.substr($measurement->DATE_TIME, 11,8);
1136 $lsql = "INSERT INTO log_fermentation SET product_uuid='".$uuid;
1137 $lsql .= "', product_code='" . mysqli_real_escape_string($db, $recipe->NR_RECIPE);
1138 $lsql .= "', product_name='" . mysqli_real_escape_string($db, $recipe->NAME);
1139 $lsql .= "', datetime='" . $dt;
1140 $lsql .= "', temperature_air='" . floatval($measurement->TEMP_SENSOR_1);
1141 $lsql .= "', temperature_beer='" . floatval($measurement->TEMP_SENSOR_2);
1142 $lsql .= "';";
1143 if (! $lresult = mysqli_query($db, $lsql)) {
1144 printf("Error: %s\n", mysqli_error($db));
1145 }
1146 }
1147 $psql .= "', log_fermentation='1";
1148 }
1105 1149
1106 if ($recipe->EQUIPMENT) { 1150 if ($recipe->EQUIPMENT) {
1107 $psql .= "', eq_name='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->NAME); 1151 $psql .= "', eq_name='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->NAME);
1108 $psql .= "', eq_boil_size='" . floatval($recipe->EQUIPMENT->BOIL_SIZE); 1152 $psql .= "', eq_boil_size='" . floatval($recipe->EQUIPMENT->BOIL_SIZE);
1109 $psql .= "', eq_batch_size='" . floatval($recipe->EQUIPMENT->BATCH_SIZE); 1153 $psql .= "', eq_batch_size='" . floatval($recipe->EQUIPMENT->BATCH_SIZE);
1110 $psql .= "', eq_tun_volume='" . floatval($recipe->EQUIPMENT->TUN_VOLUME); 1154 $psql .= "', eq_tun_volume='" . floatval($recipe->EQUIPMENT->TUN_VOLUME);
1111 $psql .= "', eq_tun_weight='" . floatval($recipe->EQUIPMENT->TUN_WEIGHT); 1155 $psql .= "', eq_tun_weight='" . floatval($recipe->EQUIPMENT->TUN_WEIGHT);
1112 $psql .= "', eq_tun_specific_heat='" . floatval($recipe->EQUIPMENT->TUN_SPECIFIC_HEAT); 1156 $psql .= "', eq_tun_specific_heat='" . floatval($recipe->EQUIPMENT->TUN_SPECIFIC_HEAT);
1113 $psql .= "', eq_tun_material='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->TUN_MATERIAL); 1157 $psql .= "', eq_tun_material='" . mysqli_real_escape_string($db, $recipe->EQUIPMENT->TUN_MATERIAL);
1114 $psql .= "', eq_tun_height='" . floatval($recipe->EQUIPMENT->TUN_HEIGHT); 1158 $psql .= "', eq_tun_height='" . floatval($recipe->EQUIPMENT->TUN_HEIGHT);
1115 //$psql .= "', eq_top_up_water='" . floatval($recipe->EQUIPMENT->); 1159 if ($recipe->EQUIPMENT->TOP_UP_WATER)
1160 $psql .= "', eq_top_up_water='" . floatval($recipe->EQUIPMENT->TOP_UP_WATER);
1116 $psql .= "', eq_trub_chiller_loss='" . floatval($recipe->EQUIPMENT->TRUB_CHILLER_LOSS); 1161 $psql .= "', eq_trub_chiller_loss='" . floatval($recipe->EQUIPMENT->TRUB_CHILLER_LOSS);
1117 $psql .= "', eq_evap_rate='" . floatval($recipe->EQUIPMENT->EVAP_RATE); 1162 $psql .= "', eq_evap_rate='" . floatval($recipe->EQUIPMENT->EVAP_RATE);
1118 $psql .= "', eq_boil_time='" . floatval($recipe->EQUIPMENT->BOIL_TIME); 1163 $psql .= "', eq_boil_time='" . floatval($recipe->EQUIPMENT->BOIL_TIME);
1119 // eq_calc_boil_volume CALC_BOIL_VOLUME 1164 ($recipe->EQUIPMENT->CALC_BOIL_VOLUME == 'TRUE') ? $psql .= "', eq_calc_boil_volume='1" : $psql .= "', eq_calc_boil_volume='0";
1120 //$psql .= "', eq_top_up_kettle='" . floatval($recipe->EQUIPMENT->); 1165 if ($recipe->EQUIPMENT->TOP_UP_KETTLE)
1166 $psql .= "', eq_top_up_kettle='" . floatval($recipe->EQUIPMENT->TOP_UP_KETTLE);
1121 $psql .= "', eq_hop_utilization='" . floatval($recipe->EQUIPMENT->HOP_UTILIZATION); 1167 $psql .= "', eq_hop_utilization='" . floatval($recipe->EQUIPMENT->HOP_UTILIZATION);
1122 $psql .= "', eq_lauter_volume='" . floatval($recipe->EQUIPMENT->LAUTER_VOLUME); 1168 $psql .= "', eq_lauter_volume='" . floatval($recipe->EQUIPMENT->LAUTER_VOLUME);
1123 $psql .= "', eq_lauter_height='" . floatval($recipe->EQUIPMENT->LAUTER_HEIGHT); 1169 $psql .= "', eq_lauter_height='" . floatval($recipe->EQUIPMENT->LAUTER_HEIGHT);
1124 $psql .= "', eq_lauter_deadspace='" . floatval($recipe->EQUIPMENT->LAUTER_DEADSPACE); 1170 $psql .= "', eq_lauter_deadspace='" . floatval($recipe->EQUIPMENT->LAUTER_DEADSPACE);
1125 $psql .= "', eq_kettle_volume='" . floatval($recipe->EQUIPMENT->KETTLE_VOLUME); 1171 $psql .= "', eq_kettle_volume='" . floatval($recipe->EQUIPMENT->KETTLE_VOLUME);
1126 $psql .= "', eq_kettle_height='" . floatval($recipe->EQUIPMENT->KETTLE_HEIGHT); 1172 $psql .= "', eq_kettle_height='" . floatval($recipe->EQUIPMENT->KETTLE_HEIGHT);
1127 $psql .= "', eq_mash_volume='" . floatval($recipe->EQUIPMENT->MASH_VOLUME); 1173 $psql .= "', eq_mash_volume='" . floatval($recipe->EQUIPMENT->MASH_VOLUME);
1128 $psql .= "', eq_efficiency='" . floatval($recipe->EQUIPMENT->EFFICIENCY); 1174 $psql .= "', eq_efficiency='" . floatval($recipe->EQUIPMENT->EFFICIENCY);
1129 } 1175 }
1130 1176
1177 if (($recipe->DATE) && (! $recipe->TIME_STARTED) && (! $recipe->TIME_ENDED)) {
1178 /* We have a plan date but haven't brewed yet, use current date */
1179 $psql .= "', birth='" . date("Y-m-d");
1180 $stage = "Wait";
1181 }
1182
1131 if (($recipe->DATE) && ($recipe->TIME_STARTED) && ($recipe->TIME_ENDED)) { 1183 if (($recipe->DATE) && ($recipe->TIME_STARTED) && ($recipe->TIME_ENDED)) {
1132 /* We have brew data */ 1184 /* We have brew data */
1133 $date_start = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2).' '.$recipe->TIME_STARTED; 1185 $stage = "Primary"; // Need to think about during a brew...
1134 $date_end = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2).' '.$recipe->TIME_ENDED; 1186 $brewdate = substr($recipe->DATE, 6, 4).substr($recipe->DATE,2,4).substr($recipe->DATE,0,2);
1187 $psql .= "', birth='" . $brewdate;
1188 $date_start = $brewdate.' '.$recipe->TIME_STARTED;
1189 $date_end = $brewdate.' '.$recipe->TIME_ENDED;
1135 $psql .= "', brew_date_start='" . $date_start; 1190 $psql .= "', brew_date_start='" . $date_start;
1136 1191
1137 if ($recipe->PH_ADJUSTED) 1192 if ($recipe->PH_ADJUSTED)
1138 $psql .= "', brew_mash_ph='" . floatval($recipe->PH_ADJUSTED); 1193 $psql .= "', brew_mash_ph='" . floatval($recipe->PH_ADJUSTED);
1139 if (floatval($recipe->SG_END_MASH) != 1.0) 1194 if (floatval($recipe->SG_END_MASH) != 1.0)
1169 if ($recipe->AERATION_TYPE != "None") { 1224 if ($recipe->AERATION_TYPE != "None") {
1170 $psql .= "', brew_aeration_speed='" . floatval($recipe->AERATION_SPEED); 1225 $psql .= "', brew_aeration_speed='" . floatval($recipe->AERATION_SPEED);
1171 $psql .= "', brew_aeration_time='" . floatval($recipe->AERATION_TIME); 1226 $psql .= "', brew_aeration_time='" . floatval($recipe->AERATION_TIME);
1172 } 1227 }
1173 $psql .= "', brew_date_end='" . $date_end; 1228 $psql .= "', brew_date_end='" . $date_end;
1174 echo $date_start . PHP_EOL; 1229 }
1175 } 1230
1176 /* 1231 if ($recipe->PRIMARY_AGE) {
1177 * AGE = onduidelijk 1232 /* PRIMARY_TEMP is the average of START_TEMP_PRIMARY MAX_TEMP_PRIMARY END_TEMP_PRIMARY */
1178 * INVENTORY_REDUCED 1233 $pdate = new DateTime($brewdate);
1179 * LOCKED 1234 $pdate->modify("+".floatval($recipe->PRIMARY_AGE)." days");
1180 * ABV 1235 $psql .= "', primary_start_temp='" . floatval($recipe->START_TEMP_PRIMARY);
1181 */ 1236 $psql .= "', primary_max_temp='" . floatval($recipe->MAX_TEMP_PRIMARY);
1182 1237 $psql .= "', primary_end_temp='" . floatval($recipe->END_TEMP_PRIMARY);
1183 /* 1238 $psql .= "', primary_end_sg='" . floatval($recipe->SG_END_PRIMARY);
1184 * FERMENTATION_STAGES 1239 $psql .= "', primary_end_date='" . $pdate->format("Y-m-d");
1185 * PRIMARY_AGE 1240 $stage = "Secondary";
1186 * PRIMARY_TEMP 1241
1187 * SECONDARY_AGE 1242 if ($recipe->SECONDARY_AGE) {
1188 * SECONDARY_TEMP 1243 $sdate = new DateTime($brewdate);
1189 * TERTIARY_AGE 1244 $sdate->modify("+".floatval($recipe->SECONDARY_AGE)." days");
1190 * TERTIARY_TEMP 1245 $psql .= "', secondary_temp='" . floatval($recipe->SECONDARY_TEMP);
1191 * SG_END_PRIMARY 1246 $psql .= "', secondary_end_date='" . $sdate->format("Y-m-d");
1192 * START_TEMP_PRIMARY 1247 $stage = "Tertiary";
1193 * MAX_TEMP_PRIMARY 1248
1194 * END_TEMP_PRIMARY 1249 if ($recipe->TERTIARY_TEMP) {
1195 */ 1250 $psql .= "', tertiary_temp='" . floatval($recipe->TERTIARY_TEMP);
1196 1251 }
1197 /* 1252 }
1198 * DATE_BOTTLING 1253 }
1199 * AMOUNT_BOTTLING 1254
1200 * CARBONATION 1255 if ($recipe->DATE_BOTTLING && ($recipe->AMOUNT_BOTTLING || $recipe->AMOUNT_KEGGED) &&
1201 * FORCED_CARB_KEGS 1256 ($recipe->AMOUNT_PRIMING || $recipe->AMOUNT_PRIMING_KEGS)) {
1202 * AMOUNT_PRIMING 1257 $bdate = substr($recipe->DATE_BOTTLING, 6, 4).substr($recipe->DATE_BOTTLING,2,4).substr($recipe->DATE_BOTTLING,0,2);
1203 * AMOUNT_PRIMING_KEGS 1258 $psql .= "', package_date='" . $bdate;
1204 * KEG_PRESSURE 1259 $stage = "Package";
1205 * PRIMING_SUGAR_BOTTLES 1260 $dStart = new DateTime($bdate);
1206 * PRIMING_SUGAR_KEGS 1261 $dEnd = new DateTime('');
1207 */ 1262 $dDiff = $dStart->diff($dEnd);
1208 /* 1263 $age = floatval($dDiff->days);
1209 * TASTE 1264 if ($age == 0) // Package day
1210 * TASTE_NOTES 1265 $stage = "Package";
1211 * TASTING_RATE 1266 else if ($age < 14) // Carbonation period
1212 * TASTE_DATE 1267 $stage = "Carbonation";
1213 * TASTE_COLOR 1268 else if ($age < 42) // Mature, fixed 6 weeks
1214 * TASTE_TRANSPARENCY 1269 $stage = "Mature";
1215 * TASTE_HEAD 1270 else // Ready for tasting.
1216 * TASTE_AROMA 1271 $stage = "Taste";
1217 * TASTE_TASTE 1272
1218 * TASTE_MOUTHFEEL 1273 if ($recipe->AMOUNT_BOTTLING && $recipe->AMOUNT_PRIMING) {
1219 * TASTE_AFTERTASTE 1274 $psql .= "', bottle_amount='" . floatval($recipe->AMOUNT_BOTTLING);
1220 */ 1275 $psql .= "', bottle_carbonation='" . floatval($recipe->CARBONATION);
1276 $psql .= "', bottle_priming_sugar='" . mysqli_real_escape_string($db, $recipe->PRIMING_SUGAR_BOTTLES);
1277 $psql .= "', bottle_priming_amount='" . floatval($recipe->AMOUNT_PRIMING);
1278 $psql .= "', bottle_carbonation_temp='" . floatval($recipe->CARBONATION_TEMP);
1279 }
1280 if ($recipe->AMOUNT_KEGGED && $recipe->AMOUNT_PRIMING_KEGS) {
1281 $psql .= "', keg_amount='" . floatval($recipe->AMOUNT_KEGGED);
1282 $psql .= "', keg_carbonation='" . floatval($recipe->CARBONATION); // Lijkt wel hetzelfde als bottles
1283 $psql .= "', keg_priming_sugar='" . mysqli_real_escape_string($db, $recipe->PRIMING_SUGAR_KEGS);
1284 $psql .= "', keg_priming_amount='" . floatval($recipe->AMOUNT_PRIMING_KEGS);
1285 $psql .= "', keg_carbonation_temp='" . floatval($recipe->KEG_CARB_TEMP);
1286 ($recipe->FORCED_CARB_KEGS == 'TRUE') ? $psql .= "', keg_forced_carb='1" : $psql .= "', keg_forced_carb='0";
1287 $psql .= "', keg_pressure='" . floatval($recipe->KEG_PRESSURE);
1288 $psql .= "', keg_priming_factor='" . floatval($recipe->KEG_PRIMING_FACTOR);
1289 }
1290 }
1291
1292 if ($recipe->TASTE_NOTES && $recipe->TASTING_RATE && $recipe->TASTE_DATE) {
1293 $stage = "Ready"; // Ready if tasted.
1294 $psql .= "', taste_notes='" . mysqli_real_escape_string($db, $recipe->TASTE_NOTES);
1295 $psql .= "', taste_rate='" . floatval($recipe->TASTING_RATE);
1296 $tdate = substr($recipe->TASTE_DATE, 6, 4).substr($recipe->TASTE_DATE,2,4).substr($recipe->TASTE_DATE,0,2);
1297 $psql .= "', taste_date='" . $tdate;
1298 $psql .= "', taste_color='" . mysqli_real_escape_string($db, $recipe->TASTE_COLOR);
1299 $psql .= "', taste_transparency='" . mysqli_real_escape_string($db, $recipe->TASTE_TRANSPARENCY);
1300 $psql .= "', taste_head='" . mysqli_real_escape_string($db, $recipe->TASTE_HEAD);
1301 $psql .= "', taste_aroma='" . mysqli_real_escape_string($db, $recipe->TASTE_AROMA);
1302 $psql .= "', taste_taste='" . mysqli_real_escape_string($db, $recipe->TASTE_TASTE);
1303 $psql .= "', taste_mouthfeel='" . mysqli_real_escape_string($db, $recipe->TASTE_MOUTHFEEL);
1304 $psql .= "', taste_aftertaste='" . mysqli_real_escape_string($db, $recipe->TASTE_AFTERTASTE);
1305 }
1306
1307 ($recipe->INVENTORY_REDUCED == 'TRUE') ? $psql .= "', inventory_reduced='1" : $psql .= "', inventory_reduced='0";
1308 if (($recipe->LOCKED == 'TRUE') && ($stage == 'Ready')) {
1309 $psql .= "', locked='1";
1310 $rsql .= "', locked='1";
1311 $stage = "Closed";
1312 } else {
1313 $psql .= "', locked='0";
1314 $rsql .= "', locked='0";
1315 }
1316
1317 $psql .= "', stage='" . $stage;
1318 // echo ' '.$brewdate.' '.$recipe->NR_RECIPE.' '.$stage . PHP_EOL;
1319
1320 $rsql .= "';";
1321 if (! $rresult = mysqli_query($db, $rsql)) {
1322 printf("Error: %s\n", mysqli_error($db));
1323 }
1324
1221 $psql .= "';"; 1325 $psql .= "';";
1222 if (! $presult = mysqli_query($db, $psql)) { 1326 if (! $presult = mysqli_query($db, $psql)) {
1223 printf("Error: %s\n", mysqli_error($db)); 1327 printf("Error: %s\n", mysqli_error($db));
1224 } 1328 }
1225 } 1329 }

Mercurial Repository: bms

mercurial