| 1 |
<?php |
| 2 |
/** |
| 3 |
* MainWP Module Cost Tracker DB class. |
| 4 |
* |
| 5 |
* @package MainWP\Dashboard |
| 6 |
* @version 4.6 |
| 7 |
*/ |
| 8 |
|
| 9 |
namespace MainWP\Dashboard\Module\CostTracker; |
| 10 |
|
| 11 |
use MainWP\Dashboard\MainWP_Install; |
| 12 |
use MainWP\Dashboard\MainWP_Utility; |
| 13 |
use MainWP\Dashboard\MainWP_DB; |
| 14 |
use MainWP\Dashboard\MainWP_DB_Client; |
| 15 |
|
| 16 |
// Exit if accessed directly. |
| 17 |
if ( ! defined( 'ABSPATH' ) ) { |
| 18 |
exit; |
| 19 |
} |
| 20 |
|
| 21 |
|
| 22 |
/** |
| 23 |
* Class Cost_Tracker_DB_Query |
| 24 |
*/ |
| 25 |
class Cost_Tracker_DB_Query extends Cost_Tracker_DB { |
| 26 |
//phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery |
| 27 |
|
| 28 |
/** |
| 29 |
* Private 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; |
| 36 |
|
| 37 |
|
| 38 |
/** |
| 39 |
* Method instance() |
| 40 |
* |
| 41 |
* Return public static instance. |
| 42 |
* |
| 43 |
* @static |
| 44 |
* @return instance of class. |
| 45 |
*/ |
| 46 |
public static function instance() { |
| 47 |
if ( null === static::$instance ) { |
| 48 |
static::$instance = new self(); |
| 49 |
} |
| 50 |
return static::$instance; |
| 51 |
} |
| 52 |
|
| 53 |
/** |
| 54 |
* Query costs records |
| 55 |
* |
| 56 |
* @param array $args Arguments to filter the records by. |
| 57 |
* |
| 58 |
* @return array Records |
| 59 |
*/ |
| 60 |
public function query_costs( $args ) { //phpcs:ignore -- NOSONAR - complex. |
| 61 |
global $wpdb; |
| 62 |
|
| 63 |
$all_defaults = Cost_Tracker_Admin::get_default_fields_values(); |
| 64 |
|
| 65 |
$filter_site_ids = ! empty( $args['filter_sites'] ) ? $args['filter_sites'] : array(); |
| 66 |
$filter_client_ids = ! empty( $args['filter_clients'] ) ? $args['filter_clients'] : array(); |
| 67 |
|
| 68 |
$filter_prod_type_slugs = ! empty( $args['filter_prods_types'] ) ? $args['filter_prods_types'] : false; |
| 69 |
$filter_cost_state = ! empty( $args['filter_states'] ) ? $args['filter_states'] : false; |
| 70 |
|
| 71 |
$filter_license_type = ! empty( $args['filter_license_type'] ) ? $args['filter_license_type'] : false; |
| 72 |
$filter_payment_method = ! empty( $args['filter_payment_method'] ) ? $args['filter_payment_method'] : false; |
| 73 |
$filter_renewal_type = ! empty( $args['filter_renewal_type'] ) ? $args['filter_renewal_type'] : false; |
| 74 |
|
| 75 |
$filter_dtsstart = ! empty( $args['dtsstart'] ) ? strtotime( $args['dtsstart'] ) : false; |
| 76 |
$filter_dtsstop = ! empty( $args['dtsstop'] ) ? ( strtotime( $args['dtsstop'] ) + DAY_IN_SECONDS - 1 ) : false; |
| 77 |
|
| 78 |
if ( ! empty( $filter_client_ids ) ) { |
| 79 |
$filter_client_ids = MainWP_Utility::array_numeric_filter( $filter_client_ids ); |
| 80 |
} |
| 81 |
if ( ! empty( $filter_site_ids ) ) { |
| 82 |
$filter_site_ids = MainWP_Utility::array_numeric_filter( $filter_site_ids ); |
| 83 |
} |
| 84 |
|
| 85 |
if ( ! empty( $filter_prod_type_slugs ) ) { |
| 86 |
$product_types = Cost_Tracker_Admin::get_product_types(); |
| 87 |
$product_types = array_keys( $product_types ); |
| 88 |
$filter_prod_type_slugs = array_filter( |
| 89 |
$filter_prod_type_slugs, |
| 90 |
function ( $e ) use ( $product_types ) { |
| 91 |
return is_string( $e ) && in_array( $e, $product_types, true ) ? true : false; // to valid. |
| 92 |
} |
| 93 |
); |
| 94 |
} |
| 95 |
|
| 96 |
if ( ! empty( $filter_cost_state ) ) { |
| 97 |
$costs_state = Cost_Tracker_Admin::get_cost_status(); |
| 98 |
$costs_state = array_keys( $costs_state ); |
| 99 |
$filter_cost_state = array_filter( |
| 100 |
$filter_cost_state, |
| 101 |
function ( $e ) use ( $costs_state ) { |
| 102 |
return is_string( $e ) && in_array( $e, $costs_state, true ) ? true : false; // to valid. |
| 103 |
} |
| 104 |
); |
| 105 |
} |
| 106 |
|
| 107 |
$license_types = $all_defaults['license_types']; |
| 108 |
$payment_methods = $all_defaults['payment_methods']; |
| 109 |
$renewal_frequency = $all_defaults['renewal_frequency']; |
| 110 |
|
| 111 |
if ( ! empty( $filter_license_type ) ) { |
| 112 |
$license_types = array_keys( $license_types ); |
| 113 |
$filter_license_type = array_filter( |
| 114 |
$filter_license_type, |
| 115 |
function ( $e ) use ( $license_types ) { |
| 116 |
return is_string( $e ) && in_array( $e, $license_types, true ) ? true : false; // to valid. |
| 117 |
} |
| 118 |
); |
| 119 |
} |
| 120 |
if ( ! empty( $filter_payment_method ) ) { |
| 121 |
$payment_methods = array_keys( $payment_methods ); |
| 122 |
$filter_payment_method = array_filter( |
| 123 |
$filter_payment_method, |
| 124 |
function ( $e ) use ( $payment_methods ) { |
| 125 |
return is_string( $e ) && in_array( $e, $payment_methods, true ) ? true : false; // to valid. |
| 126 |
} |
| 127 |
); |
| 128 |
} |
| 129 |
if ( ! empty( $filter_renewal_type ) ) { |
| 130 |
$renewal_frequency = array_keys( $renewal_frequency ); |
| 131 |
$filter_renewal_type = array_filter( |
| 132 |
$filter_renewal_type, |
| 133 |
function ( $e ) use ( $renewal_frequency ) { |
| 134 |
return is_string( $e ) && in_array( $e, $renewal_frequency, true ) ? true : false; // to valid. |
| 135 |
} |
| 136 |
); |
| 137 |
} |
| 138 |
|
| 139 |
$where = ''; |
| 140 |
|
| 141 |
$allowed_search_fields = array( 'co.name', 'co.product_type', 'co.payment_method', 'co.type' ); |
| 142 |
|
| 143 |
$where_search = array(); |
| 144 |
if ( ! empty( $args['search'] ) ) { |
| 145 |
// Sanitize field. |
| 146 |
$search_term = '%' . esc_sql( $args['search'] ) . '%'; |
| 147 |
foreach ( $allowed_search_fields as $field ) { |
| 148 |
$where_search[] = $this->wpdb->prepare( " {$field} LIKE %s ", $search_term ); |
| 149 |
} |
| 150 |
} |
| 151 |
|
| 152 |
if ( ! empty( $where_search ) ) { |
| 153 |
$where .= ' AND (' . implode( ' OR ', $where_search ) . ')'; |
| 154 |
} |
| 155 |
$join_lookup = ''; |
| 156 |
$filter_lookup = ! empty( $filter_site_ids ) || ! empty( $filter_client_ids ) ? true : false; |
| 157 |
|
| 158 |
if ( $filter_lookup ) { |
| 159 |
$lookup_table = '`' . esc_sql( $this->table_name( 'lookup_item_objects' ) ) . '`'; |
| 160 |
$join_lookup = " JOIN {$lookup_table} lco ON co.id = lco.item_id AND lco.item_name = 'cost' "; |
| 161 |
$join_and = array(); |
| 162 |
|
| 163 |
$clients_sites_ids = array(); |
| 164 |
if ( $filter_client_ids ) { |
| 165 |
$safe_client_ids = array_map( 'intval', $filter_client_ids ); |
| 166 |
$join_and[] = ' lco.object_name = "client" AND lco.object_id IN (' . implode( ',', $safe_client_ids ) . ') '; |
| 167 |
|
| 168 |
// if filter by clients need to get sites of the clients to search in lookup table. |
| 169 |
$cli_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $filter_client_ids ); |
| 170 |
if ( $cli_sites ) { |
| 171 |
foreach ( $cli_sites as $cli_site ) { |
| 172 |
$clients_sites_ids[] = $cli_site->id; |
| 173 |
} |
| 174 |
} |
| 175 |
} |
| 176 |
|
| 177 |
$filter_site_ids = array_unique( array_merge( $filter_site_ids, $clients_sites_ids ) ); |
| 178 |
|
| 179 |
if ( $filter_site_ids ) { |
| 180 |
$safe_site_ids = array_map( 'intval', $filter_site_ids ); |
| 181 |
$join_and[] = ' lco.object_name = "site" AND lco.object_id IN (' . implode( ',', $safe_site_ids ) . ') '; |
| 182 |
} |
| 183 |
|
| 184 |
if ( ! empty( $join_and ) ) { |
| 185 |
if ( 1 === count( $join_and ) ) { |
| 186 |
$join_lookup .= ' AND ' . $join_and[0]; |
| 187 |
} elseif ( 2 === count( $join_and ) ) { |
| 188 |
$join_lookup .= ' AND ( ( ' . $join_and[0] . ' ) OR ( ' . $join_and[1] . ' ) )'; |
| 189 |
} else { |
| 190 |
$join_lookup = ''; |
| 191 |
} |
| 192 |
} else { |
| 193 |
$join_lookup = ''; |
| 194 |
} |
| 195 |
} |
| 196 |
|
| 197 |
if ( ! empty( $filter_prod_type_slugs ) && is_array( $filter_prod_type_slugs ) ) { |
| 198 |
$filter_prod_type_slugs = MainWP_DB::instance()->escape_array( $filter_prod_type_slugs ); |
| 199 |
if ( ! empty( $filter_prod_type_slugs ) ) { |
| 200 |
$safe_prod_types = array_map( 'esc_sql', $filter_prod_type_slugs ); |
| 201 |
$where .= ' AND co.product_type IN ("' . implode( '","', $safe_prod_types ) . '") '; |
| 202 |
} |
| 203 |
} |
| 204 |
|
| 205 |
if ( ! empty( $filter_cost_state ) && is_array( $filter_cost_state ) ) { |
| 206 |
$filter_cost_state = MainWP_DB::instance()->escape_array( $filter_cost_state ); |
| 207 |
if ( ! empty( $filter_cost_state ) ) { |
| 208 |
$safe_cost_states = array_map( 'esc_sql', $filter_cost_state ); |
| 209 |
$where .= ' AND co.cost_status IN ("' . implode( '","', $safe_cost_states ) . '") '; |
| 210 |
} |
| 211 |
} |
| 212 |
|
| 213 |
$filter_next_renewal = false; |
| 214 |
if ( ! empty( $filter_dtsstart ) && is_numeric( $filter_dtsstart ) && ! empty( $filter_dtsstop ) && is_numeric( $filter_dtsstop ) && $filter_dtsstart < $filter_dtsstop ) { |
| 215 |
$filter_next_renewal = true; |
| 216 |
} |
| 217 |
|
| 218 |
if ( $filter_next_renewal ) { |
| 219 |
$stop_start = $filter_dtsstop - $filter_dtsstart; |
| 220 |
$where .= ' AND cost_status = "active" AND type = "subscription" AND ( CASE '; |
| 221 |
$where .= ' WHEN renewal_type = "weekly" AND ( ( next_renewal >= ' . intval( $filter_dtsstart ) . ' AND ( next_renewal <= ' . intval( $filter_dtsstop ) . ' ) ) OR ( next_renewal < ' . intval( $filter_dtsstart ) . ' AND ( MOD( ' . intval( $filter_dtsstop ) . ' - next_renewal, ' . intval( WEEK_IN_SECONDS ) . ' ) < ' . $stop_start . ' ) ) ) THEN true '; |
| 222 |
$where .= ' WHEN renewal_type = "monthly" AND ( ( next_renewal >= ' . intval( $filter_dtsstart ) . ' AND ( next_renewal <= ' . intval( $filter_dtsstop ) . ' ) ) OR ( next_renewal < ' . intval( $filter_dtsstart ) . ' AND ( MOD( ' . intval( $filter_dtsstop ) . ' - next_renewal, ' . intval( MONTH_IN_SECONDS ) . ' ) < ' . $stop_start . ' ) ) ) THEN true '; |
| 223 |
$where .= ' WHEN renewal_type = "yearly" AND ( ( next_renewal >= ' . intval( $filter_dtsstart ) . ' AND ( next_renewal <= ' . intval( $filter_dtsstop ) . ' ) ) OR ( next_renewal < ' . intval( $filter_dtsstart ) . ' AND ( MOD( ' . intval( $filter_dtsstop ) . ' - next_renewal, ' . intval( YEAR_IN_SECONDS ) . ' ) < ' . $stop_start . ' ) ) ) THEN true '; |
| 224 |
$where .= ' WHEN renewal_type = "quarterly" AND ( ( next_renewal >= ' . intval( $filter_dtsstart ) . ' AND ( next_renewal <= ' . intval( $filter_dtsstop ) . ' ) ) OR ( next_renewal < ' . intval( $filter_dtsstart ) . ' AND ( MOD( ' . intval( $filter_dtsstop ) . ' - next_renewal, ' . 3 * intval( MONTH_IN_SECONDS ) . ' ) < ' . $stop_start . ' ) ) ) THEN true '; |
| 225 |
$where .= ' ELSE false END ) '; |
| 226 |
} |
| 227 |
|
| 228 |
if ( ! empty( $filter_license_type ) && is_array( $filter_license_type ) ) { |
| 229 |
$filter_license_type = MainWP_DB::instance()->escape_array( $filter_license_type ); |
| 230 |
if ( ! empty( $filter_license_type ) ) { |
| 231 |
$safe_license_types = array_map( 'esc_sql', $filter_license_type ); |
| 232 |
$where .= ' AND co.license_type IN ("' . implode( '","', $safe_license_types ) . '") '; |
| 233 |
} |
| 234 |
} |
| 235 |
if ( ! empty( $filter_payment_method ) && is_array( $filter_payment_method ) ) { |
| 236 |
$filter_payment_method = MainWP_DB::instance()->escape_array( $filter_payment_method ); |
| 237 |
if ( ! empty( $filter_payment_method ) ) { |
| 238 |
$safe_payment_methods = array_map( 'esc_sql', $filter_payment_method ); |
| 239 |
$where .= ' AND co.payment_method IN ("' . implode( '","', $safe_payment_methods ) . '") '; |
| 240 |
} |
| 241 |
} |
| 242 |
if ( ! empty( $filter_renewal_type ) && is_array( $filter_renewal_type ) ) { |
| 243 |
$filter_renewal_type = MainWP_DB::instance()->escape_array( $filter_renewal_type ); |
| 244 |
if ( ! empty( $filter_renewal_type ) ) { |
| 245 |
$safe_renewal_types = array_map( 'esc_sql', $filter_renewal_type ); |
| 246 |
$where .= ' AND co.renewal_type IN ("' . implode( '","', $safe_renewal_types ) . '") '; |
| 247 |
} |
| 248 |
} |
| 249 |
|
| 250 |
/** |
| 251 |
* PARSE PAGINATION PARAMS |
| 252 |
*/ |
| 253 |
$limits = ''; |
| 254 |
$start = isset( $args['start'] ) ? absint( $args['start'] ) : 0; |
| 255 |
$per_page = isset( $args['records_per_page'] ) ? absint( $args['records_per_page'] ) : 99999; |
| 256 |
|
| 257 |
if ( $per_page >= 0 ) { |
| 258 |
$limits = "LIMIT {$start}, {$per_page}"; |
| 259 |
} |
| 260 |
|
| 261 |
// Default to sorting by record ID. |
| 262 |
$orderby = 'co.id'; |
| 263 |
|
| 264 |
$orderable = array( 'name', 'url', 'type', 'product_type', 'slug', 'license_type', 'cost_status', 'payment_method', 'price', 'renewal_type', 'last_renewal', 'next_renewal' ); |
| 265 |
if ( isset( $args['orderby'] ) && in_array( $args['orderby'], $orderable, true ) ) { |
| 266 |
$_orderby = 'next_renewal' === $args['orderby'] ? 'next_renewal_today' : $args['orderby']; |
| 267 |
$prefix = 'co.'; |
| 268 |
$orderby = sprintf( '%s%s', $prefix, $_orderby ); |
| 269 |
} |
| 270 |
|
| 271 |
// Show the recent records first by default. |
| 272 |
$order = 'DESC'; |
| 273 |
if ( isset( $args['order'] ) && 'ASC' === strtoupper( $args['order'] ) ) { |
| 274 |
$order = 'ASC'; |
| 275 |
} |
| 276 |
|
| 277 |
$orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); |
| 278 |
|
| 279 |
$selects = array(); |
| 280 |
$selects[] = ' DISTINCT(co.id) dist_id '; // need at first. |
| 281 |
$selects[] = ' co.* '; |
| 282 |
$select = implode( ', ', $selects ); |
| 283 |
|
| 284 |
/** |
| 285 |
* BUILD THE FINAL QUERY |
| 286 |
*/ |
| 287 |
$cost_table = '`' . esc_sql( $this->table_name( 'cost_tracker' ) ) . '`'; |
| 288 |
$query = "SELECT {$select} |
| 289 |
FROM {$cost_table} as co " . |
| 290 |
$join_lookup . |
| 291 |
" WHERE 1 {$where} |
| 292 |
{$orderby} |
| 293 |
{$limits}"; |
| 294 |
|
| 295 |
// Build result count query. |
| 296 |
$count_query = "SELECT COUNT(DISTINCT(co.id)) as found |
| 297 |
FROM {$cost_table} as co " . |
| 298 |
$join_lookup . |
| 299 |
" WHERE 1 {$where}"; |
| 300 |
|
| 301 |
/** |
| 302 |
* QUERY THE DATABASE FOR RESULTS |
| 303 |
*/ |
| 304 |
$items = $this->wpdb->get_results( $query ); // phpcs:ignore -- ok. |
| 305 |
$total = absint( $this->wpdb->get_var( $count_query ) ); // phpcs:ignore -- ok. |
| 306 |
|
| 307 |
return array( |
| 308 |
'items' => $items, |
| 309 |
'count' => $total, |
| 310 |
); |
| 311 |
} |
| 312 |
} |
| 313 |
|
