--
-- Host: localhost    Database: kvmrt2_db
-- ------------------------------------------------------

--
-- Dumping routines for database 'kvmrt2_db'
--
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` FUNCTION `fn_aaa_alert`(`aaa_alert` double, `aaa_action` double, `aaa_alarm` double, `xdata` double , `instrument` VARCHAR(50)) RETURNS tinyint(4)
BEGIN
    DECLARE filter INT;
    DECLARE aaa_value INT;
    DECLARE aaa INT;
    DECLARE i_data VARCHAR(50);
    DECLARE instr_type VARCHAR(50);
    DECLARE exclude_from VARCHAR(50);
    DECLARE exclude_to VARCHAR(50);
    DECLARE change_1 Int;
    DECLARE change_2 INT;
    DECLARE date_1 DATE;
    DECLARE date_2 DATE;
    DECLARE change_diff INT;
    DECLARE date_diff INT;
    DECLARE change_rate INT;
    DECLARE rev VARCHAR(3);
    DECLARE ratio_1 INT;
    DECLARE ratio_2 INT;
    DECLARE new_alarm INT;
    DECLARE ratio1_1 INT;
    DECLARE ratio1_2 INT;
    DECLARE multiple_data_1 INT;
    DECLARE multiple_data_2 INT;
    DECLARE multiple INT;
    DECLARE new_multiple_1 INT;
    DECLARE new_multiple_2 INT;
    DECLARE safe INT;
    DECLARE time1 TIME;
    DECLARE correct_value INT;

    DECLARE factor_ratio INT;
    DECLARE reminder INT;
    DECLARE a_count INT;


    CREATE TEMPORARY TABLE IF NOT EXISTS temp_instrum SELECT * FROM instrum WHERE instr_id =  instrument ORDER BY revision DESC LIMIT 1;

    SELECT type1 INTO instr_type FROM temp_instrum WHERE instr_id = instrument;

    SELECT alarm_filter_status INTO filter FROM types_aaa_setup WHERE type1=instr_type;

    set aaa=-1;

    SELECT alarm_on INTO i_data FROM temp_instrum WHERE instr_id=instrument;

    IF( filter = 1 ) THEN


      IF(i_data = 'ON' ) THEN
        IF ( abs(xdata) >= abs(aaa_alarm) AND aaa_alarm!=' ' ) THEN
          SET aaa = 3;
          SET aaa_value = aaa_alarm;
        ELSEIF ( abs(xdata) >= abs(aaa_action) AND aaa_action!=' ' ) THEN
          SET aaa = 2;
          SET aaa_value = aaa_action;
        ELSEIF ( abs(xdata) >= abs(aaa_alert) AND aaa_alert!=' ' ) THEN
          SET aaa =1;
          SET aaa_value = aaa_alert;
        ELSE SET aaa=0;
        END IF;
      END IF;


      IF(aaa !=-1)THEN
        SELECT alarm_type INTO i_data FROM temp_instrum WHERE instr_id=instrument;


        IF(i_data=-1) THEN
          IF(xdata>-abs(aaa_value)) THEN
            SET aaa=0;
          END IF;
        ELSEIF (i_data=0) THEN
          IF(abs(xdata)< abs(aaa_value)) THEN
            SET aaa=0;
          END IF;
        ELSEIF (i_data=1) THEN
          IF(xdata<abs(aaa_value)) THEN
            SET aaa=0;
          END IF;
        END IF;

        IF(aaa!=0) THEN




          SELECT new_alarms_count INTO new_alarm FROM types_aaa_setup WHERE type1=instr_type;
          IF(new_alarm!='')THEN
            IF(abs(aaa_value) > 0)THEN
              SELECT revision INTO rev FROM temp_instrum WHERE instr_id=instrument;
              SELECT change1 INTO change_1 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 0,1;
              SELECT date1 INTO date_1 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 0,1;
              SELECT change1 INTO change_2 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 1,1;
              SELECT date1 INTO date_2 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 1,1;
              SET ratio_1=change_1/aaa_value;
              SET ratio_2=change_1/aaa_value;
              SET ratio1_1=ratio_1-1;
              SET ratio1_2=ratio_2-1;
              SET multiple_data_1=ratio_1/new_alarm;
              SET multiple_data_2=ratio_2/new_alarm;
              SET multiple=new_alarm;
              IF(abs((ratio_1 > 0))) THEN
                SET new_multiple_1= floor(multiple_data_1);
              ELSE SET new_multiple_1=ceil(multiple_data_1);
              END IF;
              IF(abs((ratio_2 > 0))) THEN
                SET new_multiple_2= floor(multiple_data_2);
              ELSE SET new_multiple_2=ceil(multiple_data_2);
              END IF;
            END IF;
            IF(new_multiple_1<=new_multiple_2) THEN
              SET aaa=0;
              DROP TEMPORARY TABLE temp_instrum;
              return aaa;
            END IF;
          END IF;


          SELECT safe_days INTO safe FROM types_aaa_setup WHERE type1=instr_type;
          SELECT date1 INTO date_1 FROM summary WHERE instr_id=instrument  ORDER BY date1 DESC LIMIT 0,1;
          IF(safe!='')THEN
            SELECT TIMESTAMPDIFF(DAY,NOW(),date_1) INTO date_diff;
            if(date_diff>=safe)THEN
              SET aaa=0;
              DROP TEMPORARY TABLE temp_instrum;
              return aaa;
            END IF;
          END IF;

          SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id
          WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;
          SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id
          WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;


          SELECT alarms_count INTO a_count FROM types_aaa_setup WHERE type1=instr_type;
          SELECT reminder_interval INTO reminder FROM types_aaa_setup WHERE type1=instr_type;
          SELECT factor_alarm_ratio INTO factor_ratio FROM types_aaa_setup WHERE type1=instr_type;

          IF(a_count!='' AND a_count > 1)THEN
            IF(change_1=xdata) THEN
              SELECT TIMESTAMPDIFF(MINUTE,NOW(),time1) INTO date_diff;
              IF(date_diff < reminder_interval)THEN SET aaa=0;DROP TEMPORARY TABLE temp_instrum;RETURN aaa;
              END IF;
            END IF;
          END IF;



          IF(aaa=3 AND factor_ratio!='')THEN
            SET correct_value=(change_1*20)/100;
            IF(xdata < correct_value)THEN SET aaa=0;DROP TEMPORARY TABLE temp_instrum;RETURN aaa;
            END IF;
          END IF;

        END IF;
      END IF;
      DROP TEMPORARY TABLE temp_instrum;
      return aaa;


    ELSE
      IF(i_data = 'ON' ) THEN

        IF( aaa_alarm = 0 ) THEN
          SET aaa =0;
          SET aaa_value = 0;
        ELSEIF ( abs(xdata) >= abs(aaa_alarm) ) THEN
          SET aaa = 3;
          SET aaa_value = aaa_alarm;
        ELSEIF ( abs(xdata) >= abs(aaa_action) ) THEN

          SET aaa =2;
          SET aaa_value = aaa_action;
        ELSEIF ( abs(xdata) >= abs(aaa_alert) AND aaa_alert!=0 ) THEN
          SET aaa =1;
          SET aaa_value = aaa_alert;
        ELSE SET aaa=0;
        END IF;
      END IF;


      IF(aaa !=-1)THEN
        SELECT alarm_type INTO i_data FROM temp_instrum WHERE instr_id=instrument;


        IF(i_data=-1) THEN
          IF(xdata>-abs(aaa_value)) THEN
            SET aaa=0;
          END IF;
        ELSEIF (i_data=0) THEN
          IF(abs(xdata)< abs(aaa_value)) THEN
            SET aaa=0;
          END IF;
        ELSEIF (i_data=1) THEN
          IF(xdata<abs(aaa_value)) THEN
            SET aaa=0;
          END IF;
        END IF;
      END IF;
      DROP TEMPORARY TABLE temp_instrum;RETURN aaa;
    END IF;
  END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` FUNCTION `fn_aaa_instrument`(`aaa_alert` DOUBLE, `aaa_action` DOUBLE, `aaa_alarm` DOUBLE, `xdata` DOUBLE, `instrument` VARCHAR(50)
, `class_type_ref` INT, `class_ref` INT
) RETURNS varchar(50) CHARSET utf8
BEGIN
DECLARE filter INT;
DECLARE aaa_value INT;
DECLARE aaa VARCHAR(50);
DECLARE i_data VARCHAR(50);
DECLARE aa_data VARCHAR(50);
DECLARE instr_type VARCHAR(50);
DECLARE exclude_from VARCHAR(50);
DECLARE exclude_to VARCHAR(50);
DECLARE change_1 Int;
DECLARE change_2 INT;
DECLARE date_1 DATE;
DECLARE date_2 DATE;
DECLARE change_diff INT;
DECLARE date_diff INT;
DECLARE change_rate INT;
DECLARE rev VARCHAR(3);
DECLARE ratio_1 INT;
DECLARE ratio_2 INT;
DECLARE new_alarm INT;
DECLARE ratio1_1 INT;
DECLARE ratio1_2 INT;
DECLARE multiple_data_1 INT;
DECLARE multiple_data_2 INT;
DECLARE multiple INT;
DECLARE new_multiple_1 INT;
DECLARE new_multiple_2 INT;
DECLARE safe INT;
DECLARE time1 TIME;
DECLARE correct_value INT;
DECLARE factor_ratio INT;
DECLARE reminder INT;
DECLARE a_count INT;

