--
-- Host: localhost    Database: meteo
-- ------------------------------------------------------


--
-- Dumping routines for database 'meteo'
--
/*!50003 DROP FUNCTION IF EXISTS `unitconv` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `unitconv`(pValue float, pFromUnit integer, pToUnit integer) RETURNS float
BEGIN
  declare result float;
  declare a,b,c float;
  if pFromUnit=pToUnit then 
    set result = pValue;
  else
    select u.a,u.b,u.c into a,b,c from unit_conv u where from_unit=pFromUnit and to_unit=pToUnit;
    set result=b;
    set result = (pValue+a)*b+c;
  end if;
  return result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `submit_value` */;
DELIMITER ;;
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;
  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

        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;
    else
      insert into error_log(timestamp,text)
      values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
    end if;
  else
    insert into error_log(timestamp,text)
    values (current_timestamp(),CONCAT("Failed to submit ",pSType,",",pSID,",",pParam,",",pValue));
  end if;
END ;;
DELIMITER ;


