gistfile1.txt

DECLARE @databaseName varchar(50), @novaID nvarchar(50), @runAt int, @offSet int, @sqlString varchar (max), @isShutdown int, @dateStart datetime, @dateEnd datetime;
	DECLARE @now as DATETIME, @day as INT, @month as INT, @year as INT;
	DECLARE @startDate AS DATE, @endDate AS DATE, @limitDate AS DATE;

	DECLARE @FromDate AS DATETIME;
	DECLARE @ToDate AS DATETIME;
	DECLARE @CurrentDate AS DATETIME;
	
	-- Define a cursor with all companies in POS_Main..Users table
	DECLARE CompaniesCursor CURSOR FOR 
		SELECT DatabaseName, NovaID, RunAt, Offset
		FROM POS_Main..SalesInfoRunAtHourView 
		WHERE 
			DatabaseName NOT LIKE '%Template%'
			AND DatabaseName NOT LIKE '%Demo%' 
			AND DatabaseName NOT LIKE '%Test%'  
			AND DatabaseName LIKE 'POS_%'
			AND RunAt = DatePart(hh, GetDate())
		ORDER BY DatabaseName;

	-- Opoen the companies cursor
	OPEN CompaniesCursor
	FETCH NEXT FROM CompaniesCursor INTO @databaseName, @novaID, @runAt, @offset
	WHILE @@FETCH_STATUS = 0
	BEGIN

		-- Check if the database is shutdown
		use master
		select @isShutdown = databaseproperty(@databaseName,'isShutdown')

		-- exclude unexistent and offline companies
		IF (EXISTS (SELECT *
		FROM 
			sys.databases db 
			INNER JOIN sys.master_files mf ON db.database_id = mf.database_id -- where db.state <> 6			
		WHERE (1 <> @isShutdown AND db.state <> 6 AND ('[' + db.name + ']' = @databaseName OR db.name = @databaseName))))

		BEGIN

			-- save proccessed daabase
			EXEC('INSERT INTO POS_Main..SalesInfoProccesed (DatabaseName, Status) VALUES (''' + @databaseName + ''', ''started'')');

			IF @runAt + @offset > 24 
			BEGIN
				SET @FromDate = CAST(GETDATE() AS DATE)
				SET @ToDate = CAST(DATEADD(day, 1, GETDATE()) AS DATE)
			END
			ELSE
			BEGIN
				SET @FromDate = CAST(DATEADD(day, -1, GETDATE()) AS DATE)
				SET @ToDate = CAST(GETDATE() AS DATE)
			END


			
			SET @CurrentDate = @FromDate

			WHILE (@CurrentDate < @ToDate)
			BEGIN

				PRINT('USE ' + @databaseName + ' -- ' + CONVERT(VARCHAR(10),@CurrentDate,10));

				SET @StartDate = @CurrentDate
				SET @endDate = DATEADD(day, 1, @startDate)
			
				set @sqlString = 'USE ' + @databaseName + ';
				
				DECLARE @SalesTemp TABLE(
					[DatabaseName] VARCHAR(50),
					[NovaID] VARCHAR(20),
					[Start] VARCHAR(10),
					[End] VARCHAR(10),
					[LocationId] INT, 
					[LocationName] VARCHAR(50), 
					[MerchantNumber] VARCHAR(50), 
					[IsDisplayProductPriceIncTax] VARCHAR(80), 
					[IpAddress] VARCHAR(50), 
					[PaymentType] VARCHAR(50), 
					[Amount] DECIMAL(18,2)
				)
				;WITH Sales_CTE (
					[DatabaseName],
					[NovaID],
					[Start],
					[End],
					[LocationId], 
					[LocationName], 
					[MerchantNumber], 
					[IsDisplayProductPriceIncTax], 
					[IpAddress], 
					[PaymentType], 
					[Amount]
				) AS
				(SELECT 
						''' + @databaseName + ''' as DatabaseName,
						''' + @novaID + ''' as NovaID,
						''' + cast(@startDate as varchar(10)) + ''' as WeekStart,
						''' + cast(@endDate as varchar(10)) + ''' as WeekEnd,
						l.LocationID,
						l.LocationName,
						l.MerchantNumber,
						l.IsDisplayProductPriceIncTax,
						''0.0.0.0'',
						CASE
							WHEN CHARINDEX(''.net'', ip.PaymentMethod) <> 0 THEN ''Authorized .Net''
							WHEN CHARINDEX(''Mercury'', ip.PaymentMethod) <> 0 THEN ''Mercury''
							WHEN CHARINDEX(''Cash'', ip.PaymentMethod) <> 0 THEN ''Cash''
							WHEN CHARINDEX(''Check'', ip.PaymentMethod) <> 0 THEN ''Check''
							ELSE ''External''
						END AS PaymentType,
						ip.Amount
					FROM
						' + @databaseName + '..Invoice_Payments ip
						INNER JOIN ' + @databaseName + '..Invoices i ON ip.InvoiceID = i.InvoiceID
						INNER JOIN ' + @databaseName + '..Locations l ON i.LocationID = l.LocationID						
					WHERE
						ip.Timestamp BETWEEN ''' + cast(@startDate as varchar(10)) + ''' AND ''' + cast(@endDate as varchar(10)) + '''					
				)
				INSERT INTO @SalesTemp
				SELECT 
					[DatabaseName],
					[NovaID],
					[Start],
					[End],
					[LocationId], 
					[LocationName], 
					[MerchantNumber], 
					[IsDisplayProductPriceIncTax], 
					[IpAddress], 
					[PaymentType], 
					[Amount] 
				FROM Sales_CTE;
					
				INSERT INTO POS_Main..SalesInfo
				SELECT 
					Totals.[DatabaseName],
					Totals.[NovaID],
					Totals.[Start],
					Totals.[End],
					Totals.[LocationId],
					Totals.[LocationName],
					Totals.[MerchantNumber],
					Totals.[IsDisplayProductPriceIncTax],
					Totals.[IpAddress],
					Totals.[Authorized .Net],
					Counts.[Authorized .Net] AS [AuthorizedNet_Trans],
					Totals.[Mercury],
					Counts.[Mercury] AS [Mercury_Trans],
					Totals.[Cash],
					Counts.[Cash] AS [Cash_Trans],
					Totals.[Check],
					Counts.[Check] AS [Check_Trans],
					Totals.[External],
					Counts.[External] AS [External_Trans]
				FROM
				 (SELECT * FROM @SalesTemp PIVOT ( SUM(Amount) for PaymentType in ([Authorized .Net], [Mercury], [Cash], [Check], [External]) ) piv) As Totals
				 INNER JOIN
				 (SELECT * FROM @SalesTemp PIVOT ( COUNT(Amount) for PaymentType in ([Authorized .Net], [Mercury], [Cash], [Check], [External]) ) piv) As Counts
				 ON Totals.LocationId = Counts.LocationId			
				
				'

				-- EXECUTE('USE ' + @databaseName);
				EXEC(@sqlString);

				-- increment date
				SET @CurrentDate = DateAdd(day, 1, @CurrentDate);

				--PRINT('Next Date: ' + CONVERT(VARCHAR(10),@CurrentDate,10));

			END

			-- save proccessed daabase
			EXEC('INSERT INTO POS_Main..SalesInfoProccesed (DatabaseName, Status) VALUES (''' + @databaseName + ''', ''done'')');

		END

		FETCH NEXT FROM CompaniesCursor INTO @databaseName, @novaID, @runAt, @offset

	END
	
	-- close cursor and free resources
	CLOSE CompaniesCursor;
	DEALLOCATE CompaniesCursor;