SET TERM ^ ;

create or alter procedure VENTAS_SEMANALES (
    FECHA date)
returns (
    LUNES numeric(18,2),
    MARTES numeric(18,2),
    MIERCOLES numeric(18,2),
    JUEVES numeric(18,2),
    VIERNES numeric(18,2),
    SABADO numeric(18,2),
    DOMINGO numeric(18,2),
    TOTAL numeric(18,2))
as
declare variable LDFECINI date;
BEGIN
   LDFECINI = (SELECT :FECHA - EXTRACT(WEEKDAY FROM :FECHA) FROM RDB$DATABASE);
   WITH VENTASDIA AS (
      SELECT
         M.FACT_FECHA,
         SUM(M.FACT_TOTAL) AS VENTAS
      FROM
         FACTURAS M
      WHERE
         M.FACT_ANULADO = 'N'
      GROUP BY
         1
   )
   SELECT
      COALESCE(D0.VENTAS, 0),
      COALESCE(D1.VENTAS, 0),
      COALESCE(D2.VENTAS, 0),
      COALESCE(D3.VENTAS, 0),
      COALESCE(D4.VENTAS, 0),
      COALESCE(D5.VENTAS, 0),
      COALESCE(D6.VENTAS, 0)
   FROM
      RDB$DATABASE
   LEFT JOIN
      VENTASDIA D0
         ON D0.FACT_FECHA = :LDFECINI + 1
   LEFT JOIN
      VENTASDIA D1
         ON D1.FACT_FECHA = :LDFECINI + 2
   LEFT JOIN
      VENTASDIA D2
         ON D2.FACT_FECHA = :LDFECINI + 3
   LEFT JOIN
      VENTASDIA D3
         ON D3.FACT_FECHA = :LDFECINI + 4
   LEFT JOIN
      VENTASDIA D4
         ON D4.FACT_FECHA = :LDFECINI + 5
   LEFT JOIN
      VENTASDIA D5
         ON D5.FACT_FECHA = :LDFECINI + 6
   LEFT JOIN
      VENTASDIA D6
         ON D6.FACT_FECHA = :LDFECINI + 7
   INTO
      :LUNES,
      :MARTES,
      :MIERCOLES,
      :JUEVES,
      :VIERNES,
      :SABADO,
      :DOMINGO;
      TOTAL = LUNES+MARTES+MIERCOLES+JUEVES+VIERNES+SABADO+DOMINGO;
  SUSPEND;
END^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON FACTURAS TO PROCEDURE VENTAS_SEMANALES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE VENTAS_SEMANALES TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE VENTAS_SEMANALES TO SYSDBA;