From d7aea575ff77cd37c326511e7ac5fe49067c63ad Mon Sep 17 00:00:00 2001 From: Igor Scheller Date: Sun, 14 Oct 2018 18:24:42 +0200 Subject: Replaced more sql queries and old data --- includes/controller/users_controller.php | 4 +- includes/mailer/shifts_mailer.php | 6 +- includes/model/Message_model.php | 8 +- includes/model/ShiftEntry_model.php | 8 +- includes/model/Shifts_model.php | 30 +------ includes/pages/admin_user.php | 48 ++++------ includes/pages/guest_login.php | 148 +++++++++++-------------------- includes/pages/guest_stats.php | 13 +-- includes/pages/user_myshifts.php | 2 +- includes/view/User_view.php | 1 - 10 files changed, 89 insertions(+), 179 deletions(-) diff --git a/includes/controller/users_controller.php b/includes/controller/users_controller.php index 61b19231..c8d8a4a9 100644 --- a/includes/controller/users_controller.php +++ b/includes/controller/users_controller.php @@ -215,9 +215,9 @@ function user_controller() ); foreach ($shift['needed_angeltypes'] as &$needed_angeltype) { $needed_angeltype['users'] = DB::select(' - SELECT `ShiftEntry`.`freeloaded`, `User`.* + SELECT `ShiftEntry`.`freeloaded`, `users`.* FROM `ShiftEntry` - JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` + JOIN `users` ON `ShiftEntry`.`UID`=`users`.`id` WHERE `ShiftEntry`.`SID` = ? AND `ShiftEntry`.`TID` = ? ', diff --git a/includes/mailer/shifts_mailer.php b/includes/mailer/shifts_mailer.php index 90153146..5304a897 100644 --- a/includes/mailer/shifts_mailer.php +++ b/includes/mailer/shifts_mailer.php @@ -64,7 +64,8 @@ function mail_shift_change($old_shift, $new_shift) $message .= $new_room['Name'] . "\n"; foreach ($users as $user) { - if ($user['email_shiftinfo']) { + $user = User::find($user->id); + if ($user->settings->email_shiftinfo) { engelsystem_email_to_user( $user, __('Your Shift has changed'), @@ -91,7 +92,8 @@ function mail_shift_delete($shift) $message .= $room['Name'] . "\n"; foreach ($users as $user) { - if ($user['email_shiftinfo']) { + $user = User::find($user->id); + if ($user->settings->email_shiftinfo) { engelsystem_email_to_user($user, __('Your Shift was deleted'), $message, true); } } diff --git a/includes/model/Message_model.php b/includes/model/Message_model.php index 89beeb07..26f7e599 100644 --- a/includes/model/Message_model.php +++ b/includes/model/Message_model.php @@ -43,10 +43,10 @@ function Message_send($receiver_user_id, $text) if ( ($text != '' && is_numeric($receiver_user_id)) && count(DB::select(' - SELECT `UID` - FROM `User` - WHERE `UID` = ? - AND NOT `UID` = ? + SELECT `id` + FROM `users` + WHERE `id` = ? + AND NOT `id` = ? LIMIT 1 ', [$receiver_user_id, $user->id])) > 0 ) { diff --git a/includes/model/ShiftEntry_model.php b/includes/model/ShiftEntry_model.php index 593e40b2..248e7d1e 100644 --- a/includes/model/ShiftEntry_model.php +++ b/includes/model/ShiftEntry_model.php @@ -48,11 +48,7 @@ function ShiftEntries_by_shift($shift_id) { return DB::select(' SELECT - `User`.`Nick`, - `User`.`email`, - `User`.`email_shiftinfo`, - `User`.`Sprache`, - `User`.`Gekommen`, + `users`.*, `ShiftEntry`.`UID`, `ShiftEntry`.`TID`, `ShiftEntry`.`SID`, @@ -60,7 +56,7 @@ function ShiftEntries_by_shift($shift_id) `ShiftEntry`.`Comment`, `ShiftEntry`.`freeloaded` FROM `ShiftEntry` - JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` + JOIN `users` ON `ShiftEntry`.`UID`=`users`.`id` JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id` WHERE `ShiftEntry`.`SID` = ?', [$shift_id] diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 9e803efd..d7799a1e 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -235,11 +235,7 @@ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { $sql = ' SELECT - `User`.`Nick`, - `User`.`email`, - `User`.`email_shiftinfo`, - `User`.`Sprache`, - `User`.`Gekommen`, + users.* `ShiftEntry`.`UID`, `ShiftEntry`.`TID`, `ShiftEntry`.`SID`, @@ -247,7 +243,7 @@ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter) `ShiftEntry`.`freeloaded` FROM `Shifts` JOIN `ShiftEntry` ON `ShiftEntry`.`SID`=`Shifts`.`SID` - JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` + JOIN `users` ON `ShiftEntry`.`UID`=`users`.`id` WHERE `Shifts`.`RID` IN (' . implode(',', $shiftsFilter->getRooms()) . ') AND `start` BETWEEN ? AND ? ORDER BY `Shifts`.`start`'; @@ -685,25 +681,3 @@ function Shift($shift_id) return $result; } - -/** - * Returns all shifts with needed angeltypes and count of subscribed jobs. - * - * @return array - */ -function Shifts() -{ - $shifts_source = DB::select(' - SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`RID`, `Room`.`Name` AS `room_name` - FROM `Shifts` - JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) - JOIN `Room` ON `Room`.`RID` = `Shifts`.`RID` - '); - - foreach ($shifts_source as &$shift) { - $needed_angeltypes = NeededAngelTypes_by_shift($shift['SID']); - $shift['angeltypes'] = $needed_angeltypes; - } - - return $shifts_source; -} diff --git a/includes/pages/admin_user.php b/includes/pages/admin_user.php index 961d59a2..3a9a484c 100644 --- a/includes/pages/admin_user.php +++ b/includes/pages/admin_user.php @@ -244,36 +244,24 @@ function admin_user() if (in_array('admin_active', $privileges)) { $force_active = $request->input('force_active'); } - $sql = ' - UPDATE `User` SET - `Nick` = ?, - `Name` = ?, - `Vorname` = ?, - `Handy` = ?, - `DECT` = ?, - ' . ($user_source->settings->email_human - ? '`email` = ' . DB::getPdo()->quote($request->postData('eemail')) . ',' - : '') . ' - `Size` = ?, - `Gekommen`= ?, - `Aktiv`= ?, - `force_active`= ?, - `Tshirt` = ? - WHERE `UID` = ? - LIMIT 1'; - DB::update($sql, [ - User_validate_Nick($request->postData('eNick')), - $request->postData('eName'), - $request->postData('eVorname'), - $request->postData('eHandy'), - $request->postData('eDECT'), - $request->postData('eSize'), - $request->postData('eGekommen'), - $request->postData('eAktiv'), - $force_active, - $request->postData('eTshirt'), - $user_id, - ]); + if($user_source->settings->email_human){ + $user_source->email = $request->postData('eemail'); + } + $user_source->name = User_validate_Nick($request->postData('eNick')); + $user_source->save(); + $user_source->personalData->first_name = $request->postData('eVorname'); + $user_source->personalData->last_name = $request->postData('eName'); + $user_source->personalData->shirt_size = $request->postData('eSize'); + $user_source->personalData->save(); + $user_source->contact->mobile = $request->postData('eHandy'); + $user_source->contact->dect = $request->postData('eDECT'); + $user_source->contact->save(); + $user_source->state->arrived = $request->postData('eGekommen'); + $user_source->state->active = $request->postData('eAktiv'); + $user_source->state->force_active = $force_active; + $user_source->state->got_shirt = $request->postData('eTshirt'); + $user_source->state->save(); + engelsystem_log( 'Updated user: ' . $request->postData('eNick') . ', ' . $request->postData('eSize') . ', arrived: ' . $request->postData('eVorname') diff --git a/includes/pages/guest_login.php b/includes/pages/guest_login.php index bc919acf..5efe4521 100644 --- a/includes/pages/guest_login.php +++ b/includes/pages/guest_login.php @@ -2,6 +2,10 @@ use Carbon\Carbon; use Engelsystem\Database\DB; +use Engelsystem\Models\User\Contact; +use Engelsystem\Models\User\PersonalData; +use Engelsystem\Models\User\Settings; +use Engelsystem\Models\User\State; use Engelsystem\Models\User\User; /** @@ -36,7 +40,7 @@ function logout_title() function guest_register() { global $privileges; - $user = Auth()->user(); + $authUser = Auth()->user(); $tshirt_sizes = config('tshirt_sizes'); $enable_tshirt_size = config('enable_tshirt_size'); $min_password_length = config('min_password_length'); @@ -48,16 +52,11 @@ function guest_register() $nick = ''; $lastName = ''; $preName = ''; - $age = 0; - $tel = ''; $dect = ''; $mobile = ''; $mail = ''; $email_shiftinfo = false; $email_by_human_allowed = false; - $jabber = ''; - $hometown = ''; - $comment = ''; $tshirt_size = ''; $password_hash = ''; $selected_angel_types = []; @@ -72,7 +71,7 @@ function guest_register() } } - if (!in_array('register', $privileges) || (!$user && !config('registration_enabled'))) { + if (!in_array('register', $privileges) || (!$authUser && !config('registration_enabled'))) { error(__('Registration is disabled.')); return page_with_title(register_title(), [ @@ -85,7 +84,7 @@ function guest_register() if ($request->has('nick') && strlen(User_validate_Nick($request->input('nick'))) > 1) { $nick = User_validate_Nick($request->input('nick')); - if (count(DB::select('SELECT `UID` FROM `User` WHERE `Nick`=? LIMIT 1', [$nick])) > 0) { + if (User::whereName($nick)->count() > 0) { $valid = false; $msg .= error(sprintf(__('Your nick "%s" already exists.'), $nick), true); } @@ -116,14 +115,6 @@ function guest_register() $email_by_human_allowed = true; } - if ($request->has('jabber') && strlen(strip_request_item('jabber')) > 0) { - $jabber = strip_request_item('jabber'); - if (!check_email($jabber)) { - $valid = false; - $msg .= error(__('Please check your jabber account information.'), true); - } - } - if ($enable_tshirt_size) { if ($request->has('tshirt_size') && isset($tshirt_sizes[$request->input('tshirt_size')])) { $tshirt_size = $request->input('tshirt_size'); @@ -173,12 +164,6 @@ function guest_register() if ($request->has('prename')) { $preName = strip_request_item('prename'); } - if ($request->has('age') && preg_match('/^\d{1,4}$/', $request->input('age'))) { - $age = strip_request_item('age'); - } - if ($request->has('tel')) { - $tel = strip_request_item('tel'); - } if ($request->has('dect')) { if (strlen(strip_request_item('dect')) <= 5) { $dect = strip_request_item('dect'); @@ -190,88 +175,71 @@ function guest_register() if ($request->has('mobile')) { $mobile = strip_request_item('mobile'); } - if ($request->has('hometown')) { - $hometown = strip_request_item('hometown'); - } - if ($request->has('comment')) { - $comment = strip_request_item_nl('comment'); - } if ($valid) { - DB::insert(' - INSERT INTO `User` ( - `color`, - `Nick`, - `Vorname`, - `Name`, - `Alter`, - `Telefon`, - `DECT`, - `Handy`, - `email`, - `email_shiftinfo`, - `email_by_human_allowed`, - `jabber`, - `Size`, - `Passwort`, - `kommentar`, - `Hometown`, - `CreateDate`, - `Sprache`, - `arrival_date`, - `planned_arrival_date`, - `force_active`, - `lastLogIn`, - `api_key`, - `got_voucher` - ) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, NULL, ?, FALSE, 0, "", 0) - ', - [ - config('theme'), - $nick, - $preName, - $lastName, - $age, - $tel, - $dect, - $mobile, - $mail, - (int)$email_shiftinfo, - (int)$email_by_human_allowed, - $jabber, - $tshirt_size, - $password_hash, - $comment, - $hometown, - $session->get('locale'), - $planned_arrival_date, - ] - ); + $user = new User([ + 'name' => $nick, + 'password' => $password_hash, + 'email' => $mail, + 'api_key' => '', + 'last_login_at' => null, + ]); + $user->save(); + + $contact = new Contact([ + 'dect' => $dect, + 'mobile' => $mobile, + ]); + $contact->user() + ->associate($user) + ->save(); + + $personalData = new PersonalData([ + 'first_name' => $preName, + 'last_name' => $lastName, + 'shirt_size' => $tshirt_size, + 'planned_arrival_date' => Carbon::createFromTimestamp($planned_arrival_date), + ]); + $personalData->user() + ->associate($user) + ->save(); + + $settings = new Settings([ + 'language' => $session->get('locale'), + 'theme' => config('theme'), + 'email_human' => $email_by_human_allowed, + 'email_shiftinfo' => $email_shiftinfo, + ]); + $settings->user() + ->associate($user) + ->save(); + + (new State())->user() + ->associate($user) + ->save(); // Assign user-group and set password - $user_id = DB::getPdo()->lastInsertId(); - DB::insert('INSERT INTO `UserGroups` (`uid`, `group_id`) VALUES (?, -20)', [$user_id]); - set_password($user_id, $request->postData('password')); + DB::insert('INSERT INTO `UserGroups` (`uid`, `group_id`) VALUES (?, -20)', [$user->id]); + set_password($user->id, $request->postData('password')); // Assign angel-types $user_angel_types_info = []; foreach ($selected_angel_types as $selected_angel_type_id) { DB::insert( 'INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`, `supporter`) VALUES (?, ?, FALSE)', - [$user_id, $selected_angel_type_id] + [$user->id, $selected_angel_type_id] ); $user_angel_types_info[] = $angel_types[$selected_angel_type_id]; } engelsystem_log( - 'User ' . User_Nick_render(User::find($user_id)) + 'User ' . User_Nick_render($user) . ' signed up as: ' . join(', ', $user_angel_types_info) ); success(__('Angel registration successful!')); // User is already logged in - that means a supporter has registered an angel. Return to register page. - if ($user) { + if ($authUser) { redirect(page_link_to('register')); } @@ -369,11 +337,7 @@ function guest_register() div('col-sm-4', [ form_text('mobile', __('Mobile'), $mobile) ]), - div('col-sm-4', [ - form_text('tel', __('Phone'), $tel) - ]) ]), - form_text('jabber', __('Jabber'), $jabber), div('row', [ div('col-sm-6', [ form_text('prename', __('First name'), $preName) @@ -382,14 +346,6 @@ function guest_register() form_text('lastname', __('Last name'), $lastName) ]) ]), - div('row', [ - div('col-sm-3', [ - form_text('age', __('Age'), $age) - ]), - div('col-sm-9', [ - form_text('hometown', __('Hometown'), $hometown) - ]) - ]), form_info(entry_required() . ' = ' . __('Entry required!')) ]) ]), diff --git a/includes/pages/guest_stats.php b/includes/pages/guest_stats.php index bb07f4dc..d9012748 100644 --- a/includes/pages/guest_stats.php +++ b/includes/pages/guest_stats.php @@ -1,6 +1,8 @@ input('api_key') == $apiKey) { $stats = []; - list($user_count) = DB::select('SELECT count(*) AS `user_count` FROM `User`'); - $stats['user_count'] = $user_count['user_count']; - - list($arrived_user_count) = DB::select(' - SELECT count(*) AS `user_count` - FROM `User` - WHERE `Gekommen`=1 - '); - $stats['arrived_user_count'] = $arrived_user_count['user_count']; + $stats['user_count'] = User::all()->count(); + $stats['arrived_user_count'] = State::whereArrived(true)->count(); $done_shifts_seconds = DB::selectOne(' SELECT SUM(`Shifts`.`end` - `Shifts`.`start`) diff --git a/includes/pages/user_myshifts.php b/includes/pages/user_myshifts.php index 7f20139c..7fa33518 100644 --- a/includes/pages/user_myshifts.php +++ b/includes/pages/user_myshifts.php @@ -26,7 +26,7 @@ function user_myshifts() $request->has('id') && in_array('user_shifts_admin', $privileges) && preg_match('/^\d{1,}$/', $request->input('id')) - && count(DB::select('SELECT `UID` FROM `User` WHERE `UID`=?', [$request->input('id')])) > 0 + && User::find($request->input('id')) ) { $shift_entry_id = $request->input('id'); } else { diff --git a/includes/view/User_view.php b/includes/view/User_view.php index cfafaeb1..c055169e 100644 --- a/includes/view/User_view.php +++ b/includes/view/User_view.php @@ -212,7 +212,6 @@ function Users_view( $tshirts_count, $voucher_count ) { - $usersList = []; foreach ($users as $user) { $u = []; -- cgit v1.2.3-54-g00ecf