Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
76.72% |
145 / 189 |
|
40.00% |
4 / 10 |
CRAP | |
0.00% |
0 / 1 |
| UpdateMeasurementsCommand | |
76.72% |
145 / 189 |
|
40.00% |
4 / 10 |
123.64 | |
0.00% |
0 / 1 |
| getAllowedAppEnvs | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| handle | |
95.00% |
19 / 20 |
|
0.00% |
0 / 1 |
4 | |||
| backfill | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
30 | |||
| updateMeasurementsForMonth | |
73.53% |
75 / 102 |
|
0.00% |
0 / 1 |
67.21 | |||
| findSektionForMember | |
45.45% |
5 / 11 |
|
0.00% |
0 / 1 |
9.06 | |||
| splitWords | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| getSektionByWord | |
95.24% |
20 / 21 |
|
0.00% |
0 / 1 |
7 | |||
| getSektionRoles | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
2.01 | |||
| getMonthCriteria | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
1 | |||
| getMonthRange | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| 1 | <?php |
| 2 | |
| 3 | namespace Olz\Command; |
| 4 | |
| 5 | use Doctrine\Common\Collections\Criteria; |
| 6 | use Olz\Apps\Members\Utils\MembersUtilsTrait; |
| 7 | use Olz\Command\Common\OlzCommand; |
| 8 | use Olz\Entity\Measurement; |
| 9 | use Olz\Entity\Members\Member; |
| 10 | use Olz\Entity\News\NewsEntry; |
| 11 | use Olz\Entity\Roles\Role; |
| 12 | use Olz\Entity\SolvEvent; |
| 13 | use Olz\Entity\SolvPerson; |
| 14 | use Olz\Entity\SolvResult; |
| 15 | use Olz\Entity\Termine\Termin; |
| 16 | use Olz\Entity\Termine\TerminReaction; |
| 17 | use Olz\Entity\Users\User; |
| 18 | use Olz\Repository\Roles\PredefinedRole; |
| 19 | use Symfony\Component\Console\Attribute\AsCommand; |
| 20 | use Symfony\Component\Console\Command\Command; |
| 21 | use Symfony\Component\Console\Input\InputInterface; |
| 22 | use Symfony\Component\Console\Output\OutputInterface; |
| 23 | |
| 24 | #[AsCommand(name: 'olz:update-measurements')] |
| 25 | class 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 | } |