AbstractReportRepository::getVariantsOptions() - Code Metrics - monsieurbiz/SyliusSalesReportsPlugin - Measure and Improve Code Quality continuously with Scrutinizer

AbstractReportRepository::getVariantsOptions() A
last analyzed 2020-10-01 15:01 UTC

↳ Parent: AbstractReportRepository

Complexity

Conditions	2
Paths	2

Size

Total Lines	23
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	16
c	1
b	0
f	0
dl	0
loc	23
rs	9.7333
cc	2
nc	2
nop	1

<?php

declare(strict_types=1);

namespace MonsieurBiz\SyliusSalesReportsPlugin\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\QueryBuilder;
use Sylius\Component\Core\Model\AdjustmentInterface;
use Sylius\Component\Core\Model\ChannelInterface;
use Sylius\Component\Core\Model\OrderInterface;
use Sylius\Component\Core\OrderPaymentStates;
use Sylius\Component\Core\Repository\OrderRepositoryInterface;
use Sylius\Component\Core\Repository\ProductVariantRepositoryInterface;

abstract class AbstractReportRepository
{
    // Adjustment if Admin Order Creation plugin is installed
    public const ADMIN_ORDER_DISCOUNT_ADJUSTMENT = 'order_discount';
    public const ADMIN_ORDER_ITEM_DISCOUNT_ADJUSTMENT = 'order_item_discount';

    /**
     * @var OrderRepositoryInterface
     */
    protected $orderRepository;

    /**
     * @var ProductVariantRepositoryInterface
     */
    protected $productVariantRepository;

    /**
     * Result with totals, one dimensional array
     *
     * @var array
     */
    protected $result;

    /**
     * Results with totals by elements, two dimensional array
     *
     * @var array
     */
    protected $results;

    /**
     * The elements we want to group, id as key, id or label as value, one dimensional array
     *
     * @var array
     */
    protected $elements;

    /**
     * An array of elements we want to group, two dimensional array
     *
     * @var array
     */
    protected $elementsArray;

    /**
     * ReportRepository constructor.
     * @param OrderRepositoryInterface $orderRepository
     * @param ProductVariantRepositoryInterface $productVariantRepository
     */
    public function __construct(
        OrderRepositoryInterface $orderRepository,
        ProductVariantRepositoryInterface $productVariantRepository
    ) {
        $this->orderRepository = $orderRepository;
        $this->productVariantRepository = $productVariantRepository;
    }

    /**
     * Generate results for order item units
     *
     * @param ChannelInterface $channel
     * @param \DateTimeInterface $from
     * @param \DateTimeInterface $to
     * @return array
     */
    protected function getOrderItemUnitValues(
        ChannelInterface $channel,
        \DateTimeInterface $from,
        \DateTimeInterface $to
    ): array {
        $queryBuilder = $this->createOrderQuery()
            ->select($this->getSelectColumns(true, false, false))
            ->leftJoin('o.items', 'item')
            ->leftJoin('item.variant', 'variant')
            ->leftJoin('item.units', 'element');
        $queryBuilder = $this->appendAdjustmentsAndParameters($queryBuilder, $channel, $from, $to);
        return $queryBuilder->getQuery()->getArrayResult();
    }

    /**
     * Generate results for order items
     *
     * @param ChannelInterface $channel
     * @param \DateTimeInterface $from
     * @param \DateTimeInterface $to
     * @return array
     */
    protected function getOrderItemValues(
        ChannelInterface $channel,
        \DateTimeInterface $from,
        \DateTimeInterface $to
    ): array {
        $queryBuilder = $this->createOrderQuery()
            ->select($this->getSelectColumns(false, true, false))
            ->leftJoin('o.items', 'element')
            ->leftJoin('element.variant', 'variant');
        $queryBuilder = $this->appendAdjustmentsAndParameters($queryBuilder, $channel, $from, $to);
        return $queryBuilder->getQuery()->getArrayResult();
    }

    /**
     * Generate results for orders
     *
     * @param ChannelInterface $channel
     * @param \DateTimeInterface $from
     * @param \DateTimeInterface $to
     * @return array
     */
    protected function getOrderValues(
        ChannelInterface $channel,
        \DateTimeInterface $from,
        \DateTimeInterface $to
    ): array {
        $queryBuilder = $this->createOrderQuery()->select($this->getSelectColumns(false, false, true));
        $queryBuilder = $this->appendAdjustmentsAndParameters($queryBuilder, $channel, $from, $to, true);
        return $queryBuilder->getQuery()->getArrayResult();
    }

