wifi-probe-aggregator/ws/reader.php

539 lines
17 KiB
PHP
Raw Permalink Normal View History

2020-07-27 21:54:53 +02:00
<?php
header('Access-Control-Allow-Origin: *');
2020-07-27 21:54:53 +02:00
// Lettura file di configurazione
$config = parse_ini_file('../config.ini');
// Array per la risposta del web service
$result = [];
switch( $_POST['op'] ){
case 'get-last-devices':
// Vendor cache
2020-08-08 19:10:47 +02:00
$oui = file_get_contents($config['oui_path']);
2020-07-27 21:54:53 +02:00
if (!empty($_POST['date_start']) && !empty($_POST['date_end'])) {
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura device fra le date richieste
$sql = "SELECT DISTINCT logs.mac, logs.ssid, logs.dbm, logs_data.received_at, devices.nome FROM logs LEFT JOIN devices ON logs.mac=devices.mac INNER JOIN (SELECT mac, MAX(received_at) AS received_at FROM logs GROUP BY mac) AS logs_data ON logs_data.mac=logs.mac WHERE logs_data.received_at BETWEEN ? AND ? ORDER BY logs_data.received_at DESC";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
if ($stmt->bind_param('ss', $date_start, $date_end)) {
$date_start = $_POST['date_start'];
$date_end = $_POST['date_end'];
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
// Conversione del segnale in percentuale se richiesto
if ($_POST['signal_type'] == 'percent') {
if ($row['dbm'] <= -100) {
$row['dbm'] = 0;
} elseif ($row['dbm'] >= -50) {
$row['dbm'] = 100;
} else {
$row['dbm'] = 2 * ($row['dbm'] + 100);
}
}
2020-08-08 19:10:47 +02:00
// Lettura vendor
$mac = str_replace( ':', '-', substr($row['mac'], 0, 8) );
$row['vendor'] = '';
if( preg_match( '/^'.preg_quote($mac).'([\s\t]+)\(hex\)(.+?)$/im', $oui, $m) ){
$row['vendor'] = trim($m[2]);
}
2020-07-27 21:54:53 +02:00
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => (array)$records,
];
} else {
$result = [
'status' => 'ERR',
'message' => 'Errore durante l\'esecuzione query',
];
}
}
}
echo json_encode($result);
$stmt->close();
$mysqli->close();
}
break;
case 'get-online-devices':
if (!empty($_POST['date_start']) && !empty($_POST['date_end'])) {
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura numero di device unico nell'intervallo di date
$sql = "SELECT DISTINCT logs.mac FROM logs WHERE received_at BETWEEN ? AND ?";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
if ($stmt->bind_param('ss', $date_start, $date_end)) {
$date_start = $_POST['date_start'];
$date_end = $_POST['date_end'];
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
// Conversione del segnale in percentuale se richiesto
if ($_POST['signal_type'] == 'percent') {
if ($row['dbm'] <= -100) {
$row['dbm'] = 0;
} elseif ($row['dbm'] >= -50) {
$row['dbm'] = 100;
} else {
$row['dbm'] = 2 * ($row['dbm'] + 100);
}
}
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => sizeof($records),
];
} else {
$result = [
'status' => 'ERR',
'message' => 'Errore durante l\'esecuzione query',
];
}
}
}
echo json_encode($result);
$stmt->close();
$mysqli->close();
}
break;
2020-08-08 18:26:50 +02:00
case 'get-devices-by-hour':
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura numero di device unico nell'intervallo di date
$sql = "
SELECT
CONCAT( DATE_FORMAT(received_at, '%H'), ':00' ) AS indice,
COUNT(DISTINCT(mac)) AS valore
FROM
`logs`
GROUP BY
DATE_FORMAT(received_at, '%H')";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => $records,
];
}
}
echo json_encode($result);
$stmt->close();
$mysqli->close();
break;
case 'get-devices-by-weekday':
$weekdays = [
0 => 'domenica',
1 => 'lunedì',
2 => 'martedì',
3 => 'mercoledì',
4 => 'giovedì',
5 => 'venerdì',
6 => 'sabato',
];
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura numero di device unico nell'intervallo di date
$sql = "
SELECT
DATE_FORMAT(received_at, '%w') AS indice,
COUNT(DISTINCT(mac)) AS valore
FROM
`logs`
GROUP BY
DATE_FORMAT(received_at, '%w')";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
$row['indice'] = $weekdays[ $row['indice'] ];
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => $records,
];
}
}
echo json_encode($result);
2020-08-15 13:25:07 +02:00
$stmt->close();
$mysqli->close();
break;
case 'get-activity-by-hour':
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura numero di device unico nell'intervallo di date
$sql = "
SELECT
CONCAT( DATE_FORMAT(received_at, '%H'), ':00' ) AS indice,
COUNT(id) AS valore
FROM
`logs`
WHERE
mac='".$_POST['mac']."'
GROUP BY
DATE_FORMAT(received_at, '%H')";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => $records,
];
}
}
echo json_encode($result);
$stmt->close();
$mysqli->close();
break;
case 'get-activity-by-weekday':
$weekdays = [
0 => 'domenica',
1 => 'lunedì',
2 => 'martedì',
3 => 'mercoledì',
4 => 'giovedì',
5 => 'venerdì',
6 => 'sabato',
];
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura numero di device unico nell'intervallo di date
$sql = "
SELECT
DATE_FORMAT(received_at, '%w') AS indice,
COUNT(id) AS valore
FROM
`logs`
WHERE
mac='".$_POST['mac']."'
GROUP BY
DATE_FORMAT(received_at, '%w')";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
$row['indice'] = $weekdays[ $row['indice'] ];
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => $records,
];
}
}
echo json_encode($result);
2020-08-08 18:26:50 +02:00
$stmt->close();
$mysqli->close();
break;
case 'get-devices-by-vendor':
// Vendor cache
$oui = file_get_contents($config['oui_path']);
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
$sql = "
SELECT
SUBSTRING(mac, 1, 8) AS indice,
2020-08-08 22:39:47 +02:00
COUNT(DISTINCT(mac)) AS valore
FROM
`logs`
GROUP BY
SUBSTRING(mac, 1, 8)
ORDER BY
valore DESC
LIMIT
0, 10";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
// Lettura vendor
$mac = str_replace( ':', '-', substr($row['indice'], 0, 8) );
2020-08-08 22:44:41 +02:00
$row['valore_extra'] = 'sconosciuto';
if( preg_match( '/^'.preg_quote($mac).'([\s\t]+)\(hex\)(.+?)$/im', $oui, $m) ){
$row['valore_extra'] = trim($m[2]);
}
$records[] = $row;
}
$result = [
'status' => 'OK',
'records' => $records,
];
}
}
echo json_encode($result);
$stmt->close();
$mysqli->close();
break;
case 'get-presence-per-day':
// Connessione al database
$mysqli = mysqli_connect($config['db_host'], $config['db_user'], $config['db_pass'], $config['db_name']);
// Errore nella connessione a database
if (mysqli_connect_errno($mysqli)) {
$result = [
'status' => 'ERR',
'message' => mysqli_connect_error(),
];
} else {
// Lettura presenza per giorno
$sql = "
SELECT
DATE_FORMAT(received_at, '%Y-%m-%d') AS giorno,
COUNT(id) AS valore
FROM
`logs`
WHERE
`mac` = ? AND received_at BETWEEN ? AND ?
GROUP BY
DATE_FORMAT(received_at, '%Y-%m-%d')
ORDER BY
received_at ASC";
$stmt = $mysqli->prepare($sql);
// Errore nella preparazione query
if (!$stmt) {
$result = [
'status' => 'ERR',
'message' => $mysqli->error,
];
} else {
if ($stmt->bind_param('sss', $mac, $date_start, $date_end)) {
$mac = $_POST['mac'];
$date_start = $_POST['date_start'];
$date_end = $_POST['date_end'];
// Esecuzione statement
$stmt->execute();
$rs = $stmt->get_result();
while ($row = $rs->fetch_assoc()) {
$records[ $row['giorno'] ] = $row;
}
$result = [
'status' => 'OK',
'records' => (array)$records,
];
}
}
}
echo json_encode($result);
$stmt->close();
$mysqli->close();
break;
}