How to add a drop down field [Part I]

Rikardog

Well, this is by now how i added a new multi-value field

I accept any suggestions. Take in consideration that i'm not a PHP programmer

Let's create a new project field. Associate a project to tickets.

1. Create the “ost_project” table

create table ost_project(

project_id smallint unsigned not null auto_increment unique,

dept_id smallint not null,

project_code smallint,

project_name varchar(50) not null,

isactive bit default 1,

primary key(project_id),

foreign key fkey_dept_id (dept_id) references ost_department);

2. Insert some records

insert into ost_project (dept_id, project_code, project_name)

values (1, 0, "No Project");

insert into ost_project (dept_id, project_code, project_name)

values (1, 14, "P1");

insert into ost_project (dept_id, project_code, project_name)

values (1, 50, "P2");

insert into ost_project (dept_id, project_code, project_name)

values (1, 28, "P3");

3. Link your new table with the “ost_ticket” table

alter table ost_ticket

add column project_id smallint unsigned after staff_id,

add constraint fk_project_id

foreign key idx_fk_project_id (project_id)

references ost_project;

4. Update the new field value into “ost_ticket”, with a default value

update ost_ticket

set project_id = 1

5. Insert the field "default_project_id" into the “ost_config” table to set the default project

alter table ost_config

add column default_project_id tinyint unsigned after default_smtp_id;

update ost_config

set default_project_id = 1;

6. Edit the file “/var/www/html/OsTicket/include/class.config.php”

var $defaultProjectId;

function getDefaultProjectId(){

return $this->config;

}

7. Create the project class “../OsTicket/include/class.project.php”

class Project {

var $project_code;

var $project_id;

var $project_name;

var $dept_id;

     var $info; 
    
    function Project($project_id,$fetch=true){ 
        $this->project_id=$project_id; 
        if($fetch) 
            $this->load(); 
    } 
    function load() { 
        if(!$this->project_id) 
            return false; 
        
        $sql='SELECT * FROM ost_project WHERE project_id='.db_input($this->project_id); 
        if(($res=db_query($sql)) && db_num_rows($res)) { 
            $info=db_fetch_array($res); 
            $this->project_id=$info; 
            $this->project_code=$info; 
            $this->project_name=$info; 
            $this->dept_id=$info; 
            $this->info=$info; 
            return true; 
        } 
        $this->project_id=0; 
        
        return false; 
    } 
  
    function reload() { 
        return $this->load(); 
    } 
    
    function getId(){ 
        return $this->project_id; 
    } 
    
    function getName(){ 
        return $this->project_name; 
    } 
    
    function getCode() { 
        return $this->project_code; 
    } 
    function getInfo() { 
        return $this->info; 
    } 
    function getDeptId() { 
        return $this->dept_id; 
    }

}

.. To be continued

Rikardog

How to add a drop down field

...

8. Edit the class.ticket.php file (I tell you what to add, is up to you to find where)

include_once(INCLUDE_DIR.'class.project.php');

var $project; //Project class

var $project_id; //campo adicional

var $project_name; //campo adicional

$sql =' SELECT ticket.*, topic.topic_id as topicId,lock_id, dept_name, priority_desc, '. ' ticket.project_id, project.project_name '.

' FROM '.TICKET_TABLE.' ticket '.

' LEFT JOIN '.DEPT_TABLE.' dept

ON ticket.dept_id=dept.dept_id '.

' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri

ON ticket.priority_id=pri.priority_id '.

' LEFT JOIN '.TOPIC_TABLE.' topic

ON ticket.topic_id=topic.topic_id '.

' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock

ON ticket.ticket_id=tlock.ticket_id AND '.

' tlock.expire>NOW() '.

' LEFT JOIN ost_project project

ON ticket.project_id=project.project_id '.

' WHERE ticket.ticket_id='.db_input($id);

$this->project_id=$row;

$this->project_name=$row;

function getProjectId() {

return $this->project_id;

}

function getProjectName() {

if($this->project_id && ($project=$this->getProject()))

return $project->getName();

return $this->project_name;

}

