Запросы в SQL для получения общей выручки

Пример запроса:

Пример ответа:
CREATE OR ALTER PROCEDURE dbo.RK7SQLPROCGETRESTAURANTS
AS
BEGIN
SET NOCOUNT ON
SELECT R."SIFR", R."GUIDSTRING", R."CODE", R."NAME"
  FROM "RESTAURANTS" R
 WHERE R."STATUS" = 3;
END

Пример запроса:

Пример ответа:
CREATE OR ALTER PROCEDURE dbo.RK7SQLPROCGETCLASSIFICATIONS
AS
BEGIN
SET NOCOUNT ON
SELECT CG."IDENT" AS CLASSIFICATION_ID, CG."NAME" AS CLASSIFICATION_NAME, CG1."IDENT" AS GROUP_ID, CG1."NAME" AS GROUP_NAME
  FROM CLASSIFICATORGROUPS CG
    LEFT JOIN CLASSIFICATORGROUPS CG1 ON (CG1."PARENT" = CG."IDENT" AND CG1."STATUS" = 3)
 WHERE CG."PARENT" = 0 AND CG."STATUS" = 3;
END

Пример запроса:

Пример ответа:
/****** Object:  StoredProcedure [dbo].[RK7SQLPROCGETREVENUE]    Script Date: 23.10.2025 15:32:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER PROCEDURE [dbo].[RK7SQLPROCGETREVENUE]
(
    @STARTDATE NVARCHAR(50),
    @ENDDATE NVARCHAR(50),
    @CLASSIFICATORGROUPID INT =0
)
AS
BEGIN


SET NOCOUNT ON;


DECLARE @START_DATE DATETIME = CONVERT(DATETIME, @STARTDATE, 112);
DECLARE @END_DATE DATETIME = DATEADD(day, 1, CONVERT(DATETIME, @ENDDATE, 112));


DECLARE @CLASSIFICATOR_GROUP_ID INT = CASE
  WHEN @CLASSIFICATORGROUPID = 0 THEN (SELECT "SHORTVALUE" FROM "PARAMETERS" WHERE "SIFR" = 6)
  ELSE @CLASSIFICATORGROUPID
END;


SELECT
    GS1."SHIFTNUM" AS "SHIFT_NUM",
    GS1."IRESTAURANT" AS "RESTAURANT_ID",
    C1."SIFR" AS "CURRENCY_ID",
    C1."NAME" AS "CURRENCY_NAME",
    ISNULL(CLG1."IDENT", 0) AS "CATEGORY_ID",
    CASE
        WHEN CLG1."IDENT" is null THEN N'Без категории'
        ELSE CLG1."NAME"
    END AS "CATEGORY_NAME",
    sum(PB1."PAYSUM") AS "PAYSUM"
  , CSH.CODE AS "CASH_CODE"
FROM "PAYBINDINGS" PB1
-- выручка
JOIN "CURRLINES" CL1
    ON (CL1."VISIT" = PB1."VISIT") AND (CL1."MIDSERVER" = PB1."MIDSERVER") AND (CL1."UNI" = PB1."CURRUNI")
JOIN "PRINTCHECKS" PC1
    ON (PC1."VISIT" = CL1."VISIT") AND (PC1."MIDSERVER" = CL1."MIDSERVER") AND (PC1."UNI" = CL1."CHECKUNI")
-- общая смена
JOIN "ORDERS" O1
    ON (O1."VISIT" = PB1."VISIT") AND (O1."MIDSERVER" = PB1."MIDSERVER") AND (O1."IDENTINVISIT" = PB1."ORDERIDENT")
JOIN "GLOBALSHIFTS" GS1
    ON (GS1."MIDSERVER" = O1."MIDSERVER") AND (GS1."SHIFTNUM" = O1."ICOMMONSHIFT")
-- блюда и категория блюд
LEFT JOIN "SESSIONDISHES" SD1
    ON (SD1."VISIT" = PB1."VISIT") AND (SD1."MIDSERVER" = PB1."MIDSERVER") AND (SD1."UNI" = PB1."DISHUNI")
LEFT JOIN "MENUITEMS" MI1
    ON (MI1."SIFR" = SD1."SIFR")
LEFT JOIN DISHGROUPS DG1
    ON (DG1."CHILD" = MI1."SIFR") AND (DG1."Classification" = @CLASSIFICATOR_GROUP_ID)
LEFT JOIN "CLASSIFICATORGROUPS" CLG1
    ON CLG1."IDENT" = DG1."PARENT"  
-- ресторан
LEFT JOIN "CASHGROUPS" CG1
    ON (CG1."SIFR" = PB1."MIDSERVER")
LEFT JOIN "RESTAURANTS" R1
    ON (R1."SIFR" = CG1."RESTAURANT")
-- валюта
LEFT JOIN "PAYMENTS" P1
    ON (P1."VISIT" = CL1."VISIT") AND (P1."MIDSERVER" = CL1."MIDSERVER") AND (P1."UNI" = CL1."PAYUNIFOROWNERINFO")
LEFT JOIN "CURRENCIES" C1
    ON (C1."SIFR" = P1."SIFR")
LEFT JOIN CASHES CSH
    ON CSH.SIFR = O1.STATIONID
WHERE 
    GS1."STATUS" = 3 
    AND GS1."SHIFTDATE" >= @START_DATE AND GS1."SHIFTDATE" < @END_DATE
    AND (PC1."STATE" = 6)
    AND (PC1."IGNOREINREP" = 0)
GROUP BY
    GS1."SHIFTNUM",
    GS1."IRESTAURANT",
    CLG1."IDENT",
    C1."SIFR",
    C1."NAME",
    CASE
        WHEN CLG1."IDENT" is null THEN N'Без категории'
        ELSE CLG1."NAME"
    END
  , CSH.CODE
;


END