PartsDataTable::getQuery() - Code Metrics - Inspection of "Search options now working. Added Regex search." - Part-DB/Part-DB-symfony - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 120eb3...86ee10 )

by Jan

created 2020-03-08 12:06 UTC

PartsDataTable::getQuery() A

↳ Parent: PartsDataTable

Complexity

Conditions	1
Paths	1

Size

Total Lines	25
Code Lines	23

Duplication

Lines	0
Ratio	0 %

Importance

Changes	6
Bugs	0	Features	0

Metric	Value
cc	1
eloc	23
c	6
b	0
f	0
nc	1
nop	1
dl	0
loc	25
rs	9.552

<?php
/**
 * This file is part of Part-DB (https://github.com/Part-DB/Part-DB-symfony).
 *
 * Copyright (C) 2019 - 2020 Jan Böhmer (https://github.com/jbtronics)
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published
 * by the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 */

declare(strict_types=1);

/**
 * This file is part of Part-DB (https://github.com/Part-DB/Part-DB-symfony).
 *
 * Copyright (C) 2019 Jan Böhmer (https://github.com/jbtronics)
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 */

namespace App\DataTables;

use App\DataTables\Adapter\FetchJoinORMAdapter;
use App\DataTables\Column\EntityColumn;
use App\DataTables\Column\LocaleDateTimeColumn;
use App\DataTables\Column\MarkdownColumn;
use App\DataTables\Column\PartAttachmentsColumn;
use App\DataTables\Column\TagsColumn;
use App\Entity\Parts\Category;
use App\Entity\Parts\Footprint;
use App\Entity\Parts\Manufacturer;
use App\Entity\Parts\Part;
use App\Entity\Parts\Storelocation;
use App\Entity\Parts\Supplier;
use App\Services\AmountFormatter;
use App\Services\Attachments\AttachmentURLGenerator;
use App\Services\Attachments\PartPreviewGenerator;
use App\Services\EntityURLGenerator;
use App\Services\Trees\NodesListBuilder;
use Doctrine\ORM\QueryBuilder;
use Omines\DataTablesBundle\Adapter\Doctrine\ORM\SearchCriteriaProvider;
use Omines\DataTablesBundle\Column\BoolColumn;
use Omines\DataTablesBundle\Column\MapColumn;
use Omines\DataTablesBundle\Column\TextColumn;
use Omines\DataTablesBundle\DataTable;
use Omines\DataTablesBundle\DataTableTypeInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;
use Symfony\Contracts\Translation\TranslatorInterface;

final class PartsDataTable implements DataTableTypeInterface
{
    private $translator;
    private $treeBuilder;
    private $amountFormatter;
    private $previewGenerator;
    private $attachmentURLGenerator;
    /**
     * @var EntityURLGenerator
     */
    private $urlGenerator;

    public function __construct(EntityURLGenerator $urlGenerator, TranslatorInterface $translator,
        NodesListBuilder $treeBuilder, AmountFormatter $amountFormatter,
        PartPreviewGenerator $previewGenerator, AttachmentURLGenerator $attachmentURLGenerator)
    {
        $this->urlGenerator = $urlGenerator;
        $this->translator = $translator;
        $this->treeBuilder = $treeBuilder;
        $this->amountFormatter = $amountFormatter;
        $this->previewGenerator = $previewGenerator;
        $this->attachmentURLGenerator = $attachmentURLGenerator;
    }

