///////////////////////////////////
//
// wetter.java
//
// Java Programm zum Auslesen von Wetterdaten
// der ELV WS 500 aus der Postgres-DB WS 500
// inkl. Erzeugen der Datei awekas.txt
// für http://www.awekas.at7
//
// Autor: Günter Schaden
//	  wetter@dandy.at
//	  http://wetter.dandy.at/
//
// Stand: 12.01.2006
//
// Version: 0.1
///////////////////////////////////


package at.dandy;

import java.sql.*;   // All we need for JDBC
import java.math.*;
import java.io.*;

public class wetter
{
  Connection       db;        // die Struktur für die DB Verbindung
  DatabaseMetaData dbmd;      // Stuktur für div. DB Systeminfos
  static int max_oid;		  // OID von max(oid)
  static String zeit;
  static String datum;
  static double wert;
  static double airpressure_diff;
  static String query;

  public wetter(String argv[])
    throws ClassNotFoundException, SQLException
  {
    String database = argv[0];
    String username = argv[1];
    String password = argv[2];
    String awekas_pfad = argv[3];


    //////////////////////////
    // DB Verbindung
    //////////////////////////
    
    Class.forName("org.postgresql.Driver"); //load the driver
    db = DriverManager.getConnection("jdbc:postgresql:"+database,
                                     username,
                                     password); //connect to the db
    dbmd = db.getMetaData(); //get MetaData to confirm connection
    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
                       dbmd.getDatabaseProductVersion()+" successful.\n");

    //////////////////////////
    // Luftdruck
    //////////////////////////
    int oid_airpressure = 0;		  // OID von max(oid)
    query = "select max(oid) as oid from airpressure";
    GetMaxOID(db);
    oid_airpressure = max_oid;
    
    System.out.println("OID  "+oid_airpressure+"\n");
    
    String luft_zeit = "";
    String luft_datum = "";
    double luft_wert = 0;
    query = "select to_char(savetime, 'DD.MM.YYYY') as datum, to_char(savetime, 'HH24:MI') as zeit, round(cast (weatherdata as numeric),2) as wert from airpressure where oid = "+oid_airpressure;
    GetData(db);
    luft_zeit = zeit;
    luft_wert = wert;
    luft_datum = datum;
    System.out.println("LUFTDRUCK datum: "+luft_datum+", zeit: "+luft_zeit+", wert:"+luft_wert+"\n");
    
    //////////////////////////
    // Luftdruck Differenz der letzten 6 Stunden holen
    //////////////////////////

    GetHumDiff(db);

    // Differenz Wert auf 2 Stellen runden
    int decimalPlace = 2;
    double difference = 0;
    difference = (airpressure_diff-luft_wert);
    BigDecimal bd = new BigDecimal(difference);
    bd = bd.setScale(decimalPlace,BigDecimal.ROUND_UP);
    difference = bd.doubleValue();
    
    System.out.println("LUFTDRUCK DIFFERENZ Wert:"+difference+"\n");
    
    //////////////////////////
    // Temperatur aussen
    //////////////////////////
    int oid_temp_extern=0;		  // OID von max(oid)
    query = "select max(oid) as oid from outside_temp";
    GetMaxOID(db);
    oid_temp_extern = max_oid;
    
    String temp_extern_zeit = "";
    String temp_extern_datum = "";
    double temp_extern_wert=0;
    query = "select to_char(savetime, 'DD.MM.YYYY') as datum, to_char(savetime, 'HH24:MI') as zeit, weatherdata as wert from outside_temp where oid = "+oid_temp_extern;
    GetData(db);
    temp_extern_zeit = zeit;
    temp_extern_wert = wert;
    temp_extern_datum = datum;
    System.out.println("TEMP EXTERN datum: "+temp_extern_datum+", zeit: "+temp_extern_zeit+", wert:"+temp_extern_wert+"\n");

