///////////////////////////////////
//
// WetterVerlauf.java
//
// Java Programm zum Auslesen von Wetterdaten
// der ELV WS 500 aus der Postgres-DB WS 500
// inkl. Erzeugen eines Diagrammes unter
// Verwendung von JFreeChart
// http://www.jfree.org/jfreechart/index.php
//
// Autor: Günter Schaden
//	  wetter@dandy.at
//	  http://wetter.dandy.at/
//
// Stand: 16.01.2006
//
// Version: 0.1
///////////////////////////////////


// Import the Java classes
import java.util.*;
import java.text.*;
import java.io.*;

// Import the Swing classes
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

// Import the JFreeChart classes
import org.jfree.chart.*;
import org.jfree.chart.axis.*;
import org.jfree.chart.plot.*;
import org.jfree.chart.title.*;
import org.jfree.chart.renderer.*;
import org.jfree.chart.renderer.xy.*;
import org.jfree.chart.renderer.xy.XYItemRenderer;
import org.jfree.data.*;
import org.jfree.data.general.*;
import org.jfree.data.time.*;
import org.jfree.ui.*;
import org.jfree.chart.labels.StandardXYToolTipGenerator;
import org.jfree.data.xy.*;

// PostgreSQL Klassen

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


//Für Java-Windowsausgabe
//public class WetterVerlauf extends ApplicationFrame {
public class WetterVerlauf {

  static Connection       db;        // die Struktur für die DB Verbindung
  static DatabaseMetaData dbmd;      // Stuktur für div. DB Systeminfos

  static int start_minute = 0;
  static int start_stunde = 0;
  static int start_tag = 0;
  static int start_monat = 0;
  static int start_jahr = 0;
  static int anzahl_datensaetze = 0;

  static String max_datum = "";
  static String max_zeit = "";
  static String min_datum = "";
  static String min_zeit = "";
  static String zeit24 = "";


    public WetterVerlauf(final String title, final String argv[]) throws ClassNotFoundException, SQLException {

    //Für Java-Windowsausgabe
    //super(title);

    // Datenbankverbindung
    String database = argv[0];
    String username = argv[1];
    String password = argv[2];
    String jpegpfad = argv[3];
    
    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");


        final JFreeChart chart = createChart();

	//Für Java-Windowsausgabe
        //final ChartPanel chartPanel = new ChartPanel(chart);
	//chartPanel.setPreferredSize(new java.awt.Dimension(1024, 768));

	// 1.0
        //chartPanel.setDomainZoomable(true);
        //chartPanel.setRangeZoomable(true);

	//Für Java-Windowsausgabe
        //chartPanel.setHorizontalZoom(true);
        //chartPanel.setVerticalZoom(true);
        //setContentPane(chartPanel);

	try {
		ChartUtilities.saveChartAsJPEG(new File(jpegpfad), chart, 1024, 768);
		} catch (Exception e) {
		System.out.println("Problem occurred creating chart.");
	}


    db.close();

    }

    /**
     * Creates the demo chart.
     * 
     * @return The chart.
     */

