| 1: | <?php
|
| 2: | namespace Opencart\Admin\Model\Sale;
|
| 3: | |
| 4: | |
| 5: | |
| 6: | |
| 7: |
|
| 8: | class Order extends \Opencart\System\Engine\Model {
|
| 9: | |
| 10: | |
| 11: | |
| 12: | |
| 13: | |
| 14: | |
| 15: |
|
| 16: | public function getOrder(int $order_id): array {
|
| 17: | $order_query = $this->db->query("SELECT *, (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 . "'");
|
| 18: |
|
| 19: | if ($order_query->num_rows) {
|
| 20: | $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE `country_id` = '" . (int)$order_query->row['payment_country_id'] . "'");
|
| 21: |
|
| 22: | if ($country_query->num_rows) {
|
| 23: | $payment_iso_code_2 = $country_query->row['iso_code_2'];
|
| 24: | $payment_iso_code_3 = $country_query->row['iso_code_3'];
|
| 25: | } else {
|
| 26: | $payment_iso_code_2 = '';
|
| 27: | $payment_iso_code_3 = '';
|
| 28: | }
|
| 29: |
|
| 30: | $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE `zone_id` = '" . (int)$order_query->row['payment_zone_id'] . "'");
|
| 31: |
|
| 32: | if ($zone_query->num_rows) {
|
| 33: | $payment_zone_code = $zone_query->row['code'];
|
| 34: | } else {
|
| 35: | $payment_zone_code = '';
|
| 36: | }
|
| 37: |
|
| 38: | $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE `country_id` = '" . (int)$order_query->row['shipping_country_id'] . "'");
|
| 39: |
|
| 40: | if ($country_query->num_rows) {
|
| 41: | $shipping_iso_code_2 = $country_query->row['iso_code_2'];
|
| 42: | $shipping_iso_code_3 = $country_query->row['iso_code_3'];
|
| 43: | } else {
|
| 44: | $shipping_iso_code_2 = '';
|
| 45: | $shipping_iso_code_3 = '';
|
| 46: | }
|
| 47: |
|
| 48: | $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE `zone_id` = '" . (int)$order_query->row['shipping_zone_id'] . "'");
|
| 49: |
|
| 50: | if ($zone_query->num_rows) {
|
| 51: | $shipping_zone_code = $zone_query->row['code'];
|
| 52: | } else {
|
| 53: | $shipping_zone_code = '';
|
| 54: | }
|
| 55: |
|
| 56: | $reward = 0;
|
| 57: |
|
| 58: | $order_product_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "'");
|
| 59: |
|
| 60: | foreach ($order_product_query->rows as $product) {
|
| 61: | $reward += $product['reward'];
|
| 62: | }
|
| 63: |
|
| 64: | $this->load->model('customer/customer');
|
| 65: |
|
| 66: | $affiliate_info = $this->model_customer_customer->getCustomer($order_query->row['affiliate_id']);
|
| 67: |
|
| 68: | if ($affiliate_info) {
|
| 69: | $affiliate = $affiliate_info['firstname'] . ' ' . $affiliate_info['lastname'];
|
| 70: | } else {
|
| 71: | $affiliate = '';
|
| 72: | }
|
| 73: |
|
| 74: | $this->load->model('localisation/language');
|
| 75: |
|
| 76: | $language_info = $this->model_localisation_language->getLanguage($order_query->row['language_id']);
|
| 77: |
|
| 78: | if ($language_info) {
|
| 79: | $language_code = $language_info['code'];
|
| 80: | } else {
|
| 81: | $language_code = $this->config->get('config_language');
|
| 82: | }
|
| 83: |
|
| 84: | return [
|
| 85: | 'order_id' => $order_query->row['order_id'],
|
| 86: | 'invoice_no' => $order_query->row['invoice_no'],
|
| 87: | 'invoice_prefix' => $order_query->row['invoice_prefix'],
|
| 88: | 'store_id' => $order_query->row['store_id'],
|
| 89: | 'store_name' => $order_query->row['store_name'],
|
| 90: | 'store_url' => $order_query->row['store_url'],
|
| 91: | 'customer_id' => $order_query->row['customer_id'],
|
| 92: | 'customer_group_id' => $order_query->row['customer_group_id'],
|
| 93: | 'firstname' => $order_query->row['firstname'],
|
| 94: | 'lastname' => $order_query->row['lastname'],
|
| 95: | 'email' => $order_query->row['email'],
|
| 96: | 'telephone' => $order_query->row['telephone'],
|
| 97: | 'custom_field' => json_decode($order_query->row['custom_field'], true),
|
| 98: | 'payment_address_id' => $order_query->row['payment_address_id'],
|
| 99: | 'payment_firstname' => $order_query->row['payment_firstname'],
|
| 100: | 'payment_lastname' => $order_query->row['payment_lastname'],
|
| 101: | 'payment_company' => $order_query->row['payment_company'],
|
| 102: | 'payment_address_1' => $order_query->row['payment_address_1'],
|
| 103: | 'payment_address_2' => $order_query->row['payment_address_2'],
|
| 104: | 'payment_postcode' => $order_query->row['payment_postcode'],
|
| 105: | 'payment_city' => $order_query->row['payment_city'],
|
| 106: | 'payment_zone_id' => $order_query->row['payment_zone_id'],
|
| 107: | 'payment_zone' => $order_query->row['payment_zone'],
|
| 108: | 'payment_zone_code' => $payment_zone_code,
|
| 109: | 'payment_country_id' => $order_query->row['payment_country_id'],
|
| 110: | 'payment_country' => $order_query->row['payment_country'],
|
| 111: | 'payment_iso_code_2' => $payment_iso_code_2,
|
| 112: | 'payment_iso_code_3' => $payment_iso_code_3,
|
| 113: | 'payment_address_format' => $order_query->row['payment_address_format'],
|
| 114: | 'payment_custom_field' => json_decode($order_query->row['payment_custom_field'], true),
|
| 115: | 'payment_method' => json_decode($order_query->row['payment_method'], true),
|
| 116: | 'shipping_address_id' => $order_query->row['shipping_address_id'],
|
| 117: | 'shipping_firstname' => $order_query->row['shipping_firstname'],
|
| 118: | 'shipping_lastname' => $order_query->row['shipping_lastname'],
|
| 119: | 'shipping_company' => $order_query->row['shipping_company'],
|
| 120: | 'shipping_address_1' => $order_query->row['shipping_address_1'],
|
| 121: | 'shipping_address_2' => $order_query->row['shipping_address_2'],
|
| 122: | 'shipping_postcode' => $order_query->row['shipping_postcode'],
|
| 123: | 'shipping_city' => $order_query->row['shipping_city'],
|
| 124: | 'shipping_zone_id' => $order_query->row['shipping_zone_id'],
|
| 125: | 'shipping_zone' => $order_query->row['shipping_zone'],
|
| 126: | 'shipping_zone_code' => $shipping_zone_code,
|
| 127: | 'shipping_country_id' => $order_query->row['shipping_country_id'],
|
| 128: | 'shipping_country' => $order_query->row['shipping_country'],
|
| 129: | 'shipping_iso_code_2' => $shipping_iso_code_2,
|
| 130: | 'shipping_iso_code_3' => $shipping_iso_code_3,
|
| 131: | 'shipping_address_format' => $order_query->row['shipping_address_format'],
|
| 132: | 'shipping_custom_field' => json_decode($order_query->row['shipping_custom_field'], true),
|
| 133: | 'shipping_method' => json_decode($order_query->row['shipping_method'], true),
|
| 134: | 'comment' => $order_query->row['comment'],
|
| 135: | 'total' => $order_query->row['total'],
|
| 136: | 'reward' => $reward,
|
| 137: | 'order_status_id' => $order_query->row['order_status_id'],
|
| 138: | 'order_status' => $order_query->row['order_status'],
|
| 139: | 'affiliate_id' => $order_query->row['affiliate_id'],
|
| 140: | 'affiliate' => $affiliate,
|
| 141: | 'commission' => $order_query->row['commission'],
|
| 142: | 'language_id' => $order_query->row['language_id'],
|
| 143: | 'language_code' => $language_code,
|
| 144: | 'currency_id' => $order_query->row['currency_id'],
|
| 145: | 'currency_code' => $order_query->row['currency_code'],
|
| 146: | 'currency_value' => $order_query->row['currency_value'],
|
| 147: | 'ip' => $order_query->row['ip'],
|
| 148: | 'forwarded_ip' => $order_query->row['forwarded_ip'],
|
| 149: | 'user_agent' => $order_query->row['user_agent'],
|
| 150: | 'accept_language' => $order_query->row['accept_language'],
|
| 151: | 'date_added' => $order_query->row['date_added'],
|
| 152: | 'date_modified' => $order_query->row['date_modified']
|
| 153: | ];
|
| 154: | } else {
|
| 155: | return [];
|
| 156: | }
|
| 157: | }
|
| 158: |
|
| 159: | |
| 160: | |
| 161: | |
| 162: | |
| 163: | |
| 164: | |
| 165: |
|
| 166: | public function getOrders(array $data = []): array {
|
| 167: | $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`.`store_name`, `o`.`custom_field`, `o`.`payment_method`, `o`.`payment_custom_field`, `o`.`shipping_method`, `o`.`shipping_custom_field`, `o`.`total`, `o`.`currency_code`, `o`.`currency_value`, `o`.`date_added`, `o`.`date_modified` FROM `" . DB_PREFIX . "order` `o`";
|
| 168: |
|
| 169: | if (!empty($data['filter_order_status'])) {
|
| 170: | $implode = [];
|
| 171: |
|
| 172: | $order_statuses = explode(',', $data['filter_order_status']);
|
| 173: | $order_statuses = array_filter($order_statuses);
|
| 174: |
|
| 175: | foreach ($order_statuses as $order_status_id) {
|
| 176: | $implode[] = "`o`.`order_status_id` = '" . (int)$order_status_id . "'";
|
| 177: | }
|
| 178: |
|
| 179: | if ($implode) {
|
| 180: | $sql .= " WHERE (" . implode(" OR ", $implode) . ")";
|
| 181: | }
|
| 182: | } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
|
| 183: | $sql .= " WHERE `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
|
| 184: | } else {
|
| 185: | $sql .= " WHERE `o`.`order_status_id` > '0'";
|
| 186: | }
|
| 187: |
|
| 188: | if (!empty($data['filter_order_id'])) {
|
| 189: | $sql .= " AND `o`.`order_id` = '" . (int)$data['filter_order_id'] . "'";
|
| 190: | }
|
| 191: |
|
| 192: | if (isset($data['filter_store_id']) && $data['filter_store_id'] !== '') {
|
| 193: | $sql .= " AND `o`.`store_id` = '" . (int)$data['filter_store_id'] . "'";
|
| 194: | }
|
| 195: |
|
| 196: | if (!empty($data['filter_customer_id'])) {
|
| 197: | $sql .= " AND `o`.`customer_id` = '" . (int)$data['filter_customer_id'] . "'";
|
| 198: | }
|
| 199: |
|
| 200: | if (!empty($data['filter_customer'])) {
|
| 201: | $sql .= " AND LCASE(CONCAT(`o`.`firstname`, ' ', `o`.`lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_customer']) . '%') . "'";
|
| 202: | }
|
| 203: |
|
| 204: | if (!empty($data['filter_email'])) {
|
| 205: | $sql .= " AND LCASE(`o`.`email`) LIKE '" . $this->db->escape('%' . (string)$data['filter_email'] . '%') . "'";
|
| 206: | }
|
| 207: |
|
| 208: | if (!empty($data['filter_date_from'])) {
|
| 209: | $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
|
| 210: | }
|
| 211: |
|
| 212: | if (!empty($data['filter_date_to'])) {
|
| 213: | $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
|
| 214: | }
|
| 215: |
|
| 216: | if (!empty($data['filter_total'])) {
|
| 217: | $sql .= " AND `o`.`total` = '" . (float)$data['filter_total'] . "'";
|
| 218: | }
|
| 219: |
|
| 220: | $sort_data = [
|
| 221: | 'o.order_id',
|
| 222: | 'o.store_name',
|
| 223: | 'customer',
|
| 224: | 'order_status',
|
| 225: | 'o.date_added',
|
| 226: | 'o.date_modified',
|
| 227: | 'o.total'
|
| 228: | ];
|
| 229: |
|
| 230: | if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
|
| 231: | $sql .= " ORDER BY " . $data['sort'];
|
| 232: | } else {
|
| 233: | $sql .= " ORDER BY `o`.`order_id`";
|
| 234: | }
|
| 235: |
|
| 236: | if (isset($data['order']) && ($data['order'] == 'DESC')) {
|
| 237: | $sql .= " DESC";
|
| 238: | } else {
|
| 239: | $sql .= " ASC";
|
| 240: | }
|
| 241: |
|
| 242: | if (isset($data['start']) || isset($data['limit'])) {
|
| 243: | if ($data['start'] < 0) {
|
| 244: | $data['start'] = 0;
|
| 245: | }
|
| 246: |
|
| 247: | if ($data['limit'] < 1) {
|
| 248: | $data['limit'] = 20;
|
| 249: | }
|
| 250: |
|
| 251: | $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
|
| 252: | }
|
| 253: |
|
| 254: | $order_data = [];
|
| 255: |
|
| 256: | $query = $this->db->query($sql);
|
| 257: |
|
| 258: | foreach ($query->rows as $key => $result) {
|
| 259: | $order_data[$key] = $result;
|
| 260: |
|
| 261: | $order_data[$key]['custom_field'] = json_decode($result['custom_field'], true);
|
| 262: | $order_data[$key]['payment_custom_field'] = json_decode($result['payment_custom_field'], true);
|
| 263: | $order_data[$key]['payment_method'] = json_decode($result['payment_method'], true);
|
| 264: | $order_data[$key]['shipping_custom_field'] = json_decode($result['shipping_custom_field'], true);
|
| 265: | $order_data[$key]['shipping_method'] = json_decode($result['shipping_method'], true);
|
| 266: | }
|
| 267: |
|
| 268: | return $order_data;
|
| 269: | }
|
| 270: |
|
| 271: | |
| 272: | |
| 273: | |
| 274: | |
| 275: | |
| 276: | |
| 277: |
|
| 278: | public function getOrdersBySubscriptionId(int $subscription_id): array {
|
| 279: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE `subscription_id` = '" . (int)$subscription_id . "'");
|
| 280: |
|
| 281: | return $query->rows;
|
| 282: | }
|
| 283: |
|
| 284: | |
| 285: | |
| 286: | |
| 287: | |
| 288: | |
| 289: | |
| 290: |
|
| 291: | public function getTotalOrdersBySubscriptionId(int $subscription_id): int {
|
| 292: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `subscription_id` = '" . (int)$subscription_id . "'");
|
| 293: |
|
| 294: | return (int)$query->row['total'];
|
| 295: | }
|
| 296: |
|
| 297: | |
| 298: | |
| 299: | |
| 300: | |
| 301: | |
| 302: | |
| 303: |
|
| 304: | public function getProducts(int $order_id): array {
|
| 305: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "' ORDER BY order_product_id ASC");
|
| 306: |
|
| 307: | return $query->rows;
|
| 308: | }
|
| 309: |
|
| 310: | |
| 311: | |
| 312: | |
| 313: | |
| 314: | |
| 315: | |
| 316: |
|
| 317: | public function getTotalProductsByProductId(int $product_id): int {
|
| 318: | $sql = "SELECT SUM(`op`.`quantity`) AS `total` FROM `" . DB_PREFIX . "order_product` `op` LEFT JOIN `" . DB_PREFIX . "order` `o` ON (`op`.`order_id` = `o`.`order_id`) WHERE `op`.`product_id` = '" . (int)$product_id . "' AND `order_status_id` > '0'";
|
| 319: |
|
| 320: | $query = $this->db->query($sql);
|
| 321: |
|
| 322: | return (int)$query->row['total'];
|
| 323: | }
|
| 324: |
|
| 325: | |
| 326: | |
| 327: | |
| 328: | |
| 329: | |
| 330: | |
| 331: | |
| 332: |
|
| 333: | public function getOptions(int $order_id, int $order_product_id): array {
|
| 334: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_option` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
|
| 335: |
|
| 336: | return $query->rows;
|
| 337: | }
|
| 338: |
|
| 339: | |
| 340: | |
| 341: | |
| 342: | |
| 343: | |
| 344: | |
| 345: | |
| 346: |
|
| 347: | public function getSubscription(int $order_id, int $order_product_id): array {
|
| 348: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_subscription` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
|
| 349: |
|
| 350: | return $query->row;
|
| 351: | }
|
| 352: |
|
| 353: | |
| 354: | |
| 355: | |
| 356: | |
| 357: | |
| 358: | |
| 359: |
|
| 360: | public function getVouchers(int $order_id): array {
|
| 361: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE `order_id` = '" . (int)$order_id . "'");
|
| 362: |
|
| 363: | return $query->rows;
|
| 364: | }
|
| 365: |
|
| 366: | |
| 367: | |
| 368: | |
| 369: | |
| 370: | |
| 371: | |
| 372: |
|
| 373: | public function getVoucherByVoucherId(int $voucher_id): array {
|
| 374: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE `voucher_id` = '" . (int)$voucher_id . "'");
|
| 375: |
|
| 376: | return $query->row;
|
| 377: | }
|
| 378: |
|
| 379: | |
| 380: | |
| 381: | |
| 382: | |
| 383: | |
| 384: | |
| 385: |
|
| 386: | public function getTotals(int $order_id): array {
|
| 387: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_total` WHERE `order_id` = '" . (int)$order_id . "' ORDER BY `sort_order`");
|
| 388: |
|
| 389: | return $query->rows;
|
| 390: | }
|
| 391: |
|
| 392: | |
| 393: | |
| 394: | |
| 395: | |
| 396: | |
| 397: | |
| 398: |
|
| 399: | public function getTotalOrders(array $data = []): int {
|
| 400: | $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order`";
|
| 401: |
|
| 402: | if (!empty($data['filter_order_status'])) {
|
| 403: | $implode = [];
|
| 404: |
|
| 405: | $order_statuses = explode(',', $data['filter_order_status']);
|
| 406: | $order_statuses = array_filter($order_statuses);
|
| 407: |
|
| 408: | foreach ($order_statuses as $order_status_id) {
|
| 409: | $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
|
| 410: | }
|
| 411: |
|
| 412: | if ($implode) {
|
| 413: | $sql .= " WHERE (" . implode(" OR ", $implode) . ")";
|
| 414: | }
|
| 415: | } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
|
| 416: | $sql .= " WHERE `order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
|
| 417: | } else {
|
| 418: | $sql .= " WHERE `order_status_id` > '0'";
|
| 419: | }
|
| 420: |
|
| 421: | if (!empty($data['filter_order_id'])) {
|
| 422: | $sql .= " AND `order_id` = '" . (int)$data['filter_order_id'] . "'";
|
| 423: | }
|
| 424: |
|
| 425: | if (isset($data['filter_store_id']) && $data['filter_store_id'] !== '') {
|
| 426: | $sql .= " AND `store_id` = '" . (int)$data['filter_store_id'] . "'";
|
| 427: | }
|
| 428: |
|
| 429: | if (!empty($data['filter_customer_id'])) {
|
| 430: | $sql .= " AND `customer_id` = '" . (int)$data['filter_customer_id'] . "'";
|
| 431: | }
|
| 432: |
|
| 433: | if (!empty($data['filter_customer'])) {
|
| 434: | $sql .= " AND LCASE(CONCAT(`firstname`, ' ', `lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_customer']) . '%') . "'";
|
| 435: | }
|
| 436: |
|
| 437: | if (!empty($data['filter_email'])) {
|
| 438: | $sql .= " AND LCASE(`email`) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_email']) . '%') . "'";
|
| 439: | }
|
| 440: |
|
| 441: | if (!empty($data['filter_date_from'])) {
|
| 442: | $sql .= " AND DATE(`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
|
| 443: | }
|
| 444: |
|
| 445: | if (!empty($data['filter_date_to'])) {
|
| 446: | $sql .= " AND DATE(`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
|
| 447: | }
|
| 448: |
|
| 449: | if (!empty($data['filter_total'])) {
|
| 450: | $sql .= " AND `total` = '" . (float)$data['filter_total'] . "'";
|
| 451: | }
|
| 452: |
|
| 453: | $query = $this->db->query($sql);
|
| 454: |
|
| 455: | return (int)$query->row['total'];
|
| 456: | }
|
| 457: |
|
| 458: | |
| 459: | |
| 460: | |
| 461: | |
| 462: | |
| 463: | |
| 464: |
|
| 465: | public function getTotalOrdersByStoreId(int $store_id): int {
|
| 466: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `store_id` = '" . (int)$store_id . "'");
|
| 467: |
|
| 468: | return (int)$query->row['total'];
|
| 469: | }
|
| 470: |
|
| 471: | |
| 472: | |
| 473: | |
| 474: | |
| 475: | |
| 476: | |
| 477: |
|
| 478: | public function getTotalOrdersByOrderStatusId(int $order_status_id): int {
|
| 479: | $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'");
|
| 480: |
|
| 481: | return (int)$query->row['total'];
|
| 482: | }
|
| 483: |
|
| 484: | |
| 485: | |
| 486: | |
| 487: | |
| 488: |
|
| 489: | public function getTotalOrdersByProcessingStatus(): int {
|
| 490: | $implode = [];
|
| 491: |
|
| 492: | $order_statuses = $this->config->get('config_processing_status');
|
| 493: |
|
| 494: | foreach ($order_statuses as $order_status_id) {
|
| 495: | $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
|
| 496: | }
|
| 497: |
|
| 498: | if ($implode) {
|
| 499: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode));
|
| 500: |
|
| 501: | return (int)$query->row['total'];
|
| 502: | } else {
|
| 503: | return 0;
|
| 504: | }
|
| 505: | }
|
| 506: |
|
| 507: | |
| 508: | |
| 509: | |
| 510: | |
| 511: |
|
| 512: | public function getTotalOrdersByCompleteStatus(): int {
|
| 513: | $implode = [];
|
| 514: |
|
| 515: | $order_statuses = $this->config->get('config_complete_status');
|
| 516: |
|
| 517: | foreach ($order_statuses as $order_status_id) {
|
| 518: | $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
|
| 519: | }
|
| 520: |
|
| 521: | if ($implode) {
|
| 522: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode) . "");
|
| 523: |
|
| 524: | return (int)$query->row['total'];
|
| 525: | } else {
|
| 526: | return 0;
|
| 527: | }
|
| 528: | }
|
| 529: |
|
| 530: | |
| 531: | |
| 532: | |
| 533: | |
| 534: | |
| 535: | |
| 536: |
|
| 537: | public function getTotalOrdersByLanguageId(int $language_id): int {
|
| 538: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `language_id` = '" . (int)$language_id . "' AND `order_status_id` > '0'");
|
| 539: |
|
| 540: | return (int)$query->row['total'];
|
| 541: | }
|
| 542: |
|
| 543: | |
| 544: | |
| 545: | |
| 546: | |
| 547: | |
| 548: | |
| 549: |
|
| 550: | public function getTotalOrdersByCurrencyId(int $currency_id): int {
|
| 551: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `currency_id` = '" . (int)$currency_id . "' AND `order_status_id` > '0'");
|
| 552: |
|
| 553: | return (int)$query->row['total'];
|
| 554: | }
|
| 555: |
|
| 556: | |
| 557: | |
| 558: | |
| 559: | |
| 560: | |
| 561: | |
| 562: |
|
| 563: | public function getTotalSales(array $data = []): float {
|
| 564: | $sql = "SELECT SUM(`total`) AS `total` FROM `" . DB_PREFIX . "order`";
|
| 565: |
|
| 566: | if (!empty($data['filter_order_status'])) {
|
| 567: | $implode = [];
|
| 568: |
|
| 569: | $order_statuses = explode(',', $data['filter_order_status']);
|
| 570: | $order_statuses = array_filter($order_statuses);
|
| 571: |
|
| 572: | foreach ($order_statuses as $order_status_id) {
|
| 573: | $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
|
| 574: | }
|
| 575: |
|
| 576: | if ($implode) {
|
| 577: | $sql .= " WHERE (" . implode(" OR ", $implode) . ")";
|
| 578: | }
|
| 579: | } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
|
| 580: | $sql .= " WHERE `order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
|
| 581: | } else {
|
| 582: | $sql .= " WHERE `order_status_id` > '0'";
|
| 583: | }
|
| 584: |
|
| 585: | if (!empty($data['filter_order_id'])) {
|
| 586: | $sql .= " AND `order_id` = '" . (int)$data['filter_order_id'] . "'";
|
| 587: | }
|
| 588: |
|
| 589: | if (isset($data['filter_store_id']) && $data['filter_store_id'] !== '') {
|
| 590: | $sql .= " AND `store_id` = '" . (int)$data['filter_store_id'] . "'";
|
| 591: | }
|
| 592: |
|
| 593: | if (!empty($data['filter_customer_id'])) {
|
| 594: | $sql .= " AND `customer_id` = '" . (int)$data['filter_customer_id'] . "'";
|
| 595: | }
|
| 596: |
|
| 597: | if (!empty($data['filter_customer'])) {
|
| 598: | $sql .= " AND LCASE(CONCAT(`firstname`, ' ', `lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_customer']) . '%') . "'";
|
| 599: | }
|
| 600: |
|
| 601: | if (!empty($data['filter_email'])) {
|
| 602: | $sql .= " AND LCASE(`email`) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_email']) . '%') . "'";
|
| 603: | }
|
| 604: |
|
| 605: | if (!empty($data['filter_date_added'])) {
|
| 606: | $sql .= " AND DATE(`date_added`) = DATE('" . $this->db->escape((string)$data['filter_date_added']) . "')";
|
| 607: | }
|
| 608: |
|
| 609: | if (!empty($data['filter_date_modified'])) {
|
| 610: | $sql .= " AND DATE(`date_modified`) = DATE('" . $this->db->escape((string)$data['filter_date_modified']) . "')";
|
| 611: | }
|
| 612: |
|
| 613: | if (!empty($data['filter_total'])) {
|
| 614: | $sql .= " AND `total` = '" . (float)$data['filter_total'] . "'";
|
| 615: | }
|
| 616: |
|
| 617: | $query = $this->db->query($sql);
|
| 618: |
|
| 619: | return (int)$query->row['total'];
|
| 620: | }
|
| 621: |
|
| 622: | |
| 623: | |
| 624: | |
| 625: | |
| 626: | |
| 627: | |
| 628: |
|
| 629: | public function createInvoiceNo(int $order_id): string {
|
| 630: | $order_info = $this->getOrder($order_id);
|
| 631: |
|
| 632: | if ($order_info && !$order_info['invoice_no']) {
|
| 633: | $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']) . "'");
|
| 634: |
|
| 635: | if ($query->row['invoice_no']) {
|
| 636: | $invoice_no = $query->row['invoice_no'] + 1;
|
| 637: | } else {
|
| 638: | $invoice_no = 1;
|
| 639: | }
|
| 640: |
|
| 641: | $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 . "'");
|
| 642: |
|
| 643: | return $order_info['invoice_prefix'] . $invoice_no;
|
| 644: | }
|
| 645: |
|
| 646: | return '';
|
| 647: | }
|
| 648: |
|
| 649: | |
| 650: | |
| 651: | |
| 652: | |
| 653: | |
| 654: | |
| 655: |
|
| 656: | public function getRewardTotal(int $order_id): int {
|
| 657: | $query = $this->db->query("SELECT SUM(reward) AS `total` FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "'");
|
| 658: |
|
| 659: | return (int)$query->row['total'];
|
| 660: | }
|
| 661: |
|
| 662: | |
| 663: | |
| 664: | |
| 665: | |
| 666: | |
| 667: | |
| 668: | |
| 669: | |
| 670: |
|
| 671: | public function getHistories(int $order_id, int $start = 0, int $limit = 10): array {
|
| 672: | if ($start < 0) {
|
| 673: | $start = 0;
|
| 674: | }
|
| 675: |
|
| 676: | if ($limit < 1) {
|
| 677: | $limit = 10;
|
| 678: | }
|
| 679: |
|
| 680: | $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);
|
| 681: |
|
| 682: | return $query->rows;
|
| 683: | }
|
| 684: |
|
| 685: | |
| 686: | |
| 687: | |
| 688: | |
| 689: | |
| 690: | |
| 691: |
|
| 692: | public function getTotalHistories(int $order_id): int {
|
| 693: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order_history` WHERE `order_id` = '" . (int)$order_id . "'");
|
| 694: |
|
| 695: | return (int)$query->row['total'];
|
| 696: | }
|
| 697: |
|
| 698: | |
| 699: | |
| 700: | |
| 701: | |
| 702: | |
| 703: | |
| 704: |
|
| 705: | public function getTotalHistoriesByOrderStatusId(int $order_status_id): int {
|
| 706: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order_history` WHERE `order_status_id` = '" . (int)$order_status_id . "'");
|
| 707: |
|
| 708: | return (int)$query->row['total'];
|
| 709: | }
|
| 710: |
|
| 711: | |
| 712: | |
| 713: | |
| 714: | |
| 715: | |
| 716: | |
| 717: | |
| 718: | |
| 719: |
|
| 720: | public function getEmailsByProductsOrdered(array $products, int $start, int $end): array {
|
| 721: | $implode = [];
|
| 722: |
|
| 723: | foreach ($products as $product_id) {
|
| 724: | $implode[] = "`op`.`product_id` = '" . (int)$product_id . "'";
|
| 725: | }
|
| 726: |
|
| 727: | $query = $this->db->query("SELECT DISTINCT `o`.`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);
|
| 728: |
|
| 729: | return $query->rows;
|
| 730: | }
|
| 731: |
|
| 732: | |
| 733: | |
| 734: | |
| 735: | |
| 736: | |
| 737: | |
| 738: |
|
| 739: | public function getTotalEmailsByProductsOrdered(array $products): int {
|
| 740: | $implode = [];
|
| 741: |
|
| 742: | foreach ($products as $product_id) {
|
| 743: | $implode[] = "`op`.`product_id` = '" . (int)$product_id . "'";
|
| 744: | }
|
| 745: |
|
| 746: | $query = $this->db->query("SELECT COUNT(DISTINCT `o`.`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'");
|
| 747: |
|
| 748: | return (int)$query->row['total'];
|
| 749: | }
|
| 750: | }
|
| 751: | |