DROP PROCEDURE IF EXISTS `stp_recalcDailyMGP`;

CREATE PROCEDURE `stp_recalcDailyMGP`(
    IN `pDateStart` DATETIME,
    IN `pDateEnd` DATETIME,
    IN `pMachineId` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Ricalcolo statistiche giornaliere x MachGroup + Part da LogMachine'
BEGIN
   
    # Elimino dati eventualmente presenti nel periodo indicato	
    DELETE	
    FROM		PartList
    WHERE		(DtStart >= pDateStart AND DtStart < pDateEnd)
        OR		(DtEnd >= pDateStart AND DtEnd < pDateEnd);
    
    DELETE	
    FROM		MachGroupList
    WHERE		(DtStart >= pDateStart AND DtStart < pDateEnd)
        OR		(DtEnd >= pDateStart AND DtEnd < pDateEnd);
     
    DELETE	
    FROM		DayStat
    WHERE		DtRif>= pDateStart AND dtRif < pDateEnd;
    
    # creo record MachGroup con inizio e fine
    INSERT 	INTO MachGroupList(MachGroupId, ProjDbId, DtStart, DtEnd)	
    SELECT tab_s.MachGroupId, tab_s.ProjDbId, tab_s.DtStart, tab_e.DtEnd 
    FROM
    (
        SELECT * 
                ,@rownum := IF(GroupKey = @prev_col1, @rownum + 1, 1) AS numriga
              ,@prev_col1 := GroupKey
        FROM
        (
            SELECT	CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VarValue, ';',-2),';',1) AS INT) AS MachGroupId
                     , SUBSTRING_INDEX(VarValue, ';',-2) AS GroupKey
                     , ProjDbId
                     , DtEvent AS DtStart
            FROM		LogMachine
            WHERE		MachineID = pMachineId
                AND	(DtEvent >= pDateStart AND DtEvent < pDateEnd)
                AND	EvType = 2
                AND	RIGHT(VarValue,1) = 1
        ) t , (SELECT @rownum := 0, @prev_col1 := NULL) r
        ORDER BY GroupKey
    ) as tab_s
    JOIN
    (
        SELECT * 
                ,@rownum := IF(GroupKey = @prev_col1, @rownum + 1, 1) AS numriga
              ,@prev_col1 := GroupKey
        FROM
        (
            SELECT	CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VarValue, ';',-2),';',1) AS INT) AS MachGroupId
                     , SUBSTRING_INDEX(VarValue, ';',-2) AS GroupKey
                     , ProjDbId
                     , DtEvent AS DtEnd
            FROM		LogMachine
            WHERE		MachineID = pMachineId
                AND	(DtEvent >= pDateStart AND DtEvent < pDateEnd)
                AND	EvType = 2
                AND	RIGHT(VarValue,1) = 2
        ) t , (SELECT @rownum := 0, @prev_col1 := NULL) r
        ORDER BY GroupKey
    ) AS tab_e
    ON tab_s.MachGroupId = tab_e.MachGroupId AND tab_s.ProjDbId = tab_e.ProjDbId AND tab_s.numriga = tab_e.numriga;
    
    
    # creo record Part con inizio e fine
    INSERT 	INTO PartList(PartId, MachGroupId, ProjDbId, DtStart, DtEnd)	
    SELECT tab_s.Id, tab_s.MachGroupId,  tab_s.ProjDbId, tab_s.DtStart, tab_e.DtEnd 
    FROM
    (
        SELECT * 
                ,@rownum := IF(GroupKey = @prev_col1, @rownum + 1, 1) AS numriga
              ,@prev_col1 := GroupKey
        FROM
        (
            SELECT	CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VarValue, ';',-2),';',1) AS INT) AS Id
                     , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VarValue, ';',-3),';',1) AS INT) AS MachGroupId
                     , SUBSTRING_INDEX(VarValue, ';',-3) AS GroupKey
                     , ProjDbId
                     , DtEvent AS DtStart
            FROM		LogMachine
            WHERE		MachineID = pMachineId
                AND	(DtEvent >= pDateStart AND DtEvent < pDateEnd)
                AND	EvType = 1
                AND	RIGHT(VarValue,1) = 1
        ) t , (SELECT @rownum := 0, @prev_col1 := NULL) r
        ORDER BY GroupKey
    ) as tab_s
    JOIN
    (
        SELECT * 
                ,@rownum := IF(GroupKey = @prev_col1, @rownum + 1, 1) AS numriga
              ,@prev_col1 := GroupKey
        FROM
        (
            SELECT	CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VarValue, ';',-2),';',1) AS INT) AS Id
                     , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VarValue, ';',-3),';',1) AS INT) AS MachGroupId
                     , SUBSTRING_INDEX(VarValue, ';',-3) AS GroupKey
                     , ProjDbId
                     , DtEvent AS DtEnd
            FROM		LogMachine
            WHERE		MachineID = pMachineId
                AND	(DtEvent >= pDateStart AND DtEvent < pDateEnd)
                AND	EvType = 1
                AND	RIGHT(VarValue,1) = 2
        ) t , (SELECT @rownum := 0, @prev_col1 := NULL) r
        ORDER BY GroupKey
    ) AS tab_e
    ON tab_s.id = tab_e.id AND tab_s.MachGroupId = tab_e.MachGroupId AND tab_s.ProjDbId = tab_e.ProjDbId AND tab_s.numriga = tab_e.numriga;	

    # per giancarlo: verificare la procedura che crei correttamente part e machGroup, controllando che le date siano coerenti (eventi inizio/fine sequenziali), lasciando eventualmente fuori i dati "incompleti"; verificare con EMmanuele se evento inizio da prendere sia "il primo o il precedente" considerato che POTREMMO avere + record inizio che fine

    
    #SELECT	*
    #FROM		MachGroupList;	

    #SELECT	*
    #FROM		PartList;


END;