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.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 +919 -623 5.0.2 → 6.2.1 View file →
@@ -11,75 +11,81 @@
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 {
20 - //phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
21 - /**
22 - * Variable to hold the db version.
23 - *
24 - * @var string Version.
25 - */
26 - private $cost_tracker_db_version = '1.0.16';
26 + //phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
27 + /**
28 + * Variable to hold the db version.
29 + *
30 + * @var string Version.
31 + */
32 + private $cost_tracker_db_version = '1.0.18';
27 33
28 34
29 - /**
30 - * Static variable to hold the single instance of the class.
31 - *
32 - * @static
33 - *
34 - * @var mixed Default null
35 - */
36 - private static $instance = null;
35 + /**
36 + * Static variable to hold the single instance of the class.
37 + *
38 + * @static
39 + *
40 + * @var mixed Default null
41 + */
42 + private static $instance = null;
37 43
38 - /**
39 - * Get Instance
40 - *
41 - * Creates public static instance.
42 - *
43 - * @static
44 - *
45 - * @return Cost_Tracker_DB
46 - */
47 - public static function get_instance() {
48 - if ( null === self::$instance ) {
49 - self::$instance = new self();
50 - }
51 - return self::$instance;
52 - }
44 + /**
45 + * Get Instance
46 + *
47 + * Creates public static instance.
48 + *
49 + * @static
50 + *
51 + * @return Cost_Tracker_DB
52 + */
53 + public static function get_instance() {
54 + if ( null === static::$instance ) {
55 + static::$instance = new self();
56 + }
57 + return static::$instance;
58 + }
53 59
54 - /**
55 - * Init functions.
56 - */
57 - public function init() {
58 - $this->install();
59 - }
60 + /**
61 + * Init functions.
62 + */
63 + public function init() {
64 + $this->install();
65 + }
60 66
61 - /**
62 - * Install db.
63 - */
64 - public function install() {
65 - global $wpdb;
67 + /**
68 + * Install db.
69 + */
70 + public function install() {
71 + global $wpdb;
66 72
67 - $currentVersion = get_site_option( 'mainwp_module_cost_tracker_db_version' );
73 + $currentVersion = get_site_option( 'mainwp_module_cost_tracker_db_version' );
68 74
69 - $rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'cost_tracker' ) . "'" );
70 - if ( empty( self::num_rows( $rslt ) ) ) {
71 - $currentVersion = false;
72 - }
75 + $rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'cost_tracker' ) . "'" );
76 + if ( empty( static::num_rows( $rslt ) ) ) {
77 + $currentVersion = false;
78 + }
73 79
74 - if ( $currentVersion === $this->cost_tracker_db_version ) {
75 - return;
76 - }
80 + if ( $currentVersion === $this->cost_tracker_db_version ) {
81 + return;
82 + }
77 83
78 - $charset_collate = $wpdb->get_charset_collate();
79 - $sql = array();
84 + $charset_collate = $wpdb->get_charset_collate();
85 + $sql = array();
80 86
81 - $tbl = 'CREATE TABLE `' . $this->table_name( 'cost_tracker' ) . '` (
87 + $tbl = 'CREATE TABLE `' . $this->table_name( 'cost_tracker' ) . '` (
82 88 `id` int(11) NOT NULL AUTO_INCREMENT,
83 89 `name` text NOT NULL,
84 90 `url` text NOT NULL,
85 91 `type` varchar(20) NOT NULL,
@@ -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,
@@ -99,662 +106,951 @@
99 106 `groups` text NOT NULL,
100 107 `clients` text NOT NULL,
101 108 `note` text NOT NULL';
102 109
103 - if ( empty( $currentVersion ) ) {
104 - $tbl .= ',
105 -PRIMARY KEY (`id`) '; }
106 - $tbl .= ') ' . $charset_collate;
107 - $sql[] = $tbl;
110 + if ( empty( $currentVersion ) ) {
111 + $tbl .= ',
112 +PRIMARY KEY (`id`) ';
113 + }
114 + $tbl .= ') ' . $charset_collate;
115 + $sql[] = $tbl;
108 116
109 - require_once ABSPATH . 'wp-admin/includes/upgrade.php';
110 - foreach ( $sql as $query ) {
111 - dbDelta( $query );
112 - }
113 - $this->update_db_cost( $currentVersion );
114 - update_option( 'mainwp_module_cost_tracker_db_version', $this->cost_tracker_db_version );
115 - }
117 + require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // NOSONAR - WP compatible.
118 + foreach ( $sql as $query ) {
119 + dbDelta( $query );
120 + }
121 + $this->update_db_cost( $currentVersion );
122 + update_option( 'mainwp_module_cost_tracker_db_version', $this->cost_tracker_db_version );
123 + }
116 124
117 125
118 - /**
119 - * Method update_db_cost().
120 - *
121 - * @param array $current_version DB version number.
122 - *
123 - * @return void
124 - */
125 - public function update_db_cost( $current_version ) {
126 - if ( ! empty( $current_version ) ) {
127 - if ( version_compare( $current_version, '1.0.8', '<' ) ) {
128 - $this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'cost_tracker' ) . ' MODIFY COLUMN price decimal(26,8)' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
129 - }
130 - if ( version_compare( $current_version, '1.0.9', '<' ) ) {
131 - $this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'cost_tracker' ) . ' DROP COLUMN author' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
132 - }
126 + /**
127 + * Method update_db_cost().
128 + *
129 + * @param array $current_version DB version number.
130 + *
131 + * @return void
132 + */
133 + public function update_db_cost( $current_version ) { //phpcs:ignore -- NOSONAR - complex.
134 + if ( ! empty( $current_version ) ) {
135 + if ( version_compare( $current_version, '1.0.8', '<' ) ) {
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.
138 + }
139 + if ( version_compare( $current_version, '1.0.9', '<' ) ) {
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.
142 + }
133 143
134 - if ( version_compare( $current_version, '1.0.13', '<' ) ) {
135 - $costs = $this->get_cost_tracker_by( 'all' );
144 + if ( version_compare( $current_version, '1.0.13', '<' ) ) {
145 + $costs = $this->get_cost_tracker_by( 'all' );
136 146
137 - foreach ( $costs as $cost ) {
138 - $obj_name = '';
139 - if ( ! empty( $cost->sites ) ) {
140 - $items = json_decode( $cost->sites, true );
141 - $obj_name = 'site';
142 - } elseif ( ! empty( $cost->groups ) ) {
143 - $items = json_decode( $cost->groups, true );
144 - $obj_name = 'tag';
145 - } elseif ( ! empty( $cost->clients ) ) {
146 - $items = json_decode( $cost->clients, true );
147 - $obj_name = 'client';
148 - } else {
149 - continue;
150 - }
147 + foreach ( $costs as $cost ) {
148 + $obj_name = '';
149 + if ( ! empty( $cost->sites ) ) {
150 + $items = json_decode( $cost->sites, true );
151 + $obj_name = 'site';
152 + } elseif ( ! empty( $cost->groups ) ) {
153 + $items = json_decode( $cost->groups, true );
154 + $obj_name = 'tag';
155 + } elseif ( ! empty( $cost->clients ) ) {
156 + $items = json_decode( $cost->clients, true );
157 + $obj_name = 'client';
158 + } else {
159 + continue;
160 + }
151 161
152 - if ( ! empty( $items ) ) {
153 - foreach ( $items as $it_id ) {
154 - $success = $this->wpdb->insert(
155 - $this->table_name( 'lookup_item_objects' ),
156 - array(
157 - 'item_id' => $cost->id,
158 - 'item_name' => 'cost',
159 - 'object_id' => $it_id,
160 - 'object_name' => $obj_name,
161 - )
162 - );
162 + if ( ! empty( $items ) ) {
163 + foreach ( $items as $it_id ) {
164 + $this->wpdb->insert(
165 + $this->table_name( 'lookup_item_objects' ),
166 + array(
167 + 'item_id' => $cost->id,
168 + 'item_name' => 'cost',
169 + 'object_id' => $it_id,
170 + 'object_name' => $obj_name,
171 + )
172 + );
163 173
164 - }
165 - }
166 - }
167 - }
168 - }
169 - }
174 + }
175 + }
176 + }
177 + }
178 + }
179 + }
170 180
171 - /**
172 - * Method update_cost_tracker().
173 - *
174 - * @param array $update Cost tracker array data.
175 - *
176 - * @throws \Exception Existed cost tracker error.
177 - * @return mixed Result
178 - */
179 - public function update_cost_tracker( $update ) { //phpcs:ignore -- complex.
180 - /**
181 - * WP database.
182 - *
183 - * @global object
184 - */
185 - global $wpdb;
181 + /**
182 + * Method update_cost_tracker().
183 + *
184 + * @param array $update Cost tracker array data.
185 + *
186 + * @throws \MainWP_Exception Existed cost tracker error.
187 + * @return mixed Result
188 + */
189 + public function update_cost_tracker( $update ) { //phpcs:ignore -- NOSONAR - complex.
190 + /**
191 + * WP database.
192 + *
193 + * @global object
194 + */
195 + global $wpdb;
186 196
187 - if ( ! is_array( $update ) ) {
188 - return false;
189 - }
197 + if ( ! is_array( $update ) ) {
198 + return false;
199 + }
190 200
191 - $id = isset( $update['id'] ) ? $update['id'] : 0;
201 + $id = isset( $update['id'] ) ? $update['id'] : 0;
192 202
193 - if ( ! empty( $update['product_type'] ) && ! empty( $update['slug'] ) ) {
194 - if ( in_array( $update['product_type'], array( 'plugin', 'theme' ), true ) ) {
195 - // check existed cost tracker for this plugin / theme .
196 - $current = $this->get_cost_tracker_by( 'slug', $update['slug'], $update['product_type'] );
197 - if ( is_array( $current ) && ! empty( $current ) ) {
198 - $existed = false;
199 - if ( 1 === count( $current ) ) {
200 - $current = current( $current );
201 - if ( is_object( $current ) ) {
202 - if ( ! empty( $update['id'] ) ) {
203 - if ( ! empty( $current->id ) && (int) $current->id !== (int) $update['id'] ) {
204 - $existed = true; // to fix.
205 - } elseif ( ! empty( $current->id ) ) {
206 - $id = $current->id; // to update.
207 - }
208 - } else {
209 - $existed = true; // to fix: existed one.
210 - }
211 - }
212 - } else {
213 - $existed = true; // to fix found multi items.
214 - }
203 + if ( ! empty( $update['product_type'] ) && ! empty( $update['slug'] ) && in_array( $update['product_type'], array( 'plugin', 'theme' ), true ) ) {
204 + // check existed cost tracker for this plugin / theme .
205 + $current = $this->get_cost_tracker_by( 'slug', $update['slug'], $update['product_type'] );
206 + if ( is_array( $current ) && ! empty( $current ) ) {
207 + $existed = false;
208 + if ( 1 === count( $current ) ) {
209 + $current = current( $current );
210 + if ( is_object( $current ) ) {
211 + if ( ! empty( $update['id'] ) ) {
212 + if ( ! empty( $current->id ) && (int) $current->id !== (int) $update['id'] ) {
213 + $existed = true; // to fix.
214 + } elseif ( ! empty( $current->id ) ) {
215 + $id = $current->id; // to update.
216 + }
217 + } else {
218 + $existed = true; // to fix: existed one.
219 + }
220 + }
221 + } else {
222 + $existed = true; // to fix found multi items.
223 + }
215 224
216 - if ( $existed ) {
217 - $error = esc_html__( 'A cost tracker for this plugin already exists.', 'mainwp' );
218 - if ( 'theme' === $update['product_type'] ) {
219 - $error = esc_html__( 'A cost tracker for this theme already exists.', 'mainwp' );
220 - }
221 - throw new \Exception( esc_html( $error ) );
222 - }
223 - }
224 - }
225 - }
225 + if ( $existed ) {
226 + $error = esc_html__( 'A cost tracker for this plugin already exists.', 'mainwp' );
227 + if ( 'theme' === $update['product_type'] ) {
228 + $error = esc_html__( 'A cost tracker for this theme already exists.', 'mainwp' );
229 + }
230 + throw new MainWP_Exception( esc_html( $error ) );
231 + }
232 + }
233 + }
226 234
227 - if ( isset( $update['id'] ) ) {
228 - unset( $update['id'] );
229 - }
235 + if ( isset( $update['id'] ) ) {
236 + unset( $update['id'] );
237 + }
230 238
231 - $site_id = isset( $update['site_id'] ) ? $update['site_id'] : '';
239 + if ( ! empty( $id ) ) {
240 + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- write operation; caching not applicable for UPDATE.
241 + $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
242 + $this->invalidate_cost_tracker_caches( $id );
243 + return $this->get_cost_tracker_by( 'id', $id );
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 );
247 + }
248 + return false;
249 + }
232 250
233 - if ( ! empty( $id ) ) {
251 + /**
252 + * Method update_selected_lookup_cost().
253 + *
254 + * @param int $item_id Cost id.
255 + * @param array $selected_sites selected sites.
256 + * @param array $selected_groups selected tags.
257 + * @param array $selected_clients selected clients.
258 + *
259 + * @return mixed Result
260 + */
261 + public function update_selected_lookup_cost( $item_id, $selected_sites = false, $selected_groups = false, $selected_clients = false ) {
234 262
235 - $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
236 - return $this->get_cost_tracker_by( 'id', $id );
237 - } else {
238 - if ( isset( $update['id'] ) ) {
239 - unset( $update['id'] );
240 - }
263 + if ( empty( $item_id ) ) {
264 + return false;
265 + }
241 266
242 - if ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
243 - return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
244 - }
245 - }
246 - return false;
247 - }
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 + );
248 272
249 - /**
250 - * Method update_selected_lookup_cost().
251 - *
252 - * @param int $item_id Cost id.
253 - * @param array $selected_sites selected sites.
254 - * @param array $selected_groups selected tags.
255 - * @param array $selected_clients selected clients.
256 - *
257 - * @return mixed Result
258 - */
259 - public function update_selected_lookup_cost( $item_id, $selected_sites = false, $selected_groups = false, $selected_clients = false ) {
273 + if ( ! array_filter( $selected_lists ) ) {
274 + // to support saving cost without selected sites, tags, clients.
275 + MainWP_DB::instance()->delete_lookup_items(
276 + 'object_name',
277 + array(
278 + 'item_name' => 'cost',
279 + 'item_id' => $item_id,
280 + 'object_names' => array( 'site', 'tag', 'client' ),
281 + )
282 + );
283 + return true;
284 + }
260 285
261 - if ( empty( $item_id ) ) {
262 - return false;
263 - }
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 + }
264 291
265 - $obj_name = '';
292 + return true;
293 + }
266 294
267 - $new_obj_ids = array();
295 + /**
296 + * Method update_lookup_cost().
297 + *
298 + * @param int $item_id item id to insert lookup value.
299 + * @param string $obj_name loockup object name.
300 + * @param array $new_obj_ids New|Update object ids.
301 + *
302 + * @return mixed Result
303 + */
304 + public function update_lookup_cost( $item_id, $obj_name, $new_obj_ids ) { //phpcs:ignore -- NOSONAR - complex.
268 305
269 - if ( ! empty( $selected_sites ) ) {
270 - $obj_name = 'site';
271 - $new_obj_ids = $selected_sites;
272 - } elseif ( ! empty( $selected_groups ) ) {
273 - $obj_name = 'tag';
274 - $new_obj_ids = $selected_groups;
275 - } elseif ( ! empty( $selected_clients ) ) {
276 - $obj_name = 'client';
277 - $new_obj_ids = $selected_clients;
278 - }
306 + $allows = array( 'site', 'tag', 'client' );
279 307
280 - if ( ! empty( $obj_name ) ) {
281 - self::get_instance()->update_lookup_cost( $item_id, $obj_name, $new_obj_ids );
282 - } else {
283 - // to support saving cost without selected sites, tags, clients.
284 - MainWP_DB::instance()->delete_lookup_items(
285 - 'object_name',
286 - array(
287 - 'item_name' => 'cost',
288 - 'item_id' => $item_id,
289 - 'object_names' => array( 'site', 'tag', 'client' ),
290 - )
291 - );
308 + if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
309 + return false;
310 + }
292 311
293 - }
294 - return true;
295 - }
312 + $found_look_ids = array();
313 + $existed_look_id = array();
296 314
297 - /**
298 - * Method update_lookup_cost().
299 - *
300 - * @param int $item_id item id to insert lookup value.
301 - * @param string $obj_name loockup object name.
302 - * @param array $new_obj_ids New|Update object ids.
303 - *
304 - * @return mixed Result
305 - */
306 - public function update_lookup_cost( $item_id, $obj_name, $new_obj_ids ) {
315 + $results = MainWP_DB::instance()->get_lookup_items( 'cost', $item_id, $obj_name );
316 + if ( $results ) {
317 + foreach ( $results as $item ) {
318 + $found_look_ids[] = $item->lookup_id;
319 + if ( ! empty( $new_obj_ids ) && in_array( $item->object_id, $new_obj_ids ) ) {
320 + $existed_look_id[ $item->object_id ] = $item->lookup_id;
321 + }
322 + }
323 + }
307 324
308 - $allows = array( 'site', 'tag', 'client' );
325 + $new_look_ids = array();
309 326
310 - if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
311 - return false;
312 - }
327 + foreach ( $new_obj_ids as $obj_id ) {
328 + if ( isset( $existed_look_id[ $obj_id ] ) ) {
329 + $new_look_ids[] = $existed_look_id[ $obj_id ];
330 + continue;
331 + }
332 + $insert_id = MainWP_DB::instance()->insert_lookup_item( 'cost', $item_id, $obj_name, $obj_id );
333 + if ( $insert_id ) {
334 + $new_look_ids[] = $this->wpdb->insert_id;
335 + }
336 + }
337 + $remove_ids = array_diff( $found_look_ids, $new_look_ids );
338 + if ( $remove_ids ) {
339 + MainWP_DB::instance()->delete_lookup_items( 'lookup_id', array( 'lookup_ids' => $remove_ids ) );
340 + }
341 + }
313 342
314 - $remove_obj_names = array_diff( $allows, array( $obj_name ) );
343 + /**
344 + * Method get_cost_tracker_by().
345 + *
346 + * @param string $by Get by.
347 + * @param mixed $value Value.
348 + * @param array $params Others params.
349 + *
350 + * @return mixed Result
351 + */
352 + public function get_cost_tracker_by( $by = 'id', $value = null, $params = array() ) { //phpcs:ignore -- NOSONAR - complex method.
353 + global $wpdb;
315 354
316 - $found_look_ids = array();
317 - $existed_look_id = array();
355 + if ( ! is_array( $params ) ) {
356 + $params = array();
357 + }
318 358
319 - $results = MainWP_DB::instance()->get_lookup_items( 'cost', $item_id, $obj_name );
320 - if ( $results ) {
321 - foreach ( $results as $item ) {
322 - $found_look_ids[] = $item->lookup_id;
323 - if ( ! empty( $new_obj_ids ) && in_array( $item->object_id, $new_obj_ids ) ) {
324 - $existed_look_id[ $item->object_id ] = $item->lookup_id;
325 - }
326 - }
327 - }
359 + $s = '';
360 + $exclude = array();
361 + $include = array();
362 + $status = array();
363 + $types = array();
328 364
329 - $new_look_ids = array();
365 + $where = '';
366 + $limit = '';
330 367
331 - foreach ( $new_obj_ids as $obj_id ) {
332 - if ( isset( $existed_look_id[ $obj_id ] ) ) {
333 - $new_look_ids[] = $existed_look_id[ $obj_id ];
334 - continue;
335 - }
336 - $insert_id = MainWP_DB::instance()->insert_lookup_item( 'cost', $item_id, $obj_name, $obj_id );
337 - if ( $insert_id ) {
338 - $new_look_ids[] = $this->wpdb->insert_id;
339 - }
340 - }
341 - $remove_ids = array_diff( $found_look_ids, $new_look_ids );
342 - if ( $remove_ids ) {
343 - MainWP_DB::instance()->delete_lookup_items( 'lookup_id', array( 'lookup_ids' => $remove_ids ) );
344 - }
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();
345 372
346 - if ( ! empty( $remove_obj_names ) ) {
347 - MainWP_DB::instance()->delete_lookup_items(
348 - 'object_name',
349 - array(
350 - 'item_name' => 'cost',
351 - 'item_id' => $item_id,
352 - 'object_names' => $remove_obj_names,
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();
353 376
354 - )
355 - );
356 - }
357 - }
377 + $page = isset( $params['paged'] ) ? intval( $params['paged'] ) : false;
378 + $per_page = isset( $params['items_per_page'] ) ? intval( $params['items_per_page'] ) : false;
358 379
359 - /**
360 - * Method get_cost_tracker_by().
361 - *
362 - * @param string $by Get by.
363 - * @param mixed $value Value.
364 - * @param array $params Others params.
365 - *
366 - * @return mixed Result
367 - */
368 - public function get_cost_tracker_by( $by = 'id', $value = null, $params = array() ) { //phpcs:ignore -- complex method.
369 - global $wpdb;
380 + if ( ! empty( $s ) ) {
381 + $where .= ' AND ( ct.id LIKE "%' . $this->escape( $s ) . '%" OR ct.name LIKE "%' . $this->escape( $s ) . '%" ) ';
382 + }
370 383
371 - if ( ! is_array( $params ) ) {
372 - $params = array();
373 - }
384 + if ( ! empty( $exclude ) ) {
385 + $where .= ' AND ct.id NOT IN (' . implode( ',', $exclude ) . ') ';
386 + }
374 387
375 - $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
376 - $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
388 + if ( ! empty( $include ) ) {
389 + $where .= ' AND ct.id IN (' . implode( ',', $include ) . ') ';
390 + }
377 391
378 - if ( is_array( $selected_ids ) ) {
379 - $selected_ids = MainWP_Utility::array_numeric_filter( $selected_ids );
380 - } else {
381 - $selected_ids = array();
382 - }
392 + if ( ! empty( $status ) ) {
393 + $where .= ' AND ct.cost_status IN (' . $this->prepare_fields_array( $status ) . ') ';
394 + }
383 395
384 - if ( 'all' === $by ) {
385 - $where = '';
386 - if ( ! empty( $selected_ids ) ) {
387 - $where = ' AND id IN (' . implode( ',', $selected_ids ) . ') ';
388 - }
389 - return $wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE 1 ' . $where ); //phpcs:ignore -- good.
390 - }
396 + if ( ! empty( $product_type ) && ! in_array( 'any', $product_type ) ) {
397 + $where .= ' AND ct.product_type IN (' . $this->prepare_fields_array( $product_type ) . ') ';
398 + }
391 399
392 - $sql = '';
393 - if ( 'id' === $by && is_numeric( $value ) ) {
394 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id`=%d ', $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
395 - return $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
396 - } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
397 - $cost_ids = explode( ',', $value );
398 - $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
399 - if ( ! empty( $cost_ids ) ) {
400 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id` IN (' . implode( ',', $cost_ids ) . ' )';
401 - }
402 - } elseif ( 'site_id' === $by || 'client_id' === $by ) {
403 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
404 - } elseif ( 'slug' === $by && is_string( $value ) ) {
405 - 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 - $result = $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
408 - return $result;
409 - } else {
410 - $where = '';
411 - if ( ! empty( $product_type ) ) {
412 - $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
413 - }
414 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `slug`=%s ' . $where, $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
415 - }
416 - }
400 + if ( ! empty( $types ) && ! in_array( 'any', $types ) ) {
401 + $where .= ' AND ct.type IN (' . $this->prepare_fields_array( $types ) . ') ';
402 + }
417 403
418 - $data = array();
419 - if ( ! empty( $sql ) ) {
420 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
421 - if ( $result ) {
422 - if ( 'site_id' === $by ) {
423 - $site_id = intval( $value );
424 - foreach ( $result as $cost ) {
425 - if ( empty( $cost->id ) ) {
426 - continue;
427 - }
428 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
429 - if ( is_array( $sites ) && in_array( $site_id, $sites ) ) {
430 - $data[] = $cost;
431 - }
432 - }
433 - } elseif ( 'client_id' === $by ) {
434 - $client_id = intval( $value );
435 - foreach ( $result as $cost ) {
436 - if ( empty( $cost->id ) ) {
437 - continue;
438 - }
439 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
440 - if ( is_array( $clients ) && in_array( $client_id, $clients ) ) {
441 - $data[] = $cost;
442 - }
443 - }
444 - } else {
445 - foreach ( $result as $cost ) {
446 - if ( empty( $cost->id ) ) {
447 - continue;
448 - }
449 - $data[] = $cost;
450 - }
451 - }
452 - }
453 - }
454 - return $data;
455 - }
404 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
405 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
406 + }
407 + }
456 408
409 + $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
410 + $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
457 411
458 - /**
459 - * Method delete_cost_tracker().
460 - *
461 - * @param string $by Delete by.
462 - * @param mixed $value Value.
463 - *
464 - * @return mixed Result
465 - */
466 - public function delete_cost_tracker( $by = 'id', $value = null ) {
467 - global $wpdb;
412 + if ( is_array( $selected_ids ) ) {
413 + $selected_ids = MainWP_Utility::array_numeric_filter( $selected_ids );
414 + } else {
415 + $selected_ids = array();
416 + }
468 417
469 - if ( empty( $value ) ) {
470 - return false;
471 - }
418 + if ( 'all' === $by ) {
419 + if ( ! empty( $selected_ids ) ) {
420 + $where .= ' AND ct.id IN (' . implode( ',', $selected_ids ) . ') ';
421 + }
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}" );
425 + }
472 426
473 - if ( 'id' !== $by && 'site_id' !== $by ) {
474 - return false;
475 - }
476 - $deleted = false;
427 + $where = '';
428 + $limit = '';
477 429
478 - 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
480 - $deleted = true;
481 - }
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;
484 - }
430 + $sql = '';
431 + if ( 'id' === $by && is_numeric( $value ) ) {
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 + }
485 437
486 - 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
488 - }
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;
444 + } elseif ( 'count' === $by ) {
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 + }
489 457
490 - return $deleted;
491 - }
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;
464 + } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
465 + $cost_ids = explode( ',', $value );
466 + $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
467 + if ( ! empty( $cost_ids ) ) {
468 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
469 + $sql = "SELECT * FROM {$table} WHERE `id` IN (" . implode( ',', $cost_ids ) . ' )';
470 + }
471 + } elseif ( 'site_id' === $by || 'client_id' === $by ) {
472 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
473 + $sql = "SELECT * FROM {$table}";
474 + } elseif ( 'slug' === $by && is_string( $value ) ) {
475 + if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
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 + }
492 481
493 - /**
494 - * Method get_all_cost_trackers_by_clients().
495 - *
496 - * @param array $client_ids Client ids.
497 - * @param array $params Orther params.
498 - *
499 - * @return mixed Result
500 - */
501 - public function get_all_cost_trackers_by_clients( $client_ids, $params = array() ) { //phpcs:ignore -- complex method.
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;
488 + } else {
489 + $where = '';
490 + if ( ! empty( $product_type ) ) {
491 + $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
492 + }
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 );
496 + }
497 + }
502 498
503 - if ( is_string( $client_ids ) ) {
504 - $client_ids = explode( ',', $client_ids );
505 - }
499 + $data = array();
500 + if ( ! empty( $sql ) ) {
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 506
507 - if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
508 - return array();
509 - }
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' );
510 + if ( $result ) {
511 + if ( 'site_id' === $by ) {
512 + $site_id = intval( $value );
513 + foreach ( $result as $cost ) {
514 + if ( empty( $cost->id ) ) {
515 + continue;
516 + }
517 + $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
518 + if ( is_array( $sites ) && in_array( $site_id, $sites ) ) {
519 + $data[] = $cost;
520 + }
521 + }
522 + } elseif ( 'client_id' === $by ) {
523 + $client_id = intval( $value );
524 + foreach ( $result as $cost ) {
525 + if ( empty( $cost->id ) ) {
526 + continue;
527 + }
528 + $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
529 + if ( is_array( $clients ) && in_array( $client_id, $clients ) ) {
530 + $data[] = $cost;
531 + }
532 + }
533 + } else {
534 + foreach ( $result as $cost ) {
535 + if ( empty( $cost->id ) ) {
536 + continue;
537 + }
538 + $data[] = $cost;
539 + }
540 + }
541 + }
542 + }
543 + return $data;
544 + }
510 545
511 - if ( ! is_array( $params ) ) {
512 - $params = array();
513 - }
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 + }
514 560
515 - $get_sites_of_cost = isset( $params['get_cost_sites'] ) && $params['get_cost_sites'] ? true : false;
516 - $clients_site_ids = array();
561 + /**
562 + * Method delete_cost_tracker().
563 + *
564 + * @param string $by Delete by.
565 + * @param mixed $value Value.
566 + *
567 + * @return mixed Result
568 + */
569 + public function delete_cost_tracker( $by = 'id', $value = null ) {
570 + global $wpdb;
517 571
518 - $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $client_ids );
519 - if ( $client_sites ) {
520 - foreach ( $client_sites as $website ) {
521 - if ( ! empty( $website->client_id ) ) {
522 - continue;
523 - }
524 - if ( ! isset( $clients_site_ids[ $website->client_id ] ) ) {
525 - $clients_site_ids[ $website->client_id ] = array();
526 - }
527 - $clients_site_ids[ $website->client_id ][] = $website->id;
528 - }
529 - }
572 + if ( empty( $value ) ) {
573 + return false;
574 + }
530 575
531 - global $wpdb;
576 + if ( 'id' !== $by && 'site_id' !== $by ) {
577 + return false;
578 + }
579 + $deleted = false;
532 580
533 - $clients_costs = array();
534 - $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.
537 - if ( $result ) {
538 - foreach ( $result as $cost ) {
539 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
540 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
541 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
581 + if ( 'id' === $by ) {
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.
584 + $deleted = true;
585 + }
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 + }
591 + }
542 592
543 - if ( ! is_array( $sites ) ) {
544 - $sites = array();
545 - }
546 - if ( ! is_array( $groups ) ) {
547 - $groups = array();
548 - }
549 - if ( ! is_array( $clients ) ) {
550 - $clients = array();
551 - }
593 + if ( $deleted ) {
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 );
597 + }
552 598
553 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
554 - continue;
555 - }
599 + $snapshot_info = Cost_Tracker_Admin::get_costs_snapshot_info();
556 600
557 - // to reduce db queries for this case.
558 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
559 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
560 - foreach ( $sites as $siteid ) {
561 - if ( in_array( $siteid, $client_site_ids ) ) {
562 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
563 - $clients_costs[ $client_id ] = array();
564 - }
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 );
565 612
566 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
567 - $clients_costs_ids[ $client_id ] = array();
568 - }
613 + return $deleted;
614 + }
569 615
570 - if ( $get_sites_of_cost ) {
571 - $cost->cost_sites_ids = $sites; // sites ids.
572 - }
573 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
574 - $cost->count_sites = count( $sites );
575 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $sites );
576 - $clients_costs[ $client_id ][] = $cost;
577 - $clients_costs_ids[ $client_id ][] = $cost->id;
578 - }
579 - break;
580 - }
581 - }
582 - }
583 - continue;
584 - }
616 + /**
617 + * Method get_all_cost_trackers_by_clients().
618 + *
619 + * @param array $client_ids Client ids.
620 + * @param array $params Orther params.
621 + *
622 + * @return mixed Result
623 + */
624 + public function get_all_cost_trackers_by_clients( $client_ids, $params = array() ) { //phpcs:ignore -- NOSONAR - complex method.
585 625
586 - $params = array(
587 - 'sites' => $sites,
588 - 'groups' => $groups,
589 - 'clients' => $clients,
590 - );
626 + if ( is_string( $client_ids ) ) {
627 + $client_ids = explode( ',', $client_ids );
628 + }
591 629
592 - // to do: should create relation of: costs, sites, groups, clients in other way.
593 - // to make it more easier in queries.
594 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params ); // get sites of cost tracker.
595 - $cost_sites_ids = array();
630 + if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
631 + return array();
632 + }
596 633
597 - if ( is_array( $cost_sites ) ) {
634 + if ( ! is_array( $params ) ) {
635 + $params = array();
636 + }
598 637
599 - foreach ( $cost_sites as $cost_site ) {
600 - $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
601 - }
638 + $get_sites_of_cost = isset( $params['get_cost_sites'] ) && $params['get_cost_sites'] ? true : false;
639 + $clients_site_ids = array();
602 640
603 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
604 - foreach ( $cost_sites as $cost_site ) {
605 - if ( in_array( $cost_site->id, $client_site_ids ) ) {
606 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
607 - $clients_costs[ $client_id ] = array();
608 - }
609 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
610 - $clients_costs_ids[ $client_id ] = array();
611 - }
612 - if ( $get_sites_of_cost ) {
613 - $cost->cost_sites_ids = $cost_sites_ids;
614 - }
615 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
616 - $cost->count_sites = count( $cost_sites );
617 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $cost_sites_ids );
618 - $clients_costs[ $client_id ][] = $cost;
619 - $clients_costs_ids[ $client_id ][] = $cost->id;
620 - }
621 - break;
622 - }
623 - }
624 - }
625 - }
626 - }
627 - }
641 + $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $client_ids );
642 + if ( $client_sites ) {
643 + foreach ( $client_sites as $website ) {
644 + if ( empty( $website->client_id ) ) {
645 + continue;
646 + }
647 + if ( ! isset( $clients_site_ids[ $website->client_id ] ) ) {
648 + $clients_site_ids[ $website->client_id ] = array();
649 + }
650 + $clients_site_ids[ $website->client_id ][] = $website->id;
651 + }
652 + }
628 653
629 - return $clients_costs;
630 - }
654 + global $wpdb;
631 655
656 + $clients_costs = array();
657 + $clients_costs_ids = array();
632 658
633 - /**
634 - * Method get_all_cost_trackers_by_sites().
635 - *
636 - * @param array $sites_ids Sites ids.
637 - *
638 - * @return mixed Result
639 - */
640 - public function get_all_cost_trackers_by_sites( $sites_ids ) { //phpcs:ignore -- complex method.
659 + $cache_key = 'mainwp_cost_tracker_all';
660 + $result = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
641 661
642 - if ( is_string( $sites_ids ) ) {
643 - $sites_ids = explode( ',', $sites_ids );
644 - }
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 + }
645 669
646 - if ( ! is_array( $sites_ids ) || empty( $sites_ids ) ) {
647 - return array();
648 - }
670 + if ( $result ) {
671 + foreach ( $result as $cost ) {
672 + $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
673 + $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
674 + $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
649 675
650 - $sites_ids = array_map( 'intval', $sites_ids );
676 + if ( ! is_array( $sites ) ) {
677 + $sites = array();
678 + }
679 + if ( ! is_array( $groups ) ) {
680 + $groups = array();
681 + }
682 + if ( ! is_array( $clients ) ) {
683 + $clients = array();
684 + }
651 685
652 - global $wpdb;
686 + if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
687 + continue;
688 + }
653 689
654 - $sites_costs = array();
655 - $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
658 - if ( $result ) {
659 - foreach ( $result as $cost ) {
660 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
661 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
662 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
690 + // to reduce db queries for this case.
691 + if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
692 + foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
693 + foreach ( $sites as $siteid ) {
694 + if ( in_array( $siteid, $client_site_ids ) ) {
695 + if ( ! isset( $clients_costs[ $client_id ] ) ) {
696 + $clients_costs[ $client_id ] = array();
697 + }
663 698
664 - if ( ! is_array( $sites ) ) {
665 - $sites = array();
666 - }
667 - if ( ! is_array( $groups ) ) {
668 - $groups = array();
669 - }
670 - if ( ! is_array( $clients ) ) {
671 - $clients = array();
672 - }
699 + if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
700 + $clients_costs_ids[ $client_id ] = array();
701 + }
673 702
674 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
675 - continue;
676 - }
703 + if ( $get_sites_of_cost ) {
704 + $cost->cost_sites_ids = $sites; // sites ids.
705 + }
706 + if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
707 + $cost->count_sites = count( $sites );
708 + $cost->number_client_costs_sites = array_intersect( $client_site_ids, $sites );
709 + $clients_costs[ $client_id ][] = $cost;
710 + $clients_costs_ids[ $client_id ][] = $cost->id;
711 + }
712 + break;
713 + }
714 + }
715 + }
716 + continue;
717 + }
677 718
678 - // to reduce db queries for this case.
679 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
680 - foreach ( $sites_ids as $site_id ) {
681 - if ( in_array( $site_id, $sites ) ) {
682 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
683 - $sites_costs[ $site_id ] = array();
684 - }
719 + $params = array(
720 + 'sites' => $sites,
721 + 'groups' => $groups,
722 + 'clients' => $clients,
723 + );
685 724
686 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
687 - $sites_costs_ids[ $site_id ] = array();
688 - }
725 + // to do: should create relation of: costs, sites, groups, clients in other way.
726 + // to make it more easier in queries.
727 + $cost_sites = MainWP_DB::instance()->get_db_sites( $params ); // get sites of cost tracker.
728 + $cost_sites_ids = array();
689 729
690 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
691 - $cost->count_sites = count( $sites );
692 - $sites_costs[ $site_id ][] = $cost;
693 - $sites_costs_ids[ $site_id ][] = $cost->id;
694 - }
695 - }
696 - }
697 - continue;
698 - }
730 + if ( is_array( $cost_sites ) ) {
699 731
700 - $params = array(
701 - 'sites' => $sites,
702 - 'groups' => $groups,
703 - 'clients' => $clients,
704 - );
732 + foreach ( $cost_sites as $cost_site ) {
733 + $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
734 + }
705 735
706 - // to do: should create relation of: costs, sites, groups, clients in other way.
707 - // to make it more easier in queries.
708 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params );
736 + foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
737 + foreach ( $cost_sites as $cost_site ) {
738 + if ( in_array( $cost_site->id, $client_site_ids ) ) {
739 + if ( ! isset( $clients_costs[ $client_id ] ) ) {
740 + $clients_costs[ $client_id ] = array();
741 + }
742 + if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
743 + $clients_costs_ids[ $client_id ] = array();
744 + }
745 + if ( $get_sites_of_cost ) {
746 + $cost->cost_sites_ids = $cost_sites_ids;
747 + }
748 + if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
749 + $cost->count_sites = count( $cost_sites );
750 + $cost->number_client_costs_sites = array_intersect( $client_site_ids, $cost_sites_ids );
751 + $clients_costs[ $client_id ][] = $cost;
752 + $clients_costs_ids[ $client_id ][] = $cost->id;
753 + }
754 + break;
755 + }
756 + }
757 + }
758 + }
759 + }
760 + }
709 761
710 - if ( is_array( $cost_sites ) ) {
711 - foreach ( $sites_ids as $site_id ) {
712 - foreach ( $cost_sites as $cost_site ) {
713 - if ( (int) $cost_site->id === $site_id ) {
714 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
715 - $sites_costs[ $site_id ] = array();
716 - }
717 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
718 - $sites_costs_ids[ $site_id ] = array();
719 - }
720 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
721 - $cost->count_sites = count( $cost_sites );
722 - $sites_costs[ $site_id ][] = $cost;
723 - $sites_costs_ids[ $site_id ][] = $cost->id;
724 - }
725 - }
726 - }
727 - }
728 - }
729 - }
730 - }
762 + return $clients_costs;
763 + }
731 764
732 - return $sites_costs;
733 - }
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.
734 775
735 - /**
736 - * Method get_summary_data().
737 - *
738 - * @param array $params Params.
739 - *
740 - * @return mixed Result
741 - */
742 - public function get_summary_data( $params = array() ) {
743 - if ( ! is_array( $params ) ) {
744 - $params = array();
745 - }
776 + if ( empty( $cost ) || ! is_object( $cost ) ) {
777 + return array();
778 + }
746 779
747 - global $wpdb;
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.
748 783
749 - $sum_data = isset( $params['sum_data'] ) ? $params['sum_data'] : '';
750 - $where = '';
751 - $sql = '';
752 - if ( 'all' === $sum_data ) {
753 - $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 ';
755 - } else {
756 - return false;
757 - }
758 - return $wpdb->get_results( $sql ); //phpcs:ignore -- good.
759 - }
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 +
808 + /**
809 + * Method get_all_cost_trackers_by_sites().
810 + *
811 + * @param array $sites_ids Sites ids.
812 + *
813 + * @return mixed Result
814 + */
815 + public function get_all_cost_trackers_by_sites( $sites_ids ) { //phpcs:ignore -- NOSONAR - complex method.
816 +
817 + if ( is_string( $sites_ids ) ) {
818 + $sites_ids = explode( ',', $sites_ids );
819 + }
820 +
821 + if ( ! is_array( $sites_ids ) || empty( $sites_ids ) ) {
822 + return array();
823 + }
824 +
825 + $sites_ids = array_map( 'intval', $sites_ids );
826 +
827 + global $wpdb;
828 +
829 + $sites_costs = array();
830 + $sites_costs_ids = array();
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 +
843 + if ( $result ) {
844 + foreach ( $result as $cost ) {
845 + $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
846 + $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
847 + $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
848 +
849 + if ( ! is_array( $sites ) ) {
850 + $sites = array();
851 + }
852 + if ( ! is_array( $groups ) ) {
853 + $groups = array();
854 + }
855 + if ( ! is_array( $clients ) ) {
856 + $clients = array();
857 + }
858 +
859 + if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
860 + continue;
861 + }
862 +
863 + // to reduce db queries for this case.
864 + if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
865 + foreach ( $sites_ids as $site_id ) {
866 + if ( in_array( $site_id, $sites ) ) {
867 + if ( ! isset( $sites_costs[ $site_id ] ) ) {
868 + $sites_costs[ $site_id ] = array();
869 + }
870 +
871 + if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
872 + $sites_costs_ids[ $site_id ] = array();
873 + }
874 +
875 + if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
876 + $cost->count_sites = count( $sites );
877 + $sites_costs[ $site_id ][] = $cost;
878 + $sites_costs_ids[ $site_id ][] = $cost->id;
879 + }
880 + }
881 + }
882 + continue;
883 + }
884 +
885 + $params = array(
886 + 'sites' => $sites,
887 + 'groups' => $groups,
888 + 'clients' => $clients,
889 + );
890 +
891 + // to do: should create relation of: costs, sites, groups, clients in other way.
892 + // to make it more easier in queries.
893 + $cost_sites = MainWP_DB::instance()->get_db_sites( $params );
894 +
895 + if ( is_array( $cost_sites ) ) {
896 + foreach ( $sites_ids as $site_id ) {
897 + foreach ( $cost_sites as $cost_site ) {
898 + if ( (int) $cost_site->id === $site_id ) {
899 + if ( ! isset( $sites_costs[ $site_id ] ) ) {
900 + $sites_costs[ $site_id ] = array();
901 + }
902 + if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
903 + $sites_costs_ids[ $site_id ] = array();
904 + }
905 + if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
906 + $cost->count_sites = count( $cost_sites );
907 + $sites_costs[ $site_id ][] = $cost;
908 + $sites_costs_ids[ $site_id ][] = $cost->id;
909 + }
910 + }
911 + }
912 + }
913 + }
914 + }
915 + }
916 +
917 + return $sites_costs;
918 + }
919 +
920 + /**
921 + * Method get_summary_data().
922 + *
923 + * @param array $params Params.
924 + *
925 + * @return mixed Result
926 + */
927 + public function get_summary_data( $params = array() ) {
928 + if ( ! is_array( $params ) ) {
929 + $params = array();
930 + }
931 +
932 + global $wpdb;
933 +
934 + $sum_data = isset( $params['sum_data'] ) ? $params['sum_data'] : '';
935 + $where = '';
936 + $sql = '';
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 +
951 + $where .= ' AND co.cost_status = "active" AND co.type = "subscription" ';
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;
958 + } else {
959 + return false;
960 + }
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 + }
1055 + }
760 1056 }