Tag Archives: osTicket

osTicket 1.9.4 –Easy Custom Column in Ticket View

By popular demand, I have update the instructions for the Easy Custom Column for osTicket 1.9.4 for form lists.

What does this allow you to do?
It allows you to show a custom form list in your Agents Ticket Queue. This is rather helpful for when you have a list of items relating to your business and you want to be able to see it.

I have only tested this code with lists.

The full code can be found here:

https://gist.github.com/a-bennett/6febddc9e328151dc99a

For those who are interested in doing it themselves (or maybe have already edited there tickets.inc.php file) you can see the changes between the original file and the updated here:

https://gist.github.com/a-bennett/6febddc9e328151dc99a/revisions

How do I find the form ID?

  1.   By looking at the table _form_field and finding your form object by looking down the list of the labels, then once you have found it taking the ID from that row.
  2. If you know how to inspect an web element, (using web inspector, firebug, etc) then you can try the following:
    In the admin panel go to “Manage” -> “Forms” -> click on your form name.
    Inspect the input field of the name of your item mine came up like this:
    <input type=”text” size=”32″ name=”label-17″ value=”Business”>
    Then take the number after the “label-X” so mine is 17.

For those interested here is a break down:

At the top of the file add the following two lines:
$ab_list[‘field_id’] = “XX”; //ID from from _form_field table
$ab_list[‘heading’] = “XXXXXXX”; //Name displayed to front end users.

~Line 169:
Update the $sortOptions array withe the following:

$sortOptions=array(‘date’=>’effective_date’,’ID’=>’ticket.`number`*1′,
‘pri’=>’pri.priority_urgency’,’name’=>’user.name’,’subj’=>’cdata.subject’,
‘status’=>’status.name’,’assignee’=>’assigned’,’staff’=>’staff’,
‘dept’=>’dept.dept_name’,
‘custom1’=>’Custom1′); //AB Added

~Line 253
Append to the end of the $qselect. variable the following: (remember to move the ;)
.’ ,cdata.’.$CustomList1Name.’ as Custom1′; //AB – Custom 1

~Line 262
Add the following to the $qfrom variable straight after the line:

.’ LEFT JOIN ‘.TABLE_PREFIX.’ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) ‘
.’ LEFT JOIN ‘.TABLE_PREFIX.’form_entry_values fentry_val ON (cdata.field_’.$ab_list[“field_id”].’ = fentry_val.entry_id) AND fentry_val.field_id = ‘.$ab_list[“field_id”] //AB We need to link to the form values as of 1.9.4

~Line 412

Add in the following header row just above the </tr>.

<!– AB – Custom Col 1 –>
<th width=”60″ >
<a <?php echo $custom1_sort; ?> href=”tickets.php?sort=custom1&order=<?php echo $negorder; ?><?php echo $qstr; ?>”
title=”Sort By <?php echo $CustomList1Desc; ?> <?php echo $negorder; ?>”><?php echo $CustomList1Desc; ?></a></th>
<!– AB – Custom Col 1 -–>

 

~Line 498

Add in the data row just about the </tr>
<!– AB – Custom Col 1 –>
<td nowrap>&nbsp;<?php echo $row[‘Custom1’]; ?>&nbsp;</td>
<!– AB – Custom Col 1 –>

~ Line 511

Finally update the footer colspan to make everything balanced.

<td colspan=”8″>

osTicket 1.9.4 – Show Due Date Column

Andrew (another one) got in contact with me asking if I could help him to how to show an extra column in osTicket 1.9.4, he tired to follow my previous post without much luck (after rereading it, I’m surprised if that’s ever helped anyone!).

He wanted the ability to see and sort by a “Due Date” column. Well it turns out to be a really easy feature to add.

Here is a GitHub Gist of the modified tickets.inc.php

Instructions for use:

  1. Backup /[osticket-install]/include/staff/tickets.inc.php
  2. Download the following file and copy it to /[osticket-install]/include/staff/tickets.inc.php
  3. Test! (If it break badly, I’m sorry, please restore your backup file).

As a side note, it also got me to finally setup a github account!

Here is the step by step instructions:

  1. Update the sort options ~Line 170.Effectively what we are doing here is adding the ‘duedate’=>’duedate’ to the array. So replace $sortOptions with below:
    [gist id = “c66ef9d90bdcbf874685” file = “step-1.php” width = “90%”]
  2. Add the heading to the table ~Line 374
    Just add the following line in after the closing </th> tag for the line <a <?php echo $date_sort; ?>………
    [gist id = “c66ef9d90bdcbf874685” file = “step-2.php” width = “90%”]
  3. Add the data row to the table ~ Line 468
    After the <td align=”center” nowrap><?php echo Format::db_datetime($row[‘effective_date’]); ?></td> add:
    [gist id = “c66ef9d90bdcbf874685” file = “step-3.php” width = “90%”]
  4. Finally fix up the footer ~ Line 506
    Change <td colspan=”7″> to:
    [gist id = “c66ef9d90bdcbf874685” file = “step-4.php” width = “90%]
  5. Thats it!

Don’t break it!

 

osTicket 1.9.2 – Easy Custom Column in Ticket View

