Symfony Profiler

<?php
namespace App\Controller\Business;
use App\Entity\Brand;
use App\Entity\PackageRecipe;
use App\Entity\Shop;
use App\Entity\Shoppingmall;
use App\Entity\User;
use App\Form\HistoryStoresType;
use App\Form\StoresFilterType;
use App\Repository\ShopRepository;
use DateTime;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use function array_slice;
use Exception;
use function fgets;
use function fopen;
use Symfony\Component\Form\FormError;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Contracts\Cache\CacheInterface;
use Symfony\Contracts\Cache\ItemInterface;
class IndexController extends AbstractController
{
protected $i = 0;
protected $counter = 0;
/**
* Domovska stranka Business
*
*
* @Route("/", name="index")
*/
public function index(Request $request)
{
return $this->render('business/index/index.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* Dashboard (homepage for logged-in users). Renders only widget skeletons;
* each widget fetches its own data via AJAX so the page is interactive
* before any data arrives.
*
* @Route("/loginsuccess", name="index_loginsuccess")
*/
public function loginSuccess(Request $request)
{
return $this->render('admin/index/dashboard.html.twig');
}
/**
* @Route("/dashboard/packages", name="dashboard_packages", methods={"GET"})
*/
public function dashboardPackages(CacheInterface $cache)
{
$user = $this->getUser();
$isAdmin = $this->isGranted('ROLE_ADMIN');
// Recipe IDs the user is actively subscribed to.
$activeRecipeIds = [];
if ($user instanceof User) {
foreach ($user->getActivePackages() as $package) {
$recipe = $package->getPackageRecipe();
if ($recipe !== null) {
$activeRecipeIds[$recipe->getId()] = true;
}
}
}
// Per-country malls/stores/brands counts. User-independent and only
// changes on import, so cache the whole map.
$countsByCountry = $cache->get('dashboard_pkg_counts', function (ItemInterface $item) {
$item->expiresAfter(3600);
return $this->computeCountsByCountry();
});
// List every package (recipe); flag the user's active ones. Admins have
// full access, so every package counts as active for them.
$recipes = $this->getDoctrine()->getRepository(PackageRecipe::class)->findBy([], ['name' => 'ASC']);
$iso = self::countryLabelToIso();
$packages = [];
foreach ($recipes as $recipe) {
$countries = array_values(array_filter(array_map('trim', $recipe->getCountries())));
$countriesData = array_map(function ($country) use ($countsByCountry, $iso) {
$c = $countsByCountry[$country] ?? ['malls' => 0, 'stores' => 0, 'brands' => 0];
return [
'name' => $country,
'code' => $iso[$country] ?? null,
'malls' => $c['malls'],
'stores' => $c['stores'],
'brands' => $c['brands'],
];
}, $countries);
$lastUpdate = $recipe->getLastUpdateDate();
$activeFrom = $recipe->getActiveFrom();
$packages[] = [
'name' => $recipe->getName(),
'countries' => $countriesData,
'active' => $isAdmin || isset($activeRecipeIds[$recipe->getId()]),
'activeFrom' => $activeFrom ? $activeFrom->format('m/Y') : null,
'lastUpdateDate' => $lastUpdate ? $lastUpdate->format('m/Y') : null,
];
}
// Active packages first (alphabetical within each group — recipes were
// loaded name-sorted and usort is stable on PHP 8+).
usort($packages, fn($a, $b) => ($b['active'] <=> $a['active']));
return new JsonResponse([
'packages' => $packages,
'isAdmin' => $isAdmin,
]);
}
/**
* Per-country active malls / stores / brands counts, keyed by country label.
* Three separate GROUP BY aggregations on purpose: a single triple LEFT JOIN
* over cities × shops × shop_has_brand explodes into a cartesian product
* (~20s). Split, each grouping stays cheap (~2s total, then cached).
*
* All three lean on idx_shops_active_deleted_city (see
* Version20260908120000) so the shops side is covering and the rows arrive
* already grouped by country.
*
* @return array<string, array{malls:int, stores:int, brands:int}>
*/
private function computeCountsByCountry(): array
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$counts = [];
$touch = function (string $country) use (&$counts) {
if (!isset($counts[$country])) {
$counts[$country] = ['malls' => 0, 'stores' => 0, 'brands' => 0];
}
};
$malls = $conn->executeQuery("
SELECT c.country AS country, COUNT(DISTINCT m.shoppingmall_id) AS cnt
FROM shoppingmalls m
INNER JOIN cities c ON m.city = c.city_id
WHERE m.is_active = 1 AND c.country IS NOT NULL AND c.country <> ''
GROUP BY c.country
")->fetchAllAssociative();
foreach ($malls as $r) { $touch($r['country']); $counts[$r['country']]['malls'] = (int) $r['cnt']; }
$stores = $conn->executeQuery("
SELECT c.country AS country, COUNT(*) AS cnt
FROM shops s
INNER JOIN cities c ON s.city = c.city_id
WHERE s.is_active = 1 AND s.is_deleted = 0 AND c.country IS NOT NULL AND c.country <> ''
GROUP BY c.country
")->fetchAllAssociative();
foreach ($stores as $r) { $touch($r['country']); $counts[$r['country']]['stores'] = (int) $r['cnt']; }
$brands = $conn->executeQuery("
SELECT t.country AS country, COUNT(*) AS cnt
FROM (
SELECT DISTINCT c.country AS country, sb.brand_id AS brand_id
FROM shop_has_brand sb
INNER JOIN shops s ON sb.shop_id = s.shop_id
INNER JOIN cities c ON s.city = c.city_id
WHERE s.is_active = 1 AND s.is_deleted = 0 AND c.country IS NOT NULL AND c.country <> ''
) t
GROUP BY t.country
")->fetchAllAssociative();
foreach ($brands as $r) { $touch($r['country']); $counts[$r['country']]['brands'] = (int) $r['cnt']; }
return $counts;
}
/**
* Latest published news for the dashboard teaser (max 3).
*
* @Route("/dashboard/news", name="dashboard_news", methods={"GET"})
*/
public function dashboardNews(\App\Repository\NewsRepository $newsRepository)
{
$items = [];
foreach ($newsRepository->findPublished(3) as $news) {
$items[] = [
'id' => $news->getId(),
'title' => $news->getTitle(),
'shortDesc' => $news->getShortDescription(),
'createdAt' => $news->getCreatedAt() ? $news->getCreatedAt()->format('d.m.Y') : null,
];
}
return new JsonResponse([
'news' => $items,
'allUrl' => $this->generateUrl('news_index'),
]);
}
/**
* @Route("/dashboard/counts", name="dashboard_counts", methods={"GET"})
*/
public function dashboardCounts()
{
$doctrine = $this->getDoctrine();
$countMalls = $doctrine->getRepository(Shoppingmall::class)->getCountActive()[1];
$countStores = $doctrine->getRepository(Shop::class)->getCountActive()[1];
$countBrands = $doctrine->getRepository(Brand::class)->getCountActive()[1];
return new JsonResponse([
'malls' => (int) $countMalls,
'stores' => (int) $countStores,
'brands' => (int) $countBrands,
]);
}
/**
* Newcomers AJAX feed for the dashboard map widget.
*
* A "newcomer" = first appearance of a (country, shop_name) pair.
* shops.created_at = '0000-00-00 00:00:00' is excluded as unreliable.
*
* @Route("/dashboard/newcomers", name="dashboard_newcomers", methods={"GET"})
*/
public function newcomers(Request $request, CacheInterface $cache)
{
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
// Default year/month is the latest is_new_date in DB; the widget uses
// these on first load (without explicit year/month query params) and
// also picks up the available years for its selector. Cached — it only
// moves when a new import lands.
$defaults = $cache->get('dashboard_newcomers_defaults', function (ItemInterface $item) use ($conn) {
$item->expiresAfter(3600);
$raw = $conn->executeQuery(
"SELECT MAX(is_new_date) FROM shops WHERE is_new_date IS NOT NULL"
)->fetchOne();
$latest = $raw ? new DateTime($raw) : new DateTime();
return ['year' => (int) $latest->format('Y'), 'month' => (int) $latest->format('n')];
});
$defaultYear = $defaults['year'];
$defaultMonth = $defaults['month'];
$year = (int) $request->query->get('year', $defaultYear);
$month = (int) $request->query->get('month', $defaultMonth);
// Per-user accessibility — cheap, computed fresh (never cached, since it
// depends on the logged-in user's packages).
$isAdmin = $this->isGranted('ROLE_ADMIN');
$allowedCountries = [];
$user = $this->getUser();
if ($user instanceof User) {
foreach ($user->getActivePackages() as $package) {
$recipe = $package->getPackageRecipe();
if ($recipe === null) continue;
foreach ($recipe->getCountries() as $country) {
$country = trim($country);
if ($country !== '') {
$allowedCountries[$country] = true;
}
}
}
}
// Heavy, user-independent aggregation. A single window-function pass over
// all shops yields, for the chosen month, every shop whose (country, name)
// makes its first-ever appearance that month. Country counts, the
// newcomer name list and the per-country shop IDs are all derived from
// this one result set in PHP — the previous version ran the same
// "first appearance" scan three times (~6s). Cached per (year, month);
// the underlying data only changes when an import adds is_new_date.
$raw = $cache->get("dashboard_newcomers_{$year}_{$month}", function (ItemInterface $item) use ($conn, $year, $month) {
$item->expiresAfter(3600);
$sql = "
SELECT shop_id, country, shop_name, first_seen FROM (
SELECT s.shop_id, c.country AS country, s.name AS shop_name,
s.is_new_date AS first_seen,
MIN(s.is_new_date) OVER (PARTITION BY c.country, s.name) AS min_d
FROM shops s
INNER JOIN cities c ON s.city = c.city_id
WHERE s.is_new_date IS NOT NULL
AND s.name IS NOT NULL AND s.name <> ''
AND c.country IS NOT NULL AND c.country <> ''
) t
WHERE first_seen = min_d
AND YEAR(first_seen) = :year AND MONTH(first_seen) = :month
ORDER BY country, shop_name
";
$stmt = $conn->prepare($sql);
$stmt->bindValue('year', $year, \PDO::PARAM_INT);
$stmt->bindValue('month', $month, \PDO::PARAM_INT);
$rows = $stmt->executeQuery()->fetchAllAssociative();
$iso = self::countryLabelToIso();
$idsByCountry = []; // country => [shop_id, ...] — all ties; drives the Stores pre-filter
$countByCountry = []; // country => number of distinct (country, name) newcomers
$items = []; // distinct (country, name), already ordered by the SQL
$seenName = [];
foreach ($rows as $row) {
$country = $row['country'];
$idsByCountry[$country][] = (int) $row['shop_id'];
$key = $country . '|' . $row['shop_name'];
if (!isset($seenName[$key])) {
$seenName[$key] = true;
$countByCountry[$country] = ($countByCountry[$country] ?? 0) + 1;
$items[] = [
'name' => $row['shop_name'],
'country' => $country,
'country_code' => $iso[$country] ?? null,
'first_seen' => $row['first_seen'],
];
}
}
$countries = [];
foreach ($countByCountry as $country => $count) {
$countries[] = [
'country' => $country,
'code' => $iso[$country] ?? null,
'count' => $count,
'shop_ids' => $idsByCountry[$country] ?? [],
];
}
usort($countries, fn($a, $b) => $b['count'] <=> $a['count']);
return [
'countries' => $countries,
'items' => array_slice($items, 0, 200),
];
});
// Layer per-user accessibility onto the cached, user-independent aggregate.
$countries = [];
$totalAccessible = 0;
foreach ($raw['countries'] as $row) {
$accessible = $isAdmin || isset($allowedCountries[$row['country']]);
if ($accessible) $totalAccessible += $row['count'];
$countries[] = $row + ['accessible' => $accessible];
}
$items = [];
foreach ($raw['items'] as $row) {
$accessible = $isAdmin || isset($allowedCountries[$row['country']]);
$items[] = $row + ['accessible' => $accessible];
}
return new JsonResponse([
'year' => $year,
'month' => $month,
'countries' => $countries,
'items' => $items,
'total' => $totalAccessible,
'defaultYear' => $defaultYear,
'defaultMonth' => $defaultMonth,
'availableYears' => range($defaultYear, 2020),
]);
}
/**
* Country label (as stored in cities.country) to ISO 3166-1 alpha-2.
* Used by jvectormap world_mill region keys.
*
* @return array<string,string>
*/
public static function countryLabelToIso(): array
{
return [
'Austria' => 'AT',
'Belgium' => 'BE',
'Bulgaria' => 'BG',
'Canada' => 'CA',
'Croatia' => 'HR',
'Cyprus' => 'CY',
'Czech Republic' => 'CZ',
'Denmark' => 'DK',
'Estonia' => 'EE',
'Finland' => 'FI',
'France' => 'FR',
'Germany' => 'DE',
'Greece' => 'GR',
'Hungary' => 'HU',
'Ireland' => 'IE',
'Italy' => 'IT',
'Latvia' => 'LV',
'Lithuania' => 'LT',
'Mexico' => 'MX',
'Montenegro' => 'ME',
'Netherlands' => 'NL',
'Norway' => 'NO',
'Poland' => 'PL',
'Portugal' => 'PT',
'Romania' => 'RO',
'Russia' => 'RU',
'San Marino' => 'SM',
'Serbia' => 'RS',
'Slovakia' => 'SK',
'Slovenia' => 'SI',
'Spain' => 'ES',
'Sweden' => 'SE',
'Switzerland' => 'CH',
'UAE' => 'AE',
'Ukraine' => 'UA',
'United Kingdom' => 'GB',
'USA' => 'US',
];
}
/**
* FAQ Page
*
* @Route("/faq", name="faq")
*/
public function faq(Request $request)
{
return $this->render('admin/index/faq.html.twig', [
'controller_name' => 'IndexController',
]);
}
/**
* Domovska stranka Business - rozcestnik
*
*
* @Route("/welcome", name="admin_index_welcome")
*/
public function welcome(Request $request)
{
return $this->render('admin/index/index.html.twig', [
'controller_name' => 'IndexController',
]);
}
protected function render(string $view, array $parameters = [], Response $response = null): Response
{
$docrine = $this->getDoctrine();
$countBrands = $docrine->getRepository(Brand::class)->getCountActive();
$countStores = $docrine->getRepository(Shop::class)->getCountActive();
$countMalls = $docrine->getRepository(Shoppingmall::class)->getCountActive();
$parameters['countBrands'] = $countBrands[1];
$parameters['countStores'] = $countStores[1];
$parameters['countMalls'] = $countMalls[1];
return parent::render($view, $parameters, $response);
}
}

src/Controller/Business/IndexController.php line 437