function sr_query_sales($start_date, $end_date_query, $date_series, $select, $group_by, $select_top_prod, $select_top_abandoned_prod, $terms_taxonomy_ids, $post)
{
global $wpdb;
$monthly_sales = array();
$cumm_top_prod_graph_data = array();
$results_top_prod = array();
$top_prod_ids = array();
$top_prod_graph_data = array();
$top_gateway_graph_data = array();
$top_shipping_method_graph_data = array();
$sr_currency_symbol = isset($post['SR_CURRENCY_SYMBOL']) ? $post['SR_CURRENCY_SYMBOL'] : '';
$sr_decimal_places = isset($post['SR_DECIMAL_PLACES']) ? $post['SR_DECIMAL_PLACES'] : '';
// $terms_postmeta_cond = (!empty($terms_post)) ? 'AND post_id IN ('.$terms_post.')' : '';
$sr_is_woo22 = !empty($post['SR_IS_WOO22']) ? $post['SR_IS_WOO22'] : '';
if (!empty($sr_is_woo22) && $sr_is_woo22 == 'true') {
$terms_post_join = '';
$terms_post_cond = "AND posts.post_status IN ('wc-completed','wc-processing','wc-on-hold')";
} else {
$terms_post_join = !empty($terms_taxonomy_ids) ? ' JOIN ' . $wpdb->prefix . 'term_relationships AS term_relationships ON (term_relationships.object_id = posts.ID AND posts.post_status = "publish") ' : '';
$terms_post_cond = !empty($terms_taxonomy_ids) && !empty($terms_post_join) ? 'AND term_relationships.term_taxonomy_id IN (' . $terms_taxonomy_ids . ')' : '';
}
//Query for getting the cumm sales
$query_monthly_sales = "SELECT SUM( postmeta.meta_value ) AS todays_sales,\n\t \t\t\t\t\t\tCOUNT(posts.ID) AS total_orders,\n\t \t\t\t\t\t\t{$select}\n\t\t FROM `{$wpdb->prefix}postmeta` AS postmeta\n\t\t LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\n\t\t {$terms_post_join}\n\t\t WHERE postmeta.meta_key IN ('_order_total')\n\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t {$terms_post_cond}\n\t GROUP BY {$group_by}";
$results_monthly_sales = $wpdb->get_results($query_monthly_sales, 'ARRAY_A');
$rows_monthly_sales = $wpdb->num_rows;
//Query for Top 5 Customers
$index = 0;
//Reg Customers
$query_reg_cumm = "SELECT ID FROM `{$wpdb->users}` \n\t WHERE user_registered BETWEEN '{$start_date}' AND '{$end_date_query}'";
$reg_cumm_ids = $wpdb->get_col($query_reg_cumm);
$rows_reg_cumm_ids = $wpdb->num_rows;
$query_cumm_top_cust_guest = "SELECT postmeta1.meta_value AS billing_email,\n\t GROUP_CONCAT(DISTINCT postmeta2.post_id\n ORDER BY postmeta2.meta_id DESC SEPARATOR ',' ) AS post_id,\n\t MAX(postmeta2.post_id) AS post_id_max,\n\t SUM(postmeta2.meta_value) as total\n\t \n\t FROM {$wpdb->prefix}postmeta AS postmeta1\n\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta1.post_id)\n\t INNER JOIN {$wpdb->prefix}postmeta AS postmeta2\n\t ON (postmeta2.post_ID = postmeta1.post_ID AND postmeta2.meta_key IN ('_order_total'))\n\t {$terms_post_join}\n\t WHERE postmeta1.meta_key IN ('_billing_email')\n\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t AND posts.id IN (SELECT post_id FROM {$wpdb->prefix}postmeta\n\t WHERE meta_key IN ('_customer_user')\n\t AND meta_value = 0)\n\t\t\t\t\t\t\t\t\t\t{$terms_post_cond}\n\t GROUP BY postmeta1.meta_value\n\t ORDER BY total DESC\n\t LIMIT 5";
$results_cumm_top_cust_guest = $wpdb->get_results($query_cumm_top_cust_guest, 'ARRAY_A');
$rows_cumm_top_cust_guest = $wpdb->num_rows;
$results_cumm_top_cust = array();
if ($rows_cumm_top_cust_guest > 0) {
$post_id_max = array();
foreach ($results_cumm_top_cust_guest as $results_cumm_top_cust_guest1) {
$post_id_max[] = $results_cumm_top_cust_guest1['post_id_max'];
}
$post_id_imploded = implode(",", $post_id_max);
$query_cumm_top_cust_guest_detail = "SELECT postmeta.post_id as post_id,\n\t GROUP_CONCAT(postmeta.meta_key\n\t ORDER BY postmeta.meta_id DESC SEPARATOR '###' ) AS meta_key,\n\t GROUP_CONCAT(postmeta.meta_value\n\t ORDER BY postmeta.meta_id DESC SEPARATOR '###' ) AS meta_value\n\t FROM {$wpdb->prefix}postmeta AS postmeta\n\t WHERE postmeta.post_id IN ({$post_id_imploded})\n\t AND postmeta.meta_key IN ('_billing_first_name' , '_billing_last_name')\n\t GROUP BY postmeta.post_id";
$results_cumm_top_cust_guest_detail = $wpdb->get_results($query_cumm_top_cust_guest_detail, 'ARRAY_A');
$results_cumm_top_cust_guest_detail_rows = $wpdb->num_rows;
$top_cust_guest_detail = array();
if ($results_cumm_top_cust_guest_detail_rows > 0) {
foreach ($results_cumm_top_cust_guest_detail as $cumm_top_cust_guest_detail) {
$guest_meta_values = explode('###', $cumm_top_cust_guest_detail['meta_value']);
$guest_meta_key = explode('###', $cumm_top_cust_guest_detail['meta_key']);
if (count($guest_meta_values) != count($guest_meta_key)) {
continue;
}
unset($cumm_top_cust_guest_detail['meta_value']);
unset($cumm_top_cust_guest_detail['meta_key']);
$guest_meta_key_values = array_combine($guest_meta_key, $guest_meta_values);
$top_cust_guest_detail[$cumm_top_cust_guest_detail['post_id']] = $guest_meta_key_values['_billing_first_name'] . " " . $guest_meta_key_values['_billing_last_name'];
}
}
foreach ($results_cumm_top_cust_guest as $cumm_top_cust_guest) {
$results_cumm_top_cust[$index] = array();
$post_id = $cumm_top_cust_guest['post_id_max'];
$results_cumm_top_cust[$index]['total'] = $sr_currency_symbol . sr_number_format($cumm_top_cust_guest['total'], $sr_decimal_places);
$results_cumm_top_cust[$index]['calc_total'] = floatval($cumm_top_cust_guest['total']);
// value used only for sorting purpose
$results_cumm_top_cust[$index]['name'] = !empty($top_cust_guest_detail[$post_id]) ? $top_cust_guest_detail[$post_id] : '-';
$results_cumm_top_cust[$index]['billing_email'] = $cumm_top_cust_guest['billing_email'];
$results_cumm_top_cust[$index]['post_ids'] = json_encode($cumm_top_cust_guest['post_id']);
$index++;
}
}
$query_cumm_top_cust_reg = "SELECT postmeta1.meta_value AS user_id,\n\t GROUP_CONCAT(DISTINCT postmeta1.post_id\n\t ORDER BY postmeta1.meta_id DESC SEPARATOR ',' ) AS post_id,\n\t SUM(postmeta2.meta_value) as total\n\t \n\t FROM {$wpdb->prefix}postmeta AS postmeta1\n\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta1.post_id)\n\t INNER JOIN {$wpdb->prefix}postmeta AS postmeta2\n\t ON (postmeta2.post_ID = postmeta1.post_ID AND postmeta2.meta_key IN ('_order_total'))\n\t {$terms_post_join}\n\t WHERE postmeta1.meta_key IN ('_customer_user')\n\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t AND posts.id IN (SELECT post_id FROM {$wpdb->prefix}postmeta\n\t WHERE meta_key IN ('_customer_user')\n\t AND meta_value > 0)\n\t\t\t\t\t\t\t\t\t\t{$terms_post_cond}\n\t GROUP BY postmeta1.meta_value\n\t ORDER BY total DESC\n\t LIMIT 5";
$results_cumm_top_cust_reg = $wpdb->get_results($query_cumm_top_cust_reg, 'ARRAY_A');
$rows_cumm_top_cust_reg = $wpdb->num_rows;
$user_id = array();
if ($rows_cumm_top_cust_reg > 0) {
foreach ($results_cumm_top_cust_reg as $results_cumm_top_cust_reg1) {
$user_id[] = $results_cumm_top_cust_reg1['user_id'];
}
if (!empty($user_id)) {
$user_ids_imploded = implode(",", $user_id);
}
$query_reg_details = "SELECT users.ID as cust_id,\n\t users.user_email as email,\n\t GROUP_CONCAT(usermeta.meta_key\n\t ORDER BY usermeta.umeta_id DESC SEPARATOR '###' ) AS meta_key,\n\t GROUP_CONCAT(usermeta.meta_value\n\t ORDER BY usermeta.umeta_id DESC SEPARATOR '###' ) AS meta_value\n\t FROM {$wpdb->users} as users\n\t JOIN {$wpdb->usermeta} as usermeta ON (users.ID = usermeta.user_id)\n\t WHERE users.ID IN ({$user_ids_imploded})\n\t AND usermeta.meta_key IN ('first_name','last_name')\n\t GROUP BY users.ID";
$results_reg_details = $wpdb->get_results($query_reg_details, 'ARRAY_A');
$results_reg_details_rows = $wpdb->num_rows;
$reg_cust_details = array();
if ($results_reg_details_rows > 0) {
foreach ($results_reg_details as $result_reg_cust) {
$reg_meta_values = explode('###', $result_reg_cust['meta_value']);
$reg_meta_key = explode('###', $result_reg_cust['meta_key']);
if (count($reg_meta_values) != count($reg_meta_key)) {
continue;
}
unset($result_reg_cust['meta_value']);
unset($result_reg_cust['meta_key']);
$reg_meta_key_values = array_combine($reg_meta_key, $reg_meta_values);
$reg_cust_details[$result_reg_cust['cust_id']] = array();
$reg_cust_details[$result_reg_cust['cust_id']]['name'] = $reg_meta_key_values['first_name'] . " " . $reg_meta_key_values['last_name'];
$reg_cust_details[$result_reg_cust['cust_id']]['email'] = $result_reg_cust['email'];
}
}
foreach ($results_cumm_top_cust_reg as $result_cumm_top_cust_reg) {
$results_cumm_top_cust[$index] = array();
$user_id = $result_cumm_top_cust_reg['user_id'];
$results_cumm_top_cust[$index]['total'] = $sr_currency_symbol . sr_number_format($result_cumm_top_cust_reg['total'], $sr_decimal_places);
$results_cumm_top_cust[$index]['calc_total'] = floatval($result_cumm_top_cust_reg['total']);
// value used only for sorting purpose
$results_cumm_top_cust[$index]['name'] = !empty($reg_cust_details[$user_id]) ? $reg_cust_details[$user_id]['name'] : '-';
$results_cumm_top_cust[$index]['billing_email'] = !empty($reg_cust_details[$user_id]) ? $reg_cust_details[$user_id]['email'] : '-';
$results_cumm_top_cust[$index]['post_ids'] = json_encode($result_cumm_top_cust_reg['post_id']);
$index++;
}
}
if (!empty($results_cumm_top_cust)) {
usort($results_cumm_top_cust, 'usort_callback');
$results_cumm_top_cust = array_slice($results_cumm_top_cust, 0, 5);
} else {
$results_cumm_top_cust = "";
}
//Top 5 Products OR Top 50 Products Detail View
//Query to get the Top 5 Products OR Top 50 Products Detail View
if (!empty($_POST['detailed_view']) && ($_POST['detailed_view'] = '1')) {
$limit = 50;
} else {
$limit = 5;
}
$query_top_prod = "SELECT order_item.product_id as product_id,\n\t order_item.product_name as product_name,\n\t order_item.sku as sku,\n\t order_item.category as category,\n\t SUM( order_item.sales ) AS product_sales ,\n\t COUNT( order_item.sales ) AS sales_count ,\n\t SUM( order_item.quantity ) AS product_qty,\n\t SUM( order_item.discount) AS discount \n\t FROM `{$wpdb->prefix}sr_woo_order_items` AS order_item\n\t LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = order_item.order_id )\n\t {$terms_post_join}\n\t WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t {$terms_post_cond}\n\t GROUP BY order_item.product_id\n\t ORDER BY product_sales DESC\n\t LIMIT " . $limit;
$results_top_prod = $wpdb->get_results($query_top_prod, 'ARRAY_A');
$rows_top_prod = $wpdb->num_rows;
if ($rows_top_prod > 0) {
foreach (array_keys($results_top_prod) as $results_top_prod1) {
$top_prod_ids[] = $results_top_prod[$results_top_prod1]['product_id'];
if (isset($post['top_prod_option'])) {
$results_top_prod[$results_top_prod1]['product_sales_display'] = $sr_currency_symbol . sr_number_format($results_top_prod[$results_top_prod1]['product_sales'], $sr_decimal_places);
}
}
if (!empty($top_prod_ids)) {
$top_prod_ids1 = implode(",", $top_prod_ids);
}
//Query to get the Top 5 Products graph related data OR Top 50 Products Detail View graph related data
$query_top_prod_graph = "SELECT order_item.product_id as product_id,\n\t SUM( order_item.sales ) AS product_sales,\n\t SUM( order_item.quantity ) AS product_qty,\n\t {$select}\n\t FROM `{$wpdb->prefix}sr_woo_order_items` AS order_item\n\t LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = order_item.order_id )\n\t WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t AND order_item.product_id IN ({$top_prod_ids1})\n\t GROUP BY order_item.product_id,{$group_by}\n\t ORDER BY FIND_IN_SET(order_item.product_id,'{$top_prod_ids1}')";
$results_top_prod_graph = $wpdb->get_results($query_top_prod_graph, 'ARRAY_A');
$rows_top_prod_graph = $wpdb->num_rows;
if ($rows_top_prod_graph > 0) {
foreach ($results_top_prod_graph as $results_top_prod_graph1) {
$top_prod_graph_temp[] = $results_top_prod_graph1['product_id'];
}
//call function for graph data formatting
sr_graph_data_formatting($top_prod_graph_data, $results_top_prod_graph, $group_by, 'product_id', 'product_sales', 'product_qty', 'product_sales', 'product_qty');
}
}
$monthly_sales_temp = $date_series;
$max_sales = 0;
$total_monthly_sales = 0;
$tot_cumm_orders = 0;
$tot_cumm_orders_qty = 0;
$total_orders = 0;
if ($rows_monthly_sales > 0) {
foreach ($results_monthly_sales as $results_monthly_sale) {
if ($group_by == "display_date_time") {
$monthly_sales_temp[$results_monthly_sale['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $results_monthly_sale['display_time'];
$monthly_sales_temp[$results_monthly_sale['comp_time']]['sales'] = floatval($results_monthly_sale['todays_sales']);
} else {
$monthly_sales_temp[$results_monthly_sale[$group_by]]['sales'] = floatval($results_monthly_sale['todays_sales']);
}
if ($max_sales < $results_monthly_sale['todays_sales']) {
$max_sales = $results_monthly_sale['todays_sales'];
}
$total_monthly_sales = $total_monthly_sales + $results_monthly_sale['todays_sales'];
$total_orders = $total_orders + $results_monthly_sale['total_orders'];
}
foreach ($monthly_sales_temp as $monthly_sales_temp1) {
$monthly_sales[] = $monthly_sales_temp1;
}
}
//Top 5 Products Graph
$cumm_top_prod_graph_data = array();
$index = 0;
$max_values = array();
if (!empty($top_prod_graph_data)) {
foreach ($top_prod_graph_data as $results_top_prod_graph1) {
$cumm_top_prod_graph_data[$index] = array();
$temp = array();
$cumm_date = $date_series;
$max = 0;
for ($j = 0; $j < sizeof($results_top_prod_graph1); $j++) {
if ($group_by == "display_date_time") {
$cumm_date[$results_top_prod_graph1[$j]['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $results_top_prod_graph1[$j]['display_time'];
}
if (isset($post['top_prod_option'])) {
if ($post['top_prod_option'] == 'sr_opt_top_prod_price') {
if ($results_top_prod_graph1[$j]['product_sales'] > $max) {
$max = floatval($results_top_prod_graph1[$j]['product_sales']);
}
if ($group_by == "display_date_time") {
$cumm_date[$results_top_prod_graph1[$j]['comp_time']]['sales'] = floatval($results_top_prod_graph1[$j]['product_sales']);
} else {
$cumm_date[$results_top_prod_graph1[$j][$group_by]]['sales'] = floatval($results_top_prod_graph1[$j]['product_sales']);
}
} else {
if ($post['top_prod_option'] == 'sr_opt_top_prod_qty') {
if ($results_top_prod_graph1[$j]['product_qty'] > $max) {
$max = intval($results_top_prod_graph1[$j]['product_qty']);
}
if ($group_by == "display_date_time") {
$cumm_date[$results_top_prod_graph1[$j]['comp_time']]['sales'] = intval($results_top_prod_graph1[$j]['product_qty']);
} else {
$cumm_date[$results_top_prod_graph1[$j][$group_by]]['sales'] = intval($results_top_prod_graph1[$j]['product_qty']);
}
}
}
} else {
if ($results_top_prod_graph1[$j]['product_sales'] > $max) {
$max = floatval($results_top_prod_graph1[$j]['product_sales']);
}
$cumm_date[$results_top_prod_graph1[$j][$group_by]]['sales'] = floatval($results_top_prod_graph1[$j]['product_sales']);
}
$product_sales_display = $results_top_prod_graph1[$j]['product_sales'];
}
foreach ($cumm_date as $cumm_date1) {
$temp[] = $cumm_date1;
}
if (isset($post['option'])) {
// Condition to handle the change of graph on option select
$cumm_top_prod_graph_data[$index]['graph_data'] = $temp;
$cumm_top_prod_graph_data[$index]['max_value'] = $max;
} else {
$results_top_prod[$index]['graph_data'] = $temp;
$results_top_prod[$index]['max_value'] = $max;
}
$index++;
}
}
if (!empty($post['detailed_view'])) {
// for Top Product Detail View Widget
// Query for total quantity
$query_total_quantity = "SELECT SUM(quantity)\n\t \t\t\t\t\t\t\t FROM {$wpdb->prefix}sr_woo_order_items \n\t \t\t\t\t\t\t\t WHERE order_date BETWEEN '{$start_date}' AND '{$end_date_query}' \n\t \t\t\t\t\t\t\t AND order_status IN ('wc-on-hold','wc-processing','wc-completed','wc-refunded')";
$total_quantity = $wpdb->get_var($query_total_quantity);
$results_top_prod['total_quantity_sold'] = $total_quantity;
// Per Product Sales funnel
$query_added_qty = "SELECT product_id, SUM(quantity) AS added_qty \n\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items\n\t\t\t\t\t\t\t\tWHERE abandoned_cart_time BETWEEN '" . strtotime($start_date) . "' AND '" . strtotime($end_date_query) . "' \n\t\t\t\t\t\t\t\tGROUP BY product_id";
$added_qty = $wpdb->get_results($query_added_qty, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
$total_added_qty = array();
if ($num_rows > 0) {
foreach ($added_qty as $item) {
$total_added_qty[$item['product_id']]['added_qty'] = $item['added_qty'];
}
}
$query_ordered_qty = "SELECT product_id, SUM( quantity ) AS ordered_qty \n\t\t\t\t\t\t\t\t FROM {$wpdb->prefix}sr_woo_abandoned_items \n\t\t\t\t\t\t\t\t WHERE order_id !='NULL'\n\t\t\t\t\t\t\t\t AND abandoned_cart_time BETWEEN '" . strtotime($start_date) . "' AND '" . strtotime($end_date_query) . "' \n\t\t\t\t\t\t\t\t GROUP BY product_id";
$ordered_qty = $wpdb->get_results($query_ordered_qty, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
$total_ordered_qty = array();
if ($num_rows > 0) {
foreach ($ordered_qty as $item) {
$total_ordered_qty[$item['product_id']]['ordered_qty'] = $item['ordered_qty'];
}
}
$query_completed_qty = "SELECT items.product_id AS product_id, SUM(items.quantity) AS completed_qty \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items AS items\n\t\t\t\t\t\t\t\t\t\t JOIN {$wpdb->prefix}sr_woo_order_items AS sr_order ON ( sr_order.order_id = items.order_id AND sr_order.product_id = items.product_id ) \n\t\t\t\t\t\t\t\t\tWHERE sr_order.order_status = 'wc-completed' \n\t\t\t\t\t\t\t\t\tAND sr_order.order_date BETWEEN '{$start_date}' AND '{$end_date_query}' \n\t\t\t\t\t\t\t\t\tGROUP BY items.product_id";
$completed_qty = $wpdb->get_results($query_completed_qty, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
$total_completed_qty = array();
if ($num_rows > 0) {
foreach ($completed_qty as $item) {
$total_completed_qty[$item['product_id']]['completed_qty'] = $item['completed_qty'];
}
}
$total_sales_funnel = array();
// code merged all the required data for sales funnel in single array.
foreach ($added_qty as $item) {
$total_sales_funnel[$item['product_id']]['added_qty'] = floatval($item['added_qty']);
if (array_key_exists($item['product_id'], $total_ordered_qty)) {
$total_sales_funnel[$item['product_id']]['ordered_qty'] = floatval($total_ordered_qty[$item['product_id']]['ordered_qty']);
} else {
$total_sales_funnel[$item['product_id']]['ordered_qty'] = 0;
}
if (array_key_exists($item['product_id'], $total_completed_qty)) {
$total_sales_funnel[$item['product_id']]['completed_qty'] = floatval($total_completed_qty[$item['product_id']]['completed_qty']);
} else {
$total_sales_funnel[$item['product_id']]['completed_qty'] = 0;
}
}
$results_top_prod['sales_funnel_data'] = $total_sales_funnel;
// Query for refund data
$query_refund_data = "SELECT GROUP_CONCAT( itemmeta.meta_value SEPARATOR ',' ) AS meta_value,\n\t \t\t\t\t\t\t\t\tGROUP_CONCAT( itemmeta.meta_key SEPARATOR ',' ) AS meta_key\n\t\t\t\t\t\t\t\t FROM {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta\n\t\t\t\t\t\t\t\t\t\tJOIN {$wpdb->prefix}woocommerce_order_items AS items ON ( itemmeta.order_item_id = items.order_item_id ) \n\t\t\t\t\t\t\t\t\t\tJOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = items.order_id AND posts.post_status = 'wc-refunded' AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}' ) \n\t\t\t\t\t\t\t\t WHERE itemmeta.meta_key IN ('_qty', '_product_id', '_variation_id', '_line_total') \n\t\t\t\t\t\t\t\t GROUP BY items.order_item_id ";
$refunds = $wpdb->get_results($query_refund_data, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
$refund_item_meta_key_values = array();
if ($num_rows > 0) {
foreach ($refunds as $refund) {
$refund_item_meta_values = explode(',', $refund['meta_value']);
$refund_item_meta_key = explode(',', $refund['meta_key']);
if (count($refund_item_meta_values) != count($refund_item_meta_key)) {
continue;
}
$refund_item_meta_key_values[] = array_combine($refund_item_meta_key, $refund_item_meta_values);
}
}
$refund_data = array();
$total_ref_qty = 0;
$total_ref_sales = 0;
foreach ($refund_item_meta_key_values as $item) {
if (!empty($item['_variation_id']) && $item['_variation_id'] > 0) {
// if variation exists then use variation id as product id.
$item['_product_id'] = $item['_variation_id'];
unset($item['_variation_id']);
}
$key = $item['_product_id'];
if (!empty($refund_data) && array_key_exists($key, $refund_data)) {
$refund_data[$key]['_qty'] += floatval($item['_qty']);
$refund_data[$key]['_sales'] += floatval($item['_line_total']);
} else {
$refund_data[$key]['_qty'] = floatval($item['_qty']);
$refund_data[$key]['_sales'] = floatval($item['_line_total']);
}
$total_ref_qty += $item['_qty'];
$total_ref_sales += $item['_line_total'];
}
foreach ($refund_data as &$product) {
$product['percent_of_total_ref_qty'] = sr_number_format($product['_qty'] / $total_ref_qty * 100, $sr_decimal_places) . '%';
}
$results_top_prod['refund_data'] = $refund_data;
// discounted sales & qty
$query_discount_sales = "SELECT product_id, SUM(quantity) AS qty , SUM(sales) AS sales \n \t\t\t\t\t\t FROM {$wpdb->prefix}sr_woo_order_items \n \t\t\t\t\t\t WHERE order_date BETWEEN '{$start_date}' AND '{$end_date_query}' \n\t \t\t\t\t\t\t\t AND order_status IN ('wc-on-hold','wc-processing','wc-completed')\n\t \t\t\t\t\t\t\t AND discount > 0\n\t \t\t\t\t\t\t\t GROUP BY product_id";
$discount_sales = $wpdb->get_results($query_discount_sales, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
$total_discount_sales = array();
if ($num_rows > 0) {
foreach ($discount_sales as $item) {
$total_discount_sales[$item['product_id']]['disc_qty'] = floatval($item['qty']);
$total_discount_sales[$item['product_id']]['disc_sales'] = floatval($item['sales']);
}
}
$results_top_prod['total_discount_sales'] = $total_discount_sales;
$results_top_prod['total_monthly_sales'] = $total_monthly_sales;
$limit = "";
$results_top_prod['abandoned_data'] = json_decode(sr_get_abandoned_products($start_date, $end_date_query, $group_by, $sr_currency_symbol, $sr_decimal_places, $date_series, $select_top_abandoned_prod, $limit, $terms_taxonomy_ids, $sr_is_woo22), true);
$results[0] = $results_top_prod;
return $results;
}
//Query for Avg. Items Per Customer
$query_cumm_reg_cust_count = "SELECT COUNT(DISTINCT postmeta.meta_value) AS cust_orders\n\t\t FROM {$wpdb->prefix}postmeta AS postmeta\n\t\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta.post_id)\n\t\t {$terms_post_join}\n\t\t WHERE postmeta.meta_key IN ('_customer_user')\n\t\t AND postmeta.meta_value > 0\n\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t {$terms_post_cond}";
$results_cumm_reg_cust_count = $wpdb->get_col($query_cumm_reg_cust_count);
$rows_cumm_reg_cust_count = $wpdb->num_rows;
if ($rows_cumm_reg_cust_count > 0) {
$reg_cust_count = $results_cumm_reg_cust_count[0];
} else {
$reg_cust_count = 0;
}
$query_cumm_guest_cust_count = "SELECT COUNT(DISTINCT postmeta1.meta_value) AS cust_orders\n\t\t FROM {$wpdb->prefix}postmeta AS postmeta1\n\t\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta1.post_id)\n\t\t INNER JOIN {$wpdb->prefix}postmeta AS postmeta2 \n\t\t ON (postmeta2.post_ID = postmeta1.post_ID AND postmeta2.meta_key IN ('_customer_user'))\n\t {$terms_post_join}\n\t\t WHERE postmeta1.meta_key IN ('_billing_email')\n\t\t AND postmeta2.meta_value = 0\n\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t {$terms_post_cond}";
$results_cumm_guest_cust_count = $wpdb->get_col($query_cumm_guest_cust_count);
$rows_cumm_guest_cust_count = $wpdb->num_rows;
if ($rows_cumm_guest_cust_count > 0) {
$guest_cust_count = $results_cumm_guest_cust_count[0];
} else {
$guest_cust_count = 0;
}
$total_cumm_cust_count = $reg_cust_count + $guest_cust_count;
//Query for Avg. Order Total and Avg. Order Items
$query_cumm_avg_order_tot_items = "SELECT COUNT(DISTINCT order_item.order_id) as no_orders,\n\t\t\t\t SUM( order_item.quantity ) AS cumm_quantity\n\t\t\t FROM `{$wpdb->prefix}sr_woo_order_items` AS order_item\n\t\t\t LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = order_item.order_id )\n\t\t\t {$terms_post_join}\n\t\t\t WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t \t{$terms_post_cond}";
$results_cumm_avg_order_tot_items = $wpdb->get_results($query_cumm_avg_order_tot_items, 'ARRAY_A');
$rows_cumm_avg_order_tot_items = $wpdb->num_rows;
if ($rows_cumm_avg_order_tot_items > 0) {
$tot_cumm_orders = $results_cumm_avg_order_tot_items[0]['no_orders'];
$tot_cumm_orders_qty = $results_cumm_avg_order_tot_items[0]['cumm_quantity'];
} else {
$tot_cumm_orders = 0;
$tot_cumm_orders_qty = 0;
}
//Total Discount Sales Widget
$query_cumm_discount_sales = "SELECT SUM( postmeta.meta_value ) AS discount_sales,\n\t \t\t\t\t\t\t{$select}\n\t\t FROM `{$wpdb->prefix}postmeta` AS postmeta\n\t\t \tLEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\n\t\t \t{$terms_post_join}\n\t\t WHERE postmeta.meta_key IN ('_order_discount','_cart_discount')\n\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t {$terms_post_cond}\n\t GROUP BY {$group_by}";
$results_cumm_discount_sales = $wpdb->get_results($query_cumm_discount_sales, 'ARRAY_A');
$rows_cumm_discount_sales = $wpdb->num_rows;
$cumm_discount_sales_temp = $date_series;
$cumm_discount_sales = array();
$max_discount_total = 0;
$total_discount_sales = 0;
if ($rows_cumm_discount_sales > 0) {
foreach ($results_cumm_discount_sales as $results_cumm_discount_sale) {
if ($group_by == "display_date_time") {
$cumm_discount_sales_temp[$results_cumm_discount_sale['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $results_cumm_discount_sale['display_time'];
$cumm_discount_sales_temp[$results_cumm_discount_sale['comp_time']]['sales'] = floatval($results_cumm_discount_sale['discount_sales']);
} else {
$cumm_discount_sales_temp[$results_cumm_discount_sale[$group_by]]['sales'] = floatval($results_cumm_discount_sale['discount_sales']);
}
if ($max_discount_total < $results_cumm_discount_sale['discount_sales']) {
$max_discount_total = $results_cumm_discount_sale['discount_sales'];
}
$total_discount_sales = $total_discount_sales + $results_cumm_discount_sale['discount_sales'];
}
foreach ($cumm_discount_sales_temp as $cumm_discount_sales_temp1) {
$cumm_discount_sales[] = $cumm_discount_sales_temp1;
}
}
//Top Coupons Widget
$query_cumm_coupon_count = "SELECT COUNT( order_items.order_item_name ) AS coupon_count,\n\t \t\t\t\t\t\t\tSUM(order_itemmeta.meta_value) AS coupon_amount,\n\t \t\t\t\t\t\t\torder_items.order_item_name AS coupon_name,\n\t \t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT order_items.order_id\n\t ORDER BY order_items.order_item_id DESC SEPARATOR ',' ) AS order_ids\n\t\t FROM `{$wpdb->prefix}posts` AS posts\n\t\t \tJOIN {$wpdb->prefix}woocommerce_order_items as order_items ON ( posts.ID = order_items.order_id )\n\t\t \tJOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_itemmeta \n\t\t \t\tON (order_items.order_item_id = order_itemmeta.order_item_id \n\t\t \t\t\t\tAND order_itemmeta.meta_key IN ('discount_amount') )\n\t\t\t\t\t\t\t\t\t{$terms_post_join}\n\t\t WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t {$terms_post_cond}\n\t\t AND order_items.order_item_type IN ('coupon')\n\t GROUP BY order_items.order_item_name\n\t ORDER BY coupon_count DESC, coupon_amount DESC\n\t LIMIT 5";
$results_cumm_coupon_count = $wpdb->get_results($query_cumm_coupon_count, 'ARRAY_A');
$rows_cumm_coupon_count = $wpdb->num_rows;
foreach ($results_cumm_coupon_count as &$results_cumm_coupon_count1) {
$results_cumm_coupon_count1['coupon_amount'] = $sr_currency_symbol . sr_number_format($results_cumm_coupon_count1['coupon_amount'], $sr_decimal_places);
$results_cumm_coupon_count1['coupon_count'] = sr_number_format($results_cumm_coupon_count1['coupon_count'], $sr_decimal_places);
}
// % Orders Containing Coupons
$sr_per_order_containing_coupons = '';
$query_cumm_orders_coupon_count = "SELECT COUNT( posts.ID ) AS total_coupon_orders\n\t\t \t\t\t\t\t\t\t\t\tFROM `{$wpdb->prefix}posts` AS posts\n\t\t\t \t\t\t\tJOIN {$wpdb->prefix}woocommerce_order_items as order_items ON ( posts.ID = order_items.order_id )\n\t\t\t \t\t\t\t{$terms_post_join}\n\t\t\t \t\t\tWHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t\t\t\t {$terms_post_cond}\n\t\t\t\t\t \tAND order_items.order_item_type IN ('coupon')";
$results_cumm_orders_coupon_count = $wpdb->get_col($query_cumm_orders_coupon_count);
$rows_cumm_orders_coupon_count = $wpdb->num_rows;
if ($rows_cumm_orders_coupon_count > 0 && $total_orders > 0) {
$sr_per_order_containing_coupons = $results_cumm_orders_coupon_count[0] / $total_orders * 100;
}
//Orders By Payment Gateways
$query_top_payment_gateway = "SELECT postmeta1.meta_value AS payment_method,\n\t\t \t\t\t\t\t\t\tSUM(postmeta2.meta_value) AS sales_total,\n\t\t \t\t\t\t\t\t\tCOUNT(posts.ID) AS sales_count,\n\t\t \t\t\t\t\t\t\tGROUP_CONCAT(posts.ID ORDER BY posts.ID DESC SEPARATOR ',' ) AS order_ids\n\t\t\t\t FROM {$wpdb->prefix}posts AS posts \n\t\t\t\t\t LEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta1 ON ( posts.ID = postmeta1.post_id )\n\t\t\t\t\t LEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta2 ON ( posts.ID = postmeta2.post_id )\n\t\t\t\t\t {$terms_post_join}\n\t\t\t\t WHERE postmeta1.meta_key IN ('_payment_method')\n\t\t\t\t \tAND postmeta2.meta_key IN ('_order_total')\n\t\t\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t\t {$terms_post_cond}\n\t\t\t GROUP BY payment_method\n\t\t\t ORDER BY sales_total DESC\n\t\t\t LIMIT 5";
$results_top_payment_gateway = $wpdb->get_results($query_top_payment_gateway, 'ARRAY_A');
$rows_top_payment_gateway = $wpdb->num_rows;
if ($rows_top_payment_gateway > 0) {
foreach ($results_top_payment_gateway as &$results_top_payment_gateway1) {
$top_payment_gateway[] = $results_top_payment_gateway1['payment_method'];
if (isset($post['top_prod_option'])) {
$results_top_payment_gateway1['gateway_sales_display'] = !empty($results_top_payment_gateway1['sales_total']) ? $sr_currency_symbol . sr_number_format($results_top_payment_gateway1['sales_total'], $sr_decimal_places) : $sr_currency_symbol . '0';
$results_top_payment_gateway1['gateway_sales_percent'] = sr_number_format($results_top_payment_gateway1['sales_total'] / $total_monthly_sales * 100, $sr_decimal_places) . '%';
}
}
if (!empty($top_payment_gateway)) {
$top_payment_gateway_imploded = "'" . implode("','", $top_payment_gateway) . "'";
$top_payment_gateway_cond = 'AND postmeta1.meta_value IN (' . $top_payment_gateway_imploded . ')';
// $top_payment_gateway_order_by = 'ORDER BY FIND_IN_SET(postmeta1.meta_value,\'".implode(",",$top_payment_gateway)."\')';
$top_payment_gateway_order_by = "ORDER BY FIND_IN_SET(postmeta1.meta_value,'" . implode(",", $top_payment_gateway) . "')";
}
} else {
$top_payment_gateway_cond = '';
$top_payment_gateway_order_by = '';
}
//Query to get the Top 5 Products graph related data
$query_top_gateways_graph = "SELECT postmeta1.meta_value AS payment_method,\n\t \t\t\t\t\t\t\tSUM(postmeta2.meta_value) AS sales_total,\n\t \t\t\t\t\t\t\tCOUNT(posts.ID) AS sales_count,\n\t \t\t\t\t\t\t\t{$select}\n\t\t\t FROM {$wpdb->prefix}posts AS posts \n\t\t\t\t LEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta1 ON ( posts.ID = postmeta1.post_id )\n\t\t\t\t LEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta2 ON ( posts.ID = postmeta2.post_id )\n\t\t\t\t {$terms_post_join}\n\t\t\t WHERE postmeta1.meta_key IN ('_payment_method')\n\t\t\t \tAND postmeta2.meta_key IN ('_order_total')\n\t\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t {$terms_post_cond}\n\t\t\t {$top_payment_gateway_cond}\n\t\t GROUP BY payment_method, {$group_by}\n\t\t {$top_payment_gateway_order_by}";
$results_top_gateways_graph = $wpdb->get_results($query_top_gateways_graph, 'ARRAY_A');
$rows_top_gateways_graph = $wpdb->num_rows;
$cumm_payment_gateway_temp = $date_series;
$cumm_payment_gateway_sales = array();
if ($rows_top_gateways_graph > 0) {
//call function for graph data formatting
sr_graph_data_formatting($top_gateway_graph_data, $results_top_gateways_graph, $group_by, 'payment_method', 'gateway_sales_amt', 'gateway_sales_count', 'sales_total', 'sales_count');
}
//Query to get the Payment Gateway Title
$query_gateway_title = "SELECT DISTINCT postmeta2.meta_value as gateway_title,\n \t\t\t\t\t\t\tpostmeta1.meta_value as gateway_method\n\t \t\t\t\t\t\tFROM `{$wpdb->prefix}postmeta` AS postmeta2\n\t \t\t\t\t\t\t\tJOIN `{$wpdb->prefix}postmeta` AS postmeta1 ON ( postmeta2.post_id = postmeta1.post_id )\n \t\t\t\t\t\t\tWHERE postmeta2.meta_key IN ('_payment_method_title')\n \t\t\t\t\t\t\t\tAND postmeta1.meta_key IN ('_payment_method')\n \t\t\t\t\t\t\t\t{$top_payment_gateway_cond}\n \t\t\t\t\t\t\t{$top_payment_gateway_order_by}";
$result_gateway_title = $wpdb->get_results($query_gateway_title, 'ARRAY_A');
$gateway_title = array();
foreach ($result_gateway_title as $result_gateway_title1) {
$gateway_title[strtolower($result_gateway_title1['gateway_method'])] = $result_gateway_title1['gateway_title'];
}
//Top 5 Products Graph
$cumm_top_gateway_graph_data = array();
$index = 0;
$max_values = array();
if (!empty($top_gateway_graph_data)) {
foreach ($top_gateway_graph_data as $top_gateway_graph_data1) {
$cumm_top_gateway_amt_graph_data[$index] = array();
$temp_gateway_sales_amt = array();
$temp_gateway_sales_count = array();
$cumm_date_amt = $date_series;
$cumm_date_count = $date_series;
$max_amt = 0;
$max_count = 0;
for ($j = 0; $j < sizeof($top_gateway_graph_data1); $j++) {
if ($group_by == "display_date_time") {
$cumm_date_amt[$top_gateway_graph_data1[$j]['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $top_gateway_graph_data1[$j]['display_time'];
$cumm_date_count[$top_gateway_graph_data1[$j]['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $top_gateway_graph_data1[$j]['display_time'];
$cumm_date_amt[$top_gateway_graph_data1[$j]['comp_time']]['sales'] = floatval($top_gateway_graph_data1[$j]['gateway_sales_amt']);
$cumm_date_count[$top_gateway_graph_data1[$j]['comp_time']]['sales'] = floatval($top_gateway_graph_data1[$j]['gateway_sales_count']);
} else {
$cumm_date_amt[$top_gateway_graph_data1[$j][$group_by]]['sales'] = floatval($top_gateway_graph_data1[$j]['gateway_sales_amt']);
$cumm_date_count[$top_gateway_graph_data1[$j][$group_by]]['sales'] = floatval($top_gateway_graph_data1[$j]['gateway_sales_count']);
}
//Payment Gateways Sales Amt
if ($top_gateway_graph_data1[$j]['gateway_sales_amt'] > $max_amt) {
$max_amt = floatval($top_gateway_graph_data1[$j]['gateway_sales_amt']);
}
//Payment Gateways Sales Count
if ($top_gateway_graph_data1[$j]['gateway_sales_count'] > $max_count) {
$max_count = floatval($top_gateway_graph_data1[$j]['gateway_sales_count']);
}
}
foreach ($cumm_date_amt as $cumm_date_amt1) {
$temp_gateway_sales_amt[] = $cumm_date_amt1;
}
foreach ($cumm_date_count as $cumm_date_count1) {
$temp_gateway_sales_count[] = $cumm_date_count1;
}
$results_top_payment_gateway[$index]['graph_data_sales_amt'] = $temp_gateway_sales_amt;
$results_top_payment_gateway[$index]['max_value_sales_amt'] = $max_amt;
$results_top_payment_gateway[$index]['graph_data_sales_count'] = $temp_gateway_sales_count;
$results_top_payment_gateway[$index]['max_value_sales_count'] = $max_count;
$results_top_payment_gateway[$index]['payment_method'] = $gateway_title[strtolower($results_top_payment_gateway[$index]['payment_method'])];
$index++;
}
}
//Query for getting the cumm taxes
$query_cumm_taxes = "SELECT GROUP_CONCAT(postmeta.meta_key order by postmeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\n\t\t\t\t\t\t\t\t\tGROUP_CONCAT(postmeta.meta_value order by postmeta.meta_id SEPARATOR '###') AS prod_othermeta_value\n\t\t FROM `{$wpdb->prefix}postmeta` AS postmeta\n\t\t \tLEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\n\t\t \t{$terms_post_join}\n\t\t WHERE postmeta.meta_key IN ('_order_total','_order_shipping','_order_shipping_tax','_order_tax')\n\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t {$terms_post_cond}\n\t\t GROUP BY posts.ID";
$results_cumm_taxes = $wpdb->get_results($query_cumm_taxes, 'ARRAY_A');
$rows_cumm_taxes = $wpdb->num_rows;
$tax_data = array();
if ($rows_cumm_taxes > 0) {
$tax = 0;
$shipping_tax = 0;
$shipping = 0;
$order_total = 0;
foreach ($results_cumm_taxes as $results_cumm_tax) {
$prod_meta_values = explode('###', $results_cumm_tax['prod_othermeta_value']);
$prod_meta_key = explode('###', $results_cumm_tax['prod_othermeta_key']);
if (count($prod_meta_values) != count($prod_meta_key)) {
continue;
}
$prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values);
$tax = $tax + $prod_meta_key_values['_order_tax'];
$shipping_tax = $shipping_tax + $prod_meta_key_values['_order_shipping_tax'];
$shipping = $shipping + $prod_meta_key_values['_order_shipping'];
$order_total = $order_total + $prod_meta_key_values['_order_total'];
}
$tax_data['tax'] = $tax;
$tax_data['shipping_tax'] = $shipping_tax;
$tax_data['shipping'] = $shipping;
$tax_data['net_sales'] = $order_total - ($tax + $shipping_tax + $shipping);
$tax_data['total_sales'] = $order_total;
}
$query_min_abandoned_date = "SELECT MIN(abandoned_cart_time) AS min_abandoned_date\n\t \t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items";
$results_min_abandoned_date = $wpdb->get_col($query_min_abandoned_date);
$rows_min_abandoned_date = $wpdb->num_rows;
$min_abandoned_date = '';
if ($results_min_abandoned_date[0] != '') {
$min_abandoned_date = date('Y-m-d', (int) $results_min_abandoned_date[0]);
}
//Cumm Cart Abandonment Rate
$query_total_cart = "SELECT COUNT(DISTINCT cart_id) as total_cart_count\n\t\t\t \t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items\n\t\t\t \t\t\tWHERE abandoned_cart_time >= " . strtotime($start_date) . " AND abandoned_cart_time <=" . strtotime($end_date_query);
$total_cart_count = $wpdb->get_col($query_total_cart);
$rows_total_cart = $wpdb->num_rows;
$query_total_abandoned_cart = "SELECT COUNT(DISTINCT cart_id) as total_cart_abandoned_count\n\t\t\t\t\t\t \t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items\n\t\t\t\t\t\t \t\t\tWHERE abandoned_cart_time >= " . strtotime($start_date) . " AND abandoned_cart_time <=" . strtotime($end_date_query) . "\n\t\t\t\t\t\t \t\t\t\tAND product_abandoned = 1\n\t\t\t\t\t\t \t\t\t\tAND order_id IS NULL";
$total_abandoned_cart_count = $wpdb->get_col($query_total_abandoned_cart);
$rows_total_abandoned_cart = $wpdb->num_rows;
if (!empty($total_cart_count) && $total_cart_count[0] > 0) {
$cumm_cart_abandoned_rate = round($total_abandoned_cart_count[0] / $total_cart_count[0] * 100, get_option('woocommerce_price_num_decimals'));
} else {
$cumm_cart_abandoned_rate = 0;
}
//Query for getting the countries wise sales
$query_cumm_sales_billing_country = "SELECT SUM( postmeta.meta_value ) AS sales,\n\t \t\t\t\t\t\t\tCOUNT(posts.ID) AS total_orders,\n\t \t\t\t\t\t\t\tpostmeta_country.meta_value AS billing_country,\n\t \t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT postmeta.post_id\n \tORDER BY postmeta.post_id DESC SEPARATOR ',' ) AS order_ids\n\t\t FROM `{$wpdb->prefix}postmeta` AS postmeta\n\t\t \tLEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\n\t\t \tJOIN {$wpdb->prefix}postmeta AS postmeta_country ON ( postmeta_country.post_id = postmeta.post_id )\n\t\t \t{$terms_post_join}\n\t\t WHERE postmeta.meta_key IN ('_order_total')\n\t\t AND posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t AND postmeta_country.meta_key IN ('_billing_country')\n\t\t {$terms_post_cond}\n\t GROUP BY billing_country";
$results_cumm_sales_billing_country = $wpdb->get_results($query_cumm_sales_billing_country, 'ARRAY_A');
$rows_cumm_sales_billing_country = $wpdb->num_rows;
$cumm_sales_billing_country_values = array();
$cumm_sales_billing_country_tooltip = array();
if ($rows_cumm_sales_billing_country > 0) {
foreach ($results_cumm_sales_billing_country as $result_cumm_sales_billing_country) {
$cumm_sales_billing_country_values[$result_cumm_sales_billing_country['billing_country']] = $result_cumm_sales_billing_country['sales'];
$cumm_sales_billing_country_tooltip[$result_cumm_sales_billing_country['billing_country']] = array();
$cumm_sales_billing_country_tooltip[$result_cumm_sales_billing_country['billing_country']]['sales'] = $sr_currency_symbol . sr_number_format($result_cumm_sales_billing_country['sales'], $sr_decimal_places);
$cumm_sales_billing_country_tooltip[$result_cumm_sales_billing_country['billing_country']]['count'] = sr_number_format($result_cumm_sales_billing_country['total_orders'], $sr_decimal_places);
$cumm_sales_billing_country_tooltip[$result_cumm_sales_billing_country['billing_country']]['order_ids'] = $result_cumm_sales_billing_country['order_ids'];
}
}
//Query to get the top shipping methods
$result_top_shipping_method = array();
$results_top_shipping_method = array();
$rows_top_shipping_method = 0;
if (get_option('woocommerce_calc_shipping') == 'yes') {
$query_top_shipping_method = "SELECT COUNT( order_items.order_item_name ) AS shipping_count,\n\t\t\t \t\t\t\t\t\t\tSUM(order_itemmeta.meta_value) AS shipping_amount,\n\t\t\t \t\t\t\t\t\t\torder_items.order_item_name AS shipping_name,\n\t\t\t \t\t\t\t\t\t\tSUM(postmeta.meta_value) AS sales_total,\n\t\t\t \t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT order_items.order_id\n\t\t\t \t\tORDER BY order_items.order_item_id DESC SEPARATOR ',' ) AS order_ids\n\t\t\t\t\t FROM `{$wpdb->prefix}posts` AS posts\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_items as order_items ON ( posts.ID = order_items.order_id )\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_itemmeta \n\t\t\t\t\t \t\tON (order_items.order_item_id = order_itemmeta.order_item_id \n\t\t\t\t\t \t\t\t\tAND order_itemmeta.meta_key IN ('cost') )\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta ON ( posts.ID = postmeta.post_id )\n\t\t\t\t\t\t\t\t\t\t\t\t{$terms_post_join}\n\t\t\t\t\t WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t\t\t {$terms_post_cond}\n\t\t\t\t\t AND order_items.order_item_type IN ('shipping')\n\t\t\t\t\t AND postmeta.meta_key IN ('_order_total')\n\t\t\t\t GROUP BY order_items.order_item_name\n\t\t\t\t ORDER BY shipping_count DESC, shipping_amount DESC\n\t\t\t\t LIMIT 5";
$results_top_shipping_method = $wpdb->get_results($query_top_shipping_method, 'ARRAY_A');
$rows_top_shipping_method = $wpdb->num_rows;
}
$top_shipping_method = array();
if ($rows_top_shipping_method > 0) {
foreach ($results_top_shipping_method as &$result_top_shipping_method) {
$top_shipping_method[] = $result_top_shipping_method['shipping_name'];
$result_top_shipping_method['shipping_method_sales_display'] = $sr_currency_symbol . sr_number_format($result_top_shipping_method['shipping_amount'], $sr_decimal_places);
$result_top_shipping_method['shipping_method_sales_percent'] = sr_number_format($result_top_shipping_method['sales_total'] / $total_monthly_sales * 100, $sr_decimal_places) . '%';
}
if (!empty($top_shipping_method)) {
$top_shipping_method_imploded = "'" . implode("','", $top_shipping_method) . "'";
$top_shipping_method_cond = 'AND order_items.order_item_name IN (' . $top_shipping_method_imploded . ')';
$top_shipping_method_order_by = "ORDER BY FIND_IN_SET(order_items.order_item_name,'" . implode(",", $top_shipping_method) . "')";
} else {
$top_shipping_method_cond = '';
$top_shipping_method_order_by = '';
}
//Query to get the Top 5 Shipping Methods graph related data
$query_top_shipping_method_graph = "SELECT COUNT( order_items.order_item_name ) AS shipping_count,\n\t\t\t\t\t \t\t\t\t\t\t\tSUM(order_itemmeta.meta_value) AS shipping_amount,\n\t\t\t\t\t \t\t\t\t\t\t\torder_items.order_item_name AS shipping_name,\n\t\t\t\t \t\t\t\t\t\t\t\t{$select}\n\t\t\t\t\t\t\t FROM `{$wpdb->prefix}posts` AS posts\n\t\t\t\t\t\t \t\tJOIN {$wpdb->prefix}woocommerce_order_items as order_items ON ( posts.ID = order_items.order_id )\n\t\t\t\t\t\t \t\tJOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_itemmeta \n\t\t\t\t\t\t \t\t\tON (order_items.order_item_id = order_itemmeta.order_item_id \n\t\t\t\t\t\t \t\t\t\tAND order_itemmeta.meta_key IN ('cost') )\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t{$terms_post_join}\n\t\t\t\t\t\t WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t\t\t\t AND order_items.order_item_type IN ('shipping')\n\t\t\t\t\t\t {$terms_post_cond}\n\t\t\t\t\t\t {$top_shipping_method_cond}\n\t\t\t\t\t GROUP BY shipping_name, {$group_by}\n\t\t\t\t\t {$top_shipping_method_order_by}";
$results_top_shipping_method_graph = $wpdb->get_results($query_top_shipping_method_graph, 'ARRAY_A');
$rows_top_shipping_method_graph = $wpdb->num_rows;
$cumm_shipping_method_temp = $date_series;
$cumm_shipping_method_sales = array();
if ($rows_top_shipping_method_graph > 0) {
//call function for graph data formatting
sr_graph_data_formatting($top_shipping_method_graph_data, $results_top_shipping_method_graph, $group_by, 'shipping_name', 'shipping_method_sales_amt', 'shipping_method_sales_count', 'shipping_amount', 'shipping_count');
}
$cumm_top_shipping_method_graph_data = array();
$index = 0;
$max_values = array();
if (!empty($top_shipping_method_graph_data)) {
foreach ($top_shipping_method_graph_data as $top_shipping_method_graph_data1) {
$cumm_top_shipping_method_amt_graph_data[$index] = array();
$temp_shipping_method_sales_amt = array();
$temp_shipping_method_sales_count = array();
$cumm_date_amt = $date_series;
$cumm_date_count = $date_series;
$max_amt = 0;
$max_count = 0;
for ($j = 0; $j < sizeof($top_shipping_method_graph_data1); $j++) {
if ($group_by == "display_date_time") {
$cumm_date_amt[$top_shipping_method_graph_data1[$j]['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $top_shipping_method_graph_data1[$j]['display_time'];
$cumm_date_count[$top_shipping_method_graph_data1[$j]['comp_time']]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . $top_shipping_method_graph_data1[$j]['display_time'];
$cumm_date_amt[$top_shipping_method_graph_data1[$j]['comp_time']]['sales'] = floatval($top_shipping_method_graph_data1[$j]['shipping_method_sales_amt']);
$cumm_date_count[$top_shipping_method_graph_data1[$j]['comp_time']]['sales'] = floatval($top_shipping_method_graph_data1[$j]['shipping_method_sales_count']);
} else {
$cumm_date_amt[$top_shipping_method_graph_data1[$j][$group_by]]['sales'] = floatval($top_shipping_method_graph_data1[$j]['shipping_method_sales_amt']);
$cumm_date_count[$top_shipping_method_graph_data1[$j][$group_by]]['sales'] = floatval($top_shipping_method_graph_data1[$j]['shipping_method_sales_count']);
}
//Shipping Method Sales Amt
if ($top_shipping_method_graph_data1[$j]['shipping_method_sales_amt'] > $max_amt) {
$max_amt = floatval($top_shipping_method_graph_data1[$j]['shipping_method_sales_amt']);
}
//Shipping Method Sales Count
if ($top_shipping_method_graph_data1[$j]['shipping_method_sales_count'] > $max_count) {
$max_count = floatval($top_shipping_method_graph_data1[$j]['shipping_method_sales_count']);
}
}
foreach ($cumm_date_amt as $cumm_date_amt1) {
$temp_shipping_method_sales_amt[] = $cumm_date_amt1;
}
foreach ($cumm_date_count as $cumm_date_count1) {
$temp_shipping_method_sales_count[] = $cumm_date_count1;
}
$results_top_shipping_method[$index]['graph_data_sales_amt'] = $temp_shipping_method_sales_amt;
$results_top_shipping_method[$index]['max_value_sales_amt'] = $max_amt;
$results_top_shipping_method[$index]['graph_data_sales_count'] = $temp_shipping_method_sales_count;
$results_top_shipping_method[$index]['max_value_sales_count'] = $max_count;
$results_top_shipping_method[$index]['shipping_method'] = $results_top_shipping_method[$index]['shipping_name'];
$index++;
}
}
}
//Sales Funnel
$cumm_sales_funnel = array();
//Query to get the total products added to cart
if ($rows_total_cart > 0) {
$query_products_added_cart = "SELECT SUM(quantity) as total_prod_added_cart\n\t\t\t\t\t\t \t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items\n\t\t\t\t\t\t \t\t\tWHERE abandoned_cart_time BETWEEN '" . strtotime($start_date) . "' AND '" . strtotime($end_date_query) . "'";
$total_products_added_cart = $wpdb->get_col($query_products_added_cart);
$cumm_sales_funnel['total_cart_count'] = floatval($total_cart_count[0]);
$cumm_sales_funnel['total_products_added_cart'] = floatval($total_products_added_cart[0]);
} else {
$cumm_sales_funnel['total_cart_count'] = 0;
$cumm_sales_funnel['total_products_added_cart'] = 0;
}
//Fix for woo22
if (!empty($sr_is_woo22) && $sr_is_woo22 == 'true') {
$terms_post_join = '';
} else {
$terms_post_join = ' JOIN ' . $wpdb->prefix . 'term_relationships AS term_relationships ON (term_relationships.object_id = posts.ID AND posts.post_status = "publish")';
}
//Query to get the placed order ids
$query_orders_placed = "SELECT DISTINCT id as completed_order_ids\n\t \t\t\t\t\t\t\tFROM {$wpdb->prefix}posts AS posts\n\t \t{$terms_post_join}\n\t\t WHERE posts.post_type IN ('shop_order')\n\t\t AND (posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}')";
$results_orders_placed = $wpdb->get_col($query_orders_placed);
$rows_orders_placed = $wpdb->num_rows;
if ($rows_orders_placed > 0) {
$cumm_sales_funnel['orders_placed_count'] = floatval(sizeof($results_orders_placed));
//Query to get the count of the products purchased
$query_products_purchased = "SELECT SUM(quantity) as query_products_sold\n\t \t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_order_items\n\t \t\t\t\t\t\t\tWHERE order_id IN (" . implode(",", $results_orders_placed) . ")";
$results_products_purchased = $wpdb->get_col($query_products_purchased);
$rows_products_purchased = $wpdb->num_rows;
$cumm_sales_funnel['products_purchased_count'] = floatval($results_products_purchased[0]);
} else {
$cumm_sales_funnel['orders_placed_count'] = 0;
$cumm_sales_funnel['products_purchased_count'] = 0;
}
//Fix for woo22
if (!empty($sr_is_woo22) && $sr_is_woo22 == 'true') {
$terms_post_join = '';
$terms_post_cond = " AND posts.post_status IN ('wc-completed')";
} else {
$query_terms = "SELECT term_taxonomy.term_taxonomy_id\n\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}term_taxonomy AS term_taxonomy \n\t JOIN {$wpdb->prefix}terms AS terms \n\t ON term_taxonomy.term_id = terms.term_id\n\t \t\tWHERE terms.name IN ('completed')";
$terms_post = $wpdb->get_col($query_terms);
$rows_terms_post = $wpdb->num_rows;
if ($rows_terms_post > 0) {
$terms_taxonomy_ids = implode(",", $terms_post);
$terms_post_join = ' JOIN ' . $wpdb->prefix . 'term_relationships AS term_relationships ON (term_relationships.object_id = posts.ID AND posts.post_status = "publish")';
$cond_terms_post = ' term_relationships.term_taxonomy_id IN (' . $terms_taxonomy_ids . ')';
}
}
//Query to get the completed order ids
$query_orders_completed = "SELECT DISTINCT id as completed_order_ids\n\t \t\t\t\t\t\t\tFROM {$wpdb->prefix}posts AS posts\n\t \t{$terms_post_join}\n\t\t WHERE posts.post_type IN ('shop_order')\n\t\t \t{$terms_post_cond}\n\t\t AND (posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}')";
$results_orders_completed = $wpdb->get_col($query_orders_completed);
$rows_orders_completed = $wpdb->num_rows;
if ($rows_orders_completed > 0) {
$cumm_sales_funnel['orders_completed_count'] = floatval(sizeof($results_orders_completed));
//Query to get the count of the products sold
$query_products_sold = "SELECT SUM(quantity) as query_products_sold\n\t \t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_order_items\n\t \t\t\t\t\t\t\tWHERE order_id IN (" . implode(",", $results_orders_completed) . ")";
$results_products_sold = $wpdb->get_col($query_products_sold);
$rows_products_sold = $wpdb->num_rows;
$cumm_sales_funnel['products_sold_count'] = floatval($results_products_sold[0]);
} else {
$cumm_sales_funnel['orders_completed_count'] = 0;
$cumm_sales_funnel['products_sold_count'] = 0;
}
if (isset($post['option'])) {
// Condition to get the data when the Top Products Toggle button is clicked
$results[0] = $cumm_top_prod_graph_data;
} else {
$results[0] = $monthly_sales;
$results[1] = $total_monthly_sales;
$results[2] = $results_top_prod;
$results[3] = $results_cumm_top_cust;
if ($total_monthly_sales == 0) {
$results[4] = floatval(0);
} else {
if ($tot_cumm_orders == 0) {
$results[4] = floatval($total_monthly_sales);
} else {
$results[4] = floatval($total_monthly_sales / $tot_cumm_orders);
}
}
if ($tot_cumm_orders_qty == 0) {
$results[5] = floatval(0);
} else {
if ($total_cumm_cust_count == 0) {
$results[5] = floatval($tot_cumm_orders_qty);
} else {
$results[5] = floatval($tot_cumm_orders_qty / $total_cumm_cust_count);
}
}
$results[6] = floatval($max_sales + 100);
if ($total_discount_sales > 0) {
$results[7] = $cumm_discount_sales;
$results[8] = $total_discount_sales;
} else {
$results[7] = '';
$results[8] = '';
}
$results[9] = $results_cumm_coupon_count;
$results[10] = floatval($max_discount_total + 100);
$results[11] = $sr_per_order_containing_coupons;
$results[12] = $results_top_payment_gateway;
$results[13] = $tax_data;
// $results [14] = $results_top_abandoned_products;
$results[14] = json_decode(sr_get_abandoned_products($start_date, $end_date_query, $group_by, $sr_currency_symbol, $sr_decimal_places, $date_series, $select_top_abandoned_prod, "LIMIT 5", $terms_taxonomy_ids, $sr_is_woo22), true);
$results[15] = $min_abandoned_date != '' && $min_abandoned_date <= $start_date ? $cumm_cart_abandoned_rate : '';
$results[16] = $min_abandoned_date != '' && $min_abandoned_date <= $start_date ? $cumm_sales_funnel : '';
$results[17] = $cumm_sales_billing_country_values;
$results[18] = $cumm_sales_billing_country_tooltip;
$results[19] = $results_top_shipping_method;
}
return $results;
}
function sr_query_sales($cumm_dates, $date_series, $post)
{
$params = !empty($post['params']) ? $post['params'] : array();
if (!wp_verify_nonce($params['security'], 'smart-reporter-security')) {
die('Security check');
}
global $wpdb, $sr_text_domain;
$returns = array();
// Initialize the return data
$returns['chart'] = $returns['kpi'] = array();
$returns['meta'] = array('start_date' => $cumm_dates['cp_start_date'], 'end_date' => $cumm_dates['cp_end_date'], 's_link' => admin_url() . 'edit.php?post_type=shop_order&source=sr&sdate=' . $cumm_dates['cp_start_date'] . '&edate=' . $cumm_dates['cp_end_date']);
$returns['chart']['period'] = $date_series;
$periods_count = count($returns['chart']['period']);
$p2i = array_flip($returns['chart']['period']);
$time_str = $cumm_dates['format'] == '%H' ? ':00:00' : '';
if (!empty($post['cmd']) && ($post['cmd'] == 'cumm_sales' || $post['cmd'] == 'sr_summary')) {
$date_col = $cumm_dates['format'] == '%H' ? 'created_time' : 'created_date';
$chart_keys = array('sales', 'orders', 'discount');
$payment_methods = $shipping_methods = array();
// For each payment and shipping method...
foreach ((array) WC_Payment_Gateways::instance()->get_available_payment_gateways() as $key => $value) {
$chart_keys[] = 'pm_' . $key . '_sales';
$chart_keys[] = 'pm_' . $key . '_orders';
$returns['kpi']['pm'][$key] = array('title' => __($value->get_title(), $sr_text_domain), 'sales' => 0, 'orders' => 0, 's_link' => '&s=' . $value->get_title() . '&s_col=payment_method&s_val=' . $key);
}
foreach ((array) WC_Shipping::instance()->get_shipping_methods() as $key => $value) {
$chart_keys[] = 'sm_' . $key . '_sales';
$chart_keys[] = 'sm_' . $key . '_orders';
$returns['kpi']['sm'][$key] = array('title' => __($value->get_title(), $sr_text_domain), 'sales' => 0, 'orders' => 0, 's_link' => '&s=' . $value->get_title() . '&s_col=shipping_method&s_val=' . $key);
}
// Initialize chart data to 0
foreach ($chart_keys as $value) {
$returns['chart'][$value] = array_fill(0, $periods_count, 0);
}
// KPIs are single item stats.. init for current and last period (lp_)
$kpis = array('sales', 'refunds', 'orders', 'qty', 'discount', 'tax', 'shipping', 'shipping_tax');
foreach ($kpis as $value) {
$returns['kpi'][$value] = 0;
$returns['kpi']['lp_' . $value] = 0;
}
// Bring in grouped results for sales, discounts etc - then loop and process
// LAST_PERIOD is special 'period' value for comparing current period data
// with previous period
$query = $wpdb->prepare("SELECT 'LAST_PERIOD' as period, \n\t\t\t\t\t\t\t\t\t\t\tSUM( CASE WHEN type = 'shop_order' AND status != 'wc-refunded' THEN 1 ELSE 0 END ) as orders, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE total END), 0) AS sales,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN total \n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN type = 'shop_order_refund' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE 0 END), 0) AS refunds,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE qty END), 0) AS qty,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE discount+cart_discount END), 0) AS discount,\n\t\t\t\t\t\t\t\t\t\t0 AS tax,\n\t\t\t\t\t\t\t\t\t\t0 AS shipping,\n\t\t\t\t\t\t\t\t\t\t0 AS shipping_tax,\n\t\t\t\t\t\t\t\t\t '' AS payment_method, \n\t\t\t\t\t\t\t\t\t '' AS shipping_method \n\t\t\t\t\t\t\t\t\tFROM `{$wpdb->prefix}woo_sr_orders` \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-refunded')\n\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\tSELECT concat(DATE_FORMAT(" . $date_col . ", '%s'), '" . $time_str . "') AS period,\n\t\t\t\t\t\t\t\t\t\t\tSUM( CASE WHEN type = 'shop_order' AND status != 'wc-refunded' THEN 1 ELSE 0 END ) as orders, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE total END), 0) AS sales,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN total \n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN type = 'shop_order_refund' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE 0 END), 0) AS refunds,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE qty END), 0) AS qty,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE discount+cart_discount END), 0) AS discount,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE tax END), 0) AS tax,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE shipping END), 0) AS shipping,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE shipping_tax END), 0) AS shipping_tax,\n\t\t\t\t\t\t\t\t\t payment_method, \n\t\t\t\t\t\t\t\t\t shipping_method \n\t\t\t\t\t\t\t\t\tFROM `{$wpdb->prefix}woo_sr_orders` \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-refunded') \n\t\t\t\t\t\t\t\t\tGROUP BY period, payment_method, shipping_method", $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
// The first row will always be last period data
$row = array_shift($results);
foreach ($kpis as $value) {
$returns['kpi']['lp_' . $value] = $row[$value];
}
// Loop and total up values now
foreach ($results as $row) {
if (!array_key_exists($row['period'], $p2i)) {
error_log('Smart Reporter: Invalid value for "period" in DB results - ' . $row['period']);
continue;
}
// Total up sales, refunds, qty etc...
foreach ($kpis as $key) {
$returns['kpi'][$key] += $row[$key];
}
// Index of this period - this will be used to position different chart data at this period's index
$i = $p2i[$row['period']];
// Set values in charts - for data other than payment / shipping methods
foreach ($chart_keys as $key) {
if (substr($key, 1, 2) != 'm_') {
// will match pm_ and sm_ both in single condition
$returns['chart'][$key][$i] += $row[$key];
}
}
// Set values for shipping and payment methods
foreach (array('pm' => $row['payment_method'], 'sm' => $row['shipping_method']) as $type => $method) {
foreach (array('sales', 'orders') as $f) {
$key = $type . '_' . $method . '_' . $f;
if (array_key_exists($key, $returns['chart'])) {
$row[$f] = $type == 'sm' && $f == 'sales' ? $row['shipping'] : $row[$f];
$returns['chart'][$key][$i] += $row[$f];
$returns['kpi'][$type][$method][$f] += $row[$f];
}
}
}
}
// sorting the pm and sm by sales
$returns['kpi']['pm'] = array_slice(sr_multidimensional_array_sort($returns['kpi']['pm'], 'sales', 'DESC'), 0, 5);
$returns['kpi']['sm'] = array_slice(sr_multidimensional_array_sort($returns['kpi']['sm'], 'sales', 'DESC'), 0, 5);
}
}
if (!empty($post['cmd']) && ($post['cmd'] == 'cumm_cust_prod' || $post['cmd'] == 'sr_summary')) {
$chart_keys = array();
$date_col = $cumm_dates['format'] == '%H' ? 'order_time' : 'order_date';
// KPIs are single item stats.. init for current and last period (lp_)
$kpis = array('car', 'carts', 'carts_prod', 'orders', 'orders_prod', 'corders', 'corders_prod', 'aipc', 'swc');
foreach ($kpis as $value) {
$returns['kpi'][$value] = 0;
if ($value == 'car' || $value == 'aipc' || $value == 'swc') {
$returns['kpi']['lp_' . $value] = 0;
}
}
// ###############################
// Top Customers
// ###############################
$query = $wpdb->prepare("SELECT MAX(customer_name) as name,\n\t\t\t\t\t\t\t\t\t\tMAX(billing_email) as email, \n\t\t\t\t\t\t\t\t\t\tCASE WHEN user_id > 0 THEN user_id ELSE billing_email END as user, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM(total), 0) AS sales \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold') \n\t\t\t\t\t\t\t\t\tGROUP BY user \n\t\t\t\t\t\t\t\t\tORDER BY sales DESC\n\t\t\t\t\t\t\t\t\tLIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$returns['kpi']['top_cust'] = array();
foreach ($results as $row) {
$returns['kpi']['top_cust'][] = array('name' => $row['name'], 'email' => $row['email'], 'sales' => $row['sales'], 's_link' => '&s=' . $row['email'] . ($row['user'] > 0 ? '&s_col=user_id&s_val=' . $row['user'] : '******' . $row['email']));
}
}
// ###############################
// Billing Countries
// ###############################
$query = $wpdb->prepare("SELECT SUM( CASE WHEN type = 'shop_order' THEN 1 ELSE 0 END ) as orders, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM(total), 0) AS sales,\n\t\t\t\t\t\t\t\t\t\tbilling_country \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold') \n\t\t\t\t\t\t\t\t\tGROUP BY billing_country", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$returns['kpi']['billing_country'] = array();
$returns['kpi']['billing_country']['sales'] = $returns['kpi']['billing_country']['orders'] = array();
foreach ($results as $row) {
if (empty($row['billing_country'])) {
continue;
}
$returns['kpi']['billing_country']['sales'][$row['billing_country']] = $row['sales'];
$returns['kpi']['billing_country']['orders'][$row['billing_country']] = $row['orders'];
}
}
// ###############################
// Top Products
// ###############################
// Confirm the handling of the partial refunds
$t_p_ids = $t_v_ids = array();
$returns['kpi']['top_prod'] = array('sales' => array(), 'qty' => array());
$tp_results = array();
$query = $wpdb->prepare("SELECT oi.product_id as product_id, \n\t\t\t\t\t\t\t\t\t\t\toi.variation_id as variation_id,\n\t\t\t\t\t\t\t\t\t\t SUM( oi.total ) as sales,\n\t\t\t\t\t\t\t\t\t\t SUM( oi.qty ) as qty \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items oi\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\toi.order_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND oi.order_is_sale = 1\n\t\t\t\t\t\t\t\t\tAND (oi.type = 'S' OR oi.type = 'R')\n\t\t\t\t\t\t\t\t\tGROUP BY product_id, variation_id\n\t\t\t\t\t\t\t\t\tORDER BY sales DESC\n\t\t\t\t\t\t\t\t\tLIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$id = $row['product_id'];
$t_p_ids[] = $id;
if (!empty($row['variation_id'])) {
$t_v_ids[$row['variation_id']] = $row['product_id'];
$id = $row['variation_id'];
}
$returns['kpi']['top_prod']['sales'][$id] = array('title' => '-', 'sales' => $row['sales'], 'qty' => $row['qty']);
}
$keys = array_keys($returns['kpi']['top_prod']['sales']);
// array_walk($keys, function(&$value, $key) { $value = 'tps_'. $value; });
array_walk($keys, 'format_top_prod_keys', 'tps_');
$chart_keys = count($keys) > 0 ? array_merge($chart_keys, $keys) : $chart_keys;
$prod_cond = !empty($t_p_ids) ? ' AND ( (product_id IN (' . implode(",", $t_p_ids) . ') AND variation_id = 0)' : '';
$prod_cond .= !empty($t_v_ids) ? (!empty($prod_cond) ? ' OR ' : ' AND ( ') . 'variation_id IN (' . implode(",", array_keys($t_v_ids)) . ')' : '';
$prod_cond .= !empty($prod_cond) ? ' ) ' : '';
// Query to get the dates wise sales for the top products
$query = $wpdb->prepare("SELECT CASE WHEN variation_id > 0 THEN variation_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE product_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tEND AS tps_id,\n\t\t\t\t\t\t\t\t\t\t\t\tconcat(DATE_FORMAT(" . $date_col . ", '%s'), '" . $time_str . "') AS period,\n\t\t\t\t\t\t\t\t\t\t\t\tSUM( total ) as sales \n\t\t\t\t\t\t\t\t\t\tFROM wp_woo_sr_order_items \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\torder_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\t\tAND order_is_sale = 1\n\t\t\t\t\t\t\t\t\t\tAND (type = 'S' OR type = 'R')\n\t\t\t\t\t\t\t\t\t\t" . $prod_cond . "\n\t\t\t\t\t\t\t\t\t\tGROUP BY order_date, tps_id", $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$tp_results = $wpdb->get_results($query, 'ARRAY_A');
}
$query = $wpdb->prepare("SELECT oi.product_id as product_id,\n\t\t\t\t\t\t\t\t\t\t\toi.variation_id as variation_id, \n\t\t\t\t\t\t\t\t\t\t SUM( oi.total ) as sales,\n\t\t\t\t\t\t\t\t\t\t SUM( oi.qty ) as qty \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items oi\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\toi.order_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND oi.order_is_sale = 1\n\t\t\t\t\t\t\t\t\tAND (oi.type = 'S' OR oi.type = 'R')\n\t\t\t\t\t\t\t\t\tGROUP BY oi.product_id\n\t\t\t\t\t\t\t\t\tORDER BY qty DESC\n\t\t\t\t\t\t\t\t\tLIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$v_ids = array();
foreach ($results as $row) {
$id = $row['product_id'];
$t_p_ids[] = $id;
if (!empty($row['variation_id'])) {
$t_v_ids[$row['variation_id']] = $row['product_id'];
$v_ids[] = $row['variation_id'];
$id = $row['variation_id'];
}
$returns['kpi']['top_prod']['qty'][$id] = array('title' => '-', 'sales' => $row['sales'], 'qty' => $row['qty']);
}
$keys = array_keys($returns['kpi']['top_prod']['qty']);
array_walk($keys, 'format_top_prod_keys', 'tpq_');
$chart_keys = count($keys) > 0 ? array_merge($chart_keys, $keys) : $chart_keys;
$prod_cond = !empty($t_p_ids) ? ' AND ( (product_id IN (' . implode(",", $t_p_ids) . ') AND variation_id = 0)' : '';
$prod_cond .= !empty($v_ids) ? (!empty($prod_cond) ? ' OR ' : ' AND ( ') . 'variation_id IN (' . implode(",", array_keys($v_ids)) . ')' : '';
$prod_cond .= !empty($prod_cond) ? ' ) ' : '';
// Query to get the dates wise qty for the top products
$query = $wpdb->prepare("SELECT CASE WHEN variation_id > 0 THEN variation_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE product_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tEND AS tpq_id,\n\t\t\t\t\t\t\t\t\t\t\t\tconcat(DATE_FORMAT(" . $date_col . ", '%s'), '" . $time_str . "') AS period,\n\t\t\t\t\t\t\t\t\t\t\t\tSUM( qty ) as qty\n\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\torder_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\t\tAND order_is_sale = 1\n\t\t\t\t\t\t\t\t\t\tAND (type = 'S' OR type = 'R')\n\t\t\t\t\t\t\t\t\t\t" . $prod_cond . "\n\t\t\t\t\t\t\t\t\t\tGROUP BY order_date, tpq_id", $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
$tp_results = count($results) > 0 ? array_merge($tp_results, $results) : $tp_results;
}
// Initialize chart data to 0
if (count($tp_results) > 0) {
foreach ($chart_keys as $value) {
$returns['chart'][$value] = array_fill(0, $periods_count, 0);
}
}
// Loop and assign the chart data
if (count($tp_results) > 0) {
foreach ($tp_results as $row) {
if (!array_key_exists($row['period'], $p2i)) {
error_log('Smart Reporter: Invalid value for "period" in DB results - ' . $row['period']);
continue;
}
// Index of this period - this will be used to position different chart data at this period's index
$i = $p2i[$row['period']];
// Set values in charts
if (!empty($row['tps_id'])) {
$returns['chart']['tps_' . $row['tps_id']][$i] += !empty($row['sales']) ? $row['sales'] : 0;
} else {
if (!empty($row['tpq_id'])) {
$returns['chart']['tpq_' . $row['tpq_id']][$i] += !empty($row['qty']) ? $row['qty'] : 0;
}
}
}
}
// ###############################
// Cart Abandonment Rate
// ###############################
$query = $wpdb->prepare("SELECT CASE \n\t\t\t\t\t\t\t\t\t\t\t\tWHEN last_update_time >= unix_timestamp('%s') THEN 'C' \n\t\t\t\t\t\t\t\t\t\t\t\tWHEN last_update_time >= unix_timestamp('%s') THEN 'L' \n\t\t\t\t\t\t\t\t\t\t\tEND as period,\n\t\t\t\t\t\t\t\t\t\tcount(distinct( CASE WHEN cart_is_abandoned = 0 THEN concat('O#', order_id) ELSE concat('C#', cart_id) END ) ) as count, \n\t\t\t\t\t\t\t\t\t\tSUM(qty) as items, \n\t\t\t\t\t\t\t\t\t\tcart_is_abandoned \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_cart_items\n\t\t\t\t\t\t\t\t\tWHERE (last_update_time between unix_timestamp('%s') and unix_timestamp('%s')) \n\t\t\t\t\t\t\t\t\t\tOR (last_update_time between unix_timestamp('%s') and unix_timestamp('%s'))\n\t\t\t\t\t\t\t\t\tGROUP BY period, cart_is_abandoned", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
if ($row['period'] == 'C') {
$c_acarts = $row['cart_is_abandoned'] == 1 ? $row['count'] : 0;
$returns['kpi']['carts'] += $row['count'];
$returns['kpi']['carts_prod'] += $row['items'];
} else {
$l_acarts = $row['cart_is_abandoned'] == 1 ? $row['count'] : 0;
$l_carts += $row['count'];
}
}
$returns['kpi']['car'] = !empty($returns['kpi']['carts']) ? $c_acarts / $returns['kpi']['carts'] : $c_acarts;
$returns['kpi']['lp_car'] = !empty($l_carts) ? $l_acarts / $l_carts : $l_acarts;
}
// ###############################
// Top Abandoned Products
// ###############################
$returns['kpi']['top_aprod'] = array();
// get the top abandoned products
$r_aprod = sr_get_abandoned_products(array('cp_start_date' => $cumm_dates['cp_start_date'], 'cp_end_date' => $cumm_dates['cp_end_date'], 'security' => $params['security'], 'limit' => 'LIMIT 5'));
$returns['kpi']['top_aprod'] = $r_aprod['a_prod'];
if (count($returns['kpi']['top_aprod']) > 0) {
// initializing the chart data
foreach (array_keys($returns['kpi']['top_aprod']) as $key) {
$returns['chart']['tapq_' . $key] = array_fill(0, $periods_count, 0);
}
// code for getting the chart data
if ($r_aprod['a_flag'] == true) {
$query = $wpdb->prepare("SELECT product_id as id,\n\t\t\t\t\t\t\t\t\t\t\t\t\tSUM(qty) as aqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(FROM_UNIXTIME(last_update_time), '%s') AS period\n\t\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_cart_items\n\t\t\t\t\t\t\t\t\t\t\tWHERE cart_is_abandoned = 1\n\t\t\t\t\t\t\t\t\t\t\t\tAND last_update_time between unix_timestamp('%s') and unix_timestamp('%s')\n\t\t\t\t\t\t\t\t\t\t\t\tAND product_id IN (" . implode(",", array_keys($returns['kpi']['top_aprod'])) . ")\n\t\t\t\t\t\t\t\t\t\t\tGROUP BY period, id", $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
if (!array_key_exists($row['period'], $p2i)) {
error_log('Smart Reporter: Invalid value for "period" in DB results - ' . $row['period']);
continue;
}
// Index of this period - this will be used to position different chart data at this period's index
$i = $p2i[$row['period']];
// Set values in charts
$returns['chart']['tapq_' . $row['id']][$i] += $row['aqty'];
}
}
}
//get the variation ids
if (count(array_keys($returns['kpi']['top_aprod'])) > 0) {
$query = "SELECT id, post_parent\n\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}posts\n\t\t\t\t\t\t\t\tWHERE post_parent > 0\n\t\t\t\t\t\t\t\t\tAND id IN (" . implode(",", array_keys($returns['kpi']['top_aprod'])) . ")\n\t\t\t\t\t\t\t\tGROUP BY id";
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$t_v_ids[$row['id']] = $row['post_parent'];
$t_p_ids[] = $row['post_parent'];
}
$t_p_ids = array_merge($t_p_ids, array_keys(array_diff_key($returns['kpi']['top_aprod'], $t_v_ids)));
} else {
$t_p_ids = array_merge($t_p_ids, array_keys($returns['kpi']['top_aprod']));
}
}
}
// Code for getting the product title
$data = sr_get_prod_title(array(&$returns['kpi']['top_prod']['sales'], &$returns['kpi']['top_prod']['qty'], &$returns['kpi']['top_aprod']), array('t_p_ids' => $t_p_ids, 't_v_ids' => $t_v_ids, 'security' => $params['security']));
// ###############################
// Top Coupons
// ###############################
$returns['kpi']['top_coupons'] = array();
$query = $wpdb->prepare("SELECT COUNT( oi.order_item_name ) AS count,\n\t\t\t\t\t\t\t\t\t\tSUM(oim.meta_value) AS amt,\n\t\t\t\t\t\t\t\t\t\toi.order_item_name AS name\n\t\t\t\t\t FROM {$wpdb->prefix}woo_sr_orders AS so\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_items as oi ON ( so.order_id = oi.order_id )\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_itemmeta as oim \n\t\t\t\t\t \t\tON (oi.order_item_id = oim.order_item_id \n\t\t\t\t\t \t\t\t\tAND oim.meta_key = 'discount_amount' )\n\t\t\t\t\t WHERE so.created_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t AND so.status in ('wc-completed', 'wc-processing', 'wc-on-hold')\n\t\t\t\t\t AND oi.order_item_type = 'coupon'\n\t\t\t\t\t GROUP BY oi.order_item_name\n\t\t\t\t\t ORDER BY count DESC, amt DESC\n\t\t\t\t\t LIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$returns['kpi']['top_coupons'][] = array('title' => $row['name'], 'sales' => $row['amt'], 'count' => $row['count'], 's_link' => '&s=' . $row['name'] . '&s_col=order_item_name&s_val=' . $row['name']);
}
}
// ###############################
// Sales Funnel
// ###############################
$query = $wpdb->prepare("SELECT IFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS orders,\n\t\t \t\t\t\t\t\t\tIFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS lp_orders,\n\t\t\t\t\t\t\t\t\t\tIFNULL( SUM( CASE WHEN so.created_date >= '%s' THEN soim.qty END ), 0) AS orders_prod,\n\t\t\t\t\t\t\t\t\t\tIFNULL( SUM( CASE WHEN so.created_date >= '%s' THEN soim.qty END ), 0) AS lp_orders_prod,\n\t\t\t\t\t\t\t\t\t\tIFNULL( COUNT( DISTINCT(CASE WHEN so.created_date >= '%s' AND so.status = 'wc-completed' THEN so.order_id END) ), 0) AS corders,\n\t\t\t\t\t\t\t\t\t\tIFNULL( SUM( CASE WHEN so.created_date >= '%s' AND so.status = 'wc-completed' THEN soim.qty END ), 0) AS corders_prod\n\t\t\t\t\t FROM {$wpdb->prefix}woo_sr_orders AS so\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woo_sr_order_items as soim\n\t\t\t\t\t \t\tON (so.order_id = soim.order_id\n\t\t\t\t \t\t\t\tAND so.type = 'shop_order'\n\t\t\t\t \t\t\t\tAND soim.order_is_sale = 1\n\t\t\t\t \t\t\t\tAND soim.type = 'S')\n\t\t\t\t\t WHERE (so.created_date BETWEEN '%s' AND '%s'\n\t\t\t\t \t\tOR so.created_date BETWEEN '%s' AND '%s')", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
$lp_orders = $lp_orders_prod = 0;
if (count($results) > 0) {
foreach ($kpis as $value) {
$returns['kpi'][$value] = !empty($results[0][$value]) ? $results[0][$value] : $returns['kpi'][$value];
}
$lp_orders = $results[0]['lp_orders'];
$lp_orders_prod = $results[0]['lp_orders_prod'];
}
// ###############################
// Sales With Coupons
// ###############################
$query = $wpdb->prepare("SELECT IFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS cp_co,\n\t\t \t\t\t\t\t\t\t\tIFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS lp_co\n\t\t\t \t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders AS so\n\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_items as oi ON ( oi.order_id = so.order_id )\n\t\t\t\t WHERE (so.created_date BETWEEN '%s' AND '%s'\n\t\t\t\t \t\tOR so.created_date BETWEEN '%s' AND '%s')\n\t\t\t\t\t\t AND so.status in ('wc-completed', 'wc-processing', 'wc-on-hold')\n\t\t\t\t\t\t AND oi.order_item_type = 'coupon'", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$returns['kpi']['swc'] = !empty($cp_orders) ? $results[0]['cp_co'] / $returns['kpi']['orders'] * 100 : 0;
$returns['kpi']['lp_swc'] = !empty($lp_orders) ? $results[0]['lp_co'] / $lp_orders * 100 : 0;
}
// ###############################
// Total Customers
// ###############################
$query = $wpdb->prepare("SELECT \n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id > 0 AND created_date >= '%s' THEN user_id END ) ), 0) AS cust,\n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id > 0 AND created_date >= '%s' THEN user_id END ) ), 0) AS old_cust,\n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id = 0 AND created_date >= '%s' THEN billing_email END ) ), 0) AS guests,\n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id = 0 AND created_date >= '%s' THEN billing_email END ) ), 0) AS old_guests\n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\t\t\t\tWHERE (created_date BETWEEN '%s' AND '%s' \n\t\t\t\t\t\t\t\t\t\t\tOR created_date BETWEEN '%s' AND '%s' )\n\t\t\t\t\t\t\t\t\t\tAND type = 'shop_order'\n\t\t\t\t\t\t\t\t\t\tAND status IN ('wc-completed', 'wc-processing', 'wc-on-hold')", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$cp_cust = $results[0]['cust'] + $results[0]['guests'];
$lp_cust = $results[0]['old_cust'] + $results[0]['old_guests'];
$returns['kpi']['aipc'] = !empty($cp_cust) ? $returns['kpi']['orders_prod'] / $cp_cust : $cp_cust;
$returns['kpi']['lp_aipc'] = !empty($lp_cust) ? $lp_orders_prod / $lp_cust : $lp_cust;
}
}
if (!empty($post['cmd']) && $post['cmd'] == 'aprod_export') {
$r_aprod = sr_get_abandoned_products(array('cp_start_date' => $cumm_dates['cp_start_date'], 'cp_end_date' => $cumm_dates['cp_end_date'], 'security' => $params['security'], 'limit' => ''));
//get the variation ids
$t_p_ids = $t_v_ids = array();
if (count(array_keys($r_aprod['a_prod'])) > 0) {
$query = "SELECT id, post_parent\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}posts\n\t\t\t\t\t\t\tWHERE post_parent > 0\n\t\t\t\t\t\t\t\tAND id IN (" . implode(",", array_keys($r_aprod['a_prod'])) . ")\n\t\t\t\t\t\t\tGROUP BY id";
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$t_v_ids[$row['id']] = $row['post_parent'];
$t_p_ids[] = $row['post_parent'];
}
$t_p_ids = array_merge($t_p_ids, array_keys(array_diff_key($r_aprod['a_prod'], $t_v_ids)));
} else {
$t_p_ids = array_merge($t_p_ids, array_keys($r_aprod['a_prod']));
}
sr_get_prod_title(array(&$r_aprod['a_prod']), array('t_p_ids' => $t_p_ids, 't_v_ids' => $t_v_ids, 'security' => $params['security']));
return json_encode($r_aprod['a_prod']);
}
}
if (!empty($post['cmd']) && $post['cmd'] == 'sr_summary') {
return json_encode($returns['kpi']);
}
return json_encode($returns);
}
