private function query()
{
$raw1 = DB::raw("CASE \n WHEN tickets.created_at > posts.created_at OR posts.created_at IS NULL \n THEN tickets.created_at \n ELSE posts.created_at \n END as 'last_operation_date'");
$raw2 = DB::raw("CASE \n WHEN tickets.created_at > posts.created_at OR posts.created_at IS NULL \n THEN tickets.creator_id ELSE posts.author_id \n END as 'last_operation_company_person_id'");
$raw3 = DB::raw('(SELECT MAX(id) as post_id, ticket_id FROM posts GROUP BY ticket_id) as d1');
$raw4 = DB::raw("(SELECT time.ticket_id, time.active_work, epe.event_id, epe.email_text,\n \n CASE WHEN t.status_id NOT IN (" . TICKET_SOLVED_STATUS_ID . "," . TICKET_CLOSED_STATUS_ID . ")\n THEN epe.delay_time - time.active_work \n ELSE NULL\n END as deadline\n\n FROM \n (SELECT d3.ticket_id, SUM(TIMESTAMPDIFF(SECOND, d3.from, d3.to)) as 'active_work' \n \n FROM (\n SELECT th1.ticket_id, th1.created_at as 'from', CASE WHEN th2.created_at IS NULL THEN NOW() ELSE th2.created_at END as 'to'\n FROM tickets_history as th1\n LEFT JOIN tickets_history th2 ON (th1.id = th2.previous_id)\n WHERE th1.status_id IN (" . str_replace(":", ",", TICKETS_ACTIVE_STATUS_IDS) . ")\n ) as d3\n \n LEFT JOIN (\n SELECT th1.ticket_id, MAX(th2.created_at) as 'last_important_update'\n FROM tickets_history as th1\n LEFT JOIN tickets_history th2 ON (th2.previous_id = th1.id)\n WHERE (th1.level_id != th2.level_id OR th1.priority_id != th2.priority_id)\n GROUP BY th1.ticket_id\n ) as d4 ON (d4.ticket_id = d3.ticket_id AND d4.last_important_update > d3.from)\n AND d4.ticket_id IS NULL\n\n GROUP BY d3.ticket_id\n ) as time\n\n LEFT JOIN tickets t ON t.id = time.ticket_id\n LEFT JOIN companies c ON c.id = t.company_id\n LEFT JOIN escalation_profiles ep ON c.escalation_profile_id = ep.id\n LEFT JOIN escalation_profile_event epe ON (epe.priority_id = t.priority_id AND epe.level_id = t.level_id AND epe.profile_id = c.escalation_profile_id)\n\n ) as final");
$tickets = Ticket::select("tickets.*", $raw1, $raw2, 'statuses.allowed_statuses', 'final.active_work', 'final.deadline', 'final.event_id', 'final.email_text');
$tickets->leftJoin('company_person as creator_contacts', 'tickets.creator_id', '=', 'creator_contacts.id');
$tickets->leftJoin('company_person as assignee_contacts', 'tickets.assignee_id', '=', 'assignee_contacts.id');
$tickets->leftJoin('people as assignees', 'assignee_contacts.person_id', '=', 'assignees.id');
$tickets->leftJoin('people as creators', 'creator_contacts.person_id', '=', 'creators.id');
$tickets->leftJoin('divisions', 'tickets.division_id', '=', 'divisions.id');
$tickets->leftJoin('levels', 'tickets.level_id', '=', 'levels.id');
$tickets->leftJoin('statuses', 'tickets.status_id', '=', 'statuses.id');
$tickets->leftJoin('priorities', 'tickets.priority_id', '=', 'priorities.id');
$tickets->leftJoin('companies', 'tickets.company_id', '=', 'companies.id');
$tickets->leftJoin($raw3, 'd1.ticket_id', '=', 'tickets.id');
$tickets->leftJoin('posts', 'd1.post_id', '=', 'posts.id');
$tickets->leftJoin($raw4, 'final.ticket_id', '=', 'tickets.id');
if (Auth::check() && !Auth::user()->active_contact->isE80()) {
$tickets->where("tickets.company_id", "=", Auth::user()->active_contact->company_id);
}
return $tickets;
}
protected function tickets()
{
return Ticket::select(['*', DB::raw('id_user_assigned AS assigned_to_name'), DB::raw('status AS status_name')])->get();
}
