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

OpportunityRepository::getForecastQB()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 15
Code Lines 9

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 15
rs 9.4285
c 0
b 0
f 0
cc 2
eloc 9
nc 2
nop 2
1
<?php
2
3
namespace OroCRM\Bundle\SalesBundle\Entity\Repository;
4
5
use Doctrine\ORM\EntityRepository;
6
use Doctrine\ORM\QueryBuilder;
7
8
use Oro\Bundle\DashboardBundle\Filter\DateFilterProcessor;
9
use Oro\Bundle\DataAuditBundle\Loggable\LoggableManager;
10
use Oro\Bundle\SecurityBundle\ORM\Walker\AclHelper;
11
use Oro\Bundle\EntityExtendBundle\Tools\ExtendHelper;
12
use Oro\Bundle\WorkflowBundle\Entity\WorkflowStep;
13
use Oro\Component\DoctrineUtils\ORM\QueryUtils;
14
15
use OroCRM\Bundle\SalesBundle\Entity\Opportunity;
16
17
/**
18
 * @SuppressWarnings(PHPMD.ExcessiveClassComplexity)
19
 */
20
class OpportunityRepository extends EntityRepository
21
{
22
    const OPPORTUNITY_STATE_IN_PROGRESS      = 'In Progress';
23
    const OPPORTUNITY_STATE_IN_PROGRESS_CODE = 'in_progress';
24
    const OPPORTUNITY_STATUS_CLOSED_WON_CODE  = 'won';
25
26
    /**
27
     * @var WorkflowStep[]
28
     */
29
    protected $workflowStepsByName;
30
31
    /**
32
     * Get opportunities by state by current quarter
33
     *
34
     * @param           $aclHelper AclHelper
35
     * @param  array    $dateRange
36
     * @param  array    $states
37
     * @param int[]     $owners
38
     *
39
     * @param AclHelper $aclHelper
40
     * @param array     $dateRange
41
     * @param array     $states
42
     * @param int[]     $owners
43
     *
44
     * @return array
45
     */
46
    public function getOpportunitiesByStatus(AclHelper $aclHelper, $dateRange, $states, $owners = [])
47
    {
48
        $dateEnd   = $dateRange['end'];
49
        $dateStart = $dateRange['start'];
50
51
        return $this->getOpportunitiesDataByStatus($aclHelper, $dateStart, $dateEnd, $states, $owners);
52
    }
53
54
    /**
55
     * @param string $alias
56
     * @param string $orderBy
57
     * @param string $direction
58
     *
59
     * @return QueryBuilder
60
     *
61
     */
62
    public function getGroupedOpportunitiesByStatusQB(
63
        $alias,
64
        $orderBy = 'budget',
65
        $direction = 'DESC'
66
    ) {
67
        $statusClass = ExtendHelper::buildEnumValueClassName('opportunity_status');
68
        $repository  = $this->getEntityManager()->getRepository($statusClass);
69
70
        $qb = $repository->createQueryBuilder('s')
71
            ->select(
72
                's.name as label',
73
                sprintf('COUNT(%s.id) as quantity', $alias),
74
                // Use close revenue for calculating budget for opportunities with won statuses
75
                sprintf(
76
                    "SUM(
77
                        CASE WHEN s.id = 'won'
78
                            THEN
79
                                (CASE WHEN %s.closeRevenue IS NOT NULL THEN %s.closeRevenue ELSE 0 END)
80
                            ELSE
81
                                (CASE WHEN %s.budgetAmount IS NOT NULL THEN %s.budgetAmount ELSE 0 END)
82
                        END
83
                    ) as budget",
84
                    $alias,
85
                    $alias,
86
                    $alias,
87
                    $alias
88
                )
89
            )
90
            ->leftJoin('OroCRMSalesBundle:Opportunity', $alias, 'WITH', sprintf('%s.status = s', $alias))
91
            ->groupBy('s.name')
92
            ->orderBy($orderBy, $direction);
93
94
        return $qb;
95
    }
96
97
    /**
98
     * @param  AclHelper $aclHelper
99
     * @param            $dateStart
100
     * @param            $dateEnd
101
     * @param array      $states
102
     * @param int[]      $owners
103
     *
104
     * @return array
105
     */
