982 lines
42 KiB
PHP
982 lines
42 KiB
PHP
<?php
|
|
|
|
use KupShop\I18nBundle\Translations\VariationsLabelsTranslation;
|
|
use KupShop\I18nBundle\Translations\VariationsTranslation;
|
|
use KupShop\I18nBundle\Translations\VariationsValuesTranslation;
|
|
use KupShop\KupShopBundle\Context\PriceLevelContext;
|
|
use KupShop\KupShopBundle\Context\VatContext;
|
|
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
|
use KupShop\KupShopBundle\Util\Contexts;
|
|
use KupShop\KupShopBundle\Util\Price\PriceLevelPrice;
|
|
use KupShop\KupShopBundle\Util\Price\PriceUtil;
|
|
use KupShop\KupShopBundle\Util\Price\ProductPrice;
|
|
use KupShop\KupShopBundle\Wrapper\PriceWrapper;
|
|
use KupShop\PricelistBundle\Context\PricelistContext;
|
|
use Query\Operator;
|
|
use Query\Translation;
|
|
use Query\Variation;
|
|
|
|
class VariationsBase
|
|
{
|
|
// ////////////////////////////////////////////////////////////////////////////////////////////////////////////
|
|
// PUBLIC METHODS
|
|
// ////////////////////////////////////////////////////////////////////////////////////////////////////////////
|
|
|
|
public static $variationFields = 'pv.id, pv.delivery_time, COALESCE(pv.price, p.price) as price,
|
|
pv.price as variation_price, pv.title, pv.ean, pv.delivery_time as delivery_time_raw, pv.figure as variation_figure,
|
|
pv.width, pv.height, pv.depth, pv.data, pv.code as variationCode';
|
|
|
|
protected static function getProductVariationsQueryBuilder($id_product)
|
|
{
|
|
return $qb = sqlQueryBuilder()->select(static::$variationFields)
|
|
->from('products_variations', 'pv')
|
|
->join('pv', 'products', 'p', 'p.id = pv.id_product')
|
|
->andWhere('pv.id_product = :id_product')->setParameter('id_product', $id_product);
|
|
}
|
|
|
|
public static function getProductVariations($id_product, $in_store = false, $figure = true)
|
|
{
|
|
global $cfg;
|
|
|
|
$qb = static::getProductVariationsQueryBuilder($id_product);
|
|
|
|
// TODO prepsat na ProductList a nebude toto potreba
|
|
$inStoreField = \Query\Product::getInStoreField(true, $qb);
|
|
if (findModule('products', 'showMax')) {
|
|
$qb->addSelect('LEAST('.$inStoreField.', COALESCE(pv.in_store_show_max, p.in_store_show_max, '.$cfg['Modules']['products']['showMax'].')) in_store');
|
|
} else {
|
|
$qb->addSelect($inStoreField.' as in_store');
|
|
}
|
|
|
|
if (findModule('products_variations', 'variationCode')) {
|
|
$qb->addSelect('pv.code');
|
|
}
|
|
|
|
if (findModule('products', 'price_buy')) {
|
|
$qb->addSelect('pv.price_buy');
|
|
}
|
|
|
|
if (findModule('products', 'price_common')) {
|
|
$qb->addSelect('COALESCE(pv.price_common, p.price_common) price_common');
|
|
}
|
|
|
|
if (findModule('products', 'weight')) {
|
|
$qb->addSelect('COALESCE(pv.weight, p.weight) weight');
|
|
}
|
|
|
|
if ($figure) {
|
|
$qb->andWhere(\Query\Variation::isVisible());
|
|
}
|
|
|
|
if ($in_store) {
|
|
$qb->andWhere($inStoreField.' > 0');
|
|
}
|
|
|
|
if (findModule(\Modules::BONUS_PROGRAM)) {
|
|
$qb->addSelect('COALESCE(pv.bonus_points, p.bonus_points) as bonus_points');
|
|
}
|
|
|
|
if (findModule(\Modules::PRICE_HISTORY)) {
|
|
$qb->addSelect('COALESCE(pv.price_for_discount, p.price_for_discount) as price_for_discount');
|
|
}
|
|
|
|
$id_product = intval($id_product);
|
|
$variations = [];
|
|
|
|
$SQL = sqlQueryBuilder()->select('id_label')->from('products_variations_choices_categorization')
|
|
->where('id_product=:id_product')->setParameter('id_product', $id_product)
|
|
->orderBy('list_order', 'ASC')->execute();
|
|
|
|
if (sqlNumRows($SQL) <= 0) {
|
|
return $variations;
|
|
}
|
|
|
|
$index = 0;
|
|
while (($row = sqlFetchAssoc($SQL)) !== false) {
|
|
$id = $row['id_label'];
|
|
$qb->leftJoin('pv', 'products_variations_combination', 'pvc'.$index, 'pv.id = pvc'.$index.'.id_variation AND pvc'.$index.'.id_label='.$id.'')
|
|
->leftJoin('pvc'.$index, 'products_variations_choices_values', 'pvcv'.$index, 'pvcv'.$index.'.id=pvc'.$index.'.id_value')
|
|
->addOrderBy('pvcv'.$index.'.sort', 'ASC');
|
|
|
|
$index++;
|
|
}
|
|
|
|
// join pricelists
|
|
if (findModule(Modules::PRICELISTS)) {
|
|
$pricelistContext = ServiceContainer::getService(\KupShop\PricelistBundle\Context\PricelistContext::class);
|
|
if ($pricelistContext->getActiveId()) {
|
|
$qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelist($pricelistContext->getActiveId()));
|
|
}
|
|
}
|
|
|
|
$qb->andWhere(Translation::coalesceTranslatedFields(VariationsTranslation::class));
|
|
|
|
$SQL = $qb->execute();
|
|
|
|
$found = sqlNumRows($SQL);
|
|
|
|
$qbProduct = sqlQueryBuilder()->select('p.discount')
|
|
->from('products', 'p')
|
|
->addSelect(\Query\Product::withVat())
|
|
->andWhere('p.id = :id_product')->setParameter('id_product', $id_product)
|
|
->execute();
|
|
|
|
$product = sqlFetchAssoc($qbProduct);
|
|
|
|
if ($found > 0) {
|
|
$COMBINATIONS = self::getProductCombinations($id_product);
|
|
}
|
|
|
|
// Variations photos
|
|
$varPhotos = [];
|
|
if (findModule('products_variations_photos')) {
|
|
$varPhotosQb = sqlQueryBuilder()
|
|
->select('pp.id_variation, ph.id, ph.date_update, pp.show_in_lead')
|
|
->from('photos_products_relation', 'pp')
|
|
->join('pp', 'photos', 'ph', 'ph.id = pp.id_photo')
|
|
->where(Operator::equalsNullable(['pp.id_product' => $id_product, 'pp.id_variation IS NOT NULL']));
|
|
|
|
if (findModule(Modules::VIDEOS)) {
|
|
$varPhotosQb->addSelect('id_cdn id_video')
|
|
->leftJoin('ph', 'videos', 'v', 'v.id_photo = ph.id');
|
|
}
|
|
|
|
foreach ($varPhotosQb->execute() as $photo) {
|
|
$idVar = $photo['id_variation'];
|
|
if (empty($varPhotos[$idVar])) {
|
|
$varPhotos[$idVar] = [];
|
|
}
|
|
$varPhotos[$idVar][] = array_merge(getImage($photo['id'], null, null, 'product_detail', '', strtotime($photo['date_update'])), [
|
|
'id_photo' => $photo['id'],
|
|
'id_video' => $photo['id_video'] ?? null,
|
|
'show_in_lead' => $photo['show_in_lead'],
|
|
]);
|
|
}
|
|
}
|
|
|
|
foreach ($SQL as $row) {
|
|
$id_variation = $row['id'];
|
|
|
|
$row['discount'] = $product['discount'];
|
|
$row['original_vat'] = $product['original_vat'] ?? $product['vat'];
|
|
|
|
if (!empty($row['ean'])) {
|
|
$row['ean'] = formatEAN($row['ean']);
|
|
}
|
|
|
|
$currencyContext = ServiceContainer::getService(\KupShop\KupShopBundle\Context\CurrencyContext::class);
|
|
|
|
$priceForDiscountCurrency = $currencyContext->getDefault();
|
|
|
|
// create productPrice
|
|
$row['productPrice'] = new ProductPrice(
|
|
toDecimal($row['price']),
|
|
$currencyContext->getDefault(),
|
|
getVat($product['vat']),
|
|
$product['discount']
|
|
);
|
|
|
|
// set pricelist price
|
|
if (findModule(Modules::PRICELISTS) && isset($row['pricelist_price'])) {
|
|
$priceConverter = ServiceContainer::getService(\KupShop\I18nBundle\Util\PriceConverter::class);
|
|
|
|
$row['pricelist_currency'] = $currencyContext->getOrDefault($row['pricelist_currency']);
|
|
|
|
/** @var \KupShop\PricelistBundle\Entity\Pricelist $activePricelist */
|
|
$activePricelist = Contexts::get(PricelistContext::class)->getActive();
|
|
|
|
$row['priceOriginal'] = $row['price'];
|
|
$row['price'] = $row['pricelist_price'];
|
|
|
|
$extend = $activePricelist?->getUseProductDiscount();
|
|
if (($extend && $activePricelist && isset($row['pricelist_discount'])) || (!$extend && $activePricelist)) {
|
|
$row['discount'] = $row['pricelist_discount'];
|
|
} else {
|
|
$row['discount'] = $product['discount'];
|
|
}
|
|
|
|
// price convert to CZK
|
|
$originalProductPrice = $row['productPrice'];
|
|
// replace productPrice with PriceListPrice
|
|
$row['productPrice'] = new \KupShop\PricelistBundle\Util\Price\PriceListPrice(
|
|
toDecimal($row['price']),
|
|
$row['pricelist_currency'],
|
|
getVat($product['vat']),
|
|
$row['discount']
|
|
);
|
|
$row['productPrice']->setOriginalPrice($originalProductPrice);
|
|
|
|
$coefficient = $activePricelist?->getCoefficient();
|
|
if ($coefficient && in_array($row['price_source'], ['p', 'pv'])) {
|
|
/* v pripade koeficientu na ceniku je aplikovan i na originalPrice */
|
|
$row['productPrice']->applyCoefficient(toDecimal($coefficient));
|
|
if (!empty($row['price_for_discount'])) {
|
|
$row['price_for_discount'] *= $coefficient;
|
|
}
|
|
}
|
|
|
|
$row['price'] = $priceConverter->convert($row['pricelist_currency'], $currencyContext->getDefaultId(), $row['price']);
|
|
|
|
// prerazim CPS od produktu CPS z ceniku, pokud ji mam dostupnou
|
|
if (!empty($row['pricelist_price_history']) && array_key_exists('pricelist_price_for_discount', $row)) {
|
|
$row['price_for_discount'] = $row['pricelist_price_for_discount'];
|
|
$priceForDiscountCurrency = $row['pricelist_currency'];
|
|
}
|
|
}
|
|
|
|
$row['price_array'] = PriceWrapper::wrap($row['productPrice']);
|
|
self::getData($row);
|
|
$variation = $row;
|
|
|
|
if (findModule(Modules::PRICELISTS) && isset($row['pricelist_price']) && isset($row['priceOriginal'])) {
|
|
$priceOriginal = new \KupShop\KupShopBundle\Util\Price\Price(toDecimal($row['priceOriginal']), $currencyContext->getDefault(), getVat($product['vat']));
|
|
$variation['priceOriginal'] = PriceWrapper::wrap($priceOriginal);
|
|
}
|
|
|
|
$variation['price'] = formatCustomerPrice($variation['price'], $row['discount'], getVat($product['vat']), $id_product);
|
|
|
|
if (array_key_exists('price_buy', $variation)) {
|
|
$variation['price_buy'] = formatPrice(applyCurrency($variation['price_buy']), getVat($product['vat']));
|
|
}
|
|
|
|
if (array_key_exists('price_for_discount', $variation)) {
|
|
$price_for_discount = toDecimal($variation['price_for_discount']);
|
|
$price_for_discount = new ProductPrice($price_for_discount, $priceForDiscountCurrency, $variation['productPrice']->getVat());
|
|
$variation['price_for_discount'] = PriceWrapper::wrap($price_for_discount);
|
|
}
|
|
|
|
if (array_key_exists('price_common', $variation) && ($variation['price_common'] > 0)) {
|
|
$original_vat = $product['original_vat'] ?? $product['vat'];
|
|
$price_common = toDecimal($variation['price_common'])->removeVat(getVat($original_vat));
|
|
$price_common = new ProductPrice($price_common, $currencyContext->getDefault(), $variation['productPrice']->getVat());
|
|
$variation['priceCommon'] = PriceWrapper::wrap($price_common);
|
|
}
|
|
|
|
// replace productPrice with PriceLevelPrice
|
|
if ($priceLevel = Contexts::get(PriceLevelContext::class)->getActive()) {
|
|
$variation['productPrice'] = new PriceLevelPrice($row['productPrice']);
|
|
$priceLevelDiscount = $priceLevel->getDiscount($id_product, null, -1, $product);
|
|
$variation['productPrice']->setPricelevelDiscount($priceLevelDiscount);
|
|
}
|
|
|
|
if (findModule(Modules::BONUS_PROGRAM)) {
|
|
$bonus_points = (is_null($row['bonus_points']) ? null : toDecimal($row['bonus_points']));
|
|
$variation['bonus_points'] = $bonus_points;
|
|
}
|
|
|
|
$variation['combinations'] = $COMBINATIONS[$id_variation];
|
|
|
|
// wrap productPrice - cuz of templates
|
|
$variation['productPrice'] = PriceWrapper::wrap($variation['productPrice']);
|
|
|
|
Variations::prepareDeliveryText($id_product, $variation);
|
|
|
|
$variation['in_store'] = max(0, floatval($variation['in_store']));
|
|
|
|
if (findModule('products_variations_photos')) {
|
|
if (!empty($varPhotos[$id_variation])) {
|
|
$variation['photos'] = $varPhotos[$id_variation];
|
|
} else {
|
|
$variation['photos'] = false;
|
|
}
|
|
}
|
|
|
|
static::recalculatePricesVatFromPriceWithVat($variation);
|
|
|
|
$variations[$id_variation] = $variation;
|
|
}
|
|
sqlFreeResult($SQL);
|
|
|
|
return $variations;
|
|
}
|
|
|
|
public static function recalculatePricesVatFromPriceWithVat(array &$variation): void
|
|
{
|
|
if (!PriceUtil::isProductPricesVatFromTop()) {
|
|
return;
|
|
}
|
|
|
|
$fields = ['priceOriginal', 'productPrice', 'price_for_discount'];
|
|
|
|
$originalVatId = $variation['original_vat'] ?? null;
|
|
|
|
$originalVat = (float) getVat($originalVatId);
|
|
$vatContext = Contexts::get(VatContext::class);
|
|
if ($vatContext->getActive() == $vatContext::NO_VAT) {
|
|
$originalVat = $vatContext->getVat($originalVatId)['vat'];
|
|
}
|
|
|
|
foreach ($fields as $field) {
|
|
if (!isset($variation[$field])) {
|
|
continue;
|
|
}
|
|
|
|
// PriceLevelPrice se uvnitr pocita specialne :/
|
|
if ($variation[$field] instanceof PriceLevelPrice
|
|
|| ($variation[$field] instanceof PriceWrapper && $variation[$field]->getObject() instanceof PriceLevelPrice)) {
|
|
$variation[$field]->setOriginalPrice(
|
|
PriceUtil::recalculatePriceVatFromPriceWithVat(
|
|
$variation[$field]->getOriginalPrice(),
|
|
$originalVat,
|
|
$variation['vat'] ?? null
|
|
)
|
|
);
|
|
}
|
|
|
|
// prepocitam cenu tak, aby DPH bylo pocitano ze shora
|
|
$variation[$field] = PriceUtil::recalculatePriceVatFromPriceWithVat($variation[$field], $originalVat, $variation['vat'] ?? null);
|
|
}
|
|
|
|
$variation['price_array'] = $variation['productPrice'];
|
|
}
|
|
|
|
/**
|
|
* @return array|mixed
|
|
*/
|
|
public static function getData(&$variation)
|
|
{
|
|
if (empty($variation['data'])) {
|
|
$variation['data'] = [];
|
|
} else {
|
|
$variation['data'] = json_decode($variation['data'], true);
|
|
}
|
|
}
|
|
|
|
public static function getProductLabels($id_product)
|
|
{
|
|
$qb = sqlQueryBuilder()
|
|
->select('pvcl.id, pvcl.label')
|
|
->from('products_variations_choices_categorization', 'pvcc')
|
|
->join('pvcc', 'products_variations_choices_labels', 'pvcl', 'pvcc.id_label=pvcl.id')
|
|
->where(Operator::equals(['pvcc.id_product' => $id_product]))
|
|
->orderBy('pvcc.list_order');
|
|
|
|
$qb->andWhere(Translation::coalesceTranslatedFields(VariationsLabelsTranslation::class));
|
|
|
|
$productLabels = $qb->execute()
|
|
->fetchAll();
|
|
|
|
$labels = [];
|
|
foreach ($productLabels as $label) {
|
|
$iter = $label['id'];
|
|
$labels[$iter] = [
|
|
'id' => $iter,
|
|
'label' => $label['label'],
|
|
'values' => [],
|
|
'codes' => [],
|
|
];
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('pvcv.id, pvcv.value, pvcv.code, pvcv.data')
|
|
->from('products_variations', 'pv')
|
|
->join('pv', 'products_variations_combination', 'pvc', 'pvc.id_variation = pv.id')
|
|
->join('pvc', 'products_variations_choices_values', 'pvcv', 'pvcv.id = pvc.id_value')
|
|
->where(Operator::equals(['pv.id_product' => $id_product, 'pvcv.id_label' => $iter]))
|
|
->andWhere(Variation::isVisible())
|
|
->orderBy('pvcv.sort')
|
|
->addOrderBy('pvcv.value + 0 ');
|
|
|
|
$qb->andWhere(Translation::coalesceTranslatedFields(VariationsValuesTranslation::class));
|
|
|
|
foreach ($qb->execute() as $labelValue) {
|
|
$labels[$iter]['values'][$labelValue['id']] = $labelValue['value'];
|
|
$labels[$iter]['codes'][$labelValue['id']] = $labelValue['code'];
|
|
$labels[$iter]['data'][$labelValue['id']] = json_decode($labelValue['data'] ?: '', true) ?? [];
|
|
}
|
|
}
|
|
|
|
return $labels;
|
|
}
|
|
|
|
public static function fillInProductTitle($id_variation, $title_start)
|
|
{
|
|
$title = $title_start;
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('pv.title')
|
|
->from('products_variations', 'pv')
|
|
->where(Operator::equals(['pv.id' => $id_variation]));
|
|
|
|
$qb->andWhere(Translation::coalesceTranslatedFields(VariationsTranslation::class));
|
|
|
|
$variationTitle = $qb->execute()->fetchAssociative();
|
|
$variationTitle = $variationTitle['title'] ?? '';
|
|
|
|
$title .= ' ('.$variationTitle.')';
|
|
|
|
return $title;
|
|
}
|
|
|
|
/**
|
|
* @param $price Decimal
|
|
*
|
|
* @return Decimal
|
|
*/
|
|
public static function getCustomPrice($id_variation, $price)
|
|
{
|
|
if (empty($id_variation)) {
|
|
return $price;
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('pv.price')
|
|
->addSelect(\Query\Product::withVat())
|
|
->from('products_variations', 'pv')
|
|
->join('pv', 'products', 'p', 'p.id = pv.id_product')
|
|
->andWhere(Operator::equals(['pv.id' => $id_variation]));
|
|
|
|
if (findModule(Modules::PRICELISTS)) {
|
|
$pricelistContext = ServiceContainer::getService(\KupShop\PricelistBundle\Context\PricelistContext::class);
|
|
$qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelistOnVariation($pricelistContext->getActiveId()));
|
|
}
|
|
|
|
$data = $qb->execute()->fetch();
|
|
|
|
$result = toDecimal($data['price']);
|
|
|
|
if (findModule(Modules::PRICELISTS)) {
|
|
$priceConverter = ServiceContainer::getService(\KupShop\I18nBundle\Util\PriceConverter::class);
|
|
$currencyContext = ServiceContainer::getService(\KupShop\KupShopBundle\Context\CurrencyContext::class);
|
|
|
|
if (!$data['pricelist_currency']) {
|
|
$data['pricelist_currency'] = $currencyContext->getDefaultId();
|
|
}
|
|
|
|
/** @var \KupShop\PricelistBundle\Entity\Pricelist $activePricelist */
|
|
$activePricelist = Contexts::get(PricelistContext::class)->getActive();
|
|
|
|
$coefficient = $activePricelist?->getCoefficient();
|
|
if ($coefficient && in_array($data['price_source'], ['p', 'pv'])) {
|
|
$data['pricelist_price'] *= $coefficient;
|
|
}
|
|
|
|
$result = $priceConverter->convert($data['pricelist_currency'], $currencyContext->getDefaultId(), $data['pricelist_price']);
|
|
}
|
|
|
|
$originalVatId = $data['original_vat'] ?? $data['vat'];
|
|
|
|
if ($result->isPositive()) {
|
|
if (PriceUtil::isProductPricesVatFromTop()) {
|
|
$result = $result->addVat(getVat($originalVatId))->removeVat(getVat($data['vat']));
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
return $price;
|
|
}
|
|
|
|
// ////////////////////////////////////////////////////////////////////////////////////////////////////////////
|
|
// PRIVATE METHODS
|
|
// ////////////////////////////////////////////////////////////////////////////////////////////////////////////
|
|
|
|
private static function getProductCombinations($id_product)
|
|
{
|
|
// nejdrive nacist vsechy kombinace k variantam
|
|
$COMBINATIONS = [];
|
|
|
|
$SQL = sqlQuery('SELECT pvc.id_variation, pvc.id_label, pvc.id_value, pvcc.list_order
|
|
FROM '.getTableName('products_variations_combination').' AS pvc
|
|
JOIN '.getTableName('products_variations').' AS pv ON pv.id=pvc.id_variation
|
|
LEFT JOIN '.getTableName('products_variations_choices_categorization').' AS pvcc ON pvc.id_label=pvcc.id_label AND pv.id_product=pvcc.id_product
|
|
WHERE pv.id_product='.intval($id_product).'
|
|
ORDER BY pvcc.list_order ASC');
|
|
|
|
foreach ($SQL as $row) {
|
|
$IDvariation = $row['id_variation'];
|
|
if (!isset($COMBINATIONS[$IDvariation])) {
|
|
$COMBINATIONS[$IDvariation] = [];
|
|
}
|
|
|
|
$COMBINATIONS[$IDvariation][] = [
|
|
'id_label' => $row['id_label'],
|
|
'id_value' => $row['id_value'],
|
|
'order' => $row['list_order'],
|
|
];
|
|
}
|
|
|
|
return $COMBINATIONS;
|
|
}
|
|
|
|
public static function updateTitle($id_variation = null, $id_value = null, $id_label = null)
|
|
{
|
|
$where = '1';
|
|
if ($id_variation) {
|
|
$where = "pv.id = {$id_variation}";
|
|
} elseif ($id_value) {
|
|
$where = "pvc2.id_value = {$id_value}";
|
|
} elseif ($id_label) {
|
|
$where = "pvc2.id_variation = {$id_label}";
|
|
}
|
|
|
|
$query = 'UPDATE '.getTableName('products_variations')." pv
|
|
LEFT JOIN products_variations_combination pvc2 ON pv.id=pvc2.id_variation
|
|
SET title=(
|
|
SELECT GROUP_CONCAT(CONCAT_WS(': ', label, value)
|
|
ORDER BY list_order ASC
|
|
SEPARATOR ', ') as title
|
|
FROM ".getTableName('products_variations_combination').' pvc
|
|
JOIN'.getTableName('products_variations_choices_values').' pvcv ON pvcv.id=pvc.id_value
|
|
JOIN '.getTableName('products_variations_choices_labels').'pvcl ON pvcl.id=pvc.id_label
|
|
JOIN '.getTableName('products_variations_choices_categorization').'pvcc ON pvcc.id_label=pvc.id_label
|
|
WHERE pv.id=pvc.id_variation AND pvcc.id_product=pv.id_product
|
|
GROUP BY pv.id)
|
|
WHERE '.$where;
|
|
|
|
if (findModule(Modules::TRANSLATIONS) && findModule(Modules::PRODUCTS_VARIATIONS)) {
|
|
$languageContext = ServiceContainer::getService(\KupShop\KupShopBundle\Context\LanguageContext::class);
|
|
|
|
foreach ($languageContext->getSupported() as $language) {
|
|
if ($languageContext->getDefaultId() == $language->getId()) {
|
|
continue;
|
|
}
|
|
|
|
// create records in products_variations_translations
|
|
sqlQuery('INSERT INTO products_variations_translations (id_products_variation, id_language)
|
|
SELECT
|
|
pv.id as id_products_variation,
|
|
:lang as id_language
|
|
FROM products_variations pv
|
|
LEFT JOIN products_variations_combination pvc2 ON pv.id=pvc2.id_variation
|
|
LEFT JOIN products_variations_translations pvt ON pv.id = pvt.id_products_variation AND pvt.id_language = :lang
|
|
WHERE pvt.id IS NULL AND '.$where.'
|
|
GROUP BY pv.id;', ['lang' => $language->getId()]);
|
|
|
|
// update titles in products_variations_translations
|
|
$qb = sqlQueryBuilder()
|
|
->select('pv.id, pvt.id_language, pvt.id as tid,
|
|
GROUP_CONCAT(CONCAT_WS(": ", COALESCE(pvclt.label, pvcl.label), COALESCE(pvcvt.value, pvcv.value))
|
|
ORDER BY list_order ASC SEPARATOR ", ") as title')
|
|
->from('products_variations_translations', 'pvt')
|
|
->join('pvt', 'products_variations', 'pv', 'pv.id = pvt.id_products_variation')
|
|
->join('pv', 'products_variations_combination', 'pvc', 'pvc.id_variation = pv.id')
|
|
->join('pvc', 'products_variations_choices_values', 'pvcv', 'pvcv.id = pvc.id_value')
|
|
->join('pvc', 'products_variations_choices_labels', 'pvcl', 'pvcl.id=pvc.id_label')
|
|
->join('pvc', 'products_variations_choices_categorization', 'pvcc', 'pvcc.id_label=pvc.id_label')
|
|
->leftJoin('pvcv', 'products_variations_choices_values_translations', 'pvcvt', 'pvcvt.id_products_variations_choices_value = pvcv.id AND pvcvt.id_language = pvt.id_language')
|
|
->leftJoin('pvcl', 'products_variations_choices_labels_translations', 'pvclt', 'pvclt.id_products_variations_choices_label = pvcl.id AND pvclt.id_language = pvt.id_language')
|
|
->andWhere('pv.id=pvc.id_variation AND pvcc.id_product=pv.id_product')
|
|
->groupBy('pv.id, pvt.id_language');
|
|
|
|
if ($id_variation) {
|
|
$qb->andWhere(Operator::equals(['pv.id' => $id_variation]));
|
|
} elseif ($id_value) {
|
|
$qb->andWhere(Operator::equals(['pvc.id_value' => $id_value]));
|
|
} elseif ($id_label) {
|
|
$qb->andWhere(Operator::equals(['pvc.id_variation' => $id_label]));
|
|
}
|
|
|
|
sqlQuery('UPDATE products_variations_translations pvt2
|
|
JOIN products_variations pv ON pv.id = pvt2.id_products_variation
|
|
LEFT JOIN products_variations_combination pvc2 ON pv.id=pvc2.id_variation
|
|
JOIN ('.$qb->getSQL().') t1 ON pvt2.id_products_variation = t1.id AND t1.id_language = :lang
|
|
SET pvt2.title = t1.title
|
|
WHERE pvt2.id = t1.tid AND pvt2.title != t1.title OR pvt2.title IS NULL AND '.$where.';',
|
|
array_merge(['lang' => $language->getId()], $qb->getParameters()),
|
|
$qb->getParameterTypes());
|
|
}
|
|
}
|
|
|
|
sqlQuery($query);
|
|
}
|
|
|
|
public static function duplicateVariations($fromID, $toID)
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($fromID, $toID) {
|
|
$data = ['id_product_from' => $fromID, 'id_product_to' => $toID];
|
|
|
|
sqlQuery('INSERT IGNORE INTO products_variations_choices_categorization (id_product, id_label, list_order)
|
|
SELECT :id_product_to, id_label, list_order
|
|
FROM products_variations_choices_categorization
|
|
WHERE id_product=:id_product_from', $data);
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('pv.id, GROUP_CONCAT(pvc.id_value ORDER BY pvc.id_value) as valueIds')
|
|
->from('products_variations_combination', 'pvc')
|
|
->join('pvc', 'products_variations', 'pv', 'pvc.id_variation = pv.id')
|
|
->join('pvc', 'products_variations_choices_values', 'pvcv', 'pvc.id_value=pvcv.id')
|
|
->where(Operator::equals(['pv.id_product' => $fromID]))
|
|
->groupBy('pv.id');
|
|
|
|
$last_id = null;
|
|
foreach ($qb->execute() as $variant) {
|
|
$data['id_variation_from'] = $variant['id'];
|
|
|
|
// check that the variation does not exist yet
|
|
$variantExists = sqlQueryBuilder()
|
|
->select('pv.id')
|
|
->from('products_variations', 'pv')
|
|
->join('pv', 'products_variations_combination', 'pvc', 'pvc.id_variation = pv.id')
|
|
->where(Operator::equals(['pv.id_product' => $toID]))
|
|
->groupBy('pv.id')
|
|
->having('GROUP_CONCAT(pvc.id_value ORDER BY pvc.id_value) = :variationValues')
|
|
->setParameter('variationValues', $variant['valueIds'])
|
|
->execute()->fetchOne();
|
|
|
|
if ($variantExists) {
|
|
continue;
|
|
}
|
|
|
|
if ($last_id != $variant['id']) {
|
|
$data['id_variation_to'] = self::duplicateVariationData($data);
|
|
$last_id = $variant['id'];
|
|
}
|
|
|
|
if (findModule('photos') && findModule('products_variations_photos') && getVal('duplicateImages') && getVal('duplicateID')) {
|
|
sqlQuery("INSERT INTO photos_products_relation (id_photo, id_product, id_variation, show_in_lead, active, date_added, position)
|
|
SELECT id_photo, {$toID}, {$data['id_variation_to']}, show_in_lead, active, date_added, position
|
|
FROM photos_products_relation
|
|
WHERE id_product={$fromID} AND id_variation={$data['id_variation_from']}");
|
|
}
|
|
}
|
|
});
|
|
|
|
$prod = new Product($toID);
|
|
$prod->updateInStore();
|
|
$prod->updateDeliveryTime();
|
|
}
|
|
|
|
protected static function duplicateVariationData($data)
|
|
{
|
|
$fields = 'delivery_time, price, figure, ';
|
|
if (findModule(Modules::PRODUCTS, Modules::SUB_NOTE)) {
|
|
$fields .= 'note, ';
|
|
}
|
|
if (findModule(Modules::PRODUCTS, Modules::SUB_WEIGHT)) {
|
|
$fields .= 'weight, ';
|
|
}
|
|
$fields .= 'width, height, depth, data';
|
|
|
|
if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_COMMON)) {
|
|
$fields .= ', price_common';
|
|
}
|
|
|
|
if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
|
|
$fields .= ', price_buy';
|
|
}
|
|
|
|
sqlQuery('INSERT INTO products_variations (id, id_product, title, in_store, '.$fields.')
|
|
SELECT NULL, :id_product_to, title, 0, '.$fields.'
|
|
FROM products_variations WHERE id=:id_variation_from',
|
|
$data);
|
|
|
|
$insertedId = sqlInsertId();
|
|
$data['id_variation_to'] = $insertedId;
|
|
|
|
sqlQuery('INSERT INTO products_variations_combination (id_variation, id_label, id_value)
|
|
SELECT :id_variation_to, id_label, id_value
|
|
FROM products_variations_combination
|
|
WHERE id_variation=:id_variation_from',
|
|
$data);
|
|
|
|
return $insertedId;
|
|
}
|
|
|
|
public static function duplicateVariation($fromIDVariation, $toIDProduct): ?int
|
|
{
|
|
$fromIDProduct = sqlQueryBuilder()->select('id_product')->from('products_variations')
|
|
->where(Operator::equals(['id' => $fromIDVariation]))
|
|
->execute()->fetchOne();
|
|
|
|
$newVariationId = sqlGetConnection()->transactional(function () use ($fromIDVariation, $fromIDProduct, $toIDProduct) {
|
|
$data = ['id_product_from' => $fromIDProduct, 'id_product_to' => $toIDProduct];
|
|
|
|
sqlQuery('INSERT IGNORE INTO products_variations_choices_categorization (id_product, id_label, list_order)
|
|
SELECT :id_product_to, id_label, list_order
|
|
FROM products_variations_choices_categorization
|
|
WHERE id_product=:id_product_from',
|
|
$data);
|
|
|
|
$data['id_variation_from'] = $fromIDVariation;
|
|
$data['id_variation_to'] = self::duplicateVariationData($data);
|
|
|
|
if (findModule('photos') && findModule('products_variations_photos') && getVal('duplicateImages') && getVal('duplicateID')) {
|
|
sqlQuery("INSERT INTO photos_products_relation (id_photo, id_product, id_variation, show_in_lead, active, date_added, position)
|
|
SELECT id_photo, {$toIDProduct}, {$data['id_variation_to']}, show_in_lead, active, date_added, position
|
|
FROM photos_products_relation
|
|
WHERE id_product={$fromIDProduct} AND id_variation={$data['id_variation_from']}");
|
|
}
|
|
|
|
return $data['id_variation_to'];
|
|
});
|
|
|
|
$prod = new Product($toIDProduct);
|
|
$prod->updateInStore();
|
|
$prod->updateDeliveryTime();
|
|
|
|
return $newVariationId;
|
|
}
|
|
|
|
public static function createProductVariation($productId, $parts, $search = true)
|
|
{
|
|
if ($search) {
|
|
// Discover wheter variation already exists
|
|
$query = 'SELECT p.id AS id
|
|
FROM '.getTableName('products_variations').' AS p ';
|
|
$queryWhere = 'WHERE p.id_product='.$productId.' ';
|
|
|
|
foreach ($parts as $variant => $value) {
|
|
$query .= 'LEFT JOIN '.getTableName('products_variations_combination').' AS v'.$variant.' ON p.id=v'.$variant.'.id_variation and v'.$variant.'.id_label='.$variant.'
|
|
LEFT JOIN '.getTableName('products_variations_choices_values').' AS vv'.$variant.' ON v'.$variant.'.id_value=vv'.$variant.'.id ';
|
|
$queryWhere .= ' AND COALESCE(vv'.$variant.'.code, vv'.$variant.".value)='".sqlFormatInput($value)."' ";
|
|
}
|
|
$SQL = sqlQuery($query.$queryWhere);
|
|
|
|
// logError(__FILE__, __LINE__, "Testuju varianty: ".sqlNumRows($SQL)." - ".$query.$queryWhere, true);
|
|
|
|
if (sqlNumRows($SQL) > 0) { // Variation exists
|
|
$row = sqlFetchAssoc($SQL);
|
|
$variation_id = $row['id'];
|
|
}
|
|
}
|
|
if (!isset($variation_id)) {// Variatin NOT exists, create new one
|
|
// Find values IDs
|
|
$valuesQb = sqlQueryBuilder()
|
|
->select('id, COALESCE(value, code) as name, id_label')
|
|
->from('products_variations_choices_values');
|
|
|
|
$specs = ['0=1'];
|
|
foreach ($parts as $variant => $value) {
|
|
$specs[] = Operator::orX(
|
|
Operator::equals(['id_label' => $variant, 'COALESCE(code, value)' => $value]),
|
|
Operator::equals(['id_label' => $variant, 'value' => $value])
|
|
);
|
|
}
|
|
|
|
$valuesQb->andWhere(Operator::orX($specs));
|
|
|
|
$values = [];
|
|
$names = [];
|
|
foreach ($valuesQb->execute() as $row) {
|
|
$values[$row['id_label']] = $row['id'];
|
|
$names[$row['id_label']] = $row['name'];
|
|
}
|
|
|
|
// Find values labels
|
|
$query = 'SELECT id, label
|
|
FROM '.getTableName('products_variations_choices_labels').'
|
|
WHERE 0=1 ';
|
|
foreach ($parts as $variant => $value) {
|
|
$query .= " or id='".$variant."' ";
|
|
}
|
|
$SQL = sqlQuery($query);
|
|
|
|
$labels = [];
|
|
while ($row = sqlFetchAssoc($SQL)) {
|
|
$labels[$row['id']] = $row['label'];
|
|
}
|
|
|
|
$title = '';
|
|
foreach ($parts as $variant => $value) {
|
|
$title .= $labels[$variant]."\t".($names[$variant] ?? $value)."\n";
|
|
}
|
|
|
|
$query = 'INSERT INTO '.getTableName('products_variations').' (id_product, title) VALUES ('.$productId.',"'.sqlFormatInput($title).'")';
|
|
sqlQuery($query);
|
|
$variation_id = sqlInsertId();
|
|
|
|
// Insert values
|
|
foreach ($parts as $variant => $value) {
|
|
if (!isset($values[$variant])) { // Insert new value if does not exists
|
|
$query = 'INSERT INTO '.getTableName('products_variations_choices_values').'
|
|
(id_label, value, code) VALUES
|
|
('.$variant.",'".sqlFormatInput($value)."','".sqlFormatInput($value)."')";
|
|
sqlQuery($query);
|
|
$values[$variant] = sqlInsertId();
|
|
}
|
|
|
|
$query = 'INSERT INTO '.getTableName('products_variations_combination').' (id_variation, id_label, id_value) VALUES ('.$variation_id.','.$variant.",'".$values[$variant]."')";
|
|
sqlQuery($query);
|
|
}
|
|
|
|
// Make sure categorization works
|
|
$query = 'SELECT 1
|
|
FROM '.getTableName('products_variations_choices_categorization').'
|
|
WHERE id_product='.$productId;
|
|
$SQL = sqlQuery($query);
|
|
if (sqlNumRows($SQL) < count($parts)) {
|
|
$index = 1;
|
|
foreach ($parts as $variant => $value) {
|
|
$query = 'INSERT IGNORE INTO '.getTableName('products_variations_choices_categorization').' (id_product, id_label, list_order) VALUES ('.$productId.','.$variant.",'".$index++."')";
|
|
sqlQuery($query);
|
|
}
|
|
}
|
|
}
|
|
|
|
return $variation_id;
|
|
}
|
|
|
|
public static function makeCodeUnique($variationId, $code, $productId = null)
|
|
{
|
|
$data = [
|
|
'code' => $code,
|
|
'id_variation' => $variationId,
|
|
'id_product' => $productId,
|
|
];
|
|
|
|
$productsQb = sqlQueryBuilder()
|
|
->select('COUNT(*)')
|
|
->from('products', 'p')
|
|
->where('p.code = :code')
|
|
->setParameters($data);
|
|
|
|
if ($productId) {
|
|
$productsQb->andWhere('p.id != :id_product');
|
|
}
|
|
|
|
$variationsQb = sqlQueryBuilder()
|
|
->select('COUNT(*)')
|
|
->from('products_variations', 'pv')
|
|
->where('pv.code = :code')
|
|
->setParameters($data);
|
|
|
|
if ($variationId) {
|
|
$variationsQb->andWhere('pv.id != :id_variation OR pv.id IS NULL');
|
|
}
|
|
|
|
$productsResult = $productsQb->execute()->fetchOne();
|
|
$variationsResult = $variationsQb->execute()->fetchOne();
|
|
|
|
if ($productsResult == 0 && $variationsResult == 0) {
|
|
return $code;
|
|
}
|
|
|
|
// Strip "(1)"
|
|
$code = preg_replace('/ \([0-9]+\)$/', '', $code);
|
|
|
|
$index = 0;
|
|
do {
|
|
$index++;
|
|
$data['code'] = "{$code} ({$index})";
|
|
$productsQb->setParameters($data);
|
|
$variationsQb->setParameters($data);
|
|
} while ($productsQb->execute()->fetchOne() > 0 || $variationsQb->execute()->fetchOne() > 0);
|
|
|
|
return $data['code'];
|
|
}
|
|
|
|
public static function eanExists($variationId, $code, $productId = null)
|
|
{
|
|
if (findModule('products', 'allow_duplicate_ean')) {
|
|
return false;
|
|
}
|
|
|
|
$sql = sqlQueryBuilder()->select('p.id AS id_product, pv.id AS id_variation')
|
|
->from('products', 'p')
|
|
->leftJoin('p', 'products_variations', 'pv', 'p.id=pv.id_product')
|
|
->where('(pv.ean LIKE :ean OR p.ean LIKE :ean) AND (pv.id != :id_variation OR pv.id IS NULL)');
|
|
|
|
if ($productId) {
|
|
$sql->andWhere('p.id !=:id_product');
|
|
}
|
|
|
|
if (findModule(Modules::PRODUCTS_SUPPLIERS)) {
|
|
$sql->leftJoin('p', 'products_of_suppliers', 'pos', 'pos.id_product = p.id AND (pv.id = pos.id_variation OR pv.id IS NULL) AND pos.ean LIKE :ean');
|
|
}
|
|
|
|
$data = [
|
|
'ean' => ltrim(trim($code, " \t\n\r\0\x0B"), '0'),
|
|
'id_variation' => $variationId,
|
|
'id_product' => $productId,
|
|
];
|
|
|
|
$exists = $sql->setParameters($data)->execute()->fetch();
|
|
if ($exists) {
|
|
$exists['ean'] = $data['ean'];
|
|
}
|
|
|
|
return $exists;
|
|
}
|
|
|
|
public static function eanExistsMessage($exists)
|
|
{
|
|
$ean = $exists['ean'];
|
|
$productId = $exists['id_product'];
|
|
$variations = (!empty($exists['id_variation']) ? ", '&flap=3'" : '');
|
|
|
|
return "EAN <strong>{$ean}</strong> není unikátní. <a href=\"javascript:nw('product', {$productId}{$variations})\">Zobrazit duplicitní produkt.</a>";
|
|
}
|
|
|
|
public static function recalcDeliveryTimes()
|
|
{
|
|
$result = sqlQuery('SELECT id FROM products');
|
|
|
|
$changed = 0;
|
|
while (($row = sqlFetchAssoc($result)) !== false) {
|
|
$prod = new Product($row['id']);
|
|
$changed += $prod->updateDeliveryTime();
|
|
}
|
|
|
|
return $changed;
|
|
}
|
|
|
|
public static function recalcInStore()
|
|
{
|
|
$inStoreCountsQuery = sqlQueryBuilder()
|
|
->select('p.id, COALESCE(SUM(GREATEST(pv.in_store, 0)), p.in_store) in_store')
|
|
->from('products', 'p')
|
|
->leftJoin('p', 'products_variations', 'pv', 'p.id=pv.id_product AND pv.figure = "Y"')
|
|
->groupBy('p.id');
|
|
|
|
// Select which products should be updated
|
|
$productsToUpdate = sqlQueryBuilder()
|
|
->select('p.id')
|
|
->from('products', 'p')
|
|
->joinSubQuery('p', $inStoreCountsQuery, 'sums', 'p.id=sums.id')
|
|
->where('p.in_store != sums.in_store')
|
|
->setMaxResults(1000)->execute()->fetchFirstColumn();
|
|
|
|
$update = sqlQueryBuilder()->update('products', 'p')
|
|
->joinSubQuery('p', $inStoreCountsQuery, 'sums', 'p.id=sums.id')
|
|
->set('p.in_store', 'sums.in_store')
|
|
->where('p.in_store != sums.in_store');
|
|
|
|
// DB Locks optimization, reduces locked rows -> reduces deadlocks
|
|
if (count($productsToUpdate) < 1000) {
|
|
$update->andWhere(Operator::inIntArray($productsToUpdate, 'p.id'));
|
|
}
|
|
|
|
return $update->execute();
|
|
}
|
|
|
|
protected static function prepareDeliveryText($id_product, &$variation)
|
|
{
|
|
$inStore = $variation['in_store'];
|
|
$delivery_time_text = getProductDeliveryText($variation['in_store'], $variation['delivery_time']);
|
|
$variation['delivery_time_index'] = $variation['delivery_time'];
|
|
$variation['delivery_time'] = $delivery_time_text;
|
|
|
|
if ($inStore <= 0 && findModule('products_suppliers')) {
|
|
$inSuppliers = returnSQLResult('SELECT SUM(in_store)
|
|
FROM '.getTableName('products_of_suppliers')." pos
|
|
WHERE pos.id_product={$id_product} AND pos.id_variation={$variation['id']}");
|
|
|
|
if (findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_ALLOW_NEGATIVE_IN_STORE)) {
|
|
$inSuppliers += $inStore;
|
|
}
|
|
|
|
if ($inSuppliers > 0) {
|
|
$variation['in_store_suppliers'] = $inSuppliers;
|
|
|
|
if (findModule(\Modules::PRODUCTS_SUPPLIERS) && findModule(\Modules::PRODUCTS_SUPPLIERS, \Modules::SUB_DELIVERY_TIME)) {
|
|
$cfg = \KupShop\KupShopBundle\Config::get();
|
|
$languageContext = Contexts::get(\KupShop\KupShopBundle\Context\LanguageContext::class);
|
|
|
|
$variation['delivery_time_index'] = $cfg['Modules']['products_suppliers']['delivery_time'];
|
|
if (!$languageContext->translationActive()) {
|
|
$variation['delivery_time'] = sprintf($cfg['Products']['DeliveryTime'][$cfg['Modules']['products_suppliers']['delivery_time']], strval($inSuppliers));
|
|
} else {
|
|
$variation['delivery_time'] = sprintf(translate($cfg['Modules']['products_suppliers']['delivery_time'], 'deliveryTime'), strval($inSuppliers));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if (empty($subclass)) {
|
|
class Variations extends VariationsBase
|
|
{
|
|
}
|
|
}
|