File: /var/www/html/wp-content/plugins/memberpress/app/models/MeprReminder.php
<?php
if (!defined('ABSPATH')) {
die('You are not allowed to call this page directly.');
}
class MeprReminder extends MeprCptModel
{
/**
* Meta key for the trigger length.
*
* @var string
*/
public static $trigger_length_str = 'mepr_trigger_length';
/**
* Meta key for the trigger interval.
*
* @var string
*/
public static $trigger_interval_str = 'mepr_trigger_interval';
/**
* Meta key for the trigger timing.
*
* @var string
*/
public static $trigger_timing_str = 'mepr_trigger_timing';
/**
* Meta key for the trigger event.
*
* @var string
*/
public static $trigger_event_str = 'mepr_trigger_event';
/**
* Meta key for the product filter flag.
*
* @var string
*/
public static $filter_products_str = '_mepr_reminder_filter_products_str';
/**
* Meta key for the products list.
*
* @var string
*/
public static $products_str = '_mepr_reminder_products';
/**
* Meta key for the emails list.
*
* @var string
*/
public static $emails_str = '_mepr_emails';
/**
* Nonce string for reminders form.
*
* @var string
*/
public static $nonce_str = 'mepr_reminders_nonce';
/**
* Option name for the last cleanup run timestamp.
*
* @var string
*/
public static $last_run_str = 'mepr_reminders_db_cleanup_last_run';
/**
* Custom post type for reminders.
*
* @var string
*/
public static $cpt = 'mp-reminder';
/**
* Available trigger intervals.
*
* @var array
*/
public $trigger_intervals;
/**
* Available trigger timings (before/after).
*
* @var array
*/
public $trigger_timings;
/**
* Available trigger events.
*
* @var array
*/
public $trigger_events;
/**
* Compiled list of event actions.
*
* @var array
*/
public $event_actions;
/**
* Constructor for the MeprReminder class.
*
* @param mixed $obj The object to initialize the reminder with.
*/
public function __construct($obj = null)
{
$this->load_cpt(
$obj,
self::$cpt,
[
'trigger_length' => 1,
'trigger_interval' => 'days',
'trigger_timing' => 'before',
'trigger_event' => 'sub-expires',
'filter_products' => false, // Send only for specific memberships?
'products' => [], // Empty array means ALL memberships.
'emails' => [],
]
);
$this->trigger_intervals = ['hours','days','weeks','months','years'];
$this->trigger_timings = ['before','after'];
$this->trigger_events = [
'sub-expires',
'sub-renews',
'cc-expires',
'member-signup',
'signup-abandoned',
'sub-trial-ends',
];
$this->event_actions = [];
foreach ($this->trigger_events as $e) {
foreach ($this->trigger_timings as $t) {
$this->event_actions[] = sprintf(
'mepr_event_%s_%s_reminder',
$t,
str_replace('-', '_', $e)
);
}
}
}
/**
* Validate the reminder's properties.
*
* @return void
*/
public function validate()
{
$this->validate_is_numeric($this->trigger_length, 0, null, 'trigger_length');
$this->validate_is_in_array($this->trigger_interval, $this->trigger_intervals, 'trigger_interval');
$this->validate_is_in_array($this->trigger_timing, $this->trigger_timings, 'trigger_timings');
$this->validate_is_in_array($this->trigger_event, $this->trigger_events, 'trigger_events');
$this->validate_is_bool($this->filter_products, 'filter_products');
$this->validate_is_array($this->products, 'products');
$this->validate_is_array($this->emails, 'emails');
}
/**
* Placeholder for event handling logic.
*
* @return void
*/
public function events()
{
}
/**
* Get the name of the trigger event.
*
* @return string
*/
public function trigger_event_name()
{
switch ($this->trigger_event) {
case 'sub-expires':
return __('Subscription Expires', 'memberpress');
case 'sub-renews':
return __('Subscription Renews', 'memberpress');
case 'cc-expires':
return __('Credit Card Expires', 'memberpress');
case 'member-signup':
return __('Member Signs Up', 'memberpress');
case 'signup-abandoned':
return __('Sign Up Abandoned', 'memberpress');
case 'sub-trial-ends':
return __('Subscription Trial Ending', 'memberpress');
default:
return $this->trigger_event;
}
}
/**
* Get the trigger interval as a string.
*
* @return string
*/
public function get_trigger_interval_str()
{
return MeprUtils::period_type_name($this->trigger_interval, $this->trigger_length);
}
/**
* Store the reminder's metadata.
*
* @return void
*/
public function store_meta()
{
global $wpdb;
$skip_name_override = false;
$id = $this->ID;
if (isset($_POST['post_title']) && !empty($_POST['post_title'])) {
$skip_name_override = true;
}
if (!$skip_name_override) {
$title = sprintf(
// Translators: %1$d: trigger length, %2$s: trigger interval, %3$s: trigger timing, %4$s: trigger event name.
__('%1$d %2$s %3$s %4$s', 'memberpress'),
$this->trigger_length,
strtolower($this->get_trigger_interval_str()),
$this->trigger_timing,
$this->trigger_event_name()
);
// Direct SQL so we don't issue any actions / filters
// in WP itself that could get us in an infinite loop.
$wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
"UPDATE {$wpdb->posts} SET post_title=%s WHERE ID=%d",
$title,
$id
));
}
update_post_meta($id, self::$trigger_length_str, $this->trigger_length);
update_post_meta($id, self::$trigger_interval_str, $this->trigger_interval);
update_post_meta($id, self::$trigger_timing_str, $this->trigger_timing);
update_post_meta($id, self::$trigger_event_str, $this->trigger_event);
update_post_meta($id, self::$filter_products_str, $this->filter_products);
update_post_meta($id, self::$products_str, $this->products);
update_post_meta($id, self::$emails_str, $this->emails);
}
/**
* Convert the trigger interval to database format.
*
* @return string Uppercase singular form of the interval.
*/
private function db_trigger_interval()
{
return strtoupper(substr($this->trigger_interval, 0, -1));
}
/**
* Get the formatted list of products for the reminder.
*
* @return array
*/
public function get_formatted_products()
{
$formatted_array = [];
if ($this->filter_products && isset($this->products) && is_array($this->products) && !empty($this->products)) {
foreach ($this->products as $product_id) {
$product = get_post($product_id);
if (isset($product->post_title) && !empty($product->post_title)) {
$formatted_array[] = $product->post_title;
}
}
} else { // If empty, then All products.
$formatted_array[] = __('All Memberships', 'memberpress');
}
return $formatted_array;
}
/**
* Get the query string for filtering products.
*
* @param string $join_name The name of the join table.
* @return string
*/
public function get_query_products($join_name)
{
if ($this->filter_products && is_array($this->products) && !empty($this->products)) {
$product_ids = implode(',', $this->products);
return "AND {$join_name} IN({$product_ids})";
}
return '';
}
/**
* Get the next expiring transaction for subscription expiration reminders.
*
* @return object|null
*/
public function get_next_expiring_txn()
{
global $wpdb;
$unit = $this->db_trigger_interval();
$op = ( $this->trigger_timing === 'before' ? 'DATE_SUB' : 'DATE_ADD' );
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('tr.product_id');
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query = $wpdb->prepare(
// Get all info about expiring transactions.
"SELECT tr.* FROM {$wpdb->mepr_transactions} AS tr\n" .
// Lifetimes don't expire.
"WHERE tr.expires_at <> %s\n" .
// Make sure only real users are grabbed.
"AND tr.user_id > 0\n" .
// Make sure that only transactions that are
// complete or (confirmed and in a free trial) get picked up.
"AND ( tr.status = %s
OR ( tr.status = %s
AND ( SELECT sub.trial
FROM {$wpdb->mepr_subscriptions} AS sub
WHERE sub.id = tr.subscription_id AND sub.trial_amount = 0.00 ) = 1 ) )\n" .
// Determine if expiration is accurate based on the subscription
// If sub_id is 0 then treat as expiration.
"AND ( tr.subscription_id = 0 OR
( SELECT sub.status
FROM {$wpdb->mepr_subscriptions} AS sub
WHERE sub.id = tr.subscription_id ) IN (%s, %s) )\n" .
// Ensure that we're in the 2 day window after the expiration / trigger.
"AND {$op}( tr.expires_at, INTERVAL {$this->trigger_length} {$unit} ) <= %s
AND DATE_ADD(
{$op}( tr.expires_at, INTERVAL {$this->trigger_length} {$unit} ),
INTERVAL 2 DAY ) >= %s\n",
MeprUtils::db_lifetime(),
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str,
MeprSubscription::$cancelled_str,
MeprSubscription::$suspended_str,
MeprUtils::db_now(),
MeprUtils::db_now(),
);
// Make sure that if our timing is beforehand
// then we don't send after the expiration.
$query .= ($this->trigger_timing === 'before' ? $wpdb->prepare("AND tr.expires_at >= %s\n", MeprUtils::db_now()) : '');
$query .= $wpdb->prepare(
// Let's make sure the reminder event hasn't already fired ...
// This will ensure that we don't send a second reminder.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=tr.id
AND ev.evt_id_type='transactions'
AND ev.event=%s
AND ev.args=%d
LIMIT 1 ) IS NULL\n" .
// Let's make sure we're not sending expire reminders
// when your subscription is being upgraded or downgraded.
"AND ( SELECT ev2.id
FROM {$wpdb->mepr_events} AS ev2
WHERE ev2.evt_id=tr.id
AND ev2.evt_id_type='transactions'
AND ev2.event='subscription-changed'
LIMIT 1 ) IS NULL\n" .
// Let's make sure this is the latest transaction for the subscription
// in case there is a more recent transaction that expires later.
"AND ( tr.subscription_id = 0
OR tr.id = ( SELECT tr2.id
FROM {$wpdb->mepr_transactions} AS tr2
WHERE tr2.subscription_id = tr.subscription_id
ORDER BY tr2.expires_at DESC
LIMIT 1 ) )\n" .
"{$and_products} " .
// We're just getting one of these at a time ... we need the oldest one first.
"ORDER BY tr.expires_at LIMIT 1\n",
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->ID
);
// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$res = $wpdb->get_row($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $res;
}
/**
* Get the next renewed transaction for after subscription renews reminders.
*
* @return object|null
*/
public function get_renewed_txn()
{
global $wpdb;
$unit = $this->db_trigger_interval();
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('tr.product_id');
$query = $wpdb->prepare(
// Get all info about renewing transactions.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT tr.* FROM {$wpdb->mepr_transactions} AS tr\n" .
// Lifetimes don't renew.
"WHERE tr.expires_at <> %s\n" .
// Make sure it's recurring.
"AND tr.subscription_id > 0\n" .
// Make sure only real users are grabbed.
"AND tr.user_id > 0\n" .
// Make sure that only transactions that are
// complete get picked up.
"AND tr.status = %s \n" .
// Ensure that we're defined timeframe
// Giving it a 2 days buffer to the past.
"AND DATE_ADD( tr.created_at, INTERVAL {$this->trigger_length} {$unit} ) <= %s AND DATE_ADD(
DATE_ADD( tr.created_at, INTERVAL {$this->trigger_length} {$unit} ), INTERVAL 2 DAY) >= %s\n" .
"AND tr.created_at <= %s\n " .
// Let's make sure the reminder event hasn't already fired ...
// This will ensure that we don't send a second reminder.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=tr.id
AND ev.evt_id_type='transactions'
AND ev.event=%s
AND ev.args=%d
LIMIT 1 ) IS NULL\n" .
"{$and_products} " .
// We're just getting one of these at a time ... we need the oldest one first.
"ORDER BY tr.created_at
LIMIT 1\n",
MeprUtils::db_lifetime(),
MeprTransaction::$complete_str,
MeprUtils::db_now(),
MeprUtils::db_now(),
MeprUtils::db_now(),
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->ID
); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$res = $wpdb->get_row($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $res;
}
/**
* Get the next renewing transaction for before subscription renews reminders.
*
* @return object|null
*/
public function get_next_renewing_txn()
{
global $wpdb;
$unit = $this->db_trigger_interval();
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('tr.product_id');
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query = $wpdb->prepare(
// Get all info about renewing transactions.
"SELECT tr.* FROM {$wpdb->mepr_transactions} AS tr\n" .
// Lifetimes don't renew.
"WHERE tr.expires_at <> %s\n" .
// Make sure it's recurring.
"AND tr.subscription_id <> 0\n" .
// Make sure only real users are grabbed.
"AND tr.user_id > 0\n" .
// Make sure that only transactions that are
// complete or (confirmed and in a free trial) get picked up.
"AND ( tr.status = %s
OR ( tr.status = %s
AND ( SELECT sub.trial
FROM {$wpdb->mepr_subscriptions} AS sub
WHERE sub.id = tr.subscription_id AND sub.trial_amount = 0.00 ) = 1 ) )\n" .
// Determine if renewal is accurate based on the subscription.
"AND ( SELECT sub.status
FROM {$wpdb->mepr_subscriptions} AS sub
WHERE sub.id = tr.subscription_id ) = %s\n" .
// Ensure that we're in the 2 day window after the renewal / trigger.
"AND DATE_SUB( tr.expires_at, INTERVAL {$this->trigger_length} {$unit} ) <= %s
AND DATE_ADD(
DATE_SUB( tr.expires_at, INTERVAL {$this->trigger_length} {$unit} ),
INTERVAL 2 DAY
) >= %s\n" .
// Make sure that if our timing is beforehand
// then we don't send after the renewal.
"AND tr.expires_at >= %s\n" .
// Let's make sure the reminder event hasn't already fired ...
// This will ensure that we don't send a second reminder.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=tr.id
AND ev.evt_id_type='transactions'
AND ev.event=%s
AND ev.args=%d
LIMIT 1 ) IS NULL\n" .
// Let's make sure we're not sending renewal reminders
// when your subscription is being upgraded or downgraded.
"AND ( SELECT ev2.id
FROM {$wpdb->mepr_events} AS ev2
WHERE ev2.evt_id=tr.id
AND ev2.evt_id_type='transactions'
AND ev2.event='subscription-changed'
LIMIT 1 ) IS NULL\n" .
// Let's make sure this is the latest transaction for the subscription
// in case there is a more recent transaction that expires later.
"AND tr.id = ( SELECT tr2.id
FROM {$wpdb->mepr_transactions} AS tr2
WHERE tr2.subscription_id = tr.subscription_id
ORDER BY tr2.expires_at DESC
LIMIT 1 )\n" .
"{$and_products} " .
// We're just getting one of these at a time ... we need the oldest one first.
"ORDER BY tr.expires_at
LIMIT 1\n",
MeprUtils::db_lifetime(),
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str,
MeprSubscription::$active_str,
MeprUtils::db_now(),
MeprUtils::db_now(),
MeprUtils::db_now(),
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->ID
); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$res = $wpdb->get_row($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
return $res;
}
/**
* Get the next member signup for reminders.
*
* @return integer|false
*/
public function get_next_member_signup()
{
global $wpdb;
$unit = $this->db_trigger_interval();
// Sorry, don't want to incur any temporal paradoxes here.
if ($this->trigger_timing === 'before') {
return false;
}
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('txn.product_id');
// Find transactions where
// status = complete or confirmed
// no other complete & unexpired txns for this user.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query = $wpdb->prepare(
// Just select the actual transaction id.
"SELECT txn.id FROM {$wpdb->mepr_transactions} AS txn " .
// Make sure that only transactions that are
// complete or (confirmed and in a free trial) get picked up.
"WHERE ( txn.status = %s
OR ( txn.status = %s
AND ( SELECT sub.trial
FROM {$wpdb->mepr_subscriptions} AS sub
WHERE sub.id = txn.subscription_id AND sub.trial_amount = 0.00 ) = 1 ) ) " .
// We don't send on fallback txn.
' AND txn.txn_type <> %s ' .
// Ensure we grab transactions that are after the trigger period.
"AND DATE_ADD(
txn.created_at,
INTERVAL {$this->trigger_length} {$unit}
) <= %s " .
// Give it a 2 day buffer period so we don't send for really old transactions.
"AND DATE_ADD(
DATE_ADD(
txn.created_at,
INTERVAL {$this->trigger_length} {$unit}
),
INTERVAL 2 DAY
) >= %s " .
// Make sure this is the *first* complete transaction.
"AND ( SELECT txn2.id
FROM {$wpdb->mepr_transactions} AS txn2
WHERE txn2.user_id = txn.user_id
AND ( txn2.status = %s
OR ( txn2.status = %s
AND ( SELECT sub.trial
FROM {$wpdb->mepr_subscriptions} AS sub
WHERE sub.id = txn2.subscription_id AND sub.trial_amount = 0.00 ) = 1 ) )
" . $this->get_query_products('txn2.product_id') // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
. ' AND txn2.created_at < txn.created_at
LIMIT 1
) IS NULL ' .
// Don't send this twice yo ... for this user.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=txn.id
AND ev.evt_id_type='transactions'
AND ev.event=%s
AND ev.args=%s
LIMIT 1
) IS NULL " .
"{$and_products} " .
// Select the oldest transaction.
'ORDER BY txn.created_at ASC LIMIT 1',
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str,
MeprTransaction::$fallback_str,
MeprUtils::db_now(),
MeprUtils::db_now(),
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str,
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->ID
); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return $wpdb->get_var($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
}
/**
* Get the next abandoned signup for reminders.
*
* @return integer|false
*/
public function get_next_abandoned_signup()
{
global $wpdb;
$unit = $this->db_trigger_interval();
// Sorry, don't want to incur any temporal paradoxes here.
if ($this->trigger_timing === 'before') {
return false;
}
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('txn.product_id');
// Find transactions where
// status = pending
// no other complete & unexpired membership for this user.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$query = $wpdb->prepare(
// Just grab the transaction id.
"SELECT txn.id FROM {$wpdb->mepr_transactions} AS txn " .
// Ensure that we only select 'pending' transactions.
'WHERE txn.status=%s ' .
// Make sure the alotted time has passed
// before allowing to be selected.
"AND DATE_ADD(
txn.created_at,
INTERVAL {$this->trigger_length} {$unit}
) <= %s " .
// Add in the 2 day buffer period.
"AND DATE_ADD(
DATE_ADD(
txn.created_at,
INTERVAL {$this->trigger_length} {$unit}
),
INTERVAL 2 DAY
) >= %s " .
// Ensure that there's no completed or confirmed transaction that
// was created after the pending one ... if they came back and
// completed their transaction then it's not abandoned ... hahaha.
"AND ( SELECT txn2.id
FROM {$wpdb->mepr_transactions} AS txn2
WHERE txn2.user_id = txn.user_id
AND txn2.product_id = txn.product_id
AND txn2.status IN (%s,%s)
" . $this->get_query_products('txn2.product_id') // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
. ' AND txn2.created_at > txn.created_at
LIMIT 1
) IS NULL ' .
// Ensure that this reminder is only sent for the primary transaction for multi-item purchases.
"AND (
txn.order_id = 0 OR
txn.id = ( SELECT ord.primary_transaction_id FROM {$wpdb->mepr_orders} AS ord WHERE ord.id = txn.order_id )
)" .
// Don't want to send this reminder twice so make sure there's no
// reminder that has already been sent for this bro.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=txn.id
AND ev.evt_id_type='transactions'
AND ev.event=%s
AND ev.args=%s
LIMIT 1
) IS NULL " .
"{$and_products} " .
// Get the *oldest* applicable transaction.
'ORDER BY txn.created_at ASC LIMIT 1',
MeprTransaction::$pending_str,
MeprUtils::db_now(),
MeprUtils::db_now(),
MeprTransaction::$complete_str,
MeprTransaction::$confirmed_str,
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->ID
); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return $wpdb->get_var($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
}
/**
* Get the next expired credit card for reminders.
*
* @return integer|false
*/
public function get_next_expired_cc()
{
global $wpdb;
$unit = $this->db_trigger_interval();
$op = ( $this->trigger_timing === 'before' ? 'DATE_SUB' : 'DATE_ADD' );
// We want to get expiring subscriptions.
$not = ( ( $this->trigger_event === 'sub-expires' ) ? ' ' : ' NOT ' );
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('sub.product_id');
// Expiring Transactions.
$query =
// Just grab the sub.id for any subscription with an expiring transaction.
"SELECT sub.id FROM {$wpdb->mepr_subscriptions} AS sub " .
// Make sure we only send out reminders for folks with ACTIVE subscriptions.
'WHERE sub.status = %s ' .
// Subtract or add if the reminder is before or after
// The concat is just to piece together the date
// The add_date is because we actually want the first
// day of the month *after* the expiration month
// LPAD is just there to ensure the month is a
// 2 digit zero padded number.
"AND {$op}(
DATE_ADD(
CONCAT(
sub.cc_exp_year, '-',
LPAD(sub.cc_exp_month, 2, '0'),
'-01 00:00:00'
),
INTERVAL 1 MONTH
),
INTERVAL {$this->trigger_length} {$unit}
) <= %s " .
// Basically the same thing as we're doing here but let's give it
// an entire month period to send these reminders ... seeing as
// there will probably be fewer of these emails and they're pretty
// dang critical so that people's subscriptions don't lapse.
"AND DATE_ADD(
{$op}(
DATE_ADD(
CONCAT(
sub.cc_exp_year, '-',
LPAD(sub.cc_exp_month, 2, '0'),
'-01 00:00:00'
),
INTERVAL 1 MONTH
),
INTERVAL {$this->trigger_length} {$unit}
),
INTERVAL 1 MONTH
) >= %s " .
// Check that we haven't already sent a reminder for this
// subscription *and* specific expiration date.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=sub.id
AND ev.evt_id_type='subscriptions'
AND ev.event=%s
AND ev.args=CONCAT(%d, '|', sub.cc_exp_month, '|', sub.cc_exp_year)
LIMIT 1
) IS NULL " .
"{$and_products} " .
// Just ignore subs with no cc_exp date.
'AND sub.cc_exp_month IS NOT NULL
AND sub.cc_exp_month <> ""
AND sub.cc_exp_year IS NOT NULL
AND sub.cc_exp_year <> ""
';
// Get the *oldest* valid cc expiration first.
'ORDER BY CAST(sub.cc_exp_year AS UNSIGNED) ASC,
CAST(sub.cc_exp_month AS UNSIGNED) ASC
LIMIT 1';
return $wpdb->get_var($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
$query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
MeprSubscription::$active_str,
MeprUtils::db_now(),
MeprUtils::db_now(),
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->ID
));
}
/**
* Get the next trial ending subscription for reminders.
*
* @return integer|false
*/
public function get_next_trial_ends_subs()
{
global $wpdb;
if ($this->trigger_length < 0) {
return false; // Bail out.
}
$unit = $this->db_trigger_interval();
$op = ( $this->trigger_timing === 'before' ? 'DATE_SUB' : 'DATE_ADD' );
// Make sure we're only grabbing from valid product ID's for this reminder yo
// If $this->products is empty, then we should send for all product_id's.
$and_products = $this->get_query_products('sub.product_id');
// Make sure we only send out reminders for folks with the following status:.
$subs_statuses = [MeprSubscription::$active_str, MeprSubscription::$cancelled_str, MeprSubscription::$suspended_str];
$in_sub_status = implode("','", $subs_statuses);
// Expiring Trial Subscriptions.
$query =
// Just grab the trial_days sub.id for any subscription with an expiring transaction.
"SELECT sub.id FROM {$wpdb->mepr_subscriptions} AS sub " .
// Calculate trial end date with trial days for comparison.
"WHERE sub.status IN ('{$in_sub_status}') " .
// Ensure that we're in the 2 day window.
"AND DATE_ADD(
{$op}( DATE_ADD( sub.created_at, INTERVAL trial_days DAY), INTERVAL {$this->trigger_length} {$unit} ),
INTERVAL 2 DAY
) >= %s" .
"AND {$op}( DATE_ADD( sub.created_at, INTERVAL trial_days DAY), INTERVAL {$this->trigger_length} {$unit} ) <= %s " .
// Trials not expired yet.
'AND DATE_ADD( sub.created_at, INTERVAL trial_days DAY) >= %s ' .
// Check that we haven't already sent a reminder for this
// subscription *and* specific expiration date.
"AND ( SELECT ev.id
FROM {$wpdb->mepr_events} AS ev
WHERE ev.evt_id=sub.id
AND ev.evt_id_type='subscriptions'
AND ev.event=%s
AND ev.args=CONCAT(%d, '|', sub.trial_days)
LIMIT 1
) IS NULL " .
"{$and_products} " .
// Just trials subs.
'AND sub.trial = 1 AND sub.trial_days > 0 AND sub.prorated_trial = 0 ' .
// Get the *oldest* valid trial subs first.
'ORDER BY sub.created_at ASC
LIMIT 1';
$query = $wpdb->prepare(
$query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
MeprUtils::db_now(),
MeprUtils::db_now(),
MeprUtils::db_now(),
"{$this->trigger_timing}-{$this->trigger_event}-reminder",
$this->rec->ID
);
return $wpdb->get_var($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
}
// Used for drips and expirations
// public function get_next_drip( $rule, $timing, $length, $interval, $event='sub-expires' ) {
// global $wpdb;
//
// $mepr_db = new MeprDb();
//
// if($reminder->trigger_interval=='days')
// $unit = 'DAY';
// else if($reminder->trigger_interval=='weeks')
// $unit = 'WEEK';
// else if($reminder->trigger_interval=='months')
// $unit = 'MONTH';
// else if($reminder->trigger_interval=='years')
// $unit = 'YEAR';
//
// $op = ($reminder->trigger_timing='before')?'DATE_SUB':'DATE_ADD';
//
// We want to get expiring subscriptions
// $not = $expiring ? ' ' : ' NOT ';
// $query = "SELECT (SELECT user) as user, " .
// "(SELECT products) as products, " .
// "(Calculate date) as date " .
// "FROM {$wpdb->posts} AS p " .
// "WHERE p.post_status='publish' " .
// "AND p.post_type=%s " .
// "AND p.ID=%d";
// Rule:
// drip_enabled
// drip_sequential
// drip_amount
// drip_unit
// drip_after
// drip_after_fixed
// User registers
// Fixed date
// Transaction for specific membership
// Expiring Transactions
// $res = $wpdb->get_row($query);
//
// return $res;
// }.
}