CREATE DATABASE  IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `meteo`;
--
-- Host: estia    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)
BEGIN
  declare lSTID int;
  declare lSID int;
  declare lSTPID int;
  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);
      end if;
    end if;
  end if;
END ;;
DELIMITER ;


