function sr_get_daily_kpi_data()
{
global $wpdb;
//Code for date localization
$today_arr = getdate();
$curr_time_gmt = date('H:i:s', time() - date("Z"));
$new_date = date('Y-m-d') . " " . $curr_time_gmt;
$today = date('Y-m-d', (int) strtotime($new_date) + get_option('gmt_offset') * HOUR_IN_SECONDS);
$today_time = date('Y-m-d H:i:s', (int) strtotime($new_date) + get_option('gmt_offset') * HOUR_IN_SECONDS);
$yesterday = date('Y-m-d', strtotime($today . ' -1 day'));
// $today_to_date = $today . " 00:00:00";
$this_month_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date('m', strtotime($today)), 1, date('Y', strtotime($today))));
$days_in_this_month = date('t', mktime(0, 0, 0, date('m', strtotime($today)), 1, date('Y', strtotime($today))));
$comparison_to_date = date('Y-m-d', strtotime($today . ' -1 month')) . " 00:00:00";
$comparison_month_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date('m', strtotime($comparison_to_date)), 1, date('Y', strtotime($comparison_to_date))));
$comparison_days_in_month = date('t', mktime(0, 0, 0, date('m', strtotime($comparison_to_date)), 1, date('Y', strtotime($comparison_to_date))));
$cond_terms_post = '';
$terms_post_join = '';
if (!empty($_POST['SR_IS_WOO22']) && $_POST['SR_IS_WOO22'] == "true") {
$cond_terms_post = "AND posts.post_status IN ('wc-completed','wc-processing','wc-on-hold')";
$terms_post_join = '';
} 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','processing','on-hold')";
$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 = !empty($terms_post_join) ? 'AND term_relationships.term_taxonomy_id IN (' . $terms_taxonomy_ids . ')' : '';
}
}
$daily_widget_data = array();
// ================================================
// Todays Sales
// ================================================
$query_today = "SELECT SUM( postmeta.meta_value ) AS todays_sales \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')\n\t\t AND posts.post_date LIKE '{$today}%'\n\t\t {$cond_terms_post}";
$results_today = $wpdb->get_col($query_today);
$rows_results_today = $wpdb->num_rows;
if ($rows_results_today > 0 && !empty($results_today[0])) {
$daily_widget_data['sales_today'] = $results_today[0];
} else {
$daily_widget_data['sales_today'] = 0;
}
$query_yest = "SELECT SUM( postmeta.meta_value ) AS yesterdays_sales \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')\n\t\t AND posts.post_date LIKE '{$yesterday}%'\n\t\t {$cond_terms_post}";
$results_yest = $wpdb->get_col($query_yest);
$rows_results_yest = $wpdb->num_rows;
if ($rows_results_yest > 0) {
$daily_widget_data['sales_yest'] = $results_yest[0];
} else {
$daily_widget_data['sales_yest'] = 0;
}
$daily_sales_kpi = sr_get_daily_kpi_data_formatted('daily_sales', $daily_widget_data['sales_today'], $daily_widget_data['sales_yest'], $_POST);
// Query to get the month to date and forecasted sales
$query_month_to_date_sales = "SELECT COUNT( posts.ID ) as sales_count, \n\t\t\t\t\t\t\t\t\t\t\tSUM( postmeta.meta_value ) AS month_to_date \n\t\t\t\t\t FROM `{$wpdb->prefix}postmeta` AS postmeta\n\t\t\t\t\t \tLEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\n\t\t\t\t\t \t{$terms_post_join}\n\t\t\t\t\t WHERE postmeta.meta_key IN ('_order_total')\n\t\t\t\t \tAND posts.post_date between '{$this_month_start}' AND '{$today_time}'\n\t\t\t\t \t{$cond_terms_post}";
$results_month_to_date_sales = $wpdb->get_results($query_month_to_date_sales, 'ARRAY_A');
$month_to_date_sales = !empty($results_month_to_date_sales[0]['month_to_date']) ? $results_month_to_date_sales[0]['month_to_date'] : 0;
$avg_sales_per_day = round($results_month_to_date_sales[0]['month_to_date'] / $today_arr['mday'], 2);
$forcasted_sales = $avg_sales_per_day * $days_in_this_month;
// Code for calculating the sales frequency
$date_diff = round((strtotime($today_time) - strtotime($this_month_start)) / 60);
$frequency_diff_days = $date_diff / 1440;
$sales_frequency = !empty($results_month_to_date_sales[0]['sales_count']) ? $frequency_diff_days / $results_month_to_date_sales[0]['sales_count'] : '0';
// $diff = date_diff($today_time,$this_month_start);
$sales_frequency_formatted = sr_get_frequency_formatted($sales_frequency);
// Query to get the comparison month to date and forecasted sales
$query_comparison_month_to_date_sales = "SELECT COUNT( posts.ID ) as sales_count, \n\t\t\t\t\t\t\t\t\t\t\t\t\tSUM( postmeta.meta_value ) AS month_to_date\n\t\t\t\t\t\t\t FROM `{$wpdb->prefix}postmeta` AS postmeta\n\t\t\t\t\t\t\t \tLEFT JOIN {$wpdb->prefix}posts AS posts ON ( posts.ID = postmeta.post_id )\n\t\t\t\t\t\t\t \t{$terms_post_join}\n\t\t\t\t\t\t\t WHERE postmeta.meta_key IN ('_order_total')\n\t\t\t\t\t\t \tAND posts.post_date between '{$comparison_month_start}' AND '{$comparison_to_date}'\n\t\t\t\t\t\t \t{$cond_terms_post}";
$results_comparison_month_to_date_sales = $wpdb->get_results($query_comparison_month_to_date_sales, 'ARRAY_A');
$comparison_month_to_date_sales = !empty($results_comparison_month_to_date_sales[0]['month_to_date']) ? $results_comparison_month_to_date_sales[0]['month_to_date'] : 0;
$comparison_avg_sales_per_day = round($results_comparison_month_to_date_sales[0]['month_to_date'] / $today_arr['mday'], 2);
$comparison_forcasted_sales = $comparison_avg_sales_per_day * $comparison_days_in_month;
$comparison_sales_frequency = !empty($results_comparison_month_to_date_sales[0]['sales_count']) ? $frequency_diff_days / $results_comparison_month_to_date_sales[0]['sales_count'] : '0';
//Code for month to date sales KPI
$month_to_date_sales_kpi = sr_get_daily_kpi_data_formatted('month_to_date_sales', $month_to_date_sales, $comparison_month_to_date_sales, $_POST);
//Code for average sales per day KPI
$avg_sales_per_day_kpi = sr_get_daily_kpi_data_formatted('avg_sales_per_day', $avg_sales_per_day, $comparison_avg_sales_per_day, $_POST);
//Code for Forecasted Sales KPI
$forcasted_sales_kpi = sr_get_daily_kpi_data_formatted('forcasted_sales', $forcasted_sales, $comparison_forcasted_sales, $_POST);
//Code for Sales Frequency KPI
// $sales_count = (!empty($results_month_to_date_sales[0]['sales_count'])) ? $results_month_to_date_sales[0]['sales_count'] : '0';
// $comparison_sales_count = (!empty($results_comparison_month_to_date_sales[0]['sales_count'])) ? $results_comparison_month_to_date_sales[0]['sales_count'] : '0';
if ($comparison_sales_frequency == 0) {
$daily_widget_data['diff_sales_frequency'] = round($sales_frequency, 2);
} else {
$daily_widget_data['diff_sales_frequency'] = abs(round(($sales_frequency - $comparison_sales_frequency) / $comparison_sales_frequency * 100, 2));
}
if ($daily_widget_data['diff_sales_frequency'] != 0) {
if ($comparison_sales_frequency < $sales_frequency) {
$daily_widget_data['imgurl_sales_frequency'] = $_POST['SR_IMG_UP_RED'];
} else {
$daily_widget_data['imgurl_sales_frequency'] = $_POST['SR_IMG_UP_GREEN'];
}
} else {
$daily_widget_data['diff_sales_frequency'] = "";
$daily_widget_data['imgurl_sales_frequency'] = "";
}
$daily_widget_data['sales_frequency_formatted'] = $sales_frequency_formatted;
$daily_widget_data['diff_sales_frequency_formatted'] = !empty($daily_widget_data['diff_sales_frequency']) ? sr_number_format($daily_widget_data['diff_sales_frequency'], $_POST['SR_DECIMAL_PLACES']) . '%' : "";
// ================================================
// Todays Customers
// ================================================
$result_guest_today_email1 = array();
$result_guest_yest_email1 = array();
$reg_today_count = 0;
$reg_yest_count = 0;
//Reg Customers
$query_reg_today = "SELECT ID FROM `{$wpdb->users}` \n\t\t WHERE user_registered LIKE '{$today}%'";
$reg_today_ids = $wpdb->get_col($query_reg_today);
$rows_reg_today_ids = $wpdb->num_rows;
if ($rows_reg_today_ids > 0) {
$query_reg_today_count = "SELECT DISTINCT postmeta.meta_value\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 IN (" . implode(",", $reg_today_ids) . ")\n\t\t AND posts.post_date LIKE '{$today}%'\n\t\t {$cond_terms_post}";
$reg_today = $wpdb->get_col($query_reg_today_count);
$rows_reg_today = $wpdb->num_rows;
if ($rows_reg_today > 0) {
$reg_today_count = sizeof($reg_today);
}
}
$query_reg_yest = "SELECT ID FROM `{$wpdb->users}` \n\t\t WHERE user_registered LIKE '{$yesterday}%'";
$reg_yest_ids = $wpdb->get_col($query_reg_yest);
$rows_reg_yest_ids = $wpdb->num_rows;
if ($rows_reg_yest_ids > 0) {
$query_reg_today_count = "SELECT DISTINCT postmeta.meta_value\n\t\t FROM {$wpdb->prefix}postmeta AS postmeta\n\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta.post_id)\n\t {$terms_post_join}\n\t\t WHERE postmeta.meta_key IN ('_customer_user')\n\t\t AND postmeta.meta_value IN (" . implode(",", $reg_yest_ids) . ")\n\t\t AND posts.post_date LIKE '{$yesterday}%'\n\t\t {$cond_terms_post}";
$reg_yest = $wpdb->get_col($query_reg_today_count);
$rows_reg_yest = $wpdb->num_rows;
if ($rows_reg_yest > 0) {
$reg_yest_count = sizeof($reg_yest);
}
}
//Guest Customers
$query_guest_today_email = "SELECT postmeta1.meta_value\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 {$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 LIKE '{$today}%'\n\t\t {$cond_terms_post}\n\t\t GROUP BY postmeta1.meta_value";
$result_guest_today_email = $wpdb->get_col($query_guest_today_email);
$rows_guest_today_email = $wpdb->num_rows;
if ($rows_guest_today_email > 0) {
$result_guest_today_email1 = array_flip($result_guest_today_email);
$query_guest_today = "SELECT DISTINCT postmeta.meta_value\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 ('_billing_email')\n\t\t AND postmeta.meta_value IN ('" . implode("','", $result_guest_today_email) . "')\n\t\t AND posts.post_date NOT LIKE '{$today}%'\n\t\t GROUP BY posts.ID";
$result_guest_today = $wpdb->get_col($query_guest_today);
for ($i = 0; $i < sizeof($result_guest_today); $i++) {
if (isset($result_guest_today_email1[$result_guest_today[$i]])) {
unset($result_guest_today_email1[$result_guest_today[$i]]);
}
}
}
$daily_widget_data['today_count_cust'] = 0;
$daily_widget_data['today_count_cust'] = sizeof($result_guest_today_email1) + $reg_today_count;
$query_guest_yest_email = "SELECT postmeta1.meta_value\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 {$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 LIKE '{$yesterday}%'\n\t\t {$cond_terms_post}\n\t\t GROUP BY postmeta1.meta_value";
$result_guest_yest_email = $wpdb->get_col($query_guest_yest_email);
$rows_guest_yest_email = $wpdb->num_rows;
if ($rows_guest_yest_email > 0) {
$result_guest_yest_email1 = array_flip($result_guest_yest_email);
$query_guest_yest = "SELECT DISTINCT postmeta.meta_value\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 ('_billing_email')\n\t\t AND postmeta.meta_value IN ('" . implode("','", $result_guest_yest_email) . "')\n\t\t AND posts.post_date NOT LIKE '{$yesterday}%'\n\t\t AND posts.post_date NOT LIKE '{$today}%'\n\t\t GROUP BY posts.ID";
$result_guest_yest = $wpdb->get_col($query_guest_yest);
for ($i = 0; $i < sizeof($result_guest_yest); $i++) {
if (isset($result_guest_yest_email1[$result_guest_yest[$i]])) {
unset($result_guest_yest_email1[$result_guest_yest[$i]]);
}
}
}
$daily_widget_data['yest_count_cust'] = 0;
$daily_widget_data['yest_count_cust'] = sizeof($result_guest_yest_email1) + $reg_yest_count;
$daily_cust_kpi = sr_get_daily_kpi_data_formatted('daily_cust', $daily_widget_data['today_count_cust'], $daily_widget_data['yest_count_cust'], $_POST);
// ================================================
// Todays Returns
// ================================================
$cond_terms_post = '';
$terms_post_join = '';
if (!empty($_POST['SR_IS_WOO22']) && $_POST['SR_IS_WOO22'] == "true") {
$cond_terms_post = " posts.post_status IN ('wc-refunded')";
$terms_post_join = '';
} 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 ('refunded')";
$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_terms_refund = "SELECT id FROM {$wpdb->prefix}posts AS posts\n\t\t \t\t{$terms_post_join}\n\t\t\t WHERE {$cond_terms_post}";
$terms_refund_post = $wpdb->get_col($query_terms_refund);
$rows_terms_refund_post = $wpdb->num_rows;
if ($rows_terms_refund_post > 0) {
$terms_refund_post = implode(",", $terms_refund_post);
$query_today_refund = "SELECT SUM(postmeta.meta_value) as todays_refund\n\t\t\t FROM {$wpdb->prefix}postmeta AS postmeta\n\t\t\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta.post_id)\n\t\t\t WHERE postmeta.meta_key IN ('_order_total')\n\t\t\t AND posts.post_modified LIKE '{$today}%'\n\t\t\t AND posts.ID IN ({$terms_refund_post})";
$result_today_refund = $wpdb->get_col($query_today_refund);
$query_yest_refund = "SELECT SUM(postmeta.meta_value) as yest_refund\n\t\t\t FROM {$wpdb->prefix}postmeta AS postmeta\n\t\t\t JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta.post_id)\n\t\t\t WHERE postmeta.meta_key IN ('_order_total')\n\t\t\t AND posts.post_modified LIKE '{$yesterday}%'\n\t\t\t AND posts.ID IN ({$terms_refund_post})";
$result_yest_refund = $wpdb->get_col($query_yest_refund);
} else {
$rows_today_refund = 0;
$rows_yest_refund = 0;
}
if (!empty($result_today_refund[0])) {
$daily_widget_data['today_refund'] = $result_today_refund[0];
} else {
$daily_widget_data['today_refund'] = "0";
}
if (!empty($result_yest_refund[0])) {
$daily_widget_data['yest_refund'] = $result_yest_refund[0];
} else {
$daily_widget_data['yest_refund'] = "0";
}
$daily_refund_kpi = sr_get_daily_kpi_data_formatted('daily_refund', $daily_widget_data['today_refund'], $daily_widget_data['yest_refund'], $_POST);
// ================================================
// Orders Unfulfillment
// ================================================
$query_shipping_status = "SELECT option_value FROM {$wpdb->prefix}options\n\t\t WHERE option_name LIKE 'woocommerce_calc_shipping'";
$result_shipping_status = $wpdb->get_col($query_shipping_status);
$daily_widget_data['result_shipping_status'] = $result_shipping_status[0];
$query_physical_prod = "SELECT post_id\n\t\t FROM {$wpdb->prefix}postmeta\n\t\t WHERE (meta_key LIKE '_downloadable' AND meta_value LIKE 'no')\n\t\t OR (meta_key LIKE '_virtual' AND meta_value LIKE 'no')";
$result_physical_prod = $wpdb->get_col($query_physical_prod);
$rows_physical_prod = $wpdb->num_rows;
$daily_widget_data['rows_physical_prod'] = $rows_physical_prod;
//Woo 2.2 Fix
if (!empty($_POST['SR_IS_WOO22']) && $_POST['SR_IS_WOO22'] == "true") {
$cond_terms_post = " posts.post_status IN ('wc-processing')";
$terms_post_join = '';
} 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 ('processing')";
$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_order_fulfillment_today = "SELECT count(id) FROM {$wpdb->prefix}posts AS posts\n\t\t \t{$terms_post_join}\n\t\t WHERE {$cond_terms_post}\n\t\t AND (posts.post_modified LIKE '{$today}%'\n\t\t OR posts.post_date LIKE '{$today}%')";
$result_order_fulfillment_today = $wpdb->get_col($query_order_fulfillment_today);
$rows_order_fulfillment_today = $wpdb->num_rows;
if ($rows_order_fulfillment_today > 0) {
$daily_widget_data['count_order_fulfillment_today'] = $result_order_fulfillment_today[0];
} else {
$daily_widget_data['count_order_fulfillment_today'] = 0;
}
$query_order_fulfillment_yest = "SELECT count(id) FROM {$wpdb->prefix}posts AS posts\n\t\t \t{$terms_post_join}\n\t\t WHERE {$cond_terms_post}\n\t\t AND (posts.post_modified LIKE '{$yesterday}%'\n\t\t OR posts.post_date LIKE '{$yesterday}%')";
$result_order_fulfillment_yest = $wpdb->get_col($query_order_fulfillment_yest);
$rows_order_fulfillment_yest = $wpdb->num_rows;
if ($rows_order_fulfillment_yest > 0) {
$daily_widget_data['count_order_fulfillment_yest'] = $result_order_fulfillment_yest[0];
} else {
$daily_widget_data['count_order_fulfillment_yest'] = 0;
}
$daily_order_fulfillment_kpi = sr_get_daily_kpi_data_formatted('order_fulfillment', $daily_widget_data['count_order_fulfillment_today'], $daily_widget_data['count_order_fulfillment_yest'], $_POST);
$daily_widget_data = array_merge($daily_widget_data, $daily_sales_kpi, $month_to_date_sales_kpi, $avg_sales_per_day_kpi, $forcasted_sales_kpi, $daily_cust_kpi, $daily_refund_kpi, $daily_order_fulfillment_kpi);
return $daily_widget_data;
}
function sr_get_daily_kpi_data($security, $format = 'html')
{
if (!wp_verify_nonce($security, 'smart-reporter-security')) {
die('Security check');
}
global $wpdb, $sr_text_domain;
//chk if the SR db dump table exists or not
$table_name = "{$wpdb->prefix}woo_sr_orders";
if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") != $table_name) {
return '';
}
$dates = array();
$dates['today'] = current_time('Y-m-d');
$dates['yesterday'] = date('Y-m-d', strtotime($dates['today'] . ' -1 day'));
$dates['c_month_start'] = date("Y-m-d", mktime(0, 0, 0, date('m', strtotime($dates['today'])), 1, date('Y', strtotime($dates['today']))));
$dates['c_month_days'] = date('t', mktime(0, 0, 0, date('m', strtotime($dates['today'])), 1, date('Y', strtotime($dates['today']))));
$dates['lp_date'] = date('Y-m-d', strtotime($dates['today'] . ' -1 month'));
$dates['lp_month_start'] = date("Y-m-d", mktime(0, 0, 0, date('m', strtotime($dates['lp_date'])), 1, date('Y', strtotime($dates['lp_date']))));
$dates['c_mins'] = round((current_time('timestamp') - strtotime($dates['c_month_start'])) / 60, 2);
$daily_widget_data = array();
$daily_widget_keys = array('sales_today', 'new_customers_today', 'refund_today', 'orders_to_fulfill', 'month_to_date_sales', 'avg_sales/day', 'one_sale_every', 'forecasted_sales');
foreach ($daily_widget_keys as $key) {
$daily_widget_data[$key] = array();
$daily_widget_data[$key]['title'] = __(ucwords(str_replace('_', ' ', $key)), $sr_text_domain);
$daily_widget_data[$key]['c'] = 0;
$daily_widget_data[$key]['lp'] = 0;
$daily_widget_data[$key]['params'] = array();
$daily_widget_data[$key]['params']['currency_show'] = $key == 'new_customers_today' || $key == 'orders_to_fulfill' || $key == 'one_sale_every' ? false : true;
$daily_widget_data[$key]['params']['cmp_format'] = $key == 'forecasted_sales' ? 'none' : ($key == 'avg_sales/day' ? '$' : '%');
}
$daily_widget_data['sr_security'] = $security;
// ==================================================================
// Todays Sales, Refunds, MTD sales, Avg. Sales/Day, Forecasted Sales
// ==================================================================
$query = $wpdb->prepare("SELECT CASE \n\t\t\t\t\t\t\tWHEN created_date = %s THEN 'C'\n\t\t\t\t\t\t\tWHEN created_date = %s THEN 'L'\n\t\t\t\t\t\t\tWHEN created_date >= %s THEN 'CM' \n\t\t\t\t\t\t\tWHEN created_date >= %s THEN 'LM' \n\t\t\t\t\t\t\tEND \n\t\t\t\t\t\t\t as period, \n\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\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN -1*total \n\t\t\t\t\t\t\t\t\tELSE total END), 0) AS sales, \n\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN total \n\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\tELSE 0 END), 0) AS refunds \n\t\t\t\t\tFROM `{$wpdb->prefix}woo_sr_orders`\n\t\t\t\t\tWHERE \n\t\t\t\t\t\t( created_date BETWEEN %s AND %s OR \n\t\t\t\t\t\t created_date BETWEEN %s AND %s )\n\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-refunded') \n\t\t\t\t\tGROUP BY created_date", $dates['today'], $dates['yesterday'], $dates['c_month_start'], $dates['lp_month_start'], $dates['lp_month_start'], $dates['lp_date'], $dates['c_month_start'], $dates['today']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$curr_orders = $lp_orders = 0;
foreach ($results as $row) {
if (empty($row['period'])) {
continue;
}
if ($row['period'] == 'C') {
$daily_widget_data['sales_today']['c'] = !empty($row['sales']) ? $row['sales'] : 0;
$daily_widget_data['refund_today']['c'] = !empty($row['refunds']) ? $row['refunds'] : 0;
// for adding today's sales
$daily_widget_data['month_to_date_sales']['c'] += $daily_widget_data['sales_today']['c'];
$curr_orders += !empty($row['orders']) ? $row['orders'] : 0;
} else {
if ($row['period'] == 'L') {
$daily_widget_data['sales_today']['lp'] = !empty($row['sales']) ? $row['sales'] : 0;
$daily_widget_data['refund_today']['lp'] = !empty($row['refunds']) ? $row['refunds'] : 0;
// for adding yesterday's sales
if ($dates['yesterday'] <= $dates['lp_date']) {
$daily_widget_data['month_to_date_sales']['lp'] += $daily_widget_data['sales_today']['lp'];
$lp_orders += !empty($row['orders']) ? $row['orders'] : 0;
} else {
$daily_widget_data['month_to_date_sales']['c'] += $daily_widget_data['sales_today']['lp'];
$curr_orders += !empty($row['orders']) ? $row['orders'] : 0;
}
} else {
if ($row['period'] == 'CM') {
$daily_widget_data['month_to_date_sales']['c'] += !empty($row['sales']) ? $row['sales'] : 0;
$curr_orders += !empty($row['orders']) ? $row['orders'] : 0;
} else {
if ($row['period'] == 'LM') {
$daily_widget_data['month_to_date_sales']['lp'] += !empty($row['sales']) ? $row['sales'] : 0;
$lp_orders += !empty($row['orders']) ? $row['orders'] : 0;
}
}
}
}
}
$daily_widget_data['avg_sales/day']['c'] = round($daily_widget_data['month_to_date_sales']['c'] / $dates['c_mins'] * 1440, 2);
$daily_widget_data['avg_sales/day']['lp'] = round($daily_widget_data['month_to_date_sales']['lp'] / $dates['c_mins'] * 1440, 2);
$daily_widget_data['forecasted_sales']['c'] = round($daily_widget_data['avg_sales/day']['c'] * $dates['c_month_days'], 2);
// Code for calculating the sales frequency
$daily_widget_data['one_sale_every']['c'] = !empty($curr_orders) ? round($dates['c_mins'] / 1440 / $curr_orders, 2) : '0';
$daily_widget_data['one_sale_every']['lp'] = !empty($lp_orders) ? round($dates['c_mins'] / 1440 / $lp_orders, 2) : '0';
}
// ================================================
// Todays Customers
// ================================================
// Get minimum user id for people registered yesterday and today
$query = $wpdb->prepare("SELECT date(user_registered) as date, IFNULL(MIN(ID), -1) as min_user_id\n\t\t\t\t\t\t\t\t FROM `{$wpdb->prefix}users` \n\t\t\t\t\t\t\t\t WHERE user_registered BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t GROUP BY date(user_registered)", $dates['yesterday'], $dates['today']);
$results = $wpdb->get_results($query, 'ARRAY_A');
$c_cust_cond = $lp_cust_cond = '';
if (count($results) > 0) {
foreach ($results as $row) {
if ($row['date'] == $dates['today']) {
$c_cust_cond = !empty($row['min_user_id']) ? " OR user_id >= " . $row['min_user_id'] : '';
} else {
$lp_cust_cond = !empty($row['min_user_id']) ? " OR user_id >= " . $row['min_user_id'] : '';
}
}
}
// Get number of customers - guests are all considered new customers, but registered users need to have id greater than the min user id
$query = $wpdb->prepare("SELECT created_date as date,\n\t\t\t\t\t\t\t\t\t\tCOUNT( distinct( CASE WHEN user_id > 0 THEN user_id ELSE billing_email END ) ) as customers\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 = '%s' AND (user_id = 0 " . $c_cust_cond . "))\n\t\t\t\t\t\t\t\t\t\t\t\tOR (created_date = '%s' AND (user_id = 0 " . $lp_cust_cond . ")) )\n\t\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\t\tAND type = 'shop_order'\n\t\t\t\t\t\t\t\t\tGROUP BY created_date", $dates['today'], $dates['yesterday']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
if ($row['date'] == $dates['today']) {
$daily_widget_data['new_customers_today']['c'] = !empty($row['customers']) ? $row['customers'] : 0;
} else {
$daily_widget_data['new_customers_today']['lp'] = !empty($row['customers']) ? $row['customers'] : 0;
}
}
}
// ================================================
// Orders Unfulfillment
// ================================================
// get the shipping status
$query = $wpdb->prepare("SELECT option_value FROM {$wpdb->prefix}options WHERE option_name = %s", 'woocommerce_calc_shipping');
$result = $wpdb->get_var($query);
if (!empty($result) && $result != 'yes') {
// get no of physical products
$query = $wpdb->prepare("SELECT count(DISTINCT post_id ) as products \n\t\t\t FROM {$wpdb->prefix}postmeta\n\t\t\t WHERE (meta_key = %s AND meta_value = 'no')\n\t\t\t OR (meta_key = %s AND meta_value = 'no')", '_downloadable', '_virtual');
$result = $wpdb->get_var($query);
if (!empty($result) && $result > 0) {
// get no of order to fulfillment
$query = $wpdb->prepare("SELECT o.created_date as date, count( distinct( o.order_id ) ) as orders \n\t\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders as o, {$wpdb->prefix}woo_sr_order_items as oi, {$wpdb->prefix}postmeta as pm \n\t\t\t\t\t\t\t\t\t\t\tWHERE ( o.status = 'wc-processing' AND o.created_date BETWEEN '%s' AND '%s' )\n\t\t\t\t\t\t\t\t\t\t\t\tAND ( o.order_id = oi.order_id )\n\t\t\t\t\t\t\t\t\t\t\t\tAND ((pm.meta_key = '_downloadable' and pm.meta_value = 'no') OR (pm.meta_key = '_virtual' and pm.meta_value = 'no')) \n\t\t\t\t\t\t\t\t\t\t\t\tAND ( pm.post_id = oi.product_id OR pm.post_id = oi.variation_id ) \n\t\t\t\t\t\t\t\t\t\t\tGROUP by o.created_date ", $dates['yesterday'], $dates['today']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
if ($row['date'] == $dates['today']) {
$daily_widget_data['orders_to_fulfill']['c'] = !empty($row['orders']) ? $row['orders'] : 0;
} else {
$daily_widget_data['orders_to_fulfill']['lp'] = !empty($row['orders']) ? $row['orders'] : 0;
}
}
}
} else {
$daily_widget_data['orders_to_fulfill']['c'] = __('NA', $sr_text_domain);
}
} else {
$daily_widget_data['orders_to_fulfill']['c'] = __('NA', $sr_text_domain);
}
if (!empty($format) && $format == 'html') {
return json_encode(sr_get_daily_kpi_data_formatted($daily_widget_data));
} else {
return json_encode($daily_widget_data);
}
}
