public function dbRecordExists()
{
$query = "\n\t\t\tSELECT COUNT(*) cnt\n\t\t\tFROM tst_skl_qst_assigns\n\t\t\tWHERE test_fi = %s\n\t\t\tAND question_fi = %s\n\t\t\tAND skill_base_fi = %s\n\t\t\tAND skill_tref_fi = %s\n\t\t";
$res = $this->db->queryF($query, array('integer', 'integer', 'integer', 'integer'), array($this->getTestId(), $this->getQuestionId(), $this->getSkillBaseId(), $this->getSkillTrefId()));
$row = $this->db->fetchAssoc($res);
return $row['cnt'] > 0;
}
private function loadTaxonomyNodeLabels()
{
$IN_nodeIds = $this->db->in('tax_node.obj_id', $this->taxonomyNodeIds, false, 'integer');
$query = "\n\t\t\t\t\tSELECT\t\ttax_node.obj_id tax_node_id,\n\t\t\t\t\t\t\t\ttax_node.title tax_node_title\n\n\t\t\t\t\tFROM\t\ttax_node\n\n\t\t\t\t\tWHERE\t\t{$IN_nodeIds}\n\t\t\t\t";
$res = $this->db->query($query);
while ($row = $this->db->fetchAssoc($res)) {
$this->taxonomyNodeLabels[$row['tax_node_id']] = $row['tax_node_title'];
}
}
public function loadFromDb()
{
$this->resetThresholds();
$query = "\n\t\t\tSELECT test_fi, skill_base_fi, skill_tref_fi, skill_level_fi, threshold\n\t\t\tFROM tst_skl_thresholds\n\t\t\tWHERE test_fi = %s\n\t\t";
$res = $this->db->queryF($query, array('integer'), array($this->getTestId()));
while ($row = $this->db->fetchAssoc($res)) {
$threshold = $this->buildSkillLevelThresholdByArray($row);
$skillKey = $threshold->getSkillBaseId() . ':' . $threshold->getSkillTrefId();
$this->addThreshold($skillKey, $threshold->getSkillLevelId(), $threshold);
}
}
public function load()
{
$query = "\n\t\t\tSELECT *\n\t\t\tFROM qpl_qst_skl_sol_expr\n\t\t\tWHERE question_fi = %s AND skill_base_fi = %s AND skill_tref_fi = %s\n\t\t";
$res = $this->db->queryF($query, array('integer', 'integer', 'integer'), array($this->getQuestionId(), $this->getSkillBaseId(), $this->getSkillTrefId()));
while ($row = $this->db->fetchAssoc($res)) {
$expression = new ilAssQuestionSolutionComparisonExpression();
$expression->setDb($this->db);
$expression->initInstanceFromArray($row);
$this->add($expression);
}
}
public function getNextRequestableHint()
{
$query = "\n\t\t\tSELECT\t\tqht_hint_id\n\t\t\t\n\t\t\tFROM\t\tqpl_hints\n\t\t\t\n\t\t\tWHERE\t\tqht_question_fi = %s\n\t\t\t\n\t\t\tORDER BY\tqht_hint_index ASC\n\t\t";
$res = $this->db->queryF($query, array('integer'), array($this->previewSession->getQuestionId()));
while ($row = $this->db->fetchAssoc($res)) {
if (!$this->isRequested($row['qht_hint_id'])) {
return ilAssQuestionHint::getInstanceById($row['qht_hint_id']);
}
}
throw new ilTestException("no next hint found for questionId={$this->previewSession->getQuestionId()}, userId={$this->previewSession->getUserId()}");
}
private function getPurgableQuestionIds()
{
$INtypes = $this->db->in('object_data.type', $this->getIgnoredContainerObjectTypes(), true, 'text');
$query = "\n\t\t\tSELECT qpl_questions.question_id\n\t\t\tFROM qpl_questions\n\t\t\tINNER JOIN object_data\n\t\t\tON object_data.obj_id = qpl_questions.obj_fi\n\t\t\tAND {$INtypes}\n\t\t\tWHERE qpl_questions.owner = %s\n\t\t\tAND qpl_questions.tstamp = %s\n\t\t";
$res = $this->db->queryF($query, array('integer', 'integer'), array($this->getOwnerId(), 0));
$questionIds = array();
while ($row = $this->db->fetchAssoc($res)) {
$questionIds[] = $row['question_id'];
}
return $questionIds;
}
public function loadFromDb()
{
$this->reset();
$query = "\n\t\t\tSELECT test_fi, question_fi, skill_base_fi, skill_tref_fi, skill_points\n\t\t\tFROM tst_skl_qst_assigns\n\t\t\tWHERE test_fi = %s\n\t\t";
$res = $this->db->queryF($query, array('integer'), array($this->getTestId()));
while ($row = $this->db->fetchAssoc($res)) {
$assignment = $this->buildSkillQuestionAssignmentByArray($row);
$this->addAssignment($assignment);
$this->incrementNumAssignsBySkill($assignment);
$this->incrementMaxPointsBySkill($assignment);
}
}
public function load($testId)
{
$this->byActiveId = array();
$this->byUserId = array();
$query = "\n\t\t\tSELECT\t\tta.active_id,\n\t\t\t\t\t\tta.user_fi user_id,\n\t\t\t\t\t\tud.firstname,\n\t\t\t\t\t\tud.lastname\n\t\t\tFROM\t\ttst_active ta\n\t\t\tLEFT JOIN\tusr_data ud\n\t\t\tON \t\t\tud.usr_id = ta.user_fi\n\t\t\tWHERE\t\ttest_fi = %s\n\t\t\tAND\t\t\t{$this->getConditionalExpression()}\n\t\t";
$res = $this->db->queryF($query, array('integer'), array($testId));
while ($row = $this->db->fetchAssoc($res)) {
$this->byActiveId[$row['active_id']] = $row;
$this->byUserId[$row['user_id']] = $row;
}
$this->setActiveIds(array_keys($this->byActiveId));
$this->setUserIds(array_keys($this->byUserId));
}
private function stageQuestionsFromSourcePool($sourcePoolId)
{
$questionIdMapping = array();
$query = 'SELECT question_id FROM qpl_questions WHERE obj_fi = %s AND complete = %s AND original_id IS NULL';
$res = $this->db->queryF($query, array('integer', 'text'), array($sourcePoolId, 1));
while ($row = $this->db->fetchAssoc($res)) {
$question = assQuestion::_instanciateQuestion($row['question_id']);
$duplicateId = $question->duplicate(true, null, null, null, $this->testOBJ->getId());
$nextId = $this->db->nextId('tst_rnd_cpy');
$this->db->insert('tst_rnd_cpy', array('copy_id' => array('integer', $nextId), 'tst_fi' => array('integer', $this->testOBJ->getTestId()), 'qst_fi' => array('integer', $duplicateId), 'qpl_fi' => array('integer', $sourcePoolId)));
$questionIdMapping[$row['question_id']] = $duplicateId;
}
return $questionIdMapping;
}
public function saveToDb()
{
$tracking = serialize($this->questionTracking);
$postponed = serialize($this->postponedQuestions);
$hidden = serialize(array('correct' => $this->correctAnsweredQuestions, 'wrong' => $this->wrongAnsweredQuestions));
$query = "SELECT COUNT(*) cnt FROM tst_sequence WHERE active_fi = %s AND pass = %s";
$res = $this->db->queryF($query, array('integer', 'integer'), array($this->activeId, 0));
$row = $this->db->fetchAssoc($res);
if ($row['cnt'] > 0) {
$this->db->update('tst_sequence', array('sequence' => array('clob', $tracking), 'postponed' => array('text', $postponed), 'hidden' => array('text', $hidden), 'tstamp' => array('integer', time())), array('active_fi' => array('integer', $this->activeId), 'pass' => array('integer', 0)));
} else {
$this->db->insert('tst_sequence', array('active_fi' => array('integer', $this->activeId), 'pass' => array('integer', 0), 'sequence' => array('clob', $tracking), 'postponed' => array('text', $postponed), 'hidden' => array('text', $hidden), 'tstamp' => array('integer', time())));
}
}
protected function adoptSourceResult($questionId, $sourcePass)
{
$res = $this->db->execute($this->getPreparedSelectResultRecordStatement(), array($this->getActiveId(), $questionId, $sourcePass));
$row = $this->db->fetchAssoc($res);
$resultId = $this->db->nextId('tst_test_result');
$this->db->execute($this->getPreparedInsertResultRecordStatement(), array($resultId, $this->getActiveId(), $questionId, $this->getTargetPass(), time(), $row['points'], $row['manual'], $row['hint_count'], $row['hint_points'], $row['answered']));
}
/**
* @return bool
*/
public function savedDefinitionsExist()
{
$query = "SELECT COUNT(*) cnt FROM tst_rnd_quest_set_qpls WHERE test_fi = %s";
$res = $this->db->queryF($query, array('integer'), array($this->testOBJ->getTestId()));
$row = $this->db->fetchAssoc($res);
return $row['cnt'] > 0;
}
/**
* @param ilTermsOfServiceAcceptanceEntity $entity
* @return ilTermsOfServiceAcceptanceEntity
*/
public function loadById(ilTermsOfServiceAcceptanceEntity $entity)
{
$res = $this->db->queryF('
SELECT *
FROM tos_versions
WHERE id = %s
', array('integer'), array($entity->getId()));
$row = $this->db->fetchAssoc($res);
$entity->setId($row['id']);
$entity->setIso2LanguageCode($row['lng']);
$entity->setSource($row['src']);
$entity->setSourceType($row['src_type']);
$entity->setText($row['text']);
$entity->setTimestamp($row['ts']);
$entity->setHash($row['hash']);
return $entity;
}
/**
* @param integer $poolId
* @return boolean
*/
public function loadFromDb($id)
{
$res = $this->db->queryF("SELECT * FROM tst_rnd_quest_set_qpls WHERE def_id = %s", array('integer'), array($id));
while ($row = $this->db->fetchAssoc($res)) {
$this->initFromArray($row);
return true;
}
return false;
}
private function getNewOrderIndexForQuestionTracking()
{
$query = "\n\t\t\t\tSELECT (MAX(orderindex) + 1) new_order_index\n\t\t\t\tFROM tst_seq_qst_tracking\n\t\t\t\tWHERE active_fi = %s\n\t\t\t\tAND pass = %s\n\t\t\t";
$res = $this->db->queryF($query, array('integer', 'integer'), array($this->getActiveId(), 0));
$row = $this->db->fetchAssoc($res);
if ($row['new_order_index']) {
return $row['new_order_index'];
}
return 1;
}
/**
* Load the value
*/
protected function loadValue()
{
if ($this->value === NULL) {
$datatype = $this->field->getDatatype();
$query = "SELECT * FROM il_dcl_stloc" . $datatype->getStorageLocation() . "_value WHERE record_field_id = " . $this->db->quote($this->id, "integer");
$set = $this->db->query($query);
$rec = $this->db->fetchAssoc($set);
$this->value = $rec['value'];
}
}
/**
* returns the SPECIFIC answer feedback ID for a given question id and answer index.
*
* @final
* @access protected
* @param integer $questionId
* @param boolean $answerIndex
* @return string $feedbackId
*/
protected final function getGenericFeedbackId($questionId, $solutionCompleted)
{
$res = $this->db->queryF("SELECT feedback_id FROM {$this->getGenericFeedbackTableName()} WHERE question_fi = %s AND correctness = %s", array('integer', 'text'), array($questionId, (int) $solutionCompleted));
$feedbackId = null;
while ($row = $this->db->fetchAssoc($res)) {
$feedbackId = $row['feedback_id'];
break;
}
return $feedbackId;
}
private function loadRolesQuery($role)
{
$this->roles[$role] = array();
$q = "SELECT obj_id, title FROM object_data WHERE type = 'role' AND title LIKE 'il_orgu_" . $role . "%'";
$set = $this->db->query($q);
while ($res = $this->db->fetchAssoc($set)) {
$orgu_ref = $this->getRefIdFromRoleTitle($res["title"]);
$this->roles[$role][$orgu_ref] = $res["obj_id"];
$this->role_to_orgu[$role][$res["obj_id"]] = $orgu_ref;
}
}
public function loadQuestions()
{
$query = "\n\t\t\tSELECT\t\tqpl_questions.question_id,\n\t\t\t\t\t\tqpl_qst_type.type_tag,\n\t\t\t\t\t\tqpl_qst_type.plugin\n\n\t\t\tFROM\t\ttst_rnd_cpy\n\n\t\t\tINNER JOIN\tqpl_questions\n\t\t\tON\t\t\tqpl_questions.question_id = tst_rnd_cpy.qst_fi\n\n\t\t\tINNER JOIN\tqpl_qst_type\n\t\t\tON\t\t\tqpl_qst_type.question_type_id = qpl_questions.question_type_fi\n\n\t\t\tWHERE\t\ttst_rnd_cpy.tst_fi = %s\n\t\t\tAND\t\t\ttst_rnd_cpy.qpl_fi = %s\n\n\t\t\t{$this->getConditionalExpression()}\n\t\t";
$res = $this->db->queryF($query, array('integer', 'integer'), array($this->getTestId(), $this->getPoolId()));
//vd($this->db->db->last_query);
while ($row = $this->db->fetchAssoc($res)) {
if (!$this->isActiveQuestionType($row)) {
continue;
}
$this->questions[] = $row['question_id'];
}
}
private function getActiveIds()
{
if (!count($this->getTestObjIds())) {
return null;
}
$inTestObjIds = $this->db->in('obj_fi', $this->getTestObjIds(), false, 'integer');
$res = $this->db->query("\n\t\t\tSELECT active_id\n\t\t\tFROM tst_tests\n\t\t\tINNER JOIN tst_active\n\t\t\tON test_fi = test_id\n\t\t\tWHERE {$inTestObjIds}\n\t\t");
$activeIds = array();
while ($row = $this->db->fetchAssoc($res)) {
$activeIds[] = $row['active_id'];
}
return $activeIds;
}
public function load()
{
$query = "\n\t\t\tSELECT\t\tqpl_questions.*,\n\t\t\t\t\t\tqpl_qst_type.type_tag,\n\t\t\t\t\t\tqpl_qst_type.plugin\n\t\t\tFROM\t\tqpl_questions,\n\t\t\t\t\t\tqpl_qst_type\n\t\t\tWHERE\t\tqpl_questions.original_id IS NULL\n\t\t\tAND\t\t\tqpl_questions.tstamp > 0\n\t\t\tAND\t\t\tqpl_questions.question_type_fi = qpl_qst_type.question_type_id\n\t\t\tAND\t\t\tqpl_questions.obj_fi = %s\n\t\t\t\n\t\t\t{$this->getConditionalExpression()}\n\t\t";
$res = $this->db->queryF($query, array('integer'), array($this->parentObjId));
while ($row = $this->db->fetchAssoc($res)) {
if (!$this->isActiveQuestionType($row)) {
continue;
}
$row['taxonomies'] = $this->loadTaxonomyAssignmentData($row['question_id']);
$row['ttype'] = $this->lng->txt($row['type_tag']);
$this->questions[$row['question_id']] = $row;
}
}
/**
* @param array $params
* @param array $filter
* @return array
* @throws InvalidArgumentException
*/
public function getList(array $params, array $filter)
{
$data = array('items' => array(), 'cnt' => 0);
$select = $this->getSelectPart($filter);
$where = $this->getWherePart($filter);
$from = $this->getFromPart($filter);
$order = $this->getOrderByPart($params);
$group = $this->getGroupByPart();
$having = $this->getHavingPart($filter);
if (isset($params['group'])) {
if (!is_string($params['group'])) {
throw new InvalidArgumentException('Please provide a valid group field parameter.');
}
$group = $params['group'];
}
if (isset($params['limit'])) {
if (!is_numeric($params['limit'])) {
throw new InvalidArgumentException('Please provide a valid numerical limit.');
}
if (!isset($params['offset'])) {
$params['offset'] = 0;
} else {
if (!is_numeric($params['offset'])) {
throw new InvalidArgumentException('Please provide a valid numerical offset.');
}
}
$this->db->setLimit($params['limit'], $params['offset']);
}
$where = strlen($where) ? 'WHERE ' . $where : '';
$query = "SELECT {$select} FROM {$from} {$where}";
if (strlen($group)) {
$query .= " GROUP BY {$group}";
}
if (strlen($having)) {
$query .= " HAVING {$having}";
}
if (strlen($order)) {
$query .= " ORDER BY {$order}";
}
$res = $this->db->query($query);
while ($row = $this->db->fetchAssoc($res)) {
$data['items'][] = $row;
}
$data = $this->getAdditionalItems($data);
if (isset($params['limit'])) {
$cnt_sql = "SELECT COUNT(*) cnt FROM ({$query}) subquery";
$row_cnt = $this->db->fetchAssoc($this->db->query($cnt_sql));
$data['cnt'] = $row_cnt['cnt'];
}
return $data;
}
/**
* Load the value
*/
protected function loadValue()
{
if ($this->value === NULL) {
$datatype = $this->field->getDatatype();
switch ($datatype->getId()) {
case ilDataCollectionDatatype::INPUTFORMAT_RATING:
return true;
}
$query = "SELECT * FROM il_dcl_stloc" . $datatype->getStorageLocation() . "_value WHERE record_field_id = " . $this->db->quote($this->id, "integer");
$set = $this->db->query($query);
$rec = $this->db->fetchAssoc($set);
$this->value = $rec['value'];
}
}
public function loadFromDb()
{
$this->reset();
$res = $this->db->query("\n\t\t\tSELECT obj_fi, question_fi, skill_base_fi, skill_tref_fi, skill_points, eval_mode\n\t\t\tFROM qpl_qst_skl_assigns\n\t\t\tWHERE {$this->getWhereConditions()}\n\t\t");
while ($row = $this->db->fetchAssoc($res)) {
$assignment = $this->buildSkillQuestionAssignmentByArray($row);
if ($assignment->hasEvalModeBySolution()) {
$assignment->loadComparisonExpressions();
// db query
}
$this->addAssignment($assignment);
$this->incrementNumAssignsBySkill($assignment);
$this->incrementMaxPointsBySkill($assignment);
}
}
public function load()
{
$this->checkFilters();
$query = $this->buildQuery();
#vd($query);
$res = $this->db->query($query);
//echo $this->db->db->last_query;
#vd($this->db->db->last_query);
while ($row = $this->db->fetchAssoc($res)) {
if (!$this->isActiveQuestionType($row)) {
continue;
}
$row['taxonomies'] = $this->loadTaxonomyAssignmentData($row['obj_fi'], $row['question_id']);
$row['ttype'] = $this->lng->txt($row['type_tag']);
$this->questions[$row['question_id']] = $row;
}
}
/**
* @param integer $poolObjId
* @return \ilDynamicTestQuestionChangeListener
*/
public static function getPoolQuestionChangeListener(ilDB $db, $poolObjId)
{
$query = "\n\t\t\tSELECT obj_fi\n\t\t\tFROM tst_dyn_quest_set_cfg\n\t\t\tINNER JOIN tst_tests\n\t\t\tON tst_tests.test_id = tst_dyn_quest_set_cfg.test_fi\n\t\t\tWHERE source_qpl_fi = %s\n\t\t";
$res = $db->queryF($query, array('integer'), array($poolObjId));
require_once 'Modules/Test/classes/class.ilDynamicTestQuestionChangeListener.php';
$questionChangeListener = new ilDynamicTestQuestionChangeListener($db);
while ($row = $db->fetchAssoc($res)) {
$questionChangeListener->addTestObjId($row['obj_fi']);
}
return $questionChangeListener;
}
/**
* Fetch a list of entries from the database.
*
* The getList() method can be used to retrieve a collection
* of entries saved in the database in a given table.
*
* @params array $params Possible parameters indexes include:
* - limit [~numeric]
* - offset [~numeric]
* - order_field [~string]
* - order_direction [=ASC|DESC]
* - group [~string]
*
* @param array $params
* @param array $filters
* @throws InvalidArgumentException
* @return array with indexes 'items' and 'cnt'.
*/
public function getList(array $params = array(), array $filters = array())
{
$data = array('items' => array(), 'cnt' => 0);
$select = $this->getSelectPart();
$where = $this->getWherePart($filters);
$from = $this->getFromPart();
$order = "";
$group = "";
$limit = "";
/* Build ORDER BY */
if (isset($params['order_field'])) {
if (!is_string($params['order_field'])) {
throw new InvalidArgumentException("Please provide a valid order field.");
}
if (!isset($params['order_direction'])) {
/* Defaulting to ASC(ending) order. */
$params['order_direction'] = "ASC";
} elseif (!in_array(strtolower($params['order_direction']), array("asc", "desc"))) {
throw new InvalidArgumentException("Please provide a valid order direction.");
}
$order = $params['order_field'] . ' ' . $params['order_direction'];
}
/* Build GROUP BY */
if (isset($params['group'])) {
if (!is_string($params['group'])) {
throw new InvalidArgumentException("Please provide a valid group field parameter.");
}
$group = $params['group'];
}
/* Build LIMIT */
if (isset($params['limit'])) {
if (!is_numeric($params['limit'])) {
throw new InvalidArgumentException("Please provide a valid numerical limit.");
}
if (!isset($params['offset'])) {
$params['offset'] = 0;
} elseif (!is_numeric($params['offset'])) {
throw new InvalidArgumentException("Please provide a valid numerical offset.");
}
$this->db->setLimit($params['limit'], $params['offset']);
}
/* Build SQL query */
$query = "\n\t\t\tSELECT\n\t\t\t\t{$select}\n\t\t\tFROM\n\t\t\t\t{$from}\n\t\t\tWHERE\n\t\t\t\t{$where}\n\t\t";
if (!empty($group)) {
$query .= " GROUP BY {$group}";
}
if (!empty($order)) {
$query .= " ORDER BY {$order}";
}
/* Execute query and fetch items. */
$result = $this->db->query($query);
while ($row = $this->db->fetchObject($result)) {
$data['items'][] = $row;
}
if (isset($params['limit'])) {
/* Fill 'cnt' with total count of items */
$cntSQL = "SELECT COUNT(*) cnt FROM ({$query}) subquery";
$rowCnt = $this->db->fetchAssoc($this->db->query($cntSQL));
$data['cnt'] = $rowCnt['cnt'];
}
return $data;
}
/**
* "Valid" implementation of iterator interface
* @return boolean true/false
*/
public function valid()
{
$this->data = $this->db->fetchAssoc($this->res);
return is_array($this->data);
}
