--RATING(1) 

CREATE PROCEDURE [dbo].[RK7SQLPROCRATING1] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

--use delivery_test;

DECLARE @RES INT, @FOODCAT1 INT, @FOODCAT2 INT, @FOODCAT3 INT, @FOODCAT4 INT, @FOODCAT5 INT, 
@FOODFIELD1 INT, @FOODFIELD2 INT, @FOODFIELD3 INT, @FOODFIELD4 INT, @FOODFIELD5 INT,
@CURR_DAY DATETIME, @CURR_WEEK1 DATETIME, @CURR_WEEK2 DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME, @GETDATE DATETIME;


/* INPUT VARIABLES */
--SET @RES = 1021813; --CHOSEN RESTAURANT;
SELECT TOP 1 @CURR_DAY = SHIFTDATE, @RES = IRESTAURANT FROM GLOBALSHIFTS WHERE CLOSED = 0 ORDER BY SHIFTDATE DESC;

/* *************** */

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 = @FOODFIELD1 OUTPUT, @FOODFIELD2 = @FOODFIELD2 OUTPUT, @FOODFIELD3 = @FOODFIELD3 OUTPUT, @FOODFIELD4 = @FOODFIELD4 OUTPUT, @FOODFIELD5 = @FOODFIELD5 OUTPUT;

SET @CURR_DAY = DATEADD(DAY,DATEDIFF(DAY,0,@CURR_DAY),0);
SET @CURR_WEEK1 = DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,@CURR_DAY-@@DATEFIRST), 0);
SET @CURR_WEEK2 = DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,@CURR_DAY-@@DATEFIRST)+1, -1);
SET @CURR_MONTH1 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY), 0);
SET @CURR_MONTH2 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY)+1, -1);

--COUNT - CHECK COUNT, DCOUNT - DISH COUNT
SELECT	CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CATEGORY AS CATEGORY, DUMMY.PLANRATING AS PLANRATING,
		ISNULL(CDAY.AMOUNT,0)  AS CD_AMOUNT,ISNULL(CDAY_CHECKS.COUNT,0) AS CD_COUNT, ISNULL(CDAY.DCOUNT,0) AS CD_DCOUNT,
		ISNULL(CWEEK.AMOUNT,0) + ISNULL(CDAY.AMOUNT,0) AS CW_AMOUNT, ISNULL(CWEEK_CHECKS.COUNT,0) + ISNULL(CDAY_CHECKS.COUNT,0) AS CW_COUNT, ISNULL(CWEEK.DCOUNT,0) + ISNULL(CDAY.DCOUNT,0) AS CW_DCOUNT,
		ISNULL(CMONTH.AMOUNT,0) + ISNULL(CDAY.AMOUNT,0) AS CM_AMOUNT, ISNULL(CMONTH_CHECKS.COUNT,0) + ISNULL(CDAY_CHECKS.COUNT,0) AS CM_COUNT, ISNULL(CMONTH.DCOUNT,0) + ISNULL(CDAY.DCOUNT,0) AS CM_DCOUNT
