20959

Committed 13 Jun 2026 02:43PM UTC coverage: 71.021% (+1.5%) from 69.555%

Build # 20959

Build Type

push

circleci

Committed by

edwh

Commit Message

feat(web): redirect /councils to /partnerships

The councils content now lives at /partnerships; add a route rule so the old
/councils URL (which 404'd) redirects there.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

Coverage Stats

10705 of 14260 branches covered (75.07%)

Branch coverage included in aggregate %.

116833 of 165317 relevant lines covered (70.67%)

35.88 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);
                    // V1 parity: skip our own per-user-alias domains so the mail can't loop back as chat.
                    $email = $user?->email_preferred;

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

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

Freegle / Iznik / 20959

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