    public function configureOptions(OptionsResolver $optionsResolver)
    {
        $optionsResolver->setDefaults([
                                          'category' => null,
                                          'footprint' => null,
                                          'manufacturer' => null,
                                          'storelocation' => null,
                                          'supplier' => null,
                                          'tag' => null,
                                          'search' => null,
                                      ]);

        $optionsResolver->setAllowedTypes('category', ['null', Category::class]);
        $optionsResolver->setAllowedTypes('footprint', ['null', Footprint::class]);
        $optionsResolver->setAllowedTypes('manufacturer', ['null', Manufacturer::class]);
        $optionsResolver->setAllowedTypes('supplier', ['null', Supplier::class]);
        $optionsResolver->setAllowedTypes('tag', ['null', 'string']);
        $optionsResolver->setAllowedTypes('search', ['null', 'string']);

        //Configure search options
        $optionsResolver->setDefault('search_options', function (OptionsResolver $resolver) {
            $resolver->setDefaults([
                                       'name' => true,
                                       'category' => true,
                                       'description' => true,
                                       'store_location' => true,
                                       'comment' => true,
                                       'ordernr' => true,
                                       'supplier' => false,
                                       'manufacturer' => false,
                                       'footprint' => false,
                                       'tags' => false,
                                       'regex' => false,
                                   ]);
            $resolver->setAllowedTypes('name', 'bool');
            $resolver->setAllowedTypes('category', 'bool');
            $resolver->setAllowedTypes('description', 'bool');
            $resolver->setAllowedTypes('store_location', 'bool');
            $resolver->setAllowedTypes('comment', 'bool');
            $resolver->setAllowedTypes('supplier', 'bool');
            $resolver->setAllowedTypes('manufacturer', 'bool');
            $resolver->setAllowedTypes('footprint', 'bool');
            $resolver->setAllowedTypes('tags', 'bool');
            $resolver->setAllowedTypes('regex', 'bool');
        });
    }

    public function configure(DataTable $dataTable, array $options): void
    {
        $resolver = new OptionsResolver();
        $this->configureOptions($resolver);
        $options = $resolver->resolve($options);

        $dataTable
            ->add('picture', TextColumn::class, [
                'label' => '',
                'render' => function ($value, Part $context) {
                    $preview_attachment = $this->previewGenerator->getTablePreviewAttachment($context);
                    if (null === $preview_attachment) {
                        return '';
                    }

                    return sprintf(
                        '<img alt="%s" src="%s" data-thumbnail="%s" class="%s">',
                        'Part image',
                        $this->attachmentURLGenerator->getThumbnailURL($preview_attachment),
                        $this->attachmentURLGenerator->getThumbnailURL($preview_attachment, 'thumbnail_md'),
                        'img-fluid hoverpic'
                    );
                },
            ])
            ->add('name', TextColumn::class, [
                'label' => $this->translator->trans('part.table.name'),
                'render' => function ($value, Part $context) {
                    return sprintf(
                        '<a href="%s">%s</a>',
                        $this->urlGenerator->infoURL($context),
                        $context->getName()
                    );
                },
            ])
            ->add('id', TextColumn::class, [
                'label' => $this->translator->trans('part.table.id'),
                'visible' => false,
            ])
            ->add('description', MarkdownColumn::class, [
                'label' => $this->translator->trans('part.table.description'),
            ])
            ->add('category', EntityColumn::class, [
                'label' => $this->translator->trans('part.table.category'),
                'property' => 'category',
            ])
            ->add('footprint', EntityColumn::class, [
                'property' => 'footprint',
                'label' => $this->translator->trans('part.table.footprint'),
            ])
            ->add('manufacturer', EntityColumn::class, [
                'property' => 'manufacturer',
                'label' => $this->translator->trans('part.table.manufacturer'),
            ])
            ->add('storelocation', TextColumn::class, [
                'label' => $this->translator->trans('part.table.storeLocations'),
                'render' => function ($value, Part $context) {
                    $tmp = [];
                    foreach ($context->getPartLots() as $lot) {
                        //Ignore lots without storelocation
                        if (null === $lot->getStorageLocation()) {
                            continue;
                        }
                        $tmp[] = sprintf(
                            '<a href="%s">%s</a>',
                            $this->urlGenerator->listPartsURL($lot->getStorageLocation()),
                            $lot->getStorageLocation()->getName()
                        );
                    }

                    return implode('<br>', $tmp);
                },
            ])
            ->add('amount', TextColumn::class, [
                'label' => $this->translator->trans('part.table.amount'),
                'render' => function ($value, Part $context) {
                    $amount = $context->getAmountSum();

                    return $this->amountFormatter->format($amount, $context->getPartUnit());
                },
            ])
            ->add('minamount', TextColumn::class, [
                'label' => $this->translator->trans('part.table.minamount'),
                'visible' => false,
                'render' => function ($value, Part $context) {
                    return $this->amountFormatter->format($value, $context->getPartUnit());
                },
            ])
            ->add('partUnit', TextColumn::class, [
                'field' => 'partUnit.name',
                'label' => $this->translator->trans('part.table.partUnit'),
                'visible' => false,
            ])
            ->add('addedDate', LocaleDateTimeColumn::class, [
                'label' => $this->translator->trans('part.table.addedDate'),
                'visible' => false,
            ])
            ->add('lastModified', LocaleDateTimeColumn::class, [
                'label' => $this->translator->trans('part.table.lastModified'),
                'visible' => false,
            ])
            ->add('needs_review', BoolColumn::class, [
                'label' => $this->translator->trans('part.table.needsReview'),
                'trueValue' => $this->translator->trans('true'),
                'falseValue' => $this->translator->trans('false'),
                'nullValue' => '',
                'visible' => false,
            ])
            ->add('favorite', BoolColumn::class, [
                'label' => $this->translator->trans('part.table.favorite'),
                'trueValue' => $this->translator->trans('true'),
                'falseValue' => $this->translator->trans('false'),
                'nullValue' => '',
                'visible' => false,
            ])
            ->add('manufacturing_status', MapColumn::class, [
                'label' => $this->translator->trans('part.table.manufacturingStatus'),
                'visible' => false,
                'default' => $this->translator->trans('m_status.unknown'),
                'map' => [
                    '' => $this->translator->trans('m_status.unknown'),
                    'announced' => $this->translator->trans('m_status.announced'),
                    'active' => $this->translator->trans('m_status.active'),
                    'nrfnd' => $this->translator->trans('m_status.nrfnd'),
                    'eol' => $this->translator->trans('m_status.eol'),
                    'discontinued' => $this->translator->trans('m_status.discontinued'),
                ],
            ])
            ->add('manufacturer_product_number', TextColumn::class, [
                'label' => $this->translator->trans('part.table.mpn'),
                'visible' => false,
            ])
            ->add('mass', TextColumn::class, [
                'label' => $this->translator->trans('part.table.mass'),
                'visible' => false,
            ])
            ->add('tags', TagsColumn::class, [
                'label' => $this->translator->trans('part.table.tags'),
                'visible' => false,
            ])
            ->add('attachments', PartAttachmentsColumn::class, [
                'label' => $this->translator->trans('part.table.attachments'),
                'visible' => false,
            ])

            ->addOrderBy('name')
            ->createAdapter(FetchJoinORMAdapter::class, [
                'simple_total_query' => true,
                'query' => function (QueryBuilder $builder): void {
                    $this->getQuery($builder);
                },
                'entity' => Part::class,
                'criteria' => [
                    function (QueryBuilder $builder) use ($options): void {
                        $this->buildCriteria($builder, $options);
                    },
                    new SearchCriteriaProvider(),
                ],
            ]);
    }

