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.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 +919 -626 5.0.3 → 6.2.2 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,665 +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 ( 'count' === $by ) {
397 - $sql = 'SELECT count(*) FROM ' . $this->table_name( 'cost_tracker' ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
398 - return $wpdb->get_var( $sql ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
399 - } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
400 - $cost_ids = explode( ',', $value );
401 - $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
402 - if ( ! empty( $cost_ids ) ) {
403 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id` IN (' . implode( ',', $cost_ids ) . ' )';
404 - }
405 - } elseif ( 'site_id' === $by || 'client_id' === $by ) {
406 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
407 - } elseif ( 'slug' === $by && is_string( $value ) ) {
408 - if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
409 - $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
410 - $result = $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
411 - return $result;
412 - } else {
413 - $where = '';
414 - if ( ! empty( $product_type ) ) {
415 - $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
416 - }
417 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `slug`=%s ' . $where, $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
418 - }
419 - }
400 + if ( ! empty( $types ) && ! in_array( 'any', $types ) ) {
401 + $where .= ' AND ct.type IN (' . $this->prepare_fields_array( $types ) . ') ';
402 + }
420 403
421 - $data = array();
422 - if ( ! empty( $sql ) ) {
423 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
424 - if ( $result ) {
425 - if ( 'site_id' === $by ) {
426 - $site_id = intval( $value );
427 - foreach ( $result as $cost ) {
428 - if ( empty( $cost->id ) ) {
429 - continue;
430 - }
431 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
432 - if ( is_array( $sites ) && in_array( $site_id, $sites ) ) {
433 - $data[] = $cost;
434 - }
435 - }
436 - } elseif ( 'client_id' === $by ) {
437 - $client_id = intval( $value );
438 - foreach ( $result as $cost ) {
439 - if ( empty( $cost->id ) ) {
440 - continue;
441 - }
442 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
443 - if ( is_array( $clients ) && in_array( $client_id, $clients ) ) {
444 - $data[] = $cost;
445 - }
446 - }
447 - } else {
448 - foreach ( $result as $cost ) {
449 - if ( empty( $cost->id ) ) {
450 - continue;
451 - }
452 - $data[] = $cost;
453 - }
454 - }
455 - }
456 - }
457 - return $data;
458 - }
404 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
405 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
406 + }
407 + }
459 408
409 + $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
410 + $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
460 411
461 - /**
462 - * Method delete_cost_tracker().
463 - *
464 - * @param string $by Delete by.
465 - * @param mixed $value Value.
466 - *
467 - * @return mixed Result
468 - */
469 - public function delete_cost_tracker( $by = 'id', $value = null ) {
470 - 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 + }
471 417
472 - if ( empty( $value ) ) {
473 - return false;
474 - }
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 + }
475 426
476 - if ( 'id' !== $by && 'site_id' !== $by ) {
477 - return false;
478 - }
479 - $deleted = false;
427 + $where = '';
428 + $limit = '';
480 429
481 - if ( 'id' === $by ) {
482 - if ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
483 - $deleted = true;
484 - }
485 - } elseif ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE site_id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
486 - $deleted = true;
487 - }
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 + }
488 437
489 - if ( $deleted ) {
490 - $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
491 - }
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 + }
492 457
493 - return $deleted;
494 - }
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 + }
495 481
496 - /**
497 - * Method get_all_cost_trackers_by_clients().
498 - *
499 - * @param array $client_ids Client ids.
500 - * @param array $params Orther params.
501 - *
502 - * @return mixed Result
503 - */
504 - 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 + }
505 498
506 - if ( is_string( $client_ids ) ) {
507 - $client_ids = explode( ',', $client_ids );
508 - }
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 + }
509 506
510 - if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
511 - return array();
512 - }
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 + }
513 545
514 - if ( ! is_array( $params ) ) {
515 - $params = array();
516 - }
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 + }
517 560
518 - $get_sites_of_cost = isset( $params['get_cost_sites'] ) && $params['get_cost_sites'] ? true : false;
519 - $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;
520 571
521 - $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $client_ids );
522 - if ( $client_sites ) {
523 - foreach ( $client_sites as $website ) {
524 - if ( ! empty( $website->client_id ) ) {
525 - continue;
526 - }
527 - if ( ! isset( $clients_site_ids[ $website->client_id ] ) ) {
528 - $clients_site_ids[ $website->client_id ] = array();
529 - }
530 - $clients_site_ids[ $website->client_id ][] = $website->id;
531 - }
532 - }
572 + if ( empty( $value ) ) {
573 + return false;
574 + }
533 575
534 - global $wpdb;
576 + if ( 'id' !== $by && 'site_id' !== $by ) {
577 + return false;
578 + }
579 + $deleted = false;
535 580
536 - $clients_costs = array();
537 - $clients_costs_ids = array();
538 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
539 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- get all cost trackers.
540 - if ( $result ) {
541 - foreach ( $result as $cost ) {
542 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
543 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
544 - $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 + }
545 592
546 - if ( ! is_array( $sites ) ) {
547 - $sites = array();
548 - }
549 - if ( ! is_array( $groups ) ) {
550 - $groups = array();
551 - }
552 - if ( ! is_array( $clients ) ) {
553 - $clients = array();
554 - }
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 + }
555 598
556 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
557 - continue;
558 - }
599 + $snapshot_info = Cost_Tracker_Admin::get_costs_snapshot_info();
559 600
560 - // to reduce db queries for this case.
561 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
562 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
563 - foreach ( $sites as $siteid ) {
564 - if ( in_array( $siteid, $client_site_ids ) ) {
565 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
566 - $clients_costs[ $client_id ] = array();
567 - }
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 );
568 612
569 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
570 - $clients_costs_ids[ $client_id ] = array();
571 - }
613 + return $deleted;
614 + }
572 615
573 - if ( $get_sites_of_cost ) {
574 - $cost->cost_sites_ids = $sites; // sites ids.
575 - }
576 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
577 - $cost->count_sites = count( $sites );
578 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $sites );
579 - $clients_costs[ $client_id ][] = $cost;
580 - $clients_costs_ids[ $client_id ][] = $cost->id;
581 - }
582 - break;
583 - }
584 - }
585 - }
586 - continue;
587 - }
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.
588 625
589 - $params = array(
590 - 'sites' => $sites,
591 - 'groups' => $groups,
592 - 'clients' => $clients,
593 - );
626 + if ( is_string( $client_ids ) ) {
627 + $client_ids = explode( ',', $client_ids );
628 + }
594 629
595 - // to do: should create relation of: costs, sites, groups, clients in other way.
596 - // to make it more easier in queries.
597 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params ); // get sites of cost tracker.
598 - $cost_sites_ids = array();
630 + if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
631 + return array();
632 + }
599 633
600 - if ( is_array( $cost_sites ) ) {
634 + if ( ! is_array( $params ) ) {
635 + $params = array();
636 + }
601 637
602 - foreach ( $cost_sites as $cost_site ) {
603 - $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
604 - }
638 + $get_sites_of_cost = isset( $params['get_cost_sites'] ) && $params['get_cost_sites'] ? true : false;
639 + $clients_site_ids = array();
605 640
606 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
607 - foreach ( $cost_sites as $cost_site ) {
608 - if ( in_array( $cost_site->id, $client_site_ids ) ) {
609 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
610 - $clients_costs[ $client_id ] = array();
611 - }
612 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
613 - $clients_costs_ids[ $client_id ] = array();
614 - }
615 - if ( $get_sites_of_cost ) {
616 - $cost->cost_sites_ids = $cost_sites_ids;
617 - }
618 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
619 - $cost->count_sites = count( $cost_sites );
620 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $cost_sites_ids );
621 - $clients_costs[ $client_id ][] = $cost;
622 - $clients_costs_ids[ $client_id ][] = $cost->id;
623 - }
624 - break;
625 - }
626 - }
627 - }
628 - }
629 - }
630 - }
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 + }
631 653
632 - return $clients_costs;
633 - }
654 + global $wpdb;
634 655
656 + $clients_costs = array();
657 + $clients_costs_ids = array();
635 658
636 - /**
637 - * Method get_all_cost_trackers_by_sites().
638 - *
639 - * @param array $sites_ids Sites ids.
640 - *
641 - * @return mixed Result
642 - */
643 - 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' );
644 661
645 - if ( is_string( $sites_ids ) ) {
646 - $sites_ids = explode( ',', $sites_ids );
647 - }
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 + }
648 669
649 - if ( ! is_array( $sites_ids ) || empty( $sites_ids ) ) {
650 - return array();
651 - }
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();
652 675
653 - $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 + }
654 685
655 - global $wpdb;
686 + if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
687 + continue;
688 + }
656 689
657 - $sites_costs = array();
658 - $sites_costs_ids = array();
659 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
660 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
661 - if ( $result ) {
662 - foreach ( $result as $cost ) {
663 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
664 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
665 - $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 + }
666 698
667 - if ( ! is_array( $sites ) ) {
668 - $sites = array();
669 - }
670 - if ( ! is_array( $groups ) ) {
671 - $groups = array();
672 - }
673 - if ( ! is_array( $clients ) ) {
674 - $clients = array();
675 - }
699 + if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
700 + $clients_costs_ids[ $client_id ] = array();
701 + }
676 702
677 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
678 - continue;
679 - }
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 + }
680 718
681 - // to reduce db queries for this case.
682 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
683 - foreach ( $sites_ids as $site_id ) {
684 - if ( in_array( $site_id, $sites ) ) {
685 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
686 - $sites_costs[ $site_id ] = array();
687 - }
719 + $params = array(
720 + 'sites' => $sites,
721 + 'groups' => $groups,
722 + 'clients' => $clients,
723 + );
688 724
689 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
690 - $sites_costs_ids[ $site_id ] = array();
691 - }
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();
692 729
693 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
694 - $cost->count_sites = count( $sites );
695 - $sites_costs[ $site_id ][] = $cost;
696 - $sites_costs_ids[ $site_id ][] = $cost->id;
697 - }
698 - }
699 - }
700 - continue;
701 - }
730 + if ( is_array( $cost_sites ) ) {
702 731
703 - $params = array(
704 - 'sites' => $sites,
705 - 'groups' => $groups,
706 - 'clients' => $clients,
707 - );
732 + foreach ( $cost_sites as $cost_site ) {
733 + $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
734 + }
708 735
709 - // to do: should create relation of: costs, sites, groups, clients in other way.
710 - // to make it more easier in queries.
711 - $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 + }
712 761
713 - if ( is_array( $cost_sites ) ) {
714 - foreach ( $sites_ids as $site_id ) {
715 - foreach ( $cost_sites as $cost_site ) {
716 - if ( (int) $cost_site->id === $site_id ) {
717 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
718 - $sites_costs[ $site_id ] = array();
719 - }
720 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
721 - $sites_costs_ids[ $site_id ] = array();
722 - }
723 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
724 - $cost->count_sites = count( $cost_sites );
725 - $sites_costs[ $site_id ][] = $cost;
726 - $sites_costs_ids[ $site_id ][] = $cost->id;
727 - }
728 - }
729 - }
730 - }
731 - }
732 - }
733 - }
762 + return $clients_costs;
763 + }
734 764
735 - return $sites_costs;
736 - }
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.
737 775
738 - /**
739 - * Method get_summary_data().
740 - *
741 - * @param array $params Params.
742 - *
743 - * @return mixed Result
744 - */
745 - public function get_summary_data( $params = array() ) {
746 - if ( ! is_array( $params ) ) {
747 - $params = array();
748 - }
776 + if ( empty( $cost ) || ! is_object( $cost ) ) {
777 + return array();
778 + }
749 779
750 - 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.
751 783
752 - $sum_data = isset( $params['sum_data'] ) ? $params['sum_data'] : '';
753 - $where = '';
754 - $sql = '';
755 - if ( 'all' === $sum_data ) {
756 - $where .= ' AND co.cost_status = "active" AND co.type = "subscription" ';
757 - $sql .= 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' co WHERE 1 ' . $where . ' ORDER BY co.next_renewal ASC ';
758 - } else {
759 - return false;
760 - }
761 - return $wpdb->get_results( $sql ); //phpcs:ignore -- good.
762 - }
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 + }
763 1056 }