From 888e7fac57439dbb42864d77122a4cf551889477 Mon Sep 17 00:00:00 2001
From: Roman Bazalevsky <rvb@rvb.name>
Date: Mon, 30 May 2016 18:17:05 +0300
Subject: [PATCH] =?utf8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD?=
 =?utf8?q?=D0=BE=20=D0=BD=D0=B0=D1=81=D1=82=D1=80=D0=B0=D0=B8=D0=B2=D0=B0?=
 =?utf8?q?=D0=B5=D0=BC=D0=BE=D0=B5=20=D0=BE=D0=BA=D1=80=D1=83=D0=B3=D0=BB?=
 =?utf8?q?=D0=B5=D0=BD=D0=B8=D0=B5=20=D0=B7=D0=BD=D0=B0=D1=87=D0=B5=D0=BD?=
 =?utf8?q?=D0=B8=D0=B9=20=D0=BF=D1=80=D0=B8=20=D0=B2=D1=8B=D0=B2=D0=BE?=
 =?utf8?q?=D0=B4=D0=B5=20=D0=BD=D0=B0=20=D0=B3=D1=80=D0=B0=D1=84=D0=B8?=
 =?utf8?q?=D0=BA=D0=B5.?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Добавлено "прореживание" исторических данных.
---
 mysql/meteo_routines.sql    | 51 +++++++++++++++++++++++++++++++++++++
 mysql/meteo_units.sql       |  1 +
 mysql/tmp_sensor_values.sql |  8 ++++++
 web/image.php               | 10 +++++---
 web/image_minmax.php        | 22 +++++++++-------
 5 files changed, 79 insertions(+), 13 deletions(-)
 create mode 100644 mysql/tmp_sensor_values.sql

diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql
index bf72780..5f819b3 100644
--- a/mysql/meteo_routines.sql
+++ b/mysql/meteo_routines.sql
@@ -128,4 +128,55 @@ DELIMITER ;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
+DELIMITER $$
+CREATE DEFINER=`admin`@`%` PROCEDURE `Reduce`(pUpTo datetime,pRID integer)
+BEGIN
+  declare lNewRID integer;
+  declare lSeconds integer;
+
+  declare lStart,lEnd timestamp;
+
+  set lNewRID:=pRID+1;
+  select seconds into lSeconds from reduce_ranges where id=lNewRID;
+
+  delete from tmp_sensor_values;
+
+  insert into tmp_sensor_values(sensor_id,parameter_id,timestamp,value)
+  select sensor_id,parameter_id,from_unixtime(avg(unix_timestamp(timestamp))),avg(value) 
+    from sensor_values
+    where timestamp<pUpTo and reduced=pRID
+    group by sensor_id,parameter_id,floor(unix_timestamp(timestamp)/lSeconds);
+
+  select date(min(timestamp)),date(max(timestamp)) into lStart,lEnd from tmp_sensor_values;
+
+  delete from sensor_values where timestamp<pUpTo and reduced=pRID;
+
+  insert into sensor_values(sensor_id,parameter_id,timestamp,value,reduced)
+  select sensor_id,parameter_id,timestamp,value,lNewRID from tmp_sensor_values;
+
+  delete from sensors_ranges
+  where day>=lStart and day<=lEnd;
+
+  insert into sensors_ranges(day,sensor,parameter,timestamp,min,max)
+  select date(timestamp),sensor_id,parameter_id,max(timestamp),min(value),max(value)
+  from sensor_values
+  where timestamp>=lStart and timestamp<date_add(lEnd, interval 1 day)
+  group by date(timestamp),sensor_id,parameter_id;
+
+  delete from tmp_sensor_values; 
+
+END$$
+DELIMITER ;
+
+DELIMITER $$
+CREATE DEFINER=`admin`@`%` PROCEDURE `Cron_Reduce`(pMonths integer)
+BEGIN
+  declare lTime datetime;
+  select date_sub(date_format(now(),'%Y-%m-01'), interval pMonths month) into lTime;
+  call meteo.reduce(lTime,0);
+END$$
+DELIMITER ;
+
+
+
 -- Dump completed on 2014-09-25 14:45:45
diff --git a/mysql/meteo_units.sql b/mysql/meteo_units.sql
index 110b39d..76d81aa 100644
--- a/mysql/meteo_units.sql
+++ b/mysql/meteo_units.sql
@@ -27,6 +27,7 @@ CREATE TABLE `units` (
   `name` varchar(32) CHARACTER SET utf8 NOT NULL,
   `name_short` varchar(8) CHARACTER SET utf8 NOT NULL,
   `unit_group` int(11) NOT NULL,
+  `prec` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_units_group_idx` (`unit_group`),
   CONSTRAINT `fk_units_group` FOREIGN KEY (`unit_group`) REFERENCES `unit_groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
diff --git a/mysql/tmp_sensor_values.sql b/mysql/tmp_sensor_values.sql
new file mode 100644
index 0000000..696c073
--- /dev/null
+++ b/mysql/tmp_sensor_values.sql
@@ -0,0 +1,8 @@
+CREATE TABLE `tmp_sensor_values` (
+  `id` int(11) NOT NULL DEFAULT '0',
+  `sensor_id` int(11) NOT NULL,
+  `timestamp` datetime NOT NULL,
+  `value` float NOT NULL,
+  `parameter_id` int(11) NOT NULL,
+  `reduced` int(11) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/web/image.php b/web/image.php
index bf511f2..9b6ca84 100644
--- a/web/image.php
+++ b/web/image.php
@@ -1,6 +1,6 @@
 <?php // content="text/plain; charset=utf-8"
 
-error_reporting(E_ALL & ~E_STRICT & ~E_NOTICE);
+error_reporting(E_ALL & ~E_STRICT & ~E_NOTICE & ~E_DEPRECATED);
 
 include ('config_local.php');
 
@@ -12,7 +12,7 @@ require_once ('jpgraph/jpgraph_regstat.php');
 
 if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
 
-  die('Не могу подключиться к БД');
+  die('Не могу подключиться к БД');
 
 }  
 
@@ -65,12 +65,13 @@ if ($type and $param) {
   }  
 
   $q = $db -> prepare(
-    'select u.name_short from units u where u.id='.$to_unit
+    'select u.name_short,u.prec from units u where u.id='.$to_unit
   );
   $q -> execute();
 
   while ($row = $q -> fetch(PDO::FETCH_ASSOC)) {
     $param_unit = $row['name_short'];
+    $precision = $row['prec'];
   }                                                                
   
   $xdata = array();
@@ -198,6 +199,7 @@ if ($type and $param) {
     $g->xaxis->HideLabels(True);
   }
   $g->xaxis->SetPos("min");
+  $g->yaxis->SetLabelFormat("%0.".$precision."f");
 #  $g->xaxis->scale->SetTimeAlign( HOURADJ_1 );
 
 
@@ -239,7 +241,7 @@ if ($type and $param) {
 } else {
 
   header("Content-Type: text/html; charset=UTF-8");
-  die('Сенсор не выбран!');
+  die('Сенсор не выбран!');
 
 }  
 
diff --git a/web/image_minmax.php b/web/image_minmax.php
index 183942b..edc1708 100644
--- a/web/image_minmax.php
+++ b/web/image_minmax.php
@@ -48,8 +48,7 @@ if ($type and $param) {
   }                                                                
 
   $q = $db -> prepare(
-    'select st.st_fill_color_top,st.st_fill_color_bottom,st.st_description,u.id,u.unit_group from st_parameters st,units u where st.id='.$param.' and st.st_unit=u.id
-'
+    'select st.st_fill_color_top,st.st_fill_color_bottom,st.st_description,u.id,u.unit_group from st_parameters st,units u where st.id='.$param.' and st.st_unit=u.id'
   );
   $q -> execute();
 
@@ -100,8 +99,11 @@ if ($type and $param) {
     
     }
 
+    $datestr=sprintf("%04d%02d01",$year,$month);
+    $nextdatestr=sprintf("%04d%02d01",$next_year,$next_month);
+
     $q = $db -> prepare(
-      '
+     '
         select 
           x,
           unitconv(min(min),'.$from_unit.','.$to_unit.') min_value,
@@ -114,14 +116,14 @@ if ($type and $param) {
           from 
             sensors_ranges 
           where 
-            day>=str_to_date(\''.$year.$month.'\',\'%Y%m\')
-            and day<str_to_date(\''.$next_year.$next_month.'\',\'%Y%m\')
+            day>=STR_TO_DATE(\''.$datestr.'\',\'%Y%m%d\')
+            and day<STR_TO_DATE(\''.$nextdatestr.'\',\'%Y%m%d\')
             and sensor='.$sensor.'
             and parameter='.$param.'
           ) t group by x
         order by x'
     );
-    
+
   } elseif ($type == "year") {
 
     $next_year = $year+1;
@@ -134,6 +136,9 @@ if ($type and $param) {
                           
     }
 
+    $datestr=sprintf("%04d0101",$year);
+    $nextdatestr=sprintf("%04d0101",$next_year);
+
     $q = $db -> prepare(
       '
         select 
@@ -150,8 +155,8 @@ if ($type and $param) {
           from 
             sensors_ranges
           where 
-            day>=str_to_date(\''.$year.'\',\'%Y\')
-            and day<str_to_date(\''.$next_year.'\',\'%Y\')
+            day>=STR_TO_DATE(\''.$datestr.'\',\'%Y%m%d\')
+            and day<STR_TO_DATE(\''.$nextdatestr.'\',\'%Y%m%d\')
             and sensor='.$sensor.'
             and parameter='.$param.'
           ) t group by x
@@ -187,7 +192,6 @@ if ($type and $param) {
     
   }                                                                
 
-
   // Create the graph
   $g->graph_theme = null;
 
-- 
2.34.1