Esempio n. 1
0
     }
 }
 $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);
Esempio n. 2
0
/**
 * 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;
}