function checkPriorityElapseTickets()
{
$dbconnect = establishDBConnection();
$prio = $dbconnect->query("Select * FROM priority");
while ($prios = $prio->fetch_assoc()) {
switch ($prios["id"]) {
case 1:
$high = $prios["reassignHours"] * -1;
break;
case 2:
$med = $prios["reassignHours"] * -1;
break;
case 3:
$low = $prios["reassignHours"] * -1;
break;
}
}
$ticketr = $dbconnect->query("Select * FROM ticket t where (status != 'Close' and status != 'Reject' and assign_user_id != 5) AND ((priority_id = 1 AND assigned_date <= DATE_ADD(NOW(), INTERVAL -1 MINUTE)) OR (priority_id = 2 AND assigned_date <= DATE_ADD(NOW(), INTERVAL {$med} HOUR)) OR (priority_id = 3 AND assigned_date <= DATE_ADD(NOW(), INTERVAL {$low} HOUR))) AND id NOT IN (SELECT ticket_id as id FROM ticket_events where event_type_id = 5 and event_performed_by_user_id = t.assign_user_id) AND not exists (Select null from (video_conference inner join vc_invitation on id = videoconference_id) where t.assign_user_id = moderator_id and t.creator_user_id = invitee_id and subject like CONCAT(t.subject,' - Ticket #',t.id)) ");
//select all tickets without a ticket event 5 or MAYBE 8 (ask juan) over their respective priorities VERY COMPLICATED SQL query
// reassign tickets
if ($ticketr->num_rows > 0) {
while ($aticket = $ticketr->fetch_assoc()) {
$mentorToDeleteSHOW = $aticket["assign_user_id"];
$ticketToDeleteSHOW = $aticket["id"];
$toManyReassign = $dbconnect->query("SELECT count(ticket_id) as count from previous_mentors where ticket_id = " . $aticket["id"]);
if ($toManyReassign->num_rows > 0) {
$reassigns = $toManyReassign->fetch_assoc();
if ($reassigns["count"] >= 3) {
$mentor = $dbconnect->query("Select * from user WHERE id = " . $aticket["assign_user_id"]);
$aMentor = $mentor->fetch_assoc();
sendTicketCancelOutOfTime($aMentor["email"], $aticket["subject"]);
$dbconnect->query("insert into ticket_events (event_type_id, ticket_id, event_recorded_date, old_value, new_value, comment, event_performed_by_user_id) values (10, " . $aticket["id"] . ", NOW(), " . $aticket["assign_user_id"] . ", 5, null, 5)");
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = 5 WHERE id = " . $aticket["id"]);
//give to admin for manual reassign
continue;
}
}
$mentor = $dbconnect->query("Select * from user WHERE id = " . $aticket["assign_user_id"]);
$aMentor = $mentor->fetch_assoc();
sendTicketCancelOutOfTime($aMentor["email"], $aticket["subject"]);
$dbconnect->query("INSERT INTO previous_mentors (user_id, ticket_id) VALUES(" . $aMentor["id"] . ", " . $aticket["id"] . ")");
if (!is_null($aticket["subdomain_id"])) {
$sql = "SELECT * FROM user_domain left join (select assign_user_id, assigned_date from (select * from ticket order by assigned_date desc)x group by assign_user_id)x on assign_user_id = user_id WHERE domain_id = " . $aticket["domain_id"] . " AND subdomain_id = " . $aticket["subdomain_id"] . " AND tier_team = 1 AND user_id not in (select userID as user_id from away_mentor) AND user_id not in (select user_id as user_id from previous_mentors where ticket_id = " . $aticket["id"] . ") order by assigned_date ASC ";
} else {
$sql = "SELECT * FROM user_domain left join (select assign_user_id, assigned_date from (select * from ticket order by assigned_date desc)x group by assign_user_id)x on assign_user_id = user_id WHERE domain_id = " . $aticket["domain_id"] . " AND tier_team = 1 AND user_id not in (select userID as user_id from away_mentor) AND user_id not in (select user_id as user_id from previous_mentors where ticket_id = " . $aticket["id"] . ") order by assigned_date ASC ";
}
$possibleMentors = $dbconnect->query($sql);
if ($possibleMentors->num_rows <= 0) {
$dbconnect->query("insert into ticket_events (event_type_id, ticket_id, event_recorded_date, old_value, new_value, comment, event_performed_by_user_id) values (10, " . $aticket["id"] . ", NOW(), " . $aticket["assign_user_id"] . ", 5, null, 5)");
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = 5 WHERE id = " . $aticket["id"]);
//give to admin for manual reassign
} else {
$assinged = 0;
while ($aMentor = $possibleMentors->fetch_assoc()) {
$count1 = $dbconnect->query("SELECT COUNT(id) as `id` FROM ticket WHERE assign_user_id = " . $aMentor["user_id"]);
$adomainMentor1 = $dbconnect->query("SELECT * FROM domain_mentor WHERE user_id = " . $aMentor["user_id"]);
$count = $count1->fetch_assoc();
$adomainMentor = $adomainMentor1->fetch_assoc();
if ($adomainMentor) {
if ($count['id'] < $adomainMentor["max_tickets"]) {
$dbconnect->query("insert into ticket_events (event_type_id, ticket_id, event_recorded_date, old_value, new_value, comment, event_performed_by_user_id) values (10, " . $aticket["id"] . ", NOW(), " . $aticket["assign_user_id"] . ", " . $aMentor["user_id"] . ", null, 5)");
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = " . $aMentor["user_id"] . " WHERE id = " . $aticket["id"]);
$mentorb1 = $dbconnect->query("SELECT * FROM user WHERE id = " . $aMentor["user_id"]);
$mentorb = $mentorb1->fetch_assoc();
sendTicketReassignment($mentorb["email"], $aticket["subject"]);
$assinged = 1;
break;
}
} else {
//not registered as having a max ticket.
$dbconnect->query("insert into ticket_events (event_type_id, ticket_id, event_recorded_date, old_value, new_value, comment, event_performed_by_user_id) values (10, " . $aticket["id"] . ", NOW(), " . $aticket["assign_user_id"] . ", " . $aMentor["user_id"] . ", null, 5)");
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = " . $aMentor["user_id"] . " WHERE id = " . $aticket["id"]);
$mentorb1 = $dbconnect->query("SELECT * FROM user WHERE id = " . $aMentor["user_id"]);
$mentorb = $mentorb1->fetch_assoc();
sendTicketReassignment($mentorb["email"], $aticket["subject"]);
$assinged = 1;
break;
}
}
if ($assinged != 1) {
$dbconnect->query("insert into ticket_events (event_type_id, ticket_id, event_recorded_date, old_value, new_value, comment, event_performed_by_user_id) values (10, " . $aticket["id"] . ", NOW(), " . $aticket["assign_user_id"] . ", 5, null, 5)");
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = 5 WHERE id = " . $aticket["id"]);
//give to admin for manual reassign
}
}
$dbconnect->query("DELETE FROM previous_mentors where user_id = {$mentorToDeleteSHOW} and ticket_id = {$ticketToDeleteSHOW}");
// echo "a went through entire thing ticket should be reassigned\n";
}
}
}
function setAsAway($user_Id)
{
$dbconnect = establishDBConnection();
$dbconnect->query("INSERT INTO away_mentor (userID, tiStamp) VALUES ({$user_Id}, NOW())");
$ticketSubs = "";
$ftickets = $dbconnect->query("SELECT * FROM ticket WHERE assign_user_id = {$user_Id} AND assigned_date >= DATE_ADD(CURRENT_DATE , INTERVAL -1 DAY )");
//find tickets assigned to this user within last 24 hours
while ($aticket = $ftickets->fetch_assoc()) {
//reassign the tickets
if (!is_null($aticket["subdomain_id"])) {
$sql = "SELECT * FROM user_domain WHERE domain_id = " . $aticket["domain_id"] . " AND subdomain_id = " . $aticket["subdomain_id"] . " AND tier_team = 1 AND user_id not in (select userID as user_id from away_mentor) ";
//$possibleMentors = $dbconnect->query("SELECT * FROM user_domain WHERE domain_id = " . $aticket["domain_id"] . " AND subdomain_id = " . $aticket["subdomain_id"] . "AND tier_team = 1 AND user_id not in (select userID as user_id from away_mentor) ");
} else {
$sql = "SELECT * FROM user_domain WHERE domain_id = " . $aticket["domain_id"] . " AND tier_team = 1 AND user_id not in (select userID as user_id from away_mentor) ";
//$possibleMentors = $dbconnect->query("SELECT * FROM user_domain WHERE domain_id = " . $aticket["domain_id"] . " AND tier_team = 1 AND user_id not in (select userID as user_id from away_mentor) ");
}
echo $sql;
$possibleMentors = $dbconnect->query($sql);
if ($possibleMentors->num_rows < 0) {
echo "no result";
}
while ($aMentor = $possibleMentors->fetch_assoc()) {
$count1 = $dbconnect->query("SELECT COUNT(id) as `id` FROM ticket WHERE assign_user_id = " . $aMentor["user_id"]);
$adomainMentor1 = $dbconnect->query("SELECT * FROM domain_mentor WHERE user_id = " . $aMentor["user_id"]);
$count = $count1->fetch_assoc();
$adomainMentor = $adomainMentor1->fetch_assoc();
if ($adomainMentor) {
if ($count['id'] < $adomainMentor["max_tickets"]) {
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = " . $aMentor["user_id"] . " WHERE id = " . $aticket["id"]);
$mentorb1 = $dbconnect->query("SELECT * FROM user WHERE id = " . $aMentor["user_id"]);
$mentorb = $mentorb1->fetch_assoc();
sendTicketReassignment($mentorb["email"], $aticket["subject"]);
}
} else {
//not registered as having a max ticket.
$dbconnect->query("UPDATE ticket SET assigned_date = NOW(), assign_user_id = " . $aMentor["user_id"] . " WHERE id = " . $aticket["id"]);
$mentorb1 = $dbconnect->query("SELECT * FROM user WHERE id = " . $aMentor["user_id"]);
$mentorb = $mentorb1->fetch_assoc();
sendTicketReassignment($mentorb["email"], $aticket["subject"]);
}
}
$ticketSubs = $ticketSubs . $aticket["subject"] . ", ";
// do this outside the loop $awayMent = User::model()->findAllBySql("SELECT * FROM user WHERE id =:user_Id", array(":user_id"=>$user_Id));
// foreach ($awayMent as $bawayMent) {
// User::model()->sendEmailTicketCancelOutOfOffice($bawayMent->fname . " " . $bawayMent - lname, $bawayMent->email, $aticket->subject);
//}
}
$mentor2 = $dbconnect->query("SELECT * FROM user WHERE id = {$user_Id}");
$mentor = $mentor2->fetch_assoc();
sendTicketCancelEmail($mentor["email"], $ticketSubs);
}