'cs', 'web' => null, 'queue' => null, 'rabbit_server' => 'rabbitmq.elsvc.net', ]; public static $ABRA_UIDS = [ 'StoreCard' => 'product', 'ReceivedOrder' => 'order', 'ReceivedOrderRow' => 'order', 'Category' => 'ignore', 'Brand' => 'producer', 'SeriesBrand' => 'serie', 'Parameter' => 'parameters', 'ParameterValue' => 'parameters_values', 'ParameterAssortmentGroup' => 'parameters_sections', 'ParameterStoreCard' => 'parameters_products', 'AssortmentGroup' => 'ignore', 'ComplementModel' => 'complement_model', 'ComplementTypeSC' => 'complement_type', 'Model' => 'model', 'Damage' => 'damage', 'Picture' => 'picture', ]; public static $PRODUCT_FIELDS = [ // Base 'Code' => 'code', 'StoreMenuItem_ID' => 'ignore', 'Quantity' => 'in_store', 'X_LimitWEB' => 'elnino_in_store_decrement', 'X_WEBGroup' => 'ignore', 'VOC_PLN' => 'price', 'ID' => 'delete', 'EAN' => 'ean', 'X_VATRate' => 'vat', 'Brand_ID' => 'producer_id', 'SeriesBrand_ID' => 'id_serie', 'ComplementModel_ID' => 'id_complement_model', // Popis 'X_Name_Mark' => 'product_line', // Parametr Produktová řada 'Annotation' => 'short_descr', 'Description' => 'long_descr', // Flagy 'X_Gift' => 'flag_G', // Flag Dárek 'X_Action' => 'flag_A', // Flag Akce 'X_Sample' => 'ignore', // Flag Vzorek 'X_Tester' => 'flag_T', // Flag Tester 'X_Spatter' => 'ignore', // Flag Odstrik 'X_Set' => 'flag_S', // Flag Set 'Quantity_ToDelivery' => 'to_deliver', // Flag Na ceste + parametry 'X_VOEU' => 'region', // Parametry 'X_SortCosmeticsPL' => 'category', // Kategorie produktu + parametr 'X_Size' => 'size', // Parametr Velikost 'Weight' => 'weight', // Parametr Váha 'X_Damage' => 'damage', // Parameter Poškození 'X_sexB' => 'sex', // Parametr Pohlaví 'X_Constitution_PL' => 'constitution', // Parametr Složení 'X_ShadePL' => 'shade', // Parametr Odstín // Ignores 'StoreAssortmentGroup_ID' => 'ignore', 'UnitSize_ID' => 'ignore', 'SETtype' => 'ignore', 'Name2' => 'ignore', // '' => 'ignore', 'X_SETtype' => 'ignore', 'X_SortCosmetics' => 'ignore', 'X_SortCosmeticsEN' => 'ignore', 'X_LimitX_SortCosmeticsENWEB' => 'ignore', 'X_Note2EN' => 'ignore', 'X_Note2' => 'ignore', 'X_ShadeEN' => 'ignore', 'X_Shade' => 'ignore', 'X_Constitution_EN' => 'ignore', 'X_Constitution' => 'ignore', 'X_Constitution_DE' => 'ignore', 'X_ShadeDE' => 'ignore', 'X_Note2DE' => 'ignore', 'X_EU' => 'ignore', 'X_IDpar' => 'ignore', 'X_WEB_Transfer' => 'figure', 'X_WEB' => 'ignore', 'Depth' => 'ignore', 'Height' => 'ignore', 'Width' => 'ignore', 'Size' => 'ignore', 'X_Damage_ID' => 'ignore', // Prices 'MOC_EUR_GR' => 'ignore', 'MOC_EUR_PE' => 'ignore', 'VOC_CZK' => 'ignore', 'MOC_CZK_KZ' => 'ignore', 'MOC_CZK_PC' => 'ignore', 'MOC_CZK_PE' => 'ignore', 'VOC_EUR' => 'ignore', 'MOC_CZK_SP' => 'ignore', 'MOC_CZK_PV' => 'ignore', 'MOC_CHF' => 'ignore', 'MOC_CHF_PA' => 'ignore', 'MOC_BGN' => 'ignore', 'MOC_EUR_UA' => 'ignore', 'NAK_CZK_ES' => 'ignore', ]; public static $PICTURE_FIELDS = [ 'StoreCard_ID' => 'id_product', 'PictureType' => 'type', 'ONDELETE' => 'delete', ]; public static $SECTION_FIELDS = [ 'Name' => 'name', 'Code' => 'ignore', 'Parent_ID' => 'parent_id', 'Parent2_ID' => 'ignore', 'NamePlural' => 'ignore', // Disable plural sync 'PosIndex' => 'position', 'AlternativeName' => 'plural', 'X_Description' => 'descr', 'X_Activate' => 'figure', 'Replacement_ID' => 'replacement', 'ReplacementType' => 'ignore', 'ONDELETE' => 'delete', ]; public static $ORDER_FIELDS = [ 'Status_Confirmed' => 'ignore', 'Status_Closed' => 'ignore', 'Status_Storno' => 'ignore', 'Status_Authorization' => 'ignore', 'UnitPriceWithoutVAT' => 'price', 'Quantity' => 'quantity', 'Status' => 'status', 'Note_Expedition' => 'note_expedition', 'PackageNumber' => 'ignore', ]; public static $PRODUCER_FIELDS = [ 'Text' => 'name', 'Posindex' => 'position', 'X_Description' => 'descr', 'X_Activate' => 'active', 'ONDELETE' => 'delete', 'Parent_ID' => 'ignore', 'Replacement_ID' => 'replacement', ]; public static $SERIES_FIELDS = [ 'Name' => 'name', 'Brand_ID' => 'producer', 'Sex' => 'sex', 'X_Description' => 'descr', 'X_Activate' => 'active', 'Replacement_ID' => 'ignore', 'ONDELETE' => 'delete', ]; public static $PARAMETERS_FIELDS = [ 'Code' => 'id', 'Name' => 'name', 'ONDELETE' => 'delete', ]; public static $PARAMETERS_VALUES_FIELDS = [ 'Parameter_ID' => 'id_parameter', 'Name' => 'name', 'Position' => 'position', 'CDN_ID' => 'ignore', 'ONDELETE' => 'delete', ]; public static $PARAMETERS_PRODUCTS_FIELDS = [ 'StoreCard_ID' => 'id_product', 'ParameterValue_ID' => 'param_value_id', 'ONDELETE' => 'delete', ]; public static $PARAMETERS_SECTIONS_FIELDS = [ 'Parameter_ID' => 'id_parameter', 'StoreAssortmentGroup_ID' => 'id_section', 'ONDELETE' => 'delete', ]; public static $COMPLEMENT_MODEL_FIELDS = [ 'Name' => 'name', 'Code' => 'ignore', 'ONDELETE' => 'delete', ]; public static $COMPLEMENT_TYPE_FIELDS = [ 'Name' => 'name', 'Code' => 'ignore', 'ONDELETE' => 'delete', ]; public static $DAMAGE_FIELDS = [ 'Code' => 'code', 'Name' => 'name', 'ONDELETE' => 'delete', ]; public static $UNITSIZE_FIELDS = [ 'Code' => 'code', 'Name' => 'name', 'ONDELETE' => 'delete', ]; public static $MODEL_FIELDS = [ 'SeriesBrand_ID' => 'id_serie', 'StoreAssortmentGroup_ID' => 'id_category', 'ComplementModel_ID' => 'id_complement_model', 'X_Description' => 'descr', 'X_SEO_URL' => 'ignore', 'X_Activate' => 'ignore', 'Replacement_ID' => 'replacement', 'ONDELETE' => 'delete', ]; public static $GET_ALL_TYPES = [ 'Brand' => [ 'batch' => 100, ], 'SeriesBrand' => [ 'batch' => 500, ], 'AssortmentGroup' => [ 'batch' => 500, ], 'Parameter' => [ 'batch' => 1000, ], 'ParameterValue' => [ 'batch' => 1000, ], 'ParameterAssortmentGroup' => [ 'batch' => 1000, ], 'Scent' => [ 'batch' => 1000, ], 'ComplementModel' => [ 'batch' => 1000, ], 'ComplementTypeSC' => [ 'batch' => 1000, ], 'Model' => [ 'batch' => 500, ], 'ElementScentModel' => [ 'batch' => 1000, ], 'Damage' => [ 'batch' => 1000, ], 'UnitSize' => [ 'batch' => 1000, ], 'StoreCard' => [ 'batch' => 100, ], 'ParameterStoreCard' => [ 'batch' => 1000, ], 'Picture' => [ 'batch' => 1000, ], 'SecondaryAssortmentGroup' => [ 'batch' => 1000, ], ]; protected $lang; protected $site; public static $MAX_DISCOUNT = 85; /** * [ABRA_UID => [Array of fields to sync]]. */ public static $TRANSLATION_FIELDS = [ 'AssortmentGroup' => [ 'Name', 'X_Description', ], 'Damage' => [ 'Name', ], 'Brand' => [ // 'Text', - same in all languages 'X_Description', ], 'UnitSize' => [ 'Code', 'Name', ], 'ComplementTypeSC' => [ 'Name', ], 'Parameter' => [ 'Name', ], 'ParameterValue' => [ 'Name', ], 'SeriesBrand' => [ 'X_Description', ], 'Model' => [ 'X_Description', ], ]; public static $SECONDARY_ASSORTMENT_GROUP_FIELDS; public static $SCENT_FIELDS; public static $ENABLED_STORES = [1, 6, 8]; protected $languageContext; public function __construct($settings = null) { ini_set('memory_limit', '512M'); parent::__construct($settings); $this->languageContext = ServiceContainer::getService(\KupShop\KupShopBundle\Context\LanguageContext::class); } public function getOneDataBlock($type, $index, $batch) { $selection = null; $filter = null; if (isset($_GET['filter']) && !empty($_GET['filter'])) { $filter = explode(',', $_GET['filter']); $filter = array_map(function ($value) { return "'".$value."'"; }, $filter); $filter = join(',', $filter); $selection = 'SELECTION'; } return $this->client->weGetObjectType($type, $index, $batch, $selection, $filter, $this->settings['web'], $this->settings['lang']); } protected $translations = [ 'Sets' => 'Kazety', 'Testers' => 'Testery', 'pcs' => 'ks', 'Shade' => 'Odstín', 'tester' => 'tester', 'Sections' => 'Sortiment', ]; protected $batch_size = 100; public function resetProductCache() { return; } public function getOrderStatus(Order $order) { $result = $this->client->__call('weGetAttribute', [ $this->settings['web'], 'ReceivedOrders', 'ExternalNumber', $order->order_no, 'X_Status', 'integer', ]); return $result; } // Sync functions protected $rabbitConnection; protected $rabbitChannel; public function syncTranslations($type, $params) { if (!isset(static::$TRANSLATION_FIELDS[$type]) && $type != 'all') { throw new RuntimeException(sprintf('Translations field mapping is not defined for UID \'%s\'!', $type)); } if ($type == 'all') { $types = array_keys(static::$TRANSLATION_FIELDS); } else { $types = [$type]; } // 76940 - should have translations foreach ($this->languageContext->getSupported() as $language) { // do not import default language if ($language->getId() != $this->languageContext->getDefaultId()) { // option to select language to sync if (isset($_GET['language']) && $language->getId() != $_GET['language']) { continue; } echo "
Starting language {$language->getId()}
"; flush(); // set language $this->lang = $language->getId(); $this->settings['lang'] = $language->getId(); foreach ($types as $syncType) { // ugly filter set if ($filter = $this->getTranslationsFilter($syncType)) { $_GET['filter'] = $filter; } // run sync $this->getAllData($syncType, $params); } echo "
Ended language {$language->getId()}
"; flush(); } } } private function getTranslationsFilter($type) { if (isset(static::$TRANSLATION_FIELDS[$type])) { $filter = []; foreach (static::$TRANSLATION_FIELDS[$type] as $filterField) { $filter[] = $filterField; } $filter[] = 'LastID'; if (!empty($filter)) { return join(',', $filter); } } return null; } public function saveTranslationObject($translation_class, array $idArray, array $values) { $idArray = array_merge(['id_language' => $this->lang], $idArray); $translation_service = ServiceContainer::getService($translation_class); $translation_service->saveSingleObject($idArray['id_language'], $idArray['id_object'], $values); } public function isTranslationsSync() { if (!findModule(Modules::TRANSLATIONS)) { return false; } return (bool) ((isset($_GET['sync_translations']) || ($this->languageContext->getDefaultId() != $this->lang)) && $this->lang != ''); } public function translationFieldSupported($field) { if (isset(static::$TRANSLATION_FIELDS[$this->type]) && in_array($field, static::$TRANSLATION_FIELDS[$this->type])) { return true; } return false; } public function syncProducts() { $startTime = microtime(true); try { if (!$this->rabbitChannel) { // Connect $this->rabbitConnection = new PhpAmqpLib\Connection\AMQPStreamConnection($this->settings['rabbit_server'], 5672, 'wpj', 'SbyAMozJ03tPo5EA8rVU', 'main'); $this->rabbitChannel = $this->rabbitConnection->channel(); $this->rabbitChannel->basic_qos(null, 200, null); $this->rabbitChannel->basic_consume($this->settings['queue'], '', false, false, true, false, [$this, 'process_message']); } while (count($this->rabbitChannel->callbacks) && (microtime(true) - $startTime) < $this->syncTimeout) { $this->rabbitChannel->wait(null, false, 3); } var_dump('Messages still pending'); // Mark more messages pending return true; } catch (PhpAmqpLib\Exception\AMQPTimeoutException $e) { var_dump('No more messages'); } catch (Exception $e) { $this->log(['syncProducts exception' => $e->getMessage(), 'queue' => $this->settings['queue']]); // Clear try { $this->rabbitChannel->close(); } catch (Exception $e) { } try { $this->rabbitConnection->close(); } catch (Exception $e) { } $this->rabbitConnection = null; $this->rabbitChannel = null; throw $e; } } /** * @param AMQPMessage $msg * * @throws Exception */ public function process_message($msg) { $type = $msg->get_properties()['type'] ?? 'abra'; if ($type === 'claims.v2') { $type = 'claims'; // Elnino začalo posílat type=claims.v2 místo claims, a pak se diví, že se jim neposílají emaily, omgwtf } $body = json_decode($msg->body); // $this->log(['change' => $body->data, 'queue' => $this->settings['queue']]); try { // ignore unknown message types if (method_exists($this, "process_message_{$type}")) { $this->{"process_message_{$type}"}($body); } } catch (Exception $e) { $this->log(['exception' => $e->getMessage(), 'queue' => $this->settings['queue']]); // Cancel receiving messages $msg->delivery_info['channel']->basic_nack($msg->delivery_info['delivery_tag'], false, true); $msg->delivery_info['channel']->basic_cancel($msg->delivery_info['delivery_tag']); throw $e; } // Skip ACK if in development mode if (!isLocalDevelopment()) { $logAck = array_search($this->settings['web'], ['kosmetika-zdravi.cz', 'europarfemy.cz']) !== false; if ($logAck) { $this->log(['msg ack' => json_encode($body), 'queue' => $this->settings['queue']]); } $msg->delivery_info['channel']->basic_ack($msg->delivery_info['delivery_tag']); } } public function process_message_abra($body) { $this->processChange($body->data); } public function process_message_claims($data) { if (!$data) { var_dump('Claims empty'); return; } if (($data->shop ?? 'nic') != ($this->settings['claims'] ?? 'neco')) { var_dump('Claim not for us - '.$data->shop); return; } /** @var ReturnReclamationEmail $email */ $email = ServiceContainer::getService(ReturnReclamationEmail::class); $email->setData($data); $email->sendEmail($email->getEmail()); } public function processChange($change, $index = null) { $change = explode($this->separator, $change); $last_change = $change[3]; if (empty($change[1])) { var_dump('Prázdný kód: '.print_r($change, true)); return $last_change; } if (empty(static::$ABRA_UIDS[$change[1]])) { var_dump('Nepodporovaný CLSID typ změny: '.print_r($change, true)); return $last_change; } $type = static::$ABRA_UIDS[$change[1]]; $this->type = $change[1]; $method = "sync_{$type}"; $this->lang = strtolower(trim($change[7] ?? 'all')); $this->site = strtolower(trim($change[6] ?? 'all')); if ($this->lang == 'all') { $this->lang = ''; } if ($this->site == 'all') { $this->site = ''; } // Skip if change not for us if (!$this->isMessageForUs($change[3], $change[2], $change[4], $change[5])) { // $this->log(['Not for us' => $change[0]]); return $last_change; } // Check for run-away transaction if (sqlGetConnection()->getTransactionNestingLevel() > 0) { throw new Exception('Transaction level non-zero! '.sqlGetConnection()->getTransactionNestingLevel()); } // if ($index && $level > 1) // $last_change = $index; if ($change[2] !== 'LastID') { $this->$method($change[3], $change[2], $change[4], $change[5]); } return $last_change; } public function isMessageForUs($code, $field, $value, $value2) { $messageLevel = $this->getMessageLevel(); if (is_null($messageLevel)) { return false; } return true; } public function sync_product($code, $field, $value, $value2 = null) { if (!isset(static::$PRODUCT_FIELDS[$field])) { var_dump('Nedefinovaná položka synchronizace:', $field, $value, $value2); return; } if (empty($code)) { var_dump('Empty code', $field, $value, $value2); return; } if ($code[0] == '0') { var_dump('Nulový kód '.$code); return; } // Preskocit Vialky if (($code == '999998' || $code == '999999') && $field === 'X_Activate') { return; } $id = $this->getMappingFromAbra('product', $code); if (!$id) { // if ($column == 'price' || $column == 'in_store') // return; // Create Product $vat_id = null; getVat($vat_id); $this->insertSQL('products', [ 'id' => $code, 'code' => $code, 'title' => $code, 'vat' => $vat_id, 'figure' => 'Y', 'campaign' => 'N', 'date_added' => date('Y-m-d H:i:s'), ]); // maxscale tady zlobi. Kdyz mu nasetuju IDcko natvrdo, nevrati mi ho v sqlInsertId() $id = $code; // Add "Novinka - datum" parametr try { $data = ['id_product' => $id, 'id_parameter' => 35, 'value_float' => time()]; $this->insertSQL('parameters_products', $data); } catch (Exception $e) { } $this->setMappingFromAbra('product', $id, $code); } $this->sync_products_switch($code, $field, $value, $value2, $id); } public function sync_products_switch($code, $field, $value, $value2, $id) { $value = trim($value); $column = static::$PRODUCT_FIELDS[$field]; switch ($column) { case 'id_category': $id_section = $this->getMappingFromAbra('section', $value); if (!$id_section) { if ($value > 0) { echo 'Unknown section ID: '.$value; } // throw new Exception('Unknown section ID: '.$value); return; } $this->deleteSQL('products_in_sections', ['id_product' => $id]); sqlQuery('REPLACE INTO products_in_sections (id_product, id_section) VALUES (:id_product, :id_section)', ['id_product' => $id, 'id_section' => $id_section]); return; case 'category_param': // Create parameter $data = ['id_product' => $id, 'id_parameter' => 28]; $this->deleteSQL('parameters_products', $data); $data['value_list'] = $this->findParameterValue(28, $value); $this->insertSQL('parameters_products', $data); $this->scheduleProductTitleUpdate($id); return; case 'unit': if ($value != 'ks') { echo 'Unknown unit: '.$value; // throw new Exception('Unknown unit: '.$value); } return; case 'price': $value = $this->preparePriceFromAbra($value); $actual = returnSQLResult('SELECT price FROM products WHERE id=:id', ['id' => $id]); if ($value != $actual && $actual > 0) { $diff = floor((1 - ($value / $actual)) * 100); if ($diff > 5 && $diff <= $this::$MAX_DISCOUNT) { $data = ['id_product' => $id, 'id_parameter' => 34]; $this->deleteSQL('parameters_products', $data); $data = ['id_product' => $id, 'id_parameter' => 36]; $this->deleteSQL('parameters_products', $data); // Oznacit jako 'Zlevneno' $data = ['id_product' => $id, 'id_parameter' => 34]; $data['value_float'] = $diff; $this->insertSQL('parameters_products', $data); // Oznacit jako 'Zlevneno - datum' $data = ['id_product' => $id, 'id_parameter' => 36]; $data['value_float'] = time(); $this->insertSQL('parameters_products', $data); $data = ['id_product' => $id, 'flag' => 'Z']; $this->updateProductFlag($data, true); } elseif ($diff < -1) { $data = ['id_product' => $id, 'flag' => 'Z']; $this->updateProductFlag($data, false); } } break; case 'producer': $value = $this->findProducer($value); break; case 'producer_id': if ($value == $this->abraNull) { $id_producer = null; } else { $id_producer = $this->getMappingFromAbra('producer', $value); } $this->updateSQL('products', ['producer' => $id_producer], ['id' => $id]); $this->scheduleProductTitleUpdate($id); return; case 'figure': $msgLevel = $this->getMessageLevel(); $productLevel = returnSQLResult('SELECT figure_level FROM products WHERE id=:id', ['id' => $id]); if ($msgLevel < $productLevel) { return false; } // On delete, revert to zero if ($value === '' || $value === 'DEL') { $msgLevel = 0; } $value = $this->parseBool($value); $this->updateSQL('products', ['figure' => $value ? 'Y' : 'N', 'figure_level' => $msgLevel], ['id' => $id]); $this->scheduleProductTitleUpdate($id); return; case 'delete': $column = 'figure'; $value = $value > 0 ? 'Y' : 'O'; break; case 'sex': $data = ['id_product' => $id, 'id_parameter' => 18]; $this->deleteSQL('parameters_products', $data); switch ($value) { case 'M': $value = 16; break; case 'W': $value = 17; break; case 'U': $value = 22; break; case 'K': $value = 23; break; case 'B': $value = 24; break; default: throw new Exception('Nedefinované pohlaví: '.$value); } $data['value_list'] = $value; $this->insertSQL('parameters_products', $data); $this->scheduleProductTitleUpdate($id); return; case 'size': $data = ['id_product' => $id, 'id_parameter' => 20]; $this->deleteSQL('parameters_products', $data); if (preg_match('/([0-9,]+)(\w+)?/i', $value, $matches)) { $value = $this->preparePriceFromAbra($matches[1]); $data['value_float'] = $value; if (!empty($matches[2])) { $data['unit'] = $matches[2]; } $this->insertSQL('parameters_products', $data); } $this->scheduleProductTitleUpdate($id); return; case 'size_value': // Value can be "3x10" !! $value_string = $value; $this->preparePrice($value); if ($value2 == $this->abraNull || $value2 == '') { $value2 = null; } $this->updateSQL('products', ['size_value' => $value, 'size_unit' => $value2], ['id' => $id]); // This has to be from text representation. sqlQuery("UPDATE products p LEFT JOIN abra_units au ON p.size_unit = au.id_abra SET p.size = CONCAT(REPLACE(:size_value, '.', ','), ' ', IFNULL(au.code,'')) WHERE p.id=:id", ['id' => $id, 'size_value' => $value_string]); $this->scheduleProductTitleUpdate($id); break; case 'weight': $data = ['id_product' => $id, 'id_parameter' => 21]; $this->deleteSQL('parameters_products', $data); $value = $this->preparePriceFromAbra($value); if (!empty($value)) { $data['value_float'] = $value; $this->insertSQL('parameters_products', $data); } return; case 'region': $data = ['id_product' => $id, 'id_parameter' => 26]; $this->deleteSQL('parameters_products', $data); switch ($value) { case '1': $value = 19; break; case '2': $value = 20; break; case '4': $value = 21; break; } $data['value_list'] = $value; $this->insertSQL('parameters_products', $data); return; case 'product_line': $data = ['id_product' => $id, 'id_parameter' => 27]; $this->deleteSQL('parameters_products', $data); $value = $this->findParameterValue(27, $value); $data['value_list'] = $value; $this->insertSQL('parameters_products', $data); $this->scheduleProductTitleUpdate($id); return; case 'flag_S': if ($value == '') { $value = 'Ne'; } elseif ($value == 'Kazeta') { $value = 'Ano'; } else { throw new Exception("Neznámý set: {$value}"); } // no break case 'flag_G': case 'flag_A': case 'flag_T': $flag = substr($column, 5); $value = $this->parseBool($value); $data = ['id_product' => $id, 'flag' => $flag]; $this->updateProductFlag($data, $value); return; case 'vat': $value = $this->findVAT($value); break; case 'in_store': $value2 = strtolower($value2); if (!$value2) { var_dump('Neni cislo skladu!!!'); return; } if (!in_array($value2, static::$ENABLED_STORES)) { var_dump("Ignorace skladu {$value2} !!!"); return; } $value = intval($value); $storeFields = 'in_store_'.join(', in_store_', static::$ENABLED_STORES); $store = sqlFetchAssoc(sqlQuery('SELECT FIND_IN_SET(\'N\', campaign) AS new, in_store, '.$storeFields.' FROM products WHERE id=:id', ['id' => $id])); $actual = $store['in_store']; $store['in_store_'.$value2] = $value; // sum all stores for in_store field $value = 0; foreach (static::$ENABLED_STORES as $storeNumber) { $value += $store['in_store_'.$storeNumber] ?? 0; } $store['in_store'] = $value; if ($value > $actual && !$store['new']) { // Oznacit jako 'Nove naskladneno' $data = ['id_product' => $id, 'id_parameter' => 31]; $this->deleteSQL('parameters_products', $data); $data['value_float'] = time(); $this->insertSQL('parameters_products', $data); // Add "Nove naskladneno" flag $data['flag'] = 'R'; $this->updateProductFlag($data, true); // Remove "Na ceste" flag $data['flag'] = 'C'; $this->updateProductFlag($data, false); } unset($store['new']); $this->updateSQL('products', $store, ['id' => $id]); $this->scheduleProductTitleUpdate($id); return; case 'to_deliver': $date = DateTime::createFromFormat('j.n.Y', $value2); // Quantity $data = ['id_product' => $id, 'id_parameter' => 32]; $this->deleteSQL('parameters_products', $data); if ($date && $value) { $data['value_float'] = intval($value); $this->insertSQL('parameters_products', $data); } // Date $data = ['id_product' => $id, 'id_parameter' => 33]; $this->deleteSQL('parameters_products', $data); if ($date && $value) { $data['value_float'] = $date->getTimestamp(); $this->insertSQL('parameters_products', $data); } // Flag $data['flag'] = 'C'; $this->updateProductFlag($data, $date && $value); return; case 'ean': $this->prepareNull($value); break; case 'ignore': return; case 'short_descr': break; case 'elnino_in_store_decrement': break; default: $this->sync_products_common($code, $field, $value, $value2, $id); return; } $this->updateSQL('products', [$column => $value], ['id' => $id]); return; } protected function sync_products_common($code, $field, $value, $value2 = null, $id = null) { $column = static::$PRODUCT_FIELDS[$field]; switch ($column) { case 'id_section': case 'id_complement_model': case 'id_serie': $value = $this->parseNullable($value); $this->updateSQL('abra_products', [$column => $value], ['id_product' => $id]); $this->scheduleUpdateCommon($id); break; case 'id_complement_type': case 'name2': if ($value == $this->abraNull) { $value = null; } $this->updateSQL($this->main_table, [$column => $value], ['id' => $id]); $this->scheduleUpdateCommon($id); break; case 'size_value': // Value can be "3x10" !! $value_string = $value; $tmpArr = explode('x', $value); if (count($tmpArr) > 1) { $value = floatval(trim($tmpArr[0])) * floatval(trim($tmpArr[1])); } else { $this->preparePrice($value); } if ($value2 == $this->abraNull || $value2 == '') { $value2 = null; } $this->updateSQL($this->main_table, ['size_value' => $value, 'size_unit' => $value2], ['id' => $id]); // This has to be from text representation. sqlQuery("UPDATE {$this->main_table} p LEFT JOIN abra_units au ON p.size_unit = au.id_abra SET p.size = CONCAT(REPLACE(:size_value, '.', ','), ' ', IFNULL(au.code,'')) WHERE p.id=:id", ['id' => $id, 'size_value' => $value_string]); $this->scheduleUpdateCommon($id); break; case 'damage': $value = $this->parseNullable($value); $this->updateSQL($this->main_table, ['damage' => $value], ['id' => $id]); $this->scheduleUpdateCommon($id); break; case 'constitution': $this->updateSQL($this->main_table, ['set' => $value], ['id' => $id]); $this->scheduleUpdateCommon($id); break; case 'shade': $this->updateSQL($this->main_table, ['shade' => $value], ['id' => $id]); $this->scheduleUpdateCommon($id); break; case 'annotation': $this->updateSQL($this->main_table, ['annotation' => $value], ['id' => $id]); $this->scheduleUpdateCommon($id, true); break; default: var_dump('Nedefinovaná položka synchronizace: '.$field); } } public function sync_section($code, $field, $value) { $id = $this->getMappingFromAbra('section', $code); if (!$id) { // Create Product $vat_id = null; getVat($vat_id); $this->insertSQL('sections', ['name' => $value]); $id = sqlInsertId(); $this->insertSQL('sections_relation', ['id_section' => $id, 'id_topsection' => null, 'position' => 999]); $this->setMappingFromAbra('section', $id, $code); } $column = static::$SECTION_FIELDS[$field]; // translations sync if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } switch ($column) { case 'descr': $blocks = ServiceContainer::getService(\KupShop\I18nBundle\Translations\BlocksTranslation::class); $idRootBlock = $this->selectSQL('sections', ['id' => $id], ['id_block'])->fetchColumn(); if ($idRootBlock) { $objectID = $this->selectSQL('blocks', ['id_parent' => $idRootBlock, 'id_root' => $idRootBlock], ['id'])->fetchColumn(); $blocks->saveSingleObject($this->lang, $objectID, ['content' => $value]); } break; default: $this->saveTranslationObject(SectionsTranslation::class, ['id_object' => $id], [$column => $value]); break; } return; } switch ($column) { case 'parent_id': $parent_id = $this->getMappingFromAbra('section', $value); if (!$parent_id) { if ($value > 0) { echo 'Unknown section ID: '.$value; } // throw new Exception('Unknown section ID: '.$value); break; } sqlQuery('DELETE FROM sections_relation WHERE id_section=:id', ['id' => $id]); sqlQuery('INSERT INTO sections_relation (id_section, id_topsection) VALUES (:id, :parent_id)', ['id' => $id, 'parent_id' => $parent_id, 'position' => 999]); MenuSectionTree::invalidateCache(); return; case 'position': sqlQuery('UPDATE '.getTableName('sections_relation').' SET position=:position WHERE id_section=:id', ['id' => $id, 'position' => $value]); return; case 'descr': /** @var \KupShop\ContentBundle\Util\Block $blockService */ $blockService = ServiceContainer::getService(\KupShop\ContentBundle\Util\Block::class); $blockService->insertFirstBlock('sections', $id, $value); return; case 'figure': $this->updateSQL('sections', ['figure' => ($value == 'Ano' ? 'Y' : 'N')], ['id' => $id]); return; case 'ignore': return; case 'delete': $this->deleteSQL('sections', ['id' => $id]); return; case 'replacement': $id_new = $this->getMappingFromAbra('section', $value); if ($id && $id_new) { $this->createRewrite(\KupShop\ElninoBundle\Util\Rewrite::TYPE_CATEGORY, $id_new, [ 's' => 'category', 'IDcat' => $id, ]); } break; case null: var_dump('Section: Nedefinovaná položka synchronizace: '.$field); return; default: $this->updateSQL('sections', [$column => $value], ['id' => $id]); } $this->scheduleProductTitleUpdate(null, null, null, null, $id); } // Always update only one section public function getSectionIDs($abra_id, $sex) { if (!$abra_id) { return null; } $sections = sqlFetchAll(sqlQuery('SELECT abs.id_section AS id, sr.id_topsection FROM abra_sections abs LEFT JOIN sections_relation sr ON sr.id_section = abs.id_section WHERE id_abra=:code ORDER BY abs.id_section', ['code' => $abra_id] )); if (empty($sections)) { var_dump('unknown section: '.$abra_id); return null; } return array_map(function ($x) { return $x['id']; }, $sections); } public function sync_producer($code, $field, $value) { $id = $this->getMappingFromAbra('producer', $code); if (!$id) { // Create Product $vat_id = null; getVat($vat_id); $max_position = returnSQLResult('SELECT MAX(position) FROM producers'); $this->insertSQL('producers', ['name' => $code, 'position' => $max_position + 1]); $id = sqlInsertId(); $this->setMappingFromAbra('producer', $id, $code); } if (empty(static::$PRODUCER_FIELDS[$field])) { return; } $column = static::$PRODUCER_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } switch ($column) { case 'descr': $blocks = ServiceContainer::getService(\KupShop\I18nBundle\Translations\BlocksTranslation::class); $idRootBlock = $this->selectSQL('producers', ['id' => $id], ['id_block'])->fetchColumn(); if ($idRootBlock) { $objectID = $this->selectSQL('blocks', ['id_parent' => $idRootBlock, 'id_root' => $idRootBlock, 'position' => 1], ['id'])->fetchColumn(); } else { // create empty block $service = $this->getBlockUtil(); $objectID = $service->insertFirstBlock('producers', $id, ''); } if ($objectID) { $blocks->saveSingleObject($this->lang, $objectID, ['content' => $value]); } break; default: $this->saveTranslationObject(ProducersTranslation::class, ['id_object' => $id], [$column => $value]); break; } return; } switch ($column) { case 'ignore': return; case 'position': // $this->updateSQL('producers', ['position' => $value], ['id' => $id]); return; case 'descr': $blockUtil = $this->getBlockUtil(); $blockUtil->insertFirstBlock('producers', $id, $value); return; case 'active': $this->updateSQL('producers', ['active' => ($value == 'Ano' ? 'Y' : 'N')], ['id' => $id]); break; case 'delete': $this->deleteSQL('producers', ['id' => $id]); break; case 'replacement': $id_new = $this->getMappingFromAbra('producer', $value); if ($id && $id_new) { $this->createRewrite(\KupShop\ElninoBundle\Util\Rewrite::TYPE_PRODUCER, $id_new, [ 's' => 'category', 'IDpd' => $id, ]); } break; case null: var_dump('Producer: Nedefinovaná položka synchronizace: '.$field); return; default: $this->updateSQL('producers', [$column => $value], ['id' => $id]); } $this->scheduleProductTitleUpdate(null, null, null, $id); } public function sync_parameters($code, $field, $value) { $id = $this->getMappingFromAbra('parameter', $code); if (!$id) { $this->insertSQL('parameters', ['value_type' => 'list']); $id = sqlInsertId(); $this->setMappingFromAbra('parameter', $id, $code); } $column = static::$PARAMETERS_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } $this->saveTranslationObject(ParametersTranslation::class, ['id_object' => $id], [$column => $value]); return; } switch ($column) { case 'ignore': break; case 'name': $value = str_replace(' Ano x Ne', '', $value); $this->updateSQL('parameters', ['name' => $value], ['id' => $id]); break; case null: var_dump('Parameters: Nedefinovaná položka synchronizace: '.$field); return; case 'delete': $this->deleteSQL('parameters', ['id' => $id]); break; } } public function sync_parameters_values($code, $field, $value) { $id = $this->getMappingFromAbra('parameters_value', $code); if (!$id && $value) { $id_parameter = $this->getMappingFromAbra('parameter', $value); $this->insertSQL('parameters_list', ['id_parameter' => $id_parameter, 'value' => '', 'description' => '']); $id = sqlInsertId(); $this->setMappingFromAbra('parameters_value', $id, $code); } $column = static::$PARAMETERS_VALUES_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } switch ($column) { case 'name': $this->saveTranslationObject(ParametersListTranslation::class, ['id_object' => $id], ['value' => $value]); break; default: break; } return; } switch ($column) { case 'ignore': break; case 'name': $this->updateSQL('parameters_list', ['value' => $value], ['id' => $id]); break; case 'position': $this->updateSQL('parameters_list', ['position' => $value], ['id' => $id]); break; case 'delete': $this->deleteSQL('parameters_list', ['id' => $id]); break; case null: var_dump('Parameters Values: Nedefinovaná položka synchronizace: '.$field); return; } } public function sync_parameters_products($code, $field, $value) { $id = $this->getMappingFromAbra('parameters_product', $code); $column = static::$PARAMETERS_PRODUCTS_FIELDS[$field]; if (!$id) { // Only create if having valid product ID if ($column != 'id_product' || !$value) { var_dump('Nejde vytvorit parametr, nemám produkt'); return; } if ($this->main_table === 'products') { $id_product = $this->getMappingFromAbra('product', $value); } else { $id_variation = $this->getMappingFromAbra('product', $value); $id_product = returnSQLResult('SELECT id_product FROM products_variations WHERE id=:id', [ 'id' => $id_variation, ]); } if (!$id_product) { var_dump("Neexistujici produkt {$value}"); return; } $id_parameter = $this->getMappingFromAbra('parameter', '1'); $values = ['id_parameter' => $id_parameter, 'id_product' => $id_product]; if (isset($id_variation)) { $values['id_variation'] = $id_variation; } $this->insertSQL('parameters_products', $values); $id = sqlInsertId(); $this->setMappingFromAbra('parameters_product', $id, $code); } switch ($column) { case 'ignore': break; case 'id_product': if (empty($value)) { return; } if ($this->main_table === 'products') { $id_product = $this->getMappingFromAbra('product', $value); } else { $id_variation = $this->getMappingFromAbra('product', $value); $id_product = returnSQLResult('SELECT id_product FROM products_variations WHERE id=:id', [ 'id' => $id_variation, ]); } if (is_null($id_product)) { var_dump("Neexistujici produkt {$value}"); break; } $values = ['id_product' => $id_product]; if (isset($id_variation)) { $values['id_variation'] = $id_variation; } $this->updateSQL('parameters_products', $values, ['id' => $id]); break; case 'param_value_id': if (empty($value)) { return; } $id_value = $this->getMappingFromAbra('parameters_value', $value); $id_parameter = returnSQLResult('SELECT id_parameter FROM parameters_list WHERE id=:id', ['id' => $id_value]); $this->updateSQL('parameters_products', ['value_list' => $id_value, 'id_parameter' => $id_parameter], ['id' => $id]); break; case 'delete': $this->deleteSQL('parameters_products', ['id' => $id]); break; case null: var_dump('Parameters Products: Nedefinovaná položka synchronizace: '.$field); return; } } public function sync_parameters_sections($code, $field, $value) { $ids = $this->getMappingFromAbraMultiple('parameters_section', $code); if (count($ids) <= 0) { // insert new abra row or rows switch ($field) { case 'AssortmentGroup_ID': foreach ($this->getMappingFromAbraMultiple('section', $value) as $sectionID) { $this->insertSQL('abra_parameters_sections', ['id_abra' => $code, 'id_section' => $sectionID]); } break; case 'Parameter_ID': $parameterID = $this->getMappingFromAbra('parameter', $value); $this->insertSQL('abra_parameters_sections', ['id_abra' => $code, 'id_parameter' => $parameterID]); break; } } else { // update existing rows (or insert new combinations of abra_id and kupshop id_section) switch ($field) { case 'AssortmentGroup_ID': foreach ($this->getMappingFromAbraMultiple('section', $value) as $sectionID) { $row = sqlQueryBuilder()->select('*')->from('abra_parameters_sections') ->where('id_abra=:id_abra AND id_section=:id_section') ->setParameters(['id_abra' => $code, 'id_section' => $sectionID]) ->setMaxResults(1)->execute()->fetch(); // combination of abra_id and kupshop id_section does not exists, so create it if (!$row) { // find existing abra row $row = sqlQueryBuilder()->select('*')->from('abra_parameters_sections') ->where('id_abra=:id_abra') ->setParameters(['id_abra' => $code]) ->setMaxResults(1)->execute()->fetch(); if ($row && isset($row['id_parameter'])) { // duplicate existing abra row $this->insertSQL('abra_parameters_sections', ['id_abra' => $code, 'id_section' => $sectionID, 'id_parameter' => $row['id_parameter']]); } else { // create new abra row $this->insertSQL('abra_parameters_sections', ['id_abra' => $code, 'id_section' => $sectionID]); } } } break; case 'Parameter_ID': $parameterID = $this->getMappingFromAbra('parameter', $value); $this->updateSQL('abra_parameters_sections', ['id_parameter' => $parameterID], ['id_abra' => $code]); break; case 'ONDELETE': // remove related rows (parameters_sections) foreach ($this->getMappingFromAbraMultiple('parameters_section', $code) as $parametersSectionsID) { $this->deleteSQL('parameters_sections', ['id' => $parametersSectionsID]); } // remove abra_parameters_sections rows $this->deleteSQL('abra_parameters_sections', ['id_abra' => $code]); return; break; } } // when $field IS NOT 'ONDELETE', update kupshop parameters_sections table $abraSelection = sqlQueryBuilder()->select('*')->from('abra_parameters_sections') ->where('id_abra=:id_abra')->setParameter('id_abra', $code) ->execute()->fetchAll(); foreach ($abraSelection as $abraRow) { // update/insert only if id_section and id_parameter is present if (isset($abraRow['id_section']) && isset($abraRow['id_parameter'])) { // update existing kupshop parameters_sections row or insert new one if (isset($abraRow['id_parameters_section'])) { $this->updateSQL('parameters_sections', ['id_section' => $abraRow['id_section'], 'id_parameter' => $abraRow['id_parameter']], ['id' => $abraRow['id_parameters_section']]); } else { $this->insertSQL('parameters_sections', ['id_section' => $abraRow['id_section'], 'id_parameter' => $abraRow['id_parameter']]); $id = sqlInsertId(); $this->updateSQL('abra_parameters_sections', ['id_parameters_section' => $id], ['id_abra' => $code, 'id_section' => $abraRow['id_section'], 'id_parameter' => $abraRow['id_parameter']]); } } } } public function sync_complement_model($code, $field, $value) { $id = returnSQLResult('SELECT id_abra FROM abra_complement_models WHERE id_abra=:code', ['code' => $code]); if (!$id) { $this->insertSQL('abra_complement_models', ['id_abra' => $code]); $id = $code; } $column = static::$COMPLEMENT_MODEL_FIELDS[$field]; switch ($column) { case 'ignore': break; case 'name': $this->updateSQL('abra_complement_models', ['name' => $value], ['id_abra' => $id]); $this->scheduleProductTitleUpdate(null, null, null, null, null, null, $id); break; case 'delete': $this->deleteSQL('abra_complement_models', ['id_abra' => $id]); break; case null: var_dump('Complement Model: Nedefinovaná položka synchronizace: '.$field); return; } } public function sync_complement_type($code, $field, $value) { $id = returnSQLResult('SELECT id_abra FROM abra_complement_types WHERE id_abra=:code', ['code' => $code]); if (!$id) { $this->insertSQL('abra_complement_types', ['id_abra' => $code]); $id = $code; } $column = static::$COMPLEMENT_TYPE_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } $this->saveTranslationObject(AbraComplementTypeTranslation::class, ['id_object' => $id], [$column => $value]); $this->scheduleProductTitleUpdate(null, null, null, null, null, null, null, $id); return; } switch ($column) { case 'ignore': break; case 'name': $this->updateSQL('abra_complement_types', ['name' => $value], ['id_abra' => $id]); break; case 'delete': $this->deleteSQL('abra_complement_types', ['id_abra' => $id]); break; case null: var_dump('Complement Type: Nedefinovaná položka synchronizace: '.$field); return; } $this->scheduleProductTitleUpdate(null, null, null, null, null, null, null, $id); } public function sync_model($code, $field, $value) { $abra_model = sqlFetchAssoc($this->selectSQL('abra_models', ['id_abra' => $code])); if (!$abra_model) { $this->insertSQL('abra_models', ['id_abra' => $code]); } if (empty(static::$MODEL_FIELDS[$field])) { return; } $column = static::$MODEL_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } $this->saveTranslationObject(AbraModelsTranslation::class, ['id_object' => $code], [$column => $value]); $this->scheduleProductTitleUpdate(null, null, null, null, null, $code); return; } switch ($column) { case 'ignore': break; case 'id_serie': $value = $this->parseNullable($value); $this->updateSQL('abra_models', ['id_serie' => $value], ['id_abra' => $code]); $this->scheduleProductTitleUpdate(null, null, null, null, null, $code); break; case 'id_complement_model': $value = $this->parseNullable($value); $this->updateSQL('abra_models', ['id_complement_model' => $value], ['id_abra' => $code]); $this->scheduleProductTitleUpdate(null, null, null, null, null, $code); break; case 'descr': $this->updateSQL('abra_models', ['descr' => $value], ['id_abra' => $code]); $this->scheduleProductTitleUpdate(null, null, null, null, null, $code); break; case 'id_category': $value = $this->parseNullable($value); $this->updateSQL('abra_models', ['id_section' => $value], ['id_abra' => $code]); $this->scheduleProductTitleUpdate(null, null, null, null, null, $code); return; case 'delete': // Move all variations away from this model products sqlQuery('UPDATE products_variations pv JOIN products p ON pv.id_product = p.id SET pv.id_product = 0 WHERE p.id_model = :id_model', ['id_model' => $code]); $this->deleteSQL('abra_models', ['id_abra' => $code]); // no break case 'replacement': $abra_model_new = $this->selectSQL('abra_models', ['id_abra' => $value])->fetch(); if ($abra_model && $abra_model_new) { $this->createRewrite(\KupShop\ElninoBundle\Util\Rewrite::TYPE_MODEL, $abra_model_new['old_kupshop_id'], [ 's' => 'product', 'IDmodel' => $abra_model['old_kupshop_id'], ]); } return; break; case null: var_dump('Model: Nedefinovaná položka synchronizace: '.$field); return; } } public function sync_serie($code, $field, $value) { $id = $this->getMappingFromAbra('serie', $code); if (!$id) { // Create Product $this->insertSQL('abra_series', ['id_abra' => $code]); $id = sqlInsertId(); } if (empty(static::$SERIES_FIELDS[$field])) { return; } $column = static::$SERIES_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } $this->saveTranslationObject(AbraSeriesTranslation::class, ['id_object' => $code], [$column => $value]); $this->scheduleProductTitleUpdate(null, null, $id); return; } switch ($column) { case 'ignore': return; case 'producer': $id_producer = $this->getMappingFromAbra('producer', $value); $this->updateSQL('abra_series', ['id_producer' => $id_producer ?: null], ['id_serie' => $id]); break; case 'active': $active = $value == 'Ano' ? 'Y' : 'N'; $this->updateSQL('abra_series', ['active' => $active], ['id_serie' => $id]); break; case 'delete': $products = sqlQueryBuilder() ->select('p.id', 'pv.id as id_variation') ->fromProducts() ->joinVariationsOnProducts() ->where('p.id_serie = :id_serie') ->setParameter('id_serie', $id); foreach ($products as $product) { $this->scheduleProductTitleUpdate($product['id'], $product['id_variation']); } $this->deleteSQL('abra_series', ['id_serie' => $id]); break; case 'descr': $this->handleTranslatedText(['table' => 'abra_series', 'field' => $column, 'where' => ['id_serie' => $id]], $value); break; case null: var_dump('Serie: Nedefinovaná položka synchronizace: '.$field); return; default: $this->updateSQL('abra_series', [$column => $value], ['id_serie' => $id]); } $this->scheduleProductTitleUpdate(null, null, $id); } public function sync_damage($code, $field, $value) { $id = returnSQLResult('SELECT id_abra FROM abra_damages WHERE id_abra=:code', ['code' => $code]); if (!$id) { $this->insertSQL('abra_damages', ['id_abra' => $code]); $id = $code; } $column = static::$DAMAGE_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } switch ($column) { default: $this->saveTranslationObject(AbraDamagesTranslation::class, ['id_object' => $id], [$column => $value]); break; } return; } switch ($column) { case 'ignore': break; case 'code': $this->updateSQL('abra_damages', ['code' => $value], ['id_abra' => $id]); break; case 'name': $this->updateSQL('abra_damages', ['name' => $value], ['id_abra' => $id]); break; case 'delete': $this->deleteSQL('abra_damages', ['id_abra' => $id]); break; case null: var_dump('Damage: Nedefinovaná položka synchronizace: '.$field); return; } } public static $PICTURE_TYPES = [ '1' => 'nothing', '2' => 'Y', '3' => 'nothing', '4' => 'nothing', '5' => 'N', '6' => 'nothing', 'default' => 'ignore', ]; // 550949495~|~Picture~|~StoreCard_ID~|~171467~|~71203~|~~|~all~|~all // 563980919~|~Picture~|~StoreCard_ID~|~173141~|~71602~|~~|~all~|~all // 563980920~|~Picture~|~PictureType~|~173141~|~4~|~~|~all~|~all public function sync_picture($code, $field, $value) { $row = sqlFetchAssoc($this->selectSQL('abra_pictures', ['id_abra' => $code])); $column = static::$PICTURE_FIELDS[$field]; if (!$row) { $this->setMappingFromAbra('picture', null, $code); } switch ($column) { case 'id_product': $id_product = returnSQLResult('SELECT id FROM products WHERE code=:code', ['code' => $value]); if (!$id_product) { return; } $this->updateSQL('abra_pictures', ['id_abra_product' => $value], ['id_abra' => $code]); $product = sqlFetchArray(sqlQuery('SELECT apr.id_product, ap.id_picture FROM abra_pictures ap JOIN abra_products apr ON apr.id_abra = ap.id_abra_product WHERE ap.id_abra=:id_abra', ['id_abra' => $code])); $this->scheduleProductTitleUpdate($product['id_product']); break; case 'type': $product = sqlFetchArray(sqlQuery('SELECT apr.id_product, ap.id_picture FROM abra_pictures ap JOIN abra_products apr ON apr.id_abra = ap.id_abra_product WHERE ap.id_abra=:id_abra', ['id_abra' => $code])); $type = null; if (isset(static::$PICTURE_TYPES[$value])) { switch (static::$PICTURE_TYPES[$value]) { case 'ignore': return false; case 'nothing': break; case 'coty_video': $pathFinder = PathFinder::getService(); $videosDir = $pathFinder->getDataDir().'photos/shared/videos/'.mb_substr('00'.$code, -2); @mkdir($videosDir, 0777, true); $videoName = $code.'_'.$value.'.mp4'; if (file_exists($videosDir.'/'.$videoName)) { $this->updateSQL('abra_pictures', ['type' => $value], ['id_abra' => $code]); } else { $downloader = new Downloader(); if ($downloader->copyRemoteFile(trim('https://test:test@server23.elnino.cz:81/?id='.$code), $videosDir.'/'.$videoName)) { $this->updateSQL('abra_pictures', ['type' => $value], ['id_abra' => $code]); } } return; default: $type = static::$PICTURE_TYPES[$value]; } } elseif (isset(static::$PICTURE_TYPES['default'])) { switch (static::$PICTURE_TYPES['default']) { case 'ignore': return false; case 'nothing': break; default: $type = static::$PICTURE_TYPES['default']; } } else { return false; } if (!file_exists($this->getPhotoPath($code, null)['path'])) { if (!empty($row['id_picture'])) { sqlQuery('DELETE FROM photos WHERE id = :id', ['id' => $row['id_picture']]); } $row['id_picture'] = null; } if (!$row['id_picture']) { sqlGetConnection()->transactional(function () use ($code, &$product, $type) { // download if photo no exists already if (!$this->photoExists($code)) { $id = $this->downloadPhoto($code); } else { $id = $this->getPhotoId($code); } if (!$id) { $this->deleteSQL('abra_pictures', ['id_abra' => $code]); return false; // throw new Exception("Chyba stahovani fotky: ".$code); } $product['id_picture'] = $id; $this->updateSQL('abra_pictures', ['id_picture' => $id], ['id_abra' => $code]); if ($type && !empty($product['id_product'])) { sqlQuery("INSERT IGNORE INTO photos_products_relation (id_photo, id_product, show_in_lead, active) VALUES (:id_picture, :id_product, :show_in_lead , 'Y')", ['id_picture' => $id, 'id_product' => $product['id_product'], 'show_in_lead' => $type]); } // 360 if ($type == 'G') { for ($i = 2; $i <= 24; $i++) { // download if photo no exists already if (!$this->photoExists($code, $i)) { $photoId = $this->downloadPhoto($code, $i); } else { $photoId = $this->getPhotoId($code, $i); } if (!$photoId) { throw new Exception("Neco s fotkama - prerusilo stahovani rady 360° fotek. Picture_code: {$code}, frame: {$i}"); } $this->updateSQL('photos', ['parent' => $id], ['id' => $photoId]); } } }); } if ($type && !empty($product['id_product'])) { $productPhotoRelation = $this->selectSQL( 'photos_products_relation', [ 'id_photo' => $product['id_picture'], 'id_product' => $product['id_product'], ] )->fetch(); if ($productPhotoRelation) { $this->updateSQL( 'photos_products_relation', [ 'show_in_lead' => $type, 'position' => $value ?? $productPhotoRelation['position'] ?? null, ], [ 'id_photo' => $product['id_picture'], 'id_product' => $product['id_product'], ] ); } elseif ($product['id_product']) { sqlQuery("INSERT IGNORE INTO photos_products_relation (id_photo, id_product, show_in_lead, active, position) VALUES (:id_picture, :id_product, :show_in_lead , 'Y', :position)", [ 'id_picture' => $row['id_picture'], 'id_product' => $product['id_product'], 'show_in_lead' => $type, 'position' => $value ?? null, ]); } } $this->updateSQL('abra_pictures', ['type' => $value], ['id_abra' => $code]); $this->scheduleProductTitleUpdate($product['id_product']); break; case 'delete': $row = sqlQueryBuilder()->select('apr.id_product, api.id_picture, ph.source, ph.image_2') ->from('abra_pictures', 'api') ->leftJoin('api', 'abra_products', 'apr', 'apr.id_abra=api.id_abra_product') ->leftJoin('api', 'photos', 'ph', 'ph.id=api.id_picture') ->where(\Query\Operator::equals(['api.id_abra' => $code])) ->setMaxResults(1)->execute()->fetch(); if (!empty($row['id_picture'])) { // this also removes photos_products_relation and abra_pictures (both have ON DELETE CASCADE) sqlQueryBuilder()->delete('photos') ->where(\Query\Operator::equals(['id' => $row['id_picture']]))->execute(); if (!empty($row['source']) && !empty($row['image_2'])) { $cfg = Config::get(); @unlink($cfg['Path']['photos'].$row['source'].$row['image_2']); } } else { sqlQueryBuilder()->delete('abra_pictures') ->where(\Query\Operator::equals(['id_abra' => $code]))->execute(); } $this->scheduleProductTitleUpdate($row['id_product']); break; case null: var_dump('Section: Nedefinovaná položka synchronizace: '.$field); return false; } } /** * @param null $frame * * @return int */ public function getPhotoId($code, $frame = null) { $name = $code.'.jpg'; if ($frame) { $name = $code.'_'.$frame.'.jpg'; } $photo = $this->selectSQL('photos', ['image_2' => $name])->fetch(); if ($photo) { return $photo['id']; } else { return $this->savePhotoToDb($code, $frame); } } /** * @param int $frame * * @return bool */ public function photoExists($code, $frame = null) { if (file_exists($this->getPhotoPath($code, $frame)['path'])) { return true; } return false; } /** * @return array */ public function getPhotoPath($code, $frame) { $dir = intval($code) % 100; $dir = sprintf('%1$02d', $dir); $name = $code.'.jpg'; if ($frame) { $name = $code.'_'.$frame.'.jpg'; } $pathFinder = PathFinder::getService(); return [ 'dir' => $dir, 'path' => $pathFinder->getDataDir().'photos/shared/'.$dir.'/'.$name, ]; } /** * @param int|null $frame * * @return bool|string|null */ public function downloadPhoto($id, $frame = null) { $downloader = new Downloader(); $query = $id; if ($frame) { $query .= '&frame='.$frame; } $photo = $downloader->downloadImage(trim('https://test:test@server23.elnino.cz:81/?id='.$query)); $this->uploadPhoto($photo, $this->getPhotoPath($id, $frame)); return $this->savePhotoToDb($id, $frame); } public function savePhotoToDb($id, $frame) { $dir = intval($id) % 100; $dir = sprintf('%1$02d', $dir); $descr = $id; if ($frame) { $descr .= '_'.$frame; $id .= '_'.$frame; } $this->insertSQL('photos', [ 'descr' => $descr, 'source' => "shared/{$dir}/", 'image_2' => $id.'.jpg', ]); return sqlInsertId(); } /** * @return bool */ public function uploadPhoto($photo, $dest) { $pathFinder = PathFinder::getService(); $dir = $pathFinder->getDataDir().'photos/shared/'; if (!file_exists($dir)) { mkdir($dir, 0777); } if (!file_exists($dir.$dest['dir'])) { mkdir($dir.$dest['dir'], 0777); } return rename($photo['tmp_name'], $dest['path']); } public function sync_unitsize($code, $field, $value) { $id = returnSQLResult('SELECT id_abra FROM abra_units WHERE id_abra=:code', ['code' => $code]); if (!$id) { $this->insertSQL('abra_units', ['id_abra' => $code]); $id = $code; } $column = static::$UNITSIZE_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } $this->saveTranslationObject(AbraUnitsTranslation::class, ['id_object' => $id], [$column => $value]); return; } switch ($column) { case 'ignore': break; case 'code': $this->updateSQL('abra_units', ['code' => $value], ['id_abra' => $id]); break; case 'name': $this->updateSQL('abra_units', ['name' => $value], ['id_abra' => $id]); break; case 'delete': $this->deleteSQL('abra_units', ['id_abra' => $id]); break; case null: var_dump('Unit: Nedefinovaná položka synchronizace: '.$field); return; } } public function sync_order($code, $field, $value, $value2) { if (!isset(static::$ORDER_FIELDS[$field])) { var_dump('Nedefinovaná položka synchronizace:', $field, $value, $value2); return; } $column = static::$ORDER_FIELDS[$field]; if ($column == 'quantity' || $column == 'price') { list($code, $id_product) = explode('~', $code); } $id = $this->getMappingFromAbra('order', $code); if (!$id) { var_dump("Neznámá objednávka: {$code}"); return false; } $value = trim($value); $order = new Order(); $order->createFromDB($id); switch ($column) { case 'status': // Cancel order if ($value == 6) { $order->storno(false, null, true); return true; } // Confirm order elseif ($value == 1) { $status = 3; } // Complete order elseif ($value == 4) { $status = 1; } // Complete order elseif ($value == 9) { $status = 2; } // Ignore everything else else { return false; } $order->changeStatus($status, translate('abra_order_status_change', 'abra'), false); break; case 'quantity': $data = ['id_order' => $id, 'id_product' => $id_product]; $items = sqlQuery('SELECT id FROM order_items WHERE id_order=:id_order AND id_product=:id_product', $data); if (sqlNumRows($items) != 1) { return; } // throw new Exception("Nelze najít změněnou položku objednávky: ".print_r($data, true)); $item_id = sqlFetchAssoc($items)['id']; $order->updateItem($item_id, $this->preparePriceFromAbra($value)); break; case 'price': $data = ['id_order' => $id, 'id_product' => $id_product]; $items = sqlQuery('SELECT id FROM order_items WHERE id_order=:id_order AND id_product=:id_product', $data); if (sqlNumRows($items) != 1) { return; } // throw new Exception("Nelze najít změněnou položku objednávky: ".print_r($data, true)); $item_id = sqlFetchAssoc($items)['id']; $order->updateItemPrice($item_id, toDecimal($this->preparePriceFromAbra($value))); break; case 'note_expedition': $noteExpedition = translate('abra_order_error', 'abra'); $order->logHistory("{$noteExpedition}:
{$value}"); break; case 'ignore': break; default: throw new Exception('Nedefinovany sloupec: '.$column); } } public function sync_ignore($code, $field, $value, $value2 = null) { } // Orders public function syncOrder($id) { // Ignore order for now if sync failed $cacheId = 'order-sync-failed-'.$id; if (getCache($cacheId)) { return; } try { $this->log(['Sync order', 'id' => $id]); parent::syncOrder($id); } catch (RuntimeException $e) { $this->notifyOrderError($e, $id); return; } // Pokud by se stala chyba, vylitla by exception. Tady vím, že je to OK $this->log(['Sync order' => 'success', 'id' => $id]); } public function notifyOrderError($e, $id) { // Send notification email to shop owners $cacheId = 'order-sync-failed-'.$id; if (getCache($cacheId)) { return; } $order = new Order(); $order->createFromDB($id); setCache($cacheId, $id, 1800); global $dbcfg; SendMail('info@kupshop.cz', 'joe@wpj.cz, abra@it-pro.cz, '.$dbcfg->order_shopkeeper_mail, "Chyba synchronizace objednávky {$order->order_no}", "Nelze odeslat do Abry objednávku číslo {$order->order_no}.\n\nChyba: {$e->getMessage()}"); } public function getOrder($id) { $order = new Order(); $order->createFromDB($id, true, true, true); return $this->contextManager->activateOrder($order, function () use ($order) { $delivery = null; $payment = null; $id_delivery_type = null; if (findModule('eshop_delivery')) { $delivery_type = $order->getDeliveryType(); $id_delivery_type = $delivery_type->id; $delivery = $this->getMappingToAbra('delivery', $delivery_type->id_delivery) ?: null; $payment = $this->getMappingToAbra('payment', $delivery_type->id_payment) ?: null; if ($delivery == 'CZ' && $order->delivery_country == 'PL') { $delivery = 'PL'; } } $user_abra_id = getVal('elnino_abra_id', sqlFetchAssoc($this->selectSQL('users', ['id' => $order->id_user], ['elnino_abra_id'])), 519194); $sync_order = [ // Header 'WEBsource' => $this->settings['web'], 'DocumentNumber' => $order->order_no, 'WEBnote' => $this->prepareTextForOrder($order->note_user), 'Currency' => $order->currency, 'PaymentCode' => $payment, 'TransportCode' => $delivery, 'Amount' => $this->preparePriceToAbra($order->total_price), 'Department' => null, 'Points' => null, 'SMS' => ($id_delivery_type && $id_delivery_type != 3) ? 'A' : 'N', 'DocumentID' => $order->id, 'Status' => 0, 'CustomerServiceNote' => null, // Customer 'Code' => $user_abra_id, 'Email' => null, 'PhoneNumber' => null, // Invoice address 'adName' => null, 'adFirstName' => null, 'adLastName' => null, 'adStreet' => null, 'adCity' => null, 'adPostCode' => null, 'adCountryCode' => null, // Delivery address 'amName' => null, 'amFirstName' => null, 'amLastName' => null, 'amStreet' => null, 'amCity' => null, 'amPostCode' => null, 'OrgIdentNumber' => null, 'VATIdentNumber' => null, 'CH_Kanton' => null, 'CH_Recipient' => null, 'Rows' => $this->getOrderRows($order), ]; return $sync_order; }); } /** * @param $order Order * * @return array */ public function getOrderRows($order) { $orderItemInfo = ServiceContainer::getService(OrderItemInfo::class); $order->fetchItems(); $isKOZA = array_search($this->settings['web'], ['kosmetika-zdravi.cz', 'spleticna.si', 'lijepa.hr', 'europarfemy.cz', 'parfimo.ch', 'parfimo.it', 'parfumcity.cz', 'parfumcity.ch', 'profumicitta.it', 'parfimo.de', 'parfimo.at', 'eglamour.de']) !== false; $rows = []; $priceType = 'value_without_vat_no_rounding'; if ($isKOZA) { $priceType = 'value_with_vat_no_rounding'; } $delivery = null; foreach ($order->items as $item) { $store = 1; $item['descr'] = $this->prepareTextForOrder($item['descr']); if ($item['id_product']) { $code = $this->getMappingToAbra('product', $item['id_product']); } else { $code = ''; } $itemType = $orderItemInfo->getItemType($item); if ($code) { $type = 3; $name = ''; $price = $item['piece_price'][$priceType]; $quantity = $item['pieces']; $vat = $item['vat']; } else { if ($itemType == OrderItemInfo::TYPE_DELIVERY) { $delivery = $item; continue; } if ($itemType == OrderItemInfo::TYPE_COUPON) { $itemNote = $item['note']; if (!empty($itemNote['discount']['cv'])) { $item['descr'] .= ' ('.$itemNote['discount']['cv'].')'; } if (!$this->selectSQL('discounts_coupons', ['id_discount' => $itemNote['discount']['id']])->fetch()) { $itemType = OrderItemInfo::TYPE_DISCOUNT; } if (($couponData = $this->getDiscountItemValues($item)) && $itemType == OrderItemInfo::TYPE_COUPON) { $rowPrice = $couponData['coupon_price']; $this->processOrderItemToRows($rows, $item, [ 'type' => 1, 'code' => '', 'quantity' => '1', 'price' => $this->preparePriceToAbra($couponData['residue']), 'name' => translate_shop('abra_not_used_discount', 'abra'), 'vat' => 25, ]); } else { $rowPrice = $item['total_price'][$priceType]; } } else { $rowPrice = $item['total_price'][$priceType]; } $type = 3; $code = $this->getNonProductCode($itemType); if ($itemType == OrderItemInfo::TYPE_CHARGE) { $chargeId = ($item['note']['id_charge'] ?? false); if ($chargeId == self::CHARGE_BALNE) { $code = '999904'; } } $name = $item['descr']; $price = $rowPrice; $quantity = 1; $vat = $item['vat']; } if ($this->settings['web'] == 'perfumy-hurtownia.pl') { $price = roundPrice($price); } $price = $this->preparePriceToAbra($price); // Detect used store if ($item['id_product'] && array_search($this->settings['web'], [ 'europarfemy.cz', 'kosmetika-zdravi.cz', 'spleticna.si', 'parfumcity.cz', 'parfumcity.ch', 'parfimo.ch', 'wholesale-perfumes.eu', ]) !== false ) { $elnino_store = $item['note']['elnino_store'] ?? 1; if ($elnino_store) { $store = intval($elnino_store); } } if ($isKOZA) { $row = sqlFetchAll(sqlQuery('SELECT data, campaign, price FROM products WHERE id=:id', ['id' => $item['id_product']])); $campaign = []; if (isset($row[0])) { if (!empty($row[0]['campaign'])) { $campaign = explodeFlags($row[0]['campaign']); } } if (isset($campaign['G'])) { $price = calcPrice($row[0]['price'], $vat); $price = $this->preparePriceToAbra($price->printFloatValue(2)); $this->processOrderItemToRows($rows, $item, [ 'type' => 3, 'code' => $code, 'quantity' => $quantity, 'price' => $price, 'name' => $name, 'store' => $store, 'vat' => $vat, ]); $code = '999990'; $price = '-'.$price; $type = 3; $name = $item['descr']; $store = 1; } } $this->processOrderItemToRows($rows, $item, [ 'type' => $type, 'code' => $code, 'quantity' => $quantity, 'price' => $price, 'name' => $name, 'store' => $store, 'vat' => $vat, ]); } // sleva na dopravu if (findModule(Modules::DELIVERY_TYPES)) { $deliveryType = $order->getDeliveryType(); $priceUtil = ServiceContainer::getService(\KupShop\KupShopBundle\Util\Price\PriceUtil::class); $currency = $priceUtil->getCurrencyById($order->currency); $originalDeliveryPrice = $deliveryType->getPrice(); if ($currency) { $originalDeliveryPrice = PriceCalculator::convert($originalDeliveryPrice, $currency); } if ($currency->getId() == 'EUR' && !empty($deliveryType->price_eur)) { $originalDeliveryPrice = toDecimal($deliveryType->price_eur); if (!empty($deliveryType->payment_price_eur)) { $originalDeliveryPrice = $originalDeliveryPrice->add(toDecimal($deliveryType->payment_price_eur)); } $originalDeliveryPrice = new \KupShop\KupShopBundle\Util\Price\Price($originalDeliveryPrice, $priceUtil->getCurrencyById('EUR'), 0); } if ($delivery === null) { if ($deliveryDiscount = $this->getDeliveryDiscount($originalDeliveryPrice, null, $currency)) { $this->processOrderItemToRows($rows, null, [ 'type' => 3, 'code' => 999918, 'quantity' => 1, 'price' => $this->preparePriceToAbra($deliveryDiscount->printFloatValue(2)), 'name' => translate_shop('abra_delivery_discount', 'abra'), 'store' => 1, 'vat' => 0, ]); } $deliveryPrice = $originalDeliveryPrice->getPriceWithVat(); $deliveryName = $deliveryType->name; } else { $deliveryPrice = $delivery['total_price'][$priceType]; if ($deliveryDiscount = $this->getDeliveryDiscount($originalDeliveryPrice, $delivery['total_price'][$priceType], $currency)) { $this->processOrderItemToRows($rows, null, [ 'type' => 3, 'code' => 999918, 'quantity' => 1, 'price' => $this->preparePriceToAbra($deliveryDiscount->printFloatValue(2)), 'name' => translate_shop('abra_delivery_discount', 'abra'), 'store' => 1, 'vat' => 0, ]); $deliveryPrice = $originalDeliveryPrice->getPriceWithVat(); } $deliveryName = $delivery['descr']; } if ($deliveryPrice->isPositive()) { $this->processOrderItemToRows($rows, null, [ 'type' => 3, 'code' => $this->getNonProductCode(OrderItemInfo::TYPE_DELIVERY), 'quantity' => 1, 'price' => $this->preparePriceToAbra($deliveryPrice->printFloatValue(2)), 'name' => $deliveryName, 'store' => 1, 'vat' => 0, ]); } } return $rows; } protected function getDiscountItemValues($item) { $itemNote = $item['note']; if (isset($itemNote['discount']) && ($itemNote['discount']['unit'] ?? false) == 'price') { $couponPrice = toDecimal($itemNote['discount']['discount']); $residue = $couponPrice->add($item['total_price']['value_with_vat']); if ($residue->isPositive()) { return [ 'coupon_price' => $couponPrice->mul(DecimalConstants::negativeOne()), 'residue' => $residue, ]; } } return null; } protected function processOrderItemToRows(&$rows, $item, $rowData) { // "3~|~002002~|~1~|~168,5~|~", $rows[] = join($this->separator, [ $rowData['type'], $rowData['code'], $rowData['quantity'], $rowData['price'], $rowData['name'], $rowData['store'] ?? 1, $rowData['vat'], ]); } /** * @param $deliveryPriceOriginal \KupShop\KupShopBundle\Util\Price\Price * @param $deliveryPrice Decimal|null * * @return Decimal|null */ public function getDeliveryDiscount($deliveryPriceOriginal, $deliveryPrice, $currency) { $deliveryPriceOriginal = $deliveryPriceOriginal->getPriceWithVat(); if ($deliveryPrice === null) { if ($deliveryPriceOriginal->isZero()) { return null; } return $deliveryPriceOriginal->mul(DecimalConstants::negativeOne()); } $deliveryPrice = roundPrice($deliveryPrice, null, 'DB', null, $currency); $deliveryDiscount = $deliveryPrice->sub($deliveryPriceOriginal); if (!$deliveryDiscount->isNegative()) { $deliveryDiscount = null; } if ($deliveryDiscount) { $diff = DecimalConstants::one()->sub($deliveryPrice->div($deliveryPriceOriginal))->mul(DecimalConstants::hundred())->asFloat(); if ($diff <= 0.1) { $deliveryDiscount = null; } } return $deliveryDiscount; } protected function getNonProductCode($itemType) { switch ($itemType) { case OrderItemInfo::TYPE_DELIVERY: $deliveryPriceCodes = [ 'kosmetika-zdravi.cz' => 999802, 'parfumcity.cz' => 999803, 'europarfemy.cz' => 999804, 'parfumcity.ch' => 999807, 'parfimo.ch' => 999808, 'parfimo.it' => 999811, 'spleticna.si' => 999812, 'lijepa.hr' => 999813, 'profumicitta.it' => 999814, 'parfimo.de' => 999809, 'parfimo.at' => 999817, 'eglamour.de' => 999819, ]; return $deliveryPriceCodes[$this->settings['web']] ?? 999920; break; case OrderItemInfo::TYPE_DISCOUNT: return 999944; break; case OrderItemInfo::TYPE_GIFT: return 999990; break; case OrderItemInfo::TYPE_COUPON: return 999919; break; case OrderItemInfo::TYPE_CHARGE: return 999905; break; default: return 999944; // fallback, pokud se nepodari urcit typ polozky } } // Delayed processing functions public function fixProductsTitles() { // $this->scheduleProductTitleUpdate(11972, 1444); // return $this->updateScheduledProducts(); $products = sqlQueryBuilder() ->select('p.id', 'pv.id as id_variation', 'p.id_model') ->fromProducts() ->joinVariationsOnProducts() ->orderBy('p.id') ->addOrderBy('pv.id') ->execute(); foreach ($products as $index => $product) { if (isset(static::$abraKozaV2)) { $this->scheduleProductTitleUpdate($product['id'], null, null, null, null, $product['id_model']); } else { $this->scheduleProductTitleUpdate($product['id'], $product['id_variation']); } } while (($remaining = returnSQLResult('SELECT COUNT(*) FROM abra_delayed_update')) > 0) { var_dump("Updating delayed update, {$remaining} items left"); $this->updateScheduledProducts(); } $this->finalizeSync(); } public function scheduleProductTitleUpdate($product_id = null, $variation_id = null, $serie_id = null, $brand_id = null, $section_id = null, $model_id = null, $complement_model_id = null, $complement_type_id = null) { if (!is_null($product_id) && $product_id > 0) { $this->saveDelayedUpdate($product_id, 'product'); } if (!is_null($variation_id)) { $this->saveDelayedUpdate($variation_id, 'variation'); } if (!is_null($serie_id)) { $this->saveDelayedUpdate($serie_id, 'serie'); } if (!is_null($brand_id)) { $this->saveDelayedUpdate($brand_id, 'brand'); } if (!is_null($section_id)) { $this->saveDelayedUpdate($section_id, 'section'); } if (!is_null($model_id)) { $this->saveDelayedUpdate($model_id, 'model'); } if (!is_null($complement_model_id)) { $this->saveDelayedUpdate($complement_model_id, 'complement_model'); } if (!is_null($complement_type_id)) { $this->saveDelayedUpdate($complement_type_id, 'complement_type'); } } public function saveDelayedUpdate($id, $type) { sqlQuery('INSERT IGNORE INTO abra_delayed_update (related_id, type) VALUES (:id, :type)', ['id' => $id, 'type' => $type]); } public function getDelayedUpdate($type, $limit = 500) { $data = sqlQueryBuilder()->select('*')->from('abra_delayed_update') ->where(\Query\Operator::equals(['type' => $type])) ->setMaxResults($limit) ->execute(); return sqlFetchAll($data, ['related_id' => 'id']); } public function deleteDelayedUpdate(array $ids) { sqlQueryBuilder()->delete('abra_delayed_update') ->where(\Query\Operator::inIntArray($ids, 'id')) ->execute(); } public function updateScheduledProducts() { $this->collectUpdatedProducts(); // Update products if ($product_list = $this->getDelayedUpdate('product')) { $productIds = array_keys($product_list); $products = sqlQuery(' SELECT p.id, p.id_model, p.size, p.shade, s.name AS section_name, pr.name AS producer_name, abs.name AS serie_name, abcm.name AS complement_model_name, p.name2, abs.sex, ap.id_section, s.id as id_section_shop, pr.id AS id_producer, abs.id_serie AS id_serie, GROUP_CONCAT(p.campaign SEPARATOR \',\') AS campaign, abm.descr AS long_descr, abd.name AS damage_name, abct.name AS complement_type_name, ap.id_serie, ap.id_section, ap.id_complement_model, abs.id_producer, abs.id_serie AS id_serie_kupshop FROM products AS p LEFT JOIN abra_models AS abm ON abm.id_abra = p.id_model LEFT JOIN abra_products ap ON p.id = ap.id_product LEFT JOIN abra_complement_types AS abct ON abct.id_abra = p.id_complement_type LEFT JOIN abra_damages AS abd ON abd.id_abra = p.damage LEFT JOIN abra_sections AS absec ON absec.id_abra = ap.id_section LEFT JOIN sections AS s ON s.id = absec.id_section LEFT JOIN abra_series AS abs ON abs.id_abra = ap.id_serie LEFT JOIN abra_complement_models AS abcm ON abcm.id_abra = ap.id_complement_model LEFT JOIN producers AS pr ON pr.id = abs.id_producer WHERE p.id IN (:id_products) GROUP BY p.id', ['id_products' => $productIds], ['id_products' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY] ); foreach ($products as $product) { try { $this->updateProduct($product); } catch (Exception $e) { exception_handler($e); } } $this->recalcInStore(\Query\Operator::inIntArray($productIds, 'p.id')); $this->deleteDelayedUpdate($product_list); } } public function hideIncompleteProducts() { $SQL = sqlQuery('UPDATE products p LEFT JOIN photos_products_relation AS ppr ON ppr.id_product = p.id LEFT JOIN parameters_products pp ON pp.id_parameter = 22 AND pp.id_product = p.id SET p.figure = \'O\' WHERE p.figure = \'Y\' AND (p.ean IS NULL OR p.ean = 0 /*OR ppr.id_photo IS NULL*/ OR p.price <= 0 OR (FIND_IN_SET("S", p.campaign) > 0 AND (p.set IS NULL OR p.set = "")))'); var_dump(['Skryto produktů bez EANu /*nebo obrázků*/ nebo nulovou cenou nebo setů bez popisu', sqlAffectedRows($SQL)]); $SQL = sqlQuery('UPDATE products p LEFT JOIN photos_products_relation AS ppr ON ppr.id_product = p.id LEFT JOIN parameters_products pp ON pp.id_parameter = 22 AND pp.id_product = p.id SET p.figure = \'Y\' WHERE p.figure = \'O\' AND p.ean IS NOT NULL AND p.ean != 0 /*AND ppr.id_photo IS NOT NULL*/ AND p.price > 0 AND (FIND_IN_SET("S", p.campaign) <= 0 OR NOT (p.set IS NULL OR p.set = ""))'); var_dump(['Zobrazeno produktů v "prodej ukončen" s EANem/*, obrázkem*/ a cenou a setů s popisem', sqlAffectedRows($SQL)]); } /** * @param $product array * * @throws Exception */ public function updateProduct($product) { if (empty($product['id_model'])) { // Update model $product['name2'] = $product['name2'] ?: ''; $model = sqlFetchAssoc($this->selectSQL('abra_models', [ 'id_serie' => $product['id_serie'], 'id_section' => $product['id_section'], 'id_complement_model' => $product['id_complement_model'], ], ['id_abra'])); if ($model) { $this->updateSQL('products', ['id_model' => $model['id_abra']], ['id' => $product['id']]); } } // Update variation title $flags = explodeFlags($product['campaign']); $campaign = $this->getCampaignsNames( array_filter($flags, function ($k) { return in_array($k, ['V', 'T', 'O', 'S']); }, ARRAY_FILTER_USE_KEY) ); // Kategorie + Vyrobce + Rada + Doplnek modelu + Doplnek typu produktu + Name2 + velikost + odstín + poškození + (vzorek/odstřik/tester/kazeta) $attributes = [ // $product['producer_name'], // Vyrobce je uz vsude zobrazeny, tak ho vynechat $product['serie_name'], $product['complement_model_name'], $product['complement_type_name'], $product['name2'], $product['shade'], $product['section_name'], $product['sex'], $product['size'], $product['damage_name'], join(' ', $campaign), ]; $title = join(' ', array_filter($attributes)); // preskocit Vialky if (!($product['id'] == '999998' || $product['id'] == '999999')) { $this->updateSQL('products', ['title' => $title], ['id' => $product['id']]); } // update section $sectionFinder = ServiceContainer::getService(SectionFinder::class); $sectionFinder->setProductSections($product['id'], $product['id_section_shop'] ? [$product['id_section_shop']] : []); // Update sex $data = ['id_product' => $product['id'], 'id_parameter' => 18]; $this->deleteSQL('parameters_products', $data); switch ($product['sex']) { case 'M': $value = 16; break; case '': case 'W': $value = 17; break; case 'U': $value = 22; break; case 'K': $value = 23; break; case 'B': $value = 24; break; default: throw new Exception('Nedefinované pohlaví: '.$product['sex']); } $data['value_list'] = $value; $this->insertSQL('parameters_products', $data); } public function updateModel($model) { if (empty($model['id_serie']) || empty($model['id_section'])) { $this->updateSQL('products', ['figure' => 'O'], ['id_model' => $model['id']]); return false; } $data = $this->generateModelTitle($model); $this->updateSQL( 'abra_models', [ 'short_title' => $data['short_title'], 'id_serie' => $model['id_serie'], ], ['id_abra' => $model['id']] ); return true; } public function generateModelTitle($values) { // Kategorie + Vyrobce + Rada + Doplnek modelu $attributes = [ $values['producer_name'], $values['serie_name'], $values['complement_model_name'], ]; $short_title = join(' ', array_filter($attributes)); $title = join(' ', [$values['section_name'], $short_title]); return [ 'title' => $title, 'short_title' => $short_title, ]; } public function finalizeSync() { $this->updateScheduledProducts(); $this->hideIncompleteProducts(); } /** * Returns how specific current message is * Returns one of: * null - message is not for us * 1 - message is global * 2 - message is for our language * 3 - message is for our site * 4 - message is for our language + site. * * @return int|null */ public function getMessageLevel() { // Site does not match, not message for us if ($this->site and $this->site != $this->settings['web']) { return null; } // Language does not match, not message for us $supportedLanguages = $this->languageContext->getSupported(); if ($this->lang && !isset($supportedLanguages[$this->lang])) { return null; } if ($this->site and $this->lang) { return 4; } if ($this->site) { return 3; } if ($this->lang) { return 2; } return 1; } public function handleTranslatedText($object, $value) { $level = $this->getMessageLevel(); if ($value == 'DEL') { $level = 0; $value = ''; } // Update level $this->updateSQL($object['table'], [$object['field'] => $value/* "{$object['field']}_level" => $level */], $object['where']); } public function log($data) { // Do not log in development if (isDevelopment()) { return; } /** @var Symfony\Bridge\Monolog\Logger $logger */ $logger = ServiceContainer::getService('logger'); $logger->notice('Abra Elnino Log: '.print_r($data, true)); } protected function collectUpdatedProducts() { // Schedule serie update on brand update if ($brand = $this->getDelayedUpdate('brand')) { $series = sqlQueryBuilder()->select('id_serie as id') ->from('abra_series') ->where(\Query\Operator::inIntArray(array_keys($brand), 'id_producer')) ->execute(); foreach ($series as $serie) { $this->scheduleProductTitleUpdate(null, null, $serie['id']); } $this->deleteDelayedUpdate($brand); } // Schedule product update on serie update if ($serie = $this->getDelayedUpdate('serie')) { $products = sqlQueryBuilder()->select('p.id'); if (isset(static::$abraKozaV2)) { $products->addSelect('p.id_model'); } $products = $products ->from('abra_series', 'asr') ->join('asr', 'abra_models', 'am', 'am.id_serie = asr.id_abra') ->join('am', 'products', 'p', 'am.id_abra = p.id_model') ->where(\Query\Operator::inIntArray(array_keys($serie), 'asr.id_serie')) ->execute(); foreach ($products as $product) { $this->scheduleProductTitleUpdate($product['id'], null, null, null, null, $product['id_model'] ?? null); } $this->deleteDelayedUpdate($serie); } // Schedule product update on section update if ($section = $this->getDelayedUpdate('section')) { $products = sqlQueryBuilder()->select('p.id') ->fromProducts() ->joinSectionsOnProducts() ->where(\Query\Operator::inIntArray(array_keys($section), 'ps.id_section')) ->execute(); foreach ($products as $product) { $this->scheduleProductTitleUpdate($product['id']); } $this->deleteDelayedUpdate($section); } // Schedule product update on complement_model update if ($complementModel = $this->getDelayedUpdate('complement_model')) { $models = sqlQueryBuilder()->select('id_abra') ->from('abra_models') ->where(\Query\Operator::inStringArray(array_keys($complementModel), 'id_complement_model')) ->execute(); foreach ($models as $model) { $this->scheduleProductTitleUpdate(null, null, null, null, null, $model['id_abra']); } $this->deleteDelayedUpdate($complementModel); } // Schedule product update on model update if ($model = $this->getDelayedUpdate('model')) { $products = sqlQueryBuilder()->select('p.id') ->fromProducts() ->where(\Query\Operator::inIntArray(array_keys($model), 'p.id_model')) ->execute(); foreach ($products as $product) { $this->scheduleProductTitleUpdate($product['id']); } if (!isset(static::$abraKozaV2)) { $this->deleteDelayedUpdate($model); } } // Schedule product update on complement_type update if ($complementType = $this->getDelayedUpdate('complement_type')) { $products = sqlQueryBuilder()->select('id') ->from('products') ->where(\Query\Operator::inIntArray(array_keys($complementType), 'id_complement_type')) ->execute(); foreach ($products as $product) { $this->scheduleProductTitleUpdate($product['id']); } $this->deleteDelayedUpdate($complementType); } } public function getCampaignsNames($flags) { $cfg = Config::get(); if (!is_array($flags)) { $flags = explodeFlags($flags); } $campaign = []; foreach ($cfg['Products']['Flags'] as $flag => $name) { if (array_key_exists($flag, $flags)) { $campaign[] = $name['singular']; } } return $campaign; } protected function scheduleUpdateCommon($id, $force_product = false) { if ($this->main_table == 'products') { $this->scheduleProductTitleUpdate($id); } else { $idProduct = null; if ($force_product) { $idProduct = $this->selectSQL('products_variations', ['id' => $id], ['id_product'])->fetchColumn(); } $this->scheduleProductTitleUpdate($idProduct, $id); } } public function recalcInStore($spec = null) { /* * UPDATE `products` JOIN ( SELECT p.id, COALESCE(SUM(GREATEST(pv.in_store, 0)), p.in_store) in_store FROM `products` p LEFT JOIN `products_variations` pv ON pv.id_product=p.id AND pv.figure = 'Y' GROUP BY p.id ) AS sums ON products.id=sums.id SET products.in_store=sums.in_store WHERE products.in_store != sums.in_store */ $sums = sqlQueryBuilder()->select('p.id', 'COALESCE(SUM(GREATEST(pv.in_store, 0)), p.in_store) in_store') ->from('products', 'p') ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product=p.id AND pv.figure = \'Y\'') ->groupBy('p.id'); if ($spec) { $sums->andWhere($spec); } sqlQuery("UPDATE products JOIN ({$sums->getSQL()}) AS sums ON products.id=sums.id SET products.in_store=sums.in_store WHERE products.in_store != sums.in_store", $sums->getParameters(), $sums->getParameterTypes()); } public function sync_secondary_assortment_group($code, $field, $value) { $column = static::$SECONDARY_ASSORTMENT_GROUP_FIELDS[$field]; $secondaryAssortmentGroup = sqlQueryBuilder()->select('*')->from('abra_secondary_assortment_group') ->where(\Query\Operator::equals(['id_abra' => $code])) ->setMaxResults(1)->execute()->fetch(); if (!$secondaryAssortmentGroup) { $this->insertSQL('abra_secondary_assortment_group', ['id_abra' => $code]); } $id_product = $secondaryAssortmentGroup['id_product'] ?? null; switch ($column) { case 'abra_section_id': $this->updateSQL('abra_secondary_assortment_group', ['id_abra_section' => $value], ['id_abra' => $code]); break; case 'abra_product_id': $id_product = returnSQLResult('SELECT ap.id_product FROM abra_products ap WHERE ap.id_abra=:id_abra', ['id_abra' => $value]); $this->updateSQL('abra_secondary_assortment_group', ['id_product' => $id_product], ['id_abra' => $code]); break; case 'delete': $this->deleteSQL('abra_secondary_assortment_group', ['id_abra' => $code]); break; } if (!empty($id_product) && $id_product !== '0') { $this->scheduleProductTitleUpdate($id_product); } } public function sync_scent($code, $field, $value) { // var_dump('sc: ' . $code . $field . $value); $row = sqlQueryBuilder()->select('id_abra, name')->from('abra_scents') ->where('id_abra=:code') ->setParameter('code', $code) ->setMaxResults(1)->execute()->fetch(); if (!$row) { // insert abra_scent $this->insertSQL('abra_scents', ['id_abra' => $code, 'name' => $value]); } $column = static::$SCENT_FIELDS[$field]; if ($this->isTranslationsSync()) { if (!$this->translationFieldSupported($field)) { return; } $this->saveTranslationObject(AbraScentsTranslation::class, ['id_object' => $code], [$column => $value]); return; } switch ($column) { case 'name': // update abra_scent $this->updateSQL('abra_scents', ['name' => $value], ['id_abra' => $code]); break; case 'delete': // remove abra_scent sqlQueryBuilder()->delete('abra_scents')->where('id_abra=:id')->setParameter('id', $code)->execute(); echo 'Scent removed; '; break; } } public function sync_product_scent($code, $field, $value) { $row = sqlQueryBuilder()->select('*')->from('abra_products_scents') ->where('id_abra=:code') ->setParameter('code', $code) ->setMaxResults(1)->execute()->fetch(); try { switch ($field) { case 'Model_ID': if (!$value) { return; } $this->updateProductsScents($code, 'id_model', $value, $row); break; case 'Scent_ID': $this->updateProductsScents($code, 'id_scent', $value, $row); break; case 'Element': $this->updateProductsScents($code, 'element', $value, $row); break; case 'Part': $this->updateProductsScents($code, 'part', $value, $row); break; case 'ONDELETE': if ($row && $row['id_scent'] > 0 && $row['id_model'] > 0) { // remove abra_products_scents $this->deleteSQL('abra_products_scents', ['id_abra' => $row['id_abra']]); } break; } } catch (Exception $e) { // kdyz je slozka vune, ktera nema preklad, neprijde jeji objekt -> cizi klic selze. $sentry = getRaven(); $sentry->captureException($e); } } public function updateProductsScents($code, $fieldName, $value, &$row) { // update field $conn = sqlGetConnection(); try { $conn->beginTransaction(); if ($row) { $this->updateSQL('abra_products_scents', [$fieldName => $value], ['id_abra' => $code]); } else { $this->insertSQL('abra_products_scents', [$fieldName => $value, 'id_abra' => $code]); $row = true; } $conn->commit(); } catch (\Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException $e) { $conn->rollBack(); if ($fieldName == 'id_scent') { $this->insertSQL('abra_scents', ['id_abra' => $value, 'name' => '']); return $this->updateProductsScents($code, $fieldName, $value, $row); } else { $sentry = getRaven(); $sentry->captureException($e); } return; } $id_model = returnSQLResult('SELECT id_model FROM abra_products_scents WHERE id_abra=:code', ['code' => $code]); if ($id_model) { $this->scheduleProductTitleUpdate(null, null, null, null, null, $id_model); } } protected function createRewrite($rewriteType, $relatedId, array $urlParams) { if (findModule(Modules::DB_REWRITE)) { if (!$relatedId) { return; } $languageSwitcher = ServiceContainer::getService(LanguageSwitcher::class); $legacyUrlGenerator = ServiceContainer::getService(LegacyUrlGenerator::class); $rewrite = ServiceContainer::getService(\KupShop\RewriteBundle\Util\Rewrite::class); foreach ($this->languageContext->getSupported() as $language) { $url = $languageSwitcher->switchLanguage( $language->getId(), function () use ($legacyUrlGenerator, $urlParams) { return $legacyUrlGenerator->generate($urlParams); } ); $rewrite->addRewrite($url, $rewriteType, $relatedId); } } } private $blockUtil; protected function getBlockUtil(): KupShop\ContentBundle\Util\Block { if ($this->blockUtil) { return $this->blockUtil; } return $this->blockUtil = ServiceContainer::getService(\KupShop\ContentBundle\Util\Block::class); } public function getOrdersToSync(): \Query\QueryBuilder { return sqlQueryBuilder() ->select('o.id') ->from('orders', 'o') ->leftJoin('o', 'abra_orders', 'ao', 'o.id=ao.id_order') ->where('ao.id_abra IS NULL AND o.date_created < DATE_SUB(NOW(), INTERVAL 30 SECOND)') ->andWhere(Operator::not(Operator::equals(['o.source' => 'returns']))); } } if (empty($subclass)) { class AbraElnino extends AbraElninoBase { } }