function getProject(){

if(!$this->project && $this->project_id)

$this->project = new Project($this->project_id);

return $this->project;

}

function setProjectId($projectId) {

return $this->project_id = $projectId;

}

function setHelpTopic($topicId) {

return $this->helptopic = $topicId;

}

$search = array('/%id/','/%ticket/',

'/%email/','/%name/',

'/%subject/','/%topic/',

'/%phone/','/%status/',

'/%priority/','/%project_id/', /%project_name/,

'/%dept/','/%assigned_staff/',

'/%createdate/','/%duedate/',

'/%closedate/','/%url/');

$replace = array($this->getId(),

$this->getExtId(),

$this->getEmail(),

$this->getName(),

$this->getSubject(),

$this->getHelpTopic(),

$this->getPhoneNumber(),

$this->getStatus(),

$this->getPriority(),

$this->getProjectId(),

$this->getProjectName(),

($dept?$dept->getName():''),

($staff?$staff->getName():''),

Format:($this->getCreateDate()),

Format:($this->getDueDate()),

Format:($this->getCloseDate()),

$cfg->getBaseUrl());

function transfer($deptId) {

global $cfg;

/*

TODO:

1) Figure out what to do when ticket is assigned

Is the assignee allowed to access target dept? (At the moment assignee will have access to the ticket anyways regardless of Dept)

2) Send alerts to new Dept manager/members??

3) Other crap I don't have time to think about at the moment.

*/

$this->setProjectId(NULL);

$this->setHelpTopic(NULL);

return $this->setDeptId($deptId)?true;

}

$fields = array('type'=>'int', 'required'=>0, 'error'=>'Invalid Project');

$project_name='';

if($var && ($project=new Project($var))

&& $project->getId()) {

$project_name=$project->getName();

}

$sql='UPDATE '.TICKET_TABLE.' SET updated=NOW() '.

',email='.db_input($var).

',name='.db_input(Format:($var)).

',subject='.db_input(Format:($var)).

',phone="'.db_input($var,false).'"'.

',phone_ext='.db_input($var?$var).

',priority_id='.db_input($var).

',topic_id='.db_input($var).

',duedate='.($var?

db_input(date('Y-m-d G',Misc:($var.

' '.$var))):'NULL').

',project_id='.db_input($var);

$project_id=$var?$var:

$cfg->getDefaultProjectId();

$fields =

array('type'=>'int',

'required'=>0,

'error'=>'Invalid Project');

$sql= 'INSERT INTO '.TICKET_TABLE.' SET created=NOW() '.

',ticketID='.db_input($extId).

',dept_id='.db_input($deptId).

',topic_id='.db_input($topicId).

',priority_id='.db_input($priorityId).

',email='.db_input($var).

',name='.db_input(Format:($var)).

',subject='.db_input(Format:($var)).

',helptopic='.db_input(Format:($topicDesc)).

',phone="'.db_input($var,false).'"'.

',phone_ext='.db_input($var?$var:'').

',ip_address='.db_input($ipaddress).

',source='.db_input($source).

',project_id='.db_input($var);

9. Edit the /OsTicket/include/client/viewticket.inc.php file, including a new field that allows to visualize the new property value.

<tr>

<th>Project:</th>

<td><?=Format:($ticket->getProjectName())?></td>

</tr>

10. Repeat step 9 on the /OsTicket/include/staff/viewticket.inc.php file

11. Add a drop down field on /OsTicket/include/staff/newticket.inc.php, that matches the new field.

<tr>

<td align="left" ><b>Project:</b></td>

<td>

<select name="project_id">

<option value="" selected>Select Project</option>

<?

$query ='SELECT project_id, project_name '.

'FROM ost_project '.

'WHERE isactive = true '.

'ORDER BY project_name';

$services=db_query($query);

while (list($projectId,$projectName) = db_fetch_row($services)) {

$selected = ($info==$projectId)?'selected':'';?>

<option value="<?=$projectId?>"<?=$selected?>><?=$projectName?>

</option>

<?

}