106
    protected function getOpportunitiesDataByStatus(
107
        AclHelper $aclHelper,
108
        $dateStart = null,
109
        $dateEnd = null,
110
        $states = [],
111
        $owners = []
112
    ) {
113
        foreach ($states as $key => $name) {
114
            $resultData[$key] = [
0 ignored issues
show
Coding Style Comprehensibility introduced by
$resultData was never initialized. Although not strictly required by PHP, it is generally a good practice to add $resultData = array(); before regardless.

Adding an explicit array definition is generally preferable to implicit array definition as it guarantees a stable state of the code.

Let’s take a look at an example:

foreach ($collection as $item) {
    $myArray['foo'] = $item->getFoo();

    if ($item->hasBar()) {
        $myArray['bar'] = $item->getBar();
    }

    // do something with $myArray
}

As you can see in this example, the array $myArray is initialized the first time when the foreach loop is entered. You can also see that the value of the bar key is only written conditionally; thus, its value might result from a previous iteration.

This might or might not be intended. To make your intention clear, your code more readible and to avoid accidental bugs, we recommend to add an explicit initialization $myArray = array() either outside or inside the foreach loop.

Loading history...
115
                'name'   => $key,
116
                'label'  => $name,
117
                'budget' => 0,
118
            ];
119
        }
120
121
        // select opportunity data
122
        $qb = $this->createQueryBuilder('opportunity');
123
        $qb->select('IDENTITY(opportunity.status) as name, SUM(opportunity.budgetAmount) as budget')
124
            ->groupBy('opportunity.status');
125
126 View Code Duplication
        if ($dateStart && $dateEnd) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across 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...
127
            $qb->where($qb->expr()->between('opportunity.createdAt', ':dateFrom', ':dateTo'))
128
                ->setParameter('dateFrom', $dateStart)
129
                ->setParameter('dateTo', $dateEnd);
130
        }
131
132
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
133
            QueryUtils::applyOptimizedIn($qb, 'opportunity.owner', $owners);
134
        }
135
136
        $groupedData = $aclHelper->apply($qb)->getArrayResult();
137
138
        foreach ($groupedData as $statusData) {
139
            $status = $statusData['name'];
140
            $budget = (float)$statusData['budget'];
141
            if ($budget) {
142
                $resultData[$status]['budget'] = $budget;
0 ignored issues
show
Bug introduced by
The variable $resultData does not seem to be defined for all execution paths leading up to this point.

If you define a variable conditionally, it can happen that it is not defined for all execution paths.

Let’s take a look at an example:

function myFunction($a) {
    switch ($a) {
        case 'foo':
            $x = 1;
            break;

        case 'bar':
            $x = 2;
            break;
    }

    // $x is potentially undefined here.
    echo $x;
}

In the above example, the variable $x is defined if you pass “foo” or “bar” as argument for $a. However, since the switch statement has no default case statement, if you pass any other value, the variable $x would be undefined.

Available Fixes

  1. Check for existence of the variable explicitly:

    function myFunction($a) {
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
        }
    
        if (isset($x)) { // Make sure it's always set.
            echo $x;
        }
    }
    
  2. Define a default value for the variable:

    function myFunction($a) {
        $x = ''; // Set a default which gets overridden for certain paths.
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
        }
    
        echo $x;
    }
    
  3. Add a value for the missing path:

    function myFunction($a) {
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
    
            // We add support for the missing case.
            default:
                $x = '';
                break;
        }
    
        echo $x;
    }
    
Loading history...
143
            }
144
        }
145
146
        return $resultData;
147
    }
148
149
    /**
150
     * @param array       $ownerIds
151
     * @param \DateTime    $date
152
     * @param AclHelper   $aclHelper
153
     *
154
     * @param string|null $start
155
     * @param string|null $end
156
     *
157
     * @return mixed
158
     */
159
    public function getForecastOfOpportunitiesData($ownerIds, $date, AclHelper $aclHelper, $start = null, $end = null)
160
    {
161
        if ($date === null) {
162
            return $this->getForecastOfOpportunitiesCurrentData($ownerIds, $aclHelper, $start, $end);
163
        }
164
165
        return $this->getForecastOfOpportunitiesOldData($ownerIds, $date, $aclHelper);
166
    }
167
168
    /**
169
     * @param array       $ownerIds
170
     * @param AclHelper   $aclHelper
171
     * @param string|null $start
172
     * @param string|null $end
173
     *
174
     * @return mixed
175
     */
