[Mod] Add new sub tab "My Closed Tickes" and Amount of Tickets

JoseLuis

Based on the fine work of (masino_sinaga)

My team had the need of viewing not all the "Closed tickets" but "Own closed tickets" because normally we know on our history that we had deal with similar issue but the "Search" results don't help... and we go our closed tickets and that refines our query :)

Hope you like

BEFORE MOD

blank

AFTER MOD

blank

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

WARNING

Always, always backup your files before doing ANY change to your osTicket files and/or database!

BE WARNED

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

FIRST STEP

File: /scptickets.php

FIND: ~388

',count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned '.

REPLACE BY:

',count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned '.

// START MOD12 Show amount of closed

',count(closed.ticket_id) as closed '.

// END MOD12 Show amount of closed

// START MOD13 Show MyClosed and amount

',count(MyClosed.ticket_id) as MyClosed '.

// END MOD13 Show MyClosed and amount

FIND: ~392

'LEFT JOIN '.TICKET_TABLE.' overdue ON overdue.ticket_id=ticket.ticket_id AND overdue.status=\'Open\' AND overdue.isoverdue=1 '.

REPLACE BY:

'LEFT JOIN '.TICKET_TABLE.' overdue ON overdue.ticket_id=ticket.ticket_id AND overdue.status=\'Open\' AND overdue.isoverdue=1 '.

// START MOD12 Show amount of closed

'LEFT JOIN '.TICKET_TABLE.' closed ON closed.ticket_id=ticket.ticket_id AND closed.status=\'closed\' '.

// END MOD12 Show amount of closed

// START MOD13 Show MyClosed and amount

'LEFT JOIN '.TICKET_TABLE.' MyClosed ON MyClosed.ticket_id = ticket.ticket_id AND MyClosed.status=\'closed\' AND MyClosed.staff_id ='.db_input($thisuser->getId()).

// END MOD13 Show MyClosed and amount

FIND: ~425

$nav->addSubMenu(array('desc'=>'Closed Tickets','title'=>'Closed Tickets', 'href'=>'tickets.php?status=closed', 'iconclass'=>'closedTickets'));

REPLACE BY:

// START MOD13 Add MyClosed tickets

$nav->addSubMenu(array('desc'=>'My Closed Tickets ('.$stats.')','title'=>'My Closed Tickets',

'href'=>'tickets.php?status=MyClosed', 'iconclass'=>'assignedTickets'));

// END MOD13 Add MyClosed tickets

// START MOD12 Show amount of closed

$nav->addSubMenu(array('desc'=>'Closed Tickets ('.$stats.')','title'=>'Closed Tickets',

'href'=>'tickets.php?status=closed', 'iconclass'=>'closedTickets'));

// END MOD12 Show amount of closed

SECOND STEP

File: /include/stafftickets.inc.php

FIND: ~29

case 'open':

$status='open';

break;

REPLACE BY:

case 'open':

$status='open';

break;

// START MOD13 Add MyClosed tickets

case 'myclosed':

$status='myclosed';

break;

// END MOD13 Add MyClosed tickets

FIND: ~88

//STATUS

if($status){

$qwhere.=' AND status='.db_input($status);

}

REPLACE BY:

//STATUS

if($status){

// START MOD13 Add MyClosed tickets

if($status == 'myclosed'){

$status='closed';

$qwhere.=' AND status='.db_input($status).' AND staff.staff_id='.$thisuser->getId();

} else {

$qwhere.=' AND status='.db_input($status);

}

// END MOD13 Add MyClosed tickets

}

beeman

HI JoseLuis

I tried to implement this MOD but I get the following DB error;

Unknown column '39LEFT' in 'on clause'

I will appreciate it if you can look into this please.

Thanks

Beeman

JoseLuis

HI JoseLuis
I tried to implement this MOD but I get the following DB error;
Unknown column '39LEFT' in 'on clause'
I will appreciate it if you can look into this please.
Thanks
Beeman

hummm that way is hard to get the "bug" :

