/**
* Get the orders matching the conditions of the storewide email
* @param FUE_Email $email
* @return array
*/
private function get_orders_for_storewide_email($email)
{
$wpdb = Follow_Up_Emails::instance()->wpdb;
$trigger = $email->trigger;
$orders = array();
if ($trigger == 'cart') {
// cart is an unsupported trigger
return $orders;
}
if (in_array($email->trigger, $this->get_order_statuses())) {
// count the number of orders matching the email's order status trigger
// and exclude those Order IDs that are in the email queue, sent or unsent
if (WC_FUE_Compatibility::is_wc_version_gte_2_2()) {
$status = 'wc-' . $email->trigger;
$orders = $wpdb->get_col($wpdb->prepare("SELECT ID\n FROM {$wpdb->posts} p\n WHERE p.post_status = %s\n AND (\n SELECT COUNT(id)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE order_id = p.ID\n AND email_id = %d\n ) = 0", $status, $email->id));
} else {
$excluded_ids = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT order_id\n FROM {$wpdb->prefix}followup_email_orders\n WHERE order_id > 0\n AND email_id = %d", $email->id));
$orders = get_posts(array('post_status' => 'publish', 'tax_query' => array(array('taxonomy' => 'shop_order_status', 'field' => 'slug', 'terms' => $email->trigger)), 'post__not_in' => $excluded_ids, 'fields' => 'ids', 'posts_per_page' => -1));
}
// filter out the orders that don't match the email's product or category filter
if ($email->product_id) {
$order_ids = implode(',', $orders);
$orders = $wpdb->get_col($wpdb->prepare("SELECT order_id\n FROM {$wpdb->prefix}followup_order_items\n WHERE order_id IN ({$order_ids})\n AND (\n product_id = %d\n OR\n variation_id = %d\n )", $email->product_id, $email->product_id));
}
if ($email->category_id) {
$order_ids = implode(',', $orders);
$orders = $wpdb->get_col($wpdb->prepare("SELECT order_id\n FROM {$wpdb->prefix}followup_order_categories\n WHERE order_id IN ({$order_ids})\n AND category_id = %d", $email->category_id));
}
// remove orders with products/categories that are in the email exclusions list
if (!empty($email->meta['excluded_categories'])) {
foreach ($orders as $idx => $order_id) {
if (in_array($order_id, $email->meta['excluded_categories'])) {
unset($orders[$idx]);
}
}
// reset the indices
$orders = array_merge($orders, array());
}
if (!empty($email->meta['excluded_customers_products'])) {
// exclude orders from customers who have purchased any one of these products
$product_ids = implode(',', $email->meta['excluded_customers_products']);
foreach ($orders as $idx => $order_id) {
$order = WC_FUE_Compatibility::wc_get_order($order_id);
$user_id = WC_FUE_Compatibility::get_order_user_id($order);
if ($user_id) {
$customer = fue_get_customer($user_id);
} else {
$customer = fue_get_customer(0, $order->billing_email);
}
if (!$customer) {
continue;
}
$sql = "SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_order_items i\n WHERE o.followup_customer_id = %d\n AND o.order_id = i.order_id\n AND (\n i.product_id IN ( {$product_ids} )\n OR\n i.variation_id IN ( {$product_ids} )\n )";
$found = $wpdb->get_var($wpdb->prepare($sql, $customer->id));
if ($found > 0) {
unset($orders[$idx]);
}
}
}
if (!empty($email->meta['excluded_customers_categories'])) {
$product_ids = implode(',', $email->meta['excluded_customers_products']);
foreach ($orders as $idx => $order_id) {
$order = WC_FUE_Compatibility::wc_get_order($order_id);
$user_id = WC_FUE_Compatibility::get_order_user_id($order);
if ($user_id) {
$customer = fue_get_customer($user_id);
} else {
$customer = fue_get_customer(0, $order->billing_email);
}
if (!$customer) {
continue;
}
$sql = "SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_order_items i, {$wpdb->prefix}followup_customer_orders o\n WHERE o.followup_customer_id = %d\n AND o.order_id = i.order_id\n AND (\n i.product_id IN ( {$product_ids} )\n OR\n i.variation_id IN ( {$product_ids} )\n )";
$found = $wpdb->get_var($wpdb->prepare($sql, $customer->id));
if ($found > 0) {
unset($orders[$idx]);
}
}
}
} elseif ($trigger == 'first_purchase') {
// get the order IDs of customers with only 1 order
$customer_orders = $wpdb->get_col("SELECT order_id\n FROM {$wpdb->prefix}followup_customer_orders\n GROUP BY followup_customer_id\n HAVING COUNT(followup_customer_id) = 1");
if (count($customer_orders) > 0) {
$queue_orders = $wpdb->get_col($wpdb->prepare("SELECT order_id\n FROM {$wpdb->prefix}followup_email_orders\n WHERE order_id IN (" . implode(',', $customer_orders) . ")\n AND email_id = %d", $email->id));
// exclude orders that are already in the queue
foreach ($customer_orders as $customer_order) {
if (!in_array($customer_order, $queue_orders)) {
$orders[] = $customer_order;
}
}
}
} elseif ($trigger == 'product_purchase_above_one') {
// Get the orders of customers with more than 1 order
$customers = $wpdb->get_col("SELECT followup_customer_id\n FROM {$wpdb->prefix}followup_customer_orders\n GROUP BY followup_customer_id\n HAVING COUNT(followup_customer_id) > 1");
foreach ($customers as $customer_id) {
$customer_orders = $wpdb->get_col($wpdb->prepare("SELECT order_id\n FROM {$wpdb->prefix}followup_customer_orders\n WHERE followup_customer_id = %d\n ORDER BY order_id ASC", $customer_id));
if (count($customer_orders) > 0) {
// drop the customer's first order
$customer_orders = array_slice($customer_orders, 1);
$queue_orders = $wpdb->get_col($wpdb->prepare("SELECT order_id\n FROM {$wpdb->prefix}followup_email_orders\n WHERE order_id IN (" . implode(',', $customer_orders) . ")\n AND email_id = %d", $email->id));
// exclude orders that are already in the queue
foreach ($customer_orders as $customer_order) {
if (!in_array($customer_order, $queue_orders)) {
$orders[] = $customer_order;
}
}
}
}
}
// Remove the orders that match the email's send_once property
$email_meta = $email->meta;
$adjust_date = false;
if (!empty($email_meta)) {
if (isset($email_meta['adjust_date']) && $email_meta['adjust_date'] == 'yes') {
$adjust_date = true;
}
foreach ($orders as $idx => $order_id) {
$order = WC_FUE_Compatibility::wc_get_order($order_id);
// send email only once per customer
if (isset($email_meta['one_time']) && $email_meta['one_time'] == 'yes') {
$count_sent = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)\n FROM {$wpdb->prefix}followup_email_orders\n WHERE `user_email` = %s\n AND `email_id` = %d", $order->billing_email, $email->id));
if ($count_sent > 0) {
// do not send more of the same emails to this user
unset($orders[$idx]);
continue;
}
}
// adjust date only applies to non-guest orders
if ($adjust_date && $order->customer_user > 0) {
// check for similar existing and unsent email orders
// and adjust the date to send instead of inserting a duplicate row
$similar_emails = $this->fue->scheduler->get_items(array('email_id' => $email->id, 'user_id' => $order->customer_user, 'product_id' => $email->product_id, 'is_cart' => 0, 'is_sent' => 0));
if (count($similar_emails) > 0) {
unset($orders[$idx]);
continue;
}
}
}
// reset the indices
$orders = array_values($orders);
}
return $orders;
}
