Change complicated query

Lupo76

Hello everyone,

in the file "scp/ticket.php", I found the following query:

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

' 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.' assigned ON assigned.ticket_id=ticket.ticket_id AND 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());

}

I need to change it,

so that the statistics do not take into account all the messages with subject = 'TEST'.

I tried to write:

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

' 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.' assigned ON assigned.ticket_id=ticket.ticket_id AND 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()). ' AND subject="TEST"';

}

but does not work

Can you help?

Thanks in advance

hawkhead99

Hi Lupo,

When you say "statistics", what exactly do you mean? I assume your tickets with the word "TEST" in the subject line get deleted, which also removes all records of them from the database. So any reporting being done would not capture the "TEST" items once they are deleted.

However, if that is not acceptable, you could always try this:

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

' FROM '.TICKET_TABLE.' ticket '.

'LEFT JOIN '.TICKET_TABLE.' open ON open.ticket_id=ticket.ticket_id AND ticket.subject != "TEST" AND open.status=\'open\' AND open.isanswered=0 '.

'LEFT JOIN '.TICKET_TABLE.' answered ON answered.ticket_id=ticket.ticket_id AND ticket.subject != "TEST" AND answered.status=\'open\' AND answered.isanswered=1 '.

'LEFT JOIN '.TICKET_TABLE.' overdue ON overdue.ticket_id=ticket.ticket_id AND ticket.subject != "TEST" AND overdue.status=\'open\' AND overdue.isoverdue=1 '.

'LEFT JOIN '.TICKET_TABLE.' assigned ON assigned.ticket_id=ticket.ticket_id AND ticket.subject != "TEST" AND 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());

}

You want to account for every join since not everyone is a ADMIN, plus I saw you had it "subject = "TEST". You needed "ticket." in front of it plus, from your description, I imagine you also wanted "!= " instead of "=".

Using the method above, you shouldn't need it in the last where statement. Hope this helps :)

Lupo76

Ok,

perfect now everything is clear!

Thank you!