ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilUnitConfigurationRepository.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2013 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
4 include_once "./Modules/TestQuestionPool/classes/class.assFormulaQuestionUnit.php";
5 include_once "./Modules/TestQuestionPool/classes/class.assFormulaQuestionUnitCategory.php";
6 
10 class ilUnitConfigurationRepository
11 {
15  protected $consumer_id = 0;
16 
20  protected $lng;
21 
25  private $units = array();
26 
30  private $categorizedUnits = array();
31 
35  public function __construct($consumer_id)
36  {
40  global $lng;
41 
42  $this->consumer_id = $consumer_id;
43  $this->lng = $lng;
44  }
45 
49  public function setConsumerId($consumer_id)
50  {
51  $this->consumer_id = $consumer_id;
52  }
53 
57  public function getConsumerId()
58  {
59  return $this->consumer_id;
60  }
61 
66  public function isCRUDAllowed($a_category_id)
67  {
71  global $ilDB;
72 
73  $res = $ilDB->queryF(
74  'SELECT * FROM il_qpl_qst_fq_ucat WHERE category_id = %s',
75  array('integer'),
76  array($a_category_id)
77  );
78  $row = $ilDB->fetchAssoc($res);
79  return isset($row['question_fi']) && $row['question_fi'] == $this->getConsumerId();
80  }
81 
88  public function copyCategory($a_category_id, $a_question_fi, $a_category_name = null)
89  {
93  global $ilDB;
94 
95  $res = $ilDB->queryF(
96  'SELECT category FROM il_qpl_qst_fq_ucat WHERE category_id = %s',
97  array('integer'),
98  array($a_category_id)
99  );
100  $row = $ilDB->fetchAssoc($res);
101 
102  if(null === $a_category_name)
103  $a_category_name = $row['category'];
104 
105  $next_id = $ilDB->nextId('il_qpl_qst_fq_ucat');
106  $ilDB->insert('il_qpl_qst_fq_ucat',
107  array(
108  'category_id' => array('integer', $next_id),
109  'category' => array('text', $a_category_name),
110  'question_fi' => array('integer', (int)$a_question_fi)
111  )
112  );
113 
114  return $next_id;
115  }
116 
122  public function copyUnitsByCategories($a_from_category_id, $a_to_category_id, $a_question_fi)
123  {
127  global $ilDB;
128 
129  $res = $ilDB->queryF(
130  'SELECT * FROM il_qpl_qst_fq_unit WHERE category_fi = %s',
131  array('integer'),
132  array($a_from_category_id)
133  );
134  $i = 0;
135  $units = array();
136  while($row = $ilDB->fetchAssoc($res))
137  {
138  $next_id = $ilDB->nextId('il_qpl_qst_fq_unit');
139 
140  $units[$i]['old_unit_id'] = $row['unit_id'];
141  $units[$i]['new_unit_id'] = $next_id;
142 
143  $ilDB->insert('il_qpl_qst_fq_unit',
144  array(
145  'unit_id' => array('integer', $next_id),
146  'unit' => array('text', $row['unit']),
147  'factor' => array('float', $row['factor']),
148  'baseunit_fi' => array('integer', (int)$row['baseunit_fi']),
149  'category_fi' => array('integer', (int)$a_to_category_id),
150  'sequence' => array('integer', (int)$row['sequence']),
151  'question_fi' => array('integer', (int)$a_question_fi)
152  ));
153  $i++;
154  }
155 
156  foreach($units as $unit)
157  {
158  //update unit : baseunit_fi
159  $ilDB->update('il_qpl_qst_fq_unit',
160  array('baseunit_fi' => array('integer', (int)$unit['new_unit_id'])),
161  array(
162  'baseunit_fi' => array('integer', $unit['old_unit_id']),
163  'category_fi' => array('integer', $a_to_category_id)
164  ));
165 
166  //update var : unit_fi
167  $ilDB->update('il_qpl_qst_fq_var',
168  array('unit_fi' => array('integer', (int)$unit['new_unit_id'])),
169  array(
170  'unit_fi' => array('integer', $unit['old_unit_id']),
171  'question_fi' => array('integer', $a_question_fi)
172  ));
173 
174  //update res : unit_fi
175  $ilDB->update('il_qpl_qst_fq_res',
176  array('unit_fi' => array('integer', (int)$unit['new_unit_id'])),
177  array(
178  'unit_fi' => array('integer', $unit['old_unit_id']),
179  'question_fi' => array('integer', $a_question_fi)
180  ));
181 
182  //update res_unit : unit_fi
183  $ilDB->update('il_qpl_qst_fq_res_unit',
184  array('unit_fi' => array('integer', (int)$unit['new_unit_id'])),
185  array(
186  'unit_fi' => array('integer', $unit['old_unit_id']),
187  'question_fi' => array('integer', $a_question_fi)
188  ));
189  }
190  }
191 
192  public function getCategoryUnitCount($id)
193  {
194  global $ilDB;
195 
196  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_unit WHERE category_fi = %s",
197  array('integer'),
198  array($id)
199  );
200  return $result->numRows();
201  }
202 
203  public function isUnitInUse($id)
204  {
205  global $ilDB;
206 
207  $result_1 = $ilDB->queryF("SELECT unit_fi FROM il_qpl_qst_fq_res_unit WHERE unit_fi = %s",
208  array('integer'),
209  array($id)
210  );
211 
212  $result_2 = $ilDB->queryF("SELECT unit_fi FROM il_qpl_qst_fq_var WHERE unit_fi = %s",
213  array('integer'),
214  array($id)
215  );
216  $result_3 = $ilDB->queryF("SELECT unit_fi FROM il_qpl_qst_fq_res WHERE unit_fi = %s",
217  array('integer'),
218  array($id)
219  );
220 
221  $cnt_1 = $ilDB->numRows($result_1);
222  $cnt_2 = $ilDB->numRows($result_2);
223  $cnt_3 = $ilDB->numRows($result_3);
224 
225  if($cnt_1 || $cnt_2 || $cnt_3)
226  {
227  return true;
228  }
229  else
230  {
231  return false;
232  }
233  }
234 
239  public function checkDeleteCategory($id)
240  {
244  global $ilDB;
245 
246  $res = $ilDB->queryF(
247  'SELECT unit_id FROM il_qpl_qst_fq_unit WHERE category_fi = %s',
248  array('integer'),
249  array($id)
250  );
251  if($ilDB->numRows($res))
252  {
253  while($row = $ilDB->fetchAssoc($res))
254  {
255  $unit_res = $this->checkDeleteUnit($row['unit_id'], $id);
256  if(!is_null($unit_res)) return $unit_res;
257  }
258  }
259  return null;
260  }
261 
262  public function deleteUnit($id)
263  {
264  global $ilDB;
265 
266  $res = $this->checkDeleteUnit($id);
267  if(!is_null($res)) return $res;
268  $affectedRows = $ilDB->manipulateF("DELETE FROM il_qpl_qst_fq_unit WHERE unit_id = %s",
269  array('integer'),
270  array($id)
271  );
272  if($affectedRows > 0) $this->clearUnits();
273  return null;
274  }
275 
276  protected function loadUnits()
277  {
278  global $ilDB;
279 
280  $result = $ilDB->query("
281  SELECT units.*, il_qpl_qst_fq_ucat.category, baseunits.unit baseunit_title
282  FROM il_qpl_qst_fq_unit units
283  INNER JOIN il_qpl_qst_fq_ucat ON il_qpl_qst_fq_ucat.category_id = units.category_fi
284  LEFT JOIN il_qpl_qst_fq_unit baseunits ON baseunits.unit_id = units.baseunit_fi
285  ORDER BY il_qpl_qst_fq_ucat.category, units.sequence"
286  );
287 
288  if($result->numRows())
289  {
290  while($row = $ilDB->fetchAssoc($result))
291  {
292  $unit = new assFormulaQuestionUnit();
293  $unit->initFormArray($row);
294  $this->addUnit($unit);
295  }
296  }
297  }
298 
299  public function getCategorizedUnits()
300  {
301  global $ilDB;
302 
303  if(count($this->categorizedUnits) == 0)
304  {
305  $result = $ilDB->queryF("
306  SELECT units.*, il_qpl_qst_fq_ucat.category, il_qpl_qst_fq_ucat.question_fi, baseunits.unit baseunit_title
307  FROM il_qpl_qst_fq_unit units
308  INNER JOIN il_qpl_qst_fq_ucat ON il_qpl_qst_fq_ucat.category_id = units.category_fi
309  LEFT JOIN il_qpl_qst_fq_unit baseunits ON baseunits.unit_id = units.baseunit_fi
310  WHERE units.question_fi = %s
311  ORDER BY il_qpl_qst_fq_ucat.category, units.sequence",
312  array('integer'), array($this->getConsumerId()));
313 
314  if($result->numRows())
315  {
316  $category = '';
317  while($row = $ilDB->fetchAssoc($result))
318  {
319  $unit = new assFormulaQuestionUnit();
320  $unit->initFormArray($row);
321  if(strcmp($category, $unit->getCategory()) != 0)
322  {
323  $cat = new assFormulaQuestionUnitCategory();
324  $cat->initFormArray(array(
325  'category_id' => $row['category_fi'],
326  'category' => $row['category'],
327  'question_fi' => $row['question_fi'],
328  ));
329  array_push($this->categorizedUnits, $cat);
330  $category = $unit->getCategory();
331  }
332  array_push($this->categorizedUnits, $unit);
333  }
334  }
335  }
336 
337  return $this->categorizedUnits;
338  }
339 
340  protected function clearUnits()
341  {
342  $this->units = array();
343  }
344 
345  protected function addUnit($unit)
346  {
347  $this->units[$unit->getId()] = $unit;
348  }
349 
350  public function getUnits()
351  {
352  if(count($this->units) == 0)
353  {
354  $this->loadUnits();
355  }
356  return $this->units;
357  }
358 
359  public function loadUnitsForCategory($category)
360  {
361  global $ilDB;
362 
363  $units = array();
364  $result = $ilDB->queryF("
365  SELECT units.*, baseunits.unit baseunit_title
366  FROM il_qpl_qst_fq_unit units
367  INNER JOIN il_qpl_qst_fq_ucat ON il_qpl_qst_fq_ucat.category_id = units.category_fi
368  LEFT JOIN il_qpl_qst_fq_unit baseunits ON baseunits.unit_id = units.baseunit_fi
369  WHERE il_qpl_qst_fq_ucat.category_id = %s
370  ORDER BY units.sequence",
371  array('integer'),
372  array($category)
373  );
374  if($result->numRows())
375  {
376  while($row = $ilDB->fetchAssoc($result))
377  {
378  $unit = new assFormulaQuestionUnit();
379  $unit->initFormArray($row);
380  array_push($units, $unit);
381  }
382  }
383  return $units;
384  }
385 
390  public function getUnit($id)
391  {
392 
393  if(count($this->units) == 0)
394  {
395  $this->loadUnits();
396  }
397  if(array_key_exists($id, $this->units))
398  {
399  return $this->units[$id];
400  }
401  else
402  {
403  //maybee this is a new unit ...
404  // reload $this->units
405 
406  $this->loadUnits();
407  if(array_key_exists($id, $this->units))
408  {
409  return $this->units[$id];
410  }
411  }
412  return null;
413  }
414 
415 
416  public function getUnitCategories()
417  {
418  global $ilDB;
419 
420  $categories = array();
421  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_ucat WHERE question_fi > %s ORDER BY category",
422  array('integer'), array(0));
423  if($result->numRows())
424  {
425  while($row = $ilDB->fetchAssoc($result))
426  {
427  $value = (strcmp("-qpl_qst_formulaquestion_" . $row["category"] . "-", $this->lng->txt($row["category"])) == 0) ? $row["category"] : $this->lng->txt($row["category"]);
428 
429  if(strlen(trim($row["category"])))
430  {
431  $cat = array(
432  "value" => $row["category_id"],
433  "text" => $value,
434  "qst_id" => $row['question_fi']
435  );
436  $categories[$row["category_id"]] = $cat;
437 
438  }
439  }
440  }
441  return $categories;
442  }
443 
444  public function getAdminUnitCategories()
445  {
446  global $ilDB;
447 
448  $categories = array();
449  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_ucat WHERE question_fi = %s ORDER BY category",
450  array('integer'), array(0));
451  if($result->numRows())
452  {
453  while($row = $ilDB->fetchAssoc($result))
454  {
455  $value = (strcmp("-qpl_qst_formulaquestion_" . $row["category"] . "-", $this->lng->txt($row["category"])) == 0) ? $row["category"] : $this->lng->txt($row["category"]);
456 
457  if(strlen(trim($row["category"])))
458  {
459  $cat = array(
460  "value" => $row["category_id"],
461  "text" => $value,
462  "qst_id" => $row['question_fi']
463  );
464  $categories[$row["category_id"]] = $cat;
465  }
466  }
467  }
468 
469  return $categories;
470  }
471 
476  public function saveUnitOrder($unit_id, $sequence)
477  {
481  global $ilDB;
482 
483  $ilDB->manipulateF('
484  UPDATE il_qpl_qst_fq_unit
485  SET sequence = %s
486  WHERE unit_id = %s AND question_fi = %s
487  ',
488  array('integer', 'integer', 'integer'),
489  array((int)$sequence, $unit_id, $this->getConsumerId())
490  );
491  }
492 
493  public function checkDeleteUnit($id, $category_id = null)
494  {
495  global $ilDB;
496 
497  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_var WHERE unit_fi = %s",
498  array('integer'),
499  array($id)
500  );
501  if($result->numRows() > 0)
502  {
503  return $this->lng->txt("err_unit_in_variables");
504  }
505  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_res WHERE unit_fi = %s",
506  array('integer'),
507  array($id)
508  );
509  if($result->numRows() > 0)
510  {
511  return $this->lng->txt("err_unit_in_results");
512  }
513  if(!is_null($category_id))
514  {
515  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_unit WHERE baseunit_fi = %s AND category_fi != %s",
516  array('integer', 'integer', 'integer'),
517  array($id, $id, $category_id)
518  );
519  }
520  else
521  {
522  $result = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_unit WHERE baseunit_fi = %s AND unit_id != %s",
523  array('integer', 'integer'),
524  array($id, $id)
525  );
526  }
527  if($result->numRows() > 0)
528  {
529  return $this->lng->txt("err_unit_is_baseunit");
530  }
531  return null;
532  }
533 
539  public function getUnitCategoryById($id)
540  {
544  global $ilDB;
545 
546  $query = 'SELECT * FROM il_qpl_qst_fq_ucat WHERE category_id = ' . $ilDB->quote($id, 'integer');
547  $res = $ilDB->query($query);
548  if(!$ilDB->numRows($res))
549  {
550  throw new ilException('un_category_not_exist');
551  }
552 
553  $row = $ilDB->fetchAssoc($res);
554  $category = new assFormulaQuestionUnitCategory();
555  $category->initFormArray($row);
556  return $category;
557  }
558 
563  public function saveCategory(assFormulaQuestionUnitCategory $category)
564  {
568  global $ilDB;
569 
570  $res = $ilDB->queryF(
571  'SELECT * FROM il_qpl_qst_fq_ucat WHERE category = %s AND question_fi = %s AND category_id != %s',
572  array('text', 'integer', 'integer'),
573  array($category->getCategory(), $this->getConsumerId(), $category->getId())
574  );
575  if($ilDB->numRows($res))
576  {
577  throw new ilException('err_wrong_categoryname');
578  }
579 
580  $ilDB->manipulateF(
581  'UPDATE il_qpl_qst_fq_ucat SET category = %s WHERE question_fi = %s AND category_id = %s',
582  array('text', 'integer', 'integer'),
583  array($category->getCategory(), $this->getConsumerId(), $category->getId())
584  );
585  }
586 
591  public function saveNewUnitCategory(assFormulaQuestionUnitCategory $category)
592  {
596  global $ilDB;
597 
598  $res = $ilDB->queryF(
599  'SELECT category FROM il_qpl_qst_fq_ucat WHERE category = %s AND question_fi = %s',
600  array('text', 'integer'),
601  array($category->getCategory(), $this->getConsumerId())
602  );
603  if($ilDB->numRows($res))
604  {
605  throw new ilException('err_wrong_categoryname');
606  }
607 
608  $next_id = $ilDB->nextId('il_qpl_qst_fq_ucat');
609  $ilDB->manipulateF(
610  "INSERT INTO il_qpl_qst_fq_ucat (category_id, category, question_fi) VALUES (%s, %s, %s)",
611  array('integer', 'text', 'integer'),
612  array(
613  $next_id,
614  $category->getCategory(),
615  (int)$this->getConsumerId()
616  )
617  );
618  $category->setId($next_id);
619  }
620 
624  public function getAllUnitCategories()
625  {
629  global $ilDB;
630 
631  $categories = array();
632  $result = $ilDB->queryF(
633  "SELECT * FROM il_qpl_qst_fq_ucat WHERE question_fi = %s OR question_fi = %s ORDER BY category",
634  array('integer', 'integer'),
635  array($this->getConsumerId(), 0)
636  );
637  if($result->numRows())
638  {
639  while($row = $ilDB->fetchAssoc($result))
640  {
641  $category = new assFormulaQuestionUnitCategory();
642  $category->initFormArray($row);
643  $categories[] = $category;
644  }
645  }
646  return $categories;
647  }
648 
653  public function deleteCategory($id)
654  {
658  global $ilDB;
659 
660  $res = $this->checkDeleteCategory($id);
661  if(!is_null($res)) return $this->lng->txt('err_category_in_use');
662 
663  $res = $ilDB->queryF(
664  'SELECT * FROM il_qpl_qst_fq_unit WHERE category_fi = %s',
665  array('integer'),
666  array($id)
667  );
668  while($row = $ilDB->fetchAssoc($res))
669  {
670  $this->deleteUnit($row['unit_id']);
671  }
672  $ar = $ilDB->manipulateF('DELETE FROM il_qpl_qst_fq_ucat WHERE category_id = %s',
673  array('integer'),
674  array($id)
675  );
676  if($ar > 0)
677  {
678  $this->clearUnits();
679  }
680  return null;
681  }
682 
686  public function createNewUnit(assFormulaQuestionUnit $unit)
687  {
691  global $ilDB;
692 
693  $next_id = $ilDB->nextId('il_qpl_qst_fq_unit');
694  $ilDB->manipulateF(
695  'INSERT INTO il_qpl_qst_fq_unit (unit_id, unit, factor, baseunit_fi, category_fi, sequence, question_fi) VALUES (%s, %s, %s, %s, %s, %s, %s)',
696  array('integer', 'text', 'float', 'integer', 'integer', 'integer', 'integer'),
697  array(
698  $next_id,
699  $unit->getUnit(),
700  1,
701  0,
702  (int)$unit->getCategory(),
703  0,
704  (int)$this->getConsumerId()
705  )
706  );
707  $unit->setId($next_id);
708  $unit->setFactor(1);
709  $unit->setBaseUnit(0);
710  $unit->setSequence(0);
711 
712  $this->clearUnits();
713  }
714 
718  public function saveUnit(assFormulaQuestionUnit $unit)
719  {
723  global $ilDB;
724 
725  $res = $ilDB->queryF(
726  'SELECT unit_id FROM il_qpl_qst_fq_unit WHERE unit_id = %s',
727  array('integer'),
728  array($unit->getId())
729  );
730  if($ilDB->fetchAssoc($res))
731  {
732  $row = $ilDB->fetchAssoc($res);
733  $sequence = $row['sequence'];
734  if(is_null($unit->getBaseUnit()) || !strlen($unit->getBaseUnit()))
735  {
736  $unit->setFactor(1);
737  }
738  $ar = $ilDB->manipulateF(
739  'UPDATE il_qpl_qst_fq_unit SET unit = %s, factor = %s, baseunit_fi = %s, category_fi = %s, sequence = %s WHERE unit_id = %s AND question_fi = %s',
740  array('text', 'float', 'integer', 'integer', 'integer', 'integer', 'integer'),
741  array($unit->getUnit(), $unit->getFactor(), (int)$unit->getBaseUnit(), (int)$unit->getCategory(), (int)$unit->getSequence(), (int)$unit->getId(), (int)$this->getConsumerId())
742  );
743  if($ar > 0)
744  {
745  $this->clearUnits();
746  }
747  }
748  }
749 
754  public function cloneUnits($a_from_consumer_id, $a_to_consumer_id)
755  {
759  global $ilDB;
760 
761  $category_mapping = array();
762 
763  $res = $ilDB->queryF("SELECT * FROM il_qpl_qst_fq_ucat WHERE question_fi = %s", array('integer'), array($a_from_consumer_id));
764  while($row = $ilDB->fetchAssoc($res))
765  {
766  $new_category_id = $this->copyCategory($row['category_id'], $a_to_consumer_id);
767  $category_mapping[$row['category_id']] = $new_category_id;
768  }
769 
770  foreach($category_mapping as $old_category_id => $new_category_id)
771  {
772  $res = $ilDB->queryF(
773  'SELECT * FROM il_qpl_qst_fq_unit WHERE category_fi = %s',
774  array('integer'),
775  array($old_category_id)
776  );
777 
778  $i = 0;
779  $units = array();
780  while($row = $ilDB->fetchAssoc($res))
781  {
782  $next_id = $ilDB->nextId('il_qpl_qst_fq_unit');
783 
784  $units[$i]['old_unit_id'] = $row['unit_id'];
785  $units[$i]['new_unit_id'] = $next_id;
786 
787  $ilDB->insert('il_qpl_qst_fq_unit',
788  array(
789  'unit_id' => array('integer', $next_id),
790  'unit' => array('text', $row['unit']),
791  'factor' => array('float', $row['factor']),
792  'baseunit_fi' => array('integer', (int)$row['baseunit_fi']),
793  'category_fi' => array('integer', (int)$new_category_id),
794  'sequence' => array('integer', (int)$row['sequence']),
795  'question_fi' => array('integer', (int)$a_to_consumer_id)
796  ));
797  $i++;
798  }
799 
800  foreach($units as $unit)
801  {
802  //update unit : baseunit_fi
803  $ilDB->update('il_qpl_qst_fq_unit',
804  array('baseunit_fi' => array('integer', (int)$unit['new_unit_id'])),
805  array(
806  'baseunit_fi' => array('integer', (int)$unit['old_unit_id']),
807  'question_fi' => array('integer', (int)$a_to_consumer_id)
808  ));
809 
810  //update var : unit_fi
811  $ilDB->update('il_qpl_qst_fq_var',
812  array('unit_fi' => array('integer', (int)$unit['new_unit_id'])),
813  array(
814  'unit_fi' => array('integer', (int)$unit['old_unit_id']),
815  'question_fi' => array('integer', (int)$a_to_consumer_id)
816  ));
817 
818  //update res : unit_fi
819  $ilDB->update('il_qpl_qst_fq_res',
820  array('unit_fi' => array('integer', (int)$unit['new_unit_id'])),
821  array(
822  'unit_fi' => array('integer', (int)$unit['old_unit_id']),
823  'question_fi' => array('integer', (int)$a_to_consumer_id)
824  ));
825 
826  //update res_unit : unit_fi
827  $ilDB->update('il_qpl_qst_fq_res_unit',
828  array('unit_fi' => array('integer', (int)$unit['new_unit_id'])),
829  array(
830  'unit_fi' => array('integer', (int)$unit['old_unit_id']),
831  'question_fi' => array('integer', (int) $a_to_consumer_id)
832  ));
833  }
834  }
835  }
836 }
ilException
Base class for ILIAS Exception handling.
Definition: class.ilException.php:34
$result
$result
Definition: CleanUpTest.php:407
ilUnitConfigurationRepository
Class ilUnitConfigurationRepository.
Definition: class.ilUnitConfigurationRepository.php:10
$ilDB
global $ilDB
Definition: storeScorm2004.php:16