-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathassigner.php
77 lines (66 loc) · 2.71 KB
/
assigner.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
<?php
require_once("Mail.php");
/**
* Find user with less tickets and assing the new one
*/
function assignTicketToBoredStaff($conOst, $ticket) {
$sqlOkm = "SELECT id, mail FROM okm_assigner WHERE active=1 GROUP BY tickets HAVING tickets=MIN(tickets) LIMIT 1";
$stmtOkm = $conOst->prepare($sqlOkm);
$stmtOkm->execute();
if ($rsOkm = $stmtOkm->fetch()) {
$staffId = $rsOkm['id'];
$staffMail = $rsOkm['mail'];
echo "Assign ticket ".$ticket." to staff ".$staffId." (".$staffMail.")\n";
// Assign the ticket
$sqlOst = "UPDATE ost_ticket SET staff_id=:staff WHERE ticket_id=:ticket";
$stmtOst = $conOst->prepare($sqlOst);
$stmtOst->execute(array('staff' => $staffId, 'ticket' => $ticket));
// Increase ticket count
$sqlOkm = "UPDATE okm_assigner SET tickets=tickets+1 WHERE id=:id";
$stmtOkm = $conOst->prepare($sqlOkm);
$stmtOkm->execute(array('id' => $staffId));
// Send mail
$from = "<[email protected]>"; $to = $staffMail;
$host = "mail.yourdomain.com"; $username = "[email protected]"; $password = "mail-password";
$headers['From'] = $from; $headers['To'] = $to;
$headers['Subject'] = 'New ticket assigned';
$headers['MIME-Version'] = '1.0'; $headers['Content-Type'] = 'text/html; charset=UTF-8';
$smtp = Mail::factory('smtp', array ('host' => $host, 'auth' => true, 'username' => $username, 'password' => $password));
$body = '<html><body>';
$body .= 'New ticket assigned';
$body .= ' <a href="http://support.yourdomain.com/scp/tickets.php?id='.$ticket.'">Ticket #'.$ticket.'</a>';
$body .= '</body></html>';
$mail = $smtp->send($to, $headers, $body);
if (PEAR::isError($mail)) {
die("Mail send error: ".$mail->getMessage());
}
} else {
die("Query did no return any staff member");
}
}
/**
* Assing a ticket to an user
*/
function assignTicket($con, $ticket, $staff) {
$sql = "UPDATE ost_ticket SET staff_id=:staff WHERE ticket_id=:ticket";
$stmt = $con->prepare($sql);
$stmt->execute(array('staff' => $staff, 'ticket' => $ticket));
}
/**
* Obtain unassigned new created tickets
*/
function getUnassignedTickets($con) {
$sql = "SELECT ticket_id, number, dept_id, staff_id, status FROM ost_ticket WHERE status='open' and staff_id=0";
$ret = array();
$stmt = $con->prepare($sql);
$stmt->execute();
while ($rs = $stmt->fetch()) {
$ret[] = $rs['ticket_id'];
}
return $ret;
}
$conOst = new PDO('mysql:host=localhost;dbname=ost_db', 'ost_user', 'ost_password');
foreach (getUnassignedTickets($conOst) as $ticket) {
$staff = assignTicketToBoredStaff($conOst, $ticket);
}
?>