
-- ---------------------------------------------------------------
-- Authors: Matteo Quintiliani and Stefano Pintore
--          I.N.G.V. - Italy
--          Istituto Nazionale di Geofisica e Vulcanologia
-- ---------------------------------------------------------------

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_get_ew_instance_out`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_get_ew_instance_out`(
in_ewname                VARCHAR(80)  /* Name of the EW instance running */,
OUT l_id                 BIGINT
)
uscita: BEGIN
DECLARE l_ewname         VARCHAR(80);

SET l_id = -1;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: Ew instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

SELECT id INTO l_id FROM `ew_instance` WHERE ewname=l_ewname;

IF l_id = -1 THEN
    INSERT INTO `ew_instance`(ewname) VALUES (l_ewname);
    SELECT LAST_INSERT_ID() INTO l_id;
END IF;

-- SELECT l_id;

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_get_ew_module_out`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_get_ew_module_out`(
in_ewname                VARCHAR(80)  /* Name of the EW instance running */,
in_modname               VARCHAR(32)  /* EW module name*/,
OUT l_id                 BIGINT
)
uscita: BEGIN
DECLARE l_ewname         VARCHAR(80);
DECLARE l_modname        VARCHAR(32);
DECLARE l_ewid           BIGINT;

SET l_id = -1;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: Ew instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: Ew module name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

CALL `sp_get_ew_instance_out`(l_ewname, l_ewid);

SELECT id INTO l_id FROM `ew_module` WHERE ewid=l_ewid AND modname=l_modname;

IF l_id = -1 THEN
    INSERT INTO `ew_module`(ewid,modname) VALUES (l_ewid,l_modname);
    SELECT LAST_INSERT_ID() INTO l_id;
END IF;

-- SELECT l_id;

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_get_ew_spkseq_out`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_get_ew_spkseq_out`(
in_ewname                VARCHAR(80)  /* Name of the EW instance running */,
in_pkseq                 BIGINT  /* Pick sequence number from picker */,
OUT l_id                 BIGINT
)
uscita: BEGIN
DECLARE l_ewname         VARCHAR(80);
DECLARE l_pkseq          BIGINT;
DECLARE l_ewid           BIGINT;

SET l_id = -1;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: Ew instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_pkseq)='' THEN 
        SELECT 'ERROR: Pick sequence number from picker can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_pkseq=in_pkseq;

CALL `sp_get_ew_instance_out`(l_ewname, l_ewid);

SELECT id INTO l_id FROM `ew_spkseq` WHERE ewid=l_ewid AND pkseq=l_pkseq;

IF l_id = -1 THEN
    INSERT INTO `ew_spkseq`(ewid,pkseq) VALUES (l_ewid,l_pkseq);
    SELECT LAST_INSERT_ID() INTO l_id;
END IF;

-- SELECT l_id;

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_get_ew_sqkseq_out`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_get_ew_sqkseq_out`(
in_ewname                VARCHAR(80)  /* Name of the EW instance running */,
in_qkseq                 BIGINT  /* Pick sequence number from picker */,
OUT l_id                 BIGINT
)
uscita: BEGIN
DECLARE l_ewname         VARCHAR(80);
DECLARE l_qkseq          BIGINT;
DECLARE l_ewid           BIGINT;

SET l_id = -1;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: Ew instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_qkseq)='' THEN 
        SELECT 'ERROR: Quake sequence number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qkseq=in_qkseq;

CALL `sp_get_ew_instance_out`(l_ewname, l_ewid);

SELECT id INTO l_id FROM `ew_sqkseq` WHERE ewid=l_ewid AND qkseq=l_qkseq;

IF l_id = -1 THEN
    INSERT INTO `ew_sqkseq`(ewid,qkseq) VALUES (l_ewid,l_qkseq);
    SELECT LAST_INSERT_ID() INTO l_id;
END IF;

-- SELECT l_id;

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_get_ew_scnl_out`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_get_ew_scnl_out`(
in_sta                   VARCHAR(5)  /* Station code */,
in_cha                   VARCHAR(3)  /* Channel/Component code */,
in_net                   VARCHAR(2)  /* Net code */,
in_loc                   VARCHAR(2)  /* Location code */,
OUT l_id                 BIGINT
)
uscita: BEGIN
DECLARE l_sta                   VARCHAR(5);
DECLARE l_cha                   VARCHAR(3);
DECLARE l_net                   VARCHAR(2);
DECLARE l_loc                   VARCHAR(2);

SET l_id = -1;

IF TRIM(in_sta)='' THEN 
        SELECT 'ERROR: station (site) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_sta=in_sta;

IF TRIM(in_cha)='' THEN 
        SELECT 'ERROR: channel (component) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_cha=in_cha;

IF TRIM(in_net)='' THEN 
        SELECT 'ERROR: net name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_net=in_net;

IF in_loc IS NULL OR TRIM(in_loc)='' OR TRIM(in_loc)='--' THEN 
    SET in_loc='--';
END IF;
SET l_loc=in_loc;

SELECT id INTO l_id FROM `ew_scnl` WHERE sta=l_sta AND cha=l_cha AND net=l_net AND loc=l_loc;

IF l_id = -1 THEN
    INSERT INTO `ew_scnl`(sta,cha,net,loc) VALUES (l_sta,l_cha,l_net,l_loc);
    SELECT LAST_INSERT_ID() INTO l_id;
END IF;

-- SELECT l_id;

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_scnl`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_scnl`(
in_sta                   VARCHAR(5)  /* Station code */,
in_cha                   VARCHAR(3)  /* Channel/Component code */,
in_net                   VARCHAR(2)  /* Net code */,
in_loc                   VARCHAR(2)  /* Location code */,
in_lat                   DOUBLE      /* Latitude */,
in_lon                   DOUBLE      /* Longitude */,
in_ele                   DOUBLE      /* Elevation */,
in_name                  VARCHAR(80) /* Site name */,
in_ext_id                BIGINT      /* External table id, negative value for NULL */
)
uscita: BEGIN
DECLARE l_sta                   VARCHAR(5);
DECLARE l_cha                   VARCHAR(3);
DECLARE l_net                   VARCHAR(2);
DECLARE l_loc                   VARCHAR(2);
DECLARE l_lat                   DOUBLE;
DECLARE l_lon                   DOUBLE;
DECLARE l_ele                   DOUBLE;
DECLARE l_name                  VARCHAR(80);
DECLARE l_ext_id                BIGINT;

DECLARE l_scnlid                BIGINT;

IF TRIM(in_sta)='' THEN 
        SELECT 'ERROR: station (site) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_sta=in_sta;

IF TRIM(in_cha)='' THEN 
        SELECT 'ERROR: channel (component) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_cha=in_cha;

IF TRIM(in_net)='' THEN 
        SELECT 'ERROR: net name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_net=in_net;

IF TRIM(in_loc)='' THEN 
    SET in_loc=null;
