/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package mytime.dal.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Queue;
import java.util.concurrent.ConcurrentLinkedQueue;
import mytime.be.Group;
import mytime.be.Person;
import mytime.be.Volunteer;
/**
*
* @author Stefan-VpcEB3J1E
*/
public class VolunteerDAO
{
private int lastestTransId;
private Person lastDeletedPerson;
/**
*
* @param c the connection to the database
* @param group the guild whose members you want to get
* @return a list of all volunteers in a given guild
* @throws SQLException
*/
public List<Person> getAllVolunteersInGuild(Connection con, Group group) throws SQLException
{
List<Person> volunteers = new ArrayList();
//String sql = "SELECT v.name, v.email, v.phonenumber, v.description, v.id v.profilepicture FROM Volunteer v Join Works_In w ON v.id = w.volunteerid Join Guild g ON w.guildid = g.id WHERE g.id = ?";
String sql = "SELECT Volunteer.firstname, Volunteer.lastname, email, phonenumber, Volunteer.description, Volunteer.id, profilepicture FROM Volunteer Join Works_In w ON id = w.volunteerid Join Guild g ON w.guildid = g.id WHERE g.id = ? AND removed = 0";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, group.getId().get());
ResultSet rs = ps.executeQuery();
while (rs.next())
{
String firstname = rs.getString("firstname");
String lastname = rs.getString("lastname");
String email = rs.getString("email");
String phonenumber = rs.getString("phonenumber");
String description = rs.getString("description");
String profilepicture = rs.getString("profilepicture");
if (profilepicture == null)
{
profilepicture = "https://i.imgsafe.org/3945ecd93f.png";
}
int id = rs.getInt("id");
Person volunteer = new Volunteer(firstname, lastname, id, email, phonenumber, profilepicture);
volunteer.setDescription(description);
volunteers.add(volunteer);
}
return volunteers;
}
/**
* Creates and adds a volunteer to the database.
*
* @param con
* @param firstname
* @param lastname
* @param email
* @param phonenumber
* @throws SQLException
*/
public Person createVolunteer(Connection con, String firstname, String lastname, String email, String phonenumber, String description, String profilepicture) throws SQLException
{
System.out.println("Volunteer get");
String sql = "INSERT INTO Volunteer(firstname, lastname, email, phonenumber, description, profilepicture)"
+ "VALUES(?, ?, ?, ?, ?, ?)";
//String sql2 = "SELECT id FROM Volunteer WHERE firstname = ? AND lastname = ? AND email = ? AND phonenumber = ?";
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, firstname);
ps.setString(2, lastname);
ps.setString(3, email);
ps.setString(4, phonenumber);
ps.setString(5, description);
ps.setString(6, profilepicture);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys())
{
if (rs.next())
{
System.out.println(rs.getInt(1));
//System.out.println(rs.get);
if (profilepicture == null)
{
profilepicture = "https://i.imgsafe.org/3945ecd93f.png";
}
Person person = new Volunteer(firstname, lastname, rs.getInt(1), email, phonenumber, profilepicture);
person.setDescription(description);
return person;
}
} catch (SQLException ex)
{
System.out.println("Couldn't get generated keys");
}
return null;
}
/**
* Assign a volunteer to a guild
*
* @param c
* @param volunteerid
* @param guildid
* @throws SQLException
*/
public void addVolunteerToGuild(Connection con, int volunteerid, int guildid) throws SQLException
{
String sql = "INSERT INTO Works_In(volunteerid, guildid) VALUES(?, ?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, volunteerid);
ps.setInt(2, guildid);
ps.executeUpdate();
}
/**
* Adds a hour-transaction to the database. example: (Date: 24-06-2017,
* Hours added: 6, to guild(id): 2, By volunteer(id): 1)
*
* @param con
* @param volunteerid
* @param guildid
* @param hours
* @throws java.sql.SQLException
*/
public void addHoursForVolunteer(Connection con, int volunteerid, int guildid, int hours) throws SQLException
{
String sql = "INSERT INTO HoursTransaction(hours, volunteerid, guildid, date)"
+ "VALUES(?, ?, ?, ?)";
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, hours);
ps.setInt(2, volunteerid);
ps.setInt(3, guildid);
LocalDate ld = LocalDate.now();
Date date = java.sql.Date.valueOf(ld);
ps.setDate(4, (java.sql.Date) date);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys())
{
if (rs.next())
{
lastestTransId = rs.getInt(1);
}
} catch (SQLException ex)
{
System.out.println("Couldn't get generated keys");
}
}
/**
*
* @param c
* @param volunteerid
* @return the amount of hours a volunteer has worked in total
*/
public int getTotalHoursOneVolunteer(Connection con, int volunteerid) throws SQLException
{
String columnname = "hours";
String sql = "SELECT SUM(Hours) AS " + columnname + " FROM HoursTransaction WHERE volunteerid = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, volunteerid);
ResultSet rs = ps.executeQuery();
if (rs.next())
{
return rs.getInt(columnname);
} else
{
return -1;
}
}
/**
* @param c
* @param volunteerid
* @param guildid
* @return amount of hours one person worked on one guild, as an int.
* @throws SQLException
*/
public int getHoursWorkedOnOneGuildByVolunteer(Connection con, int volunteerid, int guildid) throws SQLException
{
String columnname = "hours";
String sql = "SELECT SUM(Hours) AS " + columnname + " FROM HoursTransaction WHERE volunteerid = ? AND guildid = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, volunteerid);
ps.setInt(2, guildid);
ResultSet rs = ps.executeQuery();
if (rs.next())
{
return rs.getInt(columnname);
} else
{
System.out.println("Something went wrong");
return -1;
}
}
/**
* Undoes the lastest transaction of documented hours.
*
* @param c
* @throws SQLException
*/
public void undoLastChange(Connection c) throws SQLException
{
String sql = "DELETE FROM HoursTransaction WHERE transid = ?";
PreparedStatement ps = c.prepareStatement(sql);
ps.setInt(1, lastestTransId);
ps.executeUpdate();
}
/**
* @return a list of all volunteers
*/
public List<Person> getAllVolunteers(Connection con) throws SQLException
{
List<Person> personList = new ArrayList<>();
String sql = "SELECT * FROM Volunteer WHERE removed = 0";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
String firstname = rs.getString("firstname");
String lastname = rs.getString("lastname");
String email = rs.getString("email");
String phonenumber = rs.getString("phonenumber");
String description = rs.getString("description");
int id = rs.getInt("id");
String profilepicture = rs.getString("profilepicture");
if (profilepicture == null)
{
profilepicture = "https://i.imgsafe.org/3945ecd93f.png";
}
Person person = new Volunteer(firstname, lastname, id, email, phonenumber, profilepicture);
person.setDescription(description);
personList.add(person);
}
return personList;
}
/**
* Marks a volunteer to be ignored in the database
*
* @param c
* @param volunteerid
* @throws SQLException
*/
public void deleteVolunteer(Connection c, Person p) throws SQLException
{
lastDeletedPerson = p;
String sql = "update Volunteer set removed = 'True' where id = ?";
PreparedStatement ps = c.prepareStatement(sql);
ps.setInt(1, p.getId().get());
ps.executeUpdate();
}
/**
* Undoes the lastest deleted person
*
* @param c
* @throws SQLException
*/
public Person undoLastDeletedPerson(Connection c) throws SQLException
{
// String sql = "INSERT INTO Volunteer(firstname, lastname, email, phonenumber, description, profilepicture)"
// + "VALUES(?, ?, ?, ?, ?, ?)";
String sql = "UPDATE Volunteer SET firstname = ?, lastname = ?, email = ?, phonenumber = ?, description = ?, profilepicture = ? "
+ "WHERE Volunteer.id = ?;";
PreparedStatement ps = c.prepareStatement(sql);
ps.setString(1, lastDeletedPerson.getFirstName().get());
ps.setString(2, lastDeletedPerson.getLastName().get());
ps.setString(3, lastDeletedPerson.getEmail().get());
ps.setString(4, lastDeletedPerson.getPhonenumber().get());
ps.setString(5, lastDeletedPerson.getDescription().get());
ps.setString(6, lastDeletedPerson.getProfilePicture().get());
ps.setInt(7, lastDeletedPerson.getId().get());
ps.executeUpdate();
return lastDeletedPerson;
}
/**
* Updates the information of a person
*
* @param con Connection to database
* @param person
* @throws SQLException
*/
public void updatePerson(Connection con, Person person) throws SQLException
{
String sql = "UPDATE Volunteer SET firstname = ?, lastname = ?, email = ?, phonenumber = ?, description = ?, profilepicture = ? "
+ "WHERE Volunteer.id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, person.getFirstName().get());
ps.setString(2, person.getLastName().get());
ps.setString(3, person.getEmail().get());
ps.setString(4, person.getPhonenumber().get());
if (person.getDescription().get() == null)
{
ps.setString(5, null);
} else if (person.getDescription().get().equals(""))
{
ps.setString(5, null);
} else
{
ps.setString(5, person.getDescription().get());
}
ps.setString(6, person.getProfilePicture().get());
ps.setInt(7, person.getId().get());
ps.executeUpdate();
}
/**
* Removes a person from a group
*
* @param con
* @param person
* @param group
* @throws SQLException
*/
public void removePersonFromGroup(Connection con, Person person, Group group) throws SQLException
{
String sql = "DELETE FROM Works_In WHERE volunteerid = ? AND guildid = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, person.getId().get());
ps.setInt(2, group.getId().get());
ps.executeUpdate();
}
/**
* Get sum of hours worked in each guild by one volunteer
* @param con
* @param person
* @param groupAmount
* @return int[guild id][hours]
* @throws SQLException
*/
public int[][] getHoursWorkedInEachGuildOnByPerson(Connection con, Person person, int groupAmount) throws SQLException
{
int[][] hoursInEachGuid = null;
//int[] hoursInEachGuild;
//HashMap<Integer, Integer> hmap = new HashMap<Integer, Integer>();
//Group //Hours
if (groupAmount == -1)
{
String sqlAmount = "SELECT * FROM Guild";
Statement psAmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rsAmt = psAmt.executeQuery(sqlAmount);
int j = 0;
while(rsAmt.next())
{
j++;
}
groupAmount = j;
rsAmt.beforeFirst();
hoursInEachGuid = new int[groupAmount][2];
int i = 0;
while(rsAmt.next())
{
hoursInEachGuid[i][0] = rsAmt.getInt("id");
i++;
}
}
String sql = "SELECT guildid, SUM(hours) as timer FROM HoursTransaction WHERE volunteerid = ? GROUP BY guildid;";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, person.getId().get());
ResultSet rs = ps.executeQuery();
while(rs.next())
{
int guildId = rs.getInt("guildid");
int hours = rs.getInt("timer");
for (int i = 0; i < hoursInEachGuid.length; i++)
{
if (hoursInEachGuid[i][0] == guildId)
{
hoursInEachGuid[i][1] = hours;
break;
}
}
}
return hoursInEachGuid;
}
}