Completed
Push — 1.10 ( 3f8f95...f007bc )
by
unknown
09:16
created

ForecastProvider::getCurrentData()   B

Complexity

Conditions 4
Paths 8

Size

Total Lines 22
Code Lines 16

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 22
rs 8.9197
c 0
b 0
f 0
cc 4
eloc 16
nc 8
nop 4
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\Join;
9
use Doctrine\ORM\QueryBuilder;
10
11
use Oro\Bundle\QueryDesignerBundle\QueryDesigner\FilterProcessor;
12
use Oro\Bundle\EntityExtendBundle\Provider\EnumValueProvider;
13
use Oro\Bundle\SecurityBundle\ORM\Walker\AclHelper;
14
use Oro\Bundle\UserBundle\Entity\Repository\UserRepository;
15
use Oro\Component\DoctrineUtils\ORM\QueryUtils;
16
17
use OroCRM\Bundle\SalesBundle\Entity\Repository\OpportunityRepository;
18
19
class ForecastProvider
20
{
21
    /** @var RegistryInterface */
22
    protected $doctrine;
23
24
    /** @var AclHelper */
25
    protected $aclHelper;
26
27
    /** @var EnumValueProvider */
28
    protected $enumProvider;
29
30
    /** @var  array */
31
    protected $data;
32
33
    /** @var  array */
34
    protected $statuses;
35
36
    /** @var FilterProcessor */
37
    protected $filterProcessor;
38
39
    /** @var array */
40
    protected static $fieldsAuditMap = [
41
        'status'       => ['old' => 'oldText', 'new' => 'newText'],
42
        'owner'        => ['old' => 'oldText', 'new' => 'newText'],
43
        'closeDate'    => ['old' => 'oldDatetime', 'new' => 'newDatetime'],
44
        'probability'  => ['old' => 'oldFloat', 'new' => 'newFloat'],
45
        'budgetAmount' => ['old' => 'oldFloat', 'new' => 'newFloat'],
46
    ];
47
48
    /**
49
     * @param RegistryInterface $doctrine
50
     * @param AclHelper         $aclHelper
51
     * @param EnumValueProvider $enumProvider
52
     * @param FilterProcessor   $filterProcessor
53
     */
54
    public function __construct(
55
        RegistryInterface $doctrine,
56
        AclHelper $aclHelper,
57
        EnumValueProvider $enumProvider,
58
        FilterProcessor $filterProcessor
59
    ) {
60
        $this->doctrine        = $doctrine;
61
        $this->aclHelper       = $aclHelper;
62
        $this->enumProvider    = $enumProvider;
63
        $this->filterProcessor = $filterProcessor;
64
    }
65
66
    /**
67
     * @param array          $ownerIds
68
     * @param \DateTime|null $start
69
     * @param \DateTime|null $end
70
     * @param \DateTime|null $moment
71
     * @param array|null     $queryFilter
72
     *
73
     * @return array ['inProgressCount' => <int>, 'budgetAmount' => <double>, 'weightedForecast' => <double>]
74
     */
75
    public function getForecastData(
76
        array $ownerIds,
77
        \DateTime $start = null,
78
        \DateTime $end = null,
79
        \DateTime $moment = null,
80
        array $queryFilter = null
81
    ) {
82
        $filters = isset($queryFilter['definition']['filters'])
83
            ? $queryFilter['definition']['filters']
84
            : [];
85
        $key     = $this->getDataHashKey($ownerIds, $start, $end, $moment, $filters);
86
        if (!isset($this->data[$key])) {
87
            if (!$moment) {
88
                $this->data[$key] = $this->getCurrentData($ownerIds, $start, $end, $filters);
89
            } else {
90
                $this->data[$key] = $this->getMomentData($ownerIds, $moment, $start, $end, $filters);
91
            }
92
        }
93
94
        return $this->data[$key];
95
    }
96
97
    /**
98
     * @param array     $ownerIds
99
     * @param \DateTime $start
100
     * @param \DateTime $end
101
     * @param array     $filters
102
     *
103
     * @return array
104
     */
105
    protected function getCurrentData(
106
        array $ownerIds,
107
        \DateTime $start = null,
108
        \DateTime $end = null,
109
        array $filters = []
110
    ) {
111
        $clonedStart = $start ? clone $start : null;
112
        $clonedEnd   = $end ? clone $end : null;
113
        $alias       = 'o';
114
        $qb          = $this->getOpportunityRepository()->getForecastQB($alias);
115
116
        $qb = $this->filterProcessor
117
            ->process($qb, 'OroCRM\Bundle\SalesBundle\Entity\Opportunity', $filters, $alias);
118
119
        if (!empty($ownerIds)) {
120
            $qb->join('o.owner', 'owner');
121
            QueryUtils::applyOptimizedIn($qb, 'owner.id', $ownerIds);
122
        }
123
        $this->applyDateFiltering($qb, 'o.closeDate', $clonedStart, $clonedEnd);
124
125
        return $this->aclHelper->apply($qb)->getOneOrNullResult();
126
    }
127
128
    /**
129
     * @param array          $ownerIds
130
     * @param \DateTime      $moment
131
     * @param \DateTime|null $start
132
     * @param \DateTime|null $end
133
     * @param array          $filters
134
     *
135
     * @return array
136
     */
137
    protected function getMomentData(
138
        array $ownerIds,
139
        \DateTime $moment,
140
        \DateTime $start = null,
141
        \DateTime $end = null,
142
        array $filters = []
143
    ) {
144
        // clone datetimes as doctrine modifies their timezone which breaks stuff
145
        $moment = clone $moment;
146
        $start  = $start ? clone $start : null;
147
        $end    = $end ? clone $end : null;
148
149
        $qb = $this->getAuditRepository()->createQueryBuilder('a');
150
        $qb
151
            ->select(<<<SELECT
152
(SELECT afps.newFloat FROM OroDataAuditBundle:AuditField afps WHERE afps.id = MAX(afp.id)) AS probability,
153
(SELECT afpb.newFloat FROM OroDataAuditBundle:AuditField afpb WHERE afpb.id = MAX(afb.id)) AS budgetAmount
154
SELECT
155
            )
156
            ->leftJoin('a.fields', 'afca', Join::WITH, 'afca.field = :closedAtField')
157
            ->leftJoin('a.fields', 'afc', Join::WITH, 'afc.field = :closeDateField')
158
            ->leftJoin('a.fields', 'afp', Join::WITH, 'afp.field = :probabilityField')
159
            ->leftJoin('a.fields', 'afb', Join::WITH, 'afb.field = :budgetAmountField')
160
            ->where('a.objectClass = :objectClass AND a.loggedAt < :moment')
161
            ->groupBy('a.objectId')
162
            ->having(<<<HAVING
163
NOT EXISTS(
164
    SELECT
165
        afcah.newDatetime
166
    FROM OroDataAuditBundle:AuditField afcah
167
    WHERE
168
        afcah.id = MAX(afca.id)
169
        AND afcah.newDatetime IS NOT NULL
170
)
171
AND EXISTS(
172
    SELECT
173
        afph.newFloat
174
    FROM OroDataAuditBundle:AuditField afph
175
    WHERE
176
        afph.id = MAX(afp.id)
177
)
178
HAVING
179
            )
180
            ->setParameters([
181
                'objectClass'           => 'OroCRM\Bundle\SalesBundle\Entity\Opportunity',
182
                'closedAtField'         => 'closedAt',
183
                'closeDateField'        => 'closeDate',
184
                'probabilityField'      => 'probability',
185
                'budgetAmountField'     => 'budgetAmount',
186
                'moment'                => $moment,
187
            ]);
188
189
        $this->applyHistoryDateFiltering($qb, $start, $end);
190
191
        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...
192
            $qb
193
                ->join('a.fields', 'afo', Join::WITH, 'afo.field = :ownerField')
194
                ->andHaving(<<<HAVING
195
EXISTS(
196
    SELECT
197
        afoh.newText
198
    FROM OroDataAuditBundle:AuditField afoh
199
    WHERE
200
        afoh.id = MAX(afo.id)
201
        AND afoh.newText IN (SELECT u.username FROM OroUserBundle:User u WHERE u.id IN (:ownerIds))
202
)
203
HAVING
204
                )
205
                ->setParameter('ownerField', 'owner')
206
                ->setParameter('ownerIds', $ownerIds);
207
        }
208
        // need to join opportunity to properly apply acl permissions
209
        $qb->join('OroCRMSalesBundle:Opportunity', 'o', Join::WITH, 'a.objectId = o.id');
210
        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...
211
            $this->filterProcessor
212
                ->process($qb, 'OroCRM\Bundle\SalesBundle\Entity\Opportunity', $filters, 'o');
213
        }
