Created
September 21, 2016 20:51
-
-
Save orlaqp/d239ee5cda1426ed6875bf8c8d908a78 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment