--SALES(3) 

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES3] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY 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 = NULL, @FOODCAT2 = NULL, @FOODCAT3 = NULL, @FOODCAT4 = NULL, @FOODCAT5 = NULL,
 @FOODFIELD1 = NULL, @FOODFIELD2 = NULL, @FOODFIELD3 = NULL, @FOODFIELD4 = NULL, @FOODFIELD5 = NULL;

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	CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME,
		ISNULL(CDAY.AMOUNT0,0) AS CD_AMOUNT0, ISNULL(CDAY.COUNT0,0) AS CD_COUNT0,ISNULL(CMONTH.AMOUNT0,0) + ISNULL(CDAY.AMOUNT0,0) AS CM_AMOUNT0, ISNULL(CMONTH.COUNT0,0) + ISNULL(CDAY.COUNT0,0) AS CM_COUNT0,
		ISNULL(CDAY.AMOUNT1,0) AS CD_AMOUNT1, ISNULL(CDAY.COUNT1,0) AS CD_COUNT1,ISNULL(CMONTH.AMOUNT1,0) + ISNULL(CDAY.AMOUNT1,0) AS CM_AMOUNT1, ISNULL(CMONTH.COUNT1,0) + ISNULL(CDAY.COUNT1,0) AS CM_COUNT1,
		ISNULL(CDAY.AMOUNT2,0) AS CD_AMOUNT2, ISNULL(CDAY.COUNT2,0) AS CD_COUNT2,ISNULL(CMONTH.AMOUNT2,0) + ISNULL(CDAY.AMOUNT2,0) AS CM_AMOUNT2, ISNULL(CMONTH.COUNT2,0) + ISNULL(CDAY.COUNT2,0) AS CM_COUNT2,
		ISNULL(CDAY.AMOUNT3,0) AS CD_AMOUNT3, ISNULL(CDAY.COUNT3,0) AS CD_COUNT3,ISNULL(CMONTH.AMOUNT3,0) + ISNULL(CDAY.AMOUNT3,0) AS CM_AMOUNT3, ISNULL(CMONTH.COUNT3,0) + ISNULL(CDAY.COUNT3,0) AS CM_COUNT3,
		ISNULL(CDAY.AMOUNT4,0) AS CD_AMOUNT4, ISNULL(CDAY.COUNT4,0) AS CD_COUNT4,ISNULL(CMONTH.AMOUNT4,0) + ISNULL(CDAY.AMOUNT4,0) AS CM_AMOUNT4, ISNULL(CMONTH.COUNT4,0) + ISNULL(CDAY.COUNT4,0) AS CM_COUNT4,
		ISNULL(CDAY.AMOUNT5,0) AS CD_AMOUNT5, ISNULL(CDAY.COUNT5,0) AS CD_COUNT5,ISNULL(CMONTH.AMOUNT5,0) + ISNULL(CDAY.AMOUNT5,0) AS CM_AMOUNT5, ISNULL(CMONTH.COUNT5,0) + ISNULL(CDAY.COUNT5,0) AS CM_COUNT5,
		ISNULL(CDAY.AMOUNT6,0) AS CD_AMOUNT6, ISNULL(CDAY.COUNT6,0) AS CD_COUNT6,ISNULL(CMONTH.AMOUNT6,0) + ISNULL(CDAY.AMOUNT6,0) AS CM_AMOUNT6, ISNULL(CMONTH.COUNT6,0) + ISNULL(CDAY.COUNT6,0) AS CM_COUNT6,
		ISNULL(CDAY.AMOUNT7,0) AS CD_AMOUNT7, ISNULL(CDAY.COUNT7,0) AS CD_COUNT7,ISNULL(CMONTH.AMOUNT7,0) + ISNULL(CDAY.AMOUNT7,0) AS CM_AMOUNT7, ISNULL(CMONTH.COUNT7,0) + ISNULL(CDAY.COUNT7,0) AS CM_COUNT7,
		ISNULL(CDAY.AMOUNT8,0) AS CD_AMOUNT8, ISNULL(CDAY.COUNT8,0) AS CD_COUNT8,ISNULL(CMONTH.AMOUNT8,0) + ISNULL(CDAY.AMOUNT8,0) AS CM_AMOUNT8, ISNULL(CMONTH.COUNT8,0) + ISNULL(CDAY.COUNT8,0) AS CM_COUNT8,
		ISNULL(CDAY.AMOUNT9,0) AS CD_AMOUNT9, ISNULL(CDAY.COUNT9,0) AS CD_COUNT9,ISNULL(CMONTH.AMOUNT9,0) + ISNULL(CDAY.AMOUNT9,0) AS CM_AMOUNT9, ISNULL(CMONTH.COUNT9,0) + ISNULL(CDAY.COUNT9,0) AS CM_COUNT9,
		ISNULL(CDAY.AMOUNT10,0) AS CD_AMOUNT10, ISNULL(CDAY.COUNT10,0) AS CD_COUNT10,ISNULL(CMONTH.AMOUNT10,0) + ISNULL(CDAY.AMOUNT10,0) AS CM_AMOUNT10, ISNULL(CMONTH.COUNT10,0) + ISNULL(CDAY.COUNT10,0) AS CM_COUNT10,
		ISNULL(CDAY.AMOUNT11,0) AS CD_AMOUNT11, ISNULL(CDAY.COUNT11,0) AS CD_COUNT11,ISNULL(CMONTH.AMOUNT11,0) + ISNULL(CDAY.AMOUNT11,0) AS CM_AMOUNT11, ISNULL(CMONTH.COUNT11,0) + ISNULL(CDAY.COUNT11,0) AS CM_COUNT11,
		ISNULL(CDAY.AMOUNT12,0) AS CD_AMOUNT12, ISNULL(CDAY.COUNT12,0) AS CD_COUNT12,ISNULL(CMONTH.AMOUNT12,0) + ISNULL(CDAY.AMOUNT12,0) AS CM_AMOUNT12, ISNULL(CMONTH.COUNT12,0) + ISNULL(CDAY.COUNT12,0) AS CM_COUNT12,
		ISNULL(CDAY.AMOUNT13,0) AS CD_AMOUNT13, ISNULL(CDAY.COUNT13,0) AS CD_COUNT13,ISNULL(CMONTH.AMOUNT13,0) + ISNULL(CDAY.AMOUNT13,0) AS CM_AMOUNT13, ISNULL(CMONTH.COUNT13,0) + ISNULL(CDAY.COUNT13,0) AS CM_COUNT13,
		ISNULL(CDAY.AMOUNT14,0) AS CD_AMOUNT14, ISNULL(CDAY.COUNT14,0) AS CD_COUNT14,ISNULL(CMONTH.AMOUNT14,0) + ISNULL(CDAY.AMOUNT14,0) AS CM_AMOUNT14, ISNULL(CMONTH.COUNT14,0) + ISNULL(CDAY.COUNT14,0) AS CM_COUNT14,
		ISNULL(CDAY.AMOUNT15,0) AS CD_AMOUNT15, ISNULL(CDAY.COUNT15,0) AS CD_COUNT15,ISNULL(CMONTH.AMOUNT15,0) + ISNULL(CDAY.AMOUNT15,0) AS CM_AMOUNT15, ISNULL(CMONTH.COUNT15,0) + ISNULL(CDAY.COUNT15,0) AS CM_COUNT15,
		ISNULL(CDAY.AMOUNT16,0) AS CD_AMOUNT16, ISNULL(CDAY.COUNT16,0) AS CD_COUNT16,ISNULL(CMONTH.AMOUNT16,0) + ISNULL(CDAY.AMOUNT16,0) AS CM_AMOUNT16, ISNULL(CMONTH.COUNT16,0) + ISNULL(CDAY.COUNT16,0) AS CM_COUNT16,
		ISNULL(CDAY.AMOUNT17,0) AS CD_AMOUNT17, ISNULL(CDAY.COUNT17,0) AS CD_COUNT17,ISNULL(CMONTH.AMOUNT17,0) + ISNULL(CDAY.AMOUNT17,0) AS CM_AMOUNT17, ISNULL(CMONTH.COUNT17,0) + ISNULL(CDAY.COUNT17,0) AS CM_COUNT17,
		ISNULL(CDAY.AMOUNT18,0) AS CD_AMOUNT18, ISNULL(CDAY.COUNT18,0) AS CD_COUNT18,ISNULL(CMONTH.AMOUNT18,0) + ISNULL(CDAY.AMOUNT18,0) AS CM_AMOUNT18, ISNULL(CMONTH.COUNT18,0) + ISNULL(CDAY.COUNT18,0) AS CM_COUNT18,
		ISNULL(CDAY.AMOUNT19,0) AS CD_AMOUNT19, ISNULL(CDAY.COUNT19,0) AS CD_COUNT19,ISNULL(CMONTH.AMOUNT19,0) + ISNULL(CDAY.AMOUNT19,0) AS CM_AMOUNT19, ISNULL(CMONTH.COUNT19,0) + ISNULL(CDAY.COUNT19,0) AS CM_COUNT19,
		ISNULL(CDAY.AMOUNT20,0) AS CD_AMOUNT20, ISNULL(CDAY.COUNT20,0) AS CD_COUNT20,ISNULL(CMONTH.AMOUNT20,0) + ISNULL(CDAY.AMOUNT20,0) AS CM_AMOUNT20, ISNULL(CMONTH.COUNT20,0) + ISNULL(CDAY.COUNT20,0) AS CM_COUNT20,
		ISNULL(CDAY.AMOUNT21,0) AS CD_AMOUNT21, ISNULL(CDAY.COUNT21,0) AS CD_COUNT21,ISNULL(CMONTH.AMOUNT21,0) + ISNULL(CDAY.AMOUNT21,0) AS CM_AMOUNT21, ISNULL(CMONTH.COUNT21,0) + ISNULL(CDAY.COUNT21,0) AS CM_COUNT21,
		ISNULL(CDAY.AMOUNT22,0) AS CD_AMOUNT22, ISNULL(CDAY.COUNT22,0) AS CD_COUNT22,ISNULL(CMONTH.AMOUNT22,0) + ISNULL(CDAY.AMOUNT22,0) AS CM_AMOUNT22, ISNULL(CMONTH.COUNT22,0) + ISNULL(CDAY.COUNT22,0) AS CM_COUNT22,
		ISNULL(CDAY.AMOUNT23,0) AS CD_AMOUNT23, ISNULL(CDAY.COUNT23,0) AS CD_COUNT23,ISNULL(CMONTH.AMOUNT23,0) + ISNULL(CDAY.AMOUNT23,0) AS CM_AMOUNT23, ISNULL(CMONTH.COUNT23,0) + ISNULL(CDAY.COUNT23,0) AS CM_COUNT23
