<?php 


/*  This is not an additional file, it's only a patch for bug
	Replace/merge  original function with this in  ..quexs/functions/functions.operator.php file 
	
	
	Modification : - Added additional brackets ()  in querries 
*/

function get_case_id($operator_id, $create = false)
{

	global $db;

	$db->StartTrans();

	/**
	 * See if case already assigned
	 */
	$sql = "SELECT case_id
		FROM `case`
		WHERE current_operator_id = '$operator_id'";

	$r1 = $db->GetRow($sql);

	$case_id = false;

	if (empty($r1))
	{
		$sql = "SELECT o.next_case_id 
			FROM `operator` as o, `case` as c
			WHERE o.operator_id = '$operator_id'
			AND c.case_id = o.next_case_id
			AND c.current_operator_id IS NULL";

		$rnc = $db->GetRow($sql);

		$sql = "SELECT cq.case_id, cq.case_queue_id
			FROM case_queue as cq, `case` as c
			WHERE cq.operator_id = '$operator_id'
			AND cq.case_id = c.case_id
			AND c.current_operator_id IS NULL
			ORDER BY cq.sortorder ASC
			LIMIT 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`
				SET current_operator_id = '$operator_id'
				WHERE current_operator_id IS NULL
				AND 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`
					SET next_case_id = NULL
					WHERE 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`
				SET current_operator_id = '$operator_id'
				WHERE current_operator_id IS NULL
				AND 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
					WHERE case_queue_id = '$case_queue_id'";

				$db->Execute($sql);

				$sql = "SELECT case_queue_id
					FROM case_queue
					WHERE operator_id = '$operator_id'
					ORDER BY sortorder ASC";

				$rs = $db->GetAll($sql);

				$sortorder = 1;
				foreach($rs as $r)
				{
					$sql = "UPDATE case_queue
						SET sortorder = '$sortorder'
						WHERE 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
					FROM `case` as c
					JOIN operator_questionnaire AS oq ON (oq.operator_id = '$operator_id' AND oq.questionnaire_id = c.questionnaire_id)
					JOIN questionnaire as q ON (q.questionnaire_id = c.questionnaire_id AND q.enabled = 1)
					JOIN outcome as ou ON (ou.outcome_id = c.current_outcome_id)
					JOIN operator_skill as os ON (os.operator_id = '$operator_id' AND os.outcome_type_id = ou.outcome_type_id)
					LEFT 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))
					WHERE c.sortorder IS NOT NULL
					AND c.current_operator_id IS NULL
					AND ((apn.require_operator_id IS NULL) OR (apn.require_operator_id = '$operator_id'))
					ORDER BY c.sortorder ASC
					LIMIT 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
					FROM `case` as c
					LEFT JOIN `call` as a on (a.call_id = c.last_call_id)
					JOIN (sample as s, sample_import as si) on (s.sample_id = c.sample_id and si.sample_import_id = s.import_id)
					JOIN (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)
					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))
					LEFT 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')))
					LEFT 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))
					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)
					LEFT JOIN questionnaire_sample_exclude_priority AS qsep ON (qsep.questionnaire_id = c.questionnaire_id AND qsep.sample_id = c.sample_id)
					LEFT JOIN case_availability AS casa ON (casa.case_id = c.case_id)
					LEFT JOIN availability AS ava ON (ava.availability_group_id = casa.availability_group_id)
					LEFT JOIN questionnaire_timeslot AS qast ON (qast.questionnaire_id = c.questionnaire_id)
					LEFT JOIN questionnaire_sample_timeslot AS qasts ON (qasts.questionnaire_id = c.questionnaire_id AND qasts.sample_import_id = si.sample_import_id)
					JOIN operator_skill as os on (os.operator_id = op.operator_id and os.outcome_type_id = ou.outcome_type_id)
					WHERE c.current_operator_id IS NULL
					AND ((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  ))
      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)))
          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)))
		            AND ((a.call_id is NULL) OR (a.end < CONVERT_TZ(DATE_SUB(NOW(), INTERVAL ou.default_delay_minutes MINUTE),'System','UTC')))
					AND ap.case_id is NULL
					AND ((qsep.questionnaire_id is NULL) OR (qsep.exclude = 0))
					AND !(q.restrict_work_shifts = 1 AND sh.shift_id IS NULL AND os.outcome_type_id != 2)
					AND !(si.call_restrict = 1 AND cr.day_of_week IS NULL AND os.outcome_type_id != 2)
					AND ((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))
					AND ((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))
					AND ((apn.require_operator_id IS NULL) OR (apn.require_operator_id = '$operator_id'))
					AND (SELECT count(*) FROM `questionnaire_sample_quota` WHERE questionnaire_id = c.questionnaire_id AND sample_import_id = s.import_id AND quota_reached = 1) = 0
					ORDER 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
					LIMIT 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
						FROM questionnaire_sample_exclude_priority as qsep
						JOIN operator_skill as os ON (os.operator_id = '$operator_id' AND os.outcome_type_id = 1)
						JOIN operator_questionnaire AS oq ON (oq.operator_id = '$operator_id' AND oq.questionnaire_id = qsep.questionnaire_id)
						JOIN questionnaire as q ON (q.questionnaire_id = qsep.questionnaire_id and q.enabled = 1)
						LEFT JOIN `case` as c ON (c.sample_id = qsep.sample_id AND c.questionnaire_id = qsep.questionnaire_id)
						WHERE qsep.sortorder IS NOT NULL 
						AND c.case_id IS NULL
						ORDER BY qsep.sortorder ASC
						LIMIT 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
						FROM sample as s
						JOIN (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)
						LEFT JOIN `case` as c on (c.sample_id = s.sample_id and c.questionnaire_id = qs.questionnaire_id)
						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)
						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))
						LEFT JOIN questionnaire_sample_exclude_priority AS qsep ON (qsep.questionnaire_id = qs.questionnaire_id AND qsep.sample_id = s.sample_id)
						WHERE c.case_id is NULL
						AND ((qsep.questionnaire_id IS NULL) OR (qsep.exclude = 0))
						AND !(q.restrict_work_shifts = 1 AND sh.shift_id IS NULL)
						AND !(si.call_restrict = 1 AND cr.day_of_week IS NULL)
						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
						ORDER BY qsep.priority DESC, CONVERT_TZ(NOW(), 'System' , s.Time_zone_name) DESC, rand() * qs.random_select, s.sample_id
						LIMIT 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`
					SET current_operator_id = '$operator_id'
					WHERE current_operator_id IS NULL
					AND 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;

}

?>