From a2aaba9cab6b7bdf755a023ed2503cf8cf46925a Mon Sep 17 00:00:00 2001 From: Igor Scheller Date: Wed, 17 Oct 2018 01:30:10 +0200 Subject: User: Bugfixes & code cleanup --- includes/pages/admin_active.php | 139 ++++++++++++++++++++++----------------- includes/pages/admin_arrive.php | 4 +- includes/pages/admin_free.php | 60 ++++++++--------- includes/pages/admin_user.php | 13 ++-- includes/pages/user_settings.php | 8 ++- 5 files changed, 124 insertions(+), 100 deletions(-) (limited to 'includes/pages') 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 = '« ' @@ -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 .= " email" . '' . "\n"; } $html .= ' Size' - . html_select_key('size', 'eSize', $tshirt_sizes, $user_source->personalData->shirt_size, - __('Please select...')) . '' . "\n"; + . html_select_key( + 'size', + 'eSize', + $tshirt_sizes, $user_source->personalData->shirt_size, + __('Please select...') + ) + . '' . "\n"; $options = [ '1' => __('Yes'), @@ -84,7 +89,7 @@ function admin_user() // T-Shirt bekommen? $html .= ' T-Shirt' . "\n"; - $html .= html_options('eTshirt', $options, $user_source->personalData->shirt_size) . '' . "\n"; + $html .= html_options('eTshirt', $options, $user_source->state->got_shirt) . '' . "\n"; $html .= '' . "\n" . ''; @@ -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']); -- cgit v1.2.3-54-g00ecf