Revize dabc8081

Deletion of enums from the database

server/init.sql

@@ -1 @@
--- -----------------------------------------------------
--- Table role
--- -----------------------------------------------------
-DROP TABLE IF EXISTS role;
-CREATE TABLE role (
-  id TINYINT NOT NULL AUTO_INCREMENT,
-  name VARCHAR(8) NOT NULL,
-  PRIMARY KEY (id)
-);
-
--- -----------------------------------------------------
--- Table approval_status
--- -----------------------------------------------------
-DROP TABLE IF EXISTS approval_status;
-CREATE TABLE approval_status (
-  id TINYINT NOT NULL AUTO_INCREMENT,
-  name VARCHAR(8) NOT NULL,
-  PRIMARY KEY (id)
-);
-
--- -----------------------------------------------------
--- Table vacation_type
--- -----------------------------------------------------
-DROP TABLE IF EXISTS vacation_type;
-CREATE TABLE vacation_type (
-  id TINYINT NOT NULL AUTO_INCREMENT,
-  name VARCHAR(8) NOT NULL,
-  PRIMARY KEY (id)
-);
-
 -- -----------------------------------------------------
 -- Table end_user
 -- -----------------------------------------------------
@@ -44 +14 @@
   email VARCHAR(100) NOT NULL,
   photo TEXT,
   creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  role_id TINYINT NOT NULL,
-  status_id TINYINT NOT NULL,
+  user_role VARCHAR(8) NOT NULL,
+  status VARCHAR(8) NOT NULL,
   PRIMARY KEY (id),
-  INDEX fk_end_user_role (role_id ASC),
-  INDEX fk_end_user_approval_status (status_id ASC),
-  CONSTRAINT fk_end_user_role FOREIGN KEY (role_id)
-    REFERENCES role (id)
-    ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT fk_end_user_approval_status FOREIGN KEY (status_id)
-    REFERENCES approval_status (id)
-    ON DELETE NO ACTION ON UPDATE CASCADE,
   CONSTRAINT check_taken_sick_days CHECK (taken_sick_days >= 0)
 );
 
@@ -68 +30 @@
   time_from TIME,
   time_to TIME,
   creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  status VARCHAR(8) NOT NULL,
+  vacation_type VARCHAR(8) NOT NULL,
   user_id BIGINT NOT NULL,
-  status_id TINYINT NOT NULL,
-  type_id TINYINT NOT NULL,
   PRIMARY KEY (id),
-  INDEX fk_vacation_day_approval_status (status_id ASC),
   INDEX fk_vacation_day_end_user (user_id ASC),
-  INDEX fk_vacation_day_vacation_type (type_id ASC),
-  CONSTRAINT fk_vacation_day_approval_status FOREIGN KEY (status_id)
-    REFERENCES approval_status (id)
-    ON DELETE NO ACTION ON UPDATE CASCADE,
   CONSTRAINT fk_vacation_day_end_user FOREIGN KEY (user_id)
     REFERENCES end_user (id)
     ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT fk_vacation_day_vacation_type FOREIGN KEY (type_id)
-    REFERENCES vacation_type (id)
-    ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT check_time CHECK (time_from < time_to)
 );
 
@@ -121 +75 @@
   DECLARE notification DATETIME;
 
   SELECT no_sick_days, alert INTO sickDaysCount, notification FROM default_settings ORDER BY id DESC LIMIT 1;
-  SELECT u.id, u.first_name, u.last_name, u.no_vacations, IFNULL(u.no_sick_days, sickDaysCount), u.taken_sick_days, IFNULL(u.alert, notification), u.email, u.photo, u.creation_date, r.name, s.name
+  SELECT id, first_name, last_name, no_vacations, IFNULL(no_sick_days, sickDaysCount), taken_sick_days, IFNULL(alert, notification), email, photo, creation_date, user_role, status
      INTO out_id, out_first_name, out_last_name, out_no_vacations, out_no_sick_days, out_taken_sick_days, out_alert, out_email, out_photo, out_creation_date, out_role, out_status
