15405

Committed 19 May 2026 04:44PM UTC coverage: 69.555% (-3.4%) from 72.97%

Build # 15405

Build Type

push

circleci

Committed by

edwh

Commit Message

fix(fastlane): correct File.exist? path for modtools Google Play key

Fastlane's CWD is the fastlane/ directory, so File.exist? must use a
bare filename — not 'fastlane/modtools-google-play-api-key.json' which
resolves to fastlane/fastlane/... and is always missing.
Matches the pattern used by the working Freegle beta lane.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

Coverage Stats

10030 of 13466 branches covered (74.48%)

Branch coverage included in aggregate %.

106846 of 154568 relevant lines covered (69.13%)

34.65 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

98.1

/iznik-batch/app/Services/ModNotifService.php

<?php

namespace App\Services;

use App\Models\Group;
use App\Models\Membership;
use App\Models\MessageGroup;
use App\Models\User;
use App\Models\UserEmail;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Computes pending moderation work per moderator and manages notification dedup.
 *
 * Mirrors V1 cron/mod_notifs.php:
 * - For each active Freegle group, find moderators active in the last 90 days.
 * - For each mod, count pending work items filtered by their notification threshold (minage hours).
 * - Skip if minage < 0 (notifications disabled).
 * - Only send if the summary has changed since last notification, or it's been > 24h.
 */
class ModNotifService
{
    // Default notification thresholds (hours old items must be before notifying)
    private const DEFAULT_ACTIVE_MOD_THRESHOLD = 4;

    private const DEFAULT_BACKUP_MOD_THRESHOLD = 12;

    // Moderators inactive for more than this many days are skipped
    private const MAX_INACTIVE_DAYS = 90;

    // Re-send the same summary after this many seconds even if unchanged
    private const RESEND_INTERVAL_SECONDS = 24 * 3600;

    /**
     * Build a pending-work notification for every eligible mod on every active group.
     *
     * Returns an array of notification records:
     * [
     *   'user_id'      => int,
     *   'email'        => string,
     *   'name'         => string,
     *   'text_summary' => string,
     *   'html_summary' => string,
     *   'total'        => int,
     *   'subject'      => string,
     * ]
     */
    public function getNotificationsToSend(): array
    {
        $notifications = [];

        // Per-mod accumulator keyed by user ID so a mod on many groups gets one email.
        $modData = [];

        $groups = Group::activeFreegle()->get();

        foreach ($groups as $group) {
            $mods = Membership::where('groupid', $group->id)
                ->whereIn('role', [Membership::ROLE_MODERATOR, Membership::ROLE_OWNER])
                ->get();

            foreach ($mods as $membership) {
                $modId = $membership->userid;
                $isActive = $membership->isActiveMod();
                $modSettings = $this->getModSettings($modId, $membership, $isActive);

                if ($modSettings['minage'] < 0) {
                    continue;
                }

                // Skip mods who have been inactive for too long
                if (!$this->isModRecentlyActive($modId)) {
                    continue;
                }

                $work = $this->getPendingWork($modId, $group->id, $modSettings['minage']);
                $chatReview = $this->getChatReviewCount($modId, $modSettings['minage']);
                $total = array_sum($work) + $chatReview;

                if ($total === 0) {
                    continue;
                }

                if (!isset($modData[$modId])) {
                    $user = User::find($modId);
                    $email = UserEmail::where('userid', $modId)->where('preferred', 1)->first();

                    if (!$user || !$email) {
                        continue;
                    }

                    $modData[$modId] = [
                        'user_id' => $modId,
                        'email' => $email->email,
                        'name' => $user->displayname ?? $user->fullname ?? 'Moderator',
                        'groups' => [],
                        'chat_review' => 0,
                    ];
                }

                // Use assignment, not accumulation. getChatReviewCount is a global query
                // with no group filter, so it returns the same value on every group
                // iteration for the same mod. Using += would multiply it by group count.
                $modData[$modId]['chat_review'] = $chatReview;

                $nonZeroWork = array_filter($work, fn ($v) => $v > 0);
                if (!empty($nonZeroWork)) {
                    $modData[$modId]['groups'][$group->nameshort] = $nonZeroWork;
                }
            }
        }

        $modtoolsUrl = config('freegle.sites.mod', 'https://modtools.org');
        $settingsUrl = rtrim($modtoolsUrl, '/') . '/modtools/settings';

        foreach ($modData as $modId => $data) {
            $textSummary = $this->buildTextSummary($data['groups'], $data['chat_review'], $settingsUrl);
            $htmlSummary = $this->buildHtmlSummary($data['groups'], $data['chat_review']);

            if (!$this->shouldSend($modId, $textSummary)) {
                continue;
            }

            $total = $data['chat_review'];
            foreach ($data['groups'] as $groupWork) {
                $total += array_sum($groupWork);
            }

            $notifications[] = [
                'user_id' => $modId,
                'email' => $data['email'],
                'name' => $data['name'],
                'text_summary' => $textSummary,
                'html_summary' => $htmlSummary,
                'total' => $total,
                'subject' => "MODERATE: {$total} thing" . ($total === 1 ? '' : 's') . ' to do',
            ];
        }

        return $notifications;
    }

