539 lines
17 KiB
PHP
539 lines
17 KiB
PHP
<?php
|
|
header('Access-Control-Allow-Origin: *');
|
|
|
|
// 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
|
|
$oui = file_get_contents($config['oui_path']);
|
|
|
|
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);
|
|
}
|
|
}
|
|
|
|
// 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]);
|
|
}
|
|
|
|
$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;
|
|
|
|
|
|
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);
|
|
|
|
$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);
|
|
|
|
$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,
|
|
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) );
|
|
|
|
$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;
|
|
}
|