    private function getQuery(QueryBuilder $builder): void
    {
        $builder->distinct()->select('part')
            ->addSelect('category')
            ->addSelect('footprint')
            ->addSelect('manufacturer')
            ->addSelect('partUnit')
            ->addSelect('master_picture_attachment')
            ->addSelect('footprint_attachment')
            ->addSelect('partLots')
            ->addSelect('orderdetails')
            ->addSelect('attachments')
            ->addSelect('storelocations')
            ->from(Part::class, 'part')
            ->leftJoin('part.category', 'category')
            ->leftJoin('part.master_picture_attachment', 'master_picture_attachment')
            ->leftJoin('part.partLots', 'partLots')
            ->leftJoin('partLots.storage_location', 'storelocations')
            ->leftJoin('part.footprint', 'footprint')
            ->leftJoin('footprint.master_picture_attachment', 'footprint_attachment')
            ->leftJoin('part.manufacturer', 'manufacturer')
            ->leftJoin('part.orderdetails', 'orderdetails')
            ->leftJoin('orderdetails.supplier', 'suppliers')
            ->leftJoin('part.attachments', 'attachments')
            ->leftJoin('part.partUnit', 'partUnit');
    }

    private function buildCriteria(QueryBuilder $builder, array $options): void
    {
        $em = $builder->getEntityManager();


        if (isset($options['category'])) {
            $category = $options['category'];
            $list = $this->treeBuilder->typeToNodesList(Category::class, $category);
            $list[] = $category;

            $builder->andWhere('part.category IN (:cid)')->setParameter('cid', $list);
        }

        if (isset($options['footprint'])) {
            $category = $options['footprint'];
            $list = $this->treeBuilder->typeToNodesList(Footprint::class, $category);
            $list[] = $category;

            $builder->andWhere('part.footprint IN (:cid)')->setParameter('cid', $list);
        }

        if (isset($options['manufacturer'])) {
            $category = $options['manufacturer'];
            $list = $this->treeBuilder->typeToNodesList(Manufacturer::class, $category);
            $list[] = $category;

            $builder->andWhere('part.manufacturer IN (:cid)')->setParameter('cid', $list);
        }

        if (isset($options['storelocation'])) {
            $location = $options['storelocation'];
            $list = $this->treeBuilder->typeToNodesList(Storelocation::class, $location);
            $list[] = $location;

            $builder->andWhere('partLots.storage_location IN (:cid)')->setParameter('cid', $list);
        }

        if (isset($options['supplier'])) {
            $supplier = $options['supplier'];
            $list = $this->treeBuilder->typeToNodesList(Supplier::class, $supplier);
            $list[] = $supplier;

            $builder->andWhere('orderdetails.supplier IN (:cid)')->setParameter('cid', $list);
        }

        if (isset($options['tag'])) {
            $builder->andWhere('part.tags LIKE :tag')->setParameter('tag', '%'.$options['tag'].'%');
        }

        if (!empty($options['search'])) {
            if (!$options['search_options']['regex']) {
                //Dont show results, if no things are selected
                $builder->andWhere('0=1');
                $defined = false;
                if ($options['search_options']['name']) {
                    $builder->orWhere('part.name LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['description']) {
                    $builder->orWhere('part.description LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['comment']) {
                    $builder->orWhere('part.comment LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['category']) {
                    $builder->orWhere('category.name LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['manufacturer']) {
                    $builder->orWhere('manufacturer.name LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['footprint']) {
                    $builder->orWhere('footprint.name LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['tags']) {
                    $builder->orWhere('part.tags LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['store_location']) {
                    $builder->orWhere('storelocations.name LIKE :search');
                    $defined = true;
                }
                if ($options['search_options']['supplier']) {
                    $builder->orWhere('suppliers.name LIKE :search');
                    $defined = true;
                }

                if ($defined) {
                    $builder->setParameter('search', '%'.$options['search'].'%');
                }
            } else { //Use REGEX
                $builder->andWhere('0=1');
                $defined = false;
                if ($options['search_options']['name']) {
                    $builder->orWhere('REGEXP(part.name, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['description']) {
                    $builder->orWhere('REGEXP(part.description, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['comment']) {
                    $builder->orWhere('REGEXP(part.comment, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['category']) {
                    $builder->orWhere('REGEXP(category.name, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['manufacturer']) {
                    $builder->orWhere('REGEXP(manufacturer.name, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['footprint']) {
                    $builder->orWhere('REGEXP(footprint.name, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['tags']) {
                    $builder->orWhere('REGEXP(part.tags, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['store_location']) {
                    $builder->orWhere('REGEXP(storelocations.name, :search) = 1');
                    $defined = true;
                }
                if ($options['search_options']['supplier']) {
                    $builder->orWhere('REGEXP(suppliers.name, :search) = 1');
                    $defined = true;
                }

                if ($defined) {
                    $builder->setParameter('search', $options['search']);
                }

            }
        }
    }
}


1			<?php
2			/**
3			* This file is part of Part-DB (https://github.com/Part-DB/Part-DB-symfony).
4			*
5			* Copyright (C) 2019 - 2020 Jan Böhmer (https://github.com/jbtronics)
6			*
7			* This program is free software: you can redistribute it and/or modify
8			* it under the terms of the GNU Affero General Public License as published
9			* by the Free Software Foundation, either version 3 of the License, or
10			* (at your option) any later version.
11			*
12			* This program is distributed in the hope that it will be useful,
13			* but WITHOUT ANY WARRANTY; without even the implied warranty of
14			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15			* GNU Affero General Public License for more details.
16			*
17			* You should have received a copy of the GNU Affero General Public License
18			* along with this program. If not, see <https://www.gnu.org/licenses/>.
19			*/
20
21			declare(strict_types=1);
22
23			/**
24			* This file is part of Part-DB (https://github.com/Part-DB/Part-DB-symfony).
25			*
26			* Copyright (C) 2019 Jan Böhmer (https://github.com/jbtronics)
27			*
28			* This program is free software; you can redistribute it and/or
29			* modify it under the terms of the GNU General Public License
30			* as published by the Free Software Foundation; either version 2
31			* of the License, or (at your option) any later version.
32			*
33			* This program is distributed in the hope that it will be useful,
34			* but WITHOUT ANY WARRANTY; without even the implied warranty of
35			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
36			* GNU General Public License for more details.
37			*
38			* You should have received a copy of the GNU General Public License
39			* along with this program; if not, write to the Free Software
40			* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
41			*/
42
43			namespace App\DataTables;
44
45			use App\DataTables\Adapter\FetchJoinORMAdapter;
46			use App\DataTables\Column\EntityColumn;
47			use App\DataTables\Column\LocaleDateTimeColumn;
48			use App\DataTables\Column\MarkdownColumn;
49			use App\DataTables\Column\PartAttachmentsColumn;
50			use App\DataTables\Column\TagsColumn;
51			use App\Entity\Parts\Category;
52			use App\Entity\Parts\Footprint;
53			use App\Entity\Parts\Manufacturer;
54			use App\Entity\Parts\Part;
55			use App\Entity\Parts\Storelocation;
56			use App\Entity\Parts\Supplier;
57			use App\Services\AmountFormatter;
58			use App\Services\Attachments\AttachmentURLGenerator;
59			use App\Services\Attachments\PartPreviewGenerator;
60			use App\Services\EntityURLGenerator;
61			use App\Services\Trees\NodesListBuilder;
62			use Doctrine\ORM\QueryBuilder;
63			use Omines\DataTablesBundle\Adapter\Doctrine\ORM\SearchCriteriaProvider;
64			use Omines\DataTablesBundle\Column\BoolColumn;
65			use Omines\DataTablesBundle\Column\MapColumn;
66			use Omines\DataTablesBundle\Column\TextColumn;
67			use Omines\DataTablesBundle\DataTable;
68			use Omines\DataTablesBundle\DataTableTypeInterface;
69			use Symfony\Component\OptionsResolver\OptionsResolver;
70			use Symfony\Contracts\Translation\TranslatorInterface;
71
72			final class PartsDataTable implements DataTableTypeInterface
73			{
74			private $translator;
75			private $treeBuilder;
76			private $amountFormatter;
77			private $previewGenerator;
78			private $attachmentURLGenerator;
79			/**
80			* @var EntityURLGenerator
81			*/
82			private $urlGenerator;
83
84			public function __construct(EntityURLGenerator $urlGenerator, TranslatorInterface $translator,
85			NodesListBuilder $treeBuilder, AmountFormatter $amountFormatter,
86			PartPreviewGenerator $previewGenerator, AttachmentURLGenerator $attachmentURLGenerator)
87			{
88			$this->urlGenerator = $urlGenerator;
89			$this->translator = $translator;
90			$this->treeBuilder = $treeBuilder;
91			$this->amountFormatter = $amountFormatter;
92			$this->previewGenerator = $previewGenerator;
93			$this->attachmentURLGenerator = $attachmentURLGenerator;
94			}
95
96			public function configureOptions(OptionsResolver $optionsResolver)
97			{
98			$optionsResolver->setDefaults([
99			'category' => null,
100			'footprint' => null,
101			'manufacturer' => null,
102			'storelocation' => null,
103			'supplier' => null,
104			'tag' => null,
105			'search' => null,
106			]);
107
108			$optionsResolver->setAllowedTypes('category', ['null', Category::class]);
109			$optionsResolver->setAllowedTypes('footprint', ['null', Footprint::class]);
110			$optionsResolver->setAllowedTypes('manufacturer', ['null', Manufacturer::class]);
111			$optionsResolver->setAllowedTypes('supplier', ['null', Supplier::class]);
112			$optionsResolver->setAllowedTypes('tag', ['null', 'string']);
113			$optionsResolver->setAllowedTypes('search', ['null', 'string']);
114
115			//Configure search options
116			$optionsResolver->setDefault('search_options', function (OptionsResolver $resolver) {
117			$resolver->setDefaults([
118			'name' => true,
119			'category' => true,
120			'description' => true,
121			'store_location' => true,
122			'comment' => true,
123			'ordernr' => true,
124			'supplier' => false,
125			'manufacturer' => false,
126			'footprint' => false,
127			'tags' => false,
128			'regex' => false,
129			]);
130			$resolver->setAllowedTypes('name', 'bool');
131			$resolver->setAllowedTypes('category', 'bool');
132			$resolver->setAllowedTypes('description', 'bool');
133			$resolver->setAllowedTypes('store_location', 'bool');
134			$resolver->setAllowedTypes('comment', 'bool');
135			$resolver->setAllowedTypes('supplier', 'bool');
136			$resolver->setAllowedTypes('manufacturer', 'bool');
137			$resolver->setAllowedTypes('footprint', 'bool');
138			$resolver->setAllowedTypes('tags', 'bool');
139			$resolver->setAllowedTypes('regex', 'bool');
140			});
141			}
142
143			public function configure(DataTable $dataTable, array $options): void
144			{
145			$resolver = new OptionsResolver();
146			$this->configureOptions($resolver);
147			$options = $resolver->resolve($options);
148
149			$dataTable
150			->add('picture', TextColumn::class, [
151			'label' => '',
152			'render' => function ($value, Part $context) {
153			$preview_attachment = $this->previewGenerator->getTablePreviewAttachment($context);
154			if (null === $preview_attachment) {
155			return '';
156			}
157
158			return sprintf(
159			'<img alt="%s" src="%s" data-thumbnail="%s" class="%s">',
160			'Part image',
161			$this->attachmentURLGenerator->getThumbnailURL($preview_attachment),
162			$this->attachmentURLGenerator->getThumbnailURL($preview_attachment, 'thumbnail_md'),
163			'img-fluid hoverpic'
164			);
165			},
166			])
167			->add('name', TextColumn::class, [
168			'label' => $this->translator->trans('part.table.name'),
169			'render' => function ($value, Part $context) {
170			return sprintf(
171			'<a href="%s">%s</a>',
172			$this->urlGenerator->infoURL($context),
173			$context->getName()
174			);
175			},
176			])
177			->add('id', TextColumn::class, [
178			'label' => $this->translator->trans('part.table.id'),
179			'visible' => false,
180			])
181			->add('description', MarkdownColumn::class, [
182			'label' => $this->translator->trans('part.table.description'),
183			])
184			->add('category', EntityColumn::class, [
185			'label' => $this->translator->trans('part.table.category'),
186			'property' => 'category',
187			])
188			->add('footprint', EntityColumn::class, [
189			'property' => 'footprint',
190			'label' => $this->translator->trans('part.table.footprint'),
191			])
192			->add('manufacturer', EntityColumn::class, [
193			'property' => 'manufacturer',
194			'label' => $this->translator->trans('part.table.manufacturer'),
195			])
196			->add('storelocation', TextColumn::class, [
197			'label' => $this->translator->trans('part.table.storeLocations'),
198			'render' => function ($value, Part $context) {
199			$tmp = [];
200			foreach ($context->getPartLots() as $lot) {
201			//Ignore lots without storelocation
202			if (null === $lot->getStorageLocation()) {
203			continue;
204			}
205			$tmp[] = sprintf(
206			'<a href="%s">%s</a>',
207			$this->urlGenerator->listPartsURL($lot->getStorageLocation()),
208			$lot->getStorageLocation()->getName()
209			);
210			}
211
212			return implode('<br>', $tmp);
213			},
214			])
215			->add('amount', TextColumn::class, [
216			'label' => $this->translator->trans('part.table.amount'),
217			'render' => function ($value, Part $context) {
218			$amount = $context->getAmountSum();
219
220			return $this->amountFormatter->format($amount, $context->getPartUnit());
221			},
222			])
223			->add('minamount', TextColumn::class, [
224			'label' => $this->translator->trans('part.table.minamount'),
225			'visible' => false,
226			'render' => function ($value, Part $context) {
227			return $this->amountFormatter->format($value, $context->getPartUnit());
228			},
229			])
230			->add('partUnit', TextColumn::class, [
231			'field' => 'partUnit.name',
232			'label' => $this->translator->trans('part.table.partUnit'),
233			'visible' => false,
234			])
235			->add('addedDate', LocaleDateTimeColumn::class, [
236			'label' => $this->translator->trans('part.table.addedDate'),
237			'visible' => false,
238			])
239			->add('lastModified', LocaleDateTimeColumn::class, [
240			'label' => $this->translator->trans('part.table.lastModified'),
241			'visible' => false,
242			])
243			->add('needs_review', BoolColumn::class, [
244			'label' => $this->translator->trans('part.table.needsReview'),
245			'trueValue' => $this->translator->trans('true'),
246			'falseValue' => $this->translator->trans('false'),
247			'nullValue' => '',
248			'visible' => false,
249			])
250			->add('favorite', BoolColumn::class, [
251			'label' => $this->translator->trans('part.table.favorite'),
252			'trueValue' => $this->translator->trans('true'),
253			'falseValue' => $this->translator->trans('false'),
254			'nullValue' => '',
255			'visible' => false,
256			])
257			->add('manufacturing_status', MapColumn::class, [
258			'label' => $this->translator->trans('part.table.manufacturingStatus'),
259			'visible' => false,
260			'default' => $this->translator->trans('m_status.unknown'),
261			'map' => [
262			'' => $this->translator->trans('m_status.unknown'),
263			'announced' => $this->translator->trans('m_status.announced'),
264			'active' => $this->translator->trans('m_status.active'),
265			'nrfnd' => $this->translator->trans('m_status.nrfnd'),
266			'eol' => $this->translator->trans('m_status.eol'),
267			'discontinued' => $this->translator->trans('m_status.discontinued'),
268			],
269			])
270			->add('manufacturer_product_number', TextColumn::class, [
271			'label' => $this->translator->trans('part.table.mpn'),
272			'visible' => false,
273			])
274			->add('mass', TextColumn::class, [
275			'label' => $this->translator->trans('part.table.mass'),
276			'visible' => false,
277			])
278			->add('tags', TagsColumn::class, [
279			'label' => $this->translator->trans('part.table.tags'),
280			'visible' => false,
281			])
282			->add('attachments', PartAttachmentsColumn::class, [
283			'label' => $this->translator->trans('part.table.attachments'),
284			'visible' => false,
285			])
286
287			->addOrderBy('name')
288			->createAdapter(FetchJoinORMAdapter::class, [
289			'simple_total_query' => true,
290			'query' => function (QueryBuilder $builder): void {
291			$this->getQuery($builder);
292			},
293			'entity' => Part::class,
294			'criteria' => [
295			function (QueryBuilder $builder) use ($options): void {
296			$this->buildCriteria($builder, $options);
297			},
298			new SearchCriteriaProvider(),
299			],
300			]);
301			}
302
303			private function getQuery(QueryBuilder $builder): void
304			{
305			$builder->distinct()->select('part')
306			->addSelect('category')
307			->addSelect('footprint')
308			->addSelect('manufacturer')
309			->addSelect('partUnit')
310			->addSelect('master_picture_attachment')
311			->addSelect('footprint_attachment')
312			->addSelect('partLots')
313			->addSelect('orderdetails')
314			->addSelect('attachments')
315			->addSelect('storelocations')
316			->from(Part::class, 'part')
317			->leftJoin('part.category', 'category')
318			->leftJoin('part.master_picture_attachment', 'master_picture_attachment')
319			->leftJoin('part.partLots', 'partLots')
320			->leftJoin('partLots.storage_location', 'storelocations')
321			->leftJoin('part.footprint', 'footprint')
322			->leftJoin('footprint.master_picture_attachment', 'footprint_attachment')
323			->leftJoin('part.manufacturer', 'manufacturer')
324			->leftJoin('part.orderdetails', 'orderdetails')
325			->leftJoin('orderdetails.supplier', 'suppliers')
326			->leftJoin('part.attachments', 'attachments')
327			->leftJoin('part.partUnit', 'partUnit');
328			}
329
330			private function buildCriteria(QueryBuilder $builder, array $options): void
331			{
332			$em = $builder->getEntityManager();
			0 ignored issues – show Unused Code introduced 2019-09-08 15:43 UTC by Report Bug Copy Issue Report The assignment to `$em` is dead and can be removed. Loading history...
333
334			if (isset($options['category'])) {
335			$category = $options['category'];
336			$list = $this->treeBuilder->typeToNodesList(Category::class, $category);
337			$list[] = $category;
338
339			$builder->andWhere('part.category IN (:cid)')->setParameter('cid', $list);
340			}
341
342			if (isset($options['footprint'])) {
343			$category = $options['footprint'];
344			$list = $this->treeBuilder->typeToNodesList(Footprint::class, $category);
345			$list[] = $category;
346
347			$builder->andWhere('part.footprint IN (:cid)')->setParameter('cid', $list);
348			}
349
350			if (isset($options['manufacturer'])) {
351			$category = $options['manufacturer'];
352			$list = $this->treeBuilder->typeToNodesList(Manufacturer::class, $category);
353			$list[] = $category;
354
355			$builder->andWhere('part.manufacturer IN (:cid)')->setParameter('cid', $list);
356			}
357
358			if (isset($options['storelocation'])) {
359			$location = $options['storelocation'];
360			$list = $this->treeBuilder->typeToNodesList(Storelocation::class, $location);
361			$list[] = $location;
362
363			$builder->andWhere('partLots.storage_location IN (:cid)')->setParameter('cid', $list);
364			}
365
366			if (isset($options['supplier'])) {
367			$supplier = $options['supplier'];
368			$list = $this->treeBuilder->typeToNodesList(Supplier::class, $supplier);
369			$list[] = $supplier;
370
371			$builder->andWhere('orderdetails.supplier IN (:cid)')->setParameter('cid', $list);
372			}
373
374			if (isset($options['tag'])) {
375			$builder->andWhere('part.tags LIKE :tag')->setParameter('tag', '%'.$options['tag'].'%');
376			}
377
378			if (!empty($options['search'])) {
379			if (!$options['search_options']['regex']) {
380			//Dont show results, if no things are selected
381			$builder->andWhere('0=1');
382			$defined = false;
383			if ($options['search_options']['name']) {
384			$builder->orWhere('part.name LIKE :search');
385			$defined = true;
386			}
387			if ($options['search_options']['description']) {
388			$builder->orWhere('part.description LIKE :search');
389			$defined = true;
390			}
391			if ($options['search_options']['comment']) {
392			$builder->orWhere('part.comment LIKE :search');
393			$defined = true;
394			}
395			if ($options['search_options']['category']) {
396			$builder->orWhere('category.name LIKE :search');
397			$defined = true;
398			}
399			if ($options['search_options']['manufacturer']) {
400			$builder->orWhere('manufacturer.name LIKE :search');
401			$defined = true;
402			}
403			if ($options['search_options']['footprint']) {
404			$builder->orWhere('footprint.name LIKE :search');
405			$defined = true;
406			}
407			if ($options['search_options']['tags']) {
408			$builder->orWhere('part.tags LIKE :search');
409			$defined = true;
410			}
411			if ($options['search_options']['store_location']) {
412			$builder->orWhere('storelocations.name LIKE :search');
413			$defined = true;
414			}
415			if ($options['search_options']['supplier']) {
416			$builder->orWhere('suppliers.name LIKE :search');
417			$defined = true;
418			}
419
420			if ($defined) {
421			$builder->setParameter('search', '%'.$options['search'].'%');
422			}
423			} else { //Use REGEX
424			$builder->andWhere('0=1');
425			$defined = false;
426			if ($options['search_options']['name']) {
427			$builder->orWhere('REGEXP(part.name, :search) = 1');
428			$defined = true;
429			}
430			if ($options['search_options']['description']) {
431			$builder->orWhere('REGEXP(part.description, :search) = 1');
432			$defined = true;
433			}
434			if ($options['search_options']['comment']) {
435			$builder->orWhere('REGEXP(part.comment, :search) = 1');
436			$defined = true;
437			}
438			if ($options['search_options']['category']) {
439			$builder->orWhere('REGEXP(category.name, :search) = 1');
440			$defined = true;
441			}
442			if ($options['search_options']['manufacturer']) {
443			$builder->orWhere('REGEXP(manufacturer.name, :search) = 1');
444			$defined = true;
445			}
446			if ($options['search_options']['footprint']) {
447			$builder->orWhere('REGEXP(footprint.name, :search) = 1');
448			$defined = true;
449			}
450			if ($options['search_options']['tags']) {
451			$builder->orWhere('REGEXP(part.tags, :search) = 1');
452			$defined = true;
453			}
454			if ($options['search_options']['store_location']) {
455			$builder->orWhere('REGEXP(storelocations.name, :search) = 1');
456			$defined = true;
457			}
458			if ($options['search_options']['supplier']) {
459			$builder->orWhere('REGEXP(suppliers.name, :search) = 1');
460			$defined = true;
461			}
462
463			if ($defined) {
464			$builder->setParameter('search', $options['search']);
465			}
466
467			}
468			}
469			}
470			}
471

Part-DB / Part-DB-symfony

Push — master ( 120eb3...86ee10 )

PartsDataTable::getQuery() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like