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 +950 -430 5.0 → 6.2.2 View file →
@@ -10,75 +10,82 @@
10 10
11 11 use MainWP\Dashboard\MainWP_Install;
12 12 use MainWP\Dashboard\MainWP_Utility;
13 13 use MainWP\Dashboard\MainWP_DB;
14 +use MainWP\Dashboard\MainWP_DB_Client;
15 +use MainWP\Dashboard\MainWP_Exception;
14 16
17 +// Exit if accessed directly.
18 +if ( ! defined( 'ABSPATH' ) ) {
19 + exit;
20 +}
21 +
15 22 /**
16 23 * Class Cost_Tracker_DB
17 24 */
18 25 class Cost_Tracker_DB extends MainWP_Install {
19 - //phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
20 - /**
21 - * Variable to hold the db version.
22 - *
23 - * @var string Version.
24 - */
25 - private $cost_tracker_db_version = '1.0.6';
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';
26 33
27 34
28 - /**
29 - * Static variable to hold the single instance of the class.
30 - *
31 - * @static
32 - *
33 - * @var mixed Default null
34 - */
35 - 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;
36 43
37 - /**
38 - * Get Instance
39 - *
40 - * Creates public static instance.
41 - *
42 - * @static
43 - *
44 - * @return Cost_Tracker_DB
45 - */
46 - public static function get_instance() {
47 - if ( null === self::$instance ) {
48 - self::$instance = new self();
49 - }
50 - return self::$instance;
51 - }
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 + }
52 59
53 - /**
54 - * Init functions.
55 - */
56 - public function init() {
57 - $this->install();
58 - }
60 + /**
61 + * Init functions.
62 + */
63 + public function init() {
64 + $this->install();
65 + }
59 66
60 - /**
61 - * Install db.
62 - */
63 - public function install() {
64 - global $wpdb;
67 + /**
68 + * Install db.
69 + */
70 + public function install() {
71 + global $wpdb;
65 72
66 - $currentVersion = get_site_option( 'mainwp_module_cost_tracker_db_version' );
73 + $currentVersion = get_site_option( 'mainwp_module_cost_tracker_db_version' );
67 74
68 - $rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'cost_tracker' ) . "'" );
69 - if ( empty( self::num_rows( $rslt ) ) ) {
70 - $currentVersion = false;
71 - }
75 + $rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'cost_tracker' ) . "'" );
76 + if ( empty( static::num_rows( $rslt ) ) ) {
77 + $currentVersion = false;
78 + }
72 79
73 - if ( $currentVersion === $this->cost_tracker_db_version ) {
74 - return;
75 - }
80 + if ( $currentVersion === $this->cost_tracker_db_version ) {
81 + return;
82 + }
76 83
77 - $charset_collate = $wpdb->get_charset_collate();
78 - $sql = array();
84 + $charset_collate = $wpdb->get_charset_collate();
85 + $sql = array();
79 86
80 - $tbl = 'CREATE TABLE `' . $this->table_name( 'cost_tracker' ) . '` (
87 + $tbl = 'CREATE TABLE `' . $this->table_name( 'cost_tracker' ) . '` (
81 88 `id` int(11) NOT NULL AUTO_INCREMENT,
82 89 `name` text NOT NULL,
83 90 `url` text NOT NULL,
84 91 `type` varchar(20) NOT NULL,
@@ -86,451 +93,964 @@
86 93 `slug` varchar(191) NOT NULL DEFAULT "",
87 94 `license_type` varchar(20) NOT NULL,
88 95 `cost_status` varchar(20) NOT NULL,
89 96 `payment_method` varchar(50) NOT NULL,
90 -`author` text NOT NULL,
91 -`price` decimal(12,2) NOT NULL,
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,
103 +`cost_icon` varchar(64) NOT NULL DEFAULT "",
104 +`cost_color` varchar(64) NOT NULL DEFAULT "",
96 105 `sites` text NOT NULL,
97 106 `groups` text NOT NULL,
98 107 `clients` text NOT NULL,
99 108 `note` text NOT NULL';
100 109
101 - if ( empty( $currentVersion ) ) {
102 - $tbl .= ',
103 -PRIMARY KEY (`id`) '; }
104 - $tbl .= ') ' . $charset_collate;
105 - $sql[] = $tbl;
110 + if ( empty( $currentVersion ) ) {
111 + $tbl .= ',
112 +PRIMARY KEY (`id`) ';
113 + }
114 + $tbl .= ') ' . $charset_collate;
115 + $sql[] = $tbl;
106 116
107 - require_once ABSPATH . 'wp-admin/includes/upgrade.php';
108 - foreach ( $sql as $query ) {
109 - dbDelta( $query );
110 - }
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 + }
111 124
112 - update_option( 'mainwp_module_cost_tracker_db_version', $this->cost_tracker_db_version );
113 - }
114 125
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 + }
115 143
116 - /**
117 - * Method update_cost_tracker().
118 - *
119 - * @param array $update Cost tracker array data.
120 - *
121 - * @throws \Exception Existed cost tracker error.
122 - * @return mixed Result
123 - */
124 - public function update_cost_tracker( $update ) {
125 - /**
126 - * WP database.
127 - *
128 - * @global object
129 - */
130 - global $wpdb;
144 + if ( version_compare( $current_version, '1.0.13', '<' ) ) {
145 + $costs = $this->get_cost_tracker_by( 'all' );
131 146
132 - if ( ! is_array( $update ) ) {
133 - return false;
134 - }
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 + }
135 161
136 - $id = isset( $update['id'] ) ? $update['id'] : 0;
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 + );
137 173
138 - if ( ! empty( $update['product_type'] ) && ! empty( $update['slug'] ) ) {
139 - if ( in_array( $update['product_type'], array( 'plugin', 'theme' ), true ) ) {
140 - // check existed cost tracker for this plugin / theme .
141 - $current = $this->get_cost_tracker_by( 'slug', $update['slug'], $update['product_type'] );
142 - if ( $current ) {
143 - if ( isset( $update['id'] ) && $current->id !== $update['id'] ) {
144 - $error = esc_html__( 'Existed cost tracker for this plugin. Please try again.', 'mainwp' );
145 - if ( 'theme' === $update['product_type'] ) {
146 - $error = esc_html__( 'Existed cost tracker for this theme. Please try again.', 'mainwp' );
147 - }
148 - throw new \Exception( esc_html( $error ) );
149 - }
150 - $id = $current->id; // to update.
151 - }
152 - }
153 - }
174 + }
175 + }
176 + }
177 + }
178 + }
179 + }
154 180
155 - if ( isset( $update['id'] ) ) {
156 - unset( $update['id'] );
157 - }
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;
158 196
159 - $site_id = isset( $update['site_id'] ) ? $update['site_id'] : '';
197 + if ( ! is_array( $update ) ) {
198 + return false;
199 + }
160 200
161 - if ( ! empty( $id ) ) {
162 - $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
163 - return $this->get_cost_tracker_by( 'id', $id );
164 - } else {
165 - if ( isset( $update['id'] ) ) {
166 - unset( $update['id'] );
167 - }
168 - if ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
169 - return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
170 - }
171 - }
172 - return false;
173 - }
201 + $id = isset( $update['id'] ) ? $update['id'] : 0;
174 202
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 + }
175 224
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 + }
176 234
235 + if ( isset( $update['id'] ) ) {
236 + unset( $update['id'] );
237 + }
177 238
178 - /**
179 - * Method get_cost_tracker_by().
180 - *
181 - * @param string $by Get by.
182 - * @param mixed $value Value.
183 - * @param array $params Others params.
184 - *
185 - * @return mixed Result
186 - */
187 - public function get_cost_tracker_by( $by = 'id', $value = null, $params = array() ) { //phpcs:ignore -- complex method.
188 - global $wpdb;
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 + }
189 250
190 - if ( ! is_array( $params ) ) {
191 - $params = array();
192 - }
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 ) {
193 262
194 - $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
263 + if ( empty( $item_id ) ) {
264 + return false;
265 + }
195 266
196 - if ( 'all' === $by ) {
197 - return $wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) ); //phpcs:ignore -- good.
198 - }
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 + );
199 272
200 - $sql = '';
201 - if ( 'id' === $by && is_numeric( $value ) ) {
202 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id`=%d ', $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
203 - return $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
204 - } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
205 - $cost_ids = explode( ',', $value );
206 - $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
207 - if ( ! empty( $cost_ids ) ) {
208 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id` IN (' . implode( ',', $cost_ids ) . ' )';
209 - }
210 - } elseif ( 'site_id' === $by || 'client_id' === $by ) {
211 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
212 - } elseif ( 'slug' === $by && is_string( $value ) ) {
213 - if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
214 - $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
215 - $result = $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
216 - return $result;
217 - } else {
218 - $where = '';
219 - if ( ! empty( $product_type ) ) {
220 - $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
221 - }
222 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `slug`=%s ' . $where, $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
223 - }
224 - }
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 + }
225 285
226 - $data = array();
227 - if ( ! empty( $sql ) ) {
228 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
229 - if ( $result ) {
230 - if ( 'site_id' === $by ) {
231 - $site_id = intval( $value );
232 - foreach ( $result as $cost ) {
233 - if ( empty( $cost->id ) ) {
234 - continue;
235 - }
236 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
237 - if ( is_array( $sites ) && in_array( $site_id, $sites ) ) {
238 - $data[] = $cost;
239 - }
240 - }
241 - } elseif ( 'client_id' === $by ) {
242 - $client_id = intval( $value );
243 - foreach ( $result as $cost ) {
244 - if ( empty( $cost->id ) ) {
245 - continue;
246 - }
247 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
248 - if ( is_array( $clients ) && in_array( $client_id, $clients ) ) {
249 - $data[] = $cost;
250 - }
251 - }
252 - } else {
253 - foreach ( $result as $cost ) {
254 - if ( empty( $cost->id ) ) {
255 - continue;
256 - }
257 - $data[] = $cost;
258 - }
259 - }
260 - }
261 - }
262 - return $data;
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
292 + return true;
293 + }
265 294
266 - /**
267 - * Method delete_cost_tracker().
268 - *
269 - * @param string $by Delete by.
270 - * @param mixed $value Value.
271 - *
272 - * @return mixed Result
273 - */
274 - public function delete_cost_tracker( $by = 'id', $value = null ) {
275 - global $wpdb;
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.
276 305
277 - if ( empty( $value ) ) {
278 - return false;
279 - }
306 + $allows = array( 'site', 'tag', 'client' );
280 307
281 - if ( 'id' !== $by && 'site_id' !== $by ) {
282 - return false;
283 - }
308 + if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
309 + return false;
310 + }
284 311
285 - if ( 'id' === $by ) {
286 - if ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
287 - return true;
288 - }
289 - } elseif ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE site_id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
290 - return true;
291 - }
292 - return false;
293 - }
312 + $found_look_ids = array();
313 + $existed_look_id = array();
294 314
295 - /**
296 - * Method get_cost_tracker_info_of_clients().
297 - *
298 - * @param array $client_ids Client ids.
299 - * @param array $params Orther params.
300 - *
301 - * @return mixed Result
302 - */
303 - public function get_cost_tracker_info_of_clients( $client_ids, $params = array() ) { //phpcs:ignore -- complex method.
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 + }
304 324
305 - if ( is_string( $client_ids ) ) {
306 - $client_ids = explode( ',', $client_ids );
307 - }
325 + $new_look_ids = array();
308 326
309 - if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
310 - return array();
311 - }
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 + }
312 342
313 - if ( ! is_array( $params ) ) {
314 - $params = array();
315 - }
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;
316 354
317 - $with_sites = isset( $params['with_sites'] ) && $params['with_sites'] ? true : false;
355 + if ( ! is_array( $params ) ) {
356 + $params = array();
357 + }
318 358
319 - $clients_site_ids = array();
320 - $all_sites = MainWP_DB::instance()->get_sites();
359 + $s = '';
360 + $exclude = array();
361 + $include = array();
362 + $status = array();
363 + $types = array();
321 364
322 - if ( $all_sites ) {
323 - foreach ( $all_sites as $site ) {
324 - if ( ! empty( $site['client_id'] ) ) {
325 - if ( in_array( $site['client_id'], $client_ids ) ) { //phpcs:ignore -- in array compare.
326 - if ( ! isset( $clients_site_ids[ $site['client_id'] ] ) ) {
327 - $clients_site_ids[ $site['client_id'] ] = array();
328 - }
329 - $clients_site_ids[ $site['client_id'] ][] = $site['id'];
330 - }
331 - }
332 - }
333 - }
365 + $where = '';
366 + $limit = '';
334 367
335 - global $wpdb;
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();
336 372
337 - $clients_costs = array();
338 - $clients_costs_ids = array();
339 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
340 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- get all cost trackers.
341 - if ( $result ) {
342 - foreach ( $result as $cost ) {
343 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
344 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
345 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
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();
346 376
347 - if ( ! is_array( $sites ) ) {
348 - $sites = array();
349 - }
350 - if ( ! is_array( $groups ) ) {
351 - $groups = array();
352 - }
353 - if ( ! is_array( $clients ) ) {
354 - $clients = array();
355 - }
377 + $page = isset( $params['paged'] ) ? intval( $params['paged'] ) : false;
378 + $per_page = isset( $params['items_per_page'] ) ? intval( $params['items_per_page'] ) : false;
356 379
357 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
358 - continue;
359 - }
380 + if ( ! empty( $s ) ) {
381 + $where .= ' AND ( ct.id LIKE "%' . $this->escape( $s ) . '%" OR ct.name LIKE "%' . $this->escape( $s ) . '%" ) ';
382 + }
360 383
361 - // to reduce db queries for this case.
362 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
363 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
364 - foreach ( $sites as $siteid ) {
365 - if ( in_array( $siteid, $client_site_ids ) ) {
366 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
367 - $clients_costs[ $client_id ] = array();
368 - }
384 + if ( ! empty( $exclude ) ) {
385 + $where .= ' AND ct.id NOT IN (' . implode( ',', $exclude ) . ') ';
386 + }
369 387
370 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
371 - $clients_costs_ids[ $client_id ] = array();
372 - }
388 + if ( ! empty( $include ) ) {
389 + $where .= ' AND ct.id IN (' . implode( ',', $include ) . ') ';
390 + }
373 391
374 - if ( $with_sites ) {
375 - $cost->cost_sites_ids = $sites; // sites ids.
376 - }
377 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
378 - $cost->count_sites = count( $sites );
379 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $sites );
380 - $clients_costs[ $client_id ][] = $cost;
381 - $clients_costs_ids[ $client_id ][] = $cost->id;
382 - }
383 - break;
384 - }
385 - }
386 - }
387 - continue;
388 - }
392 + if ( ! empty( $status ) ) {
393 + $where .= ' AND ct.cost_status IN (' . $this->prepare_fields_array( $status ) . ') ';
394 + }
389 395
390 - $params = array(
391 - 'sites' => $sites,
392 - 'groups' => $groups,
393 - 'clients' => $clients,
394 - );
396 + if ( ! empty( $product_type ) && ! in_array( 'any', $product_type ) ) {
397 + $where .= ' AND ct.product_type IN (' . $this->prepare_fields_array( $product_type ) . ') ';
398 + }
395 399
396 - // to do: should create relation of: costs, sites, groups, clients in other way.
397 - // to make it more easier in queries.
398 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params ); // get sites of cost tracker.
399 - $cost_sites_ids = array();
400 + if ( ! empty( $types ) && ! in_array( 'any', $types ) ) {
401 + $where .= ' AND ct.type IN (' . $this->prepare_fields_array( $types ) . ') ';
402 + }
400 403
401 - if ( is_array( $cost_sites ) ) {
404 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
405 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
406 + }
407 + }
402 408
403 - foreach ( $cost_sites as $cost_site ) {
404 - $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
405 - }
409 + $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
410 + $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
406 411
407 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
408 - foreach ( $cost_sites as $cost_site ) {
409 - if ( in_array( $cost_site->id, $client_site_ids ) ) {
410 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
411 - $clients_costs[ $client_id ] = array();
412 - }
413 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
414 - $clients_costs_ids[ $client_id ] = array();
415 - }
416 - if ( $with_sites ) {
417 - $cost->cost_sites_ids = $cost_sites_ids;
418 - }
419 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
420 - $cost->count_sites = count( $cost_sites );
421 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $cost_sites_ids );
422 - $clients_costs[ $client_id ][] = $cost;
423 - $clients_costs_ids[ $client_id ][] = $cost->id;
424 - }
425 - break;
426 - }
427 - }
428 - }
429 - }
430 - }
431 - }
412 + if ( is_array( $selected_ids ) ) {
413 + $selected_ids = MainWP_Utility::array_numeric_filter( $selected_ids );
414 + } else {
415 + $selected_ids = array();
416 + }
432 417
433 - return $clients_costs;
434 - }
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 + }
435 426
427 + $where = '';
428 + $limit = '';
436 429
437 - /**
438 - * Method get_cost_trackers_info_of_sites().
439 - *
440 - * @param array $sites_ids Sites ids.
441 - *
442 - * @return mixed Result
443 - */
444 - public function get_cost_trackers_info_of_sites( $sites_ids ) { //phpcs:ignore -- complex method.
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 + }
445 437
446 - if ( is_string( $sites_ids ) ) {
447 - $sites_ids = explode( ',', $sites_ids );
448 - }
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 + }
449 457
450 - if ( ! is_array( $sites_ids ) || empty( $sites_ids ) ) {
451 - return array();
452 - }
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 + }
453 481
454 - global $wpdb;
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 + }
455 498
456 - $sites_costs = array();
457 - $sites_costs_ids = array();
458 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
459 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
460 - if ( $result ) {
461 - foreach ( $result as $cost ) {
462 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
463 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
464 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
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 + }
465 506
466 - if ( ! is_array( $sites ) ) {
467 - $sites = array();
468 - }
469 - if ( ! is_array( $groups ) ) {
470 - $groups = array();
471 - }
472 - if ( ! is_array( $clients ) ) {
473 - $clients = array();
474 - }
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 + }
475 545
476 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
477 - continue;
478 - }
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 + }
479 560
480 - // to reduce db queries for this case.
481 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
482 - foreach ( $sites_ids as $site_id ) {
483 - if ( in_array( $site_id, $sites ) ) {
484 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
485 - $sites_costs[ $site_id ] = array();
486 - }
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;
487 571
488 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
489 - $sites_costs_ids[ $site_id ] = array();
490 - }
572 + if ( empty( $value ) ) {
573 + return false;
574 + }
491 575
492 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
493 - $cost->count_sites = count( $sites );
494 - $sites_costs[ $site_id ][] = $cost;
495 - $sites_costs_ids[ $site_id ][] = $cost->id;
496 - }
497 - }
498 - }
499 - continue;
500 - }
576 + if ( 'id' !== $by && 'site_id' !== $by ) {
577 + return false;
578 + }
579 + $deleted = false;
501 580
502 - $params = array(
503 - 'sites' => $sites,
504 - 'groups' => $groups,
505 - 'clients' => $clients,
506 - );
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 + }
507 592
508 - // to do: should create relation of: costs, sites, groups, clients in other way.
509 - // to make it more easier in queries.
510 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params );
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 + }
511 598
512 - if ( is_array( $cost_sites ) ) {
513 - foreach ( $sites_ids as $site_id ) {
514 - foreach ( $cost_sites as $cost_site ) {
515 - if ( in_array( $cost_site->id, $sites_ids ) ) {
516 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
517 - $sites_costs[ $site_id ] = array();
518 - }
519 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
520 - $sites_costs_ids[ $site_id ] = array();
521 - }
522 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
523 - $cost->count_sites = count( $cost_sites );
524 - $sites_costs[ $site_id ][] = $cost;
525 - $sites_costs_ids[ $site_id ][] = $cost->id;
526 - }
527 - }
528 - }
529 - }
530 - }
531 - }
532 - }
599 + $snapshot_info = Cost_Tracker_Admin::get_costs_snapshot_info();
533 600
534 - return $sites_costs;
535 - }
601 + /**
602 + * Action mainwp_cost_tracker_deleted.
603 + *
604 + * @param string $by Delete by.
605 + * @param mixed $value Value.
606 + * @param bool $deleted Deleted.
607 + * @param array $snapshot_info costs snapshot info.
608 + *
609 + * @since 6.0
610 + */
611 + do_action( 'mainwp_cost_tracker_deleted', $by, $value, $deleted, $snapshot_info );
612 +
613 + return $deleted;
614 + }
615 +
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.
625 +
626 + if ( is_string( $client_ids ) ) {
627 + $client_ids = explode( ',', $client_ids );
628 + }
629 +
630 + if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
631 + return array();
632 + }
633 +
634 + if ( ! is_array( $params ) ) {
635 + $params = array();
636 + }
637 +
638 + $get_sites_of_cost = isset( $params['get_cost_sites'] ) && $params['get_cost_sites'] ? true : false;
639 + $clients_site_ids = array();
640 +
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 + }
653 +
654 + global $wpdb;
655 +
656 + $clients_costs = array();
657 + $clients_costs_ids = array();
658 +
659 + $cache_key = 'mainwp_cost_tracker_all';
660 + $result = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
661 +
662 + if ( false === $result ) {
663 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
664 + $sql = "SELECT * FROM {$table}";
665 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql(); results cached immediately after retrieval
666 + $result = $wpdb->get_results( $sql, OBJECT );
667 + wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
668 + }
669 +
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();
675 +
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 + }
685 +
686 + if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
687 + continue;
688 + }
689 +
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 + }
698 +
699 + if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
700 + $clients_costs_ids[ $client_id ] = array();
701 + }
702 +
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 + }
718 +
719 + $params = array(
720 + 'sites' => $sites,
721 + 'groups' => $groups,
722 + 'clients' => $clients,
723 + );
724 +
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();
729 +
730 + if ( is_array( $cost_sites ) ) {
731 +
732 + foreach ( $cost_sites as $cost_site ) {
733 + $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
734 + }
735 +
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 + }
761 +
762 + return $clients_costs;
763 + }
764 +
765 + /**
766 + * Method get_sites_of_cost().
767 + *
768 + * Get sites of cost.
769 + *
770 + * @param object $cost Cost data.
771 + *
772 + * @return mixed Result
773 + */
774 + public function get_sites_of_cost( $cost ) { //phpcs:ignore -- NOSONAR - complex method.
775 +
776 + if ( empty( $cost ) || ! is_object( $cost ) ) {
777 + return array();
778 + }
779 +
780 + $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array(); // NOSONAR -- duplicate.
781 + $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array(); // NOSONAR -- duplicate.
782 + $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array(); // NOSONAR -- duplicate.
783 +
784 + if ( ! is_array( $sites ) ) { // NOSONAR -- duplicate.
785 + $sites = array(); // NOSONAR -- duplicate.
786 + }
787 + if ( ! is_array( $groups ) ) { // NOSONAR -- duplicate.
788 + $groups = array(); // NOSONAR -- duplicate.
789 + }
790 + if ( ! is_array( $clients ) ) { // NOSONAR -- duplicate.
791 + $clients = array(); // NOSONAR -- duplicate.
792 + }
793 +
794 + if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) { // NOSONAR -- duplicate.
795 + return array(); // NOSONAR -- duplicate.
796 + }
797 +
798 + $params = array( // NOSONAR -- duplicate.
799 + 'sites' => $sites, // NOSONAR -- duplicate.
800 + 'groups' => $groups, // NOSONAR -- duplicate.
801 + 'clients' => $clients, // NOSONAR -- duplicate.
802 + );
803 +
804 + return MainWP_DB::instance()->get_db_sites( $params ); // NOSONAR - get sites of cost tracker.
805 + }
806 +
807 +
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 + }
536 1056 }