    /**
     * Get notification threshold settings for a moderator.
     */
    public function getModSettings(int $modId, Membership $membership, bool $isActive): array
    {
        $user = User::find($modId);
        $settings = $user ? ($user->settings ?? []) : [];

        $activeMinage = (int) ($settings['modnotifs'] ?? self::DEFAULT_ACTIVE_MOD_THRESHOLD);
        $backupMinage = (int) ($settings['backupmodnotifs'] ?? self::DEFAULT_BACKUP_MOD_THRESHOLD);

        return [
            'minage' => $isActive ? $activeMinage : $backupMinage,
        ];
    }

    /**
     * Check if the moderator has been active (approved a message) in the last 90 days.
     *
     * A value of 0 means they approved something today.
     */
    public function isModRecentlyActive(int $modId): bool
    {
        $row = DB::table('messages_groups')
            ->selectRaw('DATEDIFF(NOW(), MAX(arrival)) AS activeago')
            ->where('approvedby', $modId)
            ->first();

        if (!$row) {
            return false;
        }

        $activeago = $row->activeago;

        // '0' or null means approved today; an integer <= 90 means recently active
        if ($activeago === null) {
            return false;
        }

        if ($activeago == '0' || ($activeago !== null && (int) $activeago <= self::MAX_INACTIVE_DAYS)) {
            return true;
        }

        return false;
    }

    /**
     * Count pending work items for a moderator on a specific group.
     *
     * @param  int       $minage  Hours old items must be before they appear (0 = all items)
     * @return array<string, int>
     */
    public function getPendingWork(int $modId, int $groupId, int $minage): array
    {
        $minageFilter = $minage > 0 ? now()->subHours($minage) : null;
        $now = now();
        $earliest = now()->subDays(31)->startOfDay();

        // Pending messages — exclude messages from deleted users (ModTools UI hides
        // these already, so the email count needs to match).
        $pendingMessages = DB::table('messages')
            ->join('messages_groups', 'messages.id', '=', 'messages_groups.msgid')
            ->join('users', function ($j) {
                $j->on('users.id', '=', 'messages.fromuser')
                    ->whereNull('users.deleted');
            })
            ->where('messages_groups.groupid', $groupId)
            ->where('messages_groups.collection', MessageGroup::COLLECTION_PENDING)
            ->where('messages_groups.deleted', 0)
            ->whereNull('messages.heldby')
            ->whereNull('messages.deleted')
            ->when($minageFilter, fn ($q) => $q->where('messages_groups.arrival', '<', $minageFilter))
            ->count();

        // Pending community events
        $pendingEvents = DB::table('communityevents')
            ->join('communityevents_dates', 'communityevents_dates.eventid', '=', 'communityevents.id')
            ->join('communityevents_groups', 'communityevents_groups.eventid', '=', 'communityevents.id')
            ->where('communityevents_groups.groupid', $groupId)
            ->where('communityevents.pending', 1)
            ->where('communityevents.deleted', 0)
            ->where('communityevents_dates.end', '>=', $now)
            ->when($minageFilter, fn ($q) => $q->where('communityevents.added', '<', $minageFilter))
            ->distinct('communityevents.id')
            ->count('communityevents.id');

        // Pending volunteering
        $pendingVolunteering = DB::table('volunteering')
            ->join('volunteering_groups', 'volunteering_groups.volunteeringid', '=', 'volunteering.id')
            ->leftJoin('volunteering_dates', 'volunteering_dates.volunteeringid', '=', 'volunteering.id')
            ->where('volunteering_groups.groupid', $groupId)
            ->where('volunteering.pending', 1)
            ->where('volunteering.deleted', 0)
            ->where('volunteering.expired', 0)
            ->where(fn ($q) => $q->whereNull('volunteering_dates.applyby')->orWhere('volunteering_dates.applyby', '>=', $now))
            ->where(fn ($q) => $q->whereNull('volunteering_dates.end')->orWhere('volunteering_dates.end', '>=', $now))
            ->when($minageFilter, fn ($q) => $q->where('volunteering.added', '<', $minageFilter))
            ->distinct('volunteering.id')
            ->count('volunteering.id');

        // Members to review
        $membersToReview = DB::table('memberships')
            ->where('groupid', $groupId)
            ->whereNotNull('reviewrequestedat')
            ->when($minageFilter, fn ($q) => $q->where('reviewrequestedat', '>=', $minageFilter))
            ->where(fn ($q) => $q->whereNull('reviewedat')
                ->orWhereRaw('DATE(reviewedat) < DATE_SUB(NOW(), INTERVAL 31 DAY)'))
            ->count();

        // Pending admins
        $pendingAdmins = DB::table('admins')
            ->where('groupid', $groupId)
            ->whereNull('complete')
            ->where('pending', 1)
            ->whereNull('heldby')
            ->where('created', '>=', $earliest)
            ->distinct('id')
            ->count('id');

        return [
            'Pending Messages' => $pendingMessages,
            'Pending Community Events' => $pendingEvents,
            'Pending Volunteering Opportunities' => $pendingVolunteering,
            'Members to Review' => $membersToReview,
            'Pending Admins' => $pendingAdmins,
        ];
    }

