File: /var/www/html/wp-content/plugins/memberpress/app/models/MeprSubscription.php
<?php
if (!defined('ABSPATH')) {
die('You are not allowed to call this page directly.');
}
class MeprSubscription extends MeprBaseMetaModel implements MeprProductInterface, MeprTransactionInterface
{
/**
* Instance Variables & Methods
**/
/**
* String value for pending subscription status.
*
* @var string
*/
public static $pending_str = 'pending';
/**
* String value for active subscription status.
*
* @var string
*/
public static $active_str = 'active';
/**
* String value for suspended subscription status.
*
* @var string
*/
public static $suspended_str = 'suspended';
/**
* String value for cancelled subscription status.
*
* @var string
*/
public static $cancelled_str = 'cancelled';
/**
* Array of valid subscription statuses.
*
* @var array
*/
public $statuses;
/***
* Instance Methods
***/
/**
* Constructor for the MeprSubscription class.
*
* @param mixed $obj Optional object or ID to initialize the subscription.
*/
public function __construct($obj = null)
{
parent::__construct($obj);
$this->statuses = [
self::$pending_str,
self::$active_str,
self::$suspended_str,
self::$cancelled_str,
];
$this->initialize(
[
'id' => 0,
'subscr_id' => 'mp-sub-' . uniqid(),
'gateway' => 'manual',
'user_id' => 0,
'product_id' => 0,
'coupon_id' => 0,
'price' => 0.00,
'period' => 1,
'period_type' => 'months',
'limit_cycles' => false,
'limit_cycles_num' => 0,
'limit_cycles_action' => null,
'limit_cycles_expires_after' => '1',
'limit_cycles_expires_type' => 'days',
'prorated_trial' => false,
'trial' => false,
'trial_days' => 0,
'trial_amount' => 0.00,
'trial_tax_amount' => 0.00,
'trial_tax_reversal_amount' => 0.00,
'trial_total' => 0.00,
'status' => MeprSubscription::$pending_str,
'created_at' => null,
'total' => 0.00,
'tax_rate' => 0.00,
'tax_amount' => 0.00,
'tax_reversal_amount' => 0.00,
'tax_desc' => '',
'tax_class' => 'standard',
'cc_last4' => null,
'cc_exp_month' => null,
'cc_exp_year' => null,
'token' => null,
'order_id' => 0,
],
$obj
);
}
/**
* Validates the subscription properties.
*
* @return void
*/
public function validate()
{
$p = new MeprProduct();
$this->validate_not_empty($this->subscr_id, 'subscr_id');
$this->validate_not_empty($this->gateway, 'gateway');
$this->validate_is_numeric($this->user_id, 1, null, 'user_id');
$this->validate_is_numeric($this->product_id, 1, null, 'product_id');
$this->validate_is_numeric($this->coupon_id, 0, null, 'coupon_id'); // Accept no coupon (0) here.
$this->validate_is_currency($this->price, 0, null, 'price');
$this->validate_is_numeric($this->period, 1, null, 'period');
$this->validate_is_in_array($this->period_type, $p->period_types, 'period_type');
$this->validate_is_bool($this->limit_cycles, 'limit_cycles');
if ($this->limit_cycles) {
$this->validate_is_numeric($this->limit_cycles_num, 1, null, 'limit_cycles_num');
$this->validate_is_in_array($this->limit_cycles_action, $this->limit_cycles_actions, 'limit_cycles_action');
if ('expires_after' === $this->limit_cycles_action) {
$this->validate_is_numeric($this->limit_cycles_expires_after, 1, null, 'limit_cycles_expires_after');
$this->validate_not_empty($this->limit_cycles_expires_type, 'limit_cycles_expires_type');
}
}
$this->validate_is_bool($this->prorated_trial, 'prorated_trial');
$this->validate_is_bool($this->trial, 'trial');
if ($this->trial) {
$this->validate_is_numeric($this->trial_days, 0.00, null, 'trial_days');
$this->validate_is_currency($this->trial_amount, 0.00, null, 'trial_amount');
$this->validate_is_currency($this->trial_tax_amount, 0.00, null, 'trial_tax_amount');
$this->validate_is_currency($this->trial_total, 0.00, null, 'trial_total');
}
$this->validate_is_in_array($this->status, $this->statuses, 'status');
if (!empty($this->created_at)) {
$this->validate_is_date($this->created_at, 'created_at');
}
$this->validate_is_currency($this->total, 0, null, 'total');
$this->validate_is_numeric($this->tax_rate, 0, null, 'tax_rate');
$this->validate_is_currency($this->tax_amount, 0.00, null, 'tax_amount');
// $this->validate_not_empty($this->tax_desc, 'tax_desc');
$this->validate_not_empty($this->tax_class, 'tax_class');
if (!empty($this->cc_last4)) {
$this->cc_last4 = str_pad(trim($this->cc_last4), 4, '0', STR_PAD_LEFT);
$this->validate_regex('/^\d{4}$/', $this->cc_last4, 'cc_last4');
}
if (!empty($this->cc_exp_month)) {
$this->validate_regex('/^0?([1-9]|1[012])$/', trim($this->cc_exp_month), 'cc_exp_month');
}
if (!empty($this->cc_exp_year)) {
$this->validate_regex('/^\d{2}(\d{2})?$/', $this->cc_exp_year, 'cc_exp_year');
}
}
/**
* Stores the subscription in the database.
*
* @return integer The ID of the stored subscription.
*/
public function store()
{
$old_sub = new self($this->id);
if (isset($this->id) && !is_null($this->id) && (int)$this->id > 0) {
$this->id = self::update($this);
} else {
$this->id = self::create($this);
}
// Keep this hook at the bottom of this function
// This should happen after everything is done processing including the subscr txn_count.
MeprHooks::do_action('mepr_subscription_transition_status', $old_sub->status, $this->status, $this);
MeprHooks::do_action('mepr_subscription_stored', $this);
MeprHooks::do_action('mepr_subscription_saved', $this);
MeprHooks::do_action('mepr_subscription_status_' . $this->status, $this);
MeprHooks::do_action('mepr_subscription_compare', $this, $old_sub);
// DEPRECATED ... please use the actions above instead.
MeprHooks::do_action('mepr_subscr_transition_status', $old_sub->status, $this->status, $this);
MeprHooks::do_action('mepr_subscr_store', $this);
MeprHooks::do_action('mepr_subscr_status_' . $this->status, $this);
return $this->id;
}
/**
* Creates a new subscription record in the database.
*
* @param MeprSubscription $sub The subscription object to create.
*
* @return integer The ID of the created subscription.
*/
public static function create($sub)
{
$mepr_db = new MeprDb();
if (is_null($sub->created_at)) {
$sub->created_at = MeprUtils::db_now();
}
$args = $sub->get_values();
return MeprHooks::apply_filters('mepr_create_subscription', $mepr_db->create_record($mepr_db->subscriptions, $args, false), $args, $sub->user_id);
}
/**
* Updates an existing subscription record in the database.
*
* @param MeprSubscription $sub The subscription object to update.
*
* @return integer The ID of the updated subscription.
*/
public static function update($sub)
{
$mepr_db = new MeprDb();
$args = $sub->get_values();
$str = MeprUtils::object_to_string($args);
return MeprHooks::apply_filters('mepr_update_subscription', $mepr_db->update_record($mepr_db->subscriptions, $sub->id, $args), $args, $sub->user_id);
}
/**
* Destroys the subscription and its associated transactions.
*
* @return boolean True on success, false on failure.
*/
public function destroy()
{
$mepr_db = new MeprDb();
$id = $this->id;
$args = compact('id');
$sub = self::get_one($id);
$txns = MeprTransaction::get_all_by_subscription_id($id);
if (!empty($txns)) {
foreach ($txns as $txn) {
$kill_txn = new MeprTransaction($txn->id);
$kill_txn->destroy();
}
}
$subscription_id = $this->id;
MeprHooks::do_action('mepr_subscription_pre_delete', $subscription_id);
$res = $mepr_db->delete_records($mepr_db->subscriptions, $args);
MeprHooks::do_action('mepr_subscription_deleted', $this);
return $res;
}
/**
* Retrieves the attributes of the subscription.
*
* @return array The attributes of the subscription.
*/
public function get_attrs()
{
return array_keys((array)$this->rec);
}
/**
* Returns the trial days for the subscription.
*
* @param integer $value The value to extend the trial by.
*
* @return integer The trial days.
*/
public function get_extend_trial_days($value)
{
return $this->trial ? $value : 0;
}
/**
* Returns the trial amount based on the trial status.
*
* @param float $value The value to extend the trial by.
*
* @return float The trial amount.
*/
public function get_extend_trial_amount($value)
{
return $this->trial ? $value : 0.00;
}
/**
* Checks if a subscription exists by ID.
*
* @param integer $id The ID of the subscription.
*
* @return boolean True if the subscription exists, false otherwise.
*/
public static function exists($id)
{
$mepr_db = MeprDb::fetch();
return $mepr_db->record_exists($mepr_db->subscriptions, compact('id'));
}
/**
* Retrieves a single subscription by ID.
*
* @param integer $id The ID of the subscription.
* @param string $return_type The return type (default: OBJECT).
*
* @return mixed The subscription object or array.
*/
public static function get_one($id, $return_type = OBJECT)
{
$mepr_db = new MeprDb();
$args = compact('id');
return $mepr_db->get_one_record($mepr_db->subscriptions, $args, $return_type);
}
/**
* Get a subscription by subscription ID.
*
* @param string $subscr_id The subscription ID to search for.
*
* @return MeprSubscription|false The subscription instance, or false if not found.
*/
public static function get_one_by_subscr_id($subscr_id)
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$sub_id = $wpdb->get_var(
$wpdb->prepare(
"SELECT sub.id FROM {$wpdb->mepr_subscriptions} AS sub WHERE sub.subscr_id=%s ORDER BY sub.id DESC LIMIT 1",
$subscr_id
)
);
if ($sub_id) {
return new MeprSubscription($sub_id);
} else {
return false;
}
}
/**
* Searches for subscriptions by subscription ID.
*
* @param string $search The subscription ID to search for.
*
* @return array The list of matching subscriptions.
*/
public static function search_by_subscr_id($search)
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$ids = $wpdb->get_col(
$wpdb->prepare(
"SELECT id FROM {$wpdb->mepr_subscriptions} WHERE subscr_id LIKE %s",
$wpdb->esc_like($search) . '%'
)
);
$subs = [];
if (!empty($ids)) {
foreach ($ids as $id) {
$subs[] = new MeprSubscription($id);
}
}
return $subs;
}
/**
* Retrieves all active subscriptions by user ID.
*
* @param integer $user_id The user ID.
* @param string $order The order clause.
* @param string $limit The limit clause.
* @param boolean $count Whether to return the count only.
* @param boolean $look_for_lapsed Whether to look for lapsed subscriptions.
*
* @return mixed The list of active subscriptions or the count.
*/
public static function get_all_active_by_user_id($user_id, $order = '', $limit = '', $count = false, $look_for_lapsed = false)
{
global $wpdb;
$order = empty($order) ? '' : " ORDER BY {$order}";
$limit = empty($limit) ? '' : " LIMIT {$limit}";
$fields = $count ? 'COUNT(*)' : 'sub.*';
$sql = "
SELECT {$fields}
FROM {$wpdb->mepr_subscriptions} AS sub
JOIN {$wpdb->mepr_transactions} AS t
ON sub.id = t.subscription_id
WHERE t.user_id = %d
AND t.status IN(%s,%s)
AND sub.status <> %s
";
// Canceled subscriptions are not "lapsed" - don't change this or it breaks sub -> lifetime -> sub upgrades.
if ($look_for_lapsed) {
$sql .= "
AND ( sub.status <> %s OR sub.status = %s and t.expires_at > %s )
{$order}{$limit}
";
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$prepared_sql = $wpdb->prepare($sql, $user_id, MeprTransaction::$complete_str, MeprTransaction::$confirmed_str, MeprSubscription::$pending_str, MeprSubscription::$cancelled_str, MeprSubscription::$cancelled_str, MeprUtils::db_now());
} else {
$sql .= "
AND t.expires_at > %s
{$order}{$limit}
";
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$prepared_sql = $wpdb->prepare($sql, $user_id, MeprTransaction::$complete_str, MeprTransaction::$confirmed_str, MeprSubscription::$pending_str, MeprUtils::db_now());
}
if ($count) {
return $wpdb->get_var($prepared_sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
} else {
return $wpdb->get_results($prepared_sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
}
}
/**
* Retrieves all subscriptions.
*
* @return array The list of all subscriptions.
*/
public static function get_all()
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
return $wpdb->get_results("SELECT * FROM {$wpdb->mepr_subscriptions}");
}
/**
* Checks if a subscription exists by subscription ID.
*
* @param string $subscr_id The subscription ID.
*
* @return boolean True if the subscription exists, false otherwise.
*/
public static function subscription_exists($subscr_id)
{
return is_object(self::get_one_by_subscr_id($subscr_id));
}
/**
* Sets the membership ID to 0 if for some reason a membership is deleted.
*
* @param integer $id The ID of the subscription.
*
* @return void
*/
public static function nullify_product_id_on_delete($id)
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->mepr_subscriptions} SET product_id = 0 WHERE product_id = %d", $id));
}
/**
* Sets the user ID to 0 if for some reason a user is deleted.
*
* @param integer $id The ID of the subscription.
*
* @return void
*/
public static function nullify_user_id_on_delete($id)
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->query($wpdb->prepare("UPDATE {$wpdb->mepr_subscriptions} SET user_id = 0 WHERE user_id = %d", $id));
}
/**
* Retrieves the account subscription table.
*
* @param string $order_by The column to order by.
* @param string $order The order direction.
* @param string $paged The page number.
* @param string $search The search term.
* @param string $search_field The field to search in.
* @param integer $perpage The number of items per page.
* @param boolean $countonly Whether to return the count only.
* @param array $params Additional parameters.
* @param string $encols The columns to include.
*
* @return array The subscription table data.
*/
public static function account_subscr_table(
$order_by = '',
$order = '',
$paged = '',
$search = '',
$search_field = 'any',
$perpage = 10,
$countonly = false,
$params = null,
$encols = 'all'
) {
global $wpdb;
// Get the individual queries.
$lsql = self::lifetime_subscr_table(
'',
'',
'',
$search,
$search_field,
0,
$countonly,
$params,
$encols,
true
);
$sql = self::subscr_table(
'',
'',
'',
$search,
$search_field,
0,
$countonly,
$params,
$encols,
true
);
/*
-- Ordering parameters --
*/
// Parameters that are going to be used to order the result.
$order_by = (!empty($order_by) and !empty($order)) ? ($order_by = ' ORDER BY ' . $order_by . ' ' . $order) : '';
// Page Number.
if (empty($paged) or !is_numeric($paged) or $paged <= 0) {
$paged = 1;
}
$limit = '';
// Adjust the query to take pagination into account.
if (!empty($paged) and !empty($perpage)) {
$offset = ($paged - 1) * $perpage;
$limit = ' LIMIT ' . (int)$offset . ',' . (int)$perpage;
}
$wpdb->query('SET SQL_BIG_SELECTS=1'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$asql = "({$lsql['query']}) UNION ({$sql['query']}){$order_by}{$limit}";
$acsql = "SELECT (({$lsql['total_query']}) + ({$sql['total_query']}))";
$results = $wpdb->get_results($asql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
$count = $wpdb->get_var($acsql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
return compact('results', 'count');
}
/**
* Retrieves the subscription table.
*
* @param string $order_by The column to order by.
* @param string $order The order direction.
* @param string $paged The page number.
* @param string $search The search term.
* @param string $search_field The field to search in.
* @param integer $perpage The number of items per page.
* @param boolean $countonly Whether to return the count only.
* @param array $params Additional parameters.
* @param string $encols The columns to include.
* @param boolean $queryonly Whether to return the query only.
*
* @return array The subscription table data.
*/
public static function subscr_table(
$order_by = '',
$order = '',
$paged = '',
$search = '',
$search_field = '',
$perpage = 10,
$countonly = false,
$params = null,
$encols = 'all',
$queryonly = false
) {
global $wpdb;
$mepr_options = MeprOptions::fetch();
$pmt_methods = $mepr_options->payment_methods();
$mepr_db = new MeprDb();
$en = function ($c, $e) {
return (!is_array($e) || in_array($c, $e, true));
};
if (is_null($params)) {
$params = $_GET;
}
if (!empty($pmt_methods)) {
$gateway = '(SELECT CASE sub.gateway';
foreach ($pmt_methods as $method) {
$gateway .= $wpdb->prepare(' WHEN %s THEN %s', $method->id, "{$method->label} ({$method->name})");
}
$gateway .= $wpdb->prepare(' ELSE %s END)', __('Unknown', 'memberpress'));
} else {
$gateway = 'sub.gateway';
}
// The transaction count.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
$txn_count = $wpdb->prepare(
"
(SELECT COUNT(*)
FROM {$mepr_db->transactions} AS txn_cnt
WHERE txn_cnt.subscription_id=sub.id
AND txn_cnt.status=%s)",
MeprTransaction::$complete_str
); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
$active = $wpdb->prepare(
'
(SELECT
CASE
WHEN expiring_txn.expires_at = 0
OR expiring_txn.expires_at = %s
THEN %s
WHEN expiring_txn.expires_at IS NULL
OR expiring_txn.expires_at < %s
THEN %s
WHEN expiring_txn.status IN (%s,%s)
AND expiring_txn.txn_type IN (%s,%s,%s)
THEN %s
ELSE %s
END
)',
MeprUtils::db_lifetime(),
'<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>',
MeprUtils::db_now(),
'<span class="mepr-inactive">' . __('No', 'memberpress') . '</span>',
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str,
MeprTransaction::$subscription_confirmation_str,
MeprTransaction::$sub_account_str,
MeprTransaction::$woo_txn_str,
'<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>',
'<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>'
);
$fname = "
(SELECT um_fname.meta_value
FROM {$wpdb->usermeta} AS um_fname
WHERE um_fname.user_id = u.ID
AND um_fname.meta_key = 'first_name'
LIMIT 1)
";
$lname = "
(SELECT um_lname.meta_value
FROM {$wpdb->usermeta} AS um_lname
WHERE um_lname.user_id = u.ID
AND um_lname.meta_key = 'last_name'
LIMIT 1)
";
$cols = ['sub_type' => "'subscription'"];
if ($en('id', $encols)) {
$cols['id'] = 'sub.id';
}
if ($en('user_email', $encols)) {
$cols['user_email'] = 'u.user_email';
}
if ($en('gateway', $encols)) {
$cols['gateway'] = 'sub.gateway';
}
if ($en('member', $encols)) {
$cols['member'] = 'u.user_login';
}
if ($en('first_name', $encols)) {
$cols['first_name'] = $fname;
}
if ($en('last_name', $encols)) {
$cols['last_name'] = $lname;
}
if ($en('product_name', $encols)) {
$cols['product_name'] = 'prd.post_title';
}
if ($en('first_txn_id', $encols)) {
$cols['first_txn_id'] = 'first_txn.id';
}
if ($en('latest_txn_id', $encols)) {
$cols['latest_txn_id'] = 'last_txn.id';
}
if ($en('expiring_txn_id', $encols)) {
$cols['expiring_txn_id'] = 'expiring_txn.id';
}
if ($en('txn_count', $encols)) {
$cols['txn_count'] = $txn_count;
}
if ($en('expires_at', $encols)) {
$cols['expires_at'] = 'expiring_txn.expires_at';
}
$tmp_sub = new MeprSubscription();
$pms = $tmp_sub->get_attrs();
// Add postmeta columns.
foreach ($pms as $slug) {
if ($en($slug, $encols)) {
$cols[$slug] = "sub.{$slug}";
}
}
if (wp_doing_ajax()) {
$stripe_customer_id_keys = $wpdb->get_col($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
"SELECT DISTINCT meta_key FROM {$wpdb->usermeta} WHERE meta_key LIKE %s",
$wpdb->esc_like('_mepr_stripe_customer_id_') . '%'
));
if (is_array($stripe_customer_id_keys)) {
foreach ($stripe_customer_id_keys as $stripe_customer_id_key) {
if ($en($stripe_customer_id_key, $encols)) {
$cols[$stripe_customer_id_key] = $wpdb->prepare(
"
IFNULL(
(
SELECT meta_value
FROM {$wpdb->usermeta}
WHERE meta_key = %s
AND user_id = u.ID
LIMIT 1
),
''
)
",
$stripe_customer_id_key
);
}
}
}
}
// Very important this comes after the meta columns ...
// must maintain same order as the lifetime table.
if ($en('active', $encols)) {
$cols['active'] = $active;
}
$args = [];
if (array_key_exists('member', $params)) {
if (empty($params['member'])) {
$params['member'] = null;
}
$args[] = $wpdb->prepare('u.user_login = %s', $params['member']);
}
if (isset($params['subscription']) && !empty($params['subscription'])) {
$args[] = $wpdb->prepare('sub.id = %d', $params['subscription']);
}
if (isset($params['prd_id']) && is_numeric($params['prd_id'])) {
$args[] = $wpdb->prepare('sub.product_id = %d', $params['prd_id']);
}
if (isset($params['membership']) && is_numeric($params['membership'])) {
$args[] = $wpdb->prepare('sub.product_id = %d', $params['membership']);
}
if (isset($params['status']) && $params['status'] !== 'all') {
$args[] = $wpdb->prepare('sub.status = %s', $params['status']);
}
if (isset($params['gateway']) && $params['gateway'] !== 'all') {
$args[] = $wpdb->prepare('sub.gateway = %s', $params['gateway']);
}
if (isset($params['statuses']) && !empty($params['statuses'])) {
$qry = [];
foreach ($params['statuses'] as $st) {
$qry[] = $wpdb->prepare('sub.status = %s', $st);
}
$args[] = '(' . implode(' OR ', $qry) . ')';
}
$joins = [];
$important_joins = ['status','user_id','product_id'];
$joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->users} AS u ON u.ID = sub.user_id";
$joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->posts} AS prd ON prd.ID = sub.product_id";
// The first transaction.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$joins[] = $wpdb->prepare(
"LEFT JOIN {$mepr_db->transactions} AS first_txn
ON first_txn.id=(
SELECT ft1.id
FROM {$mepr_db->transactions} AS ft1
WHERE ft1.subscription_id=sub.id
AND ft1.status IN (%s,%s)
ORDER BY ft1.id ASC
LIMIT 1
)",
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str
);
// The last transaction made.
$joins[] = $wpdb->prepare(
"LEFT JOIN {$mepr_db->transactions} AS last_txn
ON last_txn.id=(
SELECT lt1.id
FROM {$mepr_db->transactions} AS lt1
WHERE lt1.subscription_id=sub.id
AND lt1.status IN (%s,%s)
ORDER BY lt1.id DESC
LIMIT 1
)",
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str
);
// The transaction associated with this subscription with the latest expiration date.
$joins[] = $wpdb->prepare(
"LEFT JOIN {$mepr_db->transactions} AS expiring_txn
ON expiring_txn.id = (
SELECT t.id
FROM {$mepr_db->transactions} AS t
WHERE t.subscription_id=sub.id
AND t.status IN (%s,%s)
AND ( t.expires_at = %s
OR ( t.expires_at <> %s
AND t.expires_at=(
SELECT MAX(t2.expires_at)
FROM {$mepr_db->transactions} as t2
WHERE t2.subscription_id=sub.id
AND t2.status IN (%s,%s)
)
)
)
ORDER BY t.expires_at
LIMIT 1
)",
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str,
MeprUtils::db_lifetime(),
MeprUtils::db_lifetime(),
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str
); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return MeprDb::list_table(
MeprHooks::apply_filters('mepr_recurring_subscriptions_table_cols', $cols),
MeprHooks::apply_filters('mepr_recurring_subscriptions_table_from', "{$mepr_db->subscriptions} AS sub"),
MeprHooks::apply_filters('mepr_recurring_subscriptions_table_joins', $joins),
MeprHooks::apply_filters('mepr_recurring_subscriptions_table_args', $args),
$order_by,
$order,
$paged,
$search,
$search_field,
$perpage,
$countonly,
$queryonly
);
}
// Okay, these are actually transactions but to the unwashed masses ... they're subscriptions.
/**
* Generates a table of lifetime subscriptions.
*
* @param string $order_by The column to order by.
* @param string $order The order to sort the results.
* @param integer $paged The page number to display.
* @param string $search The search query.
* @param string $search_field The field to search.
* @param integer $perpage The number of results per page.
* @param boolean $countonly Whether to count only.
* @param array $params The parameters.
* @param string $encols The columns to display.
* @param boolean $queryonly Whether to return a query only.
*
* @return array The list of lifetime subscriptions.
*/
public static function lifetime_subscr_table(
$order_by = '',
$order = '',
$paged = '',
$search = '',
$search_field = 'any',
$perpage = 10,
$countonly = false,
$params = null,
$encols = 'all',
$queryonly = false
) {
global $wpdb;
$mepr_options = MeprOptions::fetch();
$pmt_methods = $mepr_options->payment_methods();
$mepr_db = new MeprDb();
$en = function ($c, $e) {
return (!is_array($e) || in_array($c, $e, true));
};
if (is_null($params)) {
$params = $_GET;
}
if (!empty($pmt_methods)) {
$gateway = '(SELECT CASE txn.gateway';
foreach ($pmt_methods as $method) {
$gateway .= $wpdb->prepare(' WHEN %s THEN %s', $method->id, "{$method->label} ({$method->name})");
}
$gateway .= $wpdb->prepare(' ELSE %s END)', __('Unknown', 'memberpress'));
} else {
$gateway = 'txn.gateway';
}
$fname = "(SELECT um_fname.meta_value FROM {$wpdb->usermeta} AS um_fname WHERE um_fname.user_id = u.ID AND um_fname.meta_key = 'first_name' LIMIT 1)";
$lname = "(SELECT um_lname.meta_value FROM {$wpdb->usermeta} AS um_lname WHERE um_lname.user_id = u.ID AND um_lname.meta_key = 'last_name' LIMIT 1)";
$cols = ['sub_type' => "'transaction'"];
if ($en('id', $encols)) {
$cols['id'] = 'txn.id';
}
if ($en('user_email', $encols)) {
$cols['user_email'] = 'u.user_email';
}
if ($en('gateway', $encols)) {
$cols['gateway'] = $gateway;
}
if ($en('member', $encols)) {
$cols['member'] = 'u.user_login';
}
if ($en('first_name', $encols)) {
$cols['first_name'] = $fname;
}
if ($en('last_name', $encols)) {
$cols['last_name'] = $lname;
}
if ($en('product_name', $encols)) {
$cols['product_name'] = 'prd.post_title';
}
if ($en('first_txn_id', $encols)) {
$cols['first_txn_id'] = 'txn.id';
}
if ($en('latest_txn_id', $encols)) {
$cols['latest_txn_id'] = 'txn.id';
}
if ($en('expiring_txn_id', $encols)) {
$cols['expiring_txn_id'] = 'txn.id';
}
if ($en('txn_count', $encols)) {
$cols['txn_count'] = $wpdb->prepare('%s', 1);
}
if ($en('expires_at', $encols)) {
$cols['expires_at'] = 'txn.expires_at';
}
if ($en('subscr_id', $encols)) {
$cols['subscr_id'] = 'txn.trans_num';
}
if ($en('user_id', $encols)) {
$cols['user_id'] = 'txn.user_id';
}
if ($en('product_id', $encols)) {
$cols['product_id'] = 'txn.product_id';
}
if ($en('coupon_id', $encols)) {
$cols['coupon_id'] = 'txn.coupon_id';
}
if ($en('coupon', $encols)) {
$cols['coupon'] = 'c.post_title';
}
if ($en('price', $encols)) {
$cols['price'] = 'txn.amount';
}
if ($en('period', $encols)) {
$cols['period'] = $wpdb->prepare('%d', 1);
}
if ($en('period_type', $encols)) {
$cols['period_type'] = $wpdb->prepare('%s', 'lifetime');
}
if ($en('prorated_trial', $encols)) {
$cols['prorated_trial'] = $wpdb->prepare('%d', 0);
}
if ($en('trial', $encols)) {
$cols['trial'] = $wpdb->prepare('%d', 0);
}
if ($en('trial_days', $encols)) {
$cols['trial_days'] = $wpdb->prepare('%d', 0);
}
if ($en('trial_amount', $encols)) {
$cols['trial_amount'] = $wpdb->prepare('%f', 0.00);
}
if ($en('trial_tax_amount', $encols)) {
$cols['trial_tax_amount'] = $wpdb->prepare('%f', 0.00);
}
if ($en('trial_total', $encols)) {
$cols['trial_total'] = $wpdb->prepare('%f', 0.00);
}
if ($en('status', $encols)) {
$cols['status'] = $wpdb->prepare('%s', __('None', 'memberpress'));
}
if ($en('created_at', $encols)) {
$cols['created_at'] = 'txn.created_at';
}
if ($en('active', $encols)) {
$cols['active'] = $wpdb->prepare(
'
(SELECT
CASE
WHEN txn.status IN (%s,%s)
AND ( txn.expires_at = %s OR
txn.expires_at >= %s )
THEN %s
ELSE %s
END)',
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str,
MeprUtils::db_lifetime(),
MeprUtils::db_now(),
'<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>',
'<span class="mepr-inactive">' . __('No', 'memberpress') . '</span>'
);
}
$args = ['(txn.subscription_id IS NULL OR txn.subscription_id <= 0)'];
if (array_key_exists('member', $params)) {
if (empty($params['member'])) {
$params['member'] = null;
}
$args[] = $wpdb->prepare('u.user_login = %s', $params['member']);
}
if (isset($params['subscription']) and !empty($params['subscription'])) {
$args[] = $wpdb->prepare('txn.id = %d', $params['subscription']);
}
if (isset($params['prd_id']) && is_numeric($params['prd_id'])) {
$args[] = $wpdb->prepare('prd.ID = %d', $params['prd_id']);
}
if (isset($params['membership']) && is_numeric($params['membership'])) {
$args[] = $wpdb->prepare('prd.ID = %d', $params['membership']);
}
if (isset($params['gateway']) && $params['gateway'] !== 'all') {
$args[] = $wpdb->prepare('txn.gateway = %s', $params['gateway']);
}
if (isset($params['statuses']) && !empty($params['statuses'])) {
$qry = [];
foreach ($params['statuses'] as $st) {
// Map subscription status to transaction status.
$txn_status = MeprTransaction::map_subscr_status($st);
if (!$txn_status) {
continue;
}
if (!is_array($txn_status)) {
$txn_status = [$txn_status];
}
foreach ($txn_status as $txn_st) {
$qry[] = $wpdb->prepare('txn.status=%s', $txn_st);
}
}
$args[] = '(' . implode(' OR ', $qry) . ')';
}
$joins = [];
$joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->users} AS u ON u.ID = txn.user_id";
$joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->posts} AS prd ON prd.ID = txn.product_id";
$joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->posts} AS c ON c.ID = txn.coupon_id";
if ($en('period_type', $encols)) {
$joins[] = $wpdb->prepare("LEFT JOIN {$wpdb->postmeta} AS pm_period_type ON pm_period_type.post_id = prd.ID AND pm_period_type.meta_key = %s", MeprProduct::$period_type_str);
}
return MeprDb::list_table(
MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_cols', $cols),
MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_from', "{$mepr_db->transactions} AS txn"),
MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_joins', $joins),
MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_args', $args),
$order_by,
$order,
$paged,
$search,
$search_field,
$perpage,
$countonly,
$queryonly
);
}
/**
* Retrieves the user associated with the subscription.
*
* @param boolean $force Whether to force a new user object.
*
* @return MeprUser The user object.
*/
public function user($force = false)
{
// Don't do static caching stuff here.
return new MeprUser($this->user_id);
}
/**
* Retrieves the product associated with the subscription.
*
* @return MeprProduct The product object.
*/
public function product()
{
// Don't do static caching stuff here.
return MeprHooks::apply_filters('mepr_subscription_product', new MeprProduct($this->product_id), $this);
}
/**
* Retrieves the membership group associated with the subscription through its product.
*
* @return MeprGroup|false The group object or false if not found.
*/
public function group()
{
$prd = $this->product();
return $prd->group();
}
/**
* Retrieves the coupon associated with the subscription.
*
* @return MeprCoupon|false The coupon object or false if not found.
*/
public function coupon()
{
// Don't do static caching stuff here.
if (!isset($this->coupon_id) || (int)$this->coupon_id <= 0) {
return false;
}
$coupon = new MeprCoupon($this->coupon_id);
if (!isset($coupon->ID) || $coupon->ID <= 0) {
return false;
}
return $coupon;
}
/**
* Get the order associated with this subscription
*
* @return MeprOrder|false
*/
public function order()
{
// Don't do static caching stuff here.
if (empty($this->order_id)) {
return false;
}
$order = new MeprOrder($this->order_id);
if ((int) $order->id <= 0) {
return false;
}
return $order;
}
/**
* Retrieves the first transaction associated with the subscription.
*
* @return MeprTransaction|false The first transaction object or false if not found.
*/
public function first_txn()
{
$first_txn_id = $this->first_txn_id;
return empty($first_txn_id) ? false : new MeprTransaction($this->first_txn_id);
}
/**
* Retrieves the latest transaction associated with the subscription.
*
* @return MeprTransaction|false The latest transaction object or false if not found.
*/
public function latest_txn()
{
$latest_txn_id = $this->latest_txn_id;
return empty($latest_txn_id) ? false : new MeprTransaction($this->latest_txn_id);
}
/**
* Retrieves the expiring transaction associated with the subscription.
*
* @return MeprTransaction|false The expiring transaction object or false if not found.
*/
public function expiring_txn()
{
$expiring_txn_id = $this->expiring_txn_id;
return empty($expiring_txn_id) ? false : new MeprTransaction($this->expiring_txn_id);
}
/**
* Retrieves transactions associated with the subscription.
*
* @param boolean $return_objects Whether to return transaction objects.
* @param string $where Additional WHERE clause.
* @param string $order ORDER BY clause.
*
* @return array The list of transactions.
*/
public function transactions($return_objects = true, $where = '', $order = 'created_at')
{
global $wpdb;
if (!empty($where)) {
$where = "AND {$where}";
}
if (!empty($order) && !preg_match('/ORDER BY/i', $order)) {
$order = "ORDER BY {$order}";
}
// phpcs:ignore WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
$res = $wpdb->get_col(
$wpdb->prepare(
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT id FROM {$wpdb->mepr_transactions} AS t WHERE t.subscription_id = %d {$where} {$order}",
$this->id
)
);
if ($return_objects and !empty($res)) {
$txns = [];
foreach ($res as $id) {
$txns[] = new MeprTransaction($id);
}
return $txns;
}
return $res;
}
/**
* Cancels the subscription if payment cycles limit is reached.
* Uses $trial_offset if a paid trial payment exists.
*
* @return void
*/
public function limit_payment_cycles()
{
// Check if limiting is even enabled.
if (!$this->limit_cycles) {
return;
}
$pm = $this->payment_method();
if ($pm === false) {
return; // What else to do here?
}
$trial_offset = (($this->trial && $this->trial_amount > 0.00) ? 1 : 0);
if (1 === $trial_offset && $this->prorated_trial && $this->trial && $this->limit_cycles) {
$trial_offset = 0;
}
// Cancel this subscription if the payment cycles are limited and have been reached.
if ($this->status === MeprSubscription::$active_str && ($this->txn_count - $trial_offset) >= $this->limit_cycles_num) {
$_REQUEST['expire'] = true; // Pass the expire request.
$_REQUEST['silent'] = true; // Don't want to send cancellation notices.
try {
$pm->process_cancel_subscription($this->id);
} catch (Exception $e) {
// TODO: We might want to actually do something here at some point.
return;
}
}
}
/**
* This should be called from process_cancel_subscription
*
* @return void
*/
public function limit_reached_actions()
{
// Check if limiting is even enabled.
if (!$this->limit_cycles) {
return;
}
if ($this->limit_cycles_action === 'lifetime' || $this->limit_cycles_action === 'expires_after') {
$txn = $this->latest_txn();
if (!empty($txn) && $txn instanceof MeprTransaction) {
if ($this->limit_cycles_action === 'lifetime') {
$txn->expires_at = MeprUtils::db_lifetime(); // Lifetime expiration.
} elseif ($this->limit_cycles_action === 'expires_after') {
$expires_at = $this->get_expires_at(strtotime($txn->created_at));
switch ($this->limit_cycles_expires_type) {
case 'days':
$expires_at += MeprUtils::days($this->limit_cycles_expires_after);
break;
case 'weeks':
$expires_at += MeprUtils::weeks($this->limit_cycles_expires_after);
break;
case 'months':
$expires_at += MeprUtils::months($this->limit_cycles_expires_after, strtotime($txn->created_at));
break;
case 'years':
$expires_at += MeprUtils::years($this->limit_cycles_expires_after, strtotime($txn->created_at));
}
$txn->expires_at = MeprUtils::ts_to_mysql_date($expires_at); // Lifetime expiration.
}
$txn->store();
}
}
MeprHooks::do_action('mepr_limit_payment_cycles_reached', $this);
}
/**
* Expires transactions for the subscription.
*
* @return void
*/
public function expire_txns()
{
global $wpdb;
$time = time();
// Set expiration 1 day in the past so it expires NOW.
$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->prepare(
"UPDATE {$wpdb->mepr_transactions} SET expires_at = %s WHERE subscription_id = %d AND subscription_id > 0 AND expires_at >= %s",
MeprUtils::ts_to_mysql_date($time - MeprUtils::days(1)),
$this->id,
MeprUtils::ts_to_mysql_date($time)
)
);
}
/**
* Expires a confirmation transaction when a payment fails, is refunded, or the subscription is cancelled.
*
* @return void
*/
public function expire_confirmation_txn()
{
global $wpdb;
$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
$wpdb->prepare(
"UPDATE {$wpdb->mepr_transactions} SET expires_at = created_at WHERE subscription_id = %d AND txn_type = %s AND expires_at >= %s",
$this->id,
MeprTransaction::$subscription_confirmation_str,
MeprUtils::db_now()
)
);
}
/**
* Retrieves the payment method associated with the subscription.
*
* @return mixed The payment method object or false if not found.
*/
public function payment_method()
{
$mepr_options = MeprOptions::fetch();
return $mepr_options->payment_method($this->gateway);
}
/**
* Checks if the subscription is in a free trial.
*
* @return boolean True if in a free trial, false otherwise.
*/
public function in_free_trial()
{
return $this->in_trial('free');
}
/**
* Checks if the subscription is in a paid trial.
*
* @return boolean True if in a paid trial, false otherwise.
*/
public function in_paid_trial()
{
return $this->in_trial('paid');
}
/**
* Checks if the subscription is in a trial.
*
* @param string $type The type of trial to check ('all', 'paid', or 'free').
*
* @return boolean True if in a trial, false otherwise.
*/
public function in_trial($type = 'all')
{
// If no sub id then we're still checking out and this should return false, we're not YET in a trial.
if ($this->id <= 0) {
return false;
}
if ($this->trial) {
$trial_started = is_null($this->created_at) ? time() : strtotime($this->created_at);
$trial_ended = $trial_started + MeprUtils::days($this->trial_days);
if (($type === 'paid' && (float)$this->trial_amount <= 0.00) || ($type === 'free' && (float)$this->trial_amount > 0.00)) {
return false;
}
return (time() < $trial_ended);
}
return false;
}
/**
* Checks if the subscription is in a grace period.
*
* @return boolean True if in a grace period, false otherwise.
*/
public function in_grace_period()
{
if ((int) $this->txn_count === 0 && $this->status === self::$active_str) {
$first_txn = $this->first_txn();
// The subscription hasn't been cancelled, and it doesn't have any "payment" txn's yet
// So let's check the confirmation expiration now (using 25 hours as a bit of a leway for the 24 hour grace period).
if (
$first_txn instanceof MeprTransaction &&
$first_txn->txn_type === MeprTransaction::$subscription_confirmation_str &&
strtotime($first_txn->expires_at) >= time() &&
(strtotime($first_txn->expires_at) - strtotime($first_txn->created_at)) <= MeprUtils::hours(25)
) {
return true;
}
}
return false;
}
/**
* Retrieves the number of days until the subscription expires.
*
* @return integer The number of days until expiration.
*/
public function days_till_expiration()
{
$expiring_txn = $this->expiring_txn();
return $expiring_txn->days_till_expiration();
}
/**
* Retrieves the number of days in the current subscription period.
*
* @param boolean $ignore_trial Whether to ignore the trial period.
*
* @return integer The number of days in the current period.
*/
public function days_in_this_period($ignore_trial = false)
{
if ($this->in_trial() && !$ignore_trial) {
$period_seconds = MeprUtils::days($this->trial_days);
} else {
if ((int)$this->id > 0) {
$latest_txn = $this->latest_txn();
if (!($latest_txn instanceof MeprTransaction)) {
$latest_txn = new MeprTransaction();
$latest_txn->created_at = MeprUtils::ts_to_mysql_date(time());
}
} else {
// This could happen when checking upgrade prorated price on a new sub.
$latest_txn = new MeprTransaction();
$latest_txn->created_at = MeprUtils::ts_to_mysql_date(time());
}
switch ($this->period_type) {
case 'weeks':
$period_seconds = MeprUtils::weeks($this->period);
break;
case 'months':
if (!$this->id) {
$period_seconds = MeprUtils::months($this->period, time()); // Probably an upgrade calculation, x months from now.
} else {
$add_days = 0;
if ($this->trial) {
$add_days = $this->trial_days;
}
$renewal_dom = gmdate('j', strtotime(gmdate('c', strtotime($this->renewal_base_date . " +{$add_days} days"))));
$period_seconds = MeprUtils::months($this->period, strtotime($latest_txn->created_at), false, $renewal_dom);
}
break;
case 'years':
$period_seconds = MeprUtils::years($this->period, strtotime($latest_txn->created_at));
break;
default:
return false;
}
}
return intval(round($period_seconds / MeprUtils::days(1)));
}
/**
* Retrieves the timestamp when the trial expires.
*
* @return integer The timestamp when the trial expires.
*/
public function trial_expires_at()
{
$created_at = strtotime($this->created_at);
return ($created_at + MeprUtils::days($this->trial_days));
}
/**
* Checks if the subscription is expired.
*
* @param integer $offset The offset in seconds to check expiration.
*
* @return boolean True if expired, false otherwise.
*/
public function is_expired($offset = 0)
{
// Check for a lifetime first.
if (is_null($this->expires_at) || $this->expires_at === MeprUtils::db_lifetime()) {
return false;
}
// Check for a false expires_at date.
if ($this->expires_at === false) {
return true;
}
$todays_ts = time() + $offset; // Use the offset to check when a txn will expire.
$expires_ts = strtotime($this->expires_at);
return ($expires_ts < $todays_ts);
}
/**
* Checks if the most recent transaction is a failure.
*
* @return boolean True if the most recent transaction is a failure, false otherwise.
*/
public function latest_txn_failed()
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$status = $wpdb->get_var(
$wpdb->prepare(
"SELECT status FROM {$wpdb->mepr_transactions} WHERE subscription_id = %d ORDER BY id DESC LIMIT 1",
$this->id
)
);
return ($status === MeprTransaction::$failed_str);
}
/**
* Checks if the most recent transaction is a refund.
*
* @return boolean True if the most recent transaction is a refund, false otherwise.
*/
public function latest_txn_refunded()
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$status = $wpdb->get_var(
$wpdb->prepare(
"SELECT status FROM {$wpdb->mepr_transactions} WHERE subscription_id = %d ORDER BY id DESC LIMIT 1",
$this->id
)
);
return ($status === MeprTransaction::$refunded_str);
}
/**
* Checks if the subscription is a lifetime subscription.
*
* @return boolean True if it is a lifetime subscription, false otherwise.
*/
public function is_lifetime()
{
return ($this->expires_at === MeprUtils::db_lifetime());
}
/**
* Checks if the subscription is active.
*
* @return boolean True if active, false otherwise.
*/
public function is_active()
{
return !$this->is_expired();
}
/**
* Checks if the subscription is cancelled.
*
* @return boolean True if cancelled, false otherwise.
*/
public function is_cancelled()
{
return ($this->status === self::$cancelled_str);
}
/**
* Retrieves the credit card number associated with the subscription.
*
* @return string The credit card number.
*/
public function cc_num()
{
return MeprUtils::cc_num($this->cc_last4);
}
/**
* Checks if the subscription is an upgrade.
*
* @return boolean True if it is an upgrade, false otherwise.
*/
public function is_upgrade()
{
return $this->is_upgrade_or_downgrade('upgrade');
}
/**
* Checks if the subscription is a downgrade.
*
* @return boolean True if it is a downgrade, false otherwise.
*/
public function is_downgrade()
{
return $this->is_upgrade_or_downgrade('downgrade');
}
/**
* Checks if the subscription is an upgrade or downgrade.
*
* @param string|boolean $type The type to check ('upgrade' or 'downgrade').
*
* @return boolean True if it is an upgrade or downgrade, false otherwise.
*/
public function is_upgrade_or_downgrade($type = false)
{
$prd = $this->product();
$usr = $this->user();
return ($prd->is_upgrade_or_downgrade($type, $usr));
}
/**
* Sets up the prorated trial.
* This doesn't store ... do what you will later on
*
* @return void
*/
public function maybe_prorate()
{
$mepr_options = MeprOptions::fetch();
$usr = $this->user();
$this->prorated_trial = false;
if (
$usr->is_logged_in_and_current_user() &&
$this->is_upgrade_or_downgrade() &&
$mepr_options->pro_rated_upgrades
) {
$grp = $this->group();
$old_lifetime = $usr->lifetime_subscription_in_group($grp->ID);
// One-time payment upgrade?
if ($old_lifetime !== false) {
$old_amount = $old_lifetime->amount;
$new_amount = ($this->trial) ? $this->trial_amount : $this->price;
$old_period = $old_lifetime->days_in_this_period();
$new_period = $this->days_in_this_period(true);
$days_left = $old_lifetime->days_till_expiration();
$r = MeprUtils::calculate_proration($old_amount, $new_amount, $old_period, $new_period, $days_left, $grp->upgrade_path_reset_period, $old_lifetime, $this);
} else { // Recurring upgrade.
$old_sub = $usr->subscription_in_group($grp->ID);
if ($old_sub && (int) $old_sub->id !== (int) $this->id && !$old_sub->in_free_trial()) {
$r = MeprUtils::calculate_proration_by_subs($old_sub, $this, $grp->upgrade_path_reset_period);
}
}
// Prorations override the trial ... if there is one
// Only makes sense if the days are greater than 0.
if (isset($r) && $r->days > 0) {
$this->prorated_trial = true;
$this->trial = true;
$this->trial_days = $r->days;
$this->trial_amount = MeprUtils::maybe_round_to_minimum_amount($r->proration);
$prd = $this->product();
if (get_option('mepr_calculate_taxes') && !$prd->tax_exempt) {
// Proration amounts are always subtotals (without tax), so we need to apply
// taxes as if they were exclusive, even when inclusive taxes are enabled.
// This prevents double-extraction of tax.
$this->set_trial_taxes(2, true);
} else {
$this->trial_tax_amount = 0.00;
$this->trial_total = $this->trial_amount;
$this->trial_tax_reversal_amount = 0.00;
}
}
}
}
/**
* Cancels the old subscription if necessary.
*
* @param boolean $force_cancel_artificial Whether to force cancel artificial subscriptions.
*
* @return mixed The event transaction or false if not applicable.
*/
public function maybe_cancel_old_sub($force_cancel_artificial = false)
{
$mepr_options = MeprOptions::fetch();
$usr = $this->user();
$grp = $this->group();
$evt_txn = false;
// No group? Not an upgrade then.
if ($grp === false) {
return false;
}
// No upgrade path here ... not an upgrade.
if (!$grp->is_upgrade_path) {
return false;
}
$pm = $this->payment_method();
if (!$force_cancel_artificial && $pm instanceof MeprArtificialGateway && $pm->settings->manually_complete && $pm->settings->no_cancel_up_down_grade) {
// If this is an artifical gateway and admin must manually approve and do not cancel when admin must manually approve
// then don't cancel.
return false;
}
try {
$old_sub = $usr->subscription_in_group($grp->ID, true, $this->id);
if ($old_sub) {
// NOTE: This was added for one specific customer, it should only be used at customers own risk,
// we do not support any custom development or issues that arise from using this hook
// to override the default group behavior.
$override_default_behavior = MeprHooks::apply_filters('mepr_override_group_default_behavior_sub', false, $old_sub);
if (!$override_default_behavior) {
$evt_txn = $old_sub->latest_txn();
$old_sub->expire_txns(); // Expire associated transactions for the old subscription.
$_REQUEST['silent'] = true; // Don't want to send cancellation notices.
if ($old_sub->status !== MeprSubscription::$cancelled_str) {
$old_sub->cancel();
}
}
} else {
$old_lifetime_txn = $usr->lifetime_subscription_in_group($grp->ID);
if ($old_lifetime_txn) {
// NOTE: This was added for one specific customer, it should only be used at customers own risk,
// we do not support any custom development or issues that arise from using this hook
// to override the default group behavior.
$override_default_behavior = MeprHooks::apply_filters('mepr_override_group_default_behavior_lt', false, $old_lifetime_txn);
if (!$override_default_behavior) {
$old_lifetime_txn->expires_at = MeprUtils::ts_to_mysql_date(time() - MeprUtils::days(1));
$old_lifetime_txn->store();
$evt_txn = $old_lifetime_txn;
}
}
}
} catch (Exception $e) {
// Nothing for now.
}
if (!empty($evt_txn)) {
MeprHooks::do_action('mepr_changing_subscription', $this->latest_txn(), $evt_txn);
}
return $evt_txn;
}
/**
* Gets the value for 'expires_at' for the given created_at time for this membership.
*
* @param integer|null $created_ts The timestamp of creation.
*
* @return integer The expiration timestamp.
*/
public function get_expires_at($created_ts = null)
{
$mepr_options = MeprOptions::fetch();
if (is_null($created_ts)) {
$created_ts = time();
}
$expires_ts = $created_ts;
$period = (int) $this->period;
// Used in monthly / yearly calcs.
$renewal_date_ts = MeprUtils::db_date_to_ts($this->renewal_base_date);
if ($this->trial && $this->created_at === $this->renewal_base_date) {
$renewal_date_ts += MeprUtils::days($this->trial_days); // Account for trial periods (but not if paused & resumed with Stripe).
}
switch ($this->period_type) {
case 'days':
$expires_ts += MeprUtils::days($period) + MeprUtils::days($mepr_options->grace_expire_days);
break;
case 'weeks':
$expires_ts += MeprUtils::weeks($period) + MeprUtils::days($mepr_options->grace_expire_days);
break;
case 'months':
$renewal_dom = gmdate('j', $renewal_date_ts);
$expires_ts += MeprUtils::months($period, $created_ts, false, $renewal_dom);
$days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));
// Fixes bug 1136 early/late monthly renewals.
if ($period === 1) {
if ($days_till_expire < ( 27 - $mepr_options->grace_expire_days )) {
// Early renewal - we need to add a month in this case.
$expires_ts += MeprUtils::months($period, $expires_ts, false, $renewal_dom);
} elseif ($days_till_expire > 32) {
// Late renewal - we need to minus a month in this case.
$expires_ts -= MeprUtils::months($period, $expires_ts, true, $renewal_dom);
}
$new_days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));
// One final check, if we're still outside of tolerance just add 1 month from the created_ts like we used to.
if (( $new_days_till_expire < ( 27 - $mepr_options->grace_expire_days ) ) || $new_days_till_expire > 32) {
$expires_ts = ( $created_ts + MeprUtils::months($period, $created_ts) );
}
}
// In the future we need to find a way to make this work with
// non-monthly renewals too. Like quarterly, semi-annual, or custom periods
// But those are pretty rare in actual use, so monthly fixes ($period = 1) here is good enogh for now
// else {
// }.
$expires_ts += MeprUtils::days($mepr_options->grace_expire_days);
break;
case 'years':
$day_num = gmdate('j', $renewal_date_ts);
$month_num = gmdate('n', $renewal_date_ts);
$period_days = $period * 365;
$tolerance = 14; // Max number of days from the renewal date to be considered an early/late renewal.
$expires_ts += MeprUtils::years($period, $created_ts, false, $day_num, $month_num);
$days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));
// Make sure we haven't under cut the expiration time.
if ($days_till_expire < ( $period_days - $tolerance )) {
// Early renewal - we need to add a year in this case.
$expires_ts += MeprUtils::years($period, $expires_ts, false, $day_num, $month_num);
} elseif ($days_till_expire > ( $period_days + $tolerance )) {
// Late renewal - we need to minus a year in this case.
$expires_ts -= MeprUtils::years($period, $expires_ts, true, $day_num, $month_num);
}
$new_days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));
// One final check, if we're still outside of tolerance just add 1 year from the created_ts like we used to.
if (( $new_days_till_expire < ( $period_days - $tolerance ) ) || ( $new_days_till_expire > ( $period_days + $tolerance ) )) {
$expires_ts = ( $created_ts + MeprUtils::years($period, $created_ts) );
}
$expires_ts += MeprUtils::days($mepr_options->grace_expire_days);
break;
default:
$expires_ts = null;
}
return $expires_ts;
}
/**
* Loads product variables into the subscription.
*
* @param MeprProduct $prd The product object.
* @param string|null $cpn_code The coupon code.
* @param boolean $set_subtotal Whether to set the subtotal.
*
* @return void
*/
public function load_product_vars($prd, $cpn_code = null, $set_subtotal = false)
{
$mock_cpn = (object)[
'post_title' => null,
'ID' => 0,
'trial' => 0,
];
if (empty($cpn_code) || !MeprCoupon::is_valid_coupon_code($cpn_code, $prd->ID)) {
$cpn = $mock_cpn;
} else {
$cpn = MeprCoupon::get_one_from_code($cpn_code);
if (!$cpn) {
$cpn = $mock_cpn;
}
}
$this->product_id = $prd->ID;
$this->coupon_id = $cpn->ID;
$this->period = $prd->period;
$this->period_type = $prd->period_type;
$this->limit_cycles = $prd->limit_cycles;
$this->limit_cycles_num = $prd->limit_cycles_num;
$this->limit_cycles_action = $prd->limit_cycles_action;
$this->limit_cycles_expires_after = $prd->limit_cycles_expires_after;
$this->limit_cycles_expires_type = $prd->limit_cycles_expires_type;
$this->trial = $prd->trial;
$this->trial_days = $prd->trial ? $prd->trial_days : 0;
$this->trial_amount = MeprUtils::maybe_round_to_minimum_amount($prd->trial_amount);
// If trial only once is set and the member has
// already had a trial then get rid of it.
if ($prd->trial_once && $prd->trial_is_expired()) {
$this->trial = false;
$this->trial_days = 0;
$this->trial_amount = 0.00;
$this->trial_tax_amount = 0.00;
$this->trial_total = 0.00;
$this->trial_tax_reversal_amount = 0.00;
}
if ($set_subtotal) {
$this->set_subtotal(MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price()));
} else {
$this->price = MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price());
}
// This will only happen with a real coupon.
if ($cpn instanceof MeprCoupon) {
$cpn->maybe_apply_trial_override($this);
// We can't do this above because we don't want to
// screw up the price before applying the trial override.
if ($set_subtotal) {
$this->set_subtotal(MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price($cpn->post_title)));
} else {
$this->price = MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price($cpn->post_title));
}
}
MeprHooks::do_action('mepr_subscription_applied_product_vars', $this);
}
/**
* Determines if the subscription can perform a certain capability.
*
* @param string $cap The capability to check.
*
* @return boolean True if the capability can be performed, false otherwise.
*/
public function can($cap)
{
$pm = $this->payment_method();
if (is_object($pm)) {
return $pm->can($cap);
}
return false;
}
/**
* Suspends the subscription.
*
* @return boolean True on success, false on failure.
*/
public function suspend()
{
if ($this->can('suspend-subscriptions')) {
try {
$pm = $this->payment_method();
return $pm->process_suspend_subscription($this->id);
} catch (Exception $e) {
return false;
}
}
return false;
}
/**
* Resumes the subscription.
*
* @return boolean True on success, false on failure.
*
* @throws Exception If the subscription could not be resumed.
*/
public function resume()
{
if ($this->can('resume-subscriptions')) {
$pm = $this->payment_method();
return $pm->process_resume_subscription($this->id);
}
return false;
}
/**
* Cancels the subscription.
*
* @return boolean True on success, false on failure.
*/
public function cancel()
{
if ($this->can('cancel-subscriptions')) {
$pm = $this->payment_method();
return $pm->process_cancel_subscription($this->id);
}
return false;
}
/**
* Checks if the credit card is expiring before the next payment.
*
* @return boolean True if expiring before the next payment, false otherwise.
*/
public function cc_expiring_before_next_payment()
{
$next_billing_at = $this->next_billing_at;
$exp_month = $this->cc_exp_month;
$exp_year = $this->cc_exp_year;
if ($next_billing_at && $exp_month && $exp_year) {
$cc_exp_ts = mktime(0, 0, 0, $exp_month, 1, $exp_year);
$next_billing_ts = strtotime($next_billing_at);
return ( $cc_exp_ts < $next_billing_ts );
}
return false;
}
/**
* Retrieves the update URL for the subscription.
*
* @return string The update URL.
*/
public function update_url()
{
$mepr_options = MeprOptions::fetch();
return $mepr_options->account_page_url("action=update&sub={$this->id}");
}
/**
* Retrieves the upgrade URL for the subscription.
*
* @return string The upgrade URL.
*/
public function upgrade_url()
{
$mepr_options = MeprOptions::fetch();
$grp = $this->group();
if ($grp && $grp->is_upgrade_path) {
return $mepr_options->account_page_url("action=upgrade&sub={$this->id}");
}
return '';
}
/**
* Calculates the catchup for the subscription.
* ONLY FOR AUTHORIZE.NET CURRENTLY but could be used for Manual Subscriptions / PayPal Reference txn's eventually
*
* @param string $type The type of catchup to calculate ('proration', 'full', 'period', or 'none').
*
* @return object The catchup calculation.
*/
public function calculate_catchup($type = 'proration')
{
/*
* $types can be any of the following
*
* none = no payment
* full = from expiration date of last txn until next billing date
* period = full amount for current period -- regardless of date
* proration = prorated amount for current period only (default)
*
*/
// If type is none, or the subscription hasn't expired -- return false.
if ($type === 'none' || !$this->is_expired()) {
return false;
}
// Calculate Next billing time.
$expired_at = strtotime($this->expires_at);
$now = time();
$time_elapsed = $now - $expired_at;
$periods_elapsed = (int)ceil($time_elapsed / MeprUtils::days($this->days_in_this_period())); // We want to round this up to INT.
$next_billing = $now;
$subscription_cost_per_day = (float)((float)$this->price / $this->days_in_this_period());
// $periods_elapsed should never be 0, but just in case:
if ($periods_elapsed <= 0) {
$periods_elapsed = 1;
}
switch ($this->period_type) {
case 'weeks':
$next_billing = $expired_at + MeprUtils::weeks($periods_elapsed * $this->period);
break;
case 'months':
$renewal_dom = gmdate('j', strtotime($this->renewal_base_date));
$next_billing = $expired_at + MeprUtils::months($periods_elapsed * $this->period, $expired_at, false, $renewal_dom);
break;
case 'years':
$next_billing = $expired_at + MeprUtils::years($periods_elapsed * $this->period, $expired_at);
break;
}
// Handle $type = period.
if ($type === 'period') {
$full_price = MeprUtils::format_float($this->price);
return (object)[
'proration' => $full_price,
'next_billing' => $next_billing,
];
}
// Handle $type = full.
if ($type === 'full') {
// Multiply $this->price * $periods_elapsed to get a nice pretty catchup number.
$sub_price = MeprUtils::format_float($this->price);
$full_proration = MeprUtils::format_float(($sub_price * $periods_elapsed));
return (object)[
'proration' => $full_proration,
'next_billing' => $next_billing,
];
}
// All other $types have been handled, so if we made it here just calculate $type = 'proration'.
$seconds_till_billing = $next_billing - $now;
$days_till_billing = (int)($seconds_till_billing / MeprUtils::days(1));
$proration = MeprUtils::format_float($subscription_cost_per_day * $days_till_billing);
return (object)compact('proration', 'next_billing');
}
/**
* Checks if the first real payment failed.
*
* @return boolean True if the first real payment failed, false otherwise.
*/
public function first_real_payment_failed()
{
if ((int) $this->txn_count > 1) {
return false;
}
if ($this->trial && $this->trial_amount > 0.00) {
// Only the trial payment exists?
if ((int) $this->txn_count === 1) {
// Check for failed.
if ($this->has_a_txn_failed()) {
return true;
}
}
} else {
// No real payments recorded yet?
if ((int) $this->txn_count === 0) {
// Check for failed.
if ($this->has_a_txn_failed()) {
return true;
}
}
}
return false;
}
/**
* Checks if the subscription has a failed transaction.
*
* @return boolean True if the subscription has a failed transaction, false otherwise.
*/
public function has_a_txn_failed()
{
global $wpdb;
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$res = $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(*) FROM {$wpdb->mepr_transactions} WHERE status = %s AND subscription_id = %d",
MeprTransaction::$failed_str,
$this->id
)
);
if ($res) {
return true;
}
return false;
}
/**
* Applies taxes to the subscription.
*
* @param float $subtotal The subtotal amount.
* @param integer $num_decimals The number of decimals for rounding.
* @param float $gross The gross amount.
*/
public function apply_tax($subtotal, $num_decimals = 2, $gross = 0.00)
{
$usr = $this->user();
$prd = $this->product();
$calculate_taxes = get_option('mepr_calculate_taxes');
// Now try to calculate tax info from the user info.
if ($prd->tax_exempt) {
list($this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class, $this->tax_reversal_amount) = [$gross, $gross, 0.00, 0.00, '', 'standard', 0.00];
if ($this->trial) {
$this->trial_total = $this->trial_amount;
$this->trial_tax_amount = 0.00;
$this->trial_tax_reversal_amount = 0.00;
}
} elseif ($calculate_taxes) {
list($this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class, $this->tax_reversal_amount) = $usr->calculate_tax($subtotal, $num_decimals, $prd->ID);
if ($this->trial) {
$this->set_trial_taxes($num_decimals);
}
} else { // If all else fails, let's blank out the tax info.
list($this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class, $this->tax_reversal_amount) = [$subtotal, $subtotal, 0.00, 0.00, '', 'standard', 0.00];
if ($this->trial) {
$this->trial_total = $this->trial_amount;
$this->trial_tax_amount = 0.00;
$this->trial_tax_reversal_amount = 0.00;
}
}
MeprHooks::do_action('mepr_subscription_apply_tax', $this);
}
/**
* Sets up the transaction total, subtotal, and tax based on a subtotal value.
* This method also checks for inclusive vs exclusive tax.
*
* @param float $subtotal The subtotal amount.
*
* @return void
*/
public function set_subtotal($subtotal)
{
$mepr_options = MeprOptions::fetch();
if ($mepr_options->attr('tax_calc_type') === 'inclusive') {
$usr = $this->user();
$subtotal = $usr->calculate_subtotal($subtotal, null, 2, $this->product());
}
$this->apply_tax($subtotal, 2, $subtotal);
}
/**
* Sets up the trial taxes for the subscription.
*
* @param integer $num_decimals The number of decimals for rounding.
* @param boolean $is_proration Whether this is for a prorated trial amount.
* Proration amounts are always subtotals (without tax),
* so they should be treated as exclusive regardless of
* the tax_calc_type setting.
*
* @return void
*/
public function set_trial_taxes($num_decimals = 2, $is_proration = false)
{
$mepr_options = MeprOptions::fetch();
$usr = $this->user();
// Proration amounts are always subtotals, so treat them as exclusive
// even when inclusive taxes are enabled. This prevents double-extraction of tax.
if ($is_proration || $mepr_options->attr('tax_calc_type') !== 'inclusive') {
$trial_taxes = $usr->calculate_tax($this->trial_amount, $num_decimals);
} else {
$subtotal = $usr->calculate_subtotal($this->trial_amount, null, 2, $this->product());
$trial_taxes = $usr->calculate_tax($subtotal, $num_decimals);
}
$this->trial_amount = $trial_taxes[0];
$this->trial_total = $trial_taxes[1];
$this->trial_tax_amount = $trial_taxes[3];
$this->trial_tax_reversal_amount = $trial_taxes[6];
}
/**
* Sets up the transaction total, subtotal, and tax based on a gross value.
* This will never check for tax inclusion because it's the gross
* it doesn't matter (since we already know the gross amount).
*
* @param float $gross The gross amount.
*
* @return void
*/
public function set_gross($gross)
{
$usr = $this->user();
$prd = $this->product();
$tax_rate = $usr->tax_rate($prd->ID);
$subtotal = $usr->calculate_subtotal($gross, $tax_rate->reversal ? 0 : null, 2, $prd);
$this->apply_tax($subtotal, 2, $gross);
}
/**
* Retrieves the tax information for the subscription.
*
* @return array The tax information.
*/
public function tax_info()
{
return [$this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class];
}
/*****
* MAGIC METHOD HANDLERS
*****/
/**
* Handles the magic method for getting the first transaction ID.
*
* @param string $mgm The magic method type.
* @param string $val The value to set.
*
* @return mixed The first transaction ID or true.
*/
protected function mgm_first_txn_id($mgm, $val = '')
{
global $wpdb;
$where = '';
switch ($mgm) {
case 'get':
// Get the first real payment.
if (isset($_REQUEST['mepr_get_real_payment'])) {
$where = $wpdb->prepare('AND t.txn_type = %s AND t.status = %s', MeprTransaction::$payment_str, MeprTransaction::$complete_str);
}
$txn_id = $wpdb->get_var($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT t.id FROM {$wpdb->mepr_transactions} AS t WHERE t.subscription_id = %d {$where} ORDER BY t.id ASC LIMIT 1",
$this->rec->id
));
// No real payments yet, so let's look for a confirmation?
if (empty($txn_id)) {
$txn_id = $wpdb->get_var($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
"SELECT t.id FROM {$wpdb->mepr_transactions} AS t WHERE t.subscription_id = %d ORDER BY t.id ASC LIMIT 1",
$this->rec->id
));
}
return empty($txn_id) ? false : $txn_id;
default:
return true;
}
}
/**
* Handles the magic method for getting the latest transaction ID.
*
* @param string $mgm The magic method type.
* @param string $val The value to set.
*
* @return mixed The latest transaction ID or true.
*/
protected function mgm_latest_txn_id($mgm, $val = '')
{
global $wpdb;
$mepr_db = new MeprDb();
switch ($mgm) {
case 'get':
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$id = $wpdb->get_var(
$wpdb->prepare(
"SELECT t.id FROM {$wpdb->mepr_transactions} AS t WHERE t.subscription_id = %d AND t.status IN (%s, %s) ORDER BY t.id DESC LIMIT 1",
$this->rec->id,
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str
)
);
return empty($id) ? false : $id;
default:
return true;
}
}
/**
* Handles the magic method for getting the expiring transaction ID.
*
* @param string $mgm The magic method type.
* @param string $val The value to set.
*
* @return mixed The expiring transaction ID or true.
*/
protected function mgm_expiring_txn_id($mgm, $val = '')
{
global $wpdb;
switch ($mgm) {
case 'get':
// phpcs:disable WordPress.DB.DirectDatabaseQuery
$id = $wpdb->get_var(
$wpdb->prepare(
"
SELECT t.id
FROM {$wpdb->mepr_transactions} AS t
WHERE t.subscription_id=%d
AND t.status IN (%s,%s)
AND (t.expires_at = %s
OR (t.expires_at <> %s
AND t.expires_at = (
SELECT MAX(t2.expires_at)
FROM {$wpdb->mepr_transactions} as t2
WHERE t2.subscription_id=%d
AND t2.status IN (%s,%s)
)
)
)
" .
// If there's a lifetime and an expires at, favor the lifetime.
'ORDER BY t.expires_at, t.status ASC LIMIT 1',
$this->rec->id,
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str,
MeprUtils::db_lifetime(),
MeprUtils::db_lifetime(),
$this->rec->id,
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str
)
);
return empty($id) ? false : $id;
default:
return true;
}
}
/**
* Handles the magic method for getting the transaction count.
*
* @param string $mgm The magic method type.
* @param string $val The value to set.
*
* @return integer|true The transaction count or true.
*/
protected function mgm_txn_count($mgm, $val = '')
{
global $wpdb;
switch ($mgm) {
case 'get':
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
return $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(*) FROM {$wpdb->mepr_transactions} AS t WHERE t.subscription_id = %d AND t.subscription_id > 0 AND t.status = %s",
$this->rec->id,
MeprTransaction::$complete_str
)
);
default:
return true;
}
}
/**
* Handles the magic method for getting the expiration date.
*
* @param string $mgm The magic method type.
* @param string $val The value to set.
*
* @return string|true The expiration date or true.
*/
protected function mgm_expires_at($mgm, $val = '')
{
global $wpdb;
switch ($mgm) {
case 'get':
if ($this->status === self::$pending_str) { // Pending subs should not be active - see self::is_active().
return false;
}
// phpcs:ignore WordPress.DB.DirectDatabaseQuery
$expires_at = $wpdb->get_var(
$wpdb->prepare(
"
SELECT t.expires_at
FROM {$wpdb->mepr_transactions} AS t
WHERE t.subscription_id=%d
AND t.status IN (%s,%s)
AND (t.expires_at = %s
OR (t.expires_at <> %s
AND t.expires_at=(
SELECT MAX(t2.expires_at)
FROM {$wpdb->mepr_transactions} as t2
WHERE t2.subscription_id=%d
AND t2.status IN (%s,%s)
)
)
)
" .
// If there's a lifetime and an expires at, favor the lifetime.
'ORDER BY t.expires_at LIMIT 1',
$this->rec->id,
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str,
MeprUtils::db_lifetime(),
MeprUtils::db_lifetime(),
$this->rec->id,
MeprTransaction::$confirmed_str,
MeprTransaction::$complete_str
)
);
if (!$this->id || is_null($expires_at) || false === $expires_at) {
// First check if latest txn was a refund, if so we're not active.
if ($this->latest_txn_refunded()) {
return false;
}
$expires_at = $this->get_expires_at();
// Convert to mysql date.
$expires_at = MeprUtils::ts_to_mysql_date($expires_at);
}
return $expires_at;
default:
return true;
}
}
/**
* Handles the magic method for getting the next billing date.
*
* @param string $mgm The magic method type.
* @param string $value The value to set.
*
* @return string|true The next billing date or true.
*/
protected function mgm_next_billing_at($mgm, $value = '')
{
global $wpdb;
$mepr_db = new MeprDb();
switch ($mgm) {
case 'get':
if (
$this->status === MeprSubscription::$active_str and
!empty($this->expires_at) and
$this->expires_at !== MeprUtils::db_lifetime() and
( !$this->limit_cycles or
( $this->limit_cycles and
$this->txn_count < $this->limit_cycles_num ) )
) {
return $this->expires_at;
} else {
return false;
}
default:
return true;
}
}
/**
* Handles the magic method for getting or setting the ID.
*
* @param string $mgm The magic method type.
* @param string $value The value to set.
*
* @return integer|true The ID or true.
*/
protected function mgm_ID($mgm, $value = '')
{
global $wpdb;
$mepr_db = new MeprDb();
switch ($mgm) {
case 'get':
return $this->rec->id;
case 'set':
$this->rec->id = $value;
break;
default:
return true;
}
}
/**
* This is the effective start date for the subscription. Under normal circumstances
* this is just the created_at date but when a subscription is paused and resumed
* the startdate will reset to whenever the latest resume_at date occurs.
*
* @param string $mgm The magic method.
* @param mixed $value The value to set.
*
* @return string The prepared column statement.
*/
protected function mgm_renewal_base_date($mgm, $value = '')
{
global $wpdb;
$mepr_db = new MeprDb();
switch ($mgm) {
case 'get':
$pm = $this->payment_method();
if ($pm instanceof MeprBaseGateway) {
return $pm->get_renewal_base_date($this);
}
return $this->created_at;
case 'set':
return false;
default:
return true;
}
}
/**
* Specifies the attributes for upgrading subscriptions to the new table.
*
* @return array The attributes for upgrading subscriptions.
*/
public static function upgrade_attrs()
{
return [
'subscr_id' => "CONCAT('mp-sub-',UUID_SHORT())",
'gateway' => 'manual',
'user_id' => 0,
'product_id' => 0,
'coupon_id' => 0,
'price' => 0.00,
'total' => '{{price}}',
'period' => 1,
'period_type' => 'months',
'limit_cycles' => false,
'limit_cycles_num' => 0,
'limit_cycles_action' => null,
'limit_cycles_expires_after' => '1',
'limit_cycles_expires_type' => 'days',
'prorated_trial' => false,
'trial' => false,
'trial_days' => 0,
'trial_amount' => 0.00,
'status' => MeprSubscription::$pending_str,
'created_at' => null,
'tax_rate' => 0.00,
'tax_amount' => 0.00,
'tax_desc' => '',
'tax_class' => 'standard',
'cc_last4' => null,
'cc_exp_month' => null,
'cc_exp_year' => null,
];
}
/**
* Prepares a column statement for SQL queries.
*
* @param string $slug The column slug.
* @param mixed $default The default value.
*
* @return string The prepared column statement.
*/
private static function col_stmt($slug, $default)
{
global $wpdb;
if (is_null($default)) {
// A left join will naturally produce a NULL value if not found...no IFNULL needed.
$col = "pm_{$slug}.meta_value";
} elseif ($slug === 'subscr_id') {
$col = "IFNULL(pm_{$slug}.meta_value,{$default})";
} elseif (is_integer($default)) {
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$col = $wpdb->prepare("IFNULL(pm_{$slug}.meta_value,%d)", $default);
} elseif (is_float($default)) {
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$col = $wpdb->prepare("IFNULL(pm_{$slug}.meta_value,%f)", $default);
} else {
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$col = $wpdb->prepare("IFNULL(pm_{$slug}.meta_value,%s)", $default);
}
return $col;
}
/**
* This is specifically used to migrate subscriptions to its new table.
*
* @param integer|null $subscription_id The subscription ID.
* @param boolean $exclude_already_upgraded Whether to exclude already upgraded subscriptions.
* @param string $limit The limit for the query.
*
* @return array The upgrade query.
*/
public static function upgrade_query($subscription_id = null, $exclude_already_upgraded = false, $limit = '')
{
global $wpdb;
$mepr_options = MeprOptions::fetch();
$mepr_db = MeprDb::fetch();
// $cols = array('id' => 'DISTINCT pst.ID');
$cols = ['id' => 'pst.ID'];
// Add postmeta columns
// Must be the same order and name as the table itself.
$pms = self::upgrade_attrs();
foreach ($pms as $slug => $default) {
if (is_string($default) && preg_match('/^\{\{([^\{\}]*)\}\}$/', $default, $m)) {
$cols[$slug] = "IFNULL(pm_{$slug}.meta_value," . self::col_stmt($m[1], $pms[$m[1]]) . ')';
} else {
$cols[$slug] = self::col_stmt($slug, $default);
}
}
// The database can handle these
// $cols['tax_compound'] = 0;
// $cols['tax_shipping'] = 1;.
$args = [$wpdb->prepare('pst.post_type = %s', 'mepr-subscriptions')];
// Don't upgrade any that are already upgraded.
if ($exclude_already_upgraded) {
$args[] = "pst.ID NOT IN (SELECT id FROM {$mepr_db->subscriptions})";
}
if (!is_null($subscription_id)) {
$args[] = $wpdb->prepare('pst.ID = %d', $subscription_id);
}
$joins = [];
// $ignore_cols = array('tax_compound','tax_shipping');
// Add postmeta joins
foreach ($pms as $slug => $default) {
$joins[] = self::join_pm($slug, 'LEFT JOIN');
}
if ($limit === false) {
$paged = '';
$perpage = 0;
} else {
$paged = 1;
$perpage = $limit;
}
$order_by = 'ID';
$order = 'DESC';
return MeprDb::list_table(
$cols,
"{$wpdb->posts} AS pst",
$joins,
$args,
$order_by,
$order,
$paged,
'',
'any',
$perpage,
false,
true
);
}
/**
* Upgrades the subscription table.
*
* @param integer|null $subscription_id The subscription ID.
* @param boolean $exclude_already_upgraded Whether to exclude already upgraded subscriptions.
* @param string $limit The limit for the query.
*
* @return void
* @throws MeprDbMigrationException If the database migration fails.
*/
public static function upgrade_table($subscription_id = null, $exclude_already_upgraded = false, $limit = '')
{
global $wpdb;
$subq = self::upgrade_query($subscription_id, $exclude_already_upgraded, $limit);
$attrs = 'id,' . implode(',', array_keys(self::upgrade_attrs()));
$query = "INSERT IGNORE INTO {$wpdb->mepr_subscriptions} ({$attrs}) {$subq['query']}";
$res = $wpdb->query($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
if ($res === false) { // $res will never return a WP_Error
throw new MeprDbMigrationException(sprintf(
// Translators: %1$s: last error message, %2$s: query.
esc_html__('MemberPress database migration failed: %1$s %2$s', 'memberpress'),
esc_html($wpdb->last_error),
esc_html($query)
));
}
}
/**
* Joins the postmeta table to the subscription table.
* STILL USING THIS TO MIGRATE THE DATABASE
*
* @param string $slug The column slug.
* @param string $join The join type.
* @param string $post The post table.
*
* @return string The join statement.
*/
private static function join_pm($slug, $join = 'LEFT JOIN', $post = 'pst')
{
global $wpdb;
$vals = self::legacy_str_vals();
$class = new ReflectionClass('MeprSubscription');
$val = $vals[$slug];
return $wpdb->prepare(
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"{$join} {$wpdb->postmeta} AS pm_{$slug} ON pm_{$slug}.post_id = {$post}.ID AND pm_{$slug}.meta_key = %s",
$val
);
}
/**
* Returns the legacy string values for the subscription table.
* STILL USING THIS TO MIGRATE THE DATABASE
*
* @return array The legacy string values.
*/
private static function legacy_str_vals()
{
return [
'subscr_id' => '_mepr_subscr_id',
'user_id' => '_mepr_subscr_user_id',
'gateway' => '_mepr_subscr_gateway',
'product_id' => '_mepr_subscr_product_id',
'coupon_id' => '_mepr_subscr_coupon_id',
'price' => '_mepr_subscr_price',
'period' => '_mepr_subscr_period',
'period_type' => '_mepr_subscr_period_type',
'limit_cycles' => '_mepr_subscr_limit_cycles',
'limit_cycles_num' => '_mepr_subscr_limit_cycles_num',
'limit_cycles_action' => '_mepr_subscr_limit_cycles_action',
'limit_cycles_expires_after' => '_mepr_subscr_limit_cycles_expires_after',
'limit_cycles_expires_type' => '_mepr_subscr_limit_cycles_expires_type',
'prorated_trial' => '_mepr_subscr_prorated_trial',
'trial' => '_mepr_subscr_trial',
'trial_days' => '_mepr_subscr_trial_days',
'trial_amount' => '_mepr_subscr_trial_amount',
'status' => '_mepr_subscr_status',
'created_at' => '_mepr_subscr_created_at',
'cc_last4' => '_mepr_subscr_cc_last4',
'cc_exp_month' => '_mepr_subscr_cc_month_exp',
'cc_exp_year' => '_mepr_subscr_cc_year_exp',
'total' => '_mepr_subscr_total',
'tax_rate' => '_mepr_subscr_tax_rate',
'tax_amount' => '_mepr_subscr_tax_amount',
'tax_desc' => '_mepr_subscr_tax_desc',
'tax_class' => '_mepr_subscr_tax_class',
'cpt' => 'mepr-subscriptions',
];
}
}