--SALES(2) 

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES2] 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, DUMMY.CASHIERNAME AS CASHIERNAME,
		ISNULL(CDAY.AMOUNT,0)  AS CD_AMOUNT, ISNULL(CDAY.COUNT,0)  AS CD_COUNT, ISNULL(CDAY.DCOUNT,0)  AS CD_DCOUNT,
		ISNULL(CMONTH.AMOUNT,0) + ISNULL(CDAY.AMOUNT,0) AS CM_AMOUNT,
		ISNULL(CMONTH.COUNT,0) + ISNULL(CDAY.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, E.SIFR AS CASHIER, E.NAME AS CASHIERNAME
FROM RESTAURANTS R, EMPLOYEES E
WHERE R.SIFR = @RES
) DUMMY
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-- AND PC.IAUTHOR = DUMMY.CASHIER
GROUP BY PC.IAUTHOR
) CDAY ON CDAY.IAUTHOR = DUMMY.CASHIER
LEFT JOIN
(
	SELECT LCD.CASHIER,SUM(AMOUNT) AS AMOUNT,SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT FROM LAST_CASHIER_DAYS LCD
	WHERE LCD.RESTAURANT = @RES AND LCD.DATE <> @CURR_DAY AND LCD.DATE BETWEEN @CURR_MONTH1 AND @CURR_MONTH2
	GROUP BY LCD.CASHIER
) CMONTH ON CMONTH.CASHIER = DUMMY.CASHIER
WHERE CDAY.AMOUNT IS NOT NULL OR CMONTH.AMOUNT IS NOT NULL
ORDER BY DUMMY.SHIFTDATE, ISNULL(CDAY.AMOUNT,0) DESC, DUMMY.RNAME, DUMMY.CASHIERNAME;

END;