================= Vars begin
{
	"MyAppSpace":"Miap",
	"MyAppClass":"MyModel",
	"Translates":
	{
		"Generated" :
		{
			"int" : "@##RandomInteger(10)##@",
			"bigint" : "@##RandomInteger(100)##@",
			"nvarchar" : "CONCAT('@{Name}@_', CAST(@##RandomInteger(100)##@ AS INT))",
			"nchar" : " CAST(@##RandomInteger(100)##@ AS INT)",
			"decimal" : "@##RandomInteger(100)##@",
			"numeric" : "@##RandomInteger(100)/10##@",
			"reference" : "CONCAT('City_', CAST((SELECT TOP 1 Id as reference FROM @{Referencing_Table}@ WHERE @{Referencing_Table}@.@{Referencing_Column}@ IS NOT NULL ORDER BY NewID()) AS INT))",
			"datetime0" : "DATEADD(MONTH, CAST(@##RandomInteger(11)##@ AS INT), DATEADD(DAY, CAST(@##RandomInteger(29)##@ AS INT), GETDATE()))",
			"datetime" : "DATEADD(DAY, CAST(@##RandomInteger(29)##@ AS INT), GETDATE())",
			"date" : "GETDATE()",
			"bit" : "0"
		}
	},	
	"Tables" : 
	[
		{
			
			"Name" : "Test_Phonebook",
			"Schema" : "dbo",
			"PK" : [ "ProductID", "CustomerID" ],
			"Columns":
			[
				{ "Name":"Id", "Type":"int", "Skeep": 1, "IsKey":1},
				{ "Name":"Name", "Type":"nvarchar", "Precision":"60"},
				{ "Name":"Age", "Type":"int", "Nullable" : true},
				{ "Name":"Phone", "Type":"nvarchar", "Precision":"60", "IsKey":false},
				{ "Name":"Description", "Type":"nvarchar", "Precision":"300", "Nullable" : true, "IsKey" : 2},
				{ "Name":"Note", "Type":"nvarchar", "Precision":"300", "Nullable" : true},
				{ "Name":"City", "Type":"nvarchar", "Precision":"60"},
				{ "Name":"Street", "Type":"nvarchar", "Precision":"60"},
				{ "Name":"URL", "Type":"nvarchar", "Precision":"50", "Nullable" : true},
				{ "Name":"Prints", "Type":"int", "Nullable" : true},
				{ "Name":"ActivFrom", "Type":"datetime", "Nullable" : true},
				{ "Name":"ActivTo", "Type":"datetime", "Nullable" : true},
				{ "Name":"IsDeliveryAddress", "Type":"bit"},
				{ "Name":"Suspended", "Type":"bit"}
			]
		},
		{			
			"Name" : "Test_Customer",
			"Schema" : "dbo",
			"PK" : [ "ProductID", "CustomerID" ],
			"Columns":
			[
				{ "Name":"CustomerId", "Type":"int", "IsKey":true},
				{ "Name":"Phone", "Type":"nvarchar", "Precision":"60", "IsKey":false},
				{ "Name":"Name", "Type":"nvarchar", "Precision":"60", "IsUnique": true},
				{ "Name":"URL", "Type":"nvarchar", "Precision":"50", "Nullable" : true},
				{ "Name":"Age", "Type":"int", "Nullable" : true},
				{ "Name":"Suspended", "Type":"bit"}
			]
		},
		{			
			"Name" : "Test_Address",
			"Schema" : "dbo",
			"PK" : [ "ProductID", "CustomerID" ],
			"Columns":
			[
				{ "Name":"AddressId", "Type":"int", "IsKey":true},
				{ "Name":"CustomerIdRef", "Type":"int", "Referencing_Table": "Test_Customer", "Referencing_Column": "CustomerId"},				
				{ "Name":"Description", "Type":"nvarchar", "Precision":"300", "Nullable" : true, "IsKey" : 2},
				{ "Name":"City", "Type":"nvarchar", "Precision":"60", "IsUnique": true },
				{ "Name":"Street", "Type":"nvarchar", "Precision":"60", "IsUnique": true },
				{ "Name":"Prints", "Type":"int", "Nullable" : true},
				{ "Name":"ActivFrom", "Type":"datetime", "Nullable" : true},
				{ "Name":"ActivTo", "Type":"datetime", "Nullable" : true},
				{ "Name":"Note", "Type":"nvarchar", "Precision":"300", "Nullable" : true},
				{ "Name":"IsDeliveryAddress", "Type":"bit"}
			]
		}
	]
}
================= Vars end
		
!=========== Tables ===========
--------------------------------- DELETE @{Name}@ TYPE ---------------------------------

IF TYPE_ID(N'@{Schema}@.@{Name}@') IS NOT NULL DROP TYPE [@{Schema}@].[@{Name}@];

--------------------------------- DELETE @{Name}@ TABLES ---------------------------------

IF OBJECT_ID('@{Schema}@.@{Name}@', 'U') IS NOT NULL DROP TABLE [@{Schema}@].[@{Name}@];
GO

===========!

!=========== Tables ===========
--------------------------------- CREATE @{Name}@ TABLE ---------------------------------