-     FROM end_user u
-     INNER JOIN role r ON u.role_id=r.id
-     INNER JOIN approval_status s ON u.status_id=s.id
-     WHERE u.id=in_id;
+     FROM end_user
+     WHERE id=in_id;
 END $$
 
 DROP PROCEDURE IF EXISTS GetUserToken $$
 CREATE PROCEDURE GetUserToken(
-  IN p_token TEXT,
+  IN in_token TEXT,
   OUT out_id BIGINT,
   OUT out_first_name VARCHAR(45),
   OUT out_last_name VARCHAR(45),
@@ -149 +101 @@
   DECLARE notification DATETIME;
 
   SELECT no_sick_days, alert INTO sickDaysCount, notification FROM default_settings ORDER BY id DESC LIMIT 1;
-  SELECT u.id, u.first_name, u.last_name, u.no_vacations, IFNULL(u.no_sick_days, sickDaysCount), u.taken_sick_days, IFNULL(u.alert, notification), u.email, u.photo, u.creation_date, r.name, s.name
+  SELECT id, first_name, last_name, no_vacations, IFNULL(no_sick_days, sickDaysCount), taken_sick_days, IFNULL(alert, notification), email, photo, creation_date, user_role, status
      INTO out_id, out_first_name, out_last_name, out_no_vacations, out_no_sick_days, out_taken_sick_days, out_alert, out_email, out_photo, out_creation_date, out_role, out_status
-     FROM end_user u
-     INNER JOIN role r ON u.role_id=r.id
-     INNER JOIN approval_status s ON u.status_id=s.id
-     WHERE u.token=p_token;
+     FROM end_user
+     WHERE token=in_token;
 END $$
 
 DELIMITER ;
 
--- -----------------------------------------------------
--- Insert table role
--- -----------------------------------------------------
-INSERT INTO role (name) VALUES ('employee');
-INSERT INTO role (name) VALUES ('employer');
-
--- -----------------------------------------------------
--- Insert table approval_status
--- -----------------------------------------------------
-INSERT INTO approval_status (name) VALUES ('accepted');
-INSERT INTO approval_status (name) VALUES ('pending');
-INSERT INTO approval_status (name) VALUES ('rejected');
-
--- -----------------------------------------------------
--- Insert table vacation_type
--- -----------------------------------------------------
-INSERT INTO vacation_type (name) VALUES ('sickday');
-INSERT INTO vacation_type (name) VALUES ('vacation');
-
 -- -----------------------------------------------------
 -- Insert table end_user
 -- -----------------------------------------------------
-INSERT INTO end_user (first_name, last_name, no_vacations, no_sick_days, taken_sick_days, alert, token, email, photo, role_id, status_id) VALUES ('admin', 'admin', 0, NULL, 0, NULL, '', '', '', 2, 1);
-INSERT INTO end_user (first_name, last_name, no_vacations, no_sick_days, taken_sick_days, alert, token, email, photo, role_id, status_id) VALUES ('test', 'test', 0, NULL, 0, NULL, '', '', '', 1, 2);
+INSERT INTO end_user (first_name, last_name, no_vacations, no_sick_days, taken_sick_days, alert, token, email, photo, user_role, status) VALUES ('admin', 'admin', 0, NULL, 0, NULL, '', '', '', 'employer', 'accepted');
+INSERT INTO end_user (first_name, last_name, no_vacations, no_sick_days, taken_sick_days, alert, token, email, photo, user_role, status) VALUES ('Jan', 'Novák', 0, 10, 0, '2019-12-6 16:30:00.000', '', '', '', 'employee', 'pending');
+INSERT INTO end_user (first_name, last_name, no_vacations, no_sick_days, taken_sick_days, alert, token, email, photo, user_role, status) VALUES ('Josef', 'Svoboda', 2, NULL, 3, NULL, '', '', '', 'employee', 'rejected');
 
 -- -----------------------------------------------------
 -- Insert table vacation_day

server/src/main/java/cz/zcu/yamanager/business/ApiManager.java

@@ -89 +89 @@
         FullUserProfile userProfile = this.userRepository.getFullUser(userId);
         System.out.println("Notification: " + userProfile.getNotification());
         System.out.println("Approval: " + userProfile.getStatus());
-        return userProfile == null ? new FullUserProfile() : userProfile;
+        return userProfile;
     }
 
     @Override

server/src/main/java/cz/zcu/yamanager/repository/RequestRepository.java

@@ -29 +29 @@
 
     /**
      * Creates a new instance of the class RequestRepository which selects and updates requests in a database.
+     *
      * @param jdbc A connection to the database.
      */
     @Autowired
