docs/api/source-admin.model.sale.order.html
| 1: | <?php |
| 2: | namespace Opencart\Admin\Model\Sale; |
| 3: | /** |
| 4: | * Class Order |
| 5: | * |
| 6: | * @package Opencart\Admin\Model\Sale |
| 7: | */ |
| 8: | class Order extends \Opencart\System\Engine\Model { |
| 9: | /** |
| 10: | * Get Order |
| 11: | * |
| 12: | * @param int $order_id |
| 13: | * |
| 14: | * @return array<string, mixed> |
| 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: | * Get Orders |
| 161: | * |
| 162: | * @param array<string, mixed> $data |
| 163: | * |
| 164: | * @return array<int, array<string, mixed>> |
| 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: | * Get Orders By Subscription ID |
| 273: | * |
| 274: | * @param int $subscription_id |
| 275: | * |
| 276: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Orders By Subscription ID |
| 286: | * |
| 287: | * @param int $subscription_id |
| 288: | * |
| 289: | * @return int |
| 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: | * Get Products |
| 299: | * |
| 300: | * @param int $order_id |
| 301: | * |
| 302: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Products By Product ID |
| 312: | * |
| 313: | * @param int $product_id |
| 314: | * |
| 315: | * @return int |
| 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: | * Get Options |
| 327: | * |
| 328: | * @param int $order_id |
| 329: | * @param int $order_product_id |
| 330: | * |
| 331: | * @return array<int, array<string, mixed>> |
| 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: | * Get Subscription |
| 341: | * |
| 342: | * @param int $order_id |
| 343: | * @param int $order_product_id |
| 344: | * |
| 345: | * @return array<string, mixed> |
| 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: | * Get Vouchers |
| 355: | * |
| 356: | * @param int $order_id |
| 357: | * |
| 358: | * @return array<int, array<string, mixed>> |
| 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: | * Get Voucher By Voucher ID |
| 368: | * |
| 369: | * @param int $voucher_id |
| 370: | * |
| 371: | * @return array<string, mixed> |
| 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: | * Get Totals |
| 381: | * |
| 382: | * @param int $order_id |
| 383: | * |
| 384: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Orders |
| 394: | * |
| 395: | * @param array<string, mixed> $data |
| 396: | * |
| 397: | * @return int |
| 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: | * Get Total Orders By Store ID |
| 460: | * |
| 461: | * @param int $store_id |
| 462: | * |
| 463: | * @return int |
| 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: | * Get Total Orders By Order Status ID |
| 473: | * |
| 474: | * @param int $order_status_id |
| 475: | * |
| 476: | * @return int |
| 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: | * Get Total Orders By Processing Status |
| 486: | * |
| 487: | * @return int |
| 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: | * Get Total Orders By Complete Status |
| 509: | * |
| 510: | * @return int |
| 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: | * Get Total Orders By Language ID |
| 532: | * |
| 533: | * @param int $language_id |
| 534: | * |
| 535: | * @return int |
| 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: | * Get Total Orders By Currency ID |
| 545: | * |
| 546: | * @param int $currency_id |
| 547: | * |
| 548: | * @return int |
| 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: | * Get Total Sales |
| 558: | * |
| 559: | * @param array<string, mixed> $data |
| 560: | * |
| 561: | * @return float |
| 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: | * Create Invoice No |
| 624: | * |
| 625: | * @param int $order_id |
| 626: | * |
| 627: | * @return string |
| 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: | * Get Reward Total |
| 651: | * |
| 652: | * @param int $order_id |
| 653: | * |
| 654: | * @return int |
| 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: | * Get Histories |
| 664: | * |
| 665: | * @param int $order_id |
| 666: | * @param int $start |
| 667: | * @param int $limit |
| 668: | * |
| 669: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Histories |
| 687: | * |
| 688: | * @param int $order_id |
| 689: | * |
| 690: | * @return int |
| 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: | * Get Total Histories By Order Status ID |
| 700: | * |
| 701: | * @param int $order_status_id |
| 702: | * |
| 703: | * @return int |
| 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: | * Get Emails By Products Ordered |
| 713: | * |
| 714: | * @param array<int> $products |
| 715: | * @param int $start |
| 716: | * @param int $end |
| 717: | * |
| 718: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Emails By Products Ordered |
| 734: | * |
| 735: | * @param array<int> $products |
| 736: | * |
| 737: | * @return int |
| 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: | |
OpenCart API API documentation generated by ApiGen dev-master