public function index()
{
if (Auth::user()->can('read-all-ticket')) {
$data['tickets'] = self::API()->all(Request::input());
$data['companies'] = Company::orderBy('name', 'asc')->get();
$data['employees'] = CompanyPersonController::API()->all(['where' => ['company_person.company_id|=|' . ELETTRIC80_COMPANY_ID], 'order' => ['people.last_name|ASC', 'people.first_name|ASC'], 'paginate' => 'false']);
$data['divisions'] = Division::orderBy('name', 'asc')->get();
$data['statuses'] = Status::where('id', '!=', TICKET_DRAFT_STATUS_ID)->orderBy('id', 'asc')->get();
$data['active_search'] = implode(",", ['tickets.id', 'tickets.title', 'tickets.post']);
if (Auth::user()->active_contact->isE80()) {
$data['menu_actions'] = [Form::addItem(route('tickets.create'), "Create Ticket", Auth::user()->can('create-ticket'))];
} else {
$data['menu_actions'] = [Form::addItem(route('ticket_requests.create'), "Request Ticket", Auth::user()->can('create-ticket'))];
}
$data['title'] = "Tickets";
return Request::ajax() ? view('tickets/tickets', $data) : view('tickets/index', $data);
} else {
return redirect()->back()->withErrors(['Access denied to tickets index page']);
}
}
public static function workingTimeData($days, $type)
{
$result = null;
if ($type == "division") {
$grouping = Division::whereIn('id', [LGV_DIVISION_ID, PLC_DIVISION_ID, PC_DIVISION_ID, BEMA_DIVISION_ID, FIELD_DIVISION_ID, SPARE_PARTS_DIVISION_ID, RELIABILITY_DIVISION_ID, OTHERS_DIVISION_ID])->orderBy("name")->get();
} elseif ($type == "priority") {
$grouping = Priority::orderBy("name")->get();
} elseif ($type == "level") {
$grouping = Level::orderBy("name")->get();
} elseif ($type == "company") {
$grouping = Company::orderBy("name")->get();
} elseif ($type == "assignee") {
$grouping = CompanyPerson::select("company_person.*", "people.first_name", "people.last_name")->leftJoin('people', 'company_person.person_id', '=', 'people.id')->where('company_person.company_id', '=', ELETTRIC80_COMPANY_ID)->orderBy("last_name")->get();
foreach ($grouping as $group) {
$group->name = $group->last_name . " " . $group->first_name;
}
}
if (isset($grouping)) {
foreach ($grouping as $index => $group) {
for ($i = 0; $i < 50; $i++) {
$query = "SELECT SUM(resolution_time) as sum, COUNT(*) as ticket_count, AVG(resolution_time) as average, DATE_SUB(NOW(), INTERVAL " . $days * ($i + 1) . " day) as date\n FROM (\n SELECT before.ticket_id, SUM(\n TIMESTAMPDIFF(SECOND, \n GREATEST(DATE_SUB(NOW(), INTERVAL " . $days * ($i + 1) . " day), before.created_at), \n LEAST(DATE_SUB(NOW(), INTERVAL " . $days * $i . " day), IFNULL(after.created_at,NOW()))\n )\n )/3600 as resolution_time\n FROM tickets_history as `before`\n LEFT JOIN tickets_history as `after` ON before.id = after.previous_id\n INNER JOIN tickets ON tickets.id = before.ticket_id \n WHERE (\n after.status_id IN (" . TICKET_NEW_STATUS_ID . "," . TICKET_IN_PROGRESS_STATUS_ID . ",\n " . TICKET_REQUESTING_STATUS_ID . "," . TICKET_SOLVED_STATUS_ID . "," . TICKET_CLOSED_STATUS_ID . ")\n OR \n (before.status_id IN (" . TICKET_NEW_STATUS_ID . "," . TICKET_IN_PROGRESS_STATUS_ID . ",\n " . TICKET_REQUESTING_STATUS_ID . ") AND after.id IS NULL)\n )\n AND tickets.deleted_at IS NULL\n AND tickets." . $type . "_id = {$group->id}\n AND TIMESTAMPDIFF(SECOND, \n GREATEST(before.created_at,DATE_SUB(NOW(), INTERVAL " . $days * ($i + 1) . " day)), \n LEAST(DATE_SUB(NOW(), INTERVAL " . $days * $i . " day), IFNULL(after.created_at,NOW()))) > 0\n GROUP BY before.ticket_id\n ) as " . $type[0] . "_{$index} ";
$temp = DB::select(DB::raw($query));
foreach ($temp[0] as $key => $value) {
if ($key != "date") {
$div_key = str_replace(" ", "_", $group->name);
if ($i == 0) {
$result[$div_key][$key]['current'] = round($temp[0]->{$key}, 2);
}
if ($i == 1) {
$result[$div_key][$key]['previous'] = round($temp[0]->{$key}, 2);
}
$result[$div_key][$key]['historical'][$temp[0]->date] = $temp[0]->{$key};
}
}
}
}
}
return $result;
}
public function edit($id)
{
$company_person = CompanyPerson::find($id);
if (Auth::user()->can('update-contact') || Auth::user()->active_contact->id == $id && Auth::user()->can('update-own-contact') || !$company_person->isE80() && Auth::user()->can('update-customer-contact')) {
$data['title'] = "Edit Contact";
$data['titles'] = Title::orderBy("name")->get();
$data['departments'] = Department::orderBy("name")->get();
$data['companies'] = Company::orderBy("name")->get();
$data['contact'] = CompanyPerson::find($id);
$data['divisions'] = Division::orderBy("name")->get();
$data['groups'] = Group::where("group_type_id", "=", $company_person->group_type_id)->orderBy("name")->get();
return view('company_person/edit', $data);
} else {
return redirect()->back()->withErrors(['Access denied to contacts edit page']);
}
}
