CREATE PROCEDURE SP_MEDIA_INCLUITE
AS
DECLARE VARIABLE DATAINICIAL DATE;
DECLARE VARIABLE PREFIXO VARCHAR(5);
DECLARE VARIABLE TIPO VARCHAR(6);
DECLARE VARIABLE DATA DATE;
DECLARE VARIABLE KMINICIAL NUMERIC(18,2);
DECLARE VARIABLE KMFINAL NUMERIC(18,2);
DECLARE VARIABLE MEDIA NUMERIC(18,2);
DECLARE VARIABLE COMBUSTIVELQTD NUMERIC(18,2);
DECLARE VARIABLE COMBUSTIVELVLR NUMERIC(18,2);
DECLARE VARIABLE GRAXAQTD NUMERIC(18,2);
DECLARE VARIABLE MANUTENCAOVLR NUMERIC(18,2);
DECLARE VARIABLE LUBRIFACANTEQTD NUMERIC(18,2);
DECLARE VARIABLE FILTROQTD NUMERIC(18,2);
DECLARE VARIABLE FILTROVLR NUMERIC(18,2);
DECLARE VARIABLE MAQUINAVEICULO VARCHAR(1);
DECLARE VARIABLE OBSERVACAO VARCHAR(200);
DECLARE VARIABLE RESPONSAVEL VARCHAR(100);
DECLARE VARIABLE HORAKM NUMERIC(18,2);
DECLARE VARIABLE IMPORTACAO INTEGER;
DECLARE VARIABLE DATAFINAL DATE;
BEGIN
/*
 PONTOS IMPORTANSSIMOS : ERROS QUE NÃO PODERÍAM DAR
 MEDIA, MEDIALEVES, MEDIAPESADOS COM 0;
 KMINICIAL E KMFINAL IGUAIS;
 QTDCOMBUSTIVEL 0;
*/
  FOR
    SELECT PREFIXO, DATA FROM IMPORTACAO INTO REFIXO, ATA
  DO
  BEGIN
    FOR
        SELECT PREFIXO, TIPO, DATA, KMFINAL, OBSERVACAO, RESPONSAVEL
        FROM MEDIAINTERMEDIARIA
        WHERE  PREFIXO = REFIXO AND DATA > ATA  AND (COALESCE(MEDIALEVES,0) <> 0)
        ORDER BY 1,3
        INTO REFIXO, :TIPO, ATAFINAL, :KMFINAL, :OBSERVACAO, :RESPONSAVEL
    DO
    BEGIN
        SELECT FIRST 1 DATA FROM MEDIAINTERMEDIARIA
        WHERE PREFIXO = REFIXO AND DATA < ATAFINAL AND (COALESCE(MEDIALEVES,0) <> 0)
        ORDER BY DATA DESC INTO ATAINICIAL;
 
        IF (DATAINICIAL IS NULL) THEN
        BEGIN
            DATAINICIAL = DATAFINAL;
        END
 
        IF (DATAINICIAL <> DATAFINAL) THEN
        BEGIN
           DATAINICIAL = DATAINICIAL + 1;
        END
 
        SELECT SUM(MEDIA) AS MEDIA, SUM(COMBUSTIVELQTD) AS COMBUSTIVELQTD, SUM(COMBUSTIVELVLR) AS COMBUSTIVELVLR, SUM(GRAXAQTD) AS GRAXAQTD, SUM(MANUTENCAOVLR) AS MANUTENCAOVLR, SUM(LUBRIFACANTEQTD) AS LUBRIFACANTEQTD, SUM(FILTROQTD) AS FILTROQTD, SUM(FILTROVLR) AS FILTROVLR
        FROM MEDIAINTERMEDIARIA
        WHERE PREFIXO = REFIXO AND DATA >= ATAINICIAL AND DATA <= ATAFINAL
        INTO :MEDIA, :COMBUSTIVELQTD, :COMBUSTIVELVLR, :GRAXAQTD, :MANUTENCAOVLR, :LUBRIFACANTEQTD, :FILTROQTD, :FILTROVLR;
 
        SELECT MIN(KMINICIAL)
        FROM MEDIAINTERMEDIARIA
        WHERE PREFIXO = REFIXO AND DATA = ATAINICIAL
        INTO :KMINICIAL;
        IF ((COALESCE(KMINICIAL,0) = 0) OR (KMINICIAL IS NULL)) THEN
        BEGIN
            SELECT MIN(KMFINAL)
            FROM MEDIAINTERMEDIARIA
            WHERE PREFIXO = REFIXO AND DATA = ATAINICIAL – 1
            INTO :KMINICIAL;
        END
        IF ((COALESCE(KMINICIAL,0) <> 0) AND (COALESCE(KMFINAL,0) <> 0) AND (COALESCE(COMBUSTIVELQTD,0) <> 0) AND ( (COALESCE(KMINICIAL,0) – COALESCE(KMFINAL,0) <> 0))  ) THEN
        BEGIN
            IF (TIPO = ‘L’) THEN
                MEDIA = COALESCE( (KMFINAL – KMINICIAL) / COMBUSTIVELQTD, 0 );
            IF (TIPO = ‘P’) THEN
                MEDIA = COALESCE( COMBUSTIVELQTD / (KMFINAL – KMINICIAL), 0 );
        END
        ELSE
        BEGIN
            MEDIA = 0;
        END
        EXECUTE PROCEDURE SP_MEDIA_INCLUI ( REFIXO, ATAINICIAL, ATAFINAL, MEDIA, :KMINICIAL, :KMFINAL, :COMBUSTIVELVLR, :GRAXAQTD, :MANUTENCAOVLR, :COMBUSTIVELQTD, :LUBRIFACANTEQTD, :KMFINAL – :KMINICIAL, :FILTROQTD, :FILTROVLR, :TIPO);
 
        SELECT COALESCE(COUNT(*),0) FROM IMPORTACAO WHERE PREFIXO = REFIXO INTO :IMPORTACAO;
        IF (IMPORTACAO = 0) THEN
        BEGIN
            INSERT INTO IMPORTACAO ( PREFIXO, DATA) VALUES (:PREFIXO,:DATA);
        END
        ELSE
        BEGIN
          UPDATE IMPORTACAO
          SET  DATA = ATAFINAL
          WHERE (PREFIXO = REFIXO);
        END
   /*     DELETE FROM MEDIA WHERE PREFIXO = REFIXO AND (MEDIA = 0 OR MEDIA IS NULL OR MEDIA < 0);*/
 /*       DELETE FROM MEDIAINTERMEDIARIA WHERE PREFIXO = REFIXO;*/
    END
  END
  DELETE FROM MEDIA WHERE MEDIA = 0 OR MEDIA IS NULL OR MEDIA < 0;
  DELETE FROM MEDIAINTERMEDIARIA;
END