#!/usr/bin/python
#--------------------------------------
# Read data from weer and verbruik database on pi_web for current day and week
# and store that data in a file that is read by gnuplot.
# 12-07-2021 : Added line for current hour averaging record for last hour
# 22-07-2021 : Line for current hour is most recent line in weer_hist_pi
# 06-08-2021 : Changed program and file names
# 20-12-2021 : Added data for verbruik to this file
# 22-12-2022 : kWh added to verwarming because of warmtepomp
# 28-09-2024 : Zon added to weerdag

from __future__ import print_function
import MySQLdb
import time
import sys

Version="12-12-2022"
Program="PI_HaalPlotData.py"
FilenameWeerDag = "weerdag.txt";
FilenameWeerWeek = "weerweek.txt";
FilenameVerbruikDag = "verbruikdag.txt";
FilenameVerbruikWeek = "verbruikweek.txt";
FilenameVerwarmingDag = "verwarmingdag.txt";

# Global variables
DoPrint=0                       # Print Debug information
DoSleep=1                       # Wait for database to start up
DoIn=1                          # Measure inTemp (and sensortype)
DoOut=1                         # Measure OutTemp (and sensortype)
Server="localhost"              # Server for database

#
# print to console or error device
#
def eprint(*args, **kwargs):
    print(*args, file=sys.stderr, **kwargs)

#
# Process parameters
#
def Do_Parameters():
  global DoPrint
  global DoSleep
  global DoIn
  global DoOut
  global Server
  i=0
  toonhelp=0
  c=' ' 

  # Check command line for next argument
  i=1
  while i<len(sys.argv):
    parameter=sys.argv[i] # next possible switch
    j=0; c=' '
    if len(parameter)>0:
      c=parameter[0]
      j=1
      if (c=='-'): # skip possible '-' before switch
        if len(parameter) >1:
          c=parameter[1] # first char is switch command
          j=2
      tekst = parameter[j:] # remainder is switch value
      try:
        waarde = int(tekst) # check whether value is numeric
      except:
        waarde=0  
      if DoPrint>1: print(sys.argv[i],"=[",c,",tekst=[",tekst,"(",len(tekst),")], waarde=[",waarde,"]") # show results

      # Process switch if command is recognised
      if ((c=='d') or (c=='D')):        
        if waarde>=0 and waarde<=3: 
          DoPrint=waarde
          if (len(tekst)==0): DoPrint=1; 
          print("DoPrint=",DoPrint)
      elif  ((c=='q') or (c=='Q')):
        if ((waarde>=0) and (waarde<=1)): 
          DoSleep=1-waarde; 
          if (len(tekst)==0): DoSleep=0; 
          print("DoSleep=",DoSleep)
      elif  ((c=='i') or (c=='I')):
        if waarde>=0 and waarde<=3:
          DoIn=waarde
          if (len(tekst)==0): DoIn=1
          print("DoIn=",DoIn)
      elif ((c=='o') or (c=='O')):
        if waarde>=0 and waarde<=3:
          DoOut=waarde
          if (len(tekst)==0): DoOut=1
          print("DoOut=",DoOut)
      elif ((c=='s') or (c=='S')):
          if (len(tekst)>0): Server=tekst
          print("Server=",Server)
      else:  
        if (DoPrint>0): print("Unknown argument ",sys.argv[i],"=[",c,",[",tekst,"],",waarde,"]") # unrecognised switch, show help info
        toonhelp=1
    i+=1
   
  if (toonhelp==1):
     print(Program," -d(Debug) -q(Quickstart) -i(Intemp) -o(Outtemp) -s(Server) -h(Help)")
# End Do_Parameters

def log_file(filename,regel):
  file = open(filename,"a")
  if (DoPrint > 1) : print ("Writing " ,regel)
  file.write(regel) 
  file.write('\n') 
  file.close() 

def create_file(filename):
  file = open(filename, "w")
  file.close()

def db_connect() :
  return MySQLdb.connect(Server,"weer","Weer1","weer" )

def log_fout_pi(fout, var1, var2) :
  sql="INSERT INTO fout_pi(datum,program,fout,var1,var2) VALUES('%s','%s','%s','%s','%s')" % (timestr,Program,fout,var1,var2);
  # Open database connection, do the insert, and close the connection
  try:
    db = db_connect()
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    db.close()
  except:
    eprint(timestr,'\t', Program, '\tVersie  ',Version, '\tError writing to database\t',sql)
    

def mysql_readWeerDag() :
  try:
    sql= """select * from (SELECT DATE_FORMAT(Hour, "%H") as Uur,AvgInTemp,AvgOutTemp,10*SumRain as SumRain, AvgRelPressure,(maxzon/1000) AS MaxZon, Hour from weer.weer_uur_pi ORDER BY Hour desc LIMIT 24) t order by Hour"""
    db = db_connect()
    cursor = db.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()

    if cursor.rowcount>0 :
      for row in rows:
        regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3])+ " " + str(row[4])+ " " + str(row[5])+ " " + str(row[6])
        log_file(FilenameWeerDag,regel)
        if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])+ " " + str(row[6]))
    else :
        eprint("Geen data gelezen van weer_uur_pi")

    sql= """select DATE_FORMAT(Time, "%H") as Uur,Intemp, OutTemp, Rain, RelPressure,(zon/1000) AS Zon,time from weer.weer_hist_pi order by time desc limit 1"""
    cursor.execute(sql)
    row = cursor.fetchone()
    if cursor.rowcount>0 :
      regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3])+ " " + str(row[4])+ " " + str(row[5])+ " " + str(row[6])
      log_file(FilenameWeerDag,regel)
      if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])+ " " + str(row[6]))
    else :
        eprint("Geen data gelezen van weer_hist_pi")
 

  except:
    eprint(timestr,"\t", Program ,"\tError reading from database\t",sql) 

def mysql_readWeerWeek() :
  try:
    sql= """
           select * from (
                select DATE_FORMAT(Hour, '%d') as Dag,min(MinInTemp) as MinInTemp,max(MaxInTemp) as MaxInTemp, min(MinOutTemp) as MinOutTemp,
                max(MaxOutTemp) as MaxOutTemp,sum(SumRain) as SumRain, min(AvgRelPressure) as AvgRelPressure, Round((Avg(MaxZon))/500,2) as Zon,
                DATE_FORMAT(Hour, '%y-%m-%d') as Datum
                from weer.weer_uur_pi group by Datum order by Datum desc limit 8
            ) t order by Datum
         """
    db = db_connect()
    cursor = db.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()

    if cursor.rowcount>0 :
      for row in rows:
        regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3])+ " " + str(row[4])+ " " + str(row[5])+ " " + str(row[6])+ " " + str(row[7])+ " " + str(row[8])
        log_file(FilenameWeerWeek,regel)
        if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])," ", str(row[6])," ", str(row[7])," ", str(row[8]))
    else :
        eprint("Geen data gelezen van weer_uur_pi")

  except:
    eprint(timestr,"\t", Program ,"\tError reading from database\t",sql) 

def mysql_readVerbruikWeek() :
  try:
    sql= """
            select * from (
                select DATE_FORMAT(Hour, '%d') as Dag, round(sum(dVL + dVH + dZon -(dPL + dPH)),3) as El, round(sum(dVL + dVH),3) as Verb,
                round(sum(dPL + dPH),3) as Prod, round(sum(dZon),3) as Zon, round(sum(dGA),3) as Gas, DATE_FORMAT(Hour, '%y-%m-%d') as Datum
                from verbruik.verbruik_uur_pi group by Datum order by Datum desc limit 8
            ) t order by Datum
         """
    db = db_connect()
    cursor = db.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()

    if cursor.rowcount>0 :
      for row in rows:
        regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3])+ " " + str(row[4])+ " " + str(row[5])+ " " + str(row[6])
        log_file(FilenameVerbruikWeek,regel)
        if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])," ", str(row[6]))
    else :
        eprint("Geen data gelezen van verbruik_uur_pi")

  except:
    eprint(timestr,"\t", Program ,"\tError reading from database\t",sql) 