FROM
(
SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME FROM RESTAURANTS R WHERE R.SIFR = @RES
) DUMMY
OUTER APPLY
(
SELECT
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 0 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT0,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 0 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT0,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 1 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT1,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 1 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT1,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 2 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT2,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 2 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT2,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 3 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT3,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 3 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT3,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 4 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT4,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 4 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT4,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 5 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT5,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 5 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT5,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 6 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT6,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 6 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT6,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 7 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT7,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 7 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT7,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 8 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT8,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 8 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT8,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 9 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT9,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 9 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT9,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 10 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT10,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 10 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT10,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 11 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT11,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 11 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT11,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 12 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT12,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 12 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT12,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 13 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT13,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 13 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT13,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 14 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT14,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 14 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT14,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 15 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT15,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 15 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT15,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 16 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT16,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 16 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT16,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 17 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT17,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 17 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT17,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 18 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT18,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 18 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT18,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 19 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT19,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 19 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT19,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 20 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT20,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 20 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT20,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 21 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT21,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 21 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT21,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 22 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT22,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 22 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT22,
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 23 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT23,
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 23 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT23
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
WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY
) CDAY
OUTER APPLY
(
	SELECT	SUM(AMOUNT0) AS AMOUNT0,SUM(COUNT0) AS COUNT0,
			SUM(AMOUNT1) AS AMOUNT1,SUM(COUNT1) AS COUNT1,
			SUM(AMOUNT2) AS AMOUNT2,SUM(COUNT2) AS COUNT2,
			SUM(AMOUNT3) AS AMOUNT3,SUM(COUNT3) AS COUNT3,
			SUM(AMOUNT4) AS AMOUNT4,SUM(COUNT4) AS COUNT4,
			SUM(AMOUNT5) AS AMOUNT5,SUM(COUNT5) AS COUNT5,
			SUM(AMOUNT6) AS AMOUNT6,SUM(COUNT6) AS COUNT6,
			SUM(AMOUNT7) AS AMOUNT7,SUM(COUNT7) AS COUNT7,
			SUM(AMOUNT8) AS AMOUNT8,SUM(COUNT8) AS COUNT8,
			SUM(AMOUNT9) AS AMOUNT9,SUM(COUNT9) AS COUNT9,
			SUM(AMOUNT10) AS AMOUNT10,SUM(COUNT10) AS COUNT10,
			SUM(AMOUNT11) AS AMOUNT11,SUM(COUNT11) AS COUNT11,
			SUM(AMOUNT12) AS AMOUNT12,SUM(COUNT12) AS COUNT12,
			SUM(AMOUNT13) AS AMOUNT13,SUM(COUNT13) AS COUNT13,
			SUM(AMOUNT14) AS AMOUNT14,SUM(COUNT14) AS COUNT14,
			SUM(AMOUNT15) AS AMOUNT15,SUM(COUNT15) AS COUNT15,
			SUM(AMOUNT16) AS AMOUNT16,SUM(COUNT16) AS COUNT16,
			SUM(AMOUNT17) AS AMOUNT17,SUM(COUNT17) AS COUNT17,
			SUM(AMOUNT18) AS AMOUNT18,SUM(COUNT18) AS COUNT18,
			SUM(AMOUNT19) AS AMOUNT19,SUM(COUNT19) AS COUNT19,
			SUM(AMOUNT20) AS AMOUNT20,SUM(COUNT20) AS COUNT20,
			SUM(AMOUNT21) AS AMOUNT21,SUM(COUNT21) AS COUNT21,
			SUM(AMOUNT22) AS AMOUNT22,SUM(COUNT22) AS COUNT22,
			SUM(AMOUNT23) AS AMOUNT23,SUM(COUNT23) AS COUNT23
	FROM LAST_HOURLY_DAYS LHD
	WHERE LHD.RESTAURANT = @RES AND LHD.DATE <> @CURR_DAY AND LHD.DATE BETWEEN @CURR_MONTH1 AND @CURR_MONTH2
) CMONTH
ORDER BY DUMMY.SHIFTDATE, DUMMY.RNAME;

END;