Will make a SQLformat

SELECT count(open.ticket_id) AS open,

count(closed.ticket_id) AS closed,

count(answered.ticket_id) AS answered,

count(overdue.ticket_id) AS overdue,

count(assigned.ticket_id) AS assigned,

count(open_assigned.ticket_id) AS open_assigned,

count(closed.ticket_id) AS closed,

count(MyClosed.ticket_id) AS MyClosed

FROM ost_ticket ticket

LEFT JOIN ost_ticket open

ON open.ticket_id = ticket.ticket_id

AND open.status = 'Open'

AND open.isanswered = 0

LEFT JOIN ost_ticket answered

ON answered.ticket_id = ticket.ticket_id

AND answered.status = 'Open'

AND answered.isanswered = 1

LEFT JOIN ost_ticket overdue

ON overdue.ticket_id = ticket.ticket_id

AND overdue.status = 'Open'

AND overdue.isoverdue = 1

LEFT JOIN ost_ticket closed

ON closed.ticket_id = ticket.ticket_id AND closed.status = 'closed'

LEFT JOIN ost_ticket MyClosed

ON MyClosed.ticket_id = ticket.ticket_id

AND MyClosed.status = 'closed'

AND MyClosed.staff_id = 39LEFT

JOIN ost_ticket assigned

ON assigned.ticket_id = ticket.ticket_id AND assigned.staff_id = 39

LEFT JOIN ost_ticket open_assigned

ON open_assigned.ticket_id = ticket.ticket_id

AND open_assigned.status = 'Open'

AND open_assigned.staff_id = 39

WHERE ticket.dept_id IN (5, 4, 7, 3, 1, 2) OR ticket.staff_id = 39

if you look at this SQLcode even not knowing SQL you will see a different row at this:AND MyClosed.staff_id = 39LEFT

This happens, because you already have an modified scptickets.php file.

FIND:

'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND assigned.staff_id='.db_input($thisuser->getId());

REPLACE BY:

' LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND assigned.staff_id='.db_input($thisuser->getId());

beeman

Thank you for the prompt reply; my SQL skills are not good but I can see the different as you have highlighted in your post. I cannot find the code you refer to below oin the /scp/ticket.php

'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND assigned.staff_id='.db_input($thisuser->getId());

Please my code of the place I search, it is obviously modified as you correctly pointed out and Masino MOD is working properly as we speak. I do not know where to modify the code below to get your MOD to work. Please have a look and see where the problem could be.

/Navigation

$submenu=array();

/*quick stats...*/

$sql='SELECT count(open.ticket_id) as open, count(closed.ticket_id) as closed, count(answered.ticket_id) as answered '.

',count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned, count(open_assigned.ticket_id) as open_assigned '.

',count(closed.ticket_id) as closed '.

' FROM '.TICKET_TABLE.' ticket '.

'LEFT JOIN '.TICKET_TABLE.' open ON open.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND open.isanswered=0 '.

// returns all the closed tickets.

'LEFT JOIN '.TICKET_TABLE.' answered ON answered.ticket_id=ticket.ticket_id AND answered.status=\'Open\' AND answered.isanswered=1 '.

'LEFT JOIN '.TICKET_TABLE.' overdue ON overdue.ticket_id=ticket.ticket_id AND overdue.status=\'Open\' AND overdue.isoverdue=1 '.

'LEFT JOIN '.TICKET_TABLE.' closed ON closed.ticket_id=ticket.ticket_id AND closed.status=\'closed\' '.

'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND assigned.staff_id='.db_input($thisuser->getId()).' '.

// returns the total number of open assigned tickets

'LEFT JOIN '.TICKET_TABLE.' open_assigned ON open_assigned.ticket_id=ticket.ticket_id AND open_assigned.status=\'Open\' AND open_assigned.staff_id='.db_input($thisuser->getId());

if(!$thisuser->isAdmin()){

$sql.=' WHERE ticket.dept_id IN('.implode(',',$thisuser->getDepts()).') OR ticket.staff_id='.db_input($thisuser->getId());

}

