File manager

File manager - Edit - /home/autoph/public_html/tasks/app/classes/ticket_class.php

Back
<?php /** * use CRUD for class naming * example: create_ and read_and update_ and delete_ / class Ticket{ function create_ticket($user_id,$sub_category_id,$priority_id,$title,$description,$status,$utility_class,$db){ $query = "INSERT INTO `tickets`(`user_id`, `sub_category_id`, `priority_level_id`, `title`, `description`, `date_added`, `status`) VALUES (NULLIF('$user_id',''),'$sub_category_id','$priority_id','$title','$description',NOW(),'$status')"; // echo $query;exit; return $db->sql_query_id($query); } function update_ticket($ticket_id,$user_id,$sub_category_id,$priority_id,$title,$description,$utility_class,$db){ $query = "UPDATE `tickets` SET `sub_category_id`=$sub_category_id,`priority_level_id`=$priority_id,`title`='$title',`description`='$description' WHERE 1 AND id = $ticket_id"; return $db->sql_query_id($query); } function create_reply_ticket($user_id,$ticket_id,$description,$status,$final_status,$utility_class,$db){ $query = "INSERT INTO `ticket_replies`(`user_id`, `ticket_id`, `description`, `ticket_status_id`, `ticket_final_status_id`,`date_added`, `status`) VALUES (NULLIF('$user_id',''),$ticket_id,NULLIF('$description',''),$status,NULLIF('$final_status',''),NOW(),1)"; return $db->sql_query_id($query); } function create_ticket_attachment($ticket_id,$attachment_change_name,$attachment_orig_name,$utility_class,$db){ $query = "INSERT INTO `ticket_attachments`(`ticket_id`, `attachment_name`, `original_name`, `date_added`, `status`) VALUES ($ticket_id,'$attachment_change_name','$attachment_orig_name',NOW(),1)"; return $db->sql_query_id($query); } function update_ticket_attachment_status($ids,$status,$utility_class,$db){ $query = "UPDATE `ticket_attachments` SET status = $status WHERE 1 AND `id` IN ($ids)"; return $db->sql_query_id($query); } function update_all_ticket_attachment_status($ticket_id,$status,$utility_class,$db){ $query = "UPDATE `ticket_attachments` SET status = $status WHERE 1 AND `ticket_id` = $ticket_id"; return $db->sql_query_id($query); } function remove_all_ticket_attachment_status($ticket_id,$utility_class,$db){ $query = "DELETE FROM `ticket_attachments` WHERE 1 AND ticket_id = $ticket_id"; return $db->sql_query_id($query); } function create_reply_ticket_attachment($ticket_reply_id,$attachment_change_name,$attachment_orig_name,$utility_class,$db){ $query = "INSERT INTO `ticket_reply_attachments`(`ticket_reply_id`, `attachment_name`, `original_name`, `status`) VALUES ($ticket_reply_id,'$attachment_change_name','$attachment_orig_name',1)"; // echo $query;exit; return $db->sql_query_id($query); } function create_vehicle_type_ticket($ticket_id,$model_id,$model_variant_id,$plate_cs,$vin,$year_model,$utility_class,$db){ $query = "INSERT INTO `ticket_type_vehicles`( `ticket_id`, `model_id`, `plate_cs`, `vin`, `year_model`, `model_variant_id`,`status`) VALUES ('$ticket_id','$model_id',NULLIF('$plate_cs',''),NULLIF('$vin',''),NULLIF('$year_model',''),NULLIF('$model_variant_id',''),1)"; return $db->sql_query_id($query); } function create_vehicle_type_ticket_v2($ticket_id,$model_id,$model_variant_id,$plate_cs,$vin,$year_model,$mileage,$breakdown,$on_site,$workshop_visit,$status,$utility_class,$db){ $query = "INSERT INTO `ticket_type_vehicles`( `ticket_id`, `model_id`, `plate_cs`, `vin`, `year_model`, `model_variant_id`,`mileage`,`breakdown`,`on_site`,`workshop_visit_count`,`status`) VALUES ('$ticket_id','$model_id',NULLIF('$plate_cs',''),NULLIF('$vin',''),NULLIF('$year_model',''),NULLIF('$model_variant_id',''),NULLIF('$mileage',''),NULLIF('$breakdown',''),NULLIF('$on_site',''),NULLIF('$workshop_visit',''),'$status')"; // echo $query; return $db->sql_query_id($query); } function update_vehicle_type_ticket($ticket_id,$model_id,$model_variant_id,$plate_cs,$vin,$year_model,$status,$utility_class,$db){ $query = "UPDATE `ticket_type_vehicles` SET `model_id`=$model_id,`plate_cs`=NULLIF('$plate_cs',''),`vin`=NULLIF('$vin',''),`year_model`=NULLIF('$year_model',''),`model_variant_id`=NULLIF('$model_variant_id',''),status = '$status' WHERE 1 AND ticket_id = $ticket_id"; return $db->sql_query($query); } function update_vehicle_type_ticket_v2($ticket_id,$model_id,$model_variant_id,$plate_cs,$vin,$year_model,$mileage,$breakdown,$on_site,$workshop_visit,$status,$utility_class,$db){ $query = "UPDATE `ticket_type_vehicles` SET `model_id`=$model_id,`plate_cs`=NULLIF('$plate_cs',''),`vin`=NULLIF('$vin',''),`year_model`=NULLIF('$year_model',''),`model_variant_id`=NULLIF('$model_variant_id',''),`mileage`=NULLIF('$mileage',''),`breakdown`=NULLIF('$breakdown',''),`on_site`=NULLIF('$on_site',''),`workshop_visit_count`=NULLIF('$workshop_visit',''),status = '$status' WHERE 1 AND ticket_id = $ticket_id"; return $db->sql_query($query); } function update_ticket_customer($ticket_id,$complaint,$work_performed,$fault_codes,$utility_class,$db){ $query = "UPDATE `tickets` SET `complaint`=NULLIF('$complaint',''),`work_performed`=NULLIF('$work_performed',''),`fault_codes`=NULLIF('$fault_codes','') WHERE 1 AND id = $ticket_id"; return $db->sql_query($query); } function update_vehicle_type_ticket_status($ticket_id,$status,$utility_class,$db){ $query = "UPDATE `ticket_type_vehicles` SET status = $status WHERE 1 AND ticket_id = $ticket_id"; return $db->sql_query($query); } function read_vehicle_type_ticket($ticket_id,$utility_class,$db){ $query = "SELECT FROM `ticket_type_vehicles` WHERE 1 AND ticket_id = $ticket_id"; return $db->sql_query($query); } function read_sub_categories($search,$utility_class,$db){ $query = "SELECT `id`, `name` FROM `sub_categories` WHERE name LIKE '%$search%' AND status = 1 ORDER BY position ASC;"; return $db->sql_query($query); } function read_sections($search,$utility_class,$db){ $query = "SELECT `id`, `name` FROM `sections` WHERE name LIKE '%$search%' AND status = 1 ORDER BY name ASC;"; return $db->sql_query($query); } function read_final_status($search,$utility_class,$db){ $query = "SELECT `id`, `name` FROM `ticket_final_status` WHERE name LIKE '%$search%' AND status = 1 ORDER BY id,name ASC;"; return $db->sql_query($query); } function read_priority_level($search,$utility_class,$db){ $query = "SELECT `id`, `name` FROM `priority_levels` WHERE name LIKE '%$search%' AND status = 1 ORDER BY level ASC;"; return $db->sql_query($query); } function read_ticket_status($search,$utility_class,$db){ $query = "SELECT `id`, `name` FROM `ticket_status` WHERE name LIKE '%$search%' AND status = 1 ORDER BY level ASC;"; return $db->sql_query($query); } function read_ticket_available_status($exclude_ids,$utility_class,$db){ $query = "SELECT `id`, `name` FROM `ticket_status` WHERE 1 AND id NOT IN ($exclude_ids) AND status = 1 ORDER BY level ASC;"; return $db->sql_query($query); } function read_sub_category_id_by_name($name,$utility_class,$db){ $query = "SELECT `id` FROM `sub_categories` WHERE name = '$name' AND status = 1 LIMIT 1;"; return $db->select($query); } function read_section_id_by_name($name,$utility_class,$db){ $query = "SELECT `id` FROM `sections` WHERE name = '$name' AND status = 1 LIMIT 1;"; return $db->select($query); } function read_category_id_by_sub_category_id($sub_category_id,$utility_class,$db){ $query = "SELECT sc.`category_id`,c.show_vehicle FROM `sub_categories` sc INNER JOIN categories c ON sc.category_id = c.id WHERE sc.id = '$sub_category_id' AND sc.status = 1 LIMIT 1;"; // echo $query;exit; // return $db->select($query); return $db->sql_query($query); } function read_priority_level_id_by_name($name,$utility_class,$db){ $query = "SELECT `id` FROM `priority_levels` WHERE name = '$name' AND status = 1 LIMIT 1;"; return $db->select($query); } function read_sub_category_count($db){ $ticket_query = $this->universal_ticket_query(); // START TICKET SECTION $ticket_fields = " sc.id, sc.name, COUNT(sc.id) as sub_category_count "; $ticket_counts = " COUNT(1) "; $ticket_query = $ticket_query." AND tfs.id IS NOT NULL /remove not close ticket/ AND t.status = 1 AND sc.status = 1 AND c.status = 1 GROUP BY sc.id ORDER BY sc.name ASC "; $ticket_query_formatted = sprintf( $ticket_query, $ticket_fields, ); return $db->sql_query($ticket_query_formatted); } function universal_ticket_query(){ return " SELECT %s FROM `tickets` t LEFT OUTER JOIN sub_categories sc ON t.sub_category_id = sc.id LEFT OUTER JOIN categories c ON sc.category_id = c.id /LEFT OUTER JOIN ticket_type_vehicles ttv ON t.id = ttv.ticket_id/ LEFT OUTER JOIN ticket_type_vehicles ttv ON ttv.id = (SELECT ttvs.id FROM ticket_type_vehicles ttvs WHERE ttvs.ticket_id = t.id /AND ttvs.status = 1/) LEFT OUTER JOIN vehicle_models vm ON ttv.model_id = vm.id LEFT OUTER JOIN vehicle_brands vb ON vm.brand_id = vb.id LEFT OUTER JOIN vehicle_model_variants vmv ON ttv.model_variant_id = vmv.id LEFT OUTER JOIN ticket_progress_status tps ON tps.id = ( SELECT MAX(trs.id) AS maxsign FROM ticket_progress_status trs WHERE trs.ticket_id = t.id GROUP BY trs.ticket_id ) LEFT OUTER JOIN priority_levels pl ON t.priority_level_id = pl.id LEFT OUTER JOIN ticket_status ts ON tps.ticket_status_id = ts.id LEFT OUTER JOIN users u ON t.user_id = u.id LEFT OUTER JOIN user_contacts uc ON u.id = uc.user_id LEFT OUTER JOIN ticket_ratings trg ON trg.id = ( SELECT MAX(trgs.id) AS maxsign FROM ticket_ratings trgs WHERE trgs.ticket_id = t.id ) LEFT OUTER JOIN ticket_expert_reminder ter ON ter.id = ( SELECT MAX(ters.id) AS maxsign FROM ticket_expert_reminder ters WHERE ters.ticket_id = t.id ) WHERE 1 "; } function read_ticket_list($source,$user_id, $owned_ticket_only, $category_ids_str, $status_ids_str, // $status_ids_str_null, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $brand_id, $model_id, $brand_access_arr, $search, $offset, $limit, $utility_class,$db){ $category_ids_str = $utility_class->isNotEmpty($category_ids_str) ? " AND t.sub_category_id IN ($category_ids_str) " : '' ; $status_ids_str = $utility_class->isNotEmpty($status_ids_str) ? " AND ts.id IN ($status_ids_str) " : '' ; // $final_status_ids_str = $utility_class->isNotEmpty($final_status_ids_str) ? " AND tfs.id IN ($final_status_ids_str) " : '' ; // $filter_escalated_status = $utility_class->isNotEmpty($filter_escalated_status) ? " AND tes.type IN ($filter_escalated_status) " : '' ; $priority_ids_str = $utility_class->isNotEmpty($priority_ids_str) ? " AND t.priority_level_id IN ($priority_ids_str) " : '' ; $brand = $utility_class->isNotEmpty($brand_id) ? " AND vb.id IN ($brand_id) " : "" ; $model = $utility_class->isNotEmpty($model_id) ? " AND vm.id IN ($model_id) " : "" ; if($utility_class->isNotEmpty($start_date) && $utility_class->isNotEmpty($end_date)){ $dates = " AND DATE_FORMAT(t.date_added, '%Y-%m-%d') BETWEEN '$start_date' AND '$end_date' "; }else{ $dates = ""; } if($owned_ticket_only){ $my_ticket_only = " AND t.user_id = $user_id " ; }else{ $my_ticket_only = "" ; } if($utility_class->isNotEmpty($search)){ $reference_no = " OR (t.reference_no = '$search' OR t.reference_no = replace('$search', '#', '')) "; }else{ $reference_no = ""; } // $filter_brands = (count($brand_access_arr) > 0) ? " AND (ttv.brand_id IN (".implode(",",$brand_access_arr).") OR ttv.brand_id IS NULL) " : "" ; $filter_brands_ids = (count($brand_access_arr) > 0) ? implode(",",$brand_access_arr) : "0" ; $filter_brands = " AND (vb.id IN ($filter_brands_ids) OR vb.id IS NULL) "; //REMOVE closed in default list $no_display_close = ""; if(!$utility_class->isNotEmpty($status_ids_str) && !$utility_class->isNotEmpty($category_ids_str) && // !$utility_class->isNotEmpty($final_status_ids_str) && // !$utility_class->isNotEmpty($filter_escalated_status) && !$utility_class->isNotEmpty($priority_ids_str) && !$utility_class->isNotEmpty($search) && !$utility_class->isNotEmpty($brand) && !$utility_class->isNotEmpty($model) && !$utility_class->isNotEmpty($my_ticket_only) ){ $no_display_close = " AND (ts.id NOT IN (3,5) OR ts.id IS NULL) "; } $final_data = null; if($source === 1 ){ $final_data = $this->read_ticket_list_table( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $status_ids_str_null, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $reference_no, $no_display_close, $search, $offset, $limit, $utility_class,$db); }else if($source === 2){ $final_data = $this->read_ticket_list_countings( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db); }else if($source === 3){ $final_data = $this->read_ticket_list_brand_count( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db); }else if($source === 4){ $final_data = $this->read_ticket_list_graph_open( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db); }else if($source === 5){ $final_data = $this->read_ticket_list_graph_close( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db); } return $final_data; } function read_ticket_list_countings( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $field = " 1 "; $query = $ticket_query." %s /sub category/ %s /ticket status/ %s /priority / %s /brand / %s /model / %s /brand access/ %s /own/ %s /dates/ AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT COUNT(1) FROM (".sprintf( $query, $field, $category_ids_str, $status_ids_str, // $final_status_ids_str, $priority_ids_str, $brand, $model, $filter_brands, $my_ticket_only, $dates ).") t1"; return $db->select($query_formatted); } function read_ticket_list_brand_count( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $field = " vb.id,vb.name "; $query = $ticket_query." %s /sub category/ %s /ticket status/ %s /priority / %s /brand / %s /model / %s /brand access/ %s /own/ %s /dates/ AND ts.id IN (1,2) AND t.status = 1 AND vb.id IS NOT NULL GROUP BY t.id "; $query_formatted = "SELECT t1.id,COUNT(1) as count FROM (".sprintf( $query, $field, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $brand, $model, $filter_brands, $my_ticket_only, $dates ).") t1 GROUP BY t1.id"; return $db->sql_query($query_formatted); } function read_open_ticket_w_brand($utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $field = " t.id,t.title,t.reference_no,sc.name as sub_category,pl.name as priority_level,t.date_added,u.id AS user_id, u.firstname, u.lastname,IF(ter.id IS NULL,t.date_added,ter.date_added) as last_reminder,vb.id as brand_id,vb.name as brand,vm.name as model,vmv.description as model_variant "; $query = $ticket_query." AND ts.id IN (1,2) AND t.status = 1 AND vb.id IS NOT NULL GROUP BY t.id "; $query_formatted = sprintf( $query, $field ); return $db->sql_query($query_formatted); } function read_ticket_list_graph_open( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $field = " YEAR(t.date_added) as data_year,MONTH(t.date_added) as data_month,t.date_added "; $query = $ticket_query." %s /sub category/ %s /ticket status/ %s /priority / %s /brand / %s /model / %s /brand access/ %s /own/ %s /dates/ AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT t1.data_year,t1.data_month,COUNT(1) as count,DATE_FORMAT(t1.date_added,'%b %Y') as data_monthw FROM (".sprintf( $query, $field, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $brand, $model, $filter_brands, $my_ticket_only, $dates ).") t1 GROUP BY t1.data_month, t1.data_year ORDER BY t1.data_year ASC, t1.data_month ASC "; return $db->sql_query($query_formatted); } function read_ticket_list_graph_close( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $field = " YEAR(t.date_added) as data_year,MONTH(t.date_added) as data_month,t.date_added "; $query = $ticket_query." %s /sub category/ %s /ticket status/ %s /priority / %s /brand / %s /model / %s /brand access/ %s /own/ %s /dates/ AND t.status = 1 AND ts.id IN (3) GROUP BY t.id "; $query_formatted = "SELECT t1.data_year,t1.data_month,COUNT(1) as count,DATE_FORMAT(t1.date_added,'%b %Y') as data_monthw FROM (".sprintf( $query, $field, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $brand, $model, $filter_brands, $my_ticket_only, $dates ).") t1 GROUP BY t1.data_month, t1.data_year ORDER BY t1.data_year ASC, t1.data_month ASC "; return $db->sql_query($query_formatted); } function read_ticket_list_table( $brand, $model, $filter_brands, $dates, $my_ticket_only, $category_ids_str, $status_ids_str, // $status_ids_str_null, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $start_date, $end_date, $reference_no, $no_display_close, $search, $offset, $limit, $utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $offset_limit = "LIMIT ".$offset.",".$limit." "; $field = " t.id,t.title,t.`date_added`,t.reference_no, DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, DATE_FORMAT(t.date_added,'%b. %d, %Y %h:%i %p') date_addedw, vb.name as brand,vm.name as model,ttv.plate_cs, pl.name as priority_level,sc.name as sub_category, IF(ts.id IS NOT NULL, ts.name , (SELECT name FROM ticket_status WHERE level = 1 AND status = 1 LIMIT 1) ) as status, u.firstname, u.lastname "; $field_count = " COUNT(1) "; // abs($db->server_info()) $left_regex = "\\\\b"; $right_regex = "\\\\b"; if(abs($db->server_info()) < 8){ $left_regex = "[[:<:]]"; $right_regex = "[[:>:]]"; } $search = " AND (CONCAT(t.title,' ',IF(ttv.id IS NULL,'',CONCAT(IFNULL(ttv.plate_cs,''),' ',IFNULL(ttv.vin,'')))) LIKE '%$search%' OR t.fault_codes RLIKE '$left_regex".$search."$right_regex' $reference_no ) "; $query = $ticket_query." %s /search/ %s /no_display_close/ %s /sub category/ %s /ticket status/ %s /priority / %s /brand / %s /model / %s /brand access/ %s /dates/ %s /my ticket/ AND t.status = 1 GROUP BY t.id ORDER BY t.date_added DESC %s "; $query_formatted = sprintf( $query, $field, $search, $no_display_close, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $brand, $model, $filter_brands, $dates, $my_ticket_only, $offset_limit ); // echo $query_formatted;exit; $query_formatted_count = 'SELECT COUNT(1) FROM ('.sprintf( $query, $field_count, $search, $no_display_close, $category_ids_str, $status_ids_str, // $final_status_ids_str, // $filter_escalated_status, $priority_ids_str, $brand, $model, $filter_brands, $dates, $my_ticket_only, '' ).') as t1'; // echo $query_formatted;exit; return array(intval($db->select($query_formatted_count)),$db->sql_query($query_formatted)); } function read_ticket_information($ticket_id,$utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $field = " t.id,t.title,t.description,t.`date_added`,t.reference_no,t.type,trg.rating, /DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf,/ DATE_FORMAT(t.date_added,'%b. %d, %Y %h:%i%p') as date_addedf, t.user_id,c.name as category, vb.id as brand_id,vb.name as brand,vm.id as model_id,vm.name as model,ttv.plate_cs,ttv.vin,ttv.mileage,ttv.workshop_visit_count, ttv.breakdown,ttv.on_site,t.complaint,t.work_performed,t.fault_codes, ts.id as ticket_status_id,ts.name as ticket_status, sc.id as sub_category_id,sc.name as sub_category,vmv.id as model_variant_id,vmv.description as model_variant, pl.id as priority_level_id,pl.name as priority_level,ttv.year_model,sc.category_id,c.show_vehicle, u.firstname, u.lastname,uc.mobile,uc.email "; $query = $ticket_query." AND t.id = $ticket_id "; $query_formatted = sprintf($query,$field); // echo $query_formatted;exit; return $db->sql_query($query_formatted); } function read_ticket_attachments($ticket_id,$utility_class,$db){ $query = "SELECT ,DATE_FORMAT(date_added, '%M %d, %Y') as date_addedf FROM `ticket_attachments` WHERE 1 AND `ticket_id` = $ticket_id AND status = 1"; return $db->sql_query($query); } function remove_role($id,$utility_class,$db){ $query = "UPDATE `lms2_roles` SET `status`=0 WHERE id = $id"; $affected_rows = $db->sql_query_affected_rows($query); return intval($affected_rows); } function read_ticket_replies($ticket_id,$offset,$limit,$order,$utility_class,$db){ $query = "SELECT tr.id,tr.ticket_id,t.user_id as owner_id,u.id as from_user,u.firstname,u.lastname,tr.description,ts.name as ticket_status, /IF(DATE_FORMAT(tr.date_added,'%Y') = DATE_FORMAT(NOW(),'%Y') , DATE_FORMAT(tr.date_added,'%b. %d %h:%i%p'),DATE_FORMAT(tr.date_added,'%b. %d, %Y %h:%i%p')) as date_addedf/ DATE_FORMAT(tr.date_added,'%b. %d, %Y %h:%i%p') as date_addedf FROM ticket_replies tr LEFT OUTER JOIN tickets t ON tr.ticket_id = t.id LEFT OUTER JOIN users u ON tr.user_id = u.id LEFT OUTER JOIN ticket_status ts ON tr.ticket_status_id = ts.id WHERE 1 AND tr.ticket_id = $ticket_id AND tr.status = 1 ORDER BY tr.date_added $order /LIMIT $offset,$limit/ "; // echo $query;exit; return $db->sql_query($query); } function read_ticket_activities($ticket_id,$offset,$limit,$order,$utility_class,$db){ // $query = "SELECT tr.id,tr.ticket_id,t.user_id as owner_id,u.id as from_user,u.firstname,u.lastname,tr.description,ts.name as ticket_status, // /IF(DATE_FORMAT(tr.date_added,'%Y') = DATE_FORMAT(NOW(),'%Y') , DATE_FORMAT(tr.date_added,'%b. %d %h:%i%p'),DATE_FORMAT(tr.date_added,'%b. %d, %Y %h:%i%p')) as date_addedf/ // DATE_FORMAT(tr.date_added,'%b. %d, %Y %h:%i%p') as date_addedf // FROM ticket_replies tr // LEFT OUTER JOIN tickets t // ON tr.ticket_id = t.id // LEFT OUTER JOIN users u // ON tr.user_id = u.id // LEFT OUTER JOIN ticket_status ts // ON tr.ticket_status_id = ts.id // WHERE 1 // AND tr.ticket_id = $ticket_id // AND tr.status = 1 // ORDER BY tr.date_added $order // /LIMIT $offset,$limit/ // "; $query = "SELECT l.,u.id_number,u.firstname,u.lastname, DATE_FORMAT(l.date_added,'%b. %d, %Y %h:%i%p') as date_addedf FROM `log` l LEFT OUTER JOIN users u ON l.user_id = u.id WHERE 1 AND type IN (2,3,4,5) AND target_id = $ticket_id ORDER BY `date_added` $order LIMIT $offset,$limit "; // echo $query;exit; return $db->sql_query($query); } function create_reopen_ticket($reply_id,$db){ $query = "INSERT INTO `ticket_reopen_status`(`ticket_reply_id`, `status`) VALUES ('$reply_id',1) "; $db->sql_query($query); } function create_escalated_ticket($reply_id,$type,$db){ $query = "INSERT INTO `ticket_escalated_status`(`ticket_reply_id`, `type`,`status`) VALUES ('$reply_id',$type,1) "; $db->sql_query($query); } function read_ticket_reply_attachments($ticket_reply_id,$utility_class,$db){ $query = "SELECT tra.id,tra.ticket_reply_id,tra.attachment_name,tra.original_name FROM ticket_replies tr LEFT OUTER JOIN tickets t ON tr.ticket_id = t.id LEFT OUTER JOIN users u ON tr.user_id = u.id LEFT OUTER JOIN ticket_status ts ON tr.ticket_status_id = ts.id LEFT OUTER JOIN ticket_reply_attachments tra ON tr.id = tra.ticket_reply_id WHERE 1 AND tra.ticket_reply_id = $ticket_reply_id AND tr.status = 1 AND tra.status = 1 ORDER BY tra.original_name ASC "; // echo $query ;exit; return $db->sql_query($query); } function read_ticket_list_autocomplete( $search, $offset, $limit, $utility_class,$db){ $ticket_query = $this->universal_ticket_query(); $offset_limit = "LIMIT ".$offset.",".$limit." "; $field = " t.id,t.title "; $query = $ticket_query." AND t.title LIKE '%%%s%%' AND t.status = 1 GROUP BY t.title ORDER BY t.date_added DESC %s "; $query_formatted = sprintf( $query, $field, $search, $offset_limit ); // echo $query_formatted;exit; return $db->sql_query($query_formatted); } function read_ticket_sub_category_ids_by_names($names,$utility_class,$db){ $query = "SELECT * FROM sub_categories WHERE name IN ($names) AND status = 1"; // echo $query;exit; return $db->sql_query($query); } function read_ticket_status_ids_by_names($names,$utility_class,$db){ $query = "SELECT * FROM ticket_status WHERE name IN ($names) AND status = 1"; // echo $query;exit; return $db->sql_query($query); } function read_ticket_final_status_ids_by_names($names,$utility_class,$db){ $query = "SELECT * FROM ticket_final_status WHERE name IN ($names) AND status = 1"; // echo $query;exit; return $db->sql_query($query); } function read_ticket_priority_ids_by_names($names,$utility_class,$db){ $query = "SELECT * FROM priority_levels WHERE name IN ($names) AND status = 1"; // echo $query;exit; return $db->sql_query($query); } function delete_ticket_reply($ticket_reply_id,$status,$db){ $query = "UPDATE `ticket_replies` SET `status`= $status WHERE 1 AND `id` = $ticket_reply_id "; // echo $query;exit; return $db->sql_query($query); } // START DASHBOARD FUNCTIONS function read_ticket_count_by_progress($status,$db){ if($status === 1){ $status_ids_str = " AND (ts.id IN ($status) OR ts.id IS NULL) "; }else{ $status_ids_str = " AND ts.id IN ($status) "; } $ticket_query = $this->universal_ticket_query(); $field = " 1 "; $query = $ticket_query." %s /ticket status/ AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT COUNT(1) FROM (".sprintf($query,$field,$status_ids_str).") t1"; return $db->select($query_formatted); } function read_ticket_count_by_final_status($status,$db){ $status_ids_str = " AND tfs.id IN ($status) "; $ticket_query = $this->universal_ticket_query(); $field = " 1 "; $query = $ticket_query." %s /ticket status/ AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT COUNT(1) FROM (".sprintf($query,$field,$status_ids_str).") t1"; return $db->select($query_formatted); } function read_ticket_active_support_count_per_brand($db){ $ticket_query = $this->universal_ticket_query(); $field = " vb.id,vb.name "; $query = $ticket_query." AND vb.id IS NOT NULL AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT t1.id,COUNT(1) as count FROM (".sprintf($query,$field).") t1 GROUP BY t1.id"; return $db->sql_query($query_formatted); } function read_open_close_graph_open($db){ $ticket_query = $this->universal_ticket_query(); $field = " YEAR(t.date_added) as data_year,MONTH(t.date_added) as data_month,t.date_added "; $query = $ticket_query." AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT t1.data_year,t1.data_month,COUNT(1) as count,DATE_FORMAT(t1.date_added,'%b') as data_monthw FROM (".sprintf($query,$field).") t1 GROUP BY t1.data_month, t1.data_year ORDER BY t1.data_year ASC, t1.data_month ASC "; return $db->sql_query($query_formatted); } function read_open_close_graph_close($db){ $ticket_query = $this->universal_ticket_query(); $field = " YEAR(t.date_added) as data_year,MONTH(t.date_added) as data_month,t.date_added "; $query = $ticket_query." AND ts.id IN (3) AND t.status = 1 GROUP BY t.id "; $query_formatted = "SELECT t1.data_year,t1.data_month,COUNT(1) as count,DATE_FORMAT(t1.date_added,'%b') as data_monthw FROM (".sprintf($query,$field).") t1 GROUP BY t1.data_month, t1.data_year ORDER BY t1.data_year ASC, t1.data_month ASC "; return $db->sql_query($query_formatted); } // END DASHBOARD FUNCTIONS function update_ticket_type($ticket_id,$type,$utility_class,$db){ $query = "UPDATE `tickets` SET `type`=$type WHERE 1 AND id = $ticket_id"; $db->sql_query($query); } function update_ticket_status($ticket_id,$status,$utility_class,$db){ $query = "UPDATE `tickets` SET `status`=$status WHERE 1 AND id = $ticket_id"; $db->sql_query($query); } function create_ticket_progress_status($ticket_id,$user_id,$status,$utility_class,$db){ $query = "INSERT INTO `ticket_progress_status`(`ticket_id`, `user_id`, `ticket_status_id`, `date_added`, `status`) VALUES ($ticket_id,$user_id,$status,NOW(),1)"; $db->sql_query($query); } function update_ticket_date_creation($ticket_id,$utility_class,$db){ $query = "UPDATE `tickets` SET `date_added`=NOW() WHERE 1 AND id = $ticket_id"; $db->sql_query($query); } function create_ticket_ref_no($ticket_id,$ref_no,$db){ $query = "UPDATE tickets SET reference_no = '$ref_no' WHERE id = $ticket_id"; $db->sql_query($query); } function create_reply_ticket_v2($user_id,$ticket_id,$description,$status,$utility_class,$db){ $query = "INSERT INTO `ticket_replies`(`user_id`, `ticket_id`, `description`, `ticket_status_id`,`date_added`, `status`) VALUES (NULLIF('$user_id',''),$ticket_id,NULLIF('$description',''),$status,NOW(),1)"; return $db->sql_query_id($query); } function read_ticket_status_id_by_name($name,$utility_class,$db){ $query = "SELECT id FROM ticket_status WHERE name = '$name' AND status = 1"; // echo $query;exit; return $db->select($query); } function read_latest_progress_status_id($ticket_id,$db){ $query = "SELECT MAX(id) FROM ticket_progress_status WHERE ticket_id = $ticket_id AND ticket_status_id IN (3,5) AND status = 1"; // echo $query;exit; return $db->select($query); } function create_ticket_rating($user_id,$ticket_id,$progress_id,$rating,$utility_class,$db){ $query = "INSERT INTO `ticket_ratings`( `user_id`,`ticket_id` ,`rating`, `progress_status_id`,`rating_comment`, `date_added`, `status`) VALUES ($user_id,$ticket_id,$rating,$progress_id,NULLIF('',''),NOW(),1)"; $db->sql_query($query); } function create_expert_reminder($ticket_id,$utility_class,$db){ $query = "INSERT INTO `ticket_expert_reminder`( `ticket_id`, `date_added`, `type`) VALUES ($ticket_id,NOW(),1)"; $db->sql_query($query); } } ?>

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