Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
76.72% covered (warning)
76.72%
145 / 189
40.00% covered (danger)
40.00%
4 / 10
CRAP
0.00% covered (danger)
0.00%
0 / 1
UpdateMeasurementsCommand
76.72% covered (warning)
76.72%
145 / 189
40.00% covered (danger)
40.00%
4 / 10
123.64
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
73.53% covered (warning)
73.53%
75 / 102
0.00% covered (danger)
0.00%
0 / 1
67.21
 findSektionForMember
45.45% covered (danger)
45.45%
5 / 11
0.00% covered (danger)
0.00%
0 / 1
9.06
 splitWords
100.00% covered (success)
100.00%
3 / 3
100.00% covered (success)
100.00%
1 / 1
2
 getSektionByWord
95.24% covered (success)
95.24%
20 / 21
0.00% covered (danger)
0.00%
0 / 1
7
 getSektionRoles
88.89% covered (warning)
88.89%
8 / 9
0.00% covered (danger)
0.00%
0 / 1
2.01
 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\Roles\Role;
12use Olz\Entity\SolvEvent;
13use Olz\Entity\SolvPerson;
14use Olz\Entity\SolvResult;
15use Olz\Entity\Termine\Termin;
16use Olz\Entity\Termine\TerminReaction;
17use Olz\Entity\Users\User;
18use Olz\Repository\Roles\PredefinedRole;
19use Symfony\Component\Console\Attribute\AsCommand;
20use Symfony\Component\Console\Command\Command;
21use Symfony\Component\Console\Input\InputInterface;
22use Symfony\Component\Console\Output\OutputInterface;
23
24#[AsCommand(name: 'olz:update-measurements')]
25class UpdateMeasurementsCommand extends OlzCommand {
26    use MembersUtilsTrait;
27
28    /** Bump to trigger a backfill whenever the measurement calculation changes. */
29    public const CALCULATION_VERSION = 4;
30
31    private const EARLIEST_YEAR = 2006;
32    private const EARLIEST_MONTH = 1;
33
34    /** How far back a login counts as "active" for `num_active_users`. */
35    private const ACTIVE_USER_WINDOW_DAYS = 30;
36
37    /** Metrics which describe a state and can be calculated for any month. */
38    private const SNAPSHOT_NAMES = [
39        'num_users',
40        'num_members',
41        'num_member_users',
42        'num_termine',
43    ];
44
45    /** Metrics which accumulate during a month; meaningless for the current (incomplete) month. */
46    private const EVENT_NAMES = [
47        'num_solv_participations',
48        'avg_training_participants',
49        'num_news',
50    ];
51
52    /** Metrics which can only be calculated for the current point in time; no backfill possible. */
53    private const POINT_NAMES = [
54        'num_active_users',
55    ];
56
57    /** @return array<string> */
58    protected function getAllowedAppEnvs(): array {
59        return ['dev', 'test', 'staging', 'prod'];
60    }
61
62    protected function handle(InputInterface $input, OutputInterface $output): int {
63        $year = intval($this->dateUtils()->getCurrentDateInFormat('Y'));
64        $month = intval($this->dateUtils()->getCurrentDateInFormat('m'));
65        $prev_year = $month > 1 ? $year : $year - 1;
66        $prev_month = $month > 1 ? $month - 1 : 12;
67
68        $this->updateMeasurementsForMonth($prev_year, $prev_month);
69        $this->updateMeasurementsForMonth($year, $month);
70
71        $measurement_repo = $this->entityManager()->getRepository(Measurement::class);
72        // Anything not calculated with the current version is stale.
73        // Only backfillable metrics are considered.
74        $stale_criteria = Criteria::create()
75            ->where(Criteria::expr()->andX(
76                Criteria::expr()->in('name', array_merge(self::SNAPSHOT_NAMES, self::EVENT_NAMES)),
77                Criteria::expr()->neq('version', self::CALCULATION_VERSION),
78            ))
79            ->setFirstResult(0)
80            ->setMaxResults(1)
81        ;
82        $is_stale = count($measurement_repo->matching($stale_criteria)) > 0;
83        if ($is_stale) {
84            $this->backfill(self::EARLIEST_YEAR, self::EARLIEST_MONTH, $year, $month);
85        }
86
87        $this->entityManager()->flush();
88        return Command::SUCCESS;
89    }
90
91    private function backfill(int $from_year, int $from_month, int $to_year, int $to_month): void {
92        $year = $from_year;
93        $month = $from_month;
94        while ($year < $to_year || ($year === $to_year && $month <= $to_month)) {
95            $this->updateMeasurementsForMonth($year, $month);
96            if ($month >= 12) {
97                $month = 1;
98                $year++;
99            } else {
100                $month++;
101            }
102        }
103    }
104
105    private function updateMeasurementsForMonth(int $year, int $month): void {
106        [$start, $next] = $this->getMonthRange($year, $month);
107        $month_key = sprintf('%04d-%02d', $year, $month);
108        // Snapshot metrics describe a state: they are evaluated at the middle of the month.
109        $at = new \DateTime(sprintf('%04d-%02d-15 12:00:00', $year, $month));
110        $now = new \DateTime($this->dateUtils()->getIsoNow());
111        $current_year = intval($this->dateUtils()->getCurrentDateInFormat('Y'));
112        $current_month = intval($this->dateUtils()->getCurrentDateInFormat('m'));
113        $is_current_month = ($year === $current_year && $month === $current_month);
114
115        $user_repo = $this->entityManager()->getRepository(User::class);
116        $member_repo = $this->entityManager()->getRepository(Member::class);
117        $termin_repo = $this->entityManager()->getRepository(Termin::class);
118        $termin_reaction_repo = $this->entityManager()->getRepository(TerminReaction::class);
119        $news_repo = $this->entityManager()->getRepository(NewsEntry::class);
120        $solv_event_repo = $this->entityManager()->getRepository(SolvEvent::class);
121        $solv_person_repo = $this->entityManager()->getRepository(SolvPerson::class);
122        $solv_result_repo = $this->entityManager()->getRepository(SolvResult::class);
123        $sektion_by_word = $is_current_month ? $this->getSektionByWord() : [];
124
125        // Benutzer & Mitglieder. Entities which were deleted later still count for the
126        // months before their deletion, which is why there is no `deleted_at` filter.
127        $active_cutoff = (clone $now)->modify('-'.self::ACTIVE_USER_WINDOW_DAYS.' days');
128        $num_users = 0;
129        $num_member_users = 0;
130        $num_active_users = 0;
131        foreach ($user_repo->findAll() as $user) {
132            // Only "real" accounts count: users with a password, and child accounts.
133            if (!$user->getPasswordHash() && $user->getParentUserId() === null) {
134                continue;
135            }
136            $user_existed = $user->exists($at);
137            if ($user_existed) {
138                $num_users++;
139            }
140            if ($user_existed && $user->isMember($at)) {
141                $num_member_users++;
142            }
143            if ($is_current_month && $user->exists($now)) {
144                $last_login_at = $user->getLastLoginAt();
145                if ($last_login_at !== null && $last_login_at >= $active_cutoff) {
146                    $num_active_users++;
147                }
148            }
149        }
150
151        // Mitglieder: `Eintritt` / `Austritt` from the member data.
152
153        $num_members = 0;
154        $num_members_by_sektion = [];
155        foreach ($member_repo->findAll() as $member) {
156            if ($this->membersUtils()->isMemberActiveAt($member, $at)) {
157                $num_members++;
158                $sektion = $this->findSektionForMember($member, $sektion_by_word);
159                if ($sektion !== null) {
160                    $name = substr("num_members_{$sektion}", 0, 31);
161                    $num_members_by_sektion[$name] ??= 0;
162                    $num_members_by_sektion[$name]++;
163                }
164            }
165        }
166
167        // Termine.
168        $termine = [...$termin_repo->matching($this->getMonthCriteria('start_date', $start, $next))];
169        $num_termine = count($termine);
170
171        // Trainings (Kartentrainings): Durchschnitt Teilnehmer pro Training.
172        $training_termin_ids = [];
173        foreach ($termine as $termin) {
174            foreach ($termin->getLabels() as $label) {
175                if ($label->getIdent() === 'training') {
176                    $termin_id = $termin->getId();
177                    if ($termin_id !== null) {
178                        $training_termin_ids[] = $termin_id;
179                    }
180                    break;
181                }
182            }
183        }
184        $num_trainings = count($training_termin_ids);
185        $sum_training_participants = 0;
186        foreach ($training_termin_ids as $termin_id) {
187            $user_ids = [];
188            foreach ($termin_reaction_repo->findBy(['termin' => $termin_id]) as $reaction) {
189                if (in_array($reaction->getEmoji(), TerminReaction::ACCEPT_EMOJIS, true)) {
190                    $user_id = $reaction->getUser()->getId();
191                    if ($user_id !== null) {
192                        $user_ids[$user_id] = true;
193                    }
194                }
195            }
196            $sum_training_participants += count($user_ids);
197        }
198        $avg_training_participants = $num_trainings > 0
199            ? $sum_training_participants / $num_trainings
200            : 0.0;
201
202        // SOLV-Anlässe: Teilnahmen unserer Mitglieder (solv_people.member).
203        $member_person_ids = [];
204        foreach ($solv_person_repo->findBy([]) as $solv_person) {
205            $person_id = $solv_person->getId();
206            if ($person_id !== null && $solv_person->getMember() > 0) {
207                $member_person_ids[$person_id] = true;
208            }
209        }
210        $num_solv_participations = 0;
211        foreach ($solv_event_repo->getSolvEventsForYear($year) as $solv_event) {
212            if ($solv_event->getDate()->format('Y-m') !== $month_key) {
213                continue;
214            }
215            foreach ($solv_result_repo->findBy(['event' => $solv_event->getSolvUid()]) as $solv_result) {
216                if (isset($member_person_ids[$solv_result->getPerson()])) {
217                    $num_solv_participations++;
218                }
219            }
220        }
221        // Website-Aktivität.
222        $num_news = count($news_repo->matching($this->getMonthCriteria('published_date', $start, $next)));
223
224        $values = [
225            'num_users' => (float) $num_users,
226            'num_members' => (float) $num_members,
227            'num_member_users' => (float) $num_member_users,
228            'num_termine' => (float) $num_termine,
229            'num_solv_participations' => (float) $num_solv_participations,
230            'avg_training_participants' => (float) $avg_training_participants,
231            'num_news' => (float) $num_news,
232            'num_active_users' => (float) $num_active_users,
233        ];
234
235        $measurement_repo = $this->entityManager()->getRepository(Measurement::class);
236        foreach (self::SNAPSHOT_NAMES as $name) {
237            $measurement_repo->set($name, $year, $month, $values[$name], self::CALCULATION_VERSION);
238        }
239        foreach (self::EVENT_NAMES as $name) {
240            if ($is_current_month) {
241                // The current month is still in progress: it doesn't have a value for these yet,
242                // so a stale row of an earlier calculation must not linger around.
243                $measurement_repo->remove($name, $year, $month);
244            } else {
245                $measurement_repo->set($name, $year, $month, $values[$name], self::CALCULATION_VERSION);
246            }
247        }
248        foreach (self::POINT_NAMES as $name) {
249            // Past months are never backfilled: only the current month has a value.
250            if ($is_current_month) {
251                $measurement_repo->set($name, $year, $month, $values[$name], self::CALCULATION_VERSION);
252            }
253        }
254        foreach ($num_members_by_sektion as $name => $value) {
255            // Past months are never backfilled: only the current month has a value.
256            if ($is_current_month) {
257                $measurement_repo->set($name, $year, $month, $value, self::CALCULATION_VERSION);
258            }
259        }
260
261        $this->logAndOutput("Updated measurements for {$month_key}");
262    }
263
264    /** @param array<string, ?string> $sektion_by_word */
265    protected function findSektionForMember(Member $member, array $sektion_by_word): ?string {
266        $member_data = $this->membersUtils()->getMemberData($member);
267        $city = $this->membersUtils()->getMemberCity($member_data);
268        $name = null;
269        foreach ($this->splitWords($city) as $city_word) {
270            $new_name = $sektion_by_word[$city_word] ?? null;
271            if ($new_name === null) {
272                continue;
273            }
274            if ($name != null && $name !== $new_name) {
275                return null; // inconsistent
276            }
277            $name = $new_name;
278        }
279        return $name;
280    }
281
282    /**
283     * Splits a text into lowercase words. Anything that is not a letter or a digit is
284     * treated as a separator, so e.g. "Zürich," and "Zürich" both yield "zürich". The
285     * `\p{L}` class (together with the `u` modifier) keeps German umlauts intact.
286     *
287     * @return array<string>
288     */
289    private function splitWords(?string $text): array {
290        $lowercase_text = mb_strtolower($text ?? '');
291        $words = preg_split('/[^\p{L}\p{N}]+/u', $lowercase_text, -1, PREG_SPLIT_NO_EMPTY);
292        return $words === false ? [] : $words;
293    }
294
295    /** @return array<string, ?string> */
296    protected function getSektionByWord(): array {
297        $name_by_word = [];
298        foreach ($this->getSektionRoles() as $sektion_role) {
299            $text = implode(' ', [
300                $sektion_role->getUsername(),
301                $sektion_role->getOldUsername(),
302                $sektion_role->getName(),
303                $sektion_role->getDescription(),
304            ]);
305            $word_counts = [];
306            foreach ($this->splitWords($text) as $word) {
307                $word_counts[$word] ??= 0;
308                $word_counts[$word]++;
309            }
310            foreach ($word_counts as $word => $count) {
311                if ($count <= 1) {
312                    continue;
313                }
314                if (array_key_exists($word, $name_by_word)) {
315                    $name_by_word[$word] = null;
316                } else {
317                    $username = $sektion_role->getUsername();
318                    $has_match = preg_match('/^sektion-(.+)$/i', $username);
319                    $name_by_word[$word] = $has_match ? substr($username, 8) : $username;
320                }
321            }
322        }
323        return $name_by_word;
324    }
325
326    /** @return array<Role> */
327    private function getSektionRoles(): array {
328        $role_repo = $this->entityManager()->getRepository(Role::class);
329        $sektionen_role = $role_repo->getPredefinedRole(PredefinedRole::Sektionen);
330        if (!$sektionen_role) {
331            return [];
332        }
333        $sektion_roles = $role_repo->findBy([
334            'parent_role' => $sektionen_role->getId(),
335            'deleted_at' => null,
336        ], ['position_within_parent' => 'ASC']);
337        return [...$sektion_roles];
338    }
339
340    private function getMonthCriteria(string $date_field, \DateTime $start, \DateTime $end): Criteria {
341        return Criteria::create()
342            ->where(Criteria::expr()->andX(
343                // Entities which were deleted later still count for past months.
344                Criteria::expr()->orX(
345                    Criteria::expr()->isNull('deleted_at'),
346                    Criteria::expr()->gte('deleted_at', $start),
347                ),
348                Criteria::expr()->gte($date_field, $start),
349                Criteria::expr()->lt($date_field, $end),
350            ))
351            ->setFirstResult(0)
352            ->setMaxResults(1000000)
353        ;
354    }
355
356    /** @return array{0: \DateTime, 1: \DateTime} */
357    private function getMonthRange(int $year, int $month): array {
358        $month_start = new \DateTime(sprintf('%04d-%02d-01 00:00:00', $year, $month));
359        return [$month_start, (clone $month_start)->modify('+1 month')];
360    }
361}