PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 6.2.2
MainWP Dashboard: Self-hosted WordPress Management for Agencies v6.2.2
6.2.3 6.2.2 6.2.1 6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 5.2 All 156 releases
← All changes | modules/cost-tracker/classes/class-cost-tracker-db.php +949 -446 5.0.1 → 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,468 +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 ( is_array( $current ) && ! empty( $current ) ) {
143 - $existed = false;
144 - if ( 1 === count( $current ) ) {
145 - $current = current( $current );
146 - if ( is_object( $current ) ) {
147 - if ( ! empty( $update['id'] ) ) {
148 - if ( ! empty( $current->id ) && (int) $current->id !== (int) $update['id'] ) {
149 - $existed = true; // to fix.
150 - } elseif ( ! empty( $current->id ) ) {
151 - $id = $current->id; // to update.
152 - }
153 - } else {
154 - $existed = true; // to fix: existed one.
155 - }
156 - }
157 - } else {
158 - $existed = true; // to fix found multi items.
159 - }
174 + }
175 + }
176 + }
177 + }
178 + }
179 + }
160 180
161 - if ( $existed ) {
162 - $error = esc_html__( 'A cost tracker for this plugin already exists.', 'mainwp' );
163 - if ( 'theme' === $update['product_type'] ) {
164 - $error = esc_html__( 'A cost tracker for this theme already exists.', 'mainwp' );
165 - }
166 - throw new \Exception( esc_html( $error ) );
167 - }
168 - }
169 - }
170 - }
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;
171 196
172 - if ( isset( $update['id'] ) ) {
173 - unset( $update['id'] );
174 - }
197 + if ( ! is_array( $update ) ) {
198 + return false;
199 + }
175 200
176 - $site_id = isset( $update['site_id'] ) ? $update['site_id'] : '';
201 + $id = isset( $update['id'] ) ? $update['id'] : 0;
177 202
178 - if ( ! empty( $id ) ) {
179 - $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
180 - return $this->get_cost_tracker_by( 'id', $id );
181 - } else {
182 - if ( isset( $update['id'] ) ) {
183 - unset( $update['id'] );
184 - }
185 - if ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
186 - return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
187 - }
188 - }
189 - return false;
190 - }
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 + }
191 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 + }
192 234
235 + if ( isset( $update['id'] ) ) {
236 + unset( $update['id'] );
237 + }
193 238
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 + }
194 250
195 - /**
196 - * Method get_cost_tracker_by().
197 - *
198 - * @param string $by Get by.
199 - * @param mixed $value Value.
200 - * @param array $params Others params.
201 - *
202 - * @return mixed Result
203 - */
204 - public function get_cost_tracker_by( $by = 'id', $value = null, $params = array() ) { //phpcs:ignore -- complex method.
205 - global $wpdb;
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 ) {
206 262
207 - if ( ! is_array( $params ) ) {
208 - $params = array();
209 - }
263 + if ( empty( $item_id ) ) {
264 + return false;
265 + }
210 266
211 - $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
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 + );
212 272
213 - if ( 'all' === $by ) {
214 - return $wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) ); //phpcs:ignore -- good.
215 - }
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 + }
216 285
217 - $sql = '';
218 - if ( 'id' === $by && is_numeric( $value ) ) {
219 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id`=%d ', $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
220 - return $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
221 - } elseif ( 'id' === $by && false !== strpos( $value, ',' ) ) {
222 - $cost_ids = explode( ',', $value );
223 - $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
224 - if ( ! empty( $cost_ids ) ) {
225 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `id` IN (' . implode( ',', $cost_ids ) . ' )';
226 - }
227 - } elseif ( 'site_id' === $by || 'client_id' === $by ) {
228 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
229 - } elseif ( 'slug' === $by && is_string( $value ) ) {
230 - if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
231 - $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
232 - $result = $wpdb->get_row( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
233 - return $result;
234 - } else {
235 - $where = '';
236 - if ( ! empty( $product_type ) ) {
237 - $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
238 - }
239 - $sql = $wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE `slug`=%s ' . $where, $value ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
240 - }
241 - }
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 + }
242 291
243 - $data = array();
244 - if ( ! empty( $sql ) ) {
245 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
246 - if ( $result ) {
247 - if ( 'site_id' === $by ) {
248 - $site_id = intval( $value );
249 - foreach ( $result as $cost ) {
250 - if ( empty( $cost->id ) ) {
251 - continue;
252 - }
253 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
254 - if ( is_array( $sites ) && in_array( $site_id, $sites ) ) {
255 - $data[] = $cost;
256 - }
257 - }
258 - } elseif ( 'client_id' === $by ) {
259 - $client_id = intval( $value );
260 - foreach ( $result as $cost ) {
261 - if ( empty( $cost->id ) ) {
262 - continue;
263 - }
264 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
265 - if ( is_array( $clients ) && in_array( $client_id, $clients ) ) {
266 - $data[] = $cost;
267 - }
268 - }
269 - } else {
270 - foreach ( $result as $cost ) {
271 - if ( empty( $cost->id ) ) {
272 - continue;
273 - }
274 - $data[] = $cost;
275 - }
276 - }
277 - }
278 - }
279 - return $data;
280 - }
292 + return true;
293 + }
281 294
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.
282 305
283 - /**
284 - * Method delete_cost_tracker().
285 - *
286 - * @param string $by Delete by.
287 - * @param mixed $value Value.
288 - *
289 - * @return mixed Result
290 - */
291 - public function delete_cost_tracker( $by = 'id', $value = null ) {
292 - global $wpdb;
306 + $allows = array( 'site', 'tag', 'client' );
293 307
294 - if ( empty( $value ) ) {
295 - return false;
296 - }
308 + if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
309 + return false;
310 + }
297 311
298 - if ( 'id' !== $by && 'site_id' !== $by ) {
299 - return false;
300 - }
312 + $found_look_ids = array();
313 + $existed_look_id = array();
301 314
302 - if ( 'id' === $by ) {
303 - if ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
304 - return true;
305 - }
306 - } elseif ( $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'cost_tracker' ) . ' WHERE site_id=%d ', $value ) ) ) { //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
307 - return true;
308 - }
309 - return false;
310 - }
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 + }
311 324
312 - /**
313 - * Method get_cost_tracker_info_of_clients().
314 - *
315 - * @param array $client_ids Client ids.
316 - * @param array $params Orther params.
317 - *
318 - * @return mixed Result
319 - */
320 - public function get_cost_tracker_info_of_clients( $client_ids, $params = array() ) { //phpcs:ignore -- complex method.
325 + $new_look_ids = array();
321 326
322 - if ( is_string( $client_ids ) ) {
323 - $client_ids = explode( ',', $client_ids );
324 - }
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 + }
325 342
326 - if ( ! is_array( $client_ids ) || empty( $client_ids ) ) {
327 - return array();
328 - }
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;
329 354
330 - if ( ! is_array( $params ) ) {
331 - $params = array();
332 - }
355 + if ( ! is_array( $params ) ) {
356 + $params = array();
357 + }
333 358
334 - $with_sites = isset( $params['with_sites'] ) && $params['with_sites'] ? true : false;
359 + $s = '';
360 + $exclude = array();
361 + $include = array();
362 + $status = array();
363 + $types = array();
335 364
336 - $clients_site_ids = array();
337 - $all_sites = MainWP_DB::instance()->get_sites();
365 + $where = '';
366 + $limit = '';
338 367
339 - if ( $all_sites ) {
340 - foreach ( $all_sites as $site ) {
341 - if ( ! empty( $site['client_id'] ) ) {
342 - if ( in_array( $site['client_id'], $client_ids ) ) { //phpcs:ignore -- in array compare.
343 - if ( ! isset( $clients_site_ids[ $site['client_id'] ] ) ) {
344 - $clients_site_ids[ $site['client_id'] ] = array();
345 - }
346 - $clients_site_ids[ $site['client_id'] ][] = $site['id'];
347 - }
348 - }
349 - }
350 - }
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();
351 372
352 - global $wpdb;
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 - $clients_costs = array();
355 - $clients_costs_ids = array();
356 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
357 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- get all cost trackers.
358 - if ( $result ) {
359 - foreach ( $result as $cost ) {
360 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
361 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
362 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
377 + $page = isset( $params['paged'] ) ? intval( $params['paged'] ) : false;
378 + $per_page = isset( $params['items_per_page'] ) ? intval( $params['items_per_page'] ) : false;
363 379
364 - if ( ! is_array( $sites ) ) {
365 - $sites = array();
366 - }
367 - if ( ! is_array( $groups ) ) {
368 - $groups = array();
369 - }
370 - if ( ! is_array( $clients ) ) {
371 - $clients = array();
372 - }
380 + if ( ! empty( $s ) ) {
381 + $where .= ' AND ( ct.id LIKE "%' . $this->escape( $s ) . '%" OR ct.name LIKE "%' . $this->escape( $s ) . '%" ) ';
382 + }
373 383
374 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
375 - continue;
376 - }
384 + if ( ! empty( $exclude ) ) {
385 + $where .= ' AND ct.id NOT IN (' . implode( ',', $exclude ) . ') ';
386 + }
377 387
378 - // to reduce db queries for this case.
379 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
380 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
381 - foreach ( $sites as $siteid ) {
382 - if ( in_array( $siteid, $client_site_ids ) ) {
383 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
384 - $clients_costs[ $client_id ] = array();
385 - }
388 + if ( ! empty( $include ) ) {
389 + $where .= ' AND ct.id IN (' . implode( ',', $include ) . ') ';
390 + }
386 391
387 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
388 - $clients_costs_ids[ $client_id ] = array();
389 - }
392 + if ( ! empty( $status ) ) {
393 + $where .= ' AND ct.cost_status IN (' . $this->prepare_fields_array( $status ) . ') ';
394 + }
390 395
391 - if ( $with_sites ) {
392 - $cost->cost_sites_ids = $sites; // sites ids.
393 - }
394 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
395 - $cost->count_sites = count( $sites );
396 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $sites );
397 - $clients_costs[ $client_id ][] = $cost;
398 - $clients_costs_ids[ $client_id ][] = $cost->id;
399 - }
400 - break;
401 - }
402 - }
403 - }
404 - continue;
405 - }
396 + if ( ! empty( $product_type ) && ! in_array( 'any', $product_type ) ) {
397 + $where .= ' AND ct.product_type IN (' . $this->prepare_fields_array( $product_type ) . ') ';
398 + }
406 399
407 - $params = array(
408 - 'sites' => $sites,
409 - 'groups' => $groups,
410 - 'clients' => $clients,
411 - );
400 + if ( ! empty( $types ) && ! in_array( 'any', $types ) ) {
401 + $where .= ' AND ct.type IN (' . $this->prepare_fields_array( $types ) . ') ';
402 + }
412 403
413 - // to do: should create relation of: costs, sites, groups, clients in other way.
414 - // to make it more easier in queries.
415 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params ); // get sites of cost tracker.
416 - $cost_sites_ids = array();
404 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
405 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
406 + }
407 + }
417 408
418 - if ( is_array( $cost_sites ) ) {
409 + $product_type = isset( $params['product_type'] ) ? $params['product_type'] : '';
410 + $selected_ids = isset( $params['selected_ids'] ) ? $params['selected_ids'] : array();
419 411
420 - foreach ( $cost_sites as $cost_site ) {
421 - $cost_sites_ids[] = $cost_site->id; // sites ids of cost tracker.
422 - }
412 + if ( is_array( $selected_ids ) ) {
413 + $selected_ids = MainWP_Utility::array_numeric_filter( $selected_ids );
414 + } else {
415 + $selected_ids = array();
416 + }
423 417
424 - foreach ( $clients_site_ids as $client_id => $client_site_ids ) {
425 - foreach ( $cost_sites as $cost_site ) {
426 - if ( in_array( $cost_site->id, $client_site_ids ) ) {
427 - if ( ! isset( $clients_costs[ $client_id ] ) ) {
428 - $clients_costs[ $client_id ] = array();
429 - }
430 - if ( ! isset( $clients_costs_ids[ $client_id ] ) ) {
431 - $clients_costs_ids[ $client_id ] = array();
432 - }
433 - if ( $with_sites ) {
434 - $cost->cost_sites_ids = $cost_sites_ids;
435 - }
436 - if ( ! in_array( $cost->id, $clients_costs_ids[ $client_id ] ) ) {
437 - $cost->count_sites = count( $cost_sites );
438 - $cost->number_client_costs_sites = array_intersect( $client_site_ids, $cost_sites_ids );
439 - $clients_costs[ $client_id ][] = $cost;
440 - $clients_costs_ids[ $client_id ][] = $cost->id;
441 - }
442 - break;
443 - }
444 - }
445 - }
446 - }
447 - }
448 - }
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 + }
449 426
450 - return $clients_costs;
451 - }
427 + $where = '';
428 + $limit = '';
452 429
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 + }
453 437
454 - /**
455 - * Method get_cost_trackers_info_of_sites().
456 - *
457 - * @param array $sites_ids Sites ids.
458 - *
459 - * @return mixed Result
460 - */
461 - public function get_cost_trackers_info_of_sites( $sites_ids ) { //phpcs:ignore -- complex method.
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 + }
462 457
463 - if ( is_string( $sites_ids ) ) {
464 - $sites_ids = explode( ',', $sites_ids );
465 - }
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 + }
466 481
467 - if ( ! is_array( $sites_ids ) || empty( $sites_ids ) ) {
468 - return array();
469 - }
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 + }
470 498
471 - global $wpdb;
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 + }
472 506
473 - $sites_costs = array();
474 - $sites_costs_ids = array();
475 - $sql = 'SELECT * FROM ' . $this->table_name( 'cost_tracker' );
476 - $result = $wpdb->get_results( $sql, OBJECT ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
477 - if ( $result ) {
478 - foreach ( $result as $cost ) {
479 - $sites = ! empty( $cost->sites ) ? json_decode( $cost->sites, true ) : array();
480 - $groups = ! empty( $cost->groups ) ? json_decode( $cost->groups, true ) : array();
481 - $clients = ! empty( $cost->clients ) ? json_decode( $cost->clients, true ) : array();
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 + }
482 545
483 - if ( ! is_array( $sites ) ) {
484 - $sites = array();
485 - }
486 - if ( ! is_array( $groups ) ) {
487 - $groups = array();
488 - }
489 - if ( ! is_array( $clients ) ) {
490 - $clients = array();
491 - }
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 + }
492 560
493 - if ( empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
494 - continue;
495 - }
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;
496 571
497 - // to reduce db queries for this case.
498 - if ( ! empty( $sites ) && empty( $groups ) && empty( $clients ) ) {
499 - foreach ( $sites_ids as $site_id ) {
500 - if ( in_array( $site_id, $sites ) ) {
501 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
502 - $sites_costs[ $site_id ] = array();
503 - }
572 + if ( empty( $value ) ) {
573 + return false;
574 + }
504 575
505 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
506 - $sites_costs_ids[ $site_id ] = array();
507 - }
576 + if ( 'id' !== $by && 'site_id' !== $by ) {
577 + return false;
578 + }
579 + $deleted = false;
508 580
509 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
510 - $cost->count_sites = count( $sites );
511 - $sites_costs[ $site_id ][] = $cost;
512 - $sites_costs_ids[ $site_id ][] = $cost->id;
513 - }
514 - }
515 - }
516 - continue;
517 - }
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 + }
518 592
519 - $params = array(
520 - 'sites' => $sites,
521 - 'groups' => $groups,
522 - 'clients' => $clients,
523 - );
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 + }
524 598
525 - // to do: should create relation of: costs, sites, groups, clients in other way.
526 - // to make it more easier in queries.
527 - $cost_sites = MainWP_DB::instance()->get_db_sites( $params );
599 + $snapshot_info = Cost_Tracker_Admin::get_costs_snapshot_info();
528 600
529 - if ( is_array( $cost_sites ) ) {
530 - foreach ( $sites_ids as $site_id ) {
531 - foreach ( $cost_sites as $cost_site ) {
532 - if ( $cost_site->id === $site_id ) {
533 - if ( ! isset( $sites_costs[ $site_id ] ) ) {
534 - $sites_costs[ $site_id ] = array();
535 - }
536 - if ( ! isset( $sites_costs_ids[ $site_id ] ) ) {
537 - $sites_costs_ids[ $site_id ] = array();
538 - }
539 - if ( ! in_array( $cost->id, $sites_costs_ids[ $site_id ] ) ) {
540 - $cost->count_sites = count( $cost_sites );
541 - $sites_costs[ $site_id ][] = $cost;
542 - $sites_costs_ids[ $site_id ][] = $cost->id;
543 - }
544 - }
545 - }
546 - }
547 - }
548 - }
549 - }
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 );
550 612
551 - return $sites_costs;
552 - }
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 + }
553 1056 }