Completed
Push — master ( a18731...e2e070 )
by
unknown
99:54 queued 47:45
created

ForecastProvider::getAuditRepository()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 4
Code Lines 2

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 4
rs 10
c 0
b 0
f 0
cc 1
eloc 2
nc 1
nop 0
1
<?php
2
3
namespace OroCRM\Bundle\SalesBundle\Provider\Opportunity;
4
5
use Symfony\Bridge\Doctrine\RegistryInterface;
6
7
use Doctrine\ORM\EntityRepository;
8
use Doctrine\ORM\Query\Expr\Composite;
9
use Doctrine\ORM\Query\Expr\Join;
10
use Doctrine\ORM\QueryBuilder;
11
12
use Oro\Bundle\QueryDesignerBundle\QueryDesigner\FilterProcessor;
13
use Oro\Bundle\EntityExtendBundle\Provider\EnumValueProvider;
14
use Oro\Bundle\SecurityBundle\ORM\Walker\AclHelper;
15
use Oro\Bundle\UserBundle\Entity\Repository\UserRepository;
16
use Oro\Component\DoctrineUtils\ORM\QueryUtils;
17
18
use OroCRM\Bundle\SalesBundle\Entity\Repository\OpportunityRepository;
19
20
class ForecastProvider
21
{
22
    /** @var RegistryInterface */
23
    protected $doctrine;
24
25
    /** @var AclHelper */
26
    protected $aclHelper;
27
28
    /** @var EnumValueProvider */
29
    protected $enumProvider;
30
31
    /** @var  array */
32
    protected $data;
33
34
    /** @var  array */
35
    protected $statuses;
36
37
    /** @var FilterProcessor */
38
    protected $filterProcessor;
39
40
    /** @var array */
41
    protected static $fieldsAuditMap = [
42
        'status'       => ['old' => 'oldText', 'new' => 'newText'],
43
        'owner'        => ['old' => 'oldText', 'new' => 'newText'],
44
        'closeDate'    => ['old' => 'oldDatetime', 'new' => 'newDatetime'],
45
        'probability'  => ['old' => 'oldFloat', 'new' => 'newFloat'],
46
        'budgetAmount' => ['old' => 'oldFloat', 'new' => 'newFloat'],
47
    ];
48
49
    /**
50
     * @param RegistryInterface $doctrine
51
     * @param AclHelper         $aclHelper
52
     * @param EnumValueProvider $enumProvider
53
     * @param FilterProcessor   $filterProcessor
54
     */
55
    public function __construct(
56
        RegistryInterface $doctrine,
57
        AclHelper $aclHelper,
58
        EnumValueProvider $enumProvider,
59
        FilterProcessor $filterProcessor
60
    ) {
61
        $this->doctrine        = $doctrine;
62
        $this->aclHelper       = $aclHelper;
63
        $this->enumProvider    = $enumProvider;
64
        $this->filterProcessor = $filterProcessor;
65
    }
66
67
    /**
68
     * @param array          $ownerIds
69
     * @param \DateTime|null $start
70
     * @param \DateTime|null $end
71
     * @param \DateTime|null $moment
72
     * @param array|null     $queryFilter
73
     *
74
     * @return array ['inProgressCount' => <int>, 'budgetAmount' => <double>, 'weightedForecast' => <double>]
75
     */
76
    public function getForecastData(
77
        array $ownerIds,
78
        \DateTime $start = null,
79
        \DateTime $end = null,
80
        \DateTime $moment = null,
81
        array $queryFilter = null
82
    ) {
83
        $filters = isset($queryFilter['definition']['filters'])
84
            ? $queryFilter['definition']['filters']
85
            : [];
86
        $key     = $this->getDataHashKey($ownerIds, $start, $end, $moment, $filters);
87
        if (!isset($this->data[$key])) {
88
            if (!$moment) {
89
                $this->data[$key] = $this->getCurrentData($ownerIds, $start, $end, $filters);
90
            } else {
91
                $this->data[$key] = $this->getMomentData($ownerIds, $moment, $start, $end, $filters);
92
            }
93
        }
94
95
        return $this->data[$key];
96
    }
97
98
    /**
99
     * @param array     $ownerIds
100
     * @param \DateTime $start
101
     * @param \DateTime $end
102
     * @param array     $filters
103
     *
104
     * @return array
105
     */
106
    protected function getCurrentData(
107
        array $ownerIds,
108
        \DateTime $start = null,
109
        \DateTime $end = null,
110
        array $filters = []
111
    ) {
112
        $clonedStart = $start ? clone $start : null;
113
        $clonedEnd   = $end ? clone $end : null;
114
        $alias       = 'o';
115
        $qb          = $this->getOpportunityRepository()->getForecastQB($alias);
116
117
        $qb = $this->filterProcessor
118
            ->process($qb, 'OroCRM\Bundle\SalesBundle\Entity\Opportunity', $filters, $alias);
119
120
        if (!empty($ownerIds)) {
121
            $qb->join('o.owner', 'owner');
122
            QueryUtils::applyOptimizedIn($qb, 'owner.id', $ownerIds);
123
        }
124
        $this->applyDateFiltering($qb, 'o.closeDate', $clonedStart, $clonedEnd);
125
        $this->applyProbabilityCondition($qb, 'o');
126
127
        return $this->aclHelper->apply($qb)->getOneOrNullResult();
128
    }
129
130
    /**
131
     * @param array          $ownerIds
132
     * @param \DateTime      $moment
133
     * @param \DateTime|null $start
134
     * @param \DateTime|null $end
135
     * @param array          $filters
136
     *
137
     * @return array
138
     */
139
    protected function getMomentData(
140
        array $ownerIds,
141
        \DateTime $moment,
142
        \DateTime $start = null,
143
        \DateTime $end = null,
144
        array $filters = []
145
    ) {
146
        // clone datetimes as doctrine modifies their timezone which breaks stuff
147
        $moment = clone $moment;
148
        $start = $start ? clone $start : null;
149
        $end = $end ? clone $end : null;
150
151
        $qb = $this->getAuditRepository()->createQueryBuilder('a');
152
        $qb
153
            ->select(<<<SELECT
154
(SELECT afps.newFloat FROM OroDataAuditBundle:AuditField afps WHERE afps.id = MAX(afp.id)) AS probability,
155
(SELECT afpb.newFloat FROM OroDataAuditBundle:AuditField afpb WHERE afpb.id = MAX(afb.id)) AS budgetAmount
156
SELECT
157
            )
158
            ->join('a.fields', 'afs', Join::WITH, 'afs.field = :statusField')
159
            ->join('a.fields', 'afc', Join::WITH, 'afc.field = :closeDateField')
160
            ->join('a.fields', 'afp', Join::WITH, 'afp.field = :probabilityField')
161
            ->join('a.fields', 'afb', Join::WITH, 'afb.field = :budgetAmountField')
162
            ->where('a.objectClass = :objectClass AND a.loggedAt < :moment')
163
            ->groupBy('a.objectId')
164
            ->having(<<<HAVING
165
EXISTS(
166
    SELECT
167
        afsh.newText
168
    FROM OroDataAuditBundle:AuditField afsh
169
    WHERE
170
        afsh.id = MAX(afs.id)
171
        AND afsh.newText NOT IN (:excludedStatuses)
172
)
173
AND EXISTS(
174
    SELECT
175
        afph.newFloat
176
    FROM OroDataAuditBundle:AuditField afph
177
    WHERE
178
        afph.id = MAX(afp.id)
179
        AND (afph.newFloat NOT IN (:excludedProbabilities) or afph.newFloat IS NULL)
180
)
181
HAVING
182
            )
183
            ->setParameters([
184
                'objectClass' => 'OroCRM\Bundle\SalesBundle\Entity\Opportunity',
185
                'statusField' => 'status',
186
                'closeDateField' => 'closeDate',
187
                'probabilityField' => 'probability',
188
                'budgetAmountField' => 'budgetAmount',
189
                'excludedProbabilities' => [0, 1],
190
                'excludedStatuses' => [
191
                    $this->getStatusTextValue('lost'),
192
                    $this->getStatusTextValue('won'),
193
                ],
194
                'moment' => $moment,
195
            ]);
196
197
        $this->applyHistoryDateFiltering($qb, $start, $end);
198
199
        if ($ownerIds) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $ownerIds of type array is implicitly converted to a boolean; are you sure this is intended? If so, consider using ! empty($expr) instead to make it clear that you intend to check for an array without elements.

This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.

Consider making the comparison explicit by using empty(..) or ! empty(...) instead.

Loading history...
200
            $qb
201
                ->join('a.fields', 'afo', Join::WITH, 'afo.field = :ownerField')
202
                ->andHaving(<<<HAVING
203
EXISTS(
204
    SELECT
205
        afoh.newText
206
    FROM OroDataAuditBundle:AuditField afoh
207
    WHERE
208
        afoh.id = MAX(afo.id)
209
        AND afoh.newText IN (SELECT u.username FROM OroUserBundle:User u WHERE u.id IN (:ownerIds))
210
)
211
HAVING
212
                )
213
                ->setParameter('ownerField', 'owner')
214
                ->setParameter('ownerIds', $ownerIds);
215
        }
216
217
        if ($filters) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $filters of type array is implicitly converted to a boolean; are you sure this is intended? If so, consider using ! empty($expr) instead to make it clear that you intend to check for an array without elements.

This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent.

Consider making the comparison explicit by using empty(..) or ! empty(...) instead.

Loading history...
218
            $qb
219
                ->join('OroCRMSalesBundle:Opportunity', 'o', Join::WITH, 'a.objectId = o.id');
220
            $this->filterProcessor
221
                ->process($qb, 'OroCRM\Bundle\SalesBundle\Entity\Opportunity', $filters, 'o');
222
        }
