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(NOW() , INTERVAL -1 DAY )");
//find tickets assigned to this user within last 24 hours
while ($aticket = $ftickets->fetch_assoc()) {
// echo "a ticket is being looked at from kimora hideki";
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) 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) order by assigned_date ASC ";
}
$possibleMentors = $dbconnect->query($sql);
//if there are no available mentors on the domain associated with the ticket gives the ticket to the admin for
// manual reassignment
if ($possibleMentors->num_rows <= 0) {
// echo "no possible mentors should assign tickets to admin";
$ticketSubs = $ticketSubs . $aticket["subject"] . ",\n";
$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"]);
//no possible mentor found assign to admin for manual assign.
} else {
$assigned = 0;
while ($aMentor = $possibleMentors->fetch_assoc()) {
// echo"going through posssible mentors";
$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"]) {
// echo"this mentor can be assigned new 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"]);
$assigned = 1;
// echo"assinged new ticket to mentor";
break;
}
} else {
//not registered as having a max cket.
// echo"mentor available not on assigned 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"]);
$assigned = 1;
break;
}
}
if ($assigned != 1) {
$ticketSubs = $ticketSubs . $aticket["subject"] . ",\n";
$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
}
}
$ticketSubs = $ticketSubs . $aticket["subject"] . ",\n ";
// 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);
}
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);
}