Dec 14 Update:While these rough instructions can be roughly used in osTicket 1.9.4, it doesn’t return the value from a list, just an ID (Which on a technical level is better in the long run, just makes this process a little harder).
I’m working on fixing this and will post the info once I’ve had a chance to work it out.
New instructions for 1.9.4 can be found here: https://www.andrewbennett.com.au/2014/12/osticket-1-9-4-easy-custom-column-in-ticket-view/

TL;DR (I rushed these notes for myself).

File to edit: /include/staff/tickets.inc.php

1) Added To the top to make things easier. (line 2-3)
The Name field (currently “field_17” ) needs to be worked out from the database by looking at the [tbl_prefix]_ticket__cdata
The Desc field (Currently “Sample 1”) is what your users will see as the column heading.

$CustomList1Name = “field_17”;
$CustomList1Desc = “Sample 1”;

2) Add custom1 to allow sorting. (~Line 157)

$sortOptions=array(‘date’=>’effective_date’,’ID’=>’ticket.`number`’,
‘pri’=>’pri.priority_urgency’,’name’=>’user.name’,’subj’=>’cdata.subject’,
‘status’=>’ticket.status’,’assignee’=>’assigned’,’staff’=>’staff’,
‘dept’=>’dept.dept_name’,
‘custom1’=>’Custom1’); //AB Added

3) Edit the last two lines, frst remove the ; after “pri.priority_color'” then add the last line  (~Line 243)
After line:

$qselect.=’ ,IF(ticket.duedate IS NULL,IF(sla.id IS NULL, NULL, DATE_ADD(ticket.created, INTERVAL sla.grace_period HOUR)), ticket.duedate) as duedate ‘
.’ ,CAST(GREATEST(IFNULL(ticket.lastmessage, 0), IFNULL(ticket.closed, 0), IFNULL(ticket.reopened, 0), ticket.created) as datetime) as effective_date ‘
.’ ,CONCAT_WS(” “, staff.firstname, staff.lastname) as staff, team.name as team ‘
.’ ,IF(staff.staff_id IS NULL,team.name,CONCAT_WS(” “, staff.lastname, staff.firstname)) as assigned ‘
.’ ,IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(” / “, ptopic.topic, topic.topic)) as helptopic ‘
.’ ,cdata.priority_id, cdata.subject, pri.priority_desc, pri.priority_color’
.’ ,cdata.’.$CustomList1Name.’ as Custom1 ‘ //AB – Custom 1
;

4) Add Column Heading (~ Line 337)

<!– AB – Custom Col 1 –>
<th width=”60″>
<a <?php echo $custom1_sort; ?> href=”tickets.php?sort=custom1&order=<?php echo $negorder; ?><?php echo $qstr; ?>”
title=”Sort By <?php echo $CustomList1Desc; ?> <?php echo $negorder; ?>”><?php echo $CustomList1Desc; ?></a></th>
<!– AB – Custom Col 1 –>

5) Add Column Data (~ Line 435)

<!– AB – Custom Col 1 –>
<td nowrap>&nbsp;<?php echo $row[‘Custom1’]; ?>&nbsp;</td>
<!– AB – Custom Col 1 –>

When I get around to it I will upload the file to github or bitbucket.

Creating a ticket via the osTicket API using PHP & Crontab (cron job)

I feel that there is very little documentation and examples around of how to do this, and it bugged me for more than 10 minutes, so I thought I’d share my findings.

The documentation can currently be found here: https://github.com/osTicket/osTicket-1.7/blob/develop/setup/doc/api.md and here: https://github.com/osTicket/osTicket-1.7/blob/develop/setup/doc/api/tickets.md

A sample script can be found here: https://github.com/osTicket/osTicket-1.7/blob/develop/setup/scripts/rcron.php (which is what I based the following example off).

But I like to be somewhat lazy and copy and paste a script, test to see it works and then go about tweaking it.

#!/usr/bin/php -q
 URL to api/tickets.json e.g http://yourdomain.com/support/api/tickets.json
# key => API's Key (see admin panel on how to generate a key)
#

$config = array(
'url'=>'http://yourdomain.com/support/api/tickets.json',
'key'=>'[long api key goes here]'
);

#pre-checks
function_exists('curl_version') or die('CURL support required');

#set timeout
set_time_limit(30);

#Sample data for the ticket
# See https://github.com/osTicket/osTicket-1.7/blob/develop/setup/doc/api/tickets.md for full list of variables and options that you can pass.
$data = array("alert" => "true",
       "autorespond" => "true",
       "source" => "API",
       "name" => "Angry User",
       "email" => "[email protected]",
       "subject" => "Testing API 3",
       "message" => "MESSAGE HERE"
);
#Convert the above array into json to POST to the API with curl below.
$data_string = json_encode($data);

#curl post
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $config['url']);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $data_string);
curl_setopt($ch, CURLOPT_USERAGENT, 'osTicket API Client v1.7');
curl_setopt($ch, CURLOPT_HEADER, TRUE);
curl_setopt($ch, CURLOPT_HTTPHEADER, array( 'Expect:', 'X-API-Key: '.$config['key']));
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, FALSE);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$result=curl_exec($ch);
curl_close($ch);

if(preg_match('/HTTP\/.* ([0-9]+) .*/', $result, $status) && $status[1] == 200)
exit(0);

echo $result;
exit(1);
?>

 

Then follow your hosts instructions for adding a Cron Job.
Mine looks something like this:

php /home/abennett/public_html/sample-api.php