214
215
        $result = $this->aclHelper->apply($qb)->getArrayResult();
216
217
        return array_reduce(
218
            $result,
219
            function ($result, $row) {
220
                $result['inProgressCount']++;
221
                $result['budgetAmount'] += $row['budgetAmount'];
222
                $result['weightedForecast'] += $row['budgetAmount'] * $row['probability'];
223
224
                return $result;
225
            },
226
            ['inProgressCount' => 0, 'budgetAmount' => 0, 'weightedForecast' => 0]
227
        );
228
    }
229
230
    /**
231
     * @param QueryBuilder $qb
232
     * @param \DateTime    $start
233
     * @param \DateTime    $end
234
     */
235
    protected function applyHistoryDateFiltering(QueryBuilder $qb, \DateTime $start = null, \DateTime $end = null)
236
    {
237
        if (!$start && !$end) {
238
            return;
239
        }
240
241
        $closeDateFieldQb = $this->getAuditFieldRepository()->createQueryBuilder('afch')
242
            ->select('afch.newDate')
243
            ->where('afch.id = MAX(afc.id)');
244
        $this->applyDateFiltering($closeDateFieldQb, 'afch.newDate', $start, $end);
245
246
        $qb->andHaving($qb->expr()->exists($closeDateFieldQb->getDQL()));
247
        foreach ($closeDateFieldQb->getParameters() as $parameter) {
248
            $qb->setParameter(
249
                $parameter->getName(),
250
                $parameter->getValue(),
251
                $parameter->getType()
252
            );
253
        }
254
    }