    //////////////////////////
    // Feuchtigkeit aussen
    //////////////////////////
    int oid_hum_extern=0;		  // OID von max(oid)
    query = "select max(oid) as oid from outside_hum";
    GetMaxOID(db);
    oid_hum_extern = max_oid;
    
    String hum_extern_zeit = "";
    String hum_extern_datum = "";
    double hum_extern_wert=0;
    query = "select to_char(savetime, 'DD.MM.YYYY') as datum, to_char(savetime, 'HH24:MI') as zeit, weatherdata as wert from outside_hum where oid = "+oid_hum_extern;
    GetData(db);
    hum_extern_zeit = zeit;
    hum_extern_wert = wert;
    hum_extern_datum = datum;
    System.out.println("HUM EXTERN datum: "+hum_extern_datum+", zeit: "+hum_extern_zeit+", wert:"+hum_extern_wert+"\n");
    
    //////////////////////////
    // Regen
    // noch todo, Datenstruktur nicht ganz klar
    //////////////////////////
    int oid_rain=0;		  // OID von max(oid)
    query = "select max(oid) as oid from rainfall";
    GetMaxOID(db);
    oid_airpressure = max_oid;
    
    String rain_zeit = "";
    String rain_datum = "";
    double rain_wert=0;
    //query = "select to_char(savetime, 'DD.MM.YYYY') as datum, to_char(savetime, 'HH24:MI') as zeit, weatherdata as wert from rain where oid = "+oid_airpressure;
    //GetData(db);
    //rain_zeit = zeit;
    //rain_wert = wert;
    //rain_datum = datum;
    System.out.println("REGEN datum: "+rain_datum+", zeit: "+rain_zeit+", wert:"+rain_wert+"\n");


    //////////////////////////
    // Windgeschwindigkeit
    //////////////////////////
    int oid_windforce=0;		  // OID von max(oid)
    query = "select max(oid) as oid from windforce";
    GetMaxOID(db);
    oid_windforce = max_oid;
    
    String windforce_zeit = "";
    String windforce_datum = "";
    double windforce_wert=0;
    query = "select to_char(savetime, 'DD.MM.YYYY') as datum, to_char(savetime, 'HH24:MI') as zeit, weatherdata as wert from windforce where oid = "+oid_windforce;
    GetData(db);
    windforce_zeit = zeit;
    windforce_wert = wert;
    windforce_datum = datum;
    System.out.println("WINDFORCE datum: "+windforce_datum+", zeit: "+windforce_zeit+", wert:"+windforce_wert+"\n");
    
    //////////////////////////
    // Windrichtung
    //////////////////////////
    int oid_winddirection=0;		  // OID von max(oid)
    query = "select max(oid) as oid from winddirection";
    GetMaxOID(db);
    oid_winddirection = max_oid;
    
    String winddirection_zeit = "";
    String winddirection_datum = "";
    double winddirection_wert=0;
    query = "select to_char(savetime, 'DD.MM.YYYY') as datum, to_char(savetime, 'HH24:MI') as zeit, cast (degree as int) as wert from winddirection where oid = "+oid_winddirection;
    GetData(db);
    winddirection_zeit = zeit;
    winddirection_wert = wert;
    winddirection_datum = datum;
    System.out.println("WINDDIRECTION datum: "+winddirection_datum+", zeit: "+winddirection_zeit+", wert:"+winddirection_wert+"\n");

    String temp = "";