176
    protected function getForecastOfOpportunitiesCurrentData(
177
        $ownerIds,
178
        AclHelper $aclHelper,
179
        $start = null,
180
        $end = null
181
    ) {
182
        $qb = $this->createQueryBuilder('opportunity');
183
184
        $select = "
185
            COUNT( opportunity.id ) as inProgressCount,
186
            SUM( opportunity.budgetAmount ) as budgetAmount,
187
            SUM( opportunity.budgetAmount * opportunity.probability ) as weightedForecast";
188
        $qb
189
            ->select($select)
190
            ->andWhere('opportunity.status NOT IN (:notCountedStatuses)')
191
            ->setParameter('notCountedStatuses', ['lost', 'won']);
192
        if (!empty($ownerIds)) {
193
            $qb->join('opportunity.owner', 'owner');
194
            QueryUtils::applyOptimizedIn($qb, 'owner.id', $ownerIds);
195
        }
196
197
        if ($start) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $start of type string|null is loosely compared to true; this is ambiguous if the string can be empty. You might want to explicitly use !== null instead.

In PHP, under loose comparison (like ==, or !=, or switch conditions), values of different types might be equal.

For string values, the empty string '' is a special case, in particular the following results might be unexpected:

''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
Loading history...
198
            $qb
199
                ->andWhere('opportunity.closeDate >= :startDate')
200
                ->setParameter('startDate', $start);
201
        }
202
        if ($end) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $end of type string|null is loosely compared to true; this is ambiguous if the string can be empty. You might want to explicitly use !== null instead.

In PHP, under loose comparison (like ==, or !=, or switch conditions), values of different types might be equal.

For string values, the empty string '' is a special case, in particular the following results might be unexpected:

''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
Loading history...
203
            $qb
204
                ->andWhere('opportunity.closeDate <= :endDate')
205
                ->setParameter('endDate', $end);
206
        }
207
208
        return $aclHelper->apply($qb)->getOneOrNullResult();
209
    }
210
211
    /**
212
     * @param array     $ownerIds
213
     * @param \DateTime $date
214
     * @param AclHelper $aclHelper
215
     *
216
     * @return mixed
217
     */
218
    protected function getForecastOfOpportunitiesOldData($ownerIds, $date, AclHelper $aclHelper)
219
    {
220
        //clone date for avoiding wrong date on printing with current locale
221
        $newDate = clone $date;
222
        $qb      = $this->createQueryBuilder('opportunity')
223
            ->where('opportunity.createdAt < :date')
224
            ->setParameter('date', $newDate);
225
226
        $opportunities = $aclHelper->apply($qb)->getResult();
227
228
        $result['inProgressCount']  = 0;
0 ignored issues
show
Coding Style Comprehensibility introduced by
$result was never initialized. Although not strictly required by PHP, it is generally a good practice to add $result = array(); before regardless.

Adding an explicit array definition is generally preferable to implicit array definition as it guarantees a stable state of the code.

Let’s take a look at an example:

foreach ($collection as $item) {
    $myArray['foo'] = $item->getFoo();

    if ($item->hasBar()) {
        $myArray['bar'] = $item->getBar();
    }

    // do something with $myArray
}

As you can see in this example, the array $myArray is initialized the first time when the foreach loop is entered. You can also see that the value of the bar key is only written conditionally; thus, its value might result from a previous iteration.

This might or might not be intended. To make your intention clear, your code more readible and to avoid accidental bugs, we recommend to add an explicit initialization $myArray = array() either outside or inside the foreach loop.

Loading history...
229
        $result['budgetAmount']     = 0;
230
        $result['weightedForecast'] = 0;
231
232
        $auditRepository = $this->getEntityManager()->getRepository('OroDataAuditBundle:Audit');
233
        /** @var Opportunity $opportunity */
234
        foreach ($opportunities as $opportunity) {
235
            $auditQb = $auditRepository->getLogEntriesQueryBuilder($opportunity);
236
            $auditQb->andWhere('a.action = :action')
237
                ->andWhere('a.loggedAt > :date')
238
                ->setParameter('action', LoggableManager::ACTION_UPDATE)
239
                ->setParameter('date', $newDate);
240
            $opportunityHistory = $aclHelper->apply($auditQb)->getResult();
241
242
            if ($oldProbability = $this->getHistoryOldValue($opportunityHistory, 'probability')) {
243
                $probability     = $oldProbability;
244
            } else {
245
                $probability     = $opportunity->getProbability();
246
            }
247
248
            if ($this->isOwnerOk($ownerIds, $opportunityHistory, $opportunity)
249
                && $this->isStatusOk($opportunityHistory, $opportunity)
250
            ) {
251
                $result = $this->calculateOpportunityOldValue($result, $opportunityHistory, $opportunity, $probability);
252
            }
253
        }
254
255
        return $result;
256
    }
