function sendTicketReassignment()
{
$dbConnect = establishDBConnection();
$query = $dbConnect->query("SELECT * from ticket where id = 87")->fetch_assoc();
$ticket_id = $query["id"];
$subjectl = $query["subject"];
$toEmail = "*****@*****.**";
$link = "http://cp-dev.cis.fiu.edu/coplat/index.php/ticket/view/" . $ticket_id;
$subject = "Ticket Assigned";
$subjectClick = "<a href='" . $link . "'>" . $subjectl . "</a>\n\n";
$body = "Collaborative Platform has assigned you a new ticket:\n\n" . $subjectClick . "\n\nthat was previously assigned to another mentor.\n Thank you for Making Collaborative Platform Great";
$headers = 'From: Collaborative Platform <*****@*****.**>' . "\r\n" . 'Content-type: text/html; charset=utf-8' . "\r\n" . 'Reply-To: fiucoplat@cp-dev.cs.fiu.edu' . "\r\n" . 'X-Mailer: PHP/' . phpversion();
echo "subject click " . $subjectClick . "\n";
echo "link " . $link . "\n";
//send the email using IMAP
if ($a = mail($toEmail, $subject, $body, $headers)) {
echo "Email sent 3!<br />";
} else {
echo "email not sent\n";
}
}
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);
}
