Это старая версия документа!
В данном документе приведён текст программы системы усреднения данных с приборов сети измерений кафедры К3 МФ МГТУ им. Н. Э. Баумана.
index.html
index.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Home page</title> <link rel="stylesheet" href="style.css"> </head> <body> <nav class="nav nav-default"> <h3>Навигация</h3> <a href="/request.html">Примеры запросов для выгрузки данных</a> <a href="/conn.php">Дебаг-монитор для дневных значений</a> <a href="/conn_hour.php">Дебаг-монитор для часовых значений</a> <a href="/conn_minutes.php">Дебаг-монитор для пятнадцатиминутных значений</a> <a href="/fileexport.html">Выгрузка файла</a> </nav> </body> </html>
conn.php
conn.php
<?php function cmp($a, $b) { return strcmp($a[0], $b[0]); } //ini_set('display_errors','1'); //ini_set('display_startup_errors','1'); //error_reporting(E_ALL); $pgi = pg_connect("host=localhost port=5432 dbname=averobo user=averobo password=*****"); ?> <!DOCTYPE html> <style> th { text-indent: 0px; border-collapse: collapse; background: #e8edfa; border-bottom: 1px solid #fff; color: #500; font-weight: 700; border-top: 0px solid transparent; padding: 2px; text-align:center; } td { text-indent: 0px; border-collapse: collapse; background: #e8edff; border-bottom: 1px solid #fff; color: #500; border-top: 0px solid transparent; padding: 2px; text-align:center; } tr:hover td {background: #eeddff;} tr{text-indent: 1px;} </style> <?php if (!$pgi) { die ("Error!"); pg_close($pgi); // echo 'yes'; } else { $query = 'SELECT average, json_file, date, name FROM main_table WHERE name NOT LIKE \'Сервер%\' AND date >= now()::date - interval \'1 day\' ORDER BY date DESC'; $result = pg_query($pgi, "$query"); if(!$result) { die("Wrong query!"); } else { $t_mas = array(); while($myrow = pg_fetch_assoc($result)) { if(!in_array($myrow['average'], $t_mas)) { array_push($t_mas, $myrow['average']); } } rsort($t_mas); array_unshift($t_mas, "name","date"); pg_result_seek($result, 0); $i = 0; while($infrow = pg_fetch_assoc($result)) { $i++; $flag = -100; for($j=1; $j<$i; $j++) { if ($infrow["name"] == $mass[$j][0]) { $flag = $j; } } unset($j); if ($flag != -100) { $mass[$flag][array_search($infrow['average'], $t_mas)] = $infrow['json_file']; $i--; } else { $mass[$i][array_search($infrow['average'], $t_mas)] = $infrow['json_file']; $mass[$i][array_search("name", $t_mas)] = $infrow['name']; $mass[$i][array_search("date", $t_mas)] = $infrow['date']; } } usort($mass, "cmp"); unset($i); $title = '<thead><tr>'; for($i=0; $i<count($t_mas); $i++) { $title .= '<th>'.$t_mas[$i].'</th>'; } unset($i); $max = ''; for($i=1; $i<count($mass)+1; $i++) { $max .= '<tr>'; for($j=0; $j<count($t_mas); $j++) { $max .= '<td>'.$mass[$i][$j].'</td>'; } $max .= '</tr>'; } $list = $title.'</tr></thead><tbody>'.$max.'</tbody>'; echo '<table width="100%" style="font-size: 11px">'.$list.'</table>'; } } pg_close($pgi); ?>
conn_hour.php
conn_hour.php
<?php function cmp($a, $b) { return strcmp($a[0], $b[0]); } //ini_set('display_errors','1'); //ini_set('display_startup_errors','1'); //error_reporting(E_ALL); $pgi = pg_connect("host=localhost port=5432 dbname=averobo user=averobo password=*****"); ?> <!DOCTYPE html> <style> th { text-indent: 0px; border-collapse: collapse; background: #e8edfa; border-bottom: 1px solid #fff; color: #500; font-weight: 700; border-top: 0px solid transparent; padding: 2px; text-align:center; } td { text-indent: 0px; border-collapse: collapse; background: #e8edff; border-bottom: 1px solid #fff; color: #500; border-top: 0px solid transparent; padding: 2px; text-align:center; } tr:hover td {background: #eeddff;} tr{text-indent: 1px;} </style> <?php if (!$pgi) { die ("Error!"); pg_close($pgi); } else { $query = 'SELECT average, json_file, date, name FROM table_with_time WHERE name NOT LIKE \'Сервер%\' AND date >= now()::date - interval \'1 hour\' AND (average = \'maximum_in_hour\' OR average = \'minimum_in_hour\' OR average = \'aver_hour\') ORDER BY date DESC'; $result = pg_query($pgi, "$query"); if(!$result) { die("Wrong query!"); } else { $t_mas = array(); while($myrow = pg_fetch_assoc($result)) { if(!in_array($myrow['average'], $t_mas)) { array_push($t_mas, $myrow['average']); } } rsort($t_mas); array_unshift($t_mas, "name","date"); pg_result_seek($result, 0); $i = 0; while($infrow = pg_fetch_assoc($result)) { $i++; $flag = -100; for($j=1; $j<$i; $j++) { if ($infrow["name"] == $mass[$j][0]) { $flag = $j; } } unset($j); if ($flag != -100) { $mass[$flag][array_search($infrow['average'], $t_mas)] = $infrow['json_file']; $i--; } else { $mass[$i][array_search($infrow['average'], $t_mas)] = $infrow['json_file']; $mass[$i][array_search("name", $t_mas)] = $infrow['name']; $mass[$i][array_search("date", $t_mas)] = $infrow['date']; } } usort($mass, "cmp"); unset($i); $title = '<thead><tr>'; for($i=0; $i<count($t_mas); $i++) { $title .= '<th>'.$t_mas[$i].'</th>'; } unset($i); $max = ''; for($i=1; $i<count($mass)+1; $i++) { $max .= '<tr>'; for($j=0; $j<count($t_mas); $j++) { $max .= '<td>'.$mass[$i][$j].'</td>'; } $max .= '</tr>'; } $list = $title.'</tr></thead><tbody>'.$max.'</tbody>'; echo '<table width="100%" style="font-size: 11px">'.$list.'</table>'; } } pg_close($pgi); ?>
conn_minutes.php
conn_minutes.php
<?php function cmp($a, $b) { return strcmp($a[0], $b[0]); } //ini_set('display_errors','1'); //ini_set('display_startup_errors','1'); //error_reporting(E_ALL); $pgi = pg_connect("host=localhost port=5432 dbname=averobo user=averobo password=genshin2021alena2021"); ?> <!DOCTYPE html> <style> th { text-indent: 0px; border-collapse: collapse; background: #e8edfa; border-bottom: 1px solid #fff; color: #500; font-weight: 700; border-top: 0px solid transparent; padding: 2px; text-align:center; } td { text-indent: 0px; border-collapse: collapse; background: #e8edff; border-bottom: 1px solid #fff; color: #500; border-top: 0px solid transparent; padding: 2px; text-align:center; } tr:hover td {background: #eeddff;} tr{text-indent: 1px;} </style> <?php if (!$pgi) { die ("Error!"); pg_close($pgi); } else { $query = 'SELECT average, json_file, date, name FROM table_with_time WHERE name NOT LIKE \'Сервер%\' AND date >= now()::date - interval \'15 minutes\' AND (average = \'minimum_in_15min\' OR average = \'maximum_in_15max\' OR average = \'aver_15min\') ORDER BY date DESC'; $result = pg_query($pgi, "$query"); if(!$result) { die("Wrong query!"); } else { $t_mas = array(); while($myrow = pg_fetch_assoc($result)) { if(!in_array($myrow['average'], $t_mas)) { array_push($t_mas, $myrow['average']); } } rsort($t_mas); array_unshift($t_mas, "name","date"); pg_result_seek($result, 0); $i = 0; while($infrow = pg_fetch_assoc($result)) { $i++; $flag = -100; for($j=1; $j<$i; $j++) { if ($infrow["name"] == $mass[$j][0]) { $flag = $j; } } unset($j); if ($flag != -100) { //echo "yes"; $mass[$flag][array_search($infrow['average'], $t_mas)] = $infrow['json_file']; $i--; } else { $mass[$i][array_search($infrow['average'], $t_mas)] = $infrow['json_file']; $mass[$i][array_search("name", $t_mas)] = $infrow['name']; $mass[$i][array_search("date", $t_mas)] = $infrow['date']; } } usort($mass, "cmp"); unset($i); $title = '<thead><tr>'; for($i=0; $i<count($t_mas); $i++) { $title .= '<th>'.$t_mas[$i].'</th>'; } unset($i); $max = ''; for($i=1; $i<count($mass)+1; $i++) { $max .= '<tr>'; for($j=0; $j<count($t_mas); $j++) { $max .= '<td>'.$mass[$i][$j].'</td>'; } $max .= '</tr>'; } $list = $title.'</tr></thead><tbody>'.$max.'</tbody>'; echo '<table width="100%" style="font-size: 11px">'.$list.'</table>'; } } pg_close($pgi); ?>
request.html
request.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Request guide</title> <link rel="stylesheet" href="stylereq.css"> </head> <body> <nav class="nav nav-default"> <h6>Перед работой с запросами не забудьте подставить нужные названия приборов и даты.</h6> <h6>Пример выгрузки данных, осредненных по дню:</h6> <a href="/averequest.php?avr_type=day&name=%27Hydra-L%2005%27,%27Hydra-L%2006%27&fdate=2023-10-04&sdate=2023-10-05">http://averobo.mgul.ac.ru/averequest.php?avr_type=day&name='Hydra-L 05','Hydra-L 06'&fdate=2023-10-04&sdate=2023-10-05</a> <h6>Пример выгрузки данных, осредненных по часу:</h6> <a href="/averequest.php?avr_type=hour&name=%27Hydra-L%2005%27,%27Hydra-L%2006%27&fdate=2023-10-04%2000:00:00&sdate=2023-10-05%2000:00:00">http://averobo.mgul.ac.ru/averequest.php?avr_type=hour&name='Hydra-L 05','Hydra-L 06'&fdate=2023-10-04 00:00:00&sdate=2023-10-05 00:00:00</a> <h6>Пример выгрузки данных, осредненных по 15 минутам:</h6> <a href="/averequest.php?avr_type=15min&name=%27Hydra-L%2005%27,%27Hydra-L%2006%27&fdate=2023-10-04%2000:00:00&sdate=2023-10-05%2000:00:00">http://averobo.mgul.ac.ru/averequest.php?avr_type=15min&name='Hydra-L 05','Hydra-L 06'&fdate=2023-10-04 00:00:00&sdate=2023-10-05 00:00:00</a> <h6>Пример выгрузки данных, сложенных за 15 минут:</h6> <a href="/averequest.php?avr_type=halfpath15&name=%27Hydra-L%2003%27,%27Hydra-L%2007%27&fdate=2023-10-14%2000:00:00&sdate=2023-10-14%2001:00:00">http://averobo.mgul.ac.ru/averequest.php?avr_type=halfpath15&name='Hydra-L 03','Hydra-L 07'&fdate=2023-10-14 00:00:00&sdate=2023-10-14 01:00:00</a> <h6>Пример выгрузки данных, сложенных за час:</h6> <a href="/averequest.php?avr_type=halfpathhour&name=%27Hydra-L%2003%27,%27Hydra-L%2007%27&fdate=2023-10-14%2000:00:00&sdate=2023-10-14%2001:00:00">http://averobo.mgul.ac.ru/averequest.php?avr_type=halfpathhour&name='Hydra-L 03','Hydra-L 07'&fdate=2023-10-14 00:00:00&sdate=2023-10-14 01:00:00</a> </nav> </body> </html>
averequest.php
averequest.php
<?php header('Content-Type: application/json; charset=utf-8'); ini_set('display_errors','1'); ini_set('display_startup_errors','1'); error_reporting(32759); $reqpar = $_SERVER['QUERY_STRING']; parse_str($reqpar, $getpar); $pgi = pg_connect("host=localhost port=5432 dbname=averobo user=averobo password=*****"); if(!$pgi) { die("Error!"); pg_close($pgi); } elseif ($getpar['help'] == 'manual') { echo "example for day: http://averobo.mgul.ac.ru/averequest.php?avr_type=day&name=%27Hydra-L%2005%27,%27Hydra-L%2006%27&fdate=2023-10-04&sdate=2023-10-05\n"; echo "example for sum and amount in 15 min: http://averobo.mgul.ac.ru/averequest.php?avr_type=halfpath15&name=%27Hydra-L%2003%27,%27Hydra-L%2007%27&fdate=2023-10-14%2000:00:00&sdate=2023-10-14%2001:00:00"; } else { if ($getpar['avr_type'] == 'day') { $query = 'SELECT * FROM main_table WHERE name in ('.$getpar['name'].') AND date >= \''.$getpar['fdate'].'\' AND date <= \''.$getpar['sdate'].'\' ORDER BY date'; } elseif ($getpar['avr_type'] == 'halfpath15') { $query = 'SELECT * FROM half_path WHERE average = \'half_path_15min\' AND name in ('.$getpar['name'].') AND date >= \''.$getpar['fdate'].'\' AND date <= \''.$getpar['sdate'].'\' ORDER BY date'; } elseif ($getpar['avr_type'] == 'halfpathhour') { $query = 'SELECT * FROM half_path WHERE average = \'half_path_hour\' AND name in ('.$getpar['name'].') AND date >= \''.$getpar['fdate'].'\' AND date <= \''.$getpar['sdate'].'\' ORDER BY date'; } elseif ($getpar['avr_type'] == '15min') { $query = 'SELECT * FROM table_with_time WHERE (average = \'minimum_in_15min\' OR average = \'maximum_in_15max\' OR average = \'aver_15min\') AND name in ('.$getpar['name'].') AND date >= \''.$getpar['fdate'].'\' AND date <= \''.$getpar['sdate'].'\' ORDER BY date'; } elseif ($getpar['avr_type'] == 'hour') { $query = 'SELECT * FROM table_with_time WHERE (average = \'minimum_in_hour\' OR average = \'maximum_in_hour\' OR average = \'aver_hour\') AND name in ('.$getpar['name'].') AND date >= \''.$getpar['fdate'].'\' AND date <= \''.$getpar['sdate'].'\' ORDER BY date'; } $result = pg_query($pgi, "$query"); if(!$result) { die("Wrong query!"); } else { $myarr = array(); $i = 0; while($myrow = pg_fetch_assoc($result)) { $myarr[(string)$i." "] = stripslashes(json_encode($myrow, JSON_UNESCAPED_UNICODE)); $i++; } $str = stripslashes(str_replace(" \"","\"",json_encode($myarr, JSON_UNESCAPED_UNICODE))); $str3 = str_replace(":\"{",":{",$str) ; echo stripslashes(str_replace("}\"","}",$str3)); } pg_close($pgi); } ?>
fileexport.html
fileexport.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Export Data</title> <link rel="stylesheet" href="style.css"> </head> <body> <nav class="nav nav-default"> <h3>Выгрузка данных</h3> <form action="export.php" method="post"> <div class="form-group"> <label style="color: rgb(220, 220, 220); font-size: 16px;" for="start_date">Время начала:</label> <input style="background-color: rgb(33, 33, 33); color: rgb(220, 220, 220); padding: 10px; font-size: 16px;" type="datetime-local" id="start_datetime" name="start_datetime" required> </div> <div class="form-group"> <label style="color: rgb(220, 220, 220); font-size: 16px;" for="end_date">Время конца:</label> <input style="background-color: rgb(33, 33, 33); color: rgb(220, 220, 220); padding: 10px; font-size: 16px;" type="datetime-local" id="end_datetime" name="end_datetime" required> </div> <div class="form-group"> <label style="color: rgb(220, 220, 220); font-size: 16px;" for="file_type">Тип файла:</label> <select style="background-color: rgb(33, 33, 33); color: rgb(220, 220, 220); padding: 10px; font-size: 16px;" id="file_type" name="file_type" required> <option value="json">JSON</option> <option value="csv">CSV</option> </select> </div> <div class="form-group"> <label style="color: rgb(220, 220, 220); font-size: 16px;" for="file_type">Промежуток осреднения:</label> <select style="background-color: rgb(33, 33, 33); color: rgb(220, 220, 220); padding: 10px; font-size: 16px;" id="avg_type" name="avg_type" required> <option value="day">День</option> <option value="hour">Час</option> <option value="15min">15 минут</option> </select> </div> <div class="form-group"> <button style="background-color: rgb(33, 33, 33); color: rgb(220, 220, 220); padding: 10px; font-size: 16px; align-items: center;" type="submit">Выгрузка</button> </div> </form> </nav> </body> </html>
export.php
export.php
<?php ini_set('display_errors', '1'); ini_set('display_startup_errors', '1'); error_reporting(32759); if ($_SERVER['REQUEST_METHOD'] == 'POST') { $start_date = $_POST['start_datetime']; $end_date = $_POST['end_datetime']; $file_type = $_POST['file_type']; $avg_type = $_POST['avg_type']; // Connect to the database $pgi = pg_connect("host=localhost port=5432 dbname=averobo user=averobo password=*****"); if(!$pgi) { die("Error!"); pg_close($pgi); } else { // Prepare and execute the query if ($avg_type == 'day') { $query = 'SELECT * FROM main_table WHERE date >= \''.$start_date.'\' AND date <= \''.$end_date.'\' ORDER BY date'; } elseif ($avg_type == 'hour') { $query = 'SELECT * FROM table_with_time WHERE (average = \'minimum_in_hour\' OR average = \'maximum_in_hour\' OR average = \'aver_hour\') AND date >= \''.$start_date.'\' AND date <= \''.$end_date.'\' ORDER BY date'; } elseif ($avg_type == '15min') { $query = 'SELECT * FROM table_with_time WHERE (average = \'minimum_in_15min\' OR average = \'maximum_in_15max\' OR average = \'aver_15min\') AND date >= \''.$start_date.'\' AND date <= \''.$end_date.'\' ORDER BY date'; } $result = pg_query($pgi, "$query"); if(!$result) { die("Wrong query!"); } else { if ($file_type == 'json') { header('Content-Type: application/json'); header('Content-Disposition: attachment; filename="data.json"'); $myarr = array(); $i = 0; while ($myrow = pg_fetch_assoc($result)) { $myarr[(string)$i." "] = stripslashes(json_encode($myrow, JSON_UNESCAPED_UNICODE)); $i++; } $str = stripslashes(str_replace(" \"", "\"", json_encode($myarr, JSON_UNESCAPED_UNICODE))); $str3 = str_replace(":\"{",":{",$str); echo stripslashes(str_replace("}\"","}",$str3)); } else if ($file_type == 'csv') { header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename="data.csv"'); $results = pg_fetch_all($result); $output = fopen('php://output', 'w'); if (!empty($results)) { // Get the headers fputcsv($output, array_keys($results[0])); // Output the data foreach ($results as $row) { foreach ($row as &$value) { $value = mb_convert_encoding($value, 'UTF-8', 'auto'); } fputcsv($output, $row); } } fclose($output); } } pg_close($pgi); } } else { echo 'Invalid request method'; } ?>
style.css
style.css
html, body { padding: 0px; margin: 0px; } body { width: 100vw; min-height: 100vh; overflow-x: hidden; display: flex; justify-content: center; align-items: center; flex-direction: column; background-color: rgb(33, 33, 33); } .nav { display: flex; flex-direction: column; width: 392px; align-items: flex-start; padding: 30px; border-radius: 3px; background-color: rgba(255, 255, 255, 0.05); font-family: 'Lucida Sans', 'Lucida Sans Regular', 'Lucida Grande', 'Lucida Sans Unicode', Geneva, Verdana, sans-serif; } .nav > h1 { width: 100%; margin: 0px; padding: 0px; font-size: 18px; border-bottom: 1px solid rgba(255, 255, 255, 0.125); padding-bottom: 10px; margin-bottom: 12px; font-weight: lighter; color: rgb(240, 240, 240); } .nav > h3 { width: 100%; margin: 0px; padding: 0px; font-size: 18px; border-bottom: 1px solid rgba(255, 255, 255, 0.125); padding-bottom: 10px; margin-bottom: 12px; font-weight: lighter; color: rgb(240, 240, 240); } .nav > h6 { width: 500px; margin: 0px; padding: 0px; font-size: 16px; border-bottom: 1px solid rgba(255, 255, 255, 0.125); padding-bottom: 10px; margin-bottom: 12px; font-weight: lighter; color: rgb(240, 240, 240); } .form-group label, .form-group input, .form-group select { display: block; font-size: 16px; color: rgb(220, 220, 220); margin-top: 8px; text-decoration: none; padding-bottom: 0px; } .nav > a { font-size: 16px; color: rgb(220, 220, 220); margin-top: 8px; text-decoration: none; padding-bottom: 0px; border-bottom: 1px solid rgb(100, 100, 100); transition: .25s; } .nav > a:hover { color: rgb(240, 240, 240); border-bottom: 1px solid rgb(160, 160, 160); }
stylereq.css
stylereq.css
html, body { padding: 0px; margin: 0px; } body { width: 100vw; min-height: 100vh; overflow-x: hidden; display: flex; justify-content: center; align-items: center; flex-direction: column; background-color: rgb(33, 33, 33); } .nav { display: flex; flex-direction: column; width: 500px; align-items: flex-start; padding: 30px; border-radius: 3px; background-color: rgba(255, 255, 255, 0.05); font-family: 'Lucida Sans', 'Lucida Sans Regular', 'Lucida Grande', 'Lucida Sans Unicode', Geneva, Verdana, sans-serif; } .nav > h3 { width: 100%; margin: 0px; padding: 0px; font-size: 18px; border-bottom: 1px solid rgba(255, 255, 255, 0.125); padding-bottom: 10px; margin-bottom: 12px; font-weight: lighter; color: rgb(240, 240, 240); } .nav > h6 { width: 500px; margin: 0px; padding: 0px; font-size: 18px; border-bottom: 1px solid rgba(255, 255, 255, 0.125); padding-bottom: 2px; margin-bottom: 12px; font-weight: lighter; color: rgb(240, 240, 240); } .nav > a { font-size: 16px; color: rgb(220, 220, 220); margin-top: 8px; text-decoration: none; padding-bottom: 2px; margin-bottom: 10px; border-bottom: 1px solid rgb(100, 100, 100); transition: .25s; } .nav > a:hover { color: rgb(240, 240, 240); border-bottom: 1px solid rgb(160, 160, 160); }