IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_CURRENCY_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE [DBO].[LAST_CURRENCY_DAYS];

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_WEEKS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_WEEKS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_MONTHS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_MONTHS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_COT_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_COT_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_ST_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_ST_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_ST_COT_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_ST_COT_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_CASHIER_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_CASHIER_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_HALFHOURLY]') AND TYPE IN (N'U'))
	DROP TABLE LAST_HALFHOURLY;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_HOURLY_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_HOURLY_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_FOODCAT_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_FOODCAT_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[LAST_FOODCAT_CASHIER_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE LAST_FOODCAT_CASHIER_DAYS;
	
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE PLAN_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_COT_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE PLAN_COT_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_ST_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE PLAN_ST_DAYS;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[PLAN_ST_COT_DAYS]') AND TYPE IN (N'U'))
	DROP TABLE PLAN_ST_COT_DAYS;

DELETE FROM DBINFORMATION WHERE ALIAS = 'RATING_REP';

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RATING_REP_CALC]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RATING_REP_CALC;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCSALES1]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCSALES1;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCSALES2]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCSALES2;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCSALES3]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCSALES3;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCSALES4]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCSALES4;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCSALES5]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCSALES5;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCSALES6]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCSALES6;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCRATING1]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCRATING1;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCRATING2]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCRATING2;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCOVERALL1]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCOVERALL1;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROCOVERALL2]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROCOVERALL2;

IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[RK7SQLPROC_REP_CALC]') AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE RK7SQLPROC_REP_CALC;

GO

