2006 lines
74 KiB
PHP
2006 lines
74 KiB
PHP
<?php
|
|
|
|
use KupShop\CatalogBundle\Util\ProductsFilterSpecs;
|
|
use KupShop\ContentBundle\View\RedirectView;
|
|
use KupShop\FeedsBundle\FeedLocator;
|
|
use KupShop\I18nBundle\Util\PriceConverter;
|
|
use KupShop\KupShopBundle\Config;
|
|
use KupShop\KupShopBundle\Context\ContextManager;
|
|
use KupShop\KupShopBundle\Context\CurrencyContext;
|
|
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
|
use KupShop\KupShopBundle\Util\Contexts;
|
|
use KupShop\KupShopBundle\Util\Functional\Mapping;
|
|
use KupShop\OrderingBundle\Util\Order\OrderInfo;
|
|
use KupShop\POSBundle\Util\PosEntity;
|
|
use KupShop\POSBundle\Util\PosOrdersFilterSpec;
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
|
|
$main_class = 'AdminAutocomplete';
|
|
|
|
class AdminAutocomplete
|
|
{
|
|
/** @var QueryBuilder */
|
|
public $qb;
|
|
|
|
protected $search;
|
|
|
|
private $onlyVisible;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->qb = sqlQueryBuilder();
|
|
}
|
|
|
|
public function run()
|
|
{
|
|
header('Content-type: application/json');
|
|
$searchTerm = $this->prepareSearch(getVal('term', null, ''));
|
|
$type = getVal('type');
|
|
$limit = getVal('limit', null, 100);
|
|
if ($limit < 0) {
|
|
$limit = 999999;
|
|
}
|
|
|
|
$this->qb->setMaxResults($limit);
|
|
|
|
$none = getVal('none');
|
|
if ($none && !$searchTerm) {
|
|
return ['value' => '', 'label' => $none, 'text' => $none];
|
|
}
|
|
|
|
$this->search = $searchTerm;
|
|
$this->onlyVisible = getVal('visible', null, true);
|
|
|
|
echo $this->handle($type);
|
|
}
|
|
|
|
public function handle($type)
|
|
{
|
|
$method = 'handle'.ucfirst($type);
|
|
if (method_exists($this, $method)) {
|
|
return $this->$method();
|
|
}
|
|
|
|
return $this->fallback($type, $this->search);
|
|
}
|
|
|
|
public function handleRedirect()
|
|
{
|
|
$result = [
|
|
'url' => null,
|
|
];
|
|
|
|
if (($type = getVal('link_type')) && (($value = getVal('value')) || getVal('value') == '0')) {
|
|
$redirectView = ServiceContainer::getService(RedirectView::class);
|
|
if ($lang = getVal('lang')) {
|
|
$redirectView->setLang($lang);
|
|
}
|
|
$redirectView->setType($type);
|
|
$redirectView->setId($value);
|
|
|
|
try {
|
|
$result['url'] = $redirectView->getUrl();
|
|
} catch (Exception $e) {
|
|
}
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleSalesman(): string
|
|
{
|
|
if (!findModule(Modules::SALESMAN)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('sm.id value, sm.name text, sm.email label')
|
|
->from('salesman', 'sm');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(
|
|
\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search,
|
|
[
|
|
['field' => 'sm.name', 'match' => 'both'],
|
|
['field' => 'sm.email', 'match' => 'both'],
|
|
]
|
|
)
|
|
);
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleSales(): string
|
|
{
|
|
if (!findModule(Modules::SALES)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, code as text, code as label')
|
|
->from('sales')
|
|
->orderBy('id');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(
|
|
\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search,
|
|
[
|
|
['field' => 'id', 'match' => 'both'],
|
|
['field' => 'code', 'match' => 'both'],
|
|
]
|
|
)
|
|
);
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleSliders(): string
|
|
{
|
|
if (!findModule(Modules::SLIDERS)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text, name as label')
|
|
->from('sliders')
|
|
->orderBy('name');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
if (empty($this->search) && getVal('allow_empty')) {
|
|
array_unshift($result, ['text' => getVal('empty_choice', default: 'Nevybráno'), 'value' => '']);
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleUsers()
|
|
{
|
|
if (!findModule(Modules::USERS)) {
|
|
return '';
|
|
}
|
|
|
|
$legacyFields = ['u.id', 'u.firm', 'u.name', 'u.surname', 'u.email'];
|
|
|
|
$fields = array_merge([
|
|
'u.ico' => 'invoice_ico',
|
|
'u.dic' => 'invoice_dic',
|
|
'u.email' => 'invoice_email',
|
|
'u.id_language' => 'id_language',
|
|
],
|
|
Mapping::mapKeys(User::getFields(), function ($_, $x) {return ['u.'.$x, "invoice_{$x}"]; }),
|
|
Mapping::mapKeys(User::getFields(), function ($_, $x) {return ["u.delivery_{$x}", "delivery_{$x}"]; })
|
|
);
|
|
|
|
if (findModule(Modules::CURRENCIES)) {
|
|
$fields['u.currency'] = 'currency';
|
|
}
|
|
|
|
$aliasFields = array_map(function ($field) use ($fields) {
|
|
return "{$field} as {$fields[$field]}";
|
|
}, array_keys($fields));
|
|
|
|
$this->qb
|
|
->select(
|
|
array_merge($aliasFields, $legacyFields)
|
|
)
|
|
->from('users', 'u')
|
|
->where("u.figure = 'Y'")
|
|
->orderBy('u.surname', 'ASC');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(
|
|
\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search,
|
|
[
|
|
['field' => 'name', 'match' => 'both'],
|
|
['field' => 'surname', 'match' => 'both'],
|
|
['field' => 'firm', 'match' => 'both'],
|
|
['field' => 'ico', 'match' => 'both'],
|
|
['field' => 'email', 'match' => 'both'],
|
|
['field' => 'street', 'match' => 'both'],
|
|
['field' => 'phone', 'match' => 'both'],
|
|
]
|
|
)
|
|
);
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
foreach ($result as &$user) {
|
|
$user['text'] = $user['label'] = "{$user['firm']} {$user['name']} {$user['surname']} - {$user['email']}";
|
|
$user['value'] = $user['id'];
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleReviews(): string
|
|
{
|
|
if (!findModule(Modules::REVIEWS)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, summary as text, summary as label')
|
|
->from('reviews')
|
|
->orderBy('id');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(
|
|
\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search,
|
|
[
|
|
['field' => 'id', 'match' => 'numeric'],
|
|
['field' => 'pros', 'match' => 'both'],
|
|
['field' => 'cons', 'match' => 'both'],
|
|
['field' => 'summary', 'match' => 'both'],
|
|
]
|
|
)
|
|
);
|
|
}
|
|
|
|
$this->qb->setMaxResults(100);
|
|
$result = $this->qb->execute()->fetchAll();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleSellers(): string
|
|
{
|
|
if (!findModule(Modules::SELLERS)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, CONCAT_WS(", ", city, street, title) as text, CONCAT_WS(", ", city, street, title) as label')
|
|
->from('sellers')
|
|
->orderBy('title');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(
|
|
\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search,
|
|
[
|
|
['field' => 'title', 'match' => 'both'],
|
|
['field' => 'city', 'match' => 'both'],
|
|
['field' => 'street', 'match' => 'both'],
|
|
['field' => 'psc', 'match' => 'both'],
|
|
]
|
|
)
|
|
);
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAll();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleStores(): string
|
|
{
|
|
if (!findModule(Modules::STORES)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text')
|
|
->from('stores')
|
|
->orderBy('name');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAll();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleSerialNumbers(): string
|
|
{
|
|
$idProduct = getVal('id_product');
|
|
if (!findModule(Modules::PRODUCTS_SERIAL_NUMBERS) || empty($idProduct)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, serial_number as text')
|
|
->from('products_serial_numbers')
|
|
->andWhere(Operator::equals(['id_product' => $idProduct]))
|
|
->andWhere('id_order_item IS NULL');
|
|
|
|
$idVariation = getVal('id_variation');
|
|
if ($idVariation) {
|
|
$this->qb->andWhere(Operator::equals(['id_variation' => $idVariation]));
|
|
}
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['serial_number' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handlePriceLists(): string
|
|
{
|
|
if (!findModule(Modules::PRICELISTS)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text')
|
|
->from('pricelists')
|
|
->orderBy('name');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleDeliveryTypes(): string
|
|
{
|
|
$this->qb->select('dt.id as value, CONCAT(COALESCE(dtd.name_admin, dtd.name), " - ", COALESCE(dtp.name_admin, dtp.name)) as text')
|
|
->from('delivery_type', 'dt')
|
|
->join('dt', 'delivery_type_delivery', 'dtd', 'dtd.id = dt.id_delivery')
|
|
->join('dt', 'delivery_type_payment', 'dtp', 'dtp.id = dt.id_payment')
|
|
->orderBy('dt.id')
|
|
->groupBy('dt.id');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(
|
|
Operator::like(
|
|
[
|
|
'dtd.name_admin' => '%'.$this->search.'%',
|
|
'dtd.name' => '%'.$this->search.'%',
|
|
'dtp.name_admin' => '%'.$this->search.'%',
|
|
'dtp.name' => '%'.$this->search.'%',
|
|
],
|
|
'OR'
|
|
)
|
|
);
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleDeliveries(): string
|
|
{
|
|
$this->qb->select('id as value, COALESCE(name_admin, name) as text')
|
|
->from('delivery_type_delivery')
|
|
->orderBy('position');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handlePayments(): string
|
|
{
|
|
$this->qb->select('id as value, COALESCE(name_admin, name) as text')
|
|
->from('delivery_type_payment')
|
|
->orderBy('position');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleProductsRelatedTypes(): string
|
|
{
|
|
if (findModule(Modules::PRODUCTS_RELATED, Modules::SUB_TYPES)) {
|
|
$this->qb->select('prt.id as value, prt.name as text')
|
|
->from('products_related_types', 'prt')
|
|
->execute()
|
|
->fetchAll();
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleArticles(): string
|
|
{
|
|
$this->qb->select('a.title as label, a.title as text, id as value')
|
|
->from('articles', 'a')
|
|
->where(Operator::like(['title' => '%'.$this->search.'%']))
|
|
->orderBy('a.title');
|
|
|
|
$except_article = getVal('except_article');
|
|
if (!empty($except_article)) {
|
|
$this->qb->andWhere(
|
|
Operator::not(Operator::equals(['a.id' => $except_article]))
|
|
);
|
|
}
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleArticlesAuthors()
|
|
{
|
|
$searchFields = [
|
|
['field' => 'name', 'match' => 'both'],
|
|
['field' => 'surname', 'match' => 'both'],
|
|
['field' => 'nick', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
|
|
$this->qb->select('id as value, CONCAT(name, " ", surname) as text')
|
|
->from('articles_authors')
|
|
->where($searchFields['where'])
|
|
->addParameters($searchFields['data'])
|
|
->orderBy('name, surname');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleOrders()
|
|
{
|
|
$searchFields = [
|
|
['field' => 'o.id', 'match' => 'both'],
|
|
['field' => 'o.order_no', 'match' => 'both'],
|
|
['field' => 'o.invoice_name', 'match' => 'both'],
|
|
['field' => 'o.invoice_surname', 'match' => 'both'],
|
|
['field' => 'o.invoice_email', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
|
|
$this->qb->select('o.id, o.order_no, o.total_price, o.total_price_without_vat, o.status_payed, o.invoice_firm, CONCAT(o.invoice_name, " ", o.invoice_surname) as customer_name')
|
|
->from('orders', 'o')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data'])
|
|
->orderBy('o.id', 'DESC');
|
|
|
|
if (findModule(Modules::CURRENCIES)) {
|
|
$this->qb->addSelect('o.currency');
|
|
} else {
|
|
$this->qb->addSelect('NULL as currency');
|
|
}
|
|
|
|
if (getVal('only_handled', null, 0)) {
|
|
$this->qb->andWhere(Operator::inIntArray(getStatuses('handled'), 'o.status'))
|
|
->andWhere('o.status_storno != 1');
|
|
}
|
|
|
|
if (getVal('only_active', null, 0)) {
|
|
$this->qb->andWhere(Operator::inIntArray(getStatuses('active'), 'o.status'));
|
|
}
|
|
|
|
if (getVal('not_handled', null, 0)) {
|
|
$this->qb->andWhere(Operator::inIntArray(getStatuses('nothandled'), 'o.status'))
|
|
->andWhere('o.status_storno != 1');
|
|
}
|
|
|
|
if ($posId = getVal('pos_id')) {
|
|
$posEntity = ServiceContainer::getService(PosEntity::class);
|
|
$posEntity->createFromDB($posId);
|
|
|
|
$deliveryTypes = array_filter([
|
|
$posEntity->getCardDeliveryType(),
|
|
$posEntity->getCashDeliveryType(),
|
|
$posEntity->getInvoiceDeliveryType(),
|
|
], fn ($value) => !is_null($value));
|
|
|
|
$deliveries = sqlQueryBuilder()
|
|
->select('id_delivery')
|
|
->from('delivery_type')
|
|
->andWhere(Operator::inIntArray($deliveryTypes, 'id'));
|
|
|
|
$this->qb->andWhere(Operator::exists(
|
|
sqlQueryBuilder()
|
|
->select('*')
|
|
->from('delivery_type', 'dt')
|
|
->andWhere('o.id_delivery = dt.id')
|
|
->andWhere(Operator::inSubQuery('dt.id_delivery', $deliveries))
|
|
));
|
|
|
|
$posOrdersFilterSpec = ServiceContainer::getService(PosOrdersFilterSpec::class);
|
|
$filterSpecs = $posOrdersFilterSpec->getSpecs(['orders_for_pos' => $posEntity]);
|
|
$this->qb->andWhere($filterSpecs);
|
|
}
|
|
|
|
$data = $this->qb->execute()->fetchAll();
|
|
foreach ($data as &$row) {
|
|
$row['print_price'] = printPrice($row['total_price'], ['currency' => $row['currency']]);
|
|
}
|
|
|
|
return json_encode($data);
|
|
}
|
|
|
|
public function handleReclamations()
|
|
{
|
|
$searchFields = [
|
|
['field' => 'r.id', 'match' => 'both'],
|
|
['field' => 'r.code', 'match' => 'both'],
|
|
['field' => 'r.name', 'match' => 'both'],
|
|
['field' => 'r.surname', 'match' => 'both'],
|
|
['field' => 'r.date_created', 'match' => 'both'],
|
|
['field' => 'r.date_handle', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
|
|
$this->qb->select('r.id, r.code, CONCAT(r.name, " ", r.surname) as customer_name, r.date_created, r.date_handle')
|
|
->from('reclamations', 'r')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data'])
|
|
->orderBy('r.id', 'DESC');
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
foreach ($result as &$reclamation) {
|
|
$reclamation['text'] = $reclamation['label'] = "{$reclamation['code']} {$reclamation['customer_name']}";
|
|
$reclamation['value'] = $reclamation['id'];
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleReturns()
|
|
{
|
|
$searchFields = [
|
|
['field' => 'r.id', 'match' => 'both'],
|
|
['field' => 'r.code', 'match' => 'both'],
|
|
['field' => 'r.name', 'match' => 'both'],
|
|
['field' => 'r.surname', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
|
|
$this->qb->select('r.id, r.code, CONCAT(r.name, " ", r.surname) as customer_name')
|
|
->from('returns', 'r')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data'])
|
|
->orderBy('r.id', 'DESC');
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
foreach ($result as &$return) {
|
|
$return['text'] = $return['label'] = "{$return['code']} {$return['customer_name']}";
|
|
$return['value'] = $return['id'];
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleLabels()
|
|
{
|
|
$searchFields = [
|
|
['field' => 'l.id', 'match' => 'both'],
|
|
['field' => 'l.name', 'match' => 'both'],
|
|
['field' => 'l.short_name', 'match' => 'both'],
|
|
['field' => 'l.name_admin', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
|
|
$this->qb->select('l.id AS id, l.id as value, l.name, COALESCE(l.name_admin, l.name) AS text, l.short_name')
|
|
->from('labels', 'l')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data'])
|
|
->orderBy('l.id', 'DESC');
|
|
|
|
return json_encode($this->qb->execute()->fetchAll());
|
|
}
|
|
|
|
public function handleCountries()
|
|
{
|
|
if (findModule(\Modules::CURRENCIES)) {
|
|
$search_fields = [
|
|
['field' => 'c.name', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($search_fields);
|
|
|
|
$this->qb->select('c.id value, c.name text')
|
|
->from('countries', 'c')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data']);
|
|
|
|
return $this->getResult();
|
|
}
|
|
$cfg = Config::get();
|
|
$searchTerm = mb_strtolower(getVal('term'), 'UTF-8');
|
|
$return = [];
|
|
if (isset($cfg['Order']['Countries'])) {
|
|
foreach ($cfg['Order']['Countries'] as $id => $country) {
|
|
if (strpos(mb_strtolower($id), $searchTerm) !== false || strpos(mb_strtolower($country), $searchTerm) !== false) {
|
|
$return[] = ['id' => $id, 'value' => $id, 'text' => $country, 'name' => $country];
|
|
}
|
|
}
|
|
}
|
|
|
|
return json_encode($return);
|
|
}
|
|
|
|
public function handleCurrencies()
|
|
{
|
|
$search_fields = [
|
|
['field' => 'c.name', 'match' => 'both'],
|
|
['field' => 'c.id', 'match' => 'both'],
|
|
['field' => 'c.symbol', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($search_fields);
|
|
|
|
$this->qb->select('c.id value, c.name text')
|
|
->from('currencies', 'c')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data']);
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleLanguages()
|
|
{
|
|
$search_fields = [
|
|
['field' => 'l.name', 'match' => 'both'],
|
|
['field' => 'l.id', 'match' => 'both'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($search_fields);
|
|
|
|
$this->qb->select('l.id value, l.name text')
|
|
->from('languages', 'l')
|
|
->andWhere($searchFields['where'])
|
|
->addParameters($searchFields['data']);
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleOssVatsCategories()
|
|
{
|
|
$search_fields = [
|
|
['field' => 'ovc.description', 'match' => 'both'],
|
|
['field' => 'ovc.cn', 'match' => 'left'],
|
|
['field' => 'ovc.cnkey', 'match' => 'left'],
|
|
];
|
|
|
|
$searchFields = $this->prepareSearchFields($search_fields);
|
|
|
|
$qb = sqlQueryBuilder()->select('cnkey')->from('kupshop_shared.oss_vats_categories', 'ovc')
|
|
->andWhere($searchFields['where'])
|
|
->setParameters($searchFields['data'])
|
|
->orWhere('ovc.cn LIKE :searchRawLeft');
|
|
|
|
$recursiveQuery = '
|
|
WITH RECURSIVE cte (level, cnkey, cn_key_original, parent, cn, name) AS (
|
|
SELECT 0, cnkey, cnkey, parent, cn, description
|
|
FROM kupshop_shared.oss_vats_categories ovc1
|
|
WHERE cnkey IN ('.$qb->getSQL().")
|
|
UNION ALL
|
|
SELECT level + 1, ovc2.cnkey, cte.cn_key_original, ovc2.parent, cte.cn,
|
|
CONCAT(ovc2.description, ' >> ', cte.name)
|
|
FROM kupshop_shared.oss_vats_categories ovc2
|
|
INNER JOIN cte ON (ovc2.cnkey = cte.parent)
|
|
)
|
|
SELECT cn_key_original AS value, CONCAT(cn, ' >> ', name) AS text, cn
|
|
FROM cte
|
|
WHERE parent IS NULL
|
|
ORDER BY level
|
|
";
|
|
|
|
$searchFields['data']['searchRawLeft'] = $this->search.'%';
|
|
|
|
$this->qb->select('*')->select('*')
|
|
->from('('.$recursiveQuery.')', 'a')
|
|
->orderBy('cn = :search_0', 'DESC')
|
|
->setParameters($searchFields['data'])
|
|
->setParameter('searchRaw', $this->search.'%');
|
|
|
|
if (getVal('hideExceptions')) {
|
|
return $this->getResult();
|
|
}
|
|
|
|
$translate = translate('exception', 'OssExceptions');
|
|
$unionQb = sqlQueryBuilder()->select("-id value, description text, '{$translate}' cn")
|
|
->from('vats_oss_exceptions')
|
|
->where(Operator::like(['description' => "{$this->search}%"]))
|
|
->addParameters($searchFields['data']);
|
|
|
|
return json_encode(array_merge(
|
|
$this->qb->execute()->fetchAllAssociative(),
|
|
$unionQb->execute()->fetchAllAssociative()
|
|
));
|
|
}
|
|
|
|
public function handleOrdersItems()
|
|
{
|
|
if ($orderIds = getVal('orderIds')) {
|
|
$orderIds = explode(',', $orderIds);
|
|
}
|
|
|
|
if (empty($orderIds)) {
|
|
return json_encode([]);
|
|
}
|
|
|
|
$searchFields = [
|
|
['field' => 'p.title', 'match' => 'both'],
|
|
['field' => 'p.code', 'match' => 'both'],
|
|
['field' => 'p.ean', 'match' => 'numeric'],
|
|
['field' => 'pv.ean', 'match' => 'numeric'],
|
|
['field' => 'oi.descr', 'match' => 'both'],
|
|
];
|
|
|
|
if (findModule(Modules::PRODUCTS_VARIATIONS, Modules::SUB_CODE)) {
|
|
$searchFields[] = ['field' => 'pv.code', 'match' => 'both'];
|
|
}
|
|
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
|
|
$this->qb->select('oi.*, oi.id as id_item, ph.id as id_photo, ph.source, ph.image_2, p.in_store, COALESCE(pv.ean, p.ean) as ean')
|
|
->from('order_items', 'oi')
|
|
->leftJoin('oi', 'orders', 'o', 'o.id = oi.id_order')
|
|
->leftJoin('oi', 'products', 'p', 'oi.id_product = p.id')
|
|
->leftJoin('oi', 'products_variations', 'pv', 'pv.id = oi.id_variation')
|
|
->leftJoin('p', 'photos_products_relation', 'ppr', 'p.id = ppr.id_product AND ppr.id_variation IS NULL')
|
|
->leftJoin('ppr', 'photos', 'ph', 'ph.id = ppr.id_photo')
|
|
->andWhere(Operator::inIntArray($orderIds, 'o.id'))
|
|
->andWhere($searchFields['where'])
|
|
->andWhere('oi.id_product IS NOT NULL')
|
|
->addParameters($searchFields['data'])
|
|
->groupBy('oi.id');
|
|
|
|
if (findModule('products_variations', 'variationCode')) {
|
|
$this->qb->addSelect('COALESCE(pv.code, p.code) as code');
|
|
} else {
|
|
$this->qb->addSelect('p.code as code');
|
|
}
|
|
|
|
$data = $this->qb->execute()->fetchAll();
|
|
|
|
$orderId = reset($orderIds);
|
|
|
|
$contextManager = ServiceContainer::getService(ContextManager::class);
|
|
|
|
$order = new Order();
|
|
$order->createFromDB($orderId);
|
|
|
|
$contextManager->activateOrder($order, function () use (&$data) {
|
|
foreach ($data as &$row) {
|
|
$row['piece_price_with_vat'] = toDecimal($row['piece_price'])->addVat($row['tax']);
|
|
$row['printable_price'] = printPrice($row['piece_price_with_vat']);
|
|
|
|
$row['image'] = getImage($row['id_photo'], $row['image_2'], $row['source'], 4);
|
|
}
|
|
});
|
|
|
|
return json_encode($data);
|
|
}
|
|
|
|
public function handleOrderSources(): string
|
|
{
|
|
$sources = [];
|
|
foreach (OrderInfo::getOrderSources() as $key => $source) {
|
|
$sources[] = [
|
|
'value' => $key,
|
|
'text' => $source,
|
|
];
|
|
}
|
|
|
|
if (!empty($this->search)) {
|
|
$sources = array_values(array_filter($sources, fn ($x) => stripos($x['text'], $this->search) !== false));
|
|
}
|
|
|
|
return json_encode($sources);
|
|
}
|
|
|
|
public function handleDropshipment(): string
|
|
{
|
|
$this->qb->select('id as value, name as text')
|
|
->from('dropshipment')
|
|
->where(Operator::like(['name' => '%'.$this->search.'%']));
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleProductId()
|
|
{
|
|
// TODO nekompatibilni s product_id ve fallbocku, dodelat
|
|
|
|
$cfg = Config::get();
|
|
|
|
$flags = $this->getTermFlags($this->search, 'sv');
|
|
|
|
$this->qb->select('p.id as id, p.id as value, p.id as id_rel_product, p.title as title, p.title as label, p.code, p.in_store, p.figure as visible,
|
|
ph.id as id_photo, ph.source, ph.image_2, ph.descr, p.title as label, CONCAT_WS(\' | \',p.title, p.code) as text')
|
|
->from('products', 'p')
|
|
->joinVariationsOnProducts()
|
|
->leftJoin('p', 'photos_products_relation', 'ppr', 'ppr.id_product=p.id AND ppr.show_in_lead = "Y"')
|
|
->leftJoin('ppr', 'photos', 'ph', 'ph.id = ppr.id_photo')
|
|
->groupBy('p.id')
|
|
->orderBy('FIELD(p.figure, "Y", "N", "O"), p.title');
|
|
|
|
if (strpos($this->search, ',') === false) {
|
|
$searchFields = [
|
|
['field' => 'p.title', 'match' => 'both'],
|
|
['field' => 'p.code', 'match' => 'both'],
|
|
['field' => 'p.ean', 'match' => 'numeric'],
|
|
['field' => 'pv.ean', 'match' => 'numeric'],
|
|
['field' => 'p.id', 'match' => 'numeric'],
|
|
['field' => 'pv.id', 'match' => 'numeric'],
|
|
];
|
|
if (findModule(Modules::PRODUCTS_VARIATIONS, Modules::SUB_CODE)) {
|
|
$searchFields[] = ['field' => 'pv.code', 'match' => 'both'];
|
|
}
|
|
if (getVal('price')) {
|
|
$this->qb->joinVatsOnProducts();
|
|
$this->qb->addSelect(\Query\Product::withVatAndDiscount($this->qb).' as price_with_vat_and_discount, v.vat');
|
|
}
|
|
if (getVal('variations')) {
|
|
$this->qb->leftJoin('pv', 'products_variations_combination', 'pvc', 'pv.id = pvc.id_variation');
|
|
$this->qb->leftJoin('pvc', 'products_variations_choices_values', 'pvcv', 'pvc.id_value = pvcv.id');
|
|
$this->qb->addSelect(
|
|
'CASE WHEN pv.id IS NOT NULL
|
|
THEN CONCAT("[",GROUP_CONCAT(DISTINCT JSON_OBJECT("id", pv.id, "discount", p.discount, "title", pv.title, "in_store", pv.in_store, "code",COALESCE(pv.code, ""), "ean", COALESCE(pv.ean, ""), "price",COALESCE(pv.price, p.price)) ORDER BY pvcv.sort ASC SEPARATOR \',\'),"]")
|
|
ELSE null
|
|
END AS variations'
|
|
);
|
|
}
|
|
$searchFields = $this->prepareSearchFields($searchFields);
|
|
$this->qb->andWhere($searchFields['where']);
|
|
|
|
foreach ($searchFields['data'] as $key => $field) {
|
|
$this->qb->setParameter($key, $field);
|
|
}
|
|
} else {
|
|
// Search in set of codes/EANs
|
|
$searchedValues = array_map(function ($x) {
|
|
return trim($x);
|
|
}, explode(',', $this->search));
|
|
|
|
$searchedInt = array_filter($searchedValues, function ($x) {
|
|
return is_numeric($x) ? $x : null;
|
|
});
|
|
|
|
$this->qb->orWhere(
|
|
Operator::inStringArray($searchedValues, 'p.code'),
|
|
Operator::inIntArray($searchedInt, 'p.ean'),
|
|
Operator::inIntArray($searchedInt, 'p.id'),
|
|
Operator::inIntArray($searchedInt, 'pv.ean'),
|
|
Operator::inIntArray($searchedInt, 'pv.id')
|
|
);
|
|
if (findModule(Modules::PRODUCTS_VARIATIONS, Modules::SUB_CODE)) {
|
|
$this->qb->orWhere(Operator::inStringArray($searchedValues, 'pv.code'));
|
|
}
|
|
}
|
|
|
|
if ($this->onlyVisible) {
|
|
$this->qb->andWhere(Operator::equals(['p.figure' => 'Y']));
|
|
}
|
|
|
|
// flags - s: in_store, v: visible
|
|
foreach ($flags as $flag) {
|
|
switch ($flag) {
|
|
case 's':
|
|
$this->qb->andWhere('p.in_store > 0');
|
|
break;
|
|
case 'v':
|
|
$this->qb->andWhere(Operator::equals(['p.figure' => 'Y']));
|
|
break;
|
|
}
|
|
}
|
|
|
|
$section = getVal('section');
|
|
$producer = getVal('producer');
|
|
if (!empty($section)) {
|
|
$s = sqlQueryBuilder()->select('virtual, data')->from('sections')
|
|
->where(Operator::equals(['id' => $section]))
|
|
->execute()->fetch();
|
|
|
|
$this->qb->leftJoin('p', 'products_in_sections', 'ps', 'p.id = ps.id_product');
|
|
|
|
$sections = getDescendantCategories($section);
|
|
$sectionsOperators = [Operator::inIntArray(array_values($sections), 'ps.id_section')];
|
|
|
|
if (($s['virtual'] ?? false) == 'Y') {
|
|
$productsFilterSpec = ServiceContainer::getService(ProductsFilterSpecs::class);
|
|
$sectionData = json_decode($s['data'], true);
|
|
$specs = $productsFilterSpec->getSpecs($sectionData['virtual_settings'] ?? []);
|
|
$sectionsOperators[] = $specs;
|
|
}
|
|
|
|
$this->qb->andWhere(Operator::orX($sectionsOperators));
|
|
}
|
|
|
|
if (!empty($producer)) {
|
|
$this->qb->andWhere(Operator::equals(['p.producer' => $producer]));
|
|
}
|
|
|
|
$posId = getVal('pos');
|
|
if ($posId) {
|
|
$this->qb->addSelect('p.ean');
|
|
$priceConverter = ServiceContainer::getService(PriceConverter::class);
|
|
$currencyContext = Contexts::get(CurrencyContext::class);
|
|
$posEntity = new PosEntity();
|
|
$posEntity->createFromDB($posId)->activateContexts();
|
|
}
|
|
|
|
$data = $this->qb->execute()->fetchAll();
|
|
foreach ($data as &$row) {
|
|
if (isset($row['variations'])) {
|
|
$row['variations'] = array_filter(json_decode($row['variations'], true), function ($variation) { return $variation['id'] ?? false; });
|
|
}
|
|
$row['image'] = getImage($row['id_photo'], $row['image_2'], $row['source'], 'product_gallery') ?: [];
|
|
$row['in_store'] = floatval($row['in_store']);
|
|
|
|
if ($posId && $posEntity->getCurrencyID() !== $currencyContext->getDefaultId()) {
|
|
if (!empty($row['variations'])) {
|
|
foreach ($row['variations'] as &$variation) {
|
|
$variation['price'] = $priceConverter->convert(
|
|
$currencyContext->getDefault(),
|
|
$currencyContext->getOrDefault($posEntity->getCurrencyID()),
|
|
$variation['price']
|
|
);
|
|
|
|
if (!empty($variation['discount'])) {
|
|
$variation['price'] = $variation['price']->addDiscount($variation['discount']);
|
|
}
|
|
}
|
|
}
|
|
|
|
$row['price_with_vat_and_discount'] = $priceConverter->convert(
|
|
$currencyContext->getDefault(),
|
|
$currencyContext->getOrDefault($posEntity->getCurrencyID()),
|
|
$row['price_with_vat_and_discount'],
|
|
);
|
|
}
|
|
}
|
|
|
|
return json_encode($data);
|
|
}
|
|
|
|
public function handleParametersListValuesByParameter()
|
|
{
|
|
$parameterId = getVal('id_parameter');
|
|
if (!$parameterId) {
|
|
return json_encode([]);
|
|
}
|
|
|
|
$this->qb->select('pl.id, pl.id as value, pl.value as text, pl.id_parameter, p.value_meaning as parameter_type, pl.description')
|
|
->from('parameters_list', 'pl')
|
|
->leftJoin('pl', 'parameters', 'p', 'p.id = pl.id_parameter')
|
|
->where('value LIKE :like')
|
|
->andWhere(Operator::equals(['id_parameter' => $parameterId]))
|
|
->setParameter('like', '%'.$this->search.'%');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleParameters()
|
|
{
|
|
$this->qb->select('id, name, id as value, name as text')->from('parameters')
|
|
->where('name LIKE :like')
|
|
->setParameter('like', '%'.$this->search.'%');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleTemplates()
|
|
{
|
|
$this->qb->select('t.id as value, CONCAT_WS(\' - \', tc.name, t.name) as text')
|
|
->from('templates', 't')
|
|
->leftJoin('t', 'templates_categories', 'tc', 'tc.id=t.id_category')
|
|
->where(\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search, [['field' => 't.name', 'match' => 'both'], ['field' => 'tc.name', 'match' => 'both']])
|
|
);
|
|
|
|
$categoryId = getVal('id_category');
|
|
if ($categoryId) {
|
|
if (!is_array($categoryId)) {
|
|
$categoryId = [$categoryId];
|
|
}
|
|
/*
|
|
* Pokud je zde pouze jedna vybraná kategorie, poté se nebude zobrazovat kategorie-jméno_kategorie ale jen jméno_kategorie
|
|
*/
|
|
if (count($categoryId) == 1) {
|
|
$this->qb->addSelect('t.name as text');
|
|
}
|
|
$this->qb->andWhere(Operator::inIntArray($categoryId, 't.id_category'));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
if (getVal('allow_empty') || getVal('allowEmpty')) {
|
|
array_unshift($result, ['text' => getVal('empty_choice', default: 'Nevybráno'), 'value' => '']);
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleVariationsLabels(): string
|
|
{
|
|
$this->qb->select('id as value, label as text')
|
|
->from('products_variations_choices_labels')
|
|
->where(Operator::like(['label' => '%'.$this->search.'%']))
|
|
->orderBy('label', 'ASC');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleVariationsLabelValues()
|
|
{
|
|
$labelId = getVal('id_label');
|
|
if (!$labelId) {
|
|
return json_encode([]);
|
|
}
|
|
|
|
$this->qb->select('id as value, value as text')
|
|
->from('products_variations_choices_values')
|
|
->where(Operator::equals(['id_label' => $labelId]))
|
|
->andWhere('value LIKE :like')
|
|
->setParameter('like', '%'.$this->search.'%')
|
|
->orderBy('sort', 'ASC');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleOrder_discount()
|
|
{
|
|
$this->qb->select('id as value, name as text')->from('order_discounts')
|
|
->andWhere('name LIKE :like')
|
|
->setParameter('like', '%'.$this->search.'%')
|
|
->orderBy('position', 'ASC');
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
if (getVal('allow_empty')) {
|
|
array_unshift($result, ['text' => getVal('empty_choice', default: 'Nevybráno'), 'value' => '']);
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleGenerated_coupons()
|
|
{
|
|
$this->qb->select('id as value, descr as text')
|
|
->from('discounts')
|
|
->andWhere(Operator::equals(['condition_type' => 'generate_coupon']))
|
|
->andWhere('descr LIKE :like')
|
|
->setParameter('like', '%'.$this->search.'%')
|
|
->orderBy('descr', 'ASC')
|
|
->execute()->fetchAll();
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleVariationsValues()
|
|
{
|
|
$this->qb->select('pvcv.id as value, CONCAT_WS("-", pvcl.label, pvcv.value) text')
|
|
->from('products_variations_choices_values', 'pvcv')
|
|
->join('pvcv', 'products_variations_choices_labels', 'pvcl', 'pvcv.id_label = pvcl.id')
|
|
->where(\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search, [['field' => 'pvcl.label', 'match' => 'both'], ['field' => 'pvcv.value', 'match' => 'both']])
|
|
)
|
|
->orderBy('(pvcv.value = :search_0)', 'DESC')
|
|
->addOrderBy('(pvcl.label = :search_0)', 'DESC')
|
|
->addOrderBy('pvcv.id_label', 'ASC')
|
|
->addOrderBy('pvcv.sort', 'ASC');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handlePriceLevels(): string
|
|
{
|
|
if (!findModule(Modules::PRICE_LEVELS)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text')
|
|
->from('price_levels')
|
|
->orderBy('name');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAll();
|
|
|
|
array_unshift($result, ['value' => '', 'text' => translate('priceLevelStandard', 'users')]);
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleUserGroups()
|
|
{
|
|
$this->qb->select('id as value, name as text')
|
|
->from('users_groups')
|
|
->orderBy('name');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAll();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleOrderDiscountsTypes(): string
|
|
{
|
|
if (!findModule(Modules::ORDER_DISCOUNT)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text')
|
|
->from('order_discounts_types')
|
|
->orderBy('position');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%', 'code' => '%'.$this->search.'%'], 'OR'));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
if (getVal('allow_empty')) {
|
|
array_unshift($result, ['value' => '', 'text' => '-']);
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleConvertors(): string
|
|
{
|
|
if (!findModule(Modules::CONVERTORS)) {
|
|
return json_encode([]);
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text')
|
|
->from('convertors_definition')
|
|
->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
|
|
$result = $this->qb->execute()->fetchAllAssociative();
|
|
|
|
array_unshift($result, ['value' => '', 'text' => '----']);
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handlePos(): string
|
|
{
|
|
$this->qb->select('id as value, name as text')
|
|
->from('pos')
|
|
->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleAdmins(): string
|
|
{
|
|
$this->qb->select('id as value, login as text')
|
|
->from('admins')
|
|
->andWhere(Operator::like(['login' => '%'.$this->search.'%']))
|
|
->addOrderBy('login', 'ASC');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleBatches(): string
|
|
{
|
|
$idVariation = getVal('id_variation') ?: -1;
|
|
$idVariation = $idVariation > 0 ? $idVariation : null;
|
|
|
|
$this->qb->select("id as value, CONCAT(code, ' - ',date_expiry) as text")
|
|
->from('products_batches')
|
|
->andWhere(Operator::equalsNullable([
|
|
'id_product' => getVal('id_product'),
|
|
'id_variation' => $idVariation,
|
|
]));
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleVariationsForProduct(): string
|
|
{
|
|
$idProduct = getVal('id_product') ?? -1;
|
|
|
|
$this->qb->select('id as value, title as text')
|
|
->from('products_variations')
|
|
->where(Operator::equals(['id_product' => $idProduct]));
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleFeedTypes()
|
|
{
|
|
$feedLocator = ServiceContainer::getService(FeedLocator::class);
|
|
$types = array_values(Mapping::withKeys($feedLocator->getAliases(), fn ($k, $v) => ['value' => $k, 'text' => $v]));
|
|
|
|
return json_encode($types);
|
|
}
|
|
|
|
public function handlePreorders()
|
|
{
|
|
if (!findModule(\Modules::B2B_PREORDERS)) {
|
|
return '';
|
|
}
|
|
|
|
$preorderAutocompleteType = \KupShop\PreordersBundle\Autocomplete\AutocompleteType::from(
|
|
getVal('entity_type', default: \KupShop\PreordersBundle\Autocomplete\AutocompleteType::Preorders->value),
|
|
);
|
|
|
|
$result = (new \KupShop\PreordersBundle\Autocomplete\PreorderAutocomplete())
|
|
->autocomplete(
|
|
type: $preorderAutocompleteType,
|
|
term: getVal('term', default: ''),
|
|
);
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleReturns_deliveries(): string
|
|
{
|
|
if (!findModule(\Modules::RETURNS)) {
|
|
return '';
|
|
}
|
|
|
|
$result = (new \KupShop\ReturnsBundle\Util\ReturnsAutocomplete())
|
|
->autocompleteReturnDeliveries(getVal('term', default: ''));
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleWarehouse_positions(): string
|
|
{
|
|
if (!findModule(\Modules::WAREHOUSE)) {
|
|
return '';
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select('wp.code AS text', 'wp.id AS value')
|
|
->from('warehouse_positions', 'wp')
|
|
->andWhere(\KupShop\CatalogBundle\Query\Search::searchFields($this->search, [
|
|
['field' => 'wp.code', 'match' => 'both'],
|
|
['field' => 'wp.id', 'match' => 'both'],
|
|
]))
|
|
->addOrderBy('wp.code', 'ASC')
|
|
->addOrderBy('wp.id', 'ASC')
|
|
->addGroupBy('wp.id');
|
|
|
|
if ($rawLocationIds = getVal('id_locations')) {
|
|
$locations = array_filter(explode(',', $rawLocationIds), fn (string $l) => ctype_digit($l));
|
|
|
|
$qb->andWhere(Operator::inIntArray($locations, 'wp.id_location'));
|
|
}
|
|
|
|
return json_encode($qb->execute()->fetchAllAssociative());
|
|
}
|
|
|
|
public function handleInvoice(): string
|
|
{
|
|
if (!findModule(Modules::INVOICES)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, name as text')
|
|
->from('invoice_numbers')
|
|
->orderBy('name');
|
|
|
|
if ($this->search) {
|
|
$this->qb->andWhere(Operator::like(['name' => '%'.$this->search.'%']));
|
|
}
|
|
|
|
$result = $this->qb->execute()->fetchAll();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleVats(): string
|
|
{
|
|
// TODO: snížená, základní
|
|
$qb = sqlQueryBuilder()->select('id as value, vat as text')
|
|
->from('vats');
|
|
|
|
if ($country = getVal('country')) {
|
|
$qb->andWhere(Operator::equals(['id_country' => $country]));
|
|
}
|
|
|
|
$result = $qb->execute()->fetchAllAssociative();
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
public function handleFragments(): string
|
|
{
|
|
$this->qb->select('COALESCE(name, code) as text, COALESCE(name, code) as label, code as value')
|
|
->from('pages')
|
|
->andWhere(Operator::equals(['type' => \KupShop\ContentBundle\Page\FragmentPage::getType()]))
|
|
->andWhere(Operator::like(['name' => '%'.$this->search.'%', 'code' => '%'.$this->search.'%'], 'OR'))
|
|
->orderBy('name', 'ASC');
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function handleUnits(): string
|
|
{
|
|
if (!findModule(Modules::PRODUCTS, Modules::SUB_UNITS)) {
|
|
return '';
|
|
}
|
|
|
|
$qb = sqlQueryBuilder()->select('id as value, COALESCE(short_name_admin, short_name) as text')
|
|
->from('products_units')
|
|
->andWhere(
|
|
Operator::like([
|
|
'short_name_admin' => '%'.$this->search.'%',
|
|
'short_name' => '%'.$this->search.'%',
|
|
'long_name' => '%'.$this->search.'%',
|
|
], 'OR')
|
|
)
|
|
->execute()
|
|
->fetchAllAssociative();
|
|
|
|
array_unshift($qb, ['value' => '', 'text' => '--']);
|
|
|
|
return json_encode($qb);
|
|
}
|
|
|
|
public function handleUserAddresses(): string
|
|
{
|
|
$id_user = getVal('id_user');
|
|
if (!findModule(Modules::USER_ADDRESSES) || empty($id_user)) {
|
|
return '';
|
|
}
|
|
|
|
$this->qb->select('id as value, CONCAT_WS(" ", delivery_name, delivery_surname, delivery_street, delivery_city) as text')
|
|
->from('users_addresses')
|
|
->andWhere(Operator::equals(['id_user' => $id_user]))
|
|
->andWhere(\KupShop\CatalogBundle\Query\Search::searchFields(
|
|
$this->search,
|
|
[
|
|
['field' => 'delivery_name', 'match' => 'both'],
|
|
['field' => 'delivery_surname', 'match' => 'both'],
|
|
['field' => 'delivery_firm', 'match' => 'both'],
|
|
['field' => 'delivery_city', 'match' => 'both'],
|
|
['field' => 'delivery_street', 'match' => 'both'],
|
|
['field' => 'delivery_zip', 'match' => 'both'],
|
|
]
|
|
));
|
|
|
|
return $this->getResult();
|
|
}
|
|
|
|
public function prepareSearchFields($fields)
|
|
{
|
|
return get_search_query($this->search, $fields);
|
|
}
|
|
|
|
public function prepareSearch($search)
|
|
{
|
|
return sqlFormatInput(trim($search));
|
|
}
|
|
|
|
public function getResult()
|
|
{
|
|
return json_encode($this->qb->execute()->fetchAll());
|
|
}
|
|
|
|
public function getTermFlags(&$term, $flags_str)
|
|
{
|
|
$flags = [];
|
|
|
|
$term = preg_replace_callback('/\s?(['.$flags_str.']):\s/', function ($matches) use (&$flags) {
|
|
$flags[] = $matches[1];
|
|
|
|
return '';
|
|
}, $term);
|
|
|
|
return $flags;
|
|
}
|
|
|
|
public function fallback($type, $term)
|
|
{
|
|
$cfg = Config::get();
|
|
|
|
$only_visible = getVal('visible', null, true);
|
|
$limit = getVal('limit', null, 100);
|
|
$group = null;
|
|
$where = ' 1 ';
|
|
$recursive = '';
|
|
$result = [];
|
|
|
|
$none = getVal('none');
|
|
if ($none && !$term) {
|
|
$result[] = ['value' => '', 'label' => $none, 'text' => $none];
|
|
}
|
|
|
|
$data = [];
|
|
|
|
if ($limit < 0) {
|
|
$limit = 999999;
|
|
}
|
|
|
|
switch ($type) {
|
|
case 'variation_id':
|
|
$fields = 'pv.title as label, pv.id as value, pv.code, pv.in_store';
|
|
$from = getTableName('products_variations').' pv
|
|
LEFT JOIN '.getTableName('products_variations_combination').' pvc ON pvc.id_variation=pv.id
|
|
LEFT JOIN '.getTableName('products_variations_choices_values').' pvcv ON pvc.id_value=pvcv.id';
|
|
$where = "pv.id_product='".sqlFormatInput($_GET['id_product'])."'";
|
|
$group = 'pv.id';
|
|
$order = 'pvcv.sort';
|
|
if (!empty($type)) {
|
|
$where .= " AND pv.title LIKE '%{$term}%'";
|
|
}
|
|
break;
|
|
|
|
case 'variation':
|
|
$fields = 'pv.title as label';
|
|
$from = getTableName('products_variations').' pv
|
|
LEFT JOIN '.getTableName('products_variations_combination').' pvc ON pvc.id_variation=pv.id
|
|
LEFT JOIN '.getTableName('products_variations_choices_values').' pvcv ON pvc.id_value=pvcv.id';
|
|
$where = "pv.id_product='".sqlFormatInput($_GET['id_product'])."'";
|
|
$group = 'pv.id';
|
|
$order = 'pvcv.sort';
|
|
if (!empty($type)) {
|
|
$where .= " AND pv.title LIKE '%{$term}%'";
|
|
}
|
|
break;
|
|
|
|
case 'product_id':
|
|
$fields = 'COALESCE(NULLIF(p.title,\'\'), CONCAT(:productNameEmpty,\' \', p.id)) as text,
|
|
CONCAT_WS(\' | \',p.title, p.code) as label, p.id as id, p.id as value, pv.id as value_variation, p.figure as visible, p.in_store, p.in_store as inStore, p.code,
|
|
p.id as id_rel_product, p.title as title, pv.title as title_variation, p.ean as ean, pv.ean as ean_variation';
|
|
$from = getTableName('products').' p LEFT JOIN '.getTableName('products_variations').' pv ON pv.id_product=p.id ';
|
|
|
|
$fields .= ' ,v.vat as vat';
|
|
$from .= ' LEFT JOIN vats v ON v.id = p.vat';
|
|
|
|
$section = getVal('section');
|
|
if (!empty($section)) {
|
|
$sections = getDescendantCategories($section);
|
|
$from .= ' LEFT JOIN products_in_sections ps ON p.id = ps.id_product';
|
|
$where .= ' AND ps.id_section IN ('.join(',', array_values($sections)).')';
|
|
}
|
|
|
|
$except_product = getVal('except_product');
|
|
if (!empty($except_product)) {
|
|
$where .= " AND p.id!={$except_product} ";
|
|
}
|
|
|
|
$ep = getVal('ep');
|
|
if (is_array($ep)) {
|
|
$where .= ' AND p.id NOT IN ('.join(',', array_values($ep)).')';
|
|
}
|
|
|
|
if ($only_visible) {
|
|
$where .= " AND p.figure='Y' ";
|
|
}
|
|
|
|
$flags = $this->getTermFlags($term, 'sv'); // flags - s: in_store, v: visible
|
|
foreach ($flags as $flag) {
|
|
switch ($flag) {
|
|
case 's':
|
|
$where .= ' AND p.in_store > 0 ';
|
|
break;
|
|
case 'v':
|
|
$where .= " AND p.figure = 'Y' ";
|
|
break;
|
|
}
|
|
}
|
|
|
|
$search_fields = [
|
|
['field' => 'p.title', 'match' => 'both'],
|
|
['field' => 'p.code', 'match' => 'both'],
|
|
['field' => 'p.ean', 'match' => 'numeric'],
|
|
['field' => 'p.id', 'match' => 'numeric'],
|
|
['field' => 'pv.ean', 'match' => 'numeric'],
|
|
];
|
|
|
|
$where .= ' AND (';
|
|
$order = 'exact_match DESC, p.title';
|
|
if (!$only_visible) {
|
|
$order = 'exact_match DESC, FIELD(p.figure, "Y", "N", "O")';
|
|
}
|
|
|
|
if (findModule('products', 'weight')) {
|
|
if (findModule('products_variations')) {
|
|
$fields .= ', COALESCE(pv.weight, p.weight) as weight';
|
|
} else {
|
|
$fields .= ', p.weight as weight';
|
|
}
|
|
}
|
|
|
|
$fromVariation = 0;
|
|
if (findModule('products_variations', 'variationCode')) {
|
|
$search_fields[] = ['field' => 'pv.code', 'match' => 'both'];
|
|
$fields .= ', IF(pv.ean LIKE :search_0 OR pv.code LIKE :field_0_both, 1, 0) as from_variation, IF(p.ean = :search_0 OR p.code LIKE :search_0 OR pv.ean = :search_0 OR pv.code LIKE :search_0, 1, 0) as exact_match ';
|
|
$fromVariation = null;
|
|
}
|
|
|
|
$search = get_search_query($term, $search_fields);
|
|
$where .= $search['where'];
|
|
$data = $search['data'];
|
|
|
|
$data['productNameEmpty'] = translate('product', 'products');
|
|
|
|
if (getVal('supplier') && (findModule(Modules::PRODUCTS_SUPPLIERS)
|
|
|| findModule(Modules::SUPPLIERS)
|
|
|| findModule(Modules::STOCK_IN)
|
|
|| findModule('automatic_import'))) {
|
|
$supplier = getVal('supplier');
|
|
|
|
$where_pos = '';
|
|
if ($supplier != '*') {
|
|
$where_pos = ' AND pos.id_supplier=:supplier';
|
|
}
|
|
|
|
if ($only_visible) {
|
|
$where_pos .= " AND p.figure='Y' ";
|
|
}
|
|
|
|
$SQL = sqlQuery("SELECT p.title as label, p.id as value, pos.id_variation as value_variation, 1 as from_variation, 1 as from_supplier, 1 as exact_match,
|
|
ph.id as id_photo, ph.source, ph.image_2
|
|
FROM products_of_suppliers pos
|
|
LEFT JOIN products p ON pos.id_product=p.id
|
|
LEFT JOIN photos_products_relation ppr ON ppr.id_product=p.id AND ppr.show_in_lead = \"Y\"
|
|
LEFT JOIN photos ph ON ph.id=ppr.id_photo
|
|
WHERE (pos.code LIKE :term OR pos.ean LIKE :term) {$where_pos}", ['supplier' => $supplier, 'term' => $term]);
|
|
$result = array_merge($result, $SQL->fetchAll());
|
|
} else {
|
|
$fields .= ', 0 as from_supplier';
|
|
}
|
|
|
|
if ($fromVariation !== null) {
|
|
$fields .= ", {$fromVariation} as from_variation";
|
|
}
|
|
|
|
if (empty($cfg['Modules']['products_variations']['variationCode'])) {
|
|
$fields .= ", IF(p.ean LIKE '{$term}' OR p.code LIKE '{$term}', 1, 0) as exact_match ";
|
|
}
|
|
|
|
$collections = getVal('collections', null, false);
|
|
if (findModule(Modules::PRODUCTS_COLLECTIONS, Modules::SUB_DENIED_MULTIPLE) && $collections) {
|
|
$from .= ' LEFT JOIN products_collections pc ON pc.id_product_related=p.id ';
|
|
$where .= ' AND pc.id_product IS NULL ';
|
|
}
|
|
|
|
// join photo
|
|
$fields .= ', ph.id as id_photo, ph.source, ph.image_2';
|
|
$from .= ' LEFT JOIN photos_products_relation ppr ON ppr.id_product=p.id AND ppr.show_in_lead = "Y"
|
|
LEFT JOIN photos ph ON ph.id=ppr.id_photo';
|
|
|
|
/*
|
|
* Tohle je fakt hnus kvuli enika.cz.
|
|
* Pokud je nejakej master produkt, tak by se v autocompletu kolekce nemeli nabizet dalsi master produkty.
|
|
*
|
|
* Az se predel autocomplete do class, tak vyhodit do specky jen na shop eniky
|
|
*/
|
|
$hidden_master_product = getVal('hidden_master_product', null, false);
|
|
if (findModule(Modules::PRODUCTS_COLLECTIONS, 'hidden_master_product') && $hidden_master_product) {
|
|
$where .= ' AND NOT FIND_IN_SET(\'H\', p.campaign) ';
|
|
}
|
|
$where .= ')';
|
|
|
|
$group = 'p.id';
|
|
|
|
if (getVal('variations')) {
|
|
$fields = $this->getAutocompleteVariationsFields($fields);
|
|
$from .= ' LEFT JOIN '.getTableName('products_variations_combination').' pvc ON pvc.id_variation=pv.id
|
|
LEFT JOIN '.getTableName('products_variations_choices_values').' pvcv ON pvc.id_value=pvcv.id';
|
|
}
|
|
|
|
if ($productId = getVal('exactProductId')) {
|
|
$where .= ' AND p.id = '.$productId;
|
|
}
|
|
|
|
break;
|
|
|
|
case 'order_id':
|
|
$fields = 'o.id, o.order_no, o.delivery_surname, o.delivery_name, o.delivery_firm, o.total_price';
|
|
if (findModule(Modules::CURRENCIES)) {
|
|
$fields .= ', o.currency';
|
|
}
|
|
$from = getTableName('orders').' o ';
|
|
$where = 'o.delivery_surname LIKE :term
|
|
OR o.delivery_name LIKE :term
|
|
OR o.delivery_firm LIKE :term
|
|
OR o.invoice_email LIKE :term
|
|
OR o.order_no LIKE :term_left
|
|
OR o.id = :term_exact';
|
|
$data['term'] = "%{$term}%";
|
|
$data['term_left'] = "%{$term}";
|
|
$data['term_exact'] = "{$term}";
|
|
$order = 'o.id DESC';
|
|
|
|
if (getVal('only_active', null, 0)) {
|
|
$where = " ( {$where} ) AND o.status IN (".join(',', getStatuses('active')).')';
|
|
}
|
|
break;
|
|
|
|
case 'category':
|
|
case 'categories':
|
|
$recursive = "WITH RECURSIVE cte (id_section, id_topsection, path, name, figure, position, depth, full_position) as (
|
|
SELECT id_section,
|
|
id_topsection,
|
|
(SELECT name FROM sections WHERE id = id_section LIMIT 1) as path,
|
|
(SELECT name FROM sections WHERE id = id_section LIMIT 1) as name,
|
|
(SELECT figure FROM sections WHERE id = id_section LIMIT 1) as figure,
|
|
position,
|
|
1 AS depth,
|
|
CAST(LPAD(position, 5, 0) AS CHAR(500)) AS full_position
|
|
FROM sections_relation
|
|
WHERE id_topsection IS NULL
|
|
UNION ALL
|
|
SELECT sr.id_section,
|
|
sr.id_topsection,
|
|
CONCAT(cte.path,' / ',(SELECT name FROM sections WHERE id = sr.id_section LIMIT 1)) as path,
|
|
(SELECT name FROM sections WHERE id = sr.id_section LIMIT 1) as name,
|
|
(SELECT figure FROM sections WHERE id = sr.id_section LIMIT 1) as figure,
|
|
sr.position,
|
|
depth + 1,
|
|
CONCAT(full_position, '/', LPAD(sr.position, 5, 0))
|
|
FROM sections_relation sr
|
|
INNER JOIN cte
|
|
on sr.id_topsection = cte.id_section
|
|
)";
|
|
$fields = 's.id_section as value, s.name as label, s.path as text';
|
|
$order = 's.full_position ASC, text ASC';
|
|
$from = 'cte as s';
|
|
$search_fields = [
|
|
['field' => 's.path', 'match' => 'both'],
|
|
];
|
|
$search = get_search_query($term, $search_fields);
|
|
$where .= ' AND '.$search['where'];
|
|
$data = $search['data'];
|
|
if ($only_visible) {
|
|
$where .= " AND s.figure='Y' ";
|
|
}
|
|
break;
|
|
|
|
case 'articles_sections':
|
|
$recursive = "WITH RECURSIVE cte (id, top_branch, path, name, figure, position, depth) as (
|
|
SELECT id,
|
|
top_branch,
|
|
name AS path,
|
|
name,
|
|
figure,
|
|
position,
|
|
1 AS depth
|
|
FROM articles_branches
|
|
WHERE top_branch IS NULL
|
|
UNION ALL
|
|
SELECT sr.id,
|
|
sr.top_branch,
|
|
CONCAT(cte.name,' / ',(SELECT name FROM articles_branches WHERE id = sr.id LIMIT 1)) as path,
|
|
sr.name,
|
|
sr.figure,
|
|
sr.position,
|
|
depth + 1
|
|
FROM articles_branches sr
|
|
INNER JOIN cte ON sr.top_branch = cte.id
|
|
)";
|
|
$fields = 's.id as value, s.name as label, s.path as text';
|
|
$order = 'text ASC';
|
|
$from = 'cte as s';
|
|
$search = get_search_query($term, [['field' => 's.path', 'match' => 'both']]);
|
|
$where .= ' AND '.$search['where'];
|
|
$data = $search['data'];
|
|
if ($only_visible) {
|
|
$where .= " AND s.figure='Y' ";
|
|
}
|
|
break;
|
|
case 'user':
|
|
case 'user_name':
|
|
$fields = 'u.id, u.firm, u.name, u.surname, u.email, u.ico, u.city, u.street';
|
|
$from = getTableName('users').' AS u ';
|
|
if (findModule('price_levels')) {
|
|
$fields .= ', udpl.id_price_level';
|
|
$from .= 'LEFT JOIN '.getTableName('users_dealer_price_level').' AS udpl ON u.id=udpl.id_user ';
|
|
}
|
|
$where .= " AND u.figure='Y' ";
|
|
$search_fields = [
|
|
['field' => 'u.name', 'match' => 'both'],
|
|
['field' => 'u.surname', 'match' => 'both'],
|
|
['field' => 'u.firm', 'match' => 'both'],
|
|
['field' => 'u.ico', 'match' => 'both'],
|
|
['field' => 'u.dic', 'match' => 'both'],
|
|
['field' => 'u.email', 'match' => 'both'],
|
|
];
|
|
$search = get_search_query($term, $search_fields);
|
|
$where .= ' AND '.$search['where'];
|
|
$data = $search['data'];
|
|
$order = 'u.surname ASC';
|
|
break;
|
|
|
|
case 'discount':
|
|
$fields = 'id, descr, discount, unit, condition_value';
|
|
$from = getTableName('discounts').' ';
|
|
$where = "descr LIKE '%{$term}%'
|
|
OR condition_value LIKE '%{$term}%'";
|
|
$order = 'descr ASC';
|
|
break;
|
|
|
|
case 'generated_coupons':
|
|
$fields = 'descr as text, descr as label, id as value';
|
|
$from = 'discounts';
|
|
$where = 'descr LIKE :descr ';
|
|
$order = 'descr ASC';
|
|
$data['descr'] = "%{$term}%";
|
|
break;
|
|
|
|
case 'page':
|
|
$fields = 'name as label, name as text, id as value';
|
|
$from = 'menu_links hp';
|
|
$where = "hp.name LIKE '%{$term}%' AND hp.type = '".\KupShop\ContentBundle\Util\MenuUtil::TYPE_PAGE."' ";
|
|
$order = 'hp.name';
|
|
break;
|
|
|
|
case 'menu_links':
|
|
$fields = 'name as label, name as text, id as value';
|
|
$from = 'menu_links hp';
|
|
$where = "hp.name LIKE '%{$term}%' ";
|
|
$order = 'hp.name';
|
|
break;
|
|
|
|
case 'article':
|
|
case 'articles':
|
|
$fields = 'a.title as label, a.title as text, id as value';
|
|
$from = getTableName('articles').' a';
|
|
$where = "a.title LIKE '%{$term}%' ";
|
|
$order = 'a.title';
|
|
break;
|
|
|
|
case 'articles_tags':
|
|
$fields = 'id as value, tag as text';
|
|
$order = 'tag';
|
|
$from = 'articles_tags';
|
|
$where = " tag LIKE '%{$term}%' ";
|
|
break;
|
|
|
|
case 'product_charge':
|
|
$fields = 'c.id as value, IF(c.admin_title != "", c.admin_title, title) as text';
|
|
$from = 'charges as c';
|
|
$where = " (c.title LIKE '%{$term}%' OR c.admin_title LIKE '%{$term}%') AND c.type='product' ";
|
|
break;
|
|
|
|
case 'order_charge':
|
|
$fields = 'c.id as value, IF(c.admin_title != "", c.admin_title, title) as text';
|
|
$from = 'charges as c';
|
|
$where = " (c.title LIKE '%{$term}%' OR c.admin_title LIKE '%{$term}%') AND c.type='order' ";
|
|
break;
|
|
|
|
case 'feed_seznam':
|
|
$fields = "CONCAT(name, ' - ', category_text) as text, id as value";
|
|
$from = 'kupshop_shared.feed_seznam fs';
|
|
$where = "name LIKE '%{$term}%' OR category_text LIKE '%{$term}%' ";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'feed_google':
|
|
$fields = "CONCAT(name, ' - ', category_text) as text, id as value";
|
|
$from = 'kupshop_shared.feed_google';
|
|
$where = "(name LIKE '%{$term}%' OR category_text LIKE '%{$term}%') AND id!=''";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'feed_google_old':
|
|
$fields = "CONCAT(name, ' - ', category_text) as text,id_old as value";
|
|
$from = 'kupshop_shared.feed_google';
|
|
$where = "name LIKE '%{$term}%' AND id!=''";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'feed_heureka':
|
|
$fields = "CONCAT(name, ' - ', category_text) as text, id as value";
|
|
$from = 'kupshop_shared.feed_heureka ';
|
|
$where = "name LIKE '%{$term}%' OR category_text LIKE '%{$term}%' ";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'feed_heureka_sk':
|
|
$fields = "CONCAT(name, ' - ', category_text) as text, id as value";
|
|
$from = 'kupshop_shared.feed_heureka_sk ';
|
|
$where = "name LIKE '%{$term}%' OR category_text LIKE '%{$term}%' ";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'feed_glami':
|
|
$fields = "CONCAT(name, ' - ', category_text) as text, id as value";
|
|
$from = 'kupshop_shared.feed_glami ';
|
|
$where = "name LIKE '%{$term}%' OR category_text LIKE '%{$term}%' ";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'producer':
|
|
case 'producers':
|
|
$fields = 'name as text, name as label, id as value';
|
|
$from = 'producers as pr';
|
|
$where = 'pr.name LIKE :name ';
|
|
$order = 'pr.name';
|
|
$data['name'] = "%{$term}%";
|
|
break;
|
|
|
|
case 'parameters_list':
|
|
$fields = "CONCAT(CONCAT(pa.name, ' - '), pl.value) AS text, pl.id as value";
|
|
$from = 'parameters_list AS pl JOIN parameters AS pa ON pa.id = pl.id_parameter';
|
|
$search_fields = [
|
|
['field' => 'pa.name', 'match' => 'both'],
|
|
['field' => 'pl.value', 'match' => 'both'],
|
|
];
|
|
$search = get_search_query($term, $search_fields);
|
|
$where .= ' AND '.$search['where'];
|
|
$data = $search['data'];
|
|
$order = 'pa.name, pl.value';
|
|
$group = 'pl.id';
|
|
break;
|
|
|
|
case 'parameter_groups':
|
|
$fields = 'pg.name AS text, pg.id as value';
|
|
$from = 'parameter_groups AS pg ';
|
|
$search_fields = [
|
|
['field' => 'pg.name', 'match' => 'both'],
|
|
['field' => 'pg.id', 'match' => 'both'],
|
|
];
|
|
$search = get_search_query($term, $search_fields);
|
|
$where .= ' AND '.$search['where'];
|
|
$data = $search['data'];
|
|
$order = 'pg.name, pg.id';
|
|
$group = 'pg.id';
|
|
|
|
$result = array_merge([['text' => translate('notSelected', 'products'), 'value' => '']], $result);
|
|
break;
|
|
|
|
case 'warehouse_locations':
|
|
$fields = 'wl.code AS text, wl.id AS value';
|
|
$from = 'warehouse_locations AS wl ';
|
|
$search = get_search_query($term, [
|
|
['field' => 'wl.code', 'match' => 'both'],
|
|
['field' => 'wl.id', 'match' => 'both'],
|
|
]);
|
|
$where .= ' AND '.$search['where'];
|
|
$data = $search['data'];
|
|
$order = 'wl.code, wl.id';
|
|
$group = 'wl.id';
|
|
break;
|
|
|
|
case 'suppliers':
|
|
$fields = 'name as text, name as label, id as value';
|
|
$from = 'suppliers as sup';
|
|
$where = 'sup.name LIKE :name ';
|
|
$order = 'sup.name';
|
|
$data['name'] = "%{$term}%";
|
|
break;
|
|
|
|
case 'users_groups':
|
|
$fields = 'id as value, name as text';
|
|
$from = 'users_groups';
|
|
$where = 'name LIKE :name ';
|
|
$data['name'] = "%{$term}%";
|
|
$order = 'name';
|
|
break;
|
|
|
|
case 'order_messages':
|
|
$fields = 'name as text, name as label, id as value';
|
|
$from = 'emails';
|
|
$where = "name LIKE '%{$term}%' OR subject LIKE '%{$term}%' ";
|
|
$order = 'name';
|
|
break;
|
|
|
|
default:
|
|
case 'products':
|
|
case 'product':
|
|
$fields = 'title as label, title as text, id as value';
|
|
$from = getTableName('products').' p';
|
|
$where = "p.figure='Y' AND p.title LIKE '%{$term}%'";
|
|
$order = 'p.title';
|
|
break;
|
|
}
|
|
|
|
if ($where) {
|
|
$where = " WHERE {$where} ";
|
|
}
|
|
|
|
if ($group) {
|
|
$where .= " GROUP BY {$group} ";
|
|
}
|
|
|
|
if ($order ?? false) {
|
|
$where .= " ORDER BY {$order} ";
|
|
}
|
|
|
|
$SQL = sqlQuery("{$recursive} SELECT {$fields}
|
|
FROM {$from}
|
|
{$where} LIMIT {$limit}", $data);
|
|
|
|
$result = array_merge($result, $SQL->fetchAll());
|
|
switch ($type) {
|
|
case 'product_id':
|
|
foreach ($result as &$product) {
|
|
if (isset($product['variations'])) {
|
|
$product['variations'] = array_filter(json_decode($product['variations'], true) ?: [], fn ($var) => !empty($var['id']));
|
|
}
|
|
$product['image'] = getImage($product['id_photo'], $product['image_2'], $product['source'], 4);
|
|
$product['in_store'] = floatval($product['in_store'] ?? 0);
|
|
$product['inStore'] = floatval($product['inStore'] ?? 0);
|
|
}
|
|
break;
|
|
|
|
case 'order_id':
|
|
foreach ($result as &$order) {
|
|
$params = [];
|
|
if (key_exists('currency', $order)) {
|
|
$params['currency'] = $order['currency'];
|
|
}
|
|
$order['label'] = "{$order['order_no']} - {$order['delivery_surname']} {$order['delivery_name']} - ".printPrice($order['total_price'], $params);
|
|
$order['value'] = $order['id'];
|
|
}
|
|
break;
|
|
|
|
case 'user':
|
|
foreach ($result as &$user) {
|
|
$user['text'] = $user['label'] = "{$user['firm']} {$user['name']} {$user['surname']} - {$user['email']}";
|
|
$user['value'] = $user['id'];
|
|
}
|
|
break;
|
|
|
|
case 'user_name':
|
|
foreach ($result as &$user) {
|
|
$user['text'] = $user['label'] = "{$user['name']} {$user['surname']} {$user['firm']}";
|
|
$user['value'] = $user['id'];
|
|
}
|
|
break;
|
|
|
|
case 'discount':
|
|
foreach ($result as &$discount) {
|
|
$discount['label'] = "{$discount['descr']} ({$discount['condition_value']}) - {$discount['discount']} {$discount['unit']}";
|
|
$discount['value'] = $discount['condition_value'];
|
|
}
|
|
break;
|
|
|
|
case 'category':
|
|
case 'categories':
|
|
if (getVal('allow_empty')) {
|
|
array_unshift($result, ['text' => getVal('empty_choice', default: 'Nevybráno'), 'value' => 0]);
|
|
}
|
|
break;
|
|
case 'parameters_list':
|
|
foreach ($result as &$value) {
|
|
$value['label'] = $value['text'];
|
|
}
|
|
break;
|
|
case 'menu_links':
|
|
array_push($result, [
|
|
'label' => translate('unclassifiedItems', 'menulinks'),
|
|
'text' => translate('unclassifiedItems', 'menulinks'),
|
|
'value' => 'null',
|
|
]);
|
|
break;
|
|
case 'users_groups':
|
|
if (getVal('allow_empty')) {
|
|
array_unshift($result, [
|
|
'label' => '-- v žádné skupině --',
|
|
'text' => '-- v žádné skupině --',
|
|
'value' => -1,
|
|
]);
|
|
}
|
|
break;
|
|
// case 'feed_seznam':
|
|
// case 'feed_seznam':
|
|
// case 'feed_google':
|
|
// case 'feed_google_old':
|
|
// case 'feed_heureka':
|
|
// $result = array_merge([[
|
|
// 'text' => 'Vyberte sekci',
|
|
// 'value' => '',
|
|
// ]], $result);
|
|
// break;
|
|
}
|
|
|
|
return json_encode($result);
|
|
}
|
|
|
|
protected function getAutocompleteVariationsFields(string $fields): string
|
|
{
|
|
$fields .= ', CONCAT("[",GROUP_CONCAT(DISTINCT JSON_OBJECT("id", pv.id, "title", pv.title, "in_store", pv.in_store, "code",COALESCE(pv.code, ""), "ean", COALESCE(pv.ean, ""), "price",COALESCE(pv.price, p.price)) ORDER BY pvcv.sort),"]") variations';
|
|
|
|
return $fields;
|
|
}
|
|
}
|