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