END IF;
SET l_loc=in_loc;

SET l_lat=in_lat;
SET l_lon=in_lon;
SET l_ele=in_ele;

IF TRIM(in_name)='' THEN 
    SET in_name=null;
END IF;
SET l_name=in_name;

IF TRIM(in_ext_id) < 0 THEN 
    SET in_ext_id=null;
END IF;
SET l_ext_id=in_ext_id;

CALL `sp_get_ew_scnl_out`(l_sta, l_cha, l_net, l_loc, l_scnlid);

UPDATE `ew_scnl`
    SET lat=l_lat,lon=l_lon,ele=l_ele,name=l_name,ext_id=l_ext_id
    WHERE id=l_scnlid;

SELECT l_scnlid;

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_tracebuf`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_tracebuf`(
in_ewname                VARCHAR(80) /* Host running the EW Module */,
in_modname               VARCHAR(32) /* Module name */,
in_pinno                 BIGINT      /* Pin number */,
in_nsamp                 BIGINT      /* Number of samples in packet */,
in_starttime             VARCHAR(30) /* time of first sample - string in datetime format with useconds */,
in_endtime               VARCHAR(30) /* time of last sample  - string in datetime format with useconds*/,
in_samprate              DOUBLE      /* Sample rate; nominal */,
in_net                   VARCHAR(2)  /* Net code */,
in_sta                   VARCHAR(5)  /* Station code */,
in_cha                   VARCHAR(3)  /* Channel/Component code */,
in_loc                   VARCHAR(2)  /* Location code */,
in_version               VARCHAR(10) /* numeric version fields (99-99) */,
in_datatype              VARCHAR(3)  /* Data format code */,
in_quality               VARCHAR(10) /* Data-quality fields (99-99) */
)
uscita: BEGIN
DECLARE l_ewname                VARCHAR(80);
DECLARE l_modname               VARCHAR(32);
DECLARE l_pinno                 BIGINT;
DECLARE l_nsamp                 BIGINT;
DECLARE l_starttime_dt          DATETIME;
DECLARE l_starttime_usec        BIGINT;
DECLARE l_endtime_dt            DATETIME;
DECLARE l_endtime_usec          BIGINT;
DECLARE l_samprate              DOUBLE;
DECLARE l_net                   VARCHAR(2);
DECLARE l_sta                   VARCHAR(5);
DECLARE l_cha                   VARCHAR(3);
DECLARE l_loc                   VARCHAR(2);
DECLARE l_version               VARCHAR(10);
DECLARE l_datatype              VARCHAR(3);
DECLARE l_quality               VARCHAR(10);

DECLARE l_ewmodid               BIGINT;
DECLARE l_scnlid               BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;


SET l_pinno=in_pinno;
SET l_nsamp=in_nsamp;

IF TRIM(in_starttime)='' THEN 
        SELECT 'ERROR: start time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_starttime_dt=in_starttime;
SELECT MICROSECOND(in_starttime) INTO l_starttime_usec ;

IF TRIM(in_endtime)='' THEN 
        SELECT 'ERROR: end time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_endtime_dt=in_endtime;
SELECT MICROSECOND(in_endtime) INTO l_endtime_usec ;

SET l_samprate=in_samprate;

IF TRIM(in_net)='' THEN 
        SELECT 'ERROR: net name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_net=in_net;

IF TRIM(in_sta)='' THEN 
        SELECT 'ERROR: station (site) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_sta=in_sta;

IF TRIM(in_cha)='' THEN 
        SELECT 'ERROR: channel (component) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_cha=in_cha;

IF TRIM(in_loc)='' THEN 
    SET in_loc=null;
END IF;
SET l_loc=in_loc;

SET l_version=in_version;
SET l_datatype=in_datatype;
SET l_quality=in_quality;

CALL `sp_get_ew_scnl_out`(l_sta, l_cha, l_net, l_loc, l_scnlid);
CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);

