SET TERM ^ ;
CREATE PROCEDURE SP_MEL_RECEPCIONISTAS (DATA1 DATE,DATA2 DATE)
RETURNS (ID INTEGER,
         DESCRICAO VARCHAR(10),
         QTD INTEGER,
         VALOR DECIMAL(15,2),
         QTD1 INTEGER,
         QTD2 INTEGER,
         QTD3 INTEGER,
         QTD4 INTEGER)
AS BEGIN
   FOR
        SELECT CAST(0 AS INTEGER) AS ID,
               CAST(W.USUARIO_ENTRADA AS VARCHAR(100)) AS DESCRICAO,
               COUNT(W.ID_WALK_IN) AS QTD,
               SUM(CAST(W.DINHEIRO + W.VISA + W.MASTER + W.HIPER + W.CHEQUE + W.NOTA + W.DEBVISA + W.DEBMASTER AS DECIMAL(15,2))) AS VALOR,
               COUNT(W.QTD_ADULTOS) AS QTD1,
               CAST(0 AS INTEGER) AS QTD2,
               CAST(0 AS INTEGER) AS QTD3,
               CAST(0 AS INTEGER) AS QTD4
        FROM WALK_IN W
        WHERE W.EXCLUIDO = 'N'
        AND W.QTD_ADULTOS = 1
        AND W.DATA_ENTRADA >= :DATA1
        AND W.DATA_ENTRADA <= :DATA2
        GROUP BY 1,2,6,7,8
        UNION
        SELECT CAST(0 AS INTEGER) AS ID,
               CAST(W.USUARIO_ENTRADA AS VARCHAR(100)) AS DESCRICAO,
               COUNT(W.ID_WALK_IN) AS QTD,
               SUM(CAST(W.DINHEIRO + W.VISA + W.MASTER + W.HIPER + W.CHEQUE + W.NOTA + W.DEBVISA + W.DEBMASTER AS DECIMAL(15,2))) AS VALOR,
               CAST(0 AS INTEGER) AS QTD1,
               COUNT(W.QTD_ADULTOS) AS QTD2,
               CAST(0 AS INTEGER) AS QTD3,
               CAST(0 AS INTEGER) AS QTD4
        FROM WALK_IN W
        WHERE W.EXCLUIDO = 'N'
        AND W.QTD_ADULTOS = 2
        AND W.DATA_ENTRADA >= :DATA1
        AND W.DATA_ENTRADA <= :DATA2
        GROUP BY 1,2,5,7,8
        UNION
        SELECT CAST(0 AS INTEGER) AS ID,
               CAST(W.USUARIO_ENTRADA AS VARCHAR(100)) AS DESCRICAO,
               COUNT(W.ID_WALK_IN) AS QTD,
               SUM(CAST(W.DINHEIRO + W.VISA + W.MASTER + W.HIPER + W.CHEQUE + W.NOTA + W.DEBVISA + W.DEBMASTER AS DECIMAL(15,2))) AS VALOR,
               CAST(0 AS INTEGER) AS QTD1,
               CAST(0 AS INTEGER) AS QTD2,
               COUNT(W.QTD_ADULTOS) AS QTD3,
               CAST(0 AS INTEGER) AS QTD4
        FROM WALK_IN W
        WHERE W.EXCLUIDO = 'N'
        AND W.QTD_ADULTOS = 3
        AND W.DATA_ENTRADA >= :DATA1
        AND W.DATA_ENTRADA <= :DATA2
        GROUP BY 1,2,5,6,8
        UNION
        SELECT CAST(0 AS INTEGER) AS ID,
               CAST(W.USUARIO_ENTRADA AS VARCHAR(100)) AS DESCRICAO,
               COUNT(W.ID_WALK_IN) AS QTD,
               SUM(CAST(W.DINHEIRO + W.VISA + W.MASTER + W.HIPER + W.CHEQUE + W.NOTA + W.DEBVISA + W.DEBMASTER AS DECIMAL(15,2))) AS VALOR,
               CAST(0 AS INTEGER) AS QTD1,
               CAST(0 AS INTEGER) AS QTD2,
               CAST(0 AS INTEGER) AS QTD3,
               COUNT(W.QTD_ADULTOS) AS QTD4
        FROM WALK_IN W
        WHERE W.EXCLUIDO = 'N'
        AND W.QTD_ADULTOS = 4
        AND W.DATA_ENTRADA >= :DATA1
        AND W.DATA_ENTRADA <= :DATA2
        GROUP BY 1,2,5,6,7
        ORDER BY 2,5
        INTO :ID,:DESCRICAO,:QTD,:VALOR,:QTD1,:QTD2,:QTD3,:QTD4
   DO
SUSPEND;

END  ^
SET TERM ;^

UPDATE PARAMETROS SET SCRIPT = 114;
