EloquentMeetingsRepository::getPreviousMeeting()   A
last analyzed

Complexity

Conditions 2
Paths 2

Size

Total Lines 23
Code Lines 8

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 23
c 0
b 0
f 0
rs 9.0856
cc 2
eloc 8
nc 2
nop 1
1
<?php
2
3
/**
4
 * Storgman - Student Organizations Management
5
 * Copyright (C) 2014-2016, Dejan Angelov <[email protected]>
6
 *
7
 * This file is part of Storgman.
8
 *
9
 * Storgman is free software: you can redistribute it and/or modify
10
 * it under the terms of the GNU General Public License as published by
11
 * the Free Software Foundation, either version 3 of the License, or
12
 * (at your option) any later version.
13
 *
14
 * Storgman is distributed in the hope that it will be useful,
15
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17
 * GNU General Public License for more details.
18
 *
19
 * You should have received a copy of the GNU General Public License
20
 * along with Storgman.  If not, see <http://www.gnu.org/licenses/>.
21
 *
22
 * @package Storgman
23
 * @copyright Copyright (C) 2014-2016, Dejan Angelov <[email protected]>
24
 * @license https://github.com/angelov/storgman/blob/master/LICENSE
25
 * @author Dejan Angelov <[email protected]>
26
 */
27
28
namespace Angelov\Storgman\Meetings\Repositories;
29
30
use Angelov\Storgman\Core\Repositories\AbstractEloquentRepository;
31
use Angelov\Storgman\Core\DateTime;
32
use Angelov\Storgman\Meetings\Exceptions\NoPreviousMeetingException;
33
use Angelov\Storgman\Meetings\Meeting;
34
use Angelov\Storgman\Meetings\Reports\MeetingAttendantsTypeReport;
35
use Angelov\Storgman\Members\Member;
36
use Angelov\Storgman\Meetings\Reports\MeetingsAttendanceDetailsReport;
37
use Angelov\Storgman\Meetings\Reports\MeetingsPerMonthReport;
38
use Carbon\Carbon;
39
use DB;
40
41
class EloquentMeetingsRepository extends AbstractEloquentRepository implements MeetingsRepositoryInterface
42
{
43
    public function __construct(Meeting $meeting)
44
    {
45
        parent::__construct($meeting);
46
    }
47
48
    public function store(Meeting $meeting)
49
    {
50
        $meeting->save();
51
    }
52
53
    public function countMeetingsInPeriod(Carbon $from, Carbon $to)
54
    {
55
        $from = $from->toDateString();
56
        $to = $to->toDateString();
57
58
        return Meeting::whereBetween('date', array($from, $to))->count();
59
    }
60
61
    public function calculateAttendanceDetails()
62
    {
63
        // The query works with both MySQL and PostgreSQL
64
        $result = (array)DB::select(
65
            '
66
                SELECT tbl2.total_meetings AS meetings,
67
                       total_attendants AS attendants,
68
                       round(total_attendants/tbl2.total_meetings) AS average
69
                FROM
70
                  (SELECT sum(details.attendants) AS total_attendants
71
                   FROM
72
                     (SELECT meeting_id AS meeting,
73
                             count(member_id) AS attendants
74
                      FROM meeting_member
75
                      GROUP BY meeting_id) AS details) AS tbl1,
76
77
                  (SELECT count(id) AS total_meetings
78
                   FROM meetings) AS tbl2
79
            '
80
        )[0];
81
82
        $report = new MeetingsAttendanceDetailsReport();
83
84
        $report->setMeetings($result['meetings'] ? : 0);
85
        $report->setAttendants($result['attendants'] ? : 0);
86
        $report->setAverage($result['average'] ? : 0);
87
88
        return $report;
89
    }
90
91
    public function countAttendanceForMember(Member $member, Carbon $from, Carbon $to)
92
    {
93
        $from = $from->toDateString();
94
        $to = $to->toDateString();
95
96
        // The query works with both MySQL and PostgreSQL
97
        $result = DB::select(
98
            '
99
                SELECT count(meeting_id) AS attended
100
                FROM meetings,
101
                     meeting_member
102
                WHERE meeting_id=meetings.id
103
                  AND member_id = ?
104
                  AND date BETWEEN ? AND ?
105
            ',
106
            array($member->getId(), $from, $to)
107
        )[0];
108
109
        return $result->attended;
110
    }
111
112 View Code Duplication
    public function countMeetingsPerMonth(DateTime $from, DateTime $to)
0 ignored issues
show
Duplication introduced by
This method seems to be duplicated in your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
113
    {
114
        $report = new MeetingsPerMonthReport($from, $to);
115
        $from = $from->toDateString();
116
        $to = $to->toDateString();
117
118
        $res = DB::select(
119
            '
120
                SELECT concat(YEAR, \'-\', lpad(cast(MONTH AS CHAR(2)), 2, \'0\')) AS month,
121
                       count(id) AS count
122
                FROM
123
                  ( SELECT id,
124
                           extract(MONTH
125
                                   FROM date) AS MONTH,
126
                           extract(YEAR
127
                                   FROM date) AS YEAR
128
                   FROM
129
                     ( SELECT id, date
130
                      FROM meetings
131
                      WHERE date BETWEEN ? AND ?) tbl1) tbl2
132
                GROUP BY YEAR,
133
                         MONTH
134
            ',
135
            [$from, $to]
136
        );
137
138
        foreach ($res as &$current) {
139
            $current = (array)$current;
140
            $report->addMonth($current["month"], (int)$current["count"]);
141
        };
142
143
        return $report;
144
    }
145
146 View Code Duplication
    public function countAttendedMeetingsByMemberPerMonth(Member $member, DateTime $from, DateTime $to)
0 ignored issues
show
Duplication introduced by
This method seems to be duplicated in your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
147
    {
148
        $report = new MeetingsPerMonthReport($from, $to);
149
        $from = $from->toDateString();
150
        $to = $to->toDateString();
151
152
        $res = DB::select(
153
            '
154
                SELECT concat(YEAR, \'-\', lpad(cast(MONTH AS CHAR(2)), 2, \'0\')) AS month,
155
                       count(id) AS count
156
                FROM
157
                  (SELECT id,
158
                          extract(MONTH
159
                                  FROM date) AS MONTH,
160
                          extract(YEAR
161
                                  FROM date) AS YEAR
162
                   FROM
163
                     (SELECT id, date
164
                      FROM meetings
165
                      WHERE id IN
166
                          (SELECT meeting_id
167
                           FROM meeting_member
168
                           WHERE member_id = ?)
169
                        AND date BETWEEN ? AND ? ) tbl1) tbl2
170
                GROUP BY YEAR,
171
                         MONTH
172
            ',
173
            [$member->getId(), $from, $to]
174
        );
175
176
        foreach ($res as &$current) {
177
            $current = (array)$current;
178
            $report->addMonth($current["month"], (int)$current["count"]);
179
        };
180
181
        return $report;
182
    }
183
184
    public function getAverageNumberOfAttendants()
185
    {
186
        $result = DB::select(
187
          '
188
                  SELECT avg(attendants) AS average
189
                  FROM
190
                    (SELECT count(meeting_member.id) AS attendants
191
                     FROM meetings,
192
                          meeting_member
193
                     WHERE meetings.id = meeting_member.meeting_id
194
                       AND meetings.date <= now()
195
                     GROUP BY meeting_member.meeting_id) AS na;
196
        '
197
        );
198
199
        $average = (int) round($result[0]->average);
200
201
        return $average;
202
    }
203
204
    // @todo optimize
205
    public function getPreviousMeeting(Meeting $meeting)
206
    {
207
        $result = DB::select(
208
            '
209
                    SELECT id,
210
                      (SELECT id
211
                       FROM meetings e2
212
                       WHERE e2.date < e1.date
213
                       ORDER BY date DESC LIMIT 1) AS previous_meeting_id
214
                    FROM meetings e1
215
                    WHERE id = ?
216
217
            ', [$meeting->getId()]
218
        );
219
220
        $prevId = (int) $result[0]->previous_meeting_id;
221
222
        if (! $prevId) {
223
            throw new NoPreviousMeetingException();
224
        }
225
226
        return Meeting::find($prevId);
227
    }
228
229
    public function getAttendantsTypeForMeeting(Meeting $meeting)
230
    {
231
        try {
232
            $previous = $this->getPreviousMeeting($meeting);
233
        } catch (NoPreviousMeetingException $e) {
234
            $attendants = count($meeting->getAttendants());
235
            return new MeetingAttendantsTypeReport($meeting, $attendants, 0);
236
        }
237
238
        $result = DB::select(
239
            '
240
                SELECT sum(CASE WHEN cnt > 1 THEN 1 ELSE 0 END) AS returning
241
                FROM
242
                  (SELECT member_id,
243
                          count(member_id) AS cnt
244
                   FROM
245
                     (SELECT meeting_member.member_id
246
                      FROM meetings,
247
                           meeting_member
248
                      WHERE meetings.id = meeting_member.meeting_id
249
                        AND meetings.id IN (?, ?)) attendants
250
                   GROUP BY member_id) attendants_count
251
            ', [$meeting->getId(), $previous->getId()]
252
        );
253
254
        $result = $result[0];
255
        $total = count($meeting->getAttendants());
256
        $returning = (int) $result->returning;
257
        $new = $total - $returning;
258
259
260
        return new MeetingAttendantsTypeReport($meeting, $new, $returning);
261
    }
262
}
263