    /**
     * Retrieve columns for select.
     * Column order_id is used to generate average report
     * Column without_tax is for unit price without tax in item units
     * Columns without_tax_promo, without_tax_shipping, tax columns are respectively for promotions, shipping, tax amounts
     * Columns item and total are respectively for total for items total for orders (With shipping etc.)
     *
     * @param bool $isItemUnit
     * @param bool $isItem
     * @param bool $isOrder
     * @return string
     */
    protected function getSelectColumns(bool $isItemUnit = false, bool $isItem = false, bool $isOrder = false): string
    {
        return implode(',', [
            // Order ID
            'o.id as order_id',

            // Product infos
            ($isItemUnit ? 'IDENTITY(variant.product) as product_id' : ($isItem ? 'IDENTITY(variant.product) as product_id' : '\'\' as product_id')),
            ($isItemUnit ? 'item.productName as product_name' : ($isItem ? 'element.productName as product_name' : '\'\' as product_name')),

            // Variant infos
            ($isItemUnit ? 'IDENTITY(item.variant) as variant_id' : ($isItem ? 'IDENTITY(element.variant) as variant_id' : '\'\' as variant_id')),
            ($isItemUnit ? 'CONCAT(item.productName, \' \' ,item.variantName) as variant_name' : ($isItem ? 'CONCAT(element.productName, \' \' , element.variantName) as variant_name' : '\'\' as variant_name')),

            // Adjustments
            $isItemUnit ? 'item.unitPrice - COALESCE(tax_adjustment.amount, 0) as without_tax' : '0 as without_tax',
            // Only retrieve without_tax price for item units
            '(COALESCE(order_promotion_adjustment.amount, 0) + COALESCE(order_item_promotion_adjustment.amount, 0) + COALESCE(order_shipping_promotion_adjustment.amount, 0) + COALESCE(order_unit_promotion_adjustment.amount, 0)) AS without_tax_promo',
            'shipping_adjustment.amount as without_tax_shipping',
            'tax_adjustment.amount as tax',

            // Totals
            $isOrder ? 'o.total as total' : '0 as total',
            $isItem ? 'element.total as item_row' : '0 as item_row'
        ]);
    }

