Symfony Profiler

<?php
namespace App\Services;
use App\Entity\Company;
use App\Entity\Product;
use App\Entity\User;
use App\Exception\FulltextSearchNullResults;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Knp\Component\Pager\PaginatorInterface;
use PhpParser\Builder;
use Symfony\Component\Form\FormInterface;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Session\Session;
class OrderFilter
{
const DATE_LOWER = 1;
const DATE_HIGHER = 2;
/**
* @var ManagerRegistry
*/
protected $em;
public function __construct(
ManagerRegistry $registry
)
{
$this->em = $registry;
}
public function storePaginationSettings(Session $session, Request $request)
{
$getOrder = $request->get('ordering', null);
if ($getOrder != null && ($getOrder == 'asc' || $getOrder == 'desc')) {
$session->set('orderOrdering', $request->get('ordering'));
}
if ($request->get('pageSize', null) != null) {
$session->set('pageSize', (int)$request->get('pageSize'));
}
return $this->loadPaginationSettings($session);
}
/**
* @param Session $session
* @return array
*/
public function loadPaginationSettings(Session $session)
{
return [
'ordering' => $session->get('orderOrdering', 'desc'),
'pageSize' => $session->get('pageSize', 50)
];
}
public function loadPrefilledData(Session $session, EntityManagerInterface $entityManager)
{
// predvyplneni formulare filtrace
$prefDataCompany = $entityManager->getRepository(Company::class)->findBy([
'id' => $session->get('filter-order-company', null)
]);
$prefDataMerchant = $entityManager->getRepository(User::class)->findBy([
'id' => $session->get('filter-order-merchant', null)
]);
$prefDataProduct = $entityManager->getRepository(Product::class)->findBy([
'id' => $session->get('filter-order-product', null)
]);
return [
"company" => (array)$prefDataCompany,
"merchant" => (array)$prefDataMerchant,
"product" => (array)$prefDataProduct
];
}
/**
* Ulozi formular se do session
*
* @param FormInterface $formFilter
* @param Request $request
* @param Session $session
* @return void
*/
public function storeFilter(FormInterface $formFilter, Request $request, Session $session)
{
$formFilter->handleRequest($request);
if ($formFilter->isSubmitted() && $formFilter->isValid()) {
$session->set('fulltext', $formFilter->get('fulltext')->getData());
try {
$companyData = $formFilter->get('company')->getData();
if (isset($companyData)) {
$cIds = array_map(function ($item) {
return $item->getId();
}, (array)$companyData);
$session->set('filter-order-company', $cIds);
}
}catch (\OutOfBoundsException $exception){
// company neexistuje - nejsi admin
}
try{
$merchantData = $formFilter->get('merchant')->getData();
if (isset($merchantData)) {
$mIds = array_map(function ($item) {
return $item->getId();
}, (array)$merchantData);
$session->set('filter-order-merchant', $mIds);
}
}catch (\OutOfBoundsException $e){
// merchant neexistuje
}
try {
$productData = $formFilter->get('product')->getData();
if (isset($productData)) {
$pIds = array_map(function ($item) {
return $item->getId();
}, (array)$productData);
$session->set('filter-order-product', $pIds);
}
}catch (\OutOfBoundsException $exception){
// kdyby nekdy v budoucnu :D
}
$this->storeDateComponent($formFilter, $session, 'inProgress');
$this->storeDateComponent($formFilter, $session, 'sent');
$this->storeDateComponent($formFilter, $session, 'paid');
$this->storeDateComponent($formFilter, $session, 'paid2');
$this->storeDateComponent($formFilter, $session, 'signed');
$this->storeDateComponent($formFilter, $session, 'vpDelivery');
// reinvest filtr
$session->remove('filter-order-reinvest');
$dateField = $formFilter->get('reinvest')->getData();
if (isset($dateField)) {
$session->set('filter-order-reinvest', (bool)$dateField);
}
}
}
/**
* Uklada selectbox i datumove polozky
*
* @param FormInterface $formFilter
* @param Session $session
* @param $dateFieldName
* @return void
*/
protected function storeDateComponent(FormInterface $formFilter, Session $session, $dateFieldName)
{
if($formFilter->offsetExists($dateFieldName) == false){
return false;
}
$session->remove('filter-order-' . $dateFieldName);
$session->remove('filter-order-' . $dateFieldName . '_from');
$session->remove('filter-order-' . $dateFieldName . '_to');
$dateField = $formFilter->get($dateFieldName)->getData();
if (isset($dateField)) {
$session->set('filter-order-' . $dateFieldName, $dateField);
}
$dateFieldFrom = $formFilter->get($dateFieldName . '_from')->getData();
if (isset($dateFieldFrom)) {
$session->set('filter-order-' . $dateFieldName . '_from', $dateFieldFrom);
}
$dateFieldTo = $formFilter->get($dateFieldName . '_to')->getData();
if (isset($dateFieldTo)) {
$session->set('filter-order-' . $dateFieldName . '_to', $dateFieldTo);
}
}
/**
* Aplikuje filtr na query builder + strankovani a vrati list objednavek
*
* @param Session $session
* @param QueryBuilder $queryBuilder
*/
public function applyFilter(Session $session, Request $request, QueryBuilder $queryBuilder, ?PaginatorInterface $paginator = null)
{
// fulltext
$fv = $session->get('fulltext', '');
$fvepl = explode(' ', $fv);
$fvimpl = implode('|', $fvepl);
if($fv != ''){
$sql = 'SELECT o.id FROM `order` o
LEFT JOIN company c ON (o.company_id = c.id)
LEFT JOIN product p ON (o.product_id = p.id)
LEFT JOIN user m ON (o.merchant_id = m.id)
WHERE
o.id = :id
OR REGEXP_LIKE(o.name, :fvimpl)
OR REGEXP_LIKE(o.surname, :fvimpl)
OR REGEXP_LIKE(o.merchant_code, :fvimpl)
OR REGEXP_LIKE(o.street, :fvimpl)
OR REGEXP_LIKE(o.town, :fvimpl)
OR REGEXP_LIKE(o.postal, :fvimpl)
OR REGEXP_LIKE(o.street2, :fvimpl)
OR REGEXP_LIKE(o.town2, :fvimpl)
OR REGEXP_LIKE(o.postal2, :fvimpl)
OR REGEXP_LIKE(o.ic, :fvimpl)
OR REGEXP_LIKE(o.phone, :fvimpl)
OR REGEXP_LIKE(o.email, :fvimpl)
OR REGEXP_LIKE(o.bank_account, :fvimpl)
OR REGEXP_LIKE(o.swift, :fvimpl)
OR REGEXP_LIKE(o.iban, :fvimpl)
OR REGEXP_LIKE(o.message, :fvimpl)
OR REGEXP_LIKE(p.name, :fvimpl)
OR REGEXP_LIKE(m.name, :fvimpl)
OR REGEXP_LIKE(m.surname, :fvimpl)
OR REGEXP_LIKE(m.code, :fvimpl)
';
$stmt = $queryBuilder->getEntityManager()->getConnection()->prepare($sql);
$result = $stmt->executeQuery(['fvimpl' => $fvimpl, 'id' => (int)$fv]);
$resultFetched = $result->fetchAllAssociative();
$ids = array_map(function($item){
return $item['id'];
}, $resultFetched);
if(count($ids) == 0) {
throw new FulltextSearchNullResults('');
}
$queryBuilder->andWhere($queryBuilder->expr()->in('o.id', $ids));
}
// endfulltext
$paginationSettings = $this->loadPaginationSettings($session);
$filterOrderCompany = $session->get('filter-order-company', null);
if (is_array($filterOrderCompany) && count($filterOrderCompany) > 0) {
$andWhere = $queryBuilder->expr()->in('o.company', $filterOrderCompany);
$queryBuilder->andWhere($andWhere);
}
$filterOrderMerchant = $session->get('filter-order-merchant', null);
if (is_array($filterOrderMerchant) && count($filterOrderMerchant) > 0) {
$andWhere = $queryBuilder->expr()->in('o.merchant', $filterOrderMerchant);
$queryBuilder->andWhere($andWhere);
}
$filterOrderProduct = $session->get('filter-order-product', null);
if (is_array($filterOrderProduct) && count($filterOrderProduct) > 0) {
$andWhere = $queryBuilder->expr()->in('o.product', $filterOrderProduct);
$queryBuilder->andWhere($andWhere);
}
$this->applyFilterBool($queryBuilder, $session, 'filter-order-sent', 'sent');
$this->applyFilterBool($queryBuilder, $session, 'filter-order-inProgress', 'inProgress');
$this->applyFilterBool($queryBuilder, $session, 'filter-order-paid', 'paid');
$this->applyFilterBool($queryBuilder, $session, 'filter-order-paid2', 'paid2');
$this->applyFilterBool($queryBuilder, $session, 'filter-order-signed', 'signed');
$this->applyFilterBool($queryBuilder, $session, 'filter-order-vpDelivery', 'vpDelivery');
$this->applyFilterBool($queryBuilder, $session, 'filter-order-reinvest', 'reinvest');
$this->applyFilterDate($queryBuilder, $session, 'filter-order-inProgress_from', 'inProgressDate', self::DATE_HIGHER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-inProgress_to', 'inProgressDate', self::DATE_LOWER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-signed_from', 'signed_date', self::DATE_HIGHER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-signed_to', 'signed_date', self::DATE_LOWER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-vpDelivery_from', 'vpDelivery_date', self::DATE_HIGHER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-vpDelivery_to', 'vpDelivery_date', self::DATE_LOWER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-paid_from', 'paid_date', self::DATE_HIGHER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-paid_to', 'paid_date', self::DATE_LOWER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-paid2_from', 'paidDate2', self::DATE_HIGHER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-paid2_to', 'paidDate2', self::DATE_LOWER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-sent_from', 'sent_date', self::DATE_HIGHER);
$this->applyFilterDate($queryBuilder, $session, 'filter-order-sent_to', 'sent_date', self::DATE_LOWER);
$queryBuilder->orderBy('o.id', $paginationSettings['ordering']);
if($paginator != false) {
return $paginator->paginate(
$queryBuilder, /* query NOT result */
(false == is_null($paginator) ? $request->query->getInt('page', 1) : null) /*page number*/,
(false == is_null($paginator) ? $paginationSettings['pageSize'] : null) /*limit per page*/
);
}
return $queryBuilder;
}
protected function applyFilterDate(QueryBuilder $builder, Session $session, $fieldName, $tableCol, $cond)
{
/** @var \DateTime $filter */
$filter = $session->get($fieldName, "");
if ($filter != "") {
$filter = $filter->format('Y-m-d');
$lit = $builder->expr()->literal($filter);
if ($cond == self::DATE_LOWER) {
$andWhere = $builder->expr()->lte('o.' . $tableCol, $lit);
$builder->andWhere($andWhere);
} else {
$andWhere = $builder->expr()->gte('o.' . $tableCol, $lit);
$builder->andWhere($andWhere);
}
}
}
protected function applyFilterBool(QueryBuilder $builder, Session $session, $fieldName, $tableCol)
{
$filter = $session->get($fieldName, "");
if ($filter !== "") {
if((int)$filter == 1) {
$andWhere = $builder->expr()->eq('o.' . $tableCol, (int)$filter);
}else{
$or1 = $builder->expr()->eq('o.' . $tableCol, $builder->expr()->literal('false'));
$or2 = $builder->expr()->isNull('o.' . $tableCol);
$andWhere = $builder->expr()->orX()->add($or1)->add($or2);
}
$builder->andWhere($andWhere);
}
}
public function clearFilter(Session $session, Request $request)
{
try {
if ($request->get('clear', null) == "1") {
$session->remove('filter-order-company');
$session->remove('filter-order-merchant');
$session->remove('filter-order-product');
$session->remove('filter-order-sent');
$session->remove('filter-order-sent_from');
$session->remove('filter-order-sent_to');
$session->remove('filter-order-inProgress');
$session->remove('filter-order-inProgress_from');
$session->remove('filter-order-inProgress_to');
$session->remove('filter-order-paid');
$session->remove('filter-order-paid_from');
$session->remove('filter-order-paid_to');
$session->remove('filter-order-paid2');
$session->remove('filter-order-paid2_from');
$session->remove('filter-order-paid2_to');
$session->remove('filter-order-signed');
$session->remove('filter-order-signed_from');
$session->remove('filter-order-signed_to');
$session->remove('filter-order-vpDelivery');
$session->remove('filter-order-vpDelivery_from');
$session->remove('filter-order-vpDelivery_to');
$session->remove('filter-order-reinvest');
$session->remove('fulltext');
return true;
}
} catch (\Exception $e) {
return false;
}
}
}

src/Services/OrderFilter.php line 244