PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 6.2.2
MainWP Dashboard: Self-hosted WordPress Management for Agencies v6.2.2
6.2.3 6.2.2 6.2.1 6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 5.2 All 156 releases
← All changes | modules/cost-tracker/classes/class-cost-tracker-db.php +363 -67 5.1.1 → 6.2.2 View file →
@@ -13,8 +13,13 @@
13 13 use MainWP\Dashboard\MainWP_DB;
14 14 use MainWP\Dashboard\MainWP_DB_Client;
15 15 use MainWP\Dashboard\MainWP_Exception;
16 16
17 +// Exit if accessed directly.
18 +if ( ! defined( 'ABSPATH' ) ) {
19 + exit;
20 +}
21 +
17 22 /**
18 23 * Class Cost_Tracker_DB
19 24 */
20 25 class Cost_Tracker_DB extends MainWP_Install {
@@ -23,9 +28,9 @@
23 28 * Variable to hold the db version.
24 29 *
25 30 * @var string Version.
26 31 */
27 - private $cost_tracker_db_version = '1.0.16';
32 + private $cost_tracker_db_version = '1.0.18';
28 33
29 34
30 35 /**
31 36 * Static variable to hold the single instance of the class.
@@ -92,8 +97,9 @@
92 97 `price` decimal(26,8) NOT NULL,
93 98 `renewal_type` varchar(20) NOT NULL,
94 99 `last_renewal` int(11) NOT NULL,
95 100 `next_renewal` int(11) NOT NULL,
101 +`next_renewal_today` int(11) NOT NULL,
96 102 `last_alert` int(11) NOT NULL,
97 103 `cost_icon` varchar(64) NOT NULL DEFAULT "",
98 104 `cost_color` varchar(64) NOT NULL DEFAULT "",
99 105 `sites` text NOT NULL,
@@ -126,12 +132,14 @@
126 132 */
127 133 public function update_db_cost( $current_version ) { //phpcs:ignore -- NOSONAR - complex.
128 134 if ( ! empty( $current_version ) ) {
129 135 if ( version_compare( $current_version, '1.0.8', '<' ) ) {
130 - $this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'cost_tracker' ) . ' MODIFY COLUMN price decimal(26,8)' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
136 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
137 + $this->wpdb->query( "ALTER TABLE {$table} MODIFY COLUMN price decimal(26,8)" ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd and a table name cannot be a prepare() placeholder.
131 138 }
132 139 if ( version_compare( $current_version, '1.0.9', '<' ) ) {
133 - $this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'cost_tracker' ) . ' DROP COLUMN author' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
140 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
141 + $this->wpdb->query( "ALTER TABLE {$table} DROP COLUMN author" ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd and a table name cannot be a prepare() placeholder.
134 142 }
135 143
136 144 if ( version_compare( $current_version, '1.0.13', '<' ) ) {
137 145 $costs = $this->get_cost_tracker_by( 'all' );
@@ -228,19 +236,15 @@
228 236 unset( $update['id'] );
229 237 }
230 238
231 239 if ( ! empty( $id ) ) {
232 -
240 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- write operation; caching not applicable for UPDATE.
233 241 $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
242 + $this->invalidate_cost_tracker_caches( $id );
234 243 return $this->get_cost_tracker_by( 'id', $id );
235 - } else {
236 - if ( isset( $update['id'] ) ) {
237 - unset( $update['id'] );
238 - }
239 -
240 - if ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
241 - return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
242 - }
244 + } elseif ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
245 + $this->invalidate_cost_tracker_caches();
246 + return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
243 247 }
244 248 return false;
245 249 }
246 250
@@ -259,26 +263,15 @@
259 263 if ( empty( $item_id ) ) {
260 264 return false;
261 265 }
262 266
263 - $obj_name = '';
267 + $selected_lists = array(
268 + 'site' => is_array( $selected_sites ) ? $selected_sites : array(),
269 + 'tag' => is_array( $selected_groups ) ? $selected_groups : array(),
270 + 'client' => is_array( $selected_clients ) ? $selected_clients : array(),
271 + );
264 272
265 - $new_obj_ids = array();
266 -
267 - if ( ! empty( $selected_sites ) ) {
268 - $obj_name = 'site';
269 - $new_obj_ids = $selected_sites;
270 - } elseif ( ! empty( $selected_groups ) ) {
271 - $obj_name = 'tag';
272 - $new_obj_ids = $selected_groups;
273 - } elseif ( ! empty( $selected_clients ) ) {
274 - $obj_name = 'client';
275 - $new_obj_ids = $selected_clients;
276 - }
277 -
278 - if ( ! empty( $obj_name ) ) {
279 - static::get_instance()->update_lookup_cost( $item_id, $obj_name, $new_obj_ids );
280 - } else {
273 + if ( ! array_filter( $selected_lists ) ) {
281 274 // to support saving cost without selected sites, tags, clients.
282 275 MainWP_DB::instance()->delete_lookup_items(
283 276 'object_name',
284 277 array(
@@ -286,10 +279,17 @@
286 279 'item_id' => $item_id,
287 280 'object_names' => array( 'site', 'tag', 'client' ),
288 281 )
289 282 );
283 + return true;
284 + }
290 285
286 + // A cost can be saved against sites, tags and clients at once, so each list is
287 + // synced against its own object name; an empty list prunes its own rows.
288 + foreach ( $selected_lists as $obj_name => $new_obj_ids ) {
289 + static::get_instance()->update_lookup_cost( $item_id, $obj_name, $new_obj_ids );
291 290 }
291 +
292 292 return true;
293 293 }
294 294
295 295 /**
@@ -308,10 +308,8 @@
308 308 if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
309 309 return false;
310 310 }
311 311
312 - $remove_obj_names = array_diff( $allows, array( $obj_name ) );
313 -
314 312 $found_look_ids = array();
315 313 $existed_look_id = array();
316 314
317 315 $results = MainWP_DB::instance()->get_lookup_items( 'cost', $item_id, $obj_name );
@@ -339,20 +337,8 @@
339 337 $remove_ids = array_diff( $found_look_ids, $new_look_ids );
340 338 if ( $remove_ids ) {
341 339 MainWP_DB::instance()->delete_lookup_items( 'lookup_id', array( 'lookup_ids' => $remove_ids ) );
342 340 }
343 -
344 - if ( ! empty( $remove_obj_names ) ) {
345 - MainWP_DB::instance()->delete_lookup_items(
346 - 'object_name',
347 - array(
348 - 'item_name' => 'cost',
349 - 'item_id' => $item_id,
350 - 'object_names' => $remove_obj_names,
351 -
352 - )
353 - );
354 - }
355 341 }
356 342
357 343 /**
358 344 * Method get_cost_tracker_by().
@@ -369,8 +355,58 @@
369 355 if ( ! is_array( $params ) ) {
370 356 $params = array();
371 357 }
372 358
359 + $s = '';
360 + $exclude = array();
361 + $include = array();
362 + $status = array();
363 + $types = array();
364 +
365 + $where = '';
366 + $limit = '';
367 +
368 + if ( $params && is_array( $params ) ) {
369 + $s = isset( $params['s'] ) ? $params['s'] : '';
370 + $exclude = isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
371 + $include = isset( $params['include'] ) && ! empty( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
372 +
373 + $status = isset( $params['status'] ) && ! empty( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
374 + $product_type = isset( $params['category'] ) && ! empty( $params['category'] ) ? wp_parse_list( $params['category'] ) : array();
375 + $types = isset( $params['type'] ) && ! empty( $params['type'] ) ? wp_parse_list( $params['type'] ) : array();
376 +
377 + $page = isset( $params['paged'] ) ? intval( $params['paged'] ) : false;
378 + $per_page = isset( $params['items_per_page'] ) ? intval( $params['items_per_page'] ) : false;
379 +
380 + if ( ! empty( $s ) ) {
381 + $where .= ' AND ( ct.id LIKE "%' . $this->escape( $s ) . '%" OR ct.name LIKE "%' . $this->escape( $s ) . '%" ) ';
382 + }
383 +
384 + if ( ! empty( $exclude ) ) {
385 + $where .= ' AND ct.id NOT IN (' . implode( ',', $exclude ) . ') ';
386 + }
387 +
388 + if ( ! empty( $include ) ) {
389 + $where .= ' AND ct.id IN (' . implode( ',', $include ) . ') ';
390 + }
391 +
392 + if ( ! empty( $status ) ) {
393 + $where .= ' AND ct.cost_status IN (' . $this->prepare_fields_array( $status ) . ') ';
394 + }
395 +
396 + if ( ! empty( $product_type ) && ! in_array( 'any', $product_type ) ) {
397 + $where .= ' AND ct.product_type IN (' . $this->prepare_fields_array( $product_type ) . ') ';
398 + }
399 +
400 + if ( ! empty( $types ) && ! in_array( 'any', $types ) ) {
401 + $where .= ' AND ct.type IN (' . $this->prepare_fields_array( $types ) . ') ';
402 + }
403 +
404 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
405 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
406 + }
407 + }
408 +
373 409 $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
374 410 $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
375 411
376 412 if ( is_array( $selected_ids ) ) {
@@ -379,46 +415,99 @@
379 415 $selected_ids = array();
380 416 }
381 417
382 418 if ( 'all' === $by ) {
383 - $where = '';
384 419 if ( ! empty( $selected_ids ) ) {
385 - $where = ' AND id IN (' . implode( ',', $selected_ids ) . ') ';
420 + $where .= ' AND ct.id IN (' . implode( ',', $selected_ids ) . ') ';
386 421 }
387 - return $wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE 1 ' . $where ); //phpcs:ignore -- good.
422 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
423 + // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is escaped with esc_sql(); $where holds id lists from wp_parse_id_list() and values escaped through prepare_fields_array(); $limit is built from intval()ed paging.
424 + return $wpdb->get_results( "SELECT * FROM {$table} ct WHERE 1 {$where}{$limit}" );
388 425 }
389 426
427 + $where = '';
428 + $limit = '';
429 +
390 430 $sql = '';
391 431 if ( 'id' === $by && is_numeric( $value ) ) {
392 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id`=%d ', $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
393 - return $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
432 + $cache_key = $this->get_cache_key_for_cost_tracker( $by, $value, $params );
433 + $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
434 + if ( false !== $cached ) {
435 + return $cached;
436 + }
437 +
438 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
439 + $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `id`=%d ", $value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
440 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
441 + $result = $wpdb->get_row( $sql, OBJECT );
442 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
443 + return $result;
394 444 } elseif ( 'count' === $by ) {
395 - $sql = 'SELECT count(*) FROM ' . $this->table_name( 'cost_tracker' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
396 - return $wpdb->get_var( $sql ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
445 + $cache_key = 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
446 + wp_json_encode(
447 + array(
448 + 'method' => 'get_cost_tracker_by',
449 + 'by' => 'count',
450 + )
451 + )
452 + );
453 + $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
454 + if ( false !== $cached ) {
455 + return $cached;
456 + }
457 +
458 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
459 + $sql = "SELECT count(*) FROM {$table}";
460 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with static table name that is escaped with esc_sql(); no dynamic input.
461 + $result = $wpdb->get_var( $sql );
462 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
463 + return $result;
397 464 } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
398 465 $cost_ids = explode( ',', $value );
399 466 $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
400 467 if ( ! empty( $cost_ids ) ) {
401 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id` IN (' . implode( ',', $cost_ids ) . ' )';
468 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
469 + $sql = "SELECT * FROM {$table} WHERE `id` IN (" . implode( ',', $cost_ids ) . ' )';
402 470 }
403 471 } elseif ( 'site_id' === $by || 'client_id' === $by ) {
404 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
472 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
473 + $sql = "SELECT * FROM {$table}";
405 474 } elseif ( 'slug' === $by && is_string( $value ) ) {
406 475 if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
407 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `slug`=%s AND product_type = %s ', $value, $product_type ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
408 - return $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
476 + $cache_key = $this->get_cache_key_for_cost_tracker( $by, $value, array( 'product_type' => $product_type ) );
477 + $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
478 + if ( false !== $cached ) {
479 + return $cached;
480 + }
481 +
482 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
483 + $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `slug`=%s AND product_type = %s ", $value, $product_type ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
484 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
485 + $result = $wpdb->get_row( $sql, OBJECT );
486 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
487 + return $result;
409 488 } else {
410 489 $where = '';
411 490 if ( ! empty( $product_type ) ) {
412 491 $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
413 492 }
414 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `slug`=%s ' . $where, $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
493 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
494 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; $where is itself from $wpdb->prepare(), so concatenation into prepare is safe here.
495 + $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `slug`=%s " . $where, $value );
415 496 }
416 497 }
417 498
418 499 $data = array();
419 500 if ( ! empty( $sql ) ) {
420 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
501 + $cache_key = $this->get_cache_key_for_cost_tracker( $by, $value, $params );
502 + $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
503 + if ( false !== $cached ) {
504 + return $cached;
505 + }
506 +
507 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
508 + $result = $wpdb->get_results( $sql, OBJECT );
509 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
421 510 if ( $result ) {
422 511 if ( 'site_id' === $by ) {
423 512 $site_id = intval( $value );
424 513 foreach ( $result as $cost ) {
@@ -453,8 +542,22 @@
453 542 }
454 543 return $data;
455 544 }
456 545
546 + /**
547 + * Method prepare_fields_array().
548 + *
549 + * @param array $values array of string.
550 + *
551 + * @return string Escaped string result.
552 + */
553 + public function prepare_fields_array( $values = array() ) {
554 + $tmp = '';
555 + foreach ( $values as $value ) {
556 + $tmp .= '"' . $this->escape( $value ) . '",';
557 + }
558 + return rtrim( $tmp, ',' );
559 + }
457 560
458 561 /**
459 562 * Method delete_cost_tracker().
460 563 *
@@ -475,19 +578,39 @@
475 578 }
476 579 $deleted = false;
477 580
478 581 if ( 'id' === $by ) {
479 - if ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
582 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
583 + if ( $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE id=%d ", $value ) ) ) { //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- delete operation that invalidates caches below; table name is esc_sql'd.
480 584 $deleted = true;
481 585 }
482 - } elseif ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE site_id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
483 - $deleted = true;
586 + } else {
587 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
588 + if ( $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE site_id=%d ", $value ) ) ) { //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- delete operation that invalidates caches below; table name is esc_sql'd.
589 + $deleted = true;
590 + }
484 591 }
485 592
486 593 if ( $deleted ) {
487 - $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'lookup_item_objects' ) . ' WHERE item_id=%d AND item_name = "cost"', $value ) ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
594 + $lookup_table = esc_sql( $this->table_name( 'lookup_item_objects' ) );
595 + $wpdb->query( $wpdb->prepare( "DELETE FROM {$lookup_table} WHERE item_id=%d AND item_name = \"cost\"", $value ) ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- delete operation; caching is not applicable for write operations; table name is esc_sql'd.
596 + $this->invalidate_cost_tracker_caches( 'id' === $by ? $value : null );
488 597 }
489 598
599 + $snapshot_info = Cost_Tracker_Admin::get_costs_snapshot_info();
600 +
601 + /**
602 + * Action mainwp_cost_tracker_deleted.
603 + *
604 + * @param string $by Delete by.
605 + * @param mixed $value Value.
606 + * @param bool $deleted Deleted.
607 + * @param array $snapshot_info costs snapshot info.
608 + *
609 + * @since 6.0
610 + */
611 + do_action( 'mainwp_cost_tracker_deleted', $by, $value, $deleted, $snapshot_info );
612 +
490 613 return $deleted;
491 614 }
492 615
493 616 /**
@@ -517,9 +640,9 @@
517 640
518 641 $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $client_ids );
519 642 if ( $client_sites ) {
520 643 foreach ( $client_sites as $website ) {
521 - if ( ! empty( $website->client_id ) ) {
644 + if ( empty( $website->client_id ) ) {
522 645 continue;
523 646 }
524 647 if ( ! isset( $clients_site_ids[ $website->client_id ] ) ) {
525 648 $clients_site_ids[ $website->client_id ] = array();
@@ -531,10 +654,20 @@
531 654 global $wpdb;
532 655
533 656 $clients_costs = array();
534 657 $clients_costs_ids = array();
535 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
536 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- get all cost trackers.
658 +
659 + $cache_key = 'mainwp_cost_tracker_all';
660 + $result = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
661 +
662 + if ( false === $result ) {
663 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
664 + $sql = "SELECT * FROM {$table}";
665 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql(); results cached immediately after retrieval
666 + $result = $wpdb->get_results( $sql, OBJECT );
667 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
668 + }
669 +
537 670 if ( $result ) {
538 671 foreach ( $result as $cost ) {
539 672 $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
540 673 $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
@@ -628,9 +761,51 @@
628 761
629 762 return $clients_costs;
630 763 }
631 764
765 + /**
766 + * Method get_sites_of_cost().
767 + *
768 + * Get sites of cost.
769 + *
770 + * @param object $cost Cost data.
771 + *
772 + * @return mixed Result
773 + */
774 + public function get_sites_of_cost( $cost ) { //phpcs:ignore -- NOSONAR - complex method.
632 775
776 + if ( empty( $cost ) || ! is_object( $cost ) ) {
777 + return array();
778 + }
779 +
780 + $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array(); // NOSONAR -- duplicate.
781 + $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array(); // NOSONAR -- duplicate.
782 + $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array(); // NOSONAR -- duplicate.
783 +
784 + if ( ! is_array( $sites ) ) { // NOSONAR -- duplicate.
785 + $sites = array(); // NOSONAR -- duplicate.
786 + }
787 + if ( ! is_array( $groups ) ) { // NOSONAR -- duplicate.
788 + $groups = array(); // NOSONAR -- duplicate.
789 + }
790 + if ( ! is_array( $clients ) ) { // NOSONAR -- duplicate.
791 + $clients = array(); // NOSONAR -- duplicate.
792 + }
793 +
794 + if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) { // NOSONAR -- duplicate.
795 + return array(); // NOSONAR -- duplicate.
796 + }
797 +
798 + $params = array( // NOSONAR -- duplicate.
799 + 'sites' => $sites, // NOSONAR -- duplicate.
800 + 'groups' => $groups, // NOSONAR -- duplicate.
801 + 'clients' => $clients, // NOSONAR -- duplicate.
802 + );
803 +
804 + return MainWP_DB::instance()->get_db_sites( $params ); // NOSONAR - get sites of cost tracker.
805 + }
806 +
807 +
633 808 /**
634 809 * Method get_all_cost_trackers_by_sites().
635 810 *
636 811 * @param array $sites_ids Sites ids.
@@ -652,10 +827,20 @@
652 827 global $wpdb;
653 828
654 829 $sites_costs = array();
655 830 $sites_costs_ids = array();
656 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
657 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
831 +
832 + $cache_key = 'mainwp_cost_tracker_all';
833 + $result = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
834 +
835 + if ( false === $result ) {
836 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
837 + $sql = "SELECT * FROM {$table}";
838 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql(); results cached immediately after retrieval
839 + $result = $wpdb->get_results( $sql, OBJECT );
840 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
841 + }
842 +
658 843 if ( $result ) {
659 844 foreach ( $result as $cost ) {
660 845 $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
661 846 $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
@@ -749,12 +934,123 @@
749 934 $sum_data = isset( $params['sum_data'] ) ? $params['sum_data'] : '';
750 935 $where = '';
751 936 $sql = '';
752 937 if ( 'all' === $sum_data ) {
938 + $cache_key = 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
939 + wp_json_encode(
940 + array(
941 + 'method' => 'get_summary_data',
942 + 'sum_data' => 'all',
943 + )
944 + )
945 + );
946 + $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
947 + if ( false !== $cached ) {
948 + return $cached;
949 + }
950 +
753 951 $where .= ' AND co.cost_status = "active" AND co.type = "subscription" ';
754 - $sql .= 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' co WHERE 1 ' . $where . ' ORDER BY co.next_renewal ASC ';
952 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
953 + $sql = "SELECT * FROM {$table} co WHERE 1 {$where} ORDER BY co.next_renewal ASC ";
954 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
955 + $result = $wpdb->get_results( $sql );
956 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
957 + return $result;
755 958 } else {
756 959 return false;
757 960 }
758 - return $wpdb->get_results( $sql ); //phpcs:ignore -- good.
961 + }
962 +
963 + /**
964 + * Method update_next_renewal_today()
965 + *
966 + * @return void
967 + */
968 + public function update_next_renewal_today() {
969 +
970 + $costs = $this->get_cost_tracker_by( 'all' );
971 + if ( $costs ) {
972 + foreach ( $costs as $cost ) {
973 + $next_renewal = Cost_Tracker_Admin::get_next_renewal( $cost->last_renewal, $cost->renewal_type );
974 + $next_today = Cost_Tracker_Admin::calc_next_renewal_today( $cost, $next_renewal );
975 + if ( $next_today !== $cost->next_renewal_today ) {
976 + $update = array(
977 + 'id' => $cost->id,
978 + 'next_renewal_today' => $next_today,
979 + );
980 + $this->update_cost_tracker( $update );
981 + }
982 + }
983 + }
984 + update_option( 'module_cost_tracker_calc_today_next_renewal', gmdate( 'Y-m-d' ) );
985 + }
986 +
987 + /**
988 + * Generate cache key for get_cost_tracker_by queries.
989 + *
990 + * @param string $by Query type.
991 + * @param mixed $value Query value.
992 + * @param array $params Additional parameters.
993 + *
994 + * @return string Cache key.
995 + */
996 + private function get_cache_key_for_cost_tracker( $by, $value, $params = array() ) {
997 + $identifier = wp_json_encode(
998 + array(
999 + 'method' => 'get_cost_tracker_by',
1000 + 'by' => $by,
1001 + 'value' => $value,
1002 + 'params' => $params,
1003 + )
1004 + );
1005 + return 'mainwp_cost_tracker_' . md5( $identifier ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1006 + }
1007 +
1008 + /**
1009 + * Invalidate cost tracker caches.
1010 + *
1011 + * @param int|null $cost_id Cost ID to invalidate specific caches, or null for all caches.
1012 + *
1013 + * @return void
1014 + */
1015 + private function invalidate_cost_tracker_caches( $cost_id = null ) {
1016 + wp_cache_delete(
1017 + 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1018 + wp_json_encode(
1019 + array(
1020 + 'method' => 'get_cost_tracker_by',
1021 + 'by' => 'count',
1022 + )
1023 + )
1024 + ),
1025 + 'mainwp_cost_tracker'
1026 + );
1027 + wp_cache_delete( 'mainwp_cost_tracker_all', 'mainwp_cost_tracker' );
1028 + wp_cache_delete(
1029 + 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1030 + wp_json_encode(
1031 + array(
1032 + 'method' => 'get_summary_data',
1033 + 'sum_data' => 'all',
1034 + )
1035 + )
1036 + ),
1037 + 'mainwp_cost_tracker'
1038 + );
1039 +
1040 + if ( $cost_id ) {
1041 + $cost_id = absint( $cost_id );
1042 + wp_cache_delete(
1043 + 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1044 + wp_json_encode(
1045 + array(
1046 + 'method' => 'get_cost_tracker_by',
1047 + 'by' => 'id',
1048 + 'value' => $cost_id,
1049 + )
1050 + )
1051 + ),
1052 + 'mainwp_cost_tracker'
1053 + );
1054 + }
759 1055 }
760 1056 }