    /**
     * Make joins with all adjustments, add conditions and set parameters to query
     *
     * @param QueryBuilder $queryBuilder
     * @param ChannelInterface $channel
     * @param \DateTimeInterface $from
     * @param \DateTimeInterface $to
     * @param bool $isOrder
     * @return mixed
     */
    protected function appendAdjustmentsAndParameters(
        QueryBuilder $queryBuilder,
        ChannelInterface $channel,
        \DateTimeInterface $from,
        \DateTimeInterface $to,
        bool $isOrder = false
    ) {
        $elementAlias = $isOrder ? 'o' : 'element';
        return $queryBuilder
            // Adjustments joins
            ->leftJoin($elementAlias . '.adjustments', 'tax_adjustment', 'WITH', 'tax_adjustment.type = :tax_type')
            ->leftJoin($elementAlias . '.adjustments', 'shipping_adjustment', 'WITH',
                'shipping_adjustment.type = :shipping_type')
            ->leftJoin($elementAlias . '.adjustments', 'order_promotion_adjustment', 'WITH',
                'order_promotion_adjustment.type = :order_promotion_type OR order_promotion_adjustment.type = :admin_order_promotion_type')
            ->leftJoin($elementAlias . '.adjustments', 'order_item_promotion_adjustment', 'WITH',
                'order_item_promotion_adjustment.type = :order_item_promotion_type OR order_item_promotion_adjustment.type = :admin_order_item_promotion_type')
            ->leftJoin($elementAlias . '.adjustments', 'order_shipping_promotion_adjustment', 'WITH',
                'order_shipping_promotion_adjustment.type = :order_shipping_promotion_type')
            ->leftJoin($elementAlias . '.adjustments', 'order_unit_promotion_adjustment', 'WITH',
                'order_unit_promotion_adjustment.type = :order_unit_promotion_type')
            // Adjustments parameters
            ->setParameter('tax_type', AdjustmentInterface::TAX_ADJUSTMENT)
            ->setParameter('shipping_type', AdjustmentInterface::SHIPPING_ADJUSTMENT)
            ->setParameter('order_promotion_type', AdjustmentInterface::ORDER_PROMOTION_ADJUSTMENT)
            ->setParameter('admin_order_promotion_type', self::ADMIN_ORDER_DISCOUNT_ADJUSTMENT)
            ->setParameter('order_item_promotion_type', AdjustmentInterface::ORDER_ITEM_PROMOTION_ADJUSTMENT)
            ->setParameter('admin_order_item_promotion_type', self::ADMIN_ORDER_ITEM_DISCOUNT_ADJUSTMENT)
            ->setParameter('order_shipping_promotion_type', AdjustmentInterface::ORDER_SHIPPING_PROMOTION_ADJUSTMENT)
            ->setParameter('order_unit_promotion_type', AdjustmentInterface::ORDER_UNIT_PROMOTION_ADJUSTMENT)
            // Filters on orders in channel, which are paid, not refunded and completed between the wanted dates
            ->andWhere('o.channel = :channel')
            ->andWhere('o.state IN (:states)')
            ->andWhere('o.paymentState IN (:payment_states)')
            ->andWhere('o.checkoutCompletedAt BETWEEN :from AND :to')
            // Filters parameters
            ->setParameter('channel', $channel)
            ->setParameter('states', [OrderInterface::STATE_FULFILLED, OrderInterface::STATE_NEW])
            ->setParameter('payment_states', [OrderPaymentStates::STATE_PAID]) // @TODO Take care of OrderPaymentStates::STATE_PARTIALLY_PAID
            ->setParameter('from', $from)
            ->setParameter('to', $to);
    }

    /**
     * Populate result array with options and option values data
     *
     * @param string $localeCode
     * @return array
     */
    protected function populateOptions(string $localeCode): array
    {
        $variantOptions = $this->getVariantsOptions($localeCode);
        $salesResults = [];

        foreach ($this->results as $result) {
            $variantId = $result['variant_id'];
            $options = $variantOptions[$variantId];

            // Rename field with _total
            foreach ($result as $key => $value) {
                if (strpos($key, '_total')) {
                    $result[str_replace('_total', '', $key)] = $value;
                    unset($result[$key]);
                }
            }
            foreach ($options as $optionCode => $option) {
                $result['option_code'] = $optionCode;
                $result['option_label'] = $option['label'];
                $result['option_value_code'] = $option['value_code'];
                $result['option_value_label'] = $option['value_label'];
                $salesResults[] = $result;
            }
        }

        return $salesResults;
    }

    /**
     * Retrieve options for all variants and build an array
     *
     * @param string $localeCode
     * @return array
     */
    protected function getVariantsOptions(string $localeCode): array
    {
        $queryBuilder = $this->createProductVariantQuery()
            ->select('v.id AS variant_id, option.code AS option_code, option_translation.name AS option_label, option_value.code AS option_value_code, option_value_translation.value AS option_value_label')
            ->leftJoin('v.optionValues', 'option_value')
            ->leftJoin('option_value.translations', 'option_value_translation', 'WITH',
                'option_value_translation.locale = :locale')
            ->leftJoin('option_value.option', 'option')
            ->leftJoin('option.translations', 'option_translation', 'WITH', 'option_translation.locale = :locale')
            ->setParameter('locale', $localeCode);

        $variantOptionsValues = [];

        $result = $queryBuilder->getQuery()->getArrayResult();
        foreach ($result as $variantOptionValue) {
            $variantOptionsValues[$variantOptionValue['variant_id']][$variantOptionValue['option_code']] = [
                'label' => $variantOptionValue['option_label'],
                'value_code' => $variantOptionValue['option_value_code'],
                'value_label' => $variantOptionValue['option_value_label'],
            ];
        }

        return $variantOptionsValues;
    }

    /**
     * Init the result with 0 totals
     */
    protected function initResult()
    {
        $this->result = [
            'without_tax_total' => 0,
            'without_tax_promo_total' => 0,
            'without_tax_shipping_total' => 0,
            'tax_total' => 0,
            'total' => 0,
            'item_row_total' => 0,
        ];
        $this->elements = [];
    }

