| @@ -1,294 +1,294 @@ | ||
| 1 | -<?php | |
| 2 | - | |
| 3 | -namespace LearnPress\Databases\Course; | |
| 4 | - | |
| 5 | -use Exception; | |
| 6 | -use LearnPress\Databases\DataBase; | |
| 7 | -use LearnPress\Filters\Course\CourseJsonFilter; | |
| 8 | -use LP_Helper; | |
| 9 | - | |
| 10 | -defined( 'ABSPATH' ) || exit(); | |
| 11 | - | |
| 12 | -/** | |
| 13 | - * Class CourseJsonDB | |
| 14 | - * | |
| 15 | - * Move from LP_Course_JSON_DB to here | |
| 16 | - * | |
| 17 | - * @since 4.3.2.3 | |
| 18 | - * @version 1.0.1 | |
| 19 | - */ | |
| 20 | -class CourseJsonDB extends DataBase { | |
| 21 | - private static $_instance; | |
| 22 | - | |
| 23 | - protected function __construct() { | |
| 24 | - parent::__construct(); | |
| 25 | - } | |
| 26 | - | |
| 27 | - public static function getInstance() { | |
| 28 | - if ( is_null( self::$_instance ) ) { | |
| 29 | - self::$_instance = new self(); | |
| 30 | - } | |
| 31 | - | |
| 32 | - return self::$_instance; | |
| 33 | - } | |
| 34 | - | |
| 35 | - /** | |
| 36 | - * Get Courses | |
| 37 | - * | |
| 38 | - * @param CourseJsonFilter $filter | |
| 39 | - * @param int $total_rows return total_rows | |
| 40 | - * | |
| 41 | - * @return array|int|string|null | |
| 42 | - * @throws Exception | |
| 43 | - * @version 1.0.1 | |
| 44 | - * @since 4.2.6.9 | |
| 45 | - */ | |
| 46 | - public function get_courses( CourseJsonFilter $filter, int &$total_rows = 0 ) { | |
| 47 | - $filter->fields = array_merge( $filter->all_fields, $filter->fields ); | |
| 48 | - | |
| 49 | - if ( empty( $filter->collection ) ) { | |
| 50 | - $filter->collection = $this->tb_lp_courses; | |
| 51 | - } | |
| 52 | - | |
| 53 | - if ( empty( $filter->collection_alias ) ) { | |
| 54 | - $filter->collection_alias = 'c'; | |
| 55 | - } | |
| 56 | - | |
| 57 | - $ca = $filter->collection_alias; | |
| 58 | - | |
| 59 | - // Find ID | |
| 60 | - if ( ! empty( $filter->ID ) ) { | |
| 61 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.ID = %d", $filter->ID ); | |
| 62 | - } | |
| 63 | - | |
| 64 | - // Status | |
| 65 | - $filter->post_status = (array) $filter->post_status; | |
| 66 | - if ( ! empty( $filter->post_status ) ) { | |
| 67 | - $post_status_format = LP_Helper::db_format_array( $filter->post_status, '%s' ); | |
| 68 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.post_status IN (" . $post_status_format . ')', $filter->post_status ); | |
| 69 | - } | |
| 70 | - | |
| 71 | - // Exclude status auto-draft, because Admin of WP not show auto-draft in list post, so all front-end and back-end of LP also not show auto-draft | |
| 72 | - if ( ! in_array( 'auto-draft', $filter->post_status, true ) ) { | |
| 73 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.post_status != %s", 'auto-draft' ); | |
| 74 | - } | |
| 75 | - | |
| 76 | - // Term ids | |
| 77 | - if ( ! empty( $filter->term_ids ) ) { | |
| 78 | - // Sanitize term ids | |
| 79 | - $filter->term_ids = array_map( 'absint', $filter->term_ids ); | |
| 80 | - $term_ids_format = join( ',', $filter->term_ids ); | |
| 81 | - $filter->join[] = "INNER JOIN $this->tb_term_relationships AS r_term_p ON $ca.ID = r_term_p.object_id"; | |
| 82 | - $filter->join[] = "INNER JOIN $this->tb_term_taxonomy AS tx_p ON r_term_p.term_taxonomy_id = tx_p.term_taxonomy_id"; | |
| 83 | - $filter->where[] = "AND r_term_p.term_taxonomy_id IN ($term_ids_format)"; | |
| 84 | - $filter->where[] = $this->wpdb->prepare( 'AND tx_p.taxonomy = %s', $filter->taxonomy ); | |
| 85 | - } | |
| 86 | - | |
| 87 | - // Course ids | |
| 88 | - if ( ! empty( $filter->ids ) ) { | |
| 89 | - $ids = array_map( 'absint', $filter->ids ); | |
| 90 | - $ids_format = join( ',', $ids ); | |
| 91 | - $filter->where[] = "AND $ca.ID IN ($ids_format)"; | |
| 92 | - } | |
| 93 | - | |
| 94 | - // Title | |
| 95 | - if ( ! empty( $filter->post_title ) ) { | |
| 96 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.post_title LIKE %s", '%' . $filter->post_title . '%' ); | |
| 97 | - } | |
| 98 | - | |
| 99 | - // Name(slug) | |
| 100 | - if ( ! empty( $filter->post_name ) ) { | |
| 101 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.post_name = %s", $filter->post_name ); | |
| 102 | - } | |
| 103 | - | |
| 104 | - // Author | |
| 105 | - if ( ! empty( $filter->post_author ) ) { | |
| 106 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.post_author = %d", $filter->post_author ); | |
| 107 | - } | |
| 108 | - | |
| 109 | - // Authors | |
| 110 | - if ( ! empty( $filter->post_authors ) ) { | |
| 111 | - $post_authors_format = LP_Helper::db_format_array( $filter->post_authors, '%d' ); | |
| 112 | - $filter->where[] = $this->wpdb->prepare( "AND $ca.post_author IN (" . $post_authors_format . ')', $filter->post_authors ); | |
| 113 | - } | |
| 114 | - | |
| 115 | - /** | |
| 116 | - * Type price | |
| 117 | - * | |
| 118 | - * if 'free' and 'paid' are not in the array or not null, we will filter the price | |
| 119 | - */ | |
| 120 | - if ( ! empty( $filter->type_price ) && | |
| 121 | - ( ! in_array( 'free', $filter->type_price ) && ! in_array( 'paid', $filter->type_price ) ) ) { | |
| 122 | - foreach ( $filter->type_price as $type_price ) { | |
| 123 | - switch ( $type_price ) { | |
| 124 | - case 'free': | |
| 125 | - $filter->where[] = 'AND price_to_sort = 0'; | |
| 126 | - break; | |
| 127 | - case 'paid': | |
| 128 | - $filter->where[] = 'AND price_to_sort > 0'; | |
| 129 | - break; | |
| 130 | - } | |
| 131 | - } | |
| 132 | - } | |
| 133 | - | |
| 134 | - // Is sale | |
| 135 | - if ( ! empty( $filter->is_sale ) ) { | |
| 136 | - $filter->where[] = 'AND is_sale = 1'; | |
| 137 | - } | |
| 138 | - | |
| 139 | - // Is feature | |
| 140 | - if ( ! empty( $filter->is_feature ) ) { | |
| 141 | - $filter = $this->get_courses_in_feature( $filter ); | |
| 142 | - } | |
| 143 | - | |
| 144 | - // Levels | |
| 145 | - if ( ! empty( $filter->levels ) ) { | |
| 146 | - $filter->join[] = "INNER JOIN $this->tb_postmeta AS pml ON c.ID = pml.post_id"; | |
| 147 | - $filter->where[] = $this->wpdb->prepare( 'AND pml.meta_key = %s', '_lp_level' ); | |
| 148 | - $levels_format = LP_Helper::db_format_array( $filter->levels, '%s' ); | |
| 149 | - $filter->where[] = $this->wpdb->prepare( 'AND pml.meta_value IN (' . $levels_format . ')', $filter->levels ); | |
| 150 | - } | |
| 151 | - | |
| 152 | - // Course type | |
| 153 | - if ( ! empty( $filter->type ) && $filter->type !== 'all' ) { | |
| 154 | - if ( $filter->type === 'offline' ) { | |
| 155 | - $filter->join[] = "INNER JOIN $this->tb_postmeta AS pm_off ON c.ID = pm_off.post_id"; | |
| 156 | - $filter->where[] = $this->wpdb->prepare( 'AND pm_off.meta_key = %s', '_lp_offline_course' ); | |
| 157 | - $filter->where[] = $this->wpdb->prepare( 'AND pm_off.meta_value = %s', 'yes' ); | |
| 158 | - } else { | |
| 159 | - $filter->where[] = $this->wpdb->prepare( | |
| 160 | - "AND c.ID NOT IN | |
| 161 | - ( SELECT id FROM $this->tb_posts as p1 | |
| 162 | - INNER JOIN $this->tb_postmeta as pm_ol on p1.ID = pm_ol.post_id | |
| 163 | - WHERE pm_ol.meta_key = %s AND pm_ol.meta_value = %s )", | |
| 164 | - '_lp_offline_course', | |
| 165 | - 'yes' | |
| 166 | - ); | |
| 167 | - } | |
| 168 | - } | |
| 169 | - | |
| 170 | - $filter = apply_filters( 'lp/courses-json/query/filter', $filter ); | |
| 171 | - | |
| 172 | - return $this->execute( $filter, $total_rows ); | |
| 173 | - } | |
| 174 | - | |
| 175 | - /** | |
| 176 | - * Get list courses is on popular | |
| 177 | - * Use "UNION" to merge 2 query | |
| 178 | - * | |
| 179 | - * @param CourseJsonFilter $filter | |
| 180 | - * | |
| 181 | - * @return CourseJsonFilter | |
| 182 | - * @throws Exception | |
| 183 | - * @version 1.0.9 | |
| 184 | - * @since 4.3.2.3 | |
| 185 | - */ | |
| 186 | - public function get_courses_order_by_popular( CourseJsonFilter &$filter ): CourseJsonFilter { | |
| 187 | - // Set list name columns get | |
| 188 | - //$columns_table_posts = $this->get_cols_of_table( $this->tb_posts ); | |
| 189 | - //$filter->fields = array_merge( $columns_table_posts, $filter->fields ); | |
| 190 | - $db = CourseJsonDB::getInstance(); | |
| 191 | - | |
| 192 | - $filter_user_course = clone $filter; | |
| 193 | - $filter_course_not_attend = clone $filter; | |
| 194 | - | |
| 195 | - // Query get users total attend courses | |
| 196 | - $fields_user_course_require = [ 'c.ID', 'COUNT(c.ID) AS total' ]; | |
| 197 | - $filter_user_course->fields = array( 'c.ID', 'COUNT(c.ID) AS total' ); | |
| 198 | - if ( ! empty( $filter_user_course->only_fields ) ) { | |
| 199 | - $pattern = '#ID.*#'; | |
| 200 | - foreach ( $filter_user_course->only_fields as $k => $field ) { | |
| 201 | - if ( preg_match( $pattern, $field ) ) { | |
| 202 | - unset( $filter_user_course->only_fields[ $k ] ); | |
| 203 | - } | |
| 204 | - } | |
| 205 | - $filter_user_course->only_fields = array_unique( array_merge( $filter_user_course->only_fields, $fields_user_course_require ) ); | |
| 206 | - } | |
| 207 | - | |
| 208 | - $filter_user_course->join[] = "INNER JOIN {$this->tb_lp_user_items} AS ui ON c.ID = ui.item_id"; | |
| 209 | - $filter_user_course->where[] = $this->wpdb->prepare( 'AND ui.item_type = %s', LP_COURSE_CPT ); | |
| 210 | - $filter_user_course->where[] = $this->wpdb->prepare( | |
| 211 | - 'AND (status = %s OR status = %s OR status = %s)', | |
| 212 | - LP_COURSE_ENROLLED, | |
| 213 | - LP_COURSE_PURCHASED, | |
| 214 | - LP_COURSE_FINISHED | |
| 215 | - ); | |
| 216 | - $filter_user_course->group_by = 'c.ID'; | |
| 217 | - $filter_user_course->order_by = ''; | |
| 218 | - $filter_user_course->return_string_query = true; | |
| 219 | - | |
| 220 | - $query_user_course = $db->get_courses( $filter_user_course ); | |
| 221 | - | |
| 222 | - // Query get courses not attend | |
| 223 | - $filter_user_course_cl = clone $filter_user_course; | |
| 224 | - $filter_user_course_cl->only_fields = array( 'c.ID' ); | |
| 225 | - $query_user_course_for_not_in = $db->get_courses( $filter_user_course_cl ); | |
| 226 | - | |
| 227 | - $fields_user_course_not_attend_require = [ 'c.ID', '0 AS total' ]; | |
| 228 | - $filter_course_not_attend->fields = [ 'c.ID', '0 AS total' ]; | |
| 229 | - if ( ! empty( $filter_course_not_attend->only_fields ) ) { | |
| 230 | - $pattern = '#ID.*#'; | |
| 231 | - foreach ( $filter_course_not_attend->only_fields as $k => $field ) { | |
| 232 | - if ( preg_match( $pattern, $field ) ) { | |
| 233 | - unset( $filter_course_not_attend->only_fields[ $k ] ); | |
| 234 | - } | |
| 235 | - } | |
| 236 | - $filter_course_not_attend->only_fields = array_unique( array_merge( $filter_course_not_attend->only_fields, $fields_user_course_not_attend_require ) ); | |
| 237 | - } | |
| 238 | - $filter_course_not_attend->where[] = 'AND c.ID NOT IN(' . $query_user_course_for_not_in . ')'; | |
| 239 | - | |
| 240 | - $filter_course_not_attend->order_by = ''; | |
| 241 | - $filter_course_not_attend->return_string_query = true; | |
| 242 | - | |
| 243 | - $query_course_not_attend = $db->get_courses( $filter_course_not_attend ); | |
| 244 | - | |
| 245 | - $filter->union[] = $query_user_course; | |
| 246 | - $filter->union[] = $query_course_not_attend; | |
| 247 | - $filter->order_by = 'total'; | |
| 248 | - $filter->order = 'DESC'; | |
| 249 | - | |
| 250 | - return $filter; | |
| 251 | - } | |
| 252 | - | |
| 253 | - /** | |
| 254 | - * Get list courses is on feature | |
| 255 | - * | |
| 256 | - * @param CourseJsonFilter $filter | |
| 257 | - * | |
| 258 | - * @return CourseJsonFilter | |
| 259 | - * @author tungnx | |
| 260 | - * @version 1.0.0 | |
| 261 | - * @since 4.1.5 | |
| 262 | - */ | |
| 263 | - public function get_courses_in_feature( CourseJsonFilter &$filter ): CourseJsonFilter { | |
| 264 | - $filter->join[] = "INNER JOIN $this->tb_postmeta AS pmf ON c.ID = pmf.post_id"; | |
| 265 | - $filter->where[] = $this->wpdb->prepare( 'AND pmf.meta_key = %s', '_lp_featured' ); | |
| 266 | - $filter->where[] = $this->wpdb->prepare( 'AND pmf.meta_value = %s', 'yes' ); | |
| 267 | - | |
| 268 | - return $filter; | |
| 269 | - } | |
| 270 | - | |
| 271 | - /** | |
| 272 | - * Get total courses of Author | |
| 273 | - * | |
| 274 | - * @param int $author_id | |
| 275 | - * @param array $status | |
| 276 | - * | |
| 277 | - * @return CourseJsonFilter | |
| 278 | - * @since 4.3.4 Clone from LP_Course_DB::count_courses_of_author | |
| 279 | - * @version 1.0.0 | |
| 280 | - */ | |
| 281 | - public function count_courses_of_author( int $author_id, array $status = [] ): CourseJsonFilter { | |
| 282 | - $filter = new CourseJsonFilter(); | |
| 283 | - $filter->only_fields = array( 'ID' ); | |
| 284 | - $filter->post_author = $author_id; | |
| 285 | - $filter->post_status = $status; | |
| 286 | - if ( empty( $status ) ) { | |
| 287 | - $filter->post_status = []; | |
| 288 | - } | |
| 289 | - $filter->field_count = 'ID'; | |
| 290 | - $filter->query_count = true; | |
| 291 | - | |
| 292 | - return apply_filters( 'lp/user/course/query/filter/count-courses-of-author', $filter ); | |
| 293 | - } | |
| 294 | -} | |
| 1 | +<?php | |
| 2 | + | |
| 3 | +namespace LearnPress\Databases\Course; | |
| 4 | + | |
| 5 | +use Exception; | |
| 6 | +use LearnPress\Databases\DataBase; | |
| 7 | +use LearnPress\Filters\Course\CourseJsonFilter; | |
| 8 | +use LP_Helper; | |
| 9 | + | |
| 10 | +defined( 'ABSPATH' ) || exit(); | |
| 11 | + | |
| 12 | +/** | |
| 13 | + * Class CourseJsonDB | |
| 14 | + * | |
| 15 | + * Move from LP_Course_JSON_DB to here | |
| 16 | + * | |
| 17 | + * @since 4.3.2.3 | |
| 18 | + * @version 1.0.1 | |
| 19 | + */ | |
| 20 | +class CourseJsonDB extends DataBase { | |
| 21 | + private static $_instance; | |
| 22 | + | |
| 23 | + protected function __construct() { | |
| 24 | + parent::__construct(); | |
| 25 | + } | |
| 26 | + | |
| 27 | + public static function getInstance() { | |
| 28 | + if ( is_null( self::$_instance ) ) { | |
| 29 | + self::$_instance = new self(); | |
| 30 | + } | |
| 31 | + | |
| 32 | + return self::$_instance; | |
| 33 | + } | |
| 34 | + | |
| 35 | + /** | |
| 36 | + * Get Courses | |
| 37 | + * | |
| 38 | + * @param CourseJsonFilter $filter | |
| 39 | + * @param int $total_rows return total_rows | |
| 40 | + * | |
| 41 | + * @return array|int|string|null | |
| 42 | + * @throws Exception | |
| 43 | + * @version 1.0.1 | |
| 44 | + * @since 4.2.6.9 | |
| 45 | + */ | |
| 46 | + public function get_courses( CourseJsonFilter $filter, int &$total_rows = 0 ) { | |
| 47 | + $filter->fields = array_merge( $filter->all_fields, $filter->fields ); | |
| 48 | + | |
| 49 | + if ( empty( $filter->collection ) ) { | |
| 50 | + $filter->collection = $this->tb_lp_courses; | |
| 51 | + } | |
| 52 | + | |
| 53 | + if ( empty( $filter->collection_alias ) ) { | |
| 54 | + $filter->collection_alias = 'c'; | |
| 55 | + } | |
| 56 | + | |
| 57 | + $ca = $filter->collection_alias; | |
| 58 | + | |
| 59 | + // Find ID | |
| 60 | + if ( ! empty( $filter->ID ) ) { | |
| 61 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.ID = %d", $filter->ID ); | |
| 62 | + } | |
| 63 | + | |
| 64 | + // Status | |
| 65 | + $filter->post_status = (array) $filter->post_status; | |
| 66 | + if ( ! empty( $filter->post_status ) ) { | |
| 67 | + $post_status_format = LP_Helper::db_format_array( $filter->post_status, '%s' ); | |
| 68 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.post_status IN (" . $post_status_format . ')', $filter->post_status ); | |
| 69 | + } | |
| 70 | + | |
| 71 | + // Exclude status auto-draft, because Admin of WP not show auto-draft in list post, so all front-end and back-end of LP also not show auto-draft | |
| 72 | + if ( ! in_array( 'auto-draft', $filter->post_status, true ) ) { | |
| 73 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.post_status != %s", 'auto-draft' ); | |
| 74 | + } | |
| 75 | + | |
| 76 | + // Term ids | |
| 77 | + if ( ! empty( $filter->term_ids ) ) { | |
| 78 | + // Sanitize term ids | |
| 79 | + $filter->term_ids = array_map( 'absint', $filter->term_ids ); | |
| 80 | + $term_ids_format = join( ',', $filter->term_ids ); | |
| 81 | + $filter->join[] = "INNER JOIN $this->tb_term_relationships AS r_term_p ON $ca.ID = r_term_p.object_id"; | |
| 82 | + $filter->join[] = "INNER JOIN $this->tb_term_taxonomy AS tx_p ON r_term_p.term_taxonomy_id = tx_p.term_taxonomy_id"; | |
| 83 | + $filter->where[] = "AND r_term_p.term_taxonomy_id IN ($term_ids_format)"; | |
| 84 | + $filter->where[] = $this->wpdb->prepare( 'AND tx_p.taxonomy = %s', $filter->taxonomy ); | |
| 85 | + } | |
| 86 | + | |
| 87 | + // Course ids | |
| 88 | + if ( ! empty( $filter->ids ) ) { | |
| 89 | + $ids = array_map( 'absint', $filter->ids ); | |
| 90 | + $ids_format = join( ',', $ids ); | |
| 91 | + $filter->where[] = "AND $ca.ID IN ($ids_format)"; | |
| 92 | + } | |
| 93 | + | |
| 94 | + // Title | |
| 95 | + if ( ! empty( $filter->post_title ) ) { | |
| 96 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.post_title LIKE %s", '%' . $filter->post_title . '%' ); | |
| 97 | + } | |
| 98 | + | |
| 99 | + // Name(slug) | |
| 100 | + if ( ! empty( $filter->post_name ) ) { | |
| 101 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.post_name = %s", $filter->post_name ); | |
| 102 | + } | |
| 103 | + | |
| 104 | + // Author | |
| 105 | + if ( ! empty( $filter->post_author ) ) { | |
| 106 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.post_author = %d", $filter->post_author ); | |
| 107 | + } | |
| 108 | + | |
| 109 | + // Authors | |
| 110 | + if ( ! empty( $filter->post_authors ) ) { | |
| 111 | + $post_authors_format = LP_Helper::db_format_array( $filter->post_authors, '%d' ); | |
| 112 | + $filter->where[] = $this->wpdb->prepare( "AND $ca.post_author IN (" . $post_authors_format . ')', $filter->post_authors ); | |
| 113 | + } | |
| 114 | + | |
| 115 | + /** | |
| 116 | + * Type price | |
| 117 | + * | |
| 118 | + * if 'free' and 'paid' are not in the array or not null, we will filter the price | |
| 119 | + */ | |
| 120 | + if ( ! empty( $filter->type_price ) && | |
| 121 | + ( ! in_array( 'free', $filter->type_price ) && ! in_array( 'paid', $filter->type_price ) ) ) { | |
| 122 | + foreach ( $filter->type_price as $type_price ) { | |
| 123 | + switch ( $type_price ) { | |
| 124 | + case 'free': | |
| 125 | + $filter->where[] = 'AND price_to_sort = 0'; | |
| 126 | + break; | |
| 127 | + case 'paid': | |
| 128 | + $filter->where[] = 'AND price_to_sort > 0'; | |
| 129 | + break; | |
| 130 | + } | |
| 131 | + } | |
| 132 | + } | |
| 133 | + | |
| 134 | + // Is sale | |
| 135 | + if ( ! empty( $filter->is_sale ) ) { | |
| 136 | + $filter->where[] = 'AND is_sale = 1'; | |
| 137 | + } | |
| 138 | + | |
| 139 | + // Is feature | |
| 140 | + if ( ! empty( $filter->is_feature ) ) { | |
| 141 | + $filter = $this->get_courses_in_feature( $filter ); | |
| 142 | + } | |
| 143 | + | |
| 144 | + // Levels | |
| 145 | + if ( ! empty( $filter->levels ) ) { | |
| 146 | + $filter->join[] = "INNER JOIN $this->tb_postmeta AS pml ON c.ID = pml.post_id"; | |
| 147 | + $filter->where[] = $this->wpdb->prepare( 'AND pml.meta_key = %s', '_lp_level' ); | |
| 148 | + $levels_format = LP_Helper::db_format_array( $filter->levels, '%s' ); | |
| 149 | + $filter->where[] = $this->wpdb->prepare( 'AND pml.meta_value IN (' . $levels_format . ')', $filter->levels ); | |
| 150 | + } | |
| 151 | + | |
| 152 | + // Course type | |
| 153 | + if ( ! empty( $filter->type ) && $filter->type !== 'all' ) { | |
| 154 | + if ( $filter->type === 'offline' ) { | |
| 155 | + $filter->join[] = "INNER JOIN $this->tb_postmeta AS pm_off ON c.ID = pm_off.post_id"; | |
| 156 | + $filter->where[] = $this->wpdb->prepare( 'AND pm_off.meta_key = %s', '_lp_offline_course' ); | |
| 157 | + $filter->where[] = $this->wpdb->prepare( 'AND pm_off.meta_value = %s', 'yes' ); | |
| 158 | + } else { | |
| 159 | + $filter->where[] = $this->wpdb->prepare( | |
| 160 | + "AND c.ID NOT IN | |
| 161 | + ( SELECT id FROM $this->tb_posts as p1 | |
| 162 | + INNER JOIN $this->tb_postmeta as pm_ol on p1.ID = pm_ol.post_id | |
| 163 | + WHERE pm_ol.meta_key = %s AND pm_ol.meta_value = %s )", | |
| 164 | + '_lp_offline_course', | |
| 165 | + 'yes' | |
| 166 | + ); | |
| 167 | + } | |
| 168 | + } | |
| 169 | + | |
| 170 | + $filter = apply_filters( 'lp/courses-json/query/filter', $filter ); | |
| 171 | + | |
| 172 | + return $this->execute( $filter, $total_rows ); | |
| 173 | + } | |
| 174 | + | |
| 175 | + /** | |
| 176 | + * Get list courses is on popular | |
| 177 | + * Use "UNION" to merge 2 query | |
| 178 | + * | |
| 179 | + * @param CourseJsonFilter $filter | |
| 180 | + * | |
| 181 | + * @return CourseJsonFilter | |
| 182 | + * @throws Exception | |
| 183 | + * @version 1.0.9 | |
| 184 | + * @since 4.3.2.3 | |
| 185 | + */ | |
| 186 | + public function get_courses_order_by_popular( CourseJsonFilter &$filter ): CourseJsonFilter { | |
| 187 | + // Set list name columns get | |
| 188 | + //$columns_table_posts = $this->get_cols_of_table( $this->tb_posts ); | |
| 189 | + //$filter->fields = array_merge( $columns_table_posts, $filter->fields ); | |
| 190 | + $db = CourseJsonDB::getInstance(); | |
| 191 | + | |
| 192 | + $filter_user_course = clone $filter; | |
| 193 | + $filter_course_not_attend = clone $filter; | |
| 194 | + | |
| 195 | + // Query get users total attend courses | |
| 196 | + $fields_user_course_require = [ 'c.ID', 'COUNT(c.ID) AS total' ]; | |
| 197 | + $filter_user_course->fields = array( 'c.ID', 'COUNT(c.ID) AS total' ); | |
| 198 | + if ( ! empty( $filter_user_course->only_fields ) ) { | |
| 199 | + $pattern = '#ID.*#'; | |
| 200 | + foreach ( $filter_user_course->only_fields as $k => $field ) { | |
| 201 | + if ( preg_match( $pattern, $field ) ) { | |
| 202 | + unset( $filter_user_course->only_fields[ $k ] ); | |
| 203 | + } | |
| 204 | + } | |
| 205 | + $filter_user_course->only_fields = array_unique( array_merge( $filter_user_course->only_fields, $fields_user_course_require ) ); | |
| 206 | + } | |
| 207 | + | |
| 208 | + $filter_user_course->join[] = "INNER JOIN {$this->tb_lp_user_items} AS ui ON c.ID = ui.item_id"; | |
| 209 | + $filter_user_course->where[] = $this->wpdb->prepare( 'AND ui.item_type = %s', LP_COURSE_CPT ); | |
| 210 | + $filter_user_course->where[] = $this->wpdb->prepare( | |
| 211 | + 'AND (status = %s OR status = %s OR status = %s)', | |
| 212 | + LP_COURSE_ENROLLED, | |
| 213 | + LP_COURSE_PURCHASED, | |
| 214 | + LP_COURSE_FINISHED | |
| 215 | + ); | |
| 216 | + $filter_user_course->group_by = 'c.ID'; | |
| 217 | + $filter_user_course->order_by = ''; | |
| 218 | + $filter_user_course->return_string_query = true; | |
| 219 | + | |
| 220 | + $query_user_course = $db->get_courses( $filter_user_course ); | |
| 221 | + | |
| 222 | + // Query get courses not attend | |
| 223 | + $filter_user_course_cl = clone $filter_user_course; | |
| 224 | + $filter_user_course_cl->only_fields = array( 'c.ID' ); | |
| 225 | + $query_user_course_for_not_in = $db->get_courses( $filter_user_course_cl ); | |
| 226 | + | |
| 227 | + $fields_user_course_not_attend_require = [ 'c.ID', '0 AS total' ]; | |
| 228 | + $filter_course_not_attend->fields = [ 'c.ID', '0 AS total' ]; | |
| 229 | + if ( ! empty( $filter_course_not_attend->only_fields ) ) { | |
| 230 | + $pattern = '#ID.*#'; | |
| 231 | + foreach ( $filter_course_not_attend->only_fields as $k => $field ) { | |
| 232 | + if ( preg_match( $pattern, $field ) ) { | |
| 233 | + unset( $filter_course_not_attend->only_fields[ $k ] ); | |
| 234 | + } | |
| 235 | + } | |
| 236 | + $filter_course_not_attend->only_fields = array_unique( array_merge( $filter_course_not_attend->only_fields, $fields_user_course_not_attend_require ) ); | |
| 237 | + } | |
| 238 | + $filter_course_not_attend->where[] = 'AND c.ID NOT IN(' . $query_user_course_for_not_in . ')'; | |
| 239 | + | |
| 240 | + $filter_course_not_attend->order_by = ''; | |
| 241 | + $filter_course_not_attend->return_string_query = true; | |
| 242 | + | |
| 243 | + $query_course_not_attend = $db->get_courses( $filter_course_not_attend ); | |
| 244 | + | |
| 245 | + $filter->union[] = $query_user_course; | |
| 246 | + $filter->union[] = $query_course_not_attend; | |
| 247 | + $filter->order_by = 'total'; | |
| 248 | + $filter->order = 'DESC'; | |
| 249 | + | |
| 250 | + return $filter; | |
| 251 | + } | |
| 252 | + | |
| 253 | + /** | |
| 254 | + * Get list courses is on feature | |
| 255 | + * | |
| 256 | + * @param CourseJsonFilter $filter | |
| 257 | + * | |
| 258 | + * @return CourseJsonFilter | |
| 259 | + * @author tungnx | |
| 260 | + * @version 1.0.0 | |
| 261 | + * @since 4.1.5 | |
| 262 | + */ | |
| 263 | + public function get_courses_in_feature( CourseJsonFilter &$filter ): CourseJsonFilter { | |
| 264 | + $filter->join[] = "INNER JOIN $this->tb_postmeta AS pmf ON c.ID = pmf.post_id"; | |
| 265 | + $filter->where[] = $this->wpdb->prepare( 'AND pmf.meta_key = %s', '_lp_featured' ); | |
| 266 | + $filter->where[] = $this->wpdb->prepare( 'AND pmf.meta_value = %s', 'yes' ); | |
| 267 | + | |
| 268 | + return $filter; | |
| 269 | + } | |
| 270 | + | |
| 271 | + /** | |
| 272 | + * Get total courses of Author | |
| 273 | + * | |
| 274 | + * @param int $author_id | |
| 275 | + * @param array $status | |
| 276 | + * | |
| 277 | + * @return CourseJsonFilter | |
| 278 | + * @since 4.3.4 Clone from LP_Course_DB::count_courses_of_author | |
| 279 | + * @version 1.0.0 | |
| 280 | + */ | |
| 281 | + public function count_courses_of_author( int $author_id, array $status = [] ): CourseJsonFilter { | |
| 282 | + $filter = new CourseJsonFilter(); | |
| 283 | + $filter->only_fields = array( 'ID' ); | |
| 284 | + $filter->post_author = $author_id; | |
| 285 | + $filter->post_status = $status; | |
| 286 | + if ( empty( $status ) ) { | |
| 287 | + $filter->post_status = []; | |
| 288 | + } | |
| 289 | + $filter->field_count = 'ID'; | |
| 290 | + $filter->query_count = true; | |
| 291 | + | |
| 292 | + return apply_filters( 'lp/user/course/query/filter/count-courses-of-author', $filter ); | |
| 293 | + } | |
| 294 | +} | |