257
258
    /**
259
     * @param mixed  $opportunityHistory
260
     * @param string $field
261
     *
262
     * @return mixed
263
     */
264
    protected function getHistoryOldValue($opportunityHistory, $field)
265
    {
266
        $result = null;
267
268
        $opportunityHistory = is_array($opportunityHistory) ? $opportunityHistory : [$opportunityHistory];
269
        foreach ($opportunityHistory as $item) {
270
            if ($item->getField($field)) {
271
                $result = $item->getField($field)->getOldValue();
272
            }
273
        }
274
275
        return $result;
276
    }
277
278
    /**
279
     * @param array       $opportunityHistory
280
     * @param Opportunity $opportunity
281
     *
282
     * @return bool
283
     */
284
    protected function isStatusOk($opportunityHistory, $opportunity)
285
    {
286
        if ($oldStatus = $this->getHistoryOldValue($opportunityHistory, 'status')) {
287
            $isStatusOk = $oldStatus === self::OPPORTUNITY_STATE_IN_PROGRESS;
288
        } else {
289
            $isStatusOk = $opportunity->getStatus()->getName() === self::OPPORTUNITY_STATE_IN_PROGRESS_CODE;
290
        }
291
292
        return $isStatusOk;
293
    }
294
295
    /**
296
     * @param array       $ownerIds
297
     * @param array       $opportunityHistory
298
     * @param Opportunity $opportunity
299
     *
300
     * @return bool
301
     */
302
    protected function isOwnerOk($ownerIds, $opportunityHistory, $opportunity)
303
    {
304
        $userRepository = $this->getEntityManager()->getRepository('OroUserBundle:User');
305
        if ($oldOwner = $this->getHistoryOldValue($opportunityHistory, 'owner')) {
306
            $isOwnerOk = in_array($userRepository->findOneByUsername($oldOwner)->getId(), $ownerIds);
307
        } else {
308
            $isOwnerOk = in_array($opportunity->getOwner()->getId(), $ownerIds);
309
        }
310
311
        return $isOwnerOk;
312
    }
313
314
    /**
315
     * @param array       $result
316
     * @param array       $opportunityHistory
317
     * @param Opportunity $opportunity
318
     * @param mixed       $probability
319
     *
320
     * @return array
321
     */
322
    protected function calculateOpportunityOldValue($result, $opportunityHistory, $opportunity, $probability)
323
    {
324
        ++$result['inProgressCount'];
325
        $oldBudgetAmount = $this->getHistoryOldValue($opportunityHistory, 'budgetAmount');
326
327
        $budget = $oldBudgetAmount !== null ? $oldBudgetAmount : $opportunity->getBudgetAmount();
328
        $result['budgetAmount'] += $budget;
329
        $result['weightedForecast'] += $budget * $probability;
330
331
        return $result;
332
    }
333
334
    /**
335
     * @param AclHelper $aclHelper
336
     * @param \DateTime  $start
337
     * @param \DateTime  $end
338
     * @param int[]     $owners
339
     *
340
     * @return int
341
     */
342 View Code Duplication
    public function getOpportunitiesCount(
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...
343
        AclHelper $aclHelper,
344
        \DateTime $start = null,
345
        \DateTime $end = null,
346
        $owners = []
347
    ) {
348
        $qb = $this->createOpportunitiesCountQb($start, $end, $owners);
349
350
        return $aclHelper->apply($qb)->getSingleScalarResult();
351
    }
352
353
    /**
354
     * @param AclHelper $aclHelper
355
     * @param \DateTime  $start
356
     * @param \DateTime  $end
357
     * @param int[]     $owners
358
     *
359
     * @return int
360
     */
