Ereignisse in Datenbank speichern & abfragen

<?php

function getEvents($username, $type, $limit, $offset, $db){
    //Ereignisse laden
	if($type !== ""){
		$results = $db->prepare("SELECT * FROM 'EVENTS' WHERE TYPE == :type ORDER BY TIMESTAMP DESC LIMIT :limit OFFSET :offset");
		$results->execute(array('type' => $type, 'limit' => $limit, 'offset' => $offset));
	}
	else{
		$results = $db->prepare("SELECT * FROM 'EVENTS' ORDER BY TIMESTAMP DESC LIMIT :limit OFFSET :offset");
		$results->execute(array('limit' => $limit, 'offset' => $offset));
	}

    $events = array();
 
    //JSON-Objekt erstellen und füllen
    foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
        $event_item = array('text' => $row['TEXT'], 'time' => $row['TIMESTAMP'], 'type' => $row['TYPE']);
        array_push($events, $event_item);
    }
 
	//EVENTS_LAST_CHECKED für $username mit aktueller Zeit aktualisieren
	$query = $db->prepare("UPDATE 'userdata' SET 'EVENTS_LAST_CHECKED' = :time WHERE USERNAME == :username");
	$query->execute(array('time' => time(), 'username' => $username));
 
    //JSON-Objekt ausgeben
    return json_encode(array('events' => $events));
}

function getUnseenEvents($username, $db){
	//EVENTS_LAST_CHECKED für Nutzer $username abfragen
	$query = $db->prepare("SELECT EVENTS_LAST_CHECKED FROM 'userdata' WHERE USERNAME == :username");
	$query->execute(array('username' => $username));
	
	$lastChecked = $query->fetch(PDO::FETCH_ASSOC)['EVENTS_LAST_CHECKED'];
	
	if($lastChecked != false){
	
		//Ereignisse zählen
		$results = $db->prepare("SELECT COUNT(*) FROM 'EVENTS' WHERE TIMESTAMP > :time");
		
		$results->execute(array('time' => $lastChecked));
	
		
		foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
			return $row['COUNT(*)'];
		}
	}
	else{
		return "usernotfound";
	}
}

function getEventTypes($db){
    //Eventtypen laden
    $results = $db->prepare("SELECT DISTINCT TYPE FROM EVENTS ORDER BY TYPE");
 
    $results->execute();
 
    $types = array();
 
    //JSON-Objekt erstellen und füllen
    foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
        array_push($types, $row['TYPE']);
    }
 
    //JSON-Objekt ausgeben
    return json_encode(array('types' => $types));
}

function addEvent($type, $text, $db){
	//Ereignis in Datenbank schreiben
	$statement = $db->prepare("INSERT INTO EVENTS (TYPE, TEXT, TIMESTAMP) VALUES (?,?,?)");
	$statement->execute(array($type, $text, time()));
	
	return "eventadded";
}

?>

<?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";

//Diese Zeile wurde hinzugefügt
include "events.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;
		
		//Diese Zeilen wurden hinzugefügt
		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 "getgraphdata":
            echo getGraphData($_POST['room'], $_POST['value'], $_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;
        case "getsensordata":
            echo getSensorData($_POST['room'], $_POST['value'], $_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;
}
  
?>

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

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

now = datetime.datetime.now().strftime("%s")

con = lite.connect('/var/www/html/database/data.sqlite')
			
def addEvent(type, text):
	#Daten in Datenbank schreiben
	cur = con.cursor()
	cur.execute("INSERT INTO EVENTS (TIMESTAMP, TEXT, TYPE) VALUES ('"+now+"', '"+text+"', '"+type+"')")
	con.commit()

#Skriptaufruf: sudo python /var/www/html/python/add-event.py [TYP] [TEXT]

text = ""

if(len(sys.argv) > 2):
	for index in range(2, len(sys.argv)):
		if(index > 2):
			text = text+" "
		text = text+sys.argv[index]

addEvent(sys.argv[1], text)

sudo python /var/www/html/python/add-event.py [TYP] [TEXT]

sudo python /var/www/html/python/add-event.py Server Der Server ist jetzt online.

crontab -e

@reboot sudo python /var/www/html/python/add-event.py Server Der Server ist jetzt online.