$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; }