Инструменты пользователя
doc:2003:mgul.200300.001.1201
Различия
Здесь показаны различия между двумя версиями данной страницы.
| Следующая версия | Предыдущая версия | ||
|
doc:2003:mgul.200300.001.1201 [2024/08/10 17:05] daftwi создано |
doc:2003:mgul.200300.001.1201 [2024/08/11 22:53] (текущий) daftwi [Корневой каталог веб-приложения] |
||
|---|---|---|---|
| Строка 6: | Строка 6: | ||
| ===== Корневой каталог веб-приложения ===== | ===== Корневой каталог веб-приложения ===== | ||
| - | <hidden conn.php> | + | <hidden index.html><code> |
| + | <!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> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden conn.php><code> | ||
| <?php | <?php | ||
| function cmp($a, $b) | function cmp($a, $b) | ||
| Строка 124: | Строка 149: | ||
| pg_close($pgi); | pg_close($pgi); | ||
| ?> | ?> | ||
| - | </hidden> | + | </code></hidden> |
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden conn_hour.php><code> | ||
| + | <?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); | ||
| + | ?> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden conn_minutes.php><code> | ||
| + | <?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 \'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); | ||
| + | ?> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden request.html><code> | ||
| + | <!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> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden averequest.php><code> | ||
| + | <?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); | ||
| + | } | ||
| + | ?> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden fileexport.html><code> | ||
| + | <!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> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden export.php><code> | ||
| + | <?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'; | ||
| + | } | ||
| + | ?> | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden style.css><code> | ||
| + | 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); | ||
| + | } | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden stylereq.css><code> | ||
| + | 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); | ||
| + | } | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | ====== ПРОГРАММНЫЙ КОД АВТОМАТИЗАЦИИ ====== | ||
| + | |||
| + | ===== Архив с кодом автоматизации и библиотеками ===== | ||
| + | |||
| + | {{ :doc:2003:filesfordoku.zip | Server}} | ||
| + | |||
| + | ===== Текст кода библиотек ===== | ||
| + | |||
| + | <hidden lib2><code> | ||
| + | import json | ||
| + | import datetime | ||
| + | import string | ||
| + | import psycopg2 | ||
| + | import requests | ||
| + | |||
| + | def is_float(a_string): | ||
| + | if a_string is None: | ||
| + | return False | ||
| + | try: | ||
| + | float(a_string) | ||
| + | return True | ||
| + | except ValueError: | ||
| + | return False | ||
| + | |||
| + | |||
| + | class Baz(): | ||
| + | "Stores name, serial and date" | ||
| + | def __init__(self, name, serial, dt): | ||
| + | self.name = name | ||
| + | self.serial = serial | ||
| + | self.dt = dt | ||
| + | |||
| + | def mindata(j: dict) -> dict: | ||
| + | """ | ||
| + | This function is finding min for new data | ||
| + | """ | ||
| + | vec = [] | ||
| + | data = [] | ||
| + | fin = [] | ||
| + | t = 0 | ||
| + | dictData = j | ||
| + | """ | ||
| + | writing in file for debug | ||
| + | with open("testfile2.json", "a") as f: | ||
| + | f.write(json.dumps(dictData)) | ||
| + | """ | ||
| + | for i in dictData: | ||
| + | "relocating names and dates in vector, will be using as set" | ||
| + | dictDatai = dictData[i] | ||
| + | t = 0 | ||
| + | for s in vec: | ||
| + | if (s.name == dictDatai["uName"] and s.serial == dictDatai["serial"] and dictDatai["Date"][0:10] == s.dt) or dictDatai["uName"].find("Сервер") != -1: | ||
| + | t = 1 | ||
| + | if t == 0: | ||
| + | vec.append(Baz(dictDatai["uName"],dictDatai["serial"],str(dictDatai["Date"][0:10]))) | ||
| + | """ | ||
| + | debug | ||
| + | with open("testfile3.json", "a") as f: | ||
| + | f.write(json.dumps(dictDatai, ensure_ascii=False)); | ||
| + | """ | ||
| + | for k in vec: | ||
| + | "creating vector of data for each pribor at each date" | ||
| + | data = [] | ||
| + | for i in dictData: | ||
| + | dictDatai = dictData[i] | ||
| + | if dictDatai["uName"] == k.name and dictDatai["serial"] == k.serial and dictDatai["Date"][0:10] == k.dt: | ||
| + | data.append(dictDatai["data"]) | ||
| + | "working with date" | ||
| + | if len(data) != 0: | ||
| + | for i in data: | ||
| + | fl = False | ||
| + | while fl == False: | ||
| + | fl = True | ||
| + | for h in i: | ||
| + | if "system" == h[0:6] or h[0:3] == "RTC" or h[0:3] == "NTP" or h.find("calibr_date") != -1: | ||
| + | i.pop(h) | ||
| + | fl = False | ||
| + | break | ||
| + | if len(data) != 0: | ||
| + | "comparison" | ||
| + | for h in data[0]: | ||
| + | if not is_float(data[0][h]): | ||
| + | data[0][h] = None | ||
| + | elif h.find("temp") != -1 and h.find("color_tempCT") == -1 and (float(data[0][h]) < -100 or float(data[0][h]) > 100): | ||
| + | data[0][h] = None | ||
| + | elif h.find("pressure") != -1 and (float(data[0][h]) < 100 or float(data[0][h]) > 1000): | ||
| + | data[0][h] = None | ||
| + | elif h.find("humidity") != -1 and (float(data[0][h]) < 0 or float(data[0][h]) > 100): | ||
| + | data[0][h] = None | ||
| + | for i in range(1,len(data)): | ||
| + | if h in data[i]: | ||
| + | tr = 0 | ||
| + | if not is_float(data[i][h]): | ||
| + | tr = 1 | ||
| + | elif h.find("temp") != -1 and h.find("color_tempCT") == -1 and (float(data[i][h]) < -100 or float(data[i][h]) > 100): | ||
| + | tr = 1 | ||
| + | elif h.find("pressure") != -1 and (float(data[i][h]) < 100 or float(data[i][h]) > 1000): | ||
| + | tr = 1 | ||
| + | elif h.find("humidity") != -1 and (float(data[i][h]) < 0 or float(data[i][h]) > 100): | ||
| + | tr = 1 | ||
| + | if tr == 0: | ||
| + | if data[0][h] is None: | ||
| + | data[0][h] = round(float(data[i][h]), 4) | ||
| + | else: | ||
| + | data[0][h] = round(float(min(float(data[0][h]), float(data[i][h]))), 4) | ||
| + | "forming result" | ||
| + | final = dict(Date= k.dt, uName = k.name, serial = k.serial, data = data[0]) | ||
| + | fin.append(final) | ||
| + | fe = {} | ||
| + | for i in range(len(fin)): | ||
| + | fe.update({str(i): fin[i]}) | ||
| + | return fe | ||
| + | |||
| + | def maxdata(j: dict) -> dict: | ||
| + | """ | ||
| + | This function is finding max new data | ||
| + | same system as min | ||
| + | """ | ||
| + | vec = [] | ||
| + | data = [] | ||
| + | fin = [] | ||
| + | t = 0 | ||
| + | dictData = j | ||
| + | """ | ||
| + | debug | ||
| + | with open("testfile2.json", "a") as f: | ||
| + | f.write(json.dumps(dictData)) | ||
| + | """ | ||
| + | for i in dictData: | ||
| + | dictDatai = dictData[i] | ||
| + | t = 0 | ||
| + | for s in vec: | ||
| + | if (s.name == dictDatai["uName"] and s.serial == dictDatai["serial"] and s.dt == dictDatai["Date"][0:10]) or dictDatai["uName"][0:6] == "Сервер": | ||
| + | t = 1 | ||
| + | if t == 0: | ||
| + | vec.append(Baz(dictDatai["uName"],dictDatai["serial"],str(dictDatai["Date"][0:10]))) | ||
| + | """ | ||
| + | debug | ||
| + | with open("testfile3.json", "a") as f: | ||
| + | f.write(json.dumps(dictDatai, ensure_ascii=False)); | ||
| + | """ | ||
| + | for k in vec: | ||
| + | data = [] | ||
| + | for i in dictData: | ||
| + | dictDatai = dictData[i] | ||
| + | if dictDatai["uName"] == k.name and dictDatai["serial"] == k.serial and dictDatai["Date"][0:10] == k.dt: | ||
| + | data.append(dictDatai["data"]) | ||
| + | if len(data) != 0: | ||
| + | for i in data: | ||
| + | fl = False | ||
| + | while fl == False: | ||
| + | fl = True | ||
| + | for h in i: | ||
| + | if "system" == h[0:6] or h[0:3] == "RTC" or h[0:3] == "NTP" or h.find("calibr_date") != -1: | ||
| + | i.pop(h) | ||
| + | fl = False | ||
| + | break | ||
| + | if len(data) != 0: | ||
| + | for h in data[0]: | ||
| + | if not is_float(data[0][h]): | ||
| + | data[0][h] = None | ||
| + | elif h.find("temp") != -1 and h.find("color_tempCT") == -1 and (float(data[0][h]) < -100 or float(data[0][h]) > 100): | ||
| + | data[0][h] = None | ||
| + | elif h.find("pressure") != -1 and (float(data[0][h]) < 100 or float(data[0][h]) > 1000): | ||
| + | data[0][h] = None | ||
| + | elif h.find("humidity") != -1 and (float(data[0][h]) < 0 or float(data[0][h]) > 100): | ||
| + | data[0][h] = None | ||
| + | for i in range(1,len(data)): | ||
| + | if h in data[i]: | ||
| + | tr = 0 | ||
| + | if not is_float(data[i][h]): | ||
| + | tr = 1 | ||
| + | elif h.find("temp") != -1 and h.find("color_tempCT") == -1 and (float(data[i][h]) < -100 or float(data[i][h]) > 100): | ||
| + | tr = 1 | ||
| + | elif h.find("pressure") != -1 and (float(data[i][h]) < 100 or float(data[i][h]) > 1000): | ||
| + | tr = 1 | ||
| + | elif h.find("humidity") != -1 and (float(data[i][h]) < 0 or float(data[i][h]) > 100): | ||
| + | tr = 1 | ||
| + | if tr == 0: | ||
| + | if data[0][h] is None: | ||
| + | data[0][h] = round(float(data[i][h]), 4) | ||
| + | else: | ||
| + | data[0][h] = round(float(max(float(data[0][h]), float(data[i][h]))), 4) | ||
| + | final = dict(Date= k.dt, uName = k.name, serial = k.serial, data = data[0]) | ||
| + | fin.append(final) | ||
| + | fe = {} | ||
| + | for i in range(len(fin)): | ||
| + | fe.update({str(i): fin[i]}) | ||
| + | return fe | ||
| + | |||
| + | def halfpath15(j: dict) -> dict: | ||
| + | """ | ||
| + | This function is finding sum and amount in 15 minutes | ||
| + | """ | ||
| + | vec = [] | ||
| + | data = [] | ||
| + | fin = [] | ||
| + | t = 0 | ||
| + | dictData = j | ||
| + | """ | ||
| + | debug | ||
| + | with open("testfile2.json", "a") as f: | ||
| + | f.write(json.dumps(dictData)) | ||
| + | """ | ||
| + | for i in dictData: | ||
| + | "appending our set of names and dates" | ||
| + | dictDatai = dictData[i] | ||
| + | t = 0 | ||
| + | for s in vec: | ||
| + | if (s.name == dictDatai["uName"] and s.serial == dictDatai["serial"] and s.dt == dictDatai["Date"][0:10]) or dictDatai["uName"][0:6] == "Сервер": | ||
| + | t = 1 | ||
| + | if t == 0: | ||
| + | vec.append(Baz(dictDatai["uName"],dictDatai["serial"],str(dictDatai["Date"][0:10]))) | ||
| + | """ | ||
| + | debug | ||
| + | with open("testfile3.json", "a") as f: | ||
| + | f.write(json.dumps(dictDatai, ensure_ascii=False)); | ||
| + | """ | ||
| + | for k in vec: | ||
| + | "working with data and deleting system info" | ||
| + | tempk = k.name | ||
| + | if (k.name[0:6] != "Сервер"): | ||
| + | data = [] | ||
| + | for i in dictData: | ||
| + | dictDatai = dictData[i] | ||
| + | if dictDatai["uName"] == k.name and dictDatai["serial"] == k.serial and dictDatai["Date"][0:10] == k.dt: | ||
| + | data.append(dictDatai["data"]) | ||
| + | if len(data) != 0: | ||
| + | for i in data: | ||
| + | fl = False | ||
| + | while fl == False: | ||
| + | fl = True | ||
| + | for h in i: | ||
| + | if "system" == h[0:6] or h[0:3] == "RTC" or h[0:3] == "NTP" or h.find("calibr_date") != -1: | ||
| + | i.pop(h) | ||
| + | fl = False | ||
| + | break | ||
| + | templendata = len(data) | ||
| + | tempdata = {} | ||
| + | if templendata != 0: | ||
| + | "averaging" | ||
| + | for h in data[0]: | ||
| + | lena = 1 | ||
| + | if not is_float(data[0][h]): | ||
| + | data[0][h] = "0" | ||
| + | lena -= 1 | ||
| + | elif h.find("temp") != -1 and h.find("color_tempCT") == -1 and (float(data[0][h]) < -100 or float(data[0][h]) > 100): | ||
| + | data[0][h] = "0" | ||
| + | lena -= 1 | ||
| + | elif h.find("pressure") != -1 and (float(data[0][h]) < 100 or float(data[0][h]) > 1000): | ||
| + | data[0][h] = "0" | ||
| + | lena -= 1 | ||
| + | elif h.find("humidity") != -1 and (float(data[0][h]) < 0 or float(data[0][h]) > 100): | ||
| + | data[0][h] = "0" | ||
| + | lena -= 1 | ||
| + | sum = float(data[0][h]) | ||
| + | for i in range(1,templendata): | ||
| + | if h in data[i]: | ||
| + | tr = 0 | ||
| + | if not is_float(data[i][h]): | ||
| + | tr = 1 | ||
| + | elif h.find("temp") != -1 and h.find("color_tempCT") == -1 and (float(data[i][h]) < -100 or float(data[i][h]) > 100): | ||
| + | tr = 1 | ||
| + | elif h.find("pressure") != -1 and (float(data[i][h]) < 100 or float(data[i][h]) > 1000): | ||
| + | tr = 1 | ||
| + | elif h.find("humidity") != -1 and (float(data[i][h]) < 0 or float(data[i][h]) > 100): | ||
| + | tr = 1 | ||
| + | if tr == 0: | ||
| + | lena += 1 | ||
| + | sum += float(data[i][h]) | ||
| + | data[0][h] = round(sum, 4) | ||
| + | tempdata.update({str(h): lena}) | ||
| + | # print(data[0], data[1]) | ||
| + | final = dict(Date= k.dt, uName = k.name, serial = k.serial, summa = data[0], length = tempdata) | ||
| + | # print(final) | ||
| + | fin.append(final) | ||
| + | fe = {} | ||
| + | for i in range(len(fin)): | ||
| + | fe.update({str(i): fin[i]}) | ||
| + | return fe | ||
| + | |||
| + | def aver15(j: dict) -> dict: | ||
| + | """ | ||
| + | This function is finding average in 15 minutes | ||
| + | """ | ||
| + | fin = [] | ||
| + | dictData = j | ||
| + | """ | ||
| + | debug | ||
| + | with open("testfile2.json", "a") as f: | ||
| + | f.write(json.dumps(dictData)) | ||
| + | """ | ||
| + | for i in dictData: | ||
| + | "appending our set of names and dates" | ||
| + | dictDatai = dictData[i] | ||
| + | data = dict() | ||
| + | for t in dictDatai["summa"]: | ||
| + | # k = dictDatai["summa"][t] | ||
| + | # print(dictDatai["uName"], t, dictDatai["length"][t]) | ||
| + | if int(dictDatai["length"][t]) == 0: | ||
| + | data[t] = 0.0000 | ||
| + | else: | ||
| + | data[t] = round(float(dictDatai["summa"][t])/int(dictDatai["length"][t]), 4) | ||
| + | final = dict(Date= dictDatai["Date"], uName = dictDatai["uName"], serial = dictDatai["serial"], avr = data) | ||
| + | fin.append(final) | ||
| + | fe = {} | ||
| + | for i in range(len(fin)): | ||
| + | fe.update({str(i): fin[i]}) | ||
| + | return fe | ||
| + | |||
| + | def uploadolddata(y, m: string) -> json: | ||
| + | "uploading old data from archive" | ||
| + | with open('/home/alenanaz/db_all_' + y + '_'+ m +'.json') as f: | ||
| + | templates = json.load(f) | ||
| + | return templates | ||
| + | |||
| + | def insert15min(p: dict, dbn, usavr, passavr, hostavr, tabavr, tabhp, datetime: string): | ||
| + | """ | ||
| + | inserting avr in data base | ||
| + | """ | ||
| + | p1 = aver15(p); | ||
| + | "connecting to the data base" | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | "sql queries" | ||
| + | for i in p: | ||
| + | if (p[i]["uName"][0:6] != "Сервер"): | ||
| + | cursor.execute(f'INSERT INTO {tabhp} VALUES(%s, %s, %s, %s, %s)', (datetime, (str(p[i]["uName"]) + ' ' + str(p[i]["serial"])), "half_path_15min", json.dumps(p[i]["summa"], ensure_ascii=False), json.dumps(p[i]["length"], ensure_ascii=False))) | ||
| + | for i in p1: | ||
| + | if (p[i]["uName"][0:6] != "Сервер"): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (datetime, (str(p1[i]["uName"]) + ' ' + str(p1[i]["serial"])), "aver_15min", json.dumps(p1[i]["avr"], ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | def insertmin15(p: dict, dbn, usavr, passavr, hostavr, tabavr, datetime: string): | ||
| + | """ | ||
| + | inserting min in data base | ||
| + | """ | ||
| + | "connecting to the data base" | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | "sql queries" | ||
| + | for i in p: | ||
| + | if (p[i]["uName"][0:6] != "Сервер"): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (datetime, (str(p[i]["uName"])+' '+str(p[i]["serial"])), "minimum_in_15min", json.dumps(p[i]["data"], ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | def insertmax15(p: dict, dbn, usavr, passavr, hostavr, tabavr, datetime: string): | ||
| + | """ | ||
| + | insert max in db | ||
| + | """ | ||
| + | "connecting to db" | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | "sql queries" | ||
| + | for i in p: | ||
| + | if (p[i]["uName"][0:6] != "Сервер"): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (datetime, (str(p[i]["uName"])+' '+str(p[i]["serial"])), "maximum_in_15max", json.dumps(p[i]["data"], ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | </code></hidden> | ||
| + | |||
| + | |||
| + | ---- | ||
| + | |||
| + | <hidden lib3><code> | ||
| + | import json | ||
| + | import datetime | ||
| + | import string | ||
| + | import psycopg2 | ||
| + | |||
| + | class Baz(): | ||
| + | "Stores name, serial and date" | ||
| + | def __init__(self, name, serial, dt): | ||
| + | self.name = name | ||
| + | self.serial = serial | ||
| + | self.dt = dt | ||
| + | |||
| + | def insertavghour(dbn, usavr, passavr, hostavr, tabavr, tabhp, dt: string): | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | dt1 = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S') | ||
| + | cursor.execute(f'SELECT a.name, parametr, SUM( CAST(sum->>parametr AS NUMERIC)) AS summa, SUM(CAST(num->>parametr AS INTEGER)) AS numbers FROM {tabhp} INNER JOIN (SELECT name, JSON_OBJECT_KEYS(sum) AS parametr FROM {tabhp} GROUP BY name, parametr) AS a ON a.name = {tabhp}.name WHERE (\'{dt1}\' - date >= \'00:00:00\') AND (\'{dt1}\' - date < \'01:00:00\') AND average = \'half_path_15min\' GROUP BY a.name,parametr ORDER BY a.name') | ||
| + | arr = tuple() | ||
| + | arr = cursor.fetchall() | ||
| + | dir_sum = {} | ||
| + | dir_num = {} | ||
| + | dir_avg = {} | ||
| + | name = "" | ||
| + | dt = str(dt1 - datetime.timedelta(minutes=8)) | ||
| + | i = 0 | ||
| + | for p in arr: | ||
| + | if(p[1] != None and p[2] != None and p[3] != None): | ||
| + | if (i == 0 or name == p[0]): | ||
| + | dir_sum[p[1]] = float(p[2]) | ||
| + | dir_num[p[1]] = float(p[3]) | ||
| + | if(p[3] != 0): | ||
| + | dir_avg[p[1]] = float(round(p[2]/p[3], 4)) | ||
| + | else: | ||
| + | if(p[3] != 0): | ||
| + | cursor.execute(f'INSERT INTO {tabhp} VALUES(%s, %s, %s, %s, %s)', (dt, (str(name)), "half_path_hour", json.dumps(dir_sum, ensure_ascii=False), json.dumps(dir_num, ensure_ascii=False))) | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), "aver_hour", json.dumps(dir_avg, ensure_ascii=False))) | ||
| + | dir_sum.clear() | ||
| + | dir_num.clear() | ||
| + | dir_avg.clear() | ||
| + | dir_sum[p[1]] = float(p[2]) | ||
| + | dir_num[p[1]] = float(p[3]) | ||
| + | if(p[3] != 0): | ||
| + | dir_avg[p[1]] = float(round(p[2]/p[3], 4)) | ||
| + | name = p[0] | ||
| + | i += 1 | ||
| + | if(i == len(arr) and p[3] != 0): | ||
| + | cursor.execute(f'INSERT INTO {tabhp} VALUES(%s, %s, %s, %s, %s)', (dt, (str(name)), "half_path_hour", json.dumps(dir_sum, ensure_ascii=False), json.dumps(dir_num, ensure_ascii=False))) | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), "aver_hour", json.dumps(dir_avg, ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | def insertmaxhour(dbn, usavr, passavr, hostavr, tabavr, dt: string): | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | dt1 = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S') | ||
| + | cursor.execute(f'select a.name, parametr, max( cast(json_file->>parametr as numeric)) as max from {tabavr} inner join (select name, json_object_keys(json_file) as parametr from {tabavr} group by name, parametr) as a on a.name = {tabavr}.name where (\'{dt1}\' - date >= \'00:00:00\') and (\'{dt1}\' - date < \'01:00:00\') and average = \'maximum_in_15max\' group by a.name,parametr order by a.name') | ||
| + | arr = tuple() | ||
| + | arr = cursor.fetchall() | ||
| + | dir_max = {} | ||
| + | name = "" | ||
| + | i = 0 | ||
| + | dt = str(dt1 - datetime.timedelta(minutes=8)) | ||
| + | for p in arr: | ||
| + | if(p[1] != None and p[2] != None): | ||
| + | if (i == 0 or name == p[0]): | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | else: | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'maximum_in_hour', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | dir_max.clear() | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | |||
| + | name = p[0] | ||
| + | i += 1 | ||
| + | if(i == len(arr)): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'maximum_in_hour', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | def insertminhour(dbn, usavr, passavr, hostavr, tabavr, dt: string): | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | dt1 = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S') | ||
| + | cursor.execute(f'select a.name, parametr, min( cast(json_file->>parametr as numeric)) as min from {tabavr} inner join (select name, json_object_keys(json_file) as parametr from {tabavr} group by name, parametr) as a on a.name = {tabavr}.name where (\'{dt1}\' - date >= \'00:00:00\') and (\'{dt1}\' - date < \'01:00:00\') and average = \'minimum_in_15min\' group by a.name,parametr order by a.name') | ||
| + | arr = tuple() | ||
| + | arr = cursor.fetchall() | ||
| + | dir_max = {} | ||
| + | name = "" | ||
| + | i = 0 | ||
| + | dt = str(dt1 - datetime.timedelta(minutes=8)) | ||
| + | for p in arr: | ||
| + | if(p[1] != None and p[2] != None): | ||
| + | if (i == 0 or name == p[0]): | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | else: | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'minimum_in_hour', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | dir_max.clear() | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | |||
| + | name = p[0] | ||
| + | i += 1 | ||
| + | if(i == len(arr)): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'minimum_in_hour', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | #days | ||
| + | |||
| + | def insertavgday(dbn, usavr, passavr, hostavr, tabavr, tabhp, dt: string): | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | dt1 = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S') | ||
| + | cursor.execute(f'SELECT a.name, parametr, SUM( CAST(sum->>parametr AS NUMERIC)) AS summa, SUM(CAST(num->>parametr AS NUMERIC)) AS numbers FROM {tabhp} INNER JOIN (SELECT name, JSON_OBJECT_KEYS(sum) AS parametr FROM {tabhp} GROUP BY name, parametr) AS a ON a.name = {tabhp}.name WHERE (\'{dt1}\' - date >= \'0:00:00\') AND (\'{dt1}\' - date < \'23:55:00\') AND average = \'half_path_hour\' GROUP BY a.name,parametr ORDER BY a.name') | ||
| + | arr = tuple() | ||
| + | arr = cursor.fetchall() | ||
| + | dir_sum = {} | ||
| + | dir_num = {} | ||
| + | dir_avg = {} | ||
| + | name = "" | ||
| + | dt = str(dt1 - datetime.timedelta(days=1))[0:10] | ||
| + | i = 0 | ||
| + | for p in arr: | ||
| + | if(p[1] != None and p[2] != None and p[3] != None): | ||
| + | if (i == 0 or name == p[0]): | ||
| + | dir_sum[p[1]] = float(p[2]) | ||
| + | dir_num[p[1]] = float(p[3]) | ||
| + | if(p[3] != 0): | ||
| + | dir_avg[p[1]] = float(round(p[2]/p[3], 4)) | ||
| + | else: | ||
| + | if(p[3] != 0): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), "average_of_day", json.dumps(dir_avg, ensure_ascii=False))) | ||
| + | dir_sum.clear() | ||
| + | dir_num.clear() | ||
| + | dir_avg.clear() | ||
| + | dir_sum[p[1]] = float(p[2]) | ||
| + | dir_num[p[1]] = float(p[3]) | ||
| + | dir_avg[p[1]] = float(round(p[2]/p[3], 4)) | ||
| + | name = p[0] | ||
| + | i += 1 | ||
| + | if(i == len(arr) and p[3] != 0): | ||
| + | cursor.execute(f'INSERT INTO {tabavr} VALUES(%s, %s, %s, %s)', (dt, (str(name)), "average_of_day", json.dumps(dir_avg, ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | def insertmaxday(dbn, usavr, passavr, hostavr, tabavr, tabmn, dt: string): | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | dt1 = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S') | ||
| + | cursor.execute(f'select a.name, parametr, max( cast(json_file->>parametr as numeric)) as max from {tabavr} inner join (select name, json_object_keys(json_file) as parametr from {tabavr} group by name, parametr) as a on a.name = {tabavr}.name where (\'{dt1}\' - date >= \'0:00:00\') and (\'{dt1}\' - date < \'23:55:00\') and average = \'maximum_in_hour\' group by a.name,parametr order by a.name') | ||
| + | arr = tuple() | ||
| + | arr = cursor.fetchall() | ||
| + | dir_max = {} | ||
| + | name = "" | ||
| + | i = 0 | ||
| + | dt = str(dt1 - datetime.timedelta(days=1))[0:10] | ||
| + | for p in arr: | ||
| + | if(p[1] != None and p[2] != None): | ||
| + | if (i == 0 or name == p[0]): | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | else: | ||
| + | cursor.execute(f'INSERT INTO {tabmn} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'maximum_in_day', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | dir_max.clear() | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | |||
| + | name = p[0] | ||
| + | i += 1 | ||
| + | if(i == len(arr)): | ||
| + | cursor.execute(f'INSERT INTO {tabmn} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'maximum_in_day', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | |||
| + | def insertminday(dbn, usavr, passavr, hostavr, tabavr, tabmn, dt: string): | ||
| + | conn = psycopg2.connect(dbname=dbn, user=usavr,password=passavr, host=hostavr) | ||
| + | cursor = conn.cursor() | ||
| + | dt1 = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S') | ||
| + | cursor.execute(f'select a.name, parametr, min( cast(json_file->>parametr as numeric)) as min from {tabavr} inner join (select name, json_object_keys(json_file) as parametr from {tabavr} group by name, parametr) as a on a.name = {tabavr}.name where (\'{dt1}\' - date >= \'0:00:00\') and (\'{dt1}\' - date < \'23:55:00\') and average = \'minimum_in_hour\' group by a.name,parametr order by a.name') | ||
| + | arr = tuple() | ||
| + | arr = cursor.fetchall() | ||
| + | dir_max = {} | ||
| + | name = "" | ||
| + | i = 0 | ||
| + | dt = str(dt1 - datetime.timedelta(days=1))[0:10] | ||
| + | for p in arr: | ||
| + | if(p[1] != None and p[2] != None): | ||
| + | if (i == 0 or name == p[0]): | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | else: | ||
| + | cursor.execute(f'INSERT INTO {tabmn} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'minimum_in_day', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | dir_max.clear() | ||
| + | dir_max[p[1]] = float(p[2]) | ||
| + | |||
| + | name = p[0] | ||
| + | i += 1 | ||
| + | if(i == len(arr)): | ||
| + | cursor.execute(f'INSERT INTO {tabmn} VALUES(%s, %s, %s, %s)', (dt, (str(name)), 'minimum_in_day', json.dumps(dir_max, ensure_ascii=False))) | ||
| + | conn.commit() | ||
| + | </code></hidden> | ||
| ---- | ---- | ||
doc/2003/mgul.200300.001.1201.1723298738.txt.gz · Последние изменения: 2024/08/10 17:05 — daftwi
Инструменты страницы
Если не указано иное, содержимое этой вики предоставляется на условиях следующей лицензии: CC Attribution-Share Alike 4.0 International