    private JFreeChart createChart() {

	// Startzeit holen
	GetTime24();

        //AXIS 1: Temperatur
	final XYDataset dataset1 = createDatasetWetter("Temperatur Süd (°C)", 100.0, new Minute(start_minute,start_stunde,start_tag,start_monat,start_jahr), anzahl_datensaetze);
        
        final JFreeChart chart = ChartFactory.createTimeSeriesChart(
            "Wetterverlauf", 
            "Zeit", 
            "Temperatur Süd",
            dataset1, 
            true, 
            true, 
            false
        );

	String titel = 	min_datum + " " + min_zeit + " bis " + max_datum + " " + max_zeit;

        chart.setBackgroundPaint(Color.white);
	chart.addSubtitle(new TextTitle(titel));  

        final XYPlot plot = chart.getXYPlot();
        plot.setOrientation(PlotOrientation.VERTICAL);
        plot.setBackgroundPaint(Color.white);
	plot.setDomainGridlinePaint(Color.darkGray);
	plot.setRangeGridlinePaint(Color.darkGray);

        final NumberAxis axis1 = (NumberAxis) plot.getRangeAxis();
	// font/größe
	axis1.setLabelFont(new Font("Arial", Font.PLAIN, 16)); 
        axis1.setLabelPaint(Color.red);

        final StandardXYItemRenderer renderer = (StandardXYItemRenderer) plot.getRenderer();
        renderer.setPaint(Color.red);

      
        // AXIS 2
	// Luftfeuchtigkeit
        final NumberAxis axis2 = new NumberAxis("Luftfeuchtigkeit");
        axis2.setAutoRangeIncludesZero(false);
        axis2.setLabelPaint(Color.green);
        axis2.setTickLabelPaint(Color.green);
	// font/größe setzen
	axis2.setLabelFont(new Font("Arial", Font.PLAIN, 16)); 

        plot.setRangeAxis(1, axis2);
        plot.setRangeAxisLocation(1, AxisLocation.BOTTOM_OR_LEFT);

	final XYDataset dataset2 = createDatasetWetter("Luftfeuchtigkeit (%)", 100.0, new Minute(start_minute,start_stunde,start_tag,start_monat,start_jahr), anzahl_datensaetze);
        plot.setDataset(1, dataset2);
        plot.mapDatasetToRangeAxis(1, 1);

        XYItemRenderer renderer2 = new StandardXYItemRenderer();

        renderer2.setSeriesPaint(0, Color.green);
        plot.setRenderer(1, renderer2);
        
        // AXIS 3
	// Luftdruck
        final NumberAxis axis3 = new NumberAxis("Luftdruck");
        axis3.setLabelPaint(Color.blue);
        axis3.setTickLabelPaint(Color.blue);
	// font/größe setzen
	axis3.setLabelFont(new Font("Arial", Font.PLAIN, 16)); 
	axis3.setRange(950.0, 1050.0);
  
        plot.setRangeAxis(2, axis3);

	final XYDataset dataset3 = createDatasetWetter("Luftdruck (hPa)", 100.0, new Minute(start_minute,start_stunde,start_tag,start_monat,start_jahr), anzahl_datensaetze);
        plot.setDataset(2, dataset3);
        plot.mapDatasetToRangeAxis(2, 2);
	
	XYItemRenderer renderer3 = new StandardXYItemRenderer();

        renderer3.setSeriesPaint(0, Color.blue);
        plot.setRenderer(2, renderer3);

        // AXIS 4    
	// Windgeschwindigkeit    

	Color c4 = new Color(254,206,133);

        final NumberAxis axis4 = new NumberAxis("Windgeschwindigkeit");
	axis4.setLabelPaint(c4);
	axis4.setTickLabelPaint(c4);
	// font/größe setzen
	axis4.setLabelFont(new Font("Arial", Font.PLAIN, 16)); 

        plot.setRangeAxis(3, axis4);
        
	final XYDataset dataset4 = createDatasetWetter("Windgeschwindigkeit (km/h)", 100.0, new Minute(start_minute,start_stunde,start_tag,start_monat,start_jahr), anzahl_datensaetze);
        plot.setDataset(3, dataset4);
        plot.mapDatasetToRangeAxis(3, 3);
        
	XYItemRenderer renderer4 = new StandardXYItemRenderer();

	renderer4.setSeriesPaint(0, c4);    
        plot.setRenderer(3, renderer4);

        // AXIS 5    
	// temp2

	Color c5 = new Color(22,243,216);

        final NumberAxis axis5 = new NumberAxis("Temperatur Nord");
	axis5.setLabelPaint(c5);
	axis5.setTickLabelPaint(c5);
	// font/größe setzen
	axis5.setLabelFont(new Font("Arial", Font.PLAIN, 16)); 

        plot.setRangeAxis(4, axis5);
        
	final XYDataset dataset5 = createDatasetWetter("Temperatur Nord (°C)", 100.0, new Minute(start_minute,start_stunde,start_tag,start_monat,start_jahr), anzahl_datensaetze);
        plot.setDataset(4, dataset5);
        plot.mapDatasetToRangeAxis(4, 4);
        
	XYItemRenderer renderer5 = new StandardXYItemRenderer();

	renderer5.setSeriesPaint(0, c5);    
        plot.setRenderer(4, renderer5);

                
        return chart;
    }
    
    
     /**
     * Erzeugt die datasets für die Achsen
     * 
     * @param name  the dataset name.
     * @param base  the starting value.
     * @param start  the starting period.
     * @param count  the number of values to generate.
     *
     * @return The dataset.
     */

