projects / weathermon.git / blobdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | inline | side by side
Version with MQ135 Air Quality sensor.
[weathermon.git] / web / archive.php
diff --git a/web/archive.php b/web/archive.php
index 76708f7ed06263cab85f9a77a236c0d2d6663500..c40386389e5a7e5bea9d368c16ee2b31ed688369 100644 (file)
--- a/web/archive.php
+++ b/web/archive.php
@@ -2,15 +2,36 @@
 <html>
 <head>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
 <html>
 <head>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
-  <meta name="GENERATOR" content="Mozilla/4.72 [cp1251] (X11; U; Linux 2.2.12-20smp i686) [Netscape]">    
+  <meta name="GENERATOR" content="Mozilla/4.72 (X11; U; Linux 2.2.12-20smp i686) [Netscape]">    
   <title>WeatherMon (архив)</title>                      
   <link rel="icon" href="favicon.png" />
   <title>WeatherMon (архив)</title>                      
   <link rel="icon" href="favicon.png" />
+  <style type="text/css">
+    a.year   {
+        padding:20px;
+        margin: 10px;
+        border:2px solid darkgrey;
+        border-radius: 10px;
+        display:inline-block;
+        width:200px;
+        float:left;
+        font-size:100%;
+        text-align:center;
+    }                                          
+    a.month   {
+        font-size:100%;
+        text-align:center;
+    }                                          
+    a:visited { text-decoration: none; color:darkblue; }
+    a:link { text-decoration: none; color:blue; }
+    a:hover { text-decoration: none; color:navy; }  
+  </style>
 </head>
 <body text="black" bgcolor="silver" link="blue" vlink="#000080" alink="#FF0000">         
 <?php
 
 include('config_local.php');
 include('calendar.php');
 </head>
 <body text="black" bgcolor="silver" link="blue" vlink="#000080" alink="#FF0000">         
 <?php
 
 include('config_local.php');
 include('calendar.php');
+include('units.php');
 
 $year = $_REQUEST['year']; 
 $month = $_REQUEST['month'];
 
 $year = $_REQUEST['year']; 
 $month = $_REQUEST['month'];
