Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
91.94% covered (success)
91.94%
114 / 124
50.00% covered (danger)
50.00%
3 / 6
CRAP
0.00% covered (danger)
0.00%
0 / 1
UpdateMeasurementsCommand
91.94% covered (success)
91.94%
114 / 124
50.00% covered (danger)
50.00%
3 / 6
41.88
0.00% covered (danger)
0.00%
0 / 1
 getAllowedAppEnvs
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 handle
95.00% covered (success)
95.00%
19 / 20
0.00% covered (danger)
0.00%
0 / 1
4
 backfill
0.00% covered (danger)
0.00%
0 / 8
0.00% covered (danger)
0.00%
0 / 1
30
 updateMeasurementsForMonth
98.77% covered (success)
98.77%
80 / 81
0.00% covered (danger)
0.00%
0 / 1
29
 getMonthCriteria
100.00% covered (success)
100.00%
12 / 12
100.00% covered (success)
100.00%
1 / 1
1
 getMonthRange
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
1
1<?php
2
3namespace Olz\Command;
4
5use Doctrine\Common\Collections\Criteria;
6use Olz\Apps\Members\Utils\MembersUtilsTrait;
7use Olz\Command\Common\OlzCommand;
8use Olz\Entity\Measurement;
9use Olz\Entity\Members\Member;
10use Olz\Entity\News\NewsEntry;
11use Olz\Entity\SolvEvent;
12use Olz\Entity\SolvPerson;
13use Olz\Entity\SolvResult;
14use Olz\Entity\Termine\Termin;
15use Olz\Entity\Termine\TerminReaction;
16use Olz\Entity\Users\User;
17use Symfony\Component\Console\Attribute\AsCommand;
18use Symfony\Component\Console\Command\Command;
19use Symfony\Component\Console\Input\InputInterface;
20use Symfony\Component\Console\Output\OutputInterface;
21
22#[AsCommand(name: 'olz:update-measurements')]
23class UpdateMeasurementsCommand extends OlzCommand {
24    use MembersUtilsTrait;
25
26    /** Bump to trigger a backfill whenever the measurement calculation changes. */
27    public const CALCULATION_VERSION = 3;
28
29    private const EARLIEST_YEAR = 2006;
30    private const EARLIEST_MONTH = 1;
31
32    /** Metrics which describe a state and can be calculated for any month. */
33    private const SNAPSHOT_NAMES = [
34        'num_users',
35        'num_members',
36        'num_member_users',
37        'num_termine',
38    ];
39
40    /** Metrics which accumulate during a month; meaningless for the current (incomplete) month. */
41    private const EVENT_NAMES = [
42        'num_solv_participations',
43        'avg_training_participants',
44        'num_news',
45    ];
46
47    /** @return array<string> */
48    protected function getAllowedAppEnvs(): array {
49        return ['dev', 'test', 'staging', 'prod'];
50    }
51
52    protected function handle(InputInterface $input, OutputInterface $output): int {
53        $year = intval($this->dateUtils()->getCurrentDateInFormat('Y'));
54        $month = intval($this->dateUtils()->getCurrentDateInFormat('m'));
55        $prev_year = $month > 1 ? $year : $year - 1;
56        $prev_month = $month > 1 ? $month - 1 : 12;
57
58        $this->updateMeasurementsForMonth($prev_year, $prev_month);
59        $this->updateMeasurementsForMonth($year, $month);
60
61        $measurement_repo = $this->entityManager()->getRepository(Measurement::class);
62        // Anything not calculated with the current version is stale. `neq` (rather than `lt`)
63        // also covers rows with a higher version, e.g. after rolling back to older code.
64        // Only the metrics calculated here are considered, so that legacy rows of removed
65        // metrics (e.g. `num_weekly_pictures`) don't trigger a backfill on every run.
66        $stale_criteria = Criteria::create()
67            ->where(Criteria::expr()->andX(
68                Criteria::expr()->in('name', array_merge(self::SNAPSHOT_NAMES, self::EVENT_NAMES)),
69                Criteria::expr()->neq('version', self::CALCULATION_VERSION),
70            ))
71            ->setFirstResult(0)
72            ->setMaxResults(1)
73        ;
74        $is_stale = count($measurement_repo->matching($stale_criteria)) > 0;
75        if ($is_stale) {
76            $this->backfill(self::EARLIEST_YEAR, self::EARLIEST_MONTH, $year, $month);
77        }
78
79        $this->entityManager()->flush();
80        return Command::SUCCESS;
81    }
82
83    private function backfill(int $from_year, int $from_month, int $to_year, int $to_month): void {
84        $year = $from_year;
85        $month = $from_month;
86        while ($year < $to_year || ($year === $to_year && $month <= $to_month)) {
87            $this->updateMeasurementsForMonth($year, $month);
88            if ($month >= 12) {
89                $month = 1;
90                $year++;
91            } else {
92                $month++;
93            }
94        }
95    }
96
97    private function updateMeasurementsForMonth(int $year, int $month): void {
98        [$start, $next] = $this->getMonthRange($year, $month);
99        $month_key = sprintf('%04d-%02d', $year, $month);
100        // Snapshot metrics describe a state: they are evaluated at the middle of the month.
101        $at = new \DateTime(sprintf('%04d-%02d-15 12:00:00', $year, $month));
102        $current_year = intval($this->dateUtils()->getCurrentDateInFormat('Y'));
103        $current_month = intval($this->dateUtils()->getCurrentDateInFormat('m'));
104        $is_current_month = ($year === $current_year && $month === $current_month);
105
106        $user_repo = $this->entityManager()->getRepository(User::class);
107        $member_repo = $this->entityManager()->getRepository(Member::class);
108        $termin_repo = $this->entityManager()->getRepository(Termin::class);
109        $termin_reaction_repo = $this->entityManager()->getRepository(TerminReaction::class);
110        $news_repo = $this->entityManager()->getRepository(NewsEntry::class);
111        $solv_event_repo = $this->entityManager()->getRepository(SolvEvent::class);
112        $solv_person_repo = $this->entityManager()->getRepository(SolvPerson::class);
113        $solv_result_repo = $this->entityManager()->getRepository(SolvResult::class);
114
115        // Benutzer & Mitglieder. Entities which were deleted later still count for the
116        // months before their deletion, which is why there is no `deleted_at` filter.
117        $num_users = 0;
118        $num_member_users = 0;
119        foreach ($user_repo->findAll() as $user) {
120            // Only "real" accounts count: users with a password, and child accounts.
121            if (!$user->getPasswordHash() && $user->getParentUserId() === null) {
122                continue;
123            }
124            $user_existed = $user->exists($at);
125            if ($user_existed) {
126                $num_users++;
127            }
128            if ($user_existed && $user->isMember($at)) {
129                $num_member_users++;
130            }
131        }
132
133        // Mitglieder: `Eintritt` / `Austritt` from the member data.
134        $num_members = 0;
135        foreach ($member_repo->findAll() as $member) {
136            if ($this->membersUtils()->isMemberActiveAt($member, $at)) {
137                $num_members++;
138            }
139        }
140
141        // Termine.
142        $termine = [...$termin_repo->matching($this->getMonthCriteria('start_date', $start, $next))];
143        $num_termine = count($termine);
144
145        // Trainings (Kartentrainings): Durchschnitt Teilnehmer pro Training.
146        $training_termin_ids = [];
147        foreach ($termine as $termin) {
148            foreach ($termin->getLabels() as $label) {
149                if ($label->getIdent() === 'training') {
150                    $termin_id = $termin->getId();
151                    if ($termin_id !== null) {
152                        $training_termin_ids[] = $termin_id;
153                    }
154                    break;
155                }
156            }
157        }
158        $num_trainings = count($training_termin_ids);
159        $sum_training_participants = 0;
160        foreach ($training_termin_ids as $termin_id) {
161            $user_ids = [];
162            foreach ($termin_reaction_repo->findBy(['termin' => $termin_id]) as $reaction) {
163                if (in_array($reaction->getEmoji(), TerminReaction::ACCEPT_EMOJIS, true)) {
164                    $user_id = $reaction->getUser()->getId();
165                    if ($user_id !== null) {
166                        $user_ids[$user_id] = true;
167                    }
168                }
169            }
170            $sum_training_participants += count($user_ids);
171        }
172        $avg_training_participants = $num_trainings > 0
173            ? $sum_training_participants / $num_trainings
174            : 0.0;
175
176        // SOLV-Anlässe: Teilnahmen unserer Mitglieder (solv_people.member).
177        $member_person_ids = [];
178        foreach ($solv_person_repo->findBy([]) as $solv_person) {
179            $person_id = $solv_person->getId();
180            if ($person_id !== null && $solv_person->getMember() > 0) {
181                $member_person_ids[$person_id] = true;
182            }
183        }
184        $num_solv_participations = 0;
185        foreach ($solv_event_repo->getSolvEventsForYear($year) as $solv_event) {
186            if ($solv_event->getDate()->format('Y-m') !== $month_key) {
187                continue;
188            }
189            foreach ($solv_result_repo->findBy(['event' => $solv_event->getSolvUid()]) as $solv_result) {
190                if (isset($member_person_ids[$solv_result->getPerson()])) {
191                    $num_solv_participations++;
192                }
193            }
194        }
195        // Website-Aktivität.
196        $num_news = count($news_repo->matching($this->getMonthCriteria('published_date', $start, $next)));
197
198        $values = [
199            'num_users' => (float) $num_users,
200            'num_members' => (float) $num_members,
201            'num_member_users' => (float) $num_member_users,
202            'num_termine' => (float) $num_termine,
203            'num_solv_participations' => (float) $num_solv_participations,
204            'avg_training_participants' => (float) $avg_training_participants,
205            'num_news' => (float) $num_news,
206        ];
207
208        $measurement_repo = $this->entityManager()->getRepository(Measurement::class);
209        foreach (self::SNAPSHOT_NAMES as $name) {
210            $measurement_repo->set($name, $year, $month, $values[$name], self::CALCULATION_VERSION);
211        }
212        foreach (self::EVENT_NAMES as $name) {
213            if ($is_current_month) {
214                // The current month is still in progress: it doesn't have a value for these yet,
215                // so a stale row of an earlier calculation must not linger around.
216                $measurement_repo->remove($name, $year, $month);
217            } else {
218                $measurement_repo->set($name, $year, $month, $values[$name], self::CALCULATION_VERSION);
219            }
220        }
221
222        $this->logAndOutput("Updated measurements for {$month_key}");
223    }
224
225    private function getMonthCriteria(string $date_field, \DateTime $start, \DateTime $end): Criteria {
226        return Criteria::create()
227            ->where(Criteria::expr()->andX(
228                // Entities which were deleted later still count for past months.
229                Criteria::expr()->orX(
230                    Criteria::expr()->isNull('deleted_at'),
231                    Criteria::expr()->gte('deleted_at', $start),
232                ),
233                Criteria::expr()->gte($date_field, $start),
234                Criteria::expr()->lt($date_field, $end),
235            ))
236            ->setFirstResult(0)
237            ->setMaxResults(1000000)
238        ;
239    }
240
241    /** @return array{0: \DateTime, 1: \DateTime} */
242    private function getMonthRange(int $year, int $month): array {
243        $month_start = new \DateTime(sprintf('%04d-%02d-01 00:00:00', $year, $month));
244        return [$month_start, (clone $month_start)->modify('+1 month')];
245    }
246}