223
224
        $result = $qb->getQuery()->getArrayResult();
225
226
        return array_reduce(
227
            $result,
228
            function ($result, $row) {
229
                $result['inProgressCount']++;
230
                $result['budgetAmount'] += $row['budgetAmount'];
231
                $result['weightedForecast'] += $row['budgetAmount'] * $row['probability'];
232
233
                return $result;
234
            },
235
            ['inProgressCount' => 0, 'budgetAmount' => 0, 'weightedForecast' => 0]
236
        );
237
    }
238
239
    /**
240
     * @param QueryBuilder $qb
241
     * @param \DateTime $start
242
     * @param \DateTime $end
243
     */
244
    protected function applyHistoryDateFiltering(QueryBuilder $qb, \DateTime $start = null, \DateTime $end = null)
245
    {
246
        if (!$start && !$end) {
247
            return;
248
        }
249
250
        $closeDateFieldQb = $this->getAuditFieldRepository()->createQueryBuilder('afch')
251
            ->select('afch.newDate')
252
            ->where('afch.id = MAX(afc.id)');
253
        $this->applyDateFiltering($closeDateFieldQb, 'afch.newDate', $start, $end);
254
255
        $qb->andHaving($qb->expr()->exists($closeDateFieldQb->getDQL()));
256
        foreach ($closeDateFieldQb->getParameters() as $parameter) {
257
            $qb->setParameter(
258
                $parameter->getName(),
259
                $parameter->getValue(),
260
                $parameter->getType()
261
            );
262
        }
263
    }
