Changing the Automaticlaly generated tracking ID

[Tinydan]

Script URL:
Version of script: 2.5.2
Hosting company:
URL of phpinfo.php:
URL of session_test.php:
What terms did you try when SEARCHING for a solution:
Tracking, ID

Write your message below:

Hi Klemen,

I'm currently trying to set up an instance of hesk for a customer service department. They already have their own system of assigning tracking ID's to tickets. Their ID's are made up of three different pieces of information: the year, the month and the ticket number. For example 13L031 would be 2013(13) December(L) ticket 31 (031). This allows them to garner some information on the ticket just from the tracking ID.

In my head during ticket generation the system would need to find the year, find the month and its corresponding letter code and then go to the system, looking at the last ticket from that year and month and then increment that ticket number by one. The problem is I'm not sure how I would go about it.

I understand there would be security risks for customers if they could log in and just increase their ticket number but with our set up the customers cannot access the website and only interact with the system through emails.

How would I implement this into my system? Would it be best to replace the tracking ID or to save it to another field in the system?

Cheers, Dan

[Tinydan]

Ok so I'm getting somewhere. This bit of code I've written gets the short version of the current year, the Month and assigns the appropriate letter to represent that month.

Code: Select all

<?php
$trackingID ='';
$year = date("y");
$trackingID.= $year;
$month = date("n");

switch($month){
	case 1:
		$monthCode ="A";
		break;
	case 2:
		$monthCode ="B";
		break;
	case 3:
		$monthCode ="C";
		break;
	case 4:
		$monthCode ="D";
		break;
	case 5:
		$monthCode ="E";
		break;
	case 6:
		$monthCode ="F";
		break;
	case 7:
		$monthCode ="G";
		break;
	case 8:
		$monthCode ="H";
		break;
	case 9:
		$monthCode ="I";
		break;
	case 10:
		$monthCode ="J";
		break;
	case 11:
		$monthCode ="K";
		break;
	case 12:
		$monthCode ="L";
		break;
	}
$trackingID.= $monthCode;
$trackingID.= 1;
echo $trackingID;
?>

I'm just looking at how I can make it so I grab the current tickets associated with the month, assign them a number that represents an incremental counter for that month, grab the last number and then increase it by one.

[Klemen]

A simple approach would be to select maximum and minimum ticket sequential ID in current month, get the difference and add +1 to get the next monthly ID.

SQL could be something like

Code: Select all

SELECT MIN(`id`) as `first`, MAX(`id`) AS `last` FROM `hesk_tickets` WHERE YEAR(`dt`) =YEAR(CURDATE()) AND MONTH(`dt`) = MONTH(CURDATE())

Get the two values into $first and $last so you can use something like:

Code: Select all

$monthSequential = intval($last) - intval($first) + 1;
$trackingID.= str_pad($monthSequential, 3 , "0", STR_PAD_LEFT);

The problem would be if the last ticket is deleted, you would get a new one with the same tracking ID as the deleted one.


If that's a problem, the most reliable way would probably be to store last ID and current month into a separate table. If month changed, you start with 1, if not use the stored ID updated by 1.

In this approach you would have to update the last ID in the custom table every time a new ticket is generated.