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

28.69

/iznik-batch/app/Models/ChatMessage.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Support\Facades\DB;
use OwenIt\Auditing\Contracts\Auditable;

/**
 * @property int $id
 * @property int $chatid
 * @property int $userid From
 * @property string $type
 * @property string|null $reportreason
 * @property int|null $refmsgid
 * @property int|null $refchatid
 * @property int|null $imageid
 * @property \Illuminate\Support\Carbon $date
 * @property string|null $message
 * @property bool $platform Whether this was created on the platform vs email
 * @property bool $seenbyall
 * @property bool $mailedtoall
 * @property bool $reviewrequired Whether a volunteer should review before it's passed on
 * @property int|null $reviewedby User id of volunteer who reviewed it
 * @property bool $reviewrejected
 * @property int|null $spamscore SpamAssassin score for mail replies
 * @property string|null $facebookid
 * @property int|null $scheduleid
 * @property bool|null $replyexpected
 * @property bool $replyreceived
 * @property bool $processingrequired
 * @property bool $processingsuccessful
 * @property bool $confirmrequired
 * @property bool $deleted
 * @property-read \App\Models\ChatRoom $chatRoom
 * @property-read \App\Models\ChatImage|null $image
 * @property-read \Illuminate\Database\Eloquent\Collection<int, \App\Models\ChatImage> $images
 * @property-read int|null $images_count
 * @property-read \App\Models\Message|null $refMessage
 * @property-read \App\Models\User|null $reviewer
 * @property-read \App\Models\User $user
 * @method static Builder<static>|ChatMessage expectingReply()
 * @method static Builder<static>|ChatMessage newModelQuery()
 * @method static Builder<static>|ChatMessage newQuery()
 * @method static Builder<static>|ChatMessage query()
 * @method static Builder<static>|ChatMessage recent(int $days = 31)
 * @method static Builder<static>|ChatMessage requiringReview()
 * @method static Builder<static>|ChatMessage unmailed()
 * @method static Builder<static>|ChatMessage unseen()
 * @method static Builder<static>|ChatMessage visible()
 * @method static Builder<static>|ChatMessage whereChatid($value)
 * @method static Builder<static>|ChatMessage whereConfirmrequired($value)
 * @method static Builder<static>|ChatMessage whereDate($value)
 * @method static Builder<static>|ChatMessage whereDeleted($value)
 * @method static Builder<static>|ChatMessage whereFacebookid($value)
 * @method static Builder<static>|ChatMessage whereId($value)
 * @method static Builder<static>|ChatMessage whereImageid($value)
 * @method static Builder<static>|ChatMessage whereMailedtoall($value)
 * @method static Builder<static>|ChatMessage whereMessage($value)
 * @method static Builder<static>|ChatMessage wherePlatform($value)
 * @method static Builder<static>|ChatMessage whereProcessingrequired($value)
 * @method static Builder<static>|ChatMessage whereProcessingsuccessful($value)
 * @method static Builder<static>|ChatMessage whereRefchatid($value)
 * @method static Builder<static>|ChatMessage whereRefmsgid($value)
 * @method static Builder<static>|ChatMessage whereReplyexpected($value)
 * @method static Builder<static>|ChatMessage whereReplyreceived($value)
 * @method static Builder<static>|ChatMessage whereReportreason($value)
 * @method static Builder<static>|ChatMessage whereReviewedby($value)
 * @method static Builder<static>|ChatMessage whereReviewrejected($value)
 * @method static Builder<static>|ChatMessage whereReviewrequired($value)
 * @method static Builder<static>|ChatMessage whereScheduleid($value)
 * @method static Builder<static>|ChatMessage whereSeenbyall($value)
 * @method static Builder<static>|ChatMessage whereSpamscore($value)
 * @method static Builder<static>|ChatMessage whereType($value)
 * @method static Builder<static>|ChatMessage whereUserid($value)
 * @mixin \Eloquent
 */
class ChatMessage extends Model implements Auditable
{
    use \OwenIt\Auditing\Auditable;

    protected $table = 'chat_messages';
    protected $guarded = ['id'];
    public $timestamps = FALSE;

