Function LAST_DATA

DELIMITER $$
CREATE FUNCTION LAST_DATA (imei int) RETURNS INT DETERMINISTIC

BEGIN

DECLARE data_number_akhir int;
SET @eventId = (SELECT MAX(data_number) FROM u5056566_naval.raw_data);
if @eventId is NULL THEN
 RETURN  1;
ELSE
 RETURN  @eventId+1;
END IF;

END$$

DELIMITER ;

Function SPLIT_STRING

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');

SP sp_add_raw_data

DELIMITER $$
 
CREATE PROCEDURE sp_add_raw_data
(
 data_string VARCHAR(200),
    no_hp VARCHAR(20)
)
BEGIN
DECLARE hp Text;
DECLARE data_fix Text;
DECLARE id Text;
DECLARE data_number INT;
DECLARE sensor1_level Float;
DECLARE sensor2_level Float;
DECLARE sensor3_level Float;
DECLARE sensor4_level Float;
DECLARE sensor5_level Float;
DECLARE sensor6_level Float;
DECLARE sensor7_level Float;
DECLARE sensor8_level Float;
DECLARE sensor_rpm_val Float;
DECLARE flowmeter_val Float;
DECLARE latitude Text;
DECLARE nslatitude Text;
DECLARE longitude Text;
DECLARE ewlongitude Text;
DECLARE speed Text;
DECLARE pintch Text;
DECLARE role Text;
DECLARE dfm_kanan Text;
DECLARE dfm_kiri Text;
DECLARE gps_date DATETIME;
DECLARE tanggal DATE;
DECLARE jam TIME;



SET hp = no_hp;
SET data_fix = data_string;

SET id = SPLIT_STRING(data_fix,',', 1);
SET data_number = LAST_DATA(hp);
SET sensor1_level = SPLIT_STRING(data_fix,',', 4);
SET sensor2_level = SPLIT_STRING(data_fix,',', 5);
SET sensor3_level = SPLIT_STRING(data_fix,',', 6);
SET sensor4_level = SPLIT_STRING(data_fix,',', 7);
SET sensor5_level = SPLIT_STRING(data_fix,',', 8);
SET sensor6_level = SPLIT_STRING(data_fix,',', 9);
SET sensor7_level = SPLIT_STRING(data_fix,',', 10);
SET sensor8_level = SPLIT_STRING(data_fix,',', 11);
SET sensor_rpm_val = SPLIT_STRING(data_fix,',', 12);
SET flowmeter_val = SPLIT_STRING(data_fix,',', 13);
SET tanggal=  DATE_FORMAT(SPLIT_STRING(data_fix,',', 14), '%Y-%m-%d');
SET jam=  TIME_FORMAT(SPLIT_STRING(data_fix,',', 15), '%H:%i:%s');

SET gps_date= CONCAT(tanggal," ",jam);

SET latitude = CONCAT(SPLIT_STRING(data_fix,',', 16), "",SPLIT_STRING(data_fix,',', 17));
SET longitude = CONCAT(SPLIT_STRING(data_fix,',', 18), "",SPLIT_STRING(data_fix,',', 19));
SET speed = SPLIT_STRING(data_fix,',', 20);
SET pintch = SPLIT_STRING(data_fix,',', 21);
SET role = SPLIT_STRING(data_fix,',', 22);
SET dfm_kanan = SPLIT_STRING(data_fix,',', 23);
SET dfm_kiri = SPLIT_STRING(data_fix,',', 24);



   INSERT INTO u5056566_naval.raw_data
       (
           imei,
           data_number,
           id,
           sensor1_level,
           sensor2_level,
           sensor3_level,
           sensor4_level,
           sensor5_level,
           sensor6_level,
           sensor7_level,
           sensor8_level,
           sensor_rpm_val,
           flowmeter_val,
           latitude,
           longitude,
           speed,
           pintch,
           role,
           dfm_kanan,
           dfm_kiri,
           gps_date
       )
    VALUES
       (
           hp,
           data_number,
           id,
           sensor1_level,
           sensor2_level,
           sensor3_level,
           sensor4_level,
           sensor5_level,
           sensor6_level,
           sensor7_level,
           sensor8_level,
           sensor_rpm_val,
           flowmeter_val,
           latitude,
           longitude,
           speed,
           pintch,
           role,
           dfm_kanan,
           dfm_kiri,
           gps_date
       );
END$$

DELIMITER ;

Trigger add_raw_data

BEGIN

DECLARE hp Text;
DECLARE data_string Text;
DECLARE del1 Text;
DECLARE del2 Text;
DECLARE del3 Text;

SET hp = new.SenderNumber;
SET data_string = new.TextDecoded;
SET del1 = REPLACE(data_string,'$','');
SET del2 = REPLACE(del1,'*','');
SET del3 = REPLACE(del2,'','');
CALL sp_add_raw_data(del3,hp);
END