361 View Code Duplication
    public function getNewOpportunitiesCount(
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...
362
        AclHelper $aclHelper,
363
        \DateTime $start = null,
364
        \DateTime $end = null,
365
        $owners = []
366
    ) {
367
        $qb = $this->createOpportunitiesCountQb($start, $end, $owners);
368
369
        return $aclHelper->apply($qb)->getSingleScalarResult();
370
    }
371
372
    /**
373
     * @param \DateTime $start
374
     * @param \DateTime $end
375
     * @param int[]    $owners
376
     *
377
     * @return QueryBuilder
378
     */
379 View Code Duplication
    public function createOpportunitiesCountQb(\DateTime $start = null, \DateTime $end = null, $owners = [])
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...
380
    {
381
        $qb = $this->createQueryBuilder('o');
382
        $qb->select('COUNT(o.id)');
383
        if ($start) {
384
            $qb
385
                ->andWhere('o.createdAt >= :start')
386
                ->setParameter('start', $start);
387
        }
388
        if ($end) {
389
            $qb
390
                ->andWhere('o.createdAt <= :end')
391
                ->setParameter('end', $end);
392
        }
393
394
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
395
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
396
        }
397
398
        return $qb;
399
    }
400
401
    /**
402
     * @param AclHelper $aclHelper
403
     * @param \DateTime  $start
404
     * @param \DateTime  $end
405
     * @param int[]     $owners
406
     *
407
     * @return double
408
     */
409
    public function getTotalServicePipelineAmount(
410
        AclHelper $aclHelper,
411
        \DateTime $start = null,
412
        \DateTime $end = null,
413
        $owners = []
414
    ) {
415
        $qb = $this->createQueryBuilder('o');
416
417
        $qb
418
            ->select('SUM(o.budgetAmount)')
419
            ->andWhere('o.closeDate IS NULL')
420
            ->andWhere('o.status = :status')
421
            ->setParameter('status', self::OPPORTUNITY_STATE_IN_PROGRESS_CODE);
422
        if ($start) {
423
            $qb
424
                ->andWhere('o.createdAt > :start')
425
                ->setParameter('start', $start);
426
        }
427
        if ($end) {
428
            $qb
429
                ->andWhere('o.createdAt < :end')
430
                ->setParameter('end', $end);
431
        }
432
433
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
434
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
435
        }
436
437
        return $aclHelper->apply($qb)->getSingleScalarResult();
438
    }
439
440
    /**
441
     * @param AclHelper $aclHelper
442
     * @param \DateTime  $start
443
     * @param \DateTime  $end
444
     *
445
     * @return double
446
     */
447 View Code Duplication
    public function getTotalServicePipelineAmountInProgress(
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...
448
        AclHelper $aclHelper,
449
        \DateTime $start = null,
450
        \DateTime $end = null
451
    ) {
452
        $qb = $this->createQueryBuilder('o');
453
454
        $qb
455
            ->select('SUM(o.budgetAmount)')
456
            ->andWhere('o.status = :status')
457
            ->setParameter('status', self::OPPORTUNITY_STATE_IN_PROGRESS_CODE);
458
        if ($start) {
459
            $qb
460
                ->andWhere('o.createdAt > :start')
461
                ->setParameter('start', $start);
462
        }
463
        if ($end) {
464
            $qb
465
                ->andWhere('o.createdAt < :end')
466
                ->setParameter('end', $end);
467
        }
468
469
        return $aclHelper->apply($qb)->getSingleScalarResult();
470
    }
471
472
    /**
473
     * @param AclHelper $aclHelper
474
     * @param \DateTime  $start
475
     * @param \DateTime  $end
476
     *
477
     * @return double
478
     */
479 View Code Duplication
    public function getWeightedPipelineAmount(AclHelper $aclHelper, \DateTime $start = null, \DateTime $end = null)
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...
480
    {
481
        $qb = $this->createQueryBuilder('o');
482
483
        $qb->select('SUM(o.budgetAmount * o.probability)');
484
        if ($start) {
485
            $qb
486
                ->andWhere('o.createdAt > :start')
487
                ->setParameter('start', $start);
488
        }
489
        if ($end) {
490
            $qb
491
                ->andWhere('o.createdAt < :end')
492
                ->setParameter('end', $end);
493
        }
494
495
        return $aclHelper->apply($qb)->getSingleScalarResult();
496
    }
