/**
* 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;
}
}
//Select code group
$sql = "SELECT code_group_id as value,description, CASE WHEN code_group_id = '{$code_group_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\tFROM code_group";
print "<div>" . T_("Select code group: ");
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("description"));
display_chooser($rs2, 'code_group_id', 'code_group_id');
print "</div>";
if ($code_group_id != 0) {
print "<form action='' method='post'><div><input type='hidden' name='code_group_id' value='{$code_group_id}'/></div>";
//Parent process (if any)
$sql = "SELECT process_id as value, description, '' AS selected\r\n\t\tFROM process";
print "<div>" . T_("Select parent process (if any): ");
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("description"));
display_chooser($rs2, 'process_id', 'process_id', true, false, false);
print "</div>";
print "<div><input type='checkbox' name='autolabel' id='autolabel'/><label for='autolabel'>" . T_("Automatically assign a code if the code label exactly matches the data?") . "</label></div>";
print "<div><input type='checkbox' name='autovalue' id='autovalue'/><label for='autovalue'>" . T_("Automatically assign a code if the code value exactly matches the data?") . "</label></div>";
print "<div><input type='checkbox' name='autokeyword' id='autokeyword'/><label for='autokeyword'>" . T_("Automatically assign a code if there is a matching code keyword in the database?") . "</label></div>";
print "<div><input type='checkbox' name='template' id='template'/><label for='template'>" . T_("Use the code group as a template? (Create a new, editable code group for each work unit that this is assigned to)") . "</label></div>";
print "<div><input type='checkbox' name='exclusive' id='exclusive'/><label for='exclusive'>" . T_("Should this process only be run by operator(s) that have not worked on the prior process (exclusive)?") . "</label></div>";
print "<div>" . T_("Name for process using this code: ");
print "<input type='text' name='description'/>";
print "</div>";
print "<p><input type='submit' name='submit' value='" . T_("Create new process") . "'/></p></form>";
}
//List existing processes
$sql = "SELECT p.process_id,p.description as pdes,c.description as cdes\r\n\tFROM process as p\r\n\tLEFT JOIN code_group AS c ON (c.code_group_id = p.code_group_id)";
p(T_("Existing processes"), 'h2');
$rs2 = $db->GetAll($sql);
}
p(T_("The CSV file must have 2 fields: code,keywords"), "p");
p(T_("No fields may be blank"), "p");
p(T_("Do not include a header line as this will be imported as a code/keyword pair"), "p");
//Select a code group to export data from
$sql = "SELECT code_group_id as value,description, '' AS selected\r\n\tFROM code_group";
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("description"));
print "</div>";
?>
<form enctype="multipart/form-data" action="" method="post">
<p><input type="hidden" name="MAX_FILE_SIZE" value="1000000000" /></p>
<p><?php
echo T_("Choose the CSV code keywords file to upload:");
?>
<input name="datafile" type="file" /></p>
<p><?php
echo T_("Description for this code keywords file:");
?>
<input name="description" type="text" /></p>
<p><?php
echo T_("What coding scheme does this list apply to?");
display_chooser($rs2, 'code_group_id', 'code_group_id', false, false, false, false);
?>
<p><input type="submit" name="import_file" value="<?php
echo T_("Create new keyword code");
?>
"/></p>
</form>
<?php
xhtml_foot();
print "<h2>" . T_("Failed to load banding XML file") . "</h2>";
}
}
}
print "<h1>" . T_("Update banding from XML") . "</h1>";
print "<p>" . T_("WARNING: All previous banding will be erased") . "</p>";
$sql = "SELECT description,qid as value, '' AS selected\r\n\tFROM questionnaires";
$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 banding XML file");
?>
: <input name="bandingxml" type="file" /></p>
<p><?php
echo T_("Select questionnaire");
?>
: <?php
display_chooser($rs, 'qid', 'qid', true, false, false, false, false);
?>
<br/></p>
<p><input type="submit" value="<?php
echo T_("Upload XML");
?>
" /></p>
</form>
<?php
xhtml_foot();
$code_id = "NULL";
if (!empty($_POST['code_id'])) {
$code_id = intval($_POST['code_id']);
}
$sql = "UPDATE code_group\r\n\t\tSET blank_code_id = {$code_id}\r\n\t\tWHERE code_group_id = {$code_group_id}";
$db->Execute($sql);
}
xhtml_head(T_("Select blank code"), true, array("../css/table.css"), array("../js/display.js"));
$code_group_id = 0;
if (isset($_GET['code_group_id'])) {
$code_group_id = intval($_GET['code_group_id']);
}
//Select code group
$sql = "SELECT code_group_id as value,description, CASE WHEN code_group_id = '{$code_group_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\tFROM code_group";
print "<div>" . T_("Select code group: ");
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("description"));
display_chooser($rs2, 'code_group_id', 'code_group_id');
print "</div>";
if ($code_group_id != 0) {
//List codes for the first level of this code groups (include null)
$sql = "SELECT c.code_id as value, c.label as description, CASE WHEN c.code_id = cg.blank_code_id THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\tFROM `code` as c, code_level as cl, code_group as cg\r\n\t\tWHERE cl.code_group_id = '{$code_group_id}'\r\n\t\tAND cg.code_group_id = '{$code_group_id}'\r\n\t\tAND cl.level = 0\r\n\t\tAND c.code_level_id = cl.code_level_id";
print "<form action='' method='post'><div><input type='hidden' name='code_group_id' value='{$code_group_id}'/></div>";
print "<div>" . T_("Select code: ");
$c = $db->GetAll($sql);
translate_array($c, array("description"));
display_chooser($c, 'code_id', 'code_id', true, false, false);
print "</div>";
print "<p><input type='submit' name='submit' value='" . T_("Assign as blank code") . "'/></p></form>";
}
xhtml_foot();
translate_array($daysofweek, array("description"));
?>
<div class="panel-body col-sm-4"><form method="post" action="" class="form-horizontal">
<table class="table-hover table-condensed " id="shifts"><thead class="text-center highlight">
<?php
print "<tr ><th >" . T_("Day") . "</th><th >" . T_("Start") . "</th><th >" . T_("End") . "</th></tr></thead><tbody>";
$count = 0;
foreach ($shifts as $shift) {
print "<tr id='row-{$count}'><td>";
// class='row_to_clone' /* these are not the rows to clone...*/
display_chooser($daysofweek, "day[{$count}]", false, true, false, false, false, array("description", $shift['dt']));
print "</td>\r\n\t\t<td><div class=\"input-group clockpicker\"><input readonly class=\"form-control\" size=\"8\" maxlength=\"8\" name=\"start[{$count}]\" type=\"text\" value=\"{$shift['start']}\"/><span class=\"input-group-addon\"><span class=\"glyphicon glyphicon-time fa\"></span></span></div></td>\r\n\t\t<td><div class=\"input-group clockpicker\"><input readonly class=\"form-control\" size=\"8\" maxlength=\"8\" name=\"end[{$count}]\" type=\"text\" value=\"{$shift['end']}\"/><span class=\"input-group-addon\"><span class=\"glyphicon glyphicon-time fa\"></span></span></div></td></tr>";
$count++;
}
print "<tr class='row_to_clone' id='row-{$count}'><td>";
display_chooser($daysofweek, "day[{$count}]", false, true, false, false, false, false);
print "\t</td><td><div class=\"input-group clockpicker\"><input readonly class=\"form-control\" size=\"8\" maxlength=\"8\" name=\"start[{$count}]\" type=\"text\" value=\"08:00:00\"/><span class=\"input-group-addon\"><span class=\"glyphicon glyphicon-time fa\"></span></span></div></td>\r\n\t\t\t<td><div class=\"input-group clockpicker\"><input readonly class=\"form-control\" size=\"8\" maxlength=\"8\" name=\"end[{$count}]\" type=\"text\" value=\"20:00:00\"/><span class=\"input-group-addon\"><span class=\"glyphicon glyphicon-time fa\"></span></span></div></td></tr>";
?>
</tbody></table>
<a class="btn btn-default btn-sm" onclick="addRow(); return false;" href=""><?php
echo T_("Add row");
?>
</a><br/><br/>
<input class="btn btn-default " type="submit" name="submit" value="<?php
echo T_("Save changes to shifts");
?>
"/>
</form></div>
<?php
xhtml_foot($js_foot);
?>
}
?>
value="1" /></div>
</div>
<div id="usesvoip" style="display:none" >
<div class="form-group">
<label class="col-lg-3 control-label"><?php
echo T_("Extension") . ": ";
?>
</label>
<?php
if (!empty($ers)) {
?>
<div class="col-lg-3"><?php
display_chooser($ers, "extension_id", "extension_id", true, false, false, true, false, true, "form-inline");
?>
</div>
<div class="col-lg-6 form-inline"> <?php
echo T_("Edit") . " ";
} else {
?>
<div class="col-lg-6 form-inline"> <?php
echo T_("Add") . " ";
}
?>
<a href='extensionstatus.php' class="btn btn-default fa"><?php
echo T_("Extensions");
?>
</a>
</div>
}
//Select a data file to display
$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>";
if ($data_id != 0) {
$column_id = 0;
if (isset($_GET['column_id'])) {
$column_id = intval($_GET['column_id']);
}
//Select variable (column)
$sql = "SELECT column_id as value, name as description, CASE WHEN column_id = '{$column_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\tFROM `column`\r\n\t\tWHERE data_id = '{$data_id}'";
print "<div>" . T_("Select variable: ");
$c = $db->GetAll($sql);
display_chooser($c, 'column_id', 'column_id', true, "data_id={$data_id}");
print "</div>";
$sql = "SELECT column_id,name,code_level_id\r\n\t\tFROM `column` \r\n\t\tWHERE data_id = '{$data_id}'";
if ($column_id != 0) {
$sql .= " AND column_id = '{$column_id}'";
$cols = $db->GetAll($sql);
$sql = "SELECT c.row_id\r\n\t\tFROM cell as c\r\n\t\tWHERE c.column_id = {$cols[0]['column_id']}\r\n\t\tGROUP BY c.row_id";
$rows = $db->GetAll($sql);
print "<table class='tclass'>";
print "<tr>";
foreach ($cols as $c) {
print "<th>" . $c['name'] . "</th>";
}
print "</tr>";
foreach ($rows as $r) {
print "<tr>";
display_chooser($rs2, 'process_id', 'process_id', true, "data_id={$data_id}&column_id={$column_id}");
if ($process_id != 0) {
print "<div>" . T_("Select operator(s) to apply this process") . "</div>";
print "<form action='' method='post'><div><input type='hidden' name='column_id' value='{$column_id}'/><input type='hidden' name='data_id' value='{$data_id}'/><input type='hidden' name='process_id' value='{$process_id}'/></div>";
//display a checkbox of all operators
$sql = "SELECT o.operator_id,o.description, CONCAT('<input type=\\'checkbox\\' name=\\'oid', o.operator_id, '\\' value=\\'', o.operator_id, '\\'/>') as cbox\r\n\t\t\t\tFROM operator as o, operator_process as op, operator_data as od\r\n\t\t\t\tWHERE o.operator_id = op.operator_id \r\n\t\t\t\tAND op.process_id = '{$process_id}'\r\n\t\t\t\tAND od.operator_id = o.operator_id\r\n\t\t\t\tAND od.data_id = '{$data_id}'";
$rs = $db->GetAll($sql);
//Add an "any operator" field
$rs[] = array('description' => T_("Any operator: enter how many"), 'cbox' => "<input type='text' name='any' value='0'/>");
xhtml_table($rs, array('description', 'cbox'), array(T_("Operator"), T_("Select")));
//Add a reference column if applicable to this process
print "<div>" . T_("If a reference column is applicable, choose it below") . "</div>";
$sql = "SELECT column_id as value, name as description, '' AS selected\r\n\t\t\t\tFROM `column`\r\n\t\t\t\tWHERE data_id = '{$data_id}'";
display_chooser($db->GetAll($sql), 'reference', 'reference', true, false, false);
//Add a code group to select to generate multiple columns
print "<div>" . T_("To create a multiple choice coding scheme, select a code group to generate multiple groups from") . "</div>";
$sql = "SELECT code_group_id as value, description, '' AS selected\r\n\t\t\t\tFROM code_group";
display_chooser($db->GetAll($sql), 'multi_code_group_id', 'multi_code_group_id', true, false, false);
print "<div><input type='submit' name='submit' value='" . T_("Create work") . "'/></div>";
//List work already created for this data_id
$sql = "SELECT w.work_id,c.name,p.description, wp.parent_work_id, o.description as oname, CONCAT('<input type=\\'checkbox\\' name=\\'cid', w.work_id, '\\' value=\\'', w.work_id, '\\'/>') as cbox\r\n\t\t\r\n\t\t\t\tFROM work as w\r\n\t\t\t\tJOIN process as p ON (p.process_id = w.process_id)\r\n\t\t\t\tJOIN `column` as c ON (c.data_id = '{$data_id}' AND w.column_id = c.column_id)\r\n\t\t\t\tLEFT JOIN work_parent as wp on (wp.work_id = w.work_id)\r\n\t\t\t\tLEFT JOIN operator as o on (w.operator_id = o.operator_id)\r\n\t\t\t\tWHERE w.column_id = '{$column_id}'\r\n\t\t\t\tORDER BY w.work_id ASC";
$rs = $db->GetAll($sql);
translate_array($rs, array("description"));
p(T_("Current work for this data file and column"), "h2");
xhtml_table($rs, array('work_id', 'name', 'description', 'parent_work_id', 'oname', 'cbox'), array(T_("Work ID"), T_("Variable name"), T_("Process description"), T_("Parent job"), T_("Assigned operator"), T_("Create comparison work")));
print "<div><input type='submit' name='submit_compare' value='" . T_("Create comparison work") . "'/></div>";
print "</form>";
}
}
}
xhtml_foot();
$btid = $row['btid'];
$varname = $row['varname'];
$width = $row['width'];
//display the cropped boxes
print "<img src=\"showpage.php?bgid={$bgid}\"/>";
?>
<form method="post" action="<?php
echo $_SERVER['PHP_SELF'] . "?bgid={$bgid}";
?>
"><?php
//display group selection
$sql = "SELECT description,btid as value, CASE WHEN btid = '{$btid}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\tFROM boxgrouptypes";
$rs = $db->GetAll($sql);
print T_("Group type:");
translate_array($rs, array("description"));
display_chooser($rs, "btid", "btid", false, false, false, false, false);
//display variable name
?>
<br/><?php
echo T_("Variable name:");
?>
<input type="text" size="12" value="<?php
echo $varname;
?>
" name="varname"><br/><?php
//display width
echo T_("Width:");
?>
<input type="text" size="12" value="<?php
echo $width;
?>
$e = $db->GetOne($sql);
if (!empty($e)) {
header('Location: index.php');
die;
}
if (isset($_POST['extension_id']) && !empty($_POST['extension_id'])) {
if ($operator_id) {
$e = intval($_POST['extension_id']);
$sql = "UPDATE `extension`\r\n SET current_operator_id = {$operator_id}\r\n WHERE current_operator_id IS NULL\r\n AND extension_id = {$e}";
$r = $db->Execute($sql);
if ($r) {
header('Location: index.php');
die;
}
}
}
xhtml_head(T_("queXS"));
$sql = "SELECT e.extension_id as value, e.extension as description\r\n FROM `extension` as e\r\n WHERE e.current_operator_id IS NULL";
$ers = $db->GetAll($sql);
if (empty($ers)) {
print "<p>" . T_("There are no extensions available, please contact the supervisor or click below to try again for an available extension") . "</p>";
print "<p><a href='?'>" . T_("Try again") . "</a></p>";
} else {
print "<h2>" . T_("Select extension") . "</h2>";
print "<p>" . T_("Please select your extension from the list below then click on 'Choose extension'") . "</p>";
print "<form action='?' method='post'>";
print "<label for='extension_id'>" . T_("Extension") . ":</label>";
display_chooser($ers, "extension_id", "extension_id", false, false, false, false);
print "<p><input type='submit' value='" . T_("Choose extension") . "'/></p></form>";
}
xhtml_foot();
$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>";
if ($data_id != 0) {
$code_column = 0;
if (isset($_GET['code_column'])) {
$code_column = intval($_GET['code_column']);
}
$sql = "SELECT c.column_id as value, c.description, CASE WHEN column_id = '{$code_column}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\tFROM `column` as c\r\n\t\tWHERE c.data_id = '{$data_id}'\r\n\t\tAND code_level_id IS NOT NULL";
//select a column containing a code
print "<div>" . T_("Select column containing the code: ");
display_chooser($db->GetAll($sql), 'code_column', 'code_column', true, "data_id={$data_id}");
print "</div>";
if ($code_column != 0) {
//select a column containing the text
$text_column = 0;
if (isset($_GET['text_column'])) {
$text_column = intval($_GET['text_column']);
}
$sql = "SELECT c.column_id as value, c.description, CASE WHEN column_id = '{$text_column}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\t\tFROM `column` as c\r\n\t\t\tWHERE c.data_id = '{$data_id}'\r\n\t\t\tAND c.column_id != '{$code_column}'";
//select a column containing a code
print "<div>" . T_("Select column containing the keyword: ");
display_chooser($db->GetAll($sql), 'text_column', 'text_column', true, "data_id={$data_id}&code_column={$code_column}");
print "</div>";
if ($text_column != 0) {
print "<div><a href='?download=download&data_id={$data_id}&code_column={$code_column}&text_column={$text_column}'>" . T_("Download correspondence CSV") . "</a></div>";
}
}
}
xhtml_foot();
print "<div>" . T_("Select column: ");
$c = $db->GetAll($sql);
display_chooser($c, 'column_id', 'column_id', true, "data_id={$data_id}");
print "</div>";
if ($column_id != 0) {
$process_id = 0;
if (isset($_GET['process_id'])) {
$process_id = intval($_GET['process_id']);
}
//List processes assigned to this data file and column
$sql = "SELECT p.process_id as value, p.description, CASE WHEN p.process_id = '{$process_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\t\tFROM `process` as p, work as w, `column` as c\r\n\t\t\tWHERE w.column_id = '{$column_id}'\r\n\t\t\tAND w.process_id = p.process_id\r\n\t\t\tGROUP BY p.process_id";
print "<div>" . T_("Select process: ");
$c = $db->GetAll($sql);
translate_array($c, array("description"));
display_chooser($c, 'process_id', 'process_id', true, "data_id={$data_id}&column_id={$column_id}");
print "</div>";
if ($process_id != 0) {
//Display columns selected for this data file and process
$sql = "SELECT p.description as pdes, c.description as cdes, CONCAT('<a href=\\'?data_id={$data_id}&remove=remove&process_id=',cpc.process_id,'&column_id=',cpc.column_id,'&relevant_column_id=',cpc.relevant_column_id,'\\'>" . T_("Remove") . "</a>') as link\r\n\t\t\t\tFROM process as p, `column` as c, column_process_column as cpc\r\n\t\t\t\tWHERE cpc.process_id = '{$process_id}'\r\n\t\t\t\tAND cpc.column_id = '{$column_id}'\r\n\t\t\t\tAND cpc.relevant_column_id = c.column_id\r\n\t\t\t\tAND cpc.process_id = p.process_id";
$rs = $db->GetAll($sql);
xhtml_table($rs, array('pdes', 'cdes', 'link'), array(T_("Process"), T_("Related column"), T_("Remove")));
print "<div>" . T_("Select column to add") . "</div>";
print "<form action='' method='post'><div><input type='hidden' name='data_id' value='{$data_id}'/><input type='hidden' name='process_id' value='{$process_id}'/><input type='hidden' name='column_id' value='{$column_id}'/></div>";
//display a dropdown of all columns
$sql = "SELECT column_id as value,description, '' AS selected\r\n\t\t\t\tFROM `column`\r\n\t\t\t\tWHERE data_id = '{$data_id}'";
display_chooser($db->GetAll($sql), 'relevant_column_id', 'relevant_column_id', true, false, false);
print "<div><input type='submit' name='submit' value='" . T_("Add column") . "'/></div></form>";
}
}
}
xhtml_foot();
print "<div class='clearfix'></div></br><div class='alert alert-info col-lg-6 '>" . T_("ATTENTION! Keep in mind that you're setting 'Start' & 'End' appoinment times in RESPONDENT LOCAL TIME !!!") . "</div><div class='clearfix'></div>";
date_default_timezone_set($rtz);
print "<label class='text-right col-lg-2 control-label'>" . T_("Respondent TimeZone") . ":</label>\r\n\t\t\t\t\t<h4 class='col-lg-2 text-danger text-uppercase fa-lg'>" . $rtz . "</h4>\r\n\t\t\t\t\t<label class=''>" . T_("Respondent Time") . ": <b class='fa fa-2x '>" . date("H:i:s") . "</b></label>";
print "<br/><br/><label class='pull-left text-right control-label col-lg-2' for='start'>" . T_("Start time") . "</label>\r\n\t\t\t\t\t<div class='pull-left'><input class='form-control' type='text' value='{$start}' id='start' name='start'/></div>";
print "<br/><br/><label class='pull-left text-right control-label col-lg-2' for='end'>" . T_("End time") . "</label>\r\n\t\t\t\t\t<div class='pull-left'><input class='form-control' type='text' value='{$end}' id='end' name='end'/></div>";
print "<br/><br/><label class='pull-left text-right control-label col-lg-2' for='require_operator_id'>" . T_("Appointment with") . "</label>";
$ops = $db->GetAll("SELECT o.operator_id as value,\r\n\t\t\t\t\t\tCONCAT(o.firstName, ' ', o.lastName) as description,\r\n\t\t\t\t\t\tCASE WHEN o.operator_id = '{$require_operator_id}' THEN 'selected=\\'selected\\'' ELSE '' END as selected\r\n\t\t\t\t\t\tFROM operator as o");
$selected = "selected=\\'selected\\'";
foreach ($ops as $o) {
if (!empty($o['selected'])) {
$selected = "";
break;
}
}
array_unshift($ops, array('value' => 0, 'description' => T_("Any operator"), 'selected' => $selected));
display_chooser($ops, "require_operator_id", "require_operator_id", false, false, false, true, false, true, "pull-left");
print "\t<input type='hidden' value='{$appointment_id}' id='appointment_id' name='appointment_id'/>\r\n\t\t\t\t\t<input type='hidden' value='update' id='update' name='update'/>\r\n\t\t\t\t\t<input type='hidden' value='{$case_id}' id='case_id' name='case_id'/>";
if (isset($_GET['new']) && $_GET['new'] == 'new') {
print "<input type='hidden' value='create' id='new' name='new'/>";
}
print "<div class='clearfix'></div><br/><br/>\r\n\t\t\t\t<div class='col-lg-2'><a href='?' class='btn btn-default pull-left'><i class='fa fa-ban fa-lg'></i> " . T_("Cancel edit") . "</a></div>";
print "<div class='col-lg-2'>\r\n\t\t\t\t\t<button type='submit' class='btn btn-primary btn-block'><i class='fa fa-floppy-o fa-lg'></i> " . T_("Save changes") . "</button>\r\n\t\t\t\t\t</div>";
print "<div class='col-lg-2'><a href='' class='btn btn-default pull-right' toggle='confirmation' data-placement='left' data-href='?delete=delete&appointment_id={$appointment_id}&case_id={$case_id}' ><i class='fa fa-trash fa-lg text-danger'></i> " . T_("Delete this appointment") . "</a></div>";
print "</form>";
}
} else {
$operator_id = get_operator_id();
$subtitle = T_("Appointments");
xhtml_head(T_("Display Appointments"), true, $css, $js_head, false, 30);
print "<h3>" . T_("All appointments (with times displayed in your time zone)") . "</h3>";
$sql = "SELECT q.description, si.description as smpl, CONVERT_TZ(a.start,'UTC',@@session.time_zone) as start, CONVERT_TZ(a.end,'UTC',@@session.time_zone) as end,CONCAT(r.firstName, ' ', r.lastName) as resp, IFNULL(ou.description,'" . TQ_("Not yet called") . "') as outcome, oo.firstName as makerName, ooo.firstName as callerName, \r\n\tCONCAT('<a href=\\'supervisor.php?case_id=', c.case_id, '\\'>', c.case_id, '</a>') as case_id, \r\n\tCONCAT(' <a href=\\'\\'><i class=\\'fa fa-trash-o fa-lg text-danger\\' toggle=\\'confirmation\\' data-placement=\\'left\\' data-href=\\'?case_id=', c.case_id, '&appointment_id=', a.appointment_id, '&delete=delete\\' ></i></a> ') as link, \r\n\tCONCAT(' <a href=\\'?case_id=', c.case_id, '&appointment_id=', a.appointment_id, '\\'><i class=\\'fa fa-pencil-square-o fa-lg\\' ></i></a> ') as edit,IFNULL(ao.firstName,'" . TQ_("Any operator") . "') as witho \r\n\tFROM appointment as a \r\n\tJOIN (`case` as c, respondent as r, questionnaire as q, operator as oo, call_attempt as cc, `sample` as s, sample_import as si) on (c.sample_id = s.sample_id and a.case_id = c.case_id and a.respondent_id = r.respondent_id and q.questionnaire_id = c.questionnaire_id and a.call_attempt_id = cc.call_attempt_id and cc.operator_id = oo.operator_id and si.sample_import_id = s.import_id) \r\n\tLEFT JOIN (`call` as ca, outcome as ou, operator as ooo) ON (ca.call_id = a.completed_call_id and ou.outcome_id = ca.outcome_id and ca.operator_id = ooo.operator_id) \r\n\tLEFT JOIN operator AS ao ON ao.operator_id = a.require_operator_id \r\n\tLEFT JOIN (questionnaire_sample_quota as qsq) on (s.import_id = qsq.sample_import_id and c.questionnaire_id = qsq.questionnaire_id)\r\n\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\tWHERE q.enabled=1 AND si.enabled=1 AND a.end >= CONVERT_TZ(NOW(),'System','UTC') AND c.current_outcome_id IN (19,20,21,22)\r\n\tAND (qsq.quota_reached IS NULL OR qsq.quota_reached != 1)\r\n\tAND (qsqr.quota_reached IS NULL OR qsqr.quota_reached != 1)\r\n\tGROUP BY c.case_id ORDER BY a.start ASC";
$rs = $db->GetAll($sql);
print "<form action='?' method='post'>";
print "<div><input type='hidden' name='column_id' value='{$column_id}'/><input type='hidden' name='data_id' value='{$data_id}'/></div>";
foreach ($rs as $r) {
print "<div><input type='text' name='n{$r['column_id']}' id='n{$r['column_id']}' value=\"{$r['name']}\" size='5'/> \r\n\t<input type='text' name='d{$r['column_id']}' id='d{$r['column_id']}' value=\"{$r['description']}\" size='100'/></div>";
if (!empty($r['code_level_id'])) {
$old_code_level_id = 0;
$cli = $r['code_level_id'];
if (isset($_GET['old_code_level_id'])) {
$old_code_level_id = $_GET['old_code_level_id'];
if ($old_code_level_id != 0) {
$cli = $old_code_level_id;
}
}
$sql = "SELECT co.code_level_id as value, co.name as description, CASE WHEN co.code_level_id ='{$old_code_level_id}' THEN 'selected=\\'selected\\'' ELSE '' END as selected\r\n\t\t\t\t\tFROM `column` as co\r\n\t\t\t\t\tWHERE co.data_id = '{$data_id}' \r\n\t\t\t\t\tAND co.column_id < '{$column_id}'\r\n\t\t\t\t\tAND co.code_level_id IS NOT NULL\r\n\t\t\t\t\tORDER BY co.column_id DESC";
$oldcodes = $db->GetAll($sql);
print "<div>" . T_("Choose an existing code level: ");
display_chooser($oldcodes, 'old_code_level_id', 'old_code_level_id', true, "data_id={$data_id}&column_id={$column_id}");
print "</div>";
$sql = "SELECT code_id,value,label\r\n\t\t\t\t\tFROM code\r\n\t\t\t\t\tWHERE code_level_id = '{$cli}'";
$codes = $db->GetAll($sql);
foreach ($codes as $c) {
print "<div><input type='text' name='c{$c['code_id']}' id='c{$c['code_id']}' size='2' value=\"{$c['value']}\"/> <input type='text' name='l{$c['code_id']}' id='l{$c['code_id']}' value=\"{$c['label']}\" size='50'/></div>";
}
print "<div><input type='hidden' name='code_level_id' id='code_level_id' value='{$cli}'/></div>";
}
}
print "<div><input type='submit' name='submit' id='submit' value='" . T_("Update") . "'/><input type='submit' name='submitmove' id='submitmove' value='" . T_("Update and move to next column") . "'/></div></form>";
}
}
xhtml_foot();
<?php
print "<thead><tr><th>" . T_("Day") . "</th><th>" . T_("Start") . "</th> <th>" . T_("End") . "</th><th>" . T_("Use shift?") . "</th></tr></thead>";
$count = 1;
foreach ($shifts as $shift) {
$checked = "";
$shift_id = "";
$prefix = "";
if (!empty($shift['shift_id'])) {
$checked = "checked=\"checked\"";
$shift_id = $shift['shift_id'];
} else {
$shift_id = $count;
$prefix = "NEW_";
}
print "<tr><td>";
display_chooser($daysofweek, $prefix . "dow_{$shift_id}", false, true, false, false, false, array("dt", $shift['dt']));
print "</td><td><div class=\"input-group clockpicker\"><input readonly size=\"8\" name=\"" . $prefix . "start_{$shift_id}\" maxlength=\"8\" type=\"time\" value=\"{$shift['start']}\" class=\"form-control \"/><span class=\"input-group-addon\"><span class=\"glyphicon glyphicon-time fa\"></span></span></div></td><td><div class=\"input-group clockpicker\"><input readonly name=\"" . $prefix . "end_{$shift_id}\" type=\"text\" size=\"8\" maxlength=\"8\" value=\"{$shift['end']}\" class=\"form-control\"/><span class=\"input-group-addon\"><span class=\"glyphicon glyphicon-time fa\"></span></span></div></td><td class=\"text-center\"><input name=\"" . $prefix . "use_{$shift_id}\" type=\"checkbox\" class=\"form-control fa\" data-toggle=\"toggle\" data-size=\"\" data-on=" . TQ_("Yes") . " data-off=" . TQ_("No") . " {$checked}/></td></tr>";
$count++;
}
?>
<!--<tr><td/><td/><td/><td>Select all</td></tr>-->
</table></br>
<!--<p><input type="submit" name="addshift" value="Add Shift"/></p>-->
<input type="submit" name="submit" value="<?php
echo T_("Save changes");
?>
" class="btn btn-primary"/>
<input type="hidden" name="year" value="<?php
echo $year;
?>
"/>
</form>
<?php
} else {
print "<div class='alert text-info' role='alert'><h5>" . T_("Availability groups not defined for this questionnaire") . "</h5></div>";
}
print "</div>";
}
//set an outcome
print "<div class='clearfix '></div><div class='panel-body col-sm-6 '><h4><i class='fa fa-dot-circle-o'></i> " . T_("Set a case outcome") . "</h4>";
?>
<form method="get" action="?" class="form-inline">
<?php
$sql = "SELECT outcome_id as value,description, CASE WHEN outcome_id = '{$current_outcome_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\t\t\tFROM outcome";
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("description"));
display_chooser($rs2, "outcome_id", "outcome_id", true, false, false, false);
?>
<input type="hidden" name="case_id" value="<?php
echo $case_id;
?>
" /><br/><br/>
<button class="submitclass btn btn-primary" type="submit" name="submit" ><i class="fa fa-dot-circle-o fa-lg"></i> <?php
echo T_("Set outcome");
?>
</button>
</form>
<?php
print "</div>";
if (isset($r[0]['sample_id'])) {
// if sample data exists deidentify record
print "<div class='panel-body col-sm-6 pull-right'><h4 class ='text-danger'><i class='fa fa-trash-o fa-lg'></i> " . T_("Deidentify") . "</h4>";
xhtml_table($rs, array("outcome_id", "des", "link", "count", "perc"), array(T_("ID"), T_("Outcome"), "", T_("Count"), " " . T_("%")), "tclass", array("des" => "Complete"), array("count", "perc"));
} else {
print "<p>" . T_("No outcomes recorded for this sample") . "</p>";
}
print "</div></div>";
}
$operator_id = false;
if (isset($_GET['operator_id'])) {
$operator_id = bigintval($_GET['operator_id']);
}
//display a list of operators !!!worked for this questionnaire_id !!!!
$sql = "SELECT s.operator_id as value, s.firstname as description, CASE WHEN s.operator_id = '{$operator_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\t\t\tFROM `call` as c , `operator`as s, `case` as ca\r\n\t\t\tWHERE ca.questionnaire_id = '{$questionnaire_id}'\r\n\t\t\tAND ca.case_id = c.case_id\r\n\t\t\tAND c.operator_id = s.operator_id\r\n\t\t\tGROUP by c.operator_id";
$r = $db->GetAll($sql);
print "<div class='clearfix '></div><h3 class='col-sm-4 pull-left text-right'>" . T_("Operator") . ": " . "</h3>";
if (!empty($r)) {
display_chooser($r, "operator_id", "operator_id", true, "questionnaire_id={$questionnaire_id}", true, true, false, true, "pull-left col-sm-4");
} else {
print "<h4 class='col-sm-3 text-danger'>" . T_("No operators assigned") . ". </h4>";
}
print "";
if ($operator_id != false) {
print "<div class='clearfix form-group'></div><div class='col-sm-6'><div class='panel panel-body'><p>" . T_("Operator call outcomes") . "</p>";
$sql = "SELECT o.description as des, o.outcome_id, count( c.call_id ) as count, ROUND((count(c.call_id) / (SELECT count(call.call_id) FROM `call` JOIN `case` ON (call.case_id = `case`.case_id AND `case`.questionnaire_id = {$questionnaire_id} ) WHERE call.operator_id = '{$operator_id}')) * 100,2) as perc\r\n\t\t\t\tFROM `call` AS c, `case` as ca, `outcome` AS o\r\n\t\t\t\tWHERE ca.questionnaire_id = '{$questionnaire_id}'\r\n\t\t\t\tAND ca.case_id = c.case_id\r\n\t\t\t\tAND c.operator_id = '{$operator_id}'\r\n\t\t\t\tAND c.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", "perc"), array(T_("Outcome"), T_("Count"), T_("%")), "tclass", array("des" => "Complete"), array("count", "perc"));
}
print "</div></div>";
}
} else {
if (isset($_GET['del'])) {
$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 functions
*/
include "../functions/functions.xhtml.php";
/**
* DB functions
*/
include "../db.inc.php";
/**
* Export functions
*/
include "../functions/functions.export.php";
if (isset($_GET['codegroup'])) {
export_code(intval($_GET['codegroup']));
exit;
}
xhtml_head(T_("Export code groups"), true, array("../css/table.css"), array("../js/display.js"));
$code_group_id = 0;
if (isset($_GET['code_group_id'])) {
$code_group_id = intval($_GET['code_group_id']);
}
//Select a code group to export data from
$sql = "SELECT code_group_id as value,description, CASE WHEN code_group_id = '{$code_group_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\tFROM code_group";
print "<div>" . T_("Select code group: ");
$rs2 = $db->GetAll($sql);
translate_array($rs2, array("description"));
display_chooser($rs2, 'code_group_id', 'code_group_id');
print "</div>";
if ($code_group_id != 0) {
print "<p><a href='?codegroup={$code_group_id}'>" . T_("Export code group") . "</a></p>";
}
xhtml_foot();
if (isset($_GET['sample_var_id'])) {
?>
 <label for="comparisons" class="control-label"><?php
echo T_("The type of comparison");
?>
</label>: <select name="comparisons" id="comparisons" class="form-control"><option value="LIKE">LIKE</option><option value="NOT LIKE">NOT LIKE</option><option value="=">=</option><option value="!=">!=</option><option value="<"><</option><option value=">">></option><option value="<="><=</option><option value=">=">>=</option></select> 
<label for="exclude_val" class="control-label"><?php
echo T_("Value");
?>
: </label>
<?php
$sql = "SELECT sv.val as value, sv.val as description, '' AS selected, sivr.var as var\r\n\t\t\t\tFROM sample_var AS sv, sample AS s, `sample_import_var_restrict` as sivr\r\n\t\t\t\tWHERE s.import_id = {$sample_import_id}\r\n\t\t\t\tAND s.sample_id = sv.sample_id\r\n\t\t\t\tAND sv.var_id = {$ssample_var_id}\r\n\t\t\t\tAND sivr.var_id = sv.var_id\r\n\t\t\t\tGROUP BY sv.val";
$val = $db->GetAll($sql);
$sample_var = $val[0]['var'];
display_chooser($val, "exclude_val", "exclude_val", false, false, false, false);
flush();
?>
 </p>
<p><label for="description" class="control-label"><?php
echo T_("Description");
?>
: </label><input type="text" class="form-control" name="description" id="description" size="80"/></p>
<input type="hidden" name="sample_var" value="<?php
print $sample_var;
?>
"/>
<p><button type="submit" class="btn btn-primary" name="adds" value=""/><i class="fa fa-plus-circle fa-lg"></i> <?php
echo TQ_("Add restriction");
?>
</button></p>
print "<li>" . T_("{Sample:var} A record from the sample where the column name is 'var'") . "</li>";
print "</ul></div>";
$sql = "SELECT sivr.var as description, CONCAT('{Sample:', sivr.var, '}') as value\r\n\t\t\tFROM `sample_import_var_restrict` as sivr, questionnaire_sample as qs\r\n\t\t\tWHERE qs.questionnaire_id = '{$questionnaire_id}' \r\n\t\t\tAND sivr.sample_import_id = qs.sample_import_id";
?>
<form action="" method="get" class="form-inline form-group">
<p><label for="value"><?php
echo T_("The value to pre fill");
?>
: </label><input type="text" name="value" id="value" size="50" class="form-control"/></p>
<p><label for="svar"><?php
echo T_("or: Select pre fill from sample list");
?>
 </label>
<?php
//display a list of possible sample variables for this questionnaire
display_chooser($db->GetAll($sql), "svar", "svar", true, false, false, false, false, true, "form-group");
?>
</p>
<input type="hidden" name="questionnaire_id" value="<?php
print $questionnaire_id;
?>
"/>
<input type="hidden" name="sgqa" value="<?php
print $sgqa;
?>
"/>
</br>
<p><input type="submit" name="add_prefill" class="btn btn-primary fa" value="<?php
print T_("Add pre fill");
?>
"/></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();
//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();
?>
$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();
:</label>
<input name="email" type="text" class="form-control" size="40" value="<?php
echo $email;
?>
"/>
</div>
<div class="form-group form-inline">
<label class="control-label col-lg-3"><a href='timezonetemplate.php'><?php
echo T_("Timezone");
echo ":</a></label><div size=\"40\">";
if (isset($_GET['edit']) && $_GET['edit'] > 0) {
$dtz = $time_zone_name;
} else {
$dtz = get_setting("DEFAULT_TIME_ZONE");
}
display_chooser($tzs, "Time_zone_name", "Time_zone_name", false, false, false, false, array("value", $dtz), true, "pull-left");
?>
</div>
</div>
<?php
if (isset($_GET['edit']) && $_GET['edit'] > 0) {
?>
<input name="uid" type="hidden" value="<?php
echo $uid;
?>
"/>
<?php
}
?>
<div class="form-group">
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();
<a class="btn btn-danger" href=" "><?php
echo T_("Yes"), ", ", T_("Delete");
?>
</a>
</div>
</div>
</div>
</div>
<?php
echo "<a href='' onclick='history.back();return false;' class='btn btn-default pull-left' ><i class='fa fa-chevron-left text-primary'></i> " . T_("Go back") . "</a>";
$sql = "SELECT sample_import_id as value,description, CASE WHEN sample_import_id = '{$sample_import_id}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected\r\n\tFROM sample_import ORDER BY description ASC";
$r = $db->GetAll($sql);
if (!empty($r)) {
print "<div class=' form-inline form-group col-md-10'><h4 class='control-label form-group col-md-4 text-right'>" . T_("Select sample ") . "</h4>";
}
display_chooser($r, "sample_import_id", "sample_import_id", true, false, true, false);
print "</div>";
if (isset($_GET['sample_id'])) {
//need to remove this sample record from the sample
$sample_id = bigintval($_GET['sample_id']);
$db->StartTrans();
$sql = "DELETE FROM sample_var\r\n\t\tWHERE sample_id = '{$sample_id}'";
$db->Execute($sql);
$sql = "DELETE FROM sample\r\n\t\tWHERE sample_id = '{$sample_id}'";
$db->Execute($sql);
$db->CompleteTrans();
print "<div class='alert alert-danger pull-left form-group col-sm-6' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='true'>×</span></button><p>" . T_("Sample ID") . " <b>" . $sample_id . "</b> " . T_("Deleted") . ".</p></div>";
}
print "<div class='clearfix'></div>";
if ($sample_import_id != false) {
$sql = "SELECT sv.sample_id, CASE WHEN c.case_id IS NULL THEN \r\n\t\tCONCAT(' <a href=\\'\\' data-toggle=\\'modal\\' data-target=\\'.delete-confirm\\' data-href=\\'?sample_import_id={$sample_import_id}&sample_id=', sv.sample_id ,'\\' data-sample_id=\\' ', sv.sample_id ,' \\' class=\\'\\'><i data-toggle=\\'tooltip\\' title=\\'" . TQ_("Delete sample record") . " ', sv.sample_id ,'\\' class=\\'fa fa-2x fa-trash-o text-danger\\'></i></a> ')\r\n\t\tELSE CONCAT('<a href=\\'supervisor.php?case_id=', c.case_id , '\\' data-toggle=\\'tooltip\\' title=\\'" . TQ_("Assigned to case ID :") . " ', c.case_id , '\\'><b>', c.case_id ,'</b></a>')\r\n\t\tEND as link\r\n\t\t\tFROM sample_var AS sv\r\n\t\t\tJOIN (sample as s) ON (s.import_id = '{$sample_import_id}' and sv.sample_id = s.sample_id)\r\n\t\t\tLEFT JOIN (`case` AS c, questionnaire AS q) ON ( c.sample_id = sv.sample_id AND q.questionnaire_id = c.questionnaire_id )\r\n\t\t\tGROUP BY s.sample_id, c.case_id";
if (isset($_GET['questionnaire_id'])) {
$questionnaire_id = bigintval($_GET['questionnaire_id']);
}
display_questionnaire_chooser($questionnaire_id, false, "form-inline col-lg-4 pull-left", "form-control");
if ($questionnaire_id) {
$sql = "SELECT lime_sid \r\n\t\tFROM questionnaire\r\n\t\tWHERE questionnaire_id = {$questionnaire_id}";
$ls = $db->GetRow($sql);
$lsid = $ls['lime_sid'];
print " <a href='" . LIME_URL . "admin/admin.php?action=exportresults&sid={$lsid}' class='btn btn-default fa btn-lime'>" . T_("Download data for this questionnaire via Limesurvey") . "</a></div>";
print "<div class='form-group clearfix'><h3 class='col-sm-4 text-right'>" . T_("Please select a 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 col-lg-4 pull-left", "form-control");
if ($sample_import_id) {
print " <a href='" . LIME_URL . "admin/admin.php?action=exportresults&sid={$lsid}&quexsfilterinc={$questionnaire_id}:{$sample_import_id}' class='btn btn-default fa btn-lime'>" . T_("Download data for this sample via Limesurvey") . "</a></div>";
//get sample vars
$sql = "SELECT sivr.var_id as value, sivr.var as description\r\n\t\tFROM `sample_import_var_restrict` as sivr\r\n\t\tWHERE sivr.sample_import_id = {$sample_import_id}";
$rs = $db->GetAll($sql);
//download a key file linking the caseid to the sample
print "<div class='form-group '><h3 class='col-sm-4 text-right'>" . T_("Download key file: select sample var") . ": </h3>";
display_chooser($rs, "sample_var", "sample_var", true, "questionnaire_id={$questionnaire_id}&sample_import_id={$sample_import_id}", true, true, false, true, "form-inline col-lg-4 pull-left");
print "</div><div class=' col-sm-4'>";
//download complete key file
print "<a href='?key=key&questionnaire_id={$questionnaire_id}&sample_import_id={$sample_import_id}' class='btn btn-default fa'>" . T_("Download complete key file") . "</a></br></br>";
//download complete sample file with outcomes
print "<a href='?sample=sample&questionnaire_id={$questionnaire_id}&sample_import_id={$sample_import_id}' class='btn btn-default fa'>" . T_("Download complete sample file with current outcomes") . "</a></div>";
}
}
xhtml_foot();
$h = $_GET['h'];
$v = $_GET['v'];
$row = array("description", "value");
$hdr = array(T_("Description"), T_("Value"));
$sbtn = T_("Save custom Outcome");
$class = "table-hover table-condensed";
}
/* for questionnire outcomes */
if (isset($_GET['qid'])) {
$qid = intval($_GET['qid']);
if ($qid == 0) {
$qid = false;
}
print "<div class='form-group'><h3 class='form-inline text-right col-lg-4'>" . T_("Questionnaire") . ": </h3>";
$sql = "SELECT questionnaire_id as value,description, CASE WHEN questionnaire_id = '{$qid}' THEN 'selected=\\'selected\\'' ELSE '' END AS selected \r\n\t\t\tFROM questionnaire\r\n\t\t\tWHERE enabled = 1";
display_chooser($db->GetAll($sql), "questionnaire", "qid", true, false, true, true, false, true, "form-inline");
print "</div>";
if ($qid != 0) {
$qd = $db->GetRow("SELECT outcomes, self_complete, referral FROM `questionnaire` WHERE questionnaire_id = {$qid}");
$qoutc = $qd['outcomes'];
$sc = $qd['self_complete'];
$ref = $qd['referral'];
if (empty($qoutc)) {
// update q.outcomes with default list
$sql = "SELECT o.outcome_id\r\n\t\t\t\t\tFROM `outcome` as o\r\n\t\t\t\t\tWHERE o.default = 1;";
$def = $db->GetAll($sql);
for ($i = 0; $i < count($def); $i++) {
foreach ($def[$i] as $key => $val) {
$do[] = $val;
}
}
