File manager

File manager - Edit - /home/autoph/public_html/projects/tickets-autohub/app/Models/Report.php

Back
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; use Illuminate\Support\Facades\Auth; use Illuminate\Support\Facades\DB; use Illuminate\Support\Carbon; class Report extends Model { use HasFactory; public function getCompanyDetails($id) { $company = Company::findOrFail($id); return $company; } public function getDealerDetails($id) { $dealer = Dealer::findOrFail($id); return $dealer; } public function ticket_counts($dept_id, $date_range) { $query = DB::table('ticket_user') ->where('assigned_dept', $dept_id); $query2 = DB::table('ticket_user') ->where('assigned_dept', $dept_id)->when(['pending', 'open', 'ongoing'], function ($query) { return $query->whereIn('status', ['pending', 'open', 'ongoing']); }); $query3 = DB::table('ticket_user') ->where('assigned_dept', $dept_id) ->where('status', 'transferred'); $query4 = DB::table('ticket_user') ->where('assigned_dept', $dept_id) ->where('status', 'assigned'); $query5 = DB::table('ticket_user') ->where('assigned_dept', $dept_id) ->where('ticket_condition', 'picked'); $query6 = DB::table('ticket_user') ->where('assigned_dept', $dept_id) ->where('status', 'closed'); $query7 = DB::table('carplus_tickets'); $query8 = DB::table('carplus_tickets') ->where('status', 'open'); $query9 = DB::table('carplus_tickets') ->where('status', 'closed'); // $carplusTicketTotal = $this->vehicle::whereBetween('created_at',$date_range)->count(); // $carplusTicketOpen = DB::table('carplus_tickets')->whereBetween('created_at',$date_range)->where('status','open')->count(); // $carplusTicketClosed = DB::table('carplus_tickets')->whereBetween('created_at',$date_range)->where('status','closed')->count(); if (!empty($date_range)) { $query->whereBetween('created_at', $date_range); $query2->whereBetween('created_at', $date_range); $query3->whereBetween('created_at', $date_range); $query4->whereBetween('created_at', $date_range); $query5->whereBetween('created_at', $date_range); $query6->whereBetween('created_at', $date_range); $query7->whereBetween('created_at', $date_range); $query8->whereBetween('created_at', $date_range); $query9->whereBetween('created_at', $date_range); } $totalTicketCount = $query->count('tix_id'); $totalOpenTicketCount = $query2->count('tix_id'); $totalTransferredTicketCount = $query3->count('tix_id'); $totalAssignedTicketCount = $query4->count('tix_id'); $totalPickedTicketCount = $query5->count('tix_id'); $totalClosedTicketCount = $query6->count('tix_id'); $carplusTicketTotal = $query7->count(); $carplusTicketOpen = $query8->count(); $carplusTicketClosed = $query9->count(); // $totalClosedTicketCount = DB::table('ticket_user') // ->where('assigned_dept', $dept_id) // ->where('status', 'closed') // ->whereBetween('created_at',$date_range) // ->count('tix_id'); // $fromWeek = Carbon::now()->subDay(7); // $toWeek = Carbon::now(); // $fromMonth = Carbon::now()->subDay(30); // $toMonth = Carbon::now(); // $totalTicketPerWeek = DB::table('ticket_user') // ->where('assigned_dept', $dept_id) // ->whereBetween('created_at',[$fromWeek,$toWeek]) // ->count('tix_id'); // $totalTicketPerMonth = DB::table('ticket_user') // ->where('assigned_dept', $dept_id) // ->whereBetween('created_at',[$fromMonth,$toMonth]) // ->count('tix_id'); $response = [ $totalTicketCount, $totalOpenTicketCount, $totalTransferredTicketCount, $totalAssignedTicketCount, $totalPickedTicketCount, $totalClosedTicketCount, $carplusTicketTotal, $carplusTicketOpen, $carplusTicketClosed ]; return $response; } public function getSupportCountFrequency($department_id) { // $query = "SELECT CONCAT(users.f_name,' ',users.l_name) as support, COUNT() as occurrence_count // FROM ticket_user // LEFT OUTER JOIN users ON ticket_user.assigned_to = users.id // WHERE users.department_id = $department_id // AND ticket_user.status = 'closed' // GROUP BY ticket_user.assigned_to "; // return DB::select($query); $query = "SELECT CONCAT(users.f_name, ' ', users.l_name) as support, COUNT() as occurrence_count FROM ticket_user LEFT OUTER JOIN users ON ticket_user.assigned_to = users.id WHERE users.department_id = $department_id AND ticket_user.status = 'closed' GROUP BY ticket_user.assigned_to ORDER BY occurrence_count DESC LIMIT 5"; return $query; return DB::select($query); } // public function getSupportSLAReportQuery() // { // return "SELECT %s // FROM ticket_user // LEFT OUTER JOIN users ON ticket_user.assigned_to = users.id // INNER JOIN ticket ON ticket_user.tix_id = ticket.id // INNER JOIN categories ON ticket.subject = categories.id // INNER JOIN service_level_agreement AS sla ON categories.sla_id = sla.id // WHERE 1 // -- AND ticket_user.status = 'closed' // %s"; // } // public function support_sla($array_data) // { // $fields = " CONCAT(users.f_name,' ',users.l_name) as support, sla.content, CONCAT(ticket.time_elapsed,' MINS.') as time_elapsed, ticket.ticket_no, categories.title as category, CONCAT(sla.content,' MINS.') as content, // CASE WHEN ticket.time_elapsed > sla.content THEN 'Over SLA' // WHEN ticket.time_elapsed = sla.content // THEN 'Within SLA' ELSE 'Beyond SLA' // END AS sla_status, ticket.created_at, ticket.start_date, ticket.end_date, ticket.level "; // $query = sprintf( // $this->getSupportSLAReportQuery(), // $fields, // $array_data['where'], // ); // // dd($query); // return DB::select($query); // } public function support_sla_pdf($results, $type, $id, $status) { $data = []; $grpData = new \stdClass(); $department = Department::where('id', $id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); $grpData->status = $status ?? " of all "; $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Support SLA Masterfile"; $reportData = [ 'data' => $data, 'webpage_title' => "Ticket Support Compliance Report", 'report_title' => $report_title, 'table_headers' => ['No.', 'Ticket No.', 'Support', 'Date Created', 'Date Started', 'Date Ended', 'SLA', 'Elapsed Time', 'SLA Status'], 'table_body' => ['ticket_no', 'support', 'created_at', 'start_date', 'end_date', 'content', 'time_elapsed', 'sla_status'] ]; return $reportData; } public function support_sla_csv($results) { } public function support_ticket_count($array_data) { $fields = " CONCAT(users.f_name,' ',users.l_name) as SUPPORT, COUNT() as occurrence_count "; $query = sprintf( $this->getSupportTicketCountReportQuery(), $fields, $array_data['where'], ); return $query; return DB::select($query); } public function getSupportTicketCountReportQuery() { return "SELECT %s FROM ticket_user LEFT OUTER JOIN users ON ticket_user.assigned_to = users.id WHERE 1 %s GROUP BY ticket_user.assigned_to "; } public function support_ticket_count_pdf($results, $type, $id, $status) { $data = []; $grpData = new \stdClass(); $department = Department::where('id', $id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); $grpData->status = $status ?? " all "; $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Ticket Count per Support Masterfile"; $reportData = [ 'data' => $data, 'webpage_title' => "Ticket Count per Support", 'report_title' => $report_title, 'table_headers' => ['No.', 'Support', 'Count'], 'table_body' => ['SUPPORT', 'occurrence_count'] ]; return $reportData; } public function getCarplusTicketCountReportQuery() { return "SELECT %s FROM carplus_tickets cpt INNER JOIN vehicles ON cpt.vehicle_id = vehicles.id INNER JOIN users crtr ON cpt.ticket_creator_id = crtr.id LEFT OUTER JOIN users assess ON cpt.assessed_by_id = assess.id LEFT OUTER JOIN vehicle_notes vn ON vehicles.id = vn.vehicle_id LEFT OUTER JOIN users usr_vn ON vn.created_by = usr_vn.id WHERE 1 %s "; } public function carplus_ticket_count($array_data) { // $fields = " cpt., CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id, CONCAT(crtr.f_name,' ',crtr.l_name) as creator, CONCAT(assess.f_name,' ',assess.l_name) as assessor, crtr.comp_code, crtr.deal_code, DATE_FORMAT(cpt.created_at,'%b. %d, %Y') as date_created "; $query = sprintf( $this->getCarplusTicketCountReportQuery(), $array_data['fields'], $array_data['where'], ); return DB::select($query); } public function carplus_ticket_count_pdf($results, $filter_data) { $data = []; $grpData = new \stdClass(); // $department = Department::where('id',$id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); $grpData->start_date = $filter_data[0]; $grpData->end_date = $filter_data[1]; $grpData->status = $filter_data[2]; // $grpData->department = $department->dept_name; array_push($data, $grpData); switch ($filter_data[3]) { case 'masterfile': $report_title = "Carplus Ticket Masterfile"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Sender', 'Company', 'Dealer', 'Assessed By', 'Status', 'Date Created', 'Date Closed'], 'table_body' => ['carplus_id', 'creator', 'comp_code', 'deal_code', 'assessor', 'status', 'date_created', 'date_closed'] ]; break; case 'cust_info': $report_title = "Carplus Ticket Customer Info"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Customer Name', 'Address', 'Mobile', 'Email'], 'table_body' => ['carplus_id', 'customer_name', 'owner_address', 'owner_phone', 'owner_email'] ]; break; case 'vhcl_info': $report_title = "Carplus Vehicle Information"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Make', 'Model', 'Year', 'VIN', 'License Plate', 'Color'], 'table_body' => ['carplus_id', 'vehicle_make', 'vehicle_model', 'vehicle_year', 'vehicle_vin', 'license_plate', 'vehicle_color'] ]; break; case 'ownshp_info': $report_title = "Carplus Ownership Info"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Purchase Date', 'Purchase Price', 'Dealer Location', 'Financing Details'], 'table_body' => ['carplus_id', 'purchase_date', 'purchase_price', 'dealer_location', 'financing_details'] ]; break; case 'reg_info': $report_title = "Carplus Registration Info"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Registration Expiry', 'Registration Number', 'Registration Date', 'Insurance Provider', 'Policy Number', 'Policy Expiry'], 'table_body' => ['carplus_id', 'registration_expiry', 'registration_number', 'registration_state', 'insurance_provider', 'policy_number', 'policy_expiry'] ]; break; case 'main_info': $report_title = "Carplus Maintenance Info"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Last Service Date', 'Last Service Type', 'Next Service Due', 'Service Provider'], 'table_body' => ['carplus_id', 'last_service_date', 'last_service_type', 'next_service_due', 'service_provider'] ]; break; case 'notes_info': $report_title = "Carplus Notes Info"; $reportData = [ 'data' => $data, 'webpage_title' => "Carplus Ticket Count Masterfile", 'report_title' => $report_title, 'table_headers' => ['No.', 'Carplus Id', 'Note', 'Sender', 'Date Created'], 'table_body' => ['carplus_id', 'note', 'sender', 'created_at'] ]; break; default: break; } return $reportData; } public function carplus_ticket_csv($results, $filter_data) { $response = []; switch ($filter_data[3]) { case 'masterfile': $response[] = [ 'No.', 'Carplus Id', 'Sender', 'Company', 'Dealer', 'Assessed By', 'Status', 'Date Created', 'Date Closed' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->creator, $row->comp_code, $row->deal_code, $row->assessor, $row->status, $row->date_created, $row->date_closed ]; } $file_name = 'carplus-masterfile.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; case 'cust_info': $response[] = [ 'No.', 'Carplus Id', 'Owner Name', 'Owner Address', 'Owner Mobile', 'Owner Email' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->customer_name, $row->owner_address, $row->owner_phone, $row->owner_email, ]; } $file_name = 'carplus-masterfile.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; case 'vhcl_info': $response[] = [ 'No.', 'Carplus Id', 'Make', 'Model', 'Year', 'VIN', 'License Plate', 'Color' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->vehicle_make, $row->vehicle_model, $row->vehicle_year, $row->vehicle_vin, $row->license_plate, $row->vehicle_color, ]; } $file_name = 'carplus-owner-info.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; case 'ownshp_info': $response[] = [ 'No.', 'Carplus Id', 'Purchase Date', 'Purchase Price', 'Dealer Location', 'Financing Details' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->purchase_date, $row->purchase_price, $row->dealer_location, $row->financing_details, ]; } $file_name = 'carplus-ownership-info.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; case 'reg_info': $response[] = [ 'No.', 'Carplus Id', 'Registration Expiry', 'Registration Number', 'Registration Date', 'Insurance Provider', 'Policy Number', 'Policy Expiry' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->registration_expiry, $row->registration_number, $row->registration_state, $row->insurance_provider, $row->policy_number, $row->policy_expiry, ]; } $file_name = 'carplus-registration-info.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; case 'main_info': $response[] = [ 'No.', 'Carplus Id', 'Last Service Date', 'Last Service Type', 'Next Service Due', 'Service Provider' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->last_service_date, $row->last_service_type, $row->next_service_due, $row->service_provider, ]; } $file_name = 'carplus-maintenance-info.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; case 'notes_info': $response[] = [ 'No.', 'Carplus Id', 'Note', 'Sender', 'Date Created' ]; foreach ($results as $key => $row) { $response[] = [ $key += 1, $row->carplus_id, $row->note, $row->sender, $row->created_at, ]; } $file_name = 'carplus-notes-info.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); break; default: # code... break; } } public function getTicketCountReportQuery() { return " SELECT %s FROM ticket_user tckt_usr INNER JOIN ticket tckt ON tckt_usr.tix_id = tckt.id INNER JOIN categories ctg ON tckt.subject = ctg.id LEFT OUTER JOIN service_level_agreement sla ON ctg.sla_id = sla.id INNER JOIN users sender ON tckt.ticket_creator = sender.id LEFT OUTER JOIN users support ON tckt_usr.assigned_to = support.id WHERE 1 %s "; } public function totalNumberOfTicketPerCompany($array_data) { // dd($array_data); $array_data['fields'] = " CONCAT(sender.f_name,' ',sender.l_name) as sender, CONCAT(support.f_name,' ',support.l_name) as support, CONCAT(tckt.time_elapsed,' MINS.') as time_elapsed, ctg.title as category, CONCAT(sla.content,' MINS.') as content, CASE WHEN tckt.time_elapsed > sla.content THEN 'Over SLA' WHEN tckt.time_elapsed = sla.content THEN 'Within SLA' ELSE 'Beyond SLA' END AS sla_status, CASE WHEN tckt_usr.ticket_condition = 'transferred' THEN 'Transferred' WHEN tckt_usr.ticket_condition = 'picked' THEN 'Picked' WHEN tckt_usr.ticket_condition = 'assigned' THEN 'Assigned' ELSE 'NULL' END AS ticket_condition, tckt.ticket_no, tckt.created_at, tckt.start_date, tckt.end_date, tckt.level, CASE WHEN tckt.status = 'open' THEN 'Open' ELSE 'Closed' END AS status, sender.comp_code, sender.deal_code "; $query = sprintf( $this->getTicketCountReportQuery(), $array_data['fields'], $array_data['where'], ); return DB::select($query); } public function totalNumberOfTicketPerCompanyPdf($results, $filter_data) { $data = []; $grpData = new \stdClass(); $grpData->list = $results; $grpData->total = sizeOf($results); array_push($data, $grpData); if ($filter_data[2] !== '' && $filter_data[3] === '') { $report_title = $filter_data[2] . " Ticket Summary"; } else if ($filter_data[2] === '' && $filter_data[3] === '') { $report_title = "Ticket Summary"; } else if ($filter_data[2] !== '' && $filter_data[3] !== '') { $report_title = $filter_data[2] . "/" . $filter_data[3] . " Ticket Summary"; } // if(($filter_data[2]) && ($filter_data[3] === '')){ // $report_title = $filter_data[2]." Ticket Summary"; // } else if (($filter_data[2] === '') && ($filter_data[3] === '')) { // $report_title = "Ticket Summary"; // } else if (($filter_data[2]) && ($filter_data[3])){ // $report_title = $filter_data[2]."/".$filter_data[3]." Ticket Summary" ; // } $reportData = [ 'data' => $data, 'webpage_title' => "", 'report_title' => $report_title, 'table_headers' => ['No.', 'Ticket No.', 'Support', 'Date Created', 'Date Started', 'Date Ended', 'SLA', 'Elapsed Time', 'SLA Status', 'Ticket Status', 'Ticket Ccondition'], 'table_body' => ['ticket_no', 'support', 'created_at', 'start_date', 'end_date', 'content', 'time_elapsed', 'sla_status', 'status', 'ticket_condition'] ]; return $reportData; } public function categorySummaryReportQuery() { return " SELECT %s FROM `categories` `ctg` WHERE 1 AND `ctg`.`parent_id` is null %s "; } public function categorySummaryReport($array_data) { $query = sprintf( $this->categorySummaryReportQuery(), $array_data['fields'], $array_data['where'], ); return DB::select($query); } public function childCategorySummaryReportQuery() { return " SELECT %s FROM `categories` `ctg` INNER JOIN `categories` `child` ON `child`.`parent_id` = `ctg`.`id` LEFT OUTER JOIN `service_level_agreement` `sla` ON `child`.`sla_id` = `sla`.`id` WHERE 1 %s "; } public function childCategorySummaryReport($array_data) { $query = sprintf( $this->childCategorySummaryReportQuery(), $array_data['fields'], $array_data['where'], ); return DB::select($query); } public function CategorySummaryReportPdf($results) { $data = []; $grpData = new \stdClass(); // $department = Department::where('id',$id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); // $grpData->status = $status ?? " all "; // $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Category Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Category Summary Report", 'report_title' => $report_title, 'table_headers' => ['No.', 'Category', 'Status', 'Date Created'], 'table_body' => ['title', 'status', 'created_at'] ]; return $reportData; } // public function SubCategorySummaryReportPdf($childWithParent){ // $data = []; // $grpData = new \stdClass(); // // $department = Department::where('id',$id)->first(); // $grpData->list = $childWithParent; // $grpData->total = sizeOf($childWithParent); // // $grpData->status = $status ?? " all "; // // $grpData->department = $department->dept_name; // array_push($data, $grpData); // $report_title = "Sub-Category Summary Report"; // $reportData = [ // 'data' => $data, // 'webpage_title' => "Category Summary Report", // 'report_title' => $report_title, // 'table_headers' => ['No.', 'Category'], // 'table_body' => ['category'] // ]; // return $reportData; // } public function SubCategorySummaryReportPdf($childWithParent) { $data = []; $grpData = new \stdClass(); $grpData->list = $childWithParent; $grpData->total = count($childWithParent); array_push($data, $grpData); $report_title = "Sub-Category Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Category Summary Report", 'report_title' => $report_title, 'table_headers' => ['No.', 'Category', 'Date Created'], // 'table_body' => ['category','created_at'] 'category' => $childWithParent ]; return $reportData; } public function CategoryWithSLASummaryReportPdf($results) { $data = []; $grpData = new \stdClass(); // $department = Department::where('id',$id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); // $grpData->status = $status ?? " all "; // $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Category w/ SLA Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Category Summary Report", 'report_title' => $report_title, 'table_headers' => ['No.', 'Category', 'Sub-Category', 'SLA', 'Date Created'], 'table_body' => ['category', 'sub-category', 'sla', 'created_at'] ]; return $reportData; } public function category_csv($results) { $response = []; $response[] = ['No.', 'Category', 'Status', 'Date Created']; $inc = 0; foreach ($results as $key => $row) { $inc++; $response[] = [ $inc, $row->title, $row->status, $row->created_at, ]; } $file_name = "Category_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } // public function sub_category_csv($results){ // $response = []; // $response[] = ['No.','Category','Date Created']; // $inc = 0; // foreach ($results as $key => $row) { // $inc++; // $parent = [ // $inc, // $row['category'], // $row['created_at'], // ]; // $children = []; // foreach ($row['children'] as $item) { // $child = [ // $item['sub-category'], // $item['created_at'] // ]; // $children[] = $child; // } // $response[] = [ // 'parent' => $parent, // 'children' => $children // ]; // } // $file_name = "Access_Summary-".date('Y-m-d').'.csv'; // header('Content-Type: text/csv'); // header('Content-Disposition: attachment; filename="'.$file_name.'"'); // $f = fopen('php://output', 'wb'); // if($f === false){ // die('Error opening the file '.$file_name); // } // if(empty($response[1])){ // $arr = [ // "No data to show" // ]; // fputcsv($f,$arr); // } else { // foreach($response['parent'] as $row){ // fputcsv($f, $row, ','); // // foreach($response['children'] as $child){ // // fputcsv($f, $child, ','); // // } // } // } // fclose($f); // } public function sub_category_csv($results) { $file_name = "Sub-Category_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } // Write the header row fputcsv($f, ['No.', 'Category', 'Date Created']); $inc = 0; foreach ($results as $key => $row) { $inc++; // Write the parent row fputcsv($f, [ $inc, $row['category'], $row['created_at'], ]); // Write the child rows foreach ($row['children'] as $item) { fputcsv($f, [ '', // No. left empty for indentation $item['sub-category'], $item['created_at'] ]); } } fclose($f); } public function cat_sla_csv($results) { $response = []; $response[] = ['No.', 'Access', 'Date Created']; $inc = 0; foreach ($results as $key => $row) { $inc++; $response[] = [ $inc, $row->title, $row->created_at, ]; } $file_name = "Access_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } public function slaSummaryReportQuery() { return " SELECT %s FROM `service_level_agreement` `sla` WHERE 1 AND `sla`.`status` is not null %s "; } public function slaSummaryReport($array_data) { $fields = " CONCAT(`sla`.`content`,' ',`sla`.`type`) as `sla`, `sla`.`created_at` "; $query = sprintf( $this->slaSummaryReportQuery(), // $array_data['fields'], $fields, $array_data['where'], ); return DB::select($query); } public function slaSummaryReportPdf($results) { $data = []; $grpData = new \stdClass(); // $department = Department::where('id',$id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); // $grpData->status = $status ?? " all "; // $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Category Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Category Summary Report", 'report_title' => $report_title, 'table_headers' => ['No.', 'SLA', 'Date Created'], 'table_body' => ['sla', 'created_at'] ]; return $reportData; } public function sla_csv($array_data) { $response = []; $response[] = ['No.', 'SLA', 'Date Created']; $inc = 0; foreach ($array_data as $key => $row) { $inc++; $response[] = [ $inc, $row->sla, $row->created_at, ]; } $file_name = "SLA_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } public function my_ticket_summary_csv($array_data) { $response = []; $response[] = ['No.', 'Ticket No.', 'Category', 'SLA']; $inc = 0; foreach ($array_data as $key => $row) { $inc++; $response[] = [ $inc, $row->ticket_no, $row->title, $row->content, ]; } $file_name = "My_Ticket_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } public function department_ticket_summary_csv($array_data) { $response = []; $response[] = ['No.', 'Ticket No.', 'Sender', 'Position', 'Category', 'SLA', 'Status']; $inc = 0; foreach ($array_data as $key => $row) { $inc++; $response[] = [ $inc, $row->ticket_no, $row->sender, $row->position, $row->title, $row->content, $row->status, ]; } $file_name = "Department_Ticket_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } public function roleSummaryReportQuery() { return "SELECT %s FROM `roles` WHERE 1 %s "; } public function getRoleSummary($array_data) { $fields = " `roles`.`title`, `roles`.`created_at` "; $query = sprintf( $this->roleSummaryReportQuery(), // $array_data['fields'], $fields, $array_data['where'], ); return DB::select($query); } public function getRoleSummaryPdf($results, $status) { $data = []; $grpData = new \stdClass(); // $department = Department::where('id',$id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); // $grpData->status = $status ?? " all "; // $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Roles Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Roles Summary Report", 'report_title' => $report_title, 'status' => $status, 'table_headers' => ['No.', 'Role', 'Date Created'], 'table_body' => ['title', 'created_at'] ]; return $reportData; } public function getRoleSummaryCsv($results, $status) { $response = []; $response[] = ['No.', 'Role', 'Date Created']; $inc = 0; foreach ($results as $key => $row) { $inc++; $response[] = [ $inc, $row->title, $row->created_at, ]; } $file_name = "Roles_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { fputcsv($f, ['STATUS', $status], ','); foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } public function accessSummaryReportQuery() { return " SELECT %s FROM `abilities` WHERE 1 AND `parent_category` is not null %s "; } public function getAccessSummary($array_data) { $fields = " `abilities`.`title`, `abilities`.`created_at` "; $query = sprintf( $this->accessSummaryReportQuery(), $fields, $array_data['where'], ); return DB::select($query); } public function getAccessSummaryPdf($results) { $data = []; $grpData = new \stdClass(); // $department = Department::where('id',$id)->first(); $grpData->list = $results; $grpData->total = sizeOf($results); // $grpData->status = $status ?? " all "; // $grpData->department = $department->dept_name; array_push($data, $grpData); $report_title = "Access Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Access Summary Report", 'report_title' => $report_title, 'table_headers' => ['No.', 'Access', 'Date Created'], 'table_body' => ['title', 'created_at'] ]; return $reportData; } public function getAccessSummaryCsv($results) { $response = []; $response[] = ['No.', 'Access', 'Date Created']; $inc = 0; foreach ($results as $key => $row) { $inc++; $response[] = [ $inc, $row->title, $row->created_at, ]; } $file_name = "Access_Summary-" . date('Y-m-d') . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $file_name . '"'); $f = fopen('php://output', 'wb'); if ($f === false) { die('Error opening the file ' . $file_name); } if (empty($response[1])) { $arr = [ "No data to show" ]; fputcsv($f, $arr); } else { foreach ($response as $row) { fputcsv($f, $row, ','); } } fclose($f); } }

| ver. 1.4 | . | PHP 8.1.32 | Generation time: 0 | proxy | phpinfo | Settings