CREATE PROCEDURE [dbo].[RATING_REP_CALC](
	@RES INT,
	@CURVER DATETIME,
	@VER DATETIME OUTPUT,
	@AMOUNTFIELD INT OUTPUT,
	@COUNTFIELD INT OUTPUT,
	--@FOODCLASS INT OUTPUT,
	@FOODCAT1 INT OUTPUT,
	@FOODCAT2 INT OUTPUT,
	@FOODCAT3 INT OUTPUT,
	@FOODCAT4 INT OUTPUT,
	@FOODCAT5 INT OUTPUT,
	@FOODFIELD1 INT OUTPUT,
	@FOODFIELD2 INT OUTPUT,
	@FOODFIELD3 INT OUTPUT,
	@FOODFIELD4 INT OUTPUT,
	@FOODFIELD5 INT OUTPUT	) AS BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

--DECLARE @CURVER DATETIME;

/* USER DEFINED VARIABLES */

DECLARE @FOODCLASS INT;

SET @COUNTFIELD = 1000027; --RESTAURANT'S CHECK COUNT PLAN EXTRA FIELD;
SET @AMOUNTFIELD = 1000024; --RESTAURANT'S AMOUNT PLAN EXTRA FIELD;
--SET @PLACECLASS = 25;--CHOSEN PLACE CLASSIFICATION (BAR/DRIVE/EXPRESS-WINDOW);
SET @FOODCLASS = 41 * 256;--CHOSEN FOOD CLASSIFICATION (POTATO/ICECREAM);
SET @FOODCAT1 = @FOODCLASS + 1;
SET @FOODCAT2 = @FOODCLASS + 2;
SET @FOODCAT3 = @FOODCLASS + 3;
SET @FOODCAT4 = @FOODCLASS + 4;
SET @FOODCAT5 = @FOODCLASS + 5;
SET @FOODFIELD1 = 1000025;
SET @FOODFIELD2 = 1000026;
SET @FOODFIELD3 = 1000032;
SET @FOODFIELD4 = 1000033;
SET @FOODFIELD5 = 1000034;
/**************************/

/*
DELETE FROM DBINFORMATION WHERE ALIAS = 'RATING_REP';
DELETE FROM LAST_DAYS;
DELETE FROM LAST_WEEKS;
DELETE FROM LAST_MONTHS;
DELETE FROM LAST_CURRENCY_DAYS;
DELETE FROM LAST_COT_DAYS;
DELETE FROM LAST_ST_DAYS;
DELETE FROM LAST_ST_COT_DAYS;
DELETE FROM LAST_CASHIER_DAYS;
DELETE FROM LAST_HOURLY_DAYS;
DELETE FROM LAST_FOODCAT_DAYS;
DELETE FROM LAST_FOODCAT_CASHIER_DAYS;
DELETE FROM PLAN_DAYS;
DELETE FROM PLAN_COT_DAYS;
DELETE FROM PLAN_ST_DAYS;
DELETE FROM PLAN_ST_COT_DAYS;
*/

IF @CURVER IS NULL RETURN 0;

SET @CURVER = DATEADD(DD, DATEDIFF(DD, 0, @CURVER), 0);
SELECT @VER = DBVER FROM DBINFORMATION WHERE ALIAS = 'RATING_REP';

IF @VER IS NULL SET @VER = 0;