497
498
    /**
499
     * @param AclHelper $aclHelper
500
     * @param \DateTime  $start
501
     * @param \DateTime  $end
502
     * @param int[]     $owners
503
     *
504
     * @return double
505
     */
506 View Code Duplication
    public function getOpenWeightedPipelineAmount(
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...
507
        AclHelper $aclHelper,
508
        \DateTime $start = null,
509
        \DateTime $end = null,
510
        $owners = []
511
    ) {
512
        $qb = $this->createQueryBuilder('o');
513
514
        $qb
515
            ->select('SUM(o.budgetAmount * o.probability)')
516
            ->andWhere('o.status = :status')
517
            ->setParameter('status', self::OPPORTUNITY_STATE_IN_PROGRESS_CODE);
518
519
        $this->setCreationPeriod($qb, $start, $end);
520
521
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
522
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
523
        }
524
525
        return $aclHelper->apply($qb)->getSingleScalarResult();
526
    }
527
528
    /**
529
     * @param AclHelper $aclHelper
530
     * @param \DateTime  $start
531
     * @param \DateTime  $end
532
     * @param int[]     $owners
533
     *
534
     * @return double
535
     */
536
    public function getNewOpportunitiesAmount(
537
        AclHelper $aclHelper,
538
        \DateTime $start = null,
539
        \DateTime $end = null,
540
        $owners = []
541
    ) {
542
        $qb = $this
543
            ->createQueryBuilder('o')
544
            ->select('SUM(o.budgetAmount)');
545
546
        $this->setCreationPeriod($qb, $start, $end);
547
548
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
549
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
550
        }
551
552
        return $aclHelper->apply($qb)->getSingleScalarResult();
553
    }
554
555
    /**
556
     * @param AclHelper $aclHelper
557
     * @param \DateTime  $start
558
     * @param \DateTime  $end
559
     * @param int[]     $owners
560
     *
561
     * @return int
562
     */
563 View Code Duplication
    public function getWonOpportunitiesToDateCount(
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...
564
        AclHelper $aclHelper,
565
        \DateTime $start = null,
566
        \DateTime $end = null,
567
        $owners = []
568
    ) {
569
        $qb = $this->createQueryBuilder('o');
570
        $qb->select('COUNT(o.id)')
571
            ->andWhere('o.status = :status')
572
            ->setParameter('status', self::OPPORTUNITY_STATUS_CLOSED_WON_CODE);
573
574
        $this->setClosedPeriod($qb, $start, $end);
575
576
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
577
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
578
        }
579
580
        return $aclHelper->apply($qb)->getSingleScalarResult();
581
    }
582
583
    /**
584
     * @param AclHelper $aclHelper
585
     * @param \DateTime  $start
586
     * @param \DateTime  $end
587
     * @param int[]     $owners
588
     *
589
     * @return double
590
     */
591 View Code Duplication
    public function getWonOpportunitiesToDateAmount(
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...
592
        AclHelper $aclHelper,
593
        \DateTime $start = null,
594
        \DateTime $end = null,
595
        $owners = []
596
    ) {
597
        $qb = $this->createQueryBuilder('o');
598
        $qb->select('SUM(o.closeRevenue)')
599
            ->andWhere('o.status = :status')
600
            ->setParameter('status', self::OPPORTUNITY_STATUS_CLOSED_WON_CODE);
601
602
        $this->setClosedPeriod($qb, $start, $end);
603
604
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners of type integer[] 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...
605
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
606
        }
607
608
        return $aclHelper->apply($qb)->getSingleScalarResult();
609
    }
610
611
    /**
612
     * @param QueryBuilder $qb
613
     * @param \DateTime|null $start
614
     * @param \DateTime|null $end
615
     */
616 View Code Duplication
    protected function setCreationPeriod(QueryBuilder $qb, \DateTime $start = null, \DateTime $end = null)
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...
617
    {
618
        if ($start) {
619
            $qb->andWhere('o.createdAt >= :dateStart')->setParameter('dateStart', $start);
620
        }
621
622
        if ($end) {
623
            $qb->andWhere('o.createdAt <= :dateEnd')->setParameter('dateEnd', $end);
624
        }
625
    }
626
627
    /**
628
     * @param QueryBuilder $qb
629
     * @param \DateTime|null $start
630
     * @param \DateTime|null $end
631
     */
