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;
}