IF @VER <> @CURVER
BEGIN
	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL,
		DCOUNT INT NULL,
		CONSTRAINT PK_LAST_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_CURRENCY_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE [dbo].[LAST_CURRENCY_DAYS](
		[RESTAURANT] [int] NOT NULL,
		[DATE] [datetime] NOT NULL,
		[CURRENCY] [int] NOT NULL,
		[AMOUNT] [money] NULL,
		[COUNT] [int] NULL,
		CONSTRAINT [PK_LAST_CURRENCY_DAYS] PRIMARY KEY CLUSTERED ([RESTAURANT] ASC,[DATE] ASC,[CURRENCY] ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_WEEKS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_WEEKS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL,

		CONSTRAINT PK_LAST_WEEKS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_MONTHS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_MONTHS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		AMOUNT MONEY NULL,
		COUNT INT NULL
		CONSTRAINT PK_LAST_MONTHS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_COT_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_COT_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		COT INT NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL
		CONSTRAINT PK_LAST_COT_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, COT ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_ST_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_ST_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		SESSIONTYPE INT NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL
		CONSTRAINT PK_LAST_ST_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, SESSIONTYPE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_ST_COT_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_ST_COT_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		SESSIONTYPE INT NOT NULL,
		COT INT NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL
		CONSTRAINT PK_LAST_ST_COT_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, SESSIONTYPE ASC, COT ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_CASHIER_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_CASHIER_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		CASHIER INT NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL,
		DCOUNT INT NULL
		CONSTRAINT PK_LAST_CASHIER_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, CASHIER ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_HOURLY_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_HOURLY_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		AMOUNT0 MONEY NULL,
		COUNT0 INT NULL,
		AMOUNT1 MONEY NULL,
		COUNT1 INT NULL,
		AMOUNT2 MONEY NULL,
		COUNT2 INT NULL,
		AMOUNT3 MONEY NULL,
		COUNT3 INT NULL,
		AMOUNT4 MONEY NULL,
		COUNT4 INT NULL,
		AMOUNT5 MONEY NULL,
		COUNT5 INT NULL,
		AMOUNT6 MONEY NULL,
		COUNT6 INT NULL,
		AMOUNT7 MONEY NULL,
		COUNT7 INT NULL,
		AMOUNT8 MONEY NULL,
		COUNT8 INT NULL,
		AMOUNT9 MONEY NULL,
		COUNT9 INT NULL,
		AMOUNT10 MONEY NULL,
		COUNT10 INT NULL,
		AMOUNT11 MONEY NULL,
		COUNT11 INT NULL,
		AMOUNT12 MONEY NULL,
		COUNT12 INT NULL,
		AMOUNT13 MONEY NULL,
		COUNT13 INT NULL,
		AMOUNT14 MONEY NULL,
		COUNT14 INT NULL,
		AMOUNT15 MONEY NULL,
		COUNT15 INT NULL,
		AMOUNT16 MONEY NULL,
		COUNT16 INT NULL,
		AMOUNT17 MONEY NULL,
		COUNT17 INT NULL,
		AMOUNT18 MONEY NULL,
		COUNT18 INT NULL,
		AMOUNT19 MONEY NULL,
		COUNT19 INT NULL,
		AMOUNT20 MONEY NULL,
		COUNT20 INT NULL,
		AMOUNT21 MONEY NULL,
		COUNT21 INT NULL,
		AMOUNT22 MONEY NULL,
		COUNT22 INT NULL,
		AMOUNT23 MONEY NULL,
		COUNT23 INT NULL
		CONSTRAINT PK_LAST_HOURLY_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_FOODCAT_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_FOODCAT_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		CATEGORY INT NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL,
		DCOUNT INT NULL
		CONSTRAINT PK_LAST_FOODCAT_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, CATEGORY ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_FOODCAT_CASHIER_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_FOODCAT_CASHIER_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,	
		CATEGORY INT NOT NULL,
		CASHIER INT NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL,
		DCOUNT INT NULL
		CONSTRAINT PK_LAST_FOODCAT_CASHIER_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, CATEGORY ASC, CASHIER ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE PLAN_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		AMOUNTRATE FLOAT NULL,
		COUNTRATE FLOAT NULL
		CONSTRAINT PK_PLAN_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_COT_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE PLAN_COT_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		COT INT NOT NULL,
		AMOUNTRATE FLOAT NULL,
		COUNTRATE FLOAT NULL
		CONSTRAINT PK_PLAN_COT_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, COT ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_ST_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE PLAN_ST_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		SESSIONTYPE INT NOT NULL,
		AMOUNTRATE FLOAT NULL,
		COUNTRATE FLOAT NULL
		CONSTRAINT PK_PLAN_ST_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, SESSIONTYPE ASC)
		) ON [PRIMARY];

	IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_ST_COT_DAYS]') AND TYPE IN (N'U'))
		CREATE TABLE PLAN_ST_COT_DAYS(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		SESSIONTYPE INT NOT NULL,
		COT INT NOT NULL,
		AMOUNTRATE FLOAT NULL,
		COUNTRATE FLOAT NULL
		CONSTRAINT PK_PLAN_ST_COT_DAYS PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE ASC, SESSIONTYPE ASC, COT ASC)
		) ON [PRIMARY];

	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @WDATE DATETIME, @MDATE DATETIME;
		DECLARE @TABLE TABLE (SHIFTDATE DATETIME, IRESTAURANT INT, MIDSERVER INT, SHIFTNUM INT);

		INSERT INTO @TABLE
		SELECT SHIFTDATE,IRESTAURANT,MIDSERVER,SHIFTNUM FROM GLOBALSHIFTS WHERE SHIFTDATE BETWEEN @VER AND @CURVER-1 AND IRESTAURANT = @RES;

		--LOOKING FOR NEW DAYS;
		INSERT INTO LAST_DAYS (RESTAURANT, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE,SUM(PB.PAYSUM),COUNT(DISTINCT PC.GLOBALIDENT),SUM(PB.QUANTITY)
		FROM @TABLE 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
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE;

		--DAYS BY CURRENCIES;
		INSERT INTO LAST_CURRENCY_DAYS (RESTAURANT, DATE, CURRENCY, AMOUNT, COUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE,CL.SIFR,SUM(CL.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE 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
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE,CL.SIFR;
		
		--DAYS BY CATEGORIES;
		INSERT INTO LAST_COT_DAYS  (RESTAURANT, DATE, COT, AMOUNT, COUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE, O.COT, SUM(PC.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE 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
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE, O.COT;

		--DAYS BY SESSIONTYPES;
		INSERT INTO LAST_ST_DAYS  (RESTAURANT, DATE, SESSIONTYPE, AMOUNT, COUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE, OS.ISESSIONTYPE, SUM(PC.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE GS
		JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
		JOIN ORDERSESSIONS OS ON OS.MIDSERVER = O.MIDSERVER AND OS.VISIT = O.VISIT AND OS.ORDERIDENT = O.IDENTINVISIT
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = O.MIDSERVER AND PC.VISIT = O.VISIT AND PC.SESSIONUNI = OS.UNI  AND PC.STATE = 6
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE, OS.ISESSIONTYPE;

		--DAYS BY SESSIONTYPES;
		INSERT INTO LAST_ST_COT_DAYS  (RESTAURANT, DATE, SESSIONTYPE, COT, AMOUNT, COUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE, OS.ISESSIONTYPE, O.COT, SUM(PC.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE GS
		JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
		JOIN ORDERSESSIONS OS ON OS.MIDSERVER = O.MIDSERVER AND OS.VISIT = O.VISIT AND OS.ORDERIDENT = O.IDENTINVISIT
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = O.MIDSERVER AND PC.VISIT = O.VISIT AND PC.SESSIONUNI = OS.UNI  AND PC.STATE = 6
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE, OS.ISESSIONTYPE, O.COT;

		--DAYS BY CASHIERS;
		INSERT INTO LAST_CASHIER_DAYS (RESTAURANT, CASHIER, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT, PC.IAUTHOR, GS.SHIFTDATE, SUM(PB.PAYSUM), COUNT(DISTINCT PC.GLOBALIDENT), SUM(PB.QUANTITY)
		FROM @TABLE 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
		GROUP BY GS.IRESTAURANT,PC.IAUTHOR,GS.SHIFTDATE;

		-- DAYS BY HOURS!
		INSERT INTO LAST_HOURLY_DAYS (RESTAURANT, DATE, AMOUNT0, COUNT0, AMOUNT1, COUNT1, AMOUNT2, COUNT2, AMOUNT3, COUNT3, AMOUNT4, COUNT4, AMOUNT5, COUNT5, AMOUNT6, COUNT6
		, AMOUNT7, COUNT7, AMOUNT8, COUNT8, AMOUNT9, COUNT9, AMOUNT10, COUNT10, AMOUNT11, COUNT11, AMOUNT12, COUNT12, AMOUNT13, COUNT13, AMOUNT14, COUNT14, AMOUNT15, COUNT15
		, AMOUNT16, COUNT16, AMOUNT17, COUNT17, AMOUNT18, COUNT18, AMOUNT19, COUNT19, AMOUNT20, COUNT20, AMOUNT21, COUNT21, AMOUNT22, COUNT22, AMOUNT23, COUNT23)
		SELECT GS.IRESTAURANT, GS.SHIFTDATE, 
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 0 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 0 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 1 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 1 THEN  PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 2 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 2 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 3 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 3 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 4 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 4 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 5 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 5 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 6 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 6 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 7 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 7 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 8 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 8 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 9 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 9 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 10 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 10 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 11 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 11 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 12 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 12 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 13 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 13 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 14 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 14 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 15 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 15 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 16 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 16 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 17 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 17 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 18 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 18 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 19 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 19 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 20 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 20 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 21 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 21 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 22 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 22 THEN PC.GLOBALIDENT ELSE NULL END),
		SUM(CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 23 THEN PC.BINDEDSUM ELSE 0 END),
		COUNT(DISTINCT CASE DATEPART(HOUR,PC.CLOSEDATETIME) WHEN 23 THEN PC.GLOBALIDENT ELSE NULL END)
		FROM @TABLE 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
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE;

		--DAYS BY FOODCATEGORIES;
		INSERT INTO LAST_FOODCAT_DAYS (RESTAURANT, CATEGORY, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT,DG.PARENT,GS.SHIFTDATE,SUM(SD.PAYSUM),COUNT(DISTINCT PC.GLOBALIDENT), SUM(SD.QUANTITY)
		FROM @TABLE 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
		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 AND SD.STATE = 6
		JOIN DISHGROUPS DG ON DG.CHILD = SD.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
		GROUP BY GS.IRESTAURANT,DG.PARENT,GS.SHIFTDATE;

		--DAYS BY MOFIDIERS
		INSERT INTO LAST_FOODCAT_DAYS (RESTAURANT, CATEGORY, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT,0,GS.SHIFTDATE,0,COUNT(DISTINCT PC.GLOBALIDENT), SUM(DM.MODICNT)
		FROM @TABLE 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
		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 AND SD.STATE = 6
		LEFT 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)
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE;

		--DAYS BY FOODCATEGORIES AND CASHIERS;
		INSERT INTO LAST_FOODCAT_CASHIER_DAYS (RESTAURANT, CATEGORY, CASHIER, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT,DG.PARENT,PC.IAUTHOR,GS.SHIFTDATE,SUM(SD.PAYSUM),COUNT(DISTINCT PC.GLOBALIDENT), SUM(SD.QUANTITY)
		FROM @TABLE 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
		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 AND SD.STATE = 6
		JOIN DISHGROUPS DG ON DG.CHILD = SD.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
		GROUP BY GS.IRESTAURANT,DG.PARENT,PC.IAUTHOR,GS.SHIFTDATE;

		--DAYS BY MOFIDIERS AND CASHIERS

		INSERT INTO LAST_FOODCAT_CASHIER_DAYS (RESTAURANT, CATEGORY, CASHIER, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT,0,PC.IAUTHOR,GS.SHIFTDATE,0,COUNT(DISTINCT PC.GLOBALIDENT), SUM(DM.MODICNT)
		FROM @TABLE 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
		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 AND SD.STATE = 6
		LEFT 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)
		GROUP BY GS.IRESTAURANT,PC.IAUTHOR,GS.SHIFTDATE;

		--LOOKING FOR NEW WEEKS! 0W0
		SET @WDATE = DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,@CURVER-@@DATEFIRST), 0);

		IF @WDATE > @VER
		BEGIN
			DELETE FROM @TABLE;
			INSERT INTO @TABLE
			SELECT DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,SHIFTDATE-@@DATEFIRST),0),IRESTAURANT,MIDSERVER,SHIFTNUM FROM GLOBALSHIFTS 
			WHERE DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST,SHIFTDATE-@@DATEFIRST),0) BETWEEN DATEADD(WEEK, DATEDIFF(WEEK,-@@DATEFIRST, @VER-@@DATEFIRST),0) AND @WDATE-1 AND IRESTAURANT = @RES;

			INSERT INTO LAST_WEEKS (RESTAURANT, DATE, AMOUNT, COUNT)
			SELECT GS.IRESTAURANT, GS.SHIFTDATE, SUM(PC.BINDEDSUM), COUNT(DISTINCT PC.GLOBALIDENT)
			FROM @TABLE 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
			GROUP BY GS.IRESTAURANT, GS.SHIFTDATE;
		END;

		--LOOKING FOR NEW MONTHS! OxO
		SET @MDATE = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURVER), 0);
		IF @MDATE > @VER
		BEGIN
			DELETE FROM @TABLE;
			INSERT INTO @TABLE
			SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,SHIFTDATE),0),IRESTAURANT,MIDSERVER,SHIFTNUM FROM GLOBALSHIFTS 
			WHERE DATEADD(MONTH, DATEDIFF(MONTH,0,SHIFTDATE),0) BETWEEN DATEADD(MONTH, DATEDIFF(MONTH,0, @VER),0) AND @MDATE-1 AND IRESTAURANT = @RES;

			INSERT INTO LAST_MONTHS (RESTAURANT, DATE, AMOUNT, COUNT)
			SELECT GS.IRESTAURANT, GS.SHIFTDATE, SUM(PC.BINDEDSUM), COUNT(DISTINCT PC.GLOBALIDENT)
			FROM @TABLE 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
			GROUP BY GS.IRESTAURANT, GS.SHIFTDATE;
		END;

		-->BUG POSSIBLE - if you use last day of the week in @ver, and then jump two days ahead in @curver, it will cause key violation
		--IF CURRENT WEEK ENDS IN ANOTHER MONTH OR CURRENT WEEK STARTS IN CURRENT NEW MONTH
		--(do you even praise the sun?)
		DECLARE @NMS DATETIME, @NME DATETIME;
		SET @NMS = DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(WEEK,1,@WDATE)-1),0);
		--SELECT @NMS AS NMS, @WDATE AS WDATE, @MDATE AS MDATE, @VER AS VER, @CURVER AS CURVER;
		IF @WDATE > @VER AND (@NMS > @MDATE OR @MDATE > @VER)
		BEGIN

			--MONTHLY DAILY PLAN
			SET @NME = DATEADD(MONTH,1,@NMS)-1;
			
			WITH CALENDAR AS (
				SELECT @NMS AS DATE
				UNION ALL
				SELECT DATEADD(DAY , 1, DATE) AS DATE
				FROM CALENDAR
				WHERE DATEADD (DAY, 1, DATE) <= @NME
			)
			INSERT INTO PLAN_DAYS (RESTAURANT,DATE,AMOUNTRATE,COUNTRATE)
			SELECT @RES,CALENDAR.DATE,
			CASE ISNULL(SUM(CASE DATEPART(WEEKDAY,CALENDAR.DATE) WHEN 1 THEN AMOUNT1 WHEN 2 THEN AMOUNT2 WHEN 3 THEN AMOUNT3 WHEN 4 THEN AMOUNT4 WHEN 5 THEN AMOUNT5 WHEN 6 THEN AMOUNT6 WHEN 7 THEN AMOUNT7 END) OVER(),0) WHEN 0 THEN 0 ELSE
			CAST(CASE DATEPART(WEEKDAY,CALENDAR.DATE) WHEN 1 THEN AMOUNT1 WHEN 2 THEN AMOUNT2 WHEN 3 THEN AMOUNT3 WHEN 4 THEN AMOUNT4 WHEN 5 THEN AMOUNT5 WHEN 6 THEN AMOUNT6 WHEN 7 THEN AMOUNT7 END AS FLOAT)
			/ SUM(CASE DATEPART(WEEKDAY,CALENDAR.DATE) WHEN 1 THEN AMOUNT1 WHEN 2 THEN AMOUNT2 WHEN 3 THEN AMOUNT3 WHEN 4 THEN AMOUNT4 WHEN 5 THEN AMOUNT5 WHEN 6 THEN AMOUNT6 WHEN 7 THEN AMOUNT7 END) OVER() END,
			CASE ISNULL(SUM(CASE DATEPART(WEEKDAY,CALENDAR.DATE) WHEN 1 THEN COUNT1 WHEN 2 THEN COUNT2 WHEN 3 THEN COUNT3 WHEN 4 THEN COUNT4 WHEN 5 THEN COUNT5 WHEN 6 THEN COUNT6 WHEN 7 THEN COUNT7 END) OVER(),0) WHEN 0 THEN 0 ELSE
			CAST(CASE DATEPART(WEEKDAY,CALENDAR.DATE) WHEN 1 THEN COUNT1 WHEN 2 THEN COUNT2 WHEN 3 THEN COUNT3 WHEN 4 THEN COUNT4 WHEN 5 THEN COUNT5 WHEN 6 THEN COUNT6 WHEN 7 THEN COUNT7 END AS FLOAT)
			/ SUM(CASE DATEPART(WEEKDAY,CALENDAR.DATE) WHEN 1 THEN COUNT1 WHEN 2 THEN COUNT2 WHEN 3 THEN COUNT3 WHEN 4 THEN COUNT4 WHEN 5 THEN COUNT5 WHEN 6 THEN COUNT6 WHEN 7 THEN COUNT7 END) OVER() END
			FROM CALENDAR
			OUTER APPLY
			(
				SELECT
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7 
				WHEN  SUM(LD.AMOUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 1 THEN LD.AMOUNT ELSE 0 END) AS FLOAT) / SUM(LD.AMOUNT) END AS AMOUNT1,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 1 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT1,
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN  SUM(LD.AMOUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 2 THEN LD.AMOUNT ELSE 0 END) AS FLOAT)/ SUM(LD.AMOUNT) END AS AMOUNT2,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 2 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT2,
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN  SUM(LD.AMOUNT) = 0 THEN  0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 3 THEN LD.AMOUNT ELSE 0 END) AS FLOAT)/ SUM(LD.AMOUNT) END AS AMOUNT3,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 3 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT3,
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN  SUM(LD.AMOUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 4 THEN LD.AMOUNT ELSE 0 END) AS FLOAT) / SUM(LD.AMOUNT) END AS AMOUNT4,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 4 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT4,
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN  SUM(LD.AMOUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 5 THEN LD.AMOUNT ELSE 0 END) AS FLOAT) / SUM(LD.AMOUNT) END AS AMOUNT5,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 5 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT5,
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN  SUM(LD.AMOUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 6 THEN LD.AMOUNT ELSE 0 END) AS FLOAT) / SUM(LD.AMOUNT) END AS AMOUNT6,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 6 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT6,
				CASE WHEN SUM(LD.AMOUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN  SUM(LD.AMOUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 7 THEN LD.AMOUNT ELSE 0 END) AS FLOAT) / SUM(LD.AMOUNT) END AS AMOUNT7,
				CASE WHEN SUM(LD.COUNT) IS NULL THEN CAST(1 AS FLOAT) / 7
				WHEN SUM(LD.COUNT) = 0 THEN 0 ELSE CAST(SUM(CASE DATEPART(WEEKDAY,LD.DATE) WHEN 7 THEN LD.COUNT ELSE 0 END) AS FLOAT) / SUM(LD.COUNT) END AS COUNT7
				FROM LAST_DAYS LD
				WHERE LD.RESTAURANT = @RES AND DATEADD(WEEK,DATEDIFF(WEEK,-@@DATEFIRST,LD.DATE-@@DATEFIRST),0) IN (SELECT TOP 4 LW.DATE FROM LAST_WEEKS LW WHERE LW.RESTAURANT = @RES AND LW.DATE < @WDATE ORDER BY DATE DESC)
			) W
			OPTION (MAXRECURSION 0);

			--MONTHLY COT PLAN
			INSERT INTO PLAN_COT_DAYS (RESTAURANT, DATE, COT, AMOUNTRATE, COUNTRATE)
			SELECT @RES, @NMS, COT.SIFR, 
			CASE WHEN SUM(LCD.AMOUNT) OVER() IS NULL THEN cast(1 as float) / COUNT(*) OVER ()
			WHEN SUM(LCD.AMOUNT) OVER() = 0 THEN 0 ELSE CAST(ISNULL(LCD.AMOUNT,0) AS FLOAT) / SUM(LCD.AMOUNT) OVER() END,			
			CASE WHEN SUM(LCD.COUNT) OVER() IS NULL THEN cast(1 as float) / COUNT(*) OVER ()
			WHEN SUM(LCD.COUNT) OVER() = 0 THEN 0 ELSE CAST(ISNULL(LCD.COUNT,0) AS FLOAT) / SUM(LCD.COUNT) OVER() END
			FROM CHANGEABLEORDERTYPES COT
			OUTER APPLY
			(
				SELECT
				SUM(LCD.AMOUNT) AS AMOUNT,
				SUM(LCD.COUNT) AS COUNT
				FROM LAST_COT_DAYS LCD
				WHERE LCD.RESTAURANT = @RES AND LCD.COT = COT.SIFR
				AND DATEADD(WEEK,DATEDIFF(WEEK,-@@DATEFIRST,LCD.DATE-@@DATEFIRST),0) IN (SELECT TOP 4 LW.DATE FROM LAST_WEEKS LW WHERE LW.RESTAURANT = @RES AND LW.DATE < @WDATE ORDER BY DATE DESC)
			) LCD;

			--MONTHLY ST PLAN
			INSERT INTO PLAN_ST_DAYS (RESTAURANT, DATE, SESSIONTYPE, AMOUNTRATE, COUNTRATE)
			SELECT @RES, @NMS, ST.SIFR, 
			CASE WHEN SUM(LSD.AMOUNT) OVER() IS NULL THEN cast(1 as float) / COUNT(*) OVER ()
			WHEN SUM(LSD.AMOUNT) OVER() = 0 THEN 0 ELSE CAST(ISNULL(LSD.AMOUNT,0) AS FLOAT) / SUM(LSD.AMOUNT) OVER() END,			
			CASE WHEN SUM(LSD.COUNT) OVER() IS NULL THEN cast(1 as float) / COUNT(*) OVER ()
			WHEN SUM(LSD.COUNT) OVER() = 0 THEN 0 ELSE CAST(ISNULL(LSD.COUNT,0) AS FLOAT) / SUM(LSD.COUNT) OVER() END
			FROM (SELECT 0 AS SIFR UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) ST
			OUTER APPLY
			(
				SELECT
				SUM(LSD.AMOUNT) AS AMOUNT,
				SUM(LSD.COUNT) AS COUNT
				FROM LAST_ST_DAYS LSD
				WHERE LSD.RESTAURANT = @RES AND LSD.SESSIONTYPE = ST.SIFR
				AND DATEADD(WEEK,DATEDIFF(WEEK,-@@DATEFIRST,LSD.DATE-@@DATEFIRST),0) IN (SELECT TOP 4 LW.DATE FROM LAST_WEEKS LW WHERE LW.RESTAURANT = @RES AND LW.DATE < @WDATE ORDER BY DATE DESC)
			) LSD;

			--MONTHLY ST+COT PLAN
			INSERT INTO PLAN_ST_COT_DAYS (RESTAURANT, DATE, SESSIONTYPE, COT, AMOUNTRATE, COUNTRATE)
			SELECT @RES, @NMS, STSIFR, COTSIFR,
			CASE WHEN SUM(LSD.AMOUNT) OVER() IS NULL THEN cast(1 as float) / COUNT(*) OVER ()
			WHEN SUM(LSD.AMOUNT) OVER() = 0 THEN 0 ELSE CAST(ISNULL(LSD.AMOUNT,0) AS FLOAT) / SUM(LSD.AMOUNT) OVER() END,			
			CASE WHEN SUM(LSD.COUNT) OVER() IS NULL THEN cast(1 as float) / COUNT(*) OVER ()
			WHEN SUM(LSD.COUNT) OVER() = 0 THEN 0 ELSE CAST(ISNULL(LSD.COUNT,0) AS FLOAT) / SUM(LSD.COUNT) OVER() END
			FROM (SELECT COT.SIFR AS COTSIFR,ST.SIFR STSIFR FROM CHANGEABLEORDERTYPES COT,(SELECT 0 AS SIFR UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) ST) AS DUMMY
			OUTER APPLY
			(
				SELECT
				SUM(LSD.AMOUNT) AS AMOUNT,
				SUM(LSD.COUNT) AS COUNT
				FROM LAST_ST_COT_DAYS LSD
				WHERE LSD.RESTAURANT = @RES AND LSD.SESSIONTYPE = DUMMY.STSIFR AND LSD.COT = DUMMY.COTSIFR
				AND DATEADD(WEEK,DATEDIFF(WEEK,-@@DATEFIRST,LSD.DATE-@@DATEFIRST),0) IN (SELECT TOP 4 LW.DATE FROM LAST_WEEKS LW WHERE LW.RESTAURANT = @RES AND LW.DATE < @WDATE ORDER BY DATE DESC)
			) LSD;
		END;

		IF @VER = 0 INSERT INTO DBINFORMATION (ALIAS, DBVER) VALUES ('RATING_REP',CAST(@CURVER AS INT))
		ELSE UPDATE DBINFORMATION SET DBVER = CAST(@CURVER AS INT) WHERE ALIAS = 'RATING_REP';

	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
		DECLARE @ERRORMESSAGE NVARCHAR(4000);
		DECLARE @ERRORSEVERITY INT;
		DECLARE @ERRORSTATE INT;

		SET @ERRORMESSAGE = ERROR_MESSAGE();
		SET @ERRORSEVERITY = ERROR_SEVERITY();
		SET @ERRORSTATE = ERROR_STATE();
		RAISERROR(@ERRORMESSAGE,@ERRORSEVERITY,@ERRORSTATE);
	END CATCH

END

IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

END;