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