--OVERALL(1)

CREATE PROCEDURE [dbo].[RK7SQLPROCOVERALL1] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY DATETIME, @GETDATE 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;

/* 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_MONTH1 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY), 0);
SET @CURR_MONTH2 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY)+1, -1);

SELECT	1 AS TYPE,CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CASHIER AS CASHIERID, DUMMY.CASHIERNAME AS CASHIERNAME, DUMMY.CGNAME AS CATEGORY,
		ISNULL(CDAY.AMOUNT,0)  AS CD_AMOUNT, ISNULL(CDAY.COUNT,0)  AS CD_COUNT, ISNULL(CDAY.DCOUNT,0)  AS CD_DCOUNT,
		DUMMY.CPLAN AS CD_CPLAN, ISNULL(CCDAY.AMOUNT,0)  AS CD_CAMOUNT, ISNULL(CCDAY.DCOUNT,0)  AS CD_CDCOUNT
FROM 
(
SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, E.SIFR AS CASHIER, E.NAME AS CASHIERNAME, CG.SIFR*256+CG.NUMINGROUP AS CGSIFR, CG.NAME AS CGNAME,
	ISNULL(CAST(GPD.PROPERTYVAL AS FLOAT),0) AS CPLAN
FROM RESTAURANTS R, EMPLOYEES E, CLASSIFICATORGROUPS CG
LEFT JOIN GENERATEDPROPDATAS GPD ON GPD.RKTYPEIDENT = 
(CASE CG.SIFR*256+CG.NUMINGROUP WHEN @FOODCAT1 THEN @FOODFIELD1 WHEN @FOODCAT2 THEN @FOODFIELD2 WHEN @FOODCAT3 THEN @FOODFIELD3 WHEN @FOODCAT4 THEN @FOODFIELD4 WHEN @FOODCAT5 THEN @FOODFIELD5 END) AND GPD.OBJECTIDENT = @RES
WHERE R.SIFR = @RES AND CG.SIFR*256+CG.NUMINGROUP IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
) DUMMY
LEFT JOIN
(
	SELECT IAUTHOR,PARENT,SUM(AMOUNT) AS AMOUNT,SUM(DCOUNT) AS DCOUNT FROM (
	SELECT PC.IAUTHOR,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 PC.IAUTHOR,DG.PARENT

	UNION ALL
	
	SELECT PC.IAUTHOR,@FOODCAT5, 0 AS AMOUNT, SUM(DM.MODICNT) 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
	JOIN DISHMODIFIERS DM ON DM.MIDSERVER = SD.MIDSERVER AND DM.VISIT = SD.VISIT AND DM.DISHUNI = SD.UNI AND DM.PRLISTSUM > 0 
	AND DM.SIFR NOT IN (SELECT MSD.DEFAULTMODIFIER FROM MODISCHEMEDETAILS MSD WHERE MSD.MODISCHEME = MI.MODISCHEME)
	WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY
	GROUP BY PC.IAUTHOR
	) T
	GROUP BY IAUTHOR,PARENT
--

) CCDAY ON CCDAY.IAUTHOR = DUMMY.CASHIER AND CCDAY.PARENT = DUMMY.CGSIFR
LEFT JOIN
(
	SELECT	PC.IAUTHOR, 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 PC.IAUTHOR
) CDAY ON CDAY.IAUTHOR = DUMMY.CASHIER
WHERE CDAY.AMOUNT IS NOT NULL
ORDER BY DUMMY.SHIFTDATE, DUMMY.RNAME, ISNULL(CDAY.AMOUNT,0) DESC, DUMMY.CASHIERNAME, DUMMY.CASHIER, DUMMY.CGNAME DESC;

END;