PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 6.2.1
MainWP Dashboard: Self-hosted WordPress Management for Agencies v6.2.1
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 5.2.1 All 155 releases
← All changes | modules/cost-tracker/classes/class-cost-tracker-db.php +364 -67 5.1 → 6.2.1 View file →
@@ -11,9 +11,15 @@
11 11 use MainWP\Dashboard\MainWP_Install;
12 12 use MainWP\Dashboard\MainWP_Utility;
13 13 use MainWP\Dashboard\MainWP_DB;
14 14 use MainWP\Dashboard\MainWP_DB_Client;
15 +use MainWP\Dashboard\MainWP_Exception;
15 16
17 +// Exit if accessed directly.
18 +if ( ! defined( 'ABSPATH' ) ) {
19 + exit;
20 +}
21 +
16 22 /**
17 23 * Class Cost_Tracker_DB
18 24 */
19 25 class Cost_Tracker_DB extends MainWP_Install {
@@ -22,9 +28,9 @@
22 28 * Variable to hold the db version.
23 29 *
24 30 * @var string Version.
25 31 */
26 - private $cost_tracker_db_version = '1.0.16';
32 + private $cost_tracker_db_version = '1.0.18';
27 33
28 34
29 35 /**
30 36 * Static variable to hold the single instance of the class.
@@ -91,8 +97,9 @@
91 97 `price` decimal(26,8) NOT NULL,
92 98 `renewal_type` varchar(20) NOT NULL,
93 99 `last_renewal` int(11) NOT NULL,
94 100 `next_renewal` int(11) NOT NULL,
101 +`next_renewal_today` int(11) NOT NULL,
95 102 `last_alert` int(11) NOT NULL,
96 103 `cost_icon` varchar(64) NOT NULL DEFAULT "",
97 104 `cost_color` varchar(64) NOT NULL DEFAULT "",
98 105 `sites` text NOT NULL,
@@ -125,12 +132,14 @@
125 132 */
126 133 public function update_db_cost( $current_version ) { //phpcs:ignore -- NOSONAR - complex.
127 134 if ( ! empty( $current_version ) ) {
128 135 if ( version_compare( $current_version, '1.0.8', '<' ) ) {
129 - $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.
130 138 }
131 139 if ( version_compare( $current_version, '1.0.9', '<' ) ) {
132 - $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.
133 142 }
134 143
135 144 if ( version_compare( $current_version, '1.0.13', '<' ) ) {
136 145 $costs = $this->get_cost_tracker_by( 'all' );
@@ -227,19 +236,15 @@
227 236 unset( $update['id'] );
228 237 }
229 238
230 239 if ( ! empty( $id ) ) {
231 -
240 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- write operation; caching not applicable for UPDATE.
232 241 $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
242 + $this->invalidate_cost_tracker_caches( $id );
233 243 return $this->get_cost_tracker_by( 'id', $id );
234 - } else {
235 - if ( isset( $update['id'] ) ) {
236 - unset( $update['id'] );
237 - }
238 -
239 - if ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
240 - return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
241 - }
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 );
242 247 }
243 248 return false;
244 249 }
245 250
@@ -258,26 +263,15 @@
258 263 if ( empty( $item_id ) ) {
259 264 return false;
260 265 }
261 266
262 - $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 + );
263 272
264 - $new_obj_ids = array();
265 -
266 - if ( ! empty( $selected_sites ) ) {
267 - $obj_name = 'site';
268 - $new_obj_ids = $selected_sites;
269 - } elseif ( ! empty( $selected_groups ) ) {
270 - $obj_name = 'tag';
271 - $new_obj_ids = $selected_groups;
272 - } elseif ( ! empty( $selected_clients ) ) {
273 - $obj_name = 'client';
274 - $new_obj_ids = $selected_clients;
275 - }
276 -
277 - if ( ! empty( $obj_name ) ) {
278 - static::get_instance()->update_lookup_cost( $item_id, $obj_name, $new_obj_ids );
279 - } else {
273 + if ( ! array_filter( $selected_lists ) ) {
280 274 // to support saving cost without selected sites, tags, clients.
281 275 MainWP_DB::instance()->delete_lookup_items(
282 276 'object_name',
283 277 array(
@@ -285,10 +279,17 @@
285 279 'item_id' => $item_id,
286 280 'object_names' => array( 'site', 'tag', 'client' ),
287 281 )
288 282 );
283 + return true;
284 + }
289 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 );
290 290 }
291 +
291 292 return true;
292 293 }
293 294
294 295 /**
@@ -307,10 +308,8 @@
307 308 if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
308 309 return false;
309 310 }
310 311
311 - $remove_obj_names = array_diff( $allows, array( $obj_name ) );
312 -
313 312 $found_look_ids = array();
314 313 $existed_look_id = array();
315 314
316 315 $results = MainWP_DB::instance()->get_lookup_items( 'cost', $item_id, $obj_name );
@@ -338,20 +337,8 @@
338 337 $remove_ids = array_diff( $found_look_ids, $new_look_ids );
339 338 if ( $remove_ids ) {
340 339 MainWP_DB::instance()->delete_lookup_items( 'lookup_id', array( 'lookup_ids' => $remove_ids ) );
341 340 }
342 -
343 - if ( ! empty( $remove_obj_names ) ) {
344 - MainWP_DB::instance()->delete_lookup_items(
345 - 'object_name',
346 - array(
347 - 'item_name' => 'cost',
348 - 'item_id' => $item_id,
349 - 'object_names' => $remove_obj_names,
350 -
351 - )
352 - );
353 - }
354 341 }
355 342
356 343 /**
357 344 * Method get_cost_tracker_by().
@@ -368,8 +355,58 @@
368 355 if ( ! is_array( $params ) ) {
369 356 $params = array();
370 357 }
371 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 +
372 409 $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
373 410 $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
374 411
375 412 if ( is_array( $selected_ids ) ) {
@@ -378,46 +415,99 @@
378 415 $selected_ids = array();
379 416 }
380 417
381 418 if ( 'all' === $by ) {
382 - $where = '';
383 419 if ( ! empty( $selected_ids ) ) {
384 - $where = ' AND id IN (' . implode( ',', $selected_ids ) . ') ';
420 + $where .= ' AND ct.id IN (' . implode( ',', $selected_ids ) . ') ';
385 421 }
386 - 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}" );
387 425 }
388 426
427 + $where = '';
428 + $limit = '';
429 +
389 430 $sql = '';
390 431 if ( 'id' === $by && is_numeric( $value ) ) {
391 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id`=%d ', $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
392 - 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;
393 444 } elseif ( 'count' === $by ) {
394 - $sql = 'SELECT count(*) FROM ' . $this->table_name( 'cost_tracker' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
395 - 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;
396 464 } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
397 465 $cost_ids = explode( ',', $value );
398 466 $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
399 467 if ( ! empty( $cost_ids ) ) {
400 - $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 ) . ' )';
401 470 }
402 471 } elseif ( 'site_id' === $by || 'client_id' === $by ) {
403 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
472 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
473 + $sql = "SELECT * FROM {$table}";
404 474 } elseif ( 'slug' === $by && is_string( $value ) ) {
405 475 if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
406 - $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
407 - 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;
408 488 } else {
409 489 $where = '';
410 490 if ( ! empty( $product_type ) ) {
411 491 $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
412 492 }
413 - $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 );
414 496 }
415 497 }
416 498
417 499 $data = array();
418 500 if ( ! empty( $sql ) ) {
419 - $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' );
420 510 if ( $result ) {
421 511 if ( 'site_id' === $by ) {
422 512 $site_id = intval( $value );
423 513 foreach ( $result as $cost ) {
@@ -452,8 +542,22 @@
452 542 }
453 543 return $data;
454 544 }
455 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 + }
456 560
457 561 /**
458 562 * Method delete_cost_tracker().
459 563 *
@@ -474,19 +578,39 @@
474 578 }
475 579 $deleted = false;
476 580
477 581 if ( 'id' === $by ) {
478 - 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.
479 584 $deleted = true;
480 585 }
481 - } elseif ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE site_id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
482 - $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 + }
483 591 }
484 592
485 593 if ( $deleted ) {
486 - $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 );
487 597 }
488 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 +
489 613 return $deleted;
490 614 }
491 615
492 616 /**
@@ -516,9 +640,9 @@
516 640
517 641 $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $client_ids );
518 642 if ( $client_sites ) {
519 643 foreach ( $client_sites as $website ) {
520 - if ( ! empty( $website->client_id ) ) {
644 + if ( empty( $website->client_id ) ) {
521 645 continue;
522 646 }
523 647 if ( ! isset( $clients_site_ids[ $website->client_id ] ) ) {
524 648 $clients_site_ids[ $website->client_id ] = array();
@@ -530,10 +654,20 @@
530 654 global $wpdb;
531 655
532 656 $clients_costs = array();
533 657 $clients_costs_ids = array();
534 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
535 - $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 +
536 670 if ( $result ) {
537 671 foreach ( $result as $cost ) {
538 672 $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
539 673 $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
@@ -627,9 +761,51 @@
627 761
628 762 return $clients_costs;
629 763 }
630 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.
631 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 +
632 808 /**
633 809 * Method get_all_cost_trackers_by_sites().
634 810 *
635 811 * @param array $sites_ids Sites ids.
@@ -651,10 +827,20 @@
651 827 global $wpdb;
652 828
653 829 $sites_costs = array();
654 830 $sites_costs_ids = array();
655 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
656 - $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 +
657 843 if ( $result ) {
658 844 foreach ( $result as $cost ) {
659 845 $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
660 846 $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
@@ -748,12 +934,123 @@
748 934 $sum_data = isset( $params['sum_data'] ) ? $params['sum_data'] : '';
749 935 $where = '';
750 936 $sql = '';
751 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 +
752 951 $where .= ' AND co.cost_status = "active" AND co.type = "subscription" ';
753 - $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;
754 958 } else {
755 959 return false;
756 960 }
757 - 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 + }
758 1055 }
759 1056 }