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) { if (Auth::user()->can('update-ticket')) { $data['ticket'] = self::API()->find(['id' => $id]); $temp = DB::table("ticket_links")->where("ticket_id", "=", $id)->get(); foreach ($temp as $elem) { $links[] = $elem->linked_ticket_id; } $data['ticket']['linked_tickets_id'] = isset($links) ? implode(",", $links) : ''; $data['companies'] = Company::where('id', '!=', ELETTRIC80_COMPANY_ID)->orderBy('name')->get(); $data['priorities'] = Priority::orderBy('id', 'desc')->get(); $data['divisions'] = Division::orderBy('name')->get(); $data['job_types'] = JobType::orderBy('name')->get(); $data['levels'] = Level::orderBy('name')->get(); $data['assignees'] = CompanyPersonController::API()->all(["where" => ["companies.id|=|" . ELETTRIC80_COMPANY_ID], "order" => ["people.last_name|ASC", "people.first_name|ASC"], "paginate" => "false"]); $data['companies'] = CompaniesController::API()->all(['where' => ['companies.id|!=|' . ELETTRIC80_COMPANY_ID], 'order' => ['companies.name|ASC'], 'paginate' => 'false']); $data['tags'] = ""; foreach ($data['ticket']->tags as $tag) { $data['tags'] .= $tag->name . ","; } $is_draft = $data['ticket']->status_id == TICKET_DRAFT_STATUS_ID ? true : false; $data['title'] = "Edit Ticket #" . $id; return view('tickets/edit', $data); } else { return redirect()->back()->withErrors(['Access denied to tickets edit page']); } }