public function status_updated($request_id, $status)
{
global $wpdb;
$order_id = get_post_meta($request_id, '_order_id', true);
$triggers = array('warranty_status');
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'warranty_status' AND status = 1");
foreach ($emails as $email) {
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'user_id' => 0, 'order_id' => $order_id, 'is_cart' => 0);
FUE::insert_email_order($insert);
// Tell FUE that an email order has been created
// to stop it from sending generic emails
if (!defined('FUE_ORDER_CREATED')) {
define('FUE_ORDER_CREATED', true);
}
}
FUE_Woocommerce::create_email_orders($triggers, $order_id);
}
public function after_points_increased($user_id, $points, $event_type, $data = null, $order_id = 0)
{
global $wpdb;
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `interval_type` IN ('points_earned', 'points_greater_than')");
foreach ($emails as $email) {
$interval = (int) $email->interval_num;
if ($email->interval_type == 'points_greater_than') {
$meta = maybe_unserialize($email->meta);
if ($points < $meta['points_greater_than']) {
continue;
}
}
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'user_id' => $user_id, 'order_id' => 0, 'is_cart' => 0);
$email_order_id = FUE::insert_email_order($insert);
$data = array('user_id' => $user_id, 'points' => $points, 'event_type' => $event_type);
update_option('fue_email_order_' . $email_order_id, $data);
}
}
public function set_expiration_reminder($user_id, $subs_key)
{
global $wpdb;
$parts = explode('_', $subs_key);
$order_id = $parts[0];
$order = new WC_Order($order_id);
if (WC_Subscriptions_Order::order_contains_subscription($order)) {
// look for renewal emails
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'subs_before_expire' AND status = 1");
if (count($emails) > 0) {
$subs = WC_Subscriptions_Manager::get_subscription($subs_key);
$expiry = WC_Subscriptions_Manager::get_subscription_expiration_date($subs_key, $user_id, 'timestamp');
if (0 == $expiry) {
return;
}
foreach ($emails as $email) {
// add this email to the queue
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = $expiry - $add;
$insert = array('user_id' => $user_id, 'send_on' => $send_on, 'email_id' => $email->id, 'product_id' => 0, 'order_id' => $order_id);
if ($subs_key) {
$insert['meta']['subs_key'] = $subs_key;
}
FUE::insert_email_order($insert);
}
}
}
}
public function set_reminders($order_id)
{
global $woocommerce, $wpdb;
// load reminder emails
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `interval_type` IN ('before_tribe_event_starts', 'after_tribe_event_ends') AND status = 1 ORDER BY `priority` ASC");
$tickets = array();
if (empty($emails)) {
return;
}
$has_tickets = get_post_meta($order_id, '_tribe_has_tickets', true);
$order = new WC_Order($order_id);
$items = $order->get_items();
foreach ($items as $item) {
$ticket_id = isset($item['id']) ? $item['id'] : $item['product_id'];
// if $item is a ticket, load the event where the ticket is attached to
$event_id = get_post_meta($ticket_id, '_tribe_wooticket_for_event', true);
if (!$event_id) {
continue;
}
if (!in_array($ticket_id, $tickets)) {
$tickets[] = $ticket_id;
}
}
$now = current_time('timestamp');
foreach ($emails as $email) {
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
foreach ($tickets as $ticket_id) {
$event_id = get_post_meta($ticket_id, '_tribe_wooticket_for_event', true);
if ($email->interval_type == 'before_tribe_event_starts') {
$start = get_post_meta($event_id, '_EventStartDate', true);
if (empty($start)) {
continue;
}
$start = strtotime($start);
// check if a limit is in place
if (isset($email->meta['tribe_limit'], $email->meta['tribe_limit_days']) && !empty($tribe_limit_days)) {
$days = ($start - $now) / 86400;
if ($days <= $email->meta['tribe_limit_days']) {
// $days is within limit - skip
continue;
}
}
$send_on = $start - $add;
// if send_on is in the past, do not queue it
if ($now > $send_on) {
continue;
}
} else {
$end = get_post_meta($event_id, '_EventEndDate', true);
if (empty($end)) {
continue;
}
$end = strtotime($end);
$send_on = $end + $add;
// if send_on is in the past, do not queue it
if ($now > $send_on) {
continue;
}
}
$insert = array('user_id' => $order->user_id, 'order_id' => $order_id, 'product_id' => $ticket_id, 'email_id' => $email->id, 'send_on' => $send_on);
FUE::insert_email_order($insert);
}
}
}
public static function create_email_orders($triggers, $order_id = '')
{
global $woocommerce, $wpdb;
$order = $order_id > 0 ? new WC_Order($order_id) : false;
$items = $order->get_items();
$item_ids = array();
$order_created = false;
$num_queued = 0;
$fue_customer_id = 0;
$all_categories = array();
if ($order) {
if ($order->user_id > 0) {
$fue_customer_id = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$wpdb->prefix}followup_customers WHERE user_id = %d", $order->user_id));
} else {
$fue_customer_id = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$wpdb->prefix}followup_customers WHERE email_address = %s", $order->billing_email));
}
}
$trigger = '';
foreach ($triggers as $t) {
$trigger .= "'" . esc_sql($t) . "',";
}
$trigger = rtrim($trigger, ',');
if (empty($trigger)) {
$trigger = "''";
}
// find a product match
$emails = array();
$always_prods = array();
$always_cats = array();
foreach ($items as $item) {
$prod_id = isset($item['id']) ? $item['id'] : $item['product_id'];
if (!in_array($prod_id, $item_ids)) {
$item_ids[] = $prod_id;
}
// variation support
$parent_id = -1;
if (isset($item['variation_id']) && $item['variation_id'] > 0) {
$parent_id = $prod_id;
$prod_id = $item['variation_id'];
}
$email_results = $wpdb->get_results($wpdb->prepare("\n SELECT DISTINCT `id`, `product_id`, `priority`, `meta`\n FROM {$wpdb->prefix}followup_emails\n WHERE `interval_type` IN ({$trigger})\n AND ( `product_id` = %d OR `product_id` = %d )\n AND `email_type` <> 'generic'\n AND `always_send` = 0\n AND status = 1\n ORDER BY `priority` ASC\n ", $prod_id, $parent_id));
if ($email_results) {
foreach ($email_results as $email) {
$meta = maybe_unserialize($email->meta);
if ($prod_id == $email->product_id) {
// exact product ID match
$emails[] = array('id' => $email->id, 'item' => $prod_id, 'priority' => $email->priority);
} elseif ($parent_id > 0 && $parent_id == $email->product_id && isset($meta['include_variations']) && $meta['include_variations'] == 'yes') {
$emails[] = array('id' => $email->id, 'item' => $parent_id, 'priority' => $email->priority);
}
}
}
// always_send product matches
$results = $wpdb->get_results($wpdb->prepare("\n SELECT DISTINCT `id`, `product_id`, `meta` , `email_type`\n FROM {$wpdb->prefix}followup_emails\n WHERE `interval_type` IN ({$trigger})\n AND ( `product_id` = %d OR `product_id` = %d OR `email_type` = 'reminder' )\n AND `always_send` = 1\n AND status = 1\n ", $prod_id, $parent_id));
foreach ($results as $row) {
$meta = maybe_unserialize($row->meta);
if ($prod_id == $row->product_id || $row->email_type == 'reminder') {
// exact product ID match
$always_prods[] = array('id' => $row->id, 'item' => $prod_id);
} elseif ($parent_id > 0 && $parent_id == $row->product_id && isset($meta['include_variations']) && $meta['include_variations'] == 'yes') {
$always_prods[] = array('id' => $row->id, 'item' => $parent_id);
}
}
// always_send category matches
$cat_ids = wp_get_object_terms($prod_id, 'product_cat', array('fields' => 'ids'));
$ids = implode(',', $cat_ids);
if (empty($ids)) {
$ids = "''";
}
$all_categories = array_merge($all_categories, $cat_ids);
$results = $wpdb->get_results("\n SELECT DISTINCT `id`\n FROM {$wpdb->prefix}followup_emails\n WHERE `interval_type` IN ({$trigger})\n AND `always_send` = 1\n AND status = 1\n AND ( `category_id` <> '' AND `category_id` IN (" . $ids . ") )\n ");
foreach ($results as $row) {
$always_cats[] = array('id' => $row->id, 'item' => $prod_id);
}
}
if (!empty($always_prods)) {
foreach ($always_prods as $row) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d AND status = 1", $row['id']));
$interval = (int) $email->interval_num;
$interval_duration = $email->interval_duration;
$reminder_count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}followup_email_orders WHERE `order_id` = {$order_id} AND `email_id` = {$row['id']}");
if ($reminder_count == 0 && $email->email_type == 'reminder') {
// get the item's quantity
$qty = 0;
$num_products = false;
foreach ($items as $item) {
$item_id = isset($item['product_id']) ? $item['product_id'] : $item['id'];
if ($item_id == $row['item']) {
$qty = $item['qty'];
if (isset($item['item_meta']) && !empty($item['item_meta'])) {
$wc2 = function_exists('get_product');
foreach ($item['item_meta'] as $meta) {
if ($wc2) {
if ($meta['meta_name'] == 'Filters/Case') {
$num_products = $meta['meta_value'];
}
} else {
if (isset($meta['Filters/Case'])) {
$num_products = $meta['Filters/Case'][0];
}
}
}
}
break;
}
}
// look for a lifespan product variable
$lifespan = get_post_meta($row['item'], 'filter_lifespan', true);
if ($lifespan && $lifespan > 0) {
$interval = (int) $lifespan;
$interval_duration = 'months';
if ($num_products !== false && $num_products > 0) {
$qty = $qty * $num_products;
}
}
if ($qty == 1) {
// only send the first email
$add = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
} elseif ($qty == 2) {
// only send the first and last emails
$add = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$last = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add + $last;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
} else {
// send all emails
$add = FUE::get_time_to_add($interval, $interval_duration);
$last = 0;
for ($x = 1; $x <= $qty; $x++) {
$send_on = current_time('timestamp') + $add + $last;
$last += $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
}
}
continue;
}
$skip = false;
do_action('fue_create_order_always_send', $email, $order_id, $row);
if (false == $skip) {
$insert = array('send_on' => FUE::get_email_send_timestamp($email), 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
}
if (!empty($always_cats)) {
foreach ($always_cats as $row) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d AND status = 1", $row['id']));
$interval = (int) $email->interval_num;
$skip = false;
do_action('fue_create_order_always_send', $email, $order_id, $row);
if (false == $skip) {
$insert = array('send_on' => FUE::get_email_send_timestamp($email), 'email_id' => $email->id, 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
}
if (!empty($emails)) {
// find the one with the highest priority
$top = false;
$highest = 1000;
foreach ($emails as $email) {
if ($email['priority'] < $highest) {
$highest = $email['priority'];
$top = $email;
}
}
if ($top !== false) {
$insert = array('send_on' => FUE::get_email_send_timestamp($top['id']), 'email_id' => $top['id'], 'product_id' => $top['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
$order_created = true;
// look for other emails with the same product id
foreach ($emails as $prod_email) {
if ($prod_email['id'] == $top['id']) {
continue;
}
if ($prod_email['item'] == $top['item']) {
$insert = array('send_on' => FUE::get_email_send_timestamp($prod_email['id']), 'email_id' => $prod_email['id'], 'product_id' => $prod_email['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
} else {
// if schedule is within 60 minutes, add to queue
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d AND status = 1", $prod_email['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
continue;
} else {
$add = FUE::get_time_to_add($interval, $email->interval_duration);
if ($add > 3600) {
continue;
}
// less than 60 minutes, add to queue
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $prod_email['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
}
}
// find a category match
if (!$order_created) {
$emails = array();
foreach ($items as $item) {
$prod_id = isset($item['id']) ? $item['id'] : $item['product_id'];
$cat_ids = wp_get_object_terms($prod_id, 'product_cat', array('fields' => 'ids'));
$ids = implode(',', $cat_ids);
if (empty($ids)) {
$ids = "''";
}
$email = $wpdb->get_results("SELECT DISTINCT `id`, `priority` FROM {$wpdb->prefix}followup_emails WHERE `interval_type` IN ({$trigger}) AND `product_id` = 0 AND `category_id` > 0 AND `category_id` IN (" . $ids . ") AND `email_type` <> 'generic' AND `always_send` = 0 AND status = 1 ORDER BY `priority` ASC");
foreach ($email as $e) {
$emails[] = array('id' => $e->id, 'item' => $prod_id, 'priority' => $e->priority);
}
}
if (!empty($emails)) {
// find the one with the highest priority
$top = false;
$highest = 1000;
foreach ($emails as $email) {
if ($email['priority'] < $highest) {
$highest = $email['priority'];
$top = $email;
}
}
if ($top !== false) {
$insert = array('send_on' => FUE::get_email_send_timestamp($top['id']), 'email_id' => $top['id'], 'product_id' => $top['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
$order_created = true;
// look for other emails with the same category id
foreach ($emails as $cat_email) {
if ($cat_email['id'] == $top['id']) {
continue;
}
if ($cat_email['item'] == $top['item']) {
$insert = array('send_on' => FUE::get_email_send_timestamp($cat_email['id']), 'email_id' => $cat_email['id'], 'product_id' => $cat_email['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
} else {
// if schedule is within 60 minutes, add to queue
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d AND status = 1", $cat_email['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
continue;
} else {
$add = FUE::get_time_to_add($interval, $email->interval_duration);
if ($add > 3600) {
continue;
}
// less than 60 minutes, add to queue
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $cat_email['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
}
}
}
// allow plugins to stop FUE from sending generic emails using this hook
// or by defining a 'FUE_ORDER_CREATED' constant
$order_created = apply_filters('fue_order_created', $order_created, $triggers, $order_id);
if (!$order_created && !defined('FUE_ORDER_CREATED')) {
// find a generic mailer
$emails = $wpdb->get_results("SELECT DISTINCT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'generic' AND `interval_type` IN ({$trigger}) AND status = 1 ORDER BY `priority` ASC");
$all_categories = array_unique($all_categories);
foreach ($emails as $email) {
// excluded categories
$meta = unserialize($email->meta);
$excludes = isset($meta['excluded_categories']) ? $meta['excluded_categories'] : array();
if (count($excludes) > 0) {
foreach ($all_categories as $cat_id) {
if (in_array($cat_id, $excludes)) {
continue 2;
}
}
}
$insert = array('send_on' => FUE::get_email_send_timestamp($email->id), 'email_id' => $email->id, 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
if ($order !== false) {
if ($fue_customer_id) {
// first purchase
/*$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE interval_type = 'first_purchase' ORDER BY priority ASC");
foreach ( $emails as $email ) {
}*/
foreach ($item_ids as $item_id) {
// product match
$emails = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE interval_type = 'first_purchase' AND email_type = 'normal' AND product_id = %d AND status = 1", $item_id));
if ($emails) {
foreach ($emails as $email) {
$count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}followup_order_items oi, {$wpdb->prefix}followup_customer_orders co WHERE co.followup_customer_id = %d AND co.order_id = oi.order_id AND oi.product_id = %d", $fue_customer_id, $item_id));
if ($count == 1) {
// first time purchasing this item
FUE::queue_email(array('order_id' => $order_id), $email);
$num_queued++;
}
}
}
// end product match
// category match
$cat_ids = wp_get_post_terms($item_id, 'product_cat', array('fields' => 'ids'));
if ($cat_ids) {
foreach ($cat_ids as $cat_id) {
$emails = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE interval_type = 'first_purchase' AND email_type = 'normal' AND category_id = %d AND status = 1", $cat_id));
if ($emails) {
foreach ($emails as $email) {
$count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->prefix}followup_order_categories oc, {$wpdb->prefix}followup_customer_orders co WHERE co.followup_customer_id = %d AND co.order_id = oc.order_id AND oc.category_id = %d", $fue_customer_id, $cat_id));
if ($count == 1) {
// first time purchasing this item
FUE::queue_email(array('order_id' => $order_id), $email);
$num_queued++;
}
}
}
}
}
// end category match
}
// storewide first purchase
$count = $wpdb->get_var($wpdb->prepare("SELECT total_orders FROM {$wpdb->prefix}followup_customers WHERE id = %d", $fue_customer_id));
if ($count == 1) {
// first time ordering
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE interval_type = 'first_purchase' AND email_type = 'generic' AND status = 1");
if ($emails) {
foreach ($emails as $email) {
// first time purchasing this item
FUE::queue_email(array('order_id' => $order_id), $email);
$num_queued++;
}
}
}
}
// look for customer emails
$emails = $wpdb->get_results("SELECT DISTINCT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'customer' AND `interval_type` IN ({$trigger}) AND status = 1 ORDER BY `priority` ASC");
foreach ($emails as $email) {
$interval = (int) $email->interval_num;
$meta = maybe_unserialize($email->meta);
// check for order total triggers first
if ($email->interval_type == 'order_total_above') {
if (!isset($meta['order_total_above'])) {
continue;
}
if ($order->order_total < $meta['order_total_above']) {
continue;
}
} elseif ($email->interval_type == 'order_total_below') {
if (!isset($meta['order_total_below'])) {
continue;
}
if ($order->order_total > $meta['order_total_below']) {
continue;
}
} elseif ($email->interval_type == 'total_orders') {
$mode = $meta['total_orders_mode'];
$requirement = $meta['total_orders'];
if (isset($meta['one_time']) && $meta['one_time'] == 'yes') {
// get the correct email address
if ($order->user_id > 0) {
$user = new WP_User($order->user_id);
$user_email = $user->user_email;
} else {
$user_email = $order->billing_email;
}
$search = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE email_id = %d\n AND user_email = %s", $email->id, $user_email));
if ($search > 0) {
continue;
}
}
// get user's total number of orders
if ($order->user_id > 0) {
$num_orders = $wpdb->get_var($wpdb->prepare("SELECT total_orders FROM {$wpdb->prefix}followup_customers WHERE user_id = %d", $order->user_id));
} else {
$num_orders = $wpdb->get_var($wpdb->prepare("SELECT total_orders FROM {$wpdb->prefix}followup_customers WHERE email_address = %s", $order->billing_email));
}
if ($mode == 'less than' && $num_orders >= $requirement) {
continue;
} elseif ($mode == 'equal to' && $num_orders != $requirement) {
continue;
} elseif ($mode == 'greater than' && $num_orders <= $requirement) {
continue;
}
} elseif ($email->interval_type == 'total_purchases') {
$mode = $meta['total_purchases_mode'];
$requirement = $meta['total_purchases'];
if (isset($meta['one_time']) && $meta['one_time'] == 'yes') {
// get the correct email address
if ($order->user_id > 0) {
$user = new WP_User($order->user_id);
$user_email = $user->user_email;
} else {
$user_email = $order->billing_email;
}
$search = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE email_id = %d\n AND user_email = %s", $email->id, $user_email));
if ($search > 0) {
continue;
}
}
// get user's total amount of purchases
if ($order->user_id > 0) {
$purchases = $wpdb->get_var($wpdb->prepare("SELECT total_purchase_price FROM {$wpdb->prefix}followup_customers WHERE user_id = %d", $order->user_id));
} else {
$purchases = $wpdb->get_var($wpdb->prepare("SELECT total_purchase_price FROM {$wpdb->prefix}followup_customers WHERE email_address = %s", $order->billing_email));
}
if ($mode == 'less than' && $purchases >= $requirement) {
continue;
} elseif ($mode == 'equal to' && $purchases != $requirement) {
continue;
} elseif ($mode == 'greater than' && $purchases <= $requirement) {
continue;
}
} elseif ($email->interval_type == 'purchase_above_one') {
// look for duplicate emails
if ($order->user_id > 0) {
$wp_user = new WP_User($order->user_id);
$user_email = $wp_user->user_email;
} else {
$user_email = $order->billing_email;
}
$num = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE email_id = %d\n AND user_email = %s", $email->id, $user_email));
if ($num > 0) {
continue;
}
}
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
// special trigger: last purchased
if ($order && ($order->status == 'processing' || $order->status == 'completed')) {
$recipient = $order->user_id > 0 ? $order->user_id : $order->billing_email;
// if there are any "last purchased" emails, automatically add this order to the queue
$emails = $wpdb->get_results("SELECT DISTINCT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'customer' AND `interval_type` = 'after_last_purchase' AND status = 1 ORDER BY `priority` ASC");
foreach ($emails as $email) {
// look for unsent emails in the queue with the same email ID
$queued = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_email_orders WHERE is_sent = 0 AND email_id = {$email->id}");
// loop through the queue and delete entries with identical customers
foreach ($queued as $queue) {
if ($queue->user_id > 0 && $order->user_id > 0 && $queue->user_id == $order->user_id) {
$wpdb->query("DELETE FROM {$wpdb->prefix}followup_email_orders WHERE id = {$queue->id}");
} elseif ($order->user_id == 0) {
// try to match the email address
$email = get_post_meta($queue->order_id, '_billing_email', true);
if ($email == $order->billing_email) {
$wpdb->query("DELETE FROM {$wpdb->prefix}followup_email_orders WHERE id = {$queue->id}");
}
}
}
if ($order->user_id > 0) {
$last_order_date = $wpdb->get_var($wpdb->prepare("SELECT p.post_date FROM {$wpdb->posts} p, {$wpdb->prefix}followup_customer_orders co WHERE co.followup_customer_id = %d AND co.order_id = p.ID AND p.post_status = 'publish' ORDER BY p.ID DESC LIMIT 1", $order->user_id));
} else {
$last_order_date = $wpdb->get_var($wpdb->prepare("SELECT p.post_date FROM {$wpdb->posts} p, {$wpdb->prefix}followup_customer_orders co WHERE co.followup_customer_id = %d AND co.order_id = p.ID AND p.post_status = 'publish' ORDER BY p.ID DESC LIMIT 1", $order->billing_email));
}
// add this email to the queue
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => 0, 'order_id' => $order_id);
FUE::insert_email_order($insert);
$num_queued++;
}
}
return $num_queued;
}
function cart_updated()
{
global $wpdb;
// only if user is logged in
$user = wp_get_current_user();
if (0 == $user->ID) {
return;
}
$cart = get_user_meta($user->ID, '_woocommerce_persistent_cart', true);
//var_dump($cart); exit;
if (!$cart || empty($cart)) {
// cart has been emptied. we need to remove existing email orders for this user
do_action('fue_cart_emptied');
$wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->prefix}followup_email_orders WHERE `is_cart` = 1 AND `is_sent` = 0 AND user_id = %d", $user->ID));
update_user_meta($user->ID, '_wcfue_cart_emails', array());
return;
}
$cart_session = get_user_meta($user->ID, '_wcfue_cart_emails', true);
if (!$cart_session) {
$cart_session = array();
}
//var_dump($cart_session); exit;
$emails = array();
$always_prods = array();
$always_cats = array();
$email_created = false;
//var_dump($cart); exit;
foreach ($cart['cart'] as $item_key => $item) {
//var_dump($item); exit;
$email = $wpdb->get_row("SELECT `id`, `priority` FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'cart' AND `product_id` = '" . $item['product_id'] . "' ORDER BY `priority` ASC");
if ($email) {
$check = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$wpdb->prefix}followup_email_orders` WHERE `is_sent` = 0 AND `order_id` = 0 AND `product_id` = %d AND `email_id` = %d AND `user_id` = %d AND `is_cart` = 1", $item['product_id'], $email->id, $user->ID));
if ($check == 0 && !in_array($email->id . '_' . $item['product_id'], $cart_session)) {
$cart_session[] = $email->id . '_' . $item['product_id'];
$emails[] = array('id' => $email->id, 'item' => $item['product_id'], 'priority' => $email->priority);
}
}
// always_send product matches
$results = $wpdb->get_results("SELECT `id` FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'cart' AND `product_id` = '" . $item['product_id'] . "' AND `always_send` = 1");
foreach ($results as $row) {
$check = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$wpdb->prefix}followup_email_orders` WHERE `is_sent` = 0 AND `order_id` = 0 AND `product_id` = %d AND `email_id` = %d AND `user_id` = %d AND `is_cart` = 1", $item['product_id'], $row->id, $user->ID));
if ($check == 0 && !in_array($row->id . '_' . $item['product_id'], $cart_session)) {
$cart_session[] = $row->id . '_' . $item['product_id'];
$always_prods[] = array('id' => $row->id, 'item' => $item['product_id']);
}
}
// always_send category matches
$cat_ids = wp_get_object_terms($item['product_id'], 'product_cat', array('fields' => 'ids'));
$ids = implode(',', $cat_ids);
if (empty($ids)) {
$ids = "''";
}
$results = $wpdb->get_results("SELECT `id` FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'cart' AND `always_send` = 1 AND `category_id` IN (" . $ids . ")");
foreach ($results as $row) {
$check = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$wpdb->prefix}followup_email_orders` WHERE `is_sent` = 0 AND `order_id` = 0 AND `product_id` = %d AND `email_id` = %d AND `user_id` = %d AND `is_cart` = 1", $item['product_id'], $row->id, $user->ID));
if ($check == 0 && !in_array($row->id . '_' . $item['product_id'], $cart_session)) {
$cart_session[] = $row->id . '_' . $item['product_id'];
$always_cats[] = array('id' => $row->id, 'item' => $item['product_id']);
}
}
}
if (!empty($always_prods)) {
foreach ($always_prods as $row) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $row['id']));
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = time() + $add;
$insert = array('product_id' => $row['item'], 'email_id' => $email->id, 'send_on' => $send_on, 'is_cart' => 1, 'user_id' => $user->ID);
FUE::insert_email_order($insert);
}
}
if (!empty($always_cats)) {
foreach ($always_cats as $row) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $row['id']));
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = time() + $add;
$insert = array('product_id' => $row['item'], 'email_id' => $email->id, 'send_on' => $send_on, 'is_cart' => 1, 'user_id' => $user->ID);
FUE::insert_email_order($insert);
}
}
// product matches
if (!empty($emails)) {
// find the one with the highest priority
$top = false;
$highest = 1000;
foreach ($emails as $email) {
if ($email['priority'] < $highest) {
$highest = $email['priority'];
$top = $email;
}
}
if ($top !== false) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $top['id']));
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = time() + $add;
$insert = array('product_id' => $top['item'], 'email_id' => $email->id, 'send_on' => $send_on, 'is_cart' => 1, 'user_id' => $user->ID);
FUE::insert_email_order($insert);
$email_created = true;
}
}
// find a category match
if (!$email_created) {
$emails = array();
foreach ($cart['cart'] as $item_key => $item) {
$cat_ids = wp_get_object_terms($item['product_id'], 'product_cat', array('fields' => 'ids'));
$ids = implode(',', $cat_ids);
if (empty($ids)) {
$ids = "''";
}
$email = $wpdb->get_results("SELECT `id`, `priority` FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'cart' AND `category_id` IN (" . $ids . ") ORDER BY `priority` ASC");
foreach ($email as $e) {
$check = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$wpdb->prefix}followup_email_orders` WHERE `is_sent` = 0 AND `order_id` = 0 AND `product_id` = %d AND `email_id` = %d AND `user_id` = %d AND `is_cart` = 1", $item['product_id'], $e->id, $user->ID));
if ($check == 0 && !in_array($e->id . '_' . $item['product_id'], $cart_session)) {
$cart_session[] = $e->id . '_' . $item['product_id'];
$emails[] = array('id' => $e->id, 'item' => $item['product_id'], 'priority' => $e->category_priority);
}
}
}
if (!empty($emails)) {
// find the one with the highest priority
$top = false;
$highest = 1000;
foreach ($emails as $email) {
if ($email['priority'] < $highest) {
$highest = $email['priority'];
$top = $email;
}
}
if ($top !== false) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $top['id']));
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = time() + $add;
$insert = array('product_id' => $top['item'], 'email_id' => $email->id, 'send_on' => $send_on, 'is_cart' => 1, 'user_id' => $user->ID);
FUE::insert_email_order($insert);
$email_created = true;
}
}
}
if (!$email_created) {
// find a generic mailer
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'generic' AND `interval_type` = 'cart' ORDER BY `priority` ASC");
foreach ($emails as $email) {
$check = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$wpdb->prefix}followup_email_orders` WHERE `is_sent` = 0 AND `order_id` = 0 AND `product_id` = 0 AND `email_id` = %d AND `user_id` = %d AND `is_cart` = 1", $email->id, $user->ID));
if ($check > 0 || in_array($email->id . '_0', $cart_session)) {
continue;
}
$cart_session[] = $email->id . '_0';
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = time() + $add;
$insert = array('email_id' => $email->id, 'send_on' => $send_on, 'is_cart' => 1, 'user_id' => $user->ID);
FUE::insert_email_order($insert);
}
}
update_user_meta($user->ID, '_wcfue_cart_emails', $cart_session);
}
/**
* Create email orders from a shop order
* @static
* @param int $order_id
* @param array $triggers
*/
public static function create_order_from_triggers($order_id = 0, $triggers = array())
{
global $woocommerce, $wpdb;
$order = $order_id > 0 ? new WC_Order($order_id) : false;
$items = $order->get_items();
$order_created = false;
$num_queued = 0;
$trigger = '';
foreach ($triggers as $t) {
$trigger .= "'" . esc_sql($t) . "',";
}
$trigger = rtrim($trigger, ',');
if (empty($trigger)) {
$trigger = "''";
}
// find a product match
$emails = array();
$always_prods = array();
$always_cats = array();
foreach ($items as $item) {
$prod_id = isset($item['id']) ? $item['id'] : $item['product_id'];
// variation support
if (isset($item['variation_id']) && $item['variation_id'] > 0) {
$prod_id = $item['variation_id'];
}
$email_results = $wpdb->get_results("\n SELECT DISTINCT `id`, `priority`\n FROM {$wpdb->prefix}followup_emails\n WHERE `interval_type` IN ({$trigger})\n AND `product_id` = '" . $prod_id . "'\n AND `email_type` <> 'generic'\n AND `always_send` = 0\n ORDER BY `priority` ASC\n ");
if ($email_results) {
foreach ($email_results as $email) {
$emails[] = array('id' => $email->id, 'item' => $prod_id, 'priority' => $email->priority);
}
}
// always_send product matches
$results = $wpdb->get_results("\n SELECT DISTINCT `id`\n FROM {$wpdb->prefix}followup_emails\n WHERE `interval_type` IN ({$trigger})\n AND `product_id` = '" . $prod_id . "'\n AND `always_send` = 1\n ");
foreach ($results as $row) {
$always_prods[] = array('id' => $row->id, 'item' => $prod_id);
}
// always_send category matches
$cat_ids = wp_get_object_terms($prod_id, 'product_cat', array('fields' => 'ids'));
$ids = implode(',', $cat_ids);
if (empty($ids)) {
$ids = "''";
}
$results = $wpdb->get_results("\n SELECT DISTINCT `id`\n FROM {$wpdb->prefix}followup_emails\n WHERE `interval_type` IN ({$trigger})\n AND `always_send` = 1\n AND `category_id` IN (" . $ids . ")\n ");
foreach ($results as $row) {
$always_cats[] = array('id' => $row->id, 'item' => $prod_id);
}
}
if (!empty($always_prods)) {
foreach ($always_prods as $row) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $row['id']));
$interval = (int) $email->interval_num;
$interval_duration = $email->interval_duration;
$reminder_count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}followup_email_orders WHERE `order_id` = {$order_id} AND `email_id` = {$row['id']}");
if ($reminder_count == 0 && $email->email_type == 'reminder') {
// get the item's quantity
$qty = 0;
foreach ($items as $item) {
$item_id = isset($item['product_id']) ? $item['product_id'] : $item['id'];
if ($item_id == $row['item']) {
$qty = $item['qty'];
break;
}
}
if ($qty == 1) {
// only send the first email
$add = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
} elseif ($qty == 2) {
// only send the first and last emails
$add = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$last = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add + $last;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
} else {
// send all emails
$add = FUE::get_time_to_add($interval, $interval_duration);
$last = 0;
for ($x = 1; $x <= $qty; $x++) {
$send_on = current_time('timestamp') + $add + $last;
$last += $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
}
}
continue;
}
$skip = false;
do_action('fue_create_order_always_send', $email, $order_id, $row);
if (false == $skip) {
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
}
if (!empty($always_cats)) {
foreach ($always_cats as $row) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $row['id']));
$interval = (int) $email->interval_num;
$interval_duration = $email->interval_duration;
$reminder_count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}followup_email_orders WHERE `order_id` = {$order_id} AND `email_id` = {$row['id']}");
if ($reminder_count == 0 && $email->email_type == 'reminder') {
// get the item's quantity
$qty = 0;
foreach ($items as $item) {
$item_id = isset($item['product_id']) ? $item['product_id'] : $item['id'];
if ($item_id == $row['item']) {
$qty = $item['qty'];
break;
}
}
echo 'QTY: ' . $qty . '<br/>';
if ($qty == 1) {
// only send the first email
$add = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
} elseif ($qty == 2) {
// only send the first and last emails
$add = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
$last = FUE::get_time_to_add($interval, $interval_duration);
$send_on = current_time('timestamp') + $add + $last;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
} else {
// send all emails
$add = FUE::get_time_to_add($interval, $interval_duration);
$last = 0;
echo 'qty: ' . $qty . '<br/>';
for ($x = 1; $x <= $qty; $x++) {
echo 'rev x: ' . $x . '<br/>';
$send_on = current_time('timestamp') + $add + $last;
$last += $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
FUE::insert_email_order($insert);
}
exit;
}
continue;
}
do_action('fue_create_order_always_send', $email, $order_id, $row);
if (false == $skip) {
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $row['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
}
if (!empty($emails)) {
// find the one with the highest priority
$top = false;
$highest = 1000;
foreach ($emails as $email) {
if ($email['priority'] < $highest) {
$highest = $email['priority'];
$top = $email;
}
}
if ($top !== false) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $top['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $top['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
$order_created = true;
// look for other emails with the same product id
foreach ($emails as $prod_email) {
if ($prod_email['id'] == $top['id']) {
continue;
}
if ($prod_email['item'] == $top['item']) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $prod_email['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $prod_email['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
} else {
// if schedule is within 60 minutes, add to queue
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $prod_email['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
continue;
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
if ($add > 3600) {
continue;
}
// less than 60 minutes, add to queue
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $prod_email['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
}
}
// find a category match
if (!$order_created) {
$emails = array();
foreach ($items as $item) {
$prod_id = isset($item['id']) ? $item['id'] : $item['product_id'];
$cat_ids = wp_get_object_terms($prod_id, 'product_cat', array('fields' => 'ids'));
$ids = implode(',', $cat_ids);
if (empty($ids)) {
$ids = "''";
}
$email = $wpdb->get_results("SELECT DISTINCT `id`, `priority` FROM {$wpdb->prefix}followup_emails WHERE `interval_type` IN ({$trigger}) AND `product_id` = 0 AND `category_id` > 0 AND `category_id` IN (" . $ids . ") AND `email_type` <> 'generic' AND `always_send` = 0 ORDER BY `priority` ASC");
foreach ($email as $e) {
$emails[] = array('id' => $e->id, 'item' => $prod_id, 'priority' => $e->priority);
}
}
if (!empty($emails)) {
// find the one with the highest priority
$top = false;
$highest = 1000;
foreach ($emails as $email) {
if ($email['priority'] < $highest) {
$highest = $email['priority'];
$top = $email;
}
}
if ($top !== false) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $top['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $top['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
$order_created = true;
// look for other emails with the same category id
foreach ($emails as $cat_email) {
if ($cat_email['id'] == $top['id']) {
continue;
}
if ($cat_email['item'] == $top['item']) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $cat_email['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $cat_email['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
} else {
// if schedule is within 60 minutes, add to queue
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `id` = %d", $cat_email['id']));
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
continue;
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
if ($add > 3600) {
continue;
}
// less than 60 minutes, add to queue
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => $cat_email['item'], 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
}
}
}
if (!$order_created) {
// find a generic mailer
$emails = $wpdb->get_results("SELECT DISTINCT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'generic' AND `interval_type` IN ({$trigger}) ORDER BY `priority` ASC");
foreach ($emails as $email) {
$interval = (int) $email->interval_num;
if ($email->interval_type == 'date') {
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => 0, 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
if ($order !== false) {
// look for customer emails
$emails = $wpdb->get_results("SELECT DISTINCT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'customer' AND `interval_type` IN ({$trigger}) ORDER BY `priority` ASC");
foreach ($emails as $email) {
$interval = (int) $email->interval_num;
$meta = maybe_unserialize($email->meta);
// check for order total triggers first
if ($email->interval_type == 'order_total_above') {
if (!isset($meta['order_total_above'])) {
continue;
}
if ($order->order_total < $meta['order_total_above']) {
continue;
}
} elseif ($email->interval_type == 'order_total_below') {
if (!isset($meta['order_total_below'])) {
continue;
}
if ($order->order_total > $meta['order_total_below']) {
continue;
}
} elseif ($email->interval_type == 'total_orders') {
$mode = $meta['total_orders_mode'];
$requirement = $meta['total_orders'];
if (isset($meta['one_time']) && $meta['one_time'] == 'yes') {
// get the correct email address
if ($order->user_id > 0) {
$user = new WP_User($order->user_id);
$user_email = $user->user_email;
} else {
$user_email = $order->billing_email;
}
$search = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE email_id = %d\n AND user_email = %s", $email->id, $user_email));
if ($search > 0) {
continue;
}
}
// get user's total number of orders
if ($order->user_id > 0) {
$num_orders = $wpdb->get_var($wpdb->prepare("SELECT total_orders FROM {$wpdb->prefix}followup_customers WHERE user_id = %d", $order->user_id));
} else {
$num_orders = $wpdb->get_var($wpdb->prepare("SELECT total_orders FROM {$wpdb->prefix}followup_customers WHERE email_address = %s", $order->billing_email));
}
if ($mode == 'less than' && $num_orders >= $requirement) {
continue;
} elseif ($mode == 'equal to' && $num_orders != $requirement) {
continue;
} elseif ($mode == 'greater than' && $num_orders <= $requirement) {
continue;
}
} elseif ($email->interval_type == 'total_purchases') {
$mode = $meta['total_purchases_mode'];
$requirement = $meta['total_purchases'];
if (isset($meta['one_time']) && $meta['one_time'] == 'yes') {
// get the correct email address
if ($order->user_id > 0) {
$user = new WP_User($order->user_id);
$user_email = $user->user_email;
} else {
$user_email = $order->billing_email;
}
$search = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE email_id = %d\n AND user_email = %s", $email->id, $user_email));
if ($search > 0) {
continue;
}
}
// get user's total amount of purchases
if ($order->user_id > 0) {
$purchases = $wpdb->get_var($wpdb->prepare("SELECT total_purchase_price FROM {$wpdb->prefix}followup_customers WHERE user_id = %d", $order->user_id));
} else {
$purchases = $wpdb->get_var($wpdb->prepare("SELECT total_purchase_price FROM {$wpdb->prefix}followup_customers WHERE email_address = %s", $order->billing_email));
}
if ($mode == 'less than' && $purchases >= $requirement) {
continue;
} elseif ($mode == 'equal to' && $purchases != $requirement) {
continue;
} elseif ($mode == 'greater than' && $purchases <= $requirement) {
continue;
}
} elseif ($email->interval_type == 'purchase_above_one') {
// look for duplicate emails
if ($order->user_id > 0) {
$wp_user = new WP_User($order->user_id);
$user_email = $wp_user->user_email;
} else {
$user_email = $order->billing_email;
}
$num = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE email_id = %d\n AND user_email = %s", $email->id, $user_email));
if ($num > 0) {
continue;
}
}
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
// special trigger: last purchased
if ($order && ($order->status == 'processing' || $order->status == 'completed')) {
$recipient = $order->user_id > 0 ? $order->user_id : $order->billing_email;
// if there are any "last purchased" emails, automatically add this order to the queue
$emails = $wpdb->get_results("SELECT DISTINCT * FROM {$wpdb->prefix}followup_emails WHERE `email_type` = 'customer' AND `interval_type` = 'after_last_purchase' ORDER BY `priority` ASC");
foreach ($emails as $email) {
// look for unsent emails in the queue with the same email ID
$queued = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_email_orders WHERE is_sent = 0 AND email_id = {$email->id}");
// loop through the queue and delete entries with identical customers
foreach ($queued as $queue) {
if ($queue->user_id > 0 && $order->user_id > 0 && $queue->user_id == $order->user_id) {
$wpdb->query("DELETE FROM {$wpdb->prefix}followup_email_orders WHERE id = {$queue->id}");
} elseif ($order->user_id == 0) {
// try to match the email address
$email = get_post_meta($queue->order_id, '_billing_email', true);
if ($email == $order->billing_email) {
$wpdb->query("DELETE FROM {$wpdb->prefix}followup_email_orders WHERE id = {$queue->id}");
}
}
}
if ($order->user_id > 0) {
$last_order_date = $wpdb->get_var($wpdb->prepare("SELECT p.post_date FROM {$wpdb->posts} p, {$wpdb->prefix}followup_customer_orders co WHERE co.followup_customer_id = %d AND co.order_id = p.ID AND p.post_status = 'publish' ORDER BY p.ID DESC LIMIT 1", $order->user_id));
} else {
$last_order_date = $wpdb->get_var($wpdb->prepare("SELECT p.post_date FROM {$wpdb->posts} p, {$wpdb->prefix}followup_customer_orders co WHERE co.followup_customer_id = %d AND co.order_id = p.ID AND p.post_status = 'publish' ORDER BY p.ID DESC LIMIT 1", $order->billing_email));
}
// add this email to the queue
$interval = (int) $email->interval_num;
$add = self::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => 0, 'order_id' => $order_id);
self::insert_email_order($insert);
$num_queued++;
}
}
return $num_queued;
}
public static function get_email_send_timestamp($email)
{
global $wpdb;
if (!is_object($email)) {
$email = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}followup_emails WHERE id = %d", $email));
}
$interval = (int) $email->interval_num;
$send_on = current_time('timestamp');
if ($email->interval_type == 'date') {
$meta = maybe_unserialize($email->meta);
if (!empty($email->send_date_hour) && !empty($email->send_date_minute)) {
$send_on = strtotime($email->send_date . ' ' . $email->send_date_hour . ':' . $email->send_date_minute . ' ' . $meta['send_date_ampm']);
if (false === $send_on) {
// fallback to only using the date
$send_on = strtotime($email->send_date);
}
} else {
$send_on = strtotime($email->send_date);
}
} else {
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = current_time('timestamp') + $add;
}
return $send_on;
}
public static function set_renewal_reminder($user_id, $subs_key)
{
global $wpdb;
$parts = explode('_', $subs_key);
$order_id = $parts[0];
$order = new WC_Order($order_id);
if (WC_Subscriptions_Order::order_contains_subscription($order)) {
// look for renewal emails
$emails = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}followup_emails WHERE `interval_type` = 'subs_before_renewal'");
if (count($emails) > 0) {
$item = WC_Subscriptions_Order::get_item_by_product_id($order);
$item_id = WC_Subscriptions_Order::get_items_product_id($item);
$renewal = WC_Subscriptions_Order::get_next_payment_timestamp($order, $item_id);
if (0 == $renewal) {
return;
}
foreach ($emails as $email) {
// add this email to the queue
$interval = (int) $email->interval_num;
$add = FUE::get_time_to_add($interval, $email->interval_duration);
$send_on = $renewal - $add;
$insert = array('send_on' => $send_on, 'email_id' => $email->id, 'product_id' => 0, 'order_id' => $order_id);
FUE::insert_email_order($insert);
}
}
}
}