Samuele Locatelli
129 lines
4.9 KiB
SQL
129 lines
4.9 KiB
SQL
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; |