Optimierung der Sensorwerteverwaltung

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite		#Für die Datenbankverbindung
import datetime				#Um Datum und Uhrzeit abzufragen
import urllib				#Um die URL aufzurufen
import httplib				#Um die URL aufzurufen
import sys					#Um Kommandozeilenargumente zu lesen

#Um die IP-Adresse zu ermitteln
import socket
import fcntl
import struct

now = datetime.datetime.now()

con = lite.connect('/var/www/html/database/data.sqlite')

#Nutzerdaten, da die API im weiteren Verlauf der Tutorials noch geschützt wird
username = 'client'
password = 'clientpassword'

#IP-Adresse des Servers feststellen und zurückgeben
#Parameter: ifname - 'wlan0' falls per WLAN verbunden und 'eth0' falls per LAN verbunden
def get_ip_address(ifname):
	s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
	return socket.inet_ntoa(fcntl.ioctl(
	s.fileno(),
	0x8915,
	struct.pack('256s', ifname[:15])
	)[20:24])
		
#Sensorwerte abfragen und Wert in Datenbank schreiben
#Parameter:
	#room: Raum, in dem der Wert gesucht werden soll
	#sensor: Art des zu suchenden Sensors
def saveSensorData(room, type, post_id, ip):
	#entsprechenden Sensorwert abfragen
	
	params = urllib.urlencode({'action': 'getsensordata', 'room': room, 'type': type, 'id': post_id, 'username': username, 'password': password})
	headers = {"Content-type": "application/x-www-form-urlencoded", "Accept": "text/plain"}
	conn = httplib.HTTPConnection(ip)
	conn.request("POST", "/api.php", params, headers)
	response = conn.getresponse()
	sensorwert = response.read()
	conn.close
	
	#Datum und Uhrzeit ermitteln
	uhrzeit = now.strftime("%Y-%m-%d %H:%M") #Datum und Uhrzeit im Format JJJJ-MM-TT HH:MM
	
	#Daten in Datenbank schreiben
	cur = con.cursor()
	cur.execute("INSERT INTO SENSOR_DATA (DEVICE_ID, DEVICE_TYPE, DATETIME, VALUE) VALUES ('"+id+"', '"+type+"', '"+uhrzeit+"', '"+sensorwert+"')")
	con.commit()

#Hier wurde die Schleife angepasst
with con:
	cur = con.cursor()
	cur.execute("SELECT * FROM ZWAVE_SENSOREN")
	
	for data in cur.fetchall():
		if(data[4] == "true"): #data[4] legt fest, ob der Sensorwerteverlauf dieses Sensors gepseichert werden soll
			saveSensorData(data[0], "Z-Wave Sensor", data[2], get_ip_address(sys.argv[1])) #data[0] enthält den KEY des Raumes und data[2] die ID des Sensors

<?php

function getSensorData($room, $type, $id, $show_einheit, $db){
	$server_ip = $_SERVER['SERVER_ADDR'];
	
	if($room === "") return "error";
	
	if($type !== "" && $id !== ""){
		switch($type){
			case "Z-Wave Sensor":
				if(!hasPermission($room, $db)){
					return "nopermission";
				}
				
				if($id !== null){
					//Z-Wave API aufrufen
					$link = "http://".$server_ip."/ZAutomation/api/v1/devices/".$id;
					
					//curl mit URL initialisieren
					$curl = curl_init($link);
					
					//Port setzen
					curl_setopt($curl, CURLOPT_PORT, 8083);
					
					//Ausgabe einstellen
					curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
					
					//Abfrage ausführen
					$result = curl_exec($curl);
					
					//Wert je nach Wunsch mit/ohne Einheit ausgeben
					$array = json_decode($result, true);
					
					$return = $array['data']['metrics']['level'];
					
					if($show_einheit == "1"){
						$return = $return." ".$array['data']['metrics']['scaleTitle'];
					}
					
					return $return;
				}
				else return "N/A";
			//Case für weitere Sensorarten
			//...
		}
	}
	else{
		//Räume laden
		if($room == "all"){
			$results = $db->prepare("SELECT * FROM 'ROOMS'");
			$results->execute();
		}
		else{
			$results = $db->prepare("SELECT * FROM 'ROOMS' WHERE LOCATION == :room");
			$results->execute(array('room' => $room));
		}
		
		//ausgabearray erzeugen
		$values = array();
		
		//Alle Räume durchlaufen
		foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
			//Räume überspringen, zu denen der Nutzer keine Berechtigung hat
			if(!hasPermission($room, $db)){
				continue;
			}
		
			//Wertearray erzeugen
			$value_array = array();
			
			//Alle Z-Wave Sensoren im aktuellen Raum laden
			$type = "Z-Wave Sensor";
			$ergebnisse = $db->prepare("SELECT * FROM 'ZWAVE_SENSOREN' WHERE RAUM == :location");
			$ergebnisse->execute(array('location' => $row['LOCATION']));
			
			//Alle Z-Wave Sensoren im Raum durchlaufen
			foreach($ergebnisse->fetchAll(PDO::FETCH_ASSOC) as $reihe){
				//Wert für jeden Sensor zusammen mit Sensorart in Wertearray schreiben
				$value = array('shortform'=> $reihe['SHORTFORM'], 'id' => $reihe['ID'], 'type' => $type, "icon" => $reihe['ICON'],
				'wert' => getSensorData($row['LOCATION'], $type, $reihe['ID'], 1, $db));
				array_push($value_array, $value);
			}
			
			//Abfrage für weitere Sensorarten
			//...
			
			//Daten für aktuellen Raum in Ausgabearray schreiben
			$value_item = array('name' => $row['NAME'], 'location' => $row['LOCATION'], 'value_array' => $value_array);
			array_push($values, $value_item);
		}
		
		//JSON-Objekt zurückgeben
		return json_encode(array('values' => $values));
	}
}

