diff options
Diffstat (limited to 'public/admin/model/extension/openbay/amazon.php')
| -rw-r--r-- | public/admin/model/extension/openbay/amazon.php | 741 |
1 files changed, 741 insertions, 0 deletions
diff --git a/public/admin/model/extension/openbay/amazon.php b/public/admin/model/extension/openbay/amazon.php new file mode 100644 index 0000000..a323ab9 --- /dev/null +++ b/public/admin/model/extension/openbay/amazon.php @@ -0,0 +1,741 @@ +<?php +class ModelExtensionOpenBayAmazon extends Model { + public function install() { + $this->load->model('setting/event'); + + $this->model_setting_event->addEvent('openbay_amazon_add_order', 'catalog/model/checkout/order/addOrderHistory/after', 'extension/openbay/amazon/eventAddOrderHistory'); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_order` ( + `order_id` int(11) NOT NULL , + `amazon_order_id` char(19) NOT NULL , + `courier_id` varchar(255) NOT NULL , + `courier_other` tinyint(1) NOT NULL, + `tracking_no` varchar(255) NOT NULL , + PRIMARY KEY (`order_id`, `amazon_order_id`) + ) DEFAULT COLLATE=utf8_general_ci;"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_order_product` ( + `order_product_id` int(11) NOT NULL , + `amazon_order_item_id` varchar(255) NOT NULL, + PRIMARY KEY(`order_product_id`, `amazon_order_item_id`) + );"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_product_unshipped` ( + `order_id` int(11) NOT NULL, + `product_id` int(11) NOT NULL, + `quantity` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`order_id`,`product_id`) + ) DEFAULT COLLATE=utf8_general_ci;"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_product` ( + `version` int(11) NOT NULL DEFAULT 2, + `product_id` int(11) NOT NULL , + `category` varchar(255) NOT NULL , + `sku` varchar(255) NOT NULL , + `insertion_id` varchar(255) NOT NULL , + `data` text NOT NULL , + `status` enum('saved','uploaded','ok','error') NOT NULL , + `price` decimal(15,4) NOT NULL COMMENT 'Price on Amazon' , + `var` char(100) NOT NULL DEFAULT '', + `marketplaces` text NOT NULL , + `messages` text NOT NULL, + PRIMARY KEY (`product_id`, `var`) + );"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_product_error` ( + `error_id` int(11) NOT NULL AUTO_INCREMENT, + `sku` varchar(255) NOT NULL , + `insertion_id` varchar(255) NOT NULL , + `error_code` int(11) NOT NULL , + `message` text NOT NULL , + PRIMARY KEY (`error_id`) + );"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_product_link` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `amazon_sku` varchar(255) NOT NULL, + `var` char(100) NOT NULL DEFAULT '', + `product_id` int(11) NOT NULL, + PRIMARY KEY (`id`) + ) DEFAULT COLLATE=utf8_general_ci;"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_product_search` ( + `product_id` int(11) NOT NULL, + `marketplace` enum('uk','de','es','it','fr') NOT NULL, + `status` enum('searching','finished') NOT NULL, + `matches` int(11) DEFAULT NULL, + `data` text, + PRIMARY KEY (`product_id`,`marketplace`) + ) DEFAULT COLLATE=utf8_general_ci;"); + + $this->db->query(" + CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "amazon_listing_report` ( + `marketplace` enum('uk','de','fr','es','it') NOT NULL, + `sku` varchar(255) NOT NULL, + `quantity` int(10) unsigned NOT NULL, + `asin` varchar(255) NOT NULL, + `price` decimal(10,4) NOT NULL, + PRIMARY KEY (`marketplace`,`sku`) + ) DEFAULT COLLATE=utf8_general_ci;"); + } + + public function uninstall() { + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_order`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_order_product`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product2`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product_link`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product_unshipped`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product_error`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_process`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product_unshipped`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_product_search`"); + $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "amazon_listing_report`"); + + $this->db->query("DELETE FROM `" . DB_PREFIX . "setting` WHERE `code` = 'openbay_amazon'"); + + $this->load->model('setting/event'); + $this->model_setting_event->deleteEventByCode('openbay_amazon_add_order'); + } + + public function patch() { + if ($this->config->get('openbay_amazon_status') == 1) { + + } + } + + public function verifyConfig($data) { + $log = new Log('amazon.log'); + + $request_xml = new SimpleXMLElement("<Request></Request>"); + $request_xml->addChild("ResponseURL", HTTP_CATALOG . 'index.php?route=extension/openbay/amazon/order'); + + $marketplace_ids = $request_xml->addChild("MarketplaceIDs"); + + foreach ($data['openbay_amazon_orders_marketplace_ids'] as $marketplace_id) { + $marketplace_ids->addChild("MarketplaceID", $marketplace_id); + } + + $response = $this->openbay->amazon->call('order/scheduleOrders', $request_xml->asXML(), false); + + libxml_use_internal_errors(true); + $response_xml = simplexml_load_string($response); + libxml_use_internal_errors(false); + + if ($response_xml && $response_xml->Status == '0') { + $log->write('Scheduling orders call was successful'); + return true; + } + + $log->write('Failed to schedule orders. Response: ' . $response); + + return false; + } + + public function saveProduct($product_id, $data_array) { + if (isset($data_array['fields']['item-price'])) { + $price = $data_array['fields']['item-price']; + } else if (isset($data_array['fields']['price'])) { + $price = $data_array['fields']['price']; + } else if (isset($data_array['fields']['StandardPrice'])) { + $price = $data_array['fields']['StandardPrice']; + } else { + $price = 0; + } + + $category = (isset($data_array['category'])) ? $data_array['category'] : ""; + $sku = (isset($data_array['fields']['sku'])) ? $data_array['fields']['sku'] : ""; + if (isset($data_array['fields']['sku'])) { + $sku = $data_array['fields']['sku']; + } else if (isset($data_array['fields']['SKU'])) { + $sku = $data_array['fields']['SKU']; + } + + $var = isset($data_array['optionVar']) ? $data_array['optionVar'] : ''; + + $marketplaces = isset($data_array['marketplace_ids']) ? serialize($data_array['marketplace_ids']) : serialize(array()); + + $data_encoded = json_encode(array('fields' => $data_array['fields'])); + + $this->db->query(" + REPLACE INTO `" . DB_PREFIX . "amazon_product` + SET `product_id` = '" . (int)$product_id . "', + `sku` = '" . $this->db->escape($sku) . "', + `category` = '" . $this->db->escape($category) . "', + `data` = '" . $this->db->escape($data_encoded) . "', + `status` = 'saved', + `insertion_id` = '', + `price` = '" . $price . "', + `var` = '" . $this->db->escape($var) . "', + `marketplaces` = '" . $this->db->escape($marketplaces) . "'"); + } + + public function deleteSaved($product_id, $var = '') { + $this->db->query("DELETE FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "'"); + } + + public function getSavedProducts() { + return $this->db->query(" + 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` + FROM `" . DB_PREFIX . "amazon_product` as `ap` + LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` + ON `ap`.`product_id` = `pd`.`product_id` + LEFT JOIN `" . DB_PREFIX . "product` as `p` + ON `ap`.`product_id` = `p`.`product_id` + WHERE `ap`.`status` = 'saved' + AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'")->rows; + } + + public function getSavedProductsData() { + return $this->db->query(" + SELECT * FROM `" . DB_PREFIX . "amazon_product` + WHERE `status` = 'saved' AND `version` = 2")->rows; + } + + public function getProduct($product_id, $var = '') { + return $this->db->query(" + SELECT * FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "' AND `version` = 2")->row; + } + + public function getProductCategory($product_id, $var = '') { + $row = $this->db->query(" + SELECT `category` FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "' AND `version` = 2")->row; + if (isset($row['category'])) { + return $row['category']; + } else { + return ""; + } + } + + public function setProductUploaded($product_id, $insertion_id, $var = '') { + $this->db->query( + "UPDATE `" . DB_PREFIX . "amazon_product` + SET `status` = 'uploaded', `insertion_id` = '" . $this->db->escape($insertion_id) . "' + WHERE `product_id` = '" . (int)$product_id . "' AND `var` = '" . $this->db->escape($var) . "' AND `version` = 2"); + } + + public function resetUploaded($insertion_id) { + $this->db->query( + "UPDATE `" . DB_PREFIX . "amazon_product` + SET `status` = 'saved', `insertion_id` = '' + WHERE `insertion_id` = '" . $this->db->escape($insertion_id) . "' AND `version` = 2"); + } + + public function getProductStatus($product_id) { + + $rows_uploaded = $this->db->query(" + SELECT COUNT(*) count + FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND status = 'uploaded'")->row; + $rows_uploaded = $rows_uploaded['count']; + + $rows_ok = $this->db->query(" + SELECT COUNT(*) count + FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND status = 'ok'")->row; + $rows_ok = $rows_ok['count']; + + $rows_error = $this->db->query(" + SELECT COUNT(*) count + FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND status = 'error'")->row; + $rows_error = $rows_error['count']; + + $rows_saved = $this->db->query(" + SELECT COUNT(*) count + FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND status = 'saved'")->row; + $rows_saved = $rows_saved['count']; + + $rows_total = $rows_uploaded + $rows_ok + $rows_error + $rows_saved; + + $links = $this->db->query(" + SELECT COUNT(*) as count + FROM `" . DB_PREFIX . "amazon_product_link` + WHERE `product_id` = '" . (int)$product_id . "'")->row; + $links = $links['count']; + + if ($rows_total === 0 && $links > 0) { + return 'linked'; + } else if ($rows_total == 0) { + return false; + } + + if ($rows_uploaded > 0) { + return 'processing'; + } + + if ($rows_uploaded == 0 && $rows_ok > 0 && $rows_error == 0) { + return 'ok'; + } + + if ($rows_saved > 0) { + return 'saved'; + } + + if ($rows_uploaded == 0 && $rows_error > 0 && $rows_ok == 0) { + $quick = $this->db->query("SELECT * FROM `" . DB_PREFIX . "amazon_product` WHERE `product_id` = " . (int)$product_id . " AND `version` = 3")->row; + + if ($quick) { + return 'error_quick'; + } else { + return 'error_advanced'; + } + } else { + return 'error_few'; + } + + return false; + } + + public function getProductErrors($product_id, $version = 2) { + if ($version == 3) { + $message_row = $this->db->query("SELECT `messages` FROM `" . DB_PREFIX . "amazon_product` WHERE `product_id` = '" . (int)$product_id . "' AND `version` = 3")->row; + + return json_decode($message_row['messages']); + } + + $result = array(); + + $insertion_rows = $this->db->query("SELECT `sku`, `insertion_id` FROM `" . DB_PREFIX . "amazon_product` WHERE `product_id` = '" . (int)$product_id . "' AND `version` = 2")->rows; + + if (!empty($insertion_rows)) { + foreach($insertion_rows as $insertion_row) { + $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; + + foreach($error_rows as $error_row) { + $result[] = $error_row; + } + } + } + return $result; + } + + public function getProductsWithErrors() { + return $this->db->query(" + SELECT `product_id`, `sku` FROM `" . DB_PREFIX . "amazon_product` + WHERE `status` = 'error' AND `version` = 2")->rows; + } + + public function deleteProduct($product_id) { + $this->db->query( + "DELETE FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "'"); + } + + public function linkProduct($amazon_sku, $product_id, $var = '') { + $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; + + if ($count['count'] == 0) { + $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) . "'"); + } + } + + public function removeProductLink($amazon_sku) { + $this->db->query( + "DELETE FROM `" . DB_PREFIX . "amazon_product_link` + WHERE `amazon_sku` = '" . $this->db->escape($amazon_sku) . "'"); + } + + public function removeAdvancedErrors($product_id) { + $product_rows = $this->db->query(" + SELECT `insertion_id` FROM `" . DB_PREFIX . "amazon_product` + WHERE `product_id` = '" . (int)$product_id . "' AND `version` = 2")->rows; + + foreach ($product_rows as $product) { + $this->db->query( + "DELETE FROM `" . DB_PREFIX . "amazon_product_error` + WHERE `insertion_id` = '" . $this->db->escape($product['insertion_id']) . "'"); + } + + $this->db->query( + "UPDATE `" . DB_PREFIX . "amazon_product` + SET `status` = 'saved', `insertion_id` = '' + WHERE `product_id` = '" . (int)$product_id . "' AND `status` = 'error' AND `version` = 2"); + } + + public function getProductLinks($product_id = 'all') { + $query = " + SELECT `apl`.`product_id`, `pd`.`name` as `product_name`, `p`.`model`, `apl`.`amazon_sku`, `p`.`sku`, `apl`.`var`, '' as `combination` + FROM `" . DB_PREFIX . "amazon_product_link` as `apl` + LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` + ON `apl`.`product_id` = `pd`.`product_id` + LEFT JOIN `" . DB_PREFIX . "product` as `p` + ON `apl`.`product_id` = `p`.`product_id`"; + + if ($product_id != 'all') { + $query .= " WHERE `apl`.`product_id` = '" . (int)$product_id . "' AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'"; + } else { + $query .= "WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'"; + } + + $product_links = $this->db->query($query)->rows; + + if ($this->openbay->addonLoad('openstock')) { + $this->load->model('extension/module/openstock'); + $this->load->model('tool/image'); + + foreach ($product_links as $key => $product_link) { + $variants = $this->model_setting_module_openstock->getVariants($product_link['product_id']); + + if (!empty($variants)) { + foreach($variants as $variant) { + if ($variant['sku'] == $product_link['var']) { + $product_links[$key]['combination'] = $variant['combination']; + break; + } + } + } + } + + return $product_links; + } else { + return $product_links; + } + } + + public function getUnlinkedProducts() { + if ($this->openbay->addonLoad('openstock')) { + $rows = $this->db->query(" + SELECT `p`.`product_id`, `p`.`model`, `p`.`sku`, `pd`.`name` as `product_name`, '' as `var`, '' as `combination`, `p`.`has_option` + FROM `" . DB_PREFIX . "product` as `p` + LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` + ON `p`.`product_id` = `pd`.`product_id` + AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'")->rows; + + $result = array(); + $this->load->model('extension/module/openstock'); + $this->load->model('tool/image'); + foreach($rows as $row) { + if ($row['has_option'] == 1) { + $stock_opts = $this->model_setting_module_openstock->getVariants($row['product_id']); + foreach($stock_opts as $opt) { + if ($this->productLinkExists($row['product_id'], $opt['sku'])) { + continue; + } + $row['combination'] = $opt['combination']; + $row['sku'] = $opt['sku']; + $result[] = $row; + } + } else { + if (!$this->productLinkExists($row['product_id'], $row['sku'])) { + $result[] = $row; + } + } + } + } else { + $result = $this->db->query(" + SELECT `p`.`product_id`, `p`.`model`, `p`.`sku`, `pd`.`name` as `product_name`, '' as `var`, '' as `combination` + FROM `" . DB_PREFIX . "product` as `p` + LEFT JOIN `" . DB_PREFIX . "product_description` as `pd` + ON `p`.`product_id` = `pd`.`product_id` + LEFT JOIN `" . DB_PREFIX . "amazon_product_link` as `apl` + ON `apl`.`product_id` = `p`.`product_id` + WHERE `apl`.`amazon_sku` IS NULL + AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'")->rows; + } + + return $result; + } + + private function productLinkExists($product_id, $var) { + $link = $this->db->query("SELECT * FROM `" . DB_PREFIX . "amazon_product_link` WHERE `product_id` = " . (int)$product_id . " AND var = '" . $this->db->escape($var) . "'")->row; + + if (empty($link)) { + return false; + } else { + return true; + } + } + + public function getOrderStatusString($order_id) { + $row = $this->db->query(" + SELECT `s`.`key` + FROM `" . DB_PREFIX . "order` `o` + JOIN `" . DB_PREFIX . "setting` `s` ON `o`.`order_id` = " . (int)$order_id . " AND `s`.`value` = `o`.`order_status_id` + WHERE `s`.`key` = 'openbay_amazon_order_status_shipped' OR `s`.`key` = 'openbay_amazon_order_status_canceled' + LIMIT 1")->row; + + if (!isset($row['key']) || empty($row['key'])) { + return null; + } + + $key = $row['key']; + + switch ($key) { + case 'openbay_amazon_order_status_shipped': + $order_status = 'shipped'; + break; + case 'openbay_amazon_order_status_canceled': + $order_status = 'canceled'; + break; + + default: + $order_status = null; + break; + } + + return $order_status; + } + + public function updateAmazonOrderTracking($order_id, $courier_id, $courier_from_list, $tracking_no) { + $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 . ""); + } + + public function getAmazonOrderId($order_id) { + $row = $this->db->query(" + SELECT `amazon_order_id` + FROM `" . DB_PREFIX . "amazon_order` + WHERE `order_id` = " . (int)$order_id . " + LIMIT 1")->row; + + if (isset($row['amazon_order_id']) && !empty($row['amazon_order_id'])) { + return $row['amazon_order_id']; + } + + return null; + } + + public function getAmazonOrderedProducts($order_id) { + return $this->db->query(" + SELECT `aop`.`amazon_order_item_id`, `op`.`quantity` + FROM `" . DB_PREFIX . "amazon_order_product` `aop` + JOIN `" . DB_PREFIX . "order_product` `op` ON `op`.`order_product_id` = `aop`.`order_product_id` + AND `op`.`order_id` = " . (int)$order_id)->rows; + } + + public function getProductQuantity($product_id, $var = '') { + $result = null; + + if ($var !== '' && $this->openbay->addonLoad('openstock')) { + $this->load->model('tool/image'); + $this->load->model('extension/module/openstock'); + $option_stocks = $this->model_setting_module_openstock->getVariants($product_id); + + $option = null; + foreach ($option_stocks as $option_iterator) { + if ($option_iterator['sku'] === $var) { + $option = $option_iterator; + break; + } + } + + if ($option != null) { + $result = $option['stock']; + } + } else { + $this->load->model('catalog/product'); + $product_info = $this->model_catalog_product->getProduct($product_id); + + if (isset($product_info['quantity'])) { + $result = $product_info['quantity']; + } + } + return $result; + } + + public function getProductSearchTotal($data = array()) { + $sql = " + SELECT COUNT(*) AS product_total + FROM " . DB_PREFIX . "product p + LEFT JOIN " . DB_PREFIX . "amazon_product_search aps ON p.product_id = aps.product_id AND aps.marketplace = '" . $this->db->escape($data['filter_marketplace']) . "' + LEFT JOIN " . DB_PREFIX . "amazon_product_link apl ON p.product_id = apl.product_id + LEFT JOIN " . DB_PREFIX . "amazon_product ap ON p.product_id = ap.product_id + WHERE apl.product_id IS NULL AND ap.product_id IS NULL "; + + if (!empty($data['status'])) { + $sql .= " AND aps.status = '" . $this->db->escape($data['status']) . "'"; + } + + return $this->db->query($sql)->row['product_total']; + } + + public function getProductSearch($data = array()) { + $sql = " + SELECT p.product_id, marketplace, aps.status, aps.data, aps.matches + FROM " . DB_PREFIX . "product p + LEFT JOIN " . DB_PREFIX . "amazon_product_search aps ON p.product_id = aps.product_id AND aps.marketplace = '" . $this->db->escape($data['filter_marketplace']) . "' + LEFT JOIN " . DB_PREFIX . "amazon_product_link apl ON p.product_id = apl.product_id + LEFT JOIN " . DB_PREFIX . "amazon_product ap ON p.product_id = ap.product_id + WHERE apl.product_id IS NULL AND ap.product_id IS NULL "; + + if (!empty($data['status'])) { + $sql .= " AND aps.status = '" . $this->db->escape($data['status']) . "'"; + } + + $sql .= " LIMIT " . (int)$data['start'] . ", " . (int)$data['limit']; + + $results = array(); + + $rows = $this->db->query($sql)->rows; + + foreach ($rows as $row) { + $results[] = array( + 'product_id' => $row['product_id'], + 'marketplace' => $row['marketplace'], + 'status' => $row['status'], + 'matches' => $row['matches'], + 'data' => json_decode($row['data'], 1), + ); + } + + return $results; + } + + public function updateAmazonSkusQuantities($skus) { + $sku_array = array(); + + foreach ($skus as $sku) { + $sku_array[] = "'" . $this->db->escape($sku) . "'"; + } + + if ($this->openbay->addonLoad('openstock')) { + $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; + } else { + $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; + } + + $return = array(); + + foreach ($rows as $row) { + $return[$row['amazon_sku']] = $row['quantity']; + } + + $this->openbay->amazon->updateQuantities($return); + } + + public function deleteListingReports($marketplace) { + $this->db->query("DELETE FROM " . DB_PREFIX . "amazon_listing_report WHERE marketplace = '" . $this->db->escape($marketplace) . "'"); + } + + public function getTotalUnlinkedItemsFromReport($marketplace) { + if ($this->openbay->addonLoad('openstock')) { + + $sql = " + 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 + FROM " . DB_PREFIX . "amazon_listing_report alr + LEFT JOIN ( + 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 + FROM " . DB_PREFIX . "product p + LEFT JOIN " . DB_PREFIX . "product_option_variant por USING(product_id) + ) AS oc_sku ON alr.sku = oc_sku.sku + 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) + 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') . " + WHERE apl.product_id IS NULL AND alr.marketplace = '" . $this->db->escape($marketplace) . "' + ORDER BY alr.sku + "; + + $result = $this->db->query($sql); + } else { + $result = $this->db->query(" + 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 + FROM " . DB_PREFIX . "amazon_listing_report alr + LEFT JOIN ( + SELECT p.product_id, p.sku, NULL AS 'var', p.quantity + FROM " . DB_PREFIX . "product p + ) AS oc_sku ON alr.sku = oc_sku.sku + 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) + 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') . " + WHERE apl.product_id IS NULL AND alr.marketplace = '" . $this->db->escape($marketplace) . "' + ORDER BY alr.sku + "); + } + + return (int)$result->num_rows; + } + + public function getUnlinkedItemsFromReport($marketplace, $limit = 100, $page = 1) { + $start = $limit * ($page - 1); + + if ($this->openbay->addonLoad('openstock')) { + $this->load->model('extension/module/openstock'); + $rows = $this->db->query(" + 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 + FROM " . DB_PREFIX . "amazon_listing_report alr + LEFT JOIN ( + 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 + FROM " . DB_PREFIX . "product p + LEFT JOIN " . DB_PREFIX . "product_option_variant por USING(product_id) + ) AS oc_sku ON alr.sku = oc_sku.sku + 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) + 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') . " + WHERE apl.product_id IS NULL AND alr.marketplace = '" . $this->db->escape($marketplace) . "' + ORDER BY alr.sku + LIMIT " . (int)$start . "," . (int)$limit)->rows; + } else { + $rows = $this->db->query(" + 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 + FROM " . DB_PREFIX . "amazon_listing_report alr + LEFT JOIN ( + SELECT p.product_id, p.sku, NULL AS 'var', p.quantity + FROM " . DB_PREFIX . "product p + ) AS oc_sku ON alr.sku = oc_sku.sku + 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) + 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') . " + WHERE apl.product_id IS NULL AND alr.marketplace = '" . $this->db->escape($marketplace) . "' + ORDER BY alr.sku + LIMIT " . (int)$start . "," . (int)$limit)->rows; + } + + $products = array(); + + foreach ($rows as $row) { + $combinations = array(); + + if (isset($row['pov_id']) && !empty($row['pov_id'])) { + $variants = (isset($row['pov_id']) ? $this->model_setting_module_openstock->getVariant($row['pov_id']) : ''); + + foreach ($variants as $variant) { + $combinations[] = $variant['option_value_name']; + } + } + + $products[] = array( + 'product_id' => $row['product_id'], + 'name' => $row['name'], + 'sku' => $row['sku'], + 'var' => $row['var'], + 'quantity' => $row['quantity'], + 'amazon_sku' => $row['amazon_sku'], + 'amazon_quantity' => $row['amazon_quantity'], + 'amazon_price' => number_format($row['amazon_price'], 2, '.', ''), + 'asin' => $row['asin'], + 'combination' => implode(' > ', $combinations), + ); + } + + return $products; + } + + public function getAsinLink($asin, $marketplace) { + switch ($marketplace) { + case 'uk': + $link = 'https://www.amazon.co.uk/dp/' . $asin . '/'; + break; + case 'de': + $link = 'https://www.amazon.de/dp/' . $asin . '/'; + break; + case 'fr': + $link = 'https://www.amazon.fr/dp/' . $asin . '/'; + break; + case 'it': + $link = 'https://www.amazon.it/dp/' . $asin . '/'; + break; + case 'es': + $link = 'https://www.amazon.es/dp/' . $asin . '/'; + break; + } + + return $link; + } +} |