Notification Script for Ticketing System

In Progress Posted 7 years ago Paid on delivery
In Progress Paid on delivery

We are in the process of migrating from one ticketing system to another ticketing system. It's important that customer requests get assigned to our technicians quickly and historically we've had an issue with this. To remedy it, I created a small little script that would retrieve any database entries that did not have an owner field set (aka NULL) and the time (in linux timestamp) that the table entry was created. If there was a greater than 10 minute different between the timestamp in the table entry and the current time, it would send an email notification to a group of people. If the time difference was greater than 20 minutes it would send a notification to another group as an escalation. I set this script on a cron job to run every 2 minutes so that it would get peoples attention over and over until the ticket was assigned.

We're in the process of moving to a new ticketing platform and I am in need of a script that perform a similar task. In this case the database is postgreSQL and the OS is windows instead of linux (so it's a bit outside of my capabilities and I'm sure someone here can write the code much much faster than I can figure it out).

In the new ticketing system there is a table called workorderstates. In that table is a column named ownerid. It appears as simple as retreive all entries where ownerid = NULL (just as I did before). Another table called workorder contains a column called createdtime which we can use to compare current time to created time so we know when to and when not to actually send notifications. A unique id of workorderid ties the two tables together.

I'm including a sample of what I wrote for the old ticketing system just to give an idea of how it worked. I am NOT a coder, so please be gentle. =) I am sure a professional could do a much cleaner job. Also note that this is in PHP (language the old ticketing system is writen in), I'm sure there are better language options for this windows platform. Also, I had to replace the [at] symbols with #'s because it thought I was including contact information.

<?php

$servername = "localhost";

$username = "****";

$password = "****";

$database = "****";

$twenty_email = '***#***.com,***#***.com#***#***.com';

#$thirty_email = '***#***.com,***#***.com,***#***.com,***#***.com,***#***.com';

$thirty_email = '***#***.com,***#***.com,***#***.com';

#$sixty_email = '***#***.com,***#***.com,***#***.com,***#***.com,***#***.com';

$sixty_email = '***#***.com,***#***.com,***#***.com,***#***.com,***#***.com';

$subject = 'Unassigned Ticket';

$message_headers = 'From: ***#***.com' . "\r\n" .

'BCC: ***#***.com' . "\r\n" .

'Reply-To: ***#***.com' . "\r\n" .

'X-Mailer: PHP/' . phpversion();

// Create connection

mysql_connect($servername, $username, $password) or

die("Could not connect: " . mysql_error());

mysql_select_db($database);

$result = mysql_query("SELECT `ticketid` , `ticketmaskid` , `ticketstatusid` , `ownerstaffid`, `subject`, `dateline` FROM `swtickets` WHERE (`departmentid` =2 OR `departmentid` =9) AND `ticketstatusid` =1 AND `ownerstaffid` =0");

//Email for each unassigned ticket

while($row = mysql_fetch_array($result)) {

$time = time();

$twenty = $row[5] + 600;

$thirty = $row[5] + 1200;

$sixty = $row[5] + 1800;

if ($time > $twenty){

$message = "Ticket# $row[1] is older than 10 minutes and has not yet been assigned to a technician.";

mail($twenty_email, $subject, $message, $message_headers);

}else{

}

if ($time > $thirty){

$message = "Ticket# $row[1] is older than 20 minutes and has not yet been assigned to a technician.";

mail($thirty_email, $subject, $message, $message_headers);

}else{

}

if ($time > $sixty){

$message = "Ticket# $row[1] is older than 30 minutes and has not yet been assigned to a technician.";

mail($sixty_email, $subject, $message, $message_headers);

}else{

}

}

?>

Please let me know if you have any questions.

PostgreSQL Windows Server

Project ID: #10955247

About the project

2 proposals Remote project Active 7 years ago

Awarded to:

donarb

My name is Don Arbow. I have been in the software industry for over 30 years, working for various companies and myself. I have been using PHP and PostgreSQL for over 10 years. It looks like you just need a simple sc More

$88 USD in 3 days
(0 Reviews)
0.0