255
256
    /**
257
     * @param QueryBuilder   $qb
258
     * @param string         $field
259
     * @param \DateTime|null $start
260
     * @param \DateTime|null $end
261
     */
262 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...
263
        QueryBuilder $qb,
264
        $field,
265
        \DateTime $start = null,
266
        \DateTime $end = null
267
    ) {
268
        if ($start) {
269
            $qb
270
                ->andWhere(sprintf('%s >= :start', $field))
271
                ->setParameter('start', $start);
272
        }
273
        if ($end) {
274
            $qb
275
                ->andWhere(sprintf('%s <= :end', $field))
276
                ->setParameter('end', $end);
277
        }
278
    }
279
280
    /**
281
     * @return OpportunityRepository
282
     */
283
    protected function getOpportunityRepository()
284
    {
285
        return $this->doctrine->getRepository('OroCRMSalesBundle:Opportunity');
286
    }
287
288
    /**
289
     * @return EntityRepository
290
     */
291
    protected function getAuditFieldRepository()
292
    {
293
        return $this->doctrine->getRepository('OroDataAuditBundle:AuditField');
294
    }
295
296
    /**
297
     * @return EntityRepository
298
     */
299
    protected function getAuditRepository()
300
    {
301
        return $this->doctrine->getRepository('OroDataAuditBundle:Audit');
302
    }
303
304
    /**
305
     * @return UserRepository
306
     */
307
    protected function getUserRepository()
308
    {
309
        return $this->doctrine->getRepository('OroUserBundle:User');
310
    }
311
312
    /**
313
     * @param $key
314
     *
315
     * @return mixed
316
     */
317
    protected function getStatusTextValue($key)
318
    {
319
        if (null === $this->statuses) {
320
            $this->statuses = $this->enumProvider->getEnumChoicesByCode('opportunity_status');
321
        }
322
323
        return $this->statuses[$key];
324
    }
325
326
    /**
327
     * @param array          $ownerIds
328
     * @param \DateTime|null $start
329
     * @param \DateTime|null $end
330
     * @param \DateTime|null $moment
331
     * @param array          $filters
332
     *
333
     * @return string
334
     */
335
    protected function getDataHashKey(
336
        array $ownerIds,
337
        \DateTime $start = null,
338
        \DateTime $end = null,
339
        \DateTime $moment = null,
340
        array $filters = []
341
    ) {
342
        return md5(
343
            serialize(
344
                [
345
                    'ownerIds' => $ownerIds,
346
                    'start'    => $start,
347
                    'end'      => $end,
348
                    'moment'   => $moment,
349
                    'filters'  => $filters
350
                ]
351
            )
352
        );
353
    }
354
}
355