?>

<?php
 
function getRoomData($room, $db){
	
	if(!hasPermission($room, $db)){
		return "nopermission";
	}
	
    $roomdata = array();
     
    $switchdata = json_decode(getModes($room, "", $db), true);
    foreach($switchdata['modi'] as $switch){
        if($switch['mode'] === "1"){
            $mode = true;
        }
        else{
            $mode = false;
        }
        array_push($roomdata, array('name' => $switch['name'], 'device' => $switch['device'], 'icon' => $switch['icon'], 'type' => "switch", 'value' => ($mode) ? 'true' : 'false'));
    }
     
    $sensordata = json_decode(getSensorData($room, "", "", "", $db), true);
    foreach($sensordata['values'][0]['value_array'] as $sensor){
        array_push($roomdata, array('name' => $sensor['shortform'], 'device' => $sensor['id'], 'type' => $sensor['type'],
		'icon' => $sensor['icon'], 'type' => "value", 'device_type' => $sensor['type'], 'value' => $sensor['wert']));
    }
     
    $scenedata = json_decode(getScenes($room, $db), true);
    if(sizeOf($scenedata['scenes']) > 0){
        array_push($roomdata, array('name' => "Szenen", 'device' => "scenes", 'icon' => "scenes", 'type' => "scenes", 'value' => ""));
    }
     
    //Heizungs-Item implementieren
     
    return json_encode(array('roomdata' => $roomdata));
}
 
?>

<?php
 
function getGraphData($type, $id, $von, $bis, $db){
     
    if(!hasPermission($type, $db)){
        return "nopermission";
    }
     
    if($von == $bis){
        //Datum formattieren
        $datum = date("Y-m-d", strtotime($von));
         
        return getDayData($type, $id, $datum, $db);
    }
    else{
        return getDayMinMax($type, $id, $von, $bis, $db);
    }
}
 
