amazonus.php (28601B)
1 <?php 2 class ModelExtensionOpenBayAmazonus extends Model { 3 public function install() { 4 $this->load->model('setting/event'); 5 6 $this->model_setting_event->addEvent('openbay_amazonus_add_order', 'catalog/model/checkout/order/addOrderHistory/after', 'extension/openbay/amazonus/eventAddOrderHistory'); 7 8 $this->db->query(" 9 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_order` ( 10 `order_id` int(11) NOT NULL , 11 `amazonus_order_id` char(19) NOT NULL , 12 `courier_id` varchar(255) NOT NULL , 13 `courier_other` tinyint(1) NOT NULL, 14 `tracking_no` varchar(255) NOT NULL , 15 PRIMARY KEY (`order_id`, `amazonus_order_id`) 16 ) DEFAULT COLLATE=utf8_general_ci;"); 17 18 $this->db->query(" 19 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_order_product` ( 20 `order_product_id` int(11) NOT NULL , 21 `amazonus_order_item_id` varchar(255) NOT NULL, 22 PRIMARY KEY(`order_product_id`, `amazonus_order_item_id`) 23 );"); 24 25 $this->db->query(" 26 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_product_unshipped` ( 27 `order_id` int(11) NOT NULL, 28 `product_id` int(11) NOT NULL, 29 `quantity` int(11) NOT NULL DEFAULT '0', 30 PRIMARY KEY (`order_id`,`product_id`) 31 ) DEFAULT COLLATE=utf8_general_ci;"); 32 33 $this->db->query(" 34 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_product` ( 35 `version` int(11) NOT NULL DEFAULT 2, 36 `product_id` int(11) NOT NULL , 37 `category` varchar(255) NOT NULL , 38 `sku` varchar(255) NOT NULL , 39 `insertion_id` varchar(255) NOT NULL , 40 `data` text NOT NULL , 41 `status` enum('saved','uploaded','ok','error') NOT NULL , 42 `price` decimal(15,4) NOT NULL COMMENT 'Price on Amazonus' , 43 `var` char(100) NOT NULL DEFAULT '', 44 `marketplaces` text NOT NULL , 45 `messages` text NOT NULL, 46 PRIMARY KEY (`product_id`, `var`) 47 );"); 48 49 $this->db->query(" 50 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_product_error` ( 51 `error_id` int(11) NOT NULL AUTO_INCREMENT, 52 `sku` varchar(255) NOT NULL , 53 `insertion_id` varchar(255) NOT NULL , 54 `error_code` int(11) NOT NULL , 55 `message` text NOT NULL , 56 PRIMARY KEY (`error_id`) 57 );"); 58 59 $this->db->query(" 60 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_product_link` ( 61 `id` int(11) NOT NULL AUTO_INCREMENT, 62 `amazonus_sku` varchar(255) NOT NULL, 63 `var` char(100) NOT NULL DEFAULT '', 64 `product_id` int(11) NOT NULL, 65 PRIMARY KEY (`id`) 66 ) DEFAULT COLLATE=utf8_general_ci;"); 67 68 $this->db->query(" 69 CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazonus_product_search` ( 70 `product_id` int(11) NOT NULL, 71 `status` enum('searching','finished') NOT NULL, 72 `matches` int(11) DEFAULT NULL, 73 `data` text, 74 PRIMARY KEY (`product_id`) 75 ) DEFAULT COLLATE=utf8_general_ci;"); 76 77 $this->db->query(" 78 CREATE TABLE IF NOT EXISTS`" . DB_PREFIX . "amazonus_listing_report` ( 79 `sku` varchar(255) NOT NULL, 80 `quantity` int(10) unsigned NOT NULL, 81 `asin` varchar(255) NOT NULL, 82 `price` decimal(10,4) NOT NULL, 83 PRIMARY KEY (`sku`) 84 ) DEFAULT COLLATE=utf8_general_ci; 85 "); 86 } 87 88 public function uninstall() { 89 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_order`"); 90 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_order_product`"); 91 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_product2`"); 92 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_product`"); 93 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_product_link`"); 94 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_product_unshipped`"); 95 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_product_error`"); 96 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_process`"); 97 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_product_search`"); 98 $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazonus_listing_report`"); 99 100 $this->db->query("DELETE FROM `" . DB_PREFIX . "setting` WHERE `code` = 'openbay_amazonus'"); 101 102 $this->load->model('setting/event'); 103 $this->model_setting_event->deleteEventByCode('openbay_amazonus_add_order'); 104 } 105 106 public function patch() { 107 if ($this->config->get('openbay_amazonus_status') == 1) { 108 109 } 110 } 111 112 public function verifyConfig($data) { 113 $log = new Log('amazonus.log'); 114 115 $request_xml = '<Request> 116 <ResponseURL>' . HTTPS_CATALOG . 'index.php?route=extension/openbay/amazonus/order</ResponseURL> 117 <MarketplaceIDs>'; 118 foreach ($data['openbay_amazonus_orders_marketplace_ids'] as $marketplace_id) { 119 $request_xml .= ' <MarketplaceID>' . $marketplace_id . '</MarketplaceID>'; 120 } 121 $request_xml .= ' 122 </MarketplaceIDs> 123 </Request>'; 124 125 $response = $this->openbay->amazonus->call('order/scheduleOrders', $request_xml, false); 126 127 libxml_use_internal_errors(true); 128 $response_xml = simplexml_load_string($response); 129 libxml_use_internal_errors(false); 130 131 if ($response_xml && $response_xml->Status == '0') { 132 $log->write('Scheduling orders call was successful'); 133 return true; 134 } 135 136 $log->write('Failed to schedule orders. Response: ' . $response); 137 138 return false; 139 } 140 141 public function saveProduct($product_id, $data_array) { 142 if (isset($data_array['fields']['item-price'])) { 143 $price = $data_array['fields']['item-price']; 144 } else if (isset($data_array['fields']['price'])) { 145 $price = $data_array['fields']['price']; 146 } else if (isset($data_array['fields']['StandardPrice'])) { 147 $price = $data_array['fields']['StandardPrice']; 148 } else { 149 $price = 0; 150 } 151 152 $category = (isset($data_array['category'])) ? $data_array['category'] : ""; 153 $sku = (isset($data_array['fields']['sku'])) ? $data_array['fields']['sku'] : ""; 154 if (isset($data_array['fields']['sku'])) { 155 $sku = $data_array['fields']['sku']; 156 } else if (isset($data_array['fields']['SKU'])) { 157 $sku = $data_array['fields']['SKU']; 158 } 159 160 $var = isset($data_array['optionVar']) ? $data_array['optionVar'] : ''; 161 162 $marketplaces = isset($data_array['marketplace_ids']) ? serialize($data_array['marketplace_ids']) : serialize(array()); 163 164 $data_encoded = json_encode(array('fields' => $data_array['fields'])); 165 166 $this->db->query(" 167 REPLACE INTO `" . DB_PREFIX . "amazonus_product` 168 SET `product_id` = '" . (int)$product_id . "', 169 `sku` = '" . $this->db->escape($sku) . "', 170 `category` = '" . $this->db->escape($category) . "', 171 `data` = '" . $this->db->escape($data_encoded) . "', 172 `status` = 'saved', 173 `insertion_id` = '', 174 `price` = '" . $price . "', 175 `var` = '" . $this->db->escape($var) . "', 176 `marketplaces` = '" . $this->db->escape($marketplaces) . "'"); 177 } 178 179 public function deleteSaved($product_id, $var = '') { 180 $this->db->query("DELETE FROM `" . DB_PREFIX . "amazonus_product` 181 WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "'"); 182 } 183 184 public function getSavedProducts() { 185 return $this->db->query(" 186 SELECT `ap`.`status`, `ap`.`product_id`, `ap`.`sku` as `amazonus_sku`, `pd`.`name` as `product_name`, `p`.`model` as `product_model`, `p`.`sku` as `product_sku`, `ap`.`var` as `var` 187 FROM `" . DB_PREFIX . "amazonus_product` as `ap` 188 LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` 189 ON `ap`.`product_id` = `pd`.`product_id` 190 LEFT JOIN `" . DB_PREFIX . "product` as `p` 191 ON `ap`.`product_id` = `p`.`product_id` 192 WHERE `ap`.`status` = 'saved' 193 AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'")->rows; 194 } 195 196 public function getSavedProductsData() { 197 return $this->db->query(" 198 SELECT * FROM `" . DB_PREFIX . "amazonus_product` 199 WHERE `status` = 'saved' AND `version` = 2")->rows; 200 } 201 202 public function getProduct($product_id, $var = '') { 203 return $this->db->query(" 204 SELECT * FROM `" . DB_PREFIX . "amazonus_product` 205 WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "' AND `version` = 2")->row; 206 } 207 208 public function getProductCategory($product_id, $var = '') { 209 $row = $this->db->query(" 210 SELECT `category` FROM `" . DB_PREFIX . "amazonus_product` 211 WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "' AND `version` = 2")->row; 212 if (isset($row['category'])) { 213 return $row['category']; 214 } else { 215 return ""; 216 } 217 } 218 219 public function setProductUploaded($product_id, $insertion_id, $var = '') { 220 $this->db->query( 221 "UPDATE `" . DB_PREFIX . "amazonus_product` 222 SET `status` = 'uploaded', `insertion_id` = '" . $this->db->escape($insertion_id) . "' 223 WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "' AND `version` = 2"); 224 } 225 226 public function resetUploaded($insertion_id) { 227 $this->db->query( 228 "UPDATE `" . DB_PREFIX . "amazonus_product` 229 SET `status` = 'saved', `insertion_id` = '' 230 WHERE `insertion_id` = '" . $this->db->escape($insertion_id) . "' AND `version` = 2"); 231 } 232 233 public function getProductStatus($product_id) { 234 235 $rows_uploaded = $this->db->query(" 236 SELECT COUNT(*) count 237 FROM `" . DB_PREFIX . "amazonus_product` 238 WHERE `product_id` = '" . (int)$product_id . "' AND status = 'uploaded'")->row; 239 $rows_uploaded = $rows_uploaded['count']; 240 241 $rows_ok = $this->db->query(" 242 SELECT COUNT(*) count 243 FROM `" . DB_PREFIX . "amazonus_product` 244 WHERE `product_id` = '" . (int)$product_id . "' AND status = 'ok'")->row; 245 $rows_ok = $rows_ok['count']; 246 247 $rows_error = $this->db->query(" 248 SELECT COUNT(*) count 249 FROM `" . DB_PREFIX . "amazonus_product` 250 WHERE `product_id` = '" . (int)$product_id . "' AND status = 'error'")->row; 251 $rows_error = $rows_error['count']; 252 253 $rows_saved = $this->db->query(" 254 SELECT COUNT(*) count 255 FROM `" . DB_PREFIX . "amazonus_product` 256 WHERE `product_id` = '" . (int)$product_id . "' AND status = 'saved'")->row; 257 $rows_saved = $rows_saved['count']; 258 259 $rows_total = $rows_uploaded + $rows_ok + $rows_error + $rows_saved; 260 261 $links = $this->db->query(" 262 SELECT COUNT(*) as count 263 FROM `" . DB_PREFIX . "amazonus_product_link` 264 WHERE `product_id` = '" . (int)$product_id . "'")->row; 265 $links = $links['count']; 266 267 if ($rows_total === 0 && $links > 0) { 268 return 'linked'; 269 } else if ($rows_total == 0) { 270 return false; 271 } 272 273 if ($rows_uploaded > 0) { 274 return 'processing'; 275 } 276 277 if ($rows_uploaded == 0 && $rows_ok > 0 && $rows_error == 0) { 278 return 'ok'; 279 } 280 281 if ($rows_saved > 0) { 282 return 'saved'; 283 } 284 285 if ($rows_uploaded == 0 && $rows_error > 0 && $rows_ok == 0) { 286 $quick = $this->db->query("SELECT * FROM `" . DB_PREFIX . "amazonus_product` WHERE `product_id` = " . (int)$product_id . " AND `version` = 3")->row; 287 288 if ($quick) { 289 return 'error_quick'; 290 } else { 291 return 'error_advanced'; 292 } 293 } else { 294 return 'error_few'; 295 } 296 297 return false; 298 } 299 300 public function getProductErrors($product_id, $version = 2) { 301 if ($version == 3) { 302 $message_row = $this->db->query(" 303 SELECT `messages` FROM `" . DB_PREFIX . "amazonus_product` 304 WHERE `product_id` = '" . (int)$product_id . "' AND `version` = 3")->row; 305 306 return json_decode($message_row['messages']); 307 } 308 309 $result = array(); 310 311 $insertion_rows = $this->db->query("SELECT `sku`, `insertion_id` FROM `" . DB_PREFIX . "amazonus_product` WHERE `product_id` = '" . (int)$product_id . "' AND `version` = 2")->rows; 312 313 if (!empty($insertion_rows)) { 314 foreach($insertion_rows as $insertion_row) { 315 $error_rows = $this->db->query("SELECT * FROM `" . DB_PREFIX . "amazonus_product_error` WHERE `sku` = '" . $this->db->escape($insertion_row['sku']) . "' AND `insertion_id` = '" . $this->db->escape($insertion_row['insertion_id']) . "'")->rows; 316 317 foreach($error_rows as $error_row) { 318 $result[] = $error_row; 319 } 320 } 321 } 322 return $result; 323 } 324 325 public function getProductsWithErrors() { 326 return $this->db->query(" 327 SELECT `product_id`, `sku` FROM `" . DB_PREFIX . "amazonus_product` 328 WHERE `status` = 'error' AND `version` = 2")->rows; 329 } 330 331 public function deleteProduct($product_id) { 332 $this->db->query( 333 "DELETE FROM `" . DB_PREFIX . "amazonus_product` 334 WHERE `product_id` = '" . (int)$product_id . "'"); 335 } 336 337 public function linkProduct($amazonus_sku, $product_id, $var = '') { 338 $count = $this->db->query("SELECT COUNT(*) as `count` FROM `" . DB_PREFIX . "amazonus_product_link` WHERE `product_id` = '" . (int)$product_id . "' AND `amazonus_sku` = '" . $this->db->escape($amazonus_sku) . "' AND `var` = '" . $this->db->escape($var) . "' LIMIT 1")->row; 339 340 if ($count['count'] == 0) { 341 $this->db->query("INSERT INTO `" . DB_PREFIX . "amazonus_product_link` SET `product_id` = '" . (int)$product_id . "', `amazonus_sku` = '" . $this->db->escape($amazonus_sku) . "', `var` = '" . $this->db->escape($var) . "'"); 342 } 343 } 344 345 public function removeProductLink($amazonus_sku) { 346 $this->db->query( 347 "DELETE FROM `" . DB_PREFIX . "amazonus_product_link` 348 WHERE `amazonus_sku` = '" . $this->db->escape($amazonus_sku) . "'"); 349 } 350 351 public function removeAdvancedErrors($product_id) { 352 $product_rows = $this->db->query(" 353 SELECT `insertion_id` FROM `" . DB_PREFIX . "amazonus_product` 354 WHERE `product_id` = '" . (int)$product_id . "' AND `version` = 2")->rows; 355 356 foreach ($product_rows as $product) { 357 $this->db->query( 358 "DELETE FROM `" . DB_PREFIX . "amazonus_product_error` 359 WHERE `insertion_id` = '" . $this->db->escape($product['insertion_id']) . "'"); 360 } 361 362 $this->db->query( 363 "UPDATE `" . DB_PREFIX . "amazonus_product` 364 SET `status` = 'saved', `insertion_id` = '' 365 WHERE `product_id` = '" . (int)$product_id . "' AND `status` = 'error' AND `version` = 2"); 366 } 367 368 public function getProductLinks($product_id = 'all') { 369 $query = "SELECT `apl`.`amazonus_sku`, `apl`.`product_id`, `pd`.`name` as `product_name`, `p`.`model`, `p`.`sku`, `apl`.`var`, '' as `combination` 370 FROM `" . DB_PREFIX . "amazonus_product_link` as `apl` 371 LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` 372 ON `apl`.`product_id` = `pd`.`product_id` 373 LEFT JOIN `" . DB_PREFIX . "product` as `p` 374 ON `apl`.`product_id` = `p`.`product_id`"; 375 if ($product_id != 'all') { 376 $query .= " WHERE `apl`.`product_id` = '" . (int)$product_id . "' AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'"; 377 } else { 378 $query .= "WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'"; 379 } 380 381 $product_links = $this->db->query($query)->rows; 382 383 if ($this->openbay->addonLoad('openstock')) { 384 $this->load->model('extension/module/openstock'); 385 $this->load->model('tool/image'); 386 387 foreach ($product_links as $key => $product_link) { 388 $variants = $this->model_setting_module_openstock->getVariants($product_link['product_id']); 389 390 if (!empty($variants)) { 391 foreach($variants as $variant) { 392 if ($variant['sku'] == $product_link['var']) { 393 $product_links[$key]['combination'] = $variant['combination']; 394 break; 395 } 396 } 397 } 398 } 399 400 return $product_links; 401 } else { 402 return $product_links; 403 } 404 } 405 406 public function getUnlinkedProducts() { 407 if ($this->openbay->addonLoad('openstock')) { 408 $rows = $this->db->query(" 409 SELECT `p`.`product_id`, `p`.`model`, `p`.`sku`, `pd`.`name` as `product_name`, '' as `var`, '' as `combination`, `p`.`has_option` 410 FROM `" . DB_PREFIX . "product` as `p` 411 LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` 412 ON `p`.`product_id` = `pd`.`product_id` 413 AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'")->rows; 414 415 $result = array(); 416 $this->load->model('extension/module/openstock'); 417 $this->load->model('tool/image'); 418 foreach($rows as $row) { 419 if ($row['has_option'] == 1) { 420 $stock_opts = $this->model_setting_module_openstock->getVariants($row['product_id']); 421 foreach($stock_opts as $opt) { 422 if ($this->productLinkExists($row['product_id'], $opt['sku'])) { 423 continue; 424 } 425 $row['sku'] = $opt['sku']; 426 $row['combination'] = $opt['combination']; 427 $row['sku'] = $opt['sku']; 428 $result[] = $row; 429 } 430 } else { 431 if (!$this->productLinkExists($row['product_id'], $row['sku'])) { 432 $result[] = $row; 433 } 434 } 435 } 436 } else { 437 $result = $this->db->query(" 438 SELECT `p`.`product_id`, `p`.`model`, `p`.`sku`, `pd`.`name` as `product_name`, '' as `var`, '' as `combination` 439 FROM `" . DB_PREFIX . "product` as `p` 440 LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` 441 ON `p`.`product_id` = `pd`.`product_id` 442 LEFT JOIN `" . DB_PREFIX . "amazonus_product_link` as `apl` 443 ON `apl`.`product_id` = `p`.`product_id` 444 WHERE `apl`.`amazonus_sku` IS NULL 445 AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'")->rows; 446 } 447 448 return $result; 449 } 450 451 private function productLinkExists($product_id, $var) { 452 $link = $this->db->query("SELECT * FROM `" . DB_PREFIX . "amazonus_product_link` WHERE `product_id` = " . (int)$product_id . " AND var = '" . $this->db->escape($var) . "'")->row; 453 454 if (empty($link)) { 455 return false; 456 } else { 457 return true; 458 } 459 } 460 461 public function getOrderStatusString($order_id) { 462 $row = $this->db->query(" 463 SELECT `s`.`key` 464 FROM `" . DB_PREFIX . "order` `o` 465 JOIN `" . DB_PREFIX . "setting` `s` ON `o`.`order_id` = " . (int)$order_id . " AND `s`.`value` = `o`.`order_status_id` 466 WHERE `s`.`key` = 'openbay_amazonus_order_status_shipped' OR `s`.`key` = 'openbay_amazonus_order_status_canceled' 467 LIMIT 1")->row; 468 469 if (!isset($row['key']) || empty($row['key'])) { 470 return null; 471 } 472 473 $key = $row['key']; 474 475 switch ($key) { 476 case 'openbay_amazonus_order_status_shipped': 477 $order_status = 'shipped'; 478 break; 479 case 'openbay_amazonus_order_status_canceled': 480 $order_status = 'canceled'; 481 break; 482 483 default: 484 $order_status = null; 485 break; 486 } 487 488 return $order_status; 489 } 490 491 public function updateAmazonusOrderTracking($order_id, $courier_id, $courier_from_list, $tracking_no) { 492 $this->db->query("UPDATE `" . DB_PREFIX . "amazonus_order` SET `courier_id` = '" . $this->db->escape($courier_id) . "', `courier_other` = " . (int)!$courier_from_list . ", `tracking_no` = '" . $this->db->escape($tracking_no) . "' WHERE `order_id` = " . (int)$order_id . ""); 493 } 494 495 public function getAmazonusOrderId($order_id) { 496 $row = $this->db->query(" 497 SELECT `amazonus_order_id` 498 FROM `" . DB_PREFIX . "amazonus_order` 499 WHERE `order_id` = " . (int)$order_id . " 500 LIMIT 1")->row; 501 502 if (isset($row['amazonus_order_id']) && !empty($row['amazonus_order_id'])) { 503 return $row['amazonus_order_id']; 504 } 505 506 return null; 507 } 508 509 public function getAmazonusOrderedProducts($order_id) { 510 return $this->db->query(" 511 SELECT `aop`.`amazonus_order_item_id`, `op`.`quantity` 512 FROM `" . DB_PREFIX . "amazonus_order_product` `aop` 513 JOIN `" . DB_PREFIX . "order_product` `op` ON `op`.`order_product_id` = `aop`.`order_product_id` 514 AND `op`.`order_id` = " . (int)$order_id)->rows; 515 } 516 517 public function getProductQuantity($product_id, $var = '') { 518 $result = null; 519 520 if ($var !== '' && $this->openbay->addonLoad('openstock')) { 521 $this->load->model('tool/image'); 522 $this->load->model('extension/module/openstock'); 523 $option_stocks = $this->model_setting_module_openstock->getVariants($product_id); 524 525 $option = null; 526 foreach ($option_stocks as $option_iterator) { 527 if ($option_iterator['sku'] === $var) { 528 $option = $option_iterator; 529 break; 530 } 531 } 532 533 if ($option != null) { 534 $result = $option['stock']; 535 } 536 } else { 537 $this->load->model('catalog/product'); 538 $product_info = $this->model_catalog_product->getProduct($product_id); 539 540 if (isset($product_info['quantity'])) { 541 $result = $product_info['quantity']; 542 } 543 } 544 return $result; 545 } 546 547 public function getProductSearchTotal($data = array()) { 548 $sql = " 549 SELECT COUNT(*) AS product_total 550 FROM " . DB_PREFIX . "product p 551 LEFT JOIN " . DB_PREFIX . "amazonus_product_search aps ON p.product_id = aps.product_id 552 LEFT JOIN " . DB_PREFIX . "amazonus_product_link apl ON p.product_id = apl.product_id 553 LEFT JOIN " . DB_PREFIX . "amazonus_product ap ON p.product_id = ap.product_id 554 WHERE apl.product_id IS NULL AND ap.product_id IS NULL "; 555 556 if (!empty($data['status'])) { 557 $sql .= " AND aps.status = '" . $this->db->escape($data['status']) . "'"; 558 } 559 560 return $this->db->query($sql)->row['product_total']; 561 } 562 563 public function getProductSearch($data = array()) { 564 $sql = " 565 SELECT p.product_id, aps.status, aps.data, aps.matches 566 FROM " . DB_PREFIX . "product p 567 LEFT JOIN " . DB_PREFIX . "amazonus_product_search aps ON p.product_id = aps.product_id 568 LEFT JOIN " . DB_PREFIX . "amazonus_product_link apl ON p.product_id = apl.product_id 569 LEFT JOIN " . DB_PREFIX . "amazonus_product ap ON p.product_id = ap.product_id 570 WHERE apl.product_id IS NULL AND ap.product_id IS NULL "; 571 572 if (!empty($data['status'])) { 573 $sql .= " AND aps.status = '" . $this->db->escape($data['status']) . "'"; 574 } 575 576 $sql .= " LIMIT " . (int)$data['start'] . ", " . (int)$data['limit']; 577 578 $results = array(); 579 580 $rows = $this->db->query($sql)->rows; 581 582 foreach ($rows as $row) { 583 $results[] = array( 584 'product_id' => $row['product_id'], 585 'status' => $row['status'], 586 'matches' => $row['matches'], 587 'data' => json_decode($row['data'], 1), 588 ); 589 } 590 591 return $results; 592 } 593 594 public function updateAmazonSkusQuantities($skus) { 595 $sku_array = array(); 596 597 foreach ($skus as $sku) { 598 $sku_array[] = "'" . $this->db->escape($sku) . "'"; 599 } 600 601 if ($this->openbay->addonLoad('openstock')) { 602 $rows = $this->db->query(" 603 SELECT apl.amazonus_sku, if (por.product_id IS NULL, p.quantity, por.stock) AS 'quantity' 604 FROM " . DB_PREFIX . "amazonus_product_link apl 605 JOIN " . DB_PREFIX . "product p ON apl.product_id = p.product_id 606 LEFT JOIN " . DB_PREFIX . "product_option_variant por ON apl.product_id = por.product_id AND apl.var = por.sku 607 WHERE apl.amazonus_sku IN (" . implode(',', $sku_array) . ") 608 ")->rows; 609 } else { 610 $rows = $this->db->query(" 611 SELECT apl.amazonus_sku, p.quantity 612 FROM " . DB_PREFIX . "amazonus_product_link apl 613 JOIN " . DB_PREFIX . "product p ON apl.product_id = p.product_id 614 WHERE apl.amazonus_sku IN (" . implode(',', $sku_array) . ") 615 ")->rows; 616 } 617 618 $return = array(); 619 620 foreach ($rows as $row) { 621 $return[$row['amazonus_sku']] = $row['quantity']; 622 } 623 624 $this->openbay->amazonus->updateQuantities($return); 625 } 626 627 public function getTotalUnlinkedItemsFromReport() { 628 if ($this->openbay->addonLoad('openstock')) { 629 $result = $this->db->query(" 630 SELECT alr.sku AS 'amazon_sku', alr.quantity AS 'amazon_quantity', alr.asin, alr.price AS 'amazon_price', oc_sku.product_id, pd.name, oc_sku.sku, oc_sku.var, oc_sku.quantity, oc_sku.pov_id 631 FROM " . DB_PREFIX . "amazonus_listing_report alr 632 LEFT JOIN ( 633 SELECT p.product_id, if (por.product_id IS NULL, p.sku, por.sku) AS 'sku', if (por.product_id IS NULL, NULL, por.sku) AS 'var', if (por.product_id IS NULL, p.quantity, por.stock) AS 'quantity', por.product_option_variant_id AS pov_id 634 FROM " . DB_PREFIX . "product p 635 LEFT JOIN " . DB_PREFIX . "product_option_variant por USING(product_id) 636 ) AS oc_sku ON alr.sku = oc_sku.sku 637 LEFT JOIN " . DB_PREFIX . "amazonus_product_link apl ON (oc_sku.var IS NULL AND oc_sku.product_id = apl.product_id) OR (oc_sku.var IS NOT NULL AND oc_sku.product_id = apl.product_id AND oc_sku.var = apl.var) 638 LEFT JOIN " . DB_PREFIX . "product_description pd ON oc_sku.product_id = pd.product_id AND pd.language_id = " . (int)$this->config->get('config_language_id') . " 639 WHERE apl.product_id IS NULL 640 "); 641 } else { 642 $result = $this->db->query(" 643 SELECT alr.sku AS 'amazon_sku', alr.quantity AS 'amazon_quantity', alr.asin, alr.price AS 'amazon_price', oc_sku.product_id, pd.name, oc_sku.sku, oc_sku.var, oc_sku.quantity, '' AS combination 644 FROM " . DB_PREFIX . "amazonus_listing_report alr 645 LEFT JOIN ( 646 SELECT p.product_id, p.sku, NULL AS 'var', p.quantity 647 FROM " . DB_PREFIX . "product p 648 ) AS oc_sku ON alr.sku = oc_sku.sku 649 LEFT JOIN " . DB_PREFIX . "amazonus_product_link apl ON (oc_sku.var IS NULL AND oc_sku.product_id = apl.product_id) OR (oc_sku.var IS NOT NULL AND oc_sku.product_id = apl.product_id AND oc_sku.var = apl.var) 650 LEFT JOIN " . DB_PREFIX . "product_description pd ON oc_sku.product_id = pd.product_id AND pd.language_id = " . (int)$this->config->get('config_language_id') . " 651 WHERE apl.product_id IS NULL 652 ORDER BY alr.sku 653 "); 654 } 655 656 return (int)$result->num_rows; 657 } 658 659 public function getUnlinkedItemsFromReport($limit = 100, $page = 1) { 660 $start = $limit * ($page - 1); 661 662 if ($this->openbay->addonLoad('openstock')) { 663 $this->load->model('extension/module/openstock'); 664 $rows = $this->db->query(" 665 SELECT alr.sku AS 'amazon_sku', alr.quantity AS 'amazon_quantity', alr.asin, alr.price AS 'amazon_price', oc_sku.product_id, pd.name, oc_sku.sku, oc_sku.var, oc_sku.quantity, oc_sku.pov_id 666 FROM " . DB_PREFIX . "amazonus_listing_report alr 667 LEFT JOIN ( 668 SELECT p.product_id, if (por.product_id IS NULL, p.sku, por.sku) AS 'sku', if (por.product_id IS NULL, NULL, por.sku) AS 'var', if (por.product_id IS NULL, p.quantity, por.stock) AS 'quantity', por.product_option_variant_id AS pov_id 669 FROM " . DB_PREFIX . "product p 670 LEFT JOIN " . DB_PREFIX . "product_option_variant por USING(product_id) 671 ) AS oc_sku ON alr.sku = oc_sku.sku 672 LEFT JOIN " . DB_PREFIX . "amazonus_product_link apl ON (oc_sku.var IS NULL AND oc_sku.product_id = apl.product_id) OR (oc_sku.var IS NOT NULL AND oc_sku.product_id = apl.product_id AND oc_sku.var = apl.var) 673 LEFT JOIN " . DB_PREFIX . "product_description pd ON oc_sku.product_id = pd.product_id AND pd.language_id = " . (int)$this->config->get('config_language_id') . " 674 WHERE apl.product_id IS NULL 675 ORDER BY alr.sku 676 LIMIT " . (int)$start . "," . (int)$limit)->rows; 677 } else { 678 $rows = $this->db->query(" 679 SELECT alr.sku AS 'amazon_sku', alr.quantity AS 'amazon_quantity', alr.asin, alr.price AS 'amazon_price', oc_sku.product_id, pd.name, oc_sku.sku, oc_sku.var, oc_sku.quantity, '' AS combination 680 FROM " . DB_PREFIX . "amazonus_listing_report alr 681 LEFT JOIN ( 682 SELECT p.product_id, p.sku, NULL AS 'var', p.quantity 683 FROM " . DB_PREFIX . "product p 684 ) AS oc_sku ON alr.sku = oc_sku.sku 685 LEFT JOIN " . DB_PREFIX . "amazonus_product_link apl ON (oc_sku.var IS NULL AND oc_sku.product_id = apl.product_id) OR (oc_sku.var IS NOT NULL AND oc_sku.product_id = apl.product_id AND oc_sku.var = apl.var) 686 LEFT JOIN " . DB_PREFIX . "product_description pd ON oc_sku.product_id = pd.product_id AND pd.language_id = " . (int)$this->config->get('config_language_id') . " 687 WHERE apl.product_id IS NULL 688 ORDER BY alr.sku 689 LIMIT " . (int)$start . "," . (int)$limit)->rows; 690 } 691 692 $products = array(); 693 694 foreach ($rows as $row) { 695 $combinations = array(); 696 697 if (isset($row['pov_id']) && !empty($row['pov_id'])) { 698 $variants = (isset($row['pov_id']) ? $this->model_setting_module_openstock->getVariant($row['pov_id']) : ''); 699 700 foreach ($variants as $variant) { 701 $combinations[] = $variant['option_value_name']; 702 } 703 } 704 705 $products[] = array( 706 'product_id' => $row['product_id'], 707 'name' => $row['name'], 708 'sku' => $row['sku'], 709 'var' => $row['var'], 710 'quantity' => $row['quantity'], 711 'amazon_sku' => $row['amazon_sku'], 712 'amazon_quantity' => $row['amazon_quantity'], 713 'amazon_price' => number_format($row['amazon_price'], 2, '.', ''), 714 'asin' => $row['asin'], 715 'combination' => implode(' > ', $combinations), 716 ); 717 } 718 719 return $products; 720 } 721 722 public function deleteListingReports() { 723 $this->db->query(" 724 DELETE FROM " . DB_PREFIX . "amazonus_listing_report 725 "); 726 } 727 }