CREATE PROCEDURE [dbo].[RATING_REP_CALC](
	@RES INT,
	@CURVER DATETIME,
	@VER DATETIME OUTPUT,
	@AMOUNTFIELD INT OUTPUT,
	@COUNTFIELD 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_HALFHOURLY]') AND TYPE IN (N'U'))
		CREATE TABLE LAST_HALFHOURLY(
		RESTAURANT INT NOT NULL,
		DATE DATETIME NOT NULL,
		AMOUNT MONEY NULL,
		COUNT INT NULL
		CONSTRAINT PK_LAST_HALFHOURLY PRIMARY KEY CLUSTERED (RESTAURANT ASC, DATE 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, VISIT INT, IDENTINVISIT INT, COT INT);

		INSERT INTO @TABLE
		SELECT SHIFTDATE,IRESTAURANT,O.MIDSERVER,O.VISIT,O.IDENTINVISIT,O.COT
		FROM GLOBALSHIFTS GS
		JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
		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 PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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 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 PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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, GS.COT, SUM(PC.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.IDENTINVISIT  AND PC.STATE = 6
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE, GS.COT;

		--DAYS BY SESSIONTYPES;
		INSERT INTO LAST_ST_DAYS  (RESTAURANT, DATE, SESSIONTYPE, AMOUNT, COUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE, CASE WHEN OS.ISESSIONTYPE IS NULL THEN 0 ELSE OS.ISESSIONTYPE END, SUM(PC.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.IDENTINVISIT AND PC.STATE = 6
		OUTER APPLY (SELECT TOP 1 OS.ISESSIONTYPE FROM ORDERSESSIONS OS WHERE OS.MIDSERVER = GS.MIDSERVER AND OS.VISIT = GS.VISIT AND OS.ORDERIDENT = GS.IDENTINVISIT AND OS.ISESSIONTYPE = 5 AND OS.STATE = 6) OS
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE, OS.ISESSIONTYPE;

		--DAYS BY SESSIONTYPES AND CATEGORIES;
		INSERT INTO LAST_ST_COT_DAYS  (RESTAURANT, DATE, SESSIONTYPE, COT, AMOUNT, COUNT)
		SELECT GS.IRESTAURANT,GS.SHIFTDATE, CASE WHEN OS.ISESSIONTYPE IS NULL THEN 0 ELSE OS.ISESSIONTYPE END, GS.COT, SUM(PC.BINDEDSUM),COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.IDENTINVISIT AND PC.STATE = 6
		OUTER APPLY (SELECT TOP 1 OS.ISESSIONTYPE FROM ORDERSESSIONS OS WHERE OS.MIDSERVER = GS.MIDSERVER AND OS.VISIT = GS.VISIT AND OS.ORDERIDENT = GS.IDENTINVISIT AND OS.ISESSIONTYPE = 5 AND OS.STATE = 6) OS
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE, OS.ISESSIONTYPE, GS.COT;

		--DAYS BY CASHIERS;
		INSERT INTO LAST_CASHIER_DAYS (RESTAURANT, CASHIER, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT, ISNULL(OS1.IAUTHOR,OS2.IAUTHOR) AS IAUTHOR, GS.SHIFTDATE, SUM(PB.PAYSUM), COUNT(DISTINCT PC.GLOBALIDENT), SUM(PB.QUANTITY)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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 AND PB.STATE = 6
		LEFT JOIN SESSIONDISHES SD ON SD.MIDSERVER = PB.MIDSERVER AND SD.VISIT = PB.VISIT AND SD.UNI = PB.DISHUNI
		LEFT JOIN ORDERSESSIONS OS1 ON OS1.MIDSERVER = SD.MIDSERVER AND OS1.VISIT = SD.VISIT AND OS1.UNI = SD.SESSIONUNI
		LEFT JOIN DISHDISCOUNTS DD ON DD.MIDSERVER = PB.MIDSERVER AND DD.VISIT = PB.VISIT AND DD.UNI = PB.CHARGEUNI
		LEFT JOIN ORDERSESSIONS OS2 ON OS2.MIDSERVER = DD.MIDSERVER AND OS2.VISIT = DD.VISIT AND OS2.UNI = DD.SESSIONUNI
		GROUP BY GS.IRESTAURANT,ISNULL(OS1.IAUTHOR,OS2.IAUTHOR),GS.SHIFTDATE;

		-- DAYS BY HALFHOURS!!!

		INSERT INTO LAST_HALFHOURLY(RESTAURANT,DATE,AMOUNT,COUNT)
		SELECT GS.IRESTAURANT,
		DATEADD(MINUTE,DATEPART(MINUTE,PC.CLOSEDATETIME) / 30 * 30,DATEADD(HOUR,DATEPART(HOUR,PC.CLOSEDATETIME),GS.SHIFTDATE)), SUM(PC.BINDEDSUM), COUNT(DISTINCT PC.GLOBALIDENT)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.IDENTINVISIT AND PC.STATE = 6
		GROUP BY GS.IRESTAURANT, DATEADD(MINUTE,DATEPART(MINUTE,PC.CLOSEDATETIME) / 30 * 30,DATEADD(HOUR,DATEPART(HOUR,PC.CLOSEDATETIME),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 PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.IDENTINVISIT AND PC.STATE = 6
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE;
*/

		--DAYS BY FOODCATEGORIES;
		WITH T AS (
			SELECT GS.IRESTAURANT,GS.SHIFTDATE,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,SD.COMBODISHUNI,SD.PAYSUM,PC.GLOBALIDENT,SD.QUANTITY
			FROM @TABLE GS
			JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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

			UNION ALL

			SELECT T.IRESTAURANT,T.SHIFTDATE,SD.MIDSERVER,SD.VISIT, SD.SIFR,SD.UNI,0       ,SD.PAYSUM,T.GLOBALIDENT,SD.QUANTITY
			FROM T
			JOIN SESSIONDISHES SD ON SD.MIDSERVER = T.MIDSERVER AND SD.VISIT = T.VISIT AND T.COMBODISHUNI = SD.UNI
		)
		INSERT INTO LAST_FOODCAT_DAYS (RESTAURANT, CATEGORY, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT IRESTAURANT,PARENT,SHIFTDATE,SUM(PAYSUM),COUNT(DISTINCT GLOBALIDENT), SUM(QUANTITY) FROM 
		(
			SELECT IRESTAURANT,SHIFTDATE,MIDSERVER,VISIT,UNI,SIFR,PAYSUM,GLOBALIDENT,QUANTITY
			FROM T
			GROUP BY IRESTAURANT,SHIFTDATE,MIDSERVER,VISIT,UNI,SIFR,PAYSUM,GLOBALIDENT,QUANTITY
		)
		DUMMY
		JOIN DISHGROUPS DG ON DG.CHILD = DUMMY.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
		GROUP BY IRESTAURANT,PARENT,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*SD.QUANTITY)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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 DISHMODIFIERS DM ON DM.MIDSERVER = SD.MIDSERVER AND DM.VISIT = SD.VISIT AND DM.DISHUNI = SD.UNI AND DM.PRLISTSUM > 0  AND DM.COMBODISHUNI = 0
		GROUP BY GS.IRESTAURANT,GS.SHIFTDATE;

		--DAYS BY FOODCATEGORIES AND CASHIERS;
		WITH T AS (
			SELECT GS.IRESTAURANT,OS.IAUTHOR,GS.SHIFTDATE,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,SD.COMBODISHUNI,SD.PAYSUM,PC.GLOBALIDENT, SD.QUANTITY
			FROM @TABLE GS
			JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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 ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI

			UNION ALL

			SELECT T.IRESTAURANT,OS.IAUTHOR,T.SHIFTDATE,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,0       ,SD.PAYSUM,T.GLOBALIDENT,SD.QUANTITY
			FROM T
			JOIN SESSIONDISHES SD ON SD.MIDSERVER = T.MIDSERVER AND SD.VISIT = T.VISIT AND T.COMBODISHUNI = SD.UNI
			JOIN ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI
		)
		INSERT INTO LAST_FOODCAT_CASHIER_DAYS (RESTAURANT, CATEGORY, CASHIER, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT IRESTAURANT,PARENT,IAUTHOR,SHIFTDATE,SUM(PAYSUM),COUNT(DISTINCT GLOBALIDENT), SUM(QUANTITY) FROM 
		(SELECT IRESTAURANT,IAUTHOR,SHIFTDATE,MIDSERVER,VISIT,UNI,SIFR,PAYSUM,GLOBALIDENT,QUANTITY FROM T
		GROUP BY IRESTAURANT,IAUTHOR,SHIFTDATE,MIDSERVER,VISIT,UNI,SIFR,PAYSUM,GLOBALIDENT,QUANTITY)
		DUMMY
		JOIN DISHGROUPS DG ON DG.CHILD = DUMMY.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
		GROUP BY IRESTAURANT,PARENT,IAUTHOR,SHIFTDATE;

		--DAYS BY MOFIDIERS AND CASHIERS

		INSERT INTO LAST_FOODCAT_CASHIER_DAYS (RESTAURANT, CATEGORY, CASHIER, DATE, AMOUNT, COUNT, DCOUNT)
		SELECT GS.IRESTAURANT,0,OS.IAUTHOR,GS.SHIFTDATE,0,COUNT(DISTINCT PC.GLOBALIDENT), SUM(DM.MODICNT*SD.QUANTITY)
		FROM @TABLE GS
		JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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 ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI
		JOIN DISHMODIFIERS DM ON DM.MIDSERVER = SD.MIDSERVER AND DM.VISIT = SD.VISIT AND DM.DISHUNI = SD.UNI AND DM.PRLISTSUM > 0 AND DM.COMBODISHUNI = 0
		GROUP BY GS.IRESTAURANT,OS.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,O.MIDSERVER,O.VISIT,O.IDENTINVISIT,O.COT 
			FROM GLOBALSHIFTS GS
			JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
			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 PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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,O.MIDSERVER,O.VISIT,O.IDENTINVISIT,O.COT
			FROM GLOBALSHIFTS GS
			JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
			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 PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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);
		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;

GO

CREATE PROCEDURE RK7SQLPROC_REP_CALC AS
BEGIN
	DECLARE @RES INT;
	DECLARE @GETDATE DATETIME;
	DECLARE @CURR_DAY DATETIME;
	SELECT TOP 1 @CURR_DAY = SHIFTDATE, @RES = IRESTAURANT FROM GLOBALSHIFTS WHERE CLOSED = 0 ORDER BY MIDSERVER,SHIFTNUM DESC;

	SET @GETDATE = GETDATE();
	
	IF(@RES IS NULL)
	BEGIN
		SELECT TOP 1 @RES = IRESTAURANT FROM GLOBALSHIFTS ORDER BY MIDSERVER,SHIFTNUM DESC;
		SET @CURR_DAY = DATEADD(DAY,DATEDIFF(DAY,0,@GETDATE),0);
	END;

	EXEC RATING_REP_CALC @RES,@GETDATE, @VER = NULL,
	@FOODCAT1 = NULL, @FOODCAT2 = NULL, @FOODCAT3 = NULL, @FOODCAT4 = NULL, @FOODCAT5 = NULL, 
	@FOODFIELD1 = NULL, @FOODFIELD2 = NULL, @FOODFIELD3 = NULL, @FOODFIELD4 = NULL, @FOODFIELD5 = NULL,
	@COUNTFIELD = NULL, @AMOUNTFIELD = NULL;

	SELECT (SELECT NAME FROM RESTAURANTS WHERE SIFR = @RES) AS RESTAURANT, @CURR_DAY AS SHIFTDATE;
END;

GO

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

GO

--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	ISNULL(OS1.IAUTHOR,OS2.IAUTHOR) AS 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
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
LEFT JOIN SESSIONDISHES SD ON SD.MIDSERVER = PB.MIDSERVER AND SD.VISIT = PB.VISIT AND SD.UNI = PB.DISHUNI
LEFT JOIN ORDERSESSIONS OS1 ON OS1.MIDSERVER = SD.MIDSERVER AND OS1.VISIT = SD.VISIT AND OS1.UNI = SD.SESSIONUNI
LEFT JOIN DISHDISCOUNTS DD ON DD.MIDSERVER = PB.MIDSERVER AND DD.VISIT = PB.VISIT AND DD.UNI = PB.CHARGEUNI
LEFT JOIN ORDERSESSIONS OS2 ON OS2.MIDSERVER = DD.MIDSERVER AND OS2.VISIT = DD.VISIT AND OS2.UNI = DD.SESSIONUNI
WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY-- AND PC.IAUTHOR = DUMMY.CASHIER
GROUP BY ISNULL(OS1.IAUTHOR,OS2.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;

GO

--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(CASE DATEPART(HOUR,LHH.DATE) WHEN 0 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT0, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 0 THEN LHH.COUNT ELSE 0 END) AS COUNT0,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 1 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT1, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 1 THEN LHH.COUNT ELSE 0 END) AS COUNT1,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 2 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT2, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 2 THEN LHH.COUNT ELSE 0 END) AS COUNT2,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 3 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT3, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 3 THEN LHH.COUNT ELSE 0 END) AS COUNT3,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 4 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT4, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 4 THEN LHH.COUNT ELSE 0 END) AS COUNT4,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 5 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT5, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 5 THEN LHH.COUNT ELSE 0 END) AS COUNT5,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 6 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT6, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 6 THEN LHH.COUNT ELSE 0 END) AS COUNT6,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 7 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT7, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 7 THEN LHH.COUNT ELSE 0 END) AS COUNT7,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 8 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT8, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 8 THEN LHH.COUNT ELSE 0 END) AS COUNT8,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 9 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT9, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 9 THEN LHH.COUNT ELSE 0 END) AS COUNT9,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 10 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT10, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 10 THEN LHH.COUNT ELSE 0 END) AS COUNT10,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 11 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT11, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 11 THEN LHH.COUNT ELSE 0 END) AS COUNT11,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 12 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT12, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 12 THEN LHH.COUNT ELSE 0 END) AS COUNT12,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 13 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT13, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 13 THEN LHH.COUNT ELSE 0 END) AS COUNT13,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 14 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT14, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 14 THEN LHH.COUNT ELSE 0 END) AS COUNT14,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 15 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT15, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 15 THEN LHH.COUNT ELSE 0 END) AS COUNT15,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 16 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT16, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 16 THEN LHH.COUNT ELSE 0 END) AS COUNT16,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 17 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT17, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 17 THEN LHH.COUNT ELSE 0 END) AS COUNT17,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 18 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT18, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 18 THEN LHH.COUNT ELSE 0 END) AS COUNT18,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 19 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT19, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 19 THEN LHH.COUNT ELSE 0 END) AS COUNT19,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 20 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT20, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 20 THEN LHH.COUNT ELSE 0 END) AS COUNT20,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 21 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT21, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 21 THEN LHH.COUNT ELSE 0 END) AS COUNT21,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 22 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT22, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 22 THEN LHH.COUNT ELSE 0 END) AS COUNT22,
	SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 23 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT23, SUM(CASE DATEPART(HOUR,LHH.DATE) WHEN 23 THEN LHH.COUNT ELSE 0 END) AS COUNT23
