public static function byRoom($room)
{
$switch = new Relay();
$switch->switches = array();
$bdd = getBDD();
$req = $bdd->query("SELECT at_sensors.id, at_sensors.sensor, at_sensors.device, at_sensors.protocol, at_sensors_devices.alias, at_sensors_devices.room FROM at_sensors INNER JOIN at_sensors_devices ON at_sensors.device = at_sensors_devices.device WHERE at_sensors.type = 'switchBinary' AND at_sensors.ignore = 0 AND at_sensors_devices.room = {$room}");
while ($data = $req->fetch()) {
$switch->switches[] = array('id' => $data['id'], 'sensor' => $data['sensor'], 'device' => $data['device'], 'protocol' => $data['protocol'], 'alias' => $data['alias'], 'room' => $data['room']);
}
return $switch;
}
public static function allLights()
{
$light = new Light();
$light->lights = array();
$bdd = getBDD();
$req = $bdd->query("SELECT * FROM at_lights");
while ($data = $req->fetch()) {
$light->lights[] = array('id' => $data['id'], 'name' => $data['name'], 'protocol' => $data['protocol'], 'ip' => $data['ip'], 'room' => $data['room'], 'uid' => $data['uid']);
}
return $light;
}
function delete($arr)
{
$mac = $arr['mac'];
$bdd = getBDD();
$req = $bdd->exec("DELETE FROM at_gcm WHERE mac = '{$mac}'");
if ($req == 1) {
http_response_code(202);
} else {
http_response_code(400);
}
}
function get()
{
$settings = new Settings();
$atlantis = array('lat' => doubleval($settings->getSettings('Atlantis', 'lat')), 'long' => doubleval($settings->getSettings('Atlantis', 'long')));
$bdd = getBDD();
$req = $bdd->query('SELECT arr.*, IFNULL(arr2.nom, at_users.nom) nom FROM (SELECT t1.* FROM at_geo t1 LEFT JOIN at_geo t2 ON (t1.mac = t2.mac AND t1.timestamp < t2.timestamp) WHERE t2.timestamp IS NULL AND t1.date = CURDATE()) AS arr LEFT JOIN (SELECT at_devices.mac, at_users.nom FROM at_devices INNER JOIN at_users ON at_devices.username = at_users.id) AS arr2 ON arr.mac = arr2.mac LEFT JOIN at_users ON arr.mac = at_users.cle');
$result = $req->fetchAll(PDO::FETCH_ASSOC);
$req->closeCursor();
http_response_code(202);
$output = array('atlantis' => $atlantis, 'positions' => $result);
return $output;
}
function getAll()
{
$bdd = getBDD();
$req = $bdd->query('SELECT * FROM at_ean ORDER BY nom');
$result = array();
while ($data = $req->fetch()) {
$result[] = array('nom' => $data['nom'], 'ean' => $data['ean']);
}
$req->closeCursor();
http_response_code(202);
return $result;
}
public function sendMessageDevice($title, $message, $device)
{
$gcpm = new GCMPushMessage($this->apiKey);
$bdd = getBDD();
$req = $bdd->query("SELECT at_gcm.gcm FROM at_gcm JOIN at_devices ON at_gcm.mac = at_devices.mac JOIN at_users ON at_devices.username = at_users.id WHERE at_gcm.mac = '{$device}' LIMIT 1");
$data = $req->fetch();
if (!$data) {
return;
}
$gcpm->setDevices($data['gcm']);
$response = $gcpm->send($message, array('title' => $title));
return $response;
}
function welcome($api)
{
$bdd = getBDD();
$req = $bdd->prepare('UPDATE at_welcome SET jour = now(), heure = now() WHERE mac = :mac');
$req->execute(array('mac' => $api));
$req->closeCursor();
$player = new Player();
$req = $bdd->prepare('SELECT at_music.file FROM at_music JOIN at_welcome ON at_music.id = at_welcome.music WHERE at_welcome.mac = mac');
$req->execute(array('mac' => $mac));
$data = $req->fetch();
$player->play($data['file']);
$req->closeCursor();
}
function get($arr)
{
$bdd = getBDD();
if (isset($arr['to'])) {
$to = $arr['to'];
} else {
$to = date('Y-m-d');
}
if (isset($arr['from'])) {
$from = $arr['from'];
} else {
$date = new DateTime();
$date->sub(new DateInterval('P3D'));
$from = $date->format('Y-m-d');
}
if (isset($arr['plant'])) {
$sensor = $arr['plant'];
$req = $bdd->query("SELECT *, AVG(moisture), AVG(air_temperature), AVG(soil_temperature), AVG(soil_conductivity), AVG(light) FROM at_plants_history WHERE sensor = '{$sensor}' AND date BETWEEN '{$from}' AND '{$to}' GROUP BY date");
$output = array();
while ($data = $req->fetch()) {
$output[] = array('light_m' => $data['AVG(light)'], 'moisture_m' => $data['AVG(moisture)'], 'air_temperature_m' => $data['AVG(air_temperature)'], 'soil_temperature_m' => $data['AVG(soil_temperature)'], 'soil_conductivity_m' => $data['AVG(soil_conductivity)'], 'date' => $data['date']);
}
http_response_code(202);
return $output;
} else {
if (isset($arr['sensor'])) {
$sensor = $arr['sensor'];
$req = $bdd->query("SELECT * FROM at_sensors_values WHERE sensor = '{$sensor}' AND date BETWEEN '{$from}' AND '{$to}' ORDER BY date, time");
$output = array();
while ($data = $req->fetch()) {
$output[] = array('value' => $data['value'], 'date' => $data['date'], 'time' => $data['time']);
}
http_response_code(202);
return $output;
} else {
$req = $bdd->query('SELECT * FROM at_plants');
$output_plants = array();
while ($data = $req->fetch()) {
$output_plants[] = array('id' => $data['id'], 'sensor' => $data['sensor'], 'title' => $data['title'], 'color' => $data['color'], 'room' => $data['room']);
}
$req = $bdd->query('SELECT at_sensors.*, at_sensors_devices.alias FROM at_sensors_values JOIN at_sensors ON at_sensors_values.sensor = at_sensors.id JOIN at_sensors_devices ON at_sensors.device = at_sensors_devices.device GROUP BY at_sensors.sensor');
$output_sensors = array();
while ($data = $req->fetch()) {
$output_sensors[] = array('id' => $data['id'], 'sensor' => $data['sensor'], 'type' => $data['type'], 'unit' => $data['unit'], 'alias' => $data['alias']);
}
http_response_code(202);
return array('plants' => $output_plants, 'sensors' => $output_sensors);
}
}
}
function delete($arr)
{
if (isset($arr['id'])) {
$bdd = getBDD();
$id = $arr['id'];
$request = $bdd->exec("UPDATE at_lights, at_plants, at_sensors_devices SET at_lights.room = NULL, at_plants.room = NULL, at_sensors_devices.room = NULL WHERE at_lights.room = '{$id}' OR at_plants.room = '{$id}' OR at_sensors_devices.room = '{$id}'");
$requestF = $bdd->exec("DELETE FROM at_room WHERE id = '{$id}'");
if ($requestF == 1) {
http_response_code(202);
} else {
http_response_code(400);
}
} else {
http_response_code(404);
}
}
function delete($arr)
{
$bdd = getBDD();
if (isset($arr['id'])) {
$id = $arr['id'];
$request = $bdd->exec("DELETE FROM at_courses WHERE id = '{$id}'");
if ($request == 1) {
http_response_code(202);
} else {
http_response_code(400);
}
} else {
$request = $bdd->exec("TRUNCATE at_courses");
if ($request > 0) {
http_response_code(202);
} else {
http_response_code(400);
}
}
}
function getCamera($id)
{
$bdd = getBDD();
$req = $bdd->query("SELECT * FROM at_cameras WHERE id = {$id}");
$data = $req->fetch();
if (!$data) {
http_response_code(404);
return;
}
saveImage($data);
$path = __DIR__ . "/home/cameras/{$id}.png";
if (file_exists($path)) {
http_response_code(202);
$img = fopen($path, 'rb');
header("Content-Type: image/png");
header("Content-Length: " . filesize($path));
header("Timestamp: " . filemtime($path));
header("Source: Atlantis");
fpassthru($img);
} else {
http_response_code(404);
}
}
/**
* Niveau des comptes :
* 0 : Administrateur (sans restrictions)
* 1 : Utilisateur quotidien
* 2 : Visiteur (uniquement lumieres)
* 3 : Urgences
* -1 : Bloque
*
* @param type $val
* @param type $level
* @return boolean
*/
function checkAPI($val, $level)
{
$bdd = getBDD();
$req = $bdd->prepare('SELECT nom, type FROM at_users WHERE cle = :cle');
$req->execute(array('cle' => $val));
$resultat = $req->fetch();
if ($resultat) {
$req->closeCursor();
$user_level = $resultat['type'];
} else {
$req = $bdd->prepare('SELECT nom FROM at_devices WHERE mac = :mac');
$req->execute(array('mac' => strtoupper($val)));
$resultat = $req->fetch();
$req->closeCursor();
if (!$resultat) {
return false;
} else {
$req2 = $bdd->prepare('SELECT at_users.type FROM at_users INNER JOIN at_devices ON at_devices.username = at_users.id WHERE at_devices.mac = :mac');
$req2->execute(array('mac' => strtoupper($val)));
$resultat2 = $req2->fetch();
$req2->closeCursor();
if ($resultat2) {
$user_level = $resultat2['type'];
} else {
return false;
}
}
}
if ($user_level == -1) {
return false;
}
if ($user_level <= $level) {
return true;
} else {
return false;
}
}
function add($values)
{
if (isset($values['element'], $values['peremption'])) {
$bdd = getBDD();
if (isset($values['quantite']) && $values['quantite'] > 0) {
$quantite = $values['quantite'];
} else {
$quantite = 1;
}
if (isset($values['endroit'])) {
$place = $values['endroit'];
} else {
$place = 'placard';
}
$element = $values['element'];
if (isset($values['ean'])) {
$ean = $values['element'];
$label = $values['ean'];
$bdd->exec("INSERT INTO at_ean VALUES('{$ean}', '{$label}')");
}
$peremption = $values['peremption'];
$request = $bdd->exec("INSERT INTO at_cuisine (`element`, `peremption`, `quantite`, `endroit`) VALUES('{$element}', '{$peremption}', '{$quantite}', '{$place}')");
if ($request == 1) {
$id = $bdd->lastInsertId();
$newRequest = $bdd->query("SELECT * FROM at_cuisine LEFT JOIN at_ean ON at_cuisine.element = at_ean.ean WHERE at_cuisine.id = '{$id}' ORDER BY date2 DESC, peremption");
$data = $newRequest->fetch();
$today = strtotime(now);
$date = strtotime($data['peremption']) - $today;
$date = intval($date / 86400);
$data2 = strtotime($data['date2']) - $today;
$date2 = intval($date2 / 86400);
if ($data['nom'] == NULL) {
$label = $data['element'];
$ean = NULL;
} else {
$label = $data['nom'];
$ean = $data['ean'];
}
$newItem = array('id' => $data['id'], 'label' => $label, 'ean' => $ean, 'quantite' => $data['quantite'], 'peremption' => $date, 'endroit' => $data['endroit'], 'status' => $data['status'], 'ouvert' => $date2, 'ignore' => $data['avoid']);
echo json_encode($newItem);
http_response_code(202);
} else {
http_response_code(400);
}
} else {
http_response_code(404);
}
}
public function delete()
{
$bdd = getBDD();
$req = $bdd->exec('TRUNCATE TABLE at_lights');
$req->closeCursor();
return 200;
}
function delete($arr)
{
if (isset($arr['id'])) {
$id = $arr['id'];
$bdd = getBDD();
$request = $bdd->exec("DELETE FROM at_entretien WHERE id = '{$id}'");
if ($request == 1) {
http_response_code(202);
} else {
http_response_code(400);
}
} else {
http_response_code(404);
}
}
if (isset($arr['msg'])) {
if (isset($arr['id'])) {
// TODO
} else {
$msg = $arr['msg'];
$push = new PushMessage();
$push->sendMessageAll('Atlantis', $msg);
http_response_code(202);
}
} else {
http_response_code(404);
}
}
if (isset($_GET['api']) && checkAPI($_GET['api'], $page_level)) {
$push = new PushMessage();
$bdd = getBDD();
if (isset($_GET['action'])) {
switch ($_GET['action']) {
case 'geo':
$message = "geo";
$title = "at_commands";
break;
case 'geoi':
$message = "geoi";
$title = "at_commands";
break;
case 'notifyall':
if (isset($_GET['msg'], $_GET['title'])) {
$message = $_GET['msg'];
$title = $_GET['title'];
}
function checkBattery()
{
$bdd = getBDD();
$req = $bdd->query('SELECT t1.* FROM at_plants_history t1 LEFT JOIN at_plants_history t2 ON (t1.sensor = t2.sensor AND t1.date < t2.date) WHERE t2.date IS NULL AND t1.battery < 15');
if ($req) {
$push = new PushMessage();
$push->sendMessage('Atlantis', 'Certains capteurs necessitent un changement de piles !');
}
}
function getRooms()
{
$bdd = getBDD();
$req = $bdd->query('SELECT * FROM at_room');
$result = $req->fetchAll(PDO::FETCH_ASSOC);
$req->closeCursor();
return $result;
}
function setRoom($light, $room)
{
$bdd = getBDD();
$req = $bdd->prepare('UPDATE at_lights SET `room` = :room WHERE `id` = :light');
$req->execute(array('room' => $room, 'light' => $light));
$req->closeCursor();
}
function loadScenarios($arr)
{
$bdd = getBDD();
$output = array();
foreach ($arr as $sensor) {
$result = array();
if ($sensor['type'] == 'Door/Window' || $sensor['type'] == 'Motion') {
$sensor = $sensor['id'];
$req = $bdd->query("SELECT * FROM at_sensors_scenarios WHERE sensor = {$sensor}");
while ($data = $req->fetch()) {
array_push($result, $data);
}
}
array_push($output, $result);
}
return $output;
}
function delete($arr)
{
if (isset($arr['id'])) {
$id = $arr['id'];
$bdd = getBDD();
$req = $bdd->query("SELECT * FROM at_devices WHERE id = '{$id}'");
$data = $req->fetch();
$mac = $data['mac'];
$bdd->exec("DELETE FROM at_gcm WHERE mac = '{$mac}'");
$req2 = $bdd->exec("DELETE FROM at_devices WHERE id = '{$id}'");
if ($req2 == 1) {
(new Sync())->update(Sync::DEVICES);
http_response_code(202);
} else {
http_response_code(400);
}
} else {
http_response_code(404);
}
}
function getActionners($id = NULL)
{
$bdd = getBDD();
$req = $bdd->query('SELECT * FROM at_sensors WHERE `type` = "switchBinary"');
$arr = array();
while ($data = $req->fetch()) {
$arr[] = array('id' => $data['id'], 'sensor' => $data['sensor'], 'device' => $data['device'], 'protocol' => $data['protocol'], 'type' => 'actionner');
}
$req->closeCursor();
if ($id != NULL) {
$req2 = $bdd->prepare('SELECT * FROM at_switches WHERE `switch` = :switch');
$req2->execute(array('switch' => $id));
$data = $req2->fetch();
$interrupt = array('switch' => $data['switch'], 'sensor' => $data['sensor'], 'type' => $data['type'], 'action' => $data['action']);
$req2->closeCursor();
$result = array('interrupt' => $interrupt, 'actionners' => $arr);
return $result;
}
return $arr;
}
function getRooms()
{
$bdd = getBDD();
$req = $bdd->query('SELECT * FROM at_room');
$arr = array();
while ($data = $req->fetch()) {
$arr[] = array('id' => $data['id'], 'room' => $data['room']);
}
$req->closeCursor();
return $arr;
}
public function delete()
{
$bdd = getBDD();
$req = $bdd->exec('TRUNCATE TABLE at_sensors');
$req2 = $bdd->exec('TRUNCATE TABLE at_sensors_devices');
$req->closeCursor();
$req2->closeCursor();
return 200;
}
function delete($arr)
{
if (isset($arr['id'])) {
$id = $arr['id'];
$bdd = getBDD();
$user_req = $bdd->query("SELECT nom FROM at_users WHERE id = '{$id}'");
$user = $user_req->fetch(PDO::FETCH_ASSOC);
(new Owncloud())->removeUser($user['nom']);
$request = $bdd->exec("DELETE FROM at_users WHERE id = '{$id}'");
if ($request == 1) {
http_response_code(202);
}
} else {
http_response_code(404);
}
}
function getSongs()
{
$arr = array();
$bdd = getBDD();
$req = $bdd->query('SELECT * FROM at_playlists');
while ($data = $req->fetch()) {
$arr[] = array('type' => 'playlist', 'title' => $data['title'], 'id' => $data['id']);
}
$req->closeCursor();
$req2 = $bdd->query('SELECT * FROM at_music');
while ($data2 = $req2->fetch()) {
$title = basename($data2['file']);
$arr[] = array('type' => 'song', 'title' => $title, 'path' => $data2['file'], 'length' => $data2['length'], 'album' => $data2['album'], 'id' => $data2['id']);
}
$req2->closeCursor();
return $arr;
}
