LocationDAO.java

/*
 * 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.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.io.RandomAccessFile;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import mytime.be.Group;
import mytime.be.Guild;
import mytime.be.Location;
import mytime.be.Person;
import mytime.be.Volunteer;

/**
 *
 * @author Stefan-VpcEB3J1E
 */
public class LocationDAO
{

    /**
     * SQL for getting a location with groups and persons.
     *
     * @param con
     * @param id
     * @return
     */
    public Location getLocationById(Connection con, int locationid) throws SQLException
    {
        List<Group> guildlist = new ArrayList();
        Location returnLocation = null;

        String sqlLoc = "SELECT * FROM Location WHERE id = ?";
        String sql = "SELECT * FROM Guild WHERE locationid = ?";

        PreparedStatement psLoc = con.prepareStatement(sqlLoc);
        psLoc.setInt(1, locationid);
        ResultSet rsLoc = psLoc.executeQuery();
        if (rsLoc.next())
        {
            String name = rsLoc.getString("name");
            String description = rsLoc.getString("description");
            returnLocation = new Location(name, locationid, description);
        } else
        {
            return null;
        }

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, locationid);
        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");
            if (icon == null)
            {
                icon = "mytime/gui/view/css/notebook.png";
            }
            Group guild = new Guild(name, id, locationid, description, icon);
            guildlist.add(guild);
        }
        for (Group group : guildlist)
        {
            List<Person> personlist = 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 sql2 = "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 ps2 = con.prepareStatement(sql2);
            ps2.setInt(1, group.getId().get());
            ResultSet rs2 = ps2.executeQuery();

            while (rs2.next())
            {
                String firstname = rs2.getString("firstname");
                String lastname = rs2.getString("lastname");
                String email = rs2.getString("email");
                String phonenumber = rs2.getString("phonenumber");
                String description = rs2.getString("description");
                String profilepicture = rs2.getString("profilepicture");
                if (profilepicture == null)
                {
                    profilepicture = "https://i.imgsafe.org/3945ecd93f.png";
                }
                int id = rs2.getInt("id");

                Person volunteer = new Volunteer(firstname, lastname, id, email, phonenumber, profilepicture);
                personlist.add(volunteer);

            }
            group.setPersonlist(personlist);
        }

        returnLocation.setGroups(guildlist);
        return returnLocation;
    }

    /**
     * Save the chosen location on the pc, so you dont have to choose everytime
     * you open the application
     *
     * @param location
     * @throws IOException
     */
    public void saveLocationLocally(int locationid) throws IOException
    {
        try (RandomAccessFile raf = new RandomAccessFile(new File("location.dat"), "rw"))
        {
            raf.writeInt(locationid);
        }
    }

    /**
     * Gets the stored location
     *
     * @return
     * @throws IOException
     */
    public int getLocalLocation() throws IOException
    {
        int locationid = -1;
        try (RandomAccessFile raf = new RandomAccessFile(new File("location.dat"), "rw"))
        {
            try
            {
                locationid = raf.readInt();
            } catch (IOException ex)
            {
                return -1;
            }
        }
        return locationid;
    }

    /**
     * Clears the location on this computer.
     * @throws IOException 
     */
    public void clearLocationLocally() throws IOException
    {
        int locationid = -1;
        try (RandomAccessFile raf = new RandomAccessFile(new File("location.dat"), "rw"))
        {
            try
            {
                raf.writeInt(locationid);
            } catch (IOException ex)
            {
                ex.printStackTrace();
            }
        }
    }
}