FROM LAST_HALFHOURLY LHH
WHERE LHH.RESTAURANT = @RES AND DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0) <> @CURR_DAY AND DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0) BETWEEN @CURR_MONTH1 AND @CURR_MONTH2
) CMONTH;

END;


GO

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

GO

--SALES(5)

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES5] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME,/* @CURR_YEAR1 DATETIME, @CURR_YEAR2 DATETIME, */@GETDATE DATETIME, 
@FOODCAT1 INT, @FOODCAT2 INT, @FOODCAT3 INT, @FOODCAT4 INT, @FOODCAT5 INT;

SELECT TOP 1 @CURR_DAY = SHIFTDATE, @RES = IRESTAURANT FROM GLOBALSHIFTS WHERE CLOSED = 0 ORDER BY SHIFTDATE DESC;

SET @CURR_DAY = DATEADD(DAY,DATEDIFF(DAY,0,@CURR_DAY),0);
SET @CURR_MONTH1 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY) - 11, 0);
SET @CURR_MONTH2 = DATEADD(MONTH, DATEDIFF(MONTH,0,@CURR_DAY)+1, -1);
/* *************** */

SET @GETDATE = GETDATE();

EXEC RATING_REP_CALC NULL, NULL, @VER = NULL, @COUNTFIELD = NULL, @AMOUNTFIELD = NULL,
@FOODCAT1 = @FOODCAT1 OUTPUT, @FOODCAT2 = @FOODCAT2 OUTPUT, @FOODCAT3 = @FOODCAT3 OUTPUT, @FOODCAT4 = @FOODCAT4 OUTPUT, @FOODCAT5 = @FOODCAT5 OUTPUT, 
@FOODFIELD1 = NULL, @FOODFIELD2 = NULL, @FOODFIELD3 = NULL, @FOODFIELD4 = NULL, @FOODFIELD5 = NULL;

