/**
* Generate the case status report
*
* @param mixed $questionnaire_id The quesitonnaire, if specified
* @param string $sample_id The sample, if speified
* @param mixed $outcome_id THe outcome id, if specified
*
* @return false if empty otherwise true if table drawn
* @author Adam Zammit <*****@*****.**>
* @since 2012-10-02
*/
function case_status_report($questionnaire_id = false, $sample_id = false, $outcome_id = false)
{
global $db;
$q = "";
if ($questionnaire_id !== false) {
$q = "AND c.questionnaire_id = {$questionnaire_id}";
}
$s = "";
if ($sample_id !== false) {
$s = "AND s.import_id = '{$sample_id}'";
}
$o = "";
if ($outcome_id !== false) {
$o = "AND c.current_outcome_id = {$outcome_id}";
}
$sql = "SELECT \tCONCAT('<a href=\\'supervisor.php?case_id=', c.case_id, '\\'>', c.case_id, '</a>') as case_id,\r\n\t\t\to.description as outcomes,\r\n\t\t\tsi.description as samples, s.Time_zone_name as timezone, TIME_FORMAT(CONVERT_TZ(NOW(),@@session.time_zone,s.Time_zone_name),'" . TIME_FORMAT . "') as time, (SELECT COUNT(*) FROM `call` WHERE `call`.case_id = c.case_id) as nrcalls, (SELECT COUNT(*) FROM call_attempt WHERE call_attempt.case_id = c.case_id) as nrattempts, \t\r\n\t\t\tCASE WHEN ca.end IS NULL THEN '" . TQ_("Available") . "'\r\n\t\t\t\tWHEN TIME_TO_SEC(TIMEDIFF(ca.end,CONVERT_TZ(DATE_SUB(NOW(), INTERVAL co.default_delay_minutes MINUTE),'System','UTC'))) < 0 THEN '" . TQ_("Available") . "'\r\n\t\t\t\tELSE CONCAT(ROUND(TIME_TO_SEC(TIMEDIFF(ca.end,CONVERT_TZ(DATE_SUB(NOW(), INTERVAL co.default_delay_minutes MINUTE),'System','UTC'))) / 60),' " . TQ_("minutes") . "')\r\n\t\t\tEND AS availableinmin,\r\n\t\t\tCASE WHEN oq.operator_id IS NULL THEN \r\n\t\t\t\tCONCAT('')\r\n\t\t\tELSE CONCAT('<span class=\\'text-info\\'>', oq.firstName,' ',oq.lastName,'</span>')\r\n\t\t\tEND AS assignedoperator,\r\n\t\t\tCASE WHEN oq.operator_id IS NULL THEN \r\n\t\t\t\tCONCAT('')\r\n\t\t\tELSE CONCAT('   ', cq.sortorder ,' ')\r\n\t\t\tEND AS ordr,\r\n\t\t\tCASE WHEN oq.operator_id IS NULL THEN \r\n\t\t\t\tCONCAT('<span data-toggle=\\'tooltip\\' title=\\'" . TQ_("Not assigned, select to assign") . "\\'><input type=\\'checkbox\\' name=\\'c', c.case_id, '\\' value=\\'', c.case_id, '\\' /></span>')\r\n\t\t\tELSE CONCAT('<a href=\"?questionnaire_id={$questionnaire_id}&sample_import_id={$sample_id}&unassign=', cq.case_queue_id, '\" data-toggle=\\'tooltip\\' title=\\'" . TQ_("Click to unassign") . "\\'><i class=\\'fa fa-trash-o fa-lg text-danger\\'></i></a>')\r\n\t\t\tEND AS flag\t\r\n\t\tFROM `case` as c\r\n\t\tJOIN questionnaire as q ON (q.questionnaire_id = c.questionnaire_id and q.enabled = 1)\r\n\t\tJOIN outcome as o ON (o.outcome_id = c.current_outcome_id AND o.outcome_type_id = 1)\r\n\t\tJOIN sample as s ON (s.sample_id = c.sample_id {$s})\r\n\t\tJOIN sample_import as si ON (s.import_id = si.sample_import_id AND si.enabled = 1)\r\n\t\tJOIN questionnaire_sample as qs ON (qs.questionnaire_id = {$questionnaire_id} AND qs.sample_import_id = s.import_id)\r\n\t\tLEFT JOIN `call` as ca ON (ca.call_id = c.last_call_id)\r\n\t\tLEFT JOIN outcome as co ON (co.outcome_id = ca.outcome_id)\r\n\t\tLEFT JOIN case_queue as cq ON (cq.case_id = c.case_id)\r\n\t\tLEFT JOIN operator as oq ON (cq.operator_id = oq.operator_id)\r\n\t\tLEFT JOIN (questionnaire_sample_quota as qsq) on (s.import_id = qsq.sample_import_id and c.questionnaire_id = qsq.questionnaire_id)\r\n\t\tLEFT JOIN (questionnaire_sample_quota_row as qsqr) on (s.import_id = qsqr.sample_import_id and c.questionnaire_id = qsqr.questionnaire_id)\r\n\t\tWHERE c.current_operator_id IS NULL {$q} {$o}\r\n\t\tAND (qsq.quota_reached IS NULL OR qsq.quota_reached != 1 )\r\n\t\tAND (qsqr.quota_reached IS NULL OR qsqr.quota_reached != 1)\r\n\t\tGROUP BY c.case_id ORDER BY c.case_id ASC";
print "<form method=\"post\" action=\"?questionnaire_id={$questionnaire_id}&sample_import_id={$sample_id}\">";
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("outcomes"));
$datacol = array('case_id', 'samples', 'timezone', 'time', 'nrattempts', 'nrcalls', 'outcomes', 'availableinmin', 'assignedoperator', 'ordr', 'flag');
$headers = array(T_("Case id"), T_("Sample"), T_("Timezone"), T_("Time NOW"), T_("Call attempts"), T_("Calls"), T_("Outcome"), T_("Available in"), T_("Assigned to"), T_("Order"), "<i class='fa fa-check-square-o fa-lg'></i>");
if (isset($_GET['sample_import_id'])) {
unset($datacol[1]);
unset($headers[1]);
}
xhtml_table($rs2, $datacol, $headers, "tclass", false, false, "bs-table");
$sql = "SELECT operator_id as value,CONCAT(firstName,' ', lastName) as description, '' selected\r\n\t\tFROM operator\r\n\t\tWHERE enabled = 1";
$rs3 = $db->GetAll($sql);
print "<h4 class='col-sm-offset-5 pull-left text-right control-label'>" . T_("Assign selected cases to") . " " . T_("operator") . " : </h4> ";
display_chooser($rs3, "operator_id", "operator_id", true, false, false, true, false, true, "pull-left");
print " <button class='btn btn-default' type='submit' data-toggle='tooltip' title='" . T_("Assign cases to operator queue") . "'><i class='fa fa-link fa-lg text-primary'></i> " . T_("Assign") . "</button>";
print "</form></br>";
return true;
}
unset($_POST['dtzph']);
$db->CompleteTrans();
}
xhtml_foot($js_foot);
exit;
}
if (isset($_GET['sampledisable'])) {
$id = intval($_GET['sampledisable']);
$sql = "UPDATE sample_import\r\n\t\tSET enabled = 0\r\n\t\tWHERE sample_import_id = '{$id}'";
$db->Execute($sql);
}
if (isset($_GET['sampleenable'])) {
$id = intval($_GET['sampleenable']);
$sql = "UPDATE sample_import\r\n\t\tSET enabled = 1\r\n\t\tWHERE sample_import_id = '{$id}'";
$db->Execute($sql);
}
$sql = "SELECT\r\n\tCONCAT(' <b class=\\'badge\\'>',sample_import_id,'</b> ') as id,\r\n\tCASE WHEN enabled = 0 THEN\r\n\t\tCONCAT('  <span class=\\'btn label label-default\\'>" . TQ_("Disabled") . "</span> ') \r\n\tELSE\r\n\t\tCONCAT('  <span class=\\'btn label label-primary\\'>" . TQ_("Enabled") . "</span> ') \r\n\tEND as status,\r\n\tCASE WHEN enabled = 0 THEN\r\n\t\tCONCAT('<a href=\\'?sampleenable=',sample_import_id,'\\' class=\\'btn btn-default col-sm-12\\'>" . TQ_("Enable") . " <i class=\\'fa fa-play fa-lg\\' style=\\'color:blue;\\'></i></a>') \r\n\tELSE\r\n\t\tCONCAT('<a href=\\'\\' class=\\'btn btn-default col-sm-12\\' data-toggle=\\'confirmation\\' data-href=\\'?sampledisable=',sample_import_id,'\\' data-title=\\'" . TQ_("ARE YOU SURE?") . "\\' data-btnOkLabel=\\'" . TQ_("Yes") . "\\' data-btnCancelLabel=\\'" . TQ_("Cancel") . "\\'><i class=\\'fa fa-ban fa-lg\\' style=\\'color:red;\\'></i>  " . TQ_("Disable") . "</a> ') \r\n\tEND\r\n\tas enabledisable,\r\n\tCASE WHEN enabled = 1 THEN\r\n\t\tCONCAT('<a href=\\'?edit=',sample_import_id,'\\' class=\\'btn btn-default btn-block\\' data-toggle=\\'tooltip\\' title=\\'" . TQ_("Edit") . " " . TQ_("Viewing permissions") . "\\'><i class=\\'fa fa-eye fa-lg fa-fw \\'></i></a>')\r\n\tELSE\r\n\t\tCONCAT('<a href=\\'?edit=',sample_import_id,'\\' class=\\'btn btn-default \\' data-toggle=\\'tooltip\\' title=\\'" . TQ_("Edit sample parameters") . "\\'><i class=\\'fa fa-eye fa-lg fa-fw text-primary\\'></i> + <i class=\\'fa fa-edit fa-lg fa-fw text-primary\\'></i> + <i class=\\'fa fa-minus-circle fa-lg fa-fw text-danger \\'></i></a>')\r\n\tEND as did,\r\n\tCASE WHEN enabled = 1 THEN\r\n\t\tCONCAT('<a href=\\'\\' class=\\'btn btn-default disabled\\'><i class=\\'fa fa-trash fa-lg fa-fw\\' style=\\'color:grey;\\'></i></a>')\r\n\tELSE\r\n\t\tCONCAT('<a href=\\'\\' class=\\'btn btn-default \\' data-toggle=\\'confirmation\\' data-href=\\'?delete_sample=',sample_import_id,'\\' data-title=\\'" . TQ_("ARE YOU SURE?") . "\\' data-btnOkLabel=\\'" . TQ_("Yes") . "\\' data-btnCancelLabel=\\'" . TQ_("Cancel") . "\\' ><i class=\\'fa fa-trash fa-lg fa-fw text-danger \\' data-toggle=\\'tooltip\\' title=\\'" . TQ_("DELETE SAMPLE") . "\\'></i></a>')\r\n\tEND as delsample,\r\n\tCONCAT('<a href=\\'samplesearch.php?sample_import_id=',sample_import_id,'\\' class=\\'btn btn-default\\' data-toggle=\\'tooltip\\' title=\\'" . TQ_("Search the sample") . "',sample_import_id,'\\'><i class=\\'fa fa-search fa-lg fa-fw text-primary\\'></i></a>') as ssearch,\r\n\tCONCAT('<a href=\\'callhistory.php?sample_import_id=',sample_import_id,'\\' class=\\'btn btn-default\\' data-toggle=\\'tooltip\\' title=\\'" . TQ_("Call history") . " \n" . TQ_("sample") . " ',sample_import_id,'\\'><i class=\\'fa fa-phone fa-lg text-primary\\'></i></a>') as calls,\r\n\tCONCAT('<h4>',description,' </h4>') as description,\r\n\tCONCAT('<h4 class=\\'fa fa-lg text-primary pull-right\\'>',(SELECT COUNT( DISTINCT`sample_var`.sample_id) FROM `sample_var`, `sample` WHERE `sample`.sample_id = `sample_var`.sample_id AND `sample`.import_id = sample_import_id ),' </h4>') as cnt\r\n\tFROM sample_import ORDER BY sample_import_id DESC";
$rs = $db->GetAll($sql);
$subtitle = T_("Sample list");
xhtml_head(T_("Sample management"), true, $css, $js_head, false, false, false, $subtitle);
echo "<div class='form-group'>\r\n\t\t<a href='' onclick='history.back();return false;' class='btn btn-default'><i class='fa fa-chevron-left fa-lg text-primary'></i> " . T_("Go back") . "</a>\r\n\t\t<a href='import.php' class='btn btn-default col-sm-offset-4' ><i class='fa fa-upload fa-lg'></i> " . T_("Import a sample file") . "</a>\r\n\t</div>";
$columns = array("id", "description", "cnt", "status", "enabledisable", "calls", "did", "ssearch", "delsample");
//"vp","rname",
//$titles = array(T_("ID"),T_("Sample"),T_("Records"), T_("Call History"),T_("Enable/Disable"), T_("Status"), T_("Deidentify"), T_("View"), T_("Rename"), T_("Search"), T_("Delete sample"));
xhtml_table($rs, $columns, false, "table-hover table-condensed ");
xhtml_foot($js_foot);
?>
<script type="text/javascript">
$('[data-toggle="confirmation"]').confirmation();
</script>
// print "<p><a href=\"" . $_SERVER['PHP_SELF'] . "?assign=assign\" onclick=\"document.getElementById('links').style.visibility='hidden'; document.getElementById('wait').style.visibility='visible';\">" . T_("Assign next form") . "</a></p>";
print "<form name=\"form1\" action=\"" . $_SERVER['PHP_SELF'] . "\" method=\"post\"><input type=\"submit\" name=\"assign\" onclick=\"document.getElementById('links').style.visibility='hidden'; document.getElementById('wait').style.visibility='visible';\" value=\"" . T_("Assign next form") . "\"/></form>";
print "</div>";
print "<div id=\"wait\" style=\"visibility: hidden;\">\r\n<p>" . T_("Assigning next form: Please wait...") . "</p>\r\n</div>";
//display performance information for each assigned questionnaire
$sql = "SELECT vq.qid, q.description \r\n\t\tFROM verifierquestionnaire as vq, questionnaires as q\r\n\t\tWHERE vq.vid = '{$vid}'\r\n\t\tAND q.qid = vq.qid";
$prs = $db->GetAll($sql);
foreach ($prs as $pr) {
$pqid = $pr['qid'];
$pdes = $pr['description'];
$sql = "SELECT count(*) as rem\r\n\t\t\tFROM forms\r\n\t\t\tWHERE qid = '{$pqid}'\r\n\t\t\tAND done = 0";
$remain = $db->GetOne($sql);
$sql = "SELECT q.description as qu, v.description as ve,f.qid,f.assigned_vid as vid , count( * ) AS c, count( * ) / ( SUM( TIMESTAMPDIFF(\r\n\t\t\tSECOND , f.assigned, f.completed ) ) /3600 ) AS CPH, (\r\n\t\t\t(\r\n\t\t\t\r\n\t\t\tSELECT count( pid )\r\n\t\t\tFROM pages\r\n\t\t\tWHERE qid = f.qid\r\n\t\t\t) * count( * )\r\n\t\t\t) / ( SUM( TIMESTAMPDIFF(\r\n\t\t\tSECOND , f.assigned, f.completed ) ) /3600 ) AS PPH\r\n\t\t\tFROM forms AS f\r\n\t\t\tJOIN questionnaires as q on (f.qid = q.qid)\r\n\t\t\tJOIN verifiers as v on (v.vid = f.assigned_vid)\r\n\t\t\tWHERE f.qid = '{$pqid}'\r\n\t\t\tGROUP BY f.qid, f.assigned_vid\r\n\t\t\tORDER BY CPH DESC";
$prss = $db->GetAll($sql);
print "<h3>{$pdes}</h3>";
xhtml_table($prss, array('ve', 'c', 'CPH', 'PPH'), array(T_("Operator"), T_("Completed Forms"), T_("Completions Per Hour"), T_("Pages Per Hour")), "tclass", array("vid" => $vid));
print "<p>" . T_("Remain to verify") . ": {$remain}</p>";
}
xhtml_foot();
exit;
}
$qid_desc = get_qid_description($fid);
$qid = $qid_desc['qid'];
$description = $qid_desc['description'];
if (!isset($_SESSION['boxes'])) {
//nothing yet known about this form
$sql = "SELECT b.bid as bid, b.tlx as tlx, b.tly as tly, b.brx as brx, b.bry as bry, b.pid as pid, bg.btid as btid, b.bgid as bgid, {$fid} as fid, bg.sortorder as sortorder, fb.filled, c.val as val\r\n\t\tFROM boxes AS b\r\n\t\tJOIN boxgroupstype as bg ON (bg.bgid = b.bgid AND bg.btid > 0 AND bg.btid != 5)\r\n JOIN pages as p ON (p.pid = b.pid AND p.qid = '{$qid}')\r\n LEFT JOIN formboxes as fb ON (fb.bid = b.bid AND fb.fid = '{$fid}')\r\n\t\tLEFT JOIN formboxverifychar AS c ON (c.fid = '{$fid}' AND c.vid = 0 AND c.bid = b.bid)\r\n\t\tORDER BY bg.sortorder ASC";
$sql2 = "SELECT b.bgid,0 as done,b.pid,bg.varname,bg.btid\r\n\t\tFROM boxes as b, boxgroupstype as bg, pages as p\r\n\t\tWHERE p.pid = b.pid\r\n\t\tAND bg.bgid = b.bgid\r\n\t\tAND p.qid = '{$qid}' \r\n\t\tAND bg.btid > 0\r\n\t\tAND bg.btid != 5\r\n\t\tGROUP BY bg.bgid\r\n\t\tORDER BY bg.sortorder ASC";
$sql3 = "SELECT b.pid,b.bgid,0 as done, fp.width, fp.height, fp.fid\r\n\t\tFROM boxes as b\r\n\t\tJOIN pages as p ON (p.qid = '{$qid}' AND b.pid = p.pid)\r\n\t\tJOIN boxgroupstype as bg ON (bg.bgid = b.bgid)\r\n\t\tLEFT JOIN formpages as fp ON (fp.fid = '{$fid}' AND fp.pid = p.pid)\r\n\t\tGROUP BY b.pid\r\n\t\tORDER BY bg.sortorder ASC";
$a = $db->GetAssoc($sql);
if (empty($a)) {
* queXF is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* queXF is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with queXF; if not, write to the Free Software
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*
*/
include_once "../config.inc.php";
include_once "../db.inc.php";
include "../functions/functions.database.php";
include "../functions/functions.xhtml.php";
if (isset($_GET['fpnid'])) {
$fpnid = intval($_GET['fpnid']);
$db->StartTrans();
$sql = "DELETE FROM formpagenote\r\n\t\tWHERE fpnid = '{$fpnid}'";
$db->Execute($sql);
$db->CompleteTrans();
}
xhtml_head(T_("Listing of forms"), true, array("../css/table.css"));
$sql = "SELECT f.fid, f.pid, v.description as name, q.description as quest, CONCAT('<a href=\"?fpnid=', f.fpnid ,'\">" . T_("Delete note") . "</a>') as link, f.note\r\n\tFROM formpagenote as f\r\n\tJOIN forms ON (forms.fid = f.fid)\r\n\tJOIN questionnaires AS q ON (forms.qid = q.qid)\r\n\tLEFT JOIN verifiers AS v ON (v.vid = f.vid)\r\n\tORDER BY f.fid,f.pid ASC";
$fs = $db->GetAll($sql);
xhtml_table($fs, array('fid', 'pid', 'name', 'quest', 'note', 'link'), array(T_('Form ID'), T_('Page ID'), T_('Operator'), T_('Questionnaire'), T_('Note'), T_('Delete note')));
xhtml_foot();
/* Copyright Deakin University 2007,2008,2009
* Written by Adam Zammit - adam.zammit@deakin.edu.au
* For the Deakin Computer Assisted Research Facility: http://www.deakin.edu.au/dcarf/
*
* This file is part of queXF
*
* queXF is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* queXF is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with queXF; if not, write to the Free Software
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*
*/
include_once "../config.inc.php";
include_once "../db.inc.php";
include "../functions/functions.database.php";
include "../functions/functions.xhtml.php";
xhtml_head(T_("Listing of duplicate forms"), true, array("../css/table.css"));
$sql = "SELECT q.description, f.fid, f.pfid\n\tFROM forms as f\n\tLEFT JOIN questionnaires as q on (f.qid = q.qid)\n\tWHERE f.pfid\n\tIN (\n\t\tSELECT pfid\n\t\tFROM forms\n\t\tGROUP BY pfid\n\t\tHAVING COUNT( * ) >1\n\t)\n\tORDER BY f.qid,f.pfid";
$fs = $db->GetAll($sql);
print "<h1>" . T_("Duplicate form listing") . "</h1><p>" . T_("Forms with the same PFID are duplicates") . "</p>";
xhtml_table($fs, array('description', 'fid', 'pfid'), array(T_("Questionnaire"), T_("Formid"), T_("PFID")));
xhtml_foot();
if (substr($key, 0, 4) == 'vid_') {
print "<input type='hidden' name='{$key}' value='{$val}'/>";
}
}
print "<input type='hidden' name='vid' value='{$qid}'/>";
print "<input type='submit' name='submit' value='" . T_("Start training process in background") . "'/>";
print "</form>";
} else {
if (isset($_GET['qid'])) {
//select verifiers
$qid = intval($_GET['qid']);
$sql = "SELECT v.vid, v.description, count( * ) AS c, CONCAT('<input type=\"checkbox\" name=\"vid_', v.vid, '\" value=\"',v.vid,'\"/>') as checkbox\r\n\t\tFROM `forms` AS f\r\n\t\tJOIN verifiers AS v ON ( v.vid = f.assigned_vid )\r\n\t\tWHERE f.qid = '{$qid}'\r\n\t\tGROUP BY f.assigned_vid";
$rs = $db->GetAll($sql);
print "<p>" . T_("Please choose which verifiers to include in training") . "</p>";
print "<form action='?' method='get'>";
xhtml_table($rs, array('description', 'c', 'checkbox'), array(T_("Verifier"), T_("Number of forms"), T_("Include in training?")));
print "<input type='hidden' name='qid' value='{$qid}'/>";
print "<input type='hidden' name='vid' value='{$qid}'/>";
print "<input type='submit' name='submitc' value='" . T_("Continue training") . "'/>";
print "</form>";
} else {
//select a questionnaire
//form to choose a questionnaire/form
$sql = "SELECT qid,description\r\n FROM questionnaires\r\n ORDER BY qid DESC";
$qs = $db->GetAll($sql);
foreach ($qs as $q) {
print "<a href=\"?qid={$q['qid']}\">" . T_("ICR Train") . ": {$q['description']}</a>";
print "<br/>";
}
}
}
if (substr($g, 0, 3) == "pid") {
$auto_code = 0;
if (isset($_POST["auto" . $v])) {
$auto_code = 1;
}
$sql = "INSERT INTO operator_process (operator_id,process_id,auto_code)\r\n\t\t\t\tVALUES ({$operator_id},{$v},{$auto_code})";
$db->Execute($sql);
}
}
$db->CompleteTrans();
}
xhtml_head(T_("Assign operators to processes"), true, array("../css/table.css"), array("../js/display.js"));
$operator_id = 0;
if (isset($_GET['operator_id'])) {
$operator_id = intval($_GET['operator_id']);
}
//Select operator
$sql = "SELECT operator_id as value, description, CASE WHEN operator_id = '{$operator_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\tFROM operator";
print "<div>" . T_("Select operator:") . "</div>";
display_chooser($db->GetAll($sql), 'operator_id', 'operator_id');
if ($operator_id != 0) {
print "<div>" . T_("Select processes available to this operator") . "</div>";
print "<form action='' method='post'>";
//display a checkbox of all processes
$sql = "SELECT p.process_id,p.description, CONCAT('<input type=\\'checkbox\\' name=\\'pid', p.process_id, '\\' value=\\'', p.process_id, '\\' ', CASE WHEN op.process_id IS NOT NULL THEN 'checked=\\'checked\\'' ELSE '' END , '/>') as cbox, CONCAT('<input type=\\'checkbox\\' name=\\'auto', p.process_id, '\\' value=\\'', p.process_id, '\\' ', CASE WHEN (op.process_id IS NOT NULL AND op.auto_code = 1) THEN 'checked=\\'checked\\'' ELSE '' END , '/>') as abox \r\n\r\n\t\tFROM process as p\r\n\t\tLEFT JOIN operator_process AS op ON (op.operator_id = {$operator_id} AND op.process_id = p.process_id)";
$rs = $db->GetAll($sql);
translate_array($rs, array("description"));
xhtml_table($rs, array('description', 'cbox', 'abox'), array(T_("Process"), T_("Select"), T_("Allow queXC to auto guess code")));
print "<div><input type='submit' name='submit' value='" . T_("Assign processes") . "'/></div></form>";
}
xhtml_foot();
exit;
}
if (isset($_GET['data'])) {
outputdata(intval($_GET['data']));
exit;
}
if (isset($_GET['csvlabel'])) {
outputdatacsv(intval($_GET['csvlabel']), "", true);
exit;
}
if (isset($_GET['csv'])) {
outputdatacsv(intval($_GET['csv']));
exit;
}
if (isset($_GET['csvmerged'])) {
outputdatacsv(intval($_GET['csvmerged']), "", false, false, false, true);
exit;
}
if (isset($_GET['banding'])) {
export_banding(intval($_GET['banding']));
exit;
}
if (isset($_GET['pspp'])) {
export_pspp(intval($_GET['pspp']));
exit;
}
xhtml_head(T_("Output data"), true, array("../css/table.css"));
$sql = "SELECT description,\n\t\tCONCAT('<a href=\"?data=', qid, '\">" . T_("Data") . "</a>') as data,\n\t\tCONCAT('<a href=\"?ddi=', qid, '\">" . T_("DDI") . "</a>') as ddi,\n\t\tCONCAT('<a href=\"?csv=', qid, '\">" . T_("CSV") . "</a>') as csv,\n\t\tCONCAT('<a href=\"?csvmerged=', qid, '\">" . T_("CSV Merged") . "</a>') as csvmerged,\n\t\tCONCAT('<a href=\"?csvlabel=', qid, '\">" . T_("CSV Labelled") . "</a>') as csvlabel,\n\t\tCONCAT('<a href=\"?pspp=', qid, '\">" . T_("PSPP (SPSS)") . "</a>') as pspp,\n\t\tCONCAT('<a href=\"?banding=', qid, '\">" . T_("Banding XML") . "</a>') as banding\n\tFROM questionnaires\n\tORDER BY qid ASC";
$qs = $db->GetAll($sql);
xhtml_table($qs, array('description', 'data', 'ddi', 'csv', 'csvmerged', 'csvlabel', 'pspp', 'banding'), array(T_("Questionnaire"), T_("Data"), T_("DDI"), T_("CSV"), T_("CSV Merged"), T_("CSV Labelled"), T_("PSPP (SPSS)"), T_("Banding XML")));
xhtml_foot();
$questionnaire_id = $q['questionnaire_id'];
$qsid = $q['lime_sid'];
$sql = "SELECT o.calc, count( c.case_id )\r\n\t\t\t\tFROM `case` AS c, `outcome` AS o\r\n\t\t\t\tWHERE c.questionnaire_id = '{$questionnaire_id}'\r\n\t\t\t\tAND c.current_outcome_id = o.outcome_id\r\n\t\t\t\tGROUP BY o.calc";
$a = $db->GetAssoc($sql);
$a = aapor_clean($a);
print "<table class='table-hover table-condensed tclass'><thead class=\"highlight\"><tr><th>" . T_("Outcome") . "</th><th>" . T_("Rate") . "</th></tr></thead>";
print "<tr><td>" . T_("Response Rate 1") . "</td><td>" . round(aapor_rr1($a), 2) . "</td></tr>";
print "<tr><td>" . T_("Refusal Rate 1") . "</td><td>" . round(aapor_ref1($a), 2) . "</td></tr>";
print "<tr><td>" . T_("Cooperation Rate 1") . "</td><td>" . round(aapor_coop1($a), 2) . "</td></tr>";
print "<tr><td>" . T_("Contact Rate 1") . "</td><td>" . round(aapor_con1($a), 2) . "</td></tr>";
print "</table></br>";
$sql = "SELECT o.description as des, o.outcome_id, count( c.case_id ) as count\r\n\t\t\t\tFROM `case` AS c, `outcome` AS o\r\n\t\t\t\tWHERE c.questionnaire_id = '{$questionnaire_id}'\r\n\t\t\t\tAND c.current_outcome_id = o.outcome_id\r\n\t\t\t\tGROUP BY o.outcome_id";
$rs = $db->GetAll($sql);
if (!empty($rs)) {
translate_array($rs, array("des"));
xhtml_table($rs, array("des", "count"), array(T_("Outcome"), T_("Count")), "tclass", array("des" => "Complete"), array("count"));
} else {
print "<p class='alert alert-info'>" . T_("No outcomes recorded for this questionnaire") . "</p>";
}
print "</br><a href=\"?qsid={$qsid}\" class=\"btn btn-default btn-block btn-lime\">" . T_("View summary results") . "</a></div></div>";
}
if (isset($_GET['qsid'])) {
$qsid = intval($_GET['qsid']);
$page = LIME_URL . "admin/admin.php?action=browse&sid={$qsid}";
} else {
$page = '';
}
if ($page) {
?>
<div class="col-lg-10 pull-right" id=" " style="height:820px;">
if (isset($_GET['qid'])) {
$qid = intval($_GET['qid']);
$sql = "SELECT q.description as qu, v.description as ve,f.qid,w.vid , count( * ) AS c, count( * ) / ( SUM( TIMESTAMPDIFF(\n\tSECOND , w.assigned, w.completed ) ) /3600 ) AS CPH, (\n\t(\n\t\n\tSELECT count( pid )\n\tFROM pages\n\tWHERE qid = f.qid\n\t) * count( * )\n\t) / ( SUM( TIMESTAMPDIFF(\n\tSECOND , w.assigned, w.completed ) ) /3600 ) AS PPH\n\tFROM worklog AS w\n\tJOIN forms AS f ON ( f.fid = w.fid )\n\tJOIN questionnaires as q on (f.qid = q.qid)\n\tJOIN verifiers as v on (v.vid = w.vid)\n\tWHERE f.qid = '{$qid}'\n\tGROUP BY f.qid, w.vid\n\tORDER BY CPH DESC";
$rs = $db->GetAll($sql);
if (!empty($rs)) {
print "<h1>{$rs[0]['qu']}</h1>";
xhtml_table($rs, array('ve', 'c', 'CPH', 'PPH'), array(T_("Operator"), T_("Completed Forms"), T_("Completions Per Hour"), T_("Pages Per Hour")));
}
} else {
if (isset($_GET['vid'])) {
$vid = intval($_GET['vid']);
$sql = "SELECT q.description as qu, v.description as ve,f.qid,w.vid , count( * ) AS c, count( * ) / ( SUM( TIMESTAMPDIFF(\n\tSECOND , w.assigned, w.completed ) ) /3600 ) AS CPH, (\n\t(\n\t\n\tSELECT count( pid )\n\tFROM pages\n\tWHERE qid = f.qid\n\t) * count( * )\n\t) / ( SUM( TIMESTAMPDIFF(\n\tSECOND , w.assigned, w.completed ) ) /3600 ) AS PPH\n\tFROM worklog AS w\n\tJOIN forms AS f ON ( f.fid = w.fid )\n\tJOIN questionnaires as q on (f.qid = q.qid)\n\tJOIN verifiers as v on (v.vid = w.vid)\n\tWHERE w.vid = '{$vid}'\n\tGROUP BY f.qid, w.vid\n\tORDER BY CPH DESC";
$rs = $db->GetAll($sql);
if (!empty($rs)) {
print "<h1>{$rs[0]['ve']}</h1>";
xhtml_table($rs, array('qu', 'c', 'CPH', 'PPH'), array(T_("Questionnaire"), T_("Completed Forms"), T_("Completions Per Hour"), T_("Pages Per Hour")));
}
} else {
$sql = "SELECT CONCAT( f.qid, '_', w.vid ) AS qv, count( * ) AS c, count( * ) / ( SUM( TIMESTAMPDIFF(\n\tSECOND , w.assigned, w.completed ) ) /3600 ) AS CPH, (\n\t(\n\t\n\tSELECT count( pid )\n\tFROM pages\n\tWHERE qid = f.qid\n\t) * count( * )\n\t) / ( SUM( TIMESTAMPDIFF(\n\tSECOND , w.assigned, w.completed ) ) /3600 ) AS PPH\n\tFROM worklog AS w\n\tJOIN forms AS f ON ( f.fid = w.fid )\n\tGROUP BY f.qid, w.vid\n\tORDER BY qid ASC , CPH DESC , qid ASC\t";
$qs = $db->GetAssoc($sql);
$sql = "SELECT qid,description\n\t\tFROM questionnaires\n\t\tORDER by qid ASC";
$questionnaires = $db->GetAll($sql);
$sql = "SELECT vid,description\n\t\tFROM verifiers\n\t\tORDER by vid ASC";
$verifiers = $db->GetAll($sql);
print "<table class='tclass'><tr><th></th>";
foreach ($questionnaires as $q) {
print "<th><a href='?qid={$q['qid']}'>{$q['description']}</a></th>";
}
print "</tr>";
$odd = 1;
foreach ($verifiers as $v) {
"/>
<input type="text" class="textclass form-control" name="note" id="note" style="width: 70%;"/> 
<button class="submitclass btn btn-default" type="submit" name="submit"><i class="fa fa-file-text"></i> <?php
echo T_("Add note");
?>
</button>
</form>
<?php
print "</div>";
//view timeslots
$sql = "SELECT count(*)\r\n FROM questionnaire_timeslot as q, `case` as c\r\n WHERE c.case_id = {$case_id}\r\n AND c.questionnaire_id = q.questionnaire_id";
if ($db->GetOne($sql) >= 1) {
$sql = "SELECT ag.description, (SELECT COUNT(*) FROM availability as a, `call_attempt` as ca \r\n\t\t\tWHERE ca.case_id = c.case_id \r\n\t\t\tAND a.availability_group_id = ag.availability_group_id\r\n\t\t\tAND (a.day_of_week = DAYOFWEEK(CONVERT_TZ(ca.start,'UTC',s.Time_zone_name)) \r\n\t\t\tAND TIME(CONVERT_TZ(ca.start, 'UTC' , s.Time_zone_name)) >= a.start \r\n\t\t\tAND TIME(CONVERT_TZ(ca.start, 'UTC' , s.Time_zone_name)) <= a.end)) as cou\r\n FROM availability_group as ag, `case` as c, `questionnaire_timeslot` as qt, sample as s\r\n WHERE c.case_id = '{$case_id}'\r\n AND s.sample_id = c.sample_id\r\n AND qt.questionnaire_id = c.questionnaire_id AND ag.availability_group_id = qt.availability_group_id";
if (array("cou") >= 1) {
print "<div class='panel-body col-sm-6'><h4 class=''><i class='fa fa-list'></i> " . T_("Call attempts by timeslot") . "</h4>";
xhtml_table($db->GetAll($sql), array('description', 'cou'), array(T_("Time slot"), T_("Call attempts")));
//,"tclass",false,array("cou")
print "</div>";
}
} else {
print "<b class=' text-info col-sm-6'>" . T_("Time slots NOT defined") . "</b>";
}
print "<div class='clearfix '></div><div class='col-sm-6'>";
if (isset($r[0]['sample_id'])) {
//if sample data exists assign this to an operator for their next case
print "<div class='panel-body'><h4><i class='fa fa-link'></i> " . T_("Assign this case to operator (will appear as next case for them)") . "</h4>";
?>
<form method="get" action="?" class="form-inline">
<?php
$sql = "SELECT operator_id as value,CONCAT(firstName,' ', lastName) as description, CASE WHEN next_case_id = '{$case_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\t\t\tFROM operator\r\n\t\t\t\tWHERE enabled = 1";
$rs3 = $db->GetAll($sql);
/**
* XHTML functions
*/
include "functions/functions.xhtml.php";
/**
* Operator functions
*/
include "functions/functions.operator.php";
$js = false;
if (AUTO_LOGOUT_MINUTES !== false) {
$js = array("include/jquery/jquery-1.4.2.min.js", "js/childnap.js");
}
xhtml_head(T_("Case History List"), false, array("css/table.css"), $js, false, 60);
//List the case call history
// display in respondent time so that the operator will be able to
// quote verbatim to the respondent if necessary
$operator_id = get_operator_id();
if ($operator_id) {
global $db;
$sql = "SELECT DATE_FORMAT(CONVERT_TZ(c.start,'UTC',op.Time_zone_name),'" . DATE_TIME_FORMAT . "') as start,CONVERT_TZ(c.end,'UTC',op.Time_zone_name) as end, o.description as des, c.case_id as case_id, r.firstName as firstName\r\n\t\tFROM `call` as c\r\n\t\tJOIN (operator as op, outcome as o, respondent as r) on (c.operator_id = op.operator_id and c.outcome_id = o.outcome_id and r.respondent_id = c.respondent_id)\r\n\t\tWHERE c.operator_id = '{$operator_id}'\r\n\t\tORDER BY c.start DESC\r\n\t\tLIMIT 25";
$rs = $db->GetAll($sql);
if (empty($rs)) {
print "<p>" . T_("No calls ever made") . "</p>";
} else {
translate_array($rs, array("des"));
xhtml_table($rs, array("start", "case_id", "des", "firstName"), array(T_("Date/Time"), T_("Case ID"), T_("Outcome"), T_("Respondent")));
}
} else {
print "<p>" . T_("No operator") . "</p>";
}
xhtml_foot();
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with queXF; if not, write to the Free Software
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*
*/
include_once "../config.inc.php";
include_once "../db.inc.php";
include "../functions/functions.database.php";
include "../functions/functions.xhtml.php";
if (isset($_GET['fid'])) {
$fid = intval($_GET['fid']);
$db->StartTrans();
$sql = "SELECT pfid \r\n\t\tFROM forms\r\n\t\tWHERE fid = '{$fid}'";
$rs = $db->GetRow($sql);
$pfid = $rs['pfid'];
$sql = "DELETE FROM formpages\r\n\t\tWHERE fid = '{$fid}'";
$db->Execute($sql);
$sql = "DELETE FROM forms\r\n\t\tWHERE fid = '{$fid}'";
$db->Execute($sql);
$sql = "UPDATE processforms\r\n\t\tSET allowanother = 1\r\n\t\tWHERE pfid = '{$pfid}'";
$db->Execute($sql);
$db->CompleteTrans();
}
xhtml_head(T_("Pages missing from scan"), true, array("../css/table.css"));
$sql = "SELECT f.fid AS fid, f.description, q.description as qdesc, \r\n\tCASE WHEN EXISTS (SELECT mpid FROM missingpages WHERE fid = f.fid) THEN CONCAT('<a href=\\'missingpages.php?missingfid=',f.fid,'\\'>" . T_("Handle undetected pages") . "</a>') ELSE CONCAT('<a href=\\'?fid=' , f.fid , '\\'>" . T_("Delete form and allow importing again") . "</a>') END as link\r\n\tFROM forms AS f\r\n\tLEFT JOIN questionnaires as q on (f.qid = q.qid)\r\n\tWHERE f.done =0\r\n\tAND f.assigned_vid IS NULL\r\n\tAND EXISTS(\r\n SELECT p.pid\r\n FROM pages AS p\r\n WHERE p.qid = f.qid\r\n AND NOT EXISTS \r\n (SELECT fp.fid \r\n FROM formpages AS fp \r\n WHERE fp.fid = f.fid \r\n AND fp.pid = p.pid))\r\n\tORDER BY f.fid ASC";
$fs = $db->GetAll($sql);
xhtml_table($fs, array('fid', 'description', 'qdesc', 'link'), array(T_('Form ID'), T_('Form'), T_('Questionnaire'), T_('Delete form and allow importing again')));
xhtml_foot();
$xmlname = $_FILES['icrxml']['tmp_name'];
$r = import_icr(file_get_contents($xmlname));
if ($a) {
if ($r) {
print "<h2>" . T_("Successfully loaded ICR XML file") . "</h2>";
} else {
print "<h2>" . T_("Failed to load ICR XML file") . "</h2>";
}
}
}
print "<h2>" . T_("Import ICR KB from XML") . "</h2>";
$sql = "SELECT CONCAT('<a href=\"?kb=',IFNULL(kb,'KB'),'\">', description,'</a>') as link\r\n\tFROM ocrkb";
$rs = $db->GetAll($sql);
?>
<form enctype="multipart/form-data" action="" method="post">
<p><input type="hidden" name="MAX_FILE_SIZE" value="1000000000" /></p>
<p><?php
echo T_("Select ICR KB XML file");
?>
: <input name="icrxml" type="file" /></p>
<p><input type="submit" value="<?php
echo T_("Upload XML");
?>
" /></p>
</form>
<?php
print "<h2>" . T_("Export ICR KB to XML") . "</h2>";
xhtml_table($rs, array("link"), array(T_("ICR KB")));
xhtml_foot();
$fdesc[] = T_("Case ID");
$sql = "SELECT var,var_id\r\n\t\t\t\tFROM sample_import_var_restrict\r\n\t\t\t\tWHERE sample_import_id = {$sample_import_id}\r\n\t\t\t\tORDER by var ASC";
$rs = $db->GetAll($sql);
foreach ($rs as $rsw) {
$fnames[] = $rsw['var_id'];
$fdesc[] = $rsw['var'];
}
foreach ($r as &$rw) {
$sql = "SELECT var_id,val\r\n\t\t\t\t\tFROM sample_var\r\n\t\t\t\t\tWHERE sample_id = {$rw['sample_id']}";
$rs = $db->GetAll($sql);
foreach ($rs as $rsw) {
$rw[$rsw['var_id']] = $rsw['val'];
}
}
print "<div class='form-group'>";
xhtml_table($r, $fnames, $fdesc, "tclass", false, false, "bs-table");
print "</div>";
} else {
print "<div class='alert alert-info col-sm-6 ' role='alert'><h4>" . T_("There's no data in this sample.") . "</h4></div>";
}
}
xhtml_foot($js_foot);
?>
<script type="text/javascript">
$('#bs-table').bdt();
/* $('#bs-table').bootgrid({
ajax: true,
post: "$sample_import_id",
url: "?sample_import_id=\"$sample_import_id\"",
}); */
xhtml_foot();
} else {
if (isset($_POST['import_file'])) {
//file has been submitted
$subtitle = T_("Check data to submit");
xhtml_head(T_("Bulk appointment generator"), true, array("../include/bootstrap/css/bootstrap.min.css", "../css/custom.css"), false, false, false, false, $subtitle);
?>
<form action="" method="post" >
<?php
$tmpfname = tempnam(TEMPORARY_DIRECTORY, "FOO");
move_uploaded_file($_FILES['file']['tmp_name'], $tmpfname);
$todo = validate_bulk_appointment($tmpfname);
if (is_array($todo) && !empty($todo)) {
print "<p class='well'>" . T_("Please check the case id's, appointment start and end times and notes are correct before accepting below") . "</p>";
$todoh = array(T_("Case id"), T_("Start time"), T_("End time"), T_("Note"));
xhtml_table($todo, array(0, 1, 2, 3), $todoh);
?>
<form action="" method="post">
<input type="hidden" name="tmpfname" value="<?php
echo $tmpfname;
?>
" />
<input type="submit" name="import_file" value="<?php
echo T_("Accept and generate bulk appointments");
?>
" class="btn btn-primary"/>
</form>
<?php
} else {
print "<p class='well text-danger'>" . T_("The file does not contain at least caseid, starttime and endtime columns. Please try again.") . "</p>";
}
<p><a href="?"><?php
echo T_("Go back");
?>
</a></p>
<?php
} else {
global $db;
$db->StartTrans();
$operator_id = get_operator_id();
$case_id = get_case_id($operator_id);
if ($case_id) {
if (isset($_POST['note'])) {
$note = $db->qstr($_POST['note']);
$sql = "INSERT INTO `case_note` (case_note_id,case_id,operator_id,note,datetime)\r\n\t\t\t\tVALUES (NULL,'{$case_id}','{$operator_id}',{$note},CONVERT_TZ(NOW(),'System','UTC'))";
$db->Execute($sql);
}
$sql = "SELECT DATE_FORMAT(CONVERT_TZ(c.datetime,'UTC',op.Time_zone_name),'" . DATE_TIME_FORMAT . "') as time, op.firstName, op.lastName, c.note as note\r\n\t\t\tFROM `case_note` as c\r\n\t\t\tJOIN (operator as op) on (c.operator_id = op.operator_id)\r\n\t\t\tWHERE c.case_id = '{$case_id}'\r\n\t\t\tORDER BY c.datetime DESC";
$rs = $db->GetAll($sql);
print "<div><a href=\"?add=add\">" . T_("Add note") . "</a></div>";
if (empty($rs)) {
print "<p>" . T_("No notes") . "</p>";
} else {
xhtml_table($rs, array("time", "firstName", "note"), array(T_("Date/Time"), T_("Operator"), T_("Note")));
}
} else {
print "<p>" . T_("No case") . "</p>";
}
$db->CompleteTrans();
}
xhtml_foot();
$qs = $db->GetAll($sql);
if (!empty($qs)) {
$co = count($qs);
if ($co > 1) {
for ($i = 0; $i < $co; $i++) {
$down = "<a href='?sort_order={$qs[$i]['sort_order']}&sort=down' data-toggle=\"tooltip\" title=\"" . T_("Pull step Down") . "\"><i class=\"fa fa-angle-down fa-2x\"></i></a>";
$up = "<a href='?sort_order={$qs[$i]['sort_order']}&sort=up' data-toggle=\"tooltip\" title=\"" . T_("Push step Up") . "\"><i class=\"fa fa-angle-up fa-2x\"></i></a>";
if ($i == 0) {
$qs[$i]['sort_order'] = "<div>   <span class=\"badge\">" . $qs[$i]['sort_order'] . "</span> " . $down . "</div>";
} else {
if ($i == $co - 1) {
$qs[$i]['sort_order'] = "<div style=\"min-width:5em;\"> " . $up . " <span class=\"badge\">" . $qs[$i]['sort_order'] . "</span>";
} else {
$qs[$i]['sort_order'] = "<div> " . $up . " <span class=\"badge\">" . $qs[$i]['sort_order'] . "</span> " . $down . "</div>";
}
}
}
} else {
$qs[0]['sort_order'] = " <i class=\"fa fa-minus fa-lg\"></i> ";
}
xhtml_table($qs, array("sort_order", "qdesc", "description", "call_max", "call_attempt_max", "answering_machine_messages", "random_select", "edit"), array(T_("Sort order"), T_("Questionnaire"), T_("Sample"), T_("Max calls"), T_("Max call attempts"), T_("Answering machine messages"), T_("Selection type"), T_("Edit")));
} else {
print "<div class='alert text-danger'><h4>" . T_("No samples assigned to questionnaires") . "</h4></div>";
}
print "</div>";
xhtml_foot($js_foot);
?>
<script type="text/javascript">
$('[data-toggle="confirmation"]').confirmation()
</script>
$column_group_id = 0;
if (isset($_GET['column_group_id'])) {
$column_group_id = intval($_GET['column_group_id']);
}
//List column_groups assigned to this data file
$sql = "SELECT cg.column_group_id as value, cg.description, CASE WHEN cg.column_group_id = '{$column_group_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\t\tFROM `column_group` as cg, work as w, `column` as c, `column` as c2\r\n\t\t\tWHERE c.column_id = '{$column_id}'\r\n\t\t\tAND c2.data_id = c.data_id\r\n\t\t\tAND c2.column_id = w.column_id\r\n\t\t\tAND w.column_group_id = cg.column_group_id\r\n\t\t\tGROUP BY cg.column_group_id";
print "<div>" . T_("Select column group to code to (work must already have been created): ");
$c = $db->GetAll($sql);
translate_array($c, array("description"));
display_chooser($c, 'column_group_id', 'column_group_id', true, "data_id={$data_id}&column_id={$column_id}");
print "</div>";
if ($column_group_id != 0) {
//Display columns selected for this data file and process
$sql = "SELECT ckg.description as pdes, c.description as cdes, CONCAT('<a href=\\'?data_id={$data_id}&remove=remove&column_code_keyword_id=',cck.column_code_keyword_id,'\\'>" . T_("Remove") . "</a>') as link\r\n\t\t\t\tFROM code_keyword_group as ckg, `column` as c, column_code_keyword as cck\r\n\t\t\t\tWHERE cck.column_group_id = '{$column_group_id}'\r\n\t\t\t\tAND cck.column_id = c.column_id\r\n\t\t\t\tAND ckg.code_keyword_group_id = cck.code_keyword_group_id";
$rs = $db->GetAll($sql);
xhtml_table($rs, array('pdes', 'cdes', 'link'), array(T_("Keyword group"), T_("Column"), T_("Remove")));
print "<div>" . T_("Select keyword group to add") . "</div>";
print "<form action='' method='post'><div><input type='hidden' name='data_id' value='{$data_id}'/><input type='hidden' name='column_group_id' value='{$column_group_id}'/><input type='hidden' name='column_id' value='{$column_id}'/></div>";
//display a dropdown of all code keyword groups relevant
$sql = "SELECT ckg.code_keyword_group_id as value,ckg.description, '' AS selected\r\n\t\t\t\tFROM `code_keyword_group` as ckg, column_group as cg\r\n\t\t\t\tWHERE ckg.code_group_id = cg.code_group_id\r\n\t\t\t\tAND cg.column_group_id = '{$column_group_id}'";
$rs = $db->GetAll($sql);
if (!empty($rs)) {
display_chooser($rs, 'code_keyword_group_id', 'code_keyword_group_id', true, false, false);
print "<div><input type='submit' name='submit' value='" . T_("Add keyword group") . "'/></div></form>";
} else {
print "<div>" . T_("No code keyword group available") . "</div></form>";
}
}
}
}
xhtml_foot();
if (isset($_POST['submit'])) {
//submitted, now update database
foreach ($_POST as $key => $val) {
if (substr($key, 0, 4) == 'pfid') {
$key = intval(substr($key, 4));
$val = intval($val);
$sql = "UPDATE processforms\n\t\t\t\tSET allowanother = '{$val}'\n\t\t\t\tWHERE pfid = '{$key}'";
$db->Execute($sql);
}
}
}
xhtml_head(T_("Listing of imported files by status"), true, array("../css/table.css"));
$status = 1;
if (isset($_GET['status'])) {
$status = intval($_GET['status']);
}
if (isset($_POST['status'])) {
$status = intval($_POST['status']);
}
if ($status == 1) {
print "<h1>" . T_("Forms successfully imported") . "</h1>";
}
if ($status == 2) {
print "<h1>" . T_("Forms not imported") . "</h1>";
}
$sql = "SELECT pfid,filepath,filehash,date,status, CONCAT('<input type=\\'radio\\' value=\\'1\\' name=\\'pfid', pfid, '\\' ', CASE WHEN allowanother = '1' THEN 'checked=\\'checked\\'' ELSE '' END, '/> Yes <input type=\\'radio\\' value=\\'0\\' name=\\'pfid', pfid, '\\' ', CASE WHEN allowanother = '0' THEN 'checked=\\'checked\\'' ELSE '' END, '/> No') as allowanother\n\tFROM processforms\n\tWHERE status = {$status}\n\tORDER BY date ASC";
$fs = $db->GetAll($sql);
print "<form method='post' action=''>";
xhtml_table($fs, array('filepath', 'filehash', 'date', 'allowanother'), array(T_('File'), T_('SHA1'), T_('Date'), T_('Allow import again?')));
print "<p><input name='status' type='hidden' id='status' value='{$status}'/><input name='submit' type='submit' value='" . T_("Save changes") . "'/></p></form>";
xhtml_foot();
end_process($p);
}
print "<h1>" . T_("Process") . " {$p}</h1>";
if (is_process_killed($p)) {
print "<h3>" . T_("Kill signal sent: Please wait...") . "</h3>";
print "<p><a href='?force_kill'>" . T_("Mark the proces as killed (i.e. when the server is rebooted)") . "</a></p>";
} else {
print "<p><a href='?kill=kill'>" . T_("Kill the running process") . "</a> (" . T_("may take up to a few minutes to take effect") . ")</p>";
}
$d = process_get_data($p);
if ($d !== false) {
xhtml_table($d, array('process_log_id', 'datetime', 'data'), array(T_("Log id"), T_("Date"), T_("Log entry")));
}
} else {
global $db;
$sql = "SELECT process_id\n\t\tFROM process\n\t\tWHERE stop IS NULL\n\t\tAND type = 2";
$rs = $db->GetAll($sql);
if (!empty($rs)) {
foreach ($rs as $r) {
print "<p><a href='?p={$r['process_id']}'>" . T_("Process") . " {$r['process_id']} " . T_("running...") . "</a></p>";
}
} else {
print "<h2>" . T_("No process running") . "</h2>";
}
print "<h2>" . T_("Outcome of last process run (if any)") . "</h2>";
$d = process_get_last_data(2);
if ($d !== false) {
xhtml_table($d, array('process_log_id', 'datetime', 'data'), array(T_("Log id"), T_("Date"), T_("Log entry")));
}
}
xhtml_foot();
$work_id = intval($_GET['del']);
$db->StartTrans();
//Delete all work that is dependend on this, including this:
delete_work($work_id);
$db->CompleteTrans();
}
xhtml_head(T_("Work remaining"), true, array("../css/table.css"), array("../js/display.js"));
$data_id = 0;
if (isset($_GET['data_id'])) {
$data_id = intval($_GET['data_id']);
}
//Select a data file
$sql = "SELECT data_id as value,description, CASE WHEN data_id = '{$data_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\tFROM data";
print "<div>" . T_("Select data file: ");
display_chooser($db->GetAll($sql), 'data_id', 'data_id');
print "</div>";
//List work to do for this data_id
$sql = "SELECT count(*) as count, d.description as datad, p.description as processd, c.name, o.description as odes, CONCAT('<a href=\\'?del=',w.work_id,'\\'>" . T_("Delete") . "</a>') as dele\r\n\tFROM `work` AS w\r\n\tLEFT JOIN work_parent AS wp ON ( wp.work_id = w.work_id )\r\n\tJOIN `process` AS p ON ( p.process_id = w.process_id )\r\n\tJOIN `column` AS c ON ( c.column_id = w.column_id )\r\n\tJOIN `data` AS d ON ( d.data_id = c.data_id)\r\n\tJOIN cell AS ce ON ( ce.column_id = w.column_id )\r\n\tLEFT JOIN work_unit AS wu2 ON ( wu2.cell_id = ce.cell_id AND wu2.work_id = wp.parent_work_id AND wu2.completed IS NOT NULL )\r\n\tLEFT JOIN work_unit AS wu ON ( wu.cell_id = ce.cell_id AND wu.process_id = w.process_id AND w.work_id = wu.work_id )\r\n\tLEFT JOIN code_group AS cg ON ( cg.code_group_id = p.code_group_id )\r\n\tLEFT JOIN operator AS o ON (w.operator_id = o.operator_id)\r\n\tWHERE wu.cell_id IS NULL\r\n\tAND (wp.work_id IS NULL OR wu2.cell_id IS NOT NULL)";
if ($data_id != 0) {
$sql .= " AND c.data_id = '{$data_id}' ";
}
$sql .= " GROUP BY c.data_id,p.process_id,w.work_id ";
$rs = $db->GetAll($sql);
print "<div>" . T_("Work remaining") . "</div>";
if (empty($rs)) {
print "<p>" . T_("No work remaining") . " <a href='markcolumns.php?data_id={$data_id}'>" . T_("Create work") . "</a></p>";
} else {
translate_array($rs, array("processd"));
xhtml_table($rs, array('count', 'name', 'datad', 'processd', 'odes', 'dele'), array(T_("Rows to do"), T_("Column"), T_("Data file"), T_("Process to apply"), T_("For specific operator?"), T_("Delete work")));
}
xhtml_foot();
include "functions/functions.operator.php";
$js = false;
if (AUTO_LOGOUT_MINUTES !== false) {
$js = array("include/jquery/jquery-1.4.2.min.js", "js/childnap.js");
}
xhtml_head(T_("Shift List"), false, array("css/table.css"), $js, false, 600);
//List the shifts
// display in operator time
$operator_id = get_operator_id();
$case_id = get_case_id($operator_id);
if ($case_id) {
global $db;
$sql = "SELECT DATE_FORMAT(CONVERT_TZ(c.start,'UTC',op.Time_zone_name),'" . DATE_TIME_FORMAT . "') as start,DATE_FORMAT(CONVERT_TZ(c.end,'UTC',op.Time_zone_name),'" . TIME_FORMAT . "') as end\r\n\t\tFROM `shift` as c, `case` as ca, `operator` as op\r\n\t\tWHERE ca.case_id = '{$case_id}'\r\n\t\tAND op.operator_id = '{$operator_id}'\r\n\t\tAND c.questionnaire_id = ca.questionnaire_id\r\n\t\tAND c.end >= CONVERT_TZ(NOW(),'System','UTC')\r\n\t\tORDER BY c.start ASC";
$rs = $db->GetAll($sql);
if (empty($rs)) {
print "<p>" . T_("No shifts for this project") . "</p>";
} else {
xhtml_table($rs, array("start", "end"), array(T_("Start"), T_("End")));
}
} else {
//no case so show all shifts for all projects that I am assigned to
global $db;
$sql = "SELECT q.description,DATE_FORMAT(CONVERT_TZ(c.start,'UTC',op.Time_zone_name),'" . DATE_TIME_FORMAT . "') as start,DATE_FORMAT(CONVERT_TZ(c.end,'UTC',op.Time_zone_name),'" . TIME_FORMAT . "') as end\r\n\t\tFROM `shift` as c, `operator` as op, `operator_questionnaire` as oq, `questionnaire` as q\r\n\t\tWHERE op.operator_id = '{$operator_id}'\r\n\t\tAND op.operator_id = oq.operator_id\r\n\t\tAND oq.questionnaire_id = c.questionnaire_id\r\n\t\tAND q.questionnaire_id = oq.questionnaire_id\r\n\t\tAND c.end >= CONVERT_TZ(NOW(),'System','UTC')\r\n\t\tORDER BY c.start ASC";
$rs = $db->GetAll($sql);
if (empty($rs)) {
print "<p>" . T_("No future shifts scheduled") . "</p>";
} else {
xhtml_table($rs, array("description", "start", "end"), array(T_("Questionnaire"), T_("Start"), T_("End")));
}
}
xhtml_foot();
}
}
}
if ($display) {
$sql = "SELECT operator_id,\r\n CONCAT(firstName, ' ', lastName) as name, \r\n\tCONCAT ('<a href=\\'extensionstatus.php?edit=',e.extension_id,'\\'>', e.extension ,'</a>') as `extension`,\r\n\tCONCAT('<a href=\\'?winbat=winbat&operator_id=',operator_id,'\\'>" . TQ_("Win .bat file") . "</a>') as winbat,\r\n\tCONCAT('<a href=\\'?sh=sh&operator_id=',operator_id,'\\'>" . TQ_("*nix script file") . "</a>') as sh,\r\n\tCASE WHEN enabled = 0 THEN\r\n\t\tCONCAT(' <a href=\\'?enable=',operator_id,'\\'><i data-toggle=\\'tooltip\\' title=\\'" . TQ_("Enable") . "\\' class=\\'fa fa-toggle-off fa-2x\\' style=\\'color:grey;\\'></i></a> ') \r\n\tELSE\r\n\t\tCONCAT(' <a href=\\'?disable=',operator_id,'\\'><i data-toggle=\\'tooltip\\' title=\\'" . TQ_("Disable") . "\\' class=\\'fa fa-toggle-on fa-2x\\'></i></a> ')\r\n\tEND as enabledisable,\r\n\tCASE WHEN voip = 0 THEN\r\n\t\tCONCAT('<a href=\\'?voipenable=',operator_id,'\\'>" . TQ_("Enable VoIP") . "</a>') \r\n\tELSE\r\n\t\tCONCAT('<a href=\\'?voipdisable=',operator_id,'\\'>" . TQ_("Disable VoIP") . "</a>') \r\n\tEND as voipenabledisable,\r\n\tCONCAT(' <a href=\\'?edit=',operator_id,'\\'><i data-toggle=\\'tooltip\\' title=\\'" . TQ_("Edit") . "\\' class=\\'fa fa-pencil-square-o fa-lg\\'></i></a> ') as edit, username\r\n FROM operator\r\n LEFT JOIN `extension` as e ON (e.current_operator_id = operator_id)";
$rs = $db->GetAll($sql);
xhtml_head(T_("Operator list"), true, array("../include/bootstrap/css/bootstrap.min.css", "../include/font-awesome/css/font-awesome.css", "../css/custom.css"));
$columns = array("operator_id", "name", "username", "extension", "enabledisable", "edit");
$titles = array("ID", T_("Operator"), T_("Username"), T_("Extension"), " <i class='fa fa-lg fa-power-off '></i>", " <i class='fa fa-lg fa-pencil-square-o'></i>");
if (VOIP_ENABLED) {
$columns[] = "voipenabledisable";
$columns[] = "winbat";
$columns[] = "sh";
$titles[] = T_("VoIP ON/Off");
$titles[] = T_("Win file");
//Windows VoIP
$titles[] = T_("*nix flle");
//*nix VoIP
}
echo "<div class='col-lg-9'><div class='panel-body'>";
xhtml_table($rs, $columns, $titles);
echo "</div></div>";
echo "<div class='form-group col-lg-3'>\r\n\t\t\t<div class='panel-body'><a href='operators.php?add=add' class='btn btn-default btn-block'><i class='fa fa-lg fa-user-plus'></i> " . T_("Add an operator") . "</a></div>\r\n\t\t\t<div class='panel-body'><a href='extensionstatus.php' class='btn btn-default btn-block'><i class='fa fa-lg fa-whatsapp'></i> " . T_("Extensions") . "</a></div>\r\n\t\t\t<div class='panel-body'><a href='operatorquestionnaire.php' class='btn btn-default btn-block'><i class='fa fa-lg fa-link'></i> " . T_("Assign to questionnaire") . "</a></div>\r\n\t\t\t<div class='panel-body'><a href='operatorskill.php' class='btn btn-default btn-block'><i class='fa fa-lg fa-user-md'></i> " . T_("Operator skills") . "</a></div>\r\n\t\t\t<div class='panel-body'><a href='operatorperformance.php' class='btn btn-default btn-block'><i class='fa fa-lg fa-signal'></i> " . T_("Operator performance") . "</a></div>";
if (VOIP_ENABLED) {
print "<div class='well'>" . T_("Download the file for each user and save in the same folder as the voip.exe executable. When the file is executed, it will run the voip.exe program with the correct connection details to connect the operator to the VoIP server") . "</br></br>";
print "<a href='../voip/voipclient.exe' class='btn btn-default btn-block' title='" . T_("Download Windows VoIP Client Executable file") . "'><i class='fa fa-lg fa-download'></i> " . T_("Download Win file") . "</a></br>";
print "<a href='../voip/voipclient' class='btn btn-default btn-block' title='" . T_("Download Linux VoIP Executable file") . "'><i class='fa fa-lg fa-download'></i> " . T_("Download Linux file") . "</a></div>";
}
print "</div>";
xhtml_foot();
}
unset($datacol[5]);
unset($headers[5]);
}
print " <a href='?csv=csv";
if (isset($qid)) {
print "&questionnaire_id={$qid}";
}
if (isset($dq)) {
print "&dq=" . $dq;
}
if (isset($sid)) {
print "&sample_import_id={$sid}";
}
if (isset($ds)) {
print "&ds=" . $ds;
}
print "' class='btn btn-default pull-right'><i class='fa fa-download fa-lg text-primary'></i> " . T_("Download Call History List") . "</a>\r\n\t\t\t\t";
//<a href='../../admin/config.php' target='_blank' class='btn btn-default col-sm-offset-6 '><i class='fa fa-link fa-lg text-primary'></i> " . T_("Go to Call History Report") . "</a>
xhtml_table($rs, $datacol, $headers, "tclass", false, false, "bs-table");
}
}
}
} else {
print "<div class='alert alert-warning col-sm-6'>" . T_("No operator") . "</div>";
}
xhtml_foot($js_foot);
?>
<script type="text/javascript">
$('#bs-table').bdt();
</script>
print "<div class=col-sm-4><h2>" . T_("Call attempt time slots for sample") . "</h2>";
print "<div class='well'>" . T_("Assigning call attempt time slots to questionnaires will only allow cases to be attempted in a time slot for the n + 1th time where it has been attempted at least n times in all assigned timeslots. Please note timeslots must cover all possible time periods otherwise no cases will be available during missing timeslots.") . "</div>";
print "<h3 class='pull-left'>" . T_("Sample") . ": </h3>";
$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-control");
if ($sample_import_id !== false) {
$sql = "SELECT q.availability_group_id,a.description as description, CONCAT('<a href=\\'?sample_import_id={$sample_import_id}&questionnaire_id={$questionnaire_id}&qs_ravailability_group=', q.availability_group_id,'\\' >" . T_("Click to unassign") . "</a>') as link\r\n FROM questionnaire_sample_timeslot as q, availability_group as a\r\n WHERE q.availability_group_id = a.availability_group_id\r\n AND q.questionnaire_id = '{$questionnaire_id}'\r\n AND q.sample_import_id = '{$sample_import_id}'";
$qs = $db->GetAll($sql);
if (empty($qs)) {
print "<h4 class='alert text-danger'>" . T_("There are no call attempt time slots selected for this questionnaire sample") . "</h4>";
} else {
print "<h4>" . T_("Call attempt time slots selected for this sample") . ":</h4>";
xhtml_table($qs, array("availability_group_id", "description", "link"), false, "table table-hover");
}
$sql = "SELECT si.availability_group_id,si.description\r\n FROM availability_group as si\r\n LEFT JOIN questionnaire_sample_timeslot as q ON (q.sample_import_id = '{$sample_import_id}' AND q.questionnaire_id = '{$questionnaire_id}' AND q.availability_group_id = si.availability_group_id)\r\n WHERE q.questionnaire_id is NULL";
$qs = $db->GetAll($sql);
if (!empty($qs)) {
print "<div class='panel-body'>";
//print "<h3>" . T_("Add a call attempt time slot to this questionnaire sample:") . "</h3>";
print "<form action='' method='get'><div class='pull-left'><select class='form-control ' name='qs_availability_group' id='qs_availability_group'>";
foreach ($qs as $q) {
print "<option value=\"{$q['availability_group_id']}\">{$q['description']}</option>";
}
print "</select></div>\r\n <input type='hidden' name='questionnaire_id' value='{$questionnaire_id}'/>\r\n <input type='hidden' name='sample_import_id' value='{$sample_import_id}'/>\r\n  <input type='submit' name='add_qs_availability' class='btn btn-default' value='" . T_("Add call attempt time slot for sample") . "'/>\r\n </form></div>";
}
}
print "</div>";
}
<?php
} else {
print "</p>";
}
print "</form>";
}
print "</div></div>";
} else {
$sql = "SELECT questionnaire_sample_quota_row_id,qsq.description,\r\n CONCAT(' <a href=\\'?edit=edit&qsqri=',questionnaire_sample_quota_row_id,'\\'><i class=\"fa fa-pencil-square-o fa-lg text-danger\"></i></a> ') as qedit,\r\n CONCAT('<div class=\\'text-center\\'><input type=\\'checkbox\\' name=\\'select_',questionnaire_sample_quota_row_id,'\\'/></div>') as qselect,\r\n qsq.completions,qsq.quota_reached,qsq.current_completions,\r\n CASE WHEN qsq.autoprioritise = 1 THEN '" . TQ_("Yes") . "' ELSE '" . TQ_("No") . "' END AS ap, qsq.priority,\r\n CASE WHEN qsq.quota_reached = 1 THEN '" . TQ_("closed") . "' ELSE '" . TQ_("open") . "' END AS status\r\n FROM questionnaire_sample_quota_row as qsq, questionnaire as q\r\n \t\t\tWHERE qsq.questionnaire_id = '{$questionnaire_id}'\r\n \t\t\tAND qsq.sample_import_id = '{$sample_import_id}'\r\n \t\t\tAND q.questionnaire_id = '{$questionnaire_id}'";
$r = $db->GetAll($sql);
if (empty($r)) {
print "<p class='well text-info col-lg-12'>" . T_("Currently no row quotas") . "</p>";
} else {
print "<form method='post' action='?questionnaire_id={$questionnaire_id}&sample_import_id={$sample_import_id}'>";
print "<div class='col-sm-12 panel-body'></br><h2>" . T_("Current row quotas") . "</h2><div class='pull-left'>";
xhtml_table($r, array('description', 'qedit', 'completions', 'current_completions', 'status', 'priority', 'ap', 'qselect'), array(T_("Description"), " <i class='fa fa-pencil-square-o fa-lg' data-toggle='tooltip' title='" . T_("Edit") . "'></i> ", T_("Quota"), T_("Completions"), T_("Status"), T_("Priority"), T_("Auto prioritise"), " <i class='fa fa-check-square-o fa-lg' data-toggle='tooltip' title='" . T_("Select") . "'></i> "));
print "</div><div class='pull-left'></br>";
print "<button class='btn btn-default col-sm-offset-2' type='submit' name='submitexport'><i class=\"fa fa-download fa-lg text-primary\"></i> " . TQ_("Export selected") . "</button></br></br>";
print "<button class='btn btn-default col-sm-offset-2' type='submit' name='submitdelete'><i class=\"fa fa-trash-o fa-lg text-danger\"></i> \r\n" . TQ_("Delete selected") . "</button></div></div>";
print "</form>";
}
print "<div class='col-sm-8 panel-body'><h2>" . T_("Add row quota") . "</h2>";
?>
<form action="?<?php
echo "questionnaire_id={$questionnaire_id}&sample_import_id={$sample_import_id}";
?>
" method="post" class="form-inline table">
<p><label for="description" class="control-label"><?php
echo T_("Describe this quota");
?>
:  </label><input type="text" class="form-control" name="description" id="description" required size="70"/></p>
" />
<?php
} else {
print "</br></br><b class='well text-danger'>" . T_("Unassign the operator from this extension to be able to delete it") . "</b>";
}
}
print "</div></form></div>";
} else {
$sql = "SELECT CONCAT('<a href=\\'operatorlist.php?edit=',o.operator_id,'\\'>',o.firstName,' ', o.lastname,'</a>') as firstName,\r\n CONCAT('<a href=\\'?edit=',e.extension_id,'\\' class=\\'\\'>',e.extension,'</a>') as extension,\r\n IF(c.case_id IS NULL,IF(e.current_operator_id IS NULL,'list'\r\n ,CONCAT('<a href=\\'?unassign=',e.extension_id,'\\'>" . TQ_("Unassign") . "</a>')),'" . TQ_("End case to change assignment") . "') as assignment, \r\n CASE e.status WHEN 0 THEN '" . TQ_("VoIP Offline") . "' ELSE '" . TQ_("VoIP Online") . "' END as status, \r\n CASE ca.state WHEN 0 THEN '" . TQ_("Not called") . "' WHEN 1 THEN '" . TQ_("Requesting call") . "' WHEN 2 THEN '" . TQ_("Ringing") . "' WHEN 3 THEN '" . TQ_("Answered") . "' WHEN 4 THEN '" . TQ_("Requires coding") . "' ELSE '" . TQ_("Done") . "' END as state,\r\n CONCAT('<a href=\\'supervisor.php?case_id=', c.case_id , '\\'>' , c.case_id, '</a>') as case_id, SEC_TO_TIME(TIMESTAMPDIFF(SECOND,cal.start,CONVERT_TZ(NOW(),'SYSTEM','UTC'))) as calltime, \r\n e.status as vs,\r\n e.extension_id\r\n FROM extension as e\r\n LEFT JOIN `operator` as o ON (o.operator_id = e.current_operator_id)\r\n \tLEFT JOIN `case` as c ON (c.current_operator_id = o.operator_id)\r\n \tLEFT JOIN `call_attempt` as cal ON (cal.operator_id = o.operator_id AND cal.end IS NULL and cal.case_id = c.case_id)\r\n \tLEFT JOIN `call` as ca ON (ca.case_id = c.case_id AND ca.operator_id = o.operator_id AND ca.outcome_id= 0 AND ca.call_attempt_id = cal.call_attempt_id)\r\n \tORDER BY e.extension_id ASC";
$rs = $db->GetAll($sql);
print "<div class='panel-body'>";
if ($msg != "") {
print "<p class='alert alert-warning'>{$msg}</p></br>";
}
if (!empty($rs)) {
$sql = "SELECT o.operator_id as value, CONCAT(o.firstName,' ',o.lastname) as description\r\n FROM `operator` as o\r\n LEFT JOIN `extension` as e ON (e.current_operator_id = o.operator_id)\r\n WHERE e.extension_id IS NULL";
$ers = $db->GetAll($sql);
for ($i = 0; $i < count($rs); $i++) {
if ($rs[$i]['assignment'] == "list") {
$rs[$i]['assignment'] = display_chooser($ers, "operator_id_" . $rs[$i]["extension_id"], "operator_id_" . $rs[$i]["extension_id"], true, "extension_id=" . $rs[$i]["extension_id"], true, false, false, false);
}
}
xhtml_table($rs, array("extension", "firstName", "assignment", "status", "case_id", "state", "calltime"), array(T_("Extension"), T_("Operator"), T_("Assignment"), T_("VoIP Status"), T_("Case ID"), T_("Call state"), T_("Time on call")), "tclass", array("vs" => "1"));
print "</br>";
} else {
print "<p class='alert alert-warning'>" . T_("No extensions") . "</p>";
}
print "<a href='?addext=addext' class='btn btn-primary '>" . T_("Add extension") . "</a>\r\n\t\t</div>";
}
xhtml_foot();
/**
* Generate the sample call attempt report
*
* @param mixed $questionnaire_id The quesitonnaire, if specified
* @param string $sample_id The sample, if speified
* @param mixed $qsqri THe questionnaire sample quota row id, if specified
*
* @return false if empty otherwise true if table drawn
* @author Adam Zammit <*****@*****.**>
* @since 2012-10-02
*/
function sample_call_attempt_report($questionnaire_id = false, $sample_id = false, $qsqri = false)
{
global $db;
$q = "";
if ($questionnaire_id !== false && $questionnaire_id != -1) {
$q = "AND c.questionnaire_id = {$questionnaire_id}";
}
$s = "";
if ($sample_id !== false) {
$s = "JOIN sample as s ON (s.sample_id = c.sample_id AND s.import_id = '{$sample_id}')";
}
$qs = "";
if ($qsqri !== false) {
$sql2 = "SELECT exclude_val,exclude_var,exclude_var_id,comparison\r\n FROM qsqr_sample\r\n WHERE questionnaire_sample_quota_row_id = {$qsqri}";
$rev = $db->GetAll($sql2);
//reduce sample by every item in the qsqr_sample table
$x = 1;
foreach ($rev as $ev) {
$qs .= " JOIN sample_var as sv{$x} ON (sv{$x}.sample_id = c.sample_id AND sv{$x}.var_id = '{$ev['exclude_var_id']}' AND sv{$x}.val {$ev['comparison']} '{$ev['exclude_val']}') ";
$x++;
}
}
$sql = "SELECT ca1 AS callattempts, COUNT( ca1 ) AS sample\r\n\t\tFROM (\tSELECT count( ca.call_attempt_id ) AS ca1\r\n\t\t\tFROM call_attempt as ca\r\n\t\t\tJOIN `case` as c ON (c.case_id = ca.case_id {$q})\r\n\t\t\t{$s}\r\n\t\t\t{$qs}\r\n\t\t\tGROUP BY ca.case_id) AS t1\r\n\t\tGROUP BY ca1";
$overall = $db->GetAll($sql);
if (empty($overall)) {
return false;
}
$sql = "SELECT outcome_id,description \r\n\t\tFROM outcome";
$outcomes = $db->GetAssoc($sql);
$rep = array("callattempts", "sample");
$rept = array(T_("Call attempts made"), T_("Number of cases"));
$totals = array("sample");
$outcomesfilled = array();
foreach ($outcomes as $key => $val) {
$rep[] = $key;
$rept[] = T_($val);
$outcomesfilled[$key] = 0;
}
//Add breakdown by call attempt
for ($i = 0; $i < count($overall); $i++) {
//break down by outcome
$sql = "SELECT oi1, ca1 as callattempts, count(ca1) as sample\r\n\t\t\tFROM ( SELECT count( ca.call_attempt_id ) AS ca1, ca.case_id, c.current_outcome_id as oi1\r\n\t\t\t\tFROM call_attempt AS ca\r\n\t\t\t\tJOIN `case` AS c ON ( c.case_id = ca.case_id {$q})\r\n\t\t\t\t{$s}\r\n\t\t\t\t{$qs}\r\n\t\t\t\tGROUP BY ca.case_id\r\n\t\t\t\t) as t1\r\n\t\t\tGROUP BY ca1,oi1\r\n\t\t\tHAVING ca1 = {$overall[$i]['callattempts']}";
$byoutcome = $db->GetAssoc($sql);
foreach ($outcomes as $key => $val) {
$sample = 0;
if (isset($byoutcome[$key])) {
$outcomesfilled[$key] = 1;
$sample = $byoutcome[$key]['sample'];
}
$overall[$i][$key] = $sample;
}
}
//Remove columns that are empty
$count = 2;
foreach ($outcomesfilled as $key => $val) {
if ($val == 0) {
unset($rep[$count]);
unset($rept[$count]);
} else {
$totals[] = $key;
}
$count++;
}
xhtml_table($overall, $rep, $rept, "tclass", false, $totals);
print "</br>";
return true;
}
//xhtml_table($rs,array("firstName","completions","totalcalls","time","callt","CPH","CALLSPH","effectiveness"),array(T_("Operator"),T_("Completions"),T_("Calls"),T_("Total time"),T_("Call time"),T_("Completions p/h"),T_("Calls p/h"),T_("Effectiveness")));
$questionnaire_id = false;
print "<h3 class='form-inline pull-left'>" . T_("Please select a questionnaire") . " </h3>";
if (isset($_GET['questionnaire_id'])) {
$questionnaire_id = bigintval($_GET['questionnaire_id']);
}
display_questionnaire_chooser($questionnaire_id, false, "form-inline clearfix", "form-control");
if ($questionnaire_id) {
$rs = get_stats_total(get_stats_by_questionnaire($questionnaire_id));
print "<h2>" . T_("This project") . "</h2>";
xhtml_table($rs, array("firstName", "completions", "totalcalls", "time", "callt", "CPH", "CALLSPH", "effectiveness"), array(T_("Operator"), T_("Completions"), T_("Calls"), T_("Total time"), T_("Call time"), T_("Completions p/h"), T_("Calls p/h"), T_("Effectiveness")));
$operator_id = get_operator_id();
$shift_id = false;
if (isset($_GET['shift_id'])) {
$shift_id = bigintval($_GET['shift_id']);
}
$sql = "SELECT s.shift_id as value,CONCAT(DATE_FORMAT(CONVERT_TZ(s.start,'UTC',o.Time_zone_name),'" . DATE_TIME_FORMAT . "'),' " . TQ_("till") . " ',DATE_FORMAT(CONVERT_TZ(s.end,'UTC',o.Time_zone_name),'" . TIME_FORMAT . "')) as description,CASE WHEN s.shift_id = '{$shift_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\tFROM shift as s\r\n\t\tLEFT JOIN (operator as o) on (o.operator_id = '{$operator_id}')\r\n\t\tWHERE s.questionnaire_id = '{$questionnaire_id}'\r\n\t\tORDER BY s.start ASC";
$rs = $db->GetAll($sql);
print "</br><h3 class='form-inline pull-left'>" . T_("Please select a shift") . " </h3>";
display_chooser($rs, "shift_id", "shift_id", true, "questionnaire_id={$questionnaire_id}", true, true, false, true, "form-inline form-group");
//,false,true,false,true,"pull-left"
if ($shift_id) {
$rs = get_stats_total(get_stats_by_shift($questionnaire_id, $shift_id));
print "<h2>" . T_("This shift") . "</h2>";
xhtml_table($rs, array("firstName", "completions", "totalcalls", "time", "callt", "CPH", "CALLSPH", "effectiveness"), array(T_("Operator"), T_("Completions"), T_("Calls"), T_("Total time"), T_("Call time"), T_("Completions p/h"), T_("Calls p/h"), T_("Effectiveness")));
}
}
xhtml_foot();
?>