INSERT INTO `ew_tracebuf`(ewmodid,pinno,nsamp,starttime_dt,starttime_usec,endtime_dt,endtime_usec,samprate,scnlid,version,datatype,quality)
    VALUES (l_ewmodid,l_pinno,l_nsamp,l_starttime_dt,l_starttime_usec,l_endtime_dt,l_endtime_usec,l_samprate,l_scnlid,l_version,l_datatype,l_quality);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_pick_scnl`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_pick_scnl`(

in_ewname        VARCHAR(80)     /*Host running the EW Module*/,
in_modname       VARCHAR(32)     /*Module name*/,
in_seq           INT             /*Sequence number*/,
in_net           VARCHAR(2)      /*Net code*/,
in_sta           VARCHAR(5)      /*Station code => pick->site*/,
in_cha           VARCHAR(3)      /*Channel code => pick->component*/,
in_loc           VARCHAR(2)      /*Location code*/,
in_dir           CHAR(1)         /*First-motion descriptor (U,D,-space-,?) => pick->fm*/,
in_wt            TINYINT         /*Pick weight or quality (0-4)*/,
in_tpick         VARCHAR(30)     /*Time of pick - a string in datetime format*/,
in_pamp_0        BIGINT          /*pick->pamp[0]*/,
in_pamp_1        BIGINT          /*pick->pamp[1]*/,    
in_pamp_2        BIGINT          /*pick->pamp[2]*/        
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname     VARCHAR(32);
DECLARE l_seq         INT;
DECLARE l_net         VARCHAR(2);
DECLARE l_sta         VARCHAR(5);
DECLARE l_cha         VARCHAR(3);
DECLARE l_loc         VARCHAR(2);
DECLARE l_dir         CHAR(1);
DECLARE l_wt          TINYINT;
DECLARE l_tpick_dt    DATETIME;
DECLARE l_tpick_usec  BIGINT;
DECLARE l_pamp_0      BIGINT;
DECLARE l_pamp_1      BIGINT;
DECLARE l_pamp_2      BIGINT;
DECLARE l_ew_pick_id  BIGINT;

DECLARE l_ewmodid               BIGINT;
DECLARE l_scnlid               BIGINT;
DECLARE l_spkseq                BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;


IF TRIM(in_seq)='' THEN 
        SELECT 'ERROR: sequence number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_seq=in_seq;

IF TRIM(in_net)='' THEN 
        SELECT 'ERROR: net name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_net=in_net;

IF TRIM(in_sta)='' THEN 
        SELECT 'ERROR: station (site) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_sta=in_sta;

IF TRIM(in_cha)='' THEN 
        SELECT 'ERROR: channel (component) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_cha=in_cha;

IF TRIM(in_loc)='' THEN 
    SET in_loc=null;
END IF;
SET l_loc=in_loc;

IF TRIM(in_dir)='' THEN 
        SELECT 'ERROR: first motion can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_dir=in_dir;

IF TRIM(in_wt)='' THEN 
        SELECT 'ERROR: weight can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_wt=in_wt;

IF TRIM(in_tpick)='' THEN 
        SELECT 'ERROR: pick time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;

SET l_tpick_dt=in_tpick;
SELECT MICROSECOND(in_tpick) INTO l_tpick_usec ;

SET l_pamp_0=in_pamp_0;
SET l_pamp_1=in_pamp_1;
SET l_pamp_2=in_pamp_2;

CALL `sp_get_ew_scnl_out`(l_sta, l_cha, l_net, l_loc, l_scnlid);
CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_spkseq_out`(l_ewname, l_seq, l_spkseq);

INSERT INTO `ew_pick_scnl` (ewmodid,ewspkid,scnlid,dir,wt,tpick_dt,tpick_usec,pamp_0,pamp_1,pamp_2)
    VALUES (l_ewmodid,l_spkseq,l_scnlid,l_dir,l_wt,l_tpick_dt,l_tpick_usec,l_pamp_0,l_pamp_1,l_pamp_2);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_coda_scnl`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_coda_scnl`(

in_ewname        VARCHAR(80)     /*Host running the EW Module*/,
in_modname       VARCHAR(32)     /*Module name*/,
in_seq           INT             /*Sequence number*/,
in_net           VARCHAR(2)      /*Net code*/,
in_sta           VARCHAR(5)      /*Station code => pick->site*/,
in_cha           VARCHAR(3)      /*Channel code => pick->component*/,
in_loc           VARCHAR(2)      /*Location code*/,
in_caav_0        BIGINT          /*coda->caav[0]*/,
in_caav_1        BIGINT          /*coda->caav[1]*/,
in_caav_2        BIGINT          /*coda->caav[2]*/,
in_caav_3        BIGINT          /*coda->caav[3]*/,
in_caav_4        BIGINT          /*coda->caav[4]*/,
in_caav_5        BIGINT          /*coda->caav[5]*/,
in_dur           BIGINT          /*coda->dur*/
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname     VARCHAR(32);
DECLARE l_seq         INT;
DECLARE l_net         VARCHAR(2);
DECLARE l_sta         VARCHAR(5);
DECLARE l_cha         VARCHAR(3);
DECLARE l_loc         VARCHAR(2);
DECLARE l_caav_0      BIGINT;
DECLARE l_caav_1      BIGINT;
DECLARE l_caav_2      BIGINT;
DECLARE l_caav_3      BIGINT;
DECLARE l_caav_4      BIGINT;
DECLARE l_caav_5      BIGINT;
DECLARE l_dur         BIGINT;

DECLARE l_ewmodid               BIGINT;
DECLARE l_scnlid               BIGINT;
DECLARE l_spkseq                BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;


IF TRIM(in_seq)='' THEN 
        SELECT 'ERROR: sequence number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_seq=in_seq;

IF TRIM(in_net)='' THEN 
        SELECT 'ERROR: net name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_net=in_net;

IF TRIM(in_sta)='' THEN 
        SELECT 'ERROR: station (site) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_sta=in_sta;

IF TRIM(in_cha)='' THEN 
        SELECT 'ERROR: channel (component) name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_cha=in_cha;

IF TRIM(in_loc)='' THEN 
    SET in_loc=null;
END IF;
SET l_loc=in_loc;

SET l_caav_0=in_caav_0;
SET l_caav_1=in_caav_1;
SET l_caav_2=in_caav_2;
SET l_caav_3=in_caav_3;
SET l_caav_4=in_caav_4;
SET l_caav_5=in_caav_5;
SET l_dur=in_dur;

CALL `sp_get_ew_scnl_out`(l_sta, l_cha, l_net, l_loc, l_scnlid);
CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_spkseq_out`(l_ewname, l_seq, l_spkseq);

-- UPDATE `ew_pick_scnl`
-- SET caav_0=l_caav_0,caav_1=l_caav_1,caav_2=l_caav_2,caav_3=l_caav_3,caav_4=l_caav_4,caav_5=l_caav_5,dur=l_dur
-- WHERE ewname=l_ewname AND modname=l_modname AND seq=l_seq AND net=l_net AND sta=l_sta AND cha=l_cha AND loc=l_loc;

UPDATE `ew_pick_scnl`
SET caav_0=l_caav_0,caav_1=l_caav_1,caav_2=l_caav_2,caav_3=l_caav_3,caav_4=l_caav_4,caav_5=l_caav_5,dur=l_dur
WHERE ewmodid=l_ewmodid AND ewspkid=l_spkseq AND scnlid=l_scnlid;

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_link`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_link`(
in_ewname        VARCHAR(80)     /*Host running the EW Module*/,
in_modname       VARCHAR(32)     /*Module name*/,
in_qkseq         BIGINT          /*link sequence number*/,
in_pkseq         BIGINT          /*phase sequence number*/,
in_iphs          INT
)
uscita: BEGIN
DECLARE l_ewname    VARCHAR(80);
DECLARE l_modname     VARCHAR(32);
DECLARE l_qkseq       INT;
DECLARE l_pkseq       BIGINT;
DECLARE l_iphs        INT;

DECLARE l_ewmodid               BIGINT;
DECLARE l_sqkseq                BIGINT;
DECLARE l_spkseq                BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

IF TRIM(in_qkseq)='' THEN 
        SELECT 'ERROR: link sequence number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qkseq=in_qkseq;

IF TRIM(in_pkseq)='' THEN 
        SELECT 'ERROR: phase sequence number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_pkseq=in_pkseq;

IF TRIM(in_iphs)='' THEN 
        SELECT 'ERROR: phase number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_iphs=in_iphs;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qkseq, l_sqkseq);
CALL `sp_get_ew_spkseq_out`(l_ewname, l_pkseq, l_spkseq);

INSERT INTO `ew_link` (ewmodid,ewsqkid,ewspkid,iphs)
    VALUES (l_ewmodid,l_sqkseq,l_spkseq,l_iphs);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_quake2k`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_quake2k`(
in_ewname                VARCHAR(80)     /*Host running the EW Module*/,
in_modname               VARCHAR(32)     /*Module name*/,
in_qkseq                 INT             /*Sequence number*/,
in_quake_ot              VARCHAR(30)      /*Quake Origin Time*/,
in_lat                   DOUBLE      /*Latitude*/,
in_lon                   DOUBLE      /*Longitude*/,
in_depth                 DOUBLE      /*Depht => QUAKE2K->z*/,
in_rms                   DOUBLE      /*Rms Residual*/,
in_dmin                  DOUBLE      /*Distance to closest station*/,
in_ravg                  DOUBLE      /*Average or median station distance*/,
in_gap                   DOUBLE      /*Largest azimuth without picks*/,
in_nph                   INT         /*Linked phases*/
)
uscita: BEGIN
DECLARE l_ewname                VARCHAR(80);
DECLARE l_modname               VARCHAR(32);
DECLARE l_qkseq                 INT;
DECLARE l_quake_ot_dt           DATETIME;
DECLARE l_quake_ot_usec         BIGINT;
DECLARE l_lat                   DOUBLE;
DECLARE l_lon                   DOUBLE;
DECLARE l_depth                 DOUBLE;
DECLARE l_rms                   DOUBLE;
DECLARE l_dmin                  DOUBLE;
DECLARE l_ravg                  DOUBLE;
DECLARE l_gap                   DOUBLE;
DECLARE l_nph                   INT;

DECLARE l_ewmodid               BIGINT;
DECLARE l_sqkseq                BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;


IF TRIM(in_qkseq)='' THEN 
        SELECT 'ERROR: sequence number can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qkseq=in_qkseq;


IF TRIM(in_quake_ot)='' THEN 
        SELECT 'ERROR: origin time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;

SET l_quake_ot_dt=in_quake_ot;
SELECT MICROSECOND(in_quake_ot) INTO l_quake_ot_usec ;


IF TRIM(in_lat)='' THEN 
        SELECT 'ERROR: latitude can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_lat=in_lat;

IF TRIM(in_lon)='' THEN 
        SELECT 'ERROR: longitude can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_lon=in_lon;

IF TRIM(in_depth)='' THEN 
        SELECT 'ERROR: depth can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_depth=in_depth;

IF TRIM(in_rms)='' THEN 
        SELECT 'ERROR: rms can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_rms=in_rms;

IF TRIM(in_dmin)='' THEN 
        SELECT 'ERROR: dmin can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_dmin=in_dmin;


IF TRIM(in_ravg)='' THEN 
        SELECT 'ERROR: ravg can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ravg=in_ravg;

IF TRIM(in_gap)='' THEN 
        SELECT 'ERROR: gap can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_gap=in_gap;

IF TRIM(in_nph)='' THEN 
        SELECT 'ERROR: nph can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_nph=in_nph;


CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qkseq, l_sqkseq);

INSERT INTO `ew_quake2k` (ewmodid,ewsqkid,quake_ot_dt,quake_ot_usec,lat,lon,depth,rms,dmin,ravg,gap,nph)
VALUES (l_ewmodid,l_sqkseq,l_quake_ot_dt,l_quake_ot_usec,l_lat,l_lon,l_depth,l_rms,l_dmin,l_ravg,l_gap,l_nph);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_arc_summary`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_arc_summary`(
in_ewname       VARCHAR(80)     /*Host running the EW Module*/,
in_modname      VARCHAR(32)     /*Module name*/,
in_qid          BIGINT       /*event id from binder */,
in_ot           VARCHAR(30)  /*Origin Time - datetime string */,
in_lat          DOUBLE       /*latitude (North=positive) */,
in_lon          DOUBLE       /*longitude(East=positive) */,
in_z            DOUBLE       /*depth (down=positive) */,
in_nph          INT          /*# phases (P&S) w/ weight >0.1 */,
in_nphS         INT          /*# S phases w/ weight >0.1 */,
in_nphtot       INT          /*# phases (P&S) w/ weight >0.0 */,
in_nPfm         INT          /*# P first motions */,
in_gap          INT          /*maximum azimuthal gap */,
in_dmin         INT          /*distance (km) to nearest station */,
in_rms          DOUBLE       /*RMS travel time residual */,
in_e0az         INT          /*azimuth of largest principal error */,
in_e0dp         INT          /*dip of largest principal error */,
in_e0           float        /*magnitude (km) of largest principal error */,
in_e1az         INT          /*azimuth of intermediate principal error */,
in_e1dp         INT          /*dip of intermediate principal error */,
in_e1           DOUBLE       /*magnitude (km) of intermed principal error */,
in_e2           DOUBLE       /*magnitude (km) of smallest principal error */,
in_erh          DOUBLE       /*horizontal error (km) */,
in_erz          DOUBLE       /*vertical error (km) */,
in_Md           DOUBLE       /*duration magnitude */,
in_reg          VARCHAR(4)   /*location region */,
in_labelpref    CHAR         /*character describing preferred magnitude */,
in_Mpref        DOUBLE       /*preferred magnitude */,
in_wtpref       DOUBLE       /*weight (~ # readings) of preferred Mag */,
in_mdtype       CHAR         /*Coda duration magnitude type code */,
in_mdmad        DOUBLE       /*Median-absolute-difference of duration mags */,
in_mdwt         DOUBLE       /*weight (~ # readings) of Md */,
in_version      BIGINT       /*version number of the origin */,
in_quality      CHAR(2)      /*INGV quality code of the location */
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname      VARCHAR(32);
DECLARE l_qid          BIGINT;
DECLARE l_ot_dt        DATETIME;
DECLARE l_ot_usec      BIGINT;
DECLARE l_lat          DOUBLE;
DECLARE l_lon          DOUBLE;
DECLARE l_z            DOUBLE;
DECLARE l_nph          INT;
DECLARE l_nphS         INT;
DECLARE l_nphtot       INT;
DECLARE l_nPfm         INT;
DECLARE l_gap          INT;
DECLARE l_dmin         INT;
DECLARE l_rms          DOUBLE;
DECLARE l_e0az         INT;
DECLARE l_e0dp         INT;
DECLARE l_e0           float;
DECLARE l_e1az         INT;
DECLARE l_e1dp         INT;
DECLARE l_e1           DOUBLE;
DECLARE l_e2           DOUBLE;
DECLARE l_erh          DOUBLE;
DECLARE l_erz          DOUBLE;
DECLARE l_Md           DOUBLE;
DECLARE l_reg          VARCHAR(4);
DECLARE l_labelpref    CHAR;
DECLARE l_Mpref        DOUBLE;
DECLARE l_wtpref       DOUBLE;
DECLARE l_mdtype       CHAR;
DECLARE l_mdmad        DOUBLE;
DECLARE l_mdwt         DOUBLE;
DECLARE l_version      BIGINT;
DECLARE l_quality      CHAR(2);

DECLARE l_ewmodid               BIGINT;
DECLARE l_sqkseq                BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

IF TRIM(in_qid)='' THEN 
        SELECT 'ERROR: quake id can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qid=in_qid;

IF TRIM(in_ot)='' THEN 
        SELECT 'ERROR: origin time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;

SET l_ot_dt= in_ot;
SELECT MICROSECOND(in_ot) INTO l_ot_usec ;

SET l_lat=in_lat;
SET l_lon=in_lon;
SET l_z=in_z;
SET l_nph=in_nph;
SET l_nphS=in_nphS;
SET l_nphtot=in_nphtot;
SET l_nPfm=in_nPfm;
SET l_gap=in_gap;
SET l_dmin=in_dmin;
SET l_rms=in_rms;
SET l_e0az=in_e0az;
SET l_e0dp=in_e0dp;
SET l_e0=in_e0;
SET l_e1az=in_e1az;
SET l_e1dp=in_e1dp;
SET l_e1=in_e1;
SET l_e2=in_e2;
SET l_erh=in_erh;
SET l_erz=in_erz;
SET l_Md=in_Md;
SET l_reg=in_reg;
SET l_labelpref=in_labelpref;
SET l_Mpref=in_Mpref;
SET l_wtpref=in_wtpref;
SET l_mdtype=in_mdtype;
SET l_mdmad=in_mdmad;
SET l_mdwt=in_mdwt;
SET l_version=in_version;
SET l_quality=in_quality;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qid, l_sqkseq);

INSERT INTO `ew_arc_summary`( ewmodid,ewsqkid,ot_dt,ot_usec,lat,lon,z,nph,nphS,nphtot,nPfm,
                                    gap,dmin,rms,e0az,e0dp,e0,e1az,e1dp,e1,e2,erh,erz,
                                    Md,reg,labelpref,Mpref,wtpref,mdtype,mdmad,mdwt,version,quality)
VALUES(l_ewmodid,l_sqkseq,l_ot_dt,l_ot_usec,l_lat,l_lon,l_z,l_nph,l_nphS,l_nphtot,l_nPfm,
       l_gap,l_dmin,l_rms,l_e0az,l_e0dp,l_e0,l_e1az,l_e1dp,l_e1,l_e2,l_erh,l_erz,
       l_Md,l_reg,l_labelpref,l_Mpref,l_wtpref,l_mdtype,l_mdmad,l_mdwt,l_version,l_quality);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_arc_phase`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_arc_phase`(
in_ewname      VARCHAR(80)     /*Host running the EW Module*/,
in_modname     VARCHAR(32)     /*Module name*/,
in_qid         BIGINT      /*event id from binder*/,    
in_site        VARCHAR(5)  /*site code, null terminated*/,
in_net         VARCHAR(3)  /*seismic network code, null terminated*/,
in_comp        VARCHAR(4)  /*component code, null terminated*/,
in_loc         VARCHAR(2)  /*location code, null terminated*/,
in_Plabel      CHAR        /*P phase label*/,
in_Slabel      CHAR        /*S phase label*/,
in_Ponset      CHAR        /*P phase onset*/,
in_Sonset      CHAR        /*S phase onset*/,
in_Pat         VARCHAR(30) /*P-arrival-time - datetime part*/,
in_Sat         VARCHAR(30) /*S-arrival-time - datetime part*/,
in_Pres        DOUBLE      /*P travel time residual*/,
in_Sres        DOUBLE      /*S travel time residual*/,
in_Pqual       INT         /*Assigned P weight code*/,
in_Squal       INT         /*Assigned S weight code*/,
in_codalen     INT         /*Coda duration time*/,
in_codawt      INT         /*Coda weight*/,
in_Pfm         CHAR        /*P first motion*/,
in_Sfm         CHAR        /*S first motion*/,
in_datasrc     CHAR        /*Data source code.*/,
in_Md          DOUBLE      /*Station duration magnitude*/,
in_azm         INT         /*azimuth*/,
in_takeoff     INT         /*emergence angle at source*/,
in_dist        DOUBLE      /*epicentral distance (km)*/,
in_Pwt         DOUBLE      /*P weight actually used.*/,
in_Swt         DOUBLE      /*S weight actually used.*/,
in_pamp        INT         /*peak P-wave half amplitude*/,
in_codalenObs  INT         /*Coda duration time (Measured)*/,
in_ccntr_0     INT         /*Window center from P time*/,
in_ccntr_1     INT         /*Window center from P time*/,
in_ccntr_2     INT         /*Window center from P time*/,
in_ccntr_3     INT         /*Window center from P time*/,
in_ccntr_4     INT         /*Window center from P time*/,
in_ccntr_5     INT         /*Window center from P time*/,
in_caav_0      INT         /*Average Amplitude for ccntr[x]*/,
in_caav_1      INT         /*Average Amplitude for ccntr[x]*/,
in_caav_2      INT         /*Average Amplitude for ccntr[x]*/,
in_caav_3      INT         /*Average Amplitude for ccntr[x]*/,
in_caav_4      INT         /*Average Amplitude for ccntr[x]*/,
in_caav_5      INT         /*Average Amplitude for ccntr[x]*/,
in_version     BIGINT      /*version number of the origin'*/
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname     VARCHAR(32);
DECLARE l_qid         BIGINT;
DECLARE l_site        VARCHAR(5);
DECLARE l_net         VARCHAR(3);
DECLARE l_comp        VARCHAR(4);
DECLARE l_loc         VARCHAR(2);
DECLARE l_Plabel      CHAR;
DECLARE l_Slabel      CHAR;
DECLARE l_Ponset      CHAR;
DECLARE l_Sonset      CHAR;
DECLARE l_Pat_dt      DATETIME;
DECLARE l_Pat_usec    BIGINT;
DECLARE l_Sat_dt      DATETIME;
DECLARE l_Sat_usec    BIGINT;
DECLARE l_Pres        DOUBLE;
DECLARE l_Sres        DOUBLE;
DECLARE l_Pqual       INT;
DECLARE l_Squal       INT;
DECLARE l_codalen     INT;
DECLARE l_codawt      INT;
DECLARE l_Pfm         CHAR;
DECLARE l_Sfm         CHAR;
DECLARE l_datasrc     CHAR;
DECLARE l_Md          DOUBLE;
DECLARE l_azm         INT;
DECLARE l_takeoff     INT;
DECLARE l_dist        DOUBLE;
DECLARE l_Pwt         DOUBLE;
DECLARE l_Swt         DOUBLE;
DECLARE l_pamp        INT;
DECLARE l_codalenObs  INT;
DECLARE l_ccntr_0     INT;
DECLARE l_ccntr_1     INT;
DECLARE l_ccntr_2     INT;
DECLARE l_ccntr_3     INT;
DECLARE l_ccntr_4     INT;
DECLARE l_ccntr_5     INT;
DECLARE l_caav_0      INT;
DECLARE l_caav_1      INT;
DECLARE l_caav_2      INT;
DECLARE l_caav_3      INT;
DECLARE l_caav_4      INT;
DECLARE l_caav_5      INT;
DECLARE l_version     BIGINT;

DECLARE l_ewmodid               BIGINT;
DECLARE l_sqkseq                BIGINT;
DECLARE l_scnlid               BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

IF TRIM(in_qid)='' THEN 
        SELECT 'ERROR: quake id can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qid=in_qid;


IF TRIM(in_Pat)='' THEN 
        SELECT 'ERROR: origin time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;

SET l_Pat_dt=in_Pat;
SELECT MICROSECOND(in_Pat) INTO l_Pat_usec ;


IF TRIM(in_Sat)='' THEN 
        SELECT 'ERROR: origin time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;

SET l_Sat_dt=in_Sat;
SELECT MICROSECOND(in_Sat) INTO l_Sat_usec ;


SET l_qid        = in_qid;
SET l_site       = in_site;
SET l_net        = in_net;
SET l_comp       = in_comp;
SET l_loc        = in_loc;
SET l_Plabel     = in_Plabel;
SET l_Slabel     = in_Slabel;
SET l_Ponset     = in_Ponset;
SET l_Sonset     = in_Sonset;
SET l_Pres       = in_Pres;
SET l_Sres       = in_Sres;
SET l_Pqual      = in_Pqual;
SET l_Squal      = in_Squal;
SET l_codalen    = in_codalen;
SET l_codawt     = in_codawt;
SET l_Pfm        = in_Pfm;
SET l_Sfm        = in_Sfm;
SET l_datasrc    = in_datasrc;
SET l_Md         = in_Md;
SET l_azm        = in_azm;
SET l_takeoff    = in_takeoff;
SET l_dist       = in_dist;
SET l_Pwt        = in_Pwt;
SET l_Swt        = in_Swt;
SET l_pamp       = in_pamp;
SET l_codalenObs = in_codalenObs;
SET l_ccntr_0    = in_ccntr_0;
SET l_ccntr_1    = in_ccntr_1;
SET l_ccntr_2    = in_ccntr_2;
SET l_ccntr_3    = in_ccntr_3;
SET l_ccntr_4    = in_ccntr_4;
SET l_ccntr_5    = in_ccntr_5;
SET l_caav_0     = in_caav_0;
SET l_caav_1     = in_caav_1;
SET l_caav_2     = in_caav_2;
SET l_caav_3     = in_caav_3;
SET l_caav_4     = in_caav_4;
SET l_caav_5     = in_caav_5;
SET l_version    = in_version;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qid, l_sqkseq);
CALL `sp_get_ew_scnl_out`(l_site, l_comp, l_net, l_loc, l_scnlid);

INSERT INTO `ew_arc_phase` (ewmodid,ewsqkid,scnlid,Plabel,Slabel,Ponset,Sonset,Pat_dt,Pat_usec,Sat_dt,Sat_usec,Pres,Sres,Pqual,Squal,
                                  codalen,codawt,Pfm,Sfm,datasrc,Md,azm,takeoff,dist,Pwt,Swt,pamp,codalenObs,
                                  ccntr_0,ccntr_1,ccntr_2,ccntr_3,ccntr_4,ccntr_5,caav_0,caav_1,caav_2,caav_3,caav_4,caav_5,version)
    VALUES ( l_ewmodid,l_sqkseq,l_scnlid,l_Plabel,l_Slabel,l_Ponset,l_Sonset,l_Pat_dt,l_Pat_usec,l_Sat_dt,l_Sat_usec,l_Pres,l_Sres,l_Pqual,l_Squal,
	l_codalen,l_codawt,l_Pfm,l_Sfm,l_datasrc,l_Md,l_azm,l_takeoff,l_dist,l_Pwt,l_Swt,l_pamp,l_codalenObs,
	l_ccntr_0,l_ccntr_1,l_ccntr_2,l_ccntr_3,l_ccntr_4,l_ccntr_5,l_caav_0,l_caav_1,l_caav_2,l_caav_3,l_caav_4,l_caav_5,l_version);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_magnitude_summary`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_magnitude_summary`(

in_ewname          VARCHAR(80)     /*Host running the EW Module*/,
in_modname         VARCHAR(32)     /*Module name*/,
in_qid             BIGINT       /*event id from binder */,
in_origin_version  INT,
in_mag             DOUBLE,
in_error           DOUBLE,
in_quality         DOUBLE,
in_mindist         DOUBLE,
in_azimuth         INT,
in_nstations       INT,
in_nchannels       INT,
in_qauthor         VARCHAR(32),
in_qdds_version    INT,
in_imagtype        INT,
in_szmagtype       VARCHAR(32),
in_algorithm       VARCHAR(32),
in_mag_quality        CHAR(2)
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname       VARCHAR(32);
DECLARE l_qid          BIGINT;
DECLARE l_origin_version INT;
DECLARE l_mag       DOUBLE;
DECLARE l_error     DOUBLE;
DECLARE l_quality   DOUBLE;
DECLARE l_mindist   DOUBLE;
DECLARE l_azimuth   INT;
DECLARE l_nstations INT;
DECLARE l_nchannels INT;
DECLARE l_qauthor   VARCHAR(32);
DECLARE l_qdds_version   INT;
DECLARE l_imagtype  INT;
DECLARE l_szmagtype VARCHAR(32);
DECLARE l_algorithm VARCHAR(32);
DECLARE l_mag_quality  CHAR(2);

DECLARE l_ewmodid               BIGINT;
DECLARE l_sqkseq                BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

SET l_qid=in_qid;
SET l_origin_version=in_origin_version;
SET l_mag=in_mag;
SET l_error=in_error;
SET l_quality=in_quality;
SET l_mindist=in_mindist;
SET l_azimuth=in_azimuth;
SET l_nstations=in_nstations;
SET l_nchannels=in_nchannels;
SET l_qauthor=in_qauthor;
SET l_qdds_version=in_qdds_version;
SET l_imagtype=in_imagtype;
SET l_szmagtype=in_szmagtype;
SET l_algorithm=in_algorithm;
SET l_mag_quality=in_mag_quality;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qid, l_sqkseq);

INSERT INTO `ew_magnitude_summary`(ewmodid,ewsqkid,version,mag,error,quality,mindist,azimuth,nstations,nchannels,qauthor,qdds_version,imagtype,szmagtype,algorithm,mag_quality)
    VALUES (l_ewmodid,l_sqkseq,l_origin_version,l_mag,l_error,l_quality,l_mindist,l_azimuth,l_nstations,l_nchannels,l_qauthor,l_qdds_version,l_imagtype,l_szmagtype,l_algorithm,l_mag_quality);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_magnitude_phase`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_magnitude_phase`(
in_ewname                 VARCHAR(80)     /*Host running the EW Module*/,
in_modname                VARCHAR(32)     /*Module name*/,
in_qid                    BIGINT      /*quake id*/,
in_version                BIGINT      /*version number of the origin*/,
in_site                   VARCHAR(5)  /*site code, null terminated*/,
in_comp                   VARCHAR(4)  /*component code, null terminated*/,
in_net                    VARCHAR(3)  /*seismic network code, null terminated*/,
in_loc                    VARCHAR(2)  /*location code, null terminated*/,
in_mag                    DOUBLE      /*local magnitude for this channel*/,
in_dist                   DOUBLE      /*station-event distance used for local magnitude*/,
in_mag_correction         DOUBLE      /*mag_correction that was added to get this local mag*/,
in_Time1                  VARCHAR(30) /*time of the first pick - a string in datetime format*/,
in_Amp1                   DOUBLE      /*amplitude of the first pick*/,
in_Period1                DOUBLE      /*period associated with the first pick*/,
in_Time2                  VARCHAR(30) /*time of the second pick - a string in datetime format*/,
in_Amp2                   DOUBLE      /*amplitude of the second pick (if used)*/,
in_Period2                DOUBLE      /*period of the second pick (if used)*/
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname     VARCHAR(32);
DECLARE l_qid         BIGINT     ;
DECLARE l_version     BIGINT     ;
DECLARE l_site        VARCHAR(5) ;
DECLARE l_net         VARCHAR(3) ;
DECLARE l_comp        VARCHAR(4) ;
DECLARE l_loc         VARCHAR(2) ;
DECLARE l_mag         DOUBLE     ;
DECLARE l_dist        DOUBLE     ;
DECLARE l_mag_correction  DOUBLE     ;
DECLARE l_Time1_dt   DATETIME   ;
DECLARE l_Time1_usec  BIGINT     ;
DECLARE l_Amp1        DOUBLE     ;
DECLARE l_Period1     DOUBLE     ;
DECLARE l_Time2_dt   DATETIME   ;
DECLARE l_Time2_usec  BIGINT     ;
DECLARE l_Amp2        DOUBLE     ;
DECLARE l_Period2     DOUBLE     ;

DECLARE l_ewmodid               BIGINT;
DECLARE l_sqkseq                BIGINT;
DECLARE l_scnlid               BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;


IF TRIM(in_qid)='' THEN 
        SELECT 'ERROR: quake id can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qid = in_qid;

SET l_version = in_version;
SET l_site = in_site;
SET l_net = in_net;
SET l_comp = in_comp;
SET l_loc = in_loc;

SET l_mag = in_mag;
SET l_dist = in_dist;
SET l_mag_correction = in_mag_correction;

SET l_Time1_dt = in_Time1;
SELECT MICROSECOND(in_Time1) INTO l_Time1_usec;

SET l_Amp1 = in_Amp1;
SET l_Period1 = in_Period1;

SET l_Time2_dt = in_Time2;
SELECT MICROSECOND(in_Time2) INTO l_Time2_usec;

SET l_Amp2 = in_Amp2;
SET l_Period2 = in_Period2;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qid, l_sqkseq);
CALL `sp_get_ew_scnl_out`(l_site, l_comp, l_net, l_loc, l_scnlid);

INSERT INTO `ew_magnitude_phase` (ewmodid, ewsqkid, version, scnlid, mag, dist, mag_correction, Time1_dt, Time1_usec, Amp1, Period1, Time2_dt, Time2_usec, Amp2, Period2)
VALUES (l_ewmodid,l_sqkseq, l_version, l_scnlid, l_mag, l_dist, l_mag_correction, l_Time1_dt, l_Time1_usec, l_Amp1, l_Period1, l_Time2_dt, l_Time2_usec, l_Amp2, l_Period2);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_strongmotionII`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_strongmotionII`(
in_ewname        VARCHAR(80)     /*Host running the EW Module*/,
in_modname       VARCHAR(32)     /*Module name*/,
in_qid           BIGINT          /*quake id*/,
-- in_version       BIGINT          /*version number of the origin*/,
in_site          VARCHAR(5)      /*site code, null terminated*/,
in_comp          VARCHAR(3)      /*component code, null terminated*/,
in_net           VARCHAR(2)      /*seismic network code, null terminated*/,
in_loc           VARCHAR(2)      /*location code, null terminated*/,
in_qauthor       VARCHAR(32),
in_t             VARCHAR(30)     /*time: trigger reported by SM box - a string in datetime format*/,
in_t_alt         VARCHAR(30)     /*alternate time: trigger reported by SM box - a string in datetime format*/,
in_altcode       INT             /*code specifying the source of the alternate time field*/,
in_pga           DOUBLE          /*REQUIRED: peak ground acceleration (cm/s/s)*/,
in_tpga          VARCHAR(30)     /*OPTIONAL: time of pga - datetime part*/,
in_pgv           DOUBLE          /*REQUIRED: peak ground velocity (cm/s)*/,
in_tpgv          VARCHAR(30)     /*OPTIONAL: time of pgv - datetime part*/,
in_pgd           DOUBLE          /*REQUIRED: peak ground displacement (cm)*/,
in_tpgd          VARCHAR(30)     /*OPTIONAL: time of pgd - datetime part*/,
in_rsa           VARCHAR(256)    /*RSA(response spectrum accel)  string format: NRSA/periodRSA valueRSA/periodRSA valueRSA*/
)
uscita: BEGIN
DECLARE l_ewname      VARCHAR(80);
DECLARE l_modname     VARCHAR(32);
DECLARE l_qid         BIGINT;
-- DECLARE l_version     BIGINT;
DECLARE l_site        VARCHAR(5);
DECLARE l_net         VARCHAR(3);
DECLARE l_comp        VARCHAR(4);
DECLARE l_loc         VARCHAR(2);
DECLARE l_qauthor     VARCHAR(32);
DECLARE l_t_dt        DATETIME;
DECLARE l_t_usec      DOUBLE;
DECLARE l_t_alt_dt    DATETIME;
DECLARE l_t_alt_usec  DATETIME;
DECLARE l_altcode     INT;
DECLARE l_pga         DOUBLE;
DECLARE l_tpga_dt     DATETIME;
DECLARE l_tpga_usec   DOUBLE;
DECLARE l_pgv         DOUBLE;
DECLARE l_tpgv_dt     DATETIME;
DECLARE l_tpgv_usec   DOUBLE;
DECLARE l_pgd         DOUBLE;
DECLARE l_tpgd_dt     DATETIME;
DECLARE l_tpgd_usec   DOUBLE;
DECLARE l_rsa         VARCHAR(256);

DECLARE l_ewmodid     BIGINT;
DECLARE l_sqkseq      BIGINT;
DECLARE l_scnlid      BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

IF TRIM(in_qid)='' THEN 
        SELECT 'ERROR: quake id can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_qid = in_qid;

SET l_site = in_site;
SET l_net = in_net;
SET l_comp = in_comp;

IF TRIM(in_loc)='' THEN 
     SET in_loc = null;
END IF;
SET l_loc = in_loc;

SET l_qauthor=in_qauthor;

SET l_t_dt = in_t;
SELECT MICROSECOND(in_t) INTO l_t_usec;

SET l_t_alt_dt = in_t_alt;
/*IF TRIM(in_t_alt)='' THEN 
    SET l_t_alt_usec = '0';
ELSE*/
    SELECT MICROSECOND(in_t_alt) INTO l_t_alt_usec;
-- END IF;

SET l_altcode = in_altcode;
SET l_pga = in_pga;

SET l_tpga_dt = in_tpga;
SELECT MICROSECOND(in_tpga) INTO l_tpga_usec;

SET l_pgv = in_pgv;