WITH T AS (
	SELECT GS.SHIFTDATE,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,SD.COMBODISHUNI,SD.PAYSUM, SD.QUANTITY
	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
	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 ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI
	WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY

	UNION ALL

	SELECT T.SHIFTDATE,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,0       ,SD.PAYSUM,SD.QUANTITY
	FROM T
	JOIN SESSIONDISHES SD ON SD.MIDSERVER = T.MIDSERVER AND SD.VISIT = T.VISIT AND T.COMBODISHUNI = SD.UNI
	JOIN ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI
)
SELECT CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CGNAME AS CATEGORY,
DATEPART(MONTH,CYEAR.DATE) AS YDATE, ISNULL(CYEAR.AMOUNT,0) + ISNULL(CDAY.AMOUNT,0) AS YAMOUNT, ISNULL(CYEAR.COUNT,0) + ISNULL(CDAY.COUNT,0) AS YCOUNT,
ISNULL(CYEAR.DCOUNT,0) + ISNULL(CDAY.DCOUNT,0) AS YDCOUNT,
ISNULL(CCAT.AMOUNT,0) + ISNULL(CCDAY.AMOUNT,0) AS CAMOUNT,ISNULL(CCAT.COUNT,0) AS CCOUNT,ISNULL(CCAT.DCOUNT,0) + ISNULL(CCDAY.DCOUNT,0) AS CDCOUNT
FROM 
(
	SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, CG.SIFR*256+CG.NUMINGROUP AS CGSIFR, CG.NAME AS CGNAME
	FROM RESTAURANTS R, CLASSIFICATORGROUPS CG 
	WHERE R.SIFR = @RES AND CG.SIFR*256+CG.NUMINGROUP IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
) DUMMY
OUTER APPLY (
	SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,DATE), 0) AS DATE, SUM(AMOUNT) AS AMOUNT, SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT
	FROM LAST_DAYS LD WHERE LD.RESTAURANT = @RES AND LD.DATE BETWEEN @CURR_MONTH1 AND @CURR_MONTH2 AND LD.DATE <> @CURR_DAY
	GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0,DATE), 0)
) CYEAR
OUTER APPLY (
	SELECT SUM(AMOUNT) AS AMOUNT, SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT
	FROM LAST_FOODCAT_DAYS LFD WHERE LFD.RESTAURANT = @RES AND CASE LFD.CATEGORY WHEN 0 THEN @FOODCAT5 ELSE LFD.CATEGORY END = DUMMY.CGSIFR AND DATEADD(MONTH, DATEDIFF(MONTH,0,LFD.DATE), 0) = CYEAR.DATE AND LFD.DATE <> @CURR_DAY
) CCAT
LEFT JOIN
(
	SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,SHIFTDATE), 0) AS DATE, DG.PARENT, SUM(PAYSUM) AS AMOUNT, SUM(QUANTITY) AS DCOUNT
	FROM 
	(SELECT SHIFTDATE,SIFR,PAYSUM,QUANTITY FROM T GROUP BY MIDSERVER,VISIT,UNI,SHIFTDATE,SIFR,PAYSUM,QUANTITY)
	DUMMY
	JOIN MENUITEMS MI ON MI.SIFR = DUMMY.SIFR
	LEFT JOIN DISHGROUPS DG ON DG.CHILD = DUMMY.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
	GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0,SHIFTDATE), 0),DG.PARENT
) CCDAY ON CCDAY.PARENT = DUMMY.CGSIFR AND CCDAY.DATE = CYEAR.DATE
LEFT JOIN
(
	SELECT	DATEADD(MONTH, DATEDIFF(MONTH,0,GS.SHIFTDATE), 0) AS DATE, 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
	GROUP BY DATEADD(MONTH, DATEDIFF(MONTH,0,GS.SHIFTDATE), 0)
) CDAY ON CCDAY.DATE = CYEAR.DATE
ORDER BY CYEAR.DATE,dummy.RNAME,DUMMY.CGNAME;

END;

GO

--SALES(3) 

