1: <?php
  2: 
  3: /**
  4:  * openhomeo_db.php
  5:  *
  6:  * PHP version 8
  7:  *
  8:  * LICENSE: This program is free software: you can redistribute it and/or modify
  9:  * it under the terms of the GNU Affero General Public License as
 10:  * published by the Free Software Foundation, either version 3 of the
 11:  * License, or (at your option) any later version.
 12:  * This program is distributed in the hope that it will be useful,
 13:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 14:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 15:  * GNU Affero General Public License for more details.
 16:  * You should have received a copy of the GNU Affero General Public License
 17:  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 18:  *
 19:  * @category  Database
 20:  * @package   OpenHomeoDB
 21:  * @author    Henri Schumacher <henri.hulski@gazeta.pl>
 22:  * @copyright 2007-2014 Henri Schumacher
 23:  * @license   http://www.gnu.org/licenses/agpl.html GNU Affero General Public License v3
 24:  * @version   1.0
 25:  * @link      https://research.openhomeo.info/download/OpenHomeopath_1.0.2.tar.gz
 26:  */
 27: 
 28: require_once ("include/classes/db/config_openhomeopath.php");
 29: require_once("include/classes/db/db.php");
 30: 
 31: /**
 32:  * OpenHomeoDB provides the database public functionality for OpenHomeopath
 33:  *
 34:  * @category  Database
 35:  * @package   OpenHomeoDB
 36:  * @author    Henri Schumacher <henri.hulski@gazeta.pl>
 37:  * @copyright 2007-2014 Henri Schumacher
 38:  * @license   http://www.gnu.org/licenses/agpl.html GNU Affero General Public License v3
 39:  */
 40: class OpenHomeoDB extends DB {
 41: 
 42:     /**
 43:      * archive_table_row archives edited or deleted table rows.
 44:      *
 45:      * @param string $table        the table to be archived
 46:      * @param string $where        WHERE declaration for the rows to be archived
 47:      * @param string $archive_type reason, why archiving
 48:      * @return void
 49:      * @access public
 50:      */
 51:     function archive_table_row($table, $where, $archive_type) {
 52:         $query = "INSERT INTO archive__$table SELECT *, '$archive_type' FROM $table WHERE $where";
 53:         $this->send_query($query);
 54: }
 55: 
 56:     /* restoring of archived table rows */
 57: 
 58:     /**
 59:      * restore_table_row restores archived table rows.
 60:      *
 61:      * @param string $table     the table to restore
 62:      * @param string $where     WHERE declaration for the rows to be restored
 63:      * @param string $timestamp timestamp given in the table row
 64:      * @return void
 65:      * @access public
 66:      */
 67:     function restore_table_row($table, $where, $timestamp) {
 68:         global $session;
 69:         $archive_type = "restore_$timestamp";
 70:         $this->archive_table_row($table, $where, $archive_type);
 71:         $query = "DESCRIBE $table";
 72:         $this->send_query($query);
 73:         while ($row = $this->db_fetch_assoc()) {
 74:             $fields_ar[] = $row["Field"];
 75:         }
 76:         $this->free_result();
 77:         $select = "";
 78:         foreach ($fields_ar as $field) {
 79:             $select .= "`$field`, ";
 80:         }
 81:         $select = substr($select, 0, -2); // delete the last ', '
 82:         $query = "REPLACE INTO `$table` SELECT $select FROM `archive__$table` WHERE $where AND `timestamp`='$timestamp'";
 83:         $this->send_query($query);
 84:         $username = $session->username;
 85:         $query = "UPDATE `$table` SET `timestamp`= NOW(), `username`= '$username' WHERE $where";
 86:         $this->send_query($query);
 87:     }
 88: 
 89:     /**
 90:      * get_custom_table determines which custom table for a given table will be used.
 91:      *
 92:      * @param string $table given table (symptoms | sym_rem | materia)
 93:      * @return string custom table
 94:      * @access public
 95:      */
 96:     function get_custom_table($table) {
 97:         global $session;
 98:         if ($session->logged_in) {  // user logged in
 99:             if ($table === "materia") {
100:                 $src = "src_materia";
101:             } else {
102:                 $src = "src_rep";
103:             }
104:             $username = $session->username;
105:             $query = "SELECT $src FROM users WHERE username='$username'";
106:             $this->send_query($query);
107:             list($src_row) = $this->db_fetch_row();
108:             $this->free_result();
109:             if ($src_row !== 'all') {
110:                 if ($src === "src_rep" && $sym_lang = $this->get_lang_only_symptom_table()) {
111:                     if ($table === "symptoms") {
112:                         $custom_table = $sym_lang['table'];
113:                     } else {
114:                         $custom_table = $table;
115:                     }
116:                 } elseif ($src_row === 'custom') {
117:                     if ($table === "materia") {
118:                         $src_table = "custom_materia";
119:                     } else {
120:                         $src_table = "custom_rep";
121:                     }
122:                     $custom_src_ar = $this->get_custom_src($username, $src_table);
123:                     $src_nr_ar = $this->get_source_nr($custom_src_ar);
124:                     $custom_src = implode("_", $src_nr_ar);
125:                     $custom_table = $table . "__" . $custom_src;
126:                     if ($table === "symptoms") {
127:                         $custom_table = "sym__" . $custom_src;
128:                         $symptom_lang = $this->get_custom_symptom_lang($custom_src_ar);
129:                         if ($symptom_lang !== false) {
130:                             $custom_table .= "_$symptom_lang";
131:                         }
132:                     }
133:                     if ($this->table_exists($custom_table) === false) {
134:                         if ($table === "symptoms") {
135:                             $this->create_custom_symptom_table();
136:                         } else {
137:                             $this->create_custom_table($table);
138:                         }
139:                     }
140:                 }
141:             } elseif ($table === "symptoms") {
142:                 $custom_table = $this->get_sym_base_table();
143:             } else {
144:                 $custom_table = $table;
145:             }
146:         } elseif ($table === "symptoms" || $table === "sym_rem") {
147:             $lang = $session->lang;
148:             if ($this->is_sym_lang($lang) === false) {
149:                 $lang = "en";
150:             }
151:             if ($table === "symptoms") {
152:                 if ($lang == 'en') {
153:                     $custom_table = DEFAULT_SYMPTOM_TABLE_EN;
154:                 } elseif ($lang == 'de') {
155:                     $custom_table = DEFAULT_SYMPTOM_TABLE_DE;
156:                 }
157:             } elseif ($table === "sym_rem") {
158:                 if ($lang === 'en') {
159:                     $custom_table = 'sym_rem' . substr(DEFAULT_SYMPTOM_TABLE_EN, 3);
160:                     // delete "_en" at the end if any
161:                     if (substr($custom_table, -3) === '_en') {
162:                         $custom_table = substr($custom_table, 0, -3);
163:                     }
164:                 } elseif ($lang === 'de') {
165:                     $custom_table = 'sym_rem' . substr(DEFAULT_SYMPTOM_TABLE_DE, 3);
166:                     // delete "_de" at the end if any
167:                     if (substr($custom_table, -3) === '_de') {
168:                         $custom_table = substr($custom_table, 0, -3);
169:                     }
170:                 }
171:             }
172:             if ($this->table_exists($custom_table) === false) {
173:                 if ($table === "symptoms") {
174:                     $this->create_custom_symptom_table($custom_table);
175:                 } else {
176:                     $this->create_custom_table($table, $custom_table);
177:                 }
178:             }
179:         } else {
180:             $custom_table = $table;
181:         }
182:         return $custom_table;
183:     }
184: 
185:     /**
186:      * get_custom_src returns an array with the sources the user has set for his personal either repertory profile or materia medica.
187:      *
188:      * @param string $username  the user
189:      * @param string $src_table which custom sources table to use (custom_rep | custom_materia)
190:      * @return array
191:      * @access public
192:      */
193:     function get_custom_src($username, $src_table) {
194:         $custom_src_ar = array();
195:         $query = "SELECT src_id FROM $src_table WHERE username='$username'";
196:         $this->send_query($query);
197:         while (list($src_id) = $this->db_fetch_row()) {
198:             $custom_src_ar[] = $src_id;
199:         }
200:         $this->free_result();
201:         return $custom_src_ar;
202:     }
203: 
204:     /**
205:      * get_sym_base_table returns the sym-basetable to be used for symptoms.
206:      *
207:      * If there exists no symptom-translations the whole sym-table will be used.
208:      * If there are translations the language-based sym-table according to the usersettings will be used.
209:      * If $custom_symptom_lang is set, the given language will be used.
210:      *
211:      * @param false|string $custom_symptom_lang optional the symptom language to be used as language code
212:      * @return string
213:      * @access public
214:      */
215:     function get_sym_base_table($custom_symptom_lang = false) {
216:         global $session;
217:         $sym_base_table = "symptoms";
218:         if ($custom_symptom_lang === false) {
219:             $custom_symptom_lang = $this->get_custom_symptom_lang();
220:         }
221:         if ($custom_symptom_lang !== false) {
222:             $sym_base_table = "sym__$custom_symptom_lang";
223:             if ($this->table_exists($sym_base_table) === false) {
224:                 $create_tables = true;
225:                 $this->update_lang_symptom_tables($create_tables);
226:             }
227:         }
228:         return $sym_base_table;
229:     }
230: 
231:     /**
232:      * get_custom_symptom_lang returns the language to be used for symptoms
233:      *
234:      * If there exists no symptom-translations the public function returns false.
235:      * If there are translations the language according to the usersettings will be used.
236:      * If given an array of src_id's as $sources the sym-language will only be returned,
237:      * when for one of the given sources exists an translation.
238:      * Else the public function returns false.
239:      *
240:      * @param array|string $sources optional an array of src_id's to check
241:      * @return string|false
242:      * @access public
243:      */
244:     function get_custom_symptom_lang($sources = 'all') {
245:         global $session;
246:         $custom_symptom_lang = false;
247:         if ($this->exist_symptom_translation($sources) === true) {
248:             $custom_symptom_lang = $session->lang;
249:             // user logged in or saved repertorization is requested
250:             if ($session->logged_in || isset($_REQUEST['patient'])) {
251:                 $username = $session->username;
252:                 list($user_sym_lang) = $this->getUserInfo($username, 'sym_lang_id');
253:                 if (!empty($user_sym_lang) && $this->is_sym_lang($user_sym_lang)) {
254:                     $custom_symptom_lang = $user_sym_lang;
255:                 }
256:             }
257:             if ($this->is_sym_lang($custom_symptom_lang) === false) {
258:                 $custom_symptom_lang = "en";
259:             }
260:         }
261:         return $custom_symptom_lang;
262:     }
263: 
264: 
265:     /**
266:      * get_lang_only_symptom_table returns an array with information about the language symptom table to be used if any, else false.
267:      *
268:      * If the user wants to use all symptoms from one language the public function returns
269:      * an array with the language code, the language name and the symptom table.
270:      * Else the public function returns false.
271:      *
272:      * @return array|false  ['id']: language code, ['name']: language name in current language, ['table']: symptom table to use
273:      * @access public
274:      */
275:     function get_lang_only_symptom_table() {
276:         global $session;
277:         $sym_lang = false;
278:         if ($this->exist_symptom_translation() === true) {
279:             $username = $session->username;
280:             $query = "SELECT src_rep FROM users WHERE username='$username'";
281:             $this->send_query($query);
282:             list($src_rep) = $this->db_fetch_row();
283:             $this->free_result();
284:             if (strpos($src_rep, "lang_") === 0) {  // starts with "lang_" -> Position 0
285:                 $sym_lang['id'] = substr($src_rep, 5);
286:                 $sym_lang['table'] = "sym__" . $sym_lang['id'] . "_only";
287:                 $lang = $session->lang;
288:                 $query = "SELECT lang_$lang FROM languages WHERE lang_id = '" . $sym_lang['id'] . "'";
289:                 $this->send_query($query);
290:                 list($sym_lang['name']) = $this->db_fetch_row();
291:                 $this->free_result();
292:             }
293:         }
294:         return $sym_lang;
295:     }
296: 
297:     /**
298:      * is_sym_lang returns true if the given language is used as a symptom language, else false.
299:      *
300:      * @param string $lang_id language code
301:      * @return boolean
302:      * @access public
303:      */
304:     function is_sym_lang($lang_id) {
305:         $query = "SELECT sym_lang FROM languages WHERE lang_id = '$lang_id'";
306:         $this->send_query($query);
307:         list ($is_sym_lang) = $this->db_fetch_row();
308:         $this->free_result();
309:         if ($is_sym_lang === 0) {
310:             $is_sym_lang = false;
311:         } else {
312:             $is_sym_lang = true;
313:         }
314:         return $is_sym_lang;
315:     }
316: 
317: 
318:     /**
319:      * get_sym_langs returns an array with used symptom languages in the database
320:      *
321:      * @return array
322:      * @access public
323:      */
324:     function get_sym_langs() {
325:         $query = "SELECT lang_id FROM languages WHERE sym_lang = 1";
326:         $this->send_query($query);
327:         while (list ($lang_id) = $this->db_fetch_row()) {
328:             $sym_lang_ar[] = $lang_id;
329:         }
330:         $this->free_result();
331:         return $sym_lang_ar;
332:     }
333: 
334:     /**
335:      * is_custom_table determines which kind of table for the given table will be used.
336:      *
337:      * If the user uses a custom table the public function returns true.
338:      * If the given table is symptoms and the user uses all symptoms from one language
339:      * the public function returns this language name in the current language.
340:      * Else or if no user is logged in the public function returns false.
341:      *
342:      * @param string  $table  given table (symptoms | sym_rem | materia)
343:      * @return boolean|string
344:      * @access public
345:      */
346:     function is_custom_table($table) {
347:         global $session;
348:         $is_custom_table = false;
349:         if ($session->logged_in) {  // user logged in
350:             if ($table == "materia") {
351:                 $src = "src_materia";
352:             } else {
353:                 $src = "src_rep";
354:             }
355:             $username = $session->username;
356:             $query = "SELECT $src FROM users WHERE username='$username'";
357:             $this->send_query($query);
358:             list($src_row) = $this->db_fetch_row();
359:             $this->free_result();
360:             if ($table === "symptoms" && $sym_lang = $this->get_lang_only_symptom_table()) {
361:                 $is_custom_table = $sym_lang['name'];
362:             } elseif ($src_row == 'custom') {
363:                 $is_custom_table = true;
364:             }
365:         }
366:         return $is_custom_table;
367:     }
368: 
369:     /**
370:      * exist_symptom_translation returns true if exists any translations for symptoms in the database, else false.
371:      *
372:      * If src_id's-array given as $sources, the public function checks only for symptoms related to the given sources.
373:      *
374:      * @param array|string  $sources optional an array of src_id's to check
375:      * @return boolean
376:      * @access public
377:      */
378:     function exist_symptom_translation($sources = 'all') {
379:         $exist_symptom_translation = false;
380:         if ($sources == 'all') {
381:             $query = "SELECT sym_id FROM symptoms s WHERE EXISTS (SELECT 1 FROM sym_translations st WHERE st.sym_id = s.sym_id) LIMIT 1";
382:         } else {
383:             $source_query = implode("' || sym_rem.src_id = '", $sources);
384:             $where = "(sym_rem.src_id = '" . $source_query . "')";
385:             $query = "SELECT s.sym_id FROM symptoms s, sym_rem WHERE s.sym_id = sym_rem.sym_id AND $where AND EXISTS (SELECT 1 FROM sym_translations st WHERE st.sym_id = s.sym_id) LIMIT 1";
386:         }
387:         $this->send_query($query);
388:         $num = $this->db_num_rows();
389:         $this->free_result();
390:         if ($num > 0) {
391:             $exist_symptom_translation = true;
392:         }
393:         return $exist_symptom_translation;
394:     }
395: 
396:     /**
397:      * is_translated checks if the given symptom has a translation.
398:      *
399:      * @param integer $sym_id sym_id from the symptom to check
400:      * @return boolean
401:      * @access public
402:      */
403:     function is_translated($sym_id) {
404:         $is_translated = false;
405:         $query = "SELECT sym_id FROM sym_translations WHERE sym_id = $sym_id LIMIT 1";
406:         $this->send_query($query);
407:         $num = $this->db_num_rows();
408:         $this->free_result();
409:         if ($num > 0) {
410:             $is_translated = true;
411:         }
412:         return $is_translated;
413:     }
414: 
415:     /**
416:      * create_custom_table creates the custom table for sym_rem or materia based on the user preferences.
417:      *
418:      * @param string $table        base table: sym_rem | materia
419:      * @param string $custom_table optional custom_table to create
420:      * @return void
421:      * @access public
422:      */
423:     function create_custom_table($table, $custom_table = "") {
424:         if (empty($custom_table)) {
425:             global $session;
426:             if ($table == "materia") {
427:                 $src = "src_materia";
428:             } else {
429:                 $src = "src_rep";
430:             }
431:             $username = $session->username;
432:             $query = "SELECT $src FROM users WHERE username='$username'";
433:             $this->send_query($query);
434:             list($src_row) = $this->db_fetch_row();
435:             $this->free_result();
436:             if ($src_row == 'custom') {
437:                 if ($table == "materia") {
438:                     $src_table = "custom_materia";
439:                 } else {
440:                     $src_table = "custom_rep";
441:                 }
442:                 $src_id_ar = $this->get_custom_src($username, $src_table);
443:                 $src_no_ar = $this->get_source_nr($src_id_ar);
444:                 $custom_src = implode("_", $src_no_ar);
445:                 $source_query = implode("' || src_id = '", $src_id_ar);
446:                 $where = "(src_id = '" . $source_query . "')";
447:                 $custom_table = $table . "__" . $custom_src;
448:             }
449:         } else {
450:             $src_no_ar = $this->extract_src_no($custom_table);
451:             $src_id_ar = $this->get_source_id($src_no_ar);
452:             $source_query = implode("' || src_id = '", $src_id_ar);
453:             $where = "(src_id = '" . $source_query . "')";
454:         }
455:         if (!empty($where)) {
456:             $query = "CREATE OR REPLACE VIEW $custom_table AS SELECT * FROM $table WHERE $where";
457:             $this->send_query($query);
458:         }
459:     }
460: 
461:     /**
462:      * create_custom_table creates the custom table for symptoms based on the user preferences.
463:      *
464:      * It also loggs the current number of symptoms of the base table and the name of the base table into the sym_stats table.
465:      * So it's possible to check if symptoms will be added to the base table and update the custom table
466:      *
467:      * @param string  $custom_table optional custom_table to create
468:      * @param boolean $update       if true updates custom symptom table when exists, if false leaves it unchanged
469:      * @return void
470:      * @access public
471:      */
472:     function create_custom_symptom_table($custom_table = "", $update = false) {
473:         global $session;
474:         $username = $session->username;
475:         if (empty($custom_table)) {
476:             $query = "SELECT src_rep FROM users WHERE username='$username'";
477:             $this->send_query($query);
478:             list($src_rep) = $this->db_fetch_row();
479:             $this->free_result();
480:             if ($src_rep == 'custom') {
481:                 $src_id_ar = $this->get_custom_src($username, 'custom_rep');
482:                 $src_no_ar = $this->get_source_nr($src_id_ar);
483:                 $custom_src = implode("_", $src_no_ar);
484:                 $symptom_lang = $this->get_custom_symptom_lang($src_id_ar);
485:                 if ($symptom_lang !== false) {
486:                     $custom_src .= "_$symptom_lang";    // for sources with different translations adds the language at the end of the symptoms tablename (e.g. _en)
487:                 }
488:                 $source_query = implode("' || sym_rem.src_id = '", $src_id_ar);
489:                 $where = "(sym_rem.src_id = '" . $source_query . "')";
490:                 $custom_table = "sym__$custom_src";
491:             }
492:         } else {
493:             $src_no_ar = $this->extract_src_no($custom_table);
494:             $src_id_ar = $this->get_source_id($src_no_ar);
495:             $source_query = implode("' || sym_rem.src_id = '", $src_id_ar);
496:             $where = "(sym_rem.src_id = '" . $source_query . "')";
497:             $symptom_lang = $session->lang;
498:         }
499:         if (!empty($custom_table) && ($this->table_exists($custom_table) === false || $update)) {
500:             set_time_limit(0);
501:             ignore_user_abort(true);
502:             if ($this->is_sym_lang($symptom_lang) === false) {
503:                 $symptom_lang = "en";
504:             }
505:             $sym_table = $this->get_sym_base_table($symptom_lang);
506:             $query = "DROP TABLE IF EXISTS $custom_table";
507:             $this->send_query($query);
508:             $query = "CREATE TABLE $custom_table LIKE symptoms";
509:             $this->send_query($query);
510:             $query = "INSERT INTO $custom_table SELECT DISTINCT $sym_table.* FROM $sym_table, sym_rem WHERE $sym_table.sym_id = sym_rem.sym_id AND $where ORDER BY $sym_table.sym_id";
511:             $this->send_query($query);
512:             $this->add_missing_parents($custom_table);
513:             $this->update_symptom_tree($custom_table);
514:             $query = "SELECT COUNT(*) FROM $sym_table";
515:             $this->send_query($query);
516:             list ($sym_num) = $this->db_fetch_row();
517:             $this->free_result();
518:             $query = "INSERT INTO sym_stats (sym_table, sym_base_table, sym_count, username) VALUES ('$custom_table', '$sym_table', $sym_num, '$username') ON DUPLICATE KEY UPDATE sym_base_table = '$sym_table', sym_count = $sym_num, username = '$username'";
519:             $this->send_query($query);
520:         } else {
521:             $this->update_custom_symptom_table();
522:         }
523:     }
524: 
525:     /**
526:      * update_custom_symptom_table updates the custom symptom table if the symptom base table has changed.
527:      *
528:      * This public function is called during log in and also if no user is logged in for updating the default symptom table.
529:      *
530:      * @return void
531:      * @access public
532:      */
533:     function update_custom_symptom_table() {
534:         global $session;
535:         if (!$session->logged_in || $this->is_custom_table('symptoms') === true) {
536:             $symptoms_tbl = $this->get_custom_table('symptoms');
537:             $query = "SELECT sym_count, sym_base_table FROM sym_stats WHERE sym_table = '$symptoms_tbl'";
538:             $this->send_query($query);
539:             list ($sym_num, $sym_base_table) = $this->db_fetch_row();
540:             $this->free_result();
541:             $query = "SELECT COUNT(*) FROM $sym_base_table";
542:             $this->send_query($query);
543:             list ($sym_num_actual) = $this->db_fetch_row();
544:             $this->free_result();
545:             if ($sym_num_actual != $sym_num) {
546:                 $update = true;
547:                 $this->create_custom_symptom_table($symptoms_tbl, $update);
548:             }
549:         }
550:     }
551: 
552:     /**
553:      * add_missing_parents completes parent-rubrics in the symptom table.
554:      *
555:      * If several rubrics exists, which starts with the same rubric before a '>',
556:      * but the parent-rubric doesn't exists, it will be generated.
557:      * If the rubric before '>' exists only once, the '>' will be converted in a comma (',').
558:      *
559:      * @param string  $sym_table optional the symptom table to update
560:      * @return string the log info as html
561:      * @access public
562:      */
563:     function add_missing_parents($sym_table = "symptoms") {
564:         global $session;
565:         $num_parents = 0;
566:         $num_changed = 0;
567:         set_time_limit(0);
568:         ignore_user_abort(true);
569:         $time_start = time();
570:         do {
571:             $num_rows = 0;
572:             $query = "SELECT s1.sym_id, s1.rubric_id, s1.symptom, s1.lang_id, s1.translation, s2.symptom FROM $sym_table AS s1 LEFT JOIN $sym_table AS s2 ON s1.pid = s2.sym_id AND s1.rubric_id = s2.rubric_id AND s1.lang_id = s2.lang_id WHERE s1.symptom LIKE '%>%' ORDER BY sym_id";
573:             $result = $this->send_query($query);
574:             while (list($sym_id, $rubric_id, $symptom, $lang_id, $translation, $db_parent) = $this->db_fetch_row($result)) {
575:                 $symptom_ar = explode(" > ", $symptom);
576:                 $sym_child = array_pop($symptom_ar);
577:                 $parent = implode(" > ", $symptom_ar);
578:                 if (empty($db_parent) || strcasecmp($parent, $db_parent) != 0) {
579:                     $parent_escaped = $this->escape_string($parent);
580:                     $query = "SELECT sym_id FROM $sym_table WHERE rubric_id = $rubric_id AND symptom = '$parent_escaped' LIMIT 1";
581:                     $this->send_query($query);
582:                     $cnt = $this->db_num_rows();
583:                     $this->free_result();
584:                     if ($cnt == 0) {
585:                         $query = "SELECT sym_id FROM $sym_table WHERE rubric_id='$rubric_id' AND symptom LIKE '$parent_escaped > %' LIMIT 2";
586:                         $this->send_query($query);
587:                         $num = $this->db_num_rows();
588:                         $this->free_result();
589:                         if ($num > 1) {
590:                             $current_user = $session->username;
591:                             $query = "INSERT INTO $sym_table (symptom, rubric_id, lang_id, translation, username) VALUES ('$parent_escaped', $rubric_id, '$lang_id', $translation, '$current_user')";
592:                             $this->send_query($query);
593:                             $num_parents ++;
594:                         } else {
595:                             $symptom = $parent . ", " . $sym_child;
596:                             $symptom_escaped = $this->escape_string($symptom);
597:                             $query = "UPDATE $sym_table SET symptom = '$symptom_escaped' WHERE sym_id = $sym_id";
598:                             $this->send_query($query);
599:                             $num_changed ++;
600:                         }
601:                         $num_rows ++;
602:                     }
603:                 }
604:             }
605:             $this->free_result($result);
606:         } while ($num_rows > 0);
607:         $time_end = time();
608:         $time = $time_end - $time_start;
609:         $log = sprintf("<p>" . _("<strong>%d parent-rubrics</strong> were added and <strong>%d symptoms</strong> without parents-rubric were renamed. Time: <strong>%d seconds</strong>.") . "</p>", $num_parents, $num_changed, $time);
610:         return $log;
611:     }
612: 
613:     /**
614:      * update_symptom_tree updates the tree-structure of the symptom table.
615:      *
616:      * First all symptoms that have no ">" in the symptom name will be parsed.
617:      * Then we check if the symptom name occur again in the table followed by ">".
618:      * In this case these symptoms get the "parent_id" of the superior symptom.
619:      * Next all symptoms with one ">" will be parsed and so on.
620:      *
621:      * @param string $sym_table optional the symptom table to update
622:      * @return string the log info as html
623:      * @access public
624:      */
625:     function update_symptom_tree($sym_table = "symptoms") {
626:         $num_rows = 0;
627:         $num_changed = 0;
628:         $num_gesamt = 0;
629:         $num_prev_loop = 0;
630:         set_time_limit(0);
631:         ignore_user_abort(true);
632:         $time_start = time();
633:         $query = "SELECT COUNT(*) FROM $sym_table WHERE symptom NOT LIKE '%>%'";
634:         $this->send_query($query);
635:         list($num_gesamt) = $this->db_fetch_row();
636:         $this->free_result();
637:         if ($num_gesamt > 0) {
638:             $query = "UPDATE $sym_table SET pid = 0 WHERE symptom NOT LIKE '%>%' AND pid != 0";
639:             $this->send_query($query);
640:             $num_changed += $this->db_affected_rows();
641:         }
642:         $symptom_schema = "%";
643:         do {
644:             $query = "SELECT rubric_id FROM $sym_table WHERE symptom LIKE '$symptom_schema>%' LIMIT 1";
645:             $this->send_query($query);
646:             $num_schema = $this->db_num_rows();
647:             $this->free_result();
648:             if ($num_schema == 0) {
649:                 $symptom_schema_max = $symptom_schema;
650:             }
651:             $symptom_schema .= ">%";
652:         } while ($num_schema > 0);
653:         do {
654:             $log = "";
655:             if (isset($num_loop)) {
656:                 $num_prev_loop = $num_loop;
657:             }
658:             $num_loop = 0;
659:             $symptom_schema = $symptom_schema_max;
660:             while ($symptom_schema !== "%") {
661:                 $query = "SELECT s1.sym_id, s1.rubric_id, s1.symptom, s2.symptom FROM $sym_table AS s1 LEFT JOIN $sym_table AS s2 ON s1.pid = s2.sym_id AND s1.rubric_id = s2.rubric_id WHERE s1.symptom LIKE '$symptom_schema' AND s1.symptom NOT LIKE '$symptom_schema>%' ORDER BY sym_id";
662:                 $result = $this->send_query($query);
663:                 $num_rows = $this->db_num_rows($result);
664:                 if ($num_rows > 0) {
665:                     while (list($sym_id, $rubric_id, $symptom, $db_parent) = $this->db_fetch_row($result)) {
666:                         $symptom_ar = explode(" > ", $symptom);
667:                         array_pop($symptom_ar);
668:                         $parent = implode(" > ", $symptom_ar);
669:                         if (empty($db_parent) || strcasecmp($parent, $db_parent) != 0) {
670:                             $parent_escaped = $this->escape_string($parent);
671:                             $query = "SELECT sym_id FROM $sym_table WHERE rubric_id = $rubric_id AND symptom = '$parent_escaped' LIMIT 1";
672:                             $this->send_query($query);
673:                             $num_parents = $this->db_num_rows();
674:                             if ($num_parents > 0) {
675:                                 list($pid) = $this->db_fetch_row();
676:                             }
677:                             $this->free_result();
678:                             if ($num_parents == 0) {
679:                                 while ($num_parents == 0 && count($symptom_ar) > 1) {
680:                                     array_pop($symptom_ar);
681:                                     $parent_escaped = $this->escape_string(implode(" > ", $symptom_ar));
682:                                     $query = "SELECT sym_id FROM $sym_table WHERE rubric_id = $rubric_id AND symptom = '$parent_escaped' LIMIT 1";
683:                                     $this->send_query($query);
684:                                     $num_parents = $this->db_num_rows();
685:                                     if ($num_parents > 0) {
686:                                         list($pid) = $this->db_fetch_row();
687:                                     }
688:                                     $this->free_result();
689:                                 }
690:                                 if ($num_parents == 0) {
691:                                     $pid = 0;
692:                                 }
693:                             }
694:                             $query = "UPDATE $sym_table SET pid='$pid' WHERE sym_id='$sym_id'";
695:                             $this->send_query($query);
696:                             $num_changed ++;
697:                             $num_loop ++;
698:                         }
699:                     }
700:                     $log .= sprintf("<p>" . ngettext("<strong>%d symptom</strong> with the schema <strong>%s</strong> was parsed.", "<strong>%d symptoms</strong> with the schema <strong>%s</strong> were parsed.", $num_rows) . "</p>", $num_rows, $symptom_schema);
701:                 }
702:                 $this->free_result($result);
703:                 $num_gesamt += $num_rows;
704:                 $symptom_schema = substr($symptom_schema, 2);
705:             }
706:         } while ($num_loop > 0 && $num_loop != $num_prev_loop);
707:         $this->send_query("OPTIMIZE TABLE $sym_table");
708:         $time_end = time();
709:         $time = $time_end - $time_start;
710:         $log .= sprintf("<p>" . _("Totally <strong>%d symptoms</strong> were parsed in <strong>%d seconds</strong>.") . _("<strong>%d parent rubrics</strong> were updated.") . "</p>", $num_gesamt, $time, $num_changed);
711:         return $log;
712:     }
713: 
714:     /**
715:      * update_symptom_tables updates the main symptoms table and the language-symptom-tables.
716:      *
717:      * If symptoms were added, the public function completes parent-rubrics in the main symptoms table and
718:      * updates its tree-structure. It also creates new language-symptom-tables and update the sym_stats table.
719:      *
720:      * If no symptoms were added but $update_tree is true the public function only creates new language-symptom-tables.
721:      *
722:      * This public function is called, when an administrator loggs in.
723:      *
724:      * @param boolean  $update_tree
725:      * @return string  the log info as html
726:      * @access public
727:      */
728:     function update_symptom_tables($update_tree = false) {
729:         global $session;
730:         $log = "";
731:         if ($session->logged_in) {
732:             $query = "SELECT sym_count FROM sym_stats WHERE sym_table = 'symptoms'";
733:             $this->send_query($query);
734:             list ($sym_num) = $this->db_fetch_row();
735:             $this->free_result();
736:             $query = "SELECT COUNT(*) FROM symptoms";
737:             $this->send_query($query);
738:             list ($sym_num_actual) = $this->db_fetch_row();
739:             $this->free_result();
740:             if ($sym_num_actual != $sym_num) {
741:                 $log_add_parents = $this->add_missing_parents();
742:                 $log_update_tree = $this->update_symptom_tree();
743:                 $create_tables = true;
744:                 $log = $this->update_lang_symptom_tables($create_tables);
745:                 $log = $log_add_parents . "\n<br>\n" . $log_update_tree . "\n<br>\n" . $log;
746:                 $current_user = $session->username;
747:                 $query = "SELECT COUNT(*) FROM symptoms";
748:                 $this->send_query($query);
749:                 list ($sym_num_new) = $this->db_fetch_row();
750:                 $query = "INSERT INTO sym_stats (sym_table, sym_base_table, sym_count, username) VALUES ('symptoms', 'symptoms', $sym_num_new, '$current_user') ON DUPLICATE KEY UPDATE sym_count = $sym_num_new, username = '$current_user'";
751:                 $this->send_query($query);
752:             } elseif ($update_tree) {
753:                 $create_tables = false;
754:                 $log = $this->update_lang_symptom_tables($create_tables);
755:             }
756:         }
757:         return $log;
758:     }
759: 
760:     /**
761:      * update_lang_symptom_tables updates the language-symptom-tables.
762:      *
763:      * The public function completes parent-rubrics in the language-symptom-tables,
764:      * updates their tree-structure and creates/replaces the language-only views.
765:      *
766:      * If $create_tables is true the language-symptom-tables ("sym__de", "sym__en", etc.)
767:      * will be created/replaced on the base of the main symptom-table ("symptoms").
768:      *
769:      * @param boolean $create_tables
770:      * @return string the log info as html
771:      * @access public
772:      */
773:     function update_lang_symptom_tables($create_tables = false) {
774:         $log = "";
775:         set_time_limit(0);
776:         ignore_user_abort(true);
777:         $query = "SELECT lang_id FROM languages WHERE sym_lang != 0";
778:         $this->send_query($query);
779:         while (list($sym_lang) = $this->db_fetch_row()) {
780:             $sym_lang_ar[] = $sym_lang;
781:         }
782:         $this->free_result();
783:         if ($this->exist_symptom_translation() === true) {
784:             foreach ($sym_lang_ar as $sym_lang) {
785:                 if ($create_tables || !$this->table_exists("sym__$sym_lang")) {
786:                     $query = "DROP TABLE IF EXISTS sym__$sym_lang";
787:                     $this->send_query($query);
788:                     $query = "CREATE TABLE IF NOT EXISTS sym__$sym_lang LIKE symptoms";
789:                     $this->send_query($query);
790:                     $query = "INSERT INTO sym__$sym_lang SELECT * FROM symptoms ORDER BY symptoms.sym_id";
791:                     $this->send_query($query);
792:                     $query = "UPDATE sym__$sym_lang s, sym_translations st SET s.symptom = st.symptom, s.lang_id = st.lang_id, s.translation = 1, s.username = st.username, s.`timestamp` = st.`timestamp` WHERE s.sym_id = st.sym_id AND st.lang_id = '$sym_lang'";
793:                     $this->send_query($query);
794:                 }
795:                 $log_add_parents = $this->add_missing_parents("sym__$sym_lang");
796:                 $log_update_tree = $this->update_symptom_tree("sym__$sym_lang");
797:                 $log .= "\n<br>" . $sym_lang . ":<br>\n" . $log_add_parents . "\n<br>\n" . $log_update_tree;
798:                 $query = "CREATE OR REPLACE VIEW sym__" . $sym_lang . "_only AS SELECT * FROM sym__$sym_lang WHERE lang_id = '$sym_lang'";
799:                 $this->send_query($query);
800:             }
801:         } else {
802:             foreach ($sym_lang_ar as $sym_lang) {
803:                 $query = "CREATE OR REPLACE VIEW sym__" . $sym_lang . "_only AS SELECT * FROM symptoms WHERE lang_id = '$sym_lang'";
804:                 $this->send_query($query);
805:             }
806:         }
807:         return $log;
808:     }
809: 
810:     /**
811:      * get_source_id returns a src_id for a given src_no.
812:      *
813:      * If $source_nr is an array of source-numbers it returns an array of source-id's.
814:      *
815:      * @param array|integer  $source_nr Parameter description (if any) ...
816:      * @return array|string
817:      * @access public
818:      */
819:     function get_source_id($source_nr) {
820:         if (is_array($source_nr)) {
821:             foreach ($source_nr as $src_no) {
822:                 $query = "SELECT src_id FROM sources WHERE src_no = $src_no";
823:                 $this->send_query($query);
824:                 list($src_id) = $this->db_fetch_row();
825:                 $this->free_result();
826:                 $source_id[$src_no] = $src_id;
827:             }
828:             ksort($source_nr, SORT_NUMERIC);
829:         } else {
830:             $query = "SELECT src_id FROM sources WHERE src_no = $source_nr";
831:             $this->send_query($query);
832:             list($source_id) = $this->db_fetch_row();
833:             $this->free_result();
834:         }
835:         return $source_id;
836:     }
837: 
838:     /**
839:      * get_source_nr returns a src_no for a given src_id.
840:      *
841:      * If $source_id is an array of source-id's it returns an array of source-numbers.
842:      *
843:      * @param array|string  $source_id
844:      * @return array|integer
845:      * @access public
846:      */
847:     function get_source_nr($source_id) {
848:         if (is_array($source_id)) {
849:             foreach ($source_id as $src_id) {
850:                 $query = "SELECT src_no FROM sources WHERE src_id = '$src_id'";
851:                 $this->send_query($query);
852:                 list($src_no) = $this->db_fetch_row();
853:                 $this->free_result();
854:                 $source_nr[] = $src_no;
855:             }
856:             sort($source_nr, SORT_NUMERIC);
857:         } else {
858:             $query = "SELECT src_no FROM sources WHERE src_id = '$source_id'";
859:             $this->send_query($query);
860:             list($source_nr) = $this->db_fetch_row();
861:             $this->free_result();
862:         }
863:         return $source_nr;
864:     }
865: 
866:     /**
867:      * extract_src_no returns an array of source-numbers extracted from the given custom table-name.
868:      *
869:      * @param string $custom_table the custom table name to extract from
870:      * @return array
871:      * @access public
872:      */
873:     function extract_src_no($custom_table) {
874:         $prefix_ar = array('sym__', 'sym_rem__', 'materia__');
875:         foreach($prefix_ar as $prefix) {
876:             if (strpos($custom_table, $prefix) === 0) {
877:                 $src_no = substr($custom_table, strlen($prefix));
878:                 break;
879:             }
880:         }
881:         $sym_lang_ar = $this->get_sym_langs();
882:         foreach($sym_lang_ar as $sym_lang) {
883:             $sym_lang_pos = strpos($src_no, "_" . $sym_lang);
884:             if ($sym_lang_pos !== false) {
885:                 $src_no = substr($src_no, 0, $sym_lang_pos);
886:                 break;
887:             }
888:         }
889:         $src_no_ar = explode('_', $src_no);
890:         return $src_no_ar;
891:     }
892: 
893:     /**
894:      * get_symptomname returns the symptom including the main rubric for a given sym_id.
895:      *
896:      * @param integer $sym_id the ID of the symptom to check
897:      * @return string
898:      * @access public
899:      */
900:     function get_symptomname($sym_id) {
901:         global $lang;
902:         $symptoms_tbl = $this->get_custom_table("symptoms");
903:         $query = "SELECT $symptoms_tbl.symptom, main_rubrics.rubric_$lang FROM $symptoms_tbl, main_rubrics WHERE $symptoms_tbl.sym_id = $sym_id AND main_rubrics.rubric_id = $symptoms_tbl.rubric_id";
904:         $this->send_query($query);
905:         list ($symptom, $main_rubric) = $this->db_fetch_row();
906:         $this->free_result();
907:         $symptomname = "$main_rubric >> $symptom";
908:         return $symptomname;
909:     }
910: }
911: // end of class OpenHomeoDB
912: