--SALES(1)

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES1] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @COUNTFIELD INT, @AMOUNTFIELD INT, @RES INT/*, @PLACECLASS INT, @FOODCLASS INT*/,
@CURR_DAY DATETIME, @CURR_WEEK1 DATETIME, @CURR_WEEK2 DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME,
@LAST_DAY DATETIME, @LAST_WEEK1 DATETIME, @LAST_WEEK2 DATETIME, @LAST_MONTH1 DATETIME, @LAST_MONTH2 DATETIME,
@PLANAMOUNT MONEY, @PLANCOUNT AS FLOAT, @GETDATE DATETIME, @PAMOUNT MONEY, @PCOUNT 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,
@FOODCAT1 = NULL, @FOODCAT2 = NULL, @FOODCAT3 = NULL, @FOODCAT4 = NULL, @FOODCAT5 = NULL, 
@FOODFIELD1 = NULL, @FOODFIELD2 = NULL, @FOODFIELD3 = NULL, @FOODFIELD4 = NULL, @FOODFIELD5 = NULL,
@COUNTFIELD = @COUNTFIELD OUTPUT, @AMOUNTFIELD = @AMOUNTFIELD OUTPUT;

SELECT @PLANAMOUNT = CAST(GPD.PROPERTYVAL AS MONEY) 
FROM GENERATEDPROPDATAS GPD
WHERE GPD.RKTYPEIDENT = @AMOUNTFIELD AND GPD.OBJECTIDENT = @RES;

SELECT @PLANCOUNT = CAST(GPD.PROPERTYVAL AS FLOAT)
FROM GENERATEDPROPDATAS GPD
WHERE GPD.RKTYPEIDENT = @COUNTFIELD AND GPD.OBJECTIDENT = @RES;

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);

SET @LAST_WEEK1 = DATEADD(WEEK,DATEDIFF(WEEK,-@@DATEFIRST,DATEADD(YEAR,DATEDIFF(YEAR,0,@CURR_DAY)-1,0)-@@DATEFIRST),0);
SET @LAST_WEEK1 = DATEADD(WEEK,DATEPART(WEEK,@CURR_DAY)-1,@LAST_WEEK1);
SET @LAST_DAY = DATEADD(DAY,DATEDIFF(DAY,@CURR_WEEK1,@CURR_DAY),@LAST_WEEK1);
SET @LAST_WEEK2 = DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,@LAST_DAY-@@DATEFIRST)+1, -1);

SET @LAST_MONTH1 = DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(YEAR,-1,@CURR_DAY)), 0);
SET @LAST_MONTH2 = DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(YEAR,-1,@CURR_DAY))+1, -1);

IF(DATEDIFF(DAY,@CURR_MONTH1,@CURR_DAY)>6)
BEGIN

	WITH DATA (WD, PAM, PCN, CN)
	AS (
		SELECT DATEPART(WEEKDAY,LD.DATE), 
		SUM(LD.AMOUNT),
		SUM(LD.COUNT),
		COUNT(*)
		FROM LAST_DAYS LD
		WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @CURR_MONTH1 AND @CURR_DAY-1
		GROUP BY DATEPART(WEEKDAY,LD.DATE)
	)
	
	SELECT @PAMOUNT = SUM(AMOUNT), @PCOUNT = SUM(COUNT)
	FROM 
	
	(
	
	SELECT SUM(PAM) AS AMOUNT,SUM(PCN) AS COUNT
	FROM DATA

	UNION ALL

	SELECT SUM(DATA.PAM / DATA.CN), SUM(DATA.PCN / DATA.CN)
	FROM PLAN_DAYS PD
	JOIN DATA ON DATA.WD = DATEPART(WEEKDAY,PD.DATE)
	WHERE PD.RESTAURANT = @RES AND PD.DATE BETWEEN @CURR_DAY AND @CURR_MONTH2

	) T;
END ELSE
BEGIN
	SET @PAMOUNT = 0;
	SET @PCOUNT =  0;
END;

--CD/CW/CM - CURRENT DAY/WEEK/MONTH, LD/LW/LM - LAST (PREVIOUS) YEAR DAY/WEEK/MONTH;
SELECT	CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CURRENCY AS CURRENCY,
		ISNULL(@PLANAMOUNT,0) AS AMOUNTPLAN,ISNULL(@PLANCOUNT,0) AS COUNTPLAN,
		ISNULL(@PAMOUNT,0) AS AMOUNTPRED,ISNULL(@PCOUNT,0) AS COUNTPRED,
		ISNULL(@PLANAMOUNT*DPLAN.AMOUNTRATE,0) AS CD_AMOUNTPLAN, ISNULL(@PLANAMOUNT*WPLAN.AMOUNTRATE,0) AS CW_AMOUNTPLAN, ISNULL(@PLANAMOUNT*MPLAN.AMOUNTRATE,0) AS CM_AMOUNTPLAN,
		ISNULL(CCDAY.AMOUNT,0)  AS CD_CAMOUNT, ISNULL(@PLANCOUNT*DPLAN.COUNTRATE,0) AS CD_COUNTPLAN, ISNULL(CCDAY.COUNT,0) AS CD_CCOUNT,
		ISNULL(CCWEEK.AMOUNT,0) AS CW_CAMOUNT, ISNULL(@PLANCOUNT*WPLAN.COUNTRATE,0) AS CW_COUNTPLAN, ISNULL(CCWEEK.COUNT,0) + ISNULL(CCDAY.COUNT,0) AS CW_CCOUNT,
		ISNULL(CCMONTH.AMOUNT,0) AS CM_CAMOUNT, ISNULL(@PLANCOUNT*MPLAN.COUNTRATE,0) AS CM_COUNTPLAN, ISNULL(CCMONTH.COUNT,0) + ISNULL(CCDAY.COUNT,0) AS CM_CCOUNT,
		ISNULL(LCDAY.AMOUNT,0) AS LD_CAMOUNT, ISNULL(LCDAY.COUNT,0)  AS LD_CCOUNT,
		ISNULL(LCWEEK.AMOUNT,0) AS LW_CAMOUNT, ISNULL(LCWEEK.COUNT,0) AS LW_CCOUNT,
		ISNULL(LCMONTH.AMOUNT,0) AS LM_CAMOUNT, ISNULL(LCMONTH.COUNT,0) AS LM_CCOUNT,
		ISNULL(CDAY.AMOUNT,0) AS CD_AMOUNT,ISNULL(CDAY.COUNT,0) AS CD_COUNT,
		ISNULL(CWEEK.AMOUNT,0) AS CW_AMOUNT,ISNULL(CWEEK.COUNT,0) AS CW_COUNT,
		ISNULL(CMONTH.AMOUNT,0) AS CM_AMOUNT,ISNULL(CMONTH.COUNT,0) AS CM_COUNT,
		ISNULL(LDAY.AMOUNT,0) AS LD_AMOUNT,ISNULL(LDAY.COUNT,0) AS LD_COUNT,
		ISNULL(LWEEK.AMOUNT,0) AS LW_AMOUNT,ISNULL(LWEEK.COUNT,0) AS LW_COUNT,
		ISNULL(LMONTH.AMOUNT,0) AS LM_AMOUNT,ISNULL(LMONTH.COUNT,0) AS LM_COUNT
FROM (SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, C.SIFR AS CSIFR, C.CODE AS CCODE, C.NAME AS CURRENCY FROM RESTAURANTS R,CURRENCIES C WHERE R.SIFR = @RES) DUMMY
OUTER APPLY
(
	SELECT SUM(CL.BINDEDSUM) AS AMOUNT, COUNT(DISTINCT PC.GLOBALIDENT) AS COUNT
	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 AND CL.SIFR = DUMMY.CSIFR
	WHERE GS.SHIFTDATE = @CURR_DAY AND GS.IRESTAURANT = @RES
) CCDAY
OUTER APPLY
(
	SELECT SUM(LCD.AMOUNT) AS AMOUNT, SUM(LCD.COUNT) AS COUNT
	FROM LAST_CURRENCY_DAYS LCD
	WHERE LCD.DATE BETWEEN @CURR_WEEK1 AND @CURR_WEEK2 AND LCD.DATE <> @CURR_DAY AND LCD.RESTAURANT = @RES AND LCD.CURRENCY = DUMMY.CSIFR
) CCWEEK
OUTER APPLY
(
	SELECT SUM(LCD.AMOUNT) AS AMOUNT, SUM(LCD.COUNT) AS COUNT
	FROM LAST_CURRENCY_DAYS LCD 
	WHERE LCD.DATE BETWEEN @CURR_MONTH1 AND @CURR_MONTH2 AND LCD.DATE <> @CURR_DAY AND LCD.RESTAURANT = @RES AND LCD.CURRENCY = DUMMY.CSIFR
) CCMONTH
OUTER APPLY
(
	SELECT SUM(LCD.AMOUNT) AS AMOUNT, SUM(LCD.COUNT) AS COUNT
	FROM LAST_CURRENCY_DAYS LCD
	WHERE LCD.RESTAURANT = @RES AND LCD.DATE = @LAST_DAY AND LCD.CURRENCY = DUMMY.CSIFR
) LCDAY
OUTER APPLY
(
	SELECT SUM(LCD.AMOUNT) AS AMOUNT, SUM(LCD.COUNT) AS COUNT
	FROM LAST_CURRENCY_DAYS LCD
	WHERE LCD.RESTAURANT = @RES AND LCD.DATE BETWEEN @LAST_WEEK1 AND @LAST_WEEK2 AND LCD.CURRENCY = DUMMY.CSIFR
) LCWEEK
OUTER APPLY
(
	SELECT SUM(LCD.AMOUNT) AS AMOUNT, SUM(LCD.COUNT) AS COUNT
	FROM LAST_CURRENCY_DAYS LCD
	WHERE LCD.RESTAURANT = @RES AND LCD.DATE BETWEEN @LAST_MONTH1 AND @LAST_MONTH2 AND LCD.CURRENCY = DUMMY.CSIFR
) LCMONTH
OUTER APPLY
(
	SELECT PD.AMOUNTRATE, PD.COUNTRATE
	FROM PLAN_DAYS PD WHERE PD.RESTAURANT = @RES AND PD.DATE = @CURR_DAY
) DPLAN
OUTER APPLY
(
	SELECT SUM(PD.AMOUNTRATE) AS AMOUNTRATE, SUM(PD.COUNTRATE) AS COUNTRATE
	FROM PLAN_DAYS PD WHERE PD.RESTAURANT = @RES AND PD.DATE BETWEEN @CURR_WEEK1 AND @CURR_DAY-1
) WPLAN
OUTER APPLY
(
	SELECT SUM(PD.AMOUNTRATE) AS AMOUNTRATE, SUM(PD.COUNTRATE) AS COUNTRATE
	FROM PLAN_DAYS PD WHERE PD.RESTAURANT = @RES AND PD.DATE BETWEEN @CURR_MONTH1 AND @CURR_DAY-1
) MPLAN
OUTER APPLY
(
	SELECT SUM(PC.BINDEDSUM) AS AMOUNT, COUNT(DISTINCT PC.GLOBALIDENT) AS COUNT
	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
	WHERE GS.SHIFTDATE = @CURR_DAY AND GS.IRESTAURANT = @RES AND PC.STATE = 6
) CDAY
OUTER APPLY
(
	SELECT SUM(LD.AMOUNT) AS AMOUNT, SUM(LD.COUNT) AS COUNT
	FROM LAST_DAYS LD
	WHERE LD.DATE BETWEEN @CURR_WEEK1 AND @CURR_DAY - 1 AND LD.RESTAURANT = @RES
) CWEEK
OUTER APPLY
(
	SELECT SUM(LD.AMOUNT) AS AMOUNT, SUM(LD.COUNT) AS COUNT
	FROM LAST_DAYS LD
	WHERE LD.DATE BETWEEN @CURR_MONTH1 AND @CURR_DAY - 1 AND LD.RESTAURANT = @RES
) CMONTH
OUTER APPLY
(
	SELECT LD.AMOUNT, LD.COUNT
	FROM LAST_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE = @LAST_DAY
) LDAY
OUTER APPLY
(
	SELECT SUM(LD.AMOUNT) AS AMOUNT, SUM(LD.COUNT) AS COUNT
	FROM LAST_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @LAST_WEEK1 AND @LAST_DAY - 1
) LWEEK
OUTER APPLY
(
	SELECT SUM(LD.AMOUNT) AS AMOUNT, SUM(LD.COUNT) AS COUNT
	FROM LAST_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @LAST_MONTH1 AND @LAST_DAY - 1
) LMONTH
WHERE ISNULL(CCDAY.AMOUNT,0) <> 0 OR ISNULL(CCDAY.COUNT,0) <> 0 OR ISNULL(CCWEEK.AMOUNT,0) <> 0 OR ISNULL(CCWEEK.COUNT,0) <> 0
 OR   ISNULL(CCMONTH.AMOUNT,0) <> 0 OR ISNULL(CCMONTH.COUNT,0) <> 0 OR ISNULL(LCDAY.AMOUNT,0) <> 0 OR ISNULL(LCDAY.COUNT,0) <> 0
  OR   ISNULL(LCWEEK.AMOUNT,0) <> 0 OR ISNULL(LCWEEK.COUNT,0) <> 0 OR ISNULL(LCMONTH.AMOUNT,0) <> 0 OR ISNULL(LCMONTH.COUNT,0) <> 0
ORDER BY DUMMY.SHIFTDATE, DUMMY.RNAME,DUMMY.CCODE;

END;