CREATE PROCEDURE [dbo].[RK7SQLPROCSALES6] 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 DATEDIFF(MINUTE,DATEADD(DAY,0,DATEDIFF(DAY,0,LHH.DATE)),LHH.DATE) / 30
from last_halfhourly LHH
order by DATEDIFF(MINUTE,DATEADD(DAY,0,DATEDIFF(DAY,0,LHH.DATE)),LHH.DATE) / 30
*/
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,
		ISNULL(CDAY.AMOUNT24,0) AS CD_AMOUNT24, ISNULL(CDAY.COUNT24,0) AS CD_COUNT24,ISNULL(CMONTH.AMOUNT24,0) + ISNULL(CDAY.AMOUNT24,0) AS CM_AMOUNT24, ISNULL(CMONTH.COUNT24,0) + ISNULL(CDAY.COUNT24,0) AS CM_COUNT24,
		ISNULL(CDAY.AMOUNT25,0) AS CD_AMOUNT25, ISNULL(CDAY.COUNT25,0) AS CD_COUNT25,ISNULL(CMONTH.AMOUNT25,0) + ISNULL(CDAY.AMOUNT25,0) AS CM_AMOUNT25, ISNULL(CMONTH.COUNT25,0) + ISNULL(CDAY.COUNT25,0) AS CM_COUNT25,
		ISNULL(CDAY.AMOUNT26,0) AS CD_AMOUNT26, ISNULL(CDAY.COUNT26,0) AS CD_COUNT26,ISNULL(CMONTH.AMOUNT26,0) + ISNULL(CDAY.AMOUNT26,0) AS CM_AMOUNT26, ISNULL(CMONTH.COUNT26,0) + ISNULL(CDAY.COUNT26,0) AS CM_COUNT26,
		ISNULL(CDAY.AMOUNT27,0) AS CD_AMOUNT27, ISNULL(CDAY.COUNT27,0) AS CD_COUNT27,ISNULL(CMONTH.AMOUNT27,0) + ISNULL(CDAY.AMOUNT27,0) AS CM_AMOUNT27, ISNULL(CMONTH.COUNT27,0) + ISNULL(CDAY.COUNT27,0) AS CM_COUNT27,
		ISNULL(CDAY.AMOUNT28,0) AS CD_AMOUNT28, ISNULL(CDAY.COUNT28,0) AS CD_COUNT28,ISNULL(CMONTH.AMOUNT28,0) + ISNULL(CDAY.AMOUNT28,0) AS CM_AMOUNT28, ISNULL(CMONTH.COUNT28,0) + ISNULL(CDAY.COUNT28,0) AS CM_COUNT28,
		ISNULL(CDAY.AMOUNT29,0) AS CD_AMOUNT29, ISNULL(CDAY.COUNT29,0) AS CD_COUNT29,ISNULL(CMONTH.AMOUNT29,0) + ISNULL(CDAY.AMOUNT29,0) AS CM_AMOUNT29, ISNULL(CMONTH.COUNT29,0) + ISNULL(CDAY.COUNT29,0) AS CM_COUNT29,
		ISNULL(CDAY.AMOUNT30,0) AS CD_AMOUNT30, ISNULL(CDAY.COUNT30,0) AS CD_COUNT30,ISNULL(CMONTH.AMOUNT30,0) + ISNULL(CDAY.AMOUNT30,0) AS CM_AMOUNT30, ISNULL(CMONTH.COUNT30,0) + ISNULL(CDAY.COUNT30,0) AS CM_COUNT30,
		ISNULL(CDAY.AMOUNT31,0) AS CD_AMOUNT31, ISNULL(CDAY.COUNT31,0) AS CD_COUNT31,ISNULL(CMONTH.AMOUNT31,0) + ISNULL(CDAY.AMOUNT31,0) AS CM_AMOUNT31, ISNULL(CMONTH.COUNT31,0) + ISNULL(CDAY.COUNT31,0) AS CM_COUNT31,
		ISNULL(CDAY.AMOUNT32,0) AS CD_AMOUNT32, ISNULL(CDAY.COUNT32,0) AS CD_COUNT32,ISNULL(CMONTH.AMOUNT32,0) + ISNULL(CDAY.AMOUNT32,0) AS CM_AMOUNT32, ISNULL(CMONTH.COUNT32,0) + ISNULL(CDAY.COUNT32,0) AS CM_COUNT32,
		ISNULL(CDAY.AMOUNT33,0) AS CD_AMOUNT33, ISNULL(CDAY.COUNT33,0) AS CD_COUNT33,ISNULL(CMONTH.AMOUNT33,0) + ISNULL(CDAY.AMOUNT33,0) AS CM_AMOUNT33, ISNULL(CMONTH.COUNT33,0) + ISNULL(CDAY.COUNT33,0) AS CM_COUNT33,
		ISNULL(CDAY.AMOUNT34,0) AS CD_AMOUNT34, ISNULL(CDAY.COUNT34,0) AS CD_COUNT34,ISNULL(CMONTH.AMOUNT34,0) + ISNULL(CDAY.AMOUNT34,0) AS CM_AMOUNT34, ISNULL(CMONTH.COUNT34,0) + ISNULL(CDAY.COUNT34,0) AS CM_COUNT34,
		ISNULL(CDAY.AMOUNT35,0) AS CD_AMOUNT35, ISNULL(CDAY.COUNT35,0) AS CD_COUNT35,ISNULL(CMONTH.AMOUNT35,0) + ISNULL(CDAY.AMOUNT35,0) AS CM_AMOUNT35, ISNULL(CMONTH.COUNT35,0) + ISNULL(CDAY.COUNT35,0) AS CM_COUNT35,
		ISNULL(CDAY.AMOUNT36,0) AS CD_AMOUNT36, ISNULL(CDAY.COUNT36,0) AS CD_COUNT36,ISNULL(CMONTH.AMOUNT36,0) + ISNULL(CDAY.AMOUNT36,0) AS CM_AMOUNT36, ISNULL(CMONTH.COUNT36,0) + ISNULL(CDAY.COUNT36,0) AS CM_COUNT36,
		ISNULL(CDAY.AMOUNT37,0) AS CD_AMOUNT37, ISNULL(CDAY.COUNT37,0) AS CD_COUNT37,ISNULL(CMONTH.AMOUNT37,0) + ISNULL(CDAY.AMOUNT37,0) AS CM_AMOUNT37, ISNULL(CMONTH.COUNT37,0) + ISNULL(CDAY.COUNT37,0) AS CM_COUNT37,
		ISNULL(CDAY.AMOUNT38,0) AS CD_AMOUNT38, ISNULL(CDAY.COUNT38,0) AS CD_COUNT38,ISNULL(CMONTH.AMOUNT38,0) + ISNULL(CDAY.AMOUNT38,0) AS CM_AMOUNT38, ISNULL(CMONTH.COUNT38,0) + ISNULL(CDAY.COUNT38,0) AS CM_COUNT38,
		ISNULL(CDAY.AMOUNT39,0) AS CD_AMOUNT39, ISNULL(CDAY.COUNT39,0) AS CD_COUNT39,ISNULL(CMONTH.AMOUNT39,0) + ISNULL(CDAY.AMOUNT39,0) AS CM_AMOUNT39, ISNULL(CMONTH.COUNT39,0) + ISNULL(CDAY.COUNT39,0) AS CM_COUNT39,
		ISNULL(CDAY.AMOUNT40,0) AS CD_AMOUNT40, ISNULL(CDAY.COUNT40,0) AS CD_COUNT40,ISNULL(CMONTH.AMOUNT40,0) + ISNULL(CDAY.AMOUNT40,0) AS CM_AMOUNT40, ISNULL(CMONTH.COUNT40,0) + ISNULL(CDAY.COUNT40,0) AS CM_COUNT40,
		ISNULL(CDAY.AMOUNT41,0) AS CD_AMOUNT41, ISNULL(CDAY.COUNT41,0) AS CD_COUNT41,ISNULL(CMONTH.AMOUNT41,0) + ISNULL(CDAY.AMOUNT41,0) AS CM_AMOUNT41, ISNULL(CMONTH.COUNT41,0) + ISNULL(CDAY.COUNT41,0) AS CM_COUNT41,
		ISNULL(CDAY.AMOUNT42,0) AS CD_AMOUNT42, ISNULL(CDAY.COUNT42,0) AS CD_COUNT42,ISNULL(CMONTH.AMOUNT42,0) + ISNULL(CDAY.AMOUNT42,0) AS CM_AMOUNT42, ISNULL(CMONTH.COUNT42,0) + ISNULL(CDAY.COUNT42,0) AS CM_COUNT42,
		ISNULL(CDAY.AMOUNT43,0) AS CD_AMOUNT43, ISNULL(CDAY.COUNT43,0) AS CD_COUNT43,ISNULL(CMONTH.AMOUNT43,0) + ISNULL(CDAY.AMOUNT43,0) AS CM_AMOUNT43, ISNULL(CMONTH.COUNT43,0) + ISNULL(CDAY.COUNT43,0) AS CM_COUNT43,
		ISNULL(CDAY.AMOUNT44,0) AS CD_AMOUNT44, ISNULL(CDAY.COUNT44,0) AS CD_COUNT44,ISNULL(CMONTH.AMOUNT44,0) + ISNULL(CDAY.AMOUNT44,0) AS CM_AMOUNT44, ISNULL(CMONTH.COUNT44,0) + ISNULL(CDAY.COUNT44,0) AS CM_COUNT44,
		ISNULL(CDAY.AMOUNT45,0) AS CD_AMOUNT45, ISNULL(CDAY.COUNT45,0) AS CD_COUNT45,ISNULL(CMONTH.AMOUNT45,0) + ISNULL(CDAY.AMOUNT45,0) AS CM_AMOUNT45, ISNULL(CMONTH.COUNT45,0) + ISNULL(CDAY.COUNT45,0) AS CM_COUNT45,
		ISNULL(CDAY.AMOUNT46,0) AS CD_AMOUNT46, ISNULL(CDAY.COUNT46,0) AS CD_COUNT46,ISNULL(CMONTH.AMOUNT46,0) + ISNULL(CDAY.AMOUNT46,0) AS CM_AMOUNT46, ISNULL(CMONTH.COUNT46,0) + ISNULL(CDAY.COUNT46,0) AS CM_COUNT46,
		ISNULL(CDAY.AMOUNT47,0) AS CD_AMOUNT47, ISNULL(CDAY.COUNT47,0) AS CD_COUNT47,ISNULL(CMONTH.AMOUNT47,0) + ISNULL(CDAY.AMOUNT47,0) AS CM_AMOUNT47, ISNULL(CMONTH.COUNT47,0) + ISNULL(CDAY.COUNT47,0) AS CM_COUNT47
FROM 
	(
	SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME FROM RESTAURANTS R WHERE R.SIFR = @RES
	) DUMMY