SELECT type1,alarm_on,alarm_type,revision INTO instr_type,aa_data,i_data,rev FROM instrum WHERE instr_id =  instrument ORDER BY revision DESC LIMIT 1;
SELECT alarm_filter_status INTO filter FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_ref AND class_id = class_ref;

IF( filter = 1 ) THEN 
	IF(aa_data = 'ON' ) THEN 
		IF ( abs(xdata) >= abs(aaa_alarm) AND !isNULL(aaa_alarm)) THEN 
			SET aaa = 'alarm';
			SET aaa_value = aaa_alarm;
		ELSEIF ( abs(xdata) >= abs(aaa_action) AND !isNULL(aaa_action)) THEN 
			SET aaa = 'action';
			SET aaa_value = aaa_action;
		ELSEIF ( abs(xdata) >= abs(aaa_alert) AND !isNULL(aaa_alert)) THEN 
			SET aaa ='alert';
			SET aaa_value = aaa_alert;
		END IF;
   END IF;

   IF(aaa !='')THEN
   	IF(i_data=-1) THEN
   		IF(xdata > -abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=0) THEN
   		IF(abs(xdata) < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=1) THEN
   		IF(xdata < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	END IF;

   	IF(aaa!='') THEN
						SELECT new_alarms_count INTO new_alarm FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_id AND class_id = class_id;
						IF(new_alarm!='')THEN	
							IF(abs(aaa_value) > 0)THEN
								SELECT change1,date1 INTO change_1,date_1 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 0,1;
								SELECT change1,date1 INTO change_2,date_2 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 1,1;
								SET ratio_1=change_1/aaa_value;
								SET ratio_2=change_1/aaa_value;
								SET ratio1_1=ratio_1-1;
								SET ratio1_2=ratio_2-1;
								SET multiple_data_1=ratio_1/new_alarm;
								SET multiple_data_2=ratio_2/new_alarm;
								SET multiple=new_alarm;
								IF(abs((ratio_1 > 0))) THEN
									SET new_multiple_1= floor(multiple_data_1);
								ELSE SET new_multiple_1=ceil(multiple_data_1);
								END IF;
								IF(abs((ratio_2 > 0))) THEN
									SET new_multiple_2= floor(multiple_data_2); 
								ELSE SET new_multiple_2=ceil(multiple_data_2);
								END IF;
							END IF;
								IF(new_multiple_1<=new_multiple_2) THEN
									SET aaa='';
									return aaa;
								END IF;
						END IF;
						SELECT safe_days INTO safe FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_id AND class_id = class_id;
						SELECT date1 INTO date_1 FROM summary WHERE instr_id=instrument  ORDER BY date1 DESC LIMIT 0,1;
						IF(safe!='')THEN
							SELECT TIMESTAMPDIFF(DAY,NOW(),date_1) INTO date_diff;
							if(date_diff>=safe)THEN
									SET aaa='';
									return aaa;
							END IF;
						END IF;
						SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id 
								WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;

					    SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id 
								WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;

						SELECT alarms_count,reminder_interval,factor_alarm_ratio INTO a_count,reminder,factor_ratio FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_id AND class_id = class_id;
						IF(a_count!='' AND a_count > 1)THEN
							IF(change_1=xdata) THEN
									SELECT TIMESTAMPDIFF(MINUTE,NOW(),time1) INTO date_diff;
									IF(date_diff < reminder_interval)THEN SET aaa='';RETURN aaa;
									END IF;
							END IF;
						END IF;

						IF(aaa='alarm' AND factor_ratio!='')THEN
								SET correct_value=(change_1*20)/100;
								IF(xdata < correct_value)THEN SET aaa='';RETURN aaa;
								END IF;
						END IF;		
   	END IF;
   END IF;
   return aaa;
ELSE 
IF(aa_data = 'ON' ) THEN 
		IF ( abs(xdata) >= abs(aaa_alarm) AND !isNULL(aaa_alarm)) THEN 
			SET aaa = 'alarm';
			SET aaa_value = aaa_alarm;
		ELSEIF ( abs(xdata) >= abs(aaa_action) AND !isNULL(aaa_action)) THEN 
			SET aaa = 'action';
			SET aaa_value = aaa_action;
		ELSEIF ( abs(xdata) >= abs(aaa_alert) AND !isNULL(aaa_alert)) THEN 
			SET aaa ='alert';
			SET aaa_value = aaa_alert;
		END IF;
   END IF;
   IF(aaa !='')THEN
   	IF(i_data=-1) THEN
   		IF(xdata > -abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=0) THEN
   		IF(abs(xdata) < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=1) THEN
   		IF(xdata < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	END IF;
   END IF;
   RETURN aaa;
END IF;

END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` FUNCTION `fn_aaa_level`(`aaa_alert` DOUBLE, `aaa_action` DOUBLE, `aaa_alarm` DOUBLE, `xdata` DOUBLE, `instrument` VARCHAR(50), `class_type_ref` INT, `class_ref` INT
) RETURNS tinyint(4)
BEGIN
DECLARE filter INT;
DECLARE aaa_value DOUBLE;
DECLARE aaa INT;
DECLARE i_data VARCHAR(50);
DECLARE al_data VARCHAR(50);
DECLARE instr_type VARCHAR(50);
DECLARE exclude_from VARCHAR(50);
DECLARE exclude_to VARCHAR(50);
DECLARE change_1 Int;
DECLARE change_2 INT;
DECLARE date_1 DATE;
DECLARE date_2 DATE;
DECLARE change_diff INT;
DECLARE date_diff INT;
DECLARE change_rate INT;
DECLARE rev VARCHAR(3);
DECLARE ratio_1 INT;
DECLARE ratio_2 INT;
DECLARE new_alarm INT;
DECLARE ratio1_1 INT;
DECLARE ratio1_2 INT;
DECLARE multiple_data_1 INT;
DECLARE multiple_data_2 INT;
DECLARE multiple INT;
DECLARE new_multiple_1 INT;
DECLARE new_multiple_2 INT;
DECLARE safe INT;
DECLARE time1 TIME;
DECLARE correct_value INT;
DECLARE factor_ratio INT;
DECLARE reminder INT;
DECLARE a_count INT;
DECLARE aaa_direction VARCHAR(50);
SELECT type1,aaa_direction,alarm_on,alarm_type,revision INTO instr_type,aaa_direction,al_data,i_data,rev FROM instrum WHERE instr_id =  instrument ORDER BY revision DESC LIMIT 1;
SELECT alarm_filter_status INTO filter FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_ref AND class_id = class_ref;
set aaa=-1;

IF( filter = 1 ) THEN 
	IF(al_data = 'ON' ) THEN 
		IF ( abs(xdata) >= abs(aaa_alarm) AND !isNULL(aaa_alarm)) THEN 
			SET aaa = 103;
			SET aaa_value = aaa_alarm;
		ELSEIF ( abs(xdata) >= abs(aaa_action) AND !isNULL(aaa_action)) THEN 
			SET aaa = 102;
			SET aaa_value = aaa_action;
		ELSEIF ( abs(xdata) >= abs(aaa_alert) AND !isNULL(aaa_alert)) THEN 
			SET aaa =101;
			SET aaa_value = aaa_alert;
		ELSE SET aaa=0;	
		END IF;
   END IF;
   IF(aaa !=-1)THEN
   	IF(i_data=-1) THEN
   		IF(xdata>-abs(aaa_value)) THEN
   			SET aaa=0;
   		END IF;
   	ELSEIF (i_data=0) THEN
   		IF(abs(xdata)< abs(aaa_value)) THEN
   			SET aaa=0;
   		END IF;
   	ELSEIF (i_data=1) THEN
   		IF(xdata<abs(aaa_value)) THEN
   			SET aaa=0;
   		END IF;
   	END IF;
   	IF(aaa!=0) THEN
						SELECT new_alarms_count INTO new_alarm FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_ref AND class_id = class_ref;
						IF(new_alarm!='')THEN	
							IF(abs(aaa_value) > 0)THEN
								SELECT aaa_direction,date1 INTO change_1,date_1 FROM pdata WHERE instr_id= instrument AND revision=rev AND record_status='LIVE' ORDER BY taken_on DESC LIMIT 0,1;
								SELECT aaa_direction,date1 INTO change_2,date_2 FROM pdata WHERE instr_id= instrument AND revision=rev AND record_status='LIVE' ORDER BY taken_on DESC LIMIT 1,1;
								SET ratio_1=change_1/aaa_value;
								SET ratio_2=change_1/aaa_value;
								SET ratio1_1=ratio_1-1;
								SET ratio1_2=ratio_2-1;
								SET multiple_data_1=ratio_1/new_alarm;
								SET multiple_data_2=ratio_2/new_alarm;
								SET multiple=new_alarm;
								IF(abs((ratio_1 > 0))) THEN
									SET new_multiple_1= floor(multiple_data_1);
								ELSE SET new_multiple_1=ceil(multiple_data_1);
								END IF;
								IF(abs((ratio_2 > 0))) THEN
									SET new_multiple_2= floor(multiple_data_2); 
								ELSE SET new_multiple_2=ceil(multiple_data_2);
								END IF;
							END IF;
								IF(new_multiple_1<=new_multiple_2) THEN
									SET aaa=0;
									return aaa;
								END IF;
						END IF;
						SELECT safe_days INTO safe FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_ref AND class_id = class_ref;
						SELECT date1 INTO date_1 FROM mydata WHERE instr_id=instrument  ORDER BY date1 DESC LIMIT 0,1;
						IF(safe!='')THEN
							SELECT TIMESTAMPDIFF(DAY,NOW(),date_1) INTO date_diff;
							if(date_diff>=safe)THEN
									SET aaa=0;
									return aaa;
							END IF;
						END IF;
						SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id 
								WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;
					    SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id 
								WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;
						SELECT alarms_count,reminder_interval,factor_alarm_ratio INTO a_count,reminder,factor_ratio FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_ref AND class_id = class_ref;
						IF(a_count!='' AND a_count > 1)THEN
							IF(change_1=xdata) THEN
									SELECT TIMESTAMPDIFF(MINUTE,NOW(),time1) INTO date_diff;
									IF(date_diff < reminder_interval)THEN SET aaa=0;RETURN aaa;
									END IF;
							END IF;
						END IF;
						IF(aaa=103 AND factor_ratio!='')THEN
								SET correct_value=(change_1*20)/100;
								IF(xdata < correct_value)THEN SET aaa=0;RETURN aaa;
								END IF;
						END IF;
   	END IF;
   END IF;
   return aaa;
ELSE
	IF(al_data = 'ON' ) THEN 
		IF ( abs(xdata) >= abs(aaa_alarm) AND !isNULL(aaa_alarm)) THEN 
			SET aaa = 101;
			SET aaa_value = aaa_alarm;
		ELSEIF ( abs(xdata) >= abs(aaa_action) AND !isNULL(aaa_action)) THEN 
			SET aaa =102;
			SET aaa_value = aaa_action;
		ELSEIF ( abs(xdata) >= abs(aaa_alert) AND !isNULL(aaa_alert)) THEN 
			SET aaa =103;
			SET aaa_value = aaa_alert;
	   ELSE SET aaa=0;
		END IF;
   END IF;
   IF(aaa !=-1)THEN
   	IF(i_data=-1) THEN
   		IF(xdata>-abs(aaa_value)) THEN
   			SET aaa=0;
   		END IF;
   	ELSEIF (i_data=0) THEN
   		IF(abs(xdata)< abs(aaa_value)) THEN
   			SET aaa=0;
   		END IF;
   	ELSEIF (i_data=1) THEN
   		IF(xdata<abs(aaa_value)) THEN
   			SET aaa=0;
   		END IF;
   	END IF;
   END IF;
  RETURN aaa;
END IF;
END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` FUNCTION `fn_get_type1_bearing`(`aa` DOUBLE, `bb` DOUBLE, `bearing` DOUBLE) RETURNS double
BEGIN

    DECLARE theta, angle DOUBLE;
    SET theta = ABS( DEGREES( ATAN( bb/ aa ) ) );



    IF ( aa > 0 AND bb = 0 ) THEN
      RETURN 0.0;
    END IF;

    IF ( aa < 0 AND bb = 0 ) THEN
      RETURN 180.0;
    END IF;

    IF( aa = 0 AND bb > 0 ) THEN
      RETURN 270.0;
    END IF;

    IF( aa = 0 AND bb < 0 )  THEN
      RETURN 90.0;
    END IF;

    IF( aa > 0 AND bb > 0 ) THEN
      SET angle = 360 - theta + bearing;
    END IF;

    IF( aa > 0 AND bb < 0 ) THEN
      SET angle = theta + bearing;
    END IF;

    IF( aa < 0 AND bb < 0 ) THEN
      SET angle = 180 - theta + bearing;
    END IF;

    IF( aa < 0 AND bb > 0 ) THEN
      SET angle = 180 + theta + bearing;
    END IF;

    IF( angle > 360 ) THEN
      RETURN angle - 360;
    END IF;
    return angle;
  END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` FUNCTION `fn_is_numeric`(`input` DOUBLE) RETURNS tinyint(4)
BEGIN
    return 1;
  END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` FUNCTION `fn_str_aaa_alert`(`aaa_alert` DOUBLE, `aaa_action` DOUBLE, `aaa_alarm` DOUBLE, `xdata` DOUBLE, `instrument` VARCHAR(50)
, `class_type_ref` INT, `class_ref` INT
) RETURNS varchar(50) CHARSET utf8
BEGIN
DECLARE filter INT;
DECLARE aaa_value DOUBLE;
DECLARE aaa VARCHAR(50);
DECLARE i_data VARCHAR(50);
DECLARE aa_data VARCHAR(50);
DECLARE instr_type VARCHAR(50);
DECLARE exclude_from VARCHAR(50);
DECLARE exclude_to VARCHAR(50);
DECLARE change_1 Int;
DECLARE change_2 INT;
DECLARE date_1 DATE;
DECLARE date_2 DATE;
DECLARE change_diff INT;
DECLARE date_diff INT;
DECLARE change_rate INT;
DECLARE rev VARCHAR(3);
DECLARE ratio_1 INT;
DECLARE ratio_2 INT;
DECLARE new_alarm INT;
DECLARE ratio1_1 INT;
DECLARE ratio1_2 INT;
DECLARE multiple_data_1 INT;
DECLARE multiple_data_2 INT;
DECLARE multiple INT;
DECLARE new_multiple_1 INT;
DECLARE new_multiple_2 INT;
DECLARE safe INT;
DECLARE time1 TIME;
DECLARE correct_value INT;
DECLARE factor_ratio INT;
DECLARE reminder INT;
DECLARE a_count INT;

SELECT type1,alarm_on,alarm_type,revision INTO instr_type,aa_data,i_data,rev FROM instrum WHERE instr_id =  instrument ORDER BY revision DESC LIMIT 1;
SELECT alarm_filter_status INTO filter FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_ref AND class_id = class_ref;

IF( filter = 1 ) THEN 
	IF(aa_data = 'ON' ) THEN 
		IF ( abs(xdata) >= abs(aaa_alarm) AND !isNULL(aaa_alarm)) THEN 
			SET aaa = 'alarm';
			SET aaa_value = aaa_alarm;
		ELSEIF ( abs(xdata) >= abs(aaa_action) AND !isNULL(aaa_action)) THEN 
			SET aaa = 'action';
			SET aaa_value = aaa_action;
		ELSEIF ( abs(xdata) >= abs(aaa_alert) AND !isNULL(aaa_alert)) THEN 
			SET aaa ='alert';
			SET aaa_value = aaa_alert;
		END IF;
   END IF;

   IF(aaa !='')THEN
   	IF(i_data=-1) THEN
   		IF(xdata > -abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=0) THEN
   		IF(abs(xdata) < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=1) THEN
   		IF(xdata < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	END IF;

   	IF(aaa!='') THEN
						SELECT new_alarms_count INTO new_alarm FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_id AND class_id = class_id;
						IF(new_alarm!='')THEN	
							IF(abs(aaa_value) > 0)THEN
								SELECT change1,date1 INTO change_1,date_1 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 0,1;
								SELECT change1,date1 INTO change_2,date_2 FROM mydata WHERE instr_id= instrument AND revision=rev ORDER BY date1 DESC LIMIT 1,1;
								SET ratio_1=change_1/aaa_value;
								SET ratio_2=change_1/aaa_value;
								SET ratio1_1=ratio_1-1;
								SET ratio1_2=ratio_2-1;
								SET multiple_data_1=ratio_1/new_alarm;
								SET multiple_data_2=ratio_2/new_alarm;
								SET multiple=new_alarm;
								IF(abs((ratio_1 > 0))) THEN
									SET new_multiple_1= floor(multiple_data_1);
								ELSE SET new_multiple_1=ceil(multiple_data_1);
								END IF;
								IF(abs((ratio_2 > 0))) THEN
									SET new_multiple_2= floor(multiple_data_2); 
								ELSE SET new_multiple_2=ceil(multiple_data_2);
								END IF;
							END IF;
								IF(new_multiple_1<=new_multiple_2) THEN
									SET aaa='';
									return aaa;
								END IF;
						END IF;
						SELECT safe_days INTO safe FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_id AND class_id = class_id;
						SELECT date1 INTO date_1 FROM summary WHERE instr_id=instrument  ORDER BY date1 DESC LIMIT 0,1;
						IF(safe!='')THEN
							SELECT TIMESTAMPDIFF(DAY,NOW(),date_1) INTO date_diff;
							if(date_diff>=safe)THEN
									SET aaa='';
									return aaa;
							END IF;
						END IF;
						SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id 
								WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;

					    SELECT m.`time` INTO time1 FROM `raw_file_alertstbl_log_child` c LEFT join raw_file_alertstbl a on a.raw_file_alert_ID=c.`raw_file_alert_id`left join raw_file_alertstbl_log_master m on m.log_id=c.log_id 
								WHERE a.instr_id =instrument and alert_status = 'sent' order by c.`log_id` desc limit 0,1 ;

						SELECT alarms_count,reminder_interval,factor_alarm_ratio INTO a_count,reminder,factor_ratio FROM types_aaa_setup WHERE type1=instr_type AND class_type_id = class_type_id AND class_id = class_id;
						IF(a_count!='' AND a_count > 1)THEN
							IF(change_1=xdata) THEN
									SELECT TIMESTAMPDIFF(MINUTE,NOW(),time1) INTO date_diff;
									IF(date_diff < reminder_interval)THEN SET aaa='';RETURN aaa;
									END IF;
							END IF;
						END IF;

						IF(aaa='alarm' AND factor_ratio!='')THEN
								SET correct_value=(change_1*20)/100;
								IF(xdata < correct_value)THEN SET aaa='';RETURN aaa;
								END IF;
						END IF;		
   	END IF;
   END IF;
   return aaa;
ELSE 

IF(aa_data = 'ON' ) THEN 
		IF ( abs(xdata) >= abs(aaa_alarm) AND !isNULL(aaa_alarm)) THEN 
			SET aaa = 'alarm';
			SET aaa_value = aaa_alarm;
		ELSEIF ( abs(xdata) >= abs(aaa_action) AND !isNULL(aaa_action)) THEN 
			SET aaa = 'action';
			SET aaa_value = aaa_action;
		ELSEIF ( abs(xdata) >= abs(aaa_alert) AND !isNULL(aaa_alert)) THEN 
			SET aaa ='alert';
			SET aaa_value = aaa_alert;
		END IF;
   END IF;
   IF(aaa !='')THEN
   	IF(i_data=-1) THEN
   		IF(xdata > -abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=0) THEN
   		IF(abs(xdata) < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	ELSEIF (i_data=1) THEN
   		IF(xdata < abs(aaa_value)) THEN
   			SET aaa='';
   		END IF;
   	END IF;
   END IF;
   RETURN aaa;
END IF;

END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`dbUser`@`%` PROCEDURE `test_aaa_data`(IN `aaa_alert` DOUBLE)
BEGIN

END ;;
DELIMITER ;


