Server : Apache System : Linux server2.corals.io 4.18.0-348.2.1.el8_5.x86_64 #1 SMP Mon Nov 15 09:17:08 EST 2021 x86_64 User : corals ( 1002) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory : /home/corals/mautic.corals.io/app/bundles/PageBundle/Entity/ |
<?php
namespace Mautic\PageBundle\Entity;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\CoreBundle\Helper\DateTimeHelper;
use Mautic\LeadBundle\Entity\Lead;
use Mautic\LeadBundle\Entity\TimelineTrait;
/**
* @extends CommonRepository<Hit>
*/
class HitRepository extends CommonRepository
{
use TimelineTrait;
/**
* Determine if the page hit is a unique.
*
* @param Page|Redirect $page
* @param string $trackingId
*/
public function isUniquePageHit($page, $trackingId, Lead $lead = null): bool
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q2 = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q2->select('null')
->from(MAUTIC_TABLE_PREFIX.'page_hits', 'h');
// If we know the lead, use that to determine uniqueness
if (null !== $lead && $lead->getId()) {
$expr = CompositeExpression::and($q2->expr()->eq('h.lead_id', $lead->getId()));
} else {
$expr = CompositeExpression::and($q2->expr()->eq('h.tracking_id', ':id'));
$q->setParameter('id', $trackingId);
}
if ($page instanceof Page) {
$expr = $expr->with(
$q2->expr()->eq('h.page_id', $page->getId())
);
} elseif ($page instanceof Redirect) {
$expr = $expr->with(
$q2->expr()->eq('h.redirect_id', $page->getId())
);
}
$q2->where($expr);
$q->select('u.is_unique')
->from(sprintf('(SELECT (NOT EXISTS (%s)) is_unique)', $q2->getSQL()), 'u');
return (bool) $q->executeQuery()->fetchOne();
}
/**
* Get a lead's page hits.
*
* @param int|null $leadId
*
* @return array
*/
public function getLeadHits($leadId = null, array $options = [])
{
$query = $this->getEntityManager()->getConnection()->createQueryBuilder();
$query->select('h.id as hitId, h.page_id, h.user_agent as userAgent, h.date_hit as dateHit, h.date_left as dateLeft, h.referer, h.source, h.source_id as sourceId, h.url, h.url_title as urlTitle, h.query, ds.client_info as clientInfo, ds.device, ds.device_os_name as deviceOsName, ds.device_brand as deviceBrand, ds.device_model as deviceModel, h.lead_id')
->from(MAUTIC_TABLE_PREFIX.'page_hits', 'h')
->leftJoin('h', MAUTIC_TABLE_PREFIX.'pages', 'p', 'h.page_id = p.id');
if ($leadId) {
$query->where('h.lead_id = '.(int) $leadId);
}
if (isset($options['search']) && $options['search']) {
$query->andWhere($query->expr()->like('p.title', $query->expr()->literal('%'.$options['search'].'%')));
}
$query->leftjoin('h', MAUTIC_TABLE_PREFIX.'lead_devices', 'ds', 'ds.id = h.device_id');
if (isset($options['url']) && $options['url']) {
$query->andWhere($query->expr()->eq('h.url', $query->expr()->literal($options['url'])));
}
return $this->getTimelineResults($query, $options, 'p.title', 'h.date_hit', ['query'], ['dateHit', 'dateLeft']);
}
/**
* @return array
*/
public function getHitCountForSource($source, $sourceId = null, $fromDate = null, $code = 200)
{
$query = $this->createQueryBuilder('h');
$query->select('count(distinct(h.trackingId)) as "hitCount"');
$query->andWhere($query->expr()->eq('h.source', $query->expr()->literal($source)));
if (null != $sourceId) {
if (is_array($sourceId)) {
$query->andWhere($query->expr()->in('h.sourceId', ':sourceIds'))
->setParameter('sourceIds', $sourceId);
} else {
$query->andWhere($query->expr()->eq('h.sourceId', (int) $sourceId));
}
}
if (null != $fromDate) {
$query->andwhere($query->expr()->gte('h.dateHit', ':date'))
->setParameter('date', $fromDate);
}
$query->andWhere($query->expr()->eq('h.code', (int) $code));
return $query->getQuery()->getArrayResult();
}
/**
* Get an array of hits via an email clickthrough.
*
* @param int $code
*/
public function getEmailClickthroughHitCount($emailIds, \DateTime $fromDate = null, $code = 200): array
{
$q = $this->_em->getConnection()->createQueryBuilder();
if (!is_array($emailIds)) {
$emailIds = [$emailIds];
}
$q->select('count(distinct(h.tracking_id)) as hit_count, h.email_id')
->from(MAUTIC_TABLE_PREFIX.'page_hits', 'h')
->where($q->expr()->in('h.email_id', $emailIds))
->groupBy('h.email_id');
if (null != $fromDate) {
$dateHelper = new DateTimeHelper($fromDate);
$q->andwhere($q->expr()->gte('h.date_hit', ':date'))
->setParameter('date', $dateHelper->toUtcString());
}
$q->andWhere($q->expr()->eq('h.code', (int) $code));
$results = $q->executeQuery()->fetchAllAssociative();
$hits = [];
foreach ($results as $r) {
$hits[$r['email_id']] = $r['hit_count'];
}
return $hits;
}
/**
* Count returning IP addresses.
*/
public function countReturningIp(): int
{
$q = $this->createQueryBuilder('h');
$q->select('COUNT(h.ipAddress) as returning')
->groupBy('h.ipAddress')
->having($q->expr()->gt('COUNT(h.ipAddress)', 1));
$results = $q->getQuery()->getResult();
return count($results);
}
/**
* Count email clickthrough.
*
* @return int
*/
public function countEmailClickthrough()
{
$q = $this->createQueryBuilder('h');
$q->select('COUNT(h.email) as clicks');
$results = $q->getQuery()->getSingleResult();
return $results['clicks'];
}
/**
* Count how many visitors hit some page in last X $seconds.
*
* @param int $seconds
* @param bool $notLeft
*/
public function countVisitors($seconds = 60, $notLeft = false): int
{
$now = new \DateTime();
$viewingTime = new \DateInterval('PT'.$seconds.'S');
$now->sub($viewingTime);
$query = $this->createQueryBuilder('h');
$query->select('count(h.code) as visitors');
if ($seconds) {
$query->where($query->expr()->gte('h.dateHit', ':date'))
->setParameter('date', $now);
}
if ($notLeft) {
$query->andWhere($query->expr()->isNull('h.dateLeft'));
}
$result = $query->getQuery()->getSingleResult();
if (!isset($result['visitors'])) {
return 0;
}
return (int) $result['visitors'];
}
/**
* Get the latest hit.
*
* @param array $options
*/
public function getLatestHit($options): ?\DateTime
{
$sq = $this->_em->getConnection()->createQueryBuilder();
$sq->select('h.date_hit latest_hit')
->from(MAUTIC_TABLE_PREFIX.'page_hits', 'h');
if (isset($options['leadId'])) {
$sq->andWhere(
$sq->expr()->eq('h.lead_id', $options['leadId'])
);
}
if (isset($options['urls']) && $options['urls']) {
$inUrls = (!is_array($options['urls'])) ? [$options['urls']] : $options['urls'];
foreach ($inUrls as $k => $u) {
$sq->andWhere($sq->expr()->like('h.url', ':url_'.$k))
->setParameter('url_'.$k, $u);
}
}
if (isset($options['second_to_last'])) {
$sq->andWhere($sq->expr()->neq('h.id', $options['second_to_last']));
} else {
$sq->orderBy('h.date_hit', 'DESC limit 1');
}
$result = $sq->executeQuery()->fetchAssociative();
return $result ? new \DateTime($result['latest_hit'], new \DateTimeZone('UTC')) : null;
}
/**
* Get the number of bounces.
*
* @param array|string $pageIds
* @param bool $isVariantCheck
*
* @return mixed[]
*/
public function getBounces($pageIds, \DateTime $fromDate = null, $isVariantCheck = false): array
{
$inOrEq = (!is_array($pageIds)) ? 'eq' : 'in';
$hitsColumn = ($isVariantCheck) ? 'variant_hits' : 'unique_hits';
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$pages = $q->select("p.id, p.$hitsColumn as totalHits, p.title")
->from(MAUTIC_TABLE_PREFIX.'pages', 'p')
->where($q->expr()->$inOrEq('p.id', $pageIds))
->executeQuery()
->fetchAllAssociative();
$return = [];
foreach ($pages as $p) {
$return[$p['id']] = [
'totalHits' => (int) $p['totalHits'],
'bounces' => 0,
'rate' => 0,
'title' => $p['title'],
];
}
// Get the total number of bounces - simplified query for if date_left is null, it'll more than likely be a bounce or
// else we would have recorded the date_left on a subsequent page hit
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$expr = $q->expr()->and(
$q->expr()->$inOrEq('h.page_id', $pageIds),
$q->expr()->eq('h.code', 200),
$q->expr()->isNull('h.date_left')
);
if (null !== $fromDate) {
// make sure the date is UTC
$dt = new DateTimeHelper($fromDate, 'Y-m-d H:i:s', 'local');
$expr = $expr->with(
$q->expr()->gte('h.date_hit', $q->expr()->literal($dt->toUtcString()))
);
}
$q->select('count(*) as bounces, h.page_id')
->from(MAUTIC_TABLE_PREFIX.'page_hits', 'h')
->where($expr)
->groupBy('h.page_id');
$results = $q->executeQuery()->fetchAllAssociative();
foreach ($results as $p) {
$return[$p['page_id']]['bounces'] = (int) $p['bounces'];
$return[$p['page_id']]['rate'] = ($return[$p['page_id']]['totalHits']) ? round(
($p['bounces'] / $return[$p['page_id']]['totalHits']) * 100,
2
) : 0;
}
return (!is_array($pageIds)) ? $return[$pageIds] : $return;
}
/**
* Get array of dwell time labels with ranges.
*/
public function getDwellTimeLabels(): array
{
return [
[
'label' => '< 1m',
'from' => 0,
'till' => 60,
],
[
'label' => '1 - 5m',
'from' => 60,
'till' => 300,
],
[
'label' => '5 - 10m',
'value' => 0,
'from' => 300,
'till' => 600,
],
[
'label' => '> 10m',
'from' => 600,
'till' => 999999,
],
];
}
/**
* Get the dwell times for bunch of pages.
*/
public function getDwellTimesForPages(array $pageIds, array $options): array
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->from(MAUTIC_TABLE_PREFIX.'page_hits', 'ph')
->leftJoin('ph', MAUTIC_TABLE_PREFIX.'pages', 'p', 'ph.page_id = p.id')
->select('ph.page_id, ph.date_hit, ph.date_left, p.title')
->orderBy('ph.date_hit', 'ASC')
->andWhere(
$q->expr()->and(
$q->expr()->in('ph.page_id', $pageIds)
)
);
if (isset($options['fromDate']) && null !== $options['fromDate']) {
// make sure the date is UTC
$dt = new DateTimeHelper($options['fromDate']);
$q->andWhere(
$q->expr()->gte('ph.date_hit', $q->expr()->literal($dt->toUtcString()))
);
}
$results = $q->executeQuery()->fetchAllAssociative();
// loop to structure
$times = [];
$titles = [];
foreach ($results as $r) {
$dateHit = $r['date_hit'] ? new \DateTime($r['date_hit']) : 0;
$dateLeft = $r['date_left'] ? new \DateTime($r['date_left']) : 0;
$titles[$r['page_id']] = $r['title'];
$times[$r['page_id']][] = $dateLeft ? ($dateLeft->getTimestamp() - $dateHit->getTimestamp()) : 0;
}
// now loop to create stats
$stats = [];
foreach ($times as $pid => $time) {
$stats[$pid] = $this->countStats($time);
$stats[$pid]['title'] = $titles[$pid];
}
return $stats;
}
/**
* Get the dwell times for bunch of URLs.
*
* @param string $url
*/
public function getDwellTimesForUrl($url, array $options): array
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->from(MAUTIC_TABLE_PREFIX.'page_hits', 'ph')
->leftJoin('ph', MAUTIC_TABLE_PREFIX.'pages', 'p', 'ph.page_id = p.id')
->select('ph.id, ph.page_id, ph.date_hit, ph.date_left, ph.tracking_id, ph.page_language, p.title')
->orderBy('ph.date_hit', 'ASC')
->andWhere($q->expr()->like('ph.url', ':url'))
->setParameter('url', $url);
if (isset($options['leadId']) && $options['leadId']) {
$q->andWhere(
$q->expr()->eq('ph.lead_id', (int) $options['leadId'])
);
}
$results = $q->executeQuery()->fetchAllAssociative();
$times = [];
foreach ($results as $r) {
$dateHit = $r['date_hit'] ? new \DateTime($r['date_hit']) : 0;
$dateLeft = $r['date_left'] ? new \DateTime($r['date_left']) : 0;
$times[] = $dateLeft ? ($dateLeft->getTimestamp() - $dateHit->getTimestamp()) : 0;
}
return $this->countStats($times);
}
/**
* Count stats from hit times.
*
* @param array $times
*/
public function countStats($times): array
{
return [
'sum' => array_sum($times),
'min' => count($times) ? min($times) : 0,
'max' => count($times) ? max($times) : 0,
'average' => count($times) ? round(array_sum($times) / count($times)) : 0,
'count' => count($times),
];
}
/**
* Update a hit with the the time the user left.
*
* @param int $lastHitId
*/
public function updateHitDateLeft($lastHitId): void
{
$dt = new DateTimeHelper();
$q = $this->_em->getConnection()->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX.'page_hits')
->set('date_left', ':datetime')
->where('id = '.(int) $lastHitId)
->setParameter('datetime', $dt->toUtcString());
$q->executeStatement();
}
/**
* Get list of referers ordered by it's count.
*
* @param \Doctrine\DBAL\Query\QueryBuilder $query
* @param int $limit
* @param int $offset
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getReferers($query, $limit = 10, $offset = 0): array
{
$query->select('ph.referer, count(ph.referer) as sessions')
->groupBy('ph.referer')
->orderBy('sessions', 'DESC')
->setMaxResults($limit)
->setFirstResult($offset);
return $query->executeQuery()->fetchAllAssociative();
}
/**
* Get list of referers ordered by it's count.
*
* @param \Doctrine\DBAL\Query\QueryBuilder $query
* @param int $limit
* @param int $offset
* @param string $column
* @param string $as
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getMostVisited($query, $limit = 10, $offset = 0, $column = 'p.hits', $as = ''): array
{
if ($as) {
$as = ' as "'.$as.'"';
}
$query->select('p.title, p.id, '.$column.$as)
->where('p.id IS NOT NULL')
->groupBy('p.id, p.title, '.$column)
->orderBy($column, 'DESC')
->setMaxResults($limit)
->setFirstResult($offset);
return $query->executeQuery()->fetchAllAssociative();
}
public function updateLeadByTrackingId($leadId, $newTrackingId, $oldTrackingId): void
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX.'page_hits')
->set('lead_id', (int) $leadId)
->set('tracking_id', ':newTrackingId')
->where(
$q->expr()->eq('tracking_id', ':oldTrackingId')
)
->setParameters([
'newTrackingId' => $newTrackingId,
'oldTrackingId' => $oldTrackingId,
])
->executeStatement();
}
/**
* Updates lead ID (e.g. after a lead merge).
*/
public function updateLead($fromLeadId, $toLeadId): void
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX.'page_hits')
->set('lead_id', (int) $toLeadId)
->where('lead_id = '.(int) $fromLeadId)
->executeStatement();
}
public function getLatestHitDateByLead(int $leadId, string $trackingId = null): ?\DateTime
{
$q = $this->_em->getConnection()->createQueryBuilder()
->select('MAX(date_hit)')
->from(MAUTIC_TABLE_PREFIX.'page_hits')
->where('lead_id = :leadId')
->setParameter('leadId', $leadId);
if (null != $trackingId) {
$q->andWhere('tracking_id = :trackingId')
->setParameter('trackingId', $trackingId);
}
$result = $q->executeQuery()->fetchOne();
return $result ? new \DateTime($result, new \DateTimeZone('UTC')) : null;
}
}