-    public RequestRepository(JdbcTemplate jdbc) {
-        log.trace("Creating a new instance of the class RequestRepository");
+    public RequestRepository(final JdbcTemplate jdbc) {
+        RequestRepository.log.trace("Creating a new instance of the class RequestRepository");
         this.jdbc = jdbc;
     }
 
     /**
      * Gets all authorization request from a database.
+     *
      * @return A list of all authorization requests.
      */
     public List<AuthorizationRequest> getAllAuthorizations() {
-        log.trace("Selecting all authorization requests from a database.");
+        RequestRepository.log.trace("Selecting all authorization requests from a database.");
 
-        return jdbc.query("SELECT u.id, u.first_name, u.last_name, u.creation_date, s.name " +
-                "FROM end_user u " +
-                "INNER JOIN approval_status s ON u.status_id = s.id",
+        return this.jdbc.query("SELECT id, first_name, last_name, creation_date, status FROM end_user",
                 (ResultSet rs, int rowNum) -> {
-                    AuthorizationRequest request = new AuthorizationRequest();
-                    request.setId(rs.getLong("u.id"));
-                    request.setFirstName(rs.getString("u.first_name"));
-                    request.setLastName(rs.getString("u.last_name"));
-                    request.setStatus(Status.getStatus(rs.getString("s.name")));
-                    request.setTimestamp(rs.getTimestamp("u.creation_date").toLocalDateTime());
+                    final AuthorizationRequest request = new AuthorizationRequest();
+                    request.setId(rs.getLong("id"));
+                    request.setFirstName(rs.getString("first_name"));
+                    request.setLastName(rs.getString("last_name"));
+                    request.setTimestamp(rs.getTimestamp("creation_date").toLocalDateTime());
+                    request.setStatus(Status.getStatus(rs.getString("status")));
                     return request;
                 });
     }
 
     /**
      * Gets all authorization request with the given status from a database.
+     *
      * @param status The approval status of the requests.
      * @return A list of all authorization requests.
      */
-    public List<AuthorizationRequest> getAllAuthorizations(Status status) {
-        log.debug("Selecting all authorization requests from a database with requested status: {}", status);
-
-        if(status == null) throw new InvalidParameterException();
-        return jdbc.query("SELECT u.id, u.first_name, u.last_name, u.creation_date, s.name " +
-                "FROM end_user u " +
-                "INNER JOIN approval_status s ON u.status_id = s.id " +
-                "WHERE s.name = ?",
+    public List<AuthorizationRequest> getAllAuthorizations(final Status status) {
+        RequestRepository.log.debug("Selecting all authorization requests from a database with requested status: {}", status);
+
+        if (status == null) throw new InvalidParameterException();
+        return this.jdbc.query("SELECT id, first_name, last_name, creation_date FROM end_user WHERE status = ?",
                 new Object[]{status.name()},
                 (ResultSet rs, int rowNum) -> {
-                    AuthorizationRequest request = new AuthorizationRequest();
-                    request.setId(rs.getLong("u.id"));
-                    request.setFirstName(rs.getString("u.first_name"));
-                    request.setLastName(rs.getString("u.last_name"));
-                    request.setStatus(Status.getStatus(rs.getString("s.name")));
-                    request.setTimestamp(rs.getTimestamp("u.creation_date").toLocalDateTime());
+                    final AuthorizationRequest request = new AuthorizationRequest();
+                    request.setId(rs.getLong("id"));
+                    request.setFirstName(rs.getString("first_name"));
+                    request.setLastName(rs.getString("last_name"));
+                    request.setTimestamp(rs.getTimestamp("creation_date").toLocalDateTime());
+                    request.setStatus(status);
                     return request;
                 });
     }
 
     /**
      * Updates a status of an authorization request with the given id.
-     * @param id The id of the request.
+     *
+     * @param id     The id of the request.
      * @param status The new status of the request.
      */
-    public void updateAuthorization(long id, Status status) {
-        log.debug("Updating authorization request with id {} in a database to {}.", id, status);
+    public void updateAuthorization(final long id, final Status status) {
+        RequestRepository.log.debug("Updating authorization request with id {} in a database to {}.", id, status);
 
-        jdbc.update("UPDATE end_user EU, approval_status APS SET EU.status_id=APS.id WHERE EU.id=? AND APS.name=?", id, status.name());
+        this.jdbc.update("UPDATE end_user SET status=? WHERE id=?", status.name(), id);
     }
 
     /**
      * Updates a status of an authorization request from a BasicRequest object.
+     *
      * @param request The BasicRequest object with new values of the vacation request.
      */
-    public void updateAuthorization(BasicRequest request) {
-        this.updateAuthorization(request.getId(), request.getStatus());
+    public void updateAuthorization(final BasicRequest request) {
+        updateAuthorization(request.getId(), request.getStatus());
     }
 
     /**
      * Updates a status of an authorization request from an AuthorizationRequest object.
+     *
      * @param request The AuthorizationRequest object with new values of the authorization request.
      */
-    public void updateAuthorization(AuthorizationRequest request) {
-        this.updateAuthorization(request.getId(), request.getStatus());
+    public void updateAuthorization(final AuthorizationRequest request) {
+        updateAuthorization(request.getId(), request.getStatus());
     }
 
     /**
      * Gets all vacation request from a database.
+     *
      * @return A list of all vacation requests.
      */
     public List<VacationRequest> getAllVacationRequests() {
-        log.trace("Selecting all vacation requests from a database.");
+        RequestRepository.log.trace("Selecting all vacation requests from a database.");
 
-        return jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.creation_date, t.name, s.name, u.first_name, u.last_name " +
-                "FROM vacation_day v " +
-                "INNER JOIN vacation_type t on v.type_id = t.id " +
-                "INNER JOIN approval_status s ON v.status_id = s.id " +
-                "INNER JOIN end_user u ON v.user_id = u.id",
+        return this.jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.creation_date, v.vacation_type, v.status, u.first_name, u.last_name " +
+                        "FROM vacation_day v " +
+                        "INNER JOIN end_user u ON v.user_id = u.id",
                 (ResultSet rs, int rowNum) -> {
-                    VacationRequest request = new VacationRequest();
+                    final VacationRequest request = new VacationRequest();
                     request.setId(rs.getLong("v.id"));
                     request.setFirstName(rs.getString("u.first_name"));
                     request.setLastName(rs.getString("u.last_name"));
                     request.setDate(rs.getDate("v.vacation_date").toLocalDate());
-                    Time timeFrom = rs.getTime("v.time_from");
-                    if(timeFrom != null) {
+                    final Time timeFrom = rs.getTime("v.time_from");
+                    if (timeFrom != null) {
                         request.setFrom(timeFrom.toLocalTime());
                     }
 
-                    Time timeTo = rs.getTime("v.time_to");
-                    if(timeTo != null) {
+                    final Time timeTo = rs.getTime("v.time_to");
+                    if (timeTo != null) {
                         request.setTo(timeTo.toLocalTime());
                     }
+
                     request.setTimestamp(rs.getTimestamp("v.creation_date").toLocalDateTime());
-                    request.setType(VacationType.getVacationType(rs.getString("t.name")));
-                    request.setStatus(Status.getStatus(rs.getString("s.name")));
+                    request.setType(VacationType.getVacationType(rs.getString("v.vacation_type")));
+                    request.setStatus(Status.getStatus(rs.getString("v.status")));
                     return request;
                 });
     }
 
     /**
      * Gets all vacation request with the given status from a database.
+     *
      * @param status The approval status of the requests.
      * @return A list of all vacation requests.
      */
-    public List<VacationRequest> getAllVacationRequests(Status status) {
-        log.debug("Selecting all vacation requests from a database with requested status: {}", status);
-
-        if(status == null) throw new InvalidParameterException();
-        return jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.creation_date, t.name, s.name, u.first_name, u.last_name " +
-                "FROM vacation_day v " +
-                "INNER JOIN vacation_type t on v.type_id = t.id " +
-                "INNER JOIN approval_status s ON v.status_id = s.id " +
-                "INNER JOIN end_user u ON v.user_id = u.id " +
-                "WHERE s.name=?",
+    public List<VacationRequest> getAllVacationRequests(final Status status) {
+        RequestRepository.log.debug("Selecting all vacation requests from a database with requested status: {}", status);
+
+        if (status == null) throw new InvalidParameterException();
+        return this.jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.creation_date, v.vacation_type, v.status, u.first_name, u.last_name " +
+                        "FROM vacation_day v " +
+                        "INNER JOIN end_user u ON v.user_id = u.id " +
+                        "WHERE v.status=?",
                 new Object[]{status.name()},
                 (ResultSet rs, int rowNum) -> {
-                    VacationRequest request = new VacationRequest();
+                    final VacationRequest request = new VacationRequest();
                     request.setId(rs.getLong("v.id"));
                     request.setFirstName(rs.getString("u.first_name"));
                     request.setLastName(rs.getString("u.last_name"));
                     request.setDate(rs.getDate("v.vacation_date").toLocalDate());
-                    Time timeFrom = rs.getTime("v.time_from");
-                    if(timeFrom != null) {
+                    final Time timeFrom = rs.getTime("v.time_from");
+                    if (timeFrom != null) {
                         request.setFrom(timeFrom.toLocalTime());
                     }
 
-                    Time timeTo = rs.getTime("v.time_to");
-                    if(timeTo != null) {
+                    final Time timeTo = rs.getTime("v.time_to");
+                    if (timeTo != null) {
                         request.setTo(timeTo.toLocalTime());
                     }
 
                     request.setTimestamp(rs.getTimestamp("v.creation_date").toLocalDateTime());
-                    request.setType(VacationType.getVacationType(rs.getString("t.name")));
-                    request.setStatus(Status.getStatus(rs.getString("s.name")));
+                    request.setType(VacationType.getVacationType(rs.getString("v.vacation_type")));
+                    request.setStatus(status);
                     return request;
                 });
     }
 
     /**
      * Updates a status of a vacation request with the given id.
-     * @param id The id of the request.
+     *
+     * @param id     The id of the request.
      * @param status The new status of the request.
      */
-    public void updateVacationRequest(long id, Status status) {
-        log.debug("Updating vacation request with id {} in a database to {}.", id, status);
+    public void updateVacationRequest(final long id, final Status status) {
+        RequestRepository.log.debug("Updating vacation request with id {} in a database to {}.", id, status);
 
-        jdbc.update("UPDATE vacation_day VD, approval_status APS SET VD.status_id=APS.id WHERE VD.id=? AND APS.name=?", id, status.name());
+        this.jdbc.update("UPDATE vacation_day SET status=? WHERE id=?", status.name(), id);
     }
 
     /**
      * Updates a status of a vacation request from a BasicRequest object.
+     *
      * @param request The BasicRequest object with new values of the vacation request.
      */
-    public void updateVacationRequest(BasicRequest request) {
-        this.updateVacationRequest(request.getId(), request.getStatus());
+    public void updateVacationRequest(final BasicRequest request) {
+        updateVacationRequest(request.getId(), request.getStatus());
     }
 
     /**
      * Updates a status of a vacation request from a VacationRequest object.
+     *
      * @param request The VacationRequest object with new values of the vacation request.
      */
-    public void updateVacationRequest(VacationRequest request) {
-        this.updateAuthorization(request.getId(), request.getStatus());
+    public void updateVacationRequest(final VacationRequest request) {
+        updateAuthorization(request.getId(), request.getStatus());
     }
 }

server/src/main/java/cz/zcu/yamanager/repository/UserRepository.java

@@ -7 +7 @@
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.SqlOutParameter;
 import org.springframework.jdbc.core.SqlParameter;
-import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
-import org.springframework.jdbc.core.simple.SimpleJdbcCall;
 import org.springframework.stereotype.Repository;
 
 import java.sql.*;
@@ -30 +28 @@
 
     /**
      * Creates a new instance of the class UserRepository which selects and updates users in a database.
+     *
      * @param jdbc A connection to the database.
      */
     @Autowired
@@ -50 +49 @@
     }
 
     public List<BasicProfileUser> getAllBasicUsers(Status status) {
-        if(status == null) throw new IllegalArgumentException();
-
-        return jdbc.query("SELECT u.id, u.first_name, u.last_name, u.photo" +
-                "FROM end_user u " +
-                "INNER JOIN approval_status s ON u.status_id = s.id " +
-                "WHERE s.name = ?",
+        return jdbc.query("SELECT id, first_name, last_name, photo FROM end_user WHERE status = ?",
                 new Object[]{status.name()}, (ResultSet rs, int rowNum) -> {
-            BasicProfileUser user = new BasicProfileUser();
-            user.setId(rs.getLong("u.id"));
-            user.setFirstName(rs.getString("u.first_name"));
-            user.setLastName(rs.getString("u.last_name"));
-            user.setPhoto(rs.getString("u.photo"));
-            return user;
-        });
+                    BasicProfileUser user = new BasicProfileUser();
+                    user.setId(rs.getLong("id"));
+                    user.setFirstName(rs.getString("first_name"));
+                    user.setLastName(rs.getString("last_name"));
+                    user.setPhoto(rs.getString("photo"));
+                    return user;
+                });
     }
 
     public FullUserProfile getFullUser(long id) {
@@ -101 +95 @@
         }, paramList);
 
         FullUserProfile user = new FullUserProfile();
-        user.setId((Long)resultMap.get("out_id"));
-        user.setFirstName((String)resultMap.get("out_first_name"));
-        user.setLastName((String)resultMap.get("out_last_name"));
-        user.setVacationCount(((Double)resultMap.get("out_no_vacations")).floatValue());
-        user.setSickdayCount((Integer)resultMap.get("out_no_sick_days"));
-        user.setTakenSickdayCount((Integer)resultMap.get("out_taken_sick_days"));
-        user.setNotification(((Timestamp)resultMap.get("out_alert")).toLocalDateTime());
-        user.setEmail((String)resultMap.get(("out_email")));
-        user.setPhoto((String)resultMap.get("out_photo"));
-        user.setRole(UserRole.getUserRole((String)resultMap.get("out_role")));
-        user.setStatus(Status.getStatus((String)resultMap.get("out_status")));
+        user.setId(id);
+        user.setFirstName((String) resultMap.get("out_first_name"));
+        user.setLastName((String) resultMap.get("out_last_name"));
+        user.setVacationCount(((Double) resultMap.get("out_no_vacations")).floatValue());
+        user.setSickdayCount((Integer) resultMap.get("out_no_sick_days"));
+        user.setTakenSickdayCount((Integer) resultMap.get("out_taken_sick_days"));
+
+        final Timestamp alertTimestamp = (Timestamp) resultMap.get(("out_alert"));
+        if (alertTimestamp != null) {
+            user.setNotification(alertTimestamp.toLocalDateTime());
+        }
+
+        user.setEmail((String) resultMap.get(("out_email")));
+        user.setPhoto((String) resultMap.get("out_photo"));
+        user.setRole(UserRole.getUserRole((String) resultMap.get("out_role")));
+        user.setStatus(Status.getStatus((String) resultMap.get("out_status")));
         return user;
 
     }
@@ -151 +150 @@
         }, paramList);
 
         FullUserProfile user = new FullUserProfile();
-        user.setId((Long)resultMap.get("out_id"));
-        user.setFirstName((String)resultMap.get("out_first_name"));
-        user.setLastName((String)resultMap.get("out_last_name"));
-        user.setVacationCount(((Double)resultMap.get("out_no_vacations")).floatValue());
-        user.setSickdayCount((Integer)resultMap.get("out_no_sick_days"));
-        user.setTakenSickdayCount((Integer)resultMap.get("out_taken_sick_days"));
-        user.setNotification(((Timestamp)resultMap.get("out_alert")).toLocalDateTime());
-        user.setEmail((String)resultMap.get(("out_email")));
-        user.setPhoto((String)resultMap.get("out_photo"));
-        user.setRole(UserRole.getUserRole((String)resultMap.get("out_role")));
-        user.setStatus(Status.getStatus((String)resultMap.get("out_status")));
+        user.setId((Long) resultMap.get("out_id"));
+        user.setFirstName((String) resultMap.get("out_first_name"));
+        user.setLastName((String) resultMap.get("out_last_name"));
+        user.setVacationCount(((Double) resultMap.get("out_no_vacations")).floatValue());
+        user.setSickdayCount((Integer) resultMap.get("out_no_sick_days"));
+        user.setTakenSickdayCount((Integer) resultMap.get("out_taken_sick_days"));
+
+        final Timestamp alertTimestamp = (Timestamp) resultMap.get(("out_alert"));
+        if (alertTimestamp != null) {
+            user.setNotification(alertTimestamp.toLocalDateTime());
+        }
+
+        user.setEmail((String) resultMap.get(("out_email")));
+        user.setPhoto((String) resultMap.get("out_photo"));
+        user.setRole(UserRole.getUserRole((String) resultMap.get("out_role")));
+        user.setStatus(Status.getStatus((String) resultMap.get("out_status")));
         return user;
     }
 
     public UserSettings getUserSettings(long id) {
-        return jdbc.queryForObject("SELECT no_vacations, no_sick_days, role_id FROM end_user WHERE = id=?",
-                new Object[]{id}, (ResultSet rs, int rowNum)->{
-            UserSettings settings = new UserSettings();
-            settings.setId(id);
-            settings.setSickdayCount(rs.getInt("no_sick_day"));
-            settings.setVacationCount(rs.getFloat("no_vacations"));
-            settings.setRole(UserRole.values()[rs.getByte("role_id")]);
-            return settings;
-        });
+        return jdbc.queryForObject("SELECT no_vacations, no_sick_days, user_role FROM end_user WHERE = id=?",
+                new Object[]{id}, (ResultSet rs, int rowNum) -> {
+                    UserSettings settings = new UserSettings();
+                    settings.setId(id);
+                    settings.setSickdayCount(rs.getInt("no_sick_day"));
+                    settings.setVacationCount(rs.getFloat("no_vacations"));
+                    settings.setRole(UserRole.getUserRole(rs.getString("user_role")));
+                    return settings;
+                });
     }
 
     public void updateNotification(UserSettings settings) {
@@ -182 +186 @@
     }
 
     public void updateUserSettings(UserSettings settings) {
-        jdbc.update("UPDATE end_user u, role r SET u.no_vacations=?, u.no_sick_days=?, u.role_id=r.id WHERE u.id = ? AND r.name=?",
-                settings.getVacationCount(), settings.getSickdayCount(), settings.getId(), settings.getRole().name());
+        jdbc.update("UPDATE end_user SET no_vacations=?, no_sick_days=?, user_role=? WHERE id = ?",
+                settings.getVacationCount(), settings.getSickdayCount(), settings.getRole().name(), settings.getId());
     }
 
     public DefaultSettings getLastDefaultSettings() {

server/src/main/java/cz/zcu/yamanager/repository/VacationRepository.java

@@ -29 +29 @@
 
     /**
      * Creates a new instance of the class VacationRepository which selects and updates users in a database.
+     *
      * @param jdbc A connection to the database.
      */
     @Autowired
-    public VacationRepository(JdbcTemplate jdbc) {
-        log.trace("Creating a new instance of the class VacationRepository");
+    public VacationRepository(final JdbcTemplate jdbc) {
+        VacationRepository.log.trace("Creating a new instance of the class VacationRepository");
         this.jdbc = jdbc;
     }
 
-    public List<VacationDay> getVacationDays(String token, LocalDate from) {
-        return jdbc.query("SELECT VD.vacation_date, VD.time_from, VD.time_to, VT.name, APS.name " +
-                        "FROM vacation_day VD " +
-                        "INNER JOIN end_user EU ON VD.user_id = EU.id " +
-                        "INNER JOIN vacation_type VT ON VD.type_id = VT.id " +
-                        "INNER JOIN approval_status APS ON VD.status_id = APS.id " +
-                        "WHERE EU.token = ? AND VD.vacation_date >= ?",
-                new Object[]{token, from}, (ResultSet rs, int rowNum) -> {
-                    VacationDay item = new VacationDay();
-                    item.setDate(rs.getDate("VD.vacation_date").toLocalDate());
-                    item.setFrom(rs.getTime("VD.time_from").toLocalTime());
-                    item.setTo(rs.getTime("VD.time_to").toLocalTime());
-                    item.setType(VacationType.getVacationType(rs.getString("VT.name")));
-                    item.setStatus(Status.getStatus(rs.getString("APS.name")));
-                    return item;
-                });
-    }
-
-    public List<VacationDay> getVacationDays(String token, LocalDate from, LocalDate to) {
-        return jdbc.query("SELECT VD.vacation_date, VD.time_from, VD.time_to, VT.name, APS.name " +
-                        "FROM vacation_day VD " +
-                        "INNER JOIN end_user EU ON VD.user_id = EU.id " +
-                        "INNER JOIN vacation_type VT ON VD.type_id = VT.id " +
-                        "INNER JOIN approval_status APS ON VD.status_id = APS.id " +
-                        "WHERE EU.token = ? AND VD.vacation_date >= ? AND VD.vacation_date <= ?",
-                new Object[]{token, from, to}, (ResultSet rs, int rowNum) -> {
-                    VacationDay item = new VacationDay();
-                    item.setDate(rs.getDate("VD.vacation_date").toLocalDate());
-                    item.setFrom(rs.getTime("VD.time_from").toLocalTime());
-                    item.setTo(rs.getTime("VD.time_to").toLocalTime());
-                    item.setType(VacationType.getVacationType(rs.getString("VT.name")));
-                    item.setStatus(Status.getStatus(rs.getString("APS.name")));
-                    return item;
-                });
-    }
-
-    public List<VacationDay> getVacationDays(long userId, LocalDate from) {
-        return jdbc.query("SELECT v.vacation_date, v.time_from, v.time_to, t.name, s.name " +
+    public List<VacationDay> getVacationDays(final long userId, final LocalDate from) {
+        return this.jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.status, v.vacation_type " +
                         "FROM vacation_day v " +
                         "INNER JOIN end_user u ON v.user_id = u.id " +
-                        "INNER JOIN vacation_type t ON v.type_id = t.id " +
-                        "INNER JOIN approval_status s ON v.status_id = s.id " +
                         "WHERE v.user_id = ? AND v.vacation_date >= ?",
                 new Object[]{userId, from}, (ResultSet rs, int rowNum) -> {
-                    VacationDay item = new VacationDay();
+                    final VacationDay item = new VacationDay();
+                    item.setId(rs.getLong("v.id"));
                     item.setDate(rs.getDate("v.vacation_date").toLocalDate());
-                    Time timeFrom = rs.getTime("v.time_from");
-                    if(timeFrom != null) {
+                    final Time timeFrom = rs.getTime("v.time_from");
+                    if (timeFrom != null) {
                         item.setFrom(timeFrom.toLocalTime());
                     }
 
-                    Time timeTo = rs.getTime("v.time_to");
-                    if(timeTo != null) {
+                    final Time timeTo = rs.getTime("v.time_to");
+                    if (timeTo != null) {
                         item.setTo(timeTo.toLocalTime());
                     }
-                    item.setType(VacationType.getVacationType(rs.getString("t.name")));
-                    item.setStatus(Status.getStatus(rs.getString("s.name")));
+
+                    item.setStatus(Status.getStatus(rs.getString("v.status")));
+                    item.setType(VacationType.getVacationType(rs.getString("v.vacation_type")));
                     return item;
                 });
     }
 
-    public List<VacationDay> getVacationDays(long userId, LocalDate from, Status status) {
-        if(status == null) throw new IllegalArgumentException();
-
-        return jdbc.query("SELECT v.vacation_date, v.time_from, v.time_to, t.name, s.name " +
+    public List<VacationDay> getVacationDays(final long userId, final LocalDate from, final Status status) {
+        return this.jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.status, v.vacation_type " +
                         "FROM vacation_day v " +
                         "INNER JOIN end_user u ON v.user_id = u.id " +
-                        "INNER JOIN vacation_type t ON v.type_id = t.id " +
-                        "INNER JOIN approval_status s ON v.status_id = s.id " +
-                        "WHERE v.user_id = ? AND v.vacation_date >= ? AND s.name = ?",
+                        "WHERE v.user_id = ? AND v.vacation_date >= ? AND v.status = ?",
                 new Object[]{userId, from, status.name()}, (ResultSet rs, int rowNum) -> {
-                    VacationDay item = new VacationDay();
+                    final VacationDay item = new VacationDay();
+                    item.setId(rs.getLong("v.id"));
                     item.setDate(rs.getDate("v.vacation_date").toLocalDate());
-                    Time timeFrom = rs.getTime("v.time_from");
-                    if(timeFrom != null) {
+                    final Time timeFrom = rs.getTime("v.time_from");
+                    if (timeFrom != null) {
                         item.setFrom(timeFrom.toLocalTime());
                     }
 
-                    Time timeTo = rs.getTime("v.time_to");
-                    if(timeTo != null) {
+                    final Time timeTo = rs.getTime("v.time_to");
+                    if (timeTo != null) {
                         item.setTo(timeTo.toLocalTime());
                     }
-                    item.setType(VacationType.getVacationType(rs.getString("t.name")));
-                    item.setStatus(Status.getStatus(rs.getString("s.name")));
+
+                    item.setStatus(Status.getStatus(rs.getString("v.status")));
+                    item.setType(VacationType.getVacationType(rs.getString("v.vacation_type")));
                     return item;
                 });
     }
 
-    public List<VacationDay> getVacationDays(long userId, LocalDate from, LocalDate to) {
-        return jdbc.query("SELECT v.vacation_date, v.time_from, v.time_to, t.name, s.name " +
+    public List<VacationDay> getVacationDays(final long userId, final LocalDate from, final LocalDate to) {
+        return this.jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.status, v.vacation_type " +
                         "FROM vacation_day v " +
                         "INNER JOIN end_user u ON v.user_id = u.id " +
-                        "INNER JOIN vacation_type t ON v.type_id = t.id " +
-                        "INNER JOIN approval_status s ON v.status_id = s.id " +
                         "WHERE v.user_id=? AND v.vacation_date >= ? AND v.vacation_date <= ?",
                 new Object[]{userId, from, to}, (ResultSet rs, int rowNum) -> {
-                    VacationDay item = new VacationDay();
+                    final VacationDay item = new VacationDay();
+                    item.setId(rs.getLong("v.id"));
                     item.setDate(rs.getDate("v.vacation_date").toLocalDate());
-                    Time timeFrom = rs.getTime("v.time_from");
-                    if(timeFrom != null) {
+                    final Time timeFrom = rs.getTime("v.time_from");
+                    if (timeFrom != null) {
                         item.setFrom(timeFrom.toLocalTime());
                     }
 
-                    Time timeTo = rs.getTime("v.time_to");
-                    if(timeTo != null) {
+                    final Time timeTo = rs.getTime("v.time_to");
+                    if (timeTo != null) {
                         item.setTo(timeTo.toLocalTime());
                     }
-                    item.setType(VacationType.getVacationType(rs.getString("t.name")));
-                    item.setStatus(Status.getStatus(rs.getString("s.name")));
+
+                    item.setStatus(Status.getStatus(rs.getString("v.status")));
+                    item.setType(VacationType.getVacationType(rs.getString("v.vacation_type")));
                     return item;
                 });
 
     }
 
-    public List<VacationDay> getVacationDays(long userId, LocalDate from, LocalDate to, Status status) {
-        if(status == null) throw new IllegalArgumentException();
-
-        return jdbc.query("SELECT v.vacation_date, v.time_from, v.time_to, t.name, s.name " +
+    public List<VacationDay> getVacationDays(final long userId, final LocalDate from, final LocalDate to, final Status status) {
+        return this.jdbc.query("SELECT v.id, v.vacation_date, v.time_from, v.time_to, v.status, v.vacation_type " +
                         "FROM vacation_day v " +
                         "INNER JOIN end_user u ON v.user_id = u.id " +
-                        "INNER JOIN vacation_type t ON v.type_id = t.id " +
-                        "INNER JOIN approval_status s ON v.status_id = s.id " +
-                        "WHERE v.user_id=? AND v.vacation_date >= ? AND v.vacation_date <= ? AND s.name = ?",
+                        "WHERE v.user_id=? AND v.vacation_date >= ? AND v.vacation_date <= ? AND v.status = ?",
                 new Object[]{userId, from, to, status.name()}, (ResultSet rs, int rowNum) -> {
-                    VacationDay item = new VacationDay();
+                    final VacationDay item = new VacationDay();
+                    item.setId(rs.getLong("v.id"));
                     item.setDate(rs.getDate("v.vacation_date").toLocalDate());
 
-                    Time timeFrom = rs.getTime("v.time_from");
-                    if(timeFrom != null) {
+                    final Time timeFrom = rs.getTime("v.time_from");
+                    if (timeFrom != null) {
                         item.setFrom(timeFrom.toLocalTime());
                     }
 
-                    Time timeTo = rs.getTime("v.time_to");
-                    if(timeTo != null) {
+                    final Time timeTo = rs.getTime("v.time_to");
+                    if (timeTo != null) {
                         item.setTo(timeTo.toLocalTime());
                     }
 
-                    item.setType(VacationType.getVacationType(rs.getString("t.name")));
                     item.setStatus(Status.getStatus(rs.getString("s.name")));
+                    item.setType(VacationType.getVacationType(rs.getString("t.name")));
                     return item;
                 });
     }
 
-    public void insertVacationDay(Long userId, VacationDay day) {
-        jdbc.update("INSERT INTO vacation_day (vacation_date, time_from, time_to, user_id, status_id, type_id) VALUES (?,?,?,?,?,?)",
-                day.getDate(), day.getFrom(), day.getTo(), userId, 2, day.getType().ordinal() + 1);
+    public void insertVacationDay(final Long userId, final VacationDay day) {
+        this.jdbc.update("INSERT INTO vacation_day (vacation_date, time_from, time_to, status, vacation_type, user_id) VALUES (?,?,?,?,?,?)",
+                day.getDate(), day.getFrom(), day.getTo(), "pending", day.getType().name(), userId);
     }
 
-    public void updateVacationDay(VacationDay item) {
-        jdbc.update("UPDATE vacation_day SET vacation_date=?, time_from=?, time_to=?, status_id=?, type_id=? WHERE id=?",
-                item.getDate(), item.getFrom(), item.getTo(), item.getStatus().ordinal() + 1, item.getType().ordinal() + 1, item.getId());
+    public void updateVacationDay(final VacationDay item) {
+        this.jdbc.update("UPDATE vacation_day SET vacation_date=?, time_from=?, time_to=?, status=?, vacation_type=? WHERE id=?",
+                item.getDate(), item.getFrom(), item.getTo(), item.getStatus().name(), item.getType().name(), item.getId());
     }
 
-    public void deleteVacationDay(long id) {
-        jdbc.update("DELETE FROM vacation_day WHERE id=?", id);
+    public void deleteVacationDay(final long id) {
+        this.jdbc.update("DELETE FROM vacation_day WHERE id=?", id);
     }
 }