CREATE TABLE [@{Schema}@].[@{Name}@]
	(
		!=========== Columns ===========
		@~Printed.Columns > 0~@~,~@~~@ [@{Name}@] [@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@~~@@~(typeof(@{IsKey}@) !== 'undefined' && @{IsKey}@ == true )~@~ IDENTITY(1,1) PRIMARY KEY~@~~@@~(typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ != true )~@~ NULL~@~~@
		===========!	
	) ON [PRIMARY];
GO

--------------------------------- CREATE @{Name}@ TYPE ---------------------------------

CREATE TYPE [@{Schema}@].[@{Name}@] AS TABLE
	(
		!=========== Columns ===========
		@~Printed.Columns > 0~@~,~@~~@ [@{Name}@] [@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@~~@
		===========!
	);
GO

===========!

!=========== Tables [{"item":"Test_Customer", "repetitions": 3},{"item":"Test_Address", "repetitions": 10},{"item":"Test_Phonebook", "repetitions": 0}] ===========

--------------------------------- FILL TABLE @{Name}@ ---------------------------------
	-- Duplicate keys will be ignored
	--SET IDENTITY_INSERT @{Name}@ ON

INSERT INTO [@{Schema}@].[@{Name}@]
	(
		!=========== Columns ===========
		@~ (typeof(@{IsKey}@)==='undefined'||@{IsKey}@==false) ~@~@~Printed.Columns > 0~@~,~@~~@[@{Name}@]~@~~@
		===========!
	)
	VALUES
	(
		!=========== Columns ===========
		@~ (typeof(@{IsKey}@)==='undefined'||@{IsKey}@==false) ~@~@~Printed.Columns > 0~@~,~@~~@ CONVERT([@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@~~@, @``Generated``@``@~(typeof(@{Referencing_Table}@) !== 'undefined')~@~reference~@~@{Type}@~@``@@~(typeof( @{Style}@) !== 'undefined')~@~, @{Style}@~@~~@)~@~~@	
		===========!
	);
	
	--SET IDENTITY_INSERT @{Name}@ OFF

===========!

!=========== Tables ===========
--------------------------------- SHOW TABLES CONTENT ---------------------------------

SELECT TOP 10 * FROM [@{Schema}@].[@{Name}@];

===========!
GO

--------------------------------- Aggregate Data ---------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Test_Get_AggregatedData')
DROP PROCEDURE Test_Get_AggregatedData
GO

-- From SQL Server 2016 DROP PROCEDURE IF EXISTS

CREATE PROCEDURE [dbo].[Test_Get_AggregatedData]
	@startingDatetime			datetime = NULL
,	@endingDatetime				datetime = NULL
AS
DECLARE
	@defaultStartingDatetime datetime, @defaultEndingDatetime datetime
	SET @defaultStartingDatetime = dateadd(hour, datediff(hour, 0, GETDATE())-2, 0)
	SET @defaultEndingDatetime	 = dateadd(hour, datediff(hour, 0, GETDATE())-1, 0)

-- If no startdate is provided, the startdate is set to last hour -1
	IF @startingDatetime IS NULL
		SET @startingDatetime = @defaultStartingDatetime

-- If no enddate is provided, the enddate is set to last hour
	IF @endingDatetime IS NULL
		SET @endingDatetime = @defaultEndingDatetime;
	
	-- Only permit queryes for the last complete hours, not for the ongoing hour
	--  IF @endingDatetime > @defaultEndingDatetime
	--	SET @endingDatetime = @defaultEndingDatetime;

SELECT
				!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
					!=========== Columns ===========
					@~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ [@{Name}@]~@~~@
					===========!
				===========!
				FROM 
					Test_Address a JOIN Test_customer b ON a.CustomerIdRef = b.CustomerId
				WHERE
					a.[ActivFrom] >= @startingDatetime AND a.[ActivFrom] <= @endingDatetime

RETURN   @@ERROR
GO

--------------------------------- Create Support Objects - REPORTS Table ---------------------------------

IF OBJECT_ID('dbo.Test_Reports', 'U') IS NOT NULL DROP TABLE [dbo].[Test_Reports];
GO

CREATE TABLE [dbo].[Test_Reports](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Transfer_Type] [nvarchar](20) NOT NULL,
	[Starting_Timestamp] [datetime] NULL,
	[Ending_Timestamp] [datetime] NULL,
	[BI_ActivityCasino_RecordsFound] [int] NULL,
	[BI_ActivityCasino_Inserts] [int] NULL,
	[BI_ActivityCasino_InsertErrors] [int] NULL,
	[BI_ActivityCasino_Updates] [int] NULL,
	[BI_Transactions_Updates] [int] NULL,
	[BI_ActivityCasino_Miliseconds] [bigint] NULL,
	[Timestamp] [datetime] NULL
) ON [PRIMARY]
GO

--------------------------------- Create Support Objects - Aggregated TYPE ---------------------------------

IF TYPE_ID(N'dbo.Test_AggregatedType') IS NOT NULL DROP TYPE [dbo].[Test_AggregatedType];
GO

CREATE TYPE [dbo].[Test_AggregatedType] AS TABLE
	(
		!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
			!=========== Columns ===========
			 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ [@{Name}@] [@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@~~@~@~~@
			===========!
		===========!
	);
GO

--------------------------------- Create Support Objects - Aggregated TABLE ---------------------------------

IF OBJECT_ID('dbo.Test_AggregatedTable', 'U') IS NOT NULL DROP TABLE [dbo].[Test_AggregatedTable];
GO

CREATE TABLE [dbo].[Test_AggregatedTable]
	(
		Id int
	!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
		!=========== Columns ===========
		@~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~, [@{Name}@] [@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@~~@@~(typeof(@{IsKey}@) !== 'undefined' && @{IsKey}@ == true )~@~ IDENTITY(1,1) PRIMARY KEY~@~~@@~(typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ != true )~@~ NULL~@~~@~@~~@
		===========!
	===========!	
	) ON [PRIMARY];
GO

--------------------------------- Create Support Objects - UPSERT Procedure ---------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Test_Upsert')
DROP PROCEDURE Test_Upsert
GO

-- From SQL Server 2016 DROP PROCEDURE IF EXISTS

CREATE PROCEDURE [dbo].[Test_Upsert]
	@startingDatetime datetime = NULL,
	@endingDatetime datetime = NULL
AS 
DECLARE 
	!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
		!=========== Columns ===========
		 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ @@{Name}@ @{Type}@@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@~~@~@~~@
		===========!
	===========!
	,
	-- Using
	@my_TMP dbo.Test_AggregatedType,
	@defaultStartingDatetime datetime,
	@defaultEndingDatetime datetime,
	-- Returning
	@RecordsFound int = 0,
	@Inserts int = 0,
	@Updates int = 0

-- prepare default time interval limits
SET @defaultStartingDatetime = dateadd(hour, datediff(hour, 0, GETDATE())-2, 0)
SET @defaultEndingDatetime = dateadd(hour, datediff(hour, 0, GETDATE())-1, 0)

IF @startingDatetime IS NULL SET @startingDatetime = @defaultStartingDatetime;
IF @endingDatetime IS NULL SET @endingDatetime = @defaultEndingDatetime;
-- IF @endingDatetime > @defaultEndingDatetime SET @endingDatetime = @defaultEndingDatetime; -- Dates in future are not permitted !!!

DECLARE @error int;

INSERT INTO @my_TMP
	EXEC @error = [dbo].[Test_Get_AggregatedData]
	@startingDatetime,
	@endingDatetime

DECLARE db_cursor CURSOR local
FOR SELECT * FROM @my_TMP

OPEN db_cursor

FETCH NEXT FROM db_cursor into 
	!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
		!=========== Columns ===========
		 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~ @~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ @@{Name}@ ~@~~@
		===========!
	===========!

WHILE @@FETCH_STATUS = 0 
BEGIN
	SET @RecordsFound += 1;
		IF EXISTS 
		(
			SELECT 
				1 
			FROM 
				dbo.[Test_AggregatedTable] 
			WHERE 
				1=1
				!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
					!=========== Columns ===========
					 @~((typeof(@{IsUnique}@) !== 'undefined' && @{IsUnique}@ == true))~@~ AND [@{Name}@] = @@{Name}@ ~@~ ~@
					===========!
				===========! 
		)
		BEGIN
			UPDATE 
			dbo.[Test_AggregatedTable] 
			SET 
				!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
					!=========== Columns ===========
					 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ [@{Name}@] = @@{Name}@ ~@~~@
					===========!
				===========! 
			WHERE 
				1=1
				!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
					!=========== Columns ===========
					 @~((typeof(@{IsUnique}@) !== 'undefined' && @{IsUnique}@ == true))~@~ AND [@{Name}@] = @@{Name}@ ~@~ ~@
					===========!
				===========! 
			;
			SET @Updates += 1;
		END 
		ELSE BEGIN
			INSERT INTO 
			dbo.[Test_AggregatedTable] 
			(
				!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
					!=========== Columns ===========
					 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ [@{Name}@] ~@~~@
					===========!
				===========! 
			)
			VALUES 
			(
				!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
					!=========== Columns ===========
					 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ @@{Name}@ ~@~~@
					===========!
				===========! 
			);
			SET @Inserts += 1;
		END
	FETCH NEXT FROM db_cursor into
		!=========== Tables [{"item":"Test_Phonebook", "repetitions": 0}] ===========
			!=========== Columns ===========
			 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false) && (typeof(@{Referencing_Table}@) === 'undefined'))~@~@~Printed.Tables > 0 || Printed.Columns > 0~@~,~@~~@ @@{Name}@ ~@~~@
			===========!
		===========! 
	END

CLOSE db_cursor 
DEALLOCATE db_cursor
SELECT 'RecordsFound' = @RecordsFound, 'Inserts' = @Inserts, 'Updates' = @Updates
GO

EXEC [dbo].[Test_Upsert] '2016-01-01', '2018-01-01'
GO

-- EXECÂ Â [dbo].[Test_Get_AggregatedData] '2016-01-01', '2018-01-01'
GO