--SALES(4) 

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES4] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @COUNTFIELD INT, @AMOUNTFIELD INT, @RES INT, @PLACECLASS INT, @CURR_DAY DATETIME, @LAST_DAY DATETIME,
@CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME,@LAST_WEEK1 DATETIME,@LAST_MONTH1 DATETIME, @LAST_MONTH2 DATETIME,
@GETDATE DATETIME, @DTHRU 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();
SET @DTHRU = 1009422;

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;

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

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,DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,@CURR_DAY-@@DATEFIRST), 0),@CURR_DAY),@LAST_WEEK1);
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);

SELECT	CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CATEGORY AS CATEGORY,
		ISNULL(DUMMY.PLANAMOUNT,0) AS AMOUNTPLAN,ISNULL(DUMMY.PLANCOUNT,0) AS COUNTPLAN,
		ISNULL(DUMMY.PLANAMOUNT*MPLAN.AMOUNTRATE*OPLAN.AMOUNTRATE,0) AS CM_AMOUNTPLAN, ISNULL(CMONTH.AMOUNT,0)  AS CM_AMOUNT, 
		ISNULL(DUMMY.PLANCOUNT*MPLAN.COUNTRATE*OPLAN.COUNTRATE,0) AS CM_COUNTPLAN, ISNULL(CMONTH.COUNT,0)  AS CM_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, ST.SIFR AS ST, ST.COT AS COT, ST.NAME AS CATEGORY, CAST(GPD1.PROPERTYVAL AS MONEY) AS PLANAMOUNT, CAST(GPD2.PROPERTYVAL AS FLOAT) AS PLANCOUNT
	FROM (SELECT 0 AS SIFR,'TSessionType0' as NAME,NULL AS COT UNION SELECT 5,'TSessionType5',NULL UNION SELECT 10,NAME,COT.SIFR FROM CHANGEABLEORDERTYPES COT WHERE COT.STATUS = 3 AND COT.SIFR = @DTHRU) ST
	, RESTAURANTS R
	LEFT JOIN GENERATEDPROPDATAS GPD1 ON GPD1.RKTYPEIDENT = @AMOUNTFIELD AND GPD1.OBJECTIDENT = R.SIFR
	LEFT JOIN GENERATEDPROPDATAS GPD2 ON GPD2.RKTYPEIDENT = @COUNTFIELD  AND GPD2.OBJECTIDENT = R.SIFR
	WHERE R.SIFR = @RES-- AND COT.STATUS = 3 AND COT.SIFR =  --<> 10069
) DUMMY
OUTER APPLY
(
	SELECT SUM(LD.AMOUNT) AS AMOUNT, SUM(LD.COUNT) AS COUNT
	FROM LAST_ST_COT_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @CURR_MONTH1 AND @CURR_DAY-1 AND (DUMMY.COT IS NULL AND LD.COT <> @DTHRU AND (DUMMY.ST = 5 AND LD.SESSIONTYPE = 5 OR DUMMY.ST <> 5 AND LD.SESSIONTYPE <> 5)  OR LD.COT = DUMMY.COT)
) CMONTH
OUTER APPLY
(
	SELECT SUM(LD.AMOUNT) AS AMOUNT, SUM(LD.COUNT) AS COUNT
	FROM LAST_ST_COT_DAYS LD
	WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @LAST_MONTH1 AND @LAST_DAY-1 AND (DUMMY.COT IS NULL AND LD.COT <> @DTHRU AND (DUMMY.ST = 5 AND LD.SESSIONTYPE = 5 OR DUMMY.ST <> 5 AND LD.SESSIONTYPE <> 5)  OR LD.COT = DUMMY.COT)
) LMONTH
OUTER APPLY
(
	SELECT SUM(PD.AMOUNTRATE) AS AMOUNTRATE, SUM(PD.COUNTRATE) AS COUNTRATE
	FROM PLAN_ST_COT_DAYS PD WHERE PD.RESTAURANT = @RES AND PD.DATE = @CURR_MONTH1 AND (DUMMY.COT IS NULL AND PD.COT <> @DTHRU AND (DUMMY.ST = 5 AND PD.SESSIONTYPE = 5 OR DUMMY.ST <> 5 AND PD.SESSIONTYPE <> 5)  OR PD.COT = DUMMY.COT)
) OPLAN
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
ORDER BY DUMMY.SHIFTDATE,DUMMY.RNAME,DUMMY.CATEGORY;
END;