--SALES(5)

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES5] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME, @CURR_YEAR1 DATETIME, @CURR_YEAR2 DATETIME, @GETDATE DATETIME, 
@FOODCAT1 INT, @FOODCAT2 INT, @FOODCAT3 INT, @FOODCAT4 INT, @FOODCAT5 INT;

SELECT TOP 1 @CURR_DAY = SHIFTDATE, @RES = IRESTAURANT FROM GLOBALSHIFTS WHERE CLOSED = 0 ORDER BY SHIFTDATE DESC;

SET @CURR_DAY = DATEADD(DAY,DATEDIFF(DAY,0,@CURR_DAY),0);
SET @CURR_MONTH1 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY), 0);
SET @CURR_MONTH2 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY)+1, -1);
SET @CURR_YEAR1 = DATEADD(YEAR, DATEDIFF(YEAR,0,@CURR_DAY), 0);
SET @CURR_YEAR2 = DATEADD(YEAR, DATEDIFF(YEAR,0,@CURR_DAY)+1, -1);
/* *************** */

SET @GETDATE = GETDATE();

EXEC RATING_REP_CALC NULL, NULL, @VER = NULL, @COUNTFIELD = NULL, @AMOUNTFIELD = NULL,
@FOODCAT1 = @FOODCAT1 OUTPUT, @FOODCAT2 = @FOODCAT2 OUTPUT, @FOODCAT3 = @FOODCAT3 OUTPUT, @FOODCAT4 = @FOODCAT4 OUTPUT, @FOODCAT5 = @FOODCAT5 OUTPUT, 
@FOODFIELD1 = NULL, @FOODFIELD2 = NULL, @FOODFIELD3 = NULL, @FOODFIELD4 = NULL, @FOODFIELD5 = NULL;


SELECT CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CGNAME AS CATEGORY,
DATEPART(MONTH,CYEAR.DATE) AS YDATE, ISNULL(CYEAR.AMOUNT,0) + ISNULL(CDAY.AMOUNT,0) AS YAMOUNT, ISNULL(CYEAR.COUNT,0) + ISNULL(CDAY.COUNT,0) AS YCOUNT,
ISNULL(CYEAR.DCOUNT,0) + ISNULL(CDAY.DCOUNT,0) AS YDCOUNT,
ISNULL(CCAT.AMOUNT,0) + ISNULL(CCDAY.AMOUNT,0) AS CAMOUNT,ISNULL(CCAT.COUNT,0) AS CCOUNT,ISNULL(CCAT.DCOUNT,0) + ISNULL(CCDAY.DCOUNT,0) AS CDCOUNT
FROM 
(
	SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, CG.SIFR*256+CG.NUMINGROUP AS CGSIFR, CG.NAME AS CGNAME
	FROM RESTAURANTS R, CLASSIFICATORGROUPS CG 
	WHERE R.SIFR = @RES AND CG.SIFR*256+CG.NUMINGROUP IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
) DUMMY
OUTER APPLY (
	SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,DATE), 0) AS DATE, SUM(AMOUNT) AS AMOUNT, SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT
	FROM LAST_DAYS LD WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @CURR_YEAR1 AND @CURR_YEAR2 AND LD.DATE <> @CURR_DAY
	GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0,DATE), 0)
) CYEAR
OUTER APPLY (
	SELECT SUM(AMOUNT) AS AMOUNT, SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT
	FROM LAST_FOODCAT_DAYS LFD WHERE LFD.RESTAURANT = @RES AND CASE LFD.CATEGORY WHEN 0 THEN @FOODCAT5 ELSE LFD.CATEGORY END = DUMMY.CGSIFR AND DATEADD(MONTH, DATEDIFF(MONTH,0,LFD.DATE), 0) = CYEAR.DATE AND LFD.DATE <> @CURR_DAY
) CCAT
LEFT JOIN
(
	SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,GS.SHIFTDATE), 0) AS DATE, DG.PARENT, SUM(PB.PAYSUM) AS AMOUNT, SUM(PB.QUANTITY) AS DCOUNT
	FROM GLOBALSHIFTS GS
	JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
	JOIN PRINTCHECKS PC ON PC.MIDSERVER = O.MIDSERVER AND PC.VISIT = O.VISIT AND PC.ORDERIDENT = O.IDENTINVISIT AND PC.STATE = 6
	JOIN CURRLINES CL ON CL.MIDSERVER = PC.MIDSERVER AND CL.VISIT = PC.VISIT AND CL.CHECKUNI = PC.UNI
	JOIN PAYBINDINGS PB ON PB.MIDSERVER = CL.MIDSERVER AND PB.VISIT = CL.VISIT AND PB.CURRUNI = CL.UNI
	JOIN SESSIONDISHES SD ON SD.MIDSERVER = PB.MIDSERVER AND SD.VISIT = PB.VISIT AND SD.UNI = PB.DISHUNI
	JOIN MENUITEMS MI ON MI.SIFR = SD.SIFR
	LEFT JOIN DISHGROUPS DG ON DG.CHILD = MI.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
	WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY
	GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0,GS.SHIFTDATE), 0),DG.PARENT
--

) CCDAY ON CCDAY.PARENT = DUMMY.CGSIFR AND CCDAY.DATE = CYEAR.DATE
LEFT JOIN
(
	SELECT	DATEADD(MONTH, DATEDIFF(MONTH,0,GS.SHIFTDATE), 0) AS DATE, SUM(PB.PAYSUM) AS AMOUNT, COUNT(DISTINCT PC.GLOBALIDENT) AS COUNT,
	SUM(PB.QUANTITY) AS DCOUNT
	FROM GLOBALSHIFTS GS
	JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
	JOIN PRINTCHECKS PC ON PC.MIDSERVER = O.MIDSERVER AND PC.VISIT = O.VISIT AND PC.ORDERIDENT = O.IDENTINVISIT AND PC.STATE = 6
	JOIN CURRLINES CL ON CL.MIDSERVER = PC.MIDSERVER AND CL.VISIT = PC.VISIT AND CL.CHECKUNI = PC.UNI
	JOIN PAYBINDINGS PB ON PB.MIDSERVER = CL.MIDSERVER AND PB.VISIT = CL.VISIT AND PB.CURRUNI = CL.UNI AND PB.STATE = 6
	WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY
	GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0,GS.SHIFTDATE), 0)
) CDAY ON CCDAY.DATE = CYEAR.DATE
ORDER BY DATEPART(MONTH,CYEAR.DATE), DUMMY.CGNAME;

END;