Menu Close

Agencies Dropdown and Admin UI for osTicket 1.7ST

ntozier

These directions are for adding a database populated drop down field to tickets feature for osTicket v1.7ST. While it will be similar for any current version (RC through ST) it has only been tested on osTicket 1.7ST. Directions for modified osTicket v1.7ST will be similar but the line numbers may be different based on how you have modified your own implementation. These directions require that you already have a working implementation of osTickets v1.7ST. If you need these instructions for 1.6ST please click here.

Before you start following the directions on this or any other mod please be aware that you should always back up your site files and your database.

Step 1 – Modifying the Database

First we are going to create a table to store the information for the extra drop down field that we will be creating. For the purposes of this article we are creating the drop down box will be called “Agencies”. To use another name (such as “Program” or “Company” simply replace every occurrence in these directions of the word “agencies” with what you want to call it.

There are several ways to create the table (command line, phpmyadmin, MySQL Administrator, MySQL Workbench, etc.) so please feel free to use the way that you are most comfortable.

CREATE TABLE `ost_agencies` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `agency` varchar(255) NOT NULL,
  `addDate` datetime NOT NULL,
  `isenabled` tinyint(1) unsigned NOT NULL,
  `editor` varchar(25) default NULL,
  `upDate` datetime default NULL,
  `notes` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `agency` (`agency`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

note1: Please make sure that the ost_ prefix is what you are using for your setup. If you are using a different prefix make sure that you change this everywhere in the instructions.

note2: Please note that if you aren’t using “Agencies” please change all references to “Agencies” or “agency” to what you want it to be. There are 4 references in the above SQL query that should be changed. This same holds true for every instance in every step and the files distributed with this mod.

While we are here lets add one (1) entry into the table that you can change or edit later.

INSERT INTO `ost_agencies` (`id`,`agency`,`addDate`,`isenabled`,`editor`,`upDate`)
VALUES (1,'EMAILED TICKET','2010-09-01 11:23:47',1,'','0000-00-00 00:00:00');

Next we need to add a field to the ost_ticket table that will hold the Agency number.

ALTER TABLE `DATABASENAME`.`ost_ticket` ADD COLUMN `agency` INTEGER 
UNSIGNED NOT NULL DEFAULT 0 AFTER `updated`;

Step 2 – Modifying the files

Next we will be performing a number of edits to different files in the OSTicket directory tree.

Edit /include/client/open.inc.php
at line 45 insert the following:

<!-- start test -->
    <tr>
        <th nowrap >Agency:</th>
        <td>
            <select name=agency>
		<option value=0 selected>--select one--</option>
		<?
		  $agencies= db_query('SELECT id,agency FROM ost_agencies WHERE isenabled=1 ORDER BY agency');
		  while (list($id,$name) = db_fetch_row($agencies)){
 		    $ck=($info['id']==$id)?'selected':''; ?>
		    <option value="<?=$id?>" <?=$ck?>><?=$name?></option>
		  <?}?>
	    </select>
            <font class="error"><b>*</b> <?=$errors['source']?></font>
        </td>
    </tr>
<!-- end test -->

edit /include/client/view.inc.php
at line 36 add:

<tr>
  <th>&nbsp;</th>
  <td>&nbsp;</td>
</tr>

on line 52 add:

<tr>
  <th width="100">Agency:</th>
  <td>
  <?
    // need to take $ticket->getAgency and convert it into its name from the ost_agencies table
    $agencies= db_query('SELECT agency FROM '.TABLE_PREFIX.'agencies WHERE id='.$ticket->getAgency().'');
    $agency = db_fetch_row($agencies);
    ?>
    <?=Format::htmlchars($agency[0]);?>
  </td>
</tr>

edit /include/class.ticket.php
in function load($id=0)
at line 68 add:

.' LEFT JOIN ost_agencies agency ON (ticket.agency=agency.id) '

at line 178 after function getEmail() add:

    function getAgency() {
        return $this->ht['agency'];
    }

at line 1962 remove the ; frmo the end of the line, and add the following on the next line:

.' ,agency='.db_input($vars['agency'])

Since email doesn’t have a “agency” value we’re going to hard code one for now. You may want to create a default value in the database of “Emailed” and change this number to that.

edit /include/class.mailfetch.php
in function createTicket($mid)
at line 382 add:

$vars['agency']="45"; // set default agency for emails

line 298
added

  'agency' => $this->getAgency(),

line 1601
add on next line

  $fields['agency']    = array('type'=>'string',    'required'=>1, 'error'=>'Agency required');

line 1644
remove ; from end of line

line 1645
add

  .' ,agency='.db_input($vars['agency']);

line 1864
add

  $fields['agency']    = array('type'=>'string',    'required'=>1, 'error'=>'Agency required');

Lets add “Agencies” to the Admin panel under “Manage” -> “Agencies”

edit /include/class.nav.php
before “break;”
on line 209 add:

$subnav[]=array('desc'=>'Agencies','href'=>'agencies.php','iconclass'=>'alert-settings');

edit /scp/settings.php

on line 24
change

  'alerts' => 'Alerts and Notices Settings');

to

  'alerts' => 'Alerts and Notices Settings',

add next line:

  'agencies' => 'Agencies');

edit /include/staff/ticket-view.inc.php

at line 142 add:

<tr>
  <th>&nbsp;</th>
  <td>&nbsp;</td>
</tr>

at line 181 add:

<tr>
  <th width="100">Agency: </th>
  <td>
  <?
    // need to take $ticket->getAgency and convert it into its name from the ost_agencies table
    $agencies= db_query('SELECT agency FROM '.TABLE_PREFIX.'agencies WHERE id='.$ticket->getAgency().'');
    $agency = db_fetch_row($agencies); ?>
    <?php echo Format::htmlchars($agency[0]);?>
  </td>
</tr>

edit /include/staff/ticket-open.inc.php
at line 38 add:

<tr>
  <td width="160" class="required">Agency</td>
  <td>
    <select name=agency>
    <option value=0 selected>--select one--
    <?
    $agencies= db_query('SELECT id,agency FROM '.TABLE_PREFIX.'agencies ORDER BY agency');
    while (list($id,$name) = db_fetch_row($agencies)){
      $ck=($info['id']==$id)?'selected':''; ?>
      <option value="<?=$id?>" <?=$ck?>><?=$name?></option>
      <?
    }?>
    </select>
    <font class="error"><b>*</b> <?=$errors['source']?></font>
  </td>
</tr>

edit /include/staff/ticket-edit.inc.php
at line 40 (after the close tr from email)
add the following:

<tr>
  <td align="left">Agency:</td>
  <td>
            
    <select name="agency">	
    <?php
    $agencies= db_query('SELECT id,agency FROM '.TABLE_PREFIX.'agencies ORDER BY agency');
    while (list($id,$name) = db_fetch_row($agencies)){
      echo sprintf('<option value="%d" %s>%s</option>',
      $id, ($info['agency']==$id)?'selected="selected"':'',$name);
    }
    ?>
    </select>
  </td>
</tr>

Attached is the agencies-dropdown-admin-interface.zip which should contain all the necessary files that you should be able to simply drop in place. Instructions are included in the archive and here for those who have already modified their osTicket 1.7ST code files.

Attached File: osticket-mod-agencies-dropdown-admin-interface.zip

Tagged , ,