コード例 #1
0
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;
}
コード例 #2
0
ファイル: json-woo.php プロジェクト: bulats/chef
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;
}
コード例 #3
0
ファイル: reporter-console.php プロジェクト: bulats/chef
                            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>

<!-- 
// ================================================