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] = [ |
|
|
|
|
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) { |
|
|
|
|
127
|
|
|
$qb->where($qb->expr()->between('opportunity.createdAt', ':dateFrom', ':dateTo')) |
128
|
|
|
->setParameter('dateFrom', $dateStart) |
129
|
|
|
->setParameter('dateTo', $dateEnd); |
130
|
|
|
} |
131
|
|
|
|
132
|
|
|
if ($owners) { |
|
|
|
|
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; |
|
|
|
|
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) { |
|
|
|
|
198
|
|
|
$qb |
199
|
|
|
->andWhere('opportunity.closeDate >= :startDate') |
200
|
|
|
->setParameter('startDate', $start); |
201
|
|
|
} |
202
|
|
|
if ($end) { |
|
|
|
|
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; |
|
|
|
|
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( |
|
|
|
|
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( |
|
|
|
|
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 = []) |
|
|
|
|
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) { |
|
|
|
|
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) { |
|
|
|
|
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( |
|
|
|
|
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) |
|
|
|
|
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( |
|
|
|
|
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) { |
|
|
|
|
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) { |
|
|
|
|
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( |
|
|
|
|
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) { |
|
|
|
|
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( |
|
|
|
|
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) { |
|
|
|
|
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) |
|
|
|
|
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) |
|
|
|
|
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( |
|
|
|
|
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( |
|
|
|
|
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) { |
|
|
|
|
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) { |
|
|
|
|
732
|
|
|
$qb->andWhere($qb->expr()->notIn(sprintf('%s.status', $alias), $excludedStatuses)); |
733
|
|
|
} |
734
|
|
|
|
735
|
|
|
return $qb; |
736
|
|
|
} |
737
|
|
|
} |
738
|
|
|
|
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:
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 thebar
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.