order.php (19321B)
1 <?php 2 class ModelSaleOrder extends Model { 3 public function getOrder($order_id) { 4 $order_query = $this->db->query("SELECT *, (SELECT CONCAT(c.firstname, ' ', c.lastname) FROM " . DB_PREFIX . "customer c WHERE c.customer_id = o.customer_id) AS customer, (SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS order_status FROM `" . DB_PREFIX . "order` o WHERE o.order_id = '" . (int)$order_id . "'"); 5 6 if ($order_query->num_rows) { 7 $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['payment_country_id'] . "'"); 8 9 if ($country_query->num_rows) { 10 $payment_iso_code_2 = $country_query->row['iso_code_2']; 11 $payment_iso_code_3 = $country_query->row['iso_code_3']; 12 } else { 13 $payment_iso_code_2 = ''; 14 $payment_iso_code_3 = ''; 15 } 16 17 $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['payment_zone_id'] . "'"); 18 19 if ($zone_query->num_rows) { 20 $payment_zone_code = $zone_query->row['code']; 21 } else { 22 $payment_zone_code = ''; 23 } 24 25 $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['shipping_country_id'] . "'"); 26 27 if ($country_query->num_rows) { 28 $shipping_iso_code_2 = $country_query->row['iso_code_2']; 29 $shipping_iso_code_3 = $country_query->row['iso_code_3']; 30 } else { 31 $shipping_iso_code_2 = ''; 32 $shipping_iso_code_3 = ''; 33 } 34 35 $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['shipping_zone_id'] . "'"); 36 37 if ($zone_query->num_rows) { 38 $shipping_zone_code = $zone_query->row['code']; 39 } else { 40 $shipping_zone_code = ''; 41 } 42 43 $reward = 0; 44 45 $order_product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'"); 46 47 foreach ($order_product_query->rows as $product) { 48 $reward += $product['reward']; 49 } 50 51 $this->load->model('customer/customer'); 52 53 $affiliate_info = $this->model_customer_customer->getCustomer($order_query->row['affiliate_id']); 54 55 if ($affiliate_info) { 56 $affiliate_firstname = $affiliate_info['firstname']; 57 $affiliate_lastname = $affiliate_info['lastname']; 58 } else { 59 $affiliate_firstname = ''; 60 $affiliate_lastname = ''; 61 } 62 63 $this->load->model('localisation/language'); 64 65 $language_info = $this->model_localisation_language->getLanguage($order_query->row['language_id']); 66 67 if ($language_info) { 68 $language_code = $language_info['code']; 69 } else { 70 $language_code = $this->config->get('config_language'); 71 } 72 73 return array( 74 'order_id' => $order_query->row['order_id'], 75 'invoice_no' => $order_query->row['invoice_no'], 76 'invoice_prefix' => $order_query->row['invoice_prefix'], 77 'store_id' => $order_query->row['store_id'], 78 'store_name' => $order_query->row['store_name'], 79 'store_url' => $order_query->row['store_url'], 80 'customer_id' => $order_query->row['customer_id'], 81 'customer' => $order_query->row['customer'], 82 'customer_group_id' => $order_query->row['customer_group_id'], 83 'firstname' => $order_query->row['firstname'], 84 'lastname' => $order_query->row['lastname'], 85 'email' => $order_query->row['email'], 86 'telephone' => $order_query->row['telephone'], 87 'custom_field' => json_decode($order_query->row['custom_field'], true), 88 'payment_firstname' => $order_query->row['payment_firstname'], 89 'payment_lastname' => $order_query->row['payment_lastname'], 90 'payment_company' => $order_query->row['payment_company'], 91 'payment_address_1' => $order_query->row['payment_address_1'], 92 'payment_address_2' => $order_query->row['payment_address_2'], 93 'payment_postcode' => $order_query->row['payment_postcode'], 94 'payment_city' => $order_query->row['payment_city'], 95 'payment_zone_id' => $order_query->row['payment_zone_id'], 96 'payment_zone' => $order_query->row['payment_zone'], 97 'payment_zone_code' => $payment_zone_code, 98 'payment_country_id' => $order_query->row['payment_country_id'], 99 'payment_country' => $order_query->row['payment_country'], 100 'payment_iso_code_2' => $payment_iso_code_2, 101 'payment_iso_code_3' => $payment_iso_code_3, 102 'payment_address_format' => $order_query->row['payment_address_format'], 103 'payment_custom_field' => json_decode($order_query->row['payment_custom_field'], true), 104 'payment_method' => $order_query->row['payment_method'], 105 'payment_code' => $order_query->row['payment_code'], 106 'shipping_firstname' => $order_query->row['shipping_firstname'], 107 'shipping_lastname' => $order_query->row['shipping_lastname'], 108 'shipping_company' => $order_query->row['shipping_company'], 109 'shipping_address_1' => $order_query->row['shipping_address_1'], 110 'shipping_address_2' => $order_query->row['shipping_address_2'], 111 'shipping_postcode' => $order_query->row['shipping_postcode'], 112 'shipping_city' => $order_query->row['shipping_city'], 113 'shipping_zone_id' => $order_query->row['shipping_zone_id'], 114 'shipping_zone' => $order_query->row['shipping_zone'], 115 'shipping_zone_code' => $shipping_zone_code, 116 'shipping_country_id' => $order_query->row['shipping_country_id'], 117 'shipping_country' => $order_query->row['shipping_country'], 118 'shipping_iso_code_2' => $shipping_iso_code_2, 119 'shipping_iso_code_3' => $shipping_iso_code_3, 120 'shipping_address_format' => $order_query->row['shipping_address_format'], 121 'shipping_custom_field' => json_decode($order_query->row['shipping_custom_field'], true), 122 'shipping_method' => $order_query->row['shipping_method'], 123 'shipping_code' => $order_query->row['shipping_code'], 124 'comment' => $order_query->row['comment'], 125 'total' => $order_query->row['total'], 126 'reward' => $reward, 127 'order_status_id' => $order_query->row['order_status_id'], 128 'order_status' => $order_query->row['order_status'], 129 'affiliate_id' => $order_query->row['affiliate_id'], 130 'affiliate_firstname' => $affiliate_firstname, 131 'affiliate_lastname' => $affiliate_lastname, 132 'commission' => $order_query->row['commission'], 133 'language_id' => $order_query->row['language_id'], 134 'language_code' => $language_code, 135 'currency_id' => $order_query->row['currency_id'], 136 'currency_code' => $order_query->row['currency_code'], 137 'currency_value' => $order_query->row['currency_value'], 138 'ip' => $order_query->row['ip'], 139 'forwarded_ip' => $order_query->row['forwarded_ip'], 140 'user_agent' => $order_query->row['user_agent'], 141 'accept_language' => $order_query->row['accept_language'], 142 'date_added' => $order_query->row['date_added'], 143 'date_modified' => $order_query->row['date_modified'] 144 ); 145 } else { 146 return; 147 } 148 } 149 150 public function getOrders($data = array()) { 151 $sql = "SELECT o.order_id, CONCAT(o.firstname, ' ', o.lastname) AS customer, (SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS order_status, o.shipping_code, o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified FROM `" . DB_PREFIX . "order` o"; 152 153 if (!empty($data['filter_order_status'])) { 154 $implode = array(); 155 156 $order_statuses = explode(',', $data['filter_order_status']); 157 158 foreach ($order_statuses as $order_status_id) { 159 $implode[] = "o.order_status_id = '" . (int)$order_status_id . "'"; 160 } 161 162 if ($implode) { 163 $sql .= " WHERE (" . implode(" OR ", $implode) . ")"; 164 } 165 } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') { 166 $sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 167 } else { 168 $sql .= " WHERE o.order_status_id > '0'"; 169 } 170 171 if (!empty($data['filter_order_id'])) { 172 $sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'"; 173 } 174 175 if (!empty($data['filter_customer'])) { 176 $sql .= " AND CONCAT(o.firstname, ' ', o.lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'"; 177 } 178 179 if (!empty($data['filter_date_added'])) { 180 $sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')"; 181 } 182 183 if (!empty($data['filter_date_modified'])) { 184 $sql .= " AND DATE(o.date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')"; 185 } 186 187 if (!empty($data['filter_total'])) { 188 $sql .= " AND o.total = '" . (float)$data['filter_total'] . "'"; 189 } 190 191 $sort_data = array( 192 'o.order_id', 193 'customer', 194 'order_status', 195 'o.date_added', 196 'o.date_modified', 197 'o.total' 198 ); 199 200 if (isset($data['sort']) && in_array($data['sort'], $sort_data)) { 201 $sql .= " ORDER BY " . $data['sort']; 202 } else { 203 $sql .= " ORDER BY o.order_id"; 204 } 205 206 if (isset($data['order']) && ($data['order'] == 'DESC')) { 207 $sql .= " DESC"; 208 } else { 209 $sql .= " ASC"; 210 } 211 212 if (isset($data['start']) || isset($data['limit'])) { 213 if ($data['start'] < 0) { 214 $data['start'] = 0; 215 } 216 217 if ($data['limit'] < 1) { 218 $data['limit'] = 20; 219 } 220 221 $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; 222 } 223 224 $query = $this->db->query($sql); 225 226 return $query->rows; 227 } 228 229 public function getOrderProducts($order_id) { 230 $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'"); 231 232 return $query->rows; 233 } 234 235 public function getOrderOptions($order_id, $order_product_id) { 236 $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product_id . "'"); 237 238 return $query->rows; 239 } 240 241 public function getOrderVouchers($order_id) { 242 $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_voucher WHERE order_id = '" . (int)$order_id . "'"); 243 244 return $query->rows; 245 } 246 247 public function getOrderVoucherByVoucherId($voucher_id) { 248 $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE voucher_id = '" . (int)$voucher_id . "'"); 249 250 return $query->row; 251 } 252 253 public function getOrderTotals($order_id) { 254 $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order"); 255 256 return $query->rows; 257 } 258 259 public function getTotalOrders($data = array()) { 260 $sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order`"; 261 262 if (!empty($data['filter_order_status'])) { 263 $implode = array(); 264 265 $order_statuses = explode(',', $data['filter_order_status']); 266 267 foreach ($order_statuses as $order_status_id) { 268 $implode[] = "order_status_id = '" . (int)$order_status_id . "'"; 269 } 270 271 if ($implode) { 272 $sql .= " WHERE (" . implode(" OR ", $implode) . ")"; 273 } 274 } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') { 275 $sql .= " WHERE order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 276 } else { 277 $sql .= " WHERE order_status_id > '0'"; 278 } 279 280 if (!empty($data['filter_order_id'])) { 281 $sql .= " AND order_id = '" . (int)$data['filter_order_id'] . "'"; 282 } 283 284 if (!empty($data['filter_customer'])) { 285 $sql .= " AND CONCAT(firstname, ' ', lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'"; 286 } 287 288 if (!empty($data['filter_date_added'])) { 289 $sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')"; 290 } 291 292 if (!empty($data['filter_date_modified'])) { 293 $sql .= " AND DATE(date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')"; 294 } 295 296 if (!empty($data['filter_total'])) { 297 $sql .= " AND total = '" . (float)$data['filter_total'] . "'"; 298 } 299 300 $query = $this->db->query($sql); 301 302 return $query->row['total']; 303 } 304 305 public function getTotalOrdersByStoreId($store_id) { 306 $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE store_id = '" . (int)$store_id . "'"); 307 308 return $query->row['total']; 309 } 310 311 public function getTotalOrdersByOrderStatusId($order_status_id) { 312 $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$order_status_id . "' AND order_status_id > '0'"); 313 314 return $query->row['total']; 315 } 316 317 public function getTotalOrdersByProcessingStatus() { 318 $implode = array(); 319 320 $order_statuses = $this->config->get('config_processing_status'); 321 322 foreach ($order_statuses as $order_status_id) { 323 $implode[] = "order_status_id = '" . (int)$order_status_id . "'"; 324 } 325 326 if ($implode) { 327 $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode)); 328 329 return $query->row['total']; 330 } else { 331 return 0; 332 } 333 } 334 335 public function getTotalOrdersByCompleteStatus() { 336 $implode = array(); 337 338 $order_statuses = $this->config->get('config_complete_status'); 339 340 foreach ($order_statuses as $order_status_id) { 341 $implode[] = "order_status_id = '" . (int)$order_status_id . "'"; 342 } 343 344 if ($implode) { 345 $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode) . ""); 346 347 return $query->row['total']; 348 } else { 349 return 0; 350 } 351 } 352 353 public function getTotalOrdersByLanguageId($language_id) { 354 $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE language_id = '" . (int)$language_id . "' AND order_status_id > '0'"); 355 356 return $query->row['total']; 357 } 358 359 public function getTotalOrdersByCurrencyId($currency_id) { 360 $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE currency_id = '" . (int)$currency_id . "' AND order_status_id > '0'"); 361 362 return $query->row['total']; 363 } 364 365 public function getTotalSales($data = array()) { 366 $sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order`"; 367 368 if (!empty($data['filter_order_status'])) { 369 $implode = array(); 370 371 $order_statuses = explode(',', $data['filter_order_status']); 372 373 foreach ($order_statuses as $order_status_id) { 374 $implode[] = "order_status_id = '" . (int)$order_status_id . "'"; 375 } 376 377 if ($implode) { 378 $sql .= " WHERE (" . implode(" OR ", $implode) . ")"; 379 } 380 } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') { 381 $sql .= " WHERE order_status_id = '" . (int)$data['filter_order_status_id'] . "'"; 382 } else { 383 $sql .= " WHERE order_status_id > '0'"; 384 } 385 386 if (!empty($data['filter_order_id'])) { 387 $sql .= " AND order_id = '" . (int)$data['filter_order_id'] . "'"; 388 } 389 390 if (!empty($data['filter_customer'])) { 391 $sql .= " AND CONCAT(firstname, ' ', o.lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'"; 392 } 393 394 if (!empty($data['filter_date_added'])) { 395 $sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')"; 396 } 397 398 if (!empty($data['filter_date_modified'])) { 399 $sql .= " AND DATE(date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')"; 400 } 401 402 if (!empty($data['filter_total'])) { 403 $sql .= " AND total = '" . (float)$data['filter_total'] . "'"; 404 } 405 406 $query = $this->db->query($sql); 407 408 return $query->row['total']; 409 } 410 411 public function createInvoiceNo($order_id) { 412 $order_info = $this->getOrder($order_id); 413 414 if ($order_info && !$order_info['invoice_no']) { 415 $query = $this->db->query("SELECT MAX(invoice_no) AS invoice_no FROM `" . DB_PREFIX . "order` WHERE invoice_prefix = '" . $this->db->escape($order_info['invoice_prefix']) . "'"); 416 417 if ($query->row['invoice_no']) { 418 $invoice_no = $query->row['invoice_no'] + 1; 419 } else { 420 $invoice_no = 1; 421 } 422 423 $this->db->query("UPDATE `" . DB_PREFIX . "order` SET invoice_no = '" . (int)$invoice_no . "', invoice_prefix = '" . $this->db->escape($order_info['invoice_prefix']) . "' WHERE order_id = '" . (int)$order_id . "'"); 424 425 return $order_info['invoice_prefix'] . $invoice_no; 426 } 427 } 428 429 public function getOrderHistories($order_id, $start = 0, $limit = 10) { 430 if ($start < 0) { 431 $start = 0; 432 } 433 434 if ($limit < 1) { 435 $limit = 10; 436 } 437 438 $query = $this->db->query("SELECT oh.date_added, os.name AS status, oh.comment, oh.notify FROM " . DB_PREFIX . "order_history oh LEFT JOIN " . DB_PREFIX . "order_status os ON oh.order_status_id = os.order_status_id WHERE oh.order_id = '" . (int)$order_id . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY oh.date_added DESC LIMIT " . (int)$start . "," . (int)$limit); 439 440 return $query->rows; 441 } 442 443 public function getTotalOrderHistories($order_id) { 444 $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "'"); 445 446 return $query->row['total']; 447 } 448 449 public function getTotalOrderHistoriesByOrderStatusId($order_status_id) { 450 $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_status_id = '" . (int)$order_status_id . "'"); 451 452 return $query->row['total']; 453 } 454 455 public function getEmailsByProductsOrdered($products, $start, $end) { 456 $implode = array(); 457 458 foreach ($products as $product_id) { 459 $implode[] = "op.product_id = '" . (int)$product_id . "'"; 460 } 461 462 $query = $this->db->query("SELECT DISTINCT email FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> '0' LIMIT " . (int)$start . "," . (int)$end); 463 464 return $query->rows; 465 } 466 467 public function getTotalEmailsByProductsOrdered($products) { 468 $implode = array(); 469 470 foreach ($products as $product_id) { 471 $implode[] = "op.product_id = '" . (int)$product_id . "'"; 472 } 473 474 $query = $this->db->query("SELECT COUNT(DISTINCT email) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> '0'"); 475 476 return $query->row['total']; 477 } 478 }