|null Last fragments added (for debug). */ private static $last_fragments = null; /** @var array Memoized txn_complete_types_sql per table alias. */ private static $txn_types_cache = []; /** * @return array|null */ public static function get_last_fragments() { return self::$last_fragments; } /** * Reset last debug fragments. * * @return void */ public static function reset_last_fragments() { self::$last_fragments = null; self::$txn_types_cache = []; } /** * Append WHERE fragments for active core MemberPress table filters. * * @param array $args Existing WHERE fragments. * @param Meprmf_Screen_Context $ctx Screen context. * @param array> $valid Normalized core field definitions. * @return array */ public static function append_mepr_exists(array $args, Meprmf_Screen_Context $ctx, array $valid) { self::$last_fragments = []; if (empty($valid) || ! $ctx->supports_core_filters()) { return $args; } if (! class_exists('MeprDb') || ! class_exists('MeprTransaction') || ! class_exists('MeprUtils')) { return $args; } if ($ctx->is_members()) { $args = self::append_members_user_exists($args, $ctx, $valid); } else { $row_alias = $ctx->get_primary_row_alias(); if ('' !== $row_alias) { $args = self::append_row_predicates($args, $ctx, $valid, $row_alias); } } $values = self::collect_request_values($valid); /** * Filter WHERE fragments after core MemberPress table predicates are built. * * @param array $args WHERE fragments. * @param Meprmf_Screen_Context $ctx Screen context. * @param array $values Active core filter request values. * @param array> $valid Normalized core field definitions. */ return apply_filters('meprmf_mepr_predicate_fragments', $args, $ctx, $values, $valid); } /** * Members list: EXISTS subqueries correlated on the user id column. * * @param array $args Existing WHERE fragments. * @param Meprmf_Screen_Context $ctx Screen context. * @param array> $valid Normalized core field definitions. * @return array */ private static function append_members_user_exists(array $args, Meprmf_Screen_Context $ctx, array $valid) { global $wpdb; $mepr_db = MeprDb::fetch(); $uid = $ctx->get_user_id_column_sql(); $values = self::collect_request_values($valid); $product_id = self::int_param($values, self::param_for_predicate($valid, 'product')); $access = self::string_param($values, self::param_for_predicate($valid, 'access')); $sub_status = self::string_param($values, self::param_for_predicate($valid, 'sub_status')); $expires = self::range_bounds($valid, $values, 'exp_from', 'exp_to'); $member_since = self::range_bounds($valid, $values, 'member_from', 'member_to'); $registered = self::range_bounds($valid, $values, 'registered_from', 'registered_to'); $last_login = self::range_bounds($valid, $values, 'last_login_from', 'last_login_to'); $member_status = self::string_param($values, self::param_for_predicate($valid, 'member_status')); $corp_type = self::string_param($values, self::param_for_predicate($valid, 'corp_type')); $spent_min = self::float_param($values, self::param_for_predicate($valid, 'spent_min')); $spent_max = self::float_param($values, self::param_for_predicate($valid, 'spent_max')); $trial = self::string_param($values, self::param_for_predicate($valid, 'trial')); if ('' !== $corp_type && in_array($corp_type, [ 'owner', 'sub_account', 'none' ], true)) { $sql = Meprmf_Corporate_Predicates::clause_for_type($corp_type, $uid, $ctx); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } // Each date pair is one fragment: a range must stay intact when fragments are ORed // together under match=any, and negation has to invert both bounds at once. $args = self::push_range( $args, self::build_column_range('u.user_registered', $registered['from'], $registered['to']), $registered['negate'] ); $args = self::push_range( $args, self::build_column_range('last_login.created_at', $last_login['from'], $last_login['to']), $last_login['negate'], 'last_login.created_at' ); if (null !== $spent_min) { $sql = $wpdb->prepare('m.total_spent >= %f', $spent_min); $args[] = $sql; self::$last_fragments[] = $sql; } if (null !== $spent_max) { $sql = $wpdb->prepare('m.total_spent <= %f', $spent_max); $args[] = $sql; self::$last_fragments[] = $sql; } if ('1' === $trial) { $sql = 'm.trial_txn_count > 0'; $args[] = $sql; self::$last_fragments[] = $sql; } if ('' !== $member_status && self::is_allowed_member_status($member_status)) { $sql = self::build_member_status_clause($member_status, $product_id); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } $args = self::push_range( $args, self::build_member_since_exists($mepr_db->members, $uid, $member_since['from'], $member_since['to']), $member_since['negate'] ); $args = self::push_range( $args, self::build_expires_range_exists($mepr_db->transactions, $uid, $product_id, $expires['from'], $expires['to']), $expires['negate'] ); if ('active' === $access) { $sql = self::build_active_access_exists($mepr_db->transactions, $uid, $product_id); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } elseif (in_array($access, [ 'inactive', 'expired' ], true)) { // `expired` kept for bookmarked URLs from earlier releases. /** * Whether to apply the inactive/expired access predicate on the Members list. * * The default predicate uses EXISTS + NOT EXISTS on mepr_transactions and can be * slow on large sites. Return false to skip it (e.g. hide the filter in UI instead). * * @since 2.0.0 * @param bool $use Default true. * @param Meprmf_Screen_Context $ctx Screen context. * @param int $product_id Selected membership product id or 0. */ $use_inactive = apply_filters('meprmf_use_inactive_access_predicate', true, $ctx, $product_id); if ($use_inactive) { $sql = self::build_inactive_access_exists($mepr_db->transactions, $uid, $product_id); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } } elseif ($product_id > 0) { $sql = self::build_product_exists($mepr_db->transactions, $uid, $product_id); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } if ('' !== $sub_status && self::is_allowed_sub_status($sub_status)) { $sql = self::build_subscription_status_exists($mepr_db->subscriptions, $uid, $sub_status, $product_id); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } return $args; } /** * Transactions / Subscriptions / Lifetimes: constrain the primary list row. * * @param array $args Existing WHERE fragments. * @param Meprmf_Screen_Context $ctx Screen context. * @param array> $valid Normalized core field definitions. * @param string $row_alias Primary table alias (tr, sub, txn). * @return array */ private static function append_row_predicates(array $args, Meprmf_Screen_Context $ctx, array $valid, $row_alias) { global $wpdb; $mepr_db = MeprDb::fetch(); $values = self::collect_request_values($valid); $product_id = self::int_param($values, self::param_for_predicate($valid, 'product')); $access = self::string_param($values, self::param_for_predicate($valid, 'access')); $sub_status = self::string_param($values, self::param_for_predicate($valid, 'sub_status')); $expires = self::range_bounds($valid, $values, 'exp_from', 'exp_to'); $member_since = self::range_bounds($valid, $values, 'member_from', 'member_to'); $created = self::range_bounds($valid, $values, 'created_from', 'created_to'); $txn_status = self::string_param($values, self::param_for_predicate($valid, 'txn_status')); $gateway = self::string_param($values, self::param_for_predicate($valid, 'gateway')); $coupon_id = self::int_param($values, self::param_for_predicate($valid, 'coupon')); $amount = self::number_bounds($valid, $values, 'amount_min', 'amount_max'); $txn_count = self::number_bounds($valid, $values, 'txn_count_min', 'txn_count_max'); $uid = $ctx->get_user_id_column_sql(); $expires_alias = $ctx->is_subscriptions_recurring() ? 'expiring_txn' : $row_alias; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Row alias and uid are fixed SQL fragments from screen context; values use placeholders. if ('' !== $gateway && self::is_allowed_gateway($gateway)) { $sql = $wpdb->prepare("{$row_alias}.gateway = %s", $gateway); $args[] = $sql; self::$last_fragments[] = $sql; } if ($coupon_id > 0 && self::is_allowed_coupon($coupon_id)) { $sql = $wpdb->prepare("{$row_alias}.coupon_id = %d", $coupon_id); $args[] = $sql; self::$last_fragments[] = $sql; } if ('' !== $txn_status && self::is_allowed_txn_status($txn_status)) { $sql = $wpdb->prepare("{$row_alias}.status = %s", $txn_status); $args[] = $sql; self::$last_fragments[] = $sql; } if (null !== $amount['min']) { $sql = $wpdb->prepare("{$row_alias}.total >= %f", $amount['min']); $args[] = $sql; self::$last_fragments[] = $sql; } if (null !== $amount['max']) { $sql = $wpdb->prepare("{$row_alias}.total <= %f", $amount['max']); $args[] = $sql; self::$last_fragments[] = $sql; } // Counts only the transactions MemberPress itself counts in the Transactions column, // so the filter and the number shown on the row agree. if (null !== $txn_count['min']) { $sql = $wpdb->prepare( "(SELECT COUNT(*) FROM {$mepr_db->transactions} AS meprmf_txn_cnt" . " WHERE meprmf_txn_cnt.subscription_id = {$row_alias}.id" . " AND meprmf_txn_cnt.status = %s) >= %d", MeprTransaction::$complete_str, (int) $txn_count['min'] ); $args[] = $sql; self::$last_fragments[] = $sql; } if (null !== $txn_count['max']) { $sql = $wpdb->prepare( "(SELECT COUNT(*) FROM {$mepr_db->transactions} AS meprmf_txn_cnt" . " WHERE meprmf_txn_cnt.subscription_id = {$row_alias}.id" . " AND meprmf_txn_cnt.status = %s) <= %d", MeprTransaction::$complete_str, (int) $txn_count['max'] ); $args[] = $sql; self::$last_fragments[] = $sql; } $subscr_sql = self::build_subscr_id_clause( self::field_for_predicate($valid, 'subscr_id'), $values ); if ('' !== $subscr_sql) { $args[] = $subscr_sql; self::$last_fragments[] = $subscr_sql; } $args = self::push_range( $args, self::build_column_range("{$row_alias}.created_at", $created['from'], $created['to']), $created['negate'] ); $args = self::push_range( $args, self::build_member_since_exists($mepr_db->members, $uid, $member_since['from'], $member_since['to']), $member_since['negate'] ); $args = self::push_range( $args, self::build_row_expires_range($expires_alias, $product_id, $expires['from'], $expires['to'], $row_alias), $expires['negate'] ); if ('active' === $access) { $sql = self::build_row_active_access($expires_alias, $product_id, $row_alias); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } elseif (in_array($access, [ 'inactive', 'expired' ], true)) { $sql = self::build_row_inactive_access($expires_alias, $product_id, $row_alias); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } elseif ($product_id > 0) { $sql = self::build_row_product_clause($row_alias, $product_id); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } if ('' !== $sub_status && self::is_allowed_sub_status($sub_status)) { if ($ctx->is_subscriptions_recurring()) { $status_sql = $wpdb->prepare("{$row_alias}.status = %s", $sub_status); if ($product_id > 0) { $status_sql .= ' AND ' . self::build_row_product_clause($row_alias, $product_id); } $args[] = $status_sql; self::$last_fragments[] = $status_sql; } else { $sql = self::build_row_subscription_status_exists( $mepr_db->subscriptions, $row_alias, $sub_status, $product_id ); if ('' !== $sql) { $args[] = $sql; self::$last_fragments[] = $sql; } } } // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared return $args; } /** * @param array> $valid Field definitions. * @param string $predicate Predicate key from field row. * @return string Param name or empty. */ private static function param_for_predicate(array $valid, $predicate) { foreach ($valid as $field) { if (! empty($field['predicate']) && $predicate === $field['predicate'] && ! empty($field['param'])) { return (string) $field['param']; } } return ''; } /** * @param array $values Values map. * @param string $key Param key. * @return string */ private static function string_param(array $values, $key) { if ('' === $key || ! isset($values[ $key ])) { return ''; } return (string) $values[ $key ]; } /** * @param array> $valid Field definitions. * @return array */ private static function collect_request_values(array $valid) { $values = []; foreach ($valid as $field) { $param = isset($field['param']) ? (string) $field['param'] : ''; if ('' === $param) { continue; } $raw = Meprmf_Util::get_request_value($param); if ('' !== $raw) { $values[ $param ] = $raw; } } return $values; } /** * @param array $values Values map. * @param string $key Param key. * @return int */ private static function int_param(array $values, $key) { if (! isset($values[ $key ]) || ! is_numeric($values[ $key ])) { return 0; } return max(0, (int) $values[ $key ]); } /** * @param array $values Values map. * @param string $key Param key. * @return string|null Y-m-d or null. */ private static function date_param(array $values, $key) { if (! isset($values[ $key ])) { return null; } $raw = (string) $values[ $key ]; if (! preg_match('/^\d{4}-\d{2}-\d{2}$/', $raw)) { return null; } return $raw; } /** * @param array $values Values map. * @param string $key Param key. * @return float|null */ private static function float_param(array $values, $key) { if ('' === $key || ! isset($values[ $key ])) { return null; } $raw = (string) $values[ $key ]; if (! is_numeric($raw)) { return null; } return max(0.0, (float) $raw); } /** * @param int $coupon_id Coupon post ID. * @return bool */ private static function is_allowed_coupon($coupon_id) { if ($coupon_id <= 0 || ! class_exists('MeprCoupon')) { return false; } if (! function_exists('get_post')) { return false; } $post = get_post($coupon_id); if (! $post || ! isset($post->post_type)) { return false; } return MeprCoupon::$cpt === $post->post_type; } /** * @param string $status Subscription status. * @return bool */ private static function is_allowed_sub_status($status) { return in_array( $status, [ MeprSubscription::$active_str, MeprSubscription::$pending_str, MeprSubscription::$cancelled_str, MeprSubscription::$suspended_str, ], true ); } /** * @param string $status Member status slug. * @return bool */ private static function is_allowed_member_status($status) { return in_array($status, [ 'active', 'inactive', 'expired', 'none' ], true); } /** * @param string $status Transaction status. * @return bool */ private static function is_allowed_txn_status($status) { return in_array( $status, [ MeprTransaction::$pending_str, MeprTransaction::$complete_str, MeprTransaction::$confirmed_str, MeprTransaction::$refunded_str, MeprTransaction::$failed_str, ], true ); } /** * @param string $gateway Gateway id. * @return bool */ private static function is_allowed_gateway($gateway) { if (! class_exists('MeprOptions')) { return false; } $methods = MeprOptions::fetch()->payment_methods(); if (! is_array($methods)) { return false; } return isset($methods[ $gateway ]); } /** * MemberPress Members list aggregate status (mepr_members AS m). * * @param string $status active|inactive|expired|none. * @param int $product_id Membership product id or 0. * @return string */ private static function build_member_status_clause($status, $product_id) { global $wpdb; if ($product_id > 0) { if ('active' === $status) { return $wpdb->prepare("m.memberships RLIKE '(^|,)%d(,|$)'", $product_id); } if (in_array($status, [ 'inactive', 'expired' ], true)) { return $wpdb->prepare("m.inactive_memberships RLIKE '(^|,)%d(,|$)'", $product_id); } return ''; } switch ($status) { case 'active': return '(m.active_txn_count > 0 OR m.trial_txn_count > 0)'; case 'inactive': return '(m.active_txn_count <= 0 AND m.expired_txn_count > 0 AND m.trial_txn_count <= 0)'; case 'expired': return "m.inactive_memberships <> ''"; case 'none': return '(m.active_txn_count <= 0 AND m.expired_txn_count <= 0 AND m.trial_txn_count <= 0)'; } return ''; } /** * One parenthesized fragment bounding a datetime column. * * @param string $column Fully qualified column (alias.column), a fixed SQL fragment. * @param string|null $from Y-m-d lower bound. * @param string|null $to Y-m-d upper bound. * @return string */ private static function build_column_range($column, $from, $to) { global $wpdb; $bits = []; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Column/alias is a fixed SQL fragment from screen context; values use placeholders. if (null !== $from) { $bits[] = $wpdb->prepare("{$column} >= %s", $from . ' 00:00:00'); } if (null !== $to) { $bits[] = $wpdb->prepare("{$column} <= %s", $to . ' 23:59:59'); } // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared if (empty($bits)) { return ''; } return '(' . implode(' AND ', $bits) . ')'; } /** * Effective bounds for one core date pair, after applying the pair's operator. * * Field rows without `range_of` (third-party definitions) keep the pre-2.1 behaviour: * the two params are read as plain from/to values with no operator. * * @param array> $valid Normalized core field definitions. * @param array $values Active request values. * @param string $from_predicate Predicate key of the lower bound. * @param string $to_predicate Predicate key of the upper bound. * @return array{from: string|null, to: string|null, negate: bool} */ private static function range_bounds(array $valid, array $values, $from_predicate, $to_predicate) { $field = self::field_for_predicate($valid, $from_predicate); if (! empty($field['range_of'])) { return Meprmf_Util::resolve_range_bounds((string) $field['range_of'], $field); } return [ 'from' => self::date_param($values, self::param_for_predicate($valid, $from_predicate)), 'to' => self::date_param($values, self::param_for_predicate($valid, $to_predicate)), 'negate' => false, ]; } /** * Effective bounds for one numeric min/max pair. * * The pair has no param that could carry an equality, so the operator only decides which * bound the typed value lands in — which the UI has already done by the time the value * reaches a GET param. Reading both bounds is therefore the whole of it, and it keeps a * hand-written or bookmarked URL working with no operator present at all. * * @param array> $valid Normalized core field definitions. * @param array $values Active request values. * @param string $min_predicate Predicate key of the lower bound. * @param string $max_predicate Predicate key of the upper bound. * @return array{min: float|null, max: float|null} */ private static function number_bounds(array $valid, array $values, $min_predicate, $max_predicate) { return [ 'min' => self::float_param($values, self::param_for_predicate($valid, $min_predicate)), 'max' => self::float_param($values, self::param_for_predicate($valid, $max_predicate)), ]; } /** * Operator-aware clause for the gateway subscription id on the Transactions list. * * `sub` is MemberPress's own LEFT JOIN onto mepr_subscriptions, so a one-off payment has * NULL here. Every negative operator keeps those rows: a transaction with no subscription * really does not contain the needle. * * @param array $field Field row, or empty when the screen has no such field. * @param array $values Active request values. * @return string Empty when the filter is not active. */ private static function build_subscr_id_clause(array $field, array $values) { global $wpdb; $param = isset($field['param']) ? (string) $field['param'] : ''; if ('' === $param) { return ''; } $column = 'sub.subscr_id'; $operator = Meprmf_Util::get_field_operator(Meprmf_Util::range_base_param($field), $field); if ('is_empty' === $operator) { return "( {$column} IS NULL OR {$column} = '' )"; } if ('is_not_empty' === $operator) { return "( {$column} IS NOT NULL AND {$column} <> '' )"; } $raw = self::string_param($values, $param); if ('' === $raw) { return ''; } if ('is' === $operator) { return $wpdb->prepare("{$column} = %s", $raw); } if ('is_not' === $operator) { return $wpdb->prepare("( {$column} IS NULL OR {$column} <> %s )", $raw); } $like = '%' . $wpdb->esc_like($raw) . '%'; if ('not_contains' === $operator) { return $wpdb->prepare("( {$column} IS NULL OR {$column} NOT LIKE %s )", $like); } // No operator, or `contains`: a text field's own match mode is a substring match. return $wpdb->prepare("{$column} LIKE %s", $like); } /** * @param array> $valid Field definitions. * @param string $predicate Predicate key. * @return array Field row or empty array. */ private static function field_for_predicate(array $valid, $predicate) { foreach ($valid as $field) { if (! empty($field['predicate']) && $predicate === $field['predicate']) { return $field; } } return []; } /** * Record a range fragment, inverting it for `not_in_last`. * * @param array $args WHERE fragments. * @param string $sql Range fragment, or empty for "no bounds". * @param bool $negate Whether the operator negated the window. * @param string $null_guard Nullable column that means "no date recorded"; * a negated window has to keep those rows. * @return array */ private static function push_range(array $args, $sql, $negate, $null_guard = '') { if (! is_string($sql) || '' === $sql) { return $args; } if ($negate) { $sql = '' !== $null_guard ? "( {$null_guard} IS NULL OR NOT ( {$sql} ) )" : "NOT ( {$sql} )"; } $args[] = $sql; self::$last_fragments[] = $sql; return $args; } /** * SQL clause matching MeprTransaction::get_all_complete_by_user_id txn types/statuses. * * @param string $alias Table alias. * @return string */ private static function txn_complete_types_sql($alias) { if (isset(self::$txn_types_cache[ $alias ])) { return self::$txn_types_cache[ $alias ]; } global $wpdb; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table alias is a fixed internal SQL fragment. self::$txn_types_cache[ $alias ] = $wpdb->prepare( "( ( {$alias}.txn_type IN (%s,%s,%s,%s) AND {$alias}.status = %s ) OR ( {$alias}.txn_type = %s AND {$alias}.status = %s ) )", MeprTransaction::$payment_str, MeprTransaction::$sub_account_str, MeprTransaction::$woo_txn_str, MeprTransaction::$fallback_str, MeprTransaction::$complete_str, MeprTransaction::$subscription_confirmation_str, MeprTransaction::$confirmed_str ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared return self::$txn_types_cache[ $alias ]; } /** * Active access expiry clause (not lifetime-expired). * * @param string $alias Table alias. * @return string */ private static function txn_active_expires_sql($alias) { global $wpdb; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table alias is a fixed internal SQL fragment. $sql = $wpdb->prepare( "( {$alias}.expires_at > %s OR {$alias}.expires_at = %s OR {$alias}.expires_at IS NULL )", MeprUtils::db_now(), MeprUtils::db_lifetime() ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared return $sql; } /** * @param string $table Transactions table name. * @param string $uid User id SQL expression. * @param int $product_id Product id or 0. * @return string */ private static function build_active_access_exists($table, $uid, $product_id) { global $wpdb; $alias = 'mpmf_t_active'; $types = self::txn_complete_types_sql($alias); $expires = self::txn_active_expires_sql($alias); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias and uid are fixed SQL fragments; values use placeholders. $product = $product_id > 0 ? $wpdb->prepare("AND {$alias}.product_id = %d", $product_id) : ''; $sql = "EXISTS ( SELECT 1 FROM {$table} AS {$alias} WHERE {$alias}.user_id = {$uid} {$product} AND {$types} AND {$expires} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared return $sql; } /** * Inactive access: had qualifying transactions but none currently grant access. * * Embeds a NOT EXISTS(active) subquery; can be heavy on large transaction tables. * * @param string $table Transactions table name. * @param string $uid User id SQL expression. * @param int $product_id Product id or 0. * @return string */ private static function build_inactive_access_exists($table, $uid, $product_id) { global $wpdb; $alias = 'mpmf_t_exp'; $types = self::txn_complete_types_sql($alias); $lifetime = MeprUtils::db_lifetime(); $now = MeprUtils::db_now(); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias and uid are fixed SQL fragments; values use placeholders. $product = $product_id > 0 ? $wpdb->prepare("AND {$alias}.product_id = %d", $product_id) : ''; $expired_dates = $wpdb->prepare( "{$alias}.expires_at <> %s AND {$alias}.expires_at IS NOT NULL AND {$alias}.expires_at <= %s", $lifetime, $now ); $had_access = "EXISTS ( SELECT 1 FROM {$table} AS {$alias} WHERE {$alias}.user_id = {$uid} {$product} AND {$types} AND {$expired_dates} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $active = self::build_active_access_exists($table, $uid, $product_id); $not_active = 'NOT ' . $active; return '(' . $had_access . ' AND ' . $not_active . ')'; } /** * Any complete transaction for a product (access not required). * * @param string $table Transactions table name. * @param string $uid User id SQL expression. * @param int $product_id Product id. * @return string */ private static function build_product_exists($table, $uid, $product_id) { if ($product_id <= 0) { return ''; } global $wpdb; $alias = 'mpmf_t_prd'; $types = self::txn_complete_types_sql($alias); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias and uid are fixed SQL fragments; values use placeholders. $product_clause = $wpdb->prepare("AND {$alias}.product_id = %d", $product_id); return "EXISTS ( SELECT 1 FROM {$table} AS {$alias} WHERE {$alias}.user_id = {$uid} {$product_clause} AND {$types} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } /** * @param string $table Transactions table name. * @param string $uid User id SQL expression. * @param int $product_id Product id or 0. * @param string|null $from Y-m-d from. * @param string|null $to Y-m-d to. * @return string */ private static function build_expires_range_exists($table, $uid, $product_id, $from, $to) { global $wpdb; $alias = 'mpmf_t_exp_rng'; $types = self::txn_complete_types_sql($alias); $lifetime = MeprUtils::db_lifetime(); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias and uid are fixed SQL fragments; values use placeholders. $product = $product_id > 0 ? $wpdb->prepare("AND {$alias}.product_id = %d", $product_id) : ''; $date_bits = []; if (null !== $from) { $date_bits[] = $wpdb->prepare("{$alias}.expires_at >= %s", $from . ' 00:00:00'); } if (null !== $to) { $date_bits[] = $wpdb->prepare("{$alias}.expires_at <= %s", $to . ' 23:59:59'); } if (empty($date_bits)) { return ''; } $date_sql = implode(' AND ', $date_bits); $exclude_lifetime = $wpdb->prepare( "{$alias}.expires_at <> %s AND {$alias}.expires_at IS NOT NULL", $lifetime ); return "EXISTS ( SELECT 1 FROM {$table} AS {$alias} WHERE {$alias}.user_id = {$uid} {$product} AND {$types} AND {$exclude_lifetime} AND {$date_sql} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } /** * @param string $table Subscriptions table name. * @param string $uid User id SQL expression. * @param string $status Subscription status. * @param int $product_id Product id or 0. * @return string */ private static function build_subscription_status_exists($table, $uid, $status, $product_id) { global $wpdb; $alias = 'mpmf_sub'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias and uid are fixed SQL fragments; values use placeholders. $product = $product_id > 0 ? $wpdb->prepare("AND {$alias}.product_id = %d", $product_id) : ''; $status_clause = $wpdb->prepare("{$alias}.status = %s", $status); return "EXISTS ( SELECT 1 FROM {$table} AS {$alias} WHERE {$alias}.user_id = {$uid} AND {$status_clause} {$product} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } /** * @param string $members_table mepr_members table name. * @param string $uid User id SQL expression. * @param string|null $from Y-m-d from. * @param string|null $to Y-m-d to. * @return string */ private static function build_member_since_exists($members_table, $uid, $from, $to) { global $wpdb; $alias = 'mpmf_mbr_rng'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias and uid are fixed SQL fragments; values use placeholders. $date_bits = []; if (null !== $from) { $date_bits[] = $wpdb->prepare("{$alias}.created_at >= %s", $from . ' 00:00:00'); } if (null !== $to) { $date_bits[] = $wpdb->prepare("{$alias}.created_at <= %s", $to . ' 23:59:59'); } if (empty($date_bits)) { return ''; } $date_sql = implode(' AND ', $date_bits); return "EXISTS ( SELECT 1 FROM {$members_table} AS {$alias} WHERE {$alias}.user_id = {$uid} AND {$date_sql} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } /** * @param string $alias Row or expiring_txn alias for expires_at. * @param int $product_id Product id or 0. * @param string|null $from Y-m-d from. * @param string|null $to Y-m-d to. * @param string $row_alias Primary row alias for optional product_id filter. * @return string */ private static function build_row_expires_range($alias, $product_id, $from, $to, $row_alias) { global $wpdb; $lifetime = MeprUtils::db_lifetime(); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Row alias is a fixed SQL fragment from screen context; values use placeholders. $product = $product_id > 0 ? $wpdb->prepare("{$row_alias}.product_id = %d", $product_id) : ''; $date_bits = []; if (null !== $from) { $date_bits[] = $wpdb->prepare("{$alias}.expires_at >= %s", $from . ' 00:00:00'); } if (null !== $to) { $date_bits[] = $wpdb->prepare("{$alias}.expires_at <= %s", $to . ' 23:59:59'); } if (empty($date_bits)) { return ''; } $date_sql = implode(' AND ', $date_bits); $exclude_lifetime = $wpdb->prepare( "{$alias}.expires_at <> %s AND {$alias}.expires_at IS NOT NULL", $lifetime ); $product_clause = '' !== $product ? ' AND ' . $product : ''; return "({$exclude_lifetime} AND {$date_sql}{$product_clause})"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } /** * @param string $expires_alias Alias holding expires_at (tr, txn, or expiring_txn). * @param int $product_id Product id or 0. * @param string $row_alias Primary row alias for product filter. * @return string */ private static function build_row_active_access($expires_alias, $product_id, $row_alias) { $types = self::txn_complete_types_sql($expires_alias); $expires = self::txn_active_expires_sql($expires_alias); $product = self::build_row_product_clause($row_alias, $product_id); $bits = [ $types, $expires ]; if ('' !== $product) { $bits[] = $product; } return '(' . implode(' AND ', $bits) . ')'; } /** * @param string $expires_alias Alias holding expires_at. * @param int $product_id Product id or 0. * @param string $row_alias Primary row alias for product filter. * @return string */ private static function build_row_inactive_access($expires_alias, $product_id, $row_alias) { global $wpdb; $types = self::txn_complete_types_sql($expires_alias); $lifetime = MeprUtils::db_lifetime(); $now = MeprUtils::db_now(); $product = self::build_row_product_clause($row_alias, $product_id); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Alias is a fixed SQL fragment from screen context; values use placeholders. $expired_dates = $wpdb->prepare( "{$expires_alias}.expires_at <> %s AND {$expires_alias}.expires_at IS NOT NULL AND {$expires_alias}.expires_at <= %s", $lifetime, $now ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $bits = [ $types, $expired_dates ]; if ('' !== $product) { $bits[] = $product; } return '(' . implode(' AND ', $bits) . ')'; } /** * @param string $alias Primary row alias. * @param int $product_id Product id. * @return string */ private static function build_row_product_clause($alias, $product_id) { if ($product_id <= 0) { return ''; } global $wpdb; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Row alias is a fixed SQL fragment from screen context. $sql = $wpdb->prepare("{$alias}.product_id = %d", $product_id); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared return $sql; } /** * Subscription status on the row's linked subscription (Transactions / Lifetimes). * * @param string $sub_table Subscriptions table name. * @param string $txn_alias Transaction row alias (tr or txn). * @param string $status Subscription status. * @param int $product_id Product id or 0. * @return string */ private static function build_row_subscription_status_exists($sub_table, $txn_alias, $status, $product_id) { global $wpdb; $alias = 'mpmf_sub_row'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Aliases are fixed SQL fragments; values use placeholders. $status_clause = $wpdb->prepare("{$alias}.status = %s", $status); $product = $product_id > 0 ? $wpdb->prepare("AND {$alias}.product_id = %d", $product_id) : ''; return "EXISTS ( SELECT 1 FROM {$sub_table} AS {$alias} WHERE {$alias}.id = {$txn_alias}.subscription_id AND {$txn_alias}.subscription_id > 0 AND {$status_clause} {$product} )"; // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared } }