web/archive.php

   1 <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
   2 <html>
   3 <head>
   4   <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
   5   <meta name="GENERATOR" content="Mozilla/4.72 [cp1251] (X11; U; Linux 2.2.12-20smp i686) [Netscape]">
   6   <title>WeatherMon (архив)</title>
   7   <link rel="icon" href="favicon.png" />
   8 </head>
   9 <body text="black" bgcolor="silver" link="blue" vlink="#000080" alink="#FF0000">
  10 <?php
  11
  12 include('config_local.php');
  13 include('calendar.php');
  14
  15 $year = $_REQUEST['year'];
  16 $month = $_REQUEST['month'];
  17 $day = $_REQUEST['day'];
  18
  19 if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
  20   die($err);
  21   }
  22
  23 $db -> exec('SET CHARACTER SET utf8');
  24
  25 if (! $year) {
  26
  27   $q = $db -> prepare(
  28     'select
  29        date_format(timestamp,\'%Y\') as year
  30      from
  31        sensor_values
  32      group by year
  33      order by year');
  34   $q -> execute();
  35 ?>
  36 <h1>Архив метеоданных</h1>
  37 <?php
  38   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
  39 ?>
  40   <a href="?year=<?php echo $row['year']; ?>">Данные за <?php echo $row['year']; ?> год</a><br>
  41 <?php
  42   }
  43
  44 } else {
  45
  46   if (! $month) {
  47
  48     $next_year = $year+1;
  49     $year      = sprintf('%04d',$year);
  50     $next_year = sprintf('%04d',$next_year);
  51     $q = $db -> prepare(
  52           'select
  53              date_format(timestamp,\'%m\') as month,
  54              date_format(timestamp,\'%d\') as day
  55            from
  56              sensor_values
  57            where
  58              timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and
  59              timestamp<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
  60            group by month,day
  61            order by month,day'
  62           );
  63     $q -> execute();
  64 ?>
  65 <h1 align="center"><?php echo $year; ?> год</h1>
  66 <?php
  67     $data = $q -> fetchAll(PDO::FETCH_ASSOC);
  68     $months = [];
  69     foreach ($data as $row) {
  70       $days [$year.$row['month'].$row['day']] = '&month='.$row['month'].'&day='.$row['day'];
  71       $months[$row['month']]=1;
  72     }
  73     $m = 1;
  74     echo '<table align="center">';
  75     for ($i=1; $i<=3; $i++) {
  76       echo '<tr>';
  77       for ($j=1; $j<=4; $j++) {
  78         echo '<td valign="top">';
  79         $month=sprintf('%02d',$m);
  80         $month_name = strftime('%B',mktime(0,0,0,$month,1,$year));
  81         if (!empty($months[$month])) {
  82           echo '<h3 align="center"><a href="?year='.$year.'&month='.$month.'">'.$month_name.'</a></h3>';
  83         } else {
  84           echo '<h3 align="center">'.$month_name.'</h3>';
  85         }
  86         calendar($year,$month,$days,'?year='.$year.'%s',0);
  87         echo '</td>';
  88         $m++;
  89         }
  90       echo '</tr>';
  91     }
  92     echo '</table>';
  93
  94     $next_year = $year+1;
  95
  96     $q = $db -> prepare (
  97       'select
  98          s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name,
  99          count(distinct date_format(v.timestamp,\'%d\')) cnt,
 100          round(min(v.value),1) min_value,
 101          round(max(v.value),1) max_value,
 102          u.name_short unit
 103        from
 104          sensor_values v,sensors s,st_parameters p,units u
 105        where
 106          v.sensor_id=s.id and
 107          v.parameter_id=p.id and
 108          p.st_unit=u.id and
 109          v.timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and
 110          v.timestamp<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
 111       group by s.id,p.id,s.s_description,p.st_description
 112       order by s.id,p.id'
 113       );
 114
 115     $q -> execute();
 116     $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
 117
 118     foreach ($sensors as $sensor) {
 119
 120       echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
 121       echo '<table align="center">';
 122       echo '<tr><td>';
 123       echo 'Минимальное значение за год <b>'.$sensor['min_value'].' '.$sensor['unit'].'</b><br>';
 124       echo 'Максимальное значение за год <b>'.$sensor['max_value'].' '.$sensor['unit'].'</b><br>';
 125       echo '</td></tr>';
 126       echo '</table>';
 127
 128       if ($sensor['cnt']>1) {
 129
 130         echo '<center><img src="image_minmax.php?sensor='.$sensor['sensor'].'&param='.$sensor['param'].
 131           '&type=year&year='.$year.'"></center>';
 132
 133       }
 134
 135     }
 136
 137
 138   } else {
 139
 140     if (!$day) {
 141
 142       $next_year = $year;
 143       $next_month = $month+1;
 144
 145       if ($next_month == 13) {
 146         $next_month = 1;
 147         $next_year = $next_year+1;
 148       }
 149
 150       $next_month = sprintf('%02d',$next_month);
 151       $next_year  = sprintf('%04d',$next_year);
 152       $month      = sprintf('%02d',$month);
 153       $year      = sprintf('%04d',$year);
 154
 155       $q = $db -> prepare(
 156           'select
 157              date_format(timestamp,\'%d\') as day
 158            from
 159              sensor_values
 160            where
 161              timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and
 162              timestamp<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
 163            group by day
 164            order by day'
 165           );
 166       $q -> execute();
 167
 168       $data = $q -> fetchAll(PDO::FETCH_ASSOC);
 169
 170       $month_name = strftime('%B %Y',mktime(0,0,0,$month,1,$year));
 171
 172 ?>
 173 <h1 align="center"><?php echo 'Данные за '.$month_name; ?></h1>
 174 <?php
 175       foreach ($data as $row) {
 176         $days [$year.$month.$row['day']] = $row['day'];
 177       }
 178
 179       calendar($year,$month,$days,'?year='.$year.'&month='.$month.'&day=%s',3);
 180
 181       $q = $db -> prepare (
 182           'select
 183              s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name,
 184              count(distinct date_format(v.timestamp,\'%d\')) cnt,
 185              round(min(v.value),1) min_value,
 186              round(max(v.value),1) max_value,
 187              u.name_short unit
 188            from
 189              sensor_values v,sensors s,st_parameters p,units u
 190            where
 191              v.sensor_id=s.id and
 192              v.parameter_id=p.id and
 193              p.st_unit=u.id and
 194              v.timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and
 195              v.timestamp<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
 196            group by s.id,p.id,s.s_description,p.st_description
 197            order by s.id,p.id'
 198         );
 199
 200       $q -> execute();
 201       $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
 202
 203       foreach ($sensors as $sensor) {
 204
 205         echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
 206         echo '<table align="center">';
 207         echo '<tr><td>';
 208         echo 'Минимальное значение за месяц <b>'.$sensor['min_value'].' '.$sensor['unit'].'</b><br>';
 209         echo 'Максимальное значение за месяц <b>'.$sensor['max_value'].' '.$sensor['unit'].'</b><br>';
 210         echo '</td></tr>';
 211         echo '</table>';
 212
 213         if ($sensor['cnt']>1) {
 214
 215           echo '<center><img src="image_minmax.php?sensor='.$sensor['sensor'].'&param='.$sensor['param'].
 216             '&type=month&year='.$year.'&month='.$month.'"></center>';
 217
 218         }
 219
 220       }
 221
 222     } else {
 223
 224       $month      = sprintf('%02d',$month);
 225       $year      = sprintf('%04d',$year);
 226       $day       = sprintf('%02d',$day);
 227
 228       $q = $db -> prepare(
 229             'select
 230                 distinct v.sensor_id,s.s_description,p.id as param_id,p.st_description
 231              from
 232                 sensor_values v,st_parameters p,sensors s
 233              where
 234                 v.timestamp>=str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\') and
 235                 v.timestamp<date_add(str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\'),interval 1 day)
 236                 and v.sensor_id=s.id
 237                 and s.st_id=p.st_id'
 238             );
 239       $q -> execute();
 240
 241       while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
 242
 243         echo '<h3 align="center">'.$row['s_description'].'/'.$row['st_description'].'</h3>';
 244 ?>
 245 <center><img src="image.php?sensor=<?php echo $row['sensor_id']; ?>&param=<?php echo $row['param_id']?>&type=range&fromdate=<?php echo $year.$month.$day.'000000'; ?>&todate=<?php echo $year.$month.$day.'235959'?>"></center>
 246 <?php
 247
 248       }
 249     }
 250   }
 251 }
 252 ?>
 253 </body>
 254