Menu Close

OSTicket 1.6: How to add a db populated drop down and admin interface [version 1.0]

ntozier

OSTicket 1.6 ST – MOD: How to add a db populated drop down and admin interface [version 1.0]

These directions are for adding this feature to OSTicket v1.6ST. While it will be similar for any current version (RC through ST) release it has only been tested on 1.6ST. Directions for modified OSTicket v1.6ST 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 implemtation of OSTickets v1.6ST.

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

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 occurance 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,
  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 referrences in the aboce SQL query that should be changed. This same holds true for every instance in every step and the files distirubuted 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`;

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

edit include/client/open.inc.php
circa line 48 add the following:

    <tr>
     <select name=agency>
     <option value=0 selected>--select one--
      
        <option value="" >
      
     </select>
    </tr>

It is good habit to make sure that this section lines up with the sections before and after it (code indentation). Copying and pasting this will probably not make it line up right. If you fix this is ultimately up to you, but it will not impact how it works either way.

Next edit include/class.ticket.php
circa line 74 add the following:

$this->agency     =$row['agency'];

circa line 141 add the following:

function getAgency(){
 return $this->agency;
}

— OPTIONAL —
If you think that you might want to use %agency in your ticket templates do the following

circa line 429 locate the line

$search = array('/%id/','/%ticket/','/%email/','/%name/','/%subject/',
'/%topic/','/%phone/','/%status/','/%priority/',

and change it to

$search = array('/%id/','/%ticket/','/%email/','/%name/','/%agency/','/%subject/',
'/%topic/','/%phone/','/%status/','/%priority/',

circa line 435 add the following:

$this->getAgency(),

–END OPTIONAL —

circa line 1085 (optional?) add line

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

circa line 1132 add the following:

',agency='.db_input(Format::striptags($var['agency'])).

If you would like agency to be required then on
circa line 1174 add the following: 

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

circa line 1321 add the following:

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

note: make sure that if you add this at the end of the $sql lines that you turn the ; on the line before it into a . or it will cause errors.

Next edit include/class.mailfetch.php
circa line 235 and add the following:

$var['agency']="1";

This means that it will use a default value of 1 for any tickets that are opened via email. This is also the entry that we added to the DB manually earlier in this tutorial.

edit /include/client/viewticket.inc.php
circa line 47 add the following:

<tr>
 <th width="100">Agency:
 <td>
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); ?>
 
 </td>
</tr>

edit /include/staff/newticket.inc.php
circa line 36 add the following:

<tr>
 <td align="left">Agency:
 <td>
  <select name=agency>
  <option value=0 selected>--select one--
  
    <option value="" ></option>
  
  </select>
   <font class="error"><b>*</b> </font>
 </td>
</tr>

edit /include/staff/editticket.inc.php
circa line 11 add the following:

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

circa line 54 add the following:

    
      <tr>
        <td align="left">Agency:
        <td>
            <select name="agency">
              
                    <option value=""  ></option>
              
            </select>
        </td>
       </tr>

edit /include/staff/viewticket.inc.php
circa line 58 add the following:

			<tr>
								<th> </th>
								<td> </td>
						</tr>

circa line 89 add the following:

            <tr>
                <th>Agency:</th>
                <td>
                    getAgency and convert it into its name from the ost_agencies table
 	            $agencies= db_query('SELECT agency FROM ost_agencies WHERE id='.$ticket->getAgency().'');
 	            $agency = db_fetch_row($agencies); ?>
 	            
		</td>
            </tr>

Now let’s create the admin interface to allow addition of, disable/enabling of, and deletion of Agencies in the DB. This part requires that you download the agencies_interface_files.zip attached to this post. It should include the following files:

README
/scp/agency.php
/include/staff/agency_name.inc.php
/include/staff/agency.inc.php

Please put those files in the directories where they belong.

Next edit /include/staff/header.inc.php
circa line 56 add the following

						if($thisuser->isAdmin() && defined('ADMINPAGE')) {?>
						    <li><a href="agency.php" title="Agencies">Agencies </a></li>

It is important to note that this mod is distributed as is. It is not distributed with any warranty of any kind express or implied. If you like this mod please consider donating to my web site.

Here is a link to the additional files mentioned in this article:
MOD-agencies.zip

Tagged , , ,