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

[MOD] Age of Tickets (Minutes, Hours, or Days)

As requested I am posting this all together again:

A note before you add all this. A portion of this is incorporated with another MOD that shows who the ticket is assigned to.

image

All of these entries are in include/staff/tickets.inc.php

Replace the original $sortOptions with the following:

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



Replace the original $qselect line with the following:


$qselect = 'SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.firstname,staff.lastname '.
',ticket.status,ticket.source,isoverdue,ticket.created,pri.*,CASE WHEN status = \"open\" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeopen,count(attach.attach_id) as attachments ';
$qfrom=' FROM '.TICKET_TABLE.' ticket LEFT JOIN '.DEPT_TABLE.' dept ON ticket.dept_id=dept.dept_id '.
' LEFT JOIN '.STAFF_TABLE.' staff ON ticket.staff_id=staff.staff_id';


On my version I removed the "Created" column as I felt it was no longer needed with an "Age" column replacing it:


<!--<td align=\"center\" nowrap><?=Format::db_date($row['created'])?></td>-->
<td class=\"nohover\" align=\"center\" >
<?
$diff =$row['timeopen'];
$min = \"min\";
$mins = \"mins\";
$hours = \"hours\";
$hour = \"hour\";
$day = \"day\";
$days = \"days\";
if ( $diff <= 1 ){
print ($diff . \" \" . $min);
}elseif ( $diff > 1 && $diff <= 59 ){
print ($diff . \" \" . $mins);
}elseif ( $diff >= 60 && $diff <= 119 ){
$diff = round($diff / 60);
print (1 . \" \" . $hour);
}elseif ( $diff >= 120 && $diff <= 1439 ){
$diff = round($diff / 60);
print ($diff . \" \" . $hours);
}elseif ( $diff >= 1440 && $diff <= 2879 ){
print (1 . \" \" . $day);
}elseif ( $diff >= 2880 ){
$diff = round($diff / 1440);
print ($diff . \" \" . $days);
}else {};
?>
</td>


Forgot the last part!
Around the lines in 385 or so you'll have several th (table headers). You'll need to add in the following where you want it to show up in the table. (and make sure to comment out your Date column as well or everything will be off by a column)
<th width=\"92\">
<a href=\"tickets.php?sort=timeopen&order=<?=$negorder?><?=$qstr?>\" title=\"Sort By Age <?=$negorder?>\">Age</a></th>


Feel free to ask questions, thanks.
«13

Comments

  • when i did this, i get a column that simply reads "min" all the way down.

    here is my current qselect (pre your mod)

    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.username '.
    ',status,source,isoverdue,ticket.created,pri.* ';


    i think thats where i am going wrong. can you paste what I should have.

    (i also cannot get webpragmatic's mod working either - but have it disabled)
  • Directly following the pri.* you should have

    ,CASE WHEN status = \"open\" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeop
    en,count(attach.attach_id) as attachments ';


    So the whole thing should be:
    $qselect = 'SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.firstname,staff.lastname '.',ticket.status,ticket.source,is
    overdue,ticket.created,pri.*,CASE WHEN status = \"open\" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeop
    en,count(attach.attach_id) as attachments ';


    Do you have all of that?
  • Try copy/pasting either of the two qselects I have up now. My original posting had some \ where it was breaking the line in my code down to the next line. I've removed them from here so that may help.
  • strange - i am still getting the same thing.

    whats strange is that this problem seems very similar to whats going on with webpragmatic's mod.

    i will need to address this tomorrow.
  • Directons for the rest of us

    Hi,

    This looks like a great addon, can someone give easier instructions on how to do this? Like what line to insert it in etc.

    Thanks
  • @MarkLake

    I didn't include the lines since most of us have several MODs already in place that end up changing the lines to something different for each of us. You should be able to use whatever Find/Search function you have available and just replace the specified code.

    Let me know if I can assist in any way.
  • Thanks

    I don't program code, but I can copy and paste code.
    I wasn't sure if I replace one line or 2 lines etc by each area.

    Also by the last one you didn't say where to place it.

    Thanks for your help
  • MarkLake;9621 said:
    Also by the last one you didn't say where to place it.
    Are you referring to
    On my version I removed the "Created" column as I felt it was no longer needed with an "Age" column replacing it:
    You can just search for the first line I have there:

    <?=Format::db_date($row['created'])?>
  • scottro, both your mod, and webpragmatic's mod's do the same thing for me - i get empty columns.

    any word on this?
  • If you want to PM me I'll give you my email address and you can send me your file and I can take a look at it.
  • Greate Mod! Works perfectly!
  • Thanks! :D
  • If you want to include color backgrounds:

    1 Day - Green
    2/3 Days - Yellow
    4+ Days - Red

    You can use the following code
    <!--<td align=\"center\" nowrap><?=Format::db_date($row['created'])?></td>-->
    <td class=\"nohover\" align=\"center\"
    style=\"background-color:<?$color=\"#33FF66\"; $diff=$row['timeopen']; $diff = round($diff / 1440); if ($diff <= 1){print ($color);}elseif ($diff >= 2 && $diff\
    < 4){$color=\"#FFFF66\"; print ($color);}elseif ($diff >= 4){$color=\"#FF745A\"; print ($color);} ?>\" >
    <?
    $diff =$row['timeopen'];
    $min = \"min\";
    $mins = \"mins\";
    $hours = \"hours\";
    $hour = \"hour\";
    $day = \"day\";
    $days = \"days\";
    if ( $diff <= 1 ){
    print ($diff . \" \" . $min);
    }elseif ( $diff > 1 && $diff <= 59 ){
    print ($diff . \" \" . $mins);
    }elseif ( $diff >= 60 && $diff <= 119 ){
    $diff = round($diff / 60);
    print (1 . \" \" . $hour);
    }elseif ( $diff >= 120 && $diff <= 1439 ){
    $diff = round($diff / 60);
    print ($diff . \" \" . $hours);
    }elseif ( $diff >= 1440 && $diff <= 2879 ){
    print (1 . \" \" . $day);
    }elseif ( $diff >= 2880 ){
    $diff = round($diff / 1440);
    print ($diff . \" \" . $days);
    }else {};
    ?>
    </td>


    instead of the original I posted:
    <!--<td align=\"center\" nowrap><?=Format::db_date($row['created'])?></td>-->
    <td class=\"nohover\" align=\"center\" >
    <?
    $diff =$row['timeopen'];
    $min = \"min\";
    $mins = \"mins\";
    $hours = \"hours\";
    $hour = \"hour\";
    $day = \"day\";
    $days = \"days\";
    if ( $diff <= 1 ){
    print ($diff . \" \" . $min);
    }elseif ( $diff > 1 && $diff <= 59 ){
    print ($diff . \" \" . $mins);
    }elseif ( $diff >= 60 && $diff <= 119 ){
    $diff = round($diff / 60);
    print (1 . \" \" . $hour);
    }elseif ( $diff >= 120 && $diff <= 1439 ){
    $diff = round($diff / 60);
    print ($diff . \" \" . $hours);
    }elseif ( $diff >= 1440 && $diff <= 2879 ){
    print (1 . \" \" . $day);
    }elseif ( $diff >= 2880 ){
    $diff = round($diff / 1440);
    print ($diff . \" \" . $days);
    }else {};
    ?>
    </td>
  • For anyone interested, Skeyelab is good to go now on both the age mod and the added color mod.

    If anyone else has any problems feel free to let me know.
  • dont worry, i was gonna pat your back... no need to do it yourself.. :D

    thanks scottro!
  • lol, cool :D
  • A little help with this mod please.

    Hello and thank you in advance for sharing your talent! I have done the "assigned to" mod and it works good but when I do this mod I get a 0 tickets response with nothing showing but a 0.

    Doing it one segment at a time, the $sortOptions works great but when I paste the $qselect portion I get the error. I did follow through with the whole mod and then started going backward to see where the error came in.

    Here is old Code:

    $qselect = 'SELECT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,email,dept_name '.
    ',status,source,isoverdue,ticket.created,pri.* ';


    and here is what I pasted:

    $qselect = 'SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.firstname,staff.lastname '.
    ',ticket.status,ticket.source,isoverdue,ticket.created,pri.*,CASE WHEN status = \"open\" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeopen,count(attach.attach_id) as attachments ';


    Any input will be appreciated,
    Scott
  • Do you have access to your mysql logs? If so you might see what is coming up for that SELECT DISTINCT query and then manually run that command from phphymadmin (you'd have to add phpmyadmin first).

    All the code looks good there so its hard to say what the problem is without being able to look at whats happening behind the scenes.
  • Actually, you wouldn't HAVE to have phpmyadmin, you could log into the database directly and run it there too. Then troubleshoot by removing bits at a time and compare.
  • or perhaps you have CASE WHEN in there twice now, since your original doesn't have it pasted in here, but my code does. Then it might be trying to CASE WHEN twice.
  • I will try your suggestions. Thank you kindly.
    scottro;9651 said:
    or perhaps you have CASE WHEN in there twice now, since your original doesn't have it pasted in here, but my code does. Then it might be trying to CASE WHEN twice.
  • I checked and only have instance of CASE... and that is in your code. Here is a DB Error code I get:

    [SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name,staff.firstname,staff.lastname ,ticket.status,ticket.source,isoverdue,ticket.created,pri.*,CASE WHEN status = \"open\" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeopen,count(attach.attach_id) as attachments  ,count(attach_id) as attachments   FROM dfs_ticket ticket LEFT JOIN dfs_department dept ON ticket.dept_id=dept.dept_id  LEFT JOIN dfs_ticket_priority pri ON ticket.priority_id=pri.priority_id  LEFT JOIN dfs_ticket_lock tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW()  LEFT JOIN dfs_ticket_attachment attach ON  ticket.ticket_id=attach.ticket_id   WHERE 1 AND status='open'  GROUP BY ticket.ticket_id ORDER BY priority_urgency,ticket.created DESC LIMIT 0,50] - Unknown column 'staff.firstname' in 'field list'
  • ah ok,

    Here is the part we need to focus on then
    Unknown column 'staff.firstname' in 'field list'
    in your ost_staff table do you have a firstname column?

    Are you on the latest build btw? RC5?
  • I do not have that in the column and I just realized I have: v1.6 RC4
    scottro;9656 said:
    ah ok,

    Here is the part we need to focus on then



    in your ost_staff table do you have a firstname column?

    Are you on the latest build btw? RC5?
  • I think you should be able to just remove the staff.firstname portion of the qselect query and then you should be good. I *THINK* that lastname is included so you probably have everything else. Remove that and see how it goes. Let me know if you get a different db error.
  • I removed first and got a last name error so I removed last and here is where I am at with the error:

    [SELECT DISTINCT ticket.ticket_id,lock_id,ticketID,ticket.dept_id,ticket.staff_id,subject,name,ticket.email,dept_name, ,ticket.status,ticket.source,isoverdue,ticket.created,pri.*,CASE WHEN status = \"open\" THEN FLOOR(TIME_TO_SEC(TIMEDIFF(now(),ticket.created))/60) ELSE FLOOR(TIME_TO_SEC(TIMEDIFF(ticket.closed,ticket.created))/60) END AS timeopen,count(attach.attach_id) as attachments  ,count(attach_id) as attachments   FROM dfs_ticket ticket LEFT JOIN dfs_department dept ON ticket.dept_id=dept.dept_id  LEFT JOIN dfs_ticket_priority pri ON ticket.priority_id=pri.priority_id  LEFT JOIN dfs_ticket_lock tlock ON ticket.ticket_id=tlock.ticket_id AND tlock.expire>NOW()  LEFT JOIN dfs_ticket_attachment attach ON  ticket.ticket_id=attach.ticket_id   WHERE 1 AND status='open'  GROUP BY ticket.ticket_id ORDER BY priority_urgency,ticket.created DESC LIMIT 0,50] - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ticket.status,ticket.source,isoverdue,ticket.created,pri.*,CASE WHEN status = \"o' at line 1


    Do you think I need to upgrade from RC4 to RC5 and redo the other mods?
    scottro;9659 said:
    I think you should be able to just remove the staff.firstname portion of the qselect query and then you should be good. I *THINK* that lastname is included so you probably have everything else. Remove that and see how it goes. Let me know if you get a different db error.
  • dept_name, ,ticket.status


    Need to remove the extra ,
  • Just wanted to update, I upgraded to RC5 and re-did everything and it works great now. Thank you very much for you time and help!
    scottro;9662 said:
    dept_name, ,ticket.status


    Need to remove the extra ,
  • Sweet! Glad to hear it! :D
  • I have tried this too, freshly installed yesterday, added a couple of tickets, some are assigned, some aren't.

    It should be 1.6.RC5 as that is what I downloaded.

    Now I'm seeing the same errors as above #1054 - Unknown column 'staff.firstname' in 'field list'
    or when I remove the staff.firstname and leave only the staff.lastname: #1054 - Unknown column 'staff.lastname' in 'field list'
    Both in de errorlog in the admin panel as in phpMyAdmin when I paste the code in the SQL query window.

    I went over my code several times (this is the only modding I've done on my installation) and I don't see anything wrong.

    Any suggestions, please? I'm not experienced in writing php, nor SQL queries, so most of it is Chinese to me. I try to understand, but I don't get why it doesn't "see" the staff.firstname, while I do see those in my database. And they are not empty either.
Sign In or Register to comment.