File manager

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

Back
<?php namespace App\Http\Controllers; use App\Events\AssignTicket; use App\Events\CreateTicket; use App\Events\PickTicket; use App\Events\TransferTicket; use App\Models\Company; use App\Models\Dealer; use App\Models\Department; use App\Models\Ticket; use App\Models\User; use Carbon\Carbon; use DateTime; use Illuminate\Http\Request; use Illuminate\Support\Facades\Auth; use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Log; use Silber\Bouncer\Database\Role; class TicketController extends Controller { /** * Display a listing of the resource. * * @return \Illuminate\Http\Response / private $ticket, $auth; public function __construct() { $this->ticket = new Ticket; $this->auth = new AuthenticationController; } public function index() { if($this->auth->checkMaintenance() == "true"){ abort(503); } $current_company_id =null; $user_company_id =\App\Models\Company::select('id')->where('comp_code',Auth::user()->comp_code)->first(); if($user_company_id){ $current_company_id =$user_company_id['id']; } return view('layouts.authentication.ticket_component.index',['user_current_company_id'=>$current_company_id]) ->with(['title_page'=>"My Tickets"]); } public function deptIndex() { if (!auth()->user()->can('view-department-ticket')) { abort(403, 'Unauthorized action.'); } if($this->auth->checkMaintenance() == "true"){ abort(503); } return view('layouts.authentication.admin.department_ticket.index') ->with(['title_page' => "Department Tickets"]); } /* * Show the form for creating a new resource. * * @return \Illuminate\Http\Response / public function create() { // } public function fetchDepartmentTickets(Request $request){ $array_data['search_keyword'] = $request->search['value']; if(empty($request->search_type)){ $array_data['search_type'] = ""; } else { $array_data['search_type'] = json_decode($request->search_type,true)[0]; } $array_data['sort'] = $request->order[0]['dir']; $array_data['order'] = $request->columns[$request->order[0]['column']]['data']; $array_data['offset'] = $request->start; $array_data['limit'] = $request->length; $array_data['offset_limit'] = " LIMIT {$array_data['offset']},{$array_data['limit']}"; $array_data['order']=($array_data['order'] =='#' ? $array_data['order'] = 'id' : $array_data['order'] ); $array_data['sort'] = " ORDER BY {$array_data['order']} {$array_data['sort']} "; $array_data['search'] = ""; if(!empty($array_data['search_keyword'])){ switch($array_data['search_type']){ case "ID": $array_data['search'] = " AND (`t`.`id` = {$array_data['search_keyword']}) "; break; case "Ticket No.": $array_data['search'] =" AND (`t`.`ticket_no` LIKE '%{$array_data['search_keyword']}%' OR CONCAT(`usr`.`f_name`,' ',`usr`.`l_name`) LIKE '%{$array_data['search_keyword']}%')"; break; case "Sender": $array_data['search'] =" AND (`sender`.`f_name` LIKE '%{$array_data['search_keyword']}%' OR `sender`.`l_name` LIKE '%{$array_data['search_keyword']}%') "; break; case "Email": $array_data['search'] =" AND (`sender`.`email` LIKE '%{$array_data['search_keyword']}%') "; break; case "Description": $array_data['search'] =" AND (`t`.`description` LIKE '%{$array_data['search_keyword']}%') "; break; } } $array_data['where'] = ""; $data = $request->data; if(!empty($data['option'])){ if(!empty($array_data['search_keyword'])){ $array_data['where'] .= " AND (`t`.`status` LIKE '%pending%' OR `t`.`status` LIKE '%closed%') "; } else if($data['option'] == "all"){ $array_data['where'] .= " AND (`t`.`status` LIKE '%ongoing%' OR `t`.`status` LIKE '%pending%') "; } else { $array_data['where'] .= " AND `t`.`status` LIKE '%{$data['option']}%' "; } } if(!empty($data['deal'])){ if($data['deal'] != "NULL"){ $dealer = Dealer::where('id',$data['deal'])->select('dealer_code')->first(); // dd($dealer); $array_data['where'] .= " AND `usr`.`deal_code` LIKE '%{$dealer->dealer_code}%' "; } } if(!empty($data['comp'])){ if($data['comp'] != "NULL"){ $comp = Company::where('id',$data['comp'])->select('comp_code')->first(); $array_data['where'] .= " AND `usr`.`comp_code` LIKE '%{$comp->comp_code}%' "; } } if(!empty($data['level'])){ $array_data['where'] .= " AND `t`.`level` LIKE '%{$data['level']}%' "; } else { $array_data['where'] .= " AND `t`.`level` LIKE '%mid%' "; } if($data['dateFrom']!=null && $data['dateTo']!=null){ $dateFrom = $data['dateFrom']; $dateTo = $data['dateTo']; $array_data['where'] .= " AND DATE(`t`.`created_at`) >= '$dateFrom' AND DATE(`t`.`created_at`)<='$dateTo' "; } $id = Auth::user()->department_id; // dd($id); if(Auth::user()->asa_id != '22222'){ $array_data['where'] .= " AND `tu`.`assigned_dept` = {$id} "; } $results = $this->ticket->fetchDepartmentTickets($array_data); // print_r($results); $result['data'] = array(); $count=1; foreach($results as $row){ $result['data'][] = [ "id" => $row->id, "#" => '',//$count++, "ticket_no" => $row->ticket_no, "description" => $row->description, "parent" => ($row->parent ==null ? 'N/A' : $row->parent), "child" => $row->child, "status" => $row->status, "level" => $row->level, "ticket_creator" => $row->ticket_creator, "assigned_dept_id" => $row->assigned_dept, "category_id" => $row->category_id, "sender_id" => $row->usr_id, "sender" => $row->SENDER, "sender_email" => $row->sender_email, "sender_number" => $row->sender_number, "sender_position" => $row->sender_position, "employee_id" => $row->employee_id, "company_id" => $row->company_id, "position" => $row->position, "solution" => $row->solution, 'type_of_support' => $row->type_of_support, "created_at" => $row->created_at, "start_date" => $row->start_date, "end_date" =>$row->end_date, "condition" => $row->ticket_condition, "assignee" => $row->assignee, "support" => $row->support, "support_position" => $row->support_position ]; } $result['draw'] = $request->draw; $result['recordsTotal'] = $this->ticket->getMyDepartmentTicketsCount($array_data)[0]->Count; $result['recordsFiltered'] = $this->ticket->getMyDepartmentTicketsFilteredCount($array_data)[0]->FilteredCount; return response()->json($result); } public function fetchMyTicket(Request $request){ $array_data['search_keyword'] = $request->search['value']; if(empty($request->search_type)){ $array_data['search_type'] = ""; } else { $array_data['search_type'] = json_decode($request->search_type,true)[0]; } $array_data['sort'] = $request->order[0]['dir']; $array_data['order'] = $request->columns[$request->order[0]['column']]['data']; // $array_data['order']=($array_data['order'] =='#' ? $array_data['order'] = 'id' : $array_data['order'] ); $array_data['offset'] = $request->start; $array_data['limit'] = $request->length; $array_data['offset_limit'] = " LIMIT {$array_data['offset']},{$array_data['limit']}"; $array_data['order']=($array_data['order'] =='#' ? $array_data['order'] = 'id' : $array_data['order'] ); $array_data['sort'] = " ORDER BY {$array_data['order']} {$array_data['sort']} "; $array_data['search'] = ""; if(!empty($array_data['search_keyword'])){ switch($array_data['search_type']){ case "ID": $array_data['search'] = " AND `t`.`id` = {$array_data['search_keyword']} "; break; case "Ticket No.": $array_data['search'] =" AND `t`.`ticket_no` LIKE '%{$array_data['search_keyword']}%' "; break; } } $array_data['where'] = ""; $data = $request->data; if(!empty($data['from_date']) && !empty($data['to_date'])){ $from = Carbon::parse($data['from_date']); $to = Carbon::parse($data['to_date']); $fromdate = $from->toDateString(); $todate = $to->toDateString(); $array_data['where'] .= " AND DATE(`t`.`created_at`) BETWEEN '$fromdate' AND '$todate' "; } if(!empty($data['option'])){ if($data['option'] != "all"){ $array_data['where'] .= " AND `t`.`status` LIKE '%{$data['option']}%' "; } } $results = $this->ticket->fetchMyTickets($array_data); // dd($results); $result['data'] = array(); $count =1; foreach($results as $row){ $result['data'][] = [ "id" => $row->id, "#" => $count++, "ticket_no" => $row->ticket_no, "description" => $row->description, "level" => $row->level, "status" => $row->status, "created_at" => $row->created_at, "start_date" => $row->start_date, "end_date" => $row->end_date, "solution" => $row->solution, "sender" => $row->SENDER, "sender_email" => $row->sender_email, "sender_position" => $row->sender_position, "ticket_creator" => $row->ticket_creator, "support" => $row->support, "support_position" => $row->support_position ]; } $result['draw'] = $request->draw; $result['recordsTotal'] = $this->ticket->getMyTicketsCount($array_data)[0]->Count; $result['recordsFiltered'] = $this->ticket->getMyTicketsFilteredCount($array_data)[0]->FilteredCount; return response()->json($result); } public function createNewCat($title,$id){ $newCat = DB::table('categories')->insertGetId([ 'title' => $title, 'dept_id' => $id ]); return $newCat; } /* * Store a newly created resource in storage. * * @param \Illuminate\Http\Request $request * @return \Illuminate\Http\Response / // public function generateRandIntToken(){ // $length = 8; // $min = pow(10, $length - 1); // $max = pow(10, $length) - 1; // $randomInt = rand($min, $max); // return $randomInt; // } /* * This part is for the checking of ticket status * Created, Picked, Assigned, Closed / public function ticketCreated(){ $check = DB::table('ticket_user') ->where([['assigned_dept',Auth::user()->department_id],['status',"=","pending"]]) ->latest('tix_id') ->first(); // dd($check); $sender = ''; if($check){ $sender = DB::table('users')->where('id',$check->usr_id)->first(); } return response()->json(['check'=>$check,'sender'=>$sender]); } public function CarplusTicketCreated(){ $carplus = DB::table('carplus_tickets') ->where('status',"=","open") ->latest('id') ->first(); return response()->json($carplus); } public function ticketClosed($id){ $check = DB::table('ticket_user') ->select('ticket_user.status','ticket_user.usr_id','ticket_user.stars') ->where('tix_id',$id) ->join('users','ticket_user.assigned_to',"=","users.id") ->latest('ticket_user.id') ->first(); if(($check) && $check->status == "closed"){ return response()->json(["status"=>1,"uid"=>$check->usr_id,"stars"=>$check->stars]); } else { return response()->json(["status"=>0]); } } public function checkTicketElapsedTime($id){ // $sla = DB::table('ticket') // ->select('ticket.time_elapsed','ticket.ticket_creator', // 'service_level_agreement.content', // 'ticket.ticket_no','ticket.start_date','ticket.end_date','ticket.time_elapsed',DB::raw('CONCAT(`users`.`f_name`," ",`users`.`l_name`) as `support_person`')) // ->join('categories','ticket.subject',"=",'categories.id') // ->join('service_level_agreement','categories.sla_id',"=",'service_level_agreement.id') // ->join('ticket_user','ticket_user.tix_id',"=",'ticket.id') // ->join('users','ticket_user.assigned_to',"=",'users.id') // ->where('ticket.id',$id) // ->first(); // DB::enableQueryLog(); $sla = DB::table('ticket') ->select('ticket.time_elapsed','ticket.ticket_creator', 'service_level_agreement.content', 'ticket.ticket_no','ticket.start_date','ticket.end_date','ticket.time_elapsed',DB::raw('CONCAT(`users`.`f_name`," ",`users`.`l_name`) as `support_person`')) ->join('categories','ticket.subject',"=",'categories.id') ->join('service_level_agreement','categories.sla_id',"=",'service_level_agreement.id') ->join('ticket_user','ticket_user.tix_id',"=",'ticket.id') ->join('users','ticket_user.assigned_to',"=",'users.id') ->where('ticket.id',$id) ->oRwhere('ticket_user.usr_id', Auth::user()->id) ->whereDate('ticket.created_at','>=' ,'2025-03-13') ->where('ticket_user.status', 'closed') ->whereNull('ticket_user.stars') ->whereNull('ticket_user.remarks') ->first(); // dd(DB::getQueryLog()); // dd($sla); if(!$sla) { return response()->json(['error'=> 'Ticket not found.'],304); } $startTime = Carbon::parse($sla->start_date); $endTime = $sla->end_date ? Carbon::parse($sla->end_date) : Carbon::now(); $time_diff = $endTime->diff($startTime); $time = [ 'days' => $time_diff->d, 'hours' => $time_diff->h, 'minutes' => $time_diff->i, 'seconds' => $time_diff->s ]; $s_l_a = $sla->content; $time_elapsed = ($sla->time_elapsed > 0 ? $sla->time_elapsed : $time_diff->h); // $compliance = ($sla->time_elapsed / $sla->content) 100 : 0); $compliance = ($sla->content > 0 && $sla->time_elapsed > 0 ? ($sla->time_elapsed / $sla->content) * 100 : ($time_diff->h / $sla->content) * 100); $support_person = $sla->support_person; $creator = $sla->ticket_creator; // dd(['ticket'=>$sla,'time'=>$time,'sla'=>$s_l_a,'compliance'=>$compliance, 'time_elapsed'=>$time_elapsed, 'support_person'=>$support_person, 'creator'=>$creator]); return response()->json(['ticket'=>$sla,'time'=>$time,'sla'=>$s_l_a,'compliance'=>$compliance, 'time_elapsed'=>$time_elapsed, 'support_person'=>$support_person, 'creator'=>$creator]); } public function getTicketSLAutilization($id){ $sla = DB::table('ticket') ->select('ticket.time_elapsed','ticket.ticket_creator', 'service_level_agreement.content', 'ticket.ticket_no','ticket.start_date','ticket.end_date','ticket.time_elapsed',DB::raw('CONCAT(`users`.`f_name`," ",`users`.`l_name`) as `support_person`')) ->join('categories','ticket.subject',"=",'categories.id') ->join('service_level_agreement','categories.sla_id',"=",'service_level_agreement.id') ->join('ticket_user','ticket_user.tix_id',"=",'ticket.id') ->join('users','ticket_user.assigned_to',"=",'users.id') ->where('ticket.id',$id) ->first(); if(!$sla) { return response()->json(['error'=> 'Ticket not found.'],304); } $startTime = Carbon::parse($sla->start_date); $endTime = $sla->end_date ? Carbon::parse($sla->end_date) : Carbon::now(); $time_diff = $endTime->diff($startTime); $time = [ 'days' => $time_diff->d, 'hours' => $time_diff->h, 'minutes' => $time_diff->i, 'seconds' => $time_diff->s ]; $s_l_a = $sla->content; $time_elapsed = ($sla->time_elapsed > 0 ? $sla->time_elapsed : $time_diff->h); // $compliance = ($sla->time_elapsed / $sla->content) * 100 : 0); $compliance = ($sla->content > 0 && $sla->time_elapsed > 0 ? ($sla->time_elapsed / $sla->content) * 100 : ($time_diff->h / $sla->content) * 100); $support_person = $sla->support_person; $creator = $sla->ticket_creator; // dd(['ticket'=>$sla,'time'=>$time,'sla'=>$s_l_a,'compliance'=>$compliance, 'time_elapsed'=>$time_elapsed, 'support_person'=>$support_person, 'creator'=>$creator]); return response()->json(['ticket'=>$sla,'time'=>$time,'sla'=>$s_l_a,'compliance'=>$compliance, 'time_elapsed'=>$time_elapsed, 'support_person'=>$support_person, 'creator'=>$creator]); } public function ticketPicked($id){ $check = DB::table('ticket_user') ->where('tix_id',$id) ->latest('id') ->first(); // dd($check); if($check->status == "ongoing"){ return response()->json(["status"=>1,"uid"=>$check->usr_id]); } else { return response()->json(["status"=>0]); } } // public function generateRandStringToken(){ // $characters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!_-[]/'; // $randomString = substr(str_shuffle($characters), 0, 4); // return $randomString; // } public function generateRandStringToken(){ $characters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'; $randomString = substr(str_shuffle($characters), 0, 8); // Increased length to 8 for better uniqueness return $randomString; } public function store(Request $request) { $request->validate([ 'company_id' => 'required', 'sender' => 'required', 'department' => 'required', // 'category' => 'required\|numeric', 'description' => 'required', 'attachment' => 'file\|mimes:jpeg,png,gif,pdf,doc,docx,xls,xlsx,csv\|max:2048', ],[ 'company_id.required' => "Please choose a company", 'sender.required' => "Please choose a sender", 'department.required' => "Please choose a department", // 'category.numeric' => "Please provide a category and sub-category", 'description.required' => "Please provide a note", ]); $path = null; if ($request->has('category') && $request->category !== 'null') { $id = $request->category; // Use the provided category } else { $id = 246; // Use the default value } // dd($id); // $id = ($request->category ==='null' ? 246 : $request->category); if ($request->hasFile('attachment')) { $file = $request->file('attachment'); // Ensure the file has a name and an extension $originalName = $file->getClientOriginalName(); $extension = $file->getClientOriginalExtension(); // Extract the base name (without extension) to ensure it has a valid name $baseName = pathinfo($originalName, PATHINFO_FILENAME); if ($baseName && $extension) { // Generate a safe, random string token $strToken = $this->generateRandStringToken(); $fileName = $strToken . '.' . $extension; // Ensure the final file name is not just an extension if (empty($fileName) \|\| $fileName[0] == '.') { return response()->json(['error' => 'File name generation failed'], 400); } if(!$file->move('files/tickets/', $fileName)){ return response()->json(['error' => 'Failed to upload attachment. Please contact system administrator support.'], 500); } $path = 'files/tickets/' . $fileName; } else { // Handle the case where the file has no valid name or extension return response()->json(['error' => 'File must have a valid name and extension'], 400); } } else { $path = null; } $user_sender = User::find($request->sender); // dd($user_sender); if (!$user_sender) { return response()->json(['error' => 'Sender Information not found'], 404); } DB::beginTransaction(); $dealer_code=$user_sender->deal_code;//Auth::user()->deal_code; $resultTicketNo = generateUniqueRandomStr($dealer_code); if (isset($resultTicketNo['error'])) { return response()->json(['error' => $resultTicketNo['error']], 500); } $ticketNumberRef = $resultTicketNo['ticket_number']; // Log::info('Ticket number reference:', ['ticket_number' => $ticketNumberRef]); $ticket = DB::table('ticket')->insertGetId([ 'ticket_no' => $ticketNumberRef, 'subject' => $id, 'file' => $path, 'description' => $request->description, 'status' => "pending", 'level' => "mid", 'active' => 1, 'ticket_creator' => Auth::user()->id, 'employee_id' => $user_sender->asa_id, 'company_id' => $request->company_id, ]); if($ticket){ DB::table('ticket_user') ->insert([ 'tix_id' => $ticket, 'usr_id' => $request->sender,//Auth::user()->id, 'assigned_dept' => $request->department, 'status' => "pending" ]); } DB::commit(); // Livewire::emit('ticketCreated','Ticket created Successfully'); $tix_no = DB::table('ticket')->find($ticket); // event(new CreateTicket(Auth::user()->id)); $to = $user_sender->email;//Auth::user()->email; $subject = "AutoHub Help Desk Ticket"; $headers = "From: Autohub Group of Companies Inc.\r\n"; $headers .= "Content-Type: text/html\r\n"; // $bodyMessage = "<p>Hi ". Auth::user()->f_name .",</p><p>Your ticket number ".$tix_no->ticket_no." has been submitted. We are currently assessing your concern and a support personnel will be assigned to you in a short while</p>"; $bodyMessage = "<p>Hi ". $user_sender->f_name .",</p><p>Your ticket number ".$tix_no->ticket_no." has been submitted. We are currently assessing your concern and a support personnel will be assigned to you in a short while</p>"; $message = view('mail.ticket',['ticket'=>$tix_no,'status'=>"CREATED",'message'=>$bodyMessage])->render(); try { $emailResponse = mail($to, $subject, $message, $headers); if ($emailResponse) { // dd($tix_no->id); // return response()->json(['message'=>"Success",'ticket'=>$tix_no->id]); // Email sent successfully // $this->emailResponseManagement(Auth::user()->id, $result->id, Auth::user()->email, NOW(), 1); // return 1; } else { // Email failed to send // $this->emailResponseManagement(Auth::user()->id, $result->id, Auth::user()->email, NOW(), 0); // return 0; } } catch (\Exception $e) { DB::rollback(); // Handle the exception $errorMessage = $e->getMessage(); // $this->emailResponseManagement(Auth::user()->id, $result->id, Auth::user()->email, NOW(), 0); return 0; } return response()->json(['message'=>"Success",'ticket'=>$tix_no->id]); } /** * Display the specified resource. * * @param \App\Models\Ticket $ticket * @return \Illuminate\Http\Response / public function show($id) { $user = new User; $userRole = $user->getUserRole(); $ticket = DB::table('ticket as tk') ->join('ticket_user as tu','tk.id',"=","tu.tix_id") ->join('users as usrSender','tu.usr_id',"=",'usrSender.id') ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') ->join('departments as dpt','tu.assigned_dept',"=","dpt.id") ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') ->where('tk.id',$id) ->select('tk.ticket_no as TIX_NO', 'tu.usr_id', 'tk.file as FILE', 'tk.description as DESCRIPTION', 'tk.created_at as CREATED_AT', DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as SENDER'), 'usrSender.f_name', 'usrSender.l_name', 'userSupp.f_name', 'userSupp.l_name', DB::raw('CONCAT(userSupp.f_name," ",userSupp.l_name) as support'), 'tk.status', 'ctg.title as CATEGORY', 'sla.content as SLA', 'sla.type as SLA_TYPE', 'dpt.DEPT_NAME as DEPARTMENT') ->first(); return view('layouts.authentication.ticket_component.show') ->with(['title_page'=>$ticket->TIX_NO, 'ticket'=>$ticket ,'userRole'=>$userRole]); } // public function showMyAssignedTicket($id) // { // $user = new User; // $userRole = $user->getUserRole(); // $ticket = DB::table('ticket as tk') // ->join('ticket_user as tu','tk.id',"=","tu.tix_id") // ->join('users as usrSender','tu.usr_id',"=",'usrSender.id') // ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') // ->join('departments as dpt','tu.assigned_dept',"=","dpt.id") // ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') // ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') // ->where('tk.id',$id) // ->select('tk.ticket_no as TIX_NO', // 'tu.usr_id', // 'tu.assigned_to', // 'tk.file as FILE', // 'tk.description as DESCRIPTION', // 'tk.created_at as CREATED_AT', // DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as SENDER'), // // DB::raw('CONCAT(userSupp.f_name," ",userSupp.l_name) as SUPPORT'), // DB::raw('tk.status as STATUS'), // 'ctg.title as CATEGORY', // 'sla.content as SLA', // 'sla.type as SLA_TYPE', // 'dpt.DEPT_NAME as DEPARTMENT') // ->first(); // return view('layouts.authentication.ticket_component.showMyAssignedTicket') // ->with(['title_page'=>$ticket->TIX_NO, 'ticket'=>$ticket ,'userRole'=>$userRole]); // } public function showMyAssignedTicket($id) { $user = new User; $userRole = $user->getUserRole(); $latestTicketUser = DB::table('ticket_user as tu') ->select('tu.') ->whereRaw('tu.id = (SELECT MAX(id) FROM ticket_user WHERE tix_id = tu.tix_id)'); $ticket = DB::table('ticket as tk') ->joinSub($latestTicketUser, 'tu', function ($join) { $join->on('tk.id', '=', 'tu.tix_id'); }) ->join('users as usrSender','tu.usr_id',"=",'usrSender.id') ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') ->leftJoin('users as transferred_by','tu.transfer_by',"=",'transferred_by.id') ->join('departments as dpt','tu.assigned_dept',"=","dpt.id") ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') ->where('tk.id',$id) ->select('tk.ticket_no as TIX_NO', 'tu.usr_id', 'tu.status', 'tu.assigned_to', 'tu.ticket_condition', 'tu.transfer_at', 'tu.transfer_by', DB::raw('CONCAT(transferred_by.f_name," ",transferred_by.l_name) as transferredBy'), 'tk.file as FILE', 'tk.description as DESCRIPTION', 'tk.created_at as CREATED_AT', DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as SENDER'), DB::raw('userSupp.id as support_id'), DB::raw('CONCAT(userSupp.f_name," ",userSupp.l_name) as SUPPORT'), DB::raw('tu.assigned_to as support_id'), DB::raw('tk.status as STATUS'), 'ctg.title as CATEGORY', 'sla.content as SLA', 'sla.type as SLA_TYPE', 'dpt.DEPT_NAME as DEPARTMENT') ->first(); return view('layouts.authentication.ticket_component.showMyAssignedTicket') ->with(['title_page'=>$ticket->TIX_NO, 'ticket'=>$ticket ,'userRole'=>$userRole]); } public function apiShow(Request $request){ $ticket = DB::table('ticket as tk') ->join('ticket_user as tu','tk.id',"=","tu.tix_id") ->join('users as usrSender','tk.ticket_creator',"=",'usrSender.id') ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') ->join('departments as dpt','tu.assigned_dept',"=","dpt.id") ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') ->where('tk.id',$request->id) ->select('tk.ticket_no as TIX_NO', 'tu.usr_id', 'tk.file as FILE', 'tk.description as DESCRIPTION', 'tk.created_at as CREATED_AT', DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as SENDER'), // DB::raw('CONCAT(userSupp.f_name," ",userSupp.l_name) as SUPPORT'), 'ctg.title as CATEGORY', 'sla.content as SLA', 'dpt.DEPT_NAME as DEPARTMENT') ->first(); // ->get(); // $ticket = DB::table('ticket as tk') // ->join('ticket_user as tu','tk.id',"=","tu.tix_id") // ->join('users as usrSender','tk.ticket_creator',"=",'usrSender.id') // ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') // ->join('departments as dpt','tu.assigned_dept',"=","dpt.id") // ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') // ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') // ->where('tk.id',$id) // ->select('tk.ticket_no as TIX_NO', // 'tu.usr_id', // 'tk.file as FILE', // 'tk.created_at as CREATED_AT', // DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as SENDER'), // // DB::raw('CONCAT(userSupp.f_name," ",userSupp.l_name) as SUPPORT'), // 'ctg.title as CATEGORY', // 'sla.content as SLA', // 'dpt.DEPT_NAME as DEPARTMENT') // ->first(); return response()->json($ticket); } public function ticketNoRating(){ DB::enableQueryLog(); $data = DB::table('ticket') ->select( 'ticket_user.id', 'ticket.time_elapsed', 'ticket.ticket_creator', 'service_level_agreement.content', 'ticket.ticket_no', 'ticket.start_date', 'ticket.end_date', DB::raw('CONCAT(users.f_name, " ", users.l_name) as support_person') ) ->join('categories', 'ticket.subject', '=', 'categories.id') ->join('service_level_agreement', 'categories.sla_id', '=', 'service_level_agreement.id') ->join('ticket_user', 'ticket_user.tix_id', '=', 'ticket.id') ->join('users', 'ticket_user.assigned_to', '=', 'users.id') ->where('ticket_user.usr_id', Auth::user()->id) ->whereDate('ticket.created_at','>=', '2025-03-27') ->where('ticket_user.status', 'closed') ->whereNull('ticket_user.stars') ->whereNull('ticket_user.remarks') ->orderBy('ticket_user.id', 'desc') // Get latest assigned user ->limit(1) // Ensure single result ->first(); if($data){ $startTime = Carbon::parse($data->start_date) ?? null; $endTime = $data->end_date ? Carbon::parse($data->end_date) : Carbon::now(); $time_diff = $endTime->diff($startTime); $duration_label = ''; $hours = ($time_diff->d * 24) + $time_diff->h; // Convert days to hours and add existing hours $minutes = $time_diff->i; $seconds = $time_diff->s; $duration_label = sprintf("%02d:%02d:%02d", $hours, $minutes, $seconds); $time = [ 'days' => $time_diff->d, 'hours' => $time_diff->h, 'minutes' => $time_diff->i, 'seconds' => $time_diff->s ]; $s_l_a = $data->content; // $time_elapsed = ($time_diff > 0) ? $time_diff : $time_diff->days * $s_l_a + $time_diff->h * 60 + $time_diff->i; $time_elapsed = ($time_diff->days * $s_l_a) + ($time_diff->h * 60) + $time_diff->i + ($time_diff->s / 60); // $time_elapsed = ($time_diff->days * 1440) + ($time_diff->h * 60) + $time_diff->i + ($time_diff->s / 60); $SLA_ulitlization = ($s_l_a > 0) ? ($time_elapsed / $s_l_a) * 100 : 0; $SLA_ulitlization = round($SLA_ulitlization,2); $compliance_rate = ($time_elapsed / $s_l_a)* 100; $compliance_rate = number_format($compliance_rate, 2); $support_person = $data->support_person; $creator = $data->ticket_creator; return response()->json([ 'ticket'=>$data, 'time'=>$time, 'sla'=>$s_l_a, 'compliance'=>$SLA_ulitlization, 'compliance_rate' => $compliance_rate, // 'time_elapsed'=>$time_elapsed + $duration_label, 'time_elapsed'=>($time_diff->h > 1 ? $duration_label. ' hrs' : $duration_label.' hr'), 'support_person'=>$support_person, 'creator'=>$creator ]); } } /** * Show the form for editing the specified resource. * * @param \App\Models\Ticket $ticket * @return \Illuminate\Http\Response / public function edit(Ticket $ticket) { // } /* * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param \App\Models\Ticket $ticket * @return \Illuminate\Http\Response / /* * Remove the specified resource from storage. * * @param \App\Models\Ticket $ticket * @return \Illuminate\Http\Response / public function destroy(Ticket $ticket) { // } public function getDepartments(Request $request){ $data = []; $term = $request->search; $response = Department::where(function($query) use ($term){ $query->orWhere('dept_name', 'like', '%'.$term.'%'); }) // ->whereIn('id',[15,23])->orderBy('dept_name','asc') ->get(); foreach($response as $item){ $data['results'][] = [ 'id' => $item->id, 'text' => $item->dept_name ]; } return response()->json($data); } public function getUsers(Request $request){ $data = []; // $currentUserID = $request->userId; // DB::enableQueryLog(); $term = $request->search; $response = User::where(function($query) use ($term) { $query->orWhere('f_name', 'like', '%'.$term.'%') ->orWhere('l_name','like', '%'.$term.'%'); })->orderBy('f_name', 'asc') ->where('active', '=', 1) // inactive user ->get(); // $results = DB::getQueryLog(); // echo'<pre>';print_r($results); echo'</pre>';exit; foreach($response as $item){ $data['results'][] = [ 'id' => $item->id, 'text' => $item->f_name.' '.$item->l_name.' - ('.$item->asa_id.')' ]; }unset($item); return response()->json($data); } public function sendMessage(Request $request, $id){ $ticketStatus = DB::table('ticket') ->where('id',"=",$id) ->first(); if($ticketStatus->status == "closed"){ abort(403); } if (!empty($request->file('files'))) { // Handle file uploads foreach ($request->file('files') as $file) { $fileName = $file->getClientOriginalName(); $file->move('files/tickets/messages/', $fileName); $path = 'files/tickets/messages/' . $fileName; $message = DB::table('messages')->insertGetId([ 'content' => $path, 'sender_id' => Auth::user()->id ]); DB::table('message_ticket')->insert([ 'm_id' => $message, 't_id' => $id ]); } } if (isset($request->message)) { // dd($request->message); // Handle the message $message = DB::table('messages')->insertGetId([ 'content' => $request->message, 'sender_id' => Auth::user()->id ]); if ($message) { DB::table('message_ticket')->insert([ 'm_id' => $message, 't_id' => $id ]); return response()->json($message, 201); } else { abort(400, ['message' => "Invalid request"]); } } } public function fetchMessages(Request $request, $id){ $message = DB::table('messages as m') ->select('m.id','m.content','m.sender_id as sender','m.created_at','t.ticket_creator as m_sender',DB::raw('CONCAT(u.f_name," ",u.l_name) as name'),'u.f_name','u.l_name') ->join('message_ticket as mt','m.id',"=",'mt.m_id') ->join('users as u','m.sender_id',"=",'u.id') ->join('ticket as t','mt.t_id',"=",'t.id') ->where('t.id',"=",$id) ->get(); $user = Auth::user()->id; return response()->json(['message'=>$message,'id'=>$user]); } public function fetchSupports(Request $request){ $dept = Auth::user()->department_id; $support = Role::where('name', 'support-access') ->first() ->users() ->select('users.id', 'f_name', 'l_name') ->selectSub(function ($query) { $query->selectRaw('COUNT()') ->from('ticket_user') ->whereColumn('ticket_user.assigned_to', 'users.id') ->whereDate('ticket_user.created_at', Carbon::today()) ->whereNotIn('ticket_user.status', ['closed', 'transferred']); }, 'totalTicket') ->where('active',1) ->where('department_id', $dept) ->get(); return response()->json($support); } public function assignSupportToTicket(Request $request){ // Validate ticket existence $tix = DB::table('ticket')->where('id', $request->tix_id)->first(); if (!$tix) { return response()->json(['message' => "Ticket not found"], 404); } // Validate ticket level if (!$tix->level) { return response()->json(['message' => "Please assign level first before assigning"], 403); } // Check if already assigned $ticket = DB::table('ticket_user')->where('tix_id', $request->tix_id)->first(); if ($ticket && $ticket->assigned_to == $request->suppId) { return response()->json(['message' => "You already assigned this ticket to this person"], 400); } // Fetch support user details $supportUser = DB::table('users') ->where('id', $request->suppId) ->select('f_name', 'l_name', 'asa_id') // Assuming 'asa_id' exists in the `users` table ->first(); if (!$supportUser) { return response()->json(['message' => "Support user not found"], 404); } try { // Begin transaction DB::beginTransaction(); // Update ticket_user DB::table('ticket_user') ->where('tix_id', $request->tix_id) ->update([ 'status' => "ongoing", 'ticket_condition' => "assigned", 'assigned_to' => $request->suppId, 'assigned_employee' => $supportUser->asa_id, 'assigned_by' => Auth::user()->id, ]); // Update support user status DB::table('users') ->where('id', $request->suppId) ->update(['status' => null]); // Update ticket status DB::table('ticket') ->where('id', $request->tix_id) ->update(['status' => "ongoing"]); DB::commit(); return response()->json(["message" => "Success", "result" => true]); } catch (\Exception $e) { // Rollback on failure DB::rollBack(); return response()->json(['message' => "An error occurred: " . $e->getMessage()], 500); } // Trigger events event(new AssignTicket($request->tix_id, $request->suppId)); event(new PickTicket($request->tix_id, $request->suppId, $tix->ticket_creator)); // Commit transaction } public function supportPickTicket(Request $request){ $tix = DB::table('ticket') ->where('id',$request->tix_id) ->first(); $ticket = DB::table('ticket_user') ->where('tix_id',$request->tix_id) ->latest('id') ->first(); //check if the record exists if(($ticket->status!='pending' \|\| $ticket->assigned_to !=null \|\| $ticket->ticket_condition !=null) && $tix->status !='pending') { return response()->json(["status"=> 3,"message"=>"Unable to pick ticket. Please contact system administrator."]); } $ticket_dtl = DB::table('ticket')->where('id',$ticket->tix_id)->first(); if($ticket_dtl->subject ==246 \|\| $ticket_dtl->subject =='') { return response()->json(['status'=>0,'message'=> 'Please update ticket category,sub and sla before pick']); // return response()->json(["message"=>"Success", "result" => $tix]); } if($ticket){ $res = DB::table('ticket_user') ->where('tix_id', $request->tix_id) ->latest('id') ->update([ 'status' => "ongoing", 'ticket_condition' => "picked", 'assigned_to' => $request->suppId, 'assigned_employee' => Auth::user()->asa_id, 'start_date' => NOW() ]); //update the record of ticket on pivot table $tckt = DB::table('ticket') ->where('id',$request->tix_id) ->update([ 'status' => "ongoing", 'start_date' => NOW() ]); //update the ticket status // event(new PickTicket($request->tix_id,$request->suppId,$tix->ticket_creator)); return response()->json(["message"=>"Success", "result" => $tix]); } } // public function update(Request $request, $id) // { // $validatedData = $request->validate([ // 'category' => 'required\|string', // Adjust validation rules as needed // ]); // $ticket = DB::table('ticket')->where('id', $id)->first(); // if (!$ticket) { // return response()->json(['message' => 'Ticket not found'], 404); // } // $ticket_user = DB::table('ticket_user') // ->where('tix_id',$id) // ->latest('id') // ->first(); //check if the record exists // if(($ticket_user->status!='pending' \|\| $ticket_user->assigned_to !=null \|\| $ticket_user->ticket_condition !=null) && $ticket->status !='pending') // { // return response()->json(["status"=> 3,"message"=>"Unable to pick ticket. Please contact system administrator."]); // } // $sla = DB::table('service_level_agreement')->where('id',$request->_sla)->first(); // if (!$sla) { // return response()->json(['message' => 'sla not found'], 404); // } // DB::beginTransaction(); // try { // DB::table('ticket') // ->where('id', $id) // ->update([ // 'subject' => $validatedData['category'], // 'type_of_support' => $request->_type_of_support, // 'status' => "ongoing", // 'start_date' => NOW() // ]); // DB::table('ticket_user') // ->where('id', $id) // ->update([ // 'sla' => $sla->content, // 'sla_type' => $sla->type, // 'status' => "ongoing", // 'ticket_condition' => "picked", // 'assigned_to' => Auth::user()->id, // 'assigned_employee' => Auth::user()->asa_id, // 'start_date' => NOW() // ]); // DB::commit(); // Commit the transaction // return response()->json(["message"=>"Success", "result" => $id]); // // return response()->json(['message' => 'Ticket updated successfully', 'ticket' => $id]); // } catch (Throwable $e) { // DB::rollback(); // Rollback the transaction on failure // return response()->json([ // 'status' => 'failed', // 'message' => 'Unable to process request. Please try again.', // 'data' => $e->getMessage() // ], 500); // Return a 500 status code for server error // } // } //UPDATE AND PICK // public function update(Request $request, $id) // { // $validatedData = $request->validate([ // 'category' => 'required\|string', // ]); // // Fetch ticket and validate existence // $ticket = DB::table('ticket')->find($id); // if (!$ticket) { // return response()->json(['message' => 'Ticket not found'], 404); // } // // Fetch latest ticket_user entry // $ticket_user = DB::table('ticket_user') // ->where('tix_id', $id) // ->latest('id') // ->first(); // if (!$ticket_user \|\| ($ticket_user->status !== 'pending' \|\| $ticket_user->assigned_to \|\| $ticket_user->ticket_condition) && $ticket->status !== 'pending') { // return response()->json(["status" => 3, "message" => "Unable to pick ticket. Please contact system administrator."]); // } // // Fetch SLA and validate existence // $sla = DB::table('service_level_agreement')->where('id',$request->_sla)->first(); // if (!$sla) { // return response()->json(['message' => 'SLA not found'], 404); // } // DB::beginTransaction(); // try { // $now = Carbon::now(); // // Update ticket // DB::table('ticket')->where('id', $id)->update([ // 'subject' => $validatedData['category'], // 'type_of_support' => $request->_type_of_support, // 'status' => "ongoing", // 'start_date' => $now // ]); // // Update ticket_user (ensure correct where condition) // DB::table('ticket_user')->where('tix_id', $id)->update([ // 'sla' => $sla->content, // 'sla_type' => $sla->type, // 'status' => "ongoing", // 'ticket_condition' => "picked", // 'assigned_to' => Auth::id(), // 'assigned_employee' => Auth::user()->asa_id, // 'start_date' => $now // ]); // DB::commit(); // return response()->json(["message" => "success", "result" => $id]); // } catch (Exception $e) { // DB::rollback(); // return response()->json([ // 'status' => 'failed', // 'message' => 'Unable to process request. Please try again.', // 'error' => $e->getMessage() // ], 500); // } // } public function update(Request $request, $id) { // $validatedData = $request->validate([ // 'category' => 'required\|string', // '_type_of_support' => 'required\|string', // '_sla' => 'required\|integer' // ]); DB::beginTransaction(); try { // Fetch ticket and validate existence $ticket = DB::table('ticket')->where('id', $id)->first(); if (!$ticket) { return response()->json(['message' => 'Ticket not found'], 404); } // Fetch latest ticket_user entry $ticket_user = DB::table('ticket_user') ->where('tix_id', $id) ->latest('id') ->first(); if (!$ticket_user \|\| ($ticket_user->status !== 'pending' \|\| $ticket_user->assigned_to \|\|$ticket_user->ticket_condition) && $ticket->status !== 'pending' ) { return response()->json(["status" => 3, "message" => "Unable to pick ticket. Please contact system administrator."]); } // Fetch SLA and validate existence $sla = DB::table('service_level_agreement')->where('id', $request->_sla)->first(); if (!$sla) { return response()->json(['message' => 'SLA not found'], 404); } $now = Carbon::now(); // ✅ Update ticket DB::table('ticket')->where('id', $id)->update([ 'subject' => $validatedData['category'], 'type_of_support' => $validatedData['_type_of_support'], 'status' => "ongoing", 'start_date' => $now ]); // ✅ Update ticket_user DB::table('ticket_user')->where('tix_id', $id)->update([ 'sla' => $sla->content, 'sla_type' => $sla->type, 'status' => "ongoing", 'ticket_condition' => "picked", 'assigned_to' => Auth::id(), 'assigned_employee' => Auth::user()->asa_id, 'start_date' => $now ]); DB::commit(); return response()->json(["message" => "success", "result" => $id]); } catch (Exception $e) { DB::rollback(); return response()->json([ 'status' => 'failed', 'message' => 'Unable to process request. Please try again.', 'error' => $e->getMessage() ], 500); } } public function update_assign_ticket(Request $request, $id) { DB::beginTransaction(); try { // Fetch ticket and validate existence $ticket = DB::table('ticket')->where('id', $id)->first(); if (!$ticket) { return response()->json(['message' => 'Ticket not found'], 404); } // Fetch latest ticket_user entry $ticket_user = DB::table('ticket_user') ->where('tix_id', $id) ->latest('id') ->first(); if (!$ticket_user \|\| ($ticket_user->status !== 'ongoing' && $ticket_user->ticket_condition !=='assigned')) { return response()->json(["status" => 3, "message" => "Unable to update ticket. Please contact system administrator."]); } // Fetch SLA and validate existence $sla = DB::table('service_level_agreement')->where('id', $request->_sla)->first(); if (!$sla) { return response()->json(['message' => 'SLA not found'], 404); } $now = Carbon::now(); // Update ticket DB::table('ticket')->where('id', $id)->update([ 'subject' => $request->category, 'type_of_support' => $request->_type_of_support, 'status' => "ongoing", 'start_date' => $now ]); // Update ticket_user DB::table('ticket_user')->where('tix_id', $id)->update([ 'sla' => $sla->content, 'sla_type' => $sla->type, 'status' => "ongoing", 'ticket_condition' => "picked", 'assigned_to' => Auth::id(), 'assigned_employee' => Auth::user()->asa_id, 'start_date' => $now ]); DB::commit(); return response()->json(["message" => "success", "result" => $id]); } catch (Exception $e) { DB::rollback(); dd($e); return response()->json([ 'status' => 'failed', 'message' => 'Unable to process request. Please try again.', 'error' => $e->getMessage() ], 500); } } public function reviewTicket(Request $request, $id) { $request->validate([ 'stars' => 'required', 'remarks' => 'required' ]); $ticket = DB::table('ticket_user') ->where('id', $id) ->latest('id') ->first(); if (!$ticket) { return response()->json(['message' => "Ticket not found"], 404); } try { DB::beginTransaction(); if ($ticket->stars) { return response()->json(['message' => "You already reviewed this ticket"], 403); } DB::table('ticket_user') ->where('id', $ticket->id) ->update([ 'stars' => $request->stars, 'remarks' => $request->remarks ]); DB::commit(); return response()->json(['message' => "Success"], 201); } catch (\Exception $e) { DB::rollBack(); return response()->json([ 'status' => 'failed', 'message' => $e->getMessage(), ], 500); } } public function ticketSolution(Request $request, $id){ $ticket = DB::table('ticket_user') ->where('tix_id',$id) ->latest('id') ->first(); $solution = DB::table('ticket') ->where('id',$id) ->first(); if($solution){ if($solution->solution){ return response()->json(['message'=>"this ticket is closed already"],403); } else { $solution = DB::table('ticket') ->where('id',$id) ->update(['solution' => $request->solution]); return response()->json(['message'=>"Success", "tix"=>$solution],201); } } } public function transferTicket(Request $request, $id){ $lastRecord = DB::table('ticket_user') ->where('tix_id', $id) ->latest('id') ->first(); if (!$lastRecord) { return response()->json(["message" => "Ticket not found"], 404); } if ($lastRecord->usr_id === $request->suppId) { return response()->json(["message" => "Ticket cannot be transferred to the sender"], 403); } DB::beginTransaction(); try { // Update the last record DB::table('ticket_user') ->where('id', $lastRecord->id) ->update([ 'status' => "transferred", 'ticket_condition' => "transferred", 'updated_at' => now(), 'end_date' => now(), 'transfer_at' => now(), 'transfer_by' => Auth::user()->id, ]); if ($request->suppId && $request->suppId > 2) { // Retrieve support user details only once $supportUser = User::select('id', 'asa_id', 'f_name', 'l_name') ->where('id', $request->suppId) ->firstOrFail(); // Insert new ticket_user record DB::table('ticket_user')->insert([ 'tix_id' => $id, 'usr_id' => $lastRecord->usr_id, 'assigned_to' => $supportUser->id, 'assigned_dept' => $lastRecord->assigned_dept, 'assigned_employee' => $supportUser->asa_id, 'status' => "ongoing", 'ticket_condition' => 'transferred', 'created_at' => now(), 'updated_at' => now(), 'start_date' => now(), 'transfer_at' => now(), 'transfer_by' => Auth::user()->id ]); // Retrieve ticket creator information $ticket = DB::table('ticket')->where('id', $id)->first(); DB::table('ticket') ->where('id', $id) ->update([ 'transfer_at' => now(), 'transfer_by' => Auth::user()->id, // 'updated_at' => now() // Optionally, if you want to update the timestamp ]); $assigneeName = "{$supportUser->f_name} {$supportUser->l_name}"; DB::commit(); // Trigger events event(new AssignTicket($request->tix_id, $supportUser->id)); event(new TransferTicket($id, $supportUser->id, $ticket->ticket_creator, $assigneeName)); return response()->json(['message' => "Success"]); } } catch (Throwable $e) { DB::rollBack(); return response()->json([ 'status' => 'failed', 'message' => 'Unable to process request. Please try again.', 'error' => $e->getMessage() ], 500); } } public function closeTicket(Request $request, $id){ // var_dump($id); // dd($request->all()); DB::beginTransaction(); try{ $lastRecord = DB::table('ticket_user') ->where('tix_id', $id) ->latest('tix_id') // Assuming 'id' is the primary key column ->first(); if(!$lastRecord){ return response()->json(['status'=>0,'message'=> "No data found."]); } if ($lastRecord) { if($lastRecord->status =='closed') { return response()->json(['status'=>0,'message'=> "Ticket has already closed."]); } // Update the last record $startTime = Carbon::parse($lastRecord->start_date); $endTime = Carbon::now(); // $elapsedTime = $endTime->diffInSeconds($startTime); // $elapsedTime = $endTime->diffInMinutes($startTime); $totalSeconds = $endTime->diffInSeconds($startTime); // Convert the total seconds to hours, minutes, and seconds $days = floor($totalSeconds / 86400); $hours = floor($totalSeconds / 3600); $whole_hours = intval($totalSeconds / 3600); $minutes = floor(($totalSeconds % 3600) / 60); $seconds = $totalSeconds % 60; // dd($whole_hours); // Format the elapsed time as "HH:MM:SS" // $elapsedTimeFormatted = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds); // Format the elapsed time as "X hrs Y mins and Z secs" // $elapsedTime = sprintf('%d hrs %d mins and %d secs', $hours, $minutes, $seconds); if ($days > 0) { $elapsedTimeFormatted = sprintf('%d days %02d:%02d:%02d', $days, $hours, $minutes, $seconds); } else { $elapsedTimeFormatted = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds); } $res = DB::table('ticket_user') ->where('tix_id', $id) ->update([ 'status' => 'closed', 'updated_at' => now(), 'end_date' => now(), 'time_elapsed' => $whole_hours,//$hours, 'time_elapsed_time' => $elapsedTimeFormatted, // Use the formatted time string here ]); } $tix = DB::table('ticket') ->where('id',$id) ->first(); $startDateTime = Carbon::parse($tix->start_date); $endDateTime = Carbon::parse($tix->end_date); $timeDiff = $endDateTime->diffInMinutes($startDateTime); $ticket = DB::table('ticket') ->where('id',$id) ->update([ 'updated_at'=>NOW(), 'end_date'=>NOW(), 'status'=>"closed", 'solution' => $request->solution, 'time_elapsed' =>$whole_hours, 'time_elapsed_time' => $elapsedTimeFormatted ]); DB::commit(); $sender = $tix->ticket_creator; $tix_no = DB::table('ticket')->find($id); $ticket_details = DB::table('ticket_user as tu') ->select('tckt.ticket_no','tckt.created_at','parent_ctg.title as parent_category', 'ctg.title as sub_category','sla.content','sla.type','tckt.description', DB::raw("CONCAT(support.f_name,' ',support.l_name) as support"), 'tckt.start_date','tckt.end_date','tckt.solution','tu.stars','tu.remarks') ->join('ticket as tckt','tu.tix_id',"=",'tckt.id') ->join('categories as ctg','tckt.subject',"=",'ctg.id') ->join('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') ->leftJoin('users as support','tu.assigned_to',"=",'support.id') ->leftJoin('categories as parent_ctg','ctg.parent_id',"=",'parent_ctg.id') ->where('tckt.id',$id) ->first(); $to = User::where('id',$sender)->select('email')->first(); $subject = "AutoHub Help Desk Ticket"; $headers = "From: Autohub Group of Companies Inc.\r\n"; $headers .= "Content-Type: text/html\r\n"; $bodyMessage = "<table class='info-table' style='margin-bottom: 25px;'> <tbody> <tr> <td style='width: 50px;'>Ticket no:</td> <td>".$ticket_details->ticket_no."</td> </tr> <tr> <td style='width: 50px;'>Date created:</td> <td>".$ticket_details->created_at."</td> </tr> <tr> <td style='width: 50px;'>Category:</td> <td>".$ticket_details->parent_category."</td> </tr> <tr> <td style='width: 50px;'>Sub-Category:</td> <td>".$ticket_details->sub_category."</td> </tr> <tr> <td style='width: 50px;'>SLA:</td> <td>".$ticket_details->content." ".$ticket_details->type."</td> </tr> <tr> <td style='width: 50px;'>Note:</td> <td>".$ticket_details->description."</td> </tr> <tr> <td style='width: 50px;'>Support Assigned:</td> <td>".$ticket_details->support."</td> </tr> <tr> <td style='width: 50px;'>Date & Time Support started:</td> <td>".$ticket_details->start_date."</td> </tr> <tr> <td style='width: 50px;'>Date & Time Support ended:</td> <td>".$ticket_details->end_date."</td> </tr> <tr> <td style='width: 50px;'>Solution:</td> <td>".$ticket_details->solution."</td> </tr> <tr> <td style='width: 50px;'>Feedback:</td> <td>".$ticket_details->stars."</td> </tr> <tr> <td style='width: 50px;'>Remarks:</td> <td>".$ticket_details->remarks."</td> </tr> </tbody> </table>"; $message = view('mail.ticket',['ticket'=>$tix_no,'status'=>"CLOSED",'message'=>$bodyMessage])->render(); try { $emailResponse = mail($to->email, $subject, $message, $headers); if ($emailResponse) { // Email sent successfully // $this->emailResponseManagement(Auth::user()->id, $result->id, Auth::user()->email, NOW(), 1); // return 1; } else { // Email failed to send // $this->emailResponseManagement(Auth::user()->id, $result->id, Auth::user()->email, NOW(), 0); // return 0; } } catch (\Exception $e) { // Handle the exception $errorMessage = $e->getMessage(); // DB::table('debug')->insert([ // 'content'=>$errorMessage // ]); Log::info($errorMessage); return 0; } return response()->json(['status'=>1,'message' => "Ticket successfully closed."]); // return response()->json(['message' => "Deleted Successfully"]); } catch(\Exception $e) { DB::rollback(); // Rollback the transaction on failure return response()->json([ 'status' => 'failed', 'message' => 'Unable to process request. Please try again.', 'data' => $e->getMessage() ], 500); // Re } } public function assignedTickets(){ if($this->auth->checkMaintenance() == "true"){ abort(503); } return view('layouts/authentication/admin/assigned_ticket/index') ->with(['title_page' => "My Assigned Tickets"]); } public function fetchAssignedTickets(Request $request){ $userId = Auth::id(); $searchKeyword = $request->search['value'] ?? null; $searchType = $request->search_type ? json_decode($request->search_type, true)[0] : ''; $sortDirection = $request->order[0]['dir'] ?? 'asc'; $sortColumn = $request->columns[$request->order[0]['column']]['data'] ?? 'created_at'; $offset = $request->start ?? 0; $limit = $request->length ?? 10; // Construct query filters $filters = [ 'search' => $searchKeyword, 'type' => $searchType, 'option' => $request->input('data.option', 'all'), 'from_date' => $request->input('data.from_date'), 'to_date' => $request->input('data.to_date'), ]; // Query for the latest ticket_user record per ticket $latestTicketUser = DB::table('ticket_user as tu') ->select('tu.*') ->whereRaw('tu.id = (SELECT MAX(id) FROM ticket_user WHERE tix_id = tu.tix_id)'); // Main query $query = DB::table('ticket as t') ->select([ 't.id as ticket_id', 't.ticket_no', 't.description', 't.created_at', DB::raw("IF(t.start_date IS NULL, '', DATE_FORMAT(t.start_date, '%M %e, %Y %h:%i %p')) as start_date"), DB::raw("IF(t.end_date IS NULL, '', DATE_FORMAT(t.end_date, '%M %e, %Y %h:%i %p')) as end_date"), 't.solution', 'ctg.title as parent_category', 'sub.title as sub_category', DB::raw("CONCAT(sla.content, ' ', sla.type) as sla"), 'tu.status as ticket_status', DB::raw("CONCAT(support.f_name, ' ', support.l_name) as support"), 'support.position as support_position', 'tu.stars', 'tu.ticket_condition', 'tu.remarks', 'tu.assigned_dept', 'tu.usr_id', DB::raw("CONCAT(sender.f_name, ' ', sender.l_name) as sender_name"), 'sender.email as sender_email', 'sender.position as sender_position', 'sender.viber', DB::raw("CONCAT(assignee.f_name, ' ', assignee.l_name) as assignee"), DB::raw("CONCAT(usr.f_name, ' ', usr.l_name) as ticket_creator"), DB::raw("CONCAT(transfer_by.f_name, ' ', transfer_by.l_name) as transferredBy"), 'tu.transfer_at', 'tu.transfer_by', ]) ->joinSub($latestTicketUser, 'tu', function ($join) { $join->on('t.id', '=', 'tu.tix_id'); }) ->join('categories as sub', 't.subject', '=', 'sub.id') ->leftJoin('categories as ctg', 'sub.parent_id', '=', 'ctg.id') ->leftJoin('service_level_agreement as sla', 'sub.sla_id', '=', 'sla.id') ->leftJoin('users as support', 'tu.assigned_to', '=', 'support.id') ->leftJoin('users as assignee', 'tu.assigned_by', '=', 'assignee.id') ->leftJoin('users as sender', 'tu.usr_id', '=', 'sender.id') ->leftJoin('users as transfer_by', 'tu.transfer_by', '=', 'transfer_by.id') ->leftJoin('users as usr', 'usr.id', '=', 't.ticket_creator') ->where('tu.assigned_to', $userId); // Apply search filters if ($filters['search']) { $query->where(function ($q) use ($filters) { $q->where('t.ticket_no', 'like', "%{$filters['search']}%") ->orWhere('t.description', 'like', "%{$filters['search']}%"); }); } if ($filters['option'] && $filters['option'] !== 'all') { if (in_array($filters['option'], ['assigned', 'picked'])) { $query->where('tu.ticket_condition', 'like', "%{$filters['option']}%"); } else { $query->where('tu.status', 'like', "%{$filters['option']}%"); } } if ($filters['from_date'] && $filters['to_date']) { $fromDate = Carbon::parse($filters['from_date'])->toDateString(); $toDate = Carbon::parse($filters['to_date'])->toDateString(); $query->whereBetween(DB::raw('DATE(tu.created_at)'), [$fromDate, $toDate]); } // Apply sorting and pagination $query->orderBy($sortColumn, $sortDirection) ->offset($offset) ->limit($limit); // Execute the query $results = $query->get(); // Format response $data = []; foreach ($results as $index => $row) { $data[] = [ "#" => $index + 1 + $offset, "ticket_id" => $row->ticket_id, "ticket_no" => $row->ticket_no, "parent_category" => $row->parent_category, "sub_category" => $row->sub_category, "sla" => $row->sla, "description" => $row->description, "support" => $row->support, "support_position" => $row->support_position, "sender_name" => $row->sender_name, "sender_email" => $row->sender_email, "sender_mobile" => $row->viber, "sender_position" => $row->sender_position, "ticket_creator" => $row->ticket_creator, "created_at" => $row->created_at, "start_date" => $row->start_date, "end_date" => $row->end_date, "remarks" => $row->remarks, "solution" => $row->solution, "status" => ($userId == $row->transfer_by ? 'transferred' : $row->ticket_status), "condition" => $row->ticket_condition, "stars" => $row->stars, "assignee" => $row->assignee, "transfer_at" => $row->transfer_at ? date('F d, Y h:i A', strtotime($row->transfer_at)) : '', "transferredBy" => $row->transferredBy, 'usr_id' => $row->usr_id, 'assigned_dept' => $row->assigned_dept ]; } // Fetch total record count $recordsTotal = DB::table('ticket as t') ->joinSub($latestTicketUser, 'tu', function ($join) { $join->on('t.id', '=', 'tu.tix_id'); }) ->where('tu.assigned_to', Auth::id()) ->count(); // Fetch filtered record count $filteredQuery = DB::table('ticket as t') ->joinSub($latestTicketUser, 'tu', function ($join) { $join->on('t.id', '=', 'tu.tix_id'); }) ->where('tu.assigned_to', Auth::id()); // Apply search filters to the filtered query if ($filters['search']) { $filteredQuery->where(function ($q) use ($filters) { $q->where('t.ticket_no', 'like', "%{$filters['search']}%") ->orWhere('t.description', 'like', "%{$filters['search']}%"); }); } if ($filters['option'] && $filters['option'] !== 'all') { if (in_array($filters['option'], ['assigned', 'picked'])) { $filteredQuery->where('tu.ticket_condition', 'like', "%{$filters['option']}%"); } else { $filteredQuery->where('tu.status', 'like', "%{$filters['option']}%"); } } if ($filters['from_date'] && $filters['to_date']) { $fromDate = Carbon::parse($filters['from_date'])->toDateString(); $toDate = Carbon::parse($filters['to_date'])->toDateString(); $filteredQuery->whereBetween(DB::raw('DATE(tu.created_at)'), [$fromDate, $toDate]); } $recordsFiltered = $filteredQuery->count(); return response()->json([ 'draw' => $request->draw, 'recordsTotal' => $recordsTotal, 'recordsFiltered' => $recordsFiltered, 'data' => $data, ]); } public function assignLevel(Request $request){ $ticket = DB::table('ticket') ->where('id',$request->tix_id) ->update([ 'level' => $request->tix_lvl ]); return response()->json(['message'=>"Success"]); } public function transferTicketToOtherDepartment(Request $request){ $ticket = DB::table('ticket_user') ->where('tix_id', $request->tix_id) // Replace 'ticket_id' with the actual column name ->orderBy('id', 'desc') // Order by 'id' in descending order ->first(); // Retrieve the record if ($ticket) { DB::table('ticket_user') ->where('id', $ticket->id) ->update(['assigned_dept' => $request->dept_id]); // Update the record } return response()->json(['message'=>"Ticket Transferred Successfully"],200); } public function companyList(Request $request) { $data = []; // Check if a specific company code is provided if ($request->has('company_code')) { $company = \App\Models\Company::select('id', 'comp_code', 'comp_name') ->where('comp_code', $request->input('company_code')) ->where('active', 1) ->first(); if ($company) { return response()->json([ 'id' => $company->id,//$company->comp_code, 'text' => $company->comp_code.' - '.$company->comp_name ]); } return response()->json(['error' => 'Company not found'], 404); } // Otherwise, return the full list $companies = \App\Models\Company::select('id', 'comp_code', 'comp_name') ->where('active', 1) ->get(); foreach ($companies as $item) { $data['results'][] = [ 'id' => $item->id, 'text' => $item->comp_code.' - '.$item->comp_name ]; } return response()->json($data); } public function usersList(Request $request) { // dd($request->input('userID')); $data = []; // Check if a specific user ID is provided if ($request->has('userID')) { $user = \App\Models\User::select('id', DB::raw("CONCAT(f_name, ' ', l_name) as name")) ->where('id', $request->input('userID')) ->where('active', 1) ->first(); if ($user) { return response()->json([ 'results' => [ ['id' => $user->id, 'text' => $user->name] ] ]); } return response()->json(['error' => 'User not found'], 404); } // Otherwise, return the full list of users $users = \App\Models\User::select('id', DB::raw("CONCAT(f_name, ' ', l_name) as name")) ->where('active', 1) ->get(); foreach ($users as $user) { $data['results'][] = [ 'id' => $user->id, 'text' => $user->name ]; } return response()->json($data); } public function DepartmentList(Request $request) { // dd($request->input('userID')); $data = []; // Check if a specific user ID is provided if ($request->has('deptID')) { $dept = \App\Models\Department::select('id','dept_name') ->where('id', $request->input('userID')) // ->where('active', 1) ->first(); if ($dept) { return response()->json([ 'results' => [ ['id' => $dept->id, 'text' => $dept->dept_name] ] ]); } return response()->json(['error' => 'User not found'], 404); } // Otherwise, return the full list of users $dept = \App\Models\Department::select('id', 'dept_name') // ->where('active', 1) ->get(); foreach ($dept as $user) { $data['results'][] = [ 'id' => $user->id, 'text' => $user->dept_name ]; } return response()->json($data); } public function getUserDetails($id) { $user = \App\Models\User::find($id); if ($user) { return response()->json([ 'viber' => $user->viber, 'anydesk' => $user->anydesk ]); } return response()->json(['error' => 'User not found'], 404); } }

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