264
265
    /**
266
     * @param QueryBuilder   $qb
267
     * @param string         $field
268
     * @param \DateTime|null $start
269
     * @param \DateTime|null $end
270
     */
271 View Code Duplication
    protected function applyDateFiltering(
0 ignored issues
show
Duplication introduced by
This method seems to be duplicated in your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
272
        QueryBuilder $qb,
273
        $field,
274
        \DateTime $start = null,
275
        \DateTime $end = null
276
    ) {
277
        if ($start) {
278
            $qb
279
                ->andWhere(sprintf('%s >= :start', $field))
280
                ->setParameter('start', $start);
281
        }
282
        if ($end) {
283
            $qb
284
                ->andWhere(sprintf('%s < :end', $field))
285
                ->setParameter('end', $end);
286
        }
287
    }
288
289
    /**
290
     * @param QueryBuilder $qb
291
     * @param string       $alias
292
     */
293
    protected function applyProbabilityCondition(QueryBuilder $qb, $alias)
294
    {
295
        $qb->andWhere(
296
            $qb->expr()->orX(
297
                $qb->expr()->andX(
298
                    sprintf('%s.probability <> 0', $alias),
299
                    sprintf('%s.probability <> 1', $alias)
300
                ),
301
                sprintf('%s.probability is NULL', $alias)
302
            )
303
        );
304
    }
305
306
    /**
307
     * @return OpportunityRepository
308
     */
309
    protected function getOpportunityRepository()
310
    {
311
        return $this->doctrine->getRepository('OroCRMSalesBundle:Opportunity');
312
    }
313
314
    /**
315
     * @return EntityRepository
316
     */
317
    protected function getAuditFieldRepository()
318
    {
319
        return $this->doctrine->getRepository('OroDataAuditBundle:AuditField');
320
    }
321
322
    /**
323
     * @return EntityRepository
324
     */
325
    protected function getAuditRepository()
326
    {
327
        return $this->doctrine->getRepository('OroDataAuditBundle:Audit');
328
    }
329
330
    /**
331
     * @return UserRepository
332
     */
333
    protected function getUserRepository()
334
    {
335
        return $this->doctrine->getRepository('OroUserBundle:User');
336
    }
337
338
    /**
339
     * @param $key
340
     *
341
     * @return mixed
342
     */
343
    protected function getStatusTextValue($key)
344
    {
345
        if (null === $this->statuses) {
346
            $this->statuses = $this->enumProvider->getEnumChoicesByCode('opportunity_status');
347
        }
348
349
        return $this->statuses[$key];
350
    }
351
352
    /**
353
     * @param array          $ownerIds
354
     * @param \DateTime|null $start
355
     * @param \DateTime|null $end
356
     * @param \DateTime|null $moment
357
     * @param array          $filters
358
     *
359
     * @return string
360
     */
361
    protected function getDataHashKey(
362
        array $ownerIds,
363
        \DateTime $start = null,
364
        \DateTime $end = null,
365
        \DateTime $moment = null,
366
        array $filters = []
367
    ) {
368
        return md5(
369
            serialize(
370
                [
371
                    'ownerIds' => $ownerIds,
372
                    'start'    => $start,
373
                    'end'      => $end,
374
                    'moment'   => $moment,
375
                    'filters'  => $filters
376
                ]
377
            )
378
        );
379
    }
380
}
381