function getDayMinMax($type, $id, $start, $ende, $db){   
    $start = date("Y-m-d", strtotime($start));
	
	$ende = date("Y-m-d", strtotime($ende));
     
    $values = array();
     
    $query = $db->query("SELECT MIN(VALUE) as MIN, MAX(VALUE) as MAX, strftime(\"%d.%m.%Y\", DATETIME) as DATE FROM SENSOR_DATA WHERE DEVICE_TYPE == :type
	AND DEVICE_ID == :id AND DATETIME >= :start AND DATETIME <= :ende GROUP BY DATE ORDER BY DATETIME ASC");
	$query->execute(array('type' => $type, 'id' => $id, 'start' => "$start 00:00", 'ende' => "$ende 23:59"));
	
	print_r(array('type' => $type, 'id' => $id, 'start' => "$start 00:00", 'ende' => "$ende 23:59"));
	
	foreach($query->fetchAll(PDO::FETCH_ASSOC) as $row){
		array_push($values, array('date' => $row['DATE'], 'min' => $row['MIN'], 'max' => $row['MAX']));
	}
	
    return json_encode(array('values' => $values, 'einheit' => getEinheit($type, $id, $db)));
}

function getEinheit($type, $id, $db){
	switch($type){
		case "Z-Wave Sensor":
			$query = $db->query("SELECT * FROM 'ZWAVE_SENSOREN' WHERE ID == :id");
			$query->execute(array('id' => $id));
			break;
	}
     
    if($result = $query->fetch(PDO::FETCH_ASSOC)){
        return $result['EINHEIT'];
    }
	else{
		return "";
	}
}

function getDayData($type, $id, $datum, $db){    
    $values = array();
     
    $query = $db->query("SELECT * FROM 'SENSOR_DATA' WHERE DEVICE_TYPE == :type AND DEVICE_ID == :id AND DATETIME >= :start AND DATETIME < :ende ORDER BY DATETIME ASC");
    $query->execute(array('type' => $type, 'id' => $id, 'start' => $datum." 00:00", 'ende'=> $datum." 23:59"));
     
    foreach($query->fetchAll(PDO::FETCH_ASSOC) as $row){
        $value_item = array('value' => floatval($row['VALUE']), 'time' => str_replace($datum." ", "", $row['DATETIME']));
        array_push($values, $value_item);
    }
    
	switch($type){
		case "Z-Wave Sensor":
			$query = $db->query("SELECT * FROM 'ZWAVE_SENSOREN' WHERE ID == :id");
			$query->execute(array('id' => $id));
			break;
	}
     
    if($result = $query->fetch(PDO::FETCH_ASSOC)){
        $einheit = $result['EINHEIT'];
    }
     
    return json_encode(array('values' => $values, 'einheit' => getEinheit($type, $id, $db)));
}
 
?>

<?php
//Datenbankverbindung herstellen
$SQLITEdb = "database/data.sqlite";
$db = new PDO("sqlite:".$SQLITEdb);

//Funksteckdosen
include "getModes.php";
include "setModes.php";
  
//Andere
include "getRooms.php";
include "getSensorData.php";
include "getSystemInfo.php";
include "getRoomData.php";
include "getGraphData.php";
include "events.php";
include "permissions.php";

//Szenen
include "scenes/getScenes.php";
include "scenes/runScene.php";
include "scenes/createScene.php";

$validUser = validateUser($_POST['username'], $_POST['password'], $db);
 
if($validUser){
    switch($_POST['action']){
        case "getrooms":
            echo getRooms($db);
            break;
		case "getevents":
			echo getEvents($_POST['username'], $_POST['type'], $_POST['limit'], $_POST['offset'], $db);
			break;
		case "geteventtypes":
			echo getEventTypes($db);
			break;
		case "addevent":
			echo addEvent($_POST['type'], $_POST['text'], $db);
			break;
		case "getunseenevents":
			echo getUnseenEvents($_POST['username'], $db);
			break;
		case "getpermissions":
			echo getPermissions($_POST['user'], $db);
			break;
		case "setpermissions":
			echo setPermissions($_POST['user'], $_POST['permissions'], $db);
			break;
		//Dieser Block wurde bearbeitet
        case "getgraphdata":
            echo getGraphData($_POST['type'], $_POST['id'], $_POST['von'], $_POST['bis'], $db);
            break;
        case "getroomdata":
            echo getRoomData($_POST['room'], $db);
            break;
        case "getmodes":
            echo getModes($_POST['room'], $_POST['device'], $db);
            break;
        case "setmodes":
            echo setModes($_POST['room'], $_POST['device'], $_POST['zustand'], $db);
            break;
		//Dieser Block wurde auch bearbeitet
        case "getsensordata":
            echo getSensorData($_POST['room'], $_POST['type'], $_POST['id'], $_POST['showeinheit'], $db);
            break;
        case "runscene":
            echo runScene($_POST['room'], $_POST['name'], $db);
            break;
        case "createscene":
            echo createScene($_POST['devices'], $_POST['rooms'], $_POST['types'], $_POST['values'], $_POST['conditions'], $_POST['room'], $_POST['name'], $db);
            break;
        case "getscenes":
            echo getScenes($_POST['room'], $db);
            break;
        case "getsysteminfo":
            echo getSystemInfo();
            break;
    }
}

function validateUser($username, $password, $db){
    //wird noch implementiert
      
    return true;
}

function hasPermission($action, $db){
	$permissions = getPermissions($_POST['username'], $db);
	$permissions = json_decode($permissions, true)['permissions'];
	
	return (in_array($action, $permissions) || in_array("admin", $permissions));
}

?>