Revize 6e79da77

Re #7526 Database queries

server/init.sql

@@ -37 +37 @@
   first_name VARCHAR(45) NOT NULL,
   last_name VARCHAR(45) NOT NULL,
   no_vacations FLOAT,
-  no_sick_days SMALLINT,
+  no_sick_days INT,
+  taken_vacations FLOAT NOT NULL,
+  taken_sick_days INT NOT NULL,
   alert DATETIME,
   token TEXT NOT NULL,
-  email VARCHAR(45) NOT NULL,
+  email VARCHAR(100) NOT NULL,
   photo TEXT,
   creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   role_id TINYINT NOT NULL,
@@ -54 +56 @@
   CONSTRAINT fk_end_user_approval_status FOREIGN KEY (status_id)
     REFERENCES approval_status (id)
     ON DELETE NO ACTION ON UPDATE CASCADE,
-  CONSTRAINT check_no_vacation CHECK (no_vacations >= 0),
-  CONSTRAINT check_no_sick_days CHECK (no_sick_days >= 0),
-  CONSTRAINT check_email CHECK (email = '%_@__%.__%')
+  CONSTRAINT check_taken_vacation CHECK (taken_vacations >= 0),
+  CONSTRAINT check_taken_sick_days CHECK (taken_sick_days >= 0)
 );
 
 -- -----------------------------------------------------
@@ -75 +76 @@
   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)
 );
 
@@ -90 +95 @@
 DROP TABLE IF EXISTS default_settings;
 CREATE TABLE default_settings (
   id BIGINT NOT NULL AUTO_INCREMENT,
-  no_sick_days SMALLINT NOT NULL,
+  no_sick_days INT NOT NULL,
   alert DATETIME NOT NULL,
   creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (id),
   CONSTRAINT check_no_sick_days CHECK (no_sick_days >= 0)
 );
 
+DELIMITER $$
+DROP PROCEDURE IF EXISTS GetUserId $$
+CREATE PROCEDURE GetUserId(
+  IN in_id BIGINT,
+  OUT out_id BIGINT,
+  OUT out_first_name VARCHAR(45),
+  OUT out_last_name VARCHAR(45),
+  OUT out_no_vacations FLOAT,
+  OUT out_no_sick_days INT,
+  OUT out_taken_vacations FLOAT,
+  OUT out_taken_sick_days INT,
+  OUT out_alert DATETIME,
+  OUT out_email VARCHAR(100),
+  OUT out_photo TEXT,
+  OUT out_creation_date DATETIME,
+  OUT out_role VARCHAR(8),
+  OUT out_status VARCHAR(8))
+BEGIN
+  DECLARE sickDaysCount INT;
+  DECLARE notification DATETIME;
+
+  SELECT no_sick_days, alert INTO sickDaysCount, notification FROM default_settings ORDER BY id DESC LIMIT 1;
+  SELECT EU.id, EU.first_name, EU.last_name, EU.no_vacation, IFNULL(EU.no_sick_days, sickDaysCount), EU.taken_vacations, EU.taken_sick_days, IFNULL(EU.alert, notification), EU.email, EU.photo, EU.creation_date, R.name, APS.name
+     INTO out_id, out_first_name, out_last_name, out_no_vacations, out_no_sick_days, out_taken_vacations, out_taken_sick_days, out_alert, out_email, out_photo, out_creation_date, out_role, out_status
+     FROM end_user EU
+     INNER JOIN role R ON EU.role_id=R.id
+     INNER JOIN approval_status APS ON EU.status_id=APS.id
+     WHERE EU.id=in_id;
+END $$
+
+DROP PROCEDURE IF EXISTS GetUserToken $$
+CREATE PROCEDURE GetUserToken(
+  IN p_token TEXT,
+  OUT out_id BIGINT,
+  OUT out_first_name VARCHAR(45),
+  OUT out_last_name VARCHAR(45),
+  OUT out_no_vacations FLOAT,
+  OUT out_no_sick_days INT,
+  OUT out_taken_vacations FLOAT,
+  OUT out_taken_sick_days INT,
+  OUT out_alert DATETIME,
+  OUT out_email VARCHAR(100),
+  OUT out_photo TEXT,
+  OUT out_creation_date DATETIME,
+  OUT out_role VARCHAR(8),
+  OUT out_status VARCHAR(8))
+BEGIN
+  DECLARE sickDaysCount INT;
+  DECLARE notification DATETIME;
+
+  SELECT no_sick_days, alert INTO sickDaysCount, notification FROM default_settings ORDER BY id DESC LIMIT 1;
+  SELECT EU.id, EU.first_name, EU.last_name, EU.no_vacation, IFNULL(EU.no_sick_days, sickDaysCount), EU.taken_vacations, EU.taken_sick_days, IFNULL(EU.alert, notification), EU.email, EU.photo, EU.creation_date, R.name, APS.name
+     INTO out_id, out_first_name, out_last_name, out_no_vacations, out_no_sick_days, out_taken_vacations, out_taken_sick_days, out_alert, out_email, out_photo, out_creation_date, out_role, out_status
+     FROM end_user EU
+     INNER JOIN role R ON EU.role_id=R.id
+     INNER JOIN approval_status APS ON EU.status_id=APS.id
+     WHERE EU.token=p_token;
+END $$
+
+DELIMITER ;
+
 -- -----------------------------------------------------
 -- Insert table role
 -- -----------------------------------------------------

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

