<?php
include("pvconstants.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//DE" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="de">
  <head>
     <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
     
	 <title>
	 <?php		
        echo ("$title");
	 ?>
    </title>
	

    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
    function drawChart() {

        <?php
		
		$ist = array(0,0,0,0,0,0,0,0,0,0,0,0);
	    
		$Ausgabe = file_get_contents("$url", "r");
        $pos1 = strpos($Ausgabe,"Gesamtenergie</td>");
        $pos2 = strpos($Ausgabe,"</td>",$pos1+30);
		$gesamt = substr($Ausgabe,($pos1+70),$pos2-$pos1-70);	
		
		//preg_match('/Gesamtenergie<\/td>\s*.+\s*.*([0-9]+)<\/td>/', $Ausgabe, $treffer);
		//$gesamt = $treffer[1];
        $pos1 = strpos($Ausgabe,"Tagesenergie</td>");
        $pos2 = strpos($Ausgabe,"</td>",$pos1+20);
		$data = substr($Ausgabe,($pos1+70),$pos2-$pos1-70);
		//preg_match('/Tagesenergie<\/td>\s*.+\s.*([0-9]+\.?[0-9]*)/', $Ausgabe, $treffer);
		//$data = $treffer[1];		
		
		$data1 = (float) $data;
		$data1 = str_replace(",", ".", $data1);
		$timestamp = time();
		$first_date = date("d.m.Y",$timestamp);
		$act_date = date("d.m.Y",$timestamp);
		$dt = str_getcsv($act_date,'.');

		$pos1 = strpos($Ausgabe,"aktuell</td>");
        $pos2 = strpos($Ausgabe,"</td>",$pos1+20);
        $data_l = substr($Ausgabe,($pos1+65),$pos2-$pos1-65);
        $leistung = (int) $data_l;
		
	try {

		
		$max_days = 395;

		
		
        // Create the data table.
        // daily last n days
		echo "\tvar data = new google.visualization.DataTable();\n";
		echo "\tdata.addColumn('date', 'Datum');\n";
        echo "\tdata.addColumn('number', 'Ertrag in kWh');\n";
		
		//monthly last 12 months
		echo "\tvar datam = new google.visualization.DataTable();\n";
        echo "\tdatam.addColumn('string', 'Monat');\n";
        echo "\tdatam.addColumn('number', 'Ertrag in kWh');\n";
		echo "\tdatam.addColumn('number', 'Prognose in kWh');\n";
		
		// best/worst yield per month
		echo "\tvar datab = new google.visualization.DataTable();\n";
        echo "\tdatab.addColumn('string', 'Monat');\n";
        echo "\tdatab.addColumn('number', 'Max kWh');\n";
		echo "\tdatab.addColumn('number', 'Min kWh');\n";

		// yearly since start
        echo "\tvar datay = new google.visualization.DataTable();\n";
		echo "\tdatay.addColumn('string', 'Jahr');\n";
        echo "\tdatay.addColumn('number', 'Ertrag in kWh');\n";

		// initialize day's and month's sum with today's value
		$ist[$dt[1]-1] = $data1;
		
        $first_line = true;
		$first_line_last_year = true;
		$act_month = $dt[1];
		$act_year  = $dt[2];
        $summe = $data1;
		$summe_monat=$data1;
		$summe_vormonat=0;
		$tag=0;
		$summe_d=$data1;
		$summe_all=$data1;
		$year="";
		
		// tdata:  daily value 
		// tdatam: monthly value
		
		// initialize day's and month's sum with today's value
		// google month numbering is 0-11 !!!
		$google_month=$dt[1]-1;
		echo "\tdata.addRow([new Date($dt[2],$google_month,$dt[0]), $data1]);\n";
		$line_nr = 0;

        //rows to fetch
		$max_days = 395;
		$link = mysqli_connect('172.18.0.10', 'dbadmin', 'pcmcia1', 'pvdata');
		if ($link->connect_errno) {
			die('Keine Verbindung möglich: ' . $link['connect_error']);
		}

		$result = mysqli_query($link, "SELECT * FROM `pvdata` where origin = 'pv-iwwerrmberg' and dt_stamp > curdate()-interval $max_days day ORDER BY `dt_stamp` DESC");
		
		if (!$result){
			die('Keine keine Datensätze gefunden: ' . mysqli_error($link));
		}

        while($line = mysqli_fetch_row($result) and $line_nr<395) {
			$line_nr = $line_nr+1;
            $datum=substr($line[2], 0, 10);
            $dt = str_getcsv($datum,'-');
            $wert=$line[4];
		    $google_month=$dt[1]-1;
			
			//today's data in 1st line
			if ($first_line) {
				$summe=$summe+$wert;
				$first_line = false;
				$month=$dt[1];
				$year=$dt[0];
				$pred_year=$year-1;
			}
 				
			//average all days
			$summe_all = $summe_all+$wert;
			
			// average 30 days
			if ( $tag < 30 ) {
				$summe_d = $summe_d+$wert;
				$tag=$tag+1;
			}				
           
			// sum per month
			$ist[$google_month] = $ist[$google_month] + $wert;

            if ($year == $dt[0]) {
				// current year
				if ($month == $dt[1]) {
					$summe_monat = $summe_monat+$wert;
				}

				if ($dt[1] != $act_month) {
					// month change
					$ind = $act_month-1;
					$s = $soll[$ind]['faktor'] * $soll[$ind]['tmw'];
					echo "\tdatam.addRow(['$year/$act_month', $ist[$ind], $s]);\n";
					$act_month = $dt[1];
					$ist[$google_month] = $wert;
				}					
			} else {
			    // previous year
				// last record from current year
				if ($first_line_last_year) {
				    $act_month="01";
					$pred_month="12";
					$first_line_last_year = false;
					$ind = $act_month-1;
					$s = $soll[$ind]['faktor'] * $soll[$ind]['tmw'];
					echo "\tdatam.addRow(['$year/$act_month', $ist[$ind], $s]);\n";
					
				}					
					
					
				// sum actual month last year
				if ($month == $dt[1]) {
					$summe_vormonat = $summe_vormonat+$wert;
				} 
				
			    // sum per month last year only for month > actual month
				if (intval($dt[1]) >= intval($month)) {
					if ($dt[1] != $pred_month) {
						// month change
						
						$ind = $pred_month-1;
						$s = $soll[$ind]['faktor'] * $soll[$ind]['tmw'];
						echo "\tdatam.addRow(['$dt[0]/$pred_month', $ist[$ind], $s]);\n";
						$pred_month = $dt[1];
						$ist[$dt[1]-1] = $wert;
					}
				} else {
					//break;
				}
			}
           		  
			echo "\tdata.addRow([new Date($dt[0],$google_month,$dt[2]), $wert]);\n";
			flush();

		} //while

		$ind = $pred_month-1;
		$s = $soll[$ind]['faktor'] * $soll[$ind]['tmw'];
		echo "\tdatam.addRow(['$dt[0]/$pred_month', $ist[$ind], $s]);\n";
		
		// query for monthly max/min proceeds
        $query="select CONCAT('Januar ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=1 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=1 group by ym
   order by 1 asc) y
UNION
select CONCAT('Februar ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=2 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=2 group by ym
   order by 1 asc) y
UNION
select CONCAT('März ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=3 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=3 group by ym
   order by 1 asc) y
UNION
select CONCAT('April ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=4 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=4 group by ym
   order by 1 asc) y
UNION
select CONCAT('Mai ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=5 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=5 group by ym
   order by 1 asc) y
UNION
select CONCAT('Juni ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=6 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=6 group by ym
   order by 1 asc) y
UNION
select CONCAT('Juli ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=7 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=7 group by ym
   order by 1 asc) y
UNION
select CONCAT('August ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg'  and month(dt)=8 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=8 group by ym
   order by 1 asc) y
UNION
select CONCAT('September', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=9 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=9 group by ym
   order by 1 asc) y
UNION
select CONCAT('Oktober ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=10 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=10 group by ym
   order by 1 asc) y
UNION
select CONCAT('November ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=11 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=11 group by ym
   order by 1 asc) y
UNION
select CONCAT('Dezember ', x.y), max(x.summe) as max, y.y, min(y.summe) as min
from 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=12 group by ym
   order by 1 desc) x, 
  (select sum(kwh_now) as summe, y from pvdata where origin = 'pv-iwwerrmberg' and month(dt)=12 group by ym
   order by 1 asc) y";
		$result = mysqli_query($link, $query);
		//print_r($result);
		if (!$result){
			die('Keine keine Datensätze gefunden: ' . mysqli_error($link));
		}		
		while($line = mysqli_fetch_row($result)) {
			echo "\tdatab.addRow(['$line[0]',$line[1], $line[3]]);\n";
		}
		
		
        // query for yearly proceeds
		$result = mysqli_query($link, "select sum(kwh_now), y from pvdata where origin = 'pv-iwwerrmberg' group by y order by y desc");
		//print_r($result);
		if (!$result){
			die('Keine keine Datensätze gefunden: ' . mysqli_error($link));
		}
		while($line = mysqli_fetch_row($result)) {
			$wert=$line[0];
			$jahr=$line[1];
			echo "\tdatay.addRow(['$jahr', $wert]);\n";
		}
		mysqli_close($link);
			
	} catch ( Exception $e ) {
		file_put_contents('php://stdout', $e->getMessage());
	}
       
        echo "var options = {'title':'Täglich ($max_days Tage)',
                             'width':800,
                             'height':300,
                             hAxis: {title: 'Datum'}
                            };\n";      
        echo "var options1 = {'title':'Monatlich ($max_days Tage)',
                             'width':800,
                             'height':300,
                             hAxis: {title: 'Ertrag/Monat'}
                            };\n";   							

        echo "var options2 = {'title':'Jährlich',
                             'width':800,
                             'height':300,
                             hAxis: {title: 'Ertrag/Jahr'}
                            };\n";   	
        echo "var options3 = {'title':'Max/Min pro Monat',
                             'width':800,
                             'height':300,
                             hAxis: {title: 'Ertrag/Monat'}
                            };\n";   								
	    ?>

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
		var chart1 = new google.visualization.BarChart(document.getElementById('chart1_div'));
        chart1.draw(datam, options1);
		var chart3 = new google.visualization.BarChart(document.getElementById('chart3_div'));
        chart3.draw(datab, options3);			
		var chart2 = new google.visualization.BarChart(document.getElementById('chart2_div'));
        chart2.draw(datay, options2);	
    }
    </script>
  </head>

  <body>
    <h2>
	<div align="left">
	<?php echo "$title<br>";?>
	</div>
	</h2>
    <!--Div that will hold the chart-->
    <div id="chart_div"></div>
	<div id="chart1_div"></div>
	<div id="chart2_div"></div>
	<div id="chart3_div"></div>
	
	<?php
	$to_date   = date_create_from_format ('d.m.Y', $act_date);
	$from_date = date_create_from_format ('d.m.Y', $start);
	$dd= date_diff($from_date,$to_date);
	$ddiff=get_object_vars($dd)['days'];
	echo "<h4>Gesamtertrag $start - $act_date ($ddiff Tage): $gesamt kWh </h4>";
	echo "<h4>AC Leistung aktuell: $leistung Watt </h4>";
	echo "<h4>Ertrag $act_date: $data1 kWh </h4>";
	echo "<h4>Ertrag $month/$year: $summe_monat kWh</h4>";
	echo "<h4>Ertrag $month/$pred_year:  $summe_vormonat kWh</h4>";
	echo "<h4>Ertrag $max_days Tage:  $summe_all kWh </h4>";
	$summe_d = round($summe_d/30,2);
	echo "<h4>Durchschnitt 30 Tage:  $summe_d kWh / Tag</h4>";
	$summe_all=round($summe_all/$max_days,2);
	echo "<h4>Durchschnitt $max_days Tage:  $summe_all kWh / Tag</h4>";
	$summe_t=round($gesamt/$ddiff,2);
	echo "<h4>Durchschnitt $ddiff Tage:  $summe_t kWh / Tag</h4>";		
	?>
  </body>
</html>