OUTER APPLY
(
SELECT
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 0 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT0,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 0 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT0,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 1 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT1,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 1 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT1,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 2 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT2,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 2 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT2,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 3 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT3,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 3 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT3,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 4 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT4,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 4 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT4,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 5 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT5,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 5 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT5,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 6 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT6,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 6 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT6,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 7 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT7,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 7 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT7,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 8 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT8,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 8 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT8,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 9 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT9,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 9 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT9,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 10 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT10,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 10 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT10,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 11 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT11,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 11 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT11,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 12 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT12,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 12 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT12,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 13 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT13,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 13 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT13,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 14 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT14,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 14 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT14,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 15 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT15,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 15 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT15,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 16 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT16,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 16 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT16,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 17 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT17,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 17 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT17,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 18 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT18,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 18 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT18,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 19 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT19,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 19 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT19,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 20 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT20,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 20 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT20,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 21 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT21,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 21 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT21,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 22 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT22,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 22 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT22,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 23 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT23,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 23 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT23,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 24 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT24,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 24 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT24,

		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 25 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT25,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 25 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT25,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 26 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT26,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 26 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT26,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 27 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT27,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 27 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT27,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 28 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT28,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 28 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT28,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 29 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT29,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 29 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT29,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 30 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT30,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 30 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT30,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 31 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT31,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 31 THEN PC.GLOBALIDENT  ELSE NULL END) AS COUNT31,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 32 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT32,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 32 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT32,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 33 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT33,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 33 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT33,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 34 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT34,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 34 THEN  PC.GLOBALIDENT ELSE NULL END) AS COUNT34,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 35 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT35,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 35 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT35,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 36 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT36,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 36 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT36,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 37 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT37,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 37 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT37,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 38 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT38,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 38 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT38,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 39 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT39,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 39 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT39,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 40 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT40,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 40 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT40,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 41 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT41,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 41 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT41,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 42 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT42,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 42 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT42,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 43 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT43,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 43 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT43,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 44 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT44,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 44 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT44,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 45 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT45,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 45 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT45,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 46 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT46,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 46 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT46,
		SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 47 THEN PC.BINDEDSUM ELSE 0 END) AS AMOUNT47,
		COUNT(DISTINCT CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,PC.CLOSEDATETIME),0),PC.CLOSEDATETIME) / 30 WHEN 47 THEN PC.GLOBALIDENT ELSE NULL END) AS COUNT47
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(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 0 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT0, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 0 THEN LHH.COUNT ELSE 0 END) AS COUNT0,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 1 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT1, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 1 THEN LHH.COUNT ELSE 0 END) AS COUNT1,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 2 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT2, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 2 THEN LHH.COUNT ELSE 0 END) AS COUNT2,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 3 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT3, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 3 THEN LHH.COUNT ELSE 0 END) AS COUNT3,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 4 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT4, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 4 THEN LHH.COUNT ELSE 0 END) AS COUNT4,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 5 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT5, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 5 THEN LHH.COUNT ELSE 0 END) AS COUNT5,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 6 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT6, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 6 THEN LHH.COUNT ELSE 0 END) AS COUNT6,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 7 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT7, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 7 THEN LHH.COUNT ELSE 0 END) AS COUNT7,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 8 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT8, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 8 THEN LHH.COUNT ELSE 0 END) AS COUNT8,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 9 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT9, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 9 THEN LHH.COUNT ELSE 0 END) AS COUNT9,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 10 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT10, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 10 THEN LHH.COUNT ELSE 0 END) AS COUNT10,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 11 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT11, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 11 THEN LHH.COUNT ELSE 0 END) AS COUNT11,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 12 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT12, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 12 THEN LHH.COUNT ELSE 0 END) AS COUNT12,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 13 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT13, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 13 THEN LHH.COUNT ELSE 0 END) AS COUNT13,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 14 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT14, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 14 THEN LHH.COUNT ELSE 0 END) AS COUNT14,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 15 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT15, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 15 THEN LHH.COUNT ELSE 0 END) AS COUNT15,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 16 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT16, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 16 THEN LHH.COUNT ELSE 0 END) AS COUNT16,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 17 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT17, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 17 THEN LHH.COUNT ELSE 0 END) AS COUNT17,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 18 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT18, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 18 THEN LHH.COUNT ELSE 0 END) AS COUNT18,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 19 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT19, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 19 THEN LHH.COUNT ELSE 0 END) AS COUNT19,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 20 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT20, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 20 THEN LHH.COUNT ELSE 0 END) AS COUNT20,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 21 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT21, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 21 THEN LHH.COUNT ELSE 0 END) AS COUNT21,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 22 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT22, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 22 THEN LHH.COUNT ELSE 0 END) AS COUNT22,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 23 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT23, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 23 THEN LHH.COUNT ELSE 0 END) AS COUNT23,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 24 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT24, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 24 THEN LHH.COUNT ELSE 0 END) AS COUNT24,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 25 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT25, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 25 THEN LHH.COUNT ELSE 0 END) AS COUNT25,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 26 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT26, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 26 THEN LHH.COUNT ELSE 0 END) AS COUNT26,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 27 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT27, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 27 THEN LHH.COUNT ELSE 0 END) AS COUNT27,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 28 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT28, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 28 THEN LHH.COUNT ELSE 0 END) AS COUNT28,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 29 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT29, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 29 THEN LHH.COUNT ELSE 0 END) AS COUNT29,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 30 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT30, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 30 THEN LHH.COUNT ELSE 0 END) AS COUNT30,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 31 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT31, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 31 THEN LHH.COUNT ELSE 0 END) AS COUNT31,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 32 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT32, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 32 THEN LHH.COUNT ELSE 0 END) AS COUNT32,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 33 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT33, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 33 THEN LHH.COUNT ELSE 0 END) AS COUNT33,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 34 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT34, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 34 THEN LHH.COUNT ELSE 0 END) AS COUNT34,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 35 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT35, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 35 THEN LHH.COUNT ELSE 0 END) AS COUNT35,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 36 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT36, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 36 THEN LHH.COUNT ELSE 0 END) AS COUNT36,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 37 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT37, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 37 THEN LHH.COUNT ELSE 0 END) AS COUNT37,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 38 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT38, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 38 THEN LHH.COUNT ELSE 0 END) AS COUNT38,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 39 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT39, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 39 THEN LHH.COUNT ELSE 0 END) AS COUNT39,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 40 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT40, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 40 THEN LHH.COUNT ELSE 0 END) AS COUNT40,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 41 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT41, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 41 THEN LHH.COUNT ELSE 0 END) AS COUNT41,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 42 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT42, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 42 THEN LHH.COUNT ELSE 0 END) AS COUNT42,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 43 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT43, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 43 THEN LHH.COUNT ELSE 0 END) AS COUNT43,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 44 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT44, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 44 THEN LHH.COUNT ELSE 0 END) AS COUNT44,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 45 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT45, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 45 THEN LHH.COUNT ELSE 0 END) AS COUNT45,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 46 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT46, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 46 THEN LHH.COUNT ELSE 0 END) AS COUNT46,
	SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 47 THEN LHH.AMOUNT ELSE 0 END) AS AMOUNT47, SUM(CASE DATEDIFF(MINUTE,DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0),LHH.DATE) / 30 WHEN 47 THEN LHH.COUNT ELSE 0 END) AS COUNT47