@@ +1 @@
+package cz.zcu.yamanager.business;
+
+import cz.zcu.yamanager.dto.*;
+import cz.zcu.yamanager.repository.RequestRepository;
+import cz.zcu.yamanager.repository.UserRepository;
+import cz.zcu.yamanager.repository.VacationRepository;
+import cz.zcu.yamanager.ws.rest.RESTFullException;
+import org.apache.tomcat.jni.Local;
+import org.springframework.jdbc.core.JdbcTemplate;
+
+import java.time.LocalDate;
+import java.util.List;
+
+public class ApiManager implements Manager {
+
+    private static final int DAYS_IN_WEEK = 7;
+
+    private RequestRepository requestRepository;
+    private UserRepository userRepository;
+    private VacationRepository vacationRepository;
+
+    public ApiManager(JdbcTemplate jdbc) {
+        this.requestRepository = new RequestRepository(jdbc);
+        this.userRepository = new UserRepository(jdbc);
+        this.vacationRepository = new VacationRepository(jdbc);
+    }
+
+    @Override
+    public List<BasicProfileUser> getUsers(Status status) throws RESTFullException {
+        List<BasicProfileUser> users = this.userRepository.getAllBasicUsers();
+        LocalDate today = LocalDate.now();
+        LocalDate weekBefore = today.minusDays(ApiManager.DAYS_IN_WEEK);
+        LocalDate weekAfter = today.plusDays(ApiManager.DAYS_IN_WEEK);
+        for (BasicProfileUser user : users) {
+            user.setCalendar(this.vacationRepository.getVacationDays(user.getId(), weekBefore, weekAfter));
+        }
+
+        return users;
+    }
+
+    @Override
+    public List<VacationRequest> getVacationRequests(Status status) throws RESTFullException {
+        return this.requestRepository.getAllVacationRequests(status);
+    }
+
+    @Override
+    public List<AuthorizationRequest> getAuthorizationRequests(Status status) throws RESTFullException {
+        return this.requestRepository.getAllAuthorizations(status);
+    }
+
+    @Override
+    public FullUserProfile getUserProfile(Long userId) throws RESTFullException {
+        return this.userRepository.getFullUser(userId);
+    }
+
+    @Override
+    public DefaultSettings getDefaultSettings() throws RESTFullException {
+        return this.userRepository.getLastDefaultSettings();
+    }
+
+    @Override
+    public List<VacationDay> getUserCalendar(Long userId, LocalDate fromDate, LocalDate toDate, Status status) throws RESTFullException {
+        return this.vacationRepository.getVacationDays(userId, fromDate, toDate, status);
+    }
+
+    @Override
+    public void createSettings(DefaultSettings settings) throws RESTFullException {
+        this.userRepository.insertSettings(settings);
+    }
+
+    @Override
+    public void createVacation(Long userId, VacationDay vacationDay) throws RESTFullException {
+        //this.vacationRepository.
+        // TODO
+    }
+
+    @Override
+    public void changeSettings(Long userId, UserSettings settings) throws RESTFullException {
+        this.userRepository.updateUserSettings(settings);
+    }
+
+    @Override
+    public void changeVacation(Long userId, VacationDay vacationDay) throws RESTFullException {
+        this.vacationRepository.updateVacationDay(vacationDay);
+    }
+
+    @Override
+    public void changeRequest(RequestType type, BasicRequest request) throws RESTFullException {
+        if(RequestType.VACATION == type) {
+            this.requestRepository.updateVacationRequest(request);
+        } else {
+            this.requestRepository.updateAuthorization(request);
+        }
+
+    }
+
+    @Override
+    public void deleteVacation(Long userId, Long vacationId) throws RESTFullException {
+        this.vacationRepository.deleteVacationDay(vacationId);
+    }
+}

