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