/**
* Get the current or next case id
*
* @param int $operator_id The operator id
* @param bool $create True if a case can be created
* @return bool|int False if no case available else the case_id
*/
function get_case_id($operator_id, $create = false)
{
global $db;
$db->StartTrans();
/**
* See if case already assigned
*/
$sql = "SELECT case_id\r\n\t\tFROM `case`\r\n\t\tWHERE current_operator_id = '{$operator_id}'";
$r1 = $db->GetRow($sql);
$case_id = false;
if (empty($r1)) {
$sql = "SELECT o.next_case_id \r\n\t\t\tFROM `operator` as o, `case` as c\r\n\t\t\tWHERE o.operator_id = '{$operator_id}'\r\n\t\t\tAND c.case_id = o.next_case_id\r\n\t\t\tAND c.current_operator_id IS NULL";
$rnc = $db->GetRow($sql);
$sql = "SELECT cq.case_id, cq.case_queue_id\r\n\t\t\tFROM case_queue as cq, `case` as c\r\n\t\t\tWHERE cq.operator_id = '{$operator_id}'\r\n\t\t\tAND cq.case_id = c.case_id\r\n\t\t\tAND c.current_operator_id IS NULL\r\n\t\t\tORDER BY cq.sortorder ASC\r\n\t\t\tLIMIT 1";
$sq = $db->GetRow($sql);
if (isset($rnc['next_case_id']) && !empty($rnc['next_case_id'])) {
$case_id = $rnc['next_case_id'];
$sql = "UPDATE `case`\r\n\t\t\t\tSET current_operator_id = '{$operator_id}'\r\n\t\t\t\tWHERE current_operator_id IS NULL\r\n\t\t\t\tAND case_id = '{$case_id}'";
$db->Execute($sql);
//should fail transaction if already assigned to another case
if ($db->Affected_Rows() != 1) {
$db->FailTrans();
} else {
//remove next case setting
$sql = "UPDATE `operator`\r\n\t\t\t\t\tSET next_case_id = NULL\r\n\t\t\t\t\tWHERE operator_id = '{$operator_id}'";
$db->Execute($sql);
}
} else {
if (isset($sq['case_id']) && !empty($sq['case_id'])) {
$case_id = $sq['case_id'];
$case_queue_id = $sq['case_queue_id'];
$sql = "UPDATE `case`\r\n\t\t\t\tSET current_operator_id = '{$operator_id}'\r\n\t\t\t\tWHERE current_operator_id IS NULL\r\n\t\t\t\tAND case_id = '{$case_id}'";
$db->Execute($sql);
//should fail transaction if already assigned to another case
if ($db->Affected_Rows() != 1) {
$db->FailTrans();
} else {
//remove case from queue and update sortorder
$sql = "DELETE FROM case_queue\r\n\t\t\t\t\tWHERE case_queue_id = '{$case_queue_id}'";
$db->Execute($sql);
$sql = "SELECT case_queue_id\r\n\t\t\t\t\tFROM case_queue\r\n\t\t\t\t\tWHERE operator_id = '{$operator_id}'\r\n\t\t\t\t\tORDER BY sortorder ASC";
$rs = $db->GetAll($sql);
$sortorder = 1;
foreach ($rs as $r) {
$sql = "UPDATE case_queue\r\n\t\t\t\t\t\tSET sortorder = '{$sortorder}'\r\n\t\t\t\t\t\tWHERE case_queue_id = '{$r['case_queue_id']}'";
$db->Execute($sql);
$sortorder++;
}
}
} else {
if ($create) {
$systemsort = get_setting('systemsort');
if ($systemsort) {
//Just make sure that this case should go to this operator (assigned to this project and skill)
//Also check if this is an exclusive appointment and that the questionnaire is enabled
$sql = "SELECT c.case_id as caseid\r\n\t\t\t\t\tFROM `case` as c\r\n\t\t\t\t\tJOIN operator_questionnaire AS oq ON (oq.operator_id = '{$operator_id}' AND oq.questionnaire_id = c.questionnaire_id)\r\n\t\t\t\t\tJOIN questionnaire as q ON (q.questionnaire_id = c.questionnaire_id AND q.enabled = 1)\r\n\t\t\t\t\tJOIN outcome as ou ON (ou.outcome_id = c.current_outcome_id)\r\n\t\t\t\t\tJOIN operator_skill as os ON (os.operator_id = '{$operator_id}' AND os.outcome_type_id = ou.outcome_type_id)\r\n\t\t\t\t\tLEFT JOIN appointment as apn on (apn.case_id = c.case_id AND apn.completed_call_id is NULL AND (CONVERT_TZ(NOW(),'System','UTC') >= apn.start) AND (CONVERT_TZ(NOW(),'System','UTC') <= apn.end))\r\n\t\t\t\t\tWHERE c.sortorder IS NOT NULL\r\n\t\t\t\t\tAND c.current_operator_id IS NULL\r\n\t\t\t\t\tAND ((apn.require_operator_id IS NULL) OR (apn.require_operator_id = '{$operator_id}'))\r\n\t\t\t\t\tORDER BY c.sortorder ASC\r\n\t\t\t\t\tLIMIT 1";
} else {
/**
* find a case that:
* Has not been called in the last x hours based on last outcome
* Is available for this operator
* Has no appointments scheduled in the future (can also check if outcome is appointment)
* Nobody else is servicing the call at the moment
* The case is not referred to the supervisor and the operator is not the supervisor
* The case is not on a refusal outcome and the operator is not a refusal converter
* Give priority if there is an appointment scheduled now
* If restricted to shift times to work, make sure we are in those
* If restricted to respondent call times, make sure we are in those
* Only assign if outcome type is assigned to the operator
* Has not reached the quota
* Is part of an enabled questionnaire
*
*
* THINGS TO ADD:
*
* @todo also could check the respondent_not_available table to see if now is a "bad time" to call
*/
$sql = "SELECT c.case_id as caseid\r\n\t\t\t\t\tFROM `case` as c\r\n\t\t\t\t\tLEFT JOIN `call` as a on (a.call_id = c.last_call_id)\r\n\t\t\t\t\tJOIN (sample as s, sample_import as si) on (s.sample_id = c.sample_id and si.sample_import_id = s.import_id)\r\n\t\t\t\t\tJOIN (questionnaire_sample as qs, operator_questionnaire as o, questionnaire as q, operator as op, outcome as ou) on (c.questionnaire_id = q.questionnaire_id and q.enabled = 1 and op.operator_id = '{$operator_id}' and qs.sample_import_id = s.import_id and o.operator_id = op.operator_id and o.questionnaire_id = qs.questionnaire_id and q.questionnaire_id = o.questionnaire_id and ou.outcome_id = c.current_outcome_id)\r\n\t\t\t\t\tLEFT JOIN shift as sh on (sh.questionnaire_id = q.questionnaire_id and (CONVERT_TZ(NOW(),'System','UTC') >= sh.start) AND (CONVERT_TZ(NOW(),'System','UTC') <= sh.end))\r\n\t\t\t\t\tLEFT JOIN appointment as ap on (ap.case_id = c.case_id AND ap.completed_call_id is NULL AND (ap.start > CONVERT_TZ(NOW(),'System','UTC')))\r\n\t\t\t\t\tLEFT JOIN appointment as apn on (apn.case_id = c.case_id AND apn.completed_call_id is NULL AND (CONVERT_TZ(NOW(),'System','UTC') >= apn.start) AND (CONVERT_TZ(NOW(),'System','UTC') <= apn.end))\r\n\t\t\t\t\tLEFT JOIN call_restrict as cr on (cr.day_of_week = DAYOFWEEK(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) >= cr.start and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) <= cr.end)\r\n\t\t\t\t\tLEFT JOIN questionnaire_sample_exclude_priority AS qsep ON (qsep.questionnaire_id = c.questionnaire_id AND qsep.sample_id = c.sample_id)\r\n\t\t\t\t\tLEFT JOIN case_availability AS casa ON (casa.case_id = c.case_id)\r\n\t\t\t\t\tLEFT JOIN availability AS ava ON (ava.availability_group_id = casa.availability_group_id)\r\n\t\t\t\t\tLEFT JOIN questionnaire_timeslot AS qast ON (qast.questionnaire_id = c.questionnaire_id)\r\n\t\t\t\t\tLEFT JOIN questionnaire_sample_timeslot AS qasts ON (qasts.questionnaire_id = c.questionnaire_id AND qasts.sample_import_id = si.sample_import_id)\r\n\t\t\t\t\tJOIN operator_skill as os on (os.operator_id = op.operator_id and os.outcome_type_id = ou.outcome_type_id)\r\n\t\t\t\t\tWHERE c.current_operator_id IS NULL\r\n\t\t\t\t\tAND ((apn.appointment_id IS NOT NULL) OR (casa.case_id IS NULL) OR (ava.day_of_week = DAYOFWEEK(CONVERT_TZ(NOW(),'System',s.Time_zone_name)) AND TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) >= ava.start AND TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) <= ava.end ))\r\n AND ((apn.appointment_id IS NOT NULL) OR (qast.questionnaire_id IS NULL) OR ((SELECT COUNT(*) FROM availability WHERE availability.availability_group_id = qast.availability_group_id AND (availability.day_of_week = DAYOFWEEK(CONVERT_TZ(NOW(),'System',s.Time_zone_name)) AND TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) >= availability.start AND TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) <= availability.end)) >= 1 AND (SELECT COUNT(call_attempt_id) FROM `call_attempt`, availability WHERE call_attempt.case_id = c.case_id AND (availability.availability_group_id = qast.availability_group_id AND (availability.day_of_week = DAYOFWEEK(CONVERT_TZ(call_attempt.start,'UTC',s.Time_zone_name)) AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) >= availability.start AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) <= availability.end))) = (SELECT (SELECT COUNT(*) FROM availability, call_attempt WHERE call_attempt.case_id = c.case_id AND availability.availability_group_id = availability_group.availability_group_id AND (availability.day_of_week = DAYOFWEEK(CONVERT_TZ(call_attempt.start,'UTC',s.Time_zone_name)) AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) >= availability.start AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) <= availability.end)) as cou FROM availability_group, questionnaire_timeslot WHERE questionnaire_timeslot.questionnaire_id = c.questionnaire_id AND availability_group.availability_group_id = questionnaire_timeslot.availability_group_id ORDER BY cou ASC LIMIT 1)))\r\n AND ((apn.appointment_id IS NOT NULL) OR (qasts.questionnaire_id IS NULL) OR ((SELECT COUNT(*) FROM availability WHERE availability.availability_group_id = qasts.availability_group_id AND (availability.day_of_week = DAYOFWEEK(CONVERT_TZ(NOW(),'System',s.Time_zone_name)) AND TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) >= availability.start AND TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) <= availability.end)) >= 1 AND (SELECT COUNT(call_attempt_id) FROM `call_attempt`, availability WHERE call_attempt.case_id = c.case_id AND (availability.availability_group_id = qasts.availability_group_id AND (availability.day_of_week = DAYOFWEEK(CONVERT_TZ(call_attempt.start,'UTC',s.Time_zone_name)) AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) >= availability.start AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) <= availability.end))) = ( SELECT (SELECT COUNT(*) FROM availability, call_attempt WHERE call_attempt.case_id = c.case_id AND availability.availability_group_id = availability_group.availability_group_id AND (availability.day_of_week = DAYOFWEEK(CONVERT_TZ(call_attempt.start,'UTC',s.Time_zone_name)) AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) >= availability.start AND TIME(CONVERT_TZ(call_attempt.start, 'UTC' , s.Time_zone_name)) <= availability.end)) as cou FROM availability_group, questionnaire_sample_timeslot WHERE questionnaire_sample_timeslot.questionnaire_id = c.questionnaire_id AND questionnaire_sample_timeslot.sample_import_id = si.sample_import_id AND availability_group.availability_group_id = questionnaire_sample_timeslot.availability_group_id ORDER BY cou ASC LIMIT 1)))\r\n\t\t AND ((a.call_id is NULL) OR (a.end < CONVERT_TZ(DATE_SUB(NOW(), INTERVAL ou.default_delay_minutes MINUTE),'System','UTC')))\r\n\t\t\t\t\tAND ap.case_id is NULL\r\n\t\t\t\t\tAND ((qsep.questionnaire_id is NULL) OR (qsep.exclude = 0))\r\n\t\t\t\t\tAND !(q.restrict_work_shifts = 1 AND sh.shift_id IS NULL AND os.outcome_type_id != 2)\r\n\t\t\t\t\tAND !(si.call_restrict = 1 AND cr.day_of_week IS NULL AND os.outcome_type_id != 2)\r\n\t\t\t\t\tAND ((apn.appointment_id IS NOT NULL) OR (qs.call_attempt_max = 0) OR ((SELECT count(*) FROM call_attempt WHERE case_id = c.case_id) < qs.call_attempt_max))\r\n\t\t\t\t\tAND ((apn.appointment_id IS NOT NULL) OR (qs.call_max = 0) OR ((SELECT count(*) FROM `call` WHERE case_id = c.case_id) < qs.call_max))\r\n\t\t\t\t\tAND ((apn.require_operator_id IS NULL) OR (apn.require_operator_id = '{$operator_id}'))\r\n\t\t\t\t\tAND (SELECT count(*) FROM `questionnaire_sample_quota` WHERE questionnaire_id = c.questionnaire_id AND sample_import_id = s.import_id AND quota_reached = 1) = 0\r\n\t\t\t\t\tORDER BY IF(ISNULL(apn.end),1,0),apn.end ASC, qsep.priority DESC, CONVERT_TZ(NOW(), 'System' , s.Time_zone_name) DESC , a.start ASC, qs.sort_order ASC\r\n\t\t\t\t\tLIMIT 1";
//apn.appointment_id contains the id of an appointment if we are calling on an appointment
}
$r2 = $db->GetRow($sql);
if (empty($r2)) {
if ($systemsort) {
//Just make sure that this case should go to this operator (assigned to this project and skill)
$sql = "SELECT qsep.sample_id as sample_id, qsep.questionnaire_id as questionnaire_id, q.testing as testing\r\n\t\t\t\t\t\tFROM questionnaire_sample_exclude_priority as qsep\r\n\t\t\t\t\t\tJOIN operator_skill as os ON (os.operator_id = '{$operator_id}' AND os.outcome_type_id = 1)\r\n\t\t\t\t\t\tJOIN operator_questionnaire AS oq ON (oq.operator_id = '{$operator_id}' AND oq.questionnaire_id = qsep.questionnaire_id)\r\n\t\t\t\t\t\tJOIN questionnaire as q ON (q.questionnaire_id = qsep.questionnaire_id and q.enabled = 1)\r\n\t\t\t\t\t\tLEFT JOIN `case` as c ON (c.sample_id = qsep.sample_id AND c.questionnaire_id = qsep.questionnaire_id)\r\n\t\t\t\t\t\tWHERE qsep.sortorder IS NOT NULL \r\n\t\t\t\t\t\tAND c.case_id IS NULL\r\n\t\t\t\t\t\tORDER BY qsep.sortorder ASC\r\n\t\t\t\t\t\tLIMIT 1";
} else {
/**
* If no case found, we must draw the next available case from the sample
* only if no case due to lack of cases to call not out of shift time/etc and
* only draw cases that are new (Temporary outcome_type_id) - this makes sure that we are not drawing
* a case just because the operator doesn't have access to temporary outcome id's.
*
*
* Method:
* next available that has not been assigned
* if none available - return false? report to operator that no one available to call at currenet settings
*
*/
$sql = "SELECT s.sample_id as sample_id,c.case_id as case_id,qs.questionnaire_id as questionnaire_id,CONVERT_TZ(NOW(), 'System' , s.Time_zone_name) as resptime, q.testing as testing\r\n\t\t\t\t\t\tFROM sample as s\r\n\t\t\t\t\t\tJOIN (questionnaire_sample as qs, operator_questionnaire as o, questionnaire as q, operator as op, sample_import as si, operator_skill as os) on (op.operator_id = '{$operator_id}' and qs.sample_import_id = s.import_id and o.operator_id = op.operator_id and o.questionnaire_id = qs.questionnaire_id and q.questionnaire_id = o.questionnaire_id and si.sample_import_id = s.import_id and os.operator_id = op.operator_id and os.outcome_type_id = 1 and q.enabled = 1 and qs.allow_new = 1)\r\n\t\t\t\t\t\tLEFT JOIN `case` as c on (c.sample_id = s.sample_id and c.questionnaire_id = qs.questionnaire_id)\r\n\t\t\t\t\t\tLEFT JOIN call_restrict as cr on (cr.day_of_week = DAYOFWEEK(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) >= cr.start and TIME(CONVERT_TZ(NOW(), 'System' , s.Time_zone_name)) <= cr.end)\r\n\t\t\t\t\t\tLEFT JOIN shift as sh on (sh.questionnaire_id = q.questionnaire_id and (CONVERT_TZ(NOW(),'System','UTC') >= sh.start) AND (CONVERT_TZ(NOW(),'System','UTC') <= sh.end))\r\n\t\t\t\t\t\tLEFT JOIN questionnaire_sample_exclude_priority AS qsep ON (qsep.questionnaire_id = qs.questionnaire_id AND qsep.sample_id = s.sample_id)\r\n\t\t\t\t\t\tWHERE c.case_id is NULL\r\n\t\t\t\t\t\tAND ((qsep.questionnaire_id IS NULL) OR (qsep.exclude = 0))\r\n\t\t\t\t\t\tAND !(q.restrict_work_shifts = 1 AND sh.shift_id IS NULL)\r\n\t\t\t\t\t\tAND !(si.call_restrict = 1 AND cr.day_of_week IS NULL)\r\n\t\t\t\t\t\tAND (SELECT count(*) FROM `questionnaire_sample_quota` WHERE questionnaire_id = qs.questionnaire_id AND sample_import_id = s.import_id AND quota_reached = 1) = 0\r\n\t\t\t\t\t\tORDER BY qsep.priority DESC, CONVERT_TZ(NOW(), 'System' , s.Time_zone_name) DESC, rand() * qs.random_select, qs.sort_order ASC\r\n\t\t\t\t\t\tLIMIT 1";
}
$r3 = $db->GetRow($sql);
/**
* If the above statement returns no rows, then there are no cases to be added to the sample at this time
* We could do a select of how many are actually available to reassure the operator that the sample has not been exhausted
*
*
*/
/**
* Now we have to add phone numbers to the contact_phone table, the case to the case table,
* assign this case to this operator
*/
if (!empty($r3)) {
$case_id = add_case($r3['sample_id'], $r3['questionnaire_id'], $operator_id, $r3['testing']);
}
} else {
$case_id = $r2['caseid'];
$sql = "UPDATE `case`\r\n\t\t\t\t\tSET current_operator_id = '{$operator_id}'\r\n\t\t\t\t\tWHERE current_operator_id IS NULL\r\n\t\t\t\t\tAND case_id = '{$case_id}'";
$db->Execute($sql);
//should fail transaction if already assigned to another case
if ($db->Affected_Rows() != 1) {
$db->FailTrans();
}
}
} else {
$case_id = false;
}
}
}
} else {
$case_id = $r1['case_id'];
}
if ($db->HasFailedTrans()) {
error_log("FAILED in get_case_id for case {$case_id}", 0);
$case_id = false;
//make sure we aren't returning an invalid case id
}
$db->CompleteTrans();
return $case_id;
}
$sql = "INSERT INTO `sample_import_var_restrict` (`var`,`type`)\r\n\t\t\t\t\tVALUES ('{$r['var']}','{$r['type']}')";
$db->Execute($sql);
$varid = $db->Insert_ID();
$sql = "INSERT INTO `sample_var` (`sample_id`,`var_id`,`val`)\r\n\t\t\t\tVALUES ('{$sample_id}','{$varid}'," . $db->qstr($_POST['v_' . $r['var']]) . ")";
$db->Execute($sql);
}
//Add CASEREFERREDFROM record
$sql = "INSERT INTO `sample_import_var_restrict` (`var`,`type`)\r\n\t\t\t\tVALUES ('CASEREFERREDFROM','1')";
$db->Execute($sql);
$varid = $db->Insert_ID();
$sql = "INSERT INTO `sample_var` (`sample_id`,`var_id`,`val`)\r\n\t\t\tVALUES ('{$sample_id}','{$varid}','{$case_id}')";
$db->Execute($sql);
//Create a new case
$sql = "SELECT questionnaire_id\r\n\t\t\tFROM `case`\r\n\t\t\tWHERE case_id = '{$case_id}'";
$questionnaire_id = $db->GetOne($sql);
$ncase_id = add_case($sample_id, $questionnaire_id);
//If selected to call now - assign to this operator
if (isset($_POST['makecase'])) {
$sql = "SELECT MAX(sortorder)\r\n\t\t\t\tFROM case_queue\r\n\t\t\t\tWHERE operator_id = '{$operator_id}'";
$sortorder = $db->GetOne($sql);
$sortorder++;
$sql = "INSERT INTO case_queue (case_id,operator_id,sortorder)\r\n\t\t\t\tVALUES ('{$ncase_id}', '{$operator_id}', '{$sortorder}')";
$db->Execute($sql);
}
//Add a note that we have referred another case
$sql = "INSERT INTO `case_note` (case_id,operator_id,note,datetime)\r\n\t\t\tVALUES ({$case_id},{$operator_id},'" . T_("Generated referral to case id") . ": {$ncase_id}',CONVERT_TZ(NOW(),'System','UTC'))";
$db->Execute($sql);
//Add a note that it is referred from another case
$sql = "INSERT INTO `case_note` (case_id,operator_id,note,datetime)\r\n\t\t\tVALUES ({$ncase_id},{$operator_id},'" . T_("Generated as referral from case id") . ": {$case_id}',CONVERT_TZ(NOW(),'System','UTC'))";
$db->Execute($sql);
if ($db->CompleteTrans()) {
foreach ($rs as $r) {
$fields[] = array('attribute_' . $i, 'C', '255');
$fieldcontents .= 'attribute_' . $i . '=' . $r['var'] . "\n";
$i++;
}
$dict = NewDataDictionary($db);
$sqlarray = $dict->ChangeTableSQL(LIME_PREFIX . "tokens_{$lime_sid}", $fields);
$execresult = $dict->ExecuteSQLArray($sqlarray, false);
$sql = "UPDATE " . LIME_PREFIX . "surveys\r\n\t\t\tSET attributedescriptions = " . $db->qstr($fieldcontents) . "\r\n\t\t\tWHERE sid='{$lime_sid}'";
$db->Execute($sql);
//generate one case for each sample record and set outcome to 41
$sql = "SELECT sample_id\r\n\t\t\tFROM sample\r\n\t\t\tWHERE import_id = '{$sid}'";
$rs = $db->GetAll($sql);
foreach ($rs as $r) {
set_time_limit(30);
add_case($r['sample_id'], $questionnaire_id, "NULL", $testing, 41, true);
}
$db->CompleteTrans();
}
}
if (isset($_POST['edit'])) {
//need to add sample to questionnaire
$questionnaire_id = bigintval($_POST['questionnaire_id']);
$sid = bigintval($_POST['sample_import_id']);
$cm = bigintval($_POST['call_max']);
$cam = bigintval($_POST['call_attempt_max']);
$am = bigintval($_POST['answering_machine_messages']);
$selecttype = 0;
if (isset($_POST['selecttype'])) {
$selecttype = 1;
}