    public const TYPE_DEFAULT = 'Default';
    public const TYPE_SYSTEM = 'System';
    public const TYPE_MODMAIL = 'ModMail';
    public const TYPE_INTERESTED = 'Interested';
    public const TYPE_PROMISED = 'Promised';
    public const TYPE_RENEGED = 'Reneged';
    public const TYPE_COMPLETED = 'Completed';
    public const TYPE_IMAGE = 'Image';
    public const TYPE_ADDRESS = 'Address';
    public const TYPE_NUDGE = 'Nudge';
    public const TYPE_REMINDER = 'Reminder';
    public const TYPE_REPORTEDUSER = 'ReportedUser';

    // Review reason values (reportreason column).
    public const REVIEW_USER = 'User';

    protected $casts = [
        'date' => 'datetime',
        'seenbyall' => 'boolean',
        'mailedtoall' => 'boolean',
        'reviewrequired' => 'boolean',
        'reviewrejected' => 'boolean',
        'replyexpected' => 'boolean',
        'replyreceived' => 'boolean',
        'processingrequired' => 'boolean',
        'processingsuccessful' => 'boolean',
        'confirmrequired' => 'boolean',
        'deleted' => 'boolean',
        'platform' => 'boolean',
    ];

    /**
     * Get the chat room.
     */
    public function chatRoom(): BelongsTo
    {
        return $this->belongsTo(ChatRoom::class, 'chatid');
    }

