function sr_get_sales($start_date, $end_date, $diff_dates, $post)
{
global $wpdb;
$cumm_sales = array();
$date_new = date("Y-m-d", strtotime($start_date));
if ($diff_dates > 0 && $diff_dates <= 30) {
$date_series[$date_new]['post_date'] = $date_new;
$date_series[$date_new]['sales'] = 0;
for ($i = 1; $i <= $diff_dates; $i++) {
$date_new = date("Y-m-d", strtotime($date_new . ' +1 day'));
$date_series[$date_new]['post_date'] = $date_new;
$date_series[$date_new]['sales'] = 0;
}
$end_date_query = $end_date;
//Dates for handling the proper Rendering of the JQplot graph
$min_date_sales = date('Y-m-d', strtotime($start_date . ' -2 day'));
$max_date_sales = date('Y-m-d', strtotime($end_date_query . ' +2 day'));
} else {
if ($diff_dates > 30 && $diff_dates <= 365) {
$month = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
$date_mn = "2012-01-01";
$date_mn_initial = $date_mn;
for ($i = 0; $i < 12; $i++) {
if ($i > 0) {
$date_series[$month[$i]]['post_date'] = date('Y-m-d', strtotime($date_mn . ' +1 month'));
$date_mn = $date_series[$month[$i]]['post_date'];
} else {
$date_series[$month[$i]]['post_date'] = $date_mn;
}
$date_series[$month[$i]]['sales'] = 0;
}
$end_date_query = $end_date;
//Dates for handling the proper Rendering of the JQplot graph
$min_date_sales = date('Y-m-d', strtotime($date_mn_initial . ' -2 month'));
$max_date_sales = date('Y-m-d', strtotime($date_mn . ' +2 month'));
} else {
if ($diff_dates > 365) {
$year_strt = substr($start_date, 0, 4);
$year_end = substr($end_date, 0, 4);
$year_tmp[0] = $year_strt;
for ($i = 1; $i <= $year_end - $year_strt; $i++) {
$year_tmp[$i] = $year_tmp[$i - 1] + 1;
}
for ($i = 0; $i < sizeof($year_tmp); $i++) {
$date_series[$year_tmp[$i]]['post_date'] = $year_tmp[$i] . "-01-01";
$date_series[$year_tmp[$i]]['sales'] = 0;
}
$end_date_query = $end_date;
//Dates for handling the proper Rendering of the JQplot graph
$min_date_sales = $year_strt - 1 . "-01-01";
$max_date_sales = $year_end + 1 . "-01-01";
} else {
$date_series[0]['post_date'] = date("Y-m-d", strtotime($start_date)) . ' ' . "00:00:00";
$date_series[0]['sales'] = 0;
for ($i = 1; $i < 24; $i++) {
$date_new = date("Y-m-d H:i:s ", strtotime($date_new . ' +1 hours'));
$date_series[$i]['post_date'] = $date_new;
$date_series[$i]['sales'] = 0;
}
$end_date_query = $end_date;
$min_date_sales = date("Y-m-d H:i:s", strtotime($start_date . ' -2 hours'));
$max_date_sales = date("Y-m-d", strtotime($start_date . ' +1 day')) . ' ' . "01:00:00";
}
}
}
$total_monthly_sales = 0;
$tot_cumm_orders = 0;
$tot_cumm_orders_qty = 0;
//Query to get the relevant order ids
$query_terms = "SELECT id FROM {$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 WHERE terms.name IN ('completed','processing','on-hold','pending')\n\t AND posts.post_status IN ('publish')";
$terms_post = $wpdb->get_col($query_terms);
$rows_terms_post = $wpdb->num_rows;
if ($rows_terms_post > 0) {
$terms_post = implode(",", $terms_post);
}
if ($diff_dates > 0 && $diff_dates <= 30) {
$select = "DATE_FORMAT(posts.`post_date`, '%Y-%m-%d') AS display_date";
$select_top_prod = "GROUP_CONCAT(order_item.sales order by order_item.order_id SEPARATOR '###') AS sales_details,\n\t GROUP_CONCAT(order_item.quantity order by order_item.order_id SEPARATOR '###') AS quantity_details,\n\t GROUP_CONCAT(DATE_FORMAT(posts.`post_date`, '%Y-%m-%d') by posts.id SEPARATOR '###') AS order_dates";
$select_top_abandoned_prod = "GROUP_CONCAT(FROM_UNIXTIME(abandoned_cart_time, '%Y-%m-%d') order by id SEPARATOR '###') AS abandoned_dates";
$results = sr_query_sales($start_date, $end_date_query, $date_series, $select, "display_date", $select_top_prod, $select_top_abandoned_prod, $terms_post, $post);
} else {
if ($diff_dates > 30 && $diff_dates <= 365) {
$select = "DATE_FORMAT(MAX(posts.`post_date`), '%Y-%m-%d') AS display_date,\n\t DATE_FORMAT(posts.`post_date`, '%b') AS month_nm";
$select_top_prod = "GROUP_CONCAT(order_item.sales order by order_item.order_id SEPARATOR '###') AS sales_details,\n\t GROUP_CONCAT(order_item.quantity order by order_item.order_id SEPARATOR '###') AS quantity_details,\n\t GROUP_CONCAT(DATE_FORMAT(posts.`post_date`, '%b') by posts.id SEPARATOR '###') AS order_dates";
$select_top_abandoned_prod = "GROUP_CONCAT(FROM_UNIXTIME(abandoned_cart_time, '%b') order by id SEPARATOR '###') AS abandoned_dates";
$results = sr_query_sales($start_date, $end_date_query, $date_series, $select, "month_nm", $select_top_prod, $select_top_abandoned_prod, $terms_post, $post);
} else {
if ($diff_dates > 365) {
$select = "DATE_FORMAT(MAX(posts.`post_date`), '%Y-%m-%d') AS display_date,\n\t DATE_FORMAT(posts.`post_date`, '%Y') AS year_nm";
$select_top_prod = "GROUP_CONCAT(order_item.sales order by order_item.order_id SEPARATOR '###') AS sales_details,\n\t GROUP_CONCAT(order_item.quantity order by order_item.order_id SEPARATOR '###') AS quantity_details,\n\t GROUP_CONCAT(DATE_FORMAT(posts.`post_date`, '%Y') by posts.id SEPARATOR '###') AS order_dates";
$select_top_abandoned_prod = "GROUP_CONCAT(FROM_UNIXTIME(abandoned_cart_time, '%Y') order by id SEPARATOR '###') AS abandoned_dates";
$results = sr_query_sales($start_date, $end_date_query, $date_series, $select, "year_nm", $select_top_prod, $select_top_abandoned_prod, $terms_post, $post);
} else {
$select = "DATE_FORMAT(posts.`post_date`, '%Y/%m/%d') AS display_date_time,\n\t DATE_FORMAT(MAX(posts.`post_date`), '%H:%i:%s') AS display_time,\n\t DATE_FORMAT(posts.`post_date`, '%k') AS comp_time";
$select_top_prod = "GROUP_CONCAT(order_item.sales order by order_item.order_id SEPARATOR '###') AS sales_details,\n\t GROUP_CONCAT(order_item.quantity order by order_item.order_id SEPARATOR '###') AS quantity_details,\n\t GROUP_CONCAT(DATE_FORMAT(posts.`post_date`, '%H:%i:%s') by posts.id SEPARATOR '###') AS display_time,\n\t GROUP_CONCAT(DATE_FORMAT(posts.`post_date`, '%k') by posts.id SEPARATOR '###') AS comp_time";
$select_top_abandoned_prod = "GROUP_CONCAT(FROM_UNIXTIME(abandoned_cart_time, '%Y/%m/%d %H:%i:%s') order by id SEPARATOR '###') AS abandoned_dates,\n\t \t\t\t\t\t\t\t GROUP_CONCAT(FROM_UNIXTIME(abandoned_cart_time, '%k') order by id SEPARATOR '###') AS comp_time";
// $end_date_query = date('Y-m-d', strtotime($end_date_query .' +1 day'));
$results = sr_query_sales($start_date, $end_date_query, $date_series, $select, "display_date_time", $select_top_prod, $select_top_abandoned_prod, $terms_post, $post);
}
}
}
if (isset($post['option'])) {
$results[1] = $min_date_sales;
$results[2] = $max_date_sales;
} else {
$results[17] = $min_date_sales;
$results[18] = $max_date_sales;
}
return $results;
}
function sr_get_cumm_stats()
{
$params = !empty($_POST['params']) ? $_POST['params'] : array();
if (!wp_verify_nonce($params['security'], 'smart-reporter-security')) {
die('Security check');
}
$cumm_dates = array();
$cumm_dates['cp_start_date'] = $_POST['start_date'];
$cumm_dates['cp_end_date'] = $_POST['end_date'];
$cumm_dates['cp_diff_dates'] = (strtotime($cumm_dates['cp_end_date']) - strtotime($cumm_dates['cp_start_date'])) / (60 * 60 * 24);
if ($cumm_dates['cp_diff_dates'] > 0) {
$cumm_dates['lp_end_date'] = date('Y-m-d', strtotime($cumm_dates['cp_start_date'] . ' -1 day'));
$cumm_dates['lp_start_date'] = date('Y-m-d', strtotime($cumm_dates['lp_end_date']) - $cumm_dates['cp_diff_dates'] * 60 * 60 * 24);
} else {
$cumm_dates['lp_end_date'] = $cumm_dates['lp_start_date'] = date('Y-m-d', strtotime($cumm_dates['cp_start_date'] . ' -1 day'));
}
$cumm_dates['lp_diff_dates'] = (strtotime($cumm_dates['lp_end_date']) - strtotime($cumm_dates['lp_start_date'])) / (60 * 60 * 24);
// ================================================================================================
// TODO: convert the jqplot code to chart.js
if ($cumm_dates['cp_diff_dates'] > 0 && $cumm_dates['cp_diff_dates'] <= 30) {
$encoded['tick_format'] = "%#d/%b/%Y";
} else {
if ($cumm_dates['cp_diff_dates'] > 30 && $cumm_dates['cp_diff_dates'] <= 365) {
$encoded['tick_format'] = "%b";
} else {
if ($cumm_dates['cp_diff_dates'] > 365) {
$encoded['tick_format'] = "%Y";
} else {
$encoded['tick_format'] = "%H:%M:%S";
}
}
}
// ================================================================================================
if ($cumm_dates['cp_diff_dates'] > 0 && $cumm_dates['cp_diff_dates'] <= 30) {
$cumm_dates['format'] = '%Y-%m-%d';
$date = $cumm_dates['cp_start_date'];
$date_series[0] = $date;
for ($i = 1; $i <= $cumm_dates['cp_diff_dates']; $i++) {
$date = date("Y-m-d", strtotime($date . ' +1 day'));
$date_series[] = $date;
}
} else {
if ($cumm_dates['cp_diff_dates'] > 30 && $cumm_dates['cp_diff_dates'] <= 365) {
$cumm_dates['format'] = '%b';
$date_series = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
} else {
if ($cumm_dates['cp_diff_dates'] > 365) {
$cumm_dates['format'] = '%Y';
$year_strt = substr($cumm_dates['cp_start_date'], 0, 4);
$year_end = substr($cumm_dates['cp_end_date'], 0, 4);
$year_tmp[0] = $year_strt;
for ($i = 1; $i <= $year_end - $year_strt; $i++) {
$year_tmp[$i] = $year_tmp[$i - 1] + 1;
}
for ($i = 0; $i < sizeof($year_tmp); $i++) {
$date_series[] = $year_tmp[$i];
}
} else {
$cumm_dates['format'] = '%H';
$date = $cumm_dates['cp_start_date'];
$date_series[0] = "00:00:00";
for ($i = 1; $i < 24; $i++) {
$date = date("H:i:s", strtotime($date . ' +1 hours'));
$date_series[$i] = $date;
}
}
}
}
if (!empty($_POST['cmd']) && ($_POST['cmd'] == 'sr_summary' || $_POST['cmd'] == 'aprod_export')) {
return sr_query_sales($cumm_dates, $date_series, $_POST);
}
echo sr_query_sales($cumm_dates, $date_series, $_POST);
exit;
}
