zend-blog-3-backend

1
<?php
2

3
namespace App\Repository;
4

5
use App\Entity\GeoLocation;
6
use App\Entity\Tracking;
7
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
8
use Doctrine\ORM\Query;
9
use Doctrine\Persistence\ManagerRegistry;
10

11
/**
12
 * TrackingRepository
13
 *
14
 * This class was generated by the Doctrine ORM. Add your own custom
15
 * repository methods below.
16
 */
17
class TrackingRepository extends ServiceEntityRepository
18
{
19
    /**
20
     * @param ManagerRegistry $registry
21
     */
22
    public function __construct(ManagerRegistry $registry)
23
    {
24
        parent::__construct($registry, Tracking::class);
25
    }
26

27
    /**
28
     * @return Query
29
     */
30
    public function getListQuery(): Query
31
    {
32
        $qb = $this->createQueryBuilder('e');
33
        $qb
34
            ->select('e', 'ta', 'ar', 'g')
35
            ->leftJoin('e.trackingAgent', 'ta')
36
            ->leftJoin('e.post', 'ar')
37
            ->leftJoin('e.geoLocation', 'g')
38
            ->orderBy('e.id', 'DESC')
39
        ;
40

41
        return $qb->getQuery();
42
    }
43

44
    /**
45
     * @param string $from
46
     * @param string $to
47
     *
48
     * @return array
49
     */
50
    public function getViewCountsInfo(string $from, string $to): array
51
    {
52
        $qb = $this->createQueryBuilder('t');
53
        $qb
54
            ->select('p.id', 'COUNT(t.id) AS cnt')
55
            ->innerJoin('t.post', 'p')
56
            ->innerJoin('t.trackingAgent', 'ta')
57
            ->andWhere($qb->expr()->gt('t.timeCreated', ':from'))
58
            ->andWhere($qb->expr()->lte('t.timeCreated', ':to'))
59
            ->andWhere($qb->expr()->eq('ta.bot', $qb->expr()->literal(false)))
60
            ->groupBy('p.id')
61
            ->setParameter('from', $from)
62
            ->setParameter('to', $to)
63
        ;
64

65
        return $qb->getQuery()->getArrayResult();
66
    }
67

68
    /**
69
     * @param string $from
70
     * @param string $to
71
     *
72
     * @return array
73
     */
74
    public function getDataAboutServerErrors(string $from, string $to): array
75
    {
76
        $qb = $this->createQueryBuilder('t');
77
        $qb
78
            ->select('IDENTITY(t.post) AS postID', 't.requestURI', 'COUNT(t.id) AS cnt')
79
            ->andWhere($qb->expr()->gte('t.timeCreated', ':from'))
80
            ->andWhere($qb->expr()->lt('t.timeCreated', ':to'))
81
            ->andWhere($qb->expr()->gte('t.statusCode', $qb->expr()->literal(500)))
82
            ->andWhere($qb->expr()->lt('t.statusCode', $qb->expr()->literal(600)))
83
            ->groupBy('postID')
84
            ->addGroupBy('t.requestURI')
85
            ->setParameter('from', $from)
86
            ->setParameter('to', $to)
87
        ;
88

89
        return $qb->getQuery()->getArrayResult();
90
    }
91

92
    public function getUncheckedIps(): array
93
    {
94
        $qb = $this->createQueryBuilder('t');
95
        $qb
96
            ->select('t.ipAddress', 'MAX(t.timeCreated) AS last_access')
97
            ->leftJoin('t.geoLocation', 'g')
98
            ->where($qb->expr()->orX(
99
                $qb->expr()->isNull('t.geoLocation'),
100
                $qb->expr()->isNull('g.city')
101
            ))
102
            ->andWhere($qb->expr()->isNotNull('t.ipAddress'))
103
            ->groupBy('t.ipAddress')
104
            ->orderBy('last_access', 'DESC')
105
            ->setMaxResults(60)
106
        ;
107

108
        return array_column($qb->getQuery()->getArrayResult(), 'ipAddress');
109
    }
110

111
    /**
112
     * @param GeoLocation $location
113
     * @param string $ip
114
     */
115
    public function updateLocation(GeoLocation $location, string $ip)
116
    {
117
        $qb = $this->createQueryBuilder('t');
118
        $qb
119
            ->update()
120
            ->set('t.geoLocation', ':location')
121
            ->where($qb->expr()->eq('t.ipAddress', ':ip'))
122
            ->setParameter('location', $location->getId())
123
            ->setParameter('ip', $ip)
124
        ;
125

126
        $qb->getQuery()->execute();
127
    }
128
}
129