File manager

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

Back
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; use Illuminate\Support\Facades\Auth; use Illuminate\Support\Facades\DB; class Ticket extends Model { protected $table = ['ticket']; protected $fillable = ['subject', 'status', 'active']; use HasFactory; public function getMyTicketQuery(){ $id = Auth::user()->id; return "SELECT %s FROM `ticket` `t` LEFT OUTER JOIN `ticket_user` `tu` ON `tu`.`tix_id` = `t`.`id` LEFT OUTER JOIN `users` `sender` ON `tu`.`usr_id` = `sender`.`id` INNER JOIN `users` `creator` ON `creator`.`id` = `t`.`ticket_creator` LEFT JOIN `users` as `support` on `tu`.`assigned_to` = `support`.`id` WHERE 1 AND `t`.`ticket_creator` = $id %s %s %s %s "; } public function fetchMyTickets($array_data){ $fields = " `t`.`id`,`t`.`ticket_no`,`t`.`description`,`t`.`level`,`t`.`status`,`t`.`created_at`,`t`.`start_date`,`t`.`end_date`,`t`.`solution`, IF(t.start_date IS NULL,'',DATE_FORMAT(`t`.`start_date`,'%M %e, %Y %h:%i %p')) as start_date, IF(t.start_date IS NULL,'',DATE_FORMAT(`t`.`end_date`,'%M %e, %Y %h:%i %p')) as end_date, CONCAT(`sender`.`f_name`,' ',`sender`.`l_name`) as SENDER,`sender`.`email` as sender_email,`sender`.`position` as sender_position, CONCAT(`creator`.`f_name`,' ',`creator`.`l_name`) as ticket_creator, CONCAT(`support`.`f_name`,' ',`support`.`l_name`) as support, `support`.`position` as `support_position` "; $query = sprintf( $this->getMyTicketQuery(), $fields, $array_data['search'], $array_data['where'], $array_data['sort'], $array_data['offset_limit'], ); return DB::select($query); } public function getMyTicketsCount($array_data){ $fields = " COUNT(1) as Count "; $query = sprintf( $this->getMyTicketQuery(), $fields, '', $array_data['where'], '', '' ); return DB::select($query); } public function getMyTicketsFilteredCount($array_data){ $fields = " COUNT(1) as FilteredCount "; $query = sprintf( $this->getMyTicketQuery(), $fields, $array_data['search'], $array_data['where'], '', '' ); return DB::select($query); } public function fetchDepartmentTicketQuery(){ return "SELECT DISTINCT %s FROM `ticket` `t` LEFT OUTER JOIN `ticket_user` `tu` ON `tu`.`tix_id` = `t`.`id` LEFT OUTER JOIN `departments` `dt` ON `dt`.`id` = `tu`.`assigned_dept` LEFT JOIN `categories` `child` ON `child`.`id` = `t`.`subject` LEFT OUTER JOIN `categories` `parent` ON `child`.`parent_id` = `parent`.`id` LEFT OUTER JOIN `service_level_agreement` `sla` ON `child`.`sla_id` = `sla`.`id` INNER JOIN `users` `usr` ON `usr`.`id` = `t`.`ticket_creator` LEFT OUTER JOIN `users` `assignee` ON `tu`.`assigned_by` = `assignee`.`id` LEFT OUTER JOIN `users` `sender` ON `tu`.`usr_id` = `sender`.`id` LEFT OUTER JOIN `users` `creator` ON `t`.`ticket_creator` = `creator`.`id` LEFT JOIN `users` as `support` on `tu`.`assigned_to` = `support`.`id` WHERE `t`.`deleted_at` IS NULL %s %s %s %s "; } public function fetchDepartmentTickets($array_data){ $fields = " `t`.`id`, `t`.`ticket_no`, `t`.`description`, DATE_FORMAT(`t`.`created_at`,'%M %e, %Y %h:%i %p') as created_at, `t`.`employee_id`, `t`.`company_id`, IF(t.start_date IS NULL,'',DATE_FORMAT(`t`.`start_date`,'%M %e, %Y %h:%i %p')) as start_date, IF(t.end_date IS NULL,'',DATE_FORMAT(`t`.`end_date`,'%M %e, %Y %h:%i %p')) as end_date, `t`.`status`,`t`.`level`, `t`.`solution`,`t`.`type_of_support`, `child`.`title` as `child`,`parent`.`title` as `parent`, CONCAT(`sender`.`f_name`,' ',`sender`.`l_name`) as SENDER,`sender`.`email` as sender_email,`sender`.`position` as sender_position, `sender`.`viber` as sender_number, `usr`.`position`, `tu`.`ticket_condition`, `tu`.`usr_id`,`tu`.`assigned_dept`,`parent`.`id` as `category_id`, CONCAT(`creator`.`f_name`,' ',`creator`.`l_name`) as ticket_creator, CONCAT(`support`.`f_name`,' ',`support`.`l_name`) as support, `support`.`position` as `support_position`, `tu`.`stars`, CONCAT(`assignee`.`f_name`,' ',`assignee`.`l_name`) as `assignee` "; $query = sprintf( $this->fetchDepartmentTicketQuery(), $fields, $array_data['search'], $array_data['where'], $array_data['sort'], $array_data['offset_limit'] ); // return $query; return DB::select($query); } public function getMyDepartmentTicketsCount($array_data){ $fields = " COUNT(1) as Count "; $query = sprintf( $this->fetchDepartmentTicketQuery(), $fields, '', $array_data['where'], '', '' ); return DB::select($query); } public function getMyDepartmentTicketsFilteredCount($array_data){ $fields = " COUNT(1) as FilteredCount "; $query = sprintf( $this->fetchDepartmentTicketQuery(), $fields, $array_data['search'], $array_data['where'], '', '' ); return DB::select($query); } public function fetchAssignedTicketQuery(){ return "SELECT DISTINCT %s FROM `ticket` as `t` INNER JOIN `ticket_user` as `tu` ON `tu`.`tix_id` = `t`.`id` INNER JOIN `categories` as `sub` ON `t`.`subject` = `sub`.`id` LEFT OUTER JOIN `categories` as `ctg` ON `sub`.`parent_id` = `ctg`.`id` LEFT OUTER JOIN `service_level_agreement` as `sla` ON `sub`.`sla_id` = `sla`.`id` LEFT OUTER JOIN `users` as `support` ON `tu`.`assigned_to` = `support`.`id` LEFT OUTER JOIN `users` as `assignee` ON `tu`.`assigned_by` = `assignee`.`id` LEFT JOIN `users` as `sender` on `tu`.`usr_id` = `sender`.`id` LEFT JOIN users as usr ON `usr`.`id` = `t`.`ticket_creator` WHERE 1 %s %s %s %s "; } public function fetchAssignedTickets($array_data){ $fields = " `t`.`id` as `ticket_id`, `t`.`ticket_no`,`t`.`description`,`t`.`created_at`,IF(t.start_date IS NULL,'', DATE_FORMAT(`t`.`start_date`,'%M %e, %Y %h:%i %p')) as start_date,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`, CONCAT(`sla`.`content`,' ',`sla`.`type`) as `sla`,`tu`.`status` as `ticket_status`, CONCAT(`support`.`f_name`,' ',`support`.`l_name`) as `support`,`tu`.`stars`,`tu`.`ticket_condition`,`tu`.`remarks` as `remarks`, CONCAT(`sender`.`f_name`,' ',`sender`.`l_name`) as `sender_name`,`sender`.`email` as `sender_email`,`sender`.`position` as `sender_position`, CONCAT(`assignee`.`f_name`,' ',`assignee`.`l_name`) as `assignee`, CONCAT(`usr`.`f_name`,' ',`usr`.`l_name`) as `ticket_creator` "; $query = sprintf( $this->fetchAssignedTicketQuery(), $fields, $array_data['search'], $array_data['where'], $array_data['sort'], $array_data['offset_limit'] ); // return $query; return DB::select($query); } public function filtering(){ $fields = " `t`.`id` as `ticket_id`, `t`.`ticket_no`,`t`.`description`,`t`.`created_at`,IF(t.start_date IS NULL,'',DATE_FORMAT(`t`.`start_date`,'%M %e, %Y %h:%i %p')) as start_date,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`, CONCAT(`sla`.`content`,' ',`sla`.`type`) as `sla`,`tu`.`status` as `ticket_status`, CONCAT(`support`.`f_name`,' ',`support`.`l_name`) as `support`,`tu`.`stars`,`tu`.`ticket_condition`,`tu`.`remarks` as `remarks`,CONCAT(`assignee`.`f_name`,' ',`assignee`.`l_name`) as `assignee` "; $query = sprintf( $this->fetchAssignedTicketQuery(), $fields, $array_data['search'], $array_data['where'], $array_data['sort'], $array_data['offset_limit'] ); return DB::select($query); } public function fetchAssignedTicketCount($array_data){ $fields = " COUNT(1) as Count "; $query = sprintf( $this->fetchAssignedTicketQuery(), $fields, '', $array_data['where'], '', '' ); return DB::select($query); } public function fetchAssignedTicketFilteredCount($array_data){ $fields = " COUNT(1) as FilteredCount "; $query = sprintf( $this->fetchAssignedTicketQuery(), $fields, $array_data['search'], $array_data['where'], '', '' ); return DB::select($query); } public function getTicketsReportQuery(){ return "SELECT %s FROM ticket LEFT JOIN users as usr ON `usr`.`id` = `ticket`.`ticket_creator` INNER JOIN `ticket_user` as `tu` ON `tu`.`tix_id` = `ticket`.`id` LEFT OUTER JOIN `departments` `dt`ON `dt`.`id` = `tu`.`assigned_dept` LEFT OUTER JOIN `users` as `support` ON `tu`.`assigned_to` = `support`.`id` INNER JOIN `categories` `ctg` ON `ctg`.`id` = `ticket`.`subject` INNER JOIN `categories` as `sub` ON `ctg`.`id` = `sub`.`parent_id` INNER JOIN `service_level_agreement` `sla` ON `sla`.`id` = `ctg`.`sla_id` LEFT JOIN `companies` `c` ON `usr`.`comp_code` = `c`.`comp_code` LEFT JOIN `dealers` `d` ON `usr`.`deal_code` =`d`.`dealer_code` WHERE 1 %s "; } public function reports($array_data){ $fields = " ticket.ticket_no,CONCAT(`usr`.`f_name`,' ',REPLACE(`usr`.`l_name`,'Ã±','ñ')) as sender, `c`.`comp_name`,`d`.`dealer_name`, `ctg`.`title` as `category`, `sub`.`title` as `sub_category`, DATE_FORMAT(ticket.created_at,'%b. %d, %Y \| %r') as date_created, DATE_FORMAT(ticket.start_date,'%b. %d, %Y \| %r') as date_started, DATE_FORMAT(ticket.end_date,'%b. %d, %Y \| %r') as date_ended, `usr`.`position`, CONCAT(`sla`.`content`,' MINS.') as content, CONCAT(`support`.`f_name`,' ',REPLACE(`support`.`l_name`,'Ã±','ñ')) as `support`, CASE WHEN ticket.status = 'pending' THEN 'Pending' WHEN ticket.status = 'ongoing' THEN 'Ongoing' WHEN ticket.status = 'closed' THEN 'Closed' ELSE ticket.status END AS capitalized_status "; $query = sprintf( $this->getTicketsReportQuery(), $fields, $array_data['where'], ); // return $query; return DB::select($query); } public function countTicketMasterFile($where) { $res = DB::select("SELECT count(ticket.id) as total FROM ticket LEFT JOIN users as usr ON `usr`.`id` = `ticket`.`ticket_creator` INNER JOIN `ticket_user` as `tu` ON `tu`.`tix_id` = `ticket`.`id` LEFT OUTER JOIN `departments` `dt`ON `dt`.`id` = `tu`.`assigned_dept` LEFT OUTER JOIN `users` as `support` ON `tu`.`assigned_to` = `support`.`id` INNER JOIN `categories` `ctg` ON `ctg`.`id` = `ticket`.`subject` INNER JOIN `categories` as `sub` ON `ctg`.`id` = `sub`.`parent_id` INNER JOIN `service_level_agreement` `sla` ON `sla`.`id` = `ctg`.`sla_id` LEFT JOIN `companies` `c` ON `usr`.`comp_code` = `c`.`comp_code` LEFT JOIN `dealers` `d` ON `usr`.`deal_code` =`d`.`dealer_code` WHERE 1 ".$where); return $res; } public function pdf($results, $type){ $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.', 'Date Created', 'Date Started', 'Date Ended', 'Status'], 'table_body' => ['ticket_no','date_created','date_started','date_ended','capitalized_status' ] ]; return $reportData; } public function getMyTicketSummaryQuery(){ return "SELECT %s FROM `ticket` `tckt` INNER JOIN `categories` `ctg` ON `ctg`.`id` = `tckt`.`subject` INNER JOIN `service_level_agreement` `sla` ON `sla`.`id` = `ctg`.`sla_id` WHERE 1 %s "; } public function getMyTicketSummary($array_data){ $fields = " `tckt`.`id`,`tckt`.`ticket_no`,`ctg`.`title`,`sla`.`content` "; $query = sprintf( $this->getMyTicketSummaryQuery(), $fields, $array_data['where'], ); return DB::select($query); } public function MyTicketSummaryReportPdf($results){ $data = []; $grpData = new \stdClass(); $grpData->list = $results; $grpData->total = sizeOf($results); array_push($data, $grpData); $report_title = "My Ticket Summary Masterfile"; $reportData = [ 'data' => $data, 'webpage_title' => "Tickets Report", 'report_title' => $report_title, 'table_headers' => ['No.','Ticket No.', 'Category', 'SLA'], 'table_body' => ['ticket_no','title','content' ] ]; return $reportData; } public function fetchDepartmentTicketSummaryQuery(){ return "SELECT DISTINCT %s FROM `ticket` `t` LEFT OUTER JOIN `ticket_user` `tu` ON `tu`.`tix_id` = `t`.`id` LEFT OUTER JOIN `departments` `dt` ON `dt`.`id` = `tu`.`assigned_dept` LEFT OUTER JOIN `categories` `ctg` ON `ctg`.`id` = `t`.`subject` LEFT OUTER JOIN `service_level_agreement` `sla` ON `ctg`.`sla_id` = `sla`.`id` INNER JOIN `users` `usr` ON `usr`.`id` = `t`.`ticket_creator` WHERE 1 %s "; } public function fetchDepartmentTicketsSummary($array_data){ // dd($array_data); $fields = " `t`.`id`, `t`.`ticket_no`, `t`.`description`, DATE_FORMAT(`t`.`created_at`,'%M %e, %Y %h:%i %p') as created_at,`t`.`status`,`t`.`level`, `ctg`.`title`, CONCAT(`usr`.`f_name`,' ',`usr`.`l_name`) as sender, `usr`.`position`, CONCAT(`sla`.`content`,' MINS.') as content "; $query = sprintf( $this->fetchDepartmentTicketSummaryQuery(), $fields, $array_data['where'], ); return DB::select($query); } public function departmentTicketSummaryReportPdf($results){ $data = []; $grpData = new \stdClass(); $grpData->list = $results; $grpData->total = sizeOf($results); array_push($data, $grpData); $report_title = "Department Ticket Summary Masterfile"; $reportData = [ 'data' => $data, 'webpage_title' => "Department Tickets Report", 'report_title' => $report_title, 'table_headers' => ['No.','Ticket No.', 'Sender', 'Position', 'Category', 'Status', 'SLA'], 'table_body' => ['ticket_no','sender','position','title','status','content' ] ]; return $reportData; } public function getAssignedTicketSummaryQuery(){ // return "SELECT %s FROM `ticket` `tckt` // INNER JOIN `ticket_user` `tu` // ON `tu`.`tix_id` = `tckt`.`id` // INNER JOIN `categories` `ctg` // ON `ctg`.`id` = `tckt`.`subject` // INNER JOIN `service_level_agreement` `sla` // ON `sla`.`id` = `ctg`.`sla_id` // WHERE 1 // %s // "; return "SELECT %s FROM `ticket_user` as `tu` INNER JOIN `ticket` as `t` ON `tu`.`tix_id` = `t`.`id` LEFT OUTER JOIN `categories` as `ctg` ON `t`.`subject` = `ctg`.`id` LEFT OUTER JOIN `service_level_agreement` as `sla` ON `ctg`.`sla_id` = `sla`.`id` WHERE 1 %s "; } public function getAssignedTicketSummary($array_data){ $fields = " `t`.*,`ctg`.`title` as `category`, `sla`.`content` as `sla`,`tu`.`status` as `ticket_status` "; $query = sprintf( $this->getAssignedTicketSummaryQuery(), $fields, $array_data['where'], ); return DB::select($query); } public function getAssignedTicketSummaryPdf($results,$name){ $data = []; $grpData = new \stdClass(); $grpData->list = $results; $grpData->total = sizeOf($results); array_push($data, $grpData); $report_title = "Assigned Ticket Summary Report"; $reportData = [ 'data' => $data, 'webpage_title' => "Department Tickets Report", 'name' => $name, 'report_title' => $report_title, 'table_headers' => ['No.','Ticket No.','Category', 'SLA', 'Description', 'Date Created', 'Status'], 'table_body' => ['ticket_no','category','sla','description','created_at','status' ] ]; return $reportData; } }

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