/*
* 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.util.ArrayList;
import java.util.List;
import mytime.be.Group;
import mytime.be.Guild;
import mytime.be.Location;
/**
*
* @author Bruger
*/
public class GuildDAO
{
/**
* Creates and adds a new guild to the database
*
* @param c
* @param name
* @param location
* @throws SQLException
*/
public Group createGuild(Connection con, String name, int locationId) throws SQLException
{
System.out.println("Guild: ( " + name + ") created!");
String sql = "INSERT INTO Guild(name, locationid)"
+ "VALUES(?, ?)";
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, name);
ps.setInt(2, locationId);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys())
{
if (rs.next())
{
System.out.println(rs.getInt(1));
//System.out.println(rs.get);
// Person person = new Volunteer(firstname, lastname, rs.getInt(1), email, phonenumber, profilepicture);
// person.setDescription(description);
// return person;
Group returnGroup = new Guild(name, rs.getInt(1), locationId, "", "mytime/gui/view/css/notebook.png");
return returnGroup;
}
} catch (SQLException ex)
{
System.out.println("Couldn't get generated keys");
}
return null;
}
/**
* Returns a list of all locations stored in database
*
* @return
*/
public List<Location> getAllLocations(Connection con) throws SQLException
{
List<Location> locations = new ArrayList();
String sql = "SELECT *FROM Location";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
String name = rs.getString("name");
int id = rs.getInt("id");
String description = rs.getString("description");
Location location = new Location(name, id, description);
locations.add(location);
}
return locations;
}
/**
* Returns a list of guilds at a certain location which the given volunteer
* is a member of
*
* @param c
* @param volunteerid
* @param locationid
* @return
*/
public List<Group> getAMembersGuildsAtLocation(Connection con, int volunteerid, int locationid) throws SQLException
{
List<Group> guildList = new ArrayList();
String sql = "SELECT Guild.id, Guild.name, Guild.description, Guild.locationid, Guild.icon FROM Guild Join Works_In w on Guild.id = w.guildid Join Volunteer v on v.id = w.volunteerid WHERE v.id = ? AND Guild.locationid = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, volunteerid);
ps.setInt(2, locationid);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
int id = rs.getInt("id");
String name = rs.getString("name");
String description = rs.getString("description");
String icon = rs.getString("icon");
if (icon == null)
{
icon = "mytime/gui/view/css/notebook.png";
}
Group guild = new Guild(name, id, locationid, description, icon);
guildList.add(guild);
}
return guildList;
}
/**
* @param con
* @param volunteerid
* @return a list of all the groups a person is assigned to
* @throws SQLException
*/
public List<Group> getAllGroupsForPerson(Connection con, int volunteerid) throws SQLException
{
List<Group> guildList = new ArrayList();
String sql = "SELECT Guild.id, Guild.name, Guild.description, Guild.locationid, Guild.icon FROM Guild Join Works_In w on Guild.id = w.guildid Join Volunteer v on v.id = w.volunteerid WHERE v.id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, volunteerid);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
int id = rs.getInt("id");
String name = rs.getString("name");
String description = rs.getString("description");
String icon = rs.getString("icon");
int locationid = rs.getInt("locationid");
if (icon == null)
{
icon = "mytime/gui/view/css/notebook.png";
}
Group guild = new Guild(name, id, locationid, description, icon);
guildList.add(guild);
}
return guildList;
}
/**
* @param c
* @param volunteerid
* @return a list of integers which represent the guild-id's of the
* available guilds for a volunteer
* @throws SQLException
*/
public List<Integer> getArrayOfAvailableGuildsForVolunteer(Connection con, int volunteerid) throws SQLException
{
List<Integer> allGuilds = new ArrayList();
String sql = "SELECT id FROM Guild";
String sql2 = "SELECT guildid FROM Works_In WHERE volunteerid = ?";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
allGuilds.add(rs.getInt("id"));
}
PreparedStatement ps2 = con.prepareStatement(sql2);
ps2.setInt(1, volunteerid);
ResultSet rs2 = ps2.executeQuery();
while (rs2.next())
{
int guildid = rs2.getInt("guildid");
System.out.println("FFDSFG id: " + guildid);
for (int i = 0; i < allGuilds.size(); i++)
{
if (allGuilds.get(i) == guildid)
{
allGuilds.remove(i);
i--;
}
}
}
return allGuilds;
}
/**
*
* @param con
* @return A list of all groups id's and names, other parameters are null.
* @throws SQLException
*/
public List<Group> getAllGroupNamesAndIds(Connection con) throws SQLException
{
List<Group> guildList = new ArrayList();
String sql = "SELECT Guild.id, Guild.name, Guild.icon FROM Guild";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
int id = rs.getInt("id");
String name = rs.getString("name");
String icon = rs.getString("icon");
if (icon == null)
{
icon = "mytime/gui/view/css/notebook.png";
}
Group guild = new Guild(name, id, 0, "null", icon);
guildList.add(guild);
}
return guildList;
}
/**
* Returns all groups stored in the database
* @param con
* @return
* @throws SQLException
*/
public List<Group> getAllGroups(Connection con) throws SQLException
{
List<Group> allGroups = new ArrayList();
String sql = "SELECT * FROM Guild";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
int id = rs.getInt("id");
String name = rs.getString("name");
String description = rs.getString("description");
int locationId = rs.getInt("locationid");
String icon = rs.getString("icon");
Guild guild = new Guild(name, id, locationId, description, icon);
allGroups.add(guild);
}
return allGroups;
}
}