package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * Created by audun on 9/26/14.
 */
public class DBManager extends DBConnector {
    private double temperature = 0.0;
    private int lighting;
    private int pressure;
    private int humidity;
    private int sound;
    private int sensorID;

    private PreparedStatement getAllCurrentValuesStmt;

    public DBManager() throws SQLException {
        String query;

        query = "SELECT DISTINCT * FROM (                   " +
                "   SELECT MAX(Date) AS MaxDate, SensorID   " +
                "   FROM ?                                  " +
                "   GROUP BY SensorID                       " +
                ") AS T1 JOIN ? AS T2                       " +
                "ON MaxDate = Date AND T1.SensorID = T2.SensorID;";
        getAllCurrentValuesStmt = connection.prepareStatement(query);
    }

    /*
     * A database connection is available through the inherited field 'connection'.
     */

    public List<SensorSample> getAllNewSamples() {
        List<SensorSample> sampleList = new ArrayList<SensorSample>();

        String[] tableNames = {
            "TemperatureSample",
            "LightSample",
            "PressureSample",
            "HumiditySample",
            "SoundSample"
        };
        for(String name : tableNames) {
            sampleList.addAll(getAllNewSamples(name));
        }

        return sampleList;
    }

    private List<SensorSample> getAllNewSamples(String tableName) {
        List<SensorSample> sampleList = null;
        ResultSet results = null;

        try {
            getAllCurrentValuesStmt.setString(1, tableName);
            getAllCurrentValuesStmt.setString(2, tableName);
            getAllCurrentValuesStmt.execute();
            results = getAllCurrentValuesStmt.getResultSet();
            sampleList = getSamplesFromResults(results, tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return sampleList;
    }

    private List<SensorSample> getSamplesFromResults(ResultSet results, String type) {
        List<SensorSample> sampleList = new ArrayList<SensorSample>();
        SensorSample sample;
        int sensorID;
        double value;
        LocalDateTime dateTime;

        try {
            while(results.next()) {
                sensorID = results.getInt("SensorID");
                value = results.getDouble("Value");
                dateTime = results.getTimestamp("Date").toLocalDateTime();
                sample = new SensorSample(type, sensorID, value, dateTime);
                sampleList.add(sample);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sampleList;
    }

    public double getTemperature(){
        String query =  "SELECT Value FROM TemperatureSample JOIN (" +
                "SELECT MAX(Date) as MaxDate FROM TemperatureSample AS T1" +
                ") AS T2 WHERE Date = MaxDate";

        try {
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.execute();
            ResultSet results = preparedStatement.getResultSet();
            results.next();
            temperature = results.getDouble(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return temperature;
    }

    public int getLighting(){ return lighting;}

    public int getPressure(){return pressure;}

    public int getHumidity(){return humidity;}

    public int getSound(){return sound;}

    public int getSensorID(){return sensorID;}

    public static void main(String[] args) throws SQLException {
        DBManager ctrl = new DBManager();
        ctrl.connect();
        List<SensorSample> sampleList = ctrl.getAllNewSamples();
        for(SensorSample sample : sampleList) {
            System.out.print(sample);
        }
    }
}

@@ -568,488 +568,8 @@
 n %7B%0A
-        String query;%0A%0A        query = %22SELECT DISTINCT * FROM (                   %22 +%0A                %22   SELECT MAX(Date) AS MaxDate, SensorID   %22 +%0A                %22   FROM ?                                  %22 +%0A                %22   GROUP BY SensorID                       %22 +%0A                %22) AS T1 JOIN ? AS T2                       %22 +%0A                %22ON MaxDate = Date AND T1.SensorID = T2.SensorID;%22;%0A        getAllCurrentValuesStmt = connection.prepareStatement(query);
 %0A   
@@ -1263,23 +1263,16 @@
  results
- = null
 ;%0A%0A     
@@ -1278,168 +1278,443 @@
     
+S
 tr
-y %7B%0A            getAllCurrentValuesStmt.setString(1, tableName);%0A            getAllCurrentValuesStmt.setString(2, tableName);%0A            getAllCurrentValuesS
+ing query = %22SELECT DISTINCT * FROM ( %22 +%0A                %22SELECT MAX(Date) AS MaxDate, SensorID %22 +%0A                %22FROM %22 + tableName + %22 %22 +%0A                %22GROUP BY SensorID %22 +%0A                %22) AS T1 JOIN %22 + tableName + %22 AS T2 %22 +%0A                %22ON MaxDate = Date AND T1.SensorID = T2.SensorID;%22;%0A%0A        PreparedStatement stmt = null;%0A        try %7B%0A            stmt = connection.prepareStatement(query);%0A            s
 tmt.
@@ -1746,36 +1746,17 @@
 sults = 
-getAllCurrentValuesS
+s
 tmt.getR
@@ -1831,24 +1831,50 @@
 tableName);%0A
+            stmt.close();%0A
         %7D ca

Fix bugs in DBManager.