File manager

File manager - Edit - /home/autoph/public_html/projects/tickets-autohub/app/Http/Controllers/ReportController.php

Back
<?php namespace App\Http\Controllers; use App\Models\Dealer; use App\Models\Report; use App\Models\Role as ModelsRole; use App\Models\Support; use App\Models\Ticket; use App\Models\TicketUser; use App\Models\User; use App\Models\Department; use Carbon\Carbon; use Illuminate\Http\Request; use Illuminate\Support\Facades\App; use Illuminate\Support\Facades\Auth; use Illuminate\Support\Facades\DB; use Silber\Bouncer\Database\Role; use Silber\Bouncer\BouncerFacade as Bouncer; use Silber\Bouncer\Database\Queries\Roles; use Illuminate\Support\Str; class ReportController extends Controller { private $support, $ticket, $user, $report, $role, $auth; public $data=[]; public function __construct(){ $this->user = new User; $this->ticket = new Ticket; $this->report = new Report; $this->role = new ModelsRole(); $this->auth = new AuthenticationController; } public function index(){ if($this->auth->checkMaintenance() == "true"){ abort(503); } $role = Bouncer::role()->where('id', 10)->firstOrFail(); // echo'<pre>';print_r($role);echo'<pre>'; // $selectedAbilities = $role->getAbilities()->pluck('name','id','title'); // foreach($selectedAbilities as $index => $ability) // { // echo'<pre>';print_r($index.' : '.$ability);echo'</pre>'; // } // echo'<pre>';print_r($selectedAbilities);echo'<pre>'; return view('layouts.authentication.admin.report.index')->with(['title_page'=>"Reports"]); } public function reportDashboard(){ return view('layouts.authentication.admin.report.blank'); } public function ticketsDashboard(){ $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); } return view('layouts.authentication.admin.report.tickets_masterfile',$this->data); } //modify public function ticketsExecutiveReport(){ $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); } return view('layouts.authentication.admin.report.tickets_executive_report',$this->data); } private function companylist($array_com_id,$full_access) { if($full_access===1) { $res = DB::select("select id,comp_code,comp_name from companies order by comp_name asc"); } else { // $res = DB::select("select id,comp_code,comp_name from companies where order by comp_name asc"); $res = DB::table('companies')->whereIn('id', $com_id_array) ->select('id', 'comp_name')->orderBy('name')->get(); } return $res; } private function loadDealers($com_id_array,$full_access) { if($full_access===1) { $dealers = DB::table('dealers')->select('id','dealer_code', 'dealer_name')->orderby('dealer_name')->get(); } else { $dealers = DB::table('dealers')->whereIn('comp_id', $com_id_array) ->select('id','dealer_code','dealer_name')->orderby('dealer_name')->get(); } return $dealers; } public function supportsDashboard(){ return view('layouts.authentication.admin.report.supports_masterfile'); } public function supportsSLADashboard(){ return view('layouts.authentication.admin.report.support_sla_masterfile'); } public function supportsTicketCount(){ $this->data['currentRole'] = $this->role::UserRole()[0]->role_id; $data = \App\Models\Department::select('id','dept_name'); if(!in_array($this->data['currentRole'],[1,9])){ $data = $data->where('id',Auth::user()->department_id); } $this->data['departments'] = $data->orderBy('dept_name','asc')->get(); return view('layouts.authentication.admin.report.support_ticket_count', $this->data); } //modify public function technical_summary_report(){ // DB::enableQueryLog(); $this->data['list_tech_personnel'] = $this->role::usersByRole(); // dd( $this->role::usersByRole()); // $queryLog = DB::getQueryLog(); // $lastQuery = end($queryLog)['query']; // echo'<pre>';print_r($list_tech_personnel);echo'</pre>';exit; return view ('layouts.authentication.admin.report.technical_summary_report',$this->data); } public function annual_support_summary(){ // DB::enableQueryLog(); $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); $this->data['departments'] = \App\Models\Department::select('id','dept_name')->where('id',Auth::user()->department_id)->get(); } return view ('layouts.authentication.admin.report.annual_support_summary',$this->data); } public function monthly_support_summary(){ // DB::enableQueryLog(); $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); $this->data['departments'] = \App\Models\Department::select('id','dept_name')->where('id',Auth::user()->department_id)->get(); } return view ('layouts.authentication.admin.report.monthly_support_summary',$this->data); } public function monthly_technical_support_summary(){ // DB::enableQueryLog(); $this->data['list_tech_personnel'] = $this->role::usersByRole(); $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); $this->data['departments'] = \App\Models\Department::select('id','dept_name')->where('id',Auth::user()->department_id)->get(); $this->data['categories'] = \App\Models\Category::select('id','title') ->where('dept_id', Auth::user()->department_id) ->where('status', 1) ->where('parent_id', null) ->where('id', '!=', 246) ->get(); } return view ('layouts.authentication.admin.report.monthly_technical_support_summary',$this->data); } public function individual_support_summary(){ // DB::enableQueryLog(); $this->data['list_tech_personnel'] = $this->role::usersByRole(); $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); $this->data['departments'] = \App\Models\Department::select('id','dept_name')->where('id',Auth::user()->department_id)->get(); $this->data['categories'] = \App\Models\Category::select('id','title') ->where('dept_id', Auth::user()->department_id) ->where('status', 1) ->where('parent_id', null) ->where('id', '!=', 246) ->get(); } return view ('layouts.authentication.admin.report.individual_support_summary',$this->data); } public function carplusTicketCount(){ return view('layouts.authentication.admin.report.carplus_masterfile'); } public function ticketSummaryReport(){ return view('layouts.authentication.admin.report.ticket_summary_report'); } public function categorySummaryReport(){ return view('layouts.authentication.admin.report.category_masterfile'); } public function slaSummaryReport(){ return view('layouts.authentication.admin.report.sla_masterfile'); } public function departmentTicketSummaryReport(){ return view('layouts.authentication.admin.report.department_ticket_summary'); } public function assignedTicketSummaryReport(){ return view('layouts.authentication.admin.report.assigned_tickets.masterfile'); } public function roleSummaryReport(){ return view('layouts.authentication.admin.report.roles_masterfile'); } public function accessSummaryReport(){ return view('layouts.authentication.admin.report.access_masterfile'); } public function myTicketsSummaryReport(){ return view('layouts.authentication.admin.report.mytickets_masterfile'); } public function solutionSummaryReport(){ return view(''); } public function ticketsMasterfile(Request $request){ $array_data['type'] = $request->type; $com_code = $request->comp_code; $dealer_code = $request->dealer_code; $array_data['search'] = ""; $array_data['where'] = ""; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`ticket`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; $array_data['where'] .= (!empty($com_code) ? " AND `usr`.`comp_code` IN ('" . implode("','", $com_code) . "') " : ''); $array_data['where'] .= (!empty($dealer_code) ? " AND `usr`.`deal_code` IN ('" . implode("','", $dealer_code) . "')" : ''); } $results = $this->ticket->reports($array_data); // echo $results;exit; // $res =[]; // DB::table('ticket')->select( // 'ticket_no', // DB::raw('DATE_FORMAT(ticket.updated_at, "%b. %d, %Y") as date_created'), // DB::raw('DATE_FORMAT(ticket.start_date,"%b. %d, %Y \| %H:%i") as date_started'), // DB::raw('DATE_FORMAT(ticket.end_date,"%b. %d, %Y \| %H:%i") as date_ended'), // 'status as capitalized_status') // ->where('created_at','>=',$fromdate)->where('created_at','<=',$todate)->orderBy('id')->lazy()->each(function (object $ticket) use (&$res) { // $res[]= $ticket; // }); // DB::table('ticket') // ->select('ticket_no', 'updated_at as date_created', 'start_date as date_started', 'end_date as date_ended', 'status as capitalized_status') // ->where('created_at', '>=', $fromdate) // ->where('created_at', '<=', $todate) // ->orderBy('id') // ->take(100) // ->each(function ($ticket) use (&$res) { // $ticket->date_created = date('M. d, Y', strtotime($ticket->date_created)); // $ticket->date_started = date('M. d, Y \| H:i', strtotime($ticket->date_started)); // $ticket->date_ended = date('M. d, Y \| H:i', strtotime($ticket->date_ended)); // $res[] = $ticket; // }); // dd($res); // $res_data = []; // DB::table('ticket') // ->where('created_at', '>=', '2023-11-10') // ->where('created_at', '<=', '2024-01-10') // ->orderBy('id') // ->lazy(200) // ->each(function (object $ticket) use (&$res_data) { // $res_data[] = $ticket; // }); // dd($res_data); // exit; $data = []; $grpData = new \stdClass(); $grpData->list = $results; $grpData->total = sizeOf($results); array_push($data, $grpData); $report_title = "Tickets Masterfile"; $reportData = [ 'data' => $data, 'webpage_title' => "Tickets Report", 'report_title' => $report_title, 'table_headers' => ['No.','Ticket No.','Sender','Category','Sub Category','Date Created', 'Date Started', 'Date Ended','SLA','Support by', 'Status'], 'table_body' => ['ticket_no','sender','category','sub_category','date_created','date_started','date_ended','content','support','capitalized_status' ] ]; // $response = $this->ticket->pdf($res,'view'); // return $reportData; // return $response = $this->ticket->pdf($results,'view'); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.tickets',$reportData); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("tickets-masterfile.pdf"); break; case('csv'): $this->ticket->csv($results); break; } } //modify public function ticketExecutiveMasterfile(Request $request){ $array_data['type'] = $request->type; $com_code = $request->comp_code; // $dealer_code = $request->dealer_code; $array_data['search'] = ""; $array_data['where'] = ""; $where =''; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); } DB::enableQueryLog(); $results =DB::table('ticket')->select( DB::raw("(@row := @row + 1) AS num"), 'c.comp_name AS company', DB::raw('COUNT(ticket.id) AS ticket_count'), DB::raw('SUM(ticket.status = "closed") AS total_close_ticket'), DB::raw('SUM(ticket.status = "ongoing") AS total_ongoing_ticket'), DB::raw('SUM(ticket.status = "pending") AS total_pending_ticket'), DB::raw('SUM(ticket.status = "transferred") AS total_transferred_ticket') // DB::raw('"TOTAL" AS total_label'), // DB::raw("(SELECT SUM(TicketCounts.total_close_ticket) ) AS total_close"), // DB::raw('"" AS placeholder_1'), // DB::raw('"" AS placeholder_2'), // DB::raw('"" AS placeholder_3') )->leftJoin('ticket_user AS tu', 'ticket.id', '=', 'tu.tix_id') ->leftJoin('users AS usr', 'tu.usr_id', '=', 'usr.id') ->leftJoin('companies as c','usr.comp_code','=','c.comp_code') ->leftJoin('dealers AS d', 'd.dealer_code', '=', 'usr.deal_code') ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <=?", [$fromdate, $todate]) ->groupBy('c.comp_name'); if (!empty($com_code)) { $results->whereIn('usr.comp_code', $com_code); } $results = $results->paginate(100); // $results = $results->get(); // if (!empty($dealer_code)) { // $results->whereIn('usr.deal_code', $dealer_code); // } // $queryLog = DB::getQueryLog(); // $lastQuery = end($queryLog)['query']; $data_set =[]; $data_set['total_records'] = $results->total(); $data_set['total_closed_ticket'] = 0; $data_set['total_ongoing_ticket'] =0; $data_set['total_sla_ticket'] = 0; $data_set['total_page'] = $results->lastPage(); $data_set['currentPage'] = $results->currentPage(); $data_set['perPage'] = $results->perPage(); $data_set['date_from'] = $fromdate; $data_set['date_to'] = $todate; $data_set['data'] =[]; foreach($results as $row) { $compliance_rate = $row->total_close_ticket / $row->ticket_count * 100; $row->compliance = number_format($compliance_rate,2).'%'; array_push($data_set['data'],$row); }unset($row); $count_request_result = 0; return response()->json($data_set); } //EXECUTIVE SUMMARY REPORT RAW QUERY // $query = " // SELECT // dealer, // ticket_count, // total_close_ticket, // total_ongoing_ticket, // total_pending_ticket, // total_transferred_ticket, // 'TOTAL' AS total_label, // (SELECT SUM(total_close_ticket) FROM ( // SELECT // COUNT(ticket.id) AS total_close_ticket // FROM // ticket // LEFT JOIN // ticket_user AS tu ON ticket.id = tu.tix_id // LEFT JOIN // users AS usr ON tu.usr_id = usr.id // LEFT JOIN // dealers AS d ON d.dealer_code = usr.deal_code // WHERE // ticket.status = 'closed' // GROUP BY // d.dealer_name // ) AS TicketCounts) AS total_close, // '' AS placeholder_1, // '' AS placeholder_2, // '' AS placeholder_3 // FROM ( // SELECT // d.dealer_name AS dealer, // COUNT(ticket.id) AS ticket_count, // SUM(ticket.status = 'closed') AS total_close_ticket, // SUM(ticket.status = 'ongoing') AS total_ongoing_ticket, // SUM(ticket.status = 'pending') AS total_pending_ticket, // SUM(ticket.status = 'transferred') AS total_transferred_ticket // FROM // ticket // LEFT JOIN // ticket_user AS tu ON ticket.id = tu.tix_id // LEFT JOIN // users AS usr ON tu.usr_id = usr.id // LEFT JOIN // dealers AS d ON d.dealer_code = usr.deal_code // GROUP BY // d.dealer_name // ) AS TicketCounts // "; // $tempTable = DB::raw("CREATE TEMPORARY TABLE temp_table AS ($query)"); // // Execute the query to create the temporary table // DB::statement($tempTable); // // Count the total number of results // $total = DB::table('temp_table')->count(); // // Paginate the results // $perPage = 100; // $page = request()->get('page', 1); // $offset = ($page - 1) * $perPage; // $results = DB::table('temp_table')->offset($offset)->limit($perPage)->get(); // // Drop the temporary table // DB::statement('DROP TEMPORARY TABLE IF EXISTS temp_table'); // // Create a paginator manually // $paginator = new \Illuminate\Pagination\LengthAwarePaginator($results, $total, $perPage, $page); //MODIFY august 2, 2024 public function tech_support_summary(Request $request){ if(!empty($request->from_date) && !empty($request->to_date)) { $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $user_id = $request->personnel; // $placeholders = implode(',', array_fill(0, count($user_id), '?')); $status = $request->ticket_status; $all_personnel =[]; if ($request->all_personnels == 1) { $personnels = $this->role::usersByRole(); if ($personnels > 0) { foreach ($personnels as $tech_personnel) { $all_personnel[] = $tech_personnel->user_id; } } $user_id = $all_personnel; } DB::enableQueryLog(); $results = DB::table('ticket') ->select(DB::raw("(@row := @row + 1) AS num"), 'ticket.ticket_no', DB::raw("CONCAT(REPLACE(users.f_name,'Ã±','ñ'),' ',REPLACE(users.l_name,'Ã±','ñ')) as fullname"), 'users.position', 'companies.comp_name as company', 'dealers.dealer_name as dealer', 'ctg.title as category', 'sub.title as sub_category', DB::raw("DATE_FORMAT(ticket.created_at,'%b. %d, %Y \| %r') as date_created"), DB::raw("DATE_FORMAT(ticket.start_date,'%b. %d, %Y \| %r') as start_date"), DB::raw("DATE_FORMAT(ticket.end_date,'%b. %d, %Y \| %r') as end_date"), DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as sender'), DB::raw("CONCAT(sla.content,' MINS.') as sla"), // DB::raw("CONCAT(ticket.time_elapsed, ' Mins') AS time_elapsed"), DB::raw("CONCAT(ticket.time_elapsed_time) AS time_elapsed"), 'ticket.status' ) ->join('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->join('users', 'ticket_user.assigned_to', '=', 'users.id') ->join('users as usrSender','ticket_user.usr_id',"=",'usrSender.id') ->leftJoin('companies','users.comp_code','=','companies.comp_code') ->leftJoin('dealers','users.deal_code','=','dealers.dealer_code') ->join('categories as sub', 'sub.id', '=', 'ticket.subject') ->join('categories as ctg', 'sub.parent_id', '=', 'ctg.id') // ->leftJoin('categories as ctg','ticket.subject','=','ctg.id') // ->leftJoin('categories as sub','ctg.parent_id','=','sub.id') ->leftJoin('service_level_agreement as sla','sub.sla_id','=','sla.id') ->where('ticket_user.assigned_dept',Auth::user()->department_id) ->whereIn('ticket_user.assigned_to', $user_id) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <= ?", [$fromdate, $todate]) ->orderBy('ticket.created_at'); if(!empty($status) && $status !='All'){ $results->where('ticket.status', $status); } // $results->get(); $results=$results->paginate(500); $query = DB::getQueryLog(); // dd($query); $data_set =[]; $data_set['total_records'] = $results->total(); $data_set['total_closed_ticket'] = 0; $data_set['total_ongoing_ticket'] =0; $data_set['total_sla_ticket'] = 0; $data_set['total_page'] = $results->lastPage(); $data_set['currentPage'] = $results->currentPage(); $data_set['perPage'] = $results->perPage(); // $data_set['count_return_data'] = count($results); $data_set['date_from'] = $fromdate; $data_set['date_to'] = $todate; $data_set['data'] =[]; // dd($status); $status_array = ['closed','All']; if($results->currentPage()===1) { $data_set['total_sla_ticket'] =$this->totalSlaPerSupport([$user_id,$fromdate,$todate,$status]); $data_set['total_closed_ticket'] = (in_array($status,$status_array) ? $this->getTicketByStatus(['closed',$user_id,$fromdate,$todate]) : 'N/A'); $data_set['total_ongoing_ticket'] = $this->getTicketByStatus(['pending',$user_id,$fromdate,$todate]); // $data_set['total_pending_ticket'] = $this->getClosedTicket(['pending',$user_id,$fromdate,$todate]); // $data_set['total_transfered_ticket'] = $this->getClosedTicket(['transfered',$user_id,$fromdate,$todate]); } foreach($results as $row) { // unset($row->closed_count); // unset($row->total_sla); // unset($row->ongoing_count); array_push($data_set['data'],$row); }unset($row); return response()->json($data_set); } } public function annual_support_summary_count_report(Request $request){ // dd($request->all()); $company = \App\Models\Company::where('id',$request->company)->first(); $dealer = \App\Models\Dealer::where('id',$request->dealer)->first(); $department = \App\Models\Department::select('dept_name')->where('id',$request->department_id)->first(); if(!empty($request->year) && (!empty($request->company)));// && !empty($request->dealer))) { // $from = Carbon::parse($request->from_date); $year = $request->year;//Carbon::parse($request->year); $companies = ($request->company > 0 ? $request->company : 0);//implode(',',$request->company); $dealers_id = $request->dealer;//implode(',',$request->dealer); DB::enableQueryLog(); // $results = DB::table('ticket') // ->select( // DB::raw('c.title'), // DB::raw("IFNULL(COUNT(ticket.id),0) as total_count") // ) // // ->join('categories as sub', 'ticket.subject', '=', 'sub.id') // ->leftJoin('categories as sub', fn($join) => $join // ->on('ticket.subject','=','sub.id') // ) // ->leftJoin('categories as c','sub.parent_id','=','c.id') // ->join('users', 'ticket.ticket_creator', '=', 'users.id') // ->join('companies','users.comp_code','=','companies.comp_code') // ->join('dealers','users.deal_code','=','dealers.dealer_code') // ->whereIn('c.id', [33,32,31,36,10]) // ->whereIn('companies.id', $companies); // if(isset($request->dealer) && count($request->dealer) > 0) // { // $results->whereIn('dealers.id', $dealers_id); // } // $results= $results->where(DB::raw('YEAR(ticket.created_at)'),$year) // ->groupBy('c.title')->get(); $results = DB::table('categories as c') ->select('id','title') ->whereIn('c.id',[31,32,33,36,10]) ->get(); // dd($results); // dd(DB::getQueryLog()); $data_set =[]; // $data_set['total_records'] = $results->total(); // $data_set['total_page'] = $results->lastPage(); // $data_set['currentPage'] = $results->currentPage(); // $data_set['perPage'] = $results->perPage(); // $data_set['count_return_data'] = count($results); $data_set['year'] = $year; $data_set['data'] =[]; $data_set['company_name'] =( $companies > 0 ? $company->comp_name : ''); $data_set['dealer_name'] =(isset($dealer->dealer_name) ? $dealer->dealer_name : ''); $data_set['department_name'] = $department->dept_name; foreach($results as $row) { $row->total_count = $this->getTotalCountperCategories($row->id,$year,$companies,$dealers_id); $data_set['data'][]=$row;//array_push($data_set['data'],$row);// [$row->title => $row->total_count]; }unset($row); // dd($data_set); return response()->json($data_set); } } public function monthly_support_summary_count_report(Request $request){ $company = \App\Models\Company::where('id',$request->company)->first(); $dealer = \App\Models\Dealer::where('id',$request->dealer)->first(); $department = \App\Models\Department::select('dept_name')->where('id',$request->department_id)->first(); if(!empty($request->year) && (!empty($request->company)));// && !empty($request->dealer))) { $year = $request->year;//Carbon::parse($request->year); $companies = $request->company;//implode(',',$request->company); $dealers_id = $request->dealer;//implode(',',$request->dealer); DB::enableQueryLog(); $results = DB::table('ticket') ->select( DB::raw('c.title'), DB::raw("MONTH(ticket.created_at) as month"), DB::raw("COUNT(ticket.id) as total_count") ) ->leftJoin('categories as sub', fn($join) => $join->on('ticket.subject', '=', 'sub.id')) ->leftJoin('categories as c', 'sub.parent_id', '=', 'c.id') ->join('users', 'ticket.ticket_creator', '=', 'users.id') ->join('companies', 'users.comp_code', '=', 'companies.comp_code') ->join('dealers', 'users.deal_code', '=', 'dealers.dealer_code') ->whereIn('c.id', [33, 32, 31, 36, 10]); // ->whereIn('companies.id', $companies) if($request->ticket_status !='All') { $results = $results ->where('ticket.status', $request->ticket_status); } if($companies && $companies > 0) { $results = $results ->where('companies.id', $companies); } $results= $results->whereYear('ticket.created_at', $year) ->groupBy('c.title', DB::raw("MONTH(ticket.created_at)")); if (isset($request->dealer) && count($request->dealer) > 0) { $results->whereIn('dealers.id', $dealers_id); } $results = $results->get(); $data_set =[]; // $data_set['total_records'] = $results->total(); // $data_set['total_page'] = $results->lastPage(); // $data_set['currentPage'] = $results->currentPage(); // $data_set['perPage'] = $results->perPage(); // $data_set['count_return_data'] = count($results); $data_set['year'] = $year; $data_set['data'] =[]; $data_set['company_name'] =($company ? $company->comp_name : ''); $data_set['dealer_name'] =(isset($dealer->dealer_name) ? $dealer->dealer_name : ''); $data_set['department_name'] = $department->dept_name; $monthly_data = []; foreach ($results as $row) { if (!isset($monthly_data[$row->title])) { $monthly_data[$row->title] = array_fill(1, 12, 0); // Initialize months with 0 counts } $monthly_data[$row->title][$row->month] = $row->total_count; } // Format the data into the final dataset structure foreach ($monthly_data as $title => $months) { $data_set['data'][] = [ 'category' => $title, 'monthly_counts' => $months ]; } return response()->json($data_set); } } public function monthly_technical_support_summary_count(Request $request){ $personnel =\App\Models\User::where('id',$request->personnel)->first(); $company = \App\Models\Company::where('id',$request->company)->first(); $dealer = \App\Models\Dealer::where('id',$request->dealer)->first(); $department = \App\Models\Department::select('dept_name')->where('id',$request->department_id)->first(); if(!empty($request->year) && (!empty($request->company)));// && !empty($request->dealer))) { $year = $request->year;//Carbon::parse($request->year); $companies = ($request->company > 0 ? $request->company : 0);//implode(',',$request->company); $dealers_id = $request->dealer;//implode(',',$request->dealer); DB::enableQueryLog(); $results = DB::table('ticket') ->select( DB::raw('c.title'), DB::raw("MONTH(ticket.created_at) as month"), DB::raw("COUNT(ticket.id) as total_count") ) ->leftJoin('categories as sub', fn($join) => $join->on('ticket.subject', '=', 'sub.id')) ->leftJoin('categories as c', 'sub.parent_id', '=', 'c.id') ->join('ticket_user','ticket.id','=','ticket_user.tix_id') ->join('users', 'ticket.ticket_creator', '=', 'users.id') ->join('companies', 'users.comp_code', '=', 'companies.comp_code') ->join('dealers', 'users.deal_code', '=', 'dealers.dealer_code'); if(count($request->categories) > 0) { $results = $results->whereIn('c.id', $request->categories); } if($request->ticket_status != 'All') { $results = $results->where('ticket.status',$request->ticket_status); } if($companies > 0) { $results = $results->where('companies.id', $companies); } $results= $results->whereYear('ticket.created_at', $year) ->groupBy('c.title', DB::raw("MONTH(ticket.created_at)")); if (isset($request->dealer) ) { $results->where('dealers.id', $dealers_id); } $results = $results->get(); $data_set =[]; // $data_set['total_records'] = $results->total(); // $data_set['total_page'] = $results->lastPage(); // $data_set['currentPage'] = $results->currentPage(); // $data_set['perPage'] = $results->perPage(); // $data_set['count_return_data'] = count($results); $data_set['year'] = $year; $data_set['data'] =[]; $data_set['personnel_name'] =$personnel->f_name.' '.$personnel->l_name.' - '.$personnel->position; $data_set['company_name'] =($companies > 0 ? $company->comp_name : ''); $data_set['dealer_name'] =(isset($dealer->dealer_name) ? $dealer->dealer_name : ''); $data_set['department_name'] = $department->dept_name; $monthly_data = []; foreach ($results as $row) { if (!isset($monthly_data[$row->title])) { $monthly_data[$row->title] = array_fill(1, 12, 0); // Initialize months with 0 counts } $monthly_data[$row->title][$row->month] = $row->total_count; } // Format the data into the final dataset structure foreach ($monthly_data as $title => $months) { $data_set['data'][] = [ 'category' => $title, 'monthly_counts' => $months ]; } return response()->json($data_set); } } public function individual_support_summary_count(Request $request) { // Validate incoming request // dd($request->all()); $request->validate([ 'personnel' => 'required\|array', 'personnel.' => 'exists:users,id', // Validate each personnel ID exists in the users table 'category' => 'required\|array', 'category.' => 'exists:categories,id', // Validate each personnel ID exists in the users table 'company' => 'nullable\|exists:companies,id', // Make company optional 'date_from' => 'required\|date', 'date_to' => 'required\|date\|after_or_equal:start_date', 'department_id' => 'required\|exists:departments,id', 'dealer' => 'nullable\|exists:dealers,id', ]); // Fetch required data $personnel = \App\Models\User::whereIn('id', $request->personnel)->get(); // Get all personnel $company = $request->company ? \App\Models\Company::where('id', $request->company)->first() : null; $dealer = \App\Models\Dealer::where('id', $request->dealer)->first(); $department = \App\Models\Department::select('dept_name')->where('id', $request->department_id)->first(); // Parse the start and end dates from the request $start_date = Carbon::parse($request->date_from)->startOfDay(); $end_date = Carbon::parse($request->date_to)->endOfDay(); if (!empty($start_date) && !empty($end_date)) { DB::enableQueryLog(); // Build the query $query = DB::table('ticket') ->select( DB::raw('c.title'), // Category name DB::raw('support.id as user_id'), DB::raw("COUNT(ticket.id) as total_count"), DB::raw("support.f_name as firstname"), DB::raw("support.l_name as lastname") ) ->leftJoin('categories as sub', fn ($join) => $join->on('ticket.subject', '=', 'sub.id')) ->Join('categories as c', 'sub.parent_id', '=', 'c.id') ->leftJoin('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->leftJoin('users as support', 'ticket_user.assigned_to', '=', 'support.id') ->join('users as sender', 'ticket_user.usr_id', '=', 'sender.id') ->leftjoin('companies', 'sender.comp_code', '=', 'companies.comp_code') // ->whereIn('c.id', [33, 32, 31, 36, 10]) // Filter specific categories (adjust this list as needed) ->whereIn('c.id', $request->category); if($request->ticket_status !='All') { $query = $query->where('ticket.status',$request->ticket_status); } $query= $query->whereIn('support.id', $request->personnel) // Filter by personnel ->whereRaw('DATE(ticket.created_at) >= ?', [$start_date]) ->whereRaw('DATE(ticket.created_at) <= ?', [$end_date]); // ->where('companies.id', '!=', null); // Always filter by company if provided // If a company is provided, add it as a filter condition if ($company && $company->id > 0) { $query= $query->where('companies.id', $company->id); } // If a dealer is provided, add it as a filter condition if ($dealer && $dealer->id > 0) { $query->join('dealers', 'sender.deal_code', '=', 'dealers.dealer_code') // Filter by dealer if provided ->where('dealers.id', $request->dealer); } // Execute the query $results = $query->groupBy('c.title', 'support.id')->get(); // Debugging the query (optional) // dd(DB::getQueryLog()); // Prepare the final dataset $data_set = []; $data_set['data'] = []; $data_set['company_name'] = $company ? $company->comp_name : ''; $data_set['dealer_name'] = $dealer->dealer_name ?? ''; $data_set['department_name'] = $department->dept_name; $data_set['date_from'] = $start_date; $data_set['date_to'] = $end_date; // Flatten the result by personnel and category $category_data = []; foreach ($results as $row) { $category_data[$row->firstname . ' ' . $row->lastname][$row->title] = $row->total_count; } // Format the data into the final dataset structure foreach ($category_data as $person => $categories) { $data_set['data'][] = [ 'personnel' => $person, 'categories' => $categories, ]; } return response()->json($data_set); } // If conditions are not met, return a failure response return response()->json([ 'error' => 'Missing required fields or invalid input.' ], 400); } private function getTotalCountperCategories($category_id,$year,$com_id,$dealer_id) { // dd($com_id); $data = DB::table('ticket') ->select(DB::raw("COUNT(ticket.id) as total_count")) ->join('categories as sub', fn($join) => $join->on('ticket.subject','=','sub.id')) ->join('users', fn($join) => $join->on('ticket.ticket_creator','=','users.id')); if($com_id > 0) { $data =$data->join('companies','users.comp_code','=','companies.comp_code'); } if($dealer_id > 0) { $data = $data->join('dealers','users.deal_code','=','dealers.dealer_code'); } $data = $data->where('sub.parent_id',$category_id) ->whereYear('ticket.created_at', $year); // ->whereIn('companies.id',$com_id); if($com_id!=null) { $data = $data->where('companies.id',$com_id); } if(isset($dealer_id) && COUNT($dealer_id) > 0) { $data = $data->whereIn('dealers.id',$dealer_id); } $data = $data->first(); return $data ? $data->total_count : 0; } //MONTHLY public function monthlyReport() { $year = 2024; // Define the reporting year // Step 1: Define months from January to December $months = range(1, 12); // [1, 2, 3, ..., 12] // Step 2: Initialize the data structure with all categories and 0 counts for each month $data_set = [ 'year' => $year, 'monthly_totals' => [] ]; // Get all relevant categories $categories = DB::table('categories as c') ->select('c.id', 'c.title') ->whereIn('c.id', [31, 32, 33, 36, 10]) // Filter categories ->get(); // Pre-fill all months with 0 for each category foreach ($categories as $category) { $data_set['monthly_totals'][$category->title] = array_fill_keys($months, 0); } // Step 3: Fetch the actual counts per category and month $results = DB::table('categories as c') ->select( 'c.title', DB::raw('MONTH(ticket.created_at) as month'), // Extract month from date DB::raw('COUNT(ticket.id) as total_count') // Count tickets per month ) ->leftJoin('categories as sub', 'c.id', '=', 'sub.parent_id') ->leftJoin('ticket', 'ticket.subject', '=', 'sub.id') ->join('users', 'ticket.ticket_creator', '=', 'users.id') ->join('companies', 'users.comp_code', '=', 'companies.comp_code') ->join('dealers', 'users.deal_code', '=', 'dealers.dealer_code') ->whereIn('c.id', [31, 32, 33, 36, 10]) // Filter categories ->whereYear('ticket.created_at', $year) // Filter by year ->where('companies.id', 19) ->where('dealers.id', 72) ->groupBy('c.id', 'c.title', DB::raw('MONTH(ticket.created_at)')) // Group by category and month ->orderBy('c.id') ->orderBy(DB::raw('MONTH(ticket.created_at)')) ->get(); // Step 4: Populate actual totals into the data structure foreach ($results as $result) { $data_set['monthly_totals'][$result->title][$result->month] = $result->total_count; } // Return or print the result for reporting return response()->json($data_set); } private function getTicketByStatus($where) { $results = DB::table('ticket') ->select( DB::raw("COUNT(ticket.status) as status_count") ) ->leftJoin('ticket_user as tu', 'ticket.id', '=', 'tu.tix_id') ->where('ticket.status', $where[0]) ->whereIn('tu.assigned_to', $where[1]) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <= ?", [$where[2], $where[3]]) ->get(); $status =0; if($results){ foreach($results as $row){ $status = $row->status_count; } } return $status; } private function totalSlaPerSupport($where) { DB::enableQueryLog(); $where[3] =($where[3]=='All' ? '' : $where[3]); $res = DB::table('ticket') ->selectRaw('SUM(sla.content) as total_sla') ->leftJoin('ticket_user as tu','ticket.id','=','tu.tix_id') ->join('categories AS ctg', 'ticket.subject', '=', 'ctg.id') ->join('service_level_agreement AS sla', 'ctg.sla_id', '=', 'sla.id') // ->where('ticket.status', '=', $where[3]) ->whereIn('tu.assigned_to', $where[0]) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <= ?", [$where[1], $where[2]]); // ->get(); if (isset($status) && $status != '') { $res->where('ticket.status', '=', $status); } $res = $res->get(); $query = DB::getQueryLog(); // dd($query); $total_sla =0; if($res) { foreach($res as $sla) { $total_sla = $sla->total_sla; } } return $total_sla; } private function totalSlaMasterfile($fromdate,$todate,$com_code,$dealer_code) { $res = DB::table('ticket as t') ->selectRaw('SUM(s.content) as total_sla') ->join('categories as c', 't.subject', '=', 'c.id') ->leftjoin('service_level_agreement as s', 'c.sla_id', '=', 's.id') ->join('users as u', 't.ticket_creator', '=', 'u.id') // ->where('t.status', 'closed') // ->whereBetween(DB::raw('DATE(t.created_at)'), [$fromdate, $todate]); ->whereRaw("DATE(t.created_at) >= ? AND DATE(t.created_at) <=?", [$fromdate, $todate]); if (!empty($com_code)) { $res->whereIn('u.comp_code', $com_code); } if (!empty($dealer_code)) { $res->whereIn('u.deal_code', $dealer_code); } $totalSla = $res->pluck('total_sla')->first(); return $totalSla; } public function supportsMasterfile(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $dept = Auth::user()->department_id; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`ticket`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } $array_data['where'] .= " AND `users`.`department_id` = $dept "; $results = $this->user->reports($array_data); $response = $this->user->pdf($results,'view'); $pdf = App::make('dompdf.wrapper')->setPaper('A4','landscape'); $pdf->loadView('layouts.reports.supports',$response); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("supports-masterfile.pdf"); break; case('csv'): $this->user->csv($results); break; } } // MODIFY August 2, 2024 public function supportsSLAMasterfile(Request $request){ if(!empty($request->from_date) && !empty($request->to_date)) { $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $status = $request->status; DB::enableQueryLog(); $results = DB::table('ticket') ->select(DB::raw("(@row := @row + 1) AS num"), 'ticket.ticket_no', DB::raw("CONCAT(REPLACE(users.f_name,'Ã±','ñ'),' ',REPLACE(users.l_name,'Ã±','ñ')) as fullname"), // 'sub.title as sub_category', DB::raw("DATE_FORMAT(ticket.created_at,'%b. %d, %Y \| %r') as date_created"), DB::raw("DATE_FORMAT(ticket.start_date,'%b. %d, %Y \| %r') as start_date"), DB::raw("DATE_FORMAT(ticket.end_date,'%b. %d, %Y \| %r') as end_date"), DB::raw('ticket.status as status'), 'sla.content', DB::raw("CONCAT(ticket.time_elapsed_time) AS time_elapsed"), DB::raw("CASE WHEN ticket.time_elapsed > 0 THEN 'Beyond SLA' WHEN ticket.time_elapsed =0 THEN 'Within SLA' ELSE '' END AS sla_status") ) ->join('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->join('users', 'ticket_user.assigned_to', '=', 'users.id') ->join('categories as sub', 'sub.id', '=', 'ticket.subject') ->join('service_level_agreement as sla','sub.sla_id','=','sla.id') ->where('ticket_user.assigned_dept',Auth::user()->department_id) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <= ?", [$fromdate, $todate]) ->orderBy('ticket.status'); if(!empty($status) && $status !='All'){ $results->where('ticket.status', $status); } // $results->get(); $results=$results->paginate(500); $query = DB::getQueryLog(); // dd($query); $data_set =[]; $data_set['total_records'] = $results->total(); $data_set['total_closed_ticket'] = 0; $data_set['total_ongoing_ticket'] =0; $data_set['total_sla_ticket'] = 0; $data_set['total_page'] = $results->lastPage(); $data_set['currentPage'] = $results->currentPage(); $data_set['perPage'] = $results->perPage(); // $data_set['count_return_data'] = count($results); $data_set['date_from'] = $fromdate; $data_set['date_to'] = $todate; $data_set['data'] =[]; // dd($status); // $status_array = ['closed','All']; // if($results->currentPage()===1) // { // $data_set['total_sla_ticket'] =$this->totalSlaPerSupport([$user_id,$fromdate,$todate,$status]); // $data_set['total_closed_ticket'] = (in_array($status,$status_array) ? $this->getTicketByStatus(['closed',$user_id,$fromdate,$todate]) : 'N/A'); // $data_set['total_ongoing_ticket'] = $this->getTicketByStatus(['pending',$user_id,$fromdate,$todate]); // $data_set['total_pending_ticket'] = $this->getClosedTicket(['pending',$user_id,$fromdate,$todate]); // $data_set['total_transfered_ticket'] = $this->getClosedTicket(['transfered',$user_id,$fromdate,$todate]); // } foreach($results as $row) { // unset($row->closed_count); // unset($row->total_sla); // unset($row->ongoing_count); array_push($data_set['data'],$row); }unset($row); return response()->json($data_set); } } // public function supportsSLAMasterfile(Request $request){ // // dd($request->all()); // $array_data['type'] = $request->type; // $array_data['search'] = ""; // $array_data['where'] = ""; // $id = Auth::user()->department_id; // $array_data['where'] .= " AND ticket_user.assigned_dept = '{$id}' "; // if(!empty($request->from_date) && !empty($request->to_date)){ // $from = Carbon::parse($request->from_date); // $to = Carbon::parse($request->to_date); // $fromdate = $from->toDateString(); // $todate = $to->toDateString(); // $array_data['where'] .= " AND DATE(`ticket`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; // } // if(!empty($request->ticket_status)){ // $array_data['where'] .= " AND `ticket_user`.`status` = '{$request->ticket_status}' "; // } // $results = $this->report->support_sla($array_data); // // dd($results); // $response = $this->report->support_sla_pdf($results,'view', $id, $request->ticket_status); // $pdf = App::make('dompdf.wrapper')->setPaper('A4','landscape'); // $pdf->loadView('layouts.reports.support_sla',$response); // switch($array_data['type']){ // case('view'): // return $pdf->stream(); // break; // case('pdf'): // return $pdf->download("support-sla-masterfile.pdf"); // break; // case('csv'): // $this->report->support_sla_csv($results); // break; // } // } public function support_ticket_count(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $department_id = Auth::user()->department_id; $status = $request->ticket_status; $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); // dd($status); DB::enableQueryLog(); $results = DB::table('ticket') ->select( DB::raw("COUNT(ticket.id) as occurrence_count"), DB::raw("CONCAT(REPLACE(users.f_name,'Ã±','ñ'),' ',REPLACE(users.l_name,'Ã±','ñ')) as SUPPORT"), ) ->join('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->join('users', 'ticket_user.assigned_to', '=', 'users.id') ->where('users.department_id',$department_id) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <= ?", [$fromdate, $todate]) ->groupBy('ticket_user.assigned_to'); if ($status !== null) { $results->where('ticket.status', $status); } $results = $results->get(); // $query = DB::getQueryLog(); // dd($query); // dd($results); $response = $this->report->support_ticket_count_pdf($results,'view', $department_id, $request->ticket_status); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.support_ticket_count',$response); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("supports-masterfile.pdf"); break; case('csv'): $this->report->support_sla_csv($results); break; } } //MODIFY AUGUST 5, 2024 public function SLAsupport_ticket_count(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $department_id = ($request->department ? $request->department : Auth::user()->department_id); $status = $request->status; $department_name =''; $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); // dd($status); DB::enableQueryLog(); $dp = Department::where(['id' => $department_id])->first(); if($dp) { $department_name = $dp->dept_name; } $results = DB::table('ticket') ->select( DB::raw("(@row := @row + 1) AS num"), DB::raw("CONCAT(REPLACE(users.f_name,'Ã±','ñ'),' ',REPLACE(users.l_name,'Ã±','ñ')) as SUPPORT"), // DB::raw('departments.dept_name as department'), DB::raw("COUNT(ticket.id) as occurrence_count"), ) ->join('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->join('users', 'ticket_user.assigned_to', '=', 'users.id') // ->leftJoin('departments','users.department_id','=','departments.id') ->where('users.department_id',$department_id) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <= ?", [$fromdate, $todate]) ->groupBy('ticket_user.assigned_to'); if ($status != null) { $results->where('ticket.status', $status); } $results=$results->paginate(100); // $results = $results->get(); // dd(DB::getQueryLog()); $data_set =[]; $data_set['total_records'] = $results->total(); $data_set['total_closed_ticket'] = 0; $data_set['total_ongoing_ticket'] =0; $data_set['total_sla_ticket'] = 0; $data_set['total_sla_ticket_count'] = 0; $data_set['department_name'] = $department_name; $data_set['total_page'] = $results->lastPage(); $data_set['currentPage'] = $results->currentPage(); $data_set['perPage'] = $results->perPage(); // $data_set['count_return_data'] = count($results); $data_set['date_from'] = $fromdate; $data_set['date_to'] = $todate; $data_set['data'] =[]; foreach($results as $index => $row) { $data_set['total_sla_ticket_count'] += $row->occurrence_count; array_push($data_set['data'],$row); }unset($row); return response()->json($data_set); } public function carplus_ticket(Request $request){ $array_data['type'] = $request->type; $array_data['filter'] = $request->filter; $array_data['search'] = ""; $array_data['where'] = ""; $filter_data = [ $request->from_date ?? "", $request->to_date ?? "", $request->carplus_status ?? "All", $array_data['filter'], ]; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`cpt`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } if(!empty($request->carplus_dealer)){ $dealer = Dealer::findOrFail($request->carplus_dealer); $array_data['where'] .= " AND `crtr`.`deal_code` LIKE '%{$dealer->dealer_code}%' "; } if(!empty($request->carplus_status)){ $array_data['where'] .= " AND `cpt`.`status` LIKE '%{$request->carplus_status}%' "; } switch ($array_data['filter']) { case 'masterfile': $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, CASE WHEN cpt.status = 'open' THEN 'Open' ELSE 'Closed' END AS status, DATE_FORMAT(cpt.deleted_at,'%b. %d, %Y') as date_closed "; break; case 'cust_info': $array_data['fields'] = " CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id,CONCAT(vehicles.owner_fname,' ',vehicles.owner_lname) as customer_name,vehicles.owner_address,vehicles.owner_phone,vehicles.owner_email "; break; case 'vhcl_info': $array_data['fields'] = " CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id,vehicles.vehicle_make,vehicles.vehicle_model,vehicles.vehicle_year,vehicles.vehicle_vin,vehicles.license_plate,vehicles.vehicle_color "; break; case 'ownshp_info': $array_data['fields'] = " CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id,vehicles.purchase_date,vehicles.purchase_price,vehicles.dealer_location,vehicles.financing_details "; break; case 'reg_info': $array_data['fields'] = " CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id,vehicles.registration_expiry,vehicles.registration_number,vehicles.registration_state,vehicles.insurance_provider,vehicles.policy_number,vehicles.policy_expiry "; break; case 'main_info': $array_data['fields'] = " CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id,vehicles.last_service_date,vehicles.last_service_type,vehicles.next_service_due,vehicles.service_provider "; break; case 'notes_info': $array_data['fields'] = " CONCAT('CARPLUS_TCKT_',cpt.id) as carplus_id, vn.note, CONCAT(usr_vn.f_name, ' ', usr_vn.l_name) as sender, vn.created_at "; break; default: break; } $results = $this->report->carplus_ticket_count($array_data); $response = $this->report->carplus_ticket_count_pdf($results,$filter_data); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.carplus_masterfile',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): switch ($array_data['filter']) { case 'masterfile': return $pdf->download("carplus-masterfile.pdf"); break; case 'cust_info': return $pdf->download("carplus-customer-info.pdf"); break; case 'vhcl_info': return $pdf->download("carplus-vehicle-info.pdf"); break; case 'ownshp_info': return $pdf->download("carplus-ownership-info.pdf"); break; case 'reg_info': return $pdf->download("carplus-registration-info.pdf"); break; case 'main_info': return $pdf->download("carplus-maintenance.pdf"); break; case 'notes_info': return $pdf->download("carplus-notes.pdf"); break; default: break; } case('csv'): $this->report->carplus_ticket_csv($results,$filter_data); break; } } public function totalNumberOfTicketPerCompany(Request $request){ $array_data['type'] = $request->type; $array_data['filter'] = $request->filter; $array_data['search'] = ""; $array_data['where'] = ""; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(tckt.created_at) BETWEEN '$fromdate' AND '$todate' "; } if(!empty($request->ticket_status)){ $array_data['where'] .= " AND tckt_usr.status LIKE '%{$request->ticket_status}%' "; } if(!empty($request->ticket_condition)){ $array_data['where'] .= " AND tckt_usr.ticket_condition LIKE '%{$request->ticket_condition}%' "; } if(!empty($request->ticket_summary_company)){ $company = $this->report->getCompanyDetails($request->ticket_summary_company); $array_data['where'] .= " AND `sender`.`comp_code` LIKE '%{$company->comp_code}%' "; } if(!empty($request->ticket_summary_dealer)){ $dealer = $this->report->getDealerDetails($request->ticket_summary_dealer); $array_data['where'] .= " AND `sender`.`deal_code` LIKE '%{$dealer->dealer_code}%' "; } $filter_data = [ $request->from_date ?? "", $request->to_date ?? "", $company->comp_code ?? "", $dealer->dealer_code ?? "", ]; $results = $this->report->totalNumberOfTicketPerCompany($array_data); // dd($results); $response = $this->report->totalNumberOfTicketPerCompanyPdf($results,$filter_data); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.ticket_summary_report',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("ticket-summary-masterfile.pdf"); break; case('csv'): $this->report->support_sla_csv($results); break; } } public function categorySummary(Request $request){ $array_data['type'] = $request->type; $array_data['filter'] = $request->filter; $array_data['search'] = ""; $array_data['where'] = ""; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`ctg`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } switch ($request->field) { case 'category_masterfile': $array_data['fields'] = " `ctg`.`id`, `ctg`.`title`, `ctg`.`created_at`, `ctg`.`status` "; $results = $this->report->categorySummaryReport($array_data); $response = $this->report->CategorySummaryReportPdf($results); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.category_summary_report',$response); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("category-summary-report.pdf"); break; case('csv'): $this->report->category_csv($results); break; } break; case 'sub_category_masterfile': $array_data['fields'] = " `ctg`.`id` as parentId, `ctg`.`title` as parentTitle, `child`.`id` as childId, `child`.`title` as childTitle, DATE_FORMAT(`child`.`created_at`,'%b. %d, %Y') as childCreatedAt, DATE_FORMAT(`ctg`.`created_at`,'%b. %d, %Y') as parentCreatedAt, `ctg`.`status` as parentStatus, `child`.`status` as childStatus , `sla`.`content` "; $results = $this->report->childCategorySummaryReport($array_data); $childWithParent = []; // Create a dictionary to group child categories by parent category ID $parentToChildMapping = []; foreach ($results as $item) { // Check if the parent category ID exists in the mapping dictionary if (!isset($parentToChildMapping[$item->parentId])) { $parentToChildMapping[$item->parentId] = [ 'category' => $item->parentTitle, 'created_at' => $item->parentCreatedAt, 'children' => [], ]; } // Add the child category to the parent's children array $parentToChildMapping[$item->parentId]['children'][] = [ 'sub-category' => $item->childTitle, 'created_at' => $item->childCreatedAt, ]; } // Convert the mapping dictionary to a flat array foreach ($parentToChildMapping as $parentId => $parentData) { $childWithParent[] = [ 'category' => $parentData['category'], 'created_at' => $parentData['created_at'], 'children' => $parentData['children'], ]; } $response = $this->report->SubCategorySummaryReportPdf($childWithParent); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.subcategory_category_summary_report',$response); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("subcategory-summary-report.pdf"); break; case('csv'): $this->report->sub_category_csv($childWithParent); break; } break; case 'cat_with_sla_masterfile': $array_data['fields'] = " `ctg`.`id`, `ctg`.`title`, DATE_FORMAT(`ctg`.`created_at`,'%b. %d, %Y') as created_at, `ctg`.`status`"; $results = $this->report->categorySummaryReport($array_data); $childWithParent = []; foreach($results as $item){ $child = DB::table('categories')->select('categories.title','categories.created_at','service_level_agreement.content','service_level_agreement.type')->join('service_level_agreement','categories.sla_id',"=",'service_level_agreement.id')->where('parent_id',$item->id)->get(); foreach($child as $sub){ $childWithParent[] = [ 'category' => $item->title, 'sub-category' => $sub->title, 'sla' => $sub->content." ".strtoupper($sub->type)."/S.", 'created_at' => $sub->created_at ]; } } $response = $this->report->CategoryWithSLASummaryReportPdf($childWithParent); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.category_sla_summary_report',$response); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("category-with-sla-summary-report.pdf"); break; case('csv'): $this->report->cat_sla_csv($results); break; } break; default: break; } } public function slaSummary(Request $request){ $array_data['type'] = $request->type; $array_data['filter'] = $request->filter; $array_data['search'] = ""; $array_data['where'] = ""; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`sla`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } $results = $this->report->slaSummaryReport($array_data); $response = $this->report->slaSummaryReportPdf($results); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.sla_summary_report',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("sla-summary-report.pdf"); break; case('csv'): $this->report->sla_csv($results); break; } } public function getMyTicketsSummary(Request $request){ // dd($request->all()); $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $id = Auth::user()->id; $array_data['where'] .= " AND `tckt`.`ticket_creator` = $id "; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`tckt`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } if($request->category){ $array_data['where'] .= " AND `tckt`.`subject` = $request->category "; } // else { // return response()->json(['message'=>'Please provide a category'],403); // } $result = $this->ticket->getMyTicketSummary($array_data); $response = $this->ticket->MyTicketSummaryReportPdf($result); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.my_tickets_summary',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("my-tickets-summary-report.pdf"); break; case('csv'): $this->report->my_ticket_summary_csv($result); break; } } public function getDepartmentTicketSummary(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $id = Auth::user()->id; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`t`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } if(!empty($request->dept_tckt_summary)){ $array_data['where'] .= " AND `t`.`status` LIKE '%{$request->dept_tckt_summary}%' "; } if(!empty($request->dept_tckt_summary_condition)){ $array_data['where'] .= " AND `tu`.`ticket_condition` LIKE '%{$request->dept_tckt_summary_condition}%' "; } // if(!$request->category){ // return response()->json(['message'=>'Please provide a category'],403); // } else { // $array_data['where'] .= " AND `tckt`.`subject` = $request->category "; // } $result = $this->ticket->fetchDepartmentTicketsSummary($array_data); // dd($result); $response = $this->ticket->departmentTicketSummaryReportPdf($result); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.department_ticket',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("department-ticket-summary-report.pdf"); break; case('csv'): $this->report->department_ticket_summary_csv($result); break; } } public function getAssignedTicketSummary(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $id = Auth::user()->id; $name = Auth::user()->f_name . " " . Auth::user()->l_name; // $array_data['where'] .= " AND `tckt`.`ticket_creator` = $id "; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`t`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } $array_data['where'] .= " AND `tu`.`assigned_to` = $id"; // if(!$request->category){ // return response()->json(['message'=>'Please provide a category'],403); // } else { // $array_data['where'] .= " AND `tckt`.`subject` = $request->category "; // } $result = $this->ticket->getAssignedTicketSummary($array_data); $response = $this->ticket->getAssignedTicketSummaryPdf($result,$name); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.my_assigned_ticket_masterfile',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("my-assigned-tickets-summary-report.pdf"); break; case('csv'): $this->report->my_ticket_summary_csv($result); break; } } public function getRoleSummary(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $id = Auth::user()->id; $status = "All"; $name = Auth::user()->f_name . " " . Auth::user()->l_name; // $array_data['where'] .= " AND `tckt`.`ticket_creator` = $id "; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`roles`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } if($request->role_status == 1){ $array_data['where'] .= " AND `roles`.`status` = 1 "; $status = "Active"; } else if ($request->role_status == 0){ $array_data['where'] .= " AND `roles`.`status` is null "; $status = "Inactive"; } $result = $this->report->getRoleSummary($array_data); $response = $this->report->getRoleSummaryPdf($result,$status); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.roles_masterfile',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("role-summary-report.pdf"); break; case('csv'): $this->report->getRoleSummaryCsv($result,$status); break; } } public function getAccessSummary(Request $request){ $array_data['type'] = $request->type; $array_data['search'] = ""; $array_data['where'] = ""; $id = Auth::user()->id; // $status = "All"; $name = Auth::user()->f_name . " " . Auth::user()->l_name; // $array_data['where'] .= " AND `tckt`.`ticket_creator` = $id "; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`abilities`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } $result = $this->report->getAccessSummary($array_data); $response = $this->report->getAccessSummaryPdf($result); $pdf = App::make('dompdf.wrapper'); $pdf->loadView('layouts.reports.access_masterfile',$response)->setPaper('long','landscape'); switch($array_data['type']){ case('view'): return $pdf->stream(); break; case('pdf'): return $pdf->download("access-summary-report.pdf"); break; case('csv'): $this->report->getAccessSummaryCsv($result); break; } } /* * Report for each support which is beyond, within or over the sla time within the department only * * * SELECT CONCAT(users.f_name," ",users.l_name) as support, sla.content, ticket.time_elapsed, ticket.ticket_no, category.title, categories.title as category, * 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 * 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 users.department_id = Auth::user()->department_id * AND ticket_user.status = "closed"; / /* * Report for support count for each ticket they supported that are closed already * SELECT ticket_user.assigned_to, COUNT() as occurrence_count FROM ticket_user * LEFT OUTER JOIN users ON ticket_user.assigned_to = users.id * WHERE users.department_id = 15 * AND ticket_user.status = "closed" * GROUP BY ticket_user.assigned_to; / //REPORT Tickets SECTION public function ticketsMasterfile2(Request $request){ $array_data['type'] = $request->type; $com_code = $request->comp_code; $dealer_code = $request->dealer_code; $array_data['search'] = ""; $array_data['where'] = ""; $where =''; if(!empty($request->from_date) && !empty($request->to_date)){ $from = Carbon::parse($request->from_date); $to = Carbon::parse($request->to_date); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`ticket`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; $where.= (!empty($com_code) ? " AND `usr`.`comp_code` IN ('" . implode("','", $com_code) . "') " : ''); $where.= (!empty($dealer_code) ? " AND `usr`.`deal_code` IN ('" . implode("','", $dealer_code) . "')" : ''); // $array_data['where'].=" LIMIT ".$paginate; } $results = DB::table('ticket') ->select(DB::raw("(@row := @row + 1) AS num"), 'ticket.ticket_no as ticket_no', DB::raw("CONCAT(`usr`.`f_name`,' ',REPLACE(`usr`.`l_name`,'Ã±','ñ')) as sender"), 'c.comp_name as company', 'd.dealer_name as dealer', 'ctg.title as category', 'sub.title as sub_category', DB::raw("DATE_FORMAT(ticket.created_at,'%b. %d, %Y \| %r') as date_created"), DB::raw("DATE_FORMAT(ticket.start_date,'%b. %d, %Y \| %r') as date_started"), DB::raw("DATE_FORMAT(ticket.end_date,'%b. %d, %Y \| %r') as date_ended"), DB::raw("CONCAT(`support`.`f_name`,' ',REPLACE(`support`.`l_name`,'Ã±','ñ')) as support_personel"), 'usr.position', DB::raw("CONCAT(`sla`.`content`,' MINS.') as sla"), DB::raw("CASE WHEN ticket.status = 'pending' THEN 'Pending' WHEN ticket.status = 'ongoing' THEN 'Ongoing' WHEN ticket.status = 'closed' THEN 'Closed' ELSE ticket.status END AS status"), DB::raw("(SELECT COUNT() FROM ticket WHERE status = 'closed') AS closed_count"), DB::raw("(SELECT COUNT() FROM ticket WHERE status = 'ongoing') AS ongoing_count"), // DB::raw("(SELECT SUM(sla.content) FROM ticket INNER JOIN categories AS ctg ON ticket.subject = ctg.id INNER JOIN service_level_agreement AS sla ON ctg.sla_id = sla.id WHERE ticket.status = 'closed') AS total_sla") ) ->leftJoin('users as usr', 'usr.id', '=', 'ticket.ticket_creator') ->join('ticket_user as tu', 'tu.tix_id', '=', 'ticket.id') ->leftJoin('departments as dt', 'dt.id', '=', 'tu.assigned_dept') ->leftJoin('users as support', 'tu.assigned_to', '=', 'support.id') ->join('categories as sub', 'sub.id', '=', 'ticket.subject') ->join('categories as ctg', 'sub.parent_id', '=', 'ctg.id') ->leftJoin('service_level_agreement as sla', 'sla.id', '=', 'sub.sla_id') ->leftJoin('companies as c', 'usr.comp_code', '=', 'c.comp_code') ->leftJoin('dealers as d', 'usr.deal_code', '=', 'd.dealer_code') ->where('tu.assigned_dept',Auth::user()->department_id) ->whereRaw("DATE(ticket.created_at) >= ? AND DATE(ticket.created_at) <=?", [$fromdate, $todate]); if (!empty($com_code)) { $results->where(function($query) use ($com_code) { $query->whereIn('usr.comp_code', $com_code); }); } if (!empty($dealer_code)) { $results->where(function($query) use ($dealer_code) { $query->whereIn('usr.deal_code', $dealer_code); }); } $results = $results->paginate(100); $queryLog = DB::getQueryLog(); // dd($queryLog); $data_set =[]; $data_set['total_records'] = $results->total(); $data_set['total_closed_ticket'] = 0; $data_set['total_ongoing_ticket'] =0; $data_set['total_sla_ticket'] = 0; $data_set['total_page'] = $results->lastPage(); $data_set['currentPage'] = $results->currentPage(); $data_set['perPage'] = $results->perPage(); if($results->currentPage()===1) { foreach($results as $total) { $data_set['total_closed_ticket'] = $total->closed_count; $data_set['total_ongoing_ticket'] = $total->ongoing_count; $data_set['total_sla_ticket'] = $this->totalSlaMasterfile($fromdate,$todate,$com_code,$dealer_code);//$total->total_sla; } } // $data_set['count_return_data'] = count($results); $data_set['date_from'] = $fromdate; $data_set['date_to'] = $todate; $data_set['data'] =[]; foreach($results as $row) { unset($row->closed_count); unset($row->total_sla); unset($row->ongoing_count); array_push($data_set['data'],$row); }unset($row); $count_request_result = 0; return response()->json($data_set); } public function CategorySub(){ // DB::enableQueryLog(); $this->data['list_tech_personnel'] = $this->role::usersByRole(); $currentUserRole = $this->role::UserRole(); $userRole = 0; foreach($currentUserRole as $uRole) { $userRole = $uRole->role_id; } $com_id_access=[]; $id =1; if($userRole>0){ $com_dealers = DB::table('company_dealer_access')->where('role_id', $id) ->select('com_id','full_access') ->get(); $com_id_access = []; $com_list =[]; // $dealer_list =[]; $full_access = 0; // if(count($com_dealers)> 0){ foreach($com_dealers as $row) { $full_access = $row->full_access; $list_com =$this->companylist($com_id_access,$full_access); $com_list = $list_com; foreach(explode(',',$row->com_id) as $key=> $com_id){ $com_id_access[] = $com_id; } } $this->data['companies'] = $com_list; $this->data['dealers']=$this->loadDealers($com_id,$full_access); $this->data['departments'] = \App\Models\Department::select('id','dept_name')->where('id',Auth::user()->department_id)->get(); $this->data['categories'] = \App\Models\Category::select('id','title') ->where('dept_id', Auth::user()->department_id) ->where('status', 1) ->where('parent_id', null) ->where('id', '!=', 246) ->get(); } return view ('layouts.authentication.admin.report.tickets.category_sub',$this->data); } public function getCategorySub(Request $request){ // dd($request->all()); $personnel =\App\Models\User::where('id',$request->personnel)->first(); $company = \App\Models\Company::where('id',$request->company)->first(); $dealer = \App\Models\Dealer::where('id',$request->dealer)->first(); $department = \App\Models\Department::select('dept_name')->where('id',$request->department_id)->first(); $ticket_status = $request->ticket_status; if((!empty($request->company)));// && !empty($request->dealer))) { $year = $request->year;//Carbon::parse($request->year); $companies = ($request->company > 0 ? $request->company : 0);//implode(',',$request->company); $dealers_id = $request->dealer;//implode(',',$request->dealer); // dd($request->all()); // DB::enableQueryLog(); $results = DB::table('ticket') ->select( 'ticket.ticket_no as ticket_no', DB::raw("CONCAT(`usr`.`f_name`,' ',REPLACE(`usr`.`l_name`,'Ã±','ñ')) as sender"), DB::raw("CONCAT(c.comp_code,' - ',d.dealer_name) as company"), // 'd.dealer_name as dealer', 'ctg.title as category', 'sub.title as sub_category', DB::raw("DATE_FORMAT(ticket.created_at,'%b. %d, %Y \| %r') as date_created"), DB::raw("DATE_FORMAT(ticket.start_date,'%b. %d, %Y \| %r') as date_started"), DB::raw("DATE_FORMAT(ticket.end_date,'%b. %d, %Y \| %r') as date_ended"), DB::raw("CONCAT(`support`.`f_name`,' ',REPLACE(`support`.`l_name`,'Ã±','ñ')) as support_personel"), 'usr.position', // DB::raw("CONCAT(`sla`.`content`,' MINS.') as sla"), 'ticket.description', DB::raw("CASE WHEN ticket.status = 'pending' THEN 'Pending' WHEN ticket.status = 'ongoing' THEN 'Ongoing' WHEN ticket.status = 'closed' THEN 'Closed' ELSE ticket.status END AS status"), DB::raw("(SELECT COUNT() FROM ticket WHERE status = 'closed') AS closed_count"), DB::raw("(SELECT COUNT(*) FROM ticket WHERE status = 'ongoing') AS ongoing_count"), 'ticket.solution' // DB::raw("(SELECT SUM(sla.content) FROM ticket INNER JOIN categories AS ctg ON ticket.subject = ctg.id INNER JOIN service_level_agreement AS sla ON ctg.sla_id = sla.id WHERE ticket.status = 'closed') AS total_sla") ) ->leftJoin('users as usr', 'usr.id', '=', 'ticket.ticket_creator') ->join('ticket_user as tu', 'tu.tix_id', '=', 'ticket.id') ->leftJoin('departments as dt', 'dt.id', '=', 'tu.assigned_dept') ->leftJoin('users as support', 'tu.assigned_to', '=', 'support.id') ->join('categories as sub', 'sub.id', '=', 'ticket.subject') ->join('categories as ctg', 'sub.parent_id', '=', 'ctg.id') ->leftJoin('service_level_agreement as sla', 'sla.id', '=', 'sub.sla_id') ->leftJoin('companies as c', 'usr.comp_code', '=', 'c.comp_code') ->leftJoin('dealers as d', 'usr.deal_code', '=', 'd.dealer_code') ->where('tu.assigned_dept',Auth::user()->department_id); if ($request->from_date && $request->to_date) { $results = $results->whereBetween('ticket.created_at', [$request->from_date, $request->to_date]); } if(isset($request->categories) && count($request->categories) > 0) { $results = $results->whereIn('c.id', $request->categories); } if(!empty($request->sub_categories) && count($request->sub_categories) > 0){ $results = $results->whereIn('sub.id',$request->sub_categories); } if($request->ticket_status != 'All') { $results = $results->where('ticket.status',$request->ticket_status); } if($companies > 0) { $results = $results->where('companies.id', $companies); } if (isset($request->dealer) ) { $results->where('dealers.id', $dealers_id); } // $results = $results->get(); // dd(DB::getQueryLog()); $results = $results->paginate(100); $data_set =[]; $data_set['total_records'] = $results->total(); $data_set['total_page'] = $results->lastPage(); $data_set['currentPage'] = $results->currentPage(); $data_set['perPage'] = $results->perPage(); $data_set['count_return_data'] = count($results); $data_set['date_from'] = $request->from_date; $data_set['date_to'] = $request->to_date; $data_set['data'] =[]; // $data_set['personnel_name'] =$personnel->f_name.' '.$personnel->l_name.' - '.$personnel->position; $data_set['company_name'] =($companies > 0 ? $company->comp_name : ''); $data_set['dealer_name'] =(isset($dealer->dealer_name) ? $dealer->dealer_name : ''); $data_set['department_name'] = $department->dept_name; $data_set['data'] =[]; foreach($results as $row) { unset($row->closed_count); unset($row->total_sla); unset($row->ongoing_count); array_push($data_set['data'],$row); }unset($row); return response()->json($data_set); } } }

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