OpenCart 🇺🇦

if ($this->openbay->addonLoad('openstock')) {
	$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,
		  (
			SELECT GROUP_CONCAT(ovd.name ORDER BY o.sort_order SEPARATOR ' > ')
			FROM " . DB_PREFIX . "product_option_value pov
			JOIN " . DB_PREFIX . "option_value_description ovd ON ovd.option_value_id = pov.option_value_id AND ovd.language_id = " . (int)$this->config->get('config_language_id') . "
			JOIN `" . DB_PREFIX . "option` o ON o.option_id = pov.option_id
			WHERE oc_sku.var LIKE CONCAT('%:', pov.product_option_value_id ,':%') OR oc_sku.var LIKE CONCAT(pov.product_option_value_id ,':%')
			  OR oc_sku.var LIKE CONCAT('%:', pov.product_option_value_id) OR oc_sku.var LIKE pov.product_option_value_id
		  ) AS 'combination'
		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.var) AS 'var', IF(por.product_id IS NULL, p.quantity, por.stock) AS 'quantity'
		  FROM " . DB_PREFIX . "product p
		  LEFT JOIN " . DB_PREFIX . "product_option_relation 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
	");
} 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, '' AS combination
		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;