File manager

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

Back
<?php /** * use CRUD for class naming * example: create_ and read_and update_ and delete_ / class Knowledgebase{ function create_knowledgebase($user_id,$sub_category_id,$section_id,$title,$description,$codes,$utility_class,$db){ $query = "INSERT INTO `knowledgebase`( `user_id`, `sub_category_id`,`section_id`, `title`, `description`,`fault_codes`, `date_added`, `status`) VALUES (NULLIF('$user_id',''),'$sub_category_id',NULLIF('$section_id',''),'$title','$description',NULLIF('$codes',''),NOW(),1)"; return $db->sql_query_id($query); } function read_vehicle_type_knowledgebase($knowledgebase_id,$utility_class,$db){ $query = "SELECT FROM `knowledgebase_type_vehicles` WHERE 1 AND knowledgebase_id = $knowledgebase_id"; return $db->sql_query($query); } function update_knowledgebase($knowledgebase_id,$user_id,$sub_category_id,$section_id,$title,$description,$codes,$utility_class,$db){ $query = "UPDATE `knowledgebase` SET `sub_category_id`=$sub_category_id,section_id = NULLIF('$section_id',''),`title`='$title',`description`='$description',`fault_codes`=NULLIF('$codes','') WHERE 1 AND id = $knowledgebase_id"; // echo $query;exit; $db->sql_query($query); } function update_knowledgebase_attachment_status($ids,$status,$utility_class,$db){ $query = "UPDATE `knowledgebase_attachments` SET status = $status WHERE 1 AND `id` IN ($ids)"; return $db->sql_query_id($query); } function update_video_attachment_status($ids,$status,$utility_class,$db){ $query = "UPDATE `video_attachments` SET status = $status WHERE 1 AND `id` IN ($ids)"; return $db->sql_query_id($query); } function update_vehicle_type_knowledgebase($knowledgebase_id,$brand_id,$model_id,$model_variant_id,$utility_class,$db){ $query = "UPDATE `knowledgebase_type_vehicles` SET `brand_id`=NULLIF('$brand_id',''),`model_id`=NULLIF('$model_id',''),`model_variant_id`=NULLIF('$model_variant_id',''),`status`=1 WHERE 1 AND knowledgebase_id = $knowledgebase_id "; // echo $query;exit; $db->sql_query($query); } function update_vehicle_type_knowledgebase_status($knowledgebase_id,$status,$utility_class,$db){ $query = "UPDATE `knowledgebase_type_vehicles` SET status = $status WHERE 1 AND knowledgebase_id = $knowledgebase_id"; $db->sql_query($query); } function read_video_attachments($video_id,$utility_class,$db){ $query = "SELECT ,DATE_FORMAT(date_added, '%M %d, %Y') as date_addedf FROM `video_attachments` WHERE 1 AND `video_id` = $video_id AND status = 1"; // echo $query;exit; return $db->sql_query($query); } function create_vehicle_type_knowledgebase($knowledgebase_id,$brand_id,$model_id,$model_variant_id,$utility_class,$db){ $query = "INSERT INTO `knowledgebase_type_vehicles`( `knowledgebase_id`, `brand_id`, `model_id`,`model_variant_id`,`status`) VALUES ('$knowledgebase_id',NULLIF('$brand_id',''),NULLIF('$model_id',''),NULLIF('$model_variant_id',''),1)"; // echo $query;exit; return $db->sql_query_id($query); } function create_knowledgebase_attachment($knowledgebase_id,$attachment_change_name,$attachment_orig_name,$utility_class,$db){ $query = "INSERT INTO `knowledgebase_attachments`(`knowledgebase_id`, `attachment_name`, `original_name`, `date_added`, `status`) VALUES ($knowledgebase_id,'$attachment_change_name','$attachment_orig_name',NOW(),1)"; // echo $query;exit; return $db->sql_query_id($query); } function create_video_attachment($video_id,$attachment_change_name,$attachment_orig_name,$utility_class,$db){ $query = "INSERT INTO `video_attachments`(`video_id`, `attachment_name`, `original_name`, `date_added`, `status`) VALUES ($video_id,'$attachment_change_name','$attachment_orig_name',NOW(),1)"; // echo $query;exit; return $db->sql_query_id($query); } function create_video($user_id,$sub_category_id,$section_id,$title,$description,$codes,$original_name,$attachment_name,$utility_class,$db){ $query = "INSERT INTO `videos`( `user_id`, `sub_category_id`,section_id, `title`, `description`,`fault_codes`, `original_name`, `attachment_name`, `date_added`, `status`) VALUES (NULLIF('$user_id',''),'$sub_category_id',NULLIF('$section_id',''),'$title','$description',NULLIF('$codes',''),'$original_name','$attachment_name',NOW(),1)"; return $db->sql_query_id($query); } function create_vehicle_type_video($video_id,$brand_id,$model_id,$model_variant_id,$utility_class,$db){ $query = "INSERT INTO `video_type_vehicles`( `video_id`, `brand_id`, `model_id`,`model_variant_id`,`status`) VALUES ('$video_id',NULLIF('$brand_id',''),NULLIF('$model_id',''),NULLIF('$model_variant_id',''),1)"; // echo $query;exit; return $db->sql_query_id($query); } function update_video($video_id,$user_id,$sub_category_id,$section_id,$title,$description,$codes,$utility_class,$db){ $query = "UPDATE `videos` SET `sub_category_id`=$sub_category_id,section_id = NULLIF('$section_id',''),`title`='$title',`description`='$description',`fault_codes`=NULLIF('$codes','') WHERE 1 AND id = $video_id"; // echo $query;exit; $db->sql_query($query); } function update_vehicle_type_video($video_id,$brand_id,$model_id,$model_variant_id,$utility_class,$db){ $query = "UPDATE `video_type_vehicles` SET `brand_id`=NULLIF('$brand_id',''),`model_id`=NULLIF('$model_id',''),`model_variant_id`=NULLIF('$model_variant_id',''),`status`=1 WHERE 1 AND video_id = $video_id "; // echo $query;exit; $db->sql_query($query); } function update_vehicle_type_video_status($video_id,$status,$utility_class,$db){ $query = "UPDATE `video_type_vehicles` SET status = $status WHERE 1 AND video_id = $video_id"; $db->sql_query($query); } function read_vehicle_type_video($video_id,$utility_class,$db){ $query = "SELECT FROM `video_type_vehicles` WHERE 1 AND video_id = $video_id"; return $db->sql_query($query); } function read_video_list($user_id,$array_data,$position_id_arr,$owned_video_only,$category_ids_str,$brand_id, $model_id,$section_id,$brand_access_arr,$search,$offset,$limit,$utility_class,$db){ $category_ids_str = $utility_class->isNotEmpty($category_ids_str) ? " AND v.sub_category_id IN ($category_ids_str) " : '' ; $brand = $utility_class->isNotEmpty($brand_id) ? " AND vtv.brand_id IN ($brand_id) " : "" ; $model = $utility_class->isNotEmpty($model_id) ? " AND vtv.model_id IN ($model_id) " : "" ; $section = $utility_class->isNotEmpty($section_id) ? " AND ks.id IN ($section_id) " : "" ; $section_remove_has_section = (isset($array_data['all_null_section']) && !$owned_video_only) ? " AND ks.id IS NULL " : "" ; if($owned_video_only){ $my_video_only = " AND v.user_id = $user_id " ; }else{ $my_video_only = "" ; } $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) "; $video_query = $this->universal_video_query(); $left_regex = "\\\\b"; $right_regex = "\\\\b"; if(abs($db->server_info()) < 8){ $left_regex = "[[:<:]]"; $right_regex = "[[:>:]]"; } $search = "AND (CONCAT(v.title,' ',v.description) LIKE '%$search%' OR v.fault_codes RLIKE '$left_regex".$search."$right_regex' ) "; if(count($position_id_arr) > 1){ $user_position_id = $position_id_arr[0]; $filter_position_id = $position_id_arr[1]; $position_filter = ' AND JSON_CONTAINS(va.json_data,\'"'.$filter_position_id.'"\',"$") = 1 '; }else{ if($owned_video_only){ $position_filter = ""; }else{ $user_position_id = $position_id_arr[0]; $position_filter = ' AND (JSON_CONTAINS(va.json_data,\'"'.$user_position_id.'"\',"$") = 1 OR va.id IS NULL OR va.json_data = "[]" OR v.user_id = '.$user_id.') '; } } // REPLACE("SQL Tutorial", "SQL", "HTML"); $offset_limit = "LIMIT ".$offset.",".$limit." "; $fields = " v.id,v.original_name,v.attachment_name,v.title, REPLACE(CONCAT(SUBSTRING(v.description,1,30),'…'),'<br>',' ') as description, v.description as descriptionf, vb.id as brand_id,vb.name as brand,vm.id as model_id,vm.name as model,vmv.description as model_variant,vmv.id as model_variant_id, v.date_added,DATE_FORMAT(v.date_added, '%M %d, %Y') AS date_addedf "; $count = " COUNT(1) "; $query = $video_query." %s /search/ %s /brand/ %s /model/ %s /section filter/ %s /brand access/ %s /sub categoryt/ %s /owner/ %s /position filter/ %s /section remove/ AND v.status = 1 ORDER BY /v.`title` ASC,/v.`date_added` DESC %s "; $query_formatted_count = sprintf( $query, $count, $search, $brand, $model, $section, $filter_brands, $category_ids_str, $my_video_only, $position_filter, $section_remove_has_section, '' ); // echo $query_formatted_count;exit; $query_formatted = sprintf( $query, $fields, $search, $brand, $model, $section, $filter_brands, $category_ids_str, $my_video_only, $position_filter, $section_remove_has_section, $offset_limit ); // echo $query_formatted;exit; return array(intval($db->select($query_formatted_count)),$db->sql_query($query_formatted)); } function update_video_status($video_id,$status,$utility_class,$db){ $query = "UPDATE `videos` SET `status`=$status WHERE 1 AND id = $video_id "; // echo $query;exit; return $db->sql_query_affected_rows($query); } function read_knowledgebase_list($user_id, $array_data, $src_type, $owned_kb_only, $short_description, $category_ids_str, $brand_id, $model_id, $section_id, $brand_access_arr, $search, $start_date, $end_date, $offset, $limit, $universal_knowledgebase_query, $universal_ticket_query, $display_union_count, $utility_class, $db){ $category_ids_str = $utility_class->isNotEmpty($category_ids_str) ? " AND t.sub_category_id IN ($category_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) " : '' ; $brand = $utility_class->isNotEmpty($brand_id) ? " AND vb.id IN ($brand_id) " : "" ; $model = $utility_class->isNotEmpty($model_id) ? " AND vm.id IN ($model_id) " : "" ; $section = $utility_class->isNotEmpty($section_id) ? " AND ks.id IN ($section_id) " : "" ; $section_remove_has_section = (isset($array_data['all_null_section']) && !$owned_kb_only) ? " AND ks.id IS NULL " : "" ; 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_kb_only){ $my_kb_only = " AND t.user_id = $user_id " ; }else{ $my_kb_only = "" ; } $offset_limit = "LIMIT ".$offset.",".$limit." "; if($display_union_count > 0){ $union_offset_limit = "LIMIT $display_union_count "; }else{ $union_offset_limit = ""; } $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) "; $left_regex = "\\\\b"; $right_regex = "\\\\b"; if(abs($db->server_info()) < 8){ $left_regex = "[[:<:]]"; $right_regex = "[[:>:]]"; } $search = "AND (CONCAT(t.title,' ',t.description) LIKE '%$search%' OR t.fault_codes RLIKE '$left_regex".$search."$right_regex' ) "; // START TICKET SECTION $ticket_fields = " '2' as source, t.id, t.title, CONCAT(SUBSTRING(t.description, 1, 30),' ',SUBSTRING(t.complaint, 1, 30)) as descriptionf, /t.description,/ t.`date_added`, DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, vb.id as brand_id, vb.name as brand, vm.id as model_id, vm.name as model, ts.name as status, sc.id as sub_category_id, sc.name as sub_category, vmv.id as model_variant_id, vmv.description as model_variant, sc.category_id, u.id_number, u.firstname, u.lastname "; $ticket_counts = " COUNT(1) "; $ticket_query = $universal_ticket_query." %s /search/ %s /sub category/ %s /brand / %s /model / %s /brand access/ %s /dates/ %s /my kb/ AND ts.id IN (3,4,5) /close and escalated/ AND (t.type NOT IN (1) OR t.type IS NULL) AND t.status = 1 GROUP BY t.id ORDER BY t.date_added DESC %s "; if($utility_class->isNotEmpty($my_kb_only) \|\| $src_type == '2' \|\| $utility_class->isNotEmpty($section)){ $ticket_query_formatted = " SELECT '','','','','','','','','','','','','','','','','','','' LIMIT 0"; }else{ $ticket_query_formatted = sprintf( $ticket_query, $ticket_fields, $search, $category_ids_str, $brand, $model, $filter_brands, $dates, $my_kb_only, $union_offset_limit, ); } // END TICKET SECTION // START KB SECTION $knowledgebase_fields = " '1' as source, t.id, t.title, SUBSTRING(t.description, 1, 30) as descriptionf, /t.description,/ t.`date_added`, DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, vb.id as brand_id, vb.name as brand, vm.id as model_id, vm.name as model, '' as status, sc.id as sub_category_id, sc.name as sub_category, vmv.id as model_variant_id, vmv.description as model_variant, sc.category_id, u.id_number, u.firstname, u.lastname "; $knowledgebase_counts = " COUNT(1) "; $knowledgebase_query = $universal_knowledgebase_query." %s /search/ %s /sub category/ /* percentS final status for ticket query only/ %s /brand / %s /model / %s / kb section/ %s /brand access/ %s /dates/ %s /my kb/ %s / exclude has section / AND t.status = 1 GROUP BY t.id ORDER BY t.date_added DESC %s "; if($src_type == '1' ){ $knowledgebase_query_formatted = " SELECT '','','','','','','','','','','','','','','','','','','' LIMIT 0"; }else{ $knowledgebase_query_formatted = sprintf( $knowledgebase_query, $knowledgebase_fields, $search, $category_ids_str, $brand, $model, $section, $filter_brands, $dates, $my_kb_only, $section_remove_has_section, $union_offset_limit, ); } // END KB SECTION // MERGE QUERIES if( $src_type == '1'){ $knowledgebase_ticket_union = "(".$ticket_query_formatted.") UNION ALL (".$knowledgebase_query_formatted.")"; }else if($utility_class->isNotEmpty($my_kb_only) \|\| $src_type == '2' \|\| $utility_class->isNotEmpty($section)){ $knowledgebase_ticket_union = "(".$knowledgebase_query_formatted.") UNION ALL (".$ticket_query_formatted.")"; }else{ $knowledgebase_ticket_union = "(".$ticket_query_formatted.") UNION ALL (".$knowledgebase_query_formatted.")"; } $final_query = "SELECT FROM (".$knowledgebase_ticket_union.") t1 ORDER BY t1.date_added DESC ".$offset_limit; $final_query_count = "SELECT COUNT(1) FROM (".$knowledgebase_ticket_union.") t1 "; // echo $final_query ;exit; // echo $ticket_query_formatted ;exit; return array(intval($db->select($final_query_count)),$db->sql_query($final_query)); // echo $final_query_count ;exit; // echo $final_query ;exit; } function read_knowledgebase_list_autocomplete( $search, $offset, $limit, $universal_knowledgebase_query, $universal_ticket_query, $display_union_count, $utility_class, $db){ $offset_limit = "LIMIT ".$offset.",".$limit." "; if($display_union_count > 0){ $union_offset_limit = "LIMIT $display_union_count "; }else{ $union_offset_limit = ""; } // START TICKET SECTION $ticket_fields = " t.id, t.title "; $ticket_query = $universal_ticket_query." AND t.title LIKE '%%%s%%' AND ts.id IN (3,4,5) /close and escalated/ AND (t.type NOT IN (1) OR t.type IS NULL) AND t.status = 1 GROUP BY t.title ORDER BY t.date_added DESC %s "; $ticket_query_formatted = sprintf( $ticket_query, $ticket_fields, $search, $union_offset_limit, ); // END TICKET SECTION // START KB SECTION $knowledgebase_fields = " t.id, t.title "; $knowledgebase_query = $universal_knowledgebase_query." AND t.title LIKE '%%%s%%' AND t.status = 1 GROUP BY t.id ORDER BY t.date_added DESC %s "; $knowledgebase_query_formatted = sprintf( $knowledgebase_query, $knowledgebase_fields, $search, $union_offset_limit, ); // END KB SECTION // MERGE QUERIES $knowledgebase_ticket_union = "(".$ticket_query_formatted.") UNION ALL (".$knowledgebase_query_formatted.")"; $final_query = "SELECT * FROM (".$knowledgebase_ticket_union.") t1 ORDER BY t1.title DESC ".$offset_limit; // echo $final_query; exit; // $final_query_count = "SELECT COUNT(1) FROM (".$knowledgebase_ticket_union.") t1 "; return $db->sql_query($final_query); } function read_related_articles( $title, $fault_codes, $sub_category_id, $brand_id, $model_id, $model_variant_id, $brand_access_arr, $offset, $limit, $universal_knowledgebase_query, $universal_ticket_query, $display_union_count, $utility_class, $db){ // format each of search keywords into the db query to be run $search = " AND (("; $keywords = explode(' ', $title); $keywords = array_unique($keywords); foreach ($keywords as $word){ $search .= " CONCAT(t.title,' ',t.description) LIKE '%%".$word."%%' OR "; } $search = substr($search, 0, strlen($search)-4).") %s )"; //remove last OR $fault_codes_s = ""; if($utility_class->isNotEmpty($fault_codes)){ $fault_codes_s = " OR ("; $fault_codes_kw = explode(',', $fault_codes); $fault_codes_kw = array_unique($fault_codes_kw); $left_regex = "\\\\b"; $right_regex = "\\\\b"; if(abs($db->server_info()) < 8){ $left_regex = "[[:<:]]"; $right_regex = "[[:>:]]"; } foreach ($fault_codes_kw as $word){ $fault_codes_s .= " t.fault_codes RLIKE '$left_regex".$word."$right_regex' OR "; } $fault_codes_s = substr($fault_codes_s, 0, strlen($fault_codes_s)-4).")"; //remove last OR } $search = sprintf($search,$fault_codes_s); // $category_ids_str = $utility_class->isNotEmpty($sub_category_id) ? " AND t.sub_category_id IN ($sub_category_id) " : '' ; // $brand = $utility_class->isNotEmpty($brand_id) ? " AND vb.id IN ($brand_id) " : "" ; // $model = $utility_class->isNotEmpty($model_id) ? " AND vm.id IN ($model_id) " : "" ; // $model_variant = $utility_class->isNotEmpty($model_variant_id) ? " AND vmv.id IN ($model_variant_id) " : "" ; $category_ids_str = ""; $brand =""; $model =""; $model_variant = ""; $offset_limit = "LIMIT ".$offset.",".$limit." "; if($display_union_count > 0){ $union_offset_limit = "LIMIT $display_union_count "; }else{ $union_offset_limit = ""; } $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) "; // START TICKET SECTION $ticket_fields = " '2' as source, t.id, t.title, CONCAT(SUBSTRING(t.description, 1, 30),' ',SUBSTRING(t.complaint, 1, 30)) as descriptionf, /SUBSTRING(t.description, 1, 100) as descriptionf,/ /t.description,/ t.`date_added`, DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, vb.id as brand_id, vb.name as brand, vm.id as model_id, vm.name as model, IF(ts.id IS NOT NULL, ts.name, (SELECT name FROM ticket_status WHERE level = 1 AND status = 1 LIMIT 1) ) as status, sc.id as sub_category_id, sc.name as sub_category, vmv.id as model_variant_id, vmv.description as model_variant, sc.category_id "; $ticket_counts = " COUNT(1) "; $ticket_query = $universal_ticket_query." %s /search/ %s /sub category/ %s /brand / %s /model / %s /brand access/ %s /mvariant/ AND ts.id IN (3,4,5) /close and escalated/ AND (t.type NOT IN (1) OR t.type IS NULL) AND t.status = 1 GROUP BY t.id ORDER BY t.date_added DESC %s "; $ticket_query_formatted = sprintf( $ticket_query, $ticket_fields, $search, $category_ids_str, $brand, $model, $filter_brands, $model_variant, $union_offset_limit, ); // echo $ticket_query_formatted;exit; // END TICKET SECTION // START KB SECTION $knowledgebase_fields = " '1' as source, t.id, t.title, SUBSTRING(t.description, 1, 100) as descriptionf, /t.description,/ t.`date_added`, DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, vb.id as brand_id, vb.name as brand, vm.id as model_id, vm.name as model, '' as status, sc.id as sub_category_id, sc.name as sub_category, vmv.id as model_variant_id, vmv.description as model_variant, sc.category_id "; $knowledgebase_counts = " COUNT(1) "; $knowledgebase_query = $universal_knowledgebase_query." %s /search/ %s /sub category/ %s /brand / %s /model / %s /brand access/ %s /mvariant/ AND t.status = 1 GROUP BY t.id ORDER BY t.date_added DESC %s "; $knowledgebase_query_formatted = sprintf( $knowledgebase_query, $knowledgebase_fields, $search, $category_ids_str, $brand, $model, $filter_brands, $model_variant, $union_offset_limit, ); // END KB SECTION // MERGE QUERIES $knowledgebase_ticket_union = "(".$ticket_query_formatted.") UNION ALL (".$knowledgebase_query_formatted.")"; $final_query = "SELECT * FROM (".$knowledgebase_ticket_union.") t1 ORDER BY t1.date_added DESC ".$offset_limit; // echo $final_query;exit; $final_query_count = "SELECT COUNT(1) FROM (".$knowledgebase_ticket_union.") t1 "; return array(intval($db->select($final_query_count)),$db->sql_query($final_query)); } function read_knowledgebase_video_autocomplete( $search, $offset, $limit, $utility_class,$db){ $knowledgebase_query = $this->universal_video_query(); $offset_limit = "LIMIT ".$offset.",".$limit." "; $field = " v.id,v.title "; $query = $knowledgebase_query." AND v.title LIKE '%%%s%%' AND v.status = 1 GROUP BY v.title ORDER BY v.title ASC %s "; $query_formatted = sprintf( $query, $field, $search, $offset_limit ); // echo $query_formatted;exit; return $db->sql_query($query_formatted); } function read_knowledgebase_attachments($knowledgebase_id,$utility_class,$db){ $query = "SELECT ,DATE_FORMAT(date_added, '%M %d, %Y') as date_addedf FROM `knowledgebase_attachments` WHERE 1 AND `knowledgebase_id` = $knowledgebase_id AND status = 1"; return $db->sql_query($query); } function read_knowledgebase_information($knowledgebase_id,$utility_class,$db){ $knowledgebase_query = $this->universal_knowledgebase_query(); $fields = " t.id, t.title, t.description, t.fault_codes, t.`date_added`, DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, vb.id as brand_id, vb.name as brand, vm.id as model_id, vm.name as model, sc.id as sub_category_id, sc.name as sub_category, vmv.id as model_variant_id, vmv.description as model_variant, sc.category_id, /ks.id as section_id,/ ks.name as section, c.show_vehicle, ttv.id as knowledgebase_vehicle_id, u.firstname, u.lastname "; $query = $knowledgebase_query." AND t.id = $knowledgebase_id "; $query_formatted = sprintf($query,$fields); return $db->sql_query($query_formatted); } function universal_knowledgebase_query(){ return " SELECT %s FROM `knowledgebase` 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 knowledgebase_type_vehicles ttv ON ttv.id = (SELECT ttvs.id FROM knowledgebase_type_vehicles ttvs WHERE ttvs.knowledgebase_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 ttv.brand_id = vb.id LEFT OUTER JOIN vehicle_model_variants vmv ON ttv.model_variant_id = vmv.id LEFT OUTER JOIN users u ON t.user_id = u.id LEFT OUTER JOIN sections ks ON t.section_id = ks.id WHERE 1 "; } function universal_video_query(){ return " SELECT %s FROM `videos` v LEFT OUTER JOIN video_type_vehicles vtv ON v.id = vtv.video_id LEFT OUTER JOIN sub_categories sc ON v.sub_category_id = sc.id LEFT OUTER JOIN vehicle_models vm ON vtv.model_id = vm.id LEFT OUTER JOIN vehicle_brands vb ON vtv.brand_id = vb.id LEFT OUTER JOIN vehicle_model_variants vmv ON vtv.model_variant_id = vmv.id LEFT OUTER JOIN users u ON v.user_id = u.id LEFT OUTER JOIN video_access va ON v.id = va.video_id LEFT OUTER JOIN sections ks ON v.section_id = ks.id WHERE 1 "; } function read_video_information($video_id,$utility_class,$db){ $video_query = $this->universal_video_query(); // $field = " t.id,t.title,t.description,t.`date_added`, // DATE_FORMAT(t.date_added,'%Y-%m-%d') date_addedf, // vb.id as brand_id,vb.name as brand,vm.id as model_id,vm.name as model,ttv.plate_cs, // ts.id as ticket_status_id,ts.name as ticket_status,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 "; $fields = "v.id,v.original_name,v.attachment_name,v.title,sc.name as sub_category,u.firstname,u.lastname,v.fault_codes, vb.id as brand_id,vb.name as brand,vm.id as model_id,vm.name as model,vmv.description as model_variant,vmv.id as model_variant_id, /CONCAT(SUBSTRING(v.description,1,30),'...') as description,/ v.description, v.date_added,DATE_FORMAT(v.date_added, '%M %d, %Y') AS date_addedf, va.json_data, ks.name as section "; $query = $video_query." AND v.id = $video_id "; $query_formatted = sprintf($query,$fields); // echo $query_formatted;exit; return $db->sql_query($query_formatted); } function read_sub_category_count($db){ $knowledgebase_query = $this->universal_knowledgebase_query(); // START knowledgebase SECTION $knowledgebase_fields = " sc.id, sc.name, COUNT(sc.id) as sub_category_count "; $knowledgebase_counts = " COUNT(1) "; $knowledgebase_query = $knowledgebase_query." AND t.status = 1 AND sc.status = 1 AND c.status = 1 GROUP BY sc.id ORDER BY sc.name ASC "; $knowledgebase_query_formatted = sprintf( $knowledgebase_query, $knowledgebase_fields, ); return $db->sql_query($knowledgebase_query_formatted); } function read_sub_category_count_v2( $session_access, $brand_access_arr, $universal_knowledgebase_query, $universal_ticket_query, $display_union_count, $utility_class, $db){ $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) "; // START TICKET SECTION $ticket_fields = " sc.id, sc.name, COUNT(sc.id) as sub_category_count "; $ticket_query = $universal_ticket_query." AND ts.id IN (3,4,5) /close and escalated/ AND (t.type NOT IN (1) OR t.type IS NULL) AND t.status = 1 AND sc.status = 1 AND c.status = 1 %s /brand access/ GROUP BY sc.id ORDER BY sc.name ASC "; $ticket_query_formatted = sprintf( $ticket_query, $ticket_fields, $filter_brands ); // echo $ticket_query_formatted; // END TICKET SECTION // START KB SECTION if(isset($session_access['4']) && in_array('4_3', $session_access['4'])){ $ait_access= ""; }else{ $ait_access= " AND ks.id IS NULL"; } $knowledgebase_fields = " sc.id, sc.name, COUNT(sc.id) as sub_category_count "; $knowledgebase_query = $universal_knowledgebase_query." AND t.status = 1 AND sc.status = 1 AND c.status = 1 %s /brand access/ %s /ait kb access / GROUP BY sc.id ORDER BY sc.name ASC "; $knowledgebase_query_formatted = sprintf( $knowledgebase_query, $knowledgebase_fields, $filter_brands, $ait_access ); // END KB SECTION // MERGE QUERIES $knowledgebase_ticket_union = "(".$ticket_query_formatted.") UNION ALL (".$knowledgebase_query_formatted.")"; $final_query = "SELECT t1.id,t1.name,SUM(t1.sub_category_count) as sub_category_count FROM (".$knowledgebase_ticket_union.") t1 GROUP BY t1.id "; return $db->sql_query($final_query); } function read_watched_video($video_id,$user_id,$utility_class,$db){ $query = "SELECT id FROM `video_views` WHERE 1 AND status = 1 AND `video_id` = $video_id AND `user_id` = $user_id LIMIT 1"; return $db->select($query); } function create_watched_video($video_id,$user_id,$utility_class,$db){ $query = "INSERT INTO `video_views`(`user_id`, `date_added`, `status`, `video_id`) VALUES ($user_id,NOW(),1,$video_id)"; // echo $query;exit; return $db->sql_query($query); } function read_video_viewers($video_id,$search,$offset,$limit,$order,$utility_class,$db){ $query = "SELECT vv.,u.id_number,u.firstname,u.lastname, DATE_FORMAT(vv.date_added,'%b. %d, %Y %h:%i%p') as date_addedf FROM `video_views` vv LEFT OUTER JOIN users u ON vv.user_id = u.id WHERE 1 AND (u.id_number = '$search' OR CONCAT(u.firstname,' ',u.lastname,' ',u.firstname) LIKE '%$search%') AND vv.video_id = $video_id ORDER BY vv.`date_added` $order LIMIT $offset,$limit "; // echo $query;exit; return $db->sql_query($query); } function create_video_access($video_id,$json_data,$utility_class,$db){ $query = "INSERT INTO `video_access`(`json_data`, `video_id`, `status`) VALUES ('$json_data',$video_id,1) ON DUPLICATE KEY UPDATE json_data = VALUES (json_data) "; return $db->sql_query($query); } } ?>

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