11342

Committed 08 May 2026 02:47PM UTC coverage: 68.97% (-3.8%) from 72.761%

Build # 11342

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #403 from Freegle/feature/exports-migration

feat(batch): migrate exports.php to users:process-exports

Coverage Stats

9127 of 10554 branches covered (86.48%)

Branch coverage included in aggregate %.

400 of 452 new or added lines in 2 files covered. (88.5%)

12172 existing lines in 167 files now uncovered.

100855 of 148909 relevant lines covered (67.73%)

19.62 hits per line

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

91.74

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

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Process pending GDPR data export requests (users_exports.completed IS NULL).
 *
 * Mirrors V1 cron/exports.php + User::export().
 *
 * For each pending export:
 * - Mark as started
 * - Assemble all user data into a structured array
 * - Compress as gzdeflate'd JSON and store in users_exports.data
 * - Mark as completed
 *
 * Also purges data (sets data = NULL) for exports completed more than 7 days ago.
 */
class UserDataExportService
{
    public function processAll(): int
    {
        $this->purgeOldExportData();

        $exports = DB::table('users_exports')
            ->whereNull('completed')
            ->orderBy('id', 'asc')
            ->get();

        $count = 0;

        foreach ($exports as $export) {
            try {
                $this->processExport($export);
                $count++;
            } catch (\Throwable $e) {
                Log::error("UserDataExport: export {$export->id} failed", [
                    'userid' => $export->userid,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        Log::info("UserDataExport: processed {$count} exports");

        return $count;
    }

    private function purgeOldExportData(): void
    {
        DB::table('users_exports')
            ->whereNotNull('completed')
            ->where('completed', '<', now()->subDays(7))
            ->update(['data' => null]);
    }

    private function processExport(object $export): void
    {
        $userId = $export->userid;
        $exportId = $export->id;
        $tag = $export->tag;

        DB::table('users_exports')
            ->where('id', $exportId)
            ->where('tag', $tag)
            ->update(['started' => now()]);

        $data = $this->assembleUserData($userId);

        $json = json_encode($data, JSON_PARTIAL_OUTPUT_ON_ERROR | JSON_UNESCAPED_UNICODE);
        $compressed = gzdeflate($json);

        DB::table('users_exports')
            ->where('id', $exportId)
            ->where('tag', $tag)
            ->update([
                'completed' => now(),
                'data' => $compressed,
            ]);

        Log::info("UserDataExport: completed export {$exportId}", ['userid' => $userId]);
    }

    private function assembleUserData(int $userId): array
    {
        $data = [];

        $data['basic'] = $this->getBasicInfo($userId);
        $data['emails'] = $this->getEmails($userId);
        $data['logins'] = $this->getLogins($userId);
        $data['invitations'] = $this->getInvitations($userId);
        $data['memberships'] = $this->getMemberships($userId);
        $data['memberships_history'] = $this->getMembershipHistory($userId);
        $data['searches'] = $this->getSearches($userId);
        $data['alerts'] = $this->getAlerts($userId);
        $data['donations'] = $this->getDonations($userId);
        $data['giftaid'] = $this->getGiftAid($userId);
        $data['bans'] = $this->getBans($userId);
        $data['spam_reports'] = $this->getSpamReports($userId);
        $data['spam_domains'] = $this->getSpamDomains($userId);
        $data['images'] = $this->getImages($userId);
        $data['notifications'] = $this->getNotifications($userId);
        $data['addresses'] = $this->getAddresses($userId);
        $data['community_events'] = $this->getCommunityEvents($userId);
        $data['volunteering'] = $this->getVolunteering($userId);
        $data['comments'] = $this->getComments($userId);
        $data['ratings'] = $this->getRatings($userId);
        $data['excluded_locations'] = $this->getExcludedLocations($userId);
        $data['messages'] = $this->getMessages($userId);
        $data['chats'] = $this->getChats($userId);
        $data['newsfeed'] = $this->getNewsfeed($userId);
        $data['aboutme'] = $this->getAboutMe($userId);
        $data['stories'] = $this->getStories($userId);
        $data['exports'] = $this->getExportHistory($userId);
        $data['logs'] = $this->getLogs($userId);

        return $data;
    }

    private function getBasicInfo(int $userId): array
    {
        $user = DB::table('users')->where('id', $userId)->first();

        if (!$user) {
            return ['id' => $userId];
        }

        $d = [
            'id' => $user->id,
            'fullname' => $user->fullname,
            'firstname' => $user->firstname,
            'lastname' => $user->lastname,
            'systemrole' => $user->systemrole,
            'added' => $user->added,
            'lastaccess' => $user->lastaccess,
            'bouncing' => $user->bouncing ? 'Yes' : 'No',
            'permissions' => $user->permissions,
            'invitesleft' => $user->invitesleft,
        ];

        if ($user->lastlocation) {
            $loc = DB::table('locations')->where('id', $user->lastlocation)->first();
            if ($loc) {
                $d['last_posted_location'] = "{$loc->name} ({$loc->lat}, {$loc->lng})";
            }
        }

        return $d;
    }

    private function getEmails(int $userId): array
    {
        return DB::table('users_emails')
            ->where('userid', $userId)
            ->select('email', 'preferred', 'added', 'validated', 'bounced')
            ->orderBy('id', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getLogins(int $userId): array
    {
        return DB::table('users_logins')
            ->where('userid', $userId)
            ->select('type', 'uid', 'added', 'lastaccess')
            ->orderBy('added', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getInvitations(int $userId): array
    {
        return DB::table('users_invitations')
            ->where('userid', $userId)
            ->select('email', 'date')
            ->orderBy('date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getMemberships(int $userId): array
    {
        return DB::table('memberships')
            ->join('groups', 'memberships.groupid', '=', 'groups.id')
            ->where('memberships.userid', $userId)
            ->select(
                'memberships.groupid',
                'memberships.collection',
                'memberships.added',
                DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')
            )
            ->orderBy('memberships.added', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getMembershipHistory(int $userId): array
    {
        return DB::table('memberships_history')
            ->join('groups', 'memberships_history.groupid', '=', 'groups.id')
            ->where('memberships_history.userid', $userId)
            ->select(
                'memberships_history.groupid',
                'memberships_history.collection',
                'memberships_history.added',
                DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')
            )
            ->orderBy('memberships_history.added', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getSearches(int $userId): array
    {
        return DB::table('search_history')
            ->leftJoin('locations', 'search_history.locationid', '=', 'locations.id')
            ->where('search_history.userid', $userId)
            ->select('search_history.date', 'search_history.term', 'locations.name as location')
            ->orderBy('search_history.date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getAlerts(int $userId): array
    {
        return DB::table('alerts_tracking')
            ->join('alerts', 'alerts_tracking.alertid', '=', 'alerts.id')
            ->where('alerts_tracking.userid', $userId)
            ->whereNotNull('alerts_tracking.responded')
            ->select('alerts.subject', 'alerts_tracking.responded', 'alerts_tracking.response')
            ->orderBy('alerts_tracking.responded', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getDonations(int $userId): array
    {
        return DB::table('users_donations')
            ->where('userid', $userId)
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getGiftAid(int $userId): array
    {
        return DB::table('giftaid')
            ->where('userid', $userId)
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getBans(int $userId): array
    {
        $bans = DB::table('users_banned')
            ->leftJoin('groups', 'users_banned.groupid', '=', 'groups.id')
            ->leftJoin('users_emails', function ($join) {
                $join->on('users_banned.userid', '=', 'users_emails.userid')
                    ->where('users_emails.preferred', '=', 1);
            })
            ->where('users_banned.byuser', $userId)
            ->select(
                'users_banned.date',
                'users_banned.userid',
                'users_emails.email',
                DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')
            )
            ->orderBy('users_banned.date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        return $bans;
    }

    private function getSpamReports(int $userId): array
    {
        return DB::table('spam_users')
            ->where('byuserid', $userId)
            ->leftJoin('users_emails', function ($join) {
                $join->on('spam_users.userid', '=', 'users_emails.userid')
                    ->where('users_emails.preferred', '=', 1);
            })
            ->select('spam_users.userid', 'spam_users.added', 'spam_users.reason', 'users_emails.email')
            ->orderBy('spam_users.added', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getSpamDomains(int $userId): array
    {
        return DB::table('spam_whitelist_links')
            ->where('userid', $userId)
            ->select('domain', 'date')
            ->orderBy('date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getImages(int $userId): array
    {
        return DB::table('users_images')
            ->where('userid', $userId)
            ->select('id', 'url')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getNotifications(int $userId): array
    {
        return DB::table('users_notifications')
            ->where('touser', $userId)
            ->where('seen', 1)
            ->select('timestamp', 'url')
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getAddresses(int $userId): array
    {
        return DB::table('users_addresses')
            ->where('userid', $userId)
            ->select('id', 'pafid', 'lat', 'lng')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getCommunityEvents(int $userId): array
    {
        return DB::table('communityevents')
            ->where('userid', $userId)
            ->select('id', 'title', 'description', 'location', 'added')
            ->orderBy('added', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getVolunteering(int $userId): array
    {
        return DB::table('volunteering')
            ->where('userid', $userId)
            ->select('id', 'title', 'description', 'location', 'added')
            ->orderBy('added', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getComments(int $userId): array
    {
        // users_comments columns are user1-user11 (one per flag/comment category).
        // Export the flag status and comment text columns.
        return DB::table('users_comments')
            ->where('byuserid', $userId)
            ->leftJoin('users_emails', function ($join) {
                $join->on('users_comments.userid', '=', 'users_emails.userid')
                    ->where('users_emails.preferred', '=', 1);
            })
            ->select(
                'users_comments.userid',
                'users_emails.email',
                'users_comments.date',
                'users_comments.flag',
                'users_comments.user1',
                'users_comments.user2',
                'users_comments.user3'
            )
            ->orderBy('users_comments.date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getRatings(int $userId): array
    {
        return DB::table('ratings')
            ->where('ratings.rater', $userId)
            ->leftJoin('users_emails', function ($join) {
                $join->on('ratings.ratee', '=', 'users_emails.userid')
                    ->where('users_emails.preferred', '=', 1);
            })
            ->select('ratings.ratee', 'users_emails.email', 'ratings.rating', 'ratings.timestamp')
            ->orderBy('ratings.timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getExcludedLocations(int $userId): array
    {
        return DB::table('locations_excluded')
            ->where('locations_excluded.userid', $userId)
            ->leftJoin('groups', 'locations_excluded.groupid', '=', 'groups.id')
            ->leftJoin('locations', 'locations_excluded.locationid', '=', 'locations.id')
            ->select(
                DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname'),
                'locations.name as location',
                'locations_excluded.date'
            )
            ->orderBy('locations_excluded.date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getMessages(int $userId): array
    {
        return DB::table('messages')
            ->join('messages_groups', 'messages.id', '=', 'messages_groups.msgid')
            ->join('groups', 'messages_groups.groupid', '=', 'groups.id')
            ->where('messages.fromuser', $userId)
            ->select(
                'messages.id',
                'messages.subject',
                'messages.type',
                'messages.arrival',
                'messages_groups.collection',
                'messages_groups.groupid',
                DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')
            )
            ->orderBy('messages.arrival', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getChats(int $userId): array
    {
        $chatIds = DB::select(
            "SELECT DISTINCT chat_rooms.id FROM chat_rooms
             INNER JOIN (
                 SELECT DISTINCT chatid FROM chat_roster WHERE userid = ?
                 UNION
                 SELECT DISTINCT chatid FROM chat_messages WHERE userid = ? OR reviewedby = ?
             ) t ON t.chatid = chat_rooms.id
             ORDER BY chat_rooms.id ASC",
            [$userId, $userId, $userId]
        );

        $chats = [];

        foreach ($chatIds as $row) {
            $chatId = $row->id;

            $room = DB::table('chat_rooms')->where('id', $chatId)->first();
            if (!$room) {
                continue;
            }

            $roster = DB::table('chat_roster')
                ->where('chatid', $chatId)
                ->where('userid', $userId)
                ->select('date', 'lastip')
                ->first();

            $messages = DB::table('chat_messages')
                ->where('chatid', $chatId)
                ->where(function ($q) use ($userId) {
                    $q->where('userid', $userId)->orWhere('reviewedby', $userId);
                })
                ->select('id', 'date', 'message', 'type', 'userid', 'reviewedby')
                ->orderBy('date', 'asc')
                ->get()
                ->map(fn($m) => (array) $m)
                ->toArray();

            if (count($messages) > 0) {
                $chats[] = [
                    'id' => $chatId,
                    'chattype' => $room->chattype,
                    'date' => $roster ? $roster->date : null,
                    'lastip' => $roster ? $roster->lastip : null,
                    'messages' => $messages,
                ];
            }
        }

        return $chats;
    }

    private function getNewsfeed(int $userId): array
    {
        $items = DB::table('newsfeed')
            ->where('userid', $userId)
            ->select('id', 'timestamp', 'type', 'message', 'msgid', 'groupid')
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        $unfollows = DB::table('newsfeed_unfollow')
            ->where('userid', $userId)
            ->select('newsfeedid', 'timestamp')
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        $likes = DB::table('newsfeed_likes')
            ->where('userid', $userId)
            ->select('newsfeedid', 'timestamp')
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        $reports = DB::table('newsfeed_reports')
            ->where('userid', $userId)
            ->select('newsfeedid', 'timestamp')
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        return [
            'items' => $items,
            'unfollows' => $unfollows,
            'likes' => $likes,
            'reports' => $reports,
        ];
    }

    private function getAboutMe(int $userId): array
    {
        return DB::table('users_aboutme')
            ->where('userid', $userId)
            ->whereRaw('LENGTH(text) > 5')
            ->select('timestamp', 'text')
            ->orderBy('timestamp', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getStories(int $userId): array
    {
        $stories = DB::table('users_stories')
            ->where('userid', $userId)
            ->select('date', 'headline', 'story')
            ->orderBy('date', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        $likes = DB::table('users_stories_likes')
            ->where('userid', $userId)
            ->select('storyid')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();

        return [
            'stories' => $stories,
            'likes' => $likes,
        ];
    }

    private function getExportHistory(int $userId): array
    {
        return DB::table('users_exports')
            ->where('userid', $userId)
            ->select('userid', 'started', 'completed')
            ->orderBy('requested', 'asc')
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }

    private function getLogs(int $userId): array
    {
        return DB::table('logs')
            ->where('byuser', $userId)
            ->orWhere('user', $userId)
            ->select('id', 'timestamp', 'type', 'subtype', 'user', 'byuser', 'groupid', 'text')
            ->orderBy('timestamp', 'asc')
            ->limit(1000)
            ->get()
            ->map(fn($e) => (array) $e)
            ->toArray();
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use Illuminate\Support\Facades\DB;
6	use Illuminate\Support\Facades\Log;
7
8	/**
9	* Process pending GDPR data export requests (users_exports.completed IS NULL).
10	*
11	* Mirrors V1 cron/exports.php + User::export().
12	*
13	* For each pending export:
14	* - Mark as started
15	* - Assemble all user data into a structured array
16	* - Compress as gzdeflate'd JSON and store in users_exports.data
17	* - Mark as completed
18	*
19	* Also purges data (sets data = NULL) for exports completed more than 7 days ago.
20	*/
21	class UserDataExportService
22	{
23	public function processAll(): int	8✔
24	{
25	$this->purgeOldExportData();	8✔
26
27	$exports = DB::table('users_exports')	8✔
28	->whereNull('completed')	8✔
29	->orderBy('id', 'asc')	8✔
30	->get();	8✔
31
32	$count = 0;	8✔
33
34	foreach ($exports as $export) {	8✔
35	try {
36	$this->processExport($export);	5✔
37	$count++;	5✔
NEW 38	} catch (\Throwable $e) {	×
NEW 39	Log::error("UserDataExport: export {$export->id} failed", [	×
NEW 40	'userid' => $export->userid,	×
NEW 41	'error' => $e->getMessage(),	×
NEW 42	]);	×
43	}
44	}
45
46	Log::info("UserDataExport: processed {$count} exports");	8✔
47
48	return $count;	8✔
49	}
50
51	private function purgeOldExportData(): void	8✔
52	{
53	DB::table('users_exports')	8✔
54	->whereNotNull('completed')	8✔
55	->where('completed', '<', now()->subDays(7))	8✔
56	->update(['data' => null]);	8✔
57	}
58
59	private function processExport(object $export): void	5✔
60	{
61	$userId = $export->userid;	5✔
62	$exportId = $export->id;	5✔
63	$tag = $export->tag;	5✔
64
65	DB::table('users_exports')	5✔
66	->where('id', $exportId)	5✔
67	->where('tag', $tag)	5✔
68	->update(['started' => now()]);	5✔
69
70	$data = $this->assembleUserData($userId);	5✔
71
72	$json = json_encode($data, JSON_PARTIAL_OUTPUT_ON_ERROR \| JSON_UNESCAPED_UNICODE);	5✔
73	$compressed = gzdeflate($json);	5✔
74
75	DB::table('users_exports')	5✔
76	->where('id', $exportId)	5✔
77	->where('tag', $tag)	5✔
78	->update([	5✔
79	'completed' => now(),	5✔
80	'data' => $compressed,	5✔
81	]);	5✔
82
83	Log::info("UserDataExport: completed export {$exportId}", ['userid' => $userId]);	5✔
84	}
85
86	private function assembleUserData(int $userId): array	5✔
87	{
88	$data = [];	5✔
89
90	$data['basic'] = $this->getBasicInfo($userId);	5✔
91	$data['emails'] = $this->getEmails($userId);	5✔
92	$data['logins'] = $this->getLogins($userId);	5✔
93	$data['invitations'] = $this->getInvitations($userId);	5✔
94	$data['memberships'] = $this->getMemberships($userId);	5✔
95	$data['memberships_history'] = $this->getMembershipHistory($userId);	5✔
96	$data['searches'] = $this->getSearches($userId);	5✔
97	$data['alerts'] = $this->getAlerts($userId);	5✔
98	$data['donations'] = $this->getDonations($userId);	5✔
99	$data['giftaid'] = $this->getGiftAid($userId);	5✔
100	$data['bans'] = $this->getBans($userId);	5✔
101	$data['spam_reports'] = $this->getSpamReports($userId);	5✔
102	$data['spam_domains'] = $this->getSpamDomains($userId);	5✔
103	$data['images'] = $this->getImages($userId);	5✔
104	$data['notifications'] = $this->getNotifications($userId);	5✔
105	$data['addresses'] = $this->getAddresses($userId);	5✔
106	$data['community_events'] = $this->getCommunityEvents($userId);	5✔
107	$data['volunteering'] = $this->getVolunteering($userId);	5✔
108	$data['comments'] = $this->getComments($userId);	5✔
109	$data['ratings'] = $this->getRatings($userId);	5✔
110	$data['excluded_locations'] = $this->getExcludedLocations($userId);	5✔
111	$data['messages'] = $this->getMessages($userId);	5✔
112	$data['chats'] = $this->getChats($userId);	5✔
113	$data['newsfeed'] = $this->getNewsfeed($userId);	5✔
114	$data['aboutme'] = $this->getAboutMe($userId);	5✔
115	$data['stories'] = $this->getStories($userId);	5✔
116	$data['exports'] = $this->getExportHistory($userId);	5✔
117	$data['logs'] = $this->getLogs($userId);	5✔
118
119	return $data;	5✔
120	}
121
122	private function getBasicInfo(int $userId): array	5✔
123	{
124	$user = DB::table('users')->where('id', $userId)->first();	5✔
125
126	if (!$user) {	5✔
NEW 127	return ['id' => $userId];	×
128	}
129
130	$d = [	5✔
131	'id' => $user->id,	5✔
132	'fullname' => $user->fullname,	5✔
133	'firstname' => $user->firstname,	5✔
134	'lastname' => $user->lastname,	5✔
135	'systemrole' => $user->systemrole,	5✔
136	'added' => $user->added,	5✔
137	'lastaccess' => $user->lastaccess,	5✔
138	'bouncing' => $user->bouncing ? 'Yes' : 'No',	5✔
139	'permissions' => $user->permissions,	5✔
140	'invitesleft' => $user->invitesleft,	5✔
141	];	5✔
142
143	if ($user->lastlocation) {	5✔
NEW 144	$loc = DB::table('locations')->where('id', $user->lastlocation)->first();	×
NEW 145	if ($loc) {	×
NEW 146	$d['last_posted_location'] = "{$loc->name} ({$loc->lat}, {$loc->lng})";	×
147	}
148	}
149
150	return $d;	5✔
151	}
152
153	private function getEmails(int $userId): array	5✔
154	{
155	return DB::table('users_emails')	5✔
156	->where('userid', $userId)	5✔
157	->select('email', 'preferred', 'added', 'validated', 'bounced')	5✔
158	->orderBy('id', 'asc')	5✔
159	->get()	5✔
160	->map(fn($e) => (array) $e)	5✔
161	->toArray();	5✔
162	}
163
164	private function getLogins(int $userId): array	5✔
165	{
166	return DB::table('users_logins')	5✔
167	->where('userid', $userId)	5✔
168	->select('type', 'uid', 'added', 'lastaccess')	5✔
169	->orderBy('added', 'asc')	5✔
170	->get()	5✔
171	->map(fn($e) => (array) $e)	5✔
172	->toArray();	5✔
173	}
174
175	private function getInvitations(int $userId): array	5✔
176	{
177	return DB::table('users_invitations')	5✔
178	->where('userid', $userId)	5✔
179	->select('email', 'date')	5✔
180	->orderBy('date', 'asc')	5✔
181	->get()	5✔
182	->map(fn($e) => (array) $e)	5✔
183	->toArray();	5✔
184	}
185
186	private function getMemberships(int $userId): array	5✔
187	{
188	return DB::table('memberships')	5✔
189	->join('groups', 'memberships.groupid', '=', 'groups.id')	5✔
190	->where('memberships.userid', $userId)	5✔
191	->select(	5✔
192	'memberships.groupid',	5✔
193	'memberships.collection',	5✔
194	'memberships.added',	5✔
195	DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')	5✔
196	)	5✔
197	->orderBy('memberships.added', 'asc')	5✔
198	->get()	5✔
199	->map(fn($e) => (array) $e)	5✔
200	->toArray();	5✔
201	}
202
203	private function getMembershipHistory(int $userId): array	5✔
204	{
205	return DB::table('memberships_history')	5✔
206	->join('groups', 'memberships_history.groupid', '=', 'groups.id')	5✔
207	->where('memberships_history.userid', $userId)	5✔
208	->select(	5✔
209	'memberships_history.groupid',	5✔
210	'memberships_history.collection',	5✔
211	'memberships_history.added',	5✔
212	DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')	5✔
213	)	5✔
214	->orderBy('memberships_history.added', 'asc')	5✔
215	->get()	5✔
216	->map(fn($e) => (array) $e)	5✔
217	->toArray();	5✔
218	}
219
220	private function getSearches(int $userId): array	5✔
221	{
222	return DB::table('search_history')	5✔
223	->leftJoin('locations', 'search_history.locationid', '=', 'locations.id')	5✔
224	->where('search_history.userid', $userId)	5✔
225	->select('search_history.date', 'search_history.term', 'locations.name as location')	5✔
226	->orderBy('search_history.date', 'asc')	5✔
227	->get()	5✔
228	->map(fn($e) => (array) $e)	5✔
229	->toArray();	5✔
230	}
231
232	private function getAlerts(int $userId): array	5✔
233	{
234	return DB::table('alerts_tracking')	5✔
235	->join('alerts', 'alerts_tracking.alertid', '=', 'alerts.id')	5✔
236	->where('alerts_tracking.userid', $userId)	5✔
237	->whereNotNull('alerts_tracking.responded')	5✔
238	->select('alerts.subject', 'alerts_tracking.responded', 'alerts_tracking.response')	5✔
239	->orderBy('alerts_tracking.responded', 'asc')	5✔
240	->get()	5✔
241	->map(fn($e) => (array) $e)	5✔
242	->toArray();	5✔
243	}
244
245	private function getDonations(int $userId): array	5✔
246	{
247	return DB::table('users_donations')	5✔
248	->where('userid', $userId)	5✔
249	->orderBy('timestamp', 'asc')	5✔
250	->get()	5✔
251	->map(fn($e) => (array) $e)	5✔
252	->toArray();	5✔
253	}
254
255	private function getGiftAid(int $userId): array	5✔
256	{
257	return DB::table('giftaid')	5✔
258	->where('userid', $userId)	5✔
259	->orderBy('timestamp', 'asc')	5✔
260	->get()	5✔
261	->map(fn($e) => (array) $e)	5✔
262	->toArray();	5✔
263	}
264
265	private function getBans(int $userId): array	5✔
266	{
267	$bans = DB::table('users_banned')	5✔
268	->leftJoin('groups', 'users_banned.groupid', '=', 'groups.id')	5✔
269	->leftJoin('users_emails', function ($join) {	5✔
270	$join->on('users_banned.userid', '=', 'users_emails.userid')	5✔
271	->where('users_emails.preferred', '=', 1);	5✔
272	})	5✔
273	->where('users_banned.byuser', $userId)	5✔
274	->select(	5✔
275	'users_banned.date',	5✔
276	'users_banned.userid',	5✔
277	'users_emails.email',	5✔
278	DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')	5✔
279	)	5✔
280	->orderBy('users_banned.date', 'asc')	5✔
281	->get()	5✔
282	->map(fn($e) => (array) $e)	5✔
283	->toArray();	5✔
284
285	return $bans;	5✔
286	}
287
288	private function getSpamReports(int $userId): array	5✔
289	{
290	return DB::table('spam_users')	5✔
291	->where('byuserid', $userId)	5✔
292	->leftJoin('users_emails', function ($join) {	5✔
293	$join->on('spam_users.userid', '=', 'users_emails.userid')	5✔
294	->where('users_emails.preferred', '=', 1);	5✔
295	})	5✔
296	->select('spam_users.userid', 'spam_users.added', 'spam_users.reason', 'users_emails.email')	5✔
297	->orderBy('spam_users.added', 'asc')	5✔
298	->get()	5✔
299	->map(fn($e) => (array) $e)	5✔
300	->toArray();	5✔
301	}
302
303	private function getSpamDomains(int $userId): array	5✔
304	{
305	return DB::table('spam_whitelist_links')	5✔
306	->where('userid', $userId)	5✔
307	->select('domain', 'date')	5✔
308	->orderBy('date', 'asc')	5✔
309	->get()	5✔
310	->map(fn($e) => (array) $e)	5✔
311	->toArray();	5✔
312	}
313
314	private function getImages(int $userId): array	5✔
315	{
316	return DB::table('users_images')	5✔
317	->where('userid', $userId)	5✔
318	->select('id', 'url')	5✔
319	->get()	5✔
320	->map(fn($e) => (array) $e)	5✔
321	->toArray();	5✔
322	}
323
324	private function getNotifications(int $userId): array	5✔
325	{
326	return DB::table('users_notifications')	5✔
327	->where('touser', $userId)	5✔
328	->where('seen', 1)	5✔
329	->select('timestamp', 'url')	5✔
330	->orderBy('timestamp', 'asc')	5✔
331	->get()	5✔
332	->map(fn($e) => (array) $e)	5✔
333	->toArray();	5✔
334	}
335
336	private function getAddresses(int $userId): array	5✔
337	{
338	return DB::table('users_addresses')	5✔
339	->where('userid', $userId)	5✔
340	->select('id', 'pafid', 'lat', 'lng')	5✔
341	->get()	5✔
342	->map(fn($e) => (array) $e)	5✔
343	->toArray();	5✔
344	}
345
346	private function getCommunityEvents(int $userId): array	5✔
347	{
348	return DB::table('communityevents')	5✔
349	->where('userid', $userId)	5✔
350	->select('id', 'title', 'description', 'location', 'added')	5✔
351	->orderBy('added', 'asc')	5✔
352	->get()	5✔
353	->map(fn($e) => (array) $e)	5✔
354	->toArray();	5✔
355	}
356
357	private function getVolunteering(int $userId): array	5✔
358	{
359	return DB::table('volunteering')	5✔
360	->where('userid', $userId)	5✔
361	->select('id', 'title', 'description', 'location', 'added')	5✔
362	->orderBy('added', 'asc')	5✔
363	->get()	5✔
364	->map(fn($e) => (array) $e)	5✔
365	->toArray();	5✔
366	}
367
368	private function getComments(int $userId): array	5✔
369	{
370	// users_comments columns are user1-user11 (one per flag/comment category).
371	// Export the flag status and comment text columns.
372	return DB::table('users_comments')	5✔
373	->where('byuserid', $userId)	5✔
374	->leftJoin('users_emails', function ($join) {	5✔
375	$join->on('users_comments.userid', '=', 'users_emails.userid')	5✔
376	->where('users_emails.preferred', '=', 1);	5✔
377	})	5✔
378	->select(	5✔
379	'users_comments.userid',	5✔
380	'users_emails.email',	5✔
381	'users_comments.date',	5✔
382	'users_comments.flag',	5✔
383	'users_comments.user1',	5✔
384	'users_comments.user2',	5✔
385	'users_comments.user3'	5✔
386	)	5✔
387	->orderBy('users_comments.date', 'asc')	5✔
388	->get()	5✔
389	->map(fn($e) => (array) $e)	5✔
390	->toArray();	5✔
391	}
392
393	private function getRatings(int $userId): array	5✔
394	{
395	return DB::table('ratings')	5✔
396	->where('ratings.rater', $userId)	5✔
397	->leftJoin('users_emails', function ($join) {	5✔
398	$join->on('ratings.ratee', '=', 'users_emails.userid')	5✔
399	->where('users_emails.preferred', '=', 1);	5✔
400	})	5✔
401	->select('ratings.ratee', 'users_emails.email', 'ratings.rating', 'ratings.timestamp')	5✔
402	->orderBy('ratings.timestamp', 'asc')	5✔
403	->get()	5✔
404	->map(fn($e) => (array) $e)	5✔
405	->toArray();	5✔
406	}
407
408	private function getExcludedLocations(int $userId): array	5✔
409	{
410	return DB::table('locations_excluded')	5✔
411	->where('locations_excluded.userid', $userId)	5✔
412	->leftJoin('groups', 'locations_excluded.groupid', '=', 'groups.id')	5✔
413	->leftJoin('locations', 'locations_excluded.locationid', '=', 'locations.id')	5✔
414	->select(	5✔
415	DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname'),	5✔
416	'locations.name as location',	5✔
417	'locations_excluded.date'	5✔
418	)	5✔
419	->orderBy('locations_excluded.date', 'asc')	5✔
420	->get()	5✔
421	->map(fn($e) => (array) $e)	5✔
422	->toArray();	5✔
423	}
424
425	private function getMessages(int $userId): array	5✔
426	{
427	return DB::table('messages')	5✔
428	->join('messages_groups', 'messages.id', '=', 'messages_groups.msgid')	5✔
429	->join('groups', 'messages_groups.groupid', '=', 'groups.id')	5✔
430	->where('messages.fromuser', $userId)	5✔
431	->select(	5✔
432	'messages.id',	5✔
433	'messages.subject',	5✔
434	'messages.type',	5✔
435	'messages.arrival',	5✔
436	'messages_groups.collection',	5✔
437	'messages_groups.groupid',	5✔
438	DB::raw('COALESCE(groups.namefull, groups.nameshort) AS groupname')	5✔
439	)	5✔
440	->orderBy('messages.arrival', 'asc')	5✔
441	->get()	5✔
442	->map(fn($e) => (array) $e)	5✔
443	->toArray();	5✔
444	}
445
446	private function getChats(int $userId): array	5✔
447	{
448	$chatIds = DB::select(	5✔
449	"SELECT DISTINCT chat_rooms.id FROM chat_rooms	5✔
450	INNER JOIN (
451	SELECT DISTINCT chatid FROM chat_roster WHERE userid = ?
452	UNION
453	SELECT DISTINCT chatid FROM chat_messages WHERE userid = ? OR reviewedby = ?
454	) t ON t.chatid = chat_rooms.id
455	ORDER BY chat_rooms.id ASC",	5✔
456	[$userId, $userId, $userId]	5✔
457	);	5✔
458
459	$chats = [];	5✔
460
461	foreach ($chatIds as $row) {	5✔
NEW 462	$chatId = $row->id;	×
463
NEW 464	$room = DB::table('chat_rooms')->where('id', $chatId)->first();	×
NEW 465	if (!$room) {	×
NEW 466	continue;	×
467	}
468
NEW 469	$roster = DB::table('chat_roster')	×
NEW 470	->where('chatid', $chatId)	×
NEW 471	->where('userid', $userId)	×
NEW 472	->select('date', 'lastip')	×
NEW 473	->first();	×
474
NEW 475	$messages = DB::table('chat_messages')	×
NEW 476	->where('chatid', $chatId)	×
NEW 477	->where(function ($q) use ($userId) {	×
NEW 478	$q->where('userid', $userId)->orWhere('reviewedby', $userId);	×
NEW 479	})	×
NEW 480	->select('id', 'date', 'message', 'type', 'userid', 'reviewedby')	×
NEW 481	->orderBy('date', 'asc')	×
NEW 482	->get()	×
NEW 483	->map(fn($m) => (array) $m)	×
NEW 484	->toArray();	×
485
NEW 486	if (count($messages) > 0) {	×
NEW 487	$chats[] = [	×
NEW 488	'id' => $chatId,	×
NEW 489	'chattype' => $room->chattype,	×
NEW 490	'date' => $roster ? $roster->date : null,	×
NEW 491	'lastip' => $roster ? $roster->lastip : null,	×
NEW 492	'messages' => $messages,	×
NEW 493	];	×
494	}
495	}
496
497	return $chats;	5✔
498	}
499
500	private function getNewsfeed(int $userId): array	5✔
501	{
502	$items = DB::table('newsfeed')	5✔
503	->where('userid', $userId)	5✔
504	->select('id', 'timestamp', 'type', 'message', 'msgid', 'groupid')	5✔
505	->orderBy('timestamp', 'asc')	5✔
506	->get()	5✔
507	->map(fn($e) => (array) $e)	5✔
508	->toArray();	5✔
509
510	$unfollows = DB::table('newsfeed_unfollow')	5✔
511	->where('userid', $userId)	5✔
512	->select('newsfeedid', 'timestamp')	5✔
513	->orderBy('timestamp', 'asc')	5✔
514	->get()	5✔
515	->map(fn($e) => (array) $e)	5✔
516	->toArray();	5✔
517
518	$likes = DB::table('newsfeed_likes')	5✔
519	->where('userid', $userId)	5✔
520	->select('newsfeedid', 'timestamp')	5✔
521	->orderBy('timestamp', 'asc')	5✔
522	->get()	5✔
523	->map(fn($e) => (array) $e)	5✔
524	->toArray();	5✔
525
526	$reports = DB::table('newsfeed_reports')	5✔
527	->where('userid', $userId)	5✔
528	->select('newsfeedid', 'timestamp')	5✔
529	->orderBy('timestamp', 'asc')	5✔
530	->get()	5✔
531	->map(fn($e) => (array) $e)	5✔
532	->toArray();	5✔
533
534	return [	5✔
535	'items' => $items,	5✔
536	'unfollows' => $unfollows,	5✔
537	'likes' => $likes,	5✔
538	'reports' => $reports,	5✔
539	];	5✔
540	}
541
542	private function getAboutMe(int $userId): array	5✔
543	{
544	return DB::table('users_aboutme')	5✔
545	->where('userid', $userId)	5✔
546	->whereRaw('LENGTH(text) > 5')	5✔
547	->select('timestamp', 'text')	5✔
548	->orderBy('timestamp', 'asc')	5✔
549	->get()	5✔
550	->map(fn($e) => (array) $e)	5✔
551	->toArray();	5✔
552	}
553
554	private function getStories(int $userId): array	5✔
555	{
556	$stories = DB::table('users_stories')	5✔
557	->where('userid', $userId)	5✔
558	->select('date', 'headline', 'story')	5✔
559	->orderBy('date', 'asc')	5✔
560	->get()	5✔
561	->map(fn($e) => (array) $e)	5✔
562	->toArray();	5✔
563
564	$likes = DB::table('users_stories_likes')	5✔
565	->where('userid', $userId)	5✔
566	->select('storyid')	5✔
567	->get()	5✔
568	->map(fn($e) => (array) $e)	5✔
569	->toArray();	5✔
570
571	return [	5✔
572	'stories' => $stories,	5✔
573	'likes' => $likes,	5✔
574	];	5✔
575	}
576
577	private function getExportHistory(int $userId): array	5✔
578	{
579	return DB::table('users_exports')	5✔
580	->where('userid', $userId)	5✔
581	->select('userid', 'started', 'completed')	5✔
582	->orderBy('requested', 'asc')	5✔
583	->get()	5✔
584	->map(fn($e) => (array) $e)	5✔
585	->toArray();	5✔
586	}
587
588	private function getLogs(int $userId): array	5✔
589	{
590	return DB::table('logs')	5✔
591	->where('byuser', $userId)	5✔
592	->orWhere('user', $userId)	5✔
593	->select('id', 'timestamp', 'type', 'subtype', 'user', 'byuser', 'groupid', 'text')	5✔
594	->orderBy('timestamp', 'asc')	5✔
595	->limit(1000)	5✔
596	->get()	5✔
597	->map(fn($e) => (array) $e)	5✔
598	->toArray();	5✔
599	}
600	}

Freegle / Iznik / 11342

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