Opencart Dashboard show last months statistics
- by John Magnolia
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>
