Home Wiki Blog Support Contact Us
› ›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Adding Organization Column to Open Ticket Overview

motodave452motodave452
in Mods and Customizations
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?
Tagged:

Comments

  • ntozierntozier
    The user's organization id is stored in the *_user table.  You would then use that id to look up the Org name in *_organization in the Name column.
  • motodave452motodave452
    I think I understand what you are saying.  So say I have this td in my /staff/tickets.inc.php file that will display the name of the organization specified on the ticket.  Is there already an existing specification for the following highlighted fields or will I need to do some coding in the class.ticket.php file?

    <td><?php echo (isset($row['org_name'])) ? $row['org_name'] : '&nbsp;';?></td>

  • ntozierntozier
    Tickets do not have organizations.  Users do.

    I imagine that you would have to write some code to grab the users org id, and then look it up.

  • motodave452motodave452
    This is my qselect statement, do you know how I would modify that to get the org name and then how would I format my td to display the name in my column?

    $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';

  • motodave452motodave452
    I know there is a getOwner()->getOrganization() function I am just confused how I would set that to a variable in the class.ticket.php file and then access that in the tickets.ini.php file to display in my td. I feel like im on the right track im just new to php lol
  • motodave452motodave452
    The part I am having trouble with is how to declare a variable in class.ticket.php and set this variable to getUser()->getOrganization(), then accessing this variable in my tickets.inc.php to display it in my table
  • motodave452motodave452
    edited August 2014
    $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'
          .' ,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?
  • ntozierntozier
    try replacing:
    ' LEFT JOIN '.ORGANIZATION_TABLE.' organization ON user.org_id = org.org_name'

    with
    ' LEFT JOIN '.ORGANIZATION_TABLE.' organization ON user.org_id = org.id'
  • motodave452motodave452
    Thank you very much.  Another thing I changed was 'organization.name as org_name' in the select statement because just having 'name' conflicted with 'user.name' if that makes sense.  I wasn't able to get a handle to my organization name.  I will comment the changes that I made to this file to get it to work soon so that others who are looking to do the same thing will be able to do so more easily.  I appreciate the help!
  • motodave452motodave452
    I was wondering is there any way to access the organization field that is a part of the newly created ticket.  Such as the organization that the ticket was created for and not necessarily the organization of the user who created it?  I think this is more where I wanted to go with this but I am not sure where this organization data is located within the database.
  • motodave452motodave452
    I know it is part of a form I'm just not sure if there is an easy way to accomplish this.
  • motodave452motodave452
    edited August 2014
    Would be awesome to have the organization that the ticket was created for as a value in the ost_ticket database for future releases.  I don't understand why this is part of a dynamically added form.
  • motodave452motodave452
    edited August 2014
    SOLUTION:

    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!
  • motodave452motodave452
    edited August 2014
    FULL SOLUTION TO ADD ORGANIZATION COLUMN TO MAIN STAFF TICKET VIEW:

    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'] : '&nbsp';?></td>


    If you want to add the organization that the ticket was created for:


    <td><?php echo (isset($row['ticket_org'])) ? $row['ticket_org'] : '&nbsp';?></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:&nbsp;
                <a id="selectAll" href="#ckb">All</a>&nbsp;&nbsp;
                <a id="selectNone" href="#ckb">None</a>&nbsp;&nbsp;
                <a id="selectToggle" href="#ckb">Toggle</a>&nbsp;&nbsp;

    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!
  • motodave452motodave452
    I forgot to mention that the above changes all take place in the /staff directory in the file tickets.inc.php
  • jnaveenjjnaveenj
    Is there any way to read some other fields of an organization other than the name. If possible what should I use instead of org.id. For example I would like to access the phone number stored under organization(not under User). I also have a custom form in which I have other fields also that I would like to access. Or is there anyway to look into the fields of ORGANIZATION_TABLE. It will be great if someone can help me on this.
  • jerrejerre
    edited January 2015
    Great article. Used this with some minor modifications in 1.9.5.1.


  • jerrejerre
    I was asked for the modified file for 1.9.5.1, so here it is for who needs it.
    Replace in /include/staff/

    Kind regards
  • meadejemeadeje
    This works great. I'm using it with 1.9.12 and it seems to be ok. 
  • haldaaahaldaaa
    I did the same as you do but it didnt work :(

    I try :   <td><?php echo $ticket->getOwner()->getOrganization()  ;?></td>    and it work !

    But a great thanks for this tutoriel, it help me a lot


  • stacey_richardsstacey_richards
    Thanks so much for all your hard work motodave452. I've just applied your changes to v1.9.14 (8b927a0) and it works great.
  • R6AR6A
    Could you please post your changes, which you made and the filenames? I'm just working on the implementation of that mod in Version 1.9.14, too.

    Thank you very much in advance.
  • kiriboolkiribool
    Anybody trying add organization field to osticket v 1.10?
  • kiriboolkiribool
    edited January 2017
    If you need this feature, use branch feature/custom-queues
    https://github.com/greezybacon/osTicket-1.8/tree/feature/custom-queues
  • dan_clementsdan_clements
    Hey, just wondering if anyone has done this on 1.10?
    Kiribool- I wasn't sure if your previous post was in relation to 1.10 or the current discussion/1.9?

    Thanks
  • ntozierntozier
    This thread is from 2014.
    Closing zombie thread.
This discussion has been closed.