--RATING(2)

CREATE PROCEDURE [dbo].[RK7SQLPROCRATING2] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY DATETIME, @CURR_WEEK1 DATETIME, @CURR_WEEK2 DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME,
@FOODCAT1 INT, @FOODCAT2 INT, @FOODCAT3 INT, @FOODCAT4 INT, @FOODCAT5 INT, 
@FOODFIELD1 INT, @FOODFIELD2 INT, @FOODFIELD3 INT, @FOODFIELD4 INT, @FOODFIELD5 INT,
@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,GS.SHIFTDATE,127) AS SHIFTDATE,
	R.NAME AS RESTAURANTNAME,
	E.SIFR AS CASHIERID,
	E.NAME AS CASHIER,
	ISNULL(CG.NAME,'') AS CATEGORY,
	ISNULL(CAST(GPD.PROPERTYVAL AS FLOAT),0) AS PLANRATING,
	CASE WHEN CG.NAME IS NULL THEN 0 ELSE ISNULL(SUM(SD.PAYSUM),0) END AS CD_AMOUNT,
	ISNULL(CDAY_CHECKS.COUNT,0) AS CD_COUNT,
	CASE WHEN CG.NAME IS NULL THEN 0 ELSE ISNULL(SUM(SD.QUANTITY),0) END AS CD_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)
JOIN RESTAURANTS R ON R.SIFR = GS.IRESTAURANT
JOIN EMPLOYEES E ON E.SIFR = O.ICREATOR
LEFT JOIN CLASSIFICATORGROUPS CG ON CG.SIFR*256 + CG.NUMINGROUP = DG.PARENT
LEFT JOIN GENERATEDPROPDATAS GPD ON GPD.RKTYPEIDENT = (CASE DG.PARENT WHEN @FOODCAT1 THEN @FOODFIELD1 WHEN @FOODCAT2 THEN @FOODFIELD2 WHEN @FOODCAT3 THEN @FOODFIELD3 WHEN @FOODCAT4 THEN @FOODFIELD4 WHEN @FOODCAT5 THEN @FOODFIELD5 END) AND GPD.OBJECTIDENT = R.SIFR
LEFT JOIN
(
			SELECT O2.ICREATOR,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
			GROUP BY O2.ICREATOR
		) CDAY_CHECKS ON CDAY_CHECKS.ICREATOR = E.SIFR
WHERE GS.SHIFTDATE = @CURR_DAY AND GS.IRESTAURANT = @RES
GROUP BY GS.SHIFTDATE, R.NAME, E.SIFR, E.NAME, CG.SIFR,CG.NAME,ISNULL(CAST(GPD.PROPERTYVAL AS FLOAT),0),CDAY_CHECKS.COUNT
ORDER BY GS.SHIFTDATE, R.NAME, E.NAME, E.SIFR, CASE WHEN CG.SIFR IS NULL THEN 1 ELSE 0 END, CG.NAME;

END;