    /**
     * Increment results with given array
     *
     * @param array $elementResults
     * @param string|null $groupField
     */
    protected function addResults(array $elementResults, ?string $groupField = null): void
    {
        // Loop on given elements to increments current result
        foreach ($elementResults as $elementResult) {
            foreach ($this->result as $key => $val) {
                if (strpos($key, 'total') !== false) {
                    // Get the field key, for example `without_tax_shipping` if we need to increment `without_tax_shipping_total`
                    $resultKey = str_replace('_total', '', $key);
                    if (isset($elementResult[$resultKey])) {
                        $this->result[$key] += (int) $elementResult[$resultKey]; // Cast in int because doctrine return string for columns with `+`, and we can have null values
                    }
                }
            }
            // Add group field value if we got one, for example, an order ID to have an average per order or a list per product variant
            if ($groupField !== null) {
                $this->elements[$elementResult[$groupField]] = $elementResult[$groupField];
            }
        }
    }

    /**
     * Make the sum of results by elements
     *
     * @param array $elementResults
     * @param string $groupField
     * @param string|null $labelField
     * @param array|null $extraFields
     */
    protected function addResultsByElement(
        array $elementResults,
        string $groupField,
        ?string $labelField = null,
        ?array $extraFields = null
    ): void {
        // Loop on given elements to increments current result
        foreach ($elementResults as $elementResult) {
            $elementId = $elementResult[$groupField];
            // Init results for this element or retrieve existing one
            if (!isset($this->results[$elementId])) {
                $this->initResult();
            } else {
                $this->result = $this->results[$elementId];
                $this->elements = $this->elementsArray[$elementId];
            }
            // Add results by order
            $this->addResults([$elementResult], 'order_id');
            $this->result['order_id'] = $elementResult['order_id'];

            // Add extra fields
            $this->result[$groupField] = $elementId; // Grouped field ID
            if ($labelField && isset($elementResult[$labelField]) && !empty($elementResult[$labelField])) {
                $this->result[$labelField] = $elementResult[$labelField]; // Grouped field label if given
            } elseif ($labelField && (!isset($elementResult[$labelField]) || empty($elementResult[$labelField]))) {
                $this->result[$labelField] = '';
            }
            if (!empty($extraFields)) {
                foreach ($extraFields as $extraField) {
                    if (isset($elementResult[$extraField])) {
                        $this->result[$extraField] = $elementResult[$extraField];
                    } else {
                        $this->result[$extraField] = '';
                    }
                }
            }

            // Update results for this element
            $this->results[$elementId] = $this->result;
            $this->elementsArray[$elementId] = $this->elements;
        }

        // Aggregate number of order per element
        foreach ($this->results as $key => $value) {
            $this->results[$key]['number_of_elements'] = count($this->elementsArray[$key]);
        }
    }

    /**
     * Make the average of results depending on number of elements
     */
    protected function averageResult(): void
    {
        if (!empty($this->elements)) {
            $numberOfElements = count($this->elements);
            foreach ($this->result as $key => $val) {
                $this->result[$key] = round($this->result[$key] / $numberOfElements);
            }
            $this->result['number_of_elements'] = count($this->elements);
        } else {
            $this->result['number_of_elements'] = 0;
        }
    }

    /**
     * @return QueryBuilder
     */
    protected function createOrderQuery()
    {
        /** @var EntityRepository $repository */
        $repository = $this->orderRepository;
        return $repository->createQueryBuilder('o');
    }

