Opencart Dashboard show last months statistics

Posted by John Magnolia on Stack Overflow See other posts from Stack Overflow or by John Magnolia
Published on 2012-11-15T11:34:21Z Indexed on 2012/11/18 11:01 UTC
Read the original article Hit count: 412

Filed under:
|

How could I added the option to show the statistics for last month.

PHP

public function chart() {
    $this->load->language('common/home');

    $data = array();

    $data['order'] = array();
    $data['customer'] = array();
    $data['xaxis'] = array();

    $data['order']['label'] = $this->language->get('text_order');
    $data['customer']['label'] = $this->language->get('text_customer');

    if (isset($this->request->get['range'])) {
        $range = $this->request->get['range'];
    } else {
        $range = 'month';
    }

    switch ($range) {
        case 'day':
            for ($i = 0; $i < 24; $i++) {
                $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND (DATE(date_added) = DATE(NOW()) AND HOUR(date_added) = '" . (int)$i . "') GROUP BY HOUR(date_added) ORDER BY date_added ASC");

                if ($query->num_rows) {
                    $data['order']['data'][]  = array($i, (int)$query->row['total']);
                } else {
                    $data['order']['data'][]  = array($i, 0);
                }

                $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE DATE(date_added) = DATE(NOW()) AND HOUR(date_added) = '" . (int)$i . "' GROUP BY HOUR(date_added) ORDER BY date_added ASC");

                if ($query->num_rows) {
                    $data['customer']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['customer']['data'][] = array($i, 0);
                }

                $data['xaxis'][] = array($i, date('H', mktime($i, 0, 0, date('n'), date('j'), date('Y'))));
            }                   
            break;
        case 'week':
            $date_start = strtotime('-' . date('w') . ' days'); 

            for ($i = 0; $i < 7; $i++) {
                $date = date('Y-m-d', $date_start + ($i * 86400));

                $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DATE(date_added)");

                if ($query->num_rows) {
                    $data['order']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['order']['data'][] = array($i, 0);
                }

                $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "customer` WHERE DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DATE(date_added)");

                if ($query->num_rows) {
                    $data['customer']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['customer']['data'][] = array($i, 0);
                }

                $data['xaxis'][] = array($i, date('D', strtotime($date)));
            }

            break;
        default:
        case 'month':
            for ($i = 1; $i <= date('t'); $i++) {
                $date = date('Y') . '-' . date('m') . '-' . $i;

                $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND (DATE(date_added) = '" . $this->db->escape($date) . "') GROUP BY DAY(date_added)");

                if ($query->num_rows) {
                    $data['order']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['order']['data'][] = array($i, 0);
                }   

                $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DAY(date_added)");

                if ($query->num_rows) {
                    $data['customer']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['customer']['data'][] = array($i, 0);
                }   

                $data['xaxis'][] = array($i, date('j', strtotime($date)));
            }
            break;
        case 'year':
            for ($i = 1; $i <= 12; $i++) {
                $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND YEAR(date_added) = '" . date('Y') . "' AND MONTH(date_added) = '" . $i . "' GROUP BY MONTH(date_added)");

                if ($query->num_rows) {
                    $data['order']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['order']['data'][] = array($i, 0);
                }

                $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE YEAR(date_added) = '" . date('Y') . "' AND MONTH(date_added) = '" . $i . "' GROUP BY MONTH(date_added)");

                if ($query->num_rows) { 
                    $data['customer']['data'][] = array($i, (int)$query->row['total']);
                } else {
                    $data['customer']['data'][] = array($i, 0);
                }

                $data['xaxis'][] = array($i, date('M', mktime(0, 0, 0, $i, 1, date('Y'))));
            }           
            break;  
    } 

    $this->response->setOutput(json_encode($data));
}

HTML

<select name="range">
    <option value="day">Today</option>
    <option value="week">This Week</option>
    <option value="month">This Month</option>
    <option value="year">This Year</option>
</select>   

© Stack Overflow or respective owner

Related posts about php

Related posts about opencart