﻿--Migration
DELETE FROM T_KIRSZIRFELADATELLATASIHELY 
GO

DECLARE @datetime datetime = '20201001'

DROP TABLE IF EXISTS #KIRSZIRFELADATELLATASIHELY

CREATE TABLE #KIRSZIRFELADATELLATASIHELY (
    ID int NOT NULL IDENTITY(1,1),
    C_FELADATELLATASIHELYID int NOT NULL,
    C_MUKODESIHELYNEV nvarchar(255) NOT NULL,
    C_MUKODESIHELYID int,
    C_OKTATASINEVELESIFELADATNEV nvarchar(500) NOT NULL,
    C_OKTATASINEVELESIFELADATTIPUS int,
    C_DATUM datetime NOT NULL,
    C_FELADATKATEGORIAID int NOT NULL,
    C_INTEZMENYID int NOT NULL,
    C_TANEVID int NOT NULL,
    TOROLT char(1) NOT NULL DEFAULT 'F',
    SERIAL int DEFAULT 0 NOT NULL,
    LASTCHANGED datetime DEFAULT GETDATE() NOT NULL,
    CREATED datetime DEFAULT GETDATE() NOT NULL,
    MODIFIER int NULL,
    CREATOR int NULL
)


INSERT INTO #KIRSZIRFELADATELLATASIHELY (
   C_FELADATELLATASIHELYID
  ,C_MUKODESIHELYNEV
  ,C_MUKODESIHELYID
  ,C_OKTATASINEVELESIFELADATNEV
  ,C_OKTATASINEVELESIFELADATTIPUS
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT
  ,SERIAL
  ,LASTCHANGED
  ,CREATED
  ,MODIFIER
  ,CREATOR
  ,C_DATUM
  ,C_FELADATKATEGORIAID
) SELECT
   fh.ID AS C_FELADATELLATASIHELYID -- * int
  ,mh.C_NEV AS C_MUKODESIHELYNEV -- * nvarchar(255)
  ,fh.C_MUKODESIHELYID AS C_MUKODESIHELYID
  ,d.C_NAME AS C_OKTATASINEVELESIFELADATNEV -- * nvarchar(500)
  ,fh.C_OKTATASINEVELESIFELADATTIPUS AS C_OKTATASINEVELESIFELADATTIPUS
  ,fh.C_INTEZMENYID AS C_INTEZMENYID -- * int
  ,fh.C_TANEVID AS C_TANEVID -- * int
  ,fh.TOROLT AS TOROLT -- * char(1)
  ,0 AS SERIAL -- * int
  ,GETDATE() AS LASTCHANGED -- * datetime
  ,GETDATE() AS CREATED -- * datetime
  ,NULL AS MODIFIER -- int
  ,NULL AS CREATOR -- int
  ,@datetime AS C_DATUM -- * datetime
  ,onf.C_FELADATKATEGORIAID AS C_FELADATKATEGORIAID -- * int
FROM T_FELADATELLATASIHELY fh
  INNER JOIN T_MUKODESIHELY mh ON mh.ID = fh.C_MUKODESIHELYID
  INNER JOIN T_DICTIONARYITEMBASE d ON d.ID = fh.C_OKTATASINEVELESIFELADATTIPUS AND d.C_TANEVID = fh.C_TANEVID
  INNER JOIN T_OKTATASINEVELESIFELADAT onf ON onf.ID = d.ID AND onf.C_ALTANEVID = d.C_TANEVID
  INNER JOIN T_TANEV tv ON tv.ID = fh.C_TANEVID AND tv.C_NEV = '2020/2021'

DECLARE @sql nvarchar(max) = ''