    /**
     * Count chat messages awaiting review by this moderator.
     *
     * Mirrors V1 ChatMessage::getReviewCount: a chat counts for this mod when
     * the recipient (the chat member who isn't the message sender) is a
     * member of a Freegle group the mod actively moderates, the chat hasn't
     * been rejected, and the message hasn't been held.
     *
     * Without these filters the query returned a global count across all of
     * Freegle, so every mod was told about every chat in the queue regardless
     * of whether they could see it in ModTools.
     */
    public function getChatReviewCount(int $modId, int $minage): int
    {
        // Active moderatorships on Freegle groups. Mirrors V1 activeModForGroup:
        // settings.active wins if present; otherwise fall back to legacy
        // settings.showmessages; otherwise default to active. Backup mods
        // (active = false, or active missing and showmessages = false) don't
        // get the chat-review queue.
        $modGroupIds = DB::table('memberships')
            ->join('groups', 'groups.id', '=', 'memberships.groupid')
            ->where('memberships.userid', $modId)
            ->whereIn('memberships.role', [Membership::ROLE_MODERATOR, Membership::ROLE_OWNER])
            ->where('groups.type', 'Freegle')
            ->where(function ($q) {
                $q->whereNull('memberships.settings')
                    ->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.active') = true")
                    ->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.active') = 1")
                    ->orWhere(function ($q2) {
                        $q2->whereRaw("JSON_EXTRACT(memberships.settings, '$.active') IS NULL")
                            ->where(function ($q3) {
                                $q3->whereRaw("JSON_EXTRACT(memberships.settings, '$.showmessages') IS NULL")
                                    ->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.showmessages') = true")
                                    ->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.showmessages') = 1");
                            });
                    });
            })
            ->pluck('memberships.groupid')
            ->all();

        if (empty($modGroupIds)) {
            return 0;
        }

        $minageFilter = $minage > 0 ? now()->subHours($minage)->format('Y-m-d H:i:s') : null;
        $earliest = now()->subDays(31)->startOfDay()->format('Y-m-d H:i:s');

        $placeholders = implode(',', array_fill(0, count($modGroupIds), '?'));

        $sql = "SELECT COUNT(DISTINCT chat_messages.id) AS count
                FROM chat_messages
                LEFT JOIN chat_messages_held ON chat_messages_held.msgid = chat_messages.id
                INNER JOIN chat_rooms ON chat_rooms.id = chat_messages.chatid
                INNER JOIN memberships
                  ON memberships.userid = (CASE WHEN chat_messages.userid = chat_rooms.user1 THEN chat_rooms.user2 ELSE chat_rooms.user1 END)
                  AND memberships.groupid IN ($placeholders)
                INNER JOIN `groups` ON memberships.groupid = groups.id AND groups.type = 'Freegle'
                WHERE chat_messages.reviewrequired = 1
                  AND chat_messages.reviewrejected = 0
                  AND chat_messages_held.userid IS NULL
                  AND chat_messages.date > ?";

        $bindings = $modGroupIds;
        $bindings[] = $earliest;

        if ($minageFilter !== null) {
            $sql .= ' AND chat_messages.date <= ?';
            $bindings[] = $minageFilter;
        }

        $row = DB::selectOne($sql, $bindings);

        return (int) ($row->count ?? 0);
    }