server/src/main/java/cz/zcu/yamanager/dto/FullUserProfile.java

@@ -11 +11 @@
     private String photo;
     private Float vacationCount;
     private Integer sickdayCount;
+    private Float takenVacationCount;
+    private Integer takenSickdayCount;
     private Status status;
     private UserRole role;
     private LocalDateTime notification;
+    private String email;
+
+    public String getEmail() {
+        return email;
+    }
+
+    public void setEmail(String email) {
+        this.email = email;
+    }
 
     public Long getId() {
         return id;
@@ -27 +38 @@
         return firstName;
     }
 
+    public Float getTakenVacationCount() {
+        return takenVacationCount;
+    }
+
+    public void setTakenVacationCount(Float takenVacationCount) {
+        this.takenVacationCount = takenVacationCount;
+    }
+
+    public Integer getTakenSickdayCount() {
+        return takenSickdayCount;
+    }
+
+    public void setTakenSickdayCount(Integer takenSickdayCount) {
+        this.takenSickdayCount = takenSickdayCount;
+    }
+
     public void setFirstName(String firstName) {
         this.firstName = firstName;
     }

server/src/main/java/cz/zcu/yamanager/dto/Status.java

@@ -1 +1 @@
 package cz.zcu.yamanager.dto;
 
 public enum Status {
-    ACCEPTED, PENDING, REJECTED
+    ACCEPTED, PENDING, REJECTED;
 
-    ;
     public static Status getStatus(String status) {
         if (status == null || status.isEmpty()) return null;
         try {

server/src/main/java/cz/zcu/yamanager/dto/UserRole.java

@@ -1 +1 @@
 package cz.zcu.yamanager.dto;
 
 public enum  UserRole {
-    EMPLOYEE, EMPLOYER
+    EMPLOYEE, EMPLOYER;
+
+    public static UserRole getUserRole(String role) {
+        if (role == null || role.isEmpty()) return null;
+        try {
+            return valueOf(role.toUpperCase());
+        } catch (IllegalArgumentException e) {
+            return null;
+        }
+    }
 }

server/src/main/java/cz/zcu/yamanager/dto/VacationType.java

@@ -1 +1 @@
 package cz.zcu.yamanager.dto;
 
 public enum VacationType {
-    VACATION, SICKDAY
+    VACATION, SICKDAY;
+
+    public static VacationType getVacationType(String type) {
+        if (type == null || type.isEmpty()) return null;
+        try {
+            return valueOf(type.toUpperCase());
+        } catch (IllegalArgumentException e) {
+            return null;
+        }
+    }
 }

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

@@ +1 @@
+package cz.zcu.yamanager.repository;
+
+import cz.zcu.yamanager.dto.*;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.stereotype.Repository;
+
+import java.sql.ResultSet;
+import java.util.List;
+
+/**
+ * An instance of the class RequestRepository handles queries which selects and updates requests in a database.
+ */
+@Repository
+public class RequestRepository {
+    /**
+     * The logger.
+     */
+    private static final Logger log = LoggerFactory.getLogger(RequestRepository.class);
+
+    /**
+     * The connection to a database.
+     */
+    private final JdbcTemplate jdbc;
+
+    /**
+     * 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");
+        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.");
+
+        return jdbc.query("SELECT EU.id, EU.first_name, EU.last_name, EU.creation_date, APS.name " +
+                "FROM end_user EU " +
+                "INNER JOIN approval_status APS ON EU.status_id = APS.id",
+                (ResultSet rs, int rowNum) -> {
+            AuthorizationRequest request = new AuthorizationRequest();
+            request.setId(rs.getLong("EU.id"));
+            request.setFirstName(rs.getString("EU.first_name"));
+            request.setLastName(rs.getString("EU.last_name"));
+            request.setStatus(Status.getStatus(rs.getString("APS.name")));
+            request.setTimestamp(rs.getTimestamp("EU.creation_date").toLocalDateTime());
+            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);
+
+        return jdbc.query("SELECT EU.id, EU.first_name, EU.last_name, EU.creation_date " +
+                "FROM end_user EU " +
+                "INNER JOIN approval_status APS ON EU.status_id = APS.id " +
+                "WHERE APS.name=?",
+                new Object[]{status.name()},
+                (ResultSet rs, int rowNum) -> {
+            AuthorizationRequest request = new AuthorizationRequest();
+            request.setId(rs.getLong("EU.id"));
+            request.setFirstName(rs.getString("EU.first_name"));
+            request.setLastName(rs.getString("EU.last_name"));
+            request.setStatus(status);
+            request.setTimestamp(rs.getTimestamp("EU.creation_date").toLocalDateTime());
+            return request;
+        });
+    }
+
+    /**
+     * Updates a status of an authorization request with the given id.
+     * @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);
+
+        jdbc.update("UPDATE end_user EU, approval_status APS SET EU.status_id=APS.id WHERE EU.id=? AND APS.name=?", id, status.name());
+    }
+
+    /**
+     * 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());
+    }
+
+    /**
+     * 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());
+    }
+
+    /**
+     * 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.");
+
+        return jdbc.query("SELECT VD.id, VD.vacation_date, VD.time_from, VD.time_to, VD.creation_date, VT.name, APS.name, EU.first_name, EU.last_name " +
+                "FROM vacation_day VD " +
+                "INNER JOIN vacation_type VT on VD.type_id=VT.id " +
+                "INNER JOIN approval_status APS ON VD.status_id=APS.id" +
+                "INNER JOIN end_user EU ON VD.user_id=EU.id",
+                (ResultSet rs, int rowNum) -> {
+            VacationRequest request = new VacationRequest();
+            request.setId(rs.getLong("VD.id"));
+            request.setFirstName(rs.getString("EU.first_name"));
+            request.setLastName(rs.getString("EU.last_name"));
+            request.setDate(rs.getDate("VD.vacation_date").toLocalDate());
+            request.setFrom(rs.getTime("VD.time_from").toLocalTime());
+            request.setTo(rs.getTime("VD.time_to").toLocalTime());
+            request.setType(VacationType.getVacationType("VT.name"));
+            request.setStatus(Status.getStatus("APS.name"));
+            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);
+
+        return jdbc.query("SELECT VD.id, VD.vacation_date, VD.time_from, VD.time_to, VD.creation_date, VT.name, APS.name, EU.first_name, EU.last_name " +
+                "FROM vacation_day VD " +
+                "INNER JOIN vacation_type VT on VD.type_id=VT.id " +
+                "INNER JOIN approval_status APS ON VD.status_id=APS.id" +
+                "INNER JOIN end_user EU ON VD.user_id=EU.id" +
+                "WHERE APS.name=?",
+                new Object[]{status.name()},
+                (ResultSet rs, int rowNum) -> {
+            VacationRequest request = new VacationRequest();
+            request.setId(rs.getLong("VD.id"));
+            request.setFirstName(rs.getString("EU.first_name"));
+            request.setLastName(rs.getString("EU.last_name"));
+            request.setDate(rs.getDate("VD.vacation_date").toLocalDate());
+            request.setFrom(rs.getTime("VD.time_from").toLocalTime());
+            request.setTo(rs.getTime("VD.time_to").toLocalTime());
+            request.setType(VacationType.getVacationType("VT.name"));
+            request.setStatus(status);
+            return request;
+        });
+    }
+
+    /**
+     * Updates a status of a vacation request with the given id.
+     * @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);
+
+        jdbc.update("UPDATE vacation_day VD, approval_status APS SET VD.status_id=APS.id WHERE VD.id=? AND APS.name=?", id, status.name());
+    }
+
+    /**
+     * 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());
+    }
+
+    /**
+     * 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());
+    }
+}

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

@@ +1 @@
+package cz.zcu.yamanager.repository;
+
+import cz.zcu.yamanager.dto.*;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.beans.factory.annotation.Autowired;
+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.CallableStatement;
+import java.sql.ResultSet;
+import java.sql.Timestamp;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+@Repository
+public class UserRepository {
+    /**
+     * The logger.
+     */
+    private static final Logger log = LoggerFactory.getLogger(UserRepository.class);
+
+    /**
+     * The connection to a database.
+     */
+    private final JdbcTemplate jdbc;
+
+    /**
+     * Creates a new instance of the class UserRepository which selects and updates users in a database.
+     * @param jdbc A connection to the database.
+     */
+    @Autowired
+    public UserRepository(JdbcTemplate jdbc) {
+        log.trace("Creating a new instance of the class UserRepository");
+        this.jdbc = jdbc;
+    }
+
+    public List<BasicProfileUser> getAllBasicUsers() {
+        return jdbc.query("SELECT id, first_name, last_name, photo FROM end_user", (ResultSet rs, int rowNum) -> {
+            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) {
+        List<SqlParameter> paramList = new ArrayList<>();
+        paramList.add(new SqlParameter(Types.BIGINT));
+        paramList.add(new SqlOutParameter("out_id", Types.BIGINT));
+        paramList.add(new SqlOutParameter("out_first_name", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_last_name", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_no_vacations", Types.FLOAT));
+        paramList.add(new SqlOutParameter("out_no_sick_days", Types.INTEGER));
+        paramList.add(new SqlOutParameter("out_taken_vacations", Types.FLOAT));
+        paramList.add(new SqlOutParameter("out_taken_sick_days", Types.INTEGER));
+        paramList.add(new SqlOutParameter("out_alert", Types.DATE));
+        paramList.add(new SqlOutParameter("out_email", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_photo", Types.LONGVARCHAR));
+        paramList.add(new SqlOutParameter("out_creation_date", Types.DATE));
+        paramList.add(new SqlOutParameter("out_role", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_status", Types.VARCHAR));
+
+        Map<String, Object> resultMap = this.jdbc.call(con -> {
+            CallableStatement callableStatement = con.prepareCall("{call GetUserId(?)}");
+            callableStatement.setLong(1, id);
+            callableStatement.registerOutParameter(2, Types.BIGINT);
+            callableStatement.registerOutParameter(3, Types.VARCHAR);
+            callableStatement.registerOutParameter(4, Types.VARCHAR);
+            callableStatement.registerOutParameter(5, Types.FLOAT);
+            callableStatement.registerOutParameter(6, Types.INTEGER);
+            callableStatement.registerOutParameter(7, Types.FLOAT);
+            callableStatement.registerOutParameter(8, Types.INTEGER);
+            callableStatement.registerOutParameter(9, Types.DATE);
+            callableStatement.registerOutParameter(10, Types.VARCHAR);
+            callableStatement.registerOutParameter(11, Types.LONGNVARCHAR);
+            callableStatement.registerOutParameter(12, Types.DATE);
+            callableStatement.registerOutParameter(13, Types.VARCHAR);
+            callableStatement.registerOutParameter(14, Types.VARCHAR);
+            return callableStatement;
+        }, 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((float)resultMap.get("out_no_vacations"));
+        user.setSickdayCount((int)resultMap.get("out_no_sick_days"));
+        user.setTakenVacationCount((float)resultMap.get("out_taken_vacations"));
+        user.setTakenSickdayCount((int)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_id")));
+        user.setStatus(Status.getStatus((String)resultMap.get("out_status_id")));
+        return user;
+
+    }
+
+    public FullUserProfile getFullUser(String token) {
+        List<SqlParameter> paramList = new ArrayList<>();
+        paramList.add(new SqlParameter(Types.LONGNVARCHAR));
+        paramList.add(new SqlOutParameter("out_id", Types.BIGINT));
+        paramList.add(new SqlOutParameter("out_first_name", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_last_name", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_no_vacations", Types.FLOAT));
+        paramList.add(new SqlOutParameter("out_no_sick_days", Types.INTEGER));
+        paramList.add(new SqlOutParameter("out_taken_vacations", Types.FLOAT));
+        paramList.add(new SqlOutParameter("out_taken_sick_days", Types.INTEGER));
+        paramList.add(new SqlOutParameter("out_alert", Types.DATE));
+        paramList.add(new SqlOutParameter("out_email", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_photo", Types.LONGVARCHAR));
+        paramList.add(new SqlOutParameter("out_creation_date", Types.DATE));
+        paramList.add(new SqlOutParameter("out_role", Types.VARCHAR));
+        paramList.add(new SqlOutParameter("out_status", Types.VARCHAR));
+
+        Map<String, Object> resultMap = this.jdbc.call(con -> {
+            CallableStatement callableStatement = con.prepareCall("{call GetUserToken(?)}");
+            callableStatement.setString(1, token);
+            callableStatement.registerOutParameter(2, Types.BIGINT);
+            callableStatement.registerOutParameter(3, Types.VARCHAR);
+            callableStatement.registerOutParameter(4, Types.VARCHAR);
+            callableStatement.registerOutParameter(5, Types.FLOAT);
+            callableStatement.registerOutParameter(6, Types.INTEGER);
+            callableStatement.registerOutParameter(7, Types.FLOAT);
+            callableStatement.registerOutParameter(8, Types.INTEGER);
+            callableStatement.registerOutParameter(9, Types.DATE);
+            callableStatement.registerOutParameter(10, Types.VARCHAR);
+            callableStatement.registerOutParameter(11, Types.LONGNVARCHAR);
+            callableStatement.registerOutParameter(12, Types.DATE);
+            callableStatement.registerOutParameter(13, Types.VARCHAR);
+            callableStatement.registerOutParameter(14, Types.VARCHAR);
+            return callableStatement;
+        }, 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((float)resultMap.get("out_no_vacations"));
+        user.setSickdayCount((int)resultMap.get("out_no_sick_days"));
+        user.setTakenVacationCount((float)resultMap.get("out_taken_vacations"));
+        user.setTakenSickdayCount((int)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_id")));
+        user.setStatus(Status.getStatus((String)resultMap.get("out_status_id")));
+        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;
+        });
+    }
+
+    public void updateUserSettings(UserSettings settings) {
+        jdbc.update("UPDATE end_user EU, role R SET EU.no_vacations=?, EU.no_sick_days=?, EU.role_id=R.id WHERE EU.id = ? AND R.name=?",
+                settings.getVacationCount(), settings.getSickdayCount(), settings.getId(), settings.getRole().name());
+    }
+
+    public DefaultSettings getLastDefaultSettings() {
+        return jdbc.queryForObject("SELECT * FROM default_settings ORDER BY id DESC LIMIT 1", (ResultSet rs, int rowNum) -> {
+            DefaultSettings settings = new DefaultSettings();
+            settings.setSickdayCount(rs.getInt("no_sick_days"));
+            settings.setNotification(rs.getTimestamp("alert").toLocalDateTime());
+            return settings;
+        });
+    }
+
+    public void insertSettings(DefaultSettings settings) {
+        jdbc.update("INSERT INTO default_settings (no_sick_days, alert) VALUES (?, ?)", settings.getSickdayCount(), settings.getNotification());
+    }
+}

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

@@ +1 @@
+package cz.zcu.yamanager.repository;
+
+import cz.zcu.yamanager.dto.Status;
+import cz.zcu.yamanager.dto.VacationDay;
+import cz.zcu.yamanager.dto.VacationType;
+
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.stereotype.Repository;
+
+import java.sql.ResultSet;
+import java.time.LocalDate;
+import java.util.List;
+
+@Repository
+public class VacationRepository {
+    /**
+     * The logger.
+     */
+    private static final Logger log = LoggerFactory.getLogger(VacationRepository.class);
+
+    /**
+     * The connection to a database.
+     */
+    private final JdbcTemplate jdbc;
+
+    /**
+     * 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");
+        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 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 VD.user_id = ? AND VD.vacation_date >= ?",
+                new Object[]{userId, 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(long userId, 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 VD.user_id=? AND VD.vacation_date >= ? AND VD.vacation_date <= ?",
+                new Object[]{userId, 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, LocalDate to, Status status) {
+        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 VD.user_id=? AND VD.vacation_date >= ? AND VD.vacation_date <= ? AND APS.name = ?",
+                new Object[]{userId, from, to, status.name()}, (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 void insertVacationDay(String token, VacationDay day) {
+        jdbc.update("INSERT INTO vacation_day (vacation_date, time_from, time_to, status_id, type_id) VALUES (?,?,?,?,?)",
+                day.getDate(), day.getFrom(), day.getTo(), day.getStatus().ordinal(), day.getType().ordinal());
+    }
+
+    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(), item.getType().ordinal(), item.getId());
+    }
+
+    public void deleteVacationDay(long id) {
+        jdbc.update("DELETE FROM vacation_day WHERE id=?", id);
+    }
+}