public function getSuggest() { if (Tools::getValue('token') != Configuration::get('EBAY_SECURITY_TOKEN')) { return $this->l('You are not logged in'); } // Loading categories $ebay = new eBayRequest(); $categoryConfigList = array(); $categoryConfigListTmp = Db::getInstance()->executeS('SELECT * FROM `' . _DB_PREFIX_ . 'ebay_category_configuration`'); foreach ($categoryConfigListTmp as $c) { $categoryConfigList[$c['id_category']] = $c; } // Get categories $categoryList = Db::getInstance()->executeS('SELECT `id_category`, `name` FROM `' . _DB_PREFIX_ . 'category_lang` WHERE `id_lang` = ' . (int) Tools::getValue('id_lang') . ' ' . (_PS_VERSION_ >= '1.5' ? $this->context->shop->addSqlRestrictionOnLang() : '')); // GET One Product by category $SQL = ' SELECT pl.`name`, pl.`description`, p.`id_category_default` FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = ' . (int) Tools::getValue('id_lang') . ' ' . (_PS_VERSION_ >= '1.5' ? $this->context->shop->addSqlRestrictionOnLang('pl') : '') . ') GROUP BY p.`id_category_default` '; $products = Db::getInstance()->executeS($SQL); // Create array $productTest = array(); foreach ($products as $product) { $productTest[$product['id_category_default']] = array('description' => $product['description'], 'name' => $product['name']); } // cats ref $refCats = Db::getInstance()->executeS('SELECT `id_ebay_category`, `id_category_ref` FROM `' . _DB_PREFIX_ . 'ebay_category` '); if (is_array($refCats) && sizeof($refCats) && $refCats) { foreach ($refCats as $cat) { $refCategories[$cat['id_category_ref']] = $cat['id_ebay_category']; } } else { return; } $i = 0; $SQL = "REPLACE INTO `" . _DB_PREFIX_ . "ebay_category_configuration` (`id_country`, `id_ebay_category`, `id_category`, `percent`, `date_add`, `date_upd`) VALUES "; if (is_array($categoryList) && sizeof($categoryList) && $categoryList) { // while categoryList foreach ($categoryList as $k => $c) { if (!isset($categoryConfigList[$c['id_category']])) { if (isset($productTest[$c['id_category']]) && !empty($productTest[$c['id_category']])) { $id_category_ref_suggested = $ebay->getSuggestedCategories($c['name'] . ' ' . $productTest[$c['id_category']]['name']); $id_ebay_category_suggested = isset($refCategories[$id_category_ref_suggested]) ? $refCategories[$id_category_ref_suggested] : 1; if ((int) $id_ebay_category_suggested > 0) { if ($i > 0) { $SQL .= ', '; } $SQL .= '(8, ' . (int) $id_ebay_category_suggested . ', ' . (int) $c['id_category'] . ', 0, NOW(), NOW()) '; $i++; } } } } if ($i > 0) { Db::getInstance()->execute($SQL); } return $this->l('Settings updated'); } }
private function _postProcessCategory() { // Init Var $date = date('Y-m-d H:i:s'); $services = Tools::getValue('service'); if (Tools::getValue('action') == 'suggestCategories') { // Loading categories $ebay = new eBayRequest(); $categoryConfigList = array(); $categoryConfigListTmp = Db::getInstance()->executeS('SELECT * FROM `' . _DB_PREFIX_ . 'ebay_category_configuration`'); foreach ($categoryConfigListTmp as $c) { $categoryConfigList[$c['id_category']] = $c; } $categoryList = Db::getInstance()->executeS('SELECT `id_category`, `name` FROM `' . _DB_PREFIX_ . 'category_lang` WHERE `id_lang` = ' . (int) $this->id_lang . ' ' . (_PS_VERSION_ >= '1.5' ? $this->context->shop->addSqlRestrictionOnLang('cl') : '')); foreach ($categoryList as $k => $c) { if (!isset($categoryConfigList[$c['id_category']])) { $productTest = Db::getInstance()->getRow(' SELECT pl.`name`, pl.`description` FROM `' . _DB_PREFIX_ . 'product` p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = ' . (int) $this->id_lang . ' ' . (_PS_VERSION_ >= '1.5' ? $this->context->shop->addSqlRestrictionOnLang('pl') : '') . ') WHERE `id_category_default` = ' . (int) $c['id_category']); $id_category_ref_suggested = $ebay->getSuggestedCategories($c['name'] . ' ' . $productTest['name']); $id_ebay_category_suggested = Db::getInstance()->getValue('SELECT `id_ebay_category` FROM `' . _DB_PREFIX_ . 'ebay_category` WHERE `id_category_ref` = ' . (int) $id_category_ref_suggested); if ((int) $id_ebay_category_suggested > 0) { Db::getInstance()->autoExecute(_DB_PREFIX_ . 'ebay_category_configuration', array('id_country' => 8, 'id_ebay_category' => (int) $id_ebay_category_suggested, 'id_category' => (int) $c['id_category'], 'percent' => 0, 'date_add' => pSQL($date), 'date_upd' => pSQL($date)), 'INSERT'); } } } $this->_html .= $this->displayConfirmation($this->l('Settings updated')); return true; } // Sort post datas $postValue = array(); foreach ($_POST as $k => $v) { if (strlen($k) > 8 && substr($k, 0, 8) == 'category') { $postValue[substr($k, 8, strlen($k) - 8)]['id_ebay_category'] = $v; } if (strlen($k) > 7 && substr($k, 0, 7) == 'percent') { $postValue[substr($k, 7, strlen($k) - 7)]['percent'] = $v; } } // Insert and update configuration foreach ($postValue as $id_category => $tab) { $arraySQL = array(); $date = date('Y-m-d H:i:s'); if ($tab['id_ebay_category']) { $arraySQL = array('id_country' => 8, 'id_ebay_category' => (int) $tab['id_ebay_category'], 'id_category' => (int) $id_category, 'percent' => pSQL($tab['percent']), 'date_upd' => pSQL($date)); } $id_ebay_category_configuration = Db::getInstance()->getValue('SELECT `id_ebay_category_configuration` FROM `' . _DB_PREFIX_ . 'ebay_category_configuration` WHERE `id_category` = ' . (int) $id_category); if ($id_ebay_category_configuration > 0) { if ($arraySQL) { Db::getInstance()->autoExecute(_DB_PREFIX_ . 'ebay_category_configuration', $arraySQL, 'UPDATE', '`id_category` = ' . (int) $id_category); } else { Db::getInstance()->execute('DELETE FROM `' . _DB_PREFIX_ . 'ebay_category_configuration` WHERE `id_category` = ' . (int) $id_category); } } elseif ($arraySQL) { $arraySQL['date_add'] = $date; Db::getInstance()->autoExecute(_DB_PREFIX_ . 'ebay_category_configuration', $arraySQL, 'INSERT'); } } $this->_html .= $this->displayConfirmation($this->l('Settings updated')); }