================= Vars begin
{
	"MyAppSpace":"Miap",
	"MyAppClass":"MyModel",
	"Translates":
	{
		"Generated" :
		{
			"int" : "@## Math.floor(Math.random() * (100 - 0 + 1)) + 0 ##@",
			"bigint" : "RAND(100)*10000",
			"nvarchar" : "CONCAT('@{Name}@_', CAST(RAND(100)*100 AS INT))",
			"nchar" : " CAST(RAND(100)*100 AS INT)",
			"decimal" : "RAND(100)*100",
			"numeric" : "RAND(100)*10",
			"datetime" : "DATEADD(MONTH, CAST(RAND(11)*100 AS INT), DATEADD(DAY, CAST(RAND(29)*100 AS INT), GETDATE()))",
			"date" : "GETDATE()",
			"bit" : "0"
		}
	},	
	"Tables" : 
	[
		{			
			"Name" : "Test_Customer",
			"Schema" : "dbo",
			"PK" : [ "ProductID", "CustomerID" ],
			"Columns":
			[
				{ "Name":"Id", "Type":"int", "IsKey":true},
				{ "Name":"Address", "Type":"int"},
				{ "Name":"Name", "Type":"nvarchar", "Precision":"60"},
				{ "Name":"Note", "Type":"nvarchar", "Precision":"300", "Nullable" : true},
				{ "Name":"cURL", "Type":"nvarchar", "Precision":"50", "Nullable" : true},
				{ "Name":"Age", "Type":"int", "Nullable" : true},
				{ "Name":"cActivFrom", "Type":"datetime", "Nullable" : true},
				{ "Name":"cActivTo", "Type":"datetime", "Nullable" : true},
				{ "Name":"Suspended", "Type":"bit"}
			]
		},
		{			
			"Name" : "Test_Address",
			"Schema" : "dbo",
			"PK" : [ "ProductID", "CustomerID" ],
			"Columns":
			[
				{ "Name":"Id", "Type":"int", "IsKey":true},
				{ "Name":"City", "Type":"nvarchar", "Precision":"60"},
				{ "Name":"Full text", "Type":"nvarchar", "Precision":"300", "Nullable" : true},
				{ "Name":"aURL", "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"}
			]
		}
	]
}
================= Vars end
================= Fragment    Tables begin

--------------------------------- DELETE @{Name}@ TYPE ---------------------------------

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

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

IF OBJECT_ID('@{Schema}@.@{Name}@', 'U') IS NOT NULL DROP TABLE [@{Schema}@].[@{Name}@]; -- For SqlServer >= 2016 DROP TABLE IF EXISTS ...;
GO

--------------------------------- CREATE @{Name}@ TABLE ---------------------------------

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

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

