File manager

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

Back
<?php namespace App\Http\Controllers; use App\Events\CreateTicket; use App\Events\AssignTicket; use App\Events\TransferTicket; use App\Models\Category; use App\Models\Department; use App\Models\Company; use App\Models\User; use App\Models\Ticket; use Illuminate\Http\Request; use Carbon\Carbon; use DateTime; // use Illuminate\Support\Facades\Auth; use Illuminate\Support\Facades\DB; class ApiController extends Controller { public function loginApi(Request $request){ $user = User::where('asa_id',$request->asa_id)->first(); // return response()json(['token'=>]) if($user){ Auth::login($user); $token = auth()->user()->createToken('ticketing')->accessToken; return response()->json(['id'=>$user->id, 'token'=>$token]); } else { return response()->json(['message'=>"invalid credentials"]); } } // function for API Login public function showAllTickets(Request $request){ //Need employee id and find it id in user tcketing $id = Auth::user()->department_id; $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') ->join('departments as dpt','tu.assigned_dept',"=",'dpt.id') ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') ->select('tk.id as ticket_id', 'tk.ticket_no as ticket_no', 'tu.usr_id', 'tk.file as FILE', 'tk.description as DESCRIPTION', 'tk.created_at as CREATED_AT', 'tu.start_date as DATE_STARTED', 'tu.end_date as DATE_ENDED', 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') ->where('tk.ticket_creator',$request->id) ->distinct('ticket_id', 'ticket_no', 'usr_id') ->get(); return response()->json($ticket); } public function dept_ticket(Request $request){ $employee = $user = User::where('asa_id',$request->employee_id)->first(); // $myTickets = DB::table('ticket as tk') // ->join('ticket_user as tu', 'tk.id', '=', 'tu.tix_id') // ->join('users as usrSender', 'tk.ticket_creator', '=', 'usrSender.id') // ->join('departments as dpt', 'tu.assigned_dept', '=', 'dpt.id') // ->leftJoin('users as userSupp', 'tu.assigned_to', '=', 'userSupp.id') // ->leftJoin('categories as ctg', 'tk.subject', '=', 'ctg.id') // ->leftJoin('service_level_agreement as sla', 'ctg.sla_id', '=', 'sla.id') // ->select( // 'tk.id as ticket_id', // 'tk.ticket_no', // 'tu.usr_id', // 'tk.file', // 'tk.description', // 'tk.created_at', // 'tu.start_date', // Make sure this column exists in the ticket_user table // 'tk.end_date', // 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', // 'tu.status as STATUS' // ) // ->where([['tu.assigned_dept', $employee->department_id]]) // ->where ('tk.status','pending'); // // if ($request->status !== 'all') { // // $myTickets->where('tk.status', $request->status); // // } // $myTickets = $myTickets->distinct('ticket_id', 'tk.ticket_no', 'tu.usr_id')->get(); // // $results = DB::getQueryLog(); // Log the query for debugging // // dd($results); // Dump the logged queries to check for issues // $totalCount = count($myTickets); // return response()->json(['department_tickets' => $myTickets, 'total' => $totalCount], 200); $myTickets = DB::table('ticket as tk') ->join('ticket_user as tu', 'tk.id', '=', 'tu.tix_id') ->join('users as usrSender', 'tk.ticket_creator', '=', 'usrSender.id') ->join('departments as dpt', 'tu.assigned_dept', '=', 'dpt.id') ->leftJoin('users as userSupp', 'tu.assigned_to', '=', 'userSupp.id') ->leftJoin('categories as ctg', 'tk.subject', '=', 'ctg.id') ->leftJoin('service_level_agreement as sla', 'ctg.sla_id', '=', 'sla.id') ->select( 'tk.id as ticket_id', 'tk.ticket_no', 'tu.usr_id', 'tk.file', 'tk.description', 'tk.created_at', 'tu.start_date', // Ensure this column exists in the ticket_user table 'tk.end_date', 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', 'tu.status as STATUS' ) ->where('tu.assigned_dept', $employee->department_id) ->where('tk.status', 'pending') ->distinct('tk.id', 'tk.ticket_no', 'tu.usr_id') ->orderBy('tk.created_at','desc'); // ->limit(10); $myTickets = $myTickets->get(); // Apply pagination, limit to 10 items per page // $myTickets = $myTickets->paginate(10); $totalCount = count($myTickets); return response()->json(['department_tickets' => $myTickets,'total'=> $totalCount], 200); } public function showTicketInfo(Request $request){ // $ticket = []; // $ticket['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') // ->join('users as support','tu.assigned_to','=','support.id') // ->where('tk.ticket_no', $request->ticket_no) // ->select( // 'tk.id', // 'tk.ticket_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(support.f_name, " ", support.l_name) as SUPPORT'), // 'ctg.title as CATEGORY', // 'sla.content as SLA', // 'dpt.DEPT_NAME as DEPARTMENT', // 'tk.status as STATUS' // ) // ->first(); $ticket = []; $ticket['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 support', 'tu.assigned_to', '=', 'support.id') // Use leftJoin to handle missing support ->leftJoin('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.ticket_no', $request->ticket_no) ->select( 'tk.id', 'tk.ticket_no', 'tk.file as FILE', 'tk.description as DESCRIPTION', 'tk.created_at as CREATED_AT', 'usrSender.asa_id as SENDER_EMPLOYEE_ID', DB::raw('CONCAT(usrSender.f_name, " ", usrSender.l_name) as SENDER'), 'support.asa_id as SUPPORT_EMPLOYEE_ID', DB::raw('CONCAT(support.f_name, " ", support.l_name) as SUPPORT'), 'support.position as POSITION', 'ctg.title as CATEGORY', 'dpt.DEPT_NAME as DEPARTMENT', 'sla.content as SLA', 'tk.status as STATUS' ) ->first(); if (is_null($ticket['ticket'])) { return response()->json(['message' => "No data found for ticket number: " . $request->ticket_no, "status" => 0], 404); } else { return response()->json($ticket); } // $ticket['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')) // ->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',"=",$request->id) // ->get(); } private function generateRandStringToken(){ $characters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!_-[]/'; $randomString = substr(str_shuffle($characters), 0, 4); return $randomString; } public function TicketStore(Request $request) { // dd($request->all()); $user = User::where('asa_id',$request->employee_id)->first(); $creator_user = User::where('asa_id',$request->employee_id)->first(); if (!$user) { return response()->json(['error' => 'Sender Information not found'], 404); } if($user) { try{ $path = null; $subject =246;//N/A //$request->category; if($request->attachment){ $strToken = $this->generateRandStringToken(); $file['image'] = $strToken.'.'.$request->attachment->getClientOriginalExtension(); $request->attachment->move('files/tickets/', $file['image']); $path = 'files/tickets/'.$file['image']; } else { $path = null; } DB::beginTransaction(); $dealer_code=$user->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']; $ticket = DB::table('ticket')->insertGetId([ 'ticket_no' => $ticketNumberRef,//$user->deal_code."-".substr(uniqid(),-4), 'subject' => $subject, 'file' => $path, 'description' => $request->description, 'status' => "pending", 'active' => 1, 'level' => 'mid', 'ticket_creator' => $creator_user->id, 'employee_id' => $request->employee_id, 'company_id' => $request->company_id, ]); if($ticket){ DB::table('ticket_user') ->insert([ 'tix_id' => $ticket, 'usr_id' => $user->id, 'assigned_dept' => $request->department_id, 'status' => "pending" ]); } // $data = Ticket::where(['id' =>$ticket])->first(); $data = DB::table('ticket')->where('id',$ticket)->get(); event(new CreateTicket($request->id)); // return response()->json(['message'=>"Ticket successfully created!","status"=>1],201);//,'ticket'=>$ticket],201); DB::commit(); return response()->json([ 'status' => 1, 'message' => 'Ticket successfully created!.', 'data' => $data, ],201); } catch (Throwable $e) { DB::connection()->rollback(); return response()->json([ 'status' => 0, 'message' => 'Unable to process request. Please try again.', 'data' => $e->getMessage() ]); } } // return response()json(['token'=>]) // if($user){ // $request->validate([ // 'department' => 'required', // 'category' => 'required', // 'description' => 'required', // 'attachment' => 'file\|mimes:jpeg,png,gif\|max:2048' // ],[ // 'department.required' => "Please choose a department", // 'category.required' => "Please provide a category", // 'description.required' => "Please provide a note", // ]); // return $request->all(); } private function validateFile($file) { return DB::table('ticket')->where('ticket_no', $ticketNumber)->exists(); } // public function getDepartments(Request $request){ // $data = []; // $term = $request->search; // $response = Department::where(function($query) use ($term){ // $query->orWhere('dept_name', 'like', '%'.$term.'%'); // })->orderBy('dept_name','asc') // ->get(); // foreach($response as $item){ // $data['results'][] = [ // 'id' => $item->id, // 'text' => $item->dept_name // ]; // } // return response()->json($data); // } public function getCategoryPerDeptId(Request $request){ $result = Category::select('categories.id','categories.title') ->join('departments','departments.id',"=",'categories.dept_id') ->where('categories.dept_id',"=",$request->id) ->where('categories.status',1) ->get(); return response()->json($result); } // public function getSLAperCategory(Request $request){ // $sla = DB::table('service_level_agreement') // ->join('categories','categories.sla_id',"=",'service_level_agreement.id') // ->where('categories.id',"=",$request->id) // ->get()->first(); // return response()->json($sla); // } public function getMyTickets(Request $request){ $user = User::where('asa_id',$request->employee_id)->first(); // dd($user); if($user) { // DB::enableQueryLog(); // $myTickets = DB::table('ticket as tk') // ->join('ticket_user as tu','tk.id',"=",'tu.tix_id') // ->join('users as usrSender','tk.ticket_creator',"=",'usrSender.id') // ->join('departments as dpt','tu.assigned_dept',"=",'dpt.id') // ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') // ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') // ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') // ->select('tk.id as ticket_id', // 'tk.ticket_no', // 'tu.usr_id', // 'tk.file', // 'tk.description', // 'tk.created_at', // 'tu.start_date ', // 'tk.end_date', // 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', // 'tu.status as STATUS') // ->where([['tk.ticket_creator',$user->id]]); // // ->distinct('ticket_id', 'tk.ticket_no', 'tu.usr_id'); // if ($request->status !== 'all') { // $myTickets->where('tk.status', $request->status); // } // $myTickets = $myTickets->distinct('ticket_id', 'tk.ticket_no', 'tu.usr_id')->get(); // $results = DB::getQueryLog(); // // dd($results); // $totalCount = count($myTickets); // return response()->json(['myTickets'=>$myTickets,'total'=>$totalCount],200); DB::enableQueryLog(); // Enable query logging $myTickets = DB::table('ticket as tk') ->join('ticket_user as tu', 'tk.id', '=', 'tu.tix_id') ->join('users as usrSender', 'tk.ticket_creator', '=', 'usrSender.id') ->join('departments as dpt', 'tu.assigned_dept', '=', 'dpt.id') ->leftJoin('users as userSupp', 'tu.assigned_to', '=', 'userSupp.id') ->leftJoin('categories as ctg', 'tk.subject', '=', 'ctg.id') ->leftJoin('service_level_agreement as sla', 'ctg.sla_id', '=', 'sla.id') ->select( 'tk.id as ticket_id', 'tk.ticket_no', 'tu.usr_id', 'tk.file', 'tk.description', 'tk.created_at', 'tu.start_date', // Make sure this column exists in the ticket_user table 'tk.end_date', 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.id as dept_id', 'dpt.DEPT_NAME as DEPARTMENT', 'tu.status as STATUS' ) ->where([['tk.ticket_creator', $user->id]]) ->orWhere('tk.employee_id', $request->employee_id); if ($request->status !== 'all') { $myTickets->where('tk.status', $request->status); } // $dept_id = $myTickets->distinct('dpt.id as dept_id')->get(); $myTickets = $myTickets->distinct('ticket_id', 'tk.ticket_no', 'tu.usr_id')->get(); $results = DB::getQueryLog(); // Log the query for debugging // dd($results); // Dump the logged queries to check for issues $totalCount = count($myTickets); return response()->json(['myTickets' => $myTickets, 'total' => $totalCount,'user_dept_id'=>$user->department_id], 200); } else { return response()->json(['status'=>0,'message'=>"No data found.",'data'=>[]],204); } } public function getMyAssignedTickets(Request $request){ $user = User::where('asa_id',$request->employee_id)->first(); // return response()->json($user); $getMyAssignedTickets = DB::table('ticket as tk') ->join('ticket_user as tu','tk.id',"=",'tu.tix_id') ->join('users as usrSender','tk.ticket_creator',"=",'usrSender.id') ->join('departments as dpt','tu.assigned_dept',"=",'dpt.id') ->leftJoin('users as userSupp','tu.assigned_to',"=",'userSupp.id') ->leftJoin('categories as ctg','tk.subject',"=",'ctg.id') ->leftJoin('service_level_agreement as sla','ctg.sla_id',"=",'sla.id') ->select('tk.id as ticket_id', 'tk.ticket_no as ticket_no', 'tu.usr_id', 'tk.file as FILE', 'tk.description as DESCRIPTION', 'tk.created_at as CREATED_AT', 'tu.start_date as DATE_STARTED', 'tu.end_date as DATE_ENDED', DB::raw('CONCAT(usrSender.f_name," ",usrSender.l_name) as SENDER'), 'tk.level AS LEVEL', DB::raw('CONCAT(userSupp.f_name," ",userSupp.l_name) as SUPPORT'), 'ctg.title as CATEGORY', 'sla.content as SLA', 'dpt.DEPT_NAME as DEPARTMENT', 'tu.status as STATUS', 'tu.stars as STARS') ->where([['tu.assigned_to',$user->id]]); // ->distinct('TIX_ID', 'TIX_NO', 'usr_id') // ->get(); if ($request->status !== 'all') { $getMyAssignedTickets->where('tk.status', $request->status); } $getMyAssignedTickets = $getMyAssignedTickets->distinct('ticket_id', 'ticket_no', 'usr_id')->get(); $totalCount = count($getMyAssignedTickets); if(!$getMyAssignedTickets){ return response()->json(['status' =>0,'data' => [], 'total' => 0], 404); } return response()->json(['status'=>1,'data'=>$getMyAssignedTickets,'total'=>$totalCount],200); } public function getCompanies(Request $request) { $dept_list = Company::select('id','comp_code','comp_name')->where('active',1)->get(); $response =[]; foreach($dept_list as $row) { $response[]= $row; } return response()->json(['companies'=>$response],200); } public function getDepartments(Request $request) { $dept_list = Department::select('id','dept_name')->get(); $response =[]; foreach($dept_list as $row) { $response[]= $row; } return response()->json(['departments'=>$response],200); } public function getCategories(Request $request) { $id = $request->department_id; $data = []; $parentCat = Category::where([['dept_id',$id],['parent_id',null],['status',1]]) ->orderBy('title',"asc")->get(); if($parentCat) { foreach($parentCat as $item){ $data[] = [ 'id' => $item->id, 'text' => $item->title, ]; } return response()->json(['data'=> $data]); } else{ return response()->json(['message'=>"No data found.",'status'=>0],204); } } public function subCategory(Request $request){ $sub = Category::select('id','title','sla_id','dept_id','parent_id')->where('parent_id',$request->category_id)->orderBy('title', 'asc')->get(); if($sub->isEmpty()) { return response()->json(['message'=>"No data found.",'status'=>0]); } return response()->json(['data'=> $sub,'status'=>1]); } public function getSlaPerCategory(Request $request) { $id = $request->sub_category_id; //category id $sla = DB::table('service_level_agreement') // ->select('id','content','type','title',) ->join('categories','categories.sla_id',"=",'service_level_agreement.id') ->where('categories.id',"=",$id) ->get()->first(); $data=[]; if($sla) { $data[] =[ 'id' => $sla->id, 'title' => $sla->title, 'sla_time' => $sla->content, 'sla_unit' => $sla->type ]; return response()->json($data); } else{ return response()->json(['message'=>"No data found.",'status'=>0]); } } public function CloseTicket(Request $request) { $ticket_no = $request->ticket_no; // User::where('id',$sender)->select('email')->first(); // $Record = DB::table('ticket_user') // ->where('tix_id', $ticket_id) // ->latest('tix_id') // Assuming 'id' is the primary key column // ->first(); $Record = DB::table('ticket') ->leftJoin('ticket_user', 'ticket.id', '=', 'ticket_user.tix_id') ->where('ticket.ticket_no', $ticket_no) ->latest('ticket.id') ->select('ticket.ticket_no','ticket.id as ticket_id', 'ticket_user.') ->first(); if($Record->status =='pending'){ return response()->json(['status' => 0,'message'=> "Failed. Ticket should be pick before closing it.",'data'=>$Record],203); } if($Record->status =='closed'){ return response()->json(['status' => 0,'message'=> 'Failed. Ticket Already closed.','data'=>$Record]); } // return response()->json($Record);exit; DB::connection()->beginTransaction(); if($Record->start_date!=NULL) { if ($Record) { // Update the last record $startTime = Carbon::parse($Record->start_date); $endTime = Carbon::now(); // $elapsedTime = $endTime->diffInSeconds($startTime); $elapsedTime = $endTime->diffInMinutes($startTime); $res = DB::table('ticket_user') ->where('tix_id', $Record->ticket_id) ->update([ 'status' => 'closed', 'updated_at' => now(), 'end_date' => now(), 'time_elapsed' => $elapsedTime ]); } $tix = DB::table('ticket') ->where('id', $Record->ticket_id) ->first(); $startDateTime = Carbon::parse($tix->start_date); $endDateTime = Carbon::parse($tix->end_date); $timeDiff = $endDateTime->diffInMinutes($startDateTime); $ticket = DB::table('ticket') ->where('id', $Record->ticket_id) ->update([ 'updated_at'=>NOW(), 'end_date'=>NOW(), 'status'=>"closed", 'time_elapsed' => $timeDiff ]); DB::connection()->commit(); $sender = $tix->ticket_creator; $tix_no = DB::table('ticket')->find( $Record->ticket_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', $Record->ticket_id) ->first(); DB::connection()->rollback(); $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(); $emailResponse = mail('jdelapaz@autohubgroup.com', $subject, $message, $headers); if($emailResponse) { return response()->json(['stastus'=>1,'message'=>'Ticket successfully closed','data' => $ticket_details],201); } else { return response()->json(['message'=>"Failed to close ticket.",'status'=>0],304); } } else { return response()->json(['message' => "Failed to close ticket. Ticket must be picked and process before closing it",'status'=>0], 400); } } public function pickTicketBySupport(Request $request) { $user = User::where('asa_id', $request->employee_id)->first(); if (!$user) { return response()->json(['message' => 'Support employee id is required', 'status' => 0]); } $ticket = DB::table('ticket_user') ->leftJoin('ticket', 'ticket.id', '=', 'ticket_user.tix_id') ->where('ticket.ticket_no', $request->ticket_no) ->latest('ticket_user.id') ->select('ticket_user.id', 'ticket.subject') ->first(); if (!$ticket) { return response()->json(['message' => 'No record found.', 'status' => 0], 404); } if ($ticket->subject == 246 \|\| $ticket->subject == '') { return response()->json(['message' => 'Please update ticket category, sub, and SLA.', 'status' => 0]); } // Update the ticket status and assigned user DB::table('ticket_user') ->where('tix_id', $ticket->id) ->latest('id') ->update([ 'status' => 'ongoing', 'ticket_condition' => 'picked', 'assigned_to' => $user->id, 'assigned_employee' => $request->employee_id, 'start_date' => now() ]); DB::table('ticket') ->where('ticket_no', $request->ticket_no) ->update([ 'status' => 'ongoing', 'start_date' => now() ]); // Retrieve the updated ticket details $updatedTicket = DB::table('ticket') ->where('ticket_no', $request->ticket_no) ->first(); return response()->json(['status'=>1,'message' => 'Ticket successfully picked.', 'ticket' => $updatedTicket],201); } public function transferTicket(Request $request){ $supportuser = User::where('asa_id',$request->transfer_to)->select('id')->first(); $support_user_id = $supportuser->id; $ticket_user_Info = DB::table('ticket_user') ->leftJoin('ticket','ticket_user.tix_id','=','ticket.id') ->where('ticket.ticket_no', $request->ticket_no) ->latest('tix_id') ->select('ticket.id as ticket_id','ticket.ticket_no','ticket_user.') ->first(); if(! $ticket_user_Info) { return response()->json(["message"=>"No record found.","status"=>0],404); } if($ticket_user_Info->usr_id == $support_user_id) { return response()->json(["message"=>"Ticket cannot be transfer to sender.","status"=>0],304); } $ticket_user_id = $ticket_user_Info->id; $startTime = Carbon::parse($ticket_user_Info->start_date); $endTime = Carbon::now(); $elapsedTime = $endTime->diffInMinutes($startTime); try{ DB::connection()->beginTransaction(); $res = DB::table('ticket_user') ->where('id', $ticket_user_id) ->update([ 'status' => "transferred", 'ticket_condition' => "transferred", 'updated_at' => NOW(), 'end_date' => NOW() ]); $newRecord = DB::table('ticket_user') ->insert([ 'tix_id' => $ticket_user_id, 'usr_id' => $ticket_user_Info->usr_id, 'assigned_to' => $support_user_id, 'assigned_employee' => $supportuser->asa_id, 'assigned_dept' => $ticket_user_Info->assigned_dept, 'status' => "pending", 'ticket_condition' => null, 'remarks' => null, 'created_at' => NOW(), 'updated_at' => NOW(), ]); DB::connection()->commit(); $support_name = $supportuser->f_name . " " . $supportuser->l_name; $tix = DB::table('ticket') ->where('id',$ticket_user_id) ->first(); // event(new AssignTicket($ticket_user_id,$support_user_id)); // event(new TransferTicket($ticket_user_id,$support_user_id,$tix->ticket_creator,$support_name)); return response()->json(["status"=>1, "message"=> "Ticket Transferred Successfully.","data"=> $newRecord]); } catch (\Exception $e) { DB::connection()->rollback(); return response()->json(["message"=>"Failed to transfer ticket. ".$e,"status"=>0]); } } public function TransferTicketDepartment(Request $request) { // return response()->json($request->all()); $ticketInfo = DB::table('ticket') ->where('ticket.ticket_no', $request->ticket_no) ->select('ticket.id') ->first(); if(!$ticketInfo) { return response()->json(["status"=>0,"message"=>"No record found.",],404); } $ticket_id = $ticketInfo->id; $ticket = DB::table('ticket_user') ->where('tix_id', $ticket_id) ->orderBy('id', 'desc') ->first(); if ($ticket) { DB::table('ticket_user') ->where('id', $ticket->id) ->update(['assigned_dept' => $request->department_id]); } return response()->json(['status'=>1,'message'=>"Ticket Transferred Successfully",'data'=>$ticket],200); } public function updateTicketCategorY(Request $request) { // dd($request->all()); try { $request->validate([ 'support_employee_id' => 'required', 'ticket_id' => 'required\|numeric', 'sub_category_id' => 'required\|numeric', ], [ 'support_employee_id.required' => "Please provide support employee ID.", 'ticket_id.required' => "Please provide ticket ID.", 'ticket_id.numeric' => "Ticket ID must be a numeric value.", 'sub_category_id.required' => "Please provide sub-category ID.", 'sub_category_id.numeric' => "Sub-category ID must be a numeric value.", ]); DB::beginTransaction(); $data = DB::table('ticket')->where('id', $request->ticket_id)->first(); if (!$data) { return response()->json(['message' => "No data found.", 'status' => 0]); } $update_data = [ 'subject' => $request->sub_category_id, 'status' => "ongoing", 'start_date' => NOW() ]; DB::table('ticket') ->where('id', $request->ticket_id) ->update($update_data); $data_ticket_user = DB::table('ticket_user')->where('tix_id', $request->ticket_id)->first(); if (!$data_ticket_user) { DB::rollback(); return response()->json(['message' => "No data found in ticket_user table for the given ticket ID.", 'status' => 0]); } $ticketUser = DB::table('ticket_user')->where('tix_id', $request->ticket_id)->first(); if ($ticketUser) { // If record exists, update it $support_ = User::where('asa_id',$request->support_employee_id)->first(); $updatedRows = DB::table('ticket_user') ->where('tix_id', $request->ticket_id) ->update([ 'assigned_to' => $support_->id, 'assigned_employee' => $request->support_employee_id, 'status' => "ongoing", 'ticket_condition' => "picked", 'start_date' => now() ]); } else { DB::rollback(); return response()->json(['message' => "No data found in ticket_user table for the given ticket ID.", 'status' => 0]); } if ($updatedRows === 0) { DB::rollback(); return response()->json(['message' => "Failed to update the ticket_user record.", 'status' => 0]); } DB::commit(); return response()->json(['message' => "Ticket category updated successfully.", 'status' => 1]); } catch (\Illuminate\Validation\ValidationException $e) { DB::rollback(); return response()->json([ 'message' => $e->errors(), 'status' => 0 ]); } catch (\Exception $e) { DB::rollback(); return response()->json([ 'message' => "An error occurred while updating the ticket.".$e, 'status' => 0 ]); } } }

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