    /**
     * Build plain-text summary of pending work.
     */
    public function buildTextSummary(array $groupWork, int $chatReview, string $settingsUrl): string
    {
        $text = "There's stuff to do on ModTools:\r\n\r\n";

        if ($chatReview > 0) {
            $text .= "You have {$chatReview} chat message" . ($chatReview > 1 ? 's' : '') . " to review.\r\n\r\n";
        }

        foreach ($groupWork as $groupName => $work) {
            $text .= "\r\n{$groupName}\r\n:";
            foreach ($work as $key => $val) {
                if ($val > 0) {
                    $text .= "{$key}: {$val}\r\n";
                }
            }
        }

        $text .= "\r\nYou can control how often you get these mails or turn them off entirely from {$settingsUrl}\r\n";

        return $text;
    }

    /**
     * Build HTML summary for MJML template.
     */
    public function buildHtmlSummary(array $groupWork, int $chatReview): string
    {
        $html = '';

        if ($chatReview > 0) {
            $html .= "<p>You have <b>{$chatReview}</b> chat message" . ($chatReview > 1 ? 's' : '') . ' to review.</p>';
        }

        foreach ($groupWork as $groupName => $work) {
            $html .= "<p>{$groupName}</p><ul>";
            foreach ($work as $key => $val) {
                if ($val > 0) {
                    $html .= "<li>{$key}: <b>{$val}</b></li>";
                }
            }
            $html .= '</ul>';
        }

        return $html;
    }

    /**
     * Check whether to send a notification for this moderator.
     *
     * Sends if: no previous record, summary has changed, or it's been > 24h since last send.
     */
    public function shouldSend(int $modId, string $textSummary): bool
    {
        $record = DB::table('modnotifs')->where('userid', $modId)->first();

        if (!$record) {
            return true;
        }

        if ($record->data !== $textSummary) {
            return true;
        }

        $age = Carbon::parse($record->timestamp)->diffInSeconds(now());

        return $age > self::RESEND_INTERVAL_SECONDS;
    }