    /**
     * @return QueryBuilder
     */
    protected function createProductVariantQuery()
    {
        /** @var EntityRepository $repository */
        $repository = $this->productVariantRepository;
        return $repository->createQueryBuilder('v');
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace MonsieurBiz\SyliusSalesReportsPlugin\Repository;
6
7			use Doctrine\ORM\EntityRepository;
8			use Doctrine\ORM\QueryBuilder;
9			use Sylius\Component\Core\Model\AdjustmentInterface;
10			use Sylius\Component\Core\Model\ChannelInterface;
11			use Sylius\Component\Core\Model\OrderInterface;
12			use Sylius\Component\Core\OrderPaymentStates;
13			use Sylius\Component\Core\Repository\OrderRepositoryInterface;
14			use Sylius\Component\Core\Repository\ProductVariantRepositoryInterface;
15
16			abstract class AbstractReportRepository
17			{
18			// Adjustment if Admin Order Creation plugin is installed
19			public const ADMIN_ORDER_DISCOUNT_ADJUSTMENT = 'order_discount';
20			public const ADMIN_ORDER_ITEM_DISCOUNT_ADJUSTMENT = 'order_item_discount';
21
22			/**
23			* @var OrderRepositoryInterface
24			*/
25			protected $orderRepository;
26
27			/**
28			* @var ProductVariantRepositoryInterface
29			*/
30			protected $productVariantRepository;
31
32			/**
33			* Result with totals, one dimensional array
34			*
35			* @var array
36			*/
37			protected $result;
38
39			/**
40			* Results with totals by elements, two dimensional array
41			*
42			* @var array
43			*/
44			protected $results;
45
46			/**
47			* The elements we want to group, id as key, id or label as value, one dimensional array
48			*
49			* @var array
50			*/
51			protected $elements;
52
53			/**
54			* An array of elements we want to group, two dimensional array
55			*
56			* @var array
57			*/
58			protected $elementsArray;
59
60			/**
61			* ReportRepository constructor.
62			* @param OrderRepositoryInterface $orderRepository
63			* @param ProductVariantRepositoryInterface $productVariantRepository
64			*/
65			public function __construct(
66			OrderRepositoryInterface $orderRepository,
67			ProductVariantRepositoryInterface $productVariantRepository
68			) {
69			$this->orderRepository = $orderRepository;
70			$this->productVariantRepository = $productVariantRepository;
71			}
72
73			/**
74			* Generate results for order item units
75			*
76			* @param ChannelInterface $channel
77			* @param \DateTimeInterface $from
78			* @param \DateTimeInterface $to
79			* @return array
80			*/
81			protected function getOrderItemUnitValues(
82			ChannelInterface $channel,
83			\DateTimeInterface $from,
84			\DateTimeInterface $to
85			): array {
86			$queryBuilder = $this->createOrderQuery()
87			->select($this->getSelectColumns(true, false, false))
88			->leftJoin('o.items', 'item')
89			->leftJoin('item.variant', 'variant')
90			->leftJoin('item.units', 'element');
91			$queryBuilder = $this->appendAdjustmentsAndParameters($queryBuilder, $channel, $from, $to);
92			return $queryBuilder->getQuery()->getArrayResult();
93			}
94
95			/**
96			* Generate results for order items
97			*
98			* @param ChannelInterface $channel
99			* @param \DateTimeInterface $from
100			* @param \DateTimeInterface $to
101			* @return array
102			*/
103			protected function getOrderItemValues(
104			ChannelInterface $channel,
105			\DateTimeInterface $from,
106			\DateTimeInterface $to
107			): array {
108			$queryBuilder = $this->createOrderQuery()
109			->select($this->getSelectColumns(false, true, false))
110			->leftJoin('o.items', 'element')
111			->leftJoin('element.variant', 'variant');
112			$queryBuilder = $this->appendAdjustmentsAndParameters($queryBuilder, $channel, $from, $to);
113			return $queryBuilder->getQuery()->getArrayResult();
114			}
115
116			/**
117			* Generate results for orders
118			*
119			* @param ChannelInterface $channel
120			* @param \DateTimeInterface $from
121			* @param \DateTimeInterface $to
122			* @return array
123			*/
124			protected function getOrderValues(
125			ChannelInterface $channel,
126			\DateTimeInterface $from,
127			\DateTimeInterface $to
128			): array {
129			$queryBuilder = $this->createOrderQuery()->select($this->getSelectColumns(false, false, true));
130			$queryBuilder = $this->appendAdjustmentsAndParameters($queryBuilder, $channel, $from, $to, true);
131			return $queryBuilder->getQuery()->getArrayResult();
132			}
133
134			/**
135			* Retrieve columns for select.
136			* Column order_id is used to generate average report
137			* Column without_tax is for unit price without tax in item units
138			* Columns without_tax_promo, without_tax_shipping, tax columns are respectively for promotions, shipping, tax amounts
139			* Columns item and total are respectively for total for items total for orders (With shipping etc.)
140			*
141			* @param bool $isItemUnit
142			* @param bool $isItem
143			* @param bool $isOrder
144			* @return string
145			*/
146			protected function getSelectColumns(bool $isItemUnit = false, bool $isItem = false, bool $isOrder = false): string
147			{
148			return implode(',', [
149			// Order ID
150			'o.id as order_id',
151
152			// Product infos
153			($isItemUnit ? 'IDENTITY(variant.product) as product_id' : ($isItem ? 'IDENTITY(variant.product) as product_id' : '\'\' as product_id')),
154			($isItemUnit ? 'item.productName as product_name' : ($isItem ? 'element.productName as product_name' : '\'\' as product_name')),
155
156			// Variant infos
157			($isItemUnit ? 'IDENTITY(item.variant) as variant_id' : ($isItem ? 'IDENTITY(element.variant) as variant_id' : '\'\' as variant_id')),
158			($isItemUnit ? 'CONCAT(item.productName, \' \' ,item.variantName) as variant_name' : ($isItem ? 'CONCAT(element.productName, \' \' , element.variantName) as variant_name' : '\'\' as variant_name')),
159
160			// Adjustments
161			$isItemUnit ? 'item.unitPrice - COALESCE(tax_adjustment.amount, 0) as without_tax' : '0 as without_tax',
162			// Only retrieve without_tax price for item units
163			'(COALESCE(order_promotion_adjustment.amount, 0) + COALESCE(order_item_promotion_adjustment.amount, 0) + COALESCE(order_shipping_promotion_adjustment.amount, 0) + COALESCE(order_unit_promotion_adjustment.amount, 0)) AS without_tax_promo',
164			'shipping_adjustment.amount as without_tax_shipping',
165			'tax_adjustment.amount as tax',
166
167			// Totals
168			$isOrder ? 'o.total as total' : '0 as total',
169			$isItem ? 'element.total as item_row' : '0 as item_row'
170			]);
171			}
172
173			/**
174			* Make joins with all adjustments, add conditions and set parameters to query
175			*
176			* @param QueryBuilder $queryBuilder
177			* @param ChannelInterface $channel
178			* @param \DateTimeInterface $from
179			* @param \DateTimeInterface $to
180			* @param bool $isOrder
181			* @return mixed
182			*/
183			protected function appendAdjustmentsAndParameters(
184			QueryBuilder $queryBuilder,
185			ChannelInterface $channel,
186			\DateTimeInterface $from,
187			\DateTimeInterface $to,
188			bool $isOrder = false
189			) {
190			$elementAlias = $isOrder ? 'o' : 'element';
191			return $queryBuilder
192			// Adjustments joins
193			->leftJoin($elementAlias . '.adjustments', 'tax_adjustment', 'WITH', 'tax_adjustment.type = :tax_type')
194			->leftJoin($elementAlias . '.adjustments', 'shipping_adjustment', 'WITH',
195			'shipping_adjustment.type = :shipping_type')
196			->leftJoin($elementAlias . '.adjustments', 'order_promotion_adjustment', 'WITH',
197			'order_promotion_adjustment.type = :order_promotion_type OR order_promotion_adjustment.type = :admin_order_promotion_type')
198			->leftJoin($elementAlias . '.adjustments', 'order_item_promotion_adjustment', 'WITH',
199			'order_item_promotion_adjustment.type = :order_item_promotion_type OR order_item_promotion_adjustment.type = :admin_order_item_promotion_type')
200			->leftJoin($elementAlias . '.adjustments', 'order_shipping_promotion_adjustment', 'WITH',
201			'order_shipping_promotion_adjustment.type = :order_shipping_promotion_type')
202			->leftJoin($elementAlias . '.adjustments', 'order_unit_promotion_adjustment', 'WITH',
203			'order_unit_promotion_adjustment.type = :order_unit_promotion_type')
204			// Adjustments parameters
205			->setParameter('tax_type', AdjustmentInterface::TAX_ADJUSTMENT)
206			->setParameter('shipping_type', AdjustmentInterface::SHIPPING_ADJUSTMENT)
207			->setParameter('order_promotion_type', AdjustmentInterface::ORDER_PROMOTION_ADJUSTMENT)
208			->setParameter('admin_order_promotion_type', self::ADMIN_ORDER_DISCOUNT_ADJUSTMENT)
209			->setParameter('order_item_promotion_type', AdjustmentInterface::ORDER_ITEM_PROMOTION_ADJUSTMENT)
210			->setParameter('admin_order_item_promotion_type', self::ADMIN_ORDER_ITEM_DISCOUNT_ADJUSTMENT)
211			->setParameter('order_shipping_promotion_type', AdjustmentInterface::ORDER_SHIPPING_PROMOTION_ADJUSTMENT)
212			->setParameter('order_unit_promotion_type', AdjustmentInterface::ORDER_UNIT_PROMOTION_ADJUSTMENT)
213			// Filters on orders in channel, which are paid, not refunded and completed between the wanted dates
214			->andWhere('o.channel = :channel')
215			->andWhere('o.state IN (:states)')
216			->andWhere('o.paymentState IN (:payment_states)')
217			->andWhere('o.checkoutCompletedAt BETWEEN :from AND :to')
218			// Filters parameters
219			->setParameter('channel', $channel)
220			->setParameter('states', [OrderInterface::STATE_FULFILLED, OrderInterface::STATE_NEW])
221			->setParameter('payment_states', [OrderPaymentStates::STATE_PAID]) // @TODO Take care of OrderPaymentStates::STATE_PARTIALLY_PAID
222			->setParameter('from', $from)
223			->setParameter('to', $to);
224			}
225
226			/**
227			* Populate result array with options and option values data
228			*
229			* @param string $localeCode
230			* @return array
231			*/
232			protected function populateOptions(string $localeCode): array
233			{
234			$variantOptions = $this->getVariantsOptions($localeCode);
235			$salesResults = [];
236
237			foreach ($this->results as $result) {
238			$variantId = $result['variant_id'];
239			$options = $variantOptions[$variantId];
240
241			// Rename field with _total
242			foreach ($result as $key => $value) {
243			if (strpos($key, '_total')) {
244			$result[str_replace('_total', '', $key)] = $value;
245			unset($result[$key]);
246			}
247			}
248			foreach ($options as $optionCode => $option) {
249			$result['option_code'] = $optionCode;
250			$result['option_label'] = $option['label'];
251			$result['option_value_code'] = $option['value_code'];
252			$result['option_value_label'] = $option['value_label'];
253			$salesResults[] = $result;
254			}
255			}
256
257			return $salesResults;
258			}
259
260			/**
261			* Retrieve options for all variants and build an array
262			*
263			* @param string $localeCode
264			* @return array
265			*/
266			protected function getVariantsOptions(string $localeCode): array
267			{
268			$queryBuilder = $this->createProductVariantQuery()
269			->select('v.id AS variant_id, option.code AS option_code, option_translation.name AS option_label, option_value.code AS option_value_code, option_value_translation.value AS option_value_label')
270			->leftJoin('v.optionValues', 'option_value')
271			->leftJoin('option_value.translations', 'option_value_translation', 'WITH',
272			'option_value_translation.locale = :locale')
273			->leftJoin('option_value.option', 'option')
274			->leftJoin('option.translations', 'option_translation', 'WITH', 'option_translation.locale = :locale')
275			->setParameter('locale', $localeCode);
276
277			$variantOptionsValues = [];
278
279			$result = $queryBuilder->getQuery()->getArrayResult();
280			foreach ($result as $variantOptionValue) {
281			$variantOptionsValues[$variantOptionValue['variant_id']][$variantOptionValue['option_code']] = [
282			'label' => $variantOptionValue['option_label'],
283			'value_code' => $variantOptionValue['option_value_code'],
284			'value_label' => $variantOptionValue['option_value_label'],
285			];
286			}
287
288			return $variantOptionsValues;
289			}
290
291			/**
292			* Init the result with 0 totals
293			*/
294			protected function initResult()
295			{
296			$this->result = [
297			'without_tax_total' => 0,
298			'without_tax_promo_total' => 0,
299			'without_tax_shipping_total' => 0,
300			'tax_total' => 0,
301			'total' => 0,
302			'item_row_total' => 0,
303			];
304			$this->elements = [];
305			}
306
307			/**
308			* Increment results with given array
309			*
310			* @param array $elementResults
311			* @param string\|null $groupField
312			*/
313			protected function addResults(array $elementResults, ?string $groupField = null): void
314			{
315			// Loop on given elements to increments current result
316			foreach ($elementResults as $elementResult) {
317			foreach ($this->result as $key => $val) {
318			if (strpos($key, 'total') !== false) {
319			// Get the field key, for example `without_tax_shipping` if we need to increment `without_tax_shipping_total`
320			$resultKey = str_replace('_total', '', $key);
321			if (isset($elementResult[$resultKey])) {
322			$this->result[$key] += (int) $elementResult[$resultKey]; // Cast in int because doctrine return string for columns with `+`, and we can have null values
323			}
324			}
325			}
326			// Add group field value if we got one, for example, an order ID to have an average per order or a list per product variant
327			if ($groupField !== null) {
328			$this->elements[$elementResult[$groupField]] = $elementResult[$groupField];
329			}
330			}
331			}
332
333			/**
334			* Make the sum of results by elements
335			*
336			* @param array $elementResults
337			* @param string $groupField
338			* @param string\|null $labelField
339			* @param array\|null $extraFields
340			*/
341			protected function addResultsByElement(
342			array $elementResults,
343			string $groupField,
344			?string $labelField = null,
345			?array $extraFields = null
346			): void {
347			// Loop on given elements to increments current result
348			foreach ($elementResults as $elementResult) {
349			$elementId = $elementResult[$groupField];
350			// Init results for this element or retrieve existing one
351			if (!isset($this->results[$elementId])) {
352			$this->initResult();
353			} else {
354			$this->result = $this->results[$elementId];
355			$this->elements = $this->elementsArray[$elementId];
356			}
357			// Add results by order
358			$this->addResults([$elementResult], 'order_id');
359			$this->result['order_id'] = $elementResult['order_id'];
360
361			// Add extra fields
362			$this->result[$groupField] = $elementId; // Grouped field ID
363			if ($labelField && isset($elementResult[$labelField]) && !empty($elementResult[$labelField])) {
364			$this->result[$labelField] = $elementResult[$labelField]; // Grouped field label if given
365			} elseif ($labelField && (!isset($elementResult[$labelField]) \|\| empty($elementResult[$labelField]))) {
366			$this->result[$labelField] = '';
367			}
368			if (!empty($extraFields)) {
369			foreach ($extraFields as $extraField) {
370			if (isset($elementResult[$extraField])) {
371			$this->result[$extraField] = $elementResult[$extraField];
372			} else {
373			$this->result[$extraField] = '';
374			}
375			}
376			}
377
378			// Update results for this element
379			$this->results[$elementId] = $this->result;
380			$this->elementsArray[$elementId] = $this->elements;
381			}
382
383			// Aggregate number of order per element
384			foreach ($this->results as $key => $value) {
385			$this->results[$key]['number_of_elements'] = count($this->elementsArray[$key]);
386			}
387			}
388
389			/**
390			* Make the average of results depending on number of elements
391			*/
392			protected function averageResult(): void
393			{
394			if (!empty($this->elements)) {
395			$numberOfElements = count($this->elements);
396			foreach ($this->result as $key => $val) {
397			$this->result[$key] = round($this->result[$key] / $numberOfElements);
398			}
399			$this->result['number_of_elements'] = count($this->elements);
400			} else {
401			$this->result['number_of_elements'] = 0;
402			}
403			}
404
405			/**
406			* @return QueryBuilder
407			*/
408			protected function createOrderQuery()
409			{
410			/** @var EntityRepository $repository */
411			$repository = $this->orderRepository;
412			return $repository->createQueryBuilder('o');
413			}
414
415			/**
416			* @return QueryBuilder
417			*/
418			protected function createProductVariantQuery()
419			{
420			/** @var EntityRepository $repository */
421			$repository = $this->productVariantRepository;
422			return $repository->createQueryBuilder('v');
423			}
424			}
425

monsieurbiz / SyliusSalesReportsPlugin

AbstractReportRepository::getVariantsOptions() A last analyzed 2020-10-01 15:01 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

AbstractReportRepository::getVariantsOptions() A
last analyzed 2020-10-01 15:01 UTC