    /**
     * Get the sender.
     */
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class, 'userid');
    }

    /**
     * Get the referenced message.
     */
    public function refMessage(): BelongsTo
    {
        return $this->belongsTo(Message::class, 'refmsgid');
    }

    /**
     * Get the reviewer.
     */
    public function reviewer(): BelongsTo
    {
        return $this->belongsTo(User::class, 'reviewedby');
    }

    /**
     * Get the image if this is an image message.
     */
    public function image(): BelongsTo
    {
        return $this->belongsTo(ChatImage::class, 'imageid');
    }

    /**
     * Get images attached to this message.
     */
    public function images(): HasMany
    {
        return $this->hasMany(ChatImage::class, 'chatmsgid');
    }

    /**
     * Scope to visible messages (not review-rejected).
     */
    public function scopeVisible(Builder $query): Builder
    {
        return $query->where('reviewrejected', 0)
            ->where('reviewrequired', 0)
            ->where('processingsuccessful', 1);
    }

    /**
     * Scope to messages requiring review.
     */
    public function scopeRequiringReview(Builder $query): Builder
    {
        return $query->where('reviewrequired', 1);
    }

    /**
     * Scope to messages not seen by all.
     */
    public function scopeUnseen(Builder $query): Builder
    {
        return $query->where('seenbyall', 0);
    }

    /**
     * Scope to messages not mailed to all.
     */
    public function scopeUnmailed(Builder $query): Builder
    {
        return $query->where('mailedtoall', 0);
    }

    /**
     * Scope to messages expecting a reply.
     */
    public function scopeExpectingReply(Builder $query): Builder
    {
        return $query->where('replyexpected', 1)
            ->where('replyreceived', 0);
    }

    /**
     * Scope to recent messages.
     */
    public function scopeRecent(Builder $query, int $days = 31): Builder
    {
        return $query->where('date', '>=', now()->subDays($days));
    }

    /**
     * Check if this message is visible to users.
     */
    public function isVisible(): bool
    {
        return !$this->reviewrejected
            && !$this->reviewrequired
            && $this->processingsuccessful;
    }

    /**
     * Check if this is a system message.
     */
    public function isSystemMessage(): bool
    {
        return $this->type === self::TYPE_SYSTEM;
    }

    /**
     * Check if this message was sent from the platform (not email).
     */
    public function isFromPlatform(): bool
    {
        return (bool) $this->platform;
    }

    /**
     * Return per-group counts of chat messages pending review for the given moderator.
     *
     * For each group the moderator actively mods, counts how many User2User chat messages
     * have reviewrequired=1 and have not yet been rejected, where either:
     *   (a) the recipient is a member of that group, or
     *   (b) the sender is a member of that group and the recipient is not on any Freegle group.
     *
     * When $other=true, counts messages that are currently held instead of unheld.
     *
     * Ported from iznik-server/include/chat/ChatMessage.php::getReviewCountByGroup().
     *
     * @param User|null $me    The moderator. NULL returns an empty array.
     * @param bool      $other When true, count held messages instead of unreviewed ones.
     * @return array<array{groupid: int, count: int}>
     */
    public function getReviewCountByGroup(?User $me, bool $other = false): array
    {
        if (!$me) {
            return [];
        }

        $widerReview = $me->widerReview();

        $groupIds = [];
        foreach ($me->getModeratorships() as $mod) {
            if ($me->activeModForGroup($mod)) {
                $groupIds[] = $mod;
            }
        }

        if (empty($groupIds)) {
            return [];
        }

        $cutoff = now()->subDays(31);

        // CASE expression for the "other user" in the chat room (i.e. the recipient).
        $otherUser = 'CASE WHEN chat_messages.userid = chat_rooms.user1 THEN chat_rooms.user2 ELSE chat_rooms.user1 END';

        // Part 1: messages where the recipient is a member of one of our modded groups.
        $part1 = DB::table('chat_messages')
            ->select(['chat_messages.id', 'memberships.groupid'])
            ->leftJoin('chat_messages_held', 'chat_messages_held.msgid', '=', 'chat_messages.id')
            ->join('chat_rooms', 'chat_rooms.id', '=', 'chat_messages.chatid')
            ->join('memberships', function ($join) use ($groupIds, $otherUser) {
                $join->whereRaw("memberships.userid = {$otherUser}")
                    ->whereIn('memberships.groupid', $groupIds);
            })
            ->join('groups', function ($join) {
                $join->on('memberships.groupid', '=', 'groups.id')
                    ->where('groups.type', Group::TYPE_FREEGLE);
            })
            ->where('chat_messages.reviewrequired', 1)
            ->where('chat_messages.reviewrejected', 0)
            ->where('chat_messages.date', '>', $cutoff);

        // Part 2: messages where the recipient is not on any Freegle group, but the sender is in one of our groups.
        $part2 = DB::table('chat_messages')
            ->select(['chat_messages.id', 'm2.groupid'])
            ->leftJoin('chat_messages_held', 'chat_messages_held.msgid', '=', 'chat_messages.id')
            ->join('chat_rooms', 'chat_rooms.id', '=', 'chat_messages.chatid')
            ->leftJoin('memberships as m1', function ($join) use ($otherUser) {
                $join->whereRaw("m1.userid = {$otherUser}");
            })
            ->leftJoin('groups', function ($join) {
                $join->on('m1.groupid', '=', 'groups.id')
                    ->where('groups.type', Group::TYPE_FREEGLE);
            })
            ->join('memberships as m2', function ($join) use ($groupIds) {
                $join->on('m2.userid', '=', 'chat_messages.userid')
                    ->whereIn('m2.groupid', $groupIds);
            })
            ->where('chat_messages.reviewrequired', 1)
            ->where('chat_messages.reviewrejected', 0)
            ->where('chat_messages.date', '>', $cutoff)
            ->whereNull('m1.id');

        foreach ([$part1, $part2] as $part) {
            if ($other) {
                $part->whereNotNull('chat_messages_held.userid');
            } else {
                $part->whereNull('chat_messages_held.userid');
            }
        }

        $query = $part1->union($part2);

        // Part 3: wider-review held messages (only when moderator has wider review and $other=true).
        if ($widerReview && $other) {
            $part3 = DB::table('chat_messages')
                ->select(['chat_messages.id', 'memberships.groupid'])
                ->join('chat_rooms', 'chat_rooms.id', '=', 'chat_messages.chatid')
                ->leftJoin('chat_messages_held', 'chat_messages.id', '=', 'chat_messages_held.msgid')
                ->join('memberships', function ($join) use ($otherUser) {
                    $join->whereRaw("memberships.userid = {$otherUser}");
                })
                ->join('groups', function ($join) {
                    $join->on('memberships.groupid', '=', 'groups.id')
                        ->where('groups.type', Group::TYPE_FREEGLE);
                })
                ->where('chat_messages.reviewrequired', 1)
                ->where('chat_messages.reviewrejected', 0)
                ->where('chat_messages.date', '>', $cutoff)
                ->whereRaw("JSON_EXTRACT(groups.settings, '$.widerchatreview') = 1")
                ->whereNull('chat_messages_held.id')
                ->where('chat_messages.reportreason', '!=', self::REVIEW_USER);

            $query = $query->union($part3);
        }

        $counts = $query->orderBy('groupid')->get();

        // The same message might appear in the query results multiple times if the recipient is on multiple
        // groups that we mod. We only want to count it once. The order here matches that in
        // ChatRoom::getMessagesForReview.
        $usedMsgs = [];
        $seenGroups = [];

        foreach ($counts as $count) {
            $usedMsgs[$count->id] = $count->groupid;
            $seenGroups[$count->groupid] = $count->groupid;
        }

        $showcounts = [];

        foreach ($seenGroups as $groupId) {
            $count = 0;

            foreach ($usedMsgs as $msgGroupId) {
                if ($msgGroupId == $groupId) {
                    $count++;
                }
            }

            $showcounts[] = [
                'groupid' => $groupId,
                'count'   => $count,
            ];
        }

        return $showcounts;
    }
}

