Howdy, Stranger!

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

In this Discussion

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

[Request] Help Topic in staff open and close ticket

Hi all,

Anyone can guide me how to insert another new column in the open and close ticket list? (scp/ticket.php)

Thank you!!

Comments

  • What do you mean new column? Could you take a screenshot and draw on the image indicating what/where etc. Do you mean a new column of data? (I've been thinking of adding a "Department" list for Admins as seeing all departments tickets can get old)
  • Sorry for making you misunderstanding. Here is the picture and I wanted to make it show the Help Topic around here.

    image
  • leonlun;42417 said:
    I wanted to make it show the Help Topic around here.
    This works:

    Open include/staff/tickets.inc.php
    find:
    title=\"Sort By Subject <?php echo $negorder; ?>\">Subject</a></th>


    Add beneath: (line 357 ish)
    <th width=\"40\">Topic</th>


    Find:
    <?php echo $row['attachments']?\"<span class='Icon file'>&nbsp;</span>\":''; ?>
    </td>

    Add beneath: (line 448 ish)
    <td><?php echo (isset($row['helptopic'])) ? $row['helptopic'] : '&nbsp;';?></td>


    Works best with short help-topics, however you can expand the field by changing the 40 to whatever works best for you.
  • Ok, you might also have to change the "colspan" attribute to prevent the clipping of the footer:

    Find:

    <tfoot>
    <tr><td colspan=\"7\">


    Change the 7 to an 8.
  • Grizly;42647 said:
    This works:

    Open include/staff/tickets.inc.php
    find:
    title=\"Sort By Subject <?php echo $negorder; ?>\">Subject</a></th>


    Add beneath: (line 357 ish)
    <th width=\"40\">Topic</th>


    Find:
    <?php echo $row['attachments']?\"<span class='Icon file'>&nbsp;</span>\":''; ?>
    </td>

    Add beneath: (line 448 ish)
    <td><?php echo (isset($row['helptopic'])) ? $row['helptopic'] : '&nbsp;';?></td>


    Works best with short help-topics, however you can expand the field by changing the 40 to whatever works best for you.
    Oh nice! It's working fine for me! Thanks!
  • I'm trying to accomplish something similar but I want it to populate with some custom fields I created. I was able to get the field to show up following these directions but nothing populates the column.


    I attached my tickets.inc.php file in a .zip


    Please help
    tickets.inc.zip
    8K
  • ghost242;42705 said:
    I'm trying to accomplish something similar but I want it to populate with some custom fields I created. I was able to get the field to show up following these directions but nothing populates the column.


    I attached my tickets.inc.php file in a .zip


    Please help
    Also here is an image of what I was able to accomplish so far on my own.
    Clipboard01.jpg
    1154 x 693 - 69K
  • ghost242;42706 said:
    Also here is an image of what I was able to accomplish so far on my own.
    <th width=\"280\">
    <a <?php echo $subj_sort; ?> href=\"tickets.php?sort=subj&order=<?php echo $negorder; ?><?php echo $qstr; ?>\"
    title=\"Sort By Subject <?php echo $negorder; ?>\">Subject</a></th>
    <!---- custom column start ---->
    <th width=\"280\">
    <a <?php echo $agency_sort; ?> href=\"tickets.php?sort=agency&order=<?php echo $negorder; ?><?php echo $qstr; ?>\"
    title=\"Sort By Model <?php echo $negorder; ?>\">Model</a></th>
    <!---- custom column end ---->
    <th width=\"170\">
    <a <?php echo $name_sort; ?> href=\"tickets.php?sort=name&order=<?php echo $negorder; ?><?php echo $qstr; ?>\"
    title=\"Sort By Name <?php echo $negorder; ?>\">From</a></th>


    then I put

    <?php echo ($threadcount>1)?\" <small>($threadcount)</small>&nbsp;\":''?>
    <?php echo $row['attachments']?\"<span class='Icon file'>&nbsp;</span>\":''; ?>
    <!--- custom code ---->
    <?php echo $row['agency']?\"<span class='Icon file'>&nbsp;</span>\":''; ?>
    <!--- custom code ---->
    </td>
    <td><?php echo (isset($row['agency'])) ? $row['agency'] : '&nbsp;';?></td>
    <td nowrap>&nbsp;<?php echo Format::truncate($row['name'],22,strpos($row['name'],'@')); ?>&nbsp;</td>
    <?php
    if($search && !$status){
    $displaystatus=ucfirst($row['status']);
    if(!strcasecmp($row['status'],'open'))
    $displaystatus=\"<b>$displaystatus</b>\";
    echo \"<td>$displaystatus</td>\";
    } else { ?>


    I'm trying to get this tied into other custom fields I created. Then I'm hoping each column can be searched and organized.
  • I had a look yesterday, but must have lost my reply.. Hmm, you will probably need to add the "agency" field into the query.

    Find this line:
             .' ,IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(\" / \", ptopic.topic, topic.topic)) as helptopic ';


    and change to:
             .',IF(ptopic.topic_pid IS NULL, topic.topic, CONCAT_WS(\" / \", ptopic.topic, topic.topic)) as helptopic, agency '; //added agency for custom field


    Give it a burl..
  • Thank you that helps some but now using this code it populates the column with id numbers vs what was selected. I attached a screenshot to give an idea.

    //ADD attachment,priorities, lock and other crap
    $qselect.=' ,count(attach.attach_id) as attachments '
    .' ,count(DISTINCT thread.id) as thread_count '
    .' ,IF(ticket.duedate IS NULL,IF(sla.id IS NULL, NULL, DATE_ADD(ticket.created, INTERVAL sla.grace_period HOUR)), ticket.duedate) as duedate '
    .' ,IF(ticket.reopened is NULL,IF(ticket.lastmessage is NULL,ticket.created,ticket.lastmessage),ticket.reopened) 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, agency '; //added agency for custom field

    $qfrom.=' LEFT JOIN '.TICKET_PRIORITY_TABLE.' pri ON (ticket.priority_id=pri.priority_id) '
    .' 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 '.TICKET_ATTACHMENT_TABLE.' attach ON (ticket.ticket_id=attach.ticket_id) '
    .' LEFT JOIN '.TICKET_THREAD_TABLE.' thread ON ( ticket.ticket_id=thread.ticket_id) '
    .' 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) ';
    Clipboard01.jpg
    1042 x 446 - 55K
  • I guess it really depends what you are storing in that field!

    Are you storing ID's? Because if so, you will need to JOIN the query with whatever other table you are storing data on to get the other values. Use the $qfrom example to guide you.
  • Grizly;42801 said:
    I guess it really depends what you are storing in that field!

    Are you storing ID's? Because if so, you will need to JOIN the query with whatever other table you are storing data on to get the other values. Use the $qfrom example to guide you.
    That makes sense. I switched agency to something else I created in the ticket column and it works fine now.

    One more question is though. I tried to add agency to the code but it just crash my site. This is what I did here. Any ideas?

    $qfrom=' FROM '.TICKET_TABLE.' ticket '.
    FROM '.AGENCIES_TABLE.' agencies '.
    ' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id ';

    $sjoin='';
    if($search && $deep_search) {
    $sjoin=' LEFT JOIN '.TICKET_THREAD_TABLE.' thread ON (ticket.ticket_id=thread.ticket_id )';
  • $qfrom=' FROM '.TICKET_TABLE.' ticket '.
    FROM '.AGENCIES_TABLE.' agencies '.
    ' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id ';


    You have two FROM arguments. I believe that you should only have one and then use LEFT JOINS. Perhaps something like this:

    $qfrom=' FROM '.TICKET_TABLE.' ticket '.
    ' LEFT JOIN ost_agencies agency ON (ticket.agency=agency.id) '.
    ' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id ';


    note: this isn't tested and I threw it together fast and I am NOT a MySQL syntax guru.
  • ntozier;42859 said:

    $qfrom=' FROM '.TICKET_TABLE.' ticket '.
    [COLOR=\"red\"]' LEFT JOIN ost_agencies agency ON (ticket.agency=agency.id) '.[/COLOR]
    ' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id ';


    hello,

    I added this code, but it is always the ID of the agency
  • Close but no. .

    You need to select the field from your agencies table and join it with the ticket table on the id, as they are the same on both tables, like the other fields in the query.
  • I'm lost ...

    I'm lost ...
    Thank you to give me your help.

    I can not make the join between the table and the ost_agencies ticket.agency

    Everything I try fails

    this is where I am

    Thank you in advance to anyone who can help me

    Red that I added in ticket.inc.php


    $qselect ='SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,ticket.team_id '
    .' ,ticket.subject,ticket.name,ticket.email,dept_name,ticket.[COLOR=\"Red\"]agency[/COLOR] '
    .' ,ticket.status,ticket.source,isoverdue,isanswered,ticket.created,pri.* ';

    $qfrom=' FROM '.TICKET_TABLE.' ticket '.
    [COLOR=\"red\"] ' LEFT JOIN '.TABLE_PREFIX.'agencies agency ON (ticket.agency=agency.id) '.[/COLOR]
    ' LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id ';

  • hypo;43304 said:
    ' LEFT JOIN '.TABLE_PREFIX.'agencies agency ON (ticket.agency=agency.id) '
    You need to join ON some common element, like, an id number.

    So your statement would be something more like:

    ...agency ON ([COLOR=\"Red\"]ticket.agency_id=agency.id[/COLOR])

    In this version, you are saying that the ID numbers on both tables share the same values, and you are simply instructing the database that they are related and to produce the "name" of the agency, rather than the internal ID number, because they are the same internally, where the tickets table has XYZ id number, replace it with the agency name.
    To accomplish this, you would instead of "SELECT ... ticket.agency" you would actually: "SELECT ... agency.name.." or whatever you named the text field you are seeking.

    Make sense?

    The following pages might enlighten:
    http://dev.mysql.com/doc/refman/5.0/en/join.html
    http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
    http://www.sitepoint.com/understanding-sql-joins-mysql-database/
  • Please help me, can't load Agency name :confused:
    12.png
    455 x 213 - 10K
  • Can the added help topic column sort?
    Grizly;42647 said:
    This works:

    Open include/staff/tickets.inc.php
    find:
    title=\"Sort By Subject <?php echo $negorder; ?>\">Subject</a></th>


    Add beneath: (line 357 ish)
    <th width=\"40\">Topic</th>


    Find:
    <?php echo $row['attachments']?\"<span class='Icon file'>&nbsp;</span>\":''; ?>
    </td>

    Add beneath: (line 448 ish)
    <td><?php echo (isset($row['helptopic'])) ? $row['helptopic'] : '&nbsp;';?></td>


    Works best with short help-topics, however you can expand the field by changing the 40 to whatever works best for you.
    Is it possible to make this help topic column have the sorting function that the other columns have?
  • I added the below line to add sorting function to the topic column

    <th width=\"100\">
    <a <?php echo $topic_sort; ?> href="tickets.php?sort=topic&order=<?php echo $negorder; ?><?php echo $qstr; ?>"title="Sort By Topic <?php echo $negorder; ?>">Topic</a></th>

     Instead of
    <th width=\"40\">Topic</th>


    Can the added help topic column sort?
    Grizly;42647 said:
    This works:

    Open include/staff/tickets.inc.php
    find:
    title=\"Sort By Subject <?php echo $negorder; ?>\">Subject</a></th>


    Add beneath: (line 357 ish)
    <th width=\"40\">Topic</th>


    Find:
    <?php echo $row['attachments']?\"<span class='Icon file'>&nbsp;</span>\":''; ?>
    </td>

    Add beneath: (line 448 ish)
    <td><?php echo (isset($row['helptopic'])) ? $row['helptopic'] : '&nbsp;';?></td>


    Works best with short help-topics, however you can expand the field by changing the 40 to whatever works best for you.
    Is it possible to make this help topic column have the sorting function that the other columns have?

Sign In or Register to comment.