osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go
get it now
Adding Organization Column to Open Ticket Overview
I added a custom form that allows an organization field to be a part of a submitted ticket. Any idea what table this will be stored in or how I can access this data? I have successfully added a Help Topic column so I know how to set it up its just accessing the organization data from the ticket that I am having trouble with. Any ideas on variable names for the organization that is part of the ticket?
Comments
<td><?php echo (isset($row['org_name'])) ? $row['org_name'] : ' ';?></td>
I imagine that you would have to write some code to grab the users org id, and then look it up.
$qselect ='SELECT ticket.ticket_id,tlock.lock_id,ticket.`number`,ticket.dept_id,ticket.staff_id,ticket.team_id '
.' ,user.name'
.' ,email.address as email, dept.dept_name'
.' ,ticket.status,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created '
.' ,topic.topic';
.' ,user.name'
.' ,email.address as email, dept.dept_name'
.' ,ticket.status,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created'
.' ,topic.topic'
.' ,organization.name';
$qfrom=' FROM '.TICKET_TABLE.' ticket '.
' LEFT JOIN '.USER_TABLE.' user ON user.id = ticket.user_id'.
' LEFT JOIN '.USER_EMAIL_TABLE.' email ON user.id = email.user_id'.
' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
' LEFT JOIN '.TOPIC_TABLE.' topic ON ticket.topic_id=topic.topic.id'.
' LEFT JOIN '.ORGANIZATION_TABLE.' organization ON user.org_id = org.org_name';
I feel like I am setting this up to get the organization name correctly but something isn't right with my select and my from statements any ideas?
' LEFT JOIN '.ORGANIZATION_TABLE.' organization ON user.org_id = org.org_name'
with
' LEFT JOIN '.ORGANIZATION_TABLE.' organization ON user.org_id = org.id'
Figured out that you can access the organization that the ticket was created for by adding the following highlighted line around line 235(approx.):
//ADD attachment,priorities, lock and other crap
$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, cdata.organization as ticket_org, pri.priority_desc, pri.priority_color';
$qfrom.=' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON (ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW()
AND tlock.staff_id!='.db_input($thisstaff->getId()).') '
.' LEFT JOIN '.STAFF_TABLE.' staff ON (ticket.staff_id=staff.staff_id) '
.' LEFT JOIN '.TEAM_TABLE.' team ON (ticket.team_id=team.team_id) '
.' LEFT JOIN '.SLA_TABLE.' sla ON (ticket.sla_id=sla.id AND sla.isactive=1) '
.' LEFT JOIN '.TOPIC_TABLE.' topic ON (ticket.topic_id=topic.topic_id) '
.' LEFT JOIN '.TOPIC_TABLE.' ptopic ON (ptopic.topic_id=topic.topic_pid) '
.' LEFT JOIN '.TABLE_PREFIX.'ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) '
.' LEFT JOIN '.PRIORITY_TABLE.' pri ON (pri.priority_id = cdata.priority_id)';
Put ticket_org in your TD and there you go!
Line 151 (estimate): Make sure your sort array looks like this:
$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', 'helptopic'=>'helptopic', 'organization'=>'ticket_org');
Disregard the helptopic portion because I also added a help topic column to my staff ticket view. Add the part highlighted in green. NOTE: ticket_org would be org_name depending on what organization you wish to include in the ticket view. You will see what I mean in the following:
Line 204 (estimate): If you want to add the organization of the user who created the ticket, modify the following qselect and qfrom statements by adding the highlighted portion:
$qselect ='SELECT ticket.ticket_id,tlock.lock_id,ticket.`number`,ticket.dept_id,ticket.staff_id,ticket.team_id '
.' ,user.name, user.org_id'
.' ,email.address as email, dept.dept_name '
.' ,ticket.status,ticket.source,ticket.isoverdue,ticket.isanswered,ticket.created'
.' ,organization.name as org_name';
$qfrom=' FROM '.TICKET_TABLE.' ticket '.
' LEFT JOIN '.USER_TABLE.' user ON user.id = ticket.user_id'.
' LEFT JOIN '.USER_EMAIL_TABLE.' email ON user.id = email.user_id'.
' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
' LEFT JOIN '.ORGANIZATION_TABLE.' organization ON user.org_id = organization.id';
If you want to add the organization that the ticket was created for, modify the following qselect and qfrom statements beginning around line 235 (estimate):
NOTE: If you want Organization to be a part of the New Ticket form, you must add it to the ticket. If you are Admin you can add this custom field to the new ticket screen by going to Manage -> Forms. When you are viewing the list of forms you can click on Ticket Details, and then after Priority Level under the Form Fields section you can add a new field, call it "Organizations", and set the type to choices. Click on config and it will let you enter a list of choices that can be selected when the ticket is created in order to specify what organization the ticket is for.
/ADD attachment,priorities, lock and other crap
$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, cdata.organization as ticket_org, pri.priority_desc, pri.priority_color';
$qfrom.=' LEFT JOIN '.TICKET_LOCK_TABLE.' tlock ON (ticket.ticket_id=tlock.ticket_id AND
tlock.expire>NOW()
AND tlock.staff_id!='.db_input($thisstaff->getId()).') '
.' LEFT JOIN '.STAFF_TABLE.' staff ON (ticket.staff_id=staff.staff_id) '
.' LEFT JOIN '.TEAM_TABLE.' team ON (ticket.team_id=team.team_id) '
.' LEFT JOIN '.SLA_TABLE.' sla ON (ticket.sla_id=sla.id AND sla.isactive=1) '
.' LEFT JOIN '.TOPIC_TABLE.' topic ON (ticket.topic_id=topic.topic_id) '
.' LEFT JOIN '.TOPIC_TABLE.' ptopic ON (ptopic.topic_id=topic.topic_pid) '
.' LEFT JOIN '.TABLE_PREFIX.'ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id) '
.' LEFT JOIN '.PRIORITY_TABLE.' pri ON (pri.priority_id = cdata.priority_id)';
All I did in the above qselect statement was get a handle to the organization name that is associated with that particular ticket by adding the highlighted statement. The qfrom statement already has a handle to the table I want in the database so it is not necessary to modify it.
Next, add your header to the table in the place that you want it. I did mine right after Help Topic which I added right after Subject. The headers start around line 325 (estimate):
<th width="110">
<a <?php echo $org_sort; ?> href="tickets.php?sort=organization&order=<?php echo $negorder; ?>
<?php echo $qstr; ?>" title="Sort By Organization <?php echo $negorder; ?>">Organization</a></th>
Next you will want to add the TD which will be your table data. Make sure this is placed in the same position as your header with regards to the other columns so everything lines up. This TDs start around line 420 (estimate):
If you want to add the organization of the user who created the ticket:
<td><?php echo (isset($row['org_name'])) ? $row['org_name'] : ' ';?></td>
If you want to add the organization that the ticket was created for:
<td><?php echo (isset($row['ticket_org'])) ? $row['ticket_org'] : ' ';?></td>
Next, you will want to change the following line so that it corresponds to the number of columns in your table.
You can find this on line 470 (estimate):
<td colspan="9">
<?php if($res && $num && $thisstaff->canManageTickets()){ ?>
Select:
<a id="selectAll" href="#ckb">All</a>
<a id="selectNone" href="#ckb">None</a>
<a id="selectToggle" href="#ckb">Toggle</a>
This above change makes the footer span all columns. I changed this to 9 because I have 9 columns in my Staff Ticket Overview.
This should be all that you need to successfully add the Organization to the Overview. It is pretty much the same process for adding any column with whatever data you want. Configure the sort array so you can sort based on the data in your column. Get a handle to the table in your database that contains the values you want, add a header, add the data, and increase the footer to span all columns. This is a somewhat complicated process and I've spent several days really getting to know the structure of the code so if I forgot something or left something out please feel free to message me or leave a comment and let me know. I hope this helps other users out in the future and makes it easier to customize this ticket system.
Enjoy!
Replace in /include/staff/
Kind regards
https://github.com/greezybacon/osTicket-1.8/tree/feature/custom-queues