//echo $sql;

Thank you so much for your help.

Regards,

Beeman

JoseLuis

/Navigation
$submenu=array();
/*quick stats...*/
$sql='SELECT count(open.ticket_id) as open, count(closed.ticket_id) as closed, count(answered.ticket_id) as answered '.
',count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned, count(open_assigned.ticket_id) as open_assigned '.
',count(closed.ticket_id) as closed '.
' FROM '.TICKET_TABLE.' ticket '.
'LEFT JOIN '.TICKET_TABLE.' open ON open.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND open.isanswered=0 '.
// returns all the closed tickets.
'LEFT JOIN '.TICKET_TABLE.' answered ON answered.ticket_id=ticket.ticket_id AND answered.status=\'Open\' AND answered.isanswered=1 '.
'LEFT JOIN '.TICKET_TABLE.' overdue ON overdue.ticket_id=ticket.ticket_id AND overdue.status=\'Open\' AND overdue.isoverdue=1 '.
'LEFT JOIN '.TICKET_TABLE.' closed ON closed.ticket_id=ticket.ticket_id AND closed.status=\'closed\' '.
'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND assigned.staff_id='.db_input($thisuser->getId()).' '.
// returns the total number of open assigned tickets
'LEFT JOIN '.TICKET_TABLE.' open_assigned ON open_assigned.ticket_id=ticket.ticket_id AND open_assigned.status=\'Open\' AND open_assigned.staff_id='.db_input($thisuser->getId());
if(!$thisuser->isAdmin()){
$sql.=' WHERE ticket.dept_id IN('.implode(',',$thisuser->getDepts()).') OR ticket.staff_id='.db_input($thisuser->getId());
}
//echo $sql;Beeman

That's why I state that should do a backup of your files..

People make mods with mods with mods... and something can be wrong with too many modifications..

Regarding your personal case this should correct your problem

//Navigation

$submenu=array();

/*quick stats...*/

$sql='SELECT count(open.ticket_id) as open, count(answered.ticket_id) as answered '.

',count(overdue.ticket_id) as overdue, count(assigned.ticket_id) as assigned '.

',count(closed.ticket_id) as closed, count(open_assigned.ticket_id) as open_assigned '.

',count(MyClosed.ticket_id) as MyClosed '.

' FROM '.TICKET_TABLE.' ticket '.

'LEFT JOIN '.TICKET_TABLE.' open ON open.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND open.isanswered=0 '.

'LEFT JOIN '.TICKET_TABLE.' answered ON answered.ticket_id=ticket.ticket_id AND answered.status=\'Open\' AND answered.isanswered=1 '.

'LEFT JOIN '.TICKET_TABLE.' overdue ON overdue.ticket_id=ticket.ticket_id AND overdue.status=\'Open\' AND overdue.isoverdue=1 '.

'LEFT JOIN '.TICKET_TABLE.' closed ON closed.ticket_id=ticket.ticket_id AND closed.status=\'closed\' '.

'LEFT JOIN '.TICKET_TABLE.' MyClosed ON MyClosed.ticket_id = ticket.ticket_id AND MyClosed.status=\'closed\' AND MyClosed.staff_id ='.db_input($thisuser->getId()).' '.

'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND open.status=\'Open\' AND assigned.staff_id='.db_input($thisuser->getId()).' '.

'LEFT JOIN '.TICKET_TABLE.' open_assigned ON open_assigned.ticket_id=ticket.ticket_id AND open_assigned.status=\'Open\' AND open_assigned.staff_id='.db_input($thisuser->getId());

if(!$thisuser->isAdmin()){

$sql.=' WHERE ticket.dept_id IN('.implode(',',$thisuser->getDepts()).') OR ticket.staff_id='.db_input($thisuser->getId());

}//echo $sql;

beeman

Thanks JoseLuis,

It works now:)

lean

error

I made changes as this post and get the following error:

Unknown column 'staff.staff_id' in 'where clause'

Can you help?