User: Bugfixes & code cleanup

5 files changed, 124 insertions, 100 deletions

diff --git a/includes/pages/admin_active.php b/includes/pages/admin_active.php
index 8d9f35bd..5b0beae9 100644
--- a/includes/pages/admin_active.php
+++ b/includes/pages/admin_active.php
@@ -2,6 +2,8 @@
 
 use Engelsystem\Models\User\State;
 use Engelsystem\Models\User\User;
+use Illuminate\Database\Query\Builder;
+use Illuminate\Database\Query\JoinClause;
 
 /**
  * @return string
@@ -23,8 +25,7 @@ function admin_active()
     $msg = '';
     $search = '';
     $forced_count = State::whereForceActive(true)->count();
-    $count = $forced_count;
-    $limit = '';
+    $count = null;
     $set_active = '';
 
     if ($request->has('search')) {
@@ -34,8 +35,6 @@ function admin_active()
     $show_all_shifts = $request->has('show_all_shifts');
 
     if ($request->has('set_active')) {
-        $valid = true;
-
         if ($request->has('count') && preg_match('/^\d+$/', $request->input('count'))) {
             $count = strip_request_item('count');
             if ($count < $forced_count) {
@@ -46,41 +45,43 @@ function admin_active()
                 redirect(page_link_to('admin_active'));
             }
         } else {
-            $valid = false;
-            $msg .= error(__('Please enter a number of angels to be marked as active.'), true);
+            $msg .= error(__('Please enter a number of angels to be marked as active.'));
+            redirect(page_link_to('admin_active'));
         }
 
-        if ($valid) {
-            $limit = ' LIMIT ' . $count;
-        }
         if ($request->has('ack')) {
             State::query()
                 ->where('got_shirt', '=', false)
+                ->where('got_shirt', '=', false)
                 ->update(['active' => false]);
 
-            /** @var User[] $users */
-            $users = User::query()->raw(sprintf('
-                  SELECT
-                      `users`.*,
-                      COUNT(`ShiftEntry`.`id`) AS `shift_count`,
-                      (%s + (
-                          SELECT COALESCE(SUM(`work_hours`) * 3600, 0) FROM `UserWorkLog` WHERE `user_id`=`users`.`id`
-                          AND `work_timestamp` < %s
-                      )) AS `shift_length`
-                  FROM `users`
-                  LEFT JOIN `ShiftEntry` ON `users`.`id` = `ShiftEntry`.`UID`
-                  LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID`
-                  LEFT JOIN `users_state` ON `users`.`id` = `users_state`.`user_id`
-                  WHERE `users_state`.`arrived` = 1
-                  AND `users_state`.`force_active` = 0
-                  GROUP BY `users`.`id`
-                  ORDER BY `force_active` DESC, `shift_length` DESC
-                  %s
-                ',
-                $shift_sum_formula,
-                time(),
-                $limit
-            ));
+            $query = User::query()
+                ->selectRaw(
+                    sprintf(
+                        '
+                            users.*,
+                            COUNT(ShiftEntry.id) AS shift_count,
+                                (%s + (
+                                      SELECT COALESCE(SUM(`work_hours`) * 3600, 0) FROM `UserWorkLog` WHERE `user_id`=`users`.`id`
+                                      AND `work_timestamp` < ?
+                                )) AS `shift_length`
+                        ',
+                        $shift_sum_formula
+                    ),
+                    [time()]
+                )
+                ->leftJoin('ShiftEntry', 'users.id', '=', 'ShiftEntry.UID')
+                ->leftJoin('Shifts', 'ShiftEntry.SID', '=', 'Shifts.SID')
+                ->leftJoin('users_state', 'users.id', '=', 'users_state.user_id')
+                ->where('users_state.arrived', '=', true)
+                ->where('users_state.force_active', '=', false)
+                ->groupBy('users.id')
+                ->orderByDesc('force_active')
+                ->orderByDesc('shift_length')
+                ->orderByDesc('name')
+                ->limit($count);
+
+            $users = $query->get();
             $user_nicks = [];
             foreach ($users as $usr) {
                 $usr->state->active = true;
@@ -88,10 +89,9 @@ function admin_active()
                 $user_nicks[] = User_Nick_render($usr);
             }
 
-            State::whereForceActive(true)->update(['active' => 'true']);
+            State::whereForceActive(true)->update(['active' => true]);
             engelsystem_log('These angels are active now: ' . join(', ', $user_nicks));
 
-            $limit = '';
             $msg = success(__('Marked angels.'), true);
         } else {
             $set_active = '<a href="' . page_link_to('admin_active', ['search' => $search]) . '">&laquo; '
@@ -120,7 +120,7 @@ function admin_active()
     } elseif ($request->has('not_active') && preg_match('/^\d+$/', $request->input('not_active'))) {
         $user_id = $request->input('not_active');
         $user_source = User::find($user_id);
-        if (!$user_source) {
+        if ($user_source) {
             $user_source->state->active = false;
             $user_source->state->save();
             engelsystem_log('User ' . User_Nick_render($user_source) . ' is NOT active now.');
@@ -131,7 +131,7 @@ function admin_active()
     } elseif ($request->has('tshirt') && preg_match('/^\d+$/', $request->input('tshirt'))) {
         $user_id = $request->input('tshirt');
         $user_source = User::find($user_id);
-        if (!$user_source) {
+        if ($user_source) {
             $user_source->state->got_shirt = true;
             $user_source->state->save();
             engelsystem_log('User ' . User_Nick_render($user_source) . ' has tshirt now.');
@@ -142,7 +142,7 @@ function admin_active()
     } elseif ($request->has('not_tshirt') && preg_match('/^\d+$/', $request->input('not_tshirt'))) {
         $user_id = $request->input('not_tshirt');
         $user_source = User::find($user_id);
-        if (!$user_source) {
+        if ($user_source) {
             $user_source->state->got_shirt = false;
             $user_source->state->save();
             engelsystem_log('User ' . User_Nick_render($user_source) . ' has NO tshirt.');
@@ -152,28 +152,45 @@ function admin_active()
         }
     }
 
-    $users = User::query()->raw(sprintf('
-            SELECT
-                `users`.*,
-                COUNT(`ShiftEntry`.`id`) AS `shift_count`,
-                (%s + (
-                    SELECT COALESCE(SUM(`work_hours`) * 3600, 0) FROM `UserWorkLog` WHERE `user_id`=`users`.`id`
-                    AND `work_timestamp` < %s
-                )) AS `shift_length`
-            FROM `users`
-            LEFT JOIN `users_state` ON `users`.`id` = `users_state`.`user_id`
-            LEFT JOIN `ShiftEntry` ON `users`.`id` = `ShiftEntry`.`UID`
-            LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` '
-        . ($show_all_shifts ? '' : 'AND (`Shifts`.`end` < ' . time() . " OR `Shifts`.`end` IS NULL)") . '
-            WHERE `users_state`.`arrived` = 1
-            GROUP BY `users`.`id`
-            ORDER BY `users_state`.`force_active` DESC, `shift_length` DESC
-            %s
-        ',
-        $shift_sum_formula,
-        time(),
-        $limit
-    ));
+    $query = User::query()
+        ->selectRaw(
+            sprintf(
+                '
+                    users.*,
+                    COUNT(ShiftEntry.id) AS shift_count,
+                        (%s + (
+                              SELECT COALESCE(SUM(`work_hours`) * 3600, 0) FROM `UserWorkLog` WHERE `user_id`=`users`.`id`
+                              AND `work_timestamp` < ?
+                        )) AS `shift_length`
+                ',
+                $shift_sum_formula
+            ),
+            [time()]
+        )
+        ->leftJoin('ShiftEntry', 'users.id', '=', 'ShiftEntry.UID')
+        ->leftJoin('Shifts', function ($join) use ($show_all_shifts) {
+            /** @var JoinClause $join */
+            $join->on('ShiftEntry.SID', '=', 'Shifts.SID');
+            if ($show_all_shifts) {
+                $join->where(function ($query) {
+                    /** @var Builder $query */
+                    $query->where('Shifts.end', '<', time())
+                        ->orWhereNull('Shifts.end');
+                });
+            }
+        })
+        ->leftJoin('users_state', 'users.id', '=', 'users_state.user_id')
+        ->where('users_state.arrived', '=', true)
+        ->groupBy('users.id')
+        ->orderByDesc('force_active')
+        ->orderByDesc('shift_length')
+        ->orderByDesc('name');
+
+    if (!is_null($count)) {
+        $query->limit($count);
+    }
+
+    $users = $query->get();
     $matched_users = [];
     if ($search == '') {
         $tokens = [];
@@ -202,6 +219,7 @@ function admin_active()
         $userData['active'] = glyph_bool($usr->state->active == 1);
         $userData['force_active'] = glyph_bool($usr->state->force_active == 1);
         $userData['tshirt'] = glyph_bool($usr->state->got_shirt == 1);
+        $userData['shift_count'] = $usr['shift_count'];
 
         $actions = [];
         if (!$usr->state->active) {
@@ -262,6 +280,7 @@ function admin_active()
     foreach (array_keys($tshirt_sizes) as $size) {
         $gc = State::query()
             ->leftJoin('users_settings', 'users_state.user_id', '=', 'users_settings.user_id')
+            ->leftJoin('users_personal_data', 'users_state.user_id', '=', 'users_personal_data.user_id')
             ->where('users_state.got_shirt', '=', true)
             ->where('users_personal_data.shirt_size', '=', $size)
             ->count();
@@ -283,7 +302,7 @@ function admin_active()
             form_submit('submit', __('Search'))
         ], page_link_to('admin_active')),
         $set_active == '' ? form([
-            form_text('count', __('How much angels should be active?'), $count),
+            form_text('count', __('How much angels should be active?'), $count ? $count : $forced_count),
             form_submit('set_active', __('Preview'))
         ]) : $set_active,
         $msg . msg(),
diff --git a/includes/pages/admin_arrive.php b/includes/pages/admin_arrive.php
index 40b8bf44..870522e2 100644
--- a/includes/pages/admin_arrive.php
+++ b/includes/pages/admin_arrive.php
@@ -44,7 +44,7 @@ function admin_arrive()
         if ($user_source) {
             $user_source->state->arrived = true;
             $user_source->state->arrival_date = new Carbon\Carbon();
-            $user_source->personalData->save();
+            $user_source->state->save();
 
             engelsystem_log('User set has arrived: ' . User_Nick_render($user_source));
             success(__('Angel has been marked as arrived.'));
@@ -170,7 +170,7 @@ function admin_arrive()
             form_submit('submit', __('Search'))
         ]),
         table([
-            'nick'                            => __('Nickname'),
+            'name'                            => __('Nickname'),
             'rendered_planned_arrival_date'   => __('Planned arrival'),
             'arrived'                         => __('Arrived?'),
             'rendered_arrival_date'           => __('Arrival date'),
diff --git a/includes/pages/admin_free.php b/includes/pages/admin_free.php
index 9b1f581b..d8787f36 100644
--- a/includes/pages/admin_free.php
+++ b/includes/pages/admin_free.php
@@ -2,6 +2,7 @@
 
 use Engelsystem\Database\DB;
 use Engelsystem\Models\User\User;
+use Illuminate\Database\Query\JoinClause;
 
 /**
  * @return string
@@ -24,18 +25,6 @@ function admin_free()
         $search = strip_request_item('search');
     }
 
-    $angelTypeSearch = '';
-    $angelType = $request->input('angeltype', '');
-    if (!empty($angelType)) {
-        $angelTypeSearch = ' INNER JOIN `UserAngelTypes` ON (`UserAngelTypes`.`angeltype_id` = '
-            . DB::getPdo()->quote($angelType)
-            . ' AND `UserAngelTypes`.`user_id` = `users`.`id`';
-        if ($request->has('confirmed_only')) {
-            $angelTypeSearch .= ' AND `UserAngelTypes`.`confirm_user_id`';
-        }
-        $angelTypeSearch .= ') ';
-    }
-
     $angel_types_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`');
     $angel_types = [
         '' => 'alle Typen'
@@ -44,27 +33,34 @@ function admin_free()
         $angel_types[$angel_type['id']] = $angel_type['name'];
     }
 
-    /** @var User[] $users */
-    $users = User::query()->raw(sprintf('
-          SELECT `users`.*
-          FROM `users`
-          %s
-          LEFT JOIN `ShiftEntry` ON `users`.`id` = `ShiftEntry`.`UID`
-          LEFT JOIN `users_state` ON `users`.`id` = `users_state`.`user_id`
-          LEFT JOIN `Shifts`
-              ON (
-                  `ShiftEntry`.`SID` = `Shifts`.`SID`
-                  AND `Shifts`.`start` < %u
-                  AND `Shifts`.`end` > %u
-              )
-          WHERE `users_state`.`arrived` = 1
-          AND `Shifts`.`SID` IS NULL
-          GROUP BY `users`.`id`
-          ORDER BY `users`
-        ', $angelTypeSearch, time(), time()
-        )
-    );
+    $angelType = $request->input('angeltype', '');
+    $query = User::query()
+        ->select('users.*')
+        ->leftJoin('ShiftEntry', 'users.id', 'ShiftEntry.UID')
+        ->leftJoin('users_state', 'users.id', 'users_state.user_id')
+        ->leftJoin('Shifts', function ($join) {
+            /** @var JoinClause $join */
+            $join->on('ShiftEntry.SID', '=', 'Shifts.SID')
+                ->where('Shifts.start', '<', time())
+                ->where('Shifts.end', '>', time());
+        })
+        ->where('users_state.arrived', '=', 1)
+        ->whereNull('Shifts.SID')
+        ->groupBy('users.id');
+
+    if (!empty($angelType)) {
+        $query->join('UserAngelTypes', function ($join) use ($angelType, $request, $query) {
+            /** @var JoinClause $join */
+            $join->on('UserAngelTypes.user_id', '=', 'users.id')
+                ->where('UserAngelTypes.angeltype_id', '=', $angelType);
+
+            if ($request->has('confirmed_only')) {
+                $join->whereNotNull('UserAngelTypes.confirm_user_id');
+            }
+        });
+    }
 
+    $users = $query->get();
     $free_users_table = [];
     if ($search == '') {
         $tokens = [];
diff --git a/includes/pages/admin_user.php b/includes/pages/admin_user.php
index 3a9a484c..a87a76cf 100644
--- a/includes/pages/admin_user.php
+++ b/includes/pages/admin_user.php
@@ -60,8 +60,13 @@ function admin_user()
             $html .= "  <tr><td>email</td><td>" . '<input type="email" size="40" name="eemail" value="' . $user_source->email . '" class="form-control"></td></tr>' . "\n";
         }
         $html .= '  <tr><td>Size</td><td>'
-            . html_select_key('size', 'eSize', $tshirt_sizes, $user_source->personalData->shirt_size,
-                __('Please select...')) . '</td></tr>' . "\n";
+            . html_select_key(
+                'size',
+                'eSize',
+                $tshirt_sizes, $user_source->personalData->shirt_size,
+                __('Please select...')
+            )
+            . '</td></tr>' . "\n";
 
         $options = [
             '1' => __('Yes'),
@@ -84,7 +89,7 @@ function admin_user()
 
         // T-Shirt bekommen?
         $html .= '  <tr><td>T-Shirt</td><td>' . "\n";
-        $html .= html_options('eTshirt', $options, $user_source->personalData->shirt_size) . '</td></tr>' . "\n";
+        $html .= html_options('eTshirt', $options, $user_source->state->got_shirt) . '</td></tr>' . "\n";
 
         $html .= '</table>' . "\n" . '</td><td valign="top"></td></tr>';
 
@@ -244,7 +249,7 @@ function admin_user()
                 if (in_array('admin_active', $privileges)) {
                     $force_active = $request->input('force_active');
                 }
-                if($user_source->settings->email_human){
+                if ($user_source->settings->email_human) {
                     $user_source->email = $request->postData('eemail');
                 }
                 $user_source->name = User_validate_Nick($request->postData('eNick'));
diff --git a/includes/pages/user_settings.php b/includes/pages/user_settings.php
index bc97099e..f833eec5 100644
--- a/includes/pages/user_settings.php
+++ b/includes/pages/user_settings.php
@@ -57,7 +57,11 @@ function user_settings_main($user_source, $enable_tshirt_size, $tshirt_sizes)
 
     if ($request->has('planned_departure_date')) {
         $tmp = parse_date('Y-m-d H:i', $request->input('planned_departure_date') . ' 00:00');
-        $result = User_validate_planned_departure_date($user_source->state->arrival_date->getTimestamp(), $tmp);
+        $plannedArrivalDate = $user_source->personalData->planned_arrival_date;
+        $result = User_validate_planned_departure_date(
+            $plannedArrivalDate ? $plannedArrivalDate->getTimestamp() : 0,
+            $tmp
+        );
         $user_source->personalData->planned_departure_date = Carbon::createFromTimestamp($result->getValue());
         if (!$result->isValid()) {
             $valid = false;
@@ -66,7 +70,7 @@ function user_settings_main($user_source, $enable_tshirt_size, $tshirt_sizes)
     }
 
     // Trivia
-    $user_source->name = strip_request_item('lastname', $user_source['Name']);
+    $user_source->personalData->last_name = strip_request_item('lastname', $user_source['Name']);
     $user_source->personalData->first_name = strip_request_item('prename', $user_source['Vorname']);
     if (strlen(strip_request_item('dect')) <= 5) {
         $user_source->contact->dect = strip_request_item('dect', $user_source['DECT']);