www/import/from_brouwhulp.php

changeset 11
d341f0a91a91
child 13
b8c3ca152984
equal deleted inserted replaced
10:606b4af8f918 11:d341f0a91a91
1 <?php
2 /*
3 * Import inventory from brouwhulp
4 */
5
6 require("../config.php");
7 require("../version.php");
8 require("../includes/formulas.php");
9
10 echo "Start adding data from brouwhulp\n";
11
12 $db = mysqli_connect(DBASE_HOST, DBASE_USER, DBASE_PASS, DBASE_NAME);
13 if (! $db) {
14 die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
15 }
16
17 $brouwhulp = '/home/mbroek/Documents/brouwhulp';
18
19
20 function do_fermentables()
21 {
22 global $brouwhulp, $db;
23
24 echo " Start adding fermentables to the database\n";
25 $sql = "TRUNCATE TABLE inventory_fermentables;";
26 if (! $result = mysqli_query($db, $sql)) {
27 printf("Error: %s\n", mysqli_error($db));
28 }
29 $myfermentables = simplexml_load_file($brouwhulp . '/fermentables.xml');
30
31 foreach ($myfermentables->FERMENTABLE as $fermentable) {
32
33 $sql = "INSERT INTO inventory_fermentables SET name='" . mysqli_real_escape_string($db, $fermentable->NAME);
34 $sql .= "', type='" . $fermentable->TYPE;
35 if ($fermentable->YIELD)
36 $sql .= "', yield='" . $fermentable->YIELD;
37 if ($fermentable->COLOR) {
38 $srm = $fermentable->COLOR;
39 $ebc = srm_to_ebc($srm);
40 if ($ebc < 0)
41 $ebc = 0;
42 $nsrm = ebc_to_srm($ebc);
43 echo 'SRM '.$srm.' EBC '.$ebc.' back '.$nsrm.PHP_EOL;
44 $sql .= "', color='" . $ebc;
45 }
46 ($fermentable->ADD_AFTER_BOIL == "TRUE") ? $sql .= "', add_after_boil='1" : $sql .= "', add_after_boil='0";
47 $sql .= "', origin='" . mysqli_real_escape_string($db, $fermentable->ORIGIN);
48 $sql .= "', supplier='" . mysqli_real_escape_string($db, $fermentable->SUPPLIER);
49 $sql .= "', notes='" . mysqli_real_escape_string($db, $fermentable->NOTES);
50 if ($fermentable->COARSE_FINE_DIFF)
51 $sql .= "', coarse_fine_diff='" . $fermentable->COARSE_FINE_DIFF;
52 if ($fermentable->MOISTURE)
53 $sql .= "', moisture='" . $fermentable->MOISTURE;
54 if ($fermentable->DIASTATIC_POWER)
55 $sql .= "', diastatic_power='" . $fermentable->DIASTATIC_POWER;
56 if ($fermentable->PROTEIN)
57 $sql .= "', protein='" . $fermentable->PROTEIN;
58 if ($fermentable->MAX_IN_BATCH)
59 $sql .= "', max_in_batch='" . $fermentable->MAX_IN_BATCH;
60 ($fermentable->RECOMMEND_MASH == "TRUE") ? $sql .= "', recommend_mash='Y" : $sql .= "', recommend_mash='N";
61 if ($fermentable->IBU_GAL_PER_LB)
62 $sql .= "', ibu_gal_per_lb='" . $fermentable->IBU_GAL_PER_LB;
63 ($fermentable->ALWAYS_ON_STOCK == "TRUE") ? $sql .= "', always_on_stock='Y" : $sql .= "', always_on_stock='N";
64 if ($fermentable->INVENTORY)
65 $sql .= "', inventory='" . $fermentable->INVENTORY;
66 if ($fermentable->COST)
67 $sql .= "', cost='" . $fermentable->COST;
68 /*
69 * These are not beerxml standard:
70 */
71 if ($fermentable->DI_pH)
72 $sql .= "', di_ph='" . $fermentable->DI_pH;
73 if ($fermentable->{'ACID_TO_pH_5.7'})
74 $sql .= "', acid_to_ph_57='" . $fermentable->{'ACID_TO_pH_5.7'};
75 $sql .= "', graintype='" . $fermentable->GRAINTYPE;
76 $sql .= "';";
77 if (! $result = mysqli_query($db, $sql)) {
78 printf("Error: %s\n", mysqli_error($db));
79 }
80 }
81 }
82
83
84 function do_hops()
85 {
86 global $brouwhulp, $db;
87
88 echo " Start adding hops to the database\n";
89 $sql = "TRUNCATE TABLE inventory_hops;";
90 if (! $result = mysqli_query($db, $sql)) {
91 printf("Error: %s\n", mysqli_error($db));
92 }
93
94 $myhops = simplexml_load_file($brouwhulp . '/hops.xml');
95
96 foreach ($myhops->HOP as $hop) {
97
98 $sql = "INSERT INTO inventory_hops SET name='" . mysqli_real_escape_string($db, $hop->NAME);
99 if ($hop->ALPHA)
100 $sql .= "', alpha='" . $hop->ALPHA;
101 if ($hop->BETA)
102 $sql .= "', beta='" . $hop->BETA;
103 if ($hop->HUMULENE)
104 $sql .= "', humulene='" . $hop->HUMULENE;
105 if ($hop->CARYOPHYLLENE)
106 $sql .= "', caryophyllene='" . $hop->CARYOPHYLLENE;
107 if ($hop->COHUMULONE)
108 $sql .= "', cohumulone='" . $hop->COHUMULONE;
109 if ($hop->MYCRENE)
110 $sql .= "', myrcene='" . $hop->MYCRENE;
111 if ($hop->HSI)
112 $sql .= "', hsi='" . $hop->HSI;
113 $sql .= "', useat='" . $hop->USE;
114 $sql .= "', type='" . $hop->TYPE;
115 $sql .= "', form='" . $hop->FORM;
116 $sql .= "', notes='" . mysqli_real_escape_string($db, $hop->NOTES);
117 $sql .= "', origin='" . mysqli_real_escape_string($db, $hop->ORIGIN);
118 $sql .= "', substitutes='" . mysqli_real_escape_string($db, $hop->SUBSTITUTES);
119 ($hop->ALWAYS_ON_STOCK == 'TRUE') ? $sql .= "', always_on_stock='Y" : $sql .= "', always_on_stock='N";
120 if ($hop->INVENTORY)
121 $sql .= "', inventory='" . $hop->INVENTORY;
122 if ($hop->COST)
123 $sql .= "', cost='" . $hop->COST;
124 $sql .= "';";
125 if (! $result = mysqli_query($db, $sql)) {
126 printf("Error: %s\n", mysqli_error($db));
127 }
128 }
129
130 }
131
132
133
134 function do_yeasts()
135 {
136 global $brouwhulp, $db;
137
138 echo " Start adding yeasts to the database\n";
139 $sql = "TRUNCATE TABLE inventory_yeasts;";
140 if (! $result = mysqli_query($db, $sql)) {
141 printf("Error: %s\n", mysqli_error($db));
142 }
143
144 $yeasts = simplexml_load_file($brouwhulp . '/yeasts.xml');
145
146 foreach ($yeasts->YEAST as $yeast) {
147
148 $sql = "INSERT INTO inventory_yeasts SET name='" . mysqli_real_escape_string($db, $yeast->NAME);
149 $sql .= "', type='" . $yeast->TYPE;
150 $sql .= "', form='" . $yeast->FORM;
151 if ($yeast->AMOUNT)
152 $sql .= "', amount='" . $yeast->AMOUNT;
153 ($yeast->AMOUNT_IS_WEIGHT == 'TRUE') ? $sql .= "', amount_is_weight='Y" : $sql .= "', amount_is_weight='N";
154 $sql .= "', laboratory='" . mysqli_real_escape_string($db, $yeast->LABORATORY);
155 $sql .= "', product_id='" . mysqli_real_escape_string($db, $yeast->PRODUCT_ID);
156 if ($yeast->MIN_TEMPERATURE)
157 $sql .= "', min_temperature='" . $yeast->MIN_TEMPERATURE;
158 if ($yeast->MAX_TEMPERATURE)
159 $sql .= "', max_temperature='" . $yeast->MAX_TEMPERATURE;
160 $sql .= "', flocculation='" . $yeast->FLOCCULATION;
161 if ($yeast->ATTENUATION)
162 $sql .= "', attenuation='" . $yeast->ATTENUATION;
163 $sql .= "', notes='" . mysqli_real_escape_string($db, $yeast->NOTES);
164 $sql .= "', best_for='" . mysqli_real_escape_string($db, $yeast->BEST_FOR);
165 if ($yeast->TIMES_CULTURED)
166 $sql .= "', times_cultured='" . $yeast->TIMES_CULTURED;
167 if ($yeast->MAX_REUSE)
168 $sql .= "', max_reuse='" . $yeast->MAX_REUSE;
169 if ($yeast->INVENTORY)
170 $sql .= "', inventory='" . $yeast->INVENTORY;
171 if ($yeast->COST)
172 $sql .= "', cost='" . $yeast->COST;
173 if ($yeast->CULTURE_DATE) {
174 $date = substr($yeast->CULTURE_DATE, 6, 4) . '-' . substr($yeast->CULTURE_DATE, 3, 2) . '-' . substr($yeast->CULTURE_DATE, 0, 2);
175 $sql .= "', production_date='" . $date;
176 }
177 $sql .= "';";
178 if (! $result = mysqli_query($db, $sql)) {
179 printf("Error: %s\n", mysqli_error($db));
180 }
181 }
182 }
183
184
185
186 function do_waters()
187 {
188 global $brouwhulp, $db;
189
190 echo " Start adding waters to the database\n";
191 $sql = "TRUNCATE TABLE inventory_water_profiles;";
192 if (! $result = mysqli_query($db, $sql)) {
193 printf("Error: %s\n", mysqli_error($db));
194 }
195
196 $waters = simplexml_load_file($brouwhulp . '/waters.xml');
197
198 foreach ($waters->WATER as $water) {
199
200 $sql = "INSERT INTO inventory_water_profiles SET name='" . mysqli_real_escape_string($db, $water->NAME);
201 ($water->ALWAYS_ON_STOCK == 'TRUE') ? $sql .= "', always_on_stock='Y" : $sql .= "', always_on_stock='N";
202 if ($water->NOTES)
203 $sql .= "', notes='" . mysqli_real_escape_string($db, $water->NOTES);
204 $sql .= "', calcium='" . $water->CALCIUM;
205 $sql .= "', bicarbonate='" . $water->BICARBONATE;
206 $sql .= "', sulfate='" . $water->SULFATE;
207 $sql .= "', chloride='" . $water->CHLORIDE;
208 $sql .= "', sodium='" . $water->SODIUM;
209 $sql .= "', magnesium='" . $water->MAGNESIUM;
210 $sql .= "', ph='" . $water->PH;
211 if ($water->TOTAL_ALKALINITY)
212 $sql .= "', total_alkalinity='" . $water->TOTAL_ALKALINITY;
213 ($water->DEFAULT_WATER == 'TRUE') ? $sql .= "', default_water='Y" : $sql .= "', default_water='N";
214 $sql .= "';";
215 if (! $result = mysqli_query($db, $sql)) {
216 printf("Error: %s\n", mysqli_error($db));
217 }
218 }
219 }
220
221
222
223 function do_miscs()
224 {
225 global $brouwhulp, $db;
226
227 echo " Start adding miscs to the database\n";
228 $sql = "TRUNCATE TABLE inventory_miscs;";
229 if (! $result = mysqli_query($db, $sql)) {
230 printf("Error: %s\n", mysqli_error($db));
231 }
232
233 $miscs = simplexml_load_file($brouwhulp . '/miscs.xml');
234
235 foreach ($miscs->MISC as $misc) {
236
237 $sql = "INSERT INTO inventory_miscs SET name='" . mysqli_real_escape_string($db, $misc->NAME);
238 if ($misc->NOTES)
239 $sql .= "', notes='" . mysqli_real_escape_string($db, $misc->NOTES);
240 $sql .= "', type='" . $misc->TYPE;
241 $sql .= "', use_use='" . $misc->USE;
242 $sql .= "', time='" . $misc->TIME;
243 $sql .= "', amount='" . $misc->AMOUNT;
244 ($misc->AMOUNT_IS_WEIGHT == 'TRUE') ? $sql .= "', amount_is_weight='Y" : $sql .= "', amount_is_weight='N";
245 if ($misc->USE_FOR)
246 $sql .= "', use_for='" . mysqli_real_escape_string($db, $misc->USE_FOR);
247 if ($misc->ALWAYS_ON_STOCK)
248 ($misc->ALWAYS_ON_STOCK == 'TRUE') ? $sql .= "', always_on_stock='Y" : $sql .= "', always_on_stock='N";
249 if ($misc->INVENTORY)
250 $sql .= "', inventory='" . $misc->INVENTORY;
251 if ($misc->COST)
252 $sql .= "', cost='" . $misc->COST;
253 $sql .= "';";
254 if (! $result = mysqli_query($db, $sql)) {
255 printf("Error: %s\n", mysqli_error($db));
256 }
257 }
258 }
259
260
261
262 function do_equipments()
263 {
264 global $brouwhulp, $db;
265
266 echo " Start adding equipments to the database\n";
267 $sql = "TRUNCATE TABLE inventory_equipments;";
268 if (! $result = mysqli_query($db, $sql)) {
269 printf("Error: %s\n", mysqli_error($db));
270 }
271
272 $equipments = simplexml_load_file($brouwhulp . '/equipments.xml');
273
274 foreach ($equipments->EQUIPMENT as $equipment) {
275
276 $sql = "INSERT INTO inventory_equipments SET name='" . mysqli_real_escape_string($db, $equipment->NAME);
277 $sql .= "', boil_size='" . $equipment->BOIL_SIZE;
278 $sql .= "', batch_size='" . $equipment->BATCH_SIZE;
279 $sql .= "', tun_volume='" . $equipment->TUN_VOLUME;
280 $sql .= "', tun_weight='" . $equipment->TUN_WEIGHT;
281 $sql .= "', tun_specific_heat='" . $equipment->TUN_SPECIFIC_HEAT;
282 $sql .= "', top_up_water='" . $equipment->TOP_UP_WATER;
283 $sql .= "', trub_chiller_loss='" . $equipment->TRUB_CHILLER_LOSS;
284 /*
285 * Brouwhulp uses a percentage for the evaporation rate. This is wrong
286 * but was made so because the beerxml standard requires this. What we
287 * do is calculate the actual evaporation and store that.
288 * This is what we use. Brouwhulp calculates this on the fly.
289 */
290 $sql .= "', evap_rate='" . ($equipment->EVAP_RATE * $equipment->BOIL_SIZE) / 100.0;
291 $sql .= "', boil_time='" . $equipment->BOIL_TIME;
292 ($equipment->CALC_BOIL_VOLUME == 'TRUE') ? $sql .= "', calc_boil_volume='Y" : $sql .= "', calc_boil_volume='N";
293 $sql .= "', lauter_deadspace='" . $equipment->LAUTER_DEADSPACE;
294 $sql .= "', top_up_kettle='" . $equipment->TOP_UP_KETTLE;
295 $sql .= "', hop_utilization='" . $equipment->HOP_UTILIZATION;
296 if ($equipment->NOTES)
297 $sql .= "', notes='" . mysqli_real_escape_string($db, $equipment->NOTES);
298 $sql .= "', lauter_volume='" . $equipment->LAUTER_VOLUME;
299 $sql .= "', kettle_volume='" . $equipment->KETTLE_VOLUME;
300 if ($equipment->TUN_MATERIAL)
301 $sql .= "', tun_material='" . mysqli_real_escape_string($db, $equipment->TUN_MATERIAL);
302 $sql .= "', tun_height='" . $equipment->TUN_HEIGHT;
303 $sql .= "', kettle_height='" . $equipment->KETTLE_HEIGHT;
304 $sql .= "', lauter_height='" . $equipment->LAUTER_HEIGHT;
305 $sql .= "', mash_volume='" . $equipment->MASH_VOLUME;
306 $sql .= "', efficiency='" . $equipment->EFFICIENCY;
307
308 $sql .= "';";
309 if (! $result = mysqli_query($db, $sql)) {
310 printf("Error: %s\n", mysqli_error($db));
311 }
312 }
313 }
314
315
316
317 function do_styles()
318 {
319 global $brouwhulp, $db;
320
321 echo " Start adding styles to the database\n";
322 $sql = "TRUNCATE TABLE inventory_styles;";
323 if (! $result = mysqli_query($db, $sql)) {
324 printf("Error: %s\n", mysqli_error($db));
325 }
326
327 $styles = simplexml_load_file($brouwhulp . '/styles.xml');
328
329 foreach ($styles->STYLE as $style) {
330
331 $sql = "INSERT INTO inventory_styles SET name='" . mysqli_real_escape_string($db, $style->NAME);
332 if ($style->NOTES)
333 $sql .= "', notes='" . mysqli_real_escape_string($db, $style->NOTES);
334 if ($style->CATEGORY)
335 $sql .= "', category='" . mysqli_real_escape_string($db, $style->CATEGORY);
336 if ($style->CATEGORY_NUMBER)
337 $sql .= "', category_number='" . $style->CATEGORY_NUMBER;
338 if ($style->STYLE_LETTER)
339 $sql .= "', style_letter='" . mysqli_real_escape_string($db, $style->STYLE_LETTER);
340 if ($style->STYLE_GUIDE)
341 $sql .= "', style_guide='" . mysqli_real_escape_string($db, $style->STYLE_GUIDE);
342 $sql .= "', type='" . $style->TYPE;
343 $sql .= "', og_min='" . $style->OG_MIN;
344 $sql .= "', og_max='" . $style->OG_MAX;
345 $sql .= "', fg_min='" . $style->FG_MIN;
346 $sql .= "', fg_max='" . $style->FG_MAX;
347 $sql .= "', ibu_min='" . $style->IBU_MIN;
348 $sql .= "', ibu_max='" . $style->IBU_MAX;
349 $sql .= "', color_min='" . $style->COLOR_MIN;
350 $sql .= "', color_max='" . $style->COLOR_MAX;
351 $sql .= "', carb_min='" . $style->CARB_MIN;
352 $sql .= "', carb_max='" . $style->CARB_MAX;
353 $sql .= "', abv_min='" . $style->ABV_MIN;
354 $sql .= "', abv_max='" . $style->ABV_MAX;
355 if ($style->PROFILE)
356 $sql .= "', profile='" . mysqli_real_escape_string($db, $style->PROFILE);
357 if ($style->INGREDIENTS)
358 $sql .= "', ingredients='" . mysqli_real_escape_string($db, $style->INGREDIENTS);
359 if ($style->EXAMPLES)
360 $sql .= "', examples='" . mysqli_real_escape_string($db, $style->EXAMPLES);
361
362 $sql .= "';";
363 if (! $result = mysqli_query($db, $sql)) {
364 printf("Error: %s\n", mysqli_error($db));
365 }
366 }
367 }
368
369
370
371 function do_mash()
372 {
373 global $brouwhulp, $db;
374
375 echo " Start adding mash profiles to the database\n";
376 $sql = "TRUNCATE TABLE inventory_mash_steps;";
377 if (! $result = mysqli_query($db, $sql)) {
378 printf("Error: %s\n", mysqli_error($db));
379 }
380 $sql = "TRUNCATE TABLE inventory_mash_profiles;";
381 if (! $result = mysqli_query($db, $sql)) {
382 printf("Error: %s\n", mysqli_error($db));
383 }
384
385 $mashes = simplexml_load_file($brouwhulp . '/mashs.xml');
386
387 foreach ($mashes->MASH as $mash) {
388 $sql = "INSERT INTO inventory_mash_profiles SET name='" . mysqli_real_escape_string($db, $mash->NAME);
389 if ($mash->NOTES)
390 $sql .= "', notes='" . mysqli_real_escape_string($db, $mash->NOTES);
391 if ($mash->GRAIN_TEMP)
392 $sql .= "', grain_temp='" . $mash->GRAIN_TEMP;
393 if ($mash->TUN_TEMP)
394 $sql .= "', tun_temp='" . $mash->TUN_TEMP;
395 if ($mash->SPARGE_TEMP)
396 $sql .= "', sparge_temp='" . $mash->SPARGE_TEMP;
397 if ($mash->PH)
398 $sql .= "', ph='" . $mash->PH;
399 if ($mash->TUN_WEIGHT)
400 $sql .= "', tun_weight='" . $mash->TUN_WEIGHT;
401 if ($mash->TUN_SPECIFIX_HEAT)
402 $sql .= "', tun_specific_heat='" . $mash->TUN_SPECIFIC_HEAT;
403 ($mash->EQUIP_ADJUST == 'TRUE') ? $sql .= "', equip_adjust='Y" : $sql .= "', equip_adjust='N";
404 $sql .= "';";
405 /*
406 * Store the record and get the record number that is created.
407 * Store that as a reference in the steps.
408 */
409 if (! $result = mysqli_query($db, $sql)) {
410 printf("Error: %s\n", mysqli_error($db));
411 }
412 $record = mysqli_insert_id($db);
413
414 /*
415 * Put the steps in a different table with a reference to the profile.
416 */
417 if ($mash->MASH_STEPS) {
418 foreach ($mash->MASH_STEPS->MASH_STEP as $step) {
419
420 $sql = "INSERT INTO inventory_mash_steps SET name='" . mysqli_real_escape_string($db, $step->NAME);
421 $sql .= "', rec_mash_profile='" . $record;
422 if ($step->TYPE)
423 $sql .= "', type='" . $step->TYPE;
424 if ($step->INFUSE_AMOUNT)
425 $sql .= "', infuse_amount='" . $step->INFUSE_AMOUNT;
426 if ($step->STEP_TEMP)
427 $sql .= "', step_temp='" . $step->STEP_TEMP;
428 if ($step->STEP_TIME)
429 $sql .= "', step_time='" . $step->STEP_TIME;
430 if ($step->RAMP_TIME)
431 $sql .= "', ramp_time='" . $step->RAMP_TIME;
432 if ($step->END_TEMP)
433 $sql .= "', end_temp='" . $step->END_TEMP;
434 if ($step->DESCRIPTION)
435 $sql .= "', description='" . mysqli_real_escape_string($db, $step->DESCRIPTION);
436 $sql .= "';";
437 if (! $result = mysqli_query($db, $sql)) {
438 printf("Error: %s\n", mysqli_error($db));
439 }
440 }
441 }
442 }
443 }
444
445
446
447 do_fermentables();
448 //do_hops();
449 //do_yeasts();
450 //do_waters();
451 //do_miscs();
452 //do_equipments();
453 //do_styles();
454 //do_mash();
455
456 mysqli_close($db);
457
458 echo "Finished adding data\n";
459
460
461 ?>

mercurial