SET l_tpgv_dt = in_tpgv;
SELECT MICROSECOND(in_tpgv) INTO l_tpgv_usec;

SET l_pgd = in_pgd;

SET l_tpgd_dt = in_tpgd;
SELECT MICROSECOND(in_tpgd) INTO l_tpgd_usec;

SET l_rsa = in_rsa;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);
CALL `sp_get_ew_sqkseq_out`(l_ewname, l_qid, l_sqkseq);
CALL `sp_get_ew_scnl_out`(l_site, l_comp, l_net, l_loc, l_scnlid);

INSERT INTO `ew_strongmotionII` (ewmodid,ewsqkid,scnlid,qauthor,
        t_dt,t_usec,t_alt_dt,t_alt_usec,altcode,
        pga,tpga_dt,tpga_usec,pgv,tpgv_dt,tpgv_usec,pgd,tpgd_dt,tpgd_usec,rsa)
VALUES (l_ewmodid,l_sqkseq,l_scnlid,l_qauthor,
        l_t_dt,l_t_usec,l_t_alt_dt,l_t_alt_usec,l_altcode,
        l_pga,l_tpga_dt,l_tpga_usec,l_pgv,l_tpgv_dt,l_tpgv_usec,l_pgd,l_tpgd_dt,l_tpgd_usec,l_rsa);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_ins_ew_error`$$
CREATE  DEFINER = CURRENT_USER  PROCEDURE `sp_ins_ew_error`(
in_ewname                VARCHAR(80) /* Host running the EW Module */,
in_modname               VARCHAR(32) /* Module name */,
in_time_dt               VARCHAR(30) /* Time of earthworm error message*/,
in_message               VARCHAR(256) /* Earthworm error message */
)
uscita: BEGIN
DECLARE l_ewname                VARCHAR(80);
DECLARE l_modname               VARCHAR(32);
DECLARE l_time_dt               DATETIME;
DECLARE l_message               VARCHAR(256);

DECLARE l_ewmodid               BIGINT;
DECLARE l_scnlid               BIGINT;

IF TRIM(in_ewname)='' THEN 
        SELECT 'ERROR: EW instance name can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_ewname=in_ewname;

IF TRIM(in_modname)='' THEN 
        SELECT 'ERROR: modname can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_modname=in_modname;

IF TRIM(in_time_dt)='' THEN 
        SELECT 'ERROR: time can not be empty' out_msg,-1 id;
        LEAVE uscita;
END IF;
SET l_time_dt=in_time_dt;

SET l_message=in_message;

CALL `sp_get_ew_module_out`(l_ewname, l_modname, l_ewmodid);

INSERT INTO `ew_error` (ewmodid,time_dt,message)
VALUES (l_ewmodid, l_time_dt, l_message);

SELECT LAST_INSERT_ID();

END$$
DELIMITER ;

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_sel_ew_event_phases` $$
CREATE PROCEDURE  `sp_sel_ew_event_phases`(
in_start_time       VARCHAR(50),
in_end_time         VARCHAR(50)
)
-- call sp_ew_event('hew1_mole','2010-04-08',now());
uscita: BEGIN
    
    IF fn_isdate(in_start_time) THEN
        SELECT'ERROR: start_time not datatime format yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;
    IF fn_isdate(in_end_time) THEN
        SELECT'ERROR: end_time not datatime format  yyy-mm-dd HH:mm:ss';
        LEAVE uscita;
    END IF;

    SELECT CAST(CONCAT(`e`.`ewsqkid`, '_', `e`.`version`) AS CHAR(50)) AS `sqkid_version`,
    `e`.`ewsqkid` AS `sqkid`,
    `e`.`qkseq` AS `qkseq`,
    `e`.`ewname` AS `instance`,
    CAST(CONCAT(`e`.`ot_dt`, '.', LEFT(`e`.`ot_usec`,4)) AS CHAR(50)) AS `ot_time`,
    `e`.`mag` AS `mag`,
    `e`.`mag_type` AS `mag_type`,
    `e`.`mag_error` AS `mag_error`,
    `e`.`arc_quality` AS `quality`,
    ROUND(`e`.`lat`,3) AS `lat`,
    ROUND(`e`.`lon`,3) AS `lon`,
    ROUND(`e`.`z`,3) AS `depth`,
    `e`.`erh` AS `error_h`,
    `e`.`erz` AS `error_z`,
    `e`.`nphtot` AS `nphtot`,
    `e`.`gap` AS `gap`,
    `e`.`dmin` AS `dmin`,
    `e`.`rms` AS `rms`,
    `e`.`ewname` AS `ewname`,
    `e`.`mag_ewmodid` AS `modmag`,
    `e`.`region` AS `region_name`,
    `e`.`version` AS `version`,
    `e`.`modified` AS `modified`,
    `s`.`net`,
    `s`.`sta`,
    `s`.`cha`,
    `p`.`dist`,
    `p`.`azm`,
    IF(`p`.`Ponset` = 'P', CONCAT('P', `p`.`Plabel`), IF(`p`.`Sonset` = 'S', CONCAT('S', `p`.`Slabel`), NULL)) as `phasename`,
    IF(`p`.`Ponset` = 'P', CAST(fn_get_str_from_dt_usec(`p`.`Pat_dt`, `p`.`Pat_usec`) AS CHAR), IF(`p`.`Sonset` = 'S',  CAST(fn_get_str_from_dt_usec(`p`.`Sat_dt`, `p`.`Sat_usec`) AS CHAR), NULL)) as `phasetime`,
    IF(`p`.`Ponset` = 'P', `p`.`Pwt`, IF(`p`.`Sonset` = 'S', `p`.`Swt`, NULL)) as `phaseweight`,
    IF(`p`.`Ponset` = 'P', `p`.`Pres`, IF(`p`.`Sonset` = 'S', `p`.`Sres`, NULL)) as `phaseresidual`,
    IF(`p`.`Ponset` = 'P', `p`.`Pqual`, IF(`p`.`Sonset` = 'S', `p`.`Squal`, NULL)) as `phasequality`
    -- TODO `m`.`mag` as `phasemagnitude`
    FROM `ew_events_summary` e
    JOIN `ew_arc_phase` p ON `p`.`ewsqkid` = `e`.`ewsqkid` AND `p`.`version` = `e`.`version`
    JOIN `ew_scnl` s ON `p`.`scnlid` = `s`.`id`
    -- TODO LEFT JOIN `ew_magnitude_phase` m ON `m`.`ewsqkid` = `e`.`ewsqkid` AND `m`.`version` = `e`.`version` AND `m`.`ewmodid` = `e`.`mag_ewmodid`
    WHERE `e`.`ot_dt` >= in_start_time 
        AND `e`.`ot_dt` <= in_end_time
    ORDER BY `e`.`ewsqkid` DESC, `e`.`version` DESC, `p`.`dist` ASC;
END $$
DELIMITER ;

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

