﻿-- =============================================
-- Description:	<Dinamikus import>
-- =============================================
DROP PROCEDURE IF EXISTS [dbo].[uspDynamicImport]
GO

CREATE PROCEDURE [dbo].[uspDynamicImport]
	 @pIntezmenyId					INT
	,@pTanevId						INT
	,@pUserId						INT
	,@pImportJson					NVARCHAR(MAX)
	,@pIsTorles						BIT
	,@pIsSubTable					BIT

AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE 
		 @sqlCommand NVARCHAR(MAX)			= ''

		,@sqlInsertCommand NVARCHAR(MAX)	= ''
		,@sqlUpdateCommand NVARCHAR(MAX)	= ''
		,@sqlDeleteCommand NVARCHAR(MAX)	= ''
	 
		,@lastChangedDateTime DATETIME		= GETDATE()

		,@openJsonWithCommand NVARCHAR(MAX)
		,@rootElementJsonWithCommand NVARCHAR(300)
		,@columnNameFromJsonCommand NVARCHAR(300)

		,@columnListForInsert NVARCHAR(MAX)
		,@columnListForUpdate NVARCHAR(MAX)

		,@rootElement NVARCHAR(100)
		,@tableNameFromJson NVARCHAR(50)
		,@columnCount INT
	
		,@openJsonPath NVARCHAR(300)
	
	CREATE TABLE #entityIds
	(
		ID INT
	)

	DECLARE @TableName TABLE
	(
		TableName NVARCHAR(50)
	)

	DECLARE @ColumnTable TABLE
	(
		ColumnName NVARCHAR(50)
	)

	DECLARE @ColumnDataTable TABLE
	(
		 ColumnName NVARCHAR(50)
		,ColumnType NVARCHAR(50)
		,MaxLengthValue NVARCHAR(50)
		,NumericPrecision NVARCHAR(50)
		,NumericScale NVARCHAR(50)
		,IsNullable NVARCHAR(50)
	)

	-- A Json Root elemének kiszedése változóba @rootElement
	SELECT
		@rootElement = [key]
	FROM 
		OPENJSON(@pImportJson, N'strict $')

	--Az importálandó táblanevének kiszedése JSON-ből (rootelem utáni elem) @tableNameFromJson
	 SET @rootElementJsonWithCommand = '
		SELECT 
			@tableNameFromJson = [key] 
		FROM 
			OPENJSON (@pImportJson, N''lax $.' + @rootElement + ''') 
		SELECT 
			@tableNameFromJson 
	'

	INSERT INTO @TableName
	EXEC sp_executesql @rootElementJsonWithCommand, N'@pImportJson NVARCHAR(MAX), @tableNameFromJson NVARCHAR(50)', @pImportJson, @tableNameFromJson

	SELECT @tableNameFromJson = (SELECT TOP 1 TableName FROM @TableName)
	
	--NOTE: Megvizsgáljuk, hogy létezik-e a tábla neve a Json-ben
	IF (@tableNameFromJson IS NOT NULL)
	BEGIN

		--Ha Törölnünk is kell akkor azzal kezdünk!
		IF @pIsTorles = 1 
		BEGIN
			--DELETE összeállítása (Igazából update mert csak töröltre állít)
			SET @sqlDeleteCommand = '
				UPDATE 
					[' + @tableNameFromJson + '] 
				SET 
					[' + @tableNameFromJson + '].[TOROLT]				= ''T'', 
					[' + @tableNameFromJson + '].[SERIAL]				= [' + @tableNameFromJson + '].[SERIAL] + 1, 
					[' + @tableNameFromJson + '].[LASTCHANGED]			= ''' + CONVERT(NVARCHAR(32), @lastChangedDateTime, 126) + ''', 
					[' + @tableNameFromJson + '].[MODIFIER]				= ''' + CONVERT(NVARCHAR(32), @pUserId) + ''' 
				OUTPUT DELETED.ID 
					INTO #entityIds 
				WHERE '
			IF(@pIsSubTable = 0)
			BEGIN 
				SET @sqlDeleteCommand = @sqlDeleteCommand + '
						[' + @tableNameFromJson + '].[C_INTEZMENYID]	= ''' + CONVERT(NVARCHAR(32), @pIntezmenyId) + ''' 
					AND	[' + @tableNameFromJson + '].[C_TANEVID]		= ''' + CONVERT(NVARCHAR(32), @pTanevId) + ''''
			END
			ELSE
			BEGIN 
				SET @sqlDeleteCommand = @sqlDeleteCommand + '
						[' + @tableNameFromJson + '].[C_ALINTEZMENYID]	= ''' + CONVERT(NVARCHAR(32), @pIntezmenyId) + ''' 
					AND	[' + @tableNameFromJson + '].[C_ALTANEVID]		= ''' + CONVERT(NVARCHAR(32), @pTanevId) + ''''
			END
			SET @sqlDeleteCommand = @sqlDeleteCommand + '
					AND	[' + @tableNameFromJson + '].[TOROLT]			= ''F'' 
					AND [' + @tableNameFromJson + '].[C_IMPORTALT]		= ''T'' 			
			'
		END

		--Oszlopok nevei a Json-ben
		SET @columnNameFromJsonCommand = '
			SELECT 
				[key]	ColumnNameList 
			INTO 
				#ColumnList 
			FROM 
				OPENJSON (@pImportJson, N''lax $.' + @rootElement + '.' + @tableNameFromJson + '[0]'')

			SELECT 
				* 
			FROM 
				#ColumnList
		'

		INSERT INTO @ColumnTable
		EXEC sp_executesql @columnNameFromJsonCommand, N'@pImportJson NVARCHAR(MAX)', @pImportJson		

		SELECT @columnCount = (SELECT COUNT(*) FROM @ColumnTable)	
		
		--NOTE: Megvizsgáljuk, hogy van-e oszlop a Json-ben
		IF (@columnCount <> 0)
		BEGIN

			INSERT INTO 
				@ColumnDataTable 
			SELECT 
				 COLUMN_NAME
				,DATA_TYPE
				,CHARACTER_MAXIMUM_LENGTH
				,NUMERIC_PRECISION
				,NUMERIC_SCALE
				,IS_NULLABLE
			FROM 
				INFORMATION_SCHEMA.columns
			WHERE 
					TABLE_NAME = @tableNameFromJson 
				AND TABLE_SCHEMA = 'dbo' 
				AND COLUMN_NAME IN (SELECT ColumnName FROM @ColumnTable) 

			SELECT @openJsonWithCommand = 
				ISNULL(@openJsonWithCommand + CHAR(13) + CHAR(10) + CHAR(9) + ',', CHAR(9) + SPACE(1) + '')
				+ '[' + ColumnName + '] ' + ColumnType
				+ CASE 
						WHEN ColumnType IN ('DECIMAL', 'NUMERIC') 
						THEN ISNULL('(' + CONVERT(VARCHAR(2), NumericPrecision) + ', ' + CONVERT(VARCHAR(2), NumericScale) + ') ', ' ')
						WHEN ColumnType IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR') 
						THEN ISNULL('(' + CONVERT(VARCHAR(4), IIF(MaxLengthValue = -1, 'MAX', MaxLengthValue)) + ') ', ' ')
					ELSE 
						' ' 
					END
				+ '''$.' + ColumnName + ''''
				FROM @ColumnDataTable

			SET @openJsonWithCommand = '(' + CHAR(13) + CHAR(10) + @openJsonWithCommand + CHAR(13) + CHAR(10) + '	,[Operation] int ''$.Operation''' + CHAR(13) + CHAR(10) + ')'

			--OSZLOPLISTA INSERTHEZ
			SELECT
				@columnListForInsert = ISNULL(@columnListForInsert + ',', '') + '[' + columnDataTable.ColumnName + ']' --TODO andrejkovicse: Függvény a []-ra
			FROM 
				@ColumnDataTable columnDataTable
			WHERE 
				@pIsSubTable = 0 AND columnDataTable.ColumnName NOT IN ('Operation', 'ID') OR 
				@pIsSubTable = 1 AND columnDataTable.ColumnName NOT IN ('Operation')

			--OSZLOPLISTA UPDATHEZ
			SELECT
				@columnListForUpdate = ISNULL(@columnListForUpdate + ',', '') + '[' + @tableNameFromJson + '].' + '[' + columnDataTable.ColumnName + '] = [ImportData].' + '[' + columnDataTable.ColumnName + ']'
			FROM 
				@ColumnDataTable columnDataTable
			WHERE 
				columnDataTable.ColumnName NOT IN ('Operation', 'ID', 'SERIAL', 'CREATOR', 'CREATED', 'C_IMPORTALT')

			-- OPENJSON path beállítása (ez megegyezik mindhárom műveletnél)
			SET @openJsonPath = N'''strict $.' + @rootElement + '.' + @tableNameFromJson + ''''

			--INSERT összeállítása
			SET @sqlInsertCommand = '
				INSERT INTO 
					[' + @tableNameFromJson + '] (
						' + @columnListForInsert + '
					) 
				OUTPUT INSERTED.ID 
					INTO #entityIds
				SELECT 
					' + @columnListForInsert + '
				FROM 
					OPENJSON (@pImportJson, ' + @openJsonPath + ') 
				WITH ' 
					+ @openJsonWithCommand + '
				WHERE 					
					[Operation] = 1 --NOTE: ImportItemOperationEnum.Insert
			'

			--UPDATE összeállítása
			SET @sqlUpdateCommand = '
				UPDATE 
					[' + @tableNameFromJson + '] 
				SET 
					' + @columnListForUpdate + ',[' + @tableNameFromJson + '].[SERIAL] = [' + @tableNameFromJson + '].[SERIAL] + 1 ' + ' 
				OUTPUT DELETED.ID 
					INTO #entityIds 
				FROM (
					SELECT
						* 
					FROM 
						OPENJSON (@pImportJson, ' + @openJsonPath + ') 
					WITH '
						+ @openJsonWithCommand + '
					WHERE						
						[Operation] = 2 --NOTE: ImportItemOperationEnum.Update
				) AS ImportData 
				WHERE '
			IF(@pIsSubTable = 0)
			BEGIN 
				SET @sqlUpdateCommand = @sqlUpdateCommand + ' 
						[' + @tableNameFromJson + '].[C_INTEZMENYID]	= ''' + CONVERT(NVARCHAR(32), @pIntezmenyId) + ''' 
					AND	[' + @tableNameFromJson + '].[C_TANEVID]		= ''' + CONVERT(NVARCHAR(32), @pTanevId) + ''''
			END
			ELSE
			BEGIN 
				SET @sqlUpdateCommand = @sqlUpdateCommand + ' 
						[' + @tableNameFromJson + '].[C_ALINTEZMENYID]	= ''' + CONVERT(NVARCHAR(32), @pIntezmenyId) + ''' 
					AND	[' + @tableNameFromJson + '].[C_ALTANEVID]		= ''' + CONVERT(NVARCHAR(32), @pTanevId) + ''''
			END
			SET @sqlUpdateCommand = @sqlUpdateCommand + ' 
					AND	[' + @tableNameFromJson + '].[TOROLT]			= ''F'' 
					AND	[' + @tableNameFromJson + '].[ID]				= ImportData.ID 
			'

			SET @sqlCommand = @sqlDeleteCommand + @sqlInsertCommand + @sqlUpdateCommand

		END

		IF(@sqlCommand IS NOT NULL OR @sqlCommand <> '')
		BEGIN
			EXEC sp_executesql @sqlCommand, N'@pImportJson NVARCHAR(MAX)', @pImportJson
			IF @tableNameFromJson IN ('T_TEREM_OSSZES','T_ESZKOZ_OSSZES','T_TANTARGY_OSSZES') 
			BEGIN
				DECLARE @entityId int
					    ,@entityTanevId int
					    ,@intezmenyId int
					    ,@kovTanevId int
				DECLARE @sql nvarchar(400) = 'SELECT TOP 1 @entityTanevId = C_TANEVID FROM ' + @tableNameFromJson + ' WHERE ID = ' + (SELECT TOP 1 CAST(ID AS varchar(9)) FROM #entityIds)
				EXEC sp_executesql @sql, N'@entityTanevId int output', @entityTanevId = @entityTanevId output
				SELECT @intezmenyId = C_INTEZMENYID FROM T_TANEV WHERE ID = @entityTanevId
				SELECT @kovTanevId = ID FROM T_TANEV_OSSZES WHERE C_INTEZMENYID = @intezmenyId AND C_KOVETKEZO = 'T' AND TOROLT = 'F'
				IF @entityTanevId <> @kovTanevId AND @kovTanevId IS NOT NULL
				BEGIN
					DECLARE entityCur CURSOR FOR 
					SELECT ID FROM #entityIds
					OPEN entityCur
					FETCH NEXT FROM entityCur
					INTO @entityId
					WHILE @@FETCH_STATUS = 0
					BEGIN
					IF @tableNameFromJson = 'T_TEREM_OSSZES' BEGIN
					    EXEC sp_FollowUpTerem
								    @intezmenyId = @intezmenyId
								,@aktTanevId = @entityTanevId
								,@kovetkezoTanevId = @kovTanevId
								,@teremId = @entityId
					END
					ELSE IF @tableNameFromJson = 'T_ESZKOZ_OSSZES' BEGIN
					    EXEC sp_FollowUpEszkoz
								 @intezmenyId = @intezmenyId
								,@aktTanevId = @entityTanevId
								,@kovetkezoTanevId = @kovTanevId
								,@eszkozId = @entityId
					END
          ELSE IF @tableNameFromJson = 'T_TANTARGY_OSSZES' BEGIN
            EXEC sp_FollowUpTantargy
								 @intezmenyId = @intezmenyId
								,@aktTanevId = @entityTanevId
								,@kovetkezoTanevId = @kovTanevId
								,@tantargyId = @entityId
          END
					FETCH NEXT FROM entityCur INTO @entityId
					END
					CLOSE entityCur
					DEALLOCATE entityCur
				END
			END
		END
	END
END


GO