CREATE TYPE [@{Schema}@].[@{Name}@] AS TABLE
	(
		================= Fragment Columns begin
		@^,^@ [@{Name}@] [@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@
		================= Fragment Columns end
	);
GO

================= Fragment Tables end

================= Fragment    Tables begin 3

--------------------------------- FILL TABLE @{Name}@ ---------------------------------
	
	SET IDENTITY_INSERT @{Name}@ ON

INSERT INTO [@{Schema}@].[@{Name}@]
	(
		================= Fragment Columns begin
		@^,^@ [@{Name}@]
		================= Fragment Columns end
	)
	VALUES
	(
		================= Fragment Columns begin
			-- @^,^@ @~(typeof(@{IsKey}@) !== 'undefined' && @{IsKey}@ == true)~@~@## Nos[Level] ##@~@
			@^,^@ CONVERT([@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@, @``Generated``@``@{Type}@``@@~(typeof( @{Style}@) !== 'undefined')~@~, @{Style}@~@)
		================= Fragment Columns end
	);
	
	SET IDENTITY_INSERT @{Name}@ OFF

================= Fragment Tables end

DROP PROCEDURE IF EXISTS [dbo].[Test_Get_AggregatedData] -- From SQL Server 2016 DROP PROCEDURE IF EXISTS
GO

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 a.Id
				================= Fragment    Tables begin
					================= Fragment Columns begin
					 @~((typeof(@{IsKey}@) === 'undefined' || @{IsKey}@ == false))~@~, [@{Name}@]~@
					================= Fragment Columns end
				================= Fragment Tables end
				FROM 
					Test_Address a JOIN Test_customer b ON a.id = b.id
				WHERE
					a.[ActivFrom] >= @startingDatetime AND a.[ActivFrom] <= @endingDatetime

RETURN   @@ERROR

================= Fragment    Tables begin

--------------------------------- SHOW TABLES CONTENT ---------------------------------

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

================= Fragment Tables end

EXEC [dbo].[Test_Get_AggregatedData] @startingDatetime = '1790-01-01', @endingDatetime = '9417-01-01'

------------------------ STOP HERE

--------------------------------- DELETE TABLES AND TYPES ---------------------------------

================= Fragment    Tables begin

IF TYPE_ID(N'@{Schema}@.@{Name}@') IS NOT NULL DROP TYPE [@{Schema}@].[@{Name}@];
	IF OBJECT_ID('@{Schema}@.@{Name}@', 'U') IS NOT NULL DROP TABLE [@{Schema}@].[@{Name}@]; --- For SqlServer >= 2016 DROP TABLE IF EXISTS ...;
GO
================= Fragment    Tables end

--------------------------------- CREATE TABLES ---------------------------------

================= Fragment    Tables begin

--------------------------------- CREATE @{Name}@ TABLE ---------------------------------

================= Fragment Tables end

--------------------------------- CREATE TYPES ---------------------------------

================= Fragment    Tables begin

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

CREATE TYPE [@{Schema}@].[@{Name}@]Type AS TABLE
	(
		================= Fragment Columns begin
		@^,^@ [@{Name}@] [@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@
		================= Fragment Columns end
	);
GO

================= Fragment Tables end

--------------------------------- FILL TABLES ---------------------------------

================= Fragment    Tables begin 0

--------------------------------- FILL TABLE @{Name}@ ---------------------------------
	
	INSERT INTO [@{Schema}@].[@{Name}@]
	(
		================= Fragment Columns begin
		@^,^@ [@{Name}@]
		================= Fragment Columns end
	)
	VALUES
	(
		================= Fragment Columns begin
		@^,^@ CONVERT([@{Type}@]@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@, @``Generated``@``@{Type}@``@@~(typeof( @{Style}@) !== 'undefined')~@~, @{Style}@~@)
		================= Fragment Columns end
	);
GO

================= Fragment Tables end

--------------------------------- SHOW TABLES CONTENT ---------------------------------

================= Fragment    Tables begin

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

================= Fragment Tables end
================= Fragment Tables begin 0

--------------------------------- CREATE UPSERT RESULTS TYPE ---------------------------------

CREATE TYPE [@{Schema}@].[MY_UPSERT_RESULTS_TYPE] AS TABLE
	(
		[Inserts] [int],
		[InsertErrors] [int],
		[Updates] [int],
		[UpdateErrors] [int]
	);

================= Fragment Tables end
================= Fragment Tables begin 0

--------------------------------- UPSERT @{Name}@ ---------------------------------

IF EXISTS (SELECT * FROM sys.objects o WHERE type = 'P' AND name = N'uSP_INS_UPD_@{Name}@' AND SCHEMA_NAME(o.schema_id) = '@{Schema}@') 
	BEGIN
	DROP PROCEDURE [@{Schema}@].uSP_INS_UPD_@{Name}@
	END
GO

CREATE PROCEDURE [@{Schema}@].[uSP_INS_UPD_@{Name}@]
	@startingDatetime			datetime = NULL,
	@endingDatetime				datetime = NULL
AS
	DECLARE	
		
		================= Fragment Columns begin
		@@{Name}@ @{Type}@@~(typeof(@{Precision}@) !== 'undefined')~@~(@{Precision}@)~@,
		================= Fragment Columns end
		-- Using
		@@{Name}@_TMP @{Schema}@.@{Name}@,
		@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;

DECLARE	@error int;
		
	INSERT INTO @@{Name}@_TMP
	EXEC	@error = [@{Schema}@].[Get_@{Name}@]
			@startingDatetime,
			@endingDatetime

DECLARE db_cursor CURSOR local
	FOR SELECT * FROM  @@{Name}@_TMP
	
	OPEN db_cursor

FETCH NEXT FROM db_cursor into 
		================= Fragment Columns begin
		@~!(typeof(@{Skeep}@) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  @@{Name}@~@ 
		================= Fragment Columns end

WHILE @@FETCH_STATUS = 0   
	BEGIN
		SET @RecordsFound += 1;
		IF EXISTS 
		(
			SELECT 
				1 
			FROM 
				[@{Schema}@].[@{Name}@] 
			WHERE 
				================= Fragment Columns begin
				@~(typeof(@{IsKey}@) !== 'undefined' && @{IsKey}@ == true)~@~@^AND^@ [@{Name}@] = @@{Name}@~@ 
				================= Fragment Columns end
		)
		BEGIN
			UPDATE 
				[@{Schema}@].[@{Name}@] 
			SET 
				================= Fragment Columns begin
				@~!(typeof(@{Skeep}@) !== 'undefined' && @{Skeep}@ == 1) && !(typeof(@{IsKey}@) !== 'undefined' && @{IsKey}@ == true)~@~@^,^@  [@{Name}@] = @@{Name}@~@ 
				================= Fragment Columns end 
			WHERE 
				================= Fragment Columns begin
				@~(typeof(@{IsKey}@) !== 'undefined' && @{IsKey}@ == true)~@~@^AND^@ [@{Name}@] = @@{Name}@~@
				================= Fragment Columns end
			;
			SET @Updates += 1;
		END 
		ELSE BEGIN
			INSERT INTO 
				[@{Schema}@].[@{Name}@] 
			(
				================= Fragment Columns begin
				@~!(typeof(@{Skeep}@) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  [@{Name}@]~@ 
				================= Fragment Columns end 
			)
			VALUES 
			(
				================= Fragment Columns begin
				@~!(typeof(@{Skeep}@) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  @@[Name}@~@ 
				================= Fragment Columns end 
			);
			SET @Inserts += 1;
		END
		FETCH NEXT FROM db_cursor into
			================= Fragment Columns begin
			@~!(typeof(@{Skeep}@) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  @@{Name}@~@ 
			================= Fragment Columns end
	END
			
	CLOSE db_cursor   
	DEALLOCATE db_cursor
	-- SET FMTONLY OFF
	SELECT 'RecordsFound' = @RecordsFound, 'Inserts' = @Inserts, 'Updates' = @Updates

================= Fragment Tables end

--------------------------------- DELETE TYPES ---------------------------------
-- For SqlServer >= 2016 DROP TABLE IF EXISTS ...;

================= Fragment    Tables begin

IF TYPE_ID(N'integ_bh.@{Name}@') IS NOT NULL
DROP TYPE [integ_bh].[@{Name}@];
================= Fragment    Tables end

--------------------------------- CREATE TYPES ---------------------------------

================= Fragment    Tables begin

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

CREATE TYPE [integ_bh].[@{Name}@] AS TABLE
(
	================= Fragment Columns begin
	@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1) && typeof( @{Precision}@ ) === 'undefined'~@~@^,^@ [@{Name}@] [@{Type}@]~@
	@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1) && typeof( @{Precision}@ ) !== 'undefined'~@~@^,^@ [@{Name}@] [@{Type}@](@{Precision}@)~@
	================= Fragment Columns end
);
================= Fragment Tables end

================= Fragment Tables begin

--------------------------------- UPSERT @{Name}@ ---------------------------------

--[@{Name}@] [@{Type}@]@~(typeof( @{Precision}@ ) !== 'undefined')~@~(@{Precision}@)~@

IF EXISTS (SELECT * FROM sys.objects o WHERE type = 'P' AND name = N'uSP_INS_UPD_@{Name}@' AND SCHEMA_NAME(o.schema_id) = 'integ_bh') 
	BEGIN
	DROP PROCEDURE [integ_bh].uSP_INS_UPD_@{Name}@
	END
GO

CREATE PROCEDURE [integ_bh].[uSP_INS_UPD_@{Name}@]
	@startingDatetime			datetime = NULL,
	@endingDatetime				datetime = NULL
AS
	DECLARE	
		
		================= Fragment Columns begin
		@@{Name}@ @{Type}@@~(typeof( @{Precision}@ ) !== 'undefined')~@~(@{Precision}@)~@,
		================= Fragment Columns end
		-- Using
		@@{Name}@_TMP integ_bh.@{Name}@,
		@defaultStartingDatetime datetime,
		@defaultEndingDatetime datetime,
		-- Returning
		@RecordsFound int = 0,
		@Inserts int = 0,
		@Updates int = 0

DECLARE	@error int;
		
	INSERT INTO @@{Name}@_TMP
	EXEC	@error = [integ_bh].[Get_@{Name}@]
			@startingDatetime,
			@endingDatetime

DECLARE db_cursor CURSOR local
	FOR SELECT * FROM  @@{Name}@_TMP
	
	OPEN db_cursor

FETCH NEXT FROM db_cursor into 
		================= Fragment Columns begin
		@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  @@{Name}@~@ 
		================= Fragment Columns end

WHILE @@FETCH_STATUS = 0   
	BEGIN
		SET @RecordsFound += 1;
		IF EXISTS 
		(
			SELECT 
				1 
			FROM 
				[STAG-AT-BH-DB-01].Beehive_Integrations.dbo.[@{Name}@] 
			WHERE 
				================= Fragment Columns begin
				@~(typeof( @{IsKey}@ ) !== 'undefined' && @{IsKey}@ == true)~@~@^AND^@ [@{Name}@] = @@{Name}@~@ 
				================= Fragment Columns end
		)
		BEGIN
			UPDATE 
				[STAG-AT-BH-DB-01].Beehive_Integrations.dbo.[@{Name}@] 
			SET 
				================= Fragment Columns begin
				@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1) && !(typeof( @{IsKey}@ ) !== 'undefined' && @{IsKey}@ == true)~@~@^,^@  [@{Name}@] = @@{Name}@~@ 
				================= Fragment Columns end 
			WHERE 
				================= Fragment Columns begin
				@~(typeof( @{IsKey}@ ) !== 'undefined' && @{IsKey}@ == true)~@~@^AND^@ [@{Name}@] = @@{Name}@~@
				================= Fragment Columns end
			;
			SET @Updates += 1;
		END 
		ELSE BEGIN
			INSERT INTO 
				[STAG-AT-BH-DB-01].Beehive_Integrations.dbo.[@{Name}@] 
			(
				================= Fragment Columns begin
				@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  [@{Name}@]~@ 
				================= Fragment Columns end 
			)
			VALUES 
			(
				================= Fragment Columns begin
				@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  @@{Name}@~@ 
				================= Fragment Columns end 
			);
			SET @Inserts += 1;
		END
		FETCH NEXT FROM db_cursor into
			================= Fragment Columns begin
			@~!(typeof( @{Skeep}@ ) !== 'undefined' && @{Skeep}@ == 1)~@~@^,^@  @@{Name}@~@ 
			================= Fragment Columns end
	END
			
	CLOSE db_cursor   
	DEALLOCATE db_cursor
	-- SET FMTONLY OFF
	SELECT 'RecordsFound' = @RecordsFound, 'Inserts' = @Inserts, 'Updates' = @Updates

================= Fragment Tables end