?>

</select>

</td>

</tr>

12. If you wish to add a new column that shows the new field in the tickets list add into /OsTicket/include/staff/tickets.inc.php

$sortOptions=array('date'=>'ticket.created','ID'=>'ticketID','pri'=>'priority_urgency','dept'=>'dept_name','proj'=>'project_name');

$orderWays=array('DESC'=>'DESC','ASC'=>'ASC');

<option value="proj" <?= $sort == 'proj' ?'selected':''?>>Dept.</option>

$qselect='SELECT DISTINCT

ticket.ticket_id,lock_id,ticketID,ticket.dept_id,

ticket.project_id, ticket.staff_id,subject,

name,email,dept_name, p.project_name '.

',ticket.status,ticket.source,isoverdue, isanswered,ticket.created,pri.* ,

count(attach.attach_id) as attachments ';

$qfrom=' FROM '.TICKET_TABLE.' ticket '.

'LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.

'INNER JOIN ost_project p ON p.project_id=ticket.project_id';

<th width="120">

<a href="tickets.php?sort=proj&order=<?=$negorder?>

<?=$qstr?>" title="Sort By Project <?=$negorder?>">Project</a>

</th>

<td nowrap>

<?=Format:($row,30)?>

</td>

13. Edit the /OsTicket/include/staff/editticket.inc.php file

'projectId' =>$ticket->getProjectId(),

'projectName' => $ticket->getProjectName(),

<?php

$query='SELECT project_id,project_name '.

' FROM ost_project '.

' WHERE isactive = true '.

' AND dept_id = '.db_input($deptId).

' ORDER BY project_name';

echo $query;

$services= db_query($query);

if($services && db_num_rows($services)){ ?>

<tr>

<td align="left" valign="top">Project:</td>

<td>

<select name="project_id">

<option value="0" selected >None</option>

<?

while (list($projectId,$projectName) = db_fetch_row($services)){

$selected = ($info==$projectId)?'selected':'';?>

<option value="<?=$projectId?>"<?=$selected?>><?=$projectName?>

</option>

<?

}?>

</select>

 (optional)<font class="error"> <?=$errors?></font>

</td>

</tr>

<?

}?>

That's pretty much of it... hope it helps !!

Rikardog

Known bugs

These are the things i haven't solved yet...

When i edit my ticket's new field (custom field i mean), it updates to the database ok. But right after i perform the update operation clicking on the "Update Ticket" button, it sends me back to the "ViewTicket.php" page. But it does not show the new value of the field recently updated. I must hit on the "refresh" icon, in order to force the right visualization of the value field.

Looking into the code i suspect, i must edit the tickets.inc.php file, but i haven't found, where to do the changes.

Any ideas??

Thanks in advance...

Other thing i would like to perform is to load the project drop-down-list, according to the value selected on the department drop-down-list. (without reloading the entire page)

I suspect this could be executed with ajax, but i don't know how (easy on me, i'm not a php programmer)

Any ideas??

thofei

Hi,

first thing: Could you do the reading audience a favor and put the code in your previous messages into a PHP or code block? It's the small icon at the right side with PHP in it or the # sign at the top of this Wysiwyg editor. Makes your posts way more readable :)

What you basically need for your preloaded project dropdown list:

- some Javascript functions to use the XMLHttpRequest and to change the innerHTML value of the div that surrounds your secondary dropdown

- a php file that queries your database for the values of the secondary dropdown according to the selection in the first dropdown

I've posted a Mod here to add kind of a Google Search to the Subject field, take a look there as i did that via Ajax and you can possibly modify that to your own needs

Best

axiscars

Where is the Mod you refer too

Where is the google search ajax Mod you refer too, can you post the link please.

ectu

did anybody try the mod? it is exactly what i'm looking for, because i would like to have a dropdown where the customer can select a product. unfortunately it's not possible for me to make it work > the project_id is not transfered to the mysql table.

any input or alternatives?

thanks

Rene88

I have the same problem that i want to at a dropdown Menu.

is ther a easier way or a mod or something?