632 View Code Duplication
    protected function setClosedPeriod(QueryBuilder $qb, \DateTime $start = null, \DateTime $end = null)
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...
633
    {
634
        if ($start) {
635
            $qb->andWhere('o.closedAt >= :dateStart')->setParameter('dateStart', $start);
636
        }
637
638
        if ($end) {
639
            $qb->andWhere('o.closedAt <= :dateEnd')->setParameter('dateEnd', $end);
640
        }
641
    }
642
643
    /**
644
     * Returns count of opportunities grouped by lead source
645
     *
646
     * @param AclHelper $aclHelper
647
     * @param DateFilterProcessor $dateFilterProcessor
648
     * @param array $dateRange
649
     * @param array $owners
650
     *
651
     * @return array [value, source]
652
     */
653 View Code Duplication
    public function getOpportunitiesCountGroupByLeadSource(
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...
654
        AclHelper $aclHelper,
655
        DateFilterProcessor $dateFilterProcessor,
656
        array $dateRange = [],
657
        array $owners = []
658
    ) {
659
        $qb = $this->getOpportunitiesGroupByLeadSourceQueryBuilder($dateFilterProcessor, $dateRange, $owners);
660
        $qb->addSelect('count(o.id) as value');
661
662
        return $aclHelper->apply($qb)->getArrayResult();
663
    }
664
665
    /**
666
     * Returns budget amount of opportunities grouped by lead source
667
     *
668
     * @param AclHelper $aclHelper
669
     * @param DateFilterProcessor $dateFilterProcessor
670
     * @param array $dateRange
671
     * @param array $owners
672
     *
673
     * @return array [value, source]
674
     */
675 View Code Duplication
    public function getOpportunitiesAmountGroupByLeadSource(
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...
676
        AclHelper $aclHelper,
677
        DateFilterProcessor $dateFilterProcessor,
678
        array $dateRange = [],
679
        array $owners = []
680
    ) {
681
        $qb = $this->getOpportunitiesGroupByLeadSourceQueryBuilder($dateFilterProcessor, $dateRange, $owners);
682
        $qb->addSelect("SUM(CASE WHEN o.status = 'won' THEN o.closeRevenue ELSE o.budgetAmount END) as value");
683
684
        return $aclHelper->apply($qb)->getArrayResult();
685
    }
686
687
    /**
688
     * Returns opportunities QB grouped by lead source filtered by $dateRange and $owners
689
     *
690
     * @param DateFilterProcessor $dateFilterProcessor
691
     * @param array $dateRange
692
     * @param array $owners
693
     *
694
     * @return QueryBuilder
695
     */
696
    protected function getOpportunitiesGroupByLeadSourceQueryBuilder(
697
        DateFilterProcessor $dateFilterProcessor,
698
        array $dateRange = [],
699
        array $owners = []
700
    ) {
701
        $qb = $this->createQueryBuilder('o')
702
            ->select('s.id as source')
703
            ->leftJoin('o.lead', 'l')
704
            ->leftJoin('l.source', 's')
705
            ->groupBy('source');
706
707
        $dateFilterProcessor->process($qb, $dateRange, 'o.createdAt');
708
709
        if ($owners) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $owners 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...
710
            QueryUtils::applyOptimizedIn($qb, 'o.owner', $owners);
711
        }
712
713
        return $qb;
714
    }
715
716
    /**
717
     * @param string $alias
718
     * @param array  $excludedStatuses
719
     *
720
     * @return QueryBuilder
721
     */
722
    public function getForecastQB($alias = 'o', array $excludedStatuses = ['lost', 'won'])
723
    {
724
        $qb     = $this->createQueryBuilder($alias);
725
        $qb->select([
726
            sprintf('COUNT(%s.id) as inProgressCount', $alias),
727
            sprintf('SUM(%s.budgetAmount) as budgetAmount', $alias),
728
            sprintf('SUM(%s.budgetAmount * %s.probability) as weightedForecast', $alias, $alias)
729
        ]);
730
731
        if ($excludedStatuses) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $excludedStatuses 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...
732
            $qb->andWhere($qb->expr()->notIn(sprintf('%s.status', $alias), $excludedStatuses));
733
        }
734
735
        return $qb;
736
    }
737
}
738