$sample_import_id = false;
if (isset($_GET['sample_import_id'])) {
$sample_import_id = bigintval($_GET['sample_import_id']);
}
display_sample_chooser($questionnaire_id, $sample_import_id, false, "form-inline form-group clearfix", "form-control");
if ($sample_import_id) {
if (isset($_GET['rowquota'])) {
$qsq = bigintval($_GET['rowquota']);
$qr = 0;
if (isset($_GET['close'])) {
$qr = 1;
}
$sql = "UPDATE questionnaire_sample_quota_row\r\n\t\t\t\tSET quota_reached = {$qr}\r\n\t\t\t\tWHERE questionnaire_sample_quota_row_id = '{$qsq}'";
$db->Execute($sql);
if ($qr == 1) {
close_row_quota($qsq);
} else {
open_row_quota($qsq, false);
}
}
//Display report of quotas
$report = array();
//Rows to display: Strata Status Quota Sample Sample Used Sample Remaining Completes % Complete
//a. (Standard quota) Monitor outcomes of questions in completed questionnaires, and exclude selected sample records when completion limit is reached
//b. (Replicate quota) Exclude selected sample records (where no qsqr_question rows)
$sql = "SELECT qsq.questionnaire_sample_quota_row_id,completions,quota_reached,lime_sid,qsq.description,current_completions, priority, autoprioritise\r\n FROM questionnaire_sample_quota_row as qsq, questionnaire as q\r\n WHERE qsq.questionnaire_id = '{$questionnaire_id}'\r\n\t\t\tAND qsq.sample_import_id = '{$sample_import_id}'\r\n AND q.questionnaire_id = '{$questionnaire_id}'";
$r = $db->GetAll($sql);
foreach ($r as $v) {
$completions = $v['current_completions'];
$priority = $v['priority'];
$autoprioritise = $v['autoprioritise'];
/**
* Update a single row quota
*
* @param int $qsqri The quota row id
* @param int|bool $case_id The case id if known to limit the scope of the search
* @return bool If priorities need to be updated or not
*/
function update_single_row_quota($qsqri, $case_id = false)
{
global $db;
$sql = "SELECT q.lime_sid, qs.questionnaire_id, qs.sample_import_id, qs.completions, qs.autoprioritise\r\n FROM questionnaire as q, questionnaire_sample_quota_row as qs\r\n WHERE q.questionnaire_id = qs.questionnaire_id\r\n AND qs.questionnaire_sample_quota_row_id = {$qsqri}";
$rs = $db->GetRow($sql);
$lime_sid = $rs['lime_sid'];
$questionnaire_id = $rs['questionnaire_id'];
$sample_import_id = $rs['sample_import_id'];
$target_completions = $rs['completions'];
$autoprioritise = $rs['autoprioritise'];
//all variables to exclude for this row quota
$sql2 = "SELECT exclude_val,exclude_var,exclude_var_id,comparison\r\n FROM qsqr_sample\r\n WHERE questionnaire_sample_quota_row_id = {$qsqri}\r\n AND exclude_var_id > 0";
$rev = $db->GetAll($sql2);
//all variables to check in limesurvey for this row quota
$sql2 = "SELECT lime_sgqa,value,comparison\r\n FROM qsqr_question\r\n WHERE questionnaire_sample_quota_row_id = {$qsqri}";
$qev = $db->GetAll($sql2);
//whether a completion was changed for this quota
$updatequota = false;
//whether priorites need to be updated
$update = false;
//default completions at 0
$completions = 0;
//if a case_Id is specified, we can just check if this case matches
//the quota criteria, and if so, increment the quota completions counter
if ($case_id != false) {
if (empty($qev)) {
//just determine if this case is linked to a matching sample record
$sql2 = "SELECT count(*) as c\r\n FROM " . LIME_PREFIX . "survey_{$lime_sid} as s\r\n JOIN `case` as c ON (c.case_id = '{$case_id}')\r\n JOIN `sample` as sam ON (c.sample_id = sam.sample_id) ";
$x = 1;
foreach ($rev as $ev) {
$sql2 .= " JOIN sample_var as sv{$x} ON (sv{$x}.sample_id = sam.sample_id AND sv{$x}.var_id = '{$ev['exclude_var_id']}' AND sv{$x}.val {$ev['comparison']} '{$ev['exclude_val']}') ";
$x++;
}
$sql2 .= " WHERE s.token = c.token";
$match = $db->GetOne($sql2);
} else {
//determine if the case is linked to a matching limesurvey record
$sql2 = "SELECT count(*) as c\r\n FROM " . LIME_PREFIX . "survey_{$lime_sid} as s\r\n JOIN `case` as c ON (c.case_id = '{$case_id}')\r\n JOIN `sample` as sam ON (c.sample_id = sam.sample_id)\r\n WHERE s.token = c.token\r\n ";
foreach ($qev as $ev) {
$sql2 .= " AND s.`{$ev['lime_sgqa']}` {$ev['comparison']} '{$ev['value']}' ";
}
$match = $db->GetOne($sql2);
}
if ($match == 1) {
//increment completions
$sql = "SELECT (current_completions + 1) as c\r\n FROM questionnaire_sample_quota_row\r\n WHERE questionnaire_sample_quota_row_id = '{$qsqri}'";
$cc = $db->GetRow($sql);
$completions = $cc['c'];
$updatequota = true;
}
} else {
if (empty($qev)) {
//find all completions from cases with matching sample records
$sql2 = "SELECT count(*) as c\r\n FROM " . LIME_PREFIX . "survey_{$lime_sid} as s\r\n JOIN `case` as c ON (c.questionnaire_id = '{$questionnaire_id}')\r\n JOIN `sample` as sam ON (c.sample_id = sam.sample_id AND sam.import_id = '{$sample_import_id}')";
$x = 1;
foreach ($rev as $ev) {
$sql2 .= " JOIN sample_var as sv{$x} ON (sv{$x}.sample_id = sam.sample_id AND sv{$x}.var_id = '{$ev['exclude_var_id']}' AND sv{$x}.val {$ev['comparison']} '{$ev['exclude_val']}') ";
$x++;
}
$sql2 .= " WHERE s.submitdate IS NOT NULL\r\n AND s.token = c.token";
$completions = $db->GetOne($sql2);
} else {
//find all completions from cases with matching limesurvey records
$sql2 = "SELECT count(*) as c \r\n FROM " . LIME_PREFIX . "survey_{$lime_sid} as s \r\n JOIN `case` as c ON (c.questionnaire_id = '{$questionnaire_id}') \r\n JOIN `sample` as sam ON (c.sample_id = sam.sample_id AND sam.import_id = '{$sample_import_id}') \r\n WHERE s.submitdate IS NOT NULL \r\n AND s.token = c.token ";
foreach ($qev as $ev) {
$sql2 .= " AND s.`{$ev['lime_sgqa']}` {$ev['comparison']} '{$ev['value']}' ";
}
$completions = $db->GetOne($sql2);
}
$updatequota = true;
}
if ($updatequota) {
if ($completions >= $target_completions) {
//set row quota to reached
$sql = "UPDATE questionnaire_sample_quota_row\r\n SET quota_reached = '1', current_completions = '{$completions}'\r\n WHERE questionnaire_sample_quota_row_id = '{$qsqri}'";
$db->Execute($sql);
close_row_quota($qsqri, false);
//don't update priorires just yet
$update = true;
} else {
$sql = "UPDATE questionnaire_sample_quota_row\r\n SET current_completions = '{$completions}' ";
//If autopriority is set update it here
if ($autoprioritise == 1) {
//priority is 100 - the percentage of completions
$pr = 100 - round(100 * ($completions / $target_completions));
$sql .= ", priority = '{$pr}' ";
//need to update quotas now
$update = true;
}
$sql .= " WHERE questionnaire_sample_quota_row_id = '{$qsqri}'";
$db->Execute($sql);
}
}
return $update;
}
