}
}
$closecasescounter++;
//Sort cases on a questionnaire by questionnaire basis
$sql = "SELECT questionnaire_id, description\r\n\t\tFROM questionnaire\r\n\t\tWHERE enabled = 1";
$qs = $db->GetAll($sql);
foreach ($qs as $q) {
print T_("Sorting cases for ") . $q['description'];
$questionnaire_id = $q['questionnaire_id'];
$time_start = microtime(true);
$db->StartTrans();
//Set all cases as unavailable
$sql = "UPDATE `case`\r\n \t\tSET sortorder = NULL\r\n WHERE sortorder IS NOT NULL\r\n AND questionnaire_id = '{$questionnaire_id}'";
$db->Execute($sql);
//update quotas
update_quotas($questionnaire_id);
//Sort current cases for this questionnaire
$sql = "SELECT c.case_id\r\n\t \tFROM `case` as c\r\n \t\tLEFT JOIN `call` as a on (a.call_id = c.last_call_id)\r\n \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\tJOIN (questionnaire_sample as qs, questionnaire as q, outcome as ou) on (c.questionnaire_id = q.questionnaire_id and qs.sample_import_id = s.import_id and ou.outcome_id = c.current_outcome_id and q.questionnaire_id = '{$questionnaire_id}' and qs.questionnaire_id = c.questionnaire_id)\r\n \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\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\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\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\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\tLEFT JOIN case_availability AS casa ON (casa.case_id = c.case_id)\r\n \t\tLEFT JOIN availability AS ava ON (ava.availability_group_id = casa.availability_group_id)\r\n\t\tLEFT JOIN questionnaire_timeslot AS qast ON (qast.questionnaire_id = c.questionnaire_id)\r\n\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\tWHERE c.current_operator_id IS NULL\r\n\t\tAND c.questionnaire_id = '{$questionnaire_id}'\r\n \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\t\t\r\n\t\tAND ((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\t\t\r\n\t\tAND ((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\r\n \t\tAND (a.call_id is NULL or (a.end < CONVERT_TZ(DATE_SUB(NOW(), INTERVAL ou.default_delay_minutes MINUTE),'System','UTC')))\r\n \t\tAND ap.case_id is NULL\r\n \t\tAND ((qsep.questionnaire_id is NULL) or qsep.exclude = 0)\r\n \t\tAND !(q.restrict_work_shifts = 1 AND sh.shift_id IS NULL)\r\n \t\tAND !(si.call_restrict = 1 AND cr.day_of_week IS NULL)\r\n \t\tAND ((apn.appointment_id IS NOT NULL) or qs.call_attempt_max = 0 or ((SELECT count(*) FROM call_attempt WHERE call_attempt.case_id = c.case_id) < qs.call_attempt_max))\r\n \t\tAND ((apn.appointment_id IS NOT NULL) or qs.call_max = 0 or ((SELECT count(*) FROM `call` WHERE `call`.case_id = c.case_id) < qs.call_max))\r\n \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\tGROUP BY c.case_id\r\n\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";
$rs = $db->GetAll($sql);
$i = 1;
foreach ($rs as $r) {
$sql = "UPDATE `case`\r\n \t\t\tSET sortorder = '{$i}'\r\n \t\t\tWHERE case_id = '{$r['case_id']}'";
$db->Execute($sql);
$i++;
}
//First set all sample records as unavailable
$sql = "UPDATE `questionnaire_sample_exclude_priority`\r\n SET sortorder = NULL\r\n WHERE sortorder IS NOT NULL\r\n AND questionnaire_id = '{$questionnaire_id}'";
$db->Execute($sql);
//Sort sample list where attached to this questionnaire
$sql = "SELECT s.sample_id as sample_id,qs.questionnaire_id as questionnaire_id\r\n FROM sample as s\r\n JOIN (questionnaire_sample as qs, questionnaire as q, sample_import as si) on (qs.sample_import_id = s.import_id and si.sample_import_id = s.import_id and q.questionnaire_id = qs.questionnaire_id AND q.questionnaire_id = '{$questionnaire_id}' AND qs.allow_new = 1)\r\n LEFT JOIN `case` as c on (c.sample_id = s.sample_id and c.questionnaire_id = qs.questionnaire_id)\r\n LEFT 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 LEFT 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 LEFT JOIN questionnaire_sample_exclude_priority AS qsep ON (qsep.questionnaire_id = qs.questionnaire_id AND qsep.sample_id = s.sample_id)\r\n WHERE c.case_id is NULL\r\n AND ((qsep.questionnaire_id IS NULL) or qsep.exclude = 0)\r\n AND !(q.restrict_work_shifts = 1 AND sh.shift_id IS NULL)\r\n AND !(si.call_restrict = 1 AND cr.day_of_week IS NULL)\r\n AND (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 GROUP BY s.sample_id,qs.questionnaire_id\r\n ORDER BY qsep.priority DESC, rand() * qs.random_select, qs.sort_order ASC";
$rs = $db->GetAll($sql);
/**
* End the current case
*
* @param int $operator_id The operator to end the case for
*
* @see get_case()
*/
function end_case($operator_id)
{
global $db;
$db->StartTrans();
$case_id = get_case_id($operator_id, false);
$questionnaire_id = get_questionnaire_id($operator_id);
$return = false;
if ($case_id) {
//End all calls (with not attempted or worked if there is a call);
end_call($operator_id, 1);
//Make sure to end call attempts
end_call_attempt($operator_id);
//determine current final outcome code
//Look over all calls, for each phone number that is to be tried again
//Calculate outcome based on
//If no phone number is to be tried again, use the outcome from the last call
//If one phone number is to be tried again, use: "Differences in Response Rates using Most recent versus Final dispositions in Telephone Surveys" by Christopher McCarty
//
//Look for any calls where none should be tried again (this should be a final outcome)
$sql = "SELECT c.call_id, c.outcome_id\r\n\t\t\tFROM `call` as c, `outcome` as o\r\n\t\t\tWHERE c.case_id = '{$case_id}'\r\n\t\t\tAND c.outcome_id = o.outcome_id\r\n\t\t\tAND o.tryanother = 0\r\n\t\t\tAND (o.outcome_type_id = 4)\r\n\t\t\tORDER BY c.call_id DESC\r\n\t\t\tLIMIT 1";
$a = $db->GetRow($sql);
if (empty($a)) {
$sql = "SELECT c.*\r\n\t\t\t\tFROM contact_phone AS c\r\n\t\t\t\tLEFT JOIN (\r\n\t\t\t\t\tSELECT contact_phone.contact_phone_id\r\n\t\t\t\t\tFROM contact_phone\r\n\t\t\t\t\tLEFT JOIN `call` ON ( call.contact_phone_id = contact_phone.contact_phone_id )\r\n\t\t\t\t\tLEFT JOIN outcome ON ( call.outcome_id = outcome.outcome_id )\r\n\t\t\t\t\tWHERE contact_phone.case_id = '{$case_id}'\r\n\t\t\t\t\tAND outcome.tryagain =0\r\n\t\t\t\t\t) AS l ON l.contact_phone_id = c.contact_phone_id\r\n\t\t\t\tWHERE c.case_id = '{$case_id}'\r\n\t\t\t\tAND l.contact_phone_id IS NULL";
$r = $db->GetAll($sql);
//$r contains one row for each phone number that is to be tried again
if (!empty($r)) {
$count = count($r);
} else {
$count = 0;
}
$outcome = 1;
//default outcome is 1 - not attempted
//last call
$sql = "SELECT call_id,outcome_id\r\n\t\t\t\tFROM `call`\r\n\t\t\t\tWHERE case_id = '{$case_id}'\r\n\t\t\t\tORDER BY call_id DESC\r\n\t\t\t\tLIMIT 1";
$l = $db->GetRow($sql);
$lastcall = 0;
if (!empty($l)) {
$lastcall = $l['call_id'];
}
if ($count == 0) {
//last call
$sql = "SELECT c.outcome_id as outcome_id\r\n\t\t\t\t\tFROM `call` as c\r\n\t\t\t\t\tJOIN outcome AS o ON ( c.outcome_id = o.outcome_id)\r\n\t\t\t\t\tWHERE c.case_id = '{$case_id}'\r\n\t\t\t\t\tAND o.outcome_id != 18\r\n\t\t\t\t\tORDER BY c.call_id DESC\r\n\t\t\t\t\tLIMIT 1";
$t = $db->GetRow($sql);
if (!empty($t)) {
$outcome = $t['outcome_id'];
}
} else {
if ($count >= 1) {
$sql = "SELECT call_attempt_max,call_max\r\n FROM questionnaire_sample as qs, `case` as c, sample as s\r\n WHERE c.case_id = '{$case_id}'\r\n AND c.sample_id = s.sample_id\r\n AND qs.sample_import_id = s.import_id\r\n AND qs.questionnaire_id = c.questionnaire_id";
$cm = $db->GetRow($sql);
$sql = "SELECT COUNT(*) as c\r\n FROM call_attempt\r\n WHERE case_id = '{$case_id}'";
$callattempts = $db->GetOne($sql);
$sql = "SELECT COUNT(*) as c\r\n FROM `call`\r\n WHERE case_id = '{$case_id}'";
$calls = $db->GetOne($sql);
$eligsql = "SELECT count(*)\r\n FROM `call` as c, `outcome` as o\r\n WHERE c.outcome_id = o.outcome_id\r\n AND o.eligible = 1\r\n AND c.case_id = '{$case_id}'";
if ($cm['call_attempt_max'] > 0 && $callattempts >= $cm['call_attempt_max']) {
//if ever eligible, code as eligible
if ($db->GetOne($eligsql) > 0) {
$outcome = 44;
} else {
$outcome = 42;
}
} else {
if ($cm['call_max'] > 0 && $calls >= $cm['call_max']) {
//if ever eligible, code as eligible
if ($db->GetOne($eligsql) > 0) {
$outcome = 45;
} else {
$outcome = 43;
}
} else {
//$r[0]['contact_phone_id'];
//code as eligible if ever eligible, or if referred to the supervisor, code as that if last call
$sql = "SELECT c.outcome_id as outcome_id\r\n \t\t\t\t\tFROM `call` as c\r\n \t\t\t\t\tJOIN outcome AS o ON ( c.outcome_id = o.outcome_id AND (o.eligible = 1 OR o.outcome_type_id = 2 OR o.outcome_type_id = 1) )\r\n \t\t\t\t\tWHERE c.case_id = '{$case_id}'\r\n \t\t\t\t\tORDER BY c.call_id DESC";
$t = $db->GetRow($sql);
if (!empty($t)) {
$outcome = $t['outcome_id'];
}
}
}
}
}
} else {
//the last call is the call with the final otucome
$outcome = $a['outcome_id'];
$lastcall = $a['call_id'];
//if the outcome is complete, then update the quota's for this questionnaire (if any)
if ($outcome == 10) {
update_quotas($questionnaire_id, $case_id);
}
}
$sql = "UPDATE `case`\r\n\t\t\tSET current_operator_id = NULL, current_call_id = NULL, sortorder = NULL, current_outcome_id = '{$outcome}', last_call_id = '{$lastcall}'\r\n\t\t\tWHERE case_id = '{$case_id}'";
$o = $db->Execute($sql);
$return = true;
} else {
$return = false;
}
//if ($db->HasFailedTrans()) { print "FAILED in end_case"; exit; }
if ($db->CompleteTrans()) {
return $return;
}
return false;
}