public function status_report()
{
$status = Ticket::groupBy('tickets.open')->selectRaw('SUM(IF(open=0 ,1,0)) AS closed, SUM(IF(open=1 ,1,0)) AS open')->get();
$unseen = Ticket::orderBy('open', 'desc')->join('ticket_replies as tr', function ($join) {
$join->on('tr.ticket_id', '=', 'tickets.id')->where('seen', '=', 0);
})->count();
return view('tickets::reports.status', compact('status', 'unseen'));
}
public function report()
{
$ticketcount = Ticket::groupBy('tickets.department_id')->selectRaw('SUM(IF(open=0 ,1,0)) AS closed, SUM(IF(open=1 ,1,0)) AS open, td.name')->with('replies', 'department')->join('ticket_departments as td', function ($join) {
$join->on('td.id', '=', 'tickets.department_id');
});
$ticketcounts = $ticketcount->get();
$unseencount = Ticket::selectRaw('SUM(IF(open=0 ,1,0)) AS closed, SUM(IF(open=1 ,1,0)) AS open')->join('ticket_replies as tr', function ($join) {
$join->on('tr.ticket_id', '=', 'tickets.id')->where('seen', '=', 0);
})->join('ticket_departments as td', function ($join) {
$join->on('td.id', '=', 'tickets.department_id');
})->join('ticket_department_user as tdu', function ($join) {
$join->on('tdu.department_id', '=', 'td.id')->where('tdu.user_id', "=", user()->id);
})->whereNull('tr.user_id')->get();
return $unseencount;
return view('tickets::tickets.report', compact('ticketcounts'));
}