Spaces:
No application file
No application file
File size: 6,313 Bytes
d2897cd |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 |
<?php
declare(strict_types=1);
namespace Mautic\CampaignBundle\Entity;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\LeadBundle\Entity\TimelineTrait;
/**
* @extends CommonRepository<Summary>
*/
class SummaryRepository extends CommonRepository
{
use TimelineTrait;
use ContactLimiterTrait;
public function getTableAlias(): string
{
return 's';
}
/**
* @return array<int|string, array<int|string, int|string>>
*/
public function getCampaignLogCounts(
int $campaignId,
\DateTimeInterface $dateFrom = null,
\DateTimeInterface $dateTo = null
): array {
$q = $this->_em->getConnection()->createQueryBuilder()
->select(
[
'cs.event_id',
'SUM(cs.scheduled_count) as scheduled_count',
'SUM(cs.triggered_count) as triggered_count',
'SUM(cs.non_action_path_taken_count) as non_action_path_taken_count',
'SUM(cs.failed_count) as failed_count',
'SUM(cs.log_counts_processed) as log_counts_processed',
]
)
->from(MAUTIC_TABLE_PREFIX.'campaign_summary', 'cs')
->where('cs.campaign_id = '.(int) $campaignId)
->groupBy('cs.event_id');
if ($dateFrom && $dateTo) {
$q->andWhere('cs.date_triggered BETWEEN FROM_UNIXTIME(:dateFrom) AND FROM_UNIXTIME(:dateTo)')
->setParameter('dateFrom', $dateFrom->getTimestamp(), \PDO::PARAM_INT)
->setParameter('dateTo', $dateTo->getTimestamp(), \PDO::PARAM_INT);
}
$results = $q->executeQuery()->fetchAllAssociative();
$return = [];
// Group by event id
foreach ($results as $row) {
$return[$row['event_id']] = [
0 => (int) $row['non_action_path_taken_count'],
1 => (int) $row['triggered_count'] + (int) $row['scheduled_count'],
2 => (int) $row['log_counts_processed'],
];
}
return $return;
}
/**
* Get the oldest triggered time for back-filling historical data.
*/
public function getOldestTriggeredDate(): ?\DateTimeInterface
{
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->select('cs.date_triggered')
->from(MAUTIC_TABLE_PREFIX.'campaign_summary', 'cs')
->orderBy('cs.date_triggered', 'ASC')
->setMaxResults(1);
$results = $qb->executeQuery()->fetchAllAssociative();
return isset($results[0]['date_triggered']) ? new \DateTime($results[0]['date_triggered']) : null;
}
/**
* Regenerate summary entries for a given time frame.
*
* @throws \Doctrine\DBAL\Exception
*/
public function summarize(
\DateTimeInterface $dateFrom,
\DateTimeInterface $dateTo,
int $campaignId = null,
int $eventId = null
): void {
$dateFromTsActual = $dateFrom->getTimestamp();
$dateToTsActual = $dateTo->getTimestamp();
$intervalInSeconds= 3600;
$dateFromStartWithZeroMinutes = $dateFromTsActual - ($dateFromTsActual % $intervalInSeconds);
$numberOfIntervals = ceil(($dateToTsActual - $dateFromStartWithZeroMinutes) / $intervalInSeconds);
for ($interval = 0; $interval < $numberOfIntervals; ++$interval) {
$dateFromTs = date('Y-m-d H:i:s', $dateFromStartWithZeroMinutes + ($interval * $intervalInSeconds));
$dateToTs = date('Y-m-d H:i:s', strtotime($dateFromTs) + ($intervalInSeconds - 1));
$sql = 'INSERT INTO '.MAUTIC_TABLE_PREFIX.'campaign_summary '.
' (campaign_id, event_id, date_triggered, scheduled_count, non_action_path_taken_count, failed_count, triggered_count, log_counts_processed) '.
' SELECT * FROM (SELECT '.
' mclel.campaign_id AS campaign_id, '.
' mclel.event_id AS event_id, '.
' "'.$dateFromTs.'" AS date_triggered_i, '.
' SUM(IF(mclel.is_scheduled = 1 AND mclel.trigger_date > NOW(), 1, 0)) AS scheduled_count_i, '.
' SUM(IF(mclel.is_scheduled = 1 AND mclel.trigger_date > NOW(), 0, mclel.non_action_path_taken)) AS non_action_path_taken_count_i, '.
' SUM(IF((mclel.is_scheduled = 1 AND mclel.trigger_date > NOW()) OR mclel.non_action_path_taken, 0, mclefl.log_id IS NOT NULL)) AS failed_count_i, '.
' SUM(IF((mclel.is_scheduled = 1 AND mclel.trigger_date > NOW()) OR mclel.non_action_path_taken OR mclefl.log_id IS NOT NULL, 0, 1)) AS triggered_count_i, '.
' COUNT((SELECT mcl.campaign_id FROM '.MAUTIC_TABLE_PREFIX.'campaign_leads mcl '.
' WHERE mcl.campaign_id = mclel.campaign_id AND mcl.manually_removed = 0 '.
' AND mclel.lead_id = mcl.lead_id AND mcl.rotation = mclel.rotation '.
' AND NOT EXISTS(SELECT NULL FROM '.MAUTIC_TABLE_PREFIX.'campaign_lead_event_failed_log mclefl2 '.
' WHERE mclefl2.log_id = mclel.id AND mclefl2.date_added BETWEEN "'.$dateFromTs.'" AND "'.$dateToTs.'")'.
' )) AS log_counts_processed_i '.
' FROM '.MAUTIC_TABLE_PREFIX.'campaign_lead_event_log mclel LEFT JOIN '.MAUTIC_TABLE_PREFIX.'campaign_lead_event_failed_log mclefl ON mclefl.log_id = mclel.id '.
' WHERE (mclel.date_triggered BETWEEN "'.$dateFromTs.'" AND "'.$dateToTs.'") ';
if ($campaignId) {
$sql .= ' AND mclel.campaign_id = '.$campaignId;
}
if ($eventId) {
$sql .= ' AND mclel.event_id = '.$eventId;
}
$sql .= ' GROUP BY mclel.campaign_id, mclel.event_id) AS `s` '.
' ON DUPLICATE KEY UPDATE '.
' scheduled_count = s.scheduled_count_i, '.
' non_action_path_taken_count = s.non_action_path_taken_count_i, '.
' failed_count = s.failed_count_i, '.
' triggered_count = s.triggered_count_i, '.
' log_counts_processed = s.log_counts_processed_i;';
$this->getEntityManager()->getConnection()->executeQuery($sql);
}
}
}
|