def mysql_readVerbruikDag() :
  try:
    sql= """
          select DATE_FORMAT(Hour, '%H') as Uur, round((dVL + dVH + dZon -(dPL + dPH)),3) as El, round((dVL + dVH),3) as Verb,
			 round((dPL + dPH),3) as Prod, round(dZon,3) as Zon, round(dGA,3) as Gas, IFNull(dBat,0) as Bat, Hour 
			 from verbruik.verbruik_uur_pi order by hour limit 24
         """
    db = db_connect()
    cursor = db.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()

    if cursor.rowcount>0 :
      for row in rows:
        regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3])+ " " + str(row[4])+ " " + str(row[5])+ " " + str(row[6])
        log_file(FilenameVerbruikDag,regel)
        if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])," ", str(row[6]))
    else :
        eprint("Geen data gelezen van verbruik_uur_pi")

    sql= """select DATE_FORMAT(Datum, "%H") as Uur, round((dVL + dVH + dZon -(dPL + dPH)),3) as El, round((dVL + dVH),3) as Verb, round((dPL + dPH),3) as Prod, round(dZon,3) as Zon, round(dGA,3) as Gas,IFNull(dBat,0) as Bat, Datum from verbruik.verbruik_hist_pi order by Datum desc limit 1"""
    cursor.execute(sql)
    row = cursor.fetchone()
    if cursor.rowcount>0 :
      regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3])+ " " + str(row[4])+ " " + str(row[5])+ " " + str(row[6])
      log_file(FilenameVerbruikDag,regel)
      if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])," ", str(row[6]))
    else :
        eprint("Geen data gelezen van verbruik_hist_pi")
 
  except:
    eprint(timestr,"\t", Program ,"\tError reading from database\t",sql) 

def mysql_readVerwarmingDag() :
  try:
    sql= """
          select * from (SELECT DATE_FORMAT(w.Hour, "%H") as Uur,w.AvgInTemp as InTemp,w.AvgOutTemp as OutTemp,w.AvgT6 as T6, 10*round(dGA,3) as Gas, 
                         if((dVH+dVL-dPH-dPL>0), 10*Round(dVH+dVL-dPH-dPL,2),0) as kWh, w.Hour 
                         from weer.weer_uur_pi w  left join verbruik.verbruik_uur_pi v on w.Hour=v.Hour ORDER BY w.Hour desc LIMIT 24
                        ) t order by Hour
         """
    db = db_connect()
    cursor = db.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()

    if cursor.rowcount>0 :
      for row in rows:
        regel=str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3]) + " " + str(row[4])+ " " + str(row[5]) + " " + str(row[6])
        log_file(FilenameVerwarmingDag,regel)
        if (DoPrint > 0) : print(str(row[0])," ", str(row[1])," ", str(row[2])," ", str(row[3])," ", str(row[4])," ", str(row[5])," ", str(row[6]))
    else :
        eprint("Geen data gelezen van weer_uur_pi join verbruik_uur_pi")

  except:
    eprint(timestr,"\t", Program ,"\tError reading from database\t",sql) 


#------------------------------------------------------------------------------
# Start program
#------------------------------------------------------------------------------

# Time of program start
lt = time.localtime(time.time())
timestr='%4i-%02i-%02i %02i:%02i:%02i' % (lt.tm_year,lt.tm_mon,lt.tm_mday,lt.tm_hour,lt.tm_min,lt.tm_sec)

# Print start of program to console or logfile
eprint(timestr,'\t',Program,'\tVersie  ',Version)

# Process command line arguments
Do_Parameters()

if (DoPrint > 0) : log_fout_pi("Started",Version,timestr)

DBOpen=0
# Open database connection
try:
  db = db_connect()
  cursor = db.cursor()
  DBOpen=1
  if DoPrint>0: print (timestr, '\t', "Opening Database")
except:
  eprint (timestr, '\t', "Error Opening Database on " , Server)
  
if (DBOpen==1) :
  create_file(FilenameWeerDag)
  mysql_readWeerDag()
  create_file(FilenameWeerWeek)
  mysql_readWeerWeek()

  create_file(FilenameVerbruikDag)
  mysql_readVerbruikDag()
  create_file(FilenameVerbruikWeek)
  mysql_readVerbruikWeek()

  create_file(FilenameVerwarmingDag)
  mysql_readVerwarmingDag()

  db.close()

#Einde programma
if DoPrint>0:
   eprint ('Program ended')
