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'));
}
/**
* Execute the console command.
*
* @return mixed
*/
public function fire()
{
$tickets = Ticket::orderBy('open', 'desc')->where('open', 1)->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');
})->whereNull('tr.user_id')->groupBy('tdu.user_id')->pluck('tdu.user_id')->toArray();
if (!empty($tickets)) {
event(new UnseenTickets($tickets));
}
}
public function index(Request $request)
{
$tickets = Ticket::orderBy('open', 'desc')->orderBy('priority', 'desc')->orderBy('tickets.updated_at', 'desc')->select('tickets.*')->with('replies', 'department', 'student')->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('user_id', "=", user()->id);
});
if (is_array(request('status'))) {
$tickets->whereIn('open', request('status'));
}
if (is_array(request('department_id'))) {
$tickets->whereIn('tickets.department_id', request('department_id'));
}
if (is_array(request('priority'))) {
$tickets->whereIn('priority', request('priority'));
}
if ($request->has('subject')) {
$tickets->where('subject', 'LIKE', '%' . request('subject') . '%');
}
if ($request->has('student')) {
$tickets->where('tickets.student_id', request('student'));
$student = Student::find(request('student'));
}
if ($request->has('create_at')) {
$tickets->where('tickets.created_at', 'LIKE', '%' . request('create_at') . '%');
}
if (request('year_term')) {
$tickets = $tickets->whereHas('student.department', function ($query) {
$query->where('term_id', request('year_term'));
});
}
if ($request->has('is_new')) {
$tickets = $tickets->join('ticket_replies as tr', function ($join) {
$join->on('tr.ticket_id', '=', 'tickets.id')->where('seen', '=', 0);
});
}
$ticket_search_count = $tickets->count();
$tickets = $tickets->paginate(30);
$tickets->appends($request->except('page'));
$ticketcount = Ticket::selectRaw('SUM(IF(open=0 ,1,0)) AS closed, SUM(IF(open=1 ,1,0)) AS open')->with('replies', 'department')->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('user_id', "=", user()->id);
});
$ticketcount = $ticketcount->first();
//dd($ticketcount);
$unseencount = Ticket::orderBy('open', 'desc')->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')->count();
$categories = TicketCategory::pluck('name', 'id')->toArray();
$categories = [NULL => NULL] + $categories;
$year_term = year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
academystructure_terms.id as tid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->orderBy('academystructure_terms.id')->pluck('name', 'tid')->toArray();
$options = [];
$departments = TicketDepartment::withDepth()->defaultOrder()->orderBy('parent_id', 'ASC')->get();
foreach ($departments as $department) {
$options[$department->id] = str_repeat("----", $department->depth) . $department->name;
}
$priorities = trans('tickets::tickets.priorities');
$has_search = count($request->except('page', 'per_page'));
if ($request->ajax()) {
$unseen = Ticket::orderBy('open', 'desc')->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')->count();
$view = view('tickets::tickets._table', compact('categories', 'year_term', 'tickets', 'options', 'priorities', 'has_search', 'student', 'ticketcount', 'ticket_search_count'))->render();
return response()->json(['unseen' => $unseen, 'view' => $view]);
}
return view('tickets::tickets.index', compact('tickets', 'categories', 'year_term', 'options', 'priorities', 'has_search', 'student', 'ticketcount', 'unseencount', 'ticket_search_count'));
}