sale.php (14183B)
1 <?php 2 class ModelExtensionReportSale extends Model { 3 public function getTotalSales($data = array()) { 4 $sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0'"; 5 6 if (!empty($data['filter_date_added'])) { 7 $sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')"; 8 } 9 10 $query = $this->db->query($sql); 11 12 return $query->row['total']; 13 } 14 15 public function getTotalOrdersByCountry() { 16 $query = $this->db->query("SELECT COUNT(*) AS total, SUM(o.total) AS amount, c.iso_code_2 FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "country` c ON (o.payment_country_id = c.country_id) WHERE o.order_status_id > '0' GROUP BY o.payment_country_id"); 17 18 return $query->rows; 19 } 20 21 public function getTotalOrdersByDay() { 22 $implode = array(); 23 24 foreach ($this->config->get('config_complete_status') as $order_status_id) { 25 $implode[] = "'" . (int)$order_status_id . "'"; 26 } 27 28 $order_data = array(); 29 30 for ($i = 0; $i < 24; $i++) { 31 $order_data[$i] = array( 32 'hour' => $i, 33 'total' => 0 34 ); 35 } 36 37 $query = $this->db->query("SELECT COUNT(*) AS total, HOUR(date_added) AS hour FROM `" . DB_PREFIX . "order` WHERE order_status_id IN(" . implode(",", $implode) . ") AND DATE(date_added) = DATE(NOW()) GROUP BY HOUR(date_added) ORDER BY date_added ASC"); 38 39 foreach ($query->rows as $result) { 40 $order_data[$result['hour']] = array( 41 'hour' => $result['hour'], 42 'total' => $result['total'] 43 ); 44 } 45 46 return $order_data; 47 } 48 49 public function getTotalOrdersByWeek() { 50 $implode = array(); 51 52 foreach ($this->config->get('config_complete_status') as $order_status_id) { 53 $implode[] = "'" . (int)$order_status_id . "'"; 54 } 55 56 $order_data = array(); 57 58 $date_start = strtotime('-' . date('w') . ' days'); 59 60 for ($i = 0; $i < 7; $i++) { 61 $date = date('Y-m-d', $date_start + ($i * 86400)); 62 63 $order_data[date('w', strtotime($date))] = array( 64 'day' => date('D', strtotime($date)), 65 'total' => 0 66 ); 67 } 68 69 $query = $this->db->query("SELECT COUNT(*) AS total, date_added FROM `" . DB_PREFIX . "order` WHERE order_status_id IN(" . implode(",", $implode) . ") AND DATE(date_added) >= DATE('" . $this->db->escape(date('Y-m-d', $date_start)) . "') GROUP BY DAYNAME(date_added)"); 70 71 foreach ($query->rows as $result) { 72 $order_data[date('w', strtotime($result['date_added']))] = array( 73 'day' => date('D', strtotime($result['date_added'])), 74 'total' => $result['total'] 75 ); 76 } 77 78 return $order_data; 79 } 80 81 public function getTotalOrdersByMonth() { 82 $implode = array(); 83 84 foreach ($this->config->get('config_complete_status') as $order_status_id) { 85 $implode[] = "'" . (int)$order_status_id . "'"; 86 } 87 88 $order_data = array(); 89 90 for ($i = 1; $i <= date('t'); $i++) { 91 $date = date('Y') . '-' . date('m') . '-' . $i; 92 93 $order_data[date('j', strtotime($date))] = array( 94 'day' => date('d', strtotime($date)), 95 'total' => 0 96 ); 97 } 98 99 $query = $this->db->query("SELECT COUNT(*) AS total, date_added FROM `" . DB_PREFIX . "order` WHERE order_status_id IN(" . implode(",", $implode) . ") AND DATE(date_added) >= '" . $this->db->escape(date('Y') . '-' . date('m') . '-1') . "' GROUP BY DATE(date_added)"); 100 101 foreach ($query->rows as $result) { 102 $order_data[date('j', strtotime($result['date_added']))] = array( 103 'day' => date('d', strtotime($result['date_added'])), 104 'total' => $result['total'] 105 ); 106 } 107 108 return $order_data; 109 } 110 111 public function getTotalOrdersByYear() { 112 $implode = array(); 113 114 foreach ($this->config->get('config_complete_status') as $order_status_id) { 115 $implode[] = "'" . (int)$order_status_id . "'"; 116 } 117 118 $order_data = array(); 119 120 for ($i = 1; $i <= 12; $i++) { 121 $order_data[$i] = array( 122 'month' => date('M', mktime(0, 0, 0, $i)), 123 'total' => 0 124 ); 125 } 126 127 $query = $this->db->query("SELECT COUNT(*) AS total, date_added FROM `" . DB_PREFIX . "order` WHERE order_status_id IN(" . implode(",", $implode) . ") AND YEAR(date_added) = YEAR(NOW()) GROUP BY MONTH(date_added)"); 128 129 foreach ($query->rows as $result) { 130 $order_data[date('n', strtotime($result['date_added']))] = array( 131 'month' => date('M', strtotime($result['date_added'])), 132 'total' => $result['total'] 133 ); 134 } 135 136 return $order_data; 137 } 138 139 public function getOrders($data = array()) { 140 $sql = "SELECT MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end, COUNT(*) AS `orders`, SUM((SELECT SUM(op.quantity) FROM `" . DB_PREFIX . "order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id)) AS products, SUM((SELECT SUM(ot.value) FROM `" . DB_PREFIX . "order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id)) AS tax, SUM(o.total) AS `total` FROM `" . DB_PREFIX . "order` o"; 141 142 if (!empty($data['filter_order_status_id'])) { 143 $sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 144 } else { 145 $sql .= " WHERE o.order_status_id > '0'"; 146 } 147 148 if (!empty($data['filter_date_start'])) { 149 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 150 } 151 152 if (!empty($data['filter_date_end'])) { 153 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 154 } 155 156 if (!empty($data['filter_group'])) { 157 $group = $data['filter_group']; 158 } else { 159 $group = 'week'; 160 } 161 162 switch($group) { 163 case 'day'; 164 $sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added)"; 165 break; 166 default: 167 case 'week': 168 $sql .= " GROUP BY YEAR(o.date_added), WEEK(o.date_added)"; 169 break; 170 case 'month': 171 $sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added)"; 172 break; 173 case 'year': 174 $sql .= " GROUP BY YEAR(o.date_added)"; 175 break; 176 } 177 178 $sql .= " ORDER BY o.date_added DESC"; 179 180 if (isset($data['start']) || isset($data['limit'])) { 181 if ($data['start'] < 0) { 182 $data['start'] = 0; 183 } 184 185 if ($data['limit'] < 1) { 186 $data['limit'] = 20; 187 } 188 189 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 190 } 191 192 $query = $this->db->query($sql); 193 194 return $query->rows; 195 } 196 197 public function getTotalOrders($data = array()) { 198 if (!empty($data['filter_group'])) { 199 $group = $data['filter_group']; 200 } else { 201 $group = 'week'; 202 } 203 204 switch($group) { 205 case 'day'; 206 $sql = "SELECT COUNT(DISTINCT YEAR(date_added), MONTH(date_added), DAY(date_added)) AS total FROM `" . DB_PREFIX . "order`"; 207 break; 208 default: 209 case 'week': 210 $sql = "SELECT COUNT(DISTINCT YEAR(date_added), WEEK(date_added)) AS total FROM `" . DB_PREFIX . "order`"; 211 break; 212 case 'month': 213 $sql = "SELECT COUNT(DISTINCT YEAR(date_added), MONTH(date_added)) AS total FROM `" . DB_PREFIX . "order`"; 214 break; 215 case 'year': 216 $sql = "SELECT COUNT(DISTINCT YEAR(date_added)) AS total FROM `" . DB_PREFIX . "order`"; 217 break; 218 } 219 220 if (!empty($data['filter_order_status_id'])) { 221 $sql .= " WHERE order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 222 } else { 223 $sql .= " WHERE order_status_id > '0'"; 224 } 225 226 if (!empty($data['filter_date_start'])) { 227 $sql .= " AND DATE(date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 228 } 229 230 if (!empty($data['filter_date_end'])) { 231 $sql .= " AND DATE(date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 232 } 233 234 $query = $this->db->query($sql); 235 236 return $query->row['total']; 237 } 238 239 public function getTaxes($data = array()) { 240 $sql = "SELECT MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end, ot.title, SUM(ot.value) AS total, COUNT(o.order_id) AS `orders` FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (ot.order_id = o.order_id) WHERE ot.code = 'tax'"; 241 242 if (!empty($data['filter_order_status_id'])) { 243 $sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 244 } else { 245 $sql .= " AND o.order_status_id > '0'"; 246 } 247 248 if (!empty($data['filter_date_start'])) { 249 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 250 } 251 252 if (!empty($data['filter_date_end'])) { 253 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 254 } 255 256 if (!empty($data['filter_group'])) { 257 $group = $data['filter_group']; 258 } else { 259 $group = 'week'; 260 } 261 262 switch($group) { 263 case 'day'; 264 $sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added), ot.title"; 265 break; 266 default: 267 case 'week': 268 $sql .= " GROUP BY YEAR(o.date_added), WEEK(o.date_added), ot.title"; 269 break; 270 case 'month': 271 $sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), ot.title"; 272 break; 273 case 'year': 274 $sql .= " GROUP BY YEAR(o.date_added), ot.title"; 275 break; 276 } 277 278 if (isset($data['start']) || isset($data['limit'])) { 279 if ($data['start'] < 0) { 280 $data['start'] = 0; 281 } 282 283 if ($data['limit'] < 1) { 284 $data['limit'] = 20; 285 } 286 287 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 288 } 289 290 $query = $this->db->query($sql); 291 292 return $query->rows; 293 } 294 295 public function getTotalTaxes($data = array()) { 296 if (!empty($data['filter_group'])) { 297 $group = $data['filter_group']; 298 } else { 299 $group = 'week'; 300 } 301 302 switch($group) { 303 case 'day'; 304 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 305 break; 306 default: 307 case 'week': 308 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), WEEK(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 309 break; 310 case 'month': 311 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), MONTH(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 312 break; 313 case 'year': 314 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 315 break; 316 } 317 318 $sql .= " LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (o.order_id = ot.order_id) WHERE ot.code = 'tax'"; 319 320 if (!empty($data['filter_order_status_id'])) { 321 $sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 322 } else { 323 $sql .= " AND o.order_status_id > '0'"; 324 } 325 326 if (!empty($data['filter_date_start'])) { 327 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 328 } 329 330 if (!empty($data['filter_date_end'])) { 331 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 332 } 333 334 $query = $this->db->query($sql); 335 336 return $query->row['total']; 337 } 338 339 public function getShipping($data = array()) { 340 $sql = "SELECT MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end, ot.title, SUM(ot.value) AS total, COUNT(o.order_id) AS `orders` FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (o.order_id = ot.order_id) WHERE ot.code = 'shipping'"; 341 342 if (!empty($data['filter_order_status_id'])) { 343 $sql .= " AND o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 344 } else { 345 $sql .= " AND o.order_status_id > '0'"; 346 } 347 348 if (!empty($data['filter_date_start'])) { 349 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 350 } 351 352 if (!empty($data['filter_date_end'])) { 353 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 354 } 355 356 if (!empty($data['filter_group'])) { 357 $group = $data['filter_group']; 358 } else { 359 $group = 'week'; 360 } 361 362 switch($group) { 363 case 'day'; 364 $sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added), ot.title"; 365 break; 366 default: 367 case 'week': 368 $sql .= " GROUP BY YEAR(o.date_added), WEEK(o.date_added), ot.title"; 369 break; 370 case 'month': 371 $sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), ot.title"; 372 break; 373 case 'year': 374 $sql .= " GROUP BY YEAR(o.date_added), ot.title"; 375 break; 376 } 377 378 if (isset($data['start']) || isset($data['limit'])) { 379 if ($data['start'] < 0) { 380 $data['start'] = 0; 381 } 382 383 if ($data['limit'] < 1) { 384 $data['limit'] = 20; 385 } 386 387 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 388 } 389 390 $query = $this->db->query($sql); 391 392 return $query->rows; 393 } 394 395 public function getTotalShipping($data = array()) { 396 if (!empty($data['filter_group'])) { 397 $group = $data['filter_group']; 398 } else { 399 $group = 'week'; 400 } 401 402 switch($group) { 403 case 'day'; 404 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 405 break; 406 default: 407 case 'week': 408 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), WEEK(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 409 break; 410 case 'month': 411 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), MONTH(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 412 break; 413 case 'year': 414 $sql = "SELECT COUNT(DISTINCT YEAR(o.date_added), ot.title) AS total FROM `" . DB_PREFIX . "order` o"; 415 break; 416 } 417 418 $sql .= " LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (o.order_id = ot.order_id) WHERE ot.code = 'shipping'"; 419 420 if (!empty($data['filter_order_status_id'])) { 421 $sql .= " AND order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 422 } else { 423 $sql .= " AND order_status_id > '0'"; 424 } 425 426 if (!empty($data['filter_date_start'])) { 427 $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'"; 428 } 429 430 if (!empty($data['filter_date_end'])) { 431 $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'"; 432 } 433 434 $query = $this->db->query($sql); 435 436 return $query->row['total']; 437 } 438 }