function get_last_few_order_details($product_id, $where_date)
{
global $wpdb, $cat_rev, $months, $order_arr;
$select = "SELECT order_item.order_id AS order_id,\n\t\t\t\t\t\t\t posts.post_date AS date,\n\t\t\t\t\t\t\t GROUP_CONCAT( distinct postmeta.meta_value\n\t\t\t\t\t\t\t\t\tORDER BY postmeta.meta_id \n\t\t\t\t\t\t\t\t\tSEPARATOR ' ' ) AS cname,\n\t\t\t\t\t\t\t ( SELECT post_meta.meta_value FROM {$wpdb->prefix}postmeta AS post_meta WHERE post_meta.post_id = order_item.order_id AND post_meta.meta_key = '_billing_country' ) AS country,\n\t\t\t\t\t\t\t ( SELECT post_meta.meta_value FROM {$wpdb->prefix}postmeta AS post_meta WHERE post_meta.post_id = order_item.order_id AND post_meta.meta_key = '_order_total' ) AS totalprice\n\t\t\t\t\t ";
$from = " FROM {$wpdb->prefix}sr_woo_order_items AS order_item\n\t\t\t\t \t LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = order_item.order_id AND posts.post_status IN ('wc-on-hold', 'wc-processing', 'wc-completed') )\n\t\t\t\t \t LEFT JOIN {$wpdb->prefix}postmeta AS postmeta ON ( order_item.order_id = postmeta.post_id AND postmeta.meta_key IN ( '_billing_first_name', '_billing_last_name' ) )\n\t\t\t\t\t";
$where = ' WHERE 1 ';
$order_by = "ORDER BY date DESC";
$limit = "limit 0,5";
if (isset($product_id)) {
$group_by = "GROUP BY order_id";
}
if (isset($product_id) && $product_id != 0) {
$where .= " AND order_item.product_id = {$product_id} ";
}
$query = "{$select} {$from} {$where} {$where_date} {$group_by} {$order_by} {$limit}";
$results = $wpdb->get_results($query, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
$no_records = $num_rows;
if ($no_records == 0) {
$encoded['orderDetails']['order'] = '';
$encoded['orderDetails']['orderTotalCount'] = 0;
} else {
$cnt = 0;
$order_data = array();
foreach ($results as $result) {
// put within condition
$order_data[$cnt]['purchaseid'] = $result['order_id'];
$order_data[$cnt]['date'] = date("d-M-Y", strtotime($result['date']));
if (!empty($_POST['detailed_view'])) {
// for detailed view widget
$order_data[$cnt]['totalprice'] = sprintf($_POST['SR_CURRENCY_POS'], $_POST['SR_CURRENCY_SYMBOL'], sr_number_format($result['totalprice'], $_POST['SR_DECIMAL_PLACES']));
$order_data[$cnt]['country_code'] = $result['country'];
if (!empty($_POST['SR_IS_WOO22']) && $_POST['SR_IS_WOO22'] == "true") {
$countries = WC()->countries->get_countries();
} elseif (!empty($_POST['SR_IS_WOO22']) && $_POST['SR_IS_WOO22'] == "false") {
global $woocommerce;
$countries = $woocommerce->countries->get_countries();
}
$order_data[$cnt]['country_name'] = html_entity_decode($countries[$result['country']]);
} else {
$order_data[$cnt]['totalprice'] = woocommerce_price($result['totalprice']);
}
$order_data[$cnt]['cname'] = $result['cname'];
$orders[] = $order_data[$cnt];
$cnt++;
}
$encoded['orderDetails']['order'] = $orders;
$encoded['orderDetails']['orderTotalCount'] = count($orders);
}
return $encoded;
}
function sr_query_sales($start_date, $end_date_query, $date_series, $select, $group_by, $select_top_prod, $select_top_abandoned_prod, $terms_post, $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();
$sr_currency_symbol = isset($post['SR_CURRENCY_SYMBOL']) ? $post['SR_CURRENCY_SYMBOL'] : '';
$sr_decimal_places = isset($post['SR_DECIMAL_PLACES']) ? $post['SR_DECIMAL_PLACES'] : '';
//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 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 posts.ID IN ({$terms_post})\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
//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\t 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 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 AND post_id IN ({$terms_post}))\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;
if ($rows_cumm_top_cust_guest > 0) {
$post_id = array();
$results_cumm_top_cust = array();
foreach ($results_cumm_top_cust_guest as $results_cumm_top_cust_guest1) {
$post_id[] = $results_cumm_top_cust_guest1['post_id_max'];
}
$post_id_imploded = implode(",", $post_id);
$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\n\t ORDER BY FIND_IN_SET(postmeta.post_id,'{$post_id_imploded}')";
$results_cumm_top_cust_guest_detail = $wpdb->get_results($query_cumm_top_cust_guest_detail, 'ARRAY_A');
for ($i = 0; $i < sizeof($results_cumm_top_cust_guest_detail); $i++) {
$results_cumm_top_cust[$i] = array();
$guest_meta_values = explode('###', $results_cumm_top_cust_guest_detail[$i]['meta_value']);
$guest_meta_key = explode('###', $results_cumm_top_cust_guest_detail[$i]['meta_key']);
if (count($guest_meta_values) != count($guest_meta_key)) {
continue;
}
unset($results_cumm_top_cust_guest_detail[$i]['meta_value']);
unset($results_cumm_top_cust_guest_detail[$i]['meta_key']);
$guest_meta_key_values = array_combine($guest_meta_key, $guest_meta_values);
$results_cumm_top_cust[$i]['total'] = $sr_currency_symbol . sr_number_format($results_cumm_top_cust_guest[$i]['total'], $sr_decimal_places);
$results_cumm_top_cust[$i]['calc_total'] = floatval($results_cumm_top_cust_guest[$i]['total']);
// value used only for sorting purpose
$results_cumm_top_cust[$i]['name'] = $guest_meta_key_values['_billing_first_name'] . " " . $guest_meta_key_values['_billing_last_name'];
$results_cumm_top_cust[$i]['billing_email'] = $results_cumm_top_cust_guest[$i]['billing_email'];
$results_cumm_top_cust[$i]['post_ids'] = json_encode($results_cumm_top_cust_guest[$i]['post_id']);
}
}
$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 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 AND post_id IN ({$terms_post}))\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;
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\n\t ORDER BY FIND_IN_SET('users.ID','{$user_ids_imploded}')";
$results_reg_details = $wpdb->get_results($query_reg_details, 'ARRAY_A');
for ($i = sizeof($results_cumm_top_cust), $j = 0; $j < sizeof($results_reg_details); $i++, $j++) {
$results_cumm_top_cust[$i] = array();
$reg_meta_values = explode('###', $results_reg_details[$j]['meta_value']);
$reg_meta_key = explode('###', $results_reg_details[$j]['meta_key']);
if (count($reg_meta_values) != count($reg_meta_key)) {
continue;
}
unset($results_reg_details[$j]['meta_value']);
unset($results_reg_details[$j]['meta_key']);
$reg_meta_key_values = array_combine($reg_meta_key, $reg_meta_values);
$results_cumm_top_cust[$i]['total'] = $sr_currency_symbol . sr_number_format($results_cumm_top_cust_reg[$j]['total'], $sr_decimal_places);
$results_cumm_top_cust[$i]['calc_total'] = floatval($results_cumm_top_cust_reg[$j]['total']);
// value used only for sorting purpose
$results_cumm_top_cust[$i]['name'] = $reg_meta_key_values['first_name'] . " " . $reg_meta_key_values['last_name'];
$results_cumm_top_cust[$i]['billing_email'] = $results_reg_details[$j]['email'];
$results_cumm_top_cust[$i]['post_ids'] = json_encode($results_cumm_top_cust_reg[$j]['post_id']);
}
}
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
//Query to get the Top 5 Products
$query_top_prod = "SELECT order_item.product_id as product_id,\n\t order_item.product_name as product_name,\n\t SUM( order_item.sales ) AS product_sales ,\n\t SUM( order_item.quantity ) AS product_qty\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 posts.id IN ({$terms_post})\n\t GROUP BY order_item.product_id\n\t ORDER BY product_sales DESC\n\t LIMIT 5";
$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
$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'];
}
for ($i = 0, $j = 0, $k = 0; $i < sizeof($results_top_prod_graph); $i++) {
if ($i > 0) {
if ($results_top_prod_graph[$i]['product_id'] == $prod_id) {
$j++;
$top_prod_graph_data[$k][$j]['product_sales'] = $results_top_prod_graph[$i]['product_sales'];
$top_prod_graph_data[$k][$j]['product_qty'] = $results_top_prod_graph[$i]['product_qty'];
$top_prod_graph_data[$k][$j][$group_by] = $results_top_prod_graph[$i][$group_by];
if ($group_by == "display_date_time") {
$top_prod_graph_data[$k][$j]['display_time'] = $results_top_prod_graph[$i]['display_time'];
$top_prod_graph_data[$k][$j]['comp_time'] = $results_top_prod_graph[$i]['comp_time'];
}
$prod_id = $results_top_prod_graph[$i]['product_id'];
} else {
$k++;
$j = 0;
$top_prod_graph_data[$k] = array();
$top_prod_graph_data[$k][$j]['product_sales'] = $results_top_prod_graph[$i]['product_sales'];
$top_prod_graph_data[$k][$j]['product_qty'] = $results_top_prod_graph[$i]['product_qty'];
$top_prod_graph_data[$k][$j][$group_by] = $results_top_prod_graph[$i][$group_by];
if ($group_by == "display_date_time") {
$top_prod_graph_data[$k][$j]['display_time'] = $results_top_prod_graph[$i]['display_time'];
$top_prod_graph_data[$k][$j]['comp_time'] = $results_top_prod_graph[$i]['comp_time'];
}
$prod_id = $results_top_prod_graph[$i]['product_id'];
}
} else {
$top_prod_graph_data[$k] = array();
$top_prod_graph_data[$k][$j]['product_sales'] = $results_top_prod_graph[$i]['product_sales'];
$top_prod_graph_data[$k][$j]['product_qty'] = $results_top_prod_graph[$i]['product_qty'];
$top_prod_graph_data[$k][$j][$group_by] = $results_top_prod_graph[$i][$group_by];
if ($group_by == "display_date_time") {
$top_prod_graph_data[$k][$j]['display_time'] = $results_top_prod_graph[$i]['display_time'];
$top_prod_graph_data[$k][$j]['comp_time'] = $results_top_prod_graph[$i]['comp_time'];
}
$prod_id = $results_top_prod_graph[$i]['product_id'];
}
}
}
}
$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++;
}
}
//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 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 AND posts.id IN ({$terms_post})\n\t\t GROUP BY postmeta.meta_value";
$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\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 AND posts.id IN ({$terms_post})\n\t\t GROUP BY postmeta1.meta_value";
$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 WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t \tAND posts.ID IN ({$terms_post})";
$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 LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\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 AND posts.ID IN ({$terms_post})\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 WHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t AND posts.ID IN ({$terms_post})\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 = 0;
$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\tWHERE posts.post_date BETWEEN '{$start_date}' AND '{$end_date_query}'\n\t\t\t\t\t\t AND posts.ID IN ({$terms_post})\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 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 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 AND posts.ID IN ({$terms_post})\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'] = $sr_currency_symbol . sr_number_format($results_top_payment_gateway1['sales_total'], $sr_decimal_places);
$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) . "'";
}
}
//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 LEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta1 ON ( posts.ID = postmeta1.post_id )\n\t\t\t LEFT JOIN `{$wpdb->prefix}postmeta` AS postmeta2 ON ( posts.ID = postmeta2.post_id )\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 AND posts.ID IN ({$terms_post})\n\t\t\t AND postmeta1.meta_value IN ({$top_payment_gateway_imploded})\n\t\t GROUP BY payment_method, {$group_by}\n\t\t ORDER BY FIND_IN_SET(postmeta1.meta_value,'" . implode(",", $top_payment_gateway) . "')";
$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) {
for ($i = 0, $j = 0, $k = 0; $i < sizeof($results_top_gateways_graph); $i++) {
if ($i > 0) {
if ($results_top_gateways_graph[$i]['payment_method'] == $payment_method) {
$j++;
$top_gateway_graph_data[$k][$j]['gateway_sales_amt'] = $results_top_gateways_graph[$i]['sales_total'];
$top_gateway_graph_data[$k][$j]['gateway_sales_count'] = $results_top_gateways_graph[$i]['sales_count'];
$top_gateway_graph_data[$k][$j][$group_by] = $results_top_gateways_graph[$i][$group_by];
if ($group_by == "display_date_time") {
$top_gateway_graph_data[$k][$j]['display_time'] = $results_top_gateways_graph[$i]['display_time'];
$top_gateway_graph_data[$k][$j]['comp_time'] = $results_top_gateways_graph[$i]['comp_time'];
}
$payment_method = $results_top_gateways_graph[$i]['payment_method'];
} else {
$k++;
$j = 0;
$top_gateway_graph_data[$k] = array();
$top_gateway_graph_data[$k][$j]['gateway_sales_amt'] = $results_top_gateways_graph[$i]['sales_total'];
$top_gateway_graph_data[$k][$j]['gateway_sales_count'] = $results_top_gateways_graph[$i]['sales_count'];
$top_gateway_graph_data[$k][$j][$group_by] = $results_top_gateways_graph[$i][$group_by];
if ($group_by == "display_date_time") {
$top_gateway_graph_data[$k][$j]['display_time'] = $results_top_gateways_graph[$i]['display_time'];
$top_gateway_graph_data[$k][$j]['comp_time'] = $results_top_gateways_graph[$i]['comp_time'];
}
$payment_method = $results_top_gateways_graph[$i]['payment_method'];
}
} else {
$top_gateway_graph_data[$k] = array();
$top_gateway_graph_data[$k][$j]['gateway_sales_amt'] = $results_top_gateways_graph[$i]['sales_total'];
$top_gateway_graph_data[$k][$j]['gateway_sales_count'] = $results_top_gateways_graph[$i]['sales_count'];
$top_gateway_graph_data[$k][$j][$group_by] = $results_top_gateways_graph[$i][$group_by];
if ($group_by == "display_date_time") {
$top_gateway_graph_data[$k][$j]['display_time'] = $results_top_gateways_graph[$i]['display_time'];
$top_gateway_graph_data[$k][$j]['comp_time'] = $results_top_gateways_graph[$i]['comp_time'];
}
$payment_method = $results_top_gateways_graph[$i]['payment_method'];
}
}
}
//Query to get the Payment Gateway Title
$query_gateway_title = "SELECT DISTINCT postmeta1.meta_value as gateway_title,\n \t\t\t\t\t\t\tpostmeta2.meta_value as gateway_method\n\t \t\t\t\t\t\tFROM `{$wpdb->prefix}postmeta` AS postmeta1\n\t \t\t\t\t\t\t\tJOIN `{$wpdb->prefix}postmeta` AS postmeta2 ON ( postmeta1.post_id = postmeta2.post_id )\n \t\t\t\t\t\t\tWHERE postmeta1.meta_key IN ('_payment_method_title')\n \t\t\t\t\t\t\t\tAND postmeta2.meta_key IN ('_payment_method')\n \t\t\t\t\t\t\t\tAND postmeta2.meta_value IN ({$top_payment_gateway_imploded})\n \t\t\t\t\t\t\tORDER BY FIND_IN_SET(postmeta1.meta_value,'" . implode(",", $top_payment_gateway) . "')";
$result_gateway_title = $wpdb->get_results($query_gateway_title, 'ARRAY_A');
$gateway_title = array();
foreach ($result_gateway_title as $result_gateway_title1) {
$gateway_title[$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[$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 LEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\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 AND posts.ID IN ({$terms_post})\n\t\t GROUP BY posts.ID";
$results_cumm_taxes = $wpdb->get_results($query_cumm_taxes, 'ARRAY_A');
$rows_cumm_taxes = $wpdb->num_rows;
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 to get Top Abandoned Products
$current_time = current_time('timestamp');
$cut_off_time = get_option('sr_abandoned_cutoff_time') ? get_option('sr_abandoned_cutoff_time') : 0;
$cart_cut_off_time = $cut_off_time * 60;
$compare_time = $current_time - $cart_cut_off_time;
//Query to update the abandoned product status
$query_abandoned_status = "UPDATE {$wpdb->prefix}sr_woo_abandoned_items\n\t \t\t\t\t\t\t\tSET product_abandoned = 1\n\t \t\t\t\t\t\t\tWHERE order_id IS NULL\n\t \t\t\t\t\t\t\t\tAND abandoned_cart_time < " . $compare_time;
$wpdb->query($query_abandoned_status);
//Query to get the Top Abandoned Products
$query_top_abandoned_products = "SELECT SUM(quantity) as abondoned_qty,\n\t\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(quantity order by id SEPARATOR '###') AS abandoned_quantity,\n\t\t\t\t\t\t\t\t\t\t\tproduct_id as id,\n\t\t\t\t\t\t\t\t\t\t\t{$select_top_abandoned_prod}\n\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items\n\t\t\t\t\t\t\t\t\t\tWHERE order_id IS NULL \n\t\t\t\t\t\t\t\t\t\t\tAND product_abandoned = 1\n\t\t\t\t\t\t\t\t\t\t\tAND abandoned_cart_time BETWEEN '" . strtotime($start_date) . "' AND '" . strtotime($end_date_query) . "'\n\t\t\t\t\t\t\t\t\t\tGROUP BY product_id\n\t\t\t\t\t\t\t\t\t\tORDER BY abondoned_qty DESC\n\t\t\t\t\t\t\t\t\t\tLIMIT 5";
$results_top_abandoned_products = $wpdb->get_results($query_top_abandoned_products, 'ARRAY_A');
$rows_top_abandoned_products = $wpdb->num_rows;
if ($rows_top_abandoned_products > 0) {
$prod_id = array();
foreach ($results_top_abandoned_products as $results_top_abandoned_product) {
$prod_id[] = $results_top_abandoned_product['id'];
}
$prod_id = implode(",", $prod_id);
$query_prod_abandoned_rate = "SELECT SUM(quantity) as abondoned_rate\n\t\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}sr_woo_abandoned_items\n\t\t\t\t\t\t\t\t\t\t\tWHERE product_abandoned = 1\n\t\t\t\t\t\t\t\t\t\t\t\tAND abandoned_cart_time BETWEEN '" . strtotime($start_date) . "' AND '" . strtotime($end_date_query) . "'\n\t\t\t\t\t\t\t\t\t\t\t\tAND product_id IN (" . $prod_id . ")\n\t\t\t\t\t\t\t\t\t\t\tGROUP BY product_id\n\t\t\t\t\t\t\t\t\t\t\tORDER BY FIND_IN_SET(product_id,'{$prod_id}') ";
$results_prod_abandoned_rate = $wpdb->get_col($query_prod_abandoned_rate);
$rows_prod_abandoned_rate = $wpdb->num_rows;
$j = 0;
$total_prod_abandoned_qty = 0;
$total_prod_qty = 0;
//Query to get the variation Attributes in a formatted manner
$query_attributes = "SELECT post_id,\n\t\t \t\t\t\t\t\t\tGROUP_CONCAT(meta_key order by meta_id SEPARATOR '###') AS meta_key,\n\t\t \t\t\t\t\t\t\tGROUP_CONCAT(meta_value order by meta_id SEPARATOR '###') AS meta_value\n\t\t\t\t \t\t\tFROM {$wpdb->prefix}postmeta\n\t\t\t\t \t\t\tWHERE meta_key like 'attribute_%'\n\t\t\t\t \t\t\t\tAND post_id IN ({$prod_id})\n\t\t\t\t \t\t\tGROUP BY post_id";
$results_attributes = $wpdb->get_results($query_attributes, 'ARRAY_A');
$rows_attributes = $wpdb->num_rows;
$variation_attributes = array();
foreach ($results_attributes as $results_attribute) {
$meta_key = explode('###', $results_attribute['meta_key']);
$meta_value = explode('###', $results_attribute['meta_value']);
if (count($meta_key) != count($meta_value)) {
continue;
}
$variation_attributes[$results_attribute['post_id']] = woocommerce_get_formatted_variation(array_combine($meta_key, $meta_value), true);
}
foreach ($results_top_abandoned_products as &$results_top_abandoned_product) {
$abandoned_quantity = explode('###', $results_top_abandoned_product['abandoned_quantity']);
$abandoned_dates = explode('###', $results_top_abandoned_product['abandoned_dates']);
if ($group_by == "display_date_time") {
$abandoned_dates_comp = explode('###', $results_top_abandoned_product['comp_time']);
}
if (count($abandoned_quantity) != count($abandoned_dates)) {
continue;
}
unset($results_top_abandoned_product['abandoned_quantity']);
unset($results_top_abandoned_product['abandoned_dates']);
if ($group_by == "display_date_time") {
unset($results_top_abandoned_product['comp_time']);
}
$abandoned_date_series = $date_series;
if ($group_by == "display_date_time") {
for ($i = 0; $i < sizeof($abandoned_dates_comp); $i++) {
$abandoned_date_series[$abandoned_dates_comp[$i]]['post_date'] = $abandoned_dates[$i];
$abandoned_date_series[$abandoned_dates_comp[$i]]['sales'] = $abandoned_date_series[$abandoned_dates_comp[$i]]['sales'] + $abandoned_quantity[$i];
}
} else {
for ($i = 0; $i < sizeof($abandoned_dates); $i++) {
$abandoned_date_series[$abandoned_dates[$i]]['sales'] = $abandoned_date_series[$abandoned_dates[$i]]['sales'] + $abandoned_quantity[$i];
}
}
$results_top_abandoned_product['graph_data'] = array();
foreach ($abandoned_date_series as $abandoned_date_series1) {
$results_top_abandoned_product['graph_data'][] = $abandoned_date_series1;
}
$results_top_abandoned_product['price'] = get_post_meta($results_top_abandoned_product['id'], '_price', true) * $results_top_abandoned_product['abondoned_qty'];
$results_top_abandoned_product['abondoned_qty'] = floatval($results_top_abandoned_product['abondoned_qty']);
$abandoned_rate = $results_top_abandoned_product['abondoned_qty'] / $results_prod_abandoned_rate[$j] * 100;
$results_top_abandoned_product['abandoned_rate'] = round($abandoned_rate, get_option('woocommerce_price_num_decimals')) . "%";
//Code for formatting the product name
$post_parent = wp_get_post_parent_id($results_top_abandoned_product['id']);
if ($post_parent > 0) {
$results_top_abandoned_product['prod_name'] = get_the_title($post_parent) . " (" . $variation_attributes[$results_top_abandoned_product['id']] . ")";
} else {
$results_top_abandoned_product['prod_name'] = get_the_title($results_top_abandoned_product['id']);
}
$total_prod_abandoned_qty = $total_prod_abandoned_qty + $results_top_abandoned_product['abondoned_qty'];
$total_prod_qty = $total_prod_qty + $results_prod_abandoned_rate[$j];
$j++;
}
}
$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 ($rows_total_abandoned_cart > 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;
}
//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;
}
//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 JOIN {$wpdb->prefix}term_relationships AS term_relationships \n\t ON term_relationships.object_id = posts.ID \n\t JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy \n\t ON term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id \n\t JOIN {$wpdb->prefix}terms AS terms \n\t ON term_taxonomy.term_id = terms.term_id\n\t\t WHERE posts.post_status IN ('publish')\n\t\t AND 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;
}
//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 JOIN {$wpdb->prefix}term_relationships AS term_relationships \n\t ON term_relationships.object_id = posts.ID \n\t JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy \n\t ON term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id \n\t JOIN {$wpdb->prefix}terms AS terms \n\t ON term_taxonomy.term_id = terms.term_id\n\t\t WHERE terms.name IN ('completed')\n\t\t AND posts.post_status IN ('publish')\n\t\t AND posts.post_type IN ('shop_order')\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[] = 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[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 : '';
}
return $results;
}
function() { $(this).css('border', '0.2em solid #e8e8e8'); }
);
});
</script>
<div class="daily_widgets_icon">
<i class = "icon-truck daily_widgets_icon1 daily_widget_4_color"> </i>
</div>
<div class="daily_widgets_data">
<span class = "daily_widgets_price daily_widget_4_color"> <?php
echo sr_number_format($count_order_fulfillment_today[0], $sr_decimal_places);
?>
<i class="<?php
echo $imgurl_order_fulfillment;
?>
"></i> <span class = "daily_widgets_comp_price daily_widget_4_color"> <?php
$diff = !empty($diff_order_fulfillment) ? sr_number_format($diff_order_fulfillment, $sr_decimal_places) : "";
echo $diff;
?>
</span> </span>
<p class="daily_widgets_text"> Orders To Fulfill </p>
</div>
</div>
</div>
<?php
}
?>
</div>
</div>
<!--
// ================================================