kupshop/bundles/KupShop/AdminBundle/Controller/AutocompletePreload.php

<?php

namespace KupShop\AdminBundle\Controller;

use Doctrine\DBAL\Connection;
use KupShop\CatalogBundle\Section\SectionTree;
use KupShop\KupShopBundle\Context\CountryContext;
use KupShop\KupShopBundle\Routing\AdminRoute;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Functional\Mapping;
use Query\Operator;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;

class AutocompletePreload extends AbstractController
{
    /**
     * @var SectionTree
     */
    protected $sectionTree;

    /**
     * @AdminRoute("/autocomplete-preload")
     */
    public function indexAction(Request $request)
    {
        $types = $request->request->all('types');
        $returnTypes = [];
        foreach ($types as $type => $ids) {
            $methodName = 'load'.ucfirst($type);
            if (!is_array($ids)) {
                $ids = [$ids];
            }
            $returnTypes[$type] = $this->$methodName($ids);
        }

        return new JsonResponse($returnTypes);
    }

    /**
     * @required
     */
    public function setSectionTree(SectionTree $sectionTree)
    {
        $this->sectionTree = $sectionTree;
    }

    private function loadArticlesAuthors(array $ids): array
    {
        return sqlQueryBuilder()->select('id, CONCAT(name, " ", surname) as name')
            ->from('articles_authors')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadPages(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, name')->from('menu_links')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadFragments(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('code as id, COALESCE(name, code) name')->from('pages')
            ->where(Operator::inStringArray($ids, 'code'));
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadMenuLinks(array $ids): array
    {
        $items = [[
            'id' => 'null',
            'name' => translate('unclassifiedItems', 'menulinks', false, true),
        ]];
        $qb = sqlQueryBuilder()->select('id, name')->from('menu_links')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadArticles(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, title AS name')->from('articles')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadProduct_variation(array $ids): array
    {
        $productIds = [];

        $requestedProducts = [];
        foreach ($ids as $id) {
            $e = explode('-', $id);
            $productIds[] = $e[0];

            $requestedProducts[$e[0]][] = $e[1] ?? null;
            $requestedProducts[$e[0]] = array_filter($requestedProducts[$e[0]]);
        }

        $qb = sqlQueryBuilder()
            ->select('p.id, p.title')
            ->addSelect("JSON_ARRAYAGG(
                JSON_OBJECT(
                  'id', pv.id,
                  'title', pv.title
                )
              ) AS variations")
            ->from('products', 'p')
            ->joinVariationsOnProducts()
            ->andWhere(Operator::inIntArray($productIds, 'p.id'))
            ->groupBy('p.id');

        $items = [];
        foreach ($qb->execute() as $item) {
            // pokud hledam jen primo produkt, tak vratim ten
            if (!($search = $requestedProducts[$item['id']] ?? [])) {
                $items[] = ['id' => $item['id'], 'name' => $item['title']];

                continue;
            }

            // varianty mam nacteny jako JSON v variations fieldu
            $variations = Mapping::mapKeys(
                json_decode($item['variations'] ?: '', true) ?: [],
                fn ($k, $v) => [$v['id'], $v]
            );

            // pokud loaduju primo varianty, tak je vratim
            foreach ($search as $variationId) {
                if (!($variation = ($variations[$variationId] ?? null))) {
                    continue;
                }

                $items[] = ['id' => "{$item['id']}-{$variationId}", 'name' => "{$item['title']} - {$variation['title']}"];
            }
        }

        return $items;
    }

    private function loadProducts(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, COALESCE(NULLIF(title,\'\'), CONCAT(:productNameEmpty,\' \',id)) AS name')
            ->from('products')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY)
            ->setParameter('productNameEmpty', translate('product', 'products', false, true));
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadProducers(array $ids): array
    {
        $items = [];
        if (in_array('-1', $ids)) {
            $items[] = ['id' => '-1', 'name' => 'Bez výrobce'];
        }

        $qb = sqlQueryBuilder()->select('id, name')->from('producers')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = [
                'id' => $item['id'],
                'name' => $item['name'],
            ];
        }

        return $items;
    }

    private function loadLabels(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, name')->from('labels')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadSections(array $ids): array
    {
        $sectionTree = $this->sectionTree->get();
        $items = [];
        foreach ($ids as $id) {
            $items[] = [
                'id' => $id,
                'name' => $sectionTree->getFullPath($id, ' / ') ?: 'Bez kategorie',
            ];
        }

        return $items;
    }

    private function loadArticlesSections(array $ids): array
    {
        $articlesSections = sqlFetchAll(sqlQuery(
            "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
                    ) SELECT s.id as value, s.name as label, s.path as text
                    FROM cte as s
                     WHERE s.id IN (:ids) AND s.figure='Y' ORDER BY text ASC LIMIT 50",
            ['ids' => $ids], ['ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
        ),
            'value');

        $items = [];
        foreach ($ids as $id) {
            $items[] = [
                'id' => $id,
                'name' => $articlesSections[$id]['text'] ?? 'Nenalezeno',
            ];
        }

        return $items;
    }

    private function loadParameters(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, name')->from('parameters')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadParametersValues(array $ids): array
    {
        return sqlQueryBuilder()->select("CONCAT(CONCAT(pa.name, ' - '), pl.value) AS name, pl.id as id")
            ->from('parameters_list', 'pl')
            ->join('pl', 'parameters', 'pa', 'pa.id = pl.id_parameter')
            ->where(Operator::inIntArray($ids, 'pl.id'))
            ->groupBy('pl.id')->orderBy('pa.name')
            ->addOrderBy('pl.value')
            ->execute()
            ->fetchAll();
    }

    private function loadParametersValuesOnly(array $ids): array
    {
        return sqlQueryBuilder()->select('pl.value AS name, pl.id as id')
            ->from('parameters_list', 'pl')
            ->join('pl', 'parameters', 'pa', 'pa.id = pl.id_parameter')
            ->where(Operator::inIntArray($ids, 'pl.id'))
            ->groupBy('pl.id')->orderBy('pa.name')
            ->addOrderBy('pl.value')
            ->execute()
            ->fetchAll();
    }

    private function loadVariationsLabels(array $ids): array
    {
        $qb = sqlQueryBuilder()->select('pvcl.id as id, pvcl.label as name')
            ->from('products_variations_choices_labels', 'pvcl')
            ->andWhere(Operator::inIntArray($ids, 'pvcl.id'));

        return $qb->execute()->fetchAll();
    }

    private function loadVariationValues(array $ids): array
    {
        $qb = sqlQueryBuilder()->select('pvcv.id as id, CONCAT_WS("-", pvcl.label, pvcv.value) name')
            ->from('products_variations_choices_values', 'pvcv')
            ->join('pvcv', 'products_variations_choices_labels', 'pvcl', 'pvcv.id_label = pvcl.id')
            ->andWhere(Operator::inIntArray($ids, 'pvcv.id'));

        return $qb->execute()->fetchAll();
    }

    private function loadVariationValuesOnly(array $ids): array
    {
        $qb = sqlQueryBuilder()->select('id as id, value name')
            ->from('products_variations_choices_values')
            ->andWhere(Operator::inIntArray($ids, 'id'));

        return $qb->execute()->fetchAll();
    }

    private function loadParameterGroups(array $ids): array
    {
        return sqlQueryBuilder()->select('pg.id AS id, pg.name AS name')
            ->from('parameter_groups', 'pg')
            ->andWhere(Operator::inIntArray($ids, 'pg.id'))
            ->execute()->fetchAll();
    }

    private function loadWarehouse_locations(array $ids): array
    {
        if (!findModule(\Modules::WAREHOUSE)) {
            return [];
        }

        return sqlQueryBuilder()->select('wl.id AS id, wl.code AS name')
            ->from('warehouse_locations', 'wl')
            ->andWhere(Operator::inIntArray($ids, 'wl.id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadWarehouse_positions(array $ids): array
    {
        if (!findModule(\Modules::WAREHOUSE)) {
            return [];
        }

        return sqlQueryBuilder()->select('wp.id AS id, wp.code AS name')
            ->from('warehouse_positions', 'wp')
            ->andWhere(Operator::inIntArray($ids, 'wp.id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadPos(array $ids): array
    {
        if (!findModule(\Modules::NEW_POS)) {
            return [];
        }

        return sqlQueryBuilder()->select('wp.id AS id, wp.code AS name')
            ->from('pos', 'p')
            ->andWhere(Operator::inIntArray($ids, 'p.id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadTemplates(array $ids): array
    {
        return sqlQueryBuilder()->select('t.id AS id, CONCAT_WS(\' - \', tc.name, t.name) AS name')
            ->from('templates', 't')
            ->leftJoin('t', 'templates_categories', 'tc', 't.id_category = tc.id')
            ->andWhere(Operator::inIntArray($ids, 't.id'))
            ->execute()->fetchAll();
    }

    private function loadArticles_tags(array $ids): array
    {
        return sqlQueryBuilder()->select('at.id as id, at.tag as name')
            ->from('articles_tags', 'at')
            ->andWhere(Operator::inIntArray($ids, 'at.id'))
            ->execute()->fetchAll();
    }

    private function loadGenerated_coupons(array $ids): array
    {
        return sqlQueryBuilder()->select('d.id as id, d.descr as name')
            ->from('discounts', 'd')
            ->andWhere(Operator::equals(['condition_type' => 'generate_coupon']))
            ->andWhere(Operator::inIntArray($ids, 'd.id'))
            ->execute()->fetchAll();
    }

    private function loadProductCharge(array $ids): array
    {
        return sqlQueryBuilder()->select('c.id as id, IF(c.admin_title != "", c.admin_title, title) as name')
            ->from('charges', 'c')
            ->andWhere("type = 'product'")
            ->andWhere(Operator::inIntArray($ids, 'c.id'))
            ->execute()->fetchAll();
    }

    private function loadOrderCharge(array $ids): array
    {
        return sqlQueryBuilder()->select('c.id as id, IF(c.admin_title != "", c.admin_title, title) as name')
            ->from('charges', 'c')
            ->andWhere("type = 'order'")
            ->andWhere(Operator::inIntArray($ids, 'c.id'))
            ->execute()->fetchAll();
    }

    private function loadOssVatName(array $ids): array
    {
        $cns = sqlQueryBuilder()->select("cnkey AS id, CONCAT(IFNULL(cn, ''), ' >> ', description) AS name")
            ->from('kupshop_shared.oss_vats_categories')
            ->where(Operator::inStringArray($ids, 'cnkey'))
            ->execute()->fetchAllAssociative();

        $exceptions = sqlQueryBuilder()->select('-id AS id, description AS name')
            ->from('vats_oss_exceptions')
            ->where(Operator::inStringArray($ids, '-id'))
            ->execute()->fetchAllAssociative();

        return array_merge($cns, $exceptions);
    }

    private function loadCountries(array $ids): array
    {
        if (!findModule(\Modules::CURRENCIES)) {
            // no I18nBundle => table `countries` doesn't exist
            $countries = Contexts::get(CountryContext::class)->getAll();
            $return = [];

            foreach ($ids as $id) {
                if ($country = $countries[$id] ?? null) {
                    $return[] = ['id' => $country->getId(), 'name' => $country->getName()];
                }
            }

            return $return;
        }

        return sqlQueryBuilder()->select('c.id, c.name')
            ->from('countries', 'c')
            ->where(Operator::inStringArray($ids, 'c.id'))
            ->execute()->fetchAll();
    }

    private function loadCurrencies(array $ids): array
    {
        return sqlQueryBuilder()->select('c.id, c.name')
            ->from('currencies', 'c')
            ->where(Operator::inStringArray($ids, 'c.id'))
            ->execute()->fetchAll();
    }

    private function loadUsersGroups(array $ids): array
    {
        $groups = sqlQueryBuilder()->select('ug.id, ug.name')
            ->from('users_groups', 'ug')
            ->andWhere(Operator::inIntArray($ids, 'ug.id'))
            ->execute()->fetchAll();

        $groups[] = [
            'id' => -1,
            'name' => '-- v žádné skupině --',
        ];

        return $groups;
    }

    private function loadUsers_groups(array $ids): array
    {
        return $this->loadUsersGroups($ids);
    }

    private function loadPriceLevels(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('id, name')
            ->from('price_levels')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadOrderDiscountsTypes(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('id, name')
            ->from('order_discounts_types')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    public function loadProductsRelatedTypes(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('id, name')
            ->from('products_related_types')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadUsers(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('id, CONCAT(CONCAT_WS(" ", NULLIF(firm, ""), NULLIF(name, ""), NULLIF(surname, "")), " - ", email) as name')
            ->from('users')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadDeliveryTypes(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('dt.id as id, CONCAT(COALESCE(dtd.name_admin, dtd.name), " - ", COALESCE(dtp.name_admin, dtp.name)) as name')
            ->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')
            ->andWhere(Operator::inIntArray($ids, 'dt.id'))
            ->orderBy('dt.id')
            ->groupBy('dt.id')
            ->execute()->fetchAllAssociative();
    }

    private function loadDeliveries(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('id, COALESCE(name_admin, name) as name')
            ->from('delivery_type_delivery')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadPayments(array $ids): array
    {
        return sqlQueryBuilder()
            ->select('id, name')
            ->from('delivery_type_payment')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadPriceLists(array $ids): array
    {
        if (!findModule(\Modules::PRICELISTS)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('pricelists')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadSuppliers(array $ids): array
    {
        if (!findModule(\Modules::SUPPLIERS)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('suppliers')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadStores(array $ids): array
    {
        if (!findModule(\Modules::STORES)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('stores')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadOrder_messages(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, name')->from('emails')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadOrders(array $ids): array
    {
        $items = [];
        $qb = sqlQueryBuilder()->select('id, order_no as name')->from('orders')
            ->where('id IN (:ids)')
            ->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY);
        foreach ($qb->execute() as $item) {
            $items[] = ['id' => $item['id'], 'name' => $item['name']];
        }

        return $items;
    }

    private function loadLanguages(array $ids): array
    {
        if (!findModule(\Modules::TRANSLATIONS)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('languages')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAll();
    }

    private function loadConvertors(array $ids): array
    {
        if (!findModule(\Modules::CONVERTORS)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('convertors_definition')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadOrder_discount(array $ids): array
    {
        if (!findModule(\Modules::ORDER_DISCOUNT)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('order_discounts')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadSliders(array $ids): array
    {
        if (!findModule(\Modules::SLIDERS)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('sliders')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadVariations(array $ids)
    {
        if (!findModule(\Modules::PRODUCTS_VARIATIONS)) {
            return [];
        }

        return sqlQueryBuilder()->select('id, title name')
            ->from('products_variations')
            ->where(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadPreorders(array $ids): array
    {
        if (!findModule(\Modules::B2B_PREORDERS)) {
            return [];
        }

        $preloader = new \KupShop\PreordersBundle\Autocomplete\PreorderAutocomplete();

        return $preloader->preload(
            \KupShop\PreordersBundle\Autocomplete\AutocompleteType::Preorders,
            $ids,
        );
    }

    private function loadPreorder_dates(array $ids): array
    {
        if (!findModule(\Modules::B2B_PREORDERS)) {
            return [];
        }

        $preloader = new \KupShop\PreordersBundle\Autocomplete\PreorderAutocomplete();

        return $preloader->preload(
            \KupShop\PreordersBundle\Autocomplete\AutocompleteType::PreordersDates,
            $ids,
        );
    }

    private function loadReturns_deliveries(array $ids): array
    {
        if (!findModule(\Modules::RETURNS)) {
            return [];
        }

        $preloader = new \KupShop\ReturnsBundle\Util\ReturnsAutocomplete();

        return $preloader->preloadReturnDeliveries($ids);
    }

    private function loadUnits(array $ids): array
    {
        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_UNITS)) {
            return [];
        }

        return sqlQueryBuilder()->select('id, short_name_admin as name')
            ->from('products_units')
            ->andWhere(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadUserAddresses(array $ids): array
    {
        if (!findModule(\Modules::USER_ADDRESSES)) {
            return [];
        }

        return sqlQueryBuilder()->select('id, CONCAT_WS(" ", delivery_name, delivery_surname, delivery_street, delivery_city) as name')
            ->from('users_addresses')
            ->andWhere(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }

    private function loadSalesman(array $ids): array
    {
        if (!findModule(\Modules::SALESMAN)) {
            return [];
        }

        return sqlQueryBuilder()
            ->select('id, name')
            ->from('salesman')
            ->andWhere(Operator::inIntArray($ids, 'id'))
            ->execute()->fetchAllAssociative();
    }
}