1	<?php
2
3	namespace App\Models;
4
5	use Illuminate\Database\Eloquent\Builder;
6	use Illuminate\Database\Eloquent\Model;
7	use Illuminate\Database\Eloquent\Relations\BelongsTo;
8	use Illuminate\Database\Eloquent\Relations\HasMany;
9	use Illuminate\Support\Facades\DB;
10	use OwenIt\Auditing\Contracts\Auditable;
11
12	/**
13	* @property int $id
14	* @property int $chatid
15	* @property int $userid From
16	* @property string $type
17	* @property string\|null $reportreason
18	* @property int\|null $refmsgid
19	* @property int\|null $refchatid
20	* @property int\|null $imageid
21	* @property \Illuminate\Support\Carbon $date
22	* @property string\|null $message
23	* @property bool $platform Whether this was created on the platform vs email
24	* @property bool $seenbyall
25	* @property bool $mailedtoall
26	* @property bool $reviewrequired Whether a volunteer should review before it's passed on
27	* @property int\|null $reviewedby User id of volunteer who reviewed it
28	* @property bool $reviewrejected
29	* @property int\|null $spamscore SpamAssassin score for mail replies
30	* @property string\|null $facebookid
31	* @property int\|null $scheduleid
32	* @property bool\|null $replyexpected
33	* @property bool $replyreceived
34	* @property bool $processingrequired
35	* @property bool $processingsuccessful
36	* @property bool $confirmrequired
37	* @property bool $deleted
38	* @property-read \App\Models\ChatRoom $chatRoom
39	* @property-read \App\Models\ChatImage\|null $image
40	* @property-read \Illuminate\Database\Eloquent\Collection<int, \App\Models\ChatImage> $images
41	* @property-read int\|null $images_count
42	* @property-read \App\Models\Message\|null $refMessage
43	* @property-read \App\Models\User\|null $reviewer
44	* @property-read \App\Models\User $user
45	* @method static Builder<static>\|ChatMessage expectingReply()
46	* @method static Builder<static>\|ChatMessage newModelQuery()
47	* @method static Builder<static>\|ChatMessage newQuery()
48	* @method static Builder<static>\|ChatMessage query()
49	* @method static Builder<static>\|ChatMessage recent(int $days = 31)
50	* @method static Builder<static>\|ChatMessage requiringReview()
51	* @method static Builder<static>\|ChatMessage unmailed()
52	* @method static Builder<static>\|ChatMessage unseen()
53	* @method static Builder<static>\|ChatMessage visible()
54	* @method static Builder<static>\|ChatMessage whereChatid($value)
55	* @method static Builder<static>\|ChatMessage whereConfirmrequired($value)
56	* @method static Builder<static>\|ChatMessage whereDate($value)
57	* @method static Builder<static>\|ChatMessage whereDeleted($value)
58	* @method static Builder<static>\|ChatMessage whereFacebookid($value)
59	* @method static Builder<static>\|ChatMessage whereId($value)
60	* @method static Builder<static>\|ChatMessage whereImageid($value)
61	* @method static Builder<static>\|ChatMessage whereMailedtoall($value)
62	* @method static Builder<static>\|ChatMessage whereMessage($value)
63	* @method static Builder<static>\|ChatMessage wherePlatform($value)
64	* @method static Builder<static>\|ChatMessage whereProcessingrequired($value)
65	* @method static Builder<static>\|ChatMessage whereProcessingsuccessful($value)
66	* @method static Builder<static>\|ChatMessage whereRefchatid($value)
67	* @method static Builder<static>\|ChatMessage whereRefmsgid($value)
68	* @method static Builder<static>\|ChatMessage whereReplyexpected($value)
69	* @method static Builder<static>\|ChatMessage whereReplyreceived($value)
70	* @method static Builder<static>\|ChatMessage whereReportreason($value)
71	* @method static Builder<static>\|ChatMessage whereReviewedby($value)
72	* @method static Builder<static>\|ChatMessage whereReviewrejected($value)
73	* @method static Builder<static>\|ChatMessage whereReviewrequired($value)
74	* @method static Builder<static>\|ChatMessage whereScheduleid($value)
75	* @method static Builder<static>\|ChatMessage whereSeenbyall($value)
76	* @method static Builder<static>\|ChatMessage whereSpamscore($value)
77	* @method static Builder<static>\|ChatMessage whereType($value)
78	* @method static Builder<static>\|ChatMessage whereUserid($value)
79	* @mixin \Eloquent
80	*/
81	class ChatMessage extends Model implements Auditable
82	{
83	use \OwenIt\Auditing\Auditable;
84
85	protected $table = 'chat_messages';
86	protected $guarded = ['id'];
87	public $timestamps = FALSE;
88
89	public const TYPE_DEFAULT = 'Default';
90	public const TYPE_SYSTEM = 'System';
91	public const TYPE_MODMAIL = 'ModMail';
92	public const TYPE_INTERESTED = 'Interested';
93	public const TYPE_PROMISED = 'Promised';
94	public const TYPE_RENEGED = 'Reneged';
95	public const TYPE_COMPLETED = 'Completed';
96	public const TYPE_IMAGE = 'Image';
97	public const TYPE_ADDRESS = 'Address';
98	public const TYPE_NUDGE = 'Nudge';
99	public const TYPE_REMINDER = 'Reminder';
100	public const TYPE_REPORTEDUSER = 'ReportedUser';
101
102	// Review reason values (reportreason column).
103	public const REVIEW_USER = 'User';
104
105	protected $casts = [
106	'date' => 'datetime',
107	'seenbyall' => 'boolean',
108	'mailedtoall' => 'boolean',
109	'reviewrequired' => 'boolean',
110	'reviewrejected' => 'boolean',
111	'replyexpected' => 'boolean',
112	'replyreceived' => 'boolean',
113	'processingrequired' => 'boolean',
114	'processingsuccessful' => 'boolean',
115	'confirmrequired' => 'boolean',
116	'deleted' => 'boolean',
117	'platform' => 'boolean',
118	];
119
120	/**
121	* Get the chat room.
122	*/
123	public function chatRoom(): BelongsTo	30✔
124	{
125	return $this->belongsTo(ChatRoom::class, 'chatid');	30✔
126	}
127
128	/**
129	* Get the sender.
130	*/
131	public function user(): BelongsTo	79✔
132	{
133	return $this->belongsTo(User::class, 'userid');	79✔
134	}
135
136	/**
137	* Get the referenced message.
138	*/
139	public function refMessage(): BelongsTo	108✔
140	{
141	return $this->belongsTo(Message::class, 'refmsgid');	108✔
142	}
143
144	/**
145	* Get the reviewer.
146	*/
147	public function reviewer(): BelongsTo	1✔
148	{
149	return $this->belongsTo(User::class, 'reviewedby');	1✔
150	}
151
152	/**
153	* Get the image if this is an image message.
154	*/
155	public function image(): BelongsTo	1✔
156	{
157	return $this->belongsTo(ChatImage::class, 'imageid');	1✔
158	}
159
160	/**
161	* Get images attached to this message.
162	*/
163	public function images(): HasMany	2✔
164	{
165	return $this->hasMany(ChatImage::class, 'chatmsgid');	2✔
166	}
167
168	/**
169	* Scope to visible messages (not review-rejected).
170	*/
171	public function scopeVisible(Builder $query): Builder	1✔
172	{
173	return $query->where('reviewrejected', 0)	1✔
174	->where('reviewrequired', 0)	1✔
175	->where('processingsuccessful', 1);	1✔
176	}
177
178	/**
179	* Scope to messages requiring review.
180	*/
181	public function scopeRequiringReview(Builder $query): Builder	1✔
182	{
183	return $query->where('reviewrequired', 1);	1✔
184	}
185
186	/**
187	* Scope to messages not seen by all.
188	*/
189	public function scopeUnseen(Builder $query): Builder	1✔
190	{
191	return $query->where('seenbyall', 0);	1✔
192	}
193
194	/**
195	* Scope to messages not mailed to all.
196	*/
197	public function scopeUnmailed(Builder $query): Builder	1✔
198	{
199	return $query->where('mailedtoall', 0);	1✔
200	}
201
202	/**
203	* Scope to messages expecting a reply.
204	*/
205	public function scopeExpectingReply(Builder $query): Builder	1✔
206	{
207	return $query->where('replyexpected', 1)	1✔
208	->where('replyreceived', 0);	1✔
209	}
210
211	/**
212	* Scope to recent messages.
213	*/
214	public function scopeRecent(Builder $query, int $days = 31): Builder	1✔
215	{
216	return $query->where('date', '>=', now()->subDays($days));	1✔
217	}
218
219	/**
220	* Check if this message is visible to users.
221	*/
222	public function isVisible(): bool	1✔
223	{
224	return !$this->reviewrejected	1✔
225	&& !$this->reviewrequired	1✔
226	&& $this->processingsuccessful;	1✔
227	}
228
229	/**
230	* Check if this is a system message.
231	*/
232	public function isSystemMessage(): bool	1✔
233	{
234	return $this->type === self::TYPE_SYSTEM;	1✔
235	}
236
237	/**
238	* Check if this message was sent from the platform (not email).
239	*/
240	public function isFromPlatform(): bool	1✔
241	{
242	return (bool) $this->platform;	1✔
243	}
244
245	/**
246	* Return per-group counts of chat messages pending review for the given moderator.
247	*
248	* For each group the moderator actively mods, counts how many User2User chat messages
249	* have reviewrequired=1 and have not yet been rejected, where either:
250	* (a) the recipient is a member of that group, or
251	* (b) the sender is a member of that group and the recipient is not on any Freegle group.
252	*
253	* When $other=true, counts messages that are currently held instead of unheld.
254	*
255	* Ported from iznik-server/include/chat/ChatMessage.php::getReviewCountByGroup().
256	*
257	* @param User\|null $me The moderator. NULL returns an empty array.
258	* @param bool $other When true, count held messages instead of unreviewed ones.
259	* @return array<array{groupid: int, count: int}>
260	*/
261	public function getReviewCountByGroup(?User $me, bool $other = false): array	×
262	{
263	if (!$me) {	×
264	return [];	×
265	}
266
267	$widerReview = $me->widerReview();	×
268
269	$groupIds = [];	×
270	foreach ($me->getModeratorships() as $mod) {	×
271	if ($me->activeModForGroup($mod)) {	×
272	$groupIds[] = $mod;	×
273	}
274	}
275
276	if (empty($groupIds)) {	×
277	return [];	×
278	}
279
280	$cutoff = now()->subDays(31);	×
281
282	// CASE expression for the "other user" in the chat room (i.e. the recipient).
283	$otherUser = 'CASE WHEN chat_messages.userid = chat_rooms.user1 THEN chat_rooms.user2 ELSE chat_rooms.user1 END';	×
284
285	// Part 1: messages where the recipient is a member of one of our modded groups.
286	$part1 = DB::table('chat_messages')	×
287	->select(['chat_messages.id', 'memberships.groupid'])	×
288	->leftJoin('chat_messages_held', 'chat_messages_held.msgid', '=', 'chat_messages.id')	×
289	->join('chat_rooms', 'chat_rooms.id', '=', 'chat_messages.chatid')	×
290	->join('memberships', function ($join) use ($groupIds, $otherUser) {	×
291	$join->whereRaw("memberships.userid = {$otherUser}")	×
292	->whereIn('memberships.groupid', $groupIds);	×
293	})	×
294	->join('groups', function ($join) {	×
295	$join->on('memberships.groupid', '=', 'groups.id')	×
296	->where('groups.type', Group::TYPE_FREEGLE);	×
297	})	×
298	->where('chat_messages.reviewrequired', 1)	×
299	->where('chat_messages.reviewrejected', 0)	×
300	->where('chat_messages.date', '>', $cutoff);	×
301
302	// Part 2: messages where the recipient is not on any Freegle group, but the sender is in one of our groups.
303	$part2 = DB::table('chat_messages')	×
304	->select(['chat_messages.id', 'm2.groupid'])	×
305	->leftJoin('chat_messages_held', 'chat_messages_held.msgid', '=', 'chat_messages.id')	×
306	->join('chat_rooms', 'chat_rooms.id', '=', 'chat_messages.chatid')	×
307	->leftJoin('memberships as m1', function ($join) use ($otherUser) {	×
308	$join->whereRaw("m1.userid = {$otherUser}");	×
309	})	×
310	->leftJoin('groups', function ($join) {	×
311	$join->on('m1.groupid', '=', 'groups.id')	×
312	->where('groups.type', Group::TYPE_FREEGLE);	×
313	})	×
314	->join('memberships as m2', function ($join) use ($groupIds) {	×
315	$join->on('m2.userid', '=', 'chat_messages.userid')	×
316	->whereIn('m2.groupid', $groupIds);	×
317	})	×
318	->where('chat_messages.reviewrequired', 1)	×
319	->where('chat_messages.reviewrejected', 0)	×
320	->where('chat_messages.date', '>', $cutoff)	×
321	->whereNull('m1.id');	×
322
323	foreach ([$part1, $part2] as $part) {	×
324	if ($other) {	×
325	$part->whereNotNull('chat_messages_held.userid');	×
326	} else {
327	$part->whereNull('chat_messages_held.userid');	×
328	}
329	}
330
331	$query = $part1->union($part2);	×
332
333	// Part 3: wider-review held messages (only when moderator has wider review and $other=true).
334	if ($widerReview && $other) {	×
335	$part3 = DB::table('chat_messages')	×
336	->select(['chat_messages.id', 'memberships.groupid'])	×
337	->join('chat_rooms', 'chat_rooms.id', '=', 'chat_messages.chatid')	×
338	->leftJoin('chat_messages_held', 'chat_messages.id', '=', 'chat_messages_held.msgid')	×
339	->join('memberships', function ($join) use ($otherUser) {	×
340	$join->whereRaw("memberships.userid = {$otherUser}");	×
341	})	×
342	->join('groups', function ($join) {	×
343	$join->on('memberships.groupid', '=', 'groups.id')	×
344	->where('groups.type', Group::TYPE_FREEGLE);	×
345	})	×
346	->where('chat_messages.reviewrequired', 1)	×
347	->where('chat_messages.reviewrejected', 0)	×
348	->where('chat_messages.date', '>', $cutoff)	×
349	->whereRaw("JSON_EXTRACT(groups.settings, '$.widerchatreview') = 1")	×
350	->whereNull('chat_messages_held.id')	×
351	->where('chat_messages.reportreason', '!=', self::REVIEW_USER);	×
352
353	$query = $query->union($part3);	×
354	}
355
356	$counts = $query->orderBy('groupid')->get();	×
357
358	// The same message might appear in the query results multiple times if the recipient is on multiple
359	// groups that we mod. We only want to count it once. The order here matches that in
360	// ChatRoom::getMessagesForReview.
361	$usedMsgs = [];	×
362	$seenGroups = [];	×
363
364	foreach ($counts as $count) {	×
365	$usedMsgs[$count->id] = $count->groupid;	×
366	$seenGroups[$count->groupid] = $count->groupid;	×
367	}
368
369	$showcounts = [];	×
370
371	foreach ($seenGroups as $groupId) {	×
372	$count = 0;	×
373
374	foreach ($usedMsgs as $msgGroupId) {	×
375	if ($msgGroupId == $groupId) {	×
376	$count++;	×
377	}
378	}
379
380	$showcounts[] = [	×
381	'groupid' => $groupId,	×
382	'count' => $count,	×
383	];	×
384	}
385
386	return $showcounts;	×
387	}
388	}

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