FROM 
(
	SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, CG.SIFR*256+CG.NUMINGROUP AS CAT, CG.NAME AS CATEGORY, 
	CASE CG.SIFR*256+CG.NUMINGROUP WHEN @FOODCAT1 THEN ISNULL(CAST(GPD1.PROPERTYVAL AS FLOAT),0)
	WHEN @FOODCAT2 THEN ISNULL(CAST(GPD2.PROPERTYVAL AS FLOAT),0)
	WHEN @FOODCAT3 THEN ISNULL(CAST(GPD3.PROPERTYVAL AS FLOAT),0)
	WHEN @FOODCAT4 THEN ISNULL(CAST(GPD4.PROPERTYVAL AS FLOAT),0)
	WHEN @FOODCAT5 THEN ISNULL(CAST(GPD5.PROPERTYVAL AS FLOAT),0) END AS PLANRATING
	FROM CLASSIFICATORGROUPS CG, RESTAURANTS R
	LEFT JOIN GENERATEDPROPDATAS GPD1 ON GPD1.RKTYPEIDENT = @FOODFIELD1 AND GPD1.OBJECTIDENT = R.SIFR
	LEFT JOIN GENERATEDPROPDATAS GPD2 ON GPD2.RKTYPEIDENT = @FOODFIELD2 AND GPD2.OBJECTIDENT = R.SIFR
	LEFT JOIN GENERATEDPROPDATAS GPD3 ON GPD3.RKTYPEIDENT = @FOODFIELD3 AND GPD3.OBJECTIDENT = R.SIFR
	LEFT JOIN GENERATEDPROPDATAS GPD4 ON GPD2.RKTYPEIDENT = @FOODFIELD4 AND GPD2.OBJECTIDENT = R.SIFR
	LEFT JOIN GENERATEDPROPDATAS GPD5 ON GPD3.RKTYPEIDENT = @FOODFIELD5 AND GPD3.OBJECTIDENT = R.SIFR
	WHERE R.SIFR = @RES AND CG.SIFR*256+CG.NUMINGROUP IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
) DUMMY
OUTER APPLY
(
	SELECT SUM(SD.PAYSUM) AS AMOUNT, COUNT(DISTINCT PC.GLOBALIDENT) AS COUNT, SUM(SD.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
	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
	JOIN DISHGROUPS DG ON DG.CHILD = MI.SIFR AND DG.PARENT = DUMMY.CAT
	WHERE GS.SHIFTDATE = @CURR_DAY AND GS.IRESTAURANT = @RES AND SD.STATE = 6 
) CDAY
OUTER APPLY
(
			SELECT COUNT(DISTINCT PC2.GLOBALIDENT) AS COUNT
			FROM GLOBALSHIFTS GS2
			JOIN ORDERS O2 ON O2.MIDSERVER = GS2.MIDSERVER AND O2.ICOMMONSHIFT = GS2.SHIFTNUM
			JOIN PRINTCHECKS PC2 ON PC2.MIDSERVER = O2.MIDSERVER AND PC2.VISIT = O2.VISIT AND PC2.ORDERIDENT = O2.IDENTINVISIT
			WHERE GS2.IRESTAURANT = @RES AND GS2.SHIFTDATE = @CURR_DAY AND PC2.STATE = 6
		) CDAY_CHECKS
OUTER APPLY
(
	SELECT SUM(LFCD.AMOUNT) AS AMOUNT,SUM(DCOUNT) AS DCOUNT FROM LAST_FOODCAT_DAYS LFCD
	WHERE LFCD.RESTAURANT = @RES AND LFCD.CATEGORY = DUMMY.CAT AND LFCD.DATE <> @CURR_DAY AND LFCD.DATE BETWEEN @CURR_WEEK1 AND @CURR_WEEK2
) CWEEK
OUTER APPLY
(
	SELECT SUM(LD.COUNT) AS COUNT FROM LAST_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE <> @CURR_DAY AND LD.DATE BETWEEN @CURR_WEEK1 AND @CURR_WEEK2
) CWEEK_CHECKS
OUTER APPLY
(
	SELECT SUM(AMOUNT) AS AMOUNT,SUM(DCOUNT) AS DCOUNT FROM LAST_FOODCAT_DAYS LFCD
	WHERE LFCD.RESTAURANT = @RES AND LFCD.CATEGORY = DUMMY.CAT AND LFCD.DATE <> @CURR_DAY AND LFCD.DATE BETWEEN @CURR_MONTH1 AND @CURR_MONTH2
) CMONTH
OUTER APPLY
(
	SELECT SUM(COUNT) AS COUNT FROM LAST_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE <> @CURR_DAY AND LD.DATE BETWEEN @CURR_MONTH1 AND @CURR_MONTH2
) CMONTH_CHECKS
ORDER BY DUMMY.SHIFTDATE, DUMMY.RNAME, DUMMY.CATEGORY;

END;