-- Feladatellátási hely adatainak visszaállítása
SELECT @sql += sqlCmd FROM(
SELECT DISTINCT 'UPDATE #KIRSZIRFELADATELLATASIHELY ' + 
  CASE
	  WHEN eh.C_REASON='New' THEN 'SET TOROLT = ''T'''
	  WHEN eh.C_REASON='Modified' THEN 'SET ' + eh.C_PROPERTYNAME + ' = '+ISNULL(''''+eh.C_ORIGINALVALUE+'''','NULL ')
  END +
  ' WHERE ID = ' + CAST(eh.C_ENTITYID AS varchar) +';' + CHAR(13) + CHAR(10) sqlCmd, eh.C_ALTERATIONDATE
FROM V_ENTITYHISTORY eh
  INNER JOIN #KIRSZIRFELADATELLATASIHELY fh ON fh.C_FELADATELLATASIHELYID = eh.C_ENTITYID
WHERE eh.C_ENTITYNAME = 'T_FELADATELLATASIHELY' 
  AND eh.C_ALTERATIONDATE >= @datetime + 1
  AND eh.C_PROPERTYNAME IN ('C_MUKODESIHELYID', 'TOROLT', 'C_OKTATASINEVELESIFELADATTIPUS')
) x WHERE x.sqlCmd IS NOT NULL
ORDER BY x.C_ALTERATIONDATE DESC

-- T_MUKODESIHELY nevének visszaállítása
SELECT @sql += sqlCmd FROM(
SELECT DISTINCT 'UPDATE #KIRSZIRFELADATELLATASIHELY ' + 
  CASE
    WHEN eh.C_REASON='New' THEN 'SET TOROLT = ''T'''
	  WHEN eh.C_REASON='Modified' THEN 'SET C_MUKODESIHELYNEV = ' + ISNULL(''''+eh.C_ORIGINALVALUE+'''','NULL')
  END + 
  ' WHERE C_MUKODESIHELYID = ' + CAST(eh.C_ENTITYID AS varchar) +';' + CHAR(13) + CHAR(10) sqlCmd, eh.C_ALTERATIONDATE
FROM V_ENTITYHISTORY eh
  INNER JOIN #KIRSZIRFELADATELLATASIHELY fh ON fh.C_MUKODESIHELYID = eh.C_ENTITYID
WHERE eh.C_ENTITYNAME = 'T_MUKODESIHELY' 
  AND eh.C_ALTERATIONDATE >= @datetime + 1 
  AND eh.C_PROPERTYNAME = 'C_NEV'
) x WHERE x.sqlCmd IS NOT NULL
ORDER BY x.C_ALTERATIONDATE DESC

-- C_OKTATASINEVELESIFELADATTIPUS nevének visszaállítása
SELECT @sql += sqlCmd FROM(
SELECT DISTINCT 'UPDATE #KIRSZIRFELADATELLATASIHELY ' + 
  CASE
    WHEN eh.C_REASON='New' THEN 'SET TOROLT = ''T'''
	  WHEN eh.C_REASON='Modified' THEN 'SET C_OKTATASINEVELESIFELADATNEV = ' + ISNULL(''''+eh.C_ORIGINALVALUE+'''','NULL') 
  END+ 
  ' WHERE C_OKTATASINEVELESIFELADATTIPUS = ' + CAST(eh.C_ENTITYID AS varchar) + ' AND C_TANEVID = ' + CAST(eh.C_TANEVID AS varchar) + ';' + CHAR(13) + CHAR(10) sqlCmd, eh.C_ALTERATIONDATE
FROM V_ENTITYHISTORY eh
  INNER JOIN #KIRSZIRFELADATELLATASIHELY fh ON fh.C_OKTATASINEVELESIFELADATTIPUS = eh.C_ENTITYID AND fh.C_TANEVID = eh.C_TANEVID
WHERE eh.C_ENTITYNAME = 'T_DICTIONARYITEMBASE' 
  AND eh.C_ALTERATIONDATE >= @datetime + 1 
  AND eh.C_PROPERTYNAME = 'C_NEV'
) x WHERE x.sqlCmd IS NOT NULL
ORDER BY x.C_ALTERATIONDATE DESC

--SELECT @sql

EXEC sp_executesql @sql
GO


INSERT INTO T_KIRSZIRFELADATELLATASIHELY (
   C_FELADATELLATASIHELYID
  ,C_MUKODESIHELYNEV
  ,C_OKTATASINEVELESIFELADATNEV
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT
  ,SERIAL
  ,LASTCHANGED
  ,CREATED
  ,MODIFIER
  ,CREATOR
  ,C_DATUM
  ,C_FELADATKATEGORIAID
) SELECT 
   C_FELADATELLATASIHELYID
  ,C_MUKODESIHELYNEV
  ,C_OKTATASINEVELESIFELADATNEV
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT
  ,SERIAL
  ,LASTCHANGED
  ,CREATED
  ,MODIFIER
  ,CREATOR
  ,C_DATUM
  ,C_FELADATKATEGORIAID
FROM #KIRSZIRFELADATELLATASIHELY
GO

TRUNCATE TABLE #KIRSZIRFELADATELLATASIHELY
GO


DECLARE @datetime datetime = '20201008'

INSERT INTO #KIRSZIRFELADATELLATASIHELY (
   C_FELADATELLATASIHELYID
  ,C_MUKODESIHELYNEV
  ,C_MUKODESIHELYID
  ,C_OKTATASINEVELESIFELADATNEV
  ,C_OKTATASINEVELESIFELADATTIPUS
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT
  ,SERIAL
  ,LASTCHANGED
  ,CREATED
  ,MODIFIER
  ,CREATOR
  ,C_DATUM
  ,C_FELADATKATEGORIAID
) SELECT
   fh.ID AS C_FELADATELLATASIHELYID -- * int
  ,mh.C_NEV AS C_MUKODESIHELYNEV -- * nvarchar(255)
  ,fh.C_MUKODESIHELYID AS C_MUKODESIHELYID
  ,d.C_NAME AS C_OKTATASINEVELESIFELADATNEV -- * nvarchar(500)
  ,fh.C_OKTATASINEVELESIFELADATTIPUS AS C_OKTATASINEVELESIFELADATTIPUS
  ,fh.C_INTEZMENYID AS C_INTEZMENYID -- * int
  ,fh.C_TANEVID AS C_TANEVID -- * int
  ,fh.TOROLT AS TOROLT -- * char(1)
  ,0 AS SERIAL -- * int
  ,GETDATE() AS LASTCHANGED -- * datetime
  ,GETDATE() AS CREATED -- * datetime
  ,NULL AS MODIFIER -- int
  ,NULL AS CREATOR -- int
  ,@datetime AS C_DATUM -- * datetime
  ,onf.C_FELADATKATEGORIAID AS C_FELADATKATEGORIAID -- * int
FROM T_FELADATELLATASIHELY fh
  INNER JOIN T_MUKODESIHELY mh ON mh.ID = fh.C_MUKODESIHELYID
  INNER JOIN T_DICTIONARYITEMBASE d ON d.ID = fh.C_OKTATASINEVELESIFELADATTIPUS AND d.C_TANEVID = fh.C_TANEVID
  INNER JOIN T_OKTATASINEVELESIFELADAT onf ON onf.ID = d.ID AND onf.C_ALTANEVID = d.C_TANEVID
  INNER JOIN T_TANEV tv ON tv.ID = fh.C_TANEVID AND tv.C_NEV = '2020/2021'

DECLARE @sql nvarchar(max) = ''

SELECT @sql += sqlCmd FROM(
SELECT DISTINCT 'UPDATE #KIRSZIRFELADATELLATASIHELY ' + 
  CASE
	  WHEN eh.C_REASON='New' THEN 'SET TOROLT = ''T'''
	  WHEN eh.C_REASON='Modified' THEN 'SET ' + eh.C_PROPERTYNAME + ' = '+ISNULL(''''+eh.C_ORIGINALVALUE+'''','NULL ')
  END +
  ' WHERE ID = ' + CAST(eh.C_ENTITYID AS varchar) +';' + CHAR(13) + CHAR(10) sqlCmd, eh.C_ALTERATIONDATE
FROM V_ENTITYHISTORY eh
  INNER JOIN #KIRSZIRFELADATELLATASIHELY fh ON fh.C_FELADATELLATASIHELYID = eh.C_ENTITYID
WHERE eh.C_ENTITYNAME = 'T_FELADATELLATASIHELY' 
  AND eh.C_ALTERATIONDATE >= @datetime + 1
  AND eh.C_PROPERTYNAME IN ('C_MUKODESIHELYID', 'TOROLT', 'C_OKTATASINEVELESIFELADATTIPUS')
) x WHERE x.sqlCmd IS NOT NULL
ORDER BY x.C_ALTERATIONDATE DESC


SELECT @sql += sqlCmd FROM(
SELECT DISTINCT 'UPDATE #KIRSZIRFELADATELLATASIHELY ' + 
  CASE
    WHEN eh.C_REASON='New' THEN 'SET TOROLT = ''T'''
	  WHEN eh.C_REASON='Modified' THEN 'SET C_MUKODESIHELYNEV = ' + ISNULL(''''+eh.C_ORIGINALVALUE+'''','NULL')
  END + 
  ' WHERE C_MUKODESIHELYID = ' + CAST(eh.C_ENTITYID AS varchar) +';' + CHAR(13) + CHAR(10) sqlCmd, eh.C_ALTERATIONDATE
FROM V_ENTITYHISTORY eh
  INNER JOIN #KIRSZIRFELADATELLATASIHELY fh ON fh.C_MUKODESIHELYID = eh.C_ENTITYID
WHERE eh.C_ENTITYNAME = 'T_MUKODESIHELY' 
  AND eh.C_ALTERATIONDATE >= @datetime + 1 
  AND eh.C_PROPERTYNAME = 'C_NEV'
) x WHERE x.sqlCmd IS NOT NULL
ORDER BY x.C_ALTERATIONDATE DESC


SELECT @sql += sqlCmd FROM(
SELECT DISTINCT 'UPDATE #KIRSZIRFELADATELLATASIHELY ' + 
  CASE
    WHEN eh.C_REASON='New' THEN 'SET TOROLT = ''T'''
	  WHEN eh.C_REASON='Modified' THEN 'SET C_OKTATASINEVELESIFELADATNEV = ' + ISNULL(''''+eh.C_ORIGINALVALUE+'''','NULL') 
  END+ 
  ' WHERE C_OKTATASINEVELESIFELADATTIPUS = ' + CAST(eh.C_ENTITYID AS varchar) + ' AND C_TANEVID = ' + CAST(eh.C_TANEVID AS varchar) + ';' + CHAR(13) + CHAR(10) sqlCmd, eh.C_ALTERATIONDATE
FROM V_ENTITYHISTORY eh
  INNER JOIN #KIRSZIRFELADATELLATASIHELY fh ON fh.C_OKTATASINEVELESIFELADATTIPUS = eh.C_ENTITYID AND fh.C_TANEVID = eh.C_TANEVID
WHERE eh.C_ENTITYNAME = 'T_DICTIONARYITEMBASE' 
  AND eh.C_ALTERATIONDATE >= @datetime + 1 
  AND eh.C_PROPERTYNAME = 'C_NEV'
) x WHERE x.sqlCmd IS NOT NULL
ORDER BY x.C_ALTERATIONDATE DESC


--SELECT @sql

EXEC sp_executesql @sql
GO


INSERT INTO T_KIRSZIRFELADATELLATASIHELY (
   C_FELADATELLATASIHELYID
  ,C_MUKODESIHELYNEV
  ,C_OKTATASINEVELESIFELADATNEV
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT
  ,SERIAL
  ,LASTCHANGED
  ,CREATED
  ,MODIFIER
  ,CREATOR
  ,C_DATUM
  ,C_FELADATKATEGORIAID
) SELECT 
   C_FELADATELLATASIHELYID
  ,C_MUKODESIHELYNEV
  ,C_OKTATASINEVELESIFELADATNEV
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT
  ,SERIAL
  ,LASTCHANGED
  ,CREATED
  ,MODIFIER
  ,CREATOR
  ,C_DATUM
  ,C_FELADATKATEGORIAID
FROM #KIRSZIRFELADATELLATASIHELY
GO

