/** * 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; }