    private XYDataset createDatasetWetter(final String name, final double base, 
                                    final RegularTimePeriod start, final int count) {

 	Statement        sql;       // die Variable für das select statement
  	ResultSet results;
  	
    //////////////////////////
    // Werte holen
    //////////////////////////
    
    String savetime = null;
    double weatherdata = 0;
    double weatherdata_alt = 0;

     final TimeSeries series = new TimeSeries(name, start.getClass());
     RegularTimePeriod period = start; 

    	
    try
    {
        sql = db.createStatement(); //create a statement that we can use later
        
	if (name.equals("Temperatur Süd (°C)")) results = sql.executeQuery(" select date_trunc('minute',savetime) as savetime,weatherdata from outside_temp where savetime > '"+zeit24+"' order by savetime");
	else if (name.equals("Temperatur Nord (°C)")) results = sql.executeQuery(" select date_trunc('minute',savetime) as savetime,weatherdata from temp2 where savetime > '"+zeit24+"' order by savetime");
	else if (name.equals("Luftfeuchtigkeit (%)")) results = sql.executeQuery("select date_trunc('minute',savetime) as savetime,weatherdata from outside_hum where savetime > '"+zeit24+"' order by savetime");
	else if (name.equals("Luftdruck (hPa)")) results = sql.executeQuery("select date_trunc('minute',savetime) as savetime,weatherdata from airpressure where savetime > '"+zeit24+"' order by savetime");
	else if (name.equals("Windgeschwindigkeit (km/h)")) results = sql.executeQuery("select date_trunc('minute',savetime) as savetime,weatherdata from windforce where savetime > '"+zeit24+"' order by savetime");
	else results = sql.executeQuery("select date_trunc('minute',savetime) as savetime,weatherdata from outside_temp where savetime > '"+zeit24+"' order by savetime");

	//System.out.println("start: "+period+" zeit24: "+zeit24+"\n");

        if (results != null)
        {
          while (results.next())
          {
		//savetime = results.getString("savetime");
		//timeseries.addOrUpdate(new Second(d), value); 
		savetime = results.getString("savetime");
          	weatherdata = results.getDouble("weatherdata");
		
		if (weatherdata == 0) weatherdata = weatherdata_alt;
		
		series.add(period, weatherdata);
		period = period.next();
		period = period.next();
		period = period.next();
		period = period.next();
		period = period.next();

		if (weatherdata != 0) weatherdata_alt = weatherdata;

		//System.out.println("Daten: "+period+" savetime: "+savetime+"\n");
          }
        }
       
        results.close();
        
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }

        final TimeSeriesCollection dataset = new TimeSeriesCollection();
        dataset.addSeries(series);

        return dataset;

    }

     /**
     * 
     * Holt den zuletzt eingetragenen Datensatz 
     * 
     */


  public static void GetTime24()
  {

 	Statement        sql;       // die Variable für das select statement
  	ResultSet results;

	int oid = 0;
	String zeit = "";
  	
    //////////////////////////
    // min/max Datum holen
    //////////////////////////
    
    	
    try
    {
        sql = db.createStatement(); //create a statement that we can use later
        
	results = sql.executeQuery("select extract (minute from (max(savetime)- interval '24 hour')) as minute,extract (hour from (max(savetime)- interval '24 hour')) as stunde,extract (day from (max(savetime)- interval '24 hour')) as tag,extract (month from (max(savetime)- interval '24 hour')) as monat,extract (year from (max(savetime)- interval '24 hour')) as jahr,to_char(max(savetime), 'DD.MM.YYYY') as datum_max,to_char(max(savetime), 'HH24:MI') as minute_max,to_char(max(savetime)- interval '24 hour', 'DD.MM.YYYY') as datum_min,to_char(max(savetime)- interval '24 hour', 'HH24:MI') as minute_min from outside_temp");

        if (results != null)
        {
          while (results.next())
          {
		start_minute = results.getInt("minute");
		start_stunde = results.getInt("stunde");
		start_tag = results.getInt("tag");
		start_monat = results.getInt("monat");
		start_jahr = results.getInt("jahr");

		max_datum = results.getString("datum_max");
		max_zeit = results.getString("minute_max");
		min_datum = results.getString("datum_min");
		min_zeit = results.getString("minute_min");
          }
        }
       
        results.close();
        
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }

    //////////////////////////
    // Anzahl Datensätze holen
    //////////////////////////

    try
    {
        sql = db.createStatement(); //create a statement that we can use later
        
	results = sql.executeQuery("select max(savetime)- interval '24 hour' as zeit from outside_temp");

        if (results != null)
        {
          while (results.next())
          {
		zeit = results.getString("zeit");
          }

	  zeit24 = zeit;
        }
       
        results.close();
        
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }



    try
    {
        sql = db.createStatement(); //create a statement that we can use later
        
	results = sql.executeQuery("select count(*) as anzahl from outside_temp where savetime >= '"+zeit+"'");

        if (results != null)
        {
          while (results.next())
          {
		anzahl_datensaetze = results.getInt("anzahl");
          }
        }
       
        results.close();
        
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }

	 System.out.println("\nAnzahl: "+anzahl_datensaetze+"\n");
   
  }


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

    /**
     * Starting point for the demonstration application.
     *
     * @param args  ignored.
     */
  public static void main (String args[])
  {

	if (args.length != 4) correctUsage();
	try {
        final WetterVerlauf demo = new WetterVerlauf("Wetterverlauf",args);

	//Für Java-Windowsausgabe
        //demo.pack();
        //RefineryUtilities.centerFrameOnScreen(demo);
        //demo.setVisible(true);
	}
	catch (Exception ex)
	    {
	      System.out.println("***Exception:\n"+ex);
	      ex.printStackTrace();
	    }


    }

}