    //////////////////////////
    // awekas.txt schreiben
    /////////////////////////
    try {
    	//C:\temp\ftp\wetter
        BufferedWriter out = new BufferedWriter(new FileWriter(awekas_pfad+"awekas.txt"));
        
        // Leerzeile
        out.write("\r\n");
        
        // Aussentemperatur
        temp = Double.toString(temp_extern_wert);
        out.write(temp.replace('.',','));
        out.write("\r\n");
        // Aussenfeuchtigkeit
        temp = Double.toString(hum_extern_wert); 
        out.write(temp.replace('.',','));
        out.write("\r\n");
        // Luftdruck
        temp = Double.toString(luft_wert); 
        out.write(temp.replace('.',','));
        out.write("\r\n");
        // Regen
        //temp = Double.toString(luft_wert); 
        out.write("0");
        out.write("\r\n");
        // Windgeschwindigkeit
        temp = Double.toString(windforce_wert); 
        out.write(temp.replace('.',','));
        out.write("\r\n");
        // Windrichtung
        temp = String.valueOf((int)winddirection_wert); 
        out.write(temp.replace('.',','));
        out.write("\r\n");
        // Zeit
        out.write(luft_zeit);
        out.write("\r\n");
        // Datum
        out.write(luft_datum);
        out.write("\r\n");
        // Luftdruck Differenz 6 Stunden
        temp = Double.toString(difference); 
        out.write(temp.replace('.',','));
        out.write("\r\n");

        out.close();
        
        System.out.println("awekas.txt: OK\n");
        
    } catch (IOException ex) {
    	System.out.println("***IO Exception:\n"+ex);
        ex.printStackTrace();
    }

    db.close();
  }

  public static void correctUsage()
  {
    System.out.println("\nIncorrect number of arguments.\nUsage:\n "+
                       "java   \n");
    System.exit(1);
  }

  // allgemeine Funktion zum holen der Max(oid) 
  public static void GetMaxOID (Connection db)
  {
  	Statement        sql;       // die Variable für das select statement
  	
    try
    {
        sql = db.createStatement(); //create a statement that we can use later

        // 1. Schritt: Luftdruck holen
        ResultSet results = sql.executeQuery(query);
        if (results != null)
        {
          while (results.next())
          {
          	max_oid = results.getInt("oid");
          }
        }
       
        results.close();

    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
  
  // allgemeine Funktion zum Holen der Daten aufgrund der OID
  public static void GetData (Connection db)
  {
  	Statement        sql;       // die Variable für das select statement
  	
    try
    {
        sql = db.createStatement(); //create a statement that we can use later

        ResultSet results = sql.executeQuery(query);
        if (results != null)
        {
          while (results.next())
          {
          	datum = results.getString("datum");
          	zeit = results.getString("zeit");
          	wert = results.getDouble("wert");
          }
        }
       
        results.close();

    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
 
  // allgemeine Funktion zum Holen der Daten aufgrund der OID
  public static void GetHumDiff (Connection db)
  {
  	Statement        sql;       // die Variable für das select statement
  	ResultSet results;
  	
    //////////////////////////
    // Luftdruck Differenz 6 Stunden
    //////////////////////////
    
    int oid_airpressure_diff = 0;		  // OID von max(oid)
    String luft_diff_zeit = "";
    String luft_diff_datum = "";
    double luft_diff_wert = 0;
      	
    try
    {
        sql = db.createStatement(); //create a statement that we can use later
        
        //System.out.println("max_oid: "+max_oid+"\n");

        results = sql.executeQuery("select savetime - interval '6 hour' as zeit from airpressure where oid = "+max_oid);
        if (results != null)
        {
          while (results.next())
          {
          	luft_diff_zeit = results.getString("zeit");
          }
        }
       
        results.close();
        
        //System.out.println("datum: "+luft_diff_zeit+"\n");
        
        results = sql.executeQuery("select round(cast (avg(weatherdata) as numeric),2) as wert from airpressure where savetime > '"+luft_diff_zeit+"'");
        if (results != null)
        {
          while (results.next())
          {
          	luft_diff_wert = results.getDouble("wert");
          }
        }
       
        results.close();
        
        System.out.println("wert 6 h: "+luft_diff_wert+"\n");
        
        airpressure_diff = luft_diff_wert;
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }

  
  public static void main (String args[])
  {
    if (args.length != 4) correctUsage();
    try
    {
      wetter demo = new wetter(args);
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
  
}