@@ -26,9 +47,9 @@ if (! $year) {
         
   $q = $db -> prepare(
     'select 
         
   $q = $db -> prepare(
     'select 
-       date_format(timestamp,\'%Y\') as year
+       date_format(day,\'%Y\') as year
      from 
      from 
-       sensor_values 
+       calendar
      group by year  
      order by year');
   $q -> execute();
      group by year  
      order by year');
   $q -> execute();
@@ -37,7 +58,7 @@ if (! $year) {
 <?php
   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
 ?> 
 <?php
   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
 ?> 
-  <a href="?year=<?php echo $row['year']; ?>">Данные за <?php echo $row['year']; ?> год</a><br>
+  <a class="year" href="?year=<?php echo $row['year']; ?>">Данные за <?php echo $row['year']; ?> год</a><br>
 <?php
   }
       
 <?php
   }
       
@@ -50,13 +71,13 @@ if (! $year) {
     $next_year = sprintf('%04d',$next_year);
     $q = $db -> prepare(
           'select 
     $next_year = sprintf('%04d',$next_year);
     $q = $db -> prepare(
           'select 
-             date_format(timestamp,\'%m\') as month,
-             date_format(timestamp,\'%d\') as day
+             date_format(day,\'%m\') as month,
+             date_format(day,\'%d\') as day
            from 
            from 
-             sensor_values
+             calendar
            where 
            where 
-             timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and 
-             timestamp<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
+             day>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and 
+             day<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
            group by month,day
            order by month,day'
           );
            group by month,day
            order by month,day'
           );
@@ -79,7 +100,7 @@ if (! $year) {
         $month=sprintf('%02d',$m);
         $month_name = strftime('%B',mktime(0,0,0,$month,1,$year));
         if (!empty($months[$month])) {
         $month=sprintf('%02d',$m);
         $month_name = strftime('%B',mktime(0,0,0,$month,1,$year));
         if (!empty($months[$month])) {
-          echo '<h3 align="center"><a href="?year='.$year.'&month='.$month.'">'.$month_name.'</a></h3>';
+          echo '<h3 align="center"><a class="month" href="?year='.$year.'&month='.$month.'">'.$month_name.'</a></h3>';
         } else {
           echo '<h3 align="center">'.$month_name.'</h3>';
         }
         } else {
           echo '<h3 align="center">'.$month_name.'</h3>';
         }
@@ -96,18 +117,18 @@ if (! $year) {
     $q = $db -> prepare (
       'select 
          s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name,
     $q = $db -> prepare (
       'select 
          s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name,
-         count(distinct date_format(v.timestamp,\'%d\')) cnt,
-         round(min(v.value),1) min_value,
-         round(max(v.value),1) max_value,
+         count(distinct day) cnt,
+         round(min(v.min),1) min_value,
+         round(max(v.max),1) max_value,
          u.name_short unit
        from
          u.name_short unit
        from
-         sensor_values v,sensors s,st_parameters p,units u
+         sensors_ranges v,sensors s,st_parameters p,units u
        where 
        where 
-         v.sensor_id=s.id and
-         v.parameter_id=p.id and
+         v.sensor=s.id and
+         v.parameter=p.id and
          p.st_unit=u.id and
          p.st_unit=u.id and
-         v.timestamp>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and 
-         v.timestamp<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
+         v.day>=str_to_date(\''.$year.'-01-01\',\'%Y-%m-%d\') and 
+         v.day<str_to_date(\''.$next_year.'-01-01\',\'%Y-%m-%d\')
       group by s.id,p.id,s.s_description,p.st_description
       order by s.id,p.id'
       );
       group by s.id,p.id,s.s_description,p.st_description
       order by s.id,p.id'
       );
@@ -116,12 +137,20 @@ if (! $year) {
     $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
       
     foreach ($sensors as $sensor) {
     $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
       
     foreach ($sensors as $sensor) {
-      
+
+      $units = get_unit($db,$sensor['param']);
+      $from_unit = $units['from'];
+      $to_unit = $units['to'];
+      $param_unit = $units['name'];
+
+      $min_val = convert_unit($db,$sensor['min_value'],$from_unit,$to_unit);
+      $max_val = convert_unit($db,$sensor['max_value'],$from_unit,$to_unit);
+     
       echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
       echo '<table align="center">';
       echo '<tr><td>';
       echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
       echo '<table align="center">';
       echo '<tr><td>';
-      echo 'Минимальное значение за год <b>'.$sensor['min_value'].' '.$sensor['unit'].'</b><br>';
-      echo 'Максимальное значение за год <b>'.$sensor['max_value'].' '.$sensor['unit'].'</b><br>';
+      echo 'Минимальное значение за год <b>'.$min_val.' '.$param_unit.'</b><br>';
+      echo 'Максимальное значение за год <b>'.$max_val.' '.$param_unit.'</b><br>';
       echo '</td></tr>';
       echo '</table>';
 
       echo '</td></tr>';
       echo '</table>';
 
@@ -154,12 +183,12 @@ if (! $year) {
 
       $q = $db -> prepare( 
           'select  
 
       $q = $db -> prepare( 
           'select  
-             date_format(timestamp,\'%d\') as day
+             date_format(day,\'%d\') as day
            from 
            from 
-             sensor_values
+             sensors_ranges
            where 
            where 
-             timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and 
-             timestamp<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
+             day>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and 
+             day<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
            group by day
            order by day'
           );
            group by day
            order by day'
           );
@@ -181,18 +210,18 @@ if (! $year) {
       $q = $db -> prepare (
           'select 
              s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name,
       $q = $db -> prepare (
           'select 
              s.id sensor,p.id param,s.s_description sensor_name,p.st_description param_name,
-             count(distinct date_format(v.timestamp,\'%d\')) cnt,
-             round(min(v.value),1) min_value,
-             round(max(v.value),1) max_value,
+             count(distinct day) cnt,
+             round(min(v.min),1) min_value,
+             round(max(v.max),1) max_value,
              u.name_short unit
            from
              u.name_short unit
            from
-             sensor_values v,sensors s,st_parameters p,units u
+             sensors_ranges v,sensors s,st_parameters p,units u
            where 
            where 
-             v.sensor_id=s.id and
-             v.parameter_id=p.id and
+             v.sensor=s.id and
+             v.parameter=p.id and
              p.st_unit=u.id and
              p.st_unit=u.id and
-             v.timestamp>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and 
-             v.timestamp<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
+             v.day>=str_to_date(\''.$year.'-'.$month.'-01\',\'%Y-%m-%d\') and 
+             v.day<str_to_date(\''.$next_year.'-'.$next_month.'-01\',\'%Y-%m-%d\')
            group by s.id,p.id,s.s_description,p.st_description
            order by s.id,p.id'
         );
            group by s.id,p.id,s.s_description,p.st_description
            order by s.id,p.id'
         );
@@ -201,12 +230,20 @@ if (! $year) {
       $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
       
       foreach ($sensors as $sensor) {
       $sensors = $q -> fetchAll(PDO::FETCH_ASSOC);
       
       foreach ($sensors as $sensor) {
+
+        $units = get_unit($db,$sensor['param']);
+        $from_unit = $units['from'];
+        $to_unit = $units['to'];
+        $param_unit = $units['name'];
+
+        $min_val = convert_unit($db,$sensor['min_value'],$from_unit,$to_unit);
+        $max_val = convert_unit($db,$sensor['max_value'],$from_unit,$to_unit);
       
         echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
         echo '<table align="center">';
         echo '<tr><td>';
       
         echo '<h3 align="center">'.$sensor['param_name'].'('.$sensor['sensor_name'].')</h3>';
         echo '<table align="center">';
         echo '<tr><td>';
-        echo 'Минимальное значение за месяц <b>'.$sensor['min_value'].' '.$sensor['unit'].'</b><br>';
-        echo 'Максимальное значение за месяц <b>'.$sensor['max_value'].' '.$sensor['unit'].'</b><br>';
+        echo 'Минимальное значение за месяц <b>'.$min_val.' '.$param_unit.'</b><br>';
+        echo 'Максимальное значение за месяц <b>'.$max_val.' '.$param_unit.'</b><br>';
         echo '</td></tr>';
         echo '</table>';
 
         echo '</td></tr>';
         echo '</table>';
 
@@ -227,14 +264,13 @@ if (! $year) {
 
       $q = $db -> prepare(
            'select 
 
       $q = $db -> prepare(
            'select 
-               distinct v.sensor_id,s.s_description,p.id as param_id,p.st_description 
+               distinct v.sensor as sensor_id,s.s_description,p.id as param_id,p.st_description 
             from 
             from 
-               sensor_values v,st_parameters p,sensors s 
+               sensors_ranges v,st_parameters p,sensors s 
             where 
             where 
-                v.timestamp>=str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\') and 
-                v.timestamp<date_add(str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\'),interval 1 day)
-                and v.sensor_id=s.id 
-                and s.st_id=p.st_id'
+                v.day=str_to_date(\''.$year.'-'.$month.'-'.$day.'\',\'%Y-%m-%d\')
+                and v.sensor=s.id 
+                and v.parameter=p.id'
             );
       $q -> execute();
 
             );
       $q -> execute();