| 1: | <?php
|
| 2: | namespace Opencart\Admin\Model\Customer;
|
| 3: | |
| 4: | |
| 5: | |
| 6: | |
| 7: |
|
| 8: | class Customer extends \Opencart\System\Engine\Model {
|
| 9: | |
| 10: | |
| 11: | |
| 12: | |
| 13: | |
| 14: | |
| 15: |
|
| 16: | public function addCustomer(array $data): int {
|
| 17: | $this->db->query("INSERT INTO `" . DB_PREFIX . "customer` SET `store_id` = '" . (int)$data['store_id'] . "', `customer_group_id` = '" . (int)$data['customer_group_id'] . "', `firstname` = '" . $this->db->escape((string)$data['firstname']) . "', `lastname` = '" . $this->db->escape((string)$data['lastname']) . "', `email` = '" . $this->db->escape((string)$data['email']) . "', `telephone` = '" . $this->db->escape((string)$data['telephone']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `newsletter` = '" . (isset($data['newsletter']) ? (bool)$data['newsletter'] : 0) . "', `password` = '" . $this->db->escape(password_hash(html_entity_decode($data['password'], ENT_QUOTES, 'UTF-8'), PASSWORD_DEFAULT)) . "', `status` = '" . (isset($data['status']) ? (bool)$data['status'] : 0) . "', `safe` = '" . (isset($data['safe']) ? (bool)$data['safe'] : 0) . "', `commenter` = '" . (isset($data['commenter']) ? (bool)$data['commenter'] : 0) . "', `date_added` = NOW()");
|
| 18: |
|
| 19: | return $this->db->getLastId();
|
| 20: | }
|
| 21: |
|
| 22: | |
| 23: | |
| 24: | |
| 25: | |
| 26: | |
| 27: | |
| 28: | |
| 29: |
|
| 30: | public function editCustomer(int $customer_id, array $data): void {
|
| 31: | $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `store_id` = '" . (int)$data['store_id'] . "', `customer_group_id` = '" . (int)$data['customer_group_id'] . "', `firstname` = '" . $this->db->escape((string)$data['firstname']) . "', `lastname` = '" . $this->db->escape((string)$data['lastname']) . "', `email` = '" . $this->db->escape((string)$data['email']) . "', `telephone` = '" . $this->db->escape((string)$data['telephone']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `newsletter` = '" . (isset($data['newsletter']) ? (bool)$data['newsletter'] : 0) . "', `status` = '" . (isset($data['status']) ? (bool)$data['status'] : 0) . "', `safe` = '" . (isset($data['safe']) ? (bool)$data['safe'] : 0) . "', `commenter` = '" . (isset($data['commenter']) ? (bool)$data['commenter'] : 0) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 32: |
|
| 33: | if ($data['password']) {
|
| 34: | $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `password` = '" . $this->db->escape(password_hash(html_entity_decode($data['password'], ENT_QUOTES, 'UTF-8'), PASSWORD_DEFAULT)) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 35: | }
|
| 36: | }
|
| 37: |
|
| 38: | |
| 39: | |
| 40: | |
| 41: | |
| 42: | |
| 43: | |
| 44: | |
| 45: |
|
| 46: | public function editToken(int $customer_id, string $token): void {
|
| 47: | $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `token` = '" . $this->db->escape($token) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 48: | }
|
| 49: |
|
| 50: | |
| 51: | |
| 52: | |
| 53: | |
| 54: | |
| 55: | |
| 56: | |
| 57: |
|
| 58: | public function editCommenter(int $customer_id, bool $status): void {
|
| 59: | $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `commenter` = '" . (bool)$status . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 60: | }
|
| 61: |
|
| 62: | |
| 63: | |
| 64: | |
| 65: | |
| 66: | |
| 67: | |
| 68: |
|
| 69: | public function deleteCustomer(int $customer_id): void {
|
| 70: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 71: |
|
| 72: | $this->deleteActivities($customer_id);
|
| 73: |
|
| 74: | $this->deleteAddresses($customer_id);
|
| 75: | $this->deleteAuthorizes($customer_id);
|
| 76: | $this->deleteHistories($customer_id);
|
| 77: | $this->deleteRewards($customer_id);
|
| 78: | $this->deleteTransactions($customer_id);
|
| 79: | $this->deleteWishlists($customer_id);
|
| 80: | $this->deleteIps($customer_id);
|
| 81: |
|
| 82: | $this->load->model('marketing/affiliate');
|
| 83: |
|
| 84: | $this->model_marketing_affiliate->deleteAffiliate($customer_id);
|
| 85: |
|
| 86: | $this->load->model('customer/customer_approval');
|
| 87: |
|
| 88: | $this->model_customer_customer_approval->deleteApprovalsByCustomerId($customer_id);
|
| 89: | }
|
| 90: |
|
| 91: | |
| 92: | |
| 93: | |
| 94: | |
| 95: | |
| 96: | |
| 97: |
|
| 98: | public function getCustomer(int $customer_id): array {
|
| 99: | $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 100: |
|
| 101: | if ($query->num_rows) {
|
| 102: | return $query->row + ['custom_field' => json_decode($query->row['custom_field'], true)];
|
| 103: | } else {
|
| 104: | return [];
|
| 105: | }
|
| 106: | }
|
| 107: |
|
| 108: | |
| 109: | |
| 110: | |
| 111: | |
| 112: | |
| 113: | |
| 114: |
|
| 115: | public function getCustomerByEmail(string $email): array {
|
| 116: | $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "customer` WHERE LCASE(`email`) = '" . $this->db->escape(oc_strtolower($email)) . "'");
|
| 117: |
|
| 118: | if ($query->num_rows) {
|
| 119: | return $query->row + ['custom_field' => json_decode($query->row['custom_field'], true)];
|
| 120: | } else {
|
| 121: | return [];
|
| 122: | }
|
| 123: | }
|
| 124: |
|
| 125: | |
| 126: | |
| 127: | |
| 128: | |
| 129: | |
| 130: | |
| 131: |
|
| 132: | public function getCustomers(array $data = []): array {
|
| 133: | $sql = "SELECT *, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `name`, `cgd`.`name` AS `customer_group` FROM `" . DB_PREFIX . "customer` `c` LEFT JOIN `" . DB_PREFIX . "customer_group_description` `cgd` ON (`c`.`customer_group_id` = `cgd`.`customer_group_id`) WHERE `cgd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
|
| 134: |
|
| 135: | if (!empty($data['filter_name'])) {
|
| 136: | $sql .= " AND LCASE(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_name']) . '%') . "'";
|
| 137: | }
|
| 138: |
|
| 139: | if (!empty($data['filter_email'])) {
|
| 140: | $sql .= " AND LCASE(`c`.`email`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_email']) . '%') . "'";
|
| 141: | }
|
| 142: |
|
| 143: | if (isset($data['filter_newsletter']) && $data['filter_newsletter'] !== '') {
|
| 144: | $sql .= " AND `c`.`newsletter` = '" . (int)$data['filter_newsletter'] . "'";
|
| 145: | }
|
| 146: |
|
| 147: | if (!empty($data['filter_customer_group_id'])) {
|
| 148: | $sql .= " AND `c`.`customer_group_id` = '" . (int)$data['filter_customer_group_id'] . "'";
|
| 149: | }
|
| 150: |
|
| 151: | if (!empty($data['filter_ip'])) {
|
| 152: | $sql .= " AND `c`.`customer_id` IN (SELECT `customer_id` FROM `" . DB_PREFIX . "customer_ip` WHERE `ip` = '" . $this->db->escape((string)$data['filter_ip']) . "')";
|
| 153: | }
|
| 154: |
|
| 155: | if (isset($data['filter_status']) && $data['filter_status'] !== '') {
|
| 156: | $sql .= " AND `c`.`status` = '" . (int)$data['filter_status'] . "'";
|
| 157: | }
|
| 158: |
|
| 159: | if (!empty($data['filter_date_from'])) {
|
| 160: | $sql .= " AND DATE(`c`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
|
| 161: | }
|
| 162: |
|
| 163: | if (!empty($data['filter_date_to'])) {
|
| 164: | $sql .= " AND DATE(`c`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
|
| 165: | }
|
| 166: |
|
| 167: | $sort_data = [
|
| 168: | 'name',
|
| 169: | 'c.email',
|
| 170: | 'customer_group',
|
| 171: | 'c.status',
|
| 172: | 'c.ip',
|
| 173: | 'c.date_added'
|
| 174: | ];
|
| 175: |
|
| 176: | if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
|
| 177: | $sql .= " ORDER BY " . $data['sort'];
|
| 178: | } else {
|
| 179: | $sql .= " ORDER BY `name`";
|
| 180: | }
|
| 181: |
|
| 182: | if (isset($data['order']) && ($data['order'] == 'DESC')) {
|
| 183: | $sql .= " DESC";
|
| 184: | } else {
|
| 185: | $sql .= " ASC";
|
| 186: | }
|
| 187: |
|
| 188: | if (isset($data['start']) || isset($data['limit'])) {
|
| 189: | if ($data['start'] < 0) {
|
| 190: | $data['start'] = 0;
|
| 191: | }
|
| 192: |
|
| 193: | if ($data['limit'] < 1) {
|
| 194: | $data['limit'] = 20;
|
| 195: | }
|
| 196: |
|
| 197: | $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
|
| 198: | }
|
| 199: |
|
| 200: | $customer_data = [];
|
| 201: |
|
| 202: | $query = $this->db->query($sql);
|
| 203: |
|
| 204: | foreach ($query->rows as $result) {
|
| 205: | $customer_data[] = $result + ['custom_field' => json_decode($result['custom_field'], true)];
|
| 206: | }
|
| 207: |
|
| 208: | return $customer_data;
|
| 209: | }
|
| 210: |
|
| 211: | |
| 212: | |
| 213: | |
| 214: | |
| 215: | |
| 216: | |
| 217: |
|
| 218: | public function getTotalCustomers(array $data = []): int {
|
| 219: | $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer` `c`";
|
| 220: |
|
| 221: | $implode = [];
|
| 222: |
|
| 223: | if (!empty($data['filter_name'])) {
|
| 224: | $implode[] = "LCASE(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_name']) . '%') . "'";
|
| 225: | }
|
| 226: |
|
| 227: | if (!empty($data['filter_email'])) {
|
| 228: | $implode[] = "LCASE(`c`.`email`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_email']) . '%') . "'";
|
| 229: | }
|
| 230: |
|
| 231: | if (isset($data['filter_newsletter']) && $data['filter_newsletter'] !== '') {
|
| 232: | $implode[] = "`c`.`newsletter` = '" . (int)$data['filter_newsletter'] . "'";
|
| 233: | }
|
| 234: |
|
| 235: | if (!empty($data['filter_customer_group_id'])) {
|
| 236: | $implode[] = "`c`.`customer_group_id` = '" . (int)$data['filter_customer_group_id'] . "'";
|
| 237: | }
|
| 238: |
|
| 239: | if (!empty($data['filter_ip'])) {
|
| 240: | $implode[] = "`c`.`customer_id` IN (SELECT `customer_id` FROM `" . DB_PREFIX . "customer_ip` WHERE `ip` = '" . $this->db->escape((string)$data['filter_ip']) . "')";
|
| 241: | }
|
| 242: |
|
| 243: | if (isset($data['filter_status']) && $data['filter_status'] !== '') {
|
| 244: | $implode[] = "`c`.`status` = '" . (int)$data['filter_status'] . "'";
|
| 245: | }
|
| 246: |
|
| 247: | if (!empty($data['filter_date_from'])) {
|
| 248: | $implode[] = "DATE(`c`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
|
| 249: | }
|
| 250: |
|
| 251: | if (!empty($data['filter_date_to'])) {
|
| 252: | $implode[] = "DATE(`c`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
|
| 253: | }
|
| 254: |
|
| 255: | if ($implode) {
|
| 256: | $sql .= " WHERE " . implode(" AND ", $implode);
|
| 257: | }
|
| 258: |
|
| 259: | $query = $this->db->query($sql);
|
| 260: |
|
| 261: | return (int)$query->row['total'];
|
| 262: | }
|
| 263: |
|
| 264: | |
| 265: | |
| 266: | |
| 267: | |
| 268: | |
| 269: | |
| 270: |
|
| 271: | public function getTotalCustomersByCustomerGroupId(int $customer_group_id): int {
|
| 272: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer` WHERE `customer_group_id` = '" . (int)$customer_group_id . "'");
|
| 273: |
|
| 274: | if ($query->num_rows) {
|
| 275: | return (int)$query->row['total'];
|
| 276: | } else {
|
| 277: | return 0;
|
| 278: | }
|
| 279: | }
|
| 280: |
|
| 281: | |
| 282: | |
| 283: | |
| 284: | |
| 285: | |
| 286: | |
| 287: |
|
| 288: | public function deleteActivities(int $customer_id): void {
|
| 289: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_activity` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 290: | }
|
| 291: |
|
| 292: | |
| 293: | |
| 294: | |
| 295: | |
| 296: | |
| 297: | |
| 298: | |
| 299: |
|
| 300: | public function addAddress(int $customer_id, array $data): int {
|
| 301: | $this->db->query("INSERT INTO `" . DB_PREFIX . "address` SET `customer_id` = '" . (int)$customer_id . "', `firstname` = '" . $this->db->escape($data['firstname']) . "', `lastname` = '" . $this->db->escape($data['lastname']) . "', `company` = '" . $this->db->escape($data['company']) . "', `address_1` = '" . $this->db->escape($data['address_1']) . "', `address_2` = '" . $this->db->escape($data['address_2']) . "', `city` = '" . $this->db->escape($data['city']) . "', `postcode` = '" . $this->db->escape($data['postcode']) . "', `country_id` = '" . (int)$data['country_id'] . "', `zone_id` = '" . (int)$data['zone_id'] . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `default` = '" . (!empty($data['default']) ? (bool)$data['default'] : 0) . "'");
|
| 302: |
|
| 303: | $address_id = $this->db->getLastId();
|
| 304: |
|
| 305: | if (!empty($data['default'])) {
|
| 306: | $this->db->query("UPDATE `" . DB_PREFIX . "address` SET `default` = '0' WHERE `customer_id` = '" . (int)$customer_id . "' AND `address_id` != '" . (int)$address_id . "'");
|
| 307: | }
|
| 308: |
|
| 309: | return $address_id;
|
| 310: | }
|
| 311: |
|
| 312: | |
| 313: | |
| 314: | |
| 315: | |
| 316: | |
| 317: | |
| 318: | |
| 319: | |
| 320: |
|
| 321: | public function editAddress(int $customer_id, int $address_id, array $data): void {
|
| 322: | $this->db->query("UPDATE `" . DB_PREFIX . "address` SET `firstname` = '" . $this->db->escape($data['firstname']) . "', `lastname` = '" . $this->db->escape($data['lastname']) . "', `company` = '" . $this->db->escape($data['company']) . "', `address_1` = '" . $this->db->escape($data['address_1']) . "', `address_2` = '" . $this->db->escape($data['address_2']) . "', `city` = '" . $this->db->escape($data['city']) . "', `postcode` = '" . $this->db->escape($data['postcode']) . "', `country_id` = '" . (int)$data['country_id'] . "', `zone_id` = '" . (int)$data['zone_id'] . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `default` = '" . (!empty($data['default']) ? (bool)$data['default'] : 0) . "' WHERE `address_id` = '" . (int)$address_id . "'");
|
| 323: |
|
| 324: | if (!empty($data['default'])) {
|
| 325: | $this->db->query("UPDATE `" . DB_PREFIX . "address` SET `default` = '0' WHERE `customer_id` = '" . (int)$customer_id . "' AND `address_id` != '" . (int)$address_id . "'");
|
| 326: | }
|
| 327: | }
|
| 328: |
|
| 329: | |
| 330: | |
| 331: | |
| 332: | |
| 333: | |
| 334: | |
| 335: | |
| 336: |
|
| 337: | public function deleteAddresses(int $customer_id, int $address_id = 0): void {
|
| 338: | $sql = "DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` = '" . (int)$customer_id . "'";
|
| 339: |
|
| 340: | if ($address_id) {
|
| 341: | $sql .= " AND `address_id` = '" . (int)$address_id . "'";
|
| 342: | }
|
| 343: |
|
| 344: | $this->db->query($sql);
|
| 345: | }
|
| 346: |
|
| 347: | |
| 348: | |
| 349: | |
| 350: | |
| 351: | |
| 352: | |
| 353: |
|
| 354: | public function getAddress(int $address_id): array {
|
| 355: | $address_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "address` WHERE `address_id` = '" . (int)$address_id . "'");
|
| 356: |
|
| 357: | if ($address_query->num_rows) {
|
| 358: | $country_query = $this->db->query("SELECT *, `c`.`name` FROM `" . DB_PREFIX . "country` `c` LEFT JOIN `" . DB_PREFIX . "address_format` `af` ON (`c`.`address_format_id` = `af`.`address_format_id`) WHERE `country_id` = '" . (int)$address_query->row['country_id'] . "'");
|
| 359: |
|
| 360: | if ($country_query->num_rows) {
|
| 361: | $country = $country_query->row['name'];
|
| 362: | $iso_code_2 = $country_query->row['iso_code_2'];
|
| 363: | $iso_code_3 = $country_query->row['iso_code_3'];
|
| 364: | $address_format = $country_query->row['address_format'];
|
| 365: | } else {
|
| 366: | $country = '';
|
| 367: | $iso_code_2 = '';
|
| 368: | $iso_code_3 = '';
|
| 369: | $address_format = '';
|
| 370: | }
|
| 371: |
|
| 372: | $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE `zone_id` = '" . (int)$address_query->row['zone_id'] . "'");
|
| 373: |
|
| 374: | if ($zone_query->num_rows) {
|
| 375: | $zone = $zone_query->row['name'];
|
| 376: | $zone_code = $zone_query->row['code'];
|
| 377: | } else {
|
| 378: | $zone = '';
|
| 379: | $zone_code = '';
|
| 380: | }
|
| 381: |
|
| 382: | return [
|
| 383: | 'address_id' => $address_query->row['address_id'],
|
| 384: | 'customer_id' => $address_query->row['customer_id'],
|
| 385: | 'firstname' => $address_query->row['firstname'],
|
| 386: | 'lastname' => $address_query->row['lastname'],
|
| 387: | 'company' => $address_query->row['company'],
|
| 388: | 'address_1' => $address_query->row['address_1'],
|
| 389: | 'address_2' => $address_query->row['address_2'],
|
| 390: | 'postcode' => $address_query->row['postcode'],
|
| 391: | 'city' => $address_query->row['city'],
|
| 392: | 'zone_id' => $address_query->row['zone_id'],
|
| 393: | 'zone' => $zone,
|
| 394: | 'zone_code' => $zone_code,
|
| 395: | 'country_id' => $address_query->row['country_id'],
|
| 396: | 'country' => $country,
|
| 397: | 'iso_code_2' => $iso_code_2,
|
| 398: | 'iso_code_3' => $iso_code_3,
|
| 399: | 'address_format' => $address_format,
|
| 400: | 'custom_field' => json_decode($address_query->row['custom_field'], true),
|
| 401: | 'default' => $address_query->row['default']
|
| 402: | ];
|
| 403: | }
|
| 404: |
|
| 405: | return [];
|
| 406: | }
|
| 407: |
|
| 408: | |
| 409: | |
| 410: | |
| 411: | |
| 412: | |
| 413: | |
| 414: |
|
| 415: | public function getAddresses(int $customer_id): array {
|
| 416: | $address_data = [];
|
| 417: |
|
| 418: | $query = $this->db->query("SELECT `address_id` FROM `" . DB_PREFIX . "address` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 419: |
|
| 420: | foreach ($query->rows as $result) {
|
| 421: | $address_info = $this->getAddress($result['address_id']);
|
| 422: |
|
| 423: | if ($address_info) {
|
| 424: | $address_data[] = $address_info;
|
| 425: | }
|
| 426: | }
|
| 427: |
|
| 428: | return $address_data;
|
| 429: | }
|
| 430: |
|
| 431: | |
| 432: | |
| 433: | |
| 434: | |
| 435: | |
| 436: | |
| 437: |
|
| 438: | public function getTotalAddresses(int $customer_id): int {
|
| 439: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "address` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 440: |
|
| 441: | return (int)$query->row['total'];
|
| 442: | }
|
| 443: |
|
| 444: | |
| 445: | |
| 446: | |
| 447: | |
| 448: | |
| 449: | |
| 450: |
|
| 451: | public function getTotalAddressesByCountryId(int $country_id): int {
|
| 452: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "address` WHERE `country_id` = '" . (int)$country_id . "'");
|
| 453: |
|
| 454: | return (int)$query->row['total'];
|
| 455: | }
|
| 456: |
|
| 457: | |
| 458: | |
| 459: | |
| 460: | |
| 461: | |
| 462: | |
| 463: |
|
| 464: | public function getTotalAddressesByZoneId(int $zone_id): int {
|
| 465: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "address` WHERE `zone_id` = '" . (int)$zone_id . "'");
|
| 466: |
|
| 467: | return (int)$query->row['total'];
|
| 468: | }
|
| 469: |
|
| 470: | |
| 471: | |
| 472: | |
| 473: | |
| 474: | |
| 475: | |
| 476: | |
| 477: |
|
| 478: | public function addHistory(int $customer_id, string $comment): void {
|
| 479: | $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_history` SET `customer_id` = '" . (int)$customer_id . "', `comment` = '" . $this->db->escape(strip_tags($comment)) . "', `date_added` = NOW()");
|
| 480: | }
|
| 481: |
|
| 482: | |
| 483: | |
| 484: | |
| 485: | |
| 486: | |
| 487: | |
| 488: |
|
| 489: | public function deleteHistories(int $customer_id): void {
|
| 490: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 491: | }
|
| 492: |
|
| 493: | |
| 494: | |
| 495: | |
| 496: | |
| 497: | |
| 498: | |
| 499: | |
| 500: | |
| 501: |
|
| 502: | public function getHistories(int $customer_id, int $start = 0, int $limit = 10): array {
|
| 503: | if ($start < 0) {
|
| 504: | $start = 0;
|
| 505: | }
|
| 506: |
|
| 507: | if ($limit < 1) {
|
| 508: | $limit = 10;
|
| 509: | }
|
| 510: |
|
| 511: | $query = $this->db->query("SELECT `comment`, `date_added` FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
|
| 512: |
|
| 513: | return $query->rows;
|
| 514: | }
|
| 515: |
|
| 516: | |
| 517: | |
| 518: | |
| 519: | |
| 520: | |
| 521: | |
| 522: |
|
| 523: | public function getTotalHistories(int $customer_id): int {
|
| 524: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 525: |
|
| 526: | return (int)$query->row['total'];
|
| 527: | }
|
| 528: |
|
| 529: | |
| 530: | |
| 531: | |
| 532: | |
| 533: | |
| 534: | |
| 535: |
|
| 536: | public function deleteWishlists(int $customer_id): void {
|
| 537: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_wishlist` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 538: | }
|
| 539: |
|
| 540: | |
| 541: | |
| 542: | |
| 543: | |
| 544: | |
| 545: | |
| 546: | |
| 547: | |
| 548: | |
| 549: |
|
| 550: | public function addTransaction(int $customer_id, string $description = '', float $amount = 0, int $order_id = 0): void {
|
| 551: | $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_transaction` SET `customer_id` = '" . (int)$customer_id . "', `order_id` = '" . (int)$order_id . "', `description` = '" . $this->db->escape($description) . "', `amount` = '" . (float)$amount . "', `date_added` = NOW()");
|
| 552: | }
|
| 553: |
|
| 554: | |
| 555: | |
| 556: | |
| 557: | |
| 558: | |
| 559: | |
| 560: |
|
| 561: | public function deleteTransactions(int $customer_id): void {
|
| 562: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 563: | }
|
| 564: |
|
| 565: | |
| 566: | |
| 567: | |
| 568: | |
| 569: | |
| 570: | |
| 571: |
|
| 572: | public function deleteTransactionsByOrderId(int $order_id): void {
|
| 573: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `order_id` = '" . (int)$order_id . "'");
|
| 574: | }
|
| 575: |
|
| 576: | |
| 577: | |
| 578: | |
| 579: | |
| 580: | |
| 581: | |
| 582: | |
| 583: | |
| 584: |
|
| 585: | public function getTransactions(int $customer_id, int $start = 0, int $limit = 10): array {
|
| 586: | if ($start < 0) {
|
| 587: | $start = 0;
|
| 588: | }
|
| 589: |
|
| 590: | if ($limit < 1) {
|
| 591: | $limit = 10;
|
| 592: | }
|
| 593: |
|
| 594: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
|
| 595: |
|
| 596: | return $query->rows;
|
| 597: | }
|
| 598: |
|
| 599: | |
| 600: | |
| 601: | |
| 602: | |
| 603: | |
| 604: | |
| 605: |
|
| 606: | public function getTotalTransactions(int $customer_id): int {
|
| 607: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 608: |
|
| 609: | return (int)$query->row['total'];
|
| 610: | }
|
| 611: |
|
| 612: | |
| 613: | |
| 614: | |
| 615: | |
| 616: | |
| 617: | |
| 618: |
|
| 619: | public function getTransactionTotal(int $customer_id): float {
|
| 620: | $query = $this->db->query("SELECT SUM(`amount`) AS `total` FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 621: |
|
| 622: | return (float)$query->row['total'];
|
| 623: | }
|
| 624: |
|
| 625: | |
| 626: | |
| 627: | |
| 628: | |
| 629: | |
| 630: | |
| 631: |
|
| 632: | public function getTotalTransactionsByOrderId(int $order_id): int {
|
| 633: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_transaction` WHERE `order_id` = '" . (int)$order_id . "'");
|
| 634: |
|
| 635: | return (int)$query->row['total'];
|
| 636: | }
|
| 637: |
|
| 638: | |
| 639: | |
| 640: | |
| 641: | |
| 642: | |
| 643: | |
| 644: | |
| 645: | |
| 646: | |
| 647: |
|
| 648: | public function addReward(int $customer_id, string $description = '', int $points = 0, int $order_id = 0): void {
|
| 649: | $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_reward` SET `customer_id` = '" . (int)$customer_id . "', `order_id` = '" . (int)$order_id . "', `points` = '" . (int)$points . "', `description` = '" . $this->db->escape($description) . "', `date_added` = NOW()");
|
| 650: | }
|
| 651: |
|
| 652: | |
| 653: | |
| 654: | |
| 655: | |
| 656: | |
| 657: | |
| 658: |
|
| 659: | public function deleteRewards(int $customer_id): void {
|
| 660: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 661: | }
|
| 662: |
|
| 663: | |
| 664: | |
| 665: | |
| 666: | |
| 667: | |
| 668: | |
| 669: |
|
| 670: | public function deleteRewardsByOrderId(int $order_id): void {
|
| 671: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `order_id` = '" . (int)$order_id . "' AND `points` > '0'");
|
| 672: | }
|
| 673: |
|
| 674: | |
| 675: | |
| 676: | |
| 677: | |
| 678: | |
| 679: | |
| 680: | |
| 681: | |
| 682: |
|
| 683: | public function getRewards(int $customer_id, int $start = 0, int $limit = 10): array {
|
| 684: | if ($start < 0) {
|
| 685: | $start = 0;
|
| 686: | }
|
| 687: |
|
| 688: | if ($limit < 1) {
|
| 689: | $limit = 10;
|
| 690: | }
|
| 691: |
|
| 692: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
|
| 693: |
|
| 694: | return $query->rows;
|
| 695: | }
|
| 696: |
|
| 697: | |
| 698: | |
| 699: | |
| 700: | |
| 701: | |
| 702: | |
| 703: |
|
| 704: | public function getTotalRewards(int $customer_id): int {
|
| 705: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 706: |
|
| 707: | return (int)$query->row['total'];
|
| 708: | }
|
| 709: |
|
| 710: | |
| 711: | |
| 712: | |
| 713: | |
| 714: | |
| 715: | |
| 716: |
|
| 717: | public function getRewardTotal(int $customer_id): int {
|
| 718: | $query = $this->db->query("SELECT SUM(points) AS `total` FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 719: |
|
| 720: | return (int)$query->row['total'];
|
| 721: | }
|
| 722: |
|
| 723: | |
| 724: | |
| 725: | |
| 726: | |
| 727: | |
| 728: | |
| 729: |
|
| 730: | public function getTotalRewardsByOrderId(int $order_id): int {
|
| 731: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_reward` WHERE `order_id` = '" . (int)$order_id . "' AND `points` > '0'");
|
| 732: |
|
| 733: | return (int)$query->row['total'];
|
| 734: | }
|
| 735: |
|
| 736: | |
| 737: | |
| 738: | |
| 739: | |
| 740: | |
| 741: | |
| 742: |
|
| 743: | public function deleteIps(int $customer_id): void {
|
| 744: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 745: | }
|
| 746: |
|
| 747: | |
| 748: | |
| 749: | |
| 750: | |
| 751: | |
| 752: | |
| 753: | |
| 754: | |
| 755: |
|
| 756: | public function getIps(int $customer_id, int $start = 0, int $limit = 10): array {
|
| 757: | if ($start < 0) {
|
| 758: | $start = 0;
|
| 759: | }
|
| 760: | if ($limit < 1) {
|
| 761: | $limit = 10;
|
| 762: | }
|
| 763: |
|
| 764: | $query = $this->db->query("SELECT `ip`, `store_id`, `country`, `date_added` FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
|
| 765: |
|
| 766: | return $query->rows;
|
| 767: | }
|
| 768: |
|
| 769: | |
| 770: | |
| 771: | |
| 772: | |
| 773: | |
| 774: | |
| 775: |
|
| 776: | public function getTotalIps(int $customer_id): int {
|
| 777: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 778: |
|
| 779: | return (int)$query->row['total'];
|
| 780: | }
|
| 781: |
|
| 782: | |
| 783: | |
| 784: | |
| 785: | |
| 786: | |
| 787: | |
| 788: |
|
| 789: | public function getTotalCustomersByIp(string $ip): int {
|
| 790: | $query = $this->db->query("SELECT COUNT(DISTINCT `customer_id`) AS `total` FROM `" . DB_PREFIX . "customer_ip` WHERE `ip` = '" . $this->db->escape($ip) . "'");
|
| 791: |
|
| 792: | return (int)$query->row['total'];
|
| 793: | }
|
| 794: |
|
| 795: | |
| 796: | |
| 797: | |
| 798: | |
| 799: | |
| 800: | |
| 801: |
|
| 802: | public function getTotalLoginAttempts(string $email): array {
|
| 803: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_login` WHERE `email` = '" . $this->db->escape(oc_strtolower($email)) . "'");
|
| 804: |
|
| 805: | return $query->row;
|
| 806: | }
|
| 807: |
|
| 808: | |
| 809: | |
| 810: | |
| 811: | |
| 812: | |
| 813: | |
| 814: |
|
| 815: | public function deleteLoginAttempts(string $email): void {
|
| 816: | $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_login` WHERE `email` = '" . $this->db->escape(oc_strtolower($email)) . "'");
|
| 817: | }
|
| 818: |
|
| 819: | |
| 820: | |
| 821: | |
| 822: | |
| 823: | |
| 824: | |
| 825: | |
| 826: |
|
| 827: | public function addAuthorize(int $customer_id, array $data): void {
|
| 828: | $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_authorize` SET `customer_id` = '" . (int)$customer_id . "', `token` = '" . $this->db->escape($data['token']) . "', `ip` = '" . $this->db->escape($data['ip']) . "', `user_agent` = '" . $this->db->escape($data['user_agent']) . "', `date_added` = NOW()");
|
| 829: | }
|
| 830: |
|
| 831: | |
| 832: | |
| 833: | |
| 834: | |
| 835: | |
| 836: | |
| 837: | |
| 838: |
|
| 839: | public function editAuthorizeStatus(int $customer_authorize_id, bool $status): void {
|
| 840: | $this->db->query("UPDATE `" . DB_PREFIX . "customer_authorize` SET `status` = '" . (bool)$status . "' WHERE `customer_authorize_id` = '" . (int)$customer_authorize_id . "'");
|
| 841: | }
|
| 842: |
|
| 843: | |
| 844: | |
| 845: | |
| 846: | |
| 847: | |
| 848: | |
| 849: | |
| 850: |
|
| 851: | public function editAuthorizeTotal(int $customer_authorize_id, int $total): void {
|
| 852: | $this->db->query("UPDATE `" . DB_PREFIX . "customer_authorize` SET `total` = '" . (int)$total . "' WHERE `customer_authorize_id` = '" . (int)$customer_authorize_id . "'");
|
| 853: | }
|
| 854: |
|
| 855: | |
| 856: | |
| 857: | |
| 858: | |
| 859: | |
| 860: | |
| 861: |
|
| 862: | public function resetAuthorizes(int $customer_id): void {
|
| 863: | $this->db->query("UPDATE `" . DB_PREFIX . "customer_authorize` SET `total` = '0' WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 864: | }
|
| 865: |
|
| 866: | |
| 867: | |
| 868: | |
| 869: | |
| 870: | |
| 871: | |
| 872: | |
| 873: |
|
| 874: | public function deleteAuthorizes(int $customer_id, int $customer_authorize_id = 0): void {
|
| 875: | $sql = "DELETE FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "'";
|
| 876: |
|
| 877: | if ($customer_authorize_id) {
|
| 878: | $sql .= " AND `customer_authorize_id` = '" . (int)$customer_authorize_id . "'";
|
| 879: | }
|
| 880: |
|
| 881: | $this->db->query($sql);
|
| 882: | }
|
| 883: |
|
| 884: | |
| 885: | |
| 886: | |
| 887: | |
| 888: | |
| 889: | |
| 890: | |
| 891: |
|
| 892: | public function getAuthorizeByToken(int $customer_id, string $token): array {
|
| 893: | $query = $this->db->query("SELECT *, (SELECT SUM(`total`) FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "') AS `attempts` FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "' AND `token` = '" . $this->db->escape($token) . "'");
|
| 894: |
|
| 895: | return $query->row;
|
| 896: | }
|
| 897: |
|
| 898: | |
| 899: | |
| 900: | |
| 901: | |
| 902: | |
| 903: | |
| 904: | |
| 905: | |
| 906: |
|
| 907: | public function getAuthorizes(int $customer_id, int $start = 0, int $limit = 10): array {
|
| 908: | if ($start < 0) {
|
| 909: | $start = 0;
|
| 910: | }
|
| 911: |
|
| 912: | if ($limit < 1) {
|
| 913: | $limit = 10;
|
| 914: | }
|
| 915: |
|
| 916: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "' LIMIT " . (int)$start . "," . (int)$limit);
|
| 917: |
|
| 918: | if ($query->num_rows) {
|
| 919: | return $query->rows;
|
| 920: | } else {
|
| 921: | return [];
|
| 922: | }
|
| 923: | }
|
| 924: |
|
| 925: | |
| 926: | |
| 927: | |
| 928: | |
| 929: | |
| 930: | |
| 931: |
|
| 932: | public function getTotalAuthorizes(int $customer_id): int {
|
| 933: | $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "'");
|
| 934: |
|
| 935: | if ($query->num_rows) {
|
| 936: | return (int)$query->row['total'];
|
| 937: | } else {
|
| 938: | return 0;
|
| 939: | }
|
| 940: | }
|
| 941: | }
|
| 942: | |