FROM LAST_HALFHOURLY LHH
WHERE LHH.RESTAURANT = @RES AND DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0) <> @CURR_DAY AND DATEADD(DAY,DATEDIFF(DAY,0,LHH.DATE),0) BETWEEN @CURR_MONTH1 AND @CURR_MONTH2
) CMONTH;

END;


GO

--OVERALL(1)

CREATE PROCEDURE [dbo].[RK7SQLPROCOVERALL1] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY DATETIME, @GETDATE DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME,
@FOODCAT1 INT, @FOODCAT2 INT, @FOODCAT3 INT, @FOODCAT4 INT, @FOODCAT5 INT,
 @FOODFIELD1 INT, @FOODFIELD2 INT, @FOODFIELD3 INT, @FOODFIELD4 INT, @FOODFIELD5 INT;

DECLARE @T1 TABLE (IAUTHOR INT,PARENT INT,AMOUNT MONEY,DCOUNT INT);
DECLARE @GS TABLE (MIDSERVER INT, VISIT INT, IDENTINVISIT 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, @COUNTFIELD = NULL, @AMOUNTFIELD = NULL,
@FOODCAT1 = @FOODCAT1 OUTPUT, @FOODCAT2 = @FOODCAT2 OUTPUT, @FOODCAT3 = @FOODCAT3 OUTPUT, @FOODCAT4 = @FOODCAT4 OUTPUT, @FOODCAT5 = @FOODCAT5 OUTPUT, 
@FOODFIELD1 = @FOODFIELD1 OUTPUT, @FOODFIELD2 = @FOODFIELD2 OUTPUT, @FOODFIELD3 = @FOODFIELD3 OUTPUT, @FOODFIELD4 = @FOODFIELD4 OUTPUT, @FOODFIELD5 = @FOODFIELD5 OUTPUT;

SET @CURR_DAY = DATEADD(DAY,DATEDIFF(DAY,0,@CURR_DAY),0);

INSERT INTO @GS
SELECT O.MIDSERVER, O.VISIT, O.IDENTINVISIT
FROM GLOBALSHIFTS GS
JOIN ORDERS O ON O.MIDSERVER = GS.MIDSERVER AND O.ICOMMONSHIFT = GS.SHIFTNUM
WHERE GS.IRESTAURANT = @RES AND GS.SHIFTDATE = @CURR_DAY;


WITH T AS (
	SELECT OS.IAUTHOR,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,SD.COMBODISHUNI,SD.PAYSUM,SD.QUANTITY
	FROM @GS GS
	JOIN PRINTCHECKS PC ON PC.MIDSERVER = GS.MIDSERVER AND PC.VISIT = GS.VISIT AND PC.ORDERIDENT = GS.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 ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI

	UNION ALL

	SELECT OS.IAUTHOR,SD.MIDSERVER,SD.VISIT,SD.UNI,SD.SIFR,0       ,SD.PAYSUM,SD.QUANTITY
	FROM T
	JOIN SESSIONDISHES SD ON SD.MIDSERVER = T.MIDSERVER AND SD.VISIT = T.VISIT AND T.COMBODISHUNI = SD.UNI
	JOIN ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI
)
INSERT INTO @T1
SELECT IAUTHOR,DG.PARENT,SUM(PAYSUM),SUM(QUANTITY)
FROM 
(SELECT T.SIFR,T.IAUTHOR,T.PAYSUM,T.QUANTITY FROM T GROUP BY T.MIDSERVER,T.VISIT,T.UNI,T.SIFR,T.IAUTHOR,T.PAYSUM,T.QUANTITY)
DUMMY
JOIN DISHGROUPS DG ON DG.CHILD = DUMMY.SIFR AND DG.PARENT IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
GROUP BY DUMMY.IAUTHOR,DG.PARENT;

INSERT INTO @T1
SELECT OS.IAUTHOR,@FOODCAT5, 0 AS AMOUNT, SUM(DM.MODICNT*SD.QUANTITY) AS DCOUNT
FROM @GS O
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 ORDERSESSIONS OS ON OS.MIDSERVER = SD.MIDSERVER AND OS.VISIT = SD.VISIT AND OS.UNI = SD.SESSIONUNI
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 OS.IAUTHOR;


SELECT	1 AS TYPE,CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CASHIER AS CASHIERID, DUMMY.CASHIERNAME AS CASHIERNAME, DUMMY.CGNAME AS CATEGORY,
		ISNULL(CDAY.AMOUNT,0)  AS CD_AMOUNT, ISNULL(CDAY.COUNT,0)  AS CD_COUNT, ISNULL(CDAY.DCOUNT,0)  AS CD_DCOUNT,
		DUMMY.CPLAN AS CD_CPLAN, ISNULL(CCDAY.AMOUNT,0)  AS CD_CAMOUNT, ISNULL(CCDAY.DCOUNT,0)  AS CD_CDCOUNT
FROM 
(
SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, E.SIFR AS CASHIER, E.NAME AS CASHIERNAME, CG.SIFR*256+CG.NUMINGROUP AS CGSIFR, CG.NAME AS CGNAME,
	ISNULL(CAST(GPD.PROPERTYVAL AS FLOAT),0) AS CPLAN
FROM RESTAURANTS R, EMPLOYEES E, CLASSIFICATORGROUPS CG
LEFT JOIN GENERATEDPROPDATAS GPD ON GPD.RKTYPEIDENT = 
(CASE CG.SIFR*256+CG.NUMINGROUP WHEN @FOODCAT1 THEN @FOODFIELD1 WHEN @FOODCAT2 THEN @FOODFIELD2 WHEN @FOODCAT3 THEN @FOODFIELD3 WHEN @FOODCAT4 THEN @FOODFIELD4 WHEN @FOODCAT5 THEN @FOODFIELD5 END) AND GPD.OBJECTIDENT = @RES
WHERE R.SIFR = @RES AND CG.SIFR*256+CG.NUMINGROUP IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
) DUMMY
LEFT JOIN
(
	SELECT IAUTHOR,PARENT,SUM(AMOUNT) AS AMOUNT,SUM(DCOUNT) AS DCOUNT FROM @T1
	GROUP BY IAUTHOR,PARENT
) CCDAY ON CCDAY.IAUTHOR = DUMMY.CASHIER AND CCDAY.PARENT = DUMMY.CGSIFR
LEFT JOIN
(
	SELECT ISNULL(OS1.IAUTHOR,OS2.IAUTHOR) AS IAUTHOR, SUM(PB.PAYSUM) AS AMOUNT, COUNT(DISTINCT PC.GLOBALIDENT) AS COUNT, SUM(PB.QUANTITY) AS DCOUNT
	FROM @GS O
	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 AND PB.STATE = 6
	LEFT JOIN SESSIONDISHES SD ON SD.MIDSERVER = PB.MIDSERVER AND SD.VISIT = PB.VISIT AND SD.UNI = PB.DISHUNI
	LEFT JOIN ORDERSESSIONS OS1 ON OS1.MIDSERVER = SD.MIDSERVER AND OS1.VISIT = SD.VISIT AND OS1.UNI = SD.SESSIONUNI
	LEFT JOIN DISHDISCOUNTS DD ON DD.MIDSERVER = PB.MIDSERVER AND DD.VISIT = PB.VISIT AND DD.UNI = PB.CHARGEUNI
	LEFT JOIN ORDERSESSIONS OS2 ON OS2.MIDSERVER = DD.MIDSERVER AND OS2.VISIT = DD.VISIT AND OS2.UNI = DD.SESSIONUNI
	GROUP BY ISNULL(OS1.IAUTHOR,OS2.IAUTHOR)
) CDAY ON CDAY.IAUTHOR = DUMMY.CASHIER
WHERE CDAY.AMOUNT IS NOT NULL
ORDER BY DUMMY.SHIFTDATE, DUMMY.RNAME, ISNULL(CDAY.AMOUNT,0) DESC, DUMMY.CASHIERNAME, DUMMY.CASHIER, DUMMY.CGNAME DESC;

END;

GO

--OVERALL(2)

CREATE PROCEDURE [dbo].[RK7SQLPROCOVERALL2] AS
BEGIN

SET DATEFORMAT YMD;
SET DATEFIRST 1;

DECLARE @RES INT, @CURR_DAY DATETIME, @GETDATE DATETIME, @CURR_MONTH1 DATETIME, @CURR_MONTH2 DATETIME,
@FOODCAT1 INT, @FOODCAT2 INT, @FOODCAT3 INT, @FOODCAT4 INT, @FOODCAT5 INT,
 @FOODFIELD1 INT, @FOODFIELD2 INT, @FOODFIELD3 INT, @FOODFIELD4 INT, @FOODFIELD5 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, @COUNTFIELD = NULL, @AMOUNTFIELD = NULL,
@FOODCAT1 = @FOODCAT1 OUTPUT, @FOODCAT2 = @FOODCAT2 OUTPUT, @FOODCAT3 = @FOODCAT3 OUTPUT, @FOODCAT4 = @FOODCAT4 OUTPUT, @FOODCAT5 = @FOODCAT5 OUTPUT, 
@FOODFIELD1 = @FOODFIELD1 OUTPUT, @FOODFIELD2 = @FOODFIELD2 OUTPUT, @FOODFIELD3 = @FOODFIELD3 OUTPUT, @FOODFIELD4 = @FOODFIELD4 OUTPUT, @FOODFIELD5 = @FOODFIELD5 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);

SELECT	2 AS TYPE,CONVERT(nvarchar,DUMMY.SHIFTDATE,127) AS SHIFTDATE, DUMMY.RNAME AS RESTAURANTNAME, DUMMY.CASHIER AS CASHIERID, DUMMY.CASHIERNAME AS CASHIERNAME, DUMMY.CGNAME AS CATEGORY,
		ISNULL(CMONTH.AMOUNT,0)  AS CD_AMOUNT, ISNULL(CMONTH.COUNT,0)  AS CD_COUNT, ISNULL(CMONTH.DCOUNT,0)  AS CD_DCOUNT,
		DUMMY.CPLAN AS CD_CPLAN, ISNULL(CCMONTH.AMOUNT,0)  AS CD_CAMOUNT, ISNULL(CCMONTH.DCOUNT,0)  AS CD_CDCOUNT
FROM 
(
SELECT @CURR_DAY AS SHIFTDATE, R.NAME AS RNAME, E.SIFR AS CASHIER, E.NAME AS CASHIERNAME, CG.SIFR*256+CG.NUMINGROUP AS CGSIFR, CG.NAME AS CGNAME,
	ISNULL(CAST(GPD.PROPERTYVAL AS FLOAT),0) AS CPLAN
FROM RESTAURANTS R, EMPLOYEES E, CLASSIFICATORGROUPS CG
LEFT JOIN GENERATEDPROPDATAS GPD ON GPD.RKTYPEIDENT = 
(CASE CG.SIFR*256+CG.NUMINGROUP WHEN @FOODCAT1 THEN @FOODFIELD1 WHEN @FOODCAT2 THEN @FOODFIELD2 WHEN @FOODCAT3 THEN @FOODFIELD3 WHEN @FOODCAT4 THEN @FOODFIELD4 WHEN @FOODCAT5 THEN @FOODFIELD5 END) AND GPD.OBJECTIDENT = @RES
WHERE R.SIFR = @RES AND CG.SIFR*256+CG.NUMINGROUP IN (@FOODCAT1,@FOODCAT2,@FOODCAT3,@FOODCAT4,@FOODCAT5)
) DUMMY
LEFT JOIN
(
	SELECT CASHIER,SUM(AMOUNT) AS AMOUNT, SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT FROM LAST_CASHIER_DAYS LCD
	WHERE RESTAURANT = @RES AND DATE BETWEEN @CURR_MONTH1 AND @CURR_DAY -1
	GROUP BY  CASHIER
) CMONTH ON CMONTH.CASHIER = DUMMY.CASHIER
LEFT JOIN
(
	SELECT CATEGORY,CASHIER,SUM(AMOUNT) AS AMOUNT, SUM(COUNT) AS COUNT, SUM(DCOUNT) AS DCOUNT FROM LAST_FOODCAT_CASHIER_DAYS LFCD
	WHERE RESTAURANT = @RES AND DATE BETWEEN @CURR_MONTH1 AND @CURR_DAY -1
	GROUP BY CATEGORY,CASHIER
) CCMONTH ON CCMONTH.CASHIER = DUMMY.CASHIER AND CASE CCMONTH.CATEGORY WHEN 0 THEN @FOODCAT5 ELSE CCMONTH.CATEGORY END = DUMMY.CGSIFR
WHERE CMONTH.AMOUNT IS NOT NULL
ORDER BY DUMMY.SHIFTDATE, DUMMY.RNAME, ISNULL(CMONTH.AMOUNT,0) DESC, DUMMY.CASHIERNAME, DUMMY.CASHIER, DUMMY.CGNAME DESC;

END;

GO