    /**
     * Record that a notification was sent.
     */
    public function recordSent(int $modId, string $textSummary): void
    {
        DB::table('modnotifs')->updateOrInsert(
            ['userid' => $modId],
            ['data' => $textSummary, 'timestamp' => now()]
        );
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use App\Models\Group;
6	use App\Models\Membership;
7	use App\Models\MessageGroup;
8	use App\Models\User;
9	use App\Models\UserEmail;
10	use Carbon\Carbon;
11	use Illuminate\Support\Facades\DB;
12	use Illuminate\Support\Facades\Log;
13
14	/**
15	* Computes pending moderation work per moderator and manages notification dedup.
16	*
17	* Mirrors V1 cron/mod_notifs.php:
18	* - For each active Freegle group, find moderators active in the last 90 days.
19	* - For each mod, count pending work items filtered by their notification threshold (minage hours).
20	* - Skip if minage < 0 (notifications disabled).
21	* - Only send if the summary has changed since last notification, or it's been > 24h.
22	*/
23	class ModNotifService
24	{
25	// Default notification thresholds (hours old items must be before notifying)
26	private const DEFAULT_ACTIVE_MOD_THRESHOLD = 4;
27
28	private const DEFAULT_BACKUP_MOD_THRESHOLD = 12;
29
30	// Moderators inactive for more than this many days are skipped
31	private const MAX_INACTIVE_DAYS = 90;
32
33	// Re-send the same summary after this many seconds even if unchanged
34	private const RESEND_INTERVAL_SECONDS = 24 * 3600;
35
36	/**
37	* Build a pending-work notification for every eligible mod on every active group.
38	*
39	* Returns an array of notification records:
40	* [
41	* 'user_id' => int,
42	* 'email' => string,
43	* 'name' => string,
44	* 'text_summary' => string,
45	* 'html_summary' => string,
46	* 'total' => int,
47	* 'subject' => string,
48	* ]
49	*/
50	public function getNotificationsToSend(): array	4✔
51	{
52	$notifications = [];	4✔
53
54	// Per-mod accumulator keyed by user ID so a mod on many groups gets one email.
55	$modData = [];	4✔
56
57	$groups = Group::activeFreegle()->get();	4✔
58
59	foreach ($groups as $group) {	4✔
60	$mods = Membership::where('groupid', $group->id)	4✔
61	->whereIn('role', [Membership::ROLE_MODERATOR, Membership::ROLE_OWNER])	4✔
62	->get();	4✔
63
64	foreach ($mods as $membership) {	4✔
65	$modId = $membership->userid;	4✔
66	$isActive = $membership->isActiveMod();	4✔
67	$modSettings = $this->getModSettings($modId, $membership, $isActive);	4✔
68
69	if ($modSettings['minage'] < 0) {	4✔
70	continue;	1✔
71	}
72
73	// Skip mods who have been inactive for too long
74	if (!$this->isModRecentlyActive($modId)) {	4✔
75	continue;	4✔
76	}
77
78	$work = $this->getPendingWork($modId, $group->id, $modSettings['minage']);	2✔
79	$chatReview = $this->getChatReviewCount($modId, $modSettings['minage']);	2✔
80	$total = array_sum($work) + $chatReview;	2✔
81
82	if ($total === 0) {	2✔
83	continue;	×
84	}
85
86	if (!isset($modData[$modId])) {	2✔
87	$user = User::find($modId);	2✔
88	$email = UserEmail::where('userid', $modId)->where('preferred', 1)->first();	2✔
89
90	if (!$user \|\| !$email) {	2✔
91	continue;	×
92	}
93
94	$modData[$modId] = [	2✔
95	'user_id' => $modId,	2✔
96	'email' => $email->email,	2✔
97	'name' => $user->displayname ?? $user->fullname ?? 'Moderator',	2✔
98	'groups' => [],	2✔
99	'chat_review' => 0,	2✔
100	];	2✔
101	}
102
103	// Use assignment, not accumulation. getChatReviewCount is a global query
104	// with no group filter, so it returns the same value on every group
105	// iteration for the same mod. Using += would multiply it by group count.
106	$modData[$modId]['chat_review'] = $chatReview;	2✔
107
108	$nonZeroWork = array_filter($work, fn ($v) => $v > 0);	2✔
109	if (!empty($nonZeroWork)) {	2✔
110	$modData[$modId]['groups'][$group->nameshort] = $nonZeroWork;	2✔
111	}
112	}
113	}
114
115	$modtoolsUrl = config('freegle.sites.mod', 'https://modtools.org');	4✔
116	$settingsUrl = rtrim($modtoolsUrl, '/') . '/modtools/settings';	4✔
117
118	foreach ($modData as $modId => $data) {	4✔
119	$textSummary = $this->buildTextSummary($data['groups'], $data['chat_review'], $settingsUrl);	2✔
120	$htmlSummary = $this->buildHtmlSummary($data['groups'], $data['chat_review']);	2✔
121
122	if (!$this->shouldSend($modId, $textSummary)) {	2✔
123	continue;	×
124	}
125
126	$total = $data['chat_review'];	2✔
127	foreach ($data['groups'] as $groupWork) {	2✔
128	$total += array_sum($groupWork);	2✔
129	}
130
131	$notifications[] = [	2✔
132	'user_id' => $modId,	2✔
133	'email' => $data['email'],	2✔
134	'name' => $data['name'],	2✔
135	'text_summary' => $textSummary,	2✔
136	'html_summary' => $htmlSummary,	2✔
137	'total' => $total,	2✔
138	'subject' => "MODERATE: {$total} thing" . ($total === 1 ? '' : 's') . ' to do',	2✔
139	];	2✔
140	}
141
142	return $notifications;	4✔
143	}
144
145	/**
146	* Get notification threshold settings for a moderator.
147	*/
148	public function getModSettings(int $modId, Membership $membership, bool $isActive): array	8✔
149	{
150	$user = User::find($modId);	8✔
151	$settings = $user ? ($user->settings ?? []) : [];	8✔
152
153	$activeMinage = (int) ($settings['modnotifs'] ?? self::DEFAULT_ACTIVE_MOD_THRESHOLD);	8✔
154	$backupMinage = (int) ($settings['backupmodnotifs'] ?? self::DEFAULT_BACKUP_MOD_THRESHOLD);	8✔
155
156	return [	8✔
157	'minage' => $isActive ? $activeMinage : $backupMinage,	8✔
158	];	8✔
159	}
160
161	/**
162	* Check if the moderator has been active (approved a message) in the last 90 days.
163	*
164	* A value of 0 means they approved something today.
165	*/
166	public function isModRecentlyActive(int $modId): bool	7✔
167	{
168	$row = DB::table('messages_groups')	7✔
169	->selectRaw('DATEDIFF(NOW(), MAX(arrival)) AS activeago')	7✔
170	->where('approvedby', $modId)	7✔
171	->first();	7✔
172
173	if (!$row) {	7✔
174	return false;	×
175	}
176
177	$activeago = $row->activeago;	7✔
178
179	// '0' or null means approved today; an integer <= 90 means recently active
180	if ($activeago === null) {	7✔
181	return false;	5✔
182	}
183
184	if ($activeago == '0' \|\| ($activeago !== null && (int) $activeago <= self::MAX_INACTIVE_DAYS)) {	4✔
185	return true;	3✔
186	}
187
188	return false;	1✔
189	}
190
191	/**
192	* Count pending work items for a moderator on a specific group.
193	*
194	* @param int $minage Hours old items must be before they appear (0 = all items)
195	* @return array<string, int>
196	*/
197	public function getPendingWork(int $modId, int $groupId, int $minage): array	9✔
198	{
199	$minageFilter = $minage > 0 ? now()->subHours($minage) : null;	9✔
200	$now = now();	9✔
201	$earliest = now()->subDays(31)->startOfDay();	9✔
202
203	// Pending messages — exclude messages from deleted users (ModTools UI hides
204	// these already, so the email count needs to match).
205	$pendingMessages = DB::table('messages')	9✔
206	->join('messages_groups', 'messages.id', '=', 'messages_groups.msgid')	9✔
207	->join('users', function ($j) {	9✔
208	$j->on('users.id', '=', 'messages.fromuser')	9✔
209	->whereNull('users.deleted');	9✔
210	})	9✔
211	->where('messages_groups.groupid', $groupId)	9✔
212	->where('messages_groups.collection', MessageGroup::COLLECTION_PENDING)	9✔
213	->where('messages_groups.deleted', 0)	9✔
214	->whereNull('messages.heldby')	9✔
215	->whereNull('messages.deleted')	9✔
216	->when($minageFilter, fn ($q) => $q->where('messages_groups.arrival', '<', $minageFilter))	9✔
217	->count();	9✔
218
219	// Pending community events
220	$pendingEvents = DB::table('communityevents')	9✔
221	->join('communityevents_dates', 'communityevents_dates.eventid', '=', 'communityevents.id')	9✔
222	->join('communityevents_groups', 'communityevents_groups.eventid', '=', 'communityevents.id')	9✔
223	->where('communityevents_groups.groupid', $groupId)	9✔
224	->where('communityevents.pending', 1)	9✔
225	->where('communityevents.deleted', 0)	9✔
226	->where('communityevents_dates.end', '>=', $now)	9✔
227	->when($minageFilter, fn ($q) => $q->where('communityevents.added', '<', $minageFilter))	9✔
228	->distinct('communityevents.id')	9✔
229	->count('communityevents.id');	9✔
230
231	// Pending volunteering
232	$pendingVolunteering = DB::table('volunteering')	9✔
233	->join('volunteering_groups', 'volunteering_groups.volunteeringid', '=', 'volunteering.id')	9✔
234	->leftJoin('volunteering_dates', 'volunteering_dates.volunteeringid', '=', 'volunteering.id')	9✔
235	->where('volunteering_groups.groupid', $groupId)	9✔
236	->where('volunteering.pending', 1)	9✔
237	->where('volunteering.deleted', 0)	9✔
238	->where('volunteering.expired', 0)	9✔
239	->where(fn ($q) => $q->whereNull('volunteering_dates.applyby')->orWhere('volunteering_dates.applyby', '>=', $now))	9✔
240	->where(fn ($q) => $q->whereNull('volunteering_dates.end')->orWhere('volunteering_dates.end', '>=', $now))	9✔
241	->when($minageFilter, fn ($q) => $q->where('volunteering.added', '<', $minageFilter))	9✔
242	->distinct('volunteering.id')	9✔
243	->count('volunteering.id');	9✔
244
245	// Members to review
246	$membersToReview = DB::table('memberships')	9✔
247	->where('groupid', $groupId)	9✔
248	->whereNotNull('reviewrequestedat')	9✔
249	->when($minageFilter, fn ($q) => $q->where('reviewrequestedat', '>=', $minageFilter))	9✔
250	->where(fn ($q) => $q->whereNull('reviewedat')	9✔
251	->orWhereRaw('DATE(reviewedat) < DATE_SUB(NOW(), INTERVAL 31 DAY)'))	9✔
252	->count();	9✔
253
254	// Pending admins
255	$pendingAdmins = DB::table('admins')	9✔
256	->where('groupid', $groupId)	9✔
257	->whereNull('complete')	9✔
258	->where('pending', 1)	9✔
259	->whereNull('heldby')	9✔
260	->where('created', '>=', $earliest)	9✔
261	->distinct('id')	9✔
262	->count('id');	9✔
263
264	return [	9✔
265	'Pending Messages' => $pendingMessages,	9✔
266	'Pending Community Events' => $pendingEvents,	9✔
267	'Pending Volunteering Opportunities' => $pendingVolunteering,	9✔
268	'Members to Review' => $membersToReview,	9✔
269	'Pending Admins' => $pendingAdmins,	9✔
270	];	9✔
271	}
272
273	/**
274	* Count chat messages awaiting review by this moderator.
275	*
276	* Mirrors V1 ChatMessage::getReviewCount: a chat counts for this mod when
277	* the recipient (the chat member who isn't the message sender) is a
278	* member of a Freegle group the mod actively moderates, the chat hasn't
279	* been rejected, and the message hasn't been held.
280	*
281	* Without these filters the query returned a global count across all of
282	* Freegle, so every mod was told about every chat in the queue regardless
283	* of whether they could see it in ModTools.
284	*/
285	public function getChatReviewCount(int $modId, int $minage): int	10✔
286	{
287	// Active moderatorships on Freegle groups. Mirrors V1 activeModForGroup:
288	// settings.active wins if present; otherwise fall back to legacy
289	// settings.showmessages; otherwise default to active. Backup mods
290	// (active = false, or active missing and showmessages = false) don't
291	// get the chat-review queue.
292	$modGroupIds = DB::table('memberships')	10✔
293	->join('groups', 'groups.id', '=', 'memberships.groupid')	10✔
294	->where('memberships.userid', $modId)	10✔
295	->whereIn('memberships.role', [Membership::ROLE_MODERATOR, Membership::ROLE_OWNER])	10✔
296	->where('groups.type', 'Freegle')	10✔
297	->where(function ($q) {	10✔
298	$q->whereNull('memberships.settings')	10✔
299	->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.active') = true")	10✔
300	->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.active') = 1")	10✔
301	->orWhere(function ($q2) {	10✔
302	$q2->whereRaw("JSON_EXTRACT(memberships.settings, '$.active') IS NULL")	10✔
303	->where(function ($q3) {	10✔
304	$q3->whereRaw("JSON_EXTRACT(memberships.settings, '$.showmessages') IS NULL")	10✔
305	->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.showmessages') = true")	10✔
306	->orWhereRaw("JSON_EXTRACT(memberships.settings, '$.showmessages') = 1");	10✔
307	});	10✔
308	});	10✔
309	})	10✔
310	->pluck('memberships.groupid')	10✔
311	->all();	10✔
312
313	if (empty($modGroupIds)) {	10✔
314	return 0;	2✔
315	}
316
317	$minageFilter = $minage > 0 ? now()->subHours($minage)->format('Y-m-d H:i:s') : null;	8✔
318	$earliest = now()->subDays(31)->startOfDay()->format('Y-m-d H:i:s');	8✔
319
320	$placeholders = implode(',', array_fill(0, count($modGroupIds), '?'));	8✔
321
322	$sql = "SELECT COUNT(DISTINCT chat_messages.id) AS count	8✔
323	FROM chat_messages
324	LEFT JOIN chat_messages_held ON chat_messages_held.msgid = chat_messages.id
325	INNER JOIN chat_rooms ON chat_rooms.id = chat_messages.chatid
326	INNER JOIN memberships
327	ON memberships.userid = (CASE WHEN chat_messages.userid = chat_rooms.user1 THEN chat_rooms.user2 ELSE chat_rooms.user1 END)
328	AND memberships.groupid IN ($placeholders)	8✔
329	INNER JOIN `groups` ON memberships.groupid = groups.id AND groups.type = 'Freegle'
330	WHERE chat_messages.reviewrequired = 1
331	AND chat_messages.reviewrejected = 0
332	AND chat_messages_held.userid IS NULL
333	AND chat_messages.date > ?";	8✔
334
335	$bindings = $modGroupIds;	8✔
336	$bindings[] = $earliest;	8✔
337
338	if ($minageFilter !== null) {	8✔
339	$sql .= ' AND chat_messages.date <= ?';	3✔
340	$bindings[] = $minageFilter;	3✔
341	}
342
343	$row = DB::selectOne($sql, $bindings);	8✔
344
345	return (int) ($row->count ?? 0);	8✔
346	}
347
348	/**
349	* Build plain-text summary of pending work.
350	*/
351	public function buildTextSummary(array $groupWork, int $chatReview, string $settingsUrl): string	5✔
352	{
353	$text = "There's stuff to do on ModTools:\r\n\r\n";	5✔
354
355	if ($chatReview > 0) {	5✔
356	$text .= "You have {$chatReview} chat message" . ($chatReview > 1 ? 's' : '') . " to review.\r\n\r\n";	2✔
357	}
358
359	foreach ($groupWork as $groupName => $work) {	5✔
360	$text .= "\r\n{$groupName}\r\n:";	3✔
361	foreach ($work as $key => $val) {	3✔
362	if ($val > 0) {	3✔
363	$text .= "{$key}: {$val}\r\n";	3✔
364	}
365	}
366	}
367
368	$text .= "\r\nYou can control how often you get these mails or turn them off entirely from {$settingsUrl}\r\n";	5✔
369
370	return $text;	5✔
371	}
372
373	/**
374	* Build HTML summary for MJML template.
375	*/
376	public function buildHtmlSummary(array $groupWork, int $chatReview): string	5✔
377	{
378	$html = '';	5✔
379
380	if ($chatReview > 0) {	5✔
381	$html .= "<p>You have <b>{$chatReview}</b> chat message" . ($chatReview > 1 ? 's' : '') . ' to review.</p>';	2✔
382	}
383
384	foreach ($groupWork as $groupName => $work) {	5✔
385	$html .= "<p>{$groupName}</p><ul>";	3✔
386	foreach ($work as $key => $val) {	3✔
387	if ($val > 0) {	3✔
388	$html .= "<li>{$key}: <b>{$val}</b></li>";	3✔
389	}
390	}
391	$html .= '</ul>';	3✔
392	}
393
394	return $html;	5✔
395	}
396
397	/**
398	* Check whether to send a notification for this moderator.
399	*
400	* Sends if: no previous record, summary has changed, or it's been > 24h since last send.
401	*/
402	public function shouldSend(int $modId, string $textSummary): bool	6✔
403	{
404	$record = DB::table('modnotifs')->where('userid', $modId)->first();	6✔
405
406	if (!$record) {	6✔
407	return true;	3✔
408	}
409
410	if ($record->data !== $textSummary) {	3✔
411	return true;	1✔
412	}
413
414	$age = Carbon::parse($record->timestamp)->diffInSeconds(now());	2✔
415
416	return $age > self::RESEND_INTERVAL_SECONDS;	2✔
417	}
418
419	/**
420	* Record that a notification was sent.
421	*/
422	public function recordSent(int $modId, string $textSummary): void	2✔
423	{
424	DB::table('modnotifs')->updateOrInsert(	2✔
425	['userid' => $modId],	2✔
426	['data' => $textSummary, 'timestamp' => now()]	2✔
427	);	2✔
428	}
429	}

Freegle / Iznik / 15405

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous