Workaround for broken data transmission from Arduino part

[weathermon.git] / mysql / meteo_routines.sql

diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql
index bdfa3ccf6190a1bb227fb81c4b82c1f4d09507c9..bf72780f001753081afaa6adb04b05ccddc7a6a0 100644 (file)
--- a/mysql/meteo_routines.sql
+++ b/mysql/meteo_routines.sql
@@ -1,10 +1,8 @@
-CREATE DATABASE  IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */;
-USE `meteo`;
--- MySQL dump 10.13  Distrib 5.5.35, for debian-linux-gnu (i686)
+-- MySQL dump 10.13  Distrib 5.5.38, for debian-linux-gnu (x86_64)

 --
 --

--- Host: estia    Database: meteo
+-- Host: localhost    Database: meteo

 -- ------------------------------------------------------
 -- ------------------------------------------------------

--- Server version      5.5.35-0ubuntu0.13.10.2
+-- Server version      5.5.38-0ubuntu0.14.04.1

 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;


@@ -58,21 +56,61 @@ DELIMITER ;
 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 /*!50003 SET sql_mode              = '' */ ;
 DELIMITER ;;
 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 /*!50003 SET sql_mode              = '' */ ;
 DELIMITER ;;

-CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float)
+CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float,pTimestamp datetime)

 BEGIN
   declare lSTID int;
   declare lSID int;
   declare lSTPID int;
 BEGIN
   declare lSTID int;
   declare lSID int;
   declare lSTPID int;

+  declare lTime DATETIME;
+  declare lDay DATE;
+  declare lCnt integer;

   select max(id) into lSTID from sensor_types where st_name=pSType;
   if lSTID is not null then 
     select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID;
     if lSID is not null then
          select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam;
       if lSTPID is not null then
   select max(id) into lSTID from sensor_types where st_name=pSType;
   if lSTID is not null then 
     select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID;
     if lSID is not null then
          select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam;
       if lSTPID is not null then

-        insert into sensor_values(sensor_id,parameter_id,timestamp,value)
-          values(lSID,lSTPID,current_timestamp(),pValue);
+
+        if lSTPID>=0 then
+
+          if pTimestamp is null then
+                   set lTime:=current_timestamp();
+                 else
+            set lTime:=pTimestamp;
+          end if;
+
+          insert into sensor_values(sensor_id,parameter_id,timestamp,value)
+            values(lSID,lSTPID,lTime,pValue);
+
+          set lDay:=DATE(lTime);
+          select count(*) into lCnt from calendar where sensor=lSID and day=lDay;
+          if lCnt=0 then
+            insert into calendar(day,sensor) values(lDay,lSID);
+          end if;
+
+          select count(*) into lCnt from sensors_ranges where sensor=lSID and day=lDay and parameter=lSTPID;
+          if lCnt=0 then
+            insert into sensors_ranges(day,sensor,parameter,min,max) values (lDay,lSID,lSTPID,pValue,pValue);
+          else
+            update sensors_ranges
+            set
+              min=LEAST(min,pValue),
+              max=GREATEST(max,pValue)
+            where
+              day=lDay and sensor=lSID and parameter=lSTPID;
+          end if;
+        end if;
+      else
+        insert into error_log(timestamp,text)
+        values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));

       end if;
       end if;

+    else
+      insert into error_log(timestamp,text)
+      values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));

     end if;
     end if;

+  else
+    insert into error_log(timestamp,text)
+    values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));

   end if;
 END ;;
 DELIMITER ;
   end if;
 END ;;
 DELIMITER ;


@@ -90,4 +128,4 @@ DELIMITER ;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 

--- Dump completed on 2014-03-17 20:31:48
+-- Dump completed on 2014-09-25 14:45:45