SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS dbo.uspOsztondijHistoryFeltoltes
GO
CREATE PROCEDURE dbo.uspOsztondijHistoryFeltoltes
AS
SET NOCOUNT ON;

DROP TABLE IF EXISTS #TablaOszlopLista;
CREATE TABLE #TablaOszlopLista(
    TablaId TINYINT,
    TablaNev SYSNAME COLLATE Hungarian_CI_AI,
    OszlopNev SYSNAME COLLATE Hungarian_CI_AI,
    PRIMARY KEY CLUSTERED (TablaId, OszlopNev),
    UNIQUE (TablaNev, OszlopNev)
);

DROP TABLE IF EXISTS #columns
CREATE TABLE #columns(
    table_name sysname,
    column_name sysname,
    created_date datetime,
    data_type sysname null,
    default_value nvarchar(100) null,
    primary key clustered (table_name, column_name)
)

DECLARE @sql nvarchar(max) = ''

SELECT 
    @sql += CONCAT('(''', t.name, ''', ''', c.name, ''', ''', CONVERT(varchar(23), ISNULL(d.modify_date, GETDATE()), 121), ''', ''', y.name, ''', CAST(', ISNULL(d.definition, 'NULL'), ' AS nvarchar(100))),', CHAR(13))
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.types y ON y.system_type_id = c.system_type_id and y.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints d ON  d.parent_object_id = c.object_id 
                                    AND d.parent_column_id = c.column_id
                                    AND c.is_nullable = 0
                                    AND d.definition NOT LIKE '%next%'
                                    AND d.definition NOT LIKE '%getdate()%'
                                    AND d.definition NOT LIKE '%newid()%'
WHERE s.name = 'dbo'
AND c.name NOT IN ('SERIAL', 'LASTCHANGED', 'CREATED', 'ID', 'C_INTEZMENYID', 'C_ALINTEZMENYID', 'C_TANEVID', 'C_ALTANEVID')
AND y.name NOT IN ('varbinary', 'binary')



set @sql = 'SELECT *
FROM (
VALUES
' + @sql + '
('''', '''', '''', '''', '''')
) cols(table_name, column_name, created_date, data_type, default_value)'

INSERT INTO #columns(table_name, column_name, created_date, data_type, default_value)
EXEC(@sql)



INSERT INTO #TablaOszlopLista(TablaId, TablaNev, OszlopNev)
SELECT TablaId, TablaNev, OszlopNev FROM dbo.fnOsztondijHistoryOszlopok();

DROP TABLE IF EXISTS #TablaLista;
CREATE TABLE #TablaLista(
    TablaId TINYINT,
    TablaNev SYSNAME COLLATE Hungarian_CI_AI,
    PRIMARY KEY CLUSTERED (TablaId),
    UNIQUE (TablaNev)
);

INSERT INTO #TablaLista(TablaId, TablaNev)
SELECT DISTINCT TablaId, TablaNev
FROM #TablaOszlopLista;

DECLARE @LastHistoryID INT = 0;
SELECT @LastHistoryID = ISNULL(MAX(ID), 0) FROM dbo.T_OSZTONDIJHISTORY WITH (NOLOCK);

-- normál sorok beszúrása a T_ENTITYHISTORY/T_ENTITYATTRIBUTEHISTORY táblákból
INSERT INTO dbo.T_OSZTONDIJHISTORY(
     ID
    ,C_ENTITYID
    ,C_TABLEID
    ,C_ALTERATIONDATE
    ,C_REASONID
    ,C_FELHASZNALOID
    ,C_TANULOID
    ,C_TANEVID
    ,C_PROPERTYNAME
    ,C_ORIGINALVALUE
    ,C_CURRENTVALUE
)
SELECT 
    eh.ID,
    eh.C_ENTITYID,
    t.TablaId AS C_TABLEID,
    eh.C_ALTERATIONDATE,
    CASE eh.C_REASON 
        WHEN 'New' THEN 1
        WHEN 'Removed' THEN 2
        WHEN 'Modified' THEN 3
        ELSE 0
    END AS C_REASONID,
    eh.C_FELHASZNALOID,
    CASE eh.C_ENTITYNAME
		WHEN 'T_OSZTALYCSOPORT' THEN 0
        WHEN 'T_TANULO' THEN eh.C_ENTITYID
        WHEN 'T_TANULOCSOPORT' THEN tcs1.C_TANULOID
        WHEN 'T_TANULOMULASZTAS' THEN tm.C_ORATANULOIID
        WHEN 'T_TANULOTANUGYIADATOK' THEN tcs2.C_TANULOID
        ELSE null
    END AS C_TANULOID,
    eh.C_TANEVID,
    eah.C_PROPERTYNAME,
    eah.C_ORIGINALVALUE,
    eah.C_CURRENTVALUE
FROM T_ENTITYHISTORY eh WITH (NOLOCK)
INNER JOIN T_TANEV v WITH (NOLOCK) on v.ID = eh.C_TANEVID
INNER JOIN T_INTEZMENY i WITH (NOLOCK) ON i.ID = v.C_INTEZMENYID
INNER JOIN T_ENTITYATTRIBUTEHISTORY eah WITH (NOLOCK) ON eah.C_ENTITYHISTORYID = eh.ID
LEFT JOIN #TablaLista t ON t.TablaNev = eh.C_ENTITYNAME
LEFT JOIN T_TANULOCSOPORT tcs1 WITH (NOLOCK) ON tcs1.ID = eh.C_ENTITYID and eh.C_ENTITYNAME = 'T_TANULOCSOPORT'
LEFT JOIN T_TANULOMULASZTAS tm WITH (NOLOCK) ON tm.ID = eh.C_ENTITYID and eh.C_ENTITYNAME = 'T_TANULOMULASZTAS'
LEFT JOIN T_TANULOTANUGYIADATOK tt WITH (NOLOCK) ON tt.ID = eh.C_ENTITYID and eh.C_ENTITYNAME = 'T_TANULOTANUGYIADATOK'
LEFT JOIN T_TANULOCSOPORT tcs2 WITH (NOLOCK) ON tcs2.ID = tt.C_TANULOCSOPORTID
WHERE (v.C_AKTIV = 'T' OR v.C_KOVETKEZO = 'T')
AND v.C_SORSZAM >= ((YEAR(GETDATE()) - 1 - 2000) * 2)
AND v.TOROLT = 'F'
AND i.TOROLT = 'F'
AND i.C_FENNTARTOAZONOSITO <> '999'
AND i.C_AZONOSITO NOT LIKE '%deleted'
AND i.C_AZONOSITO NOT LIKE '%moved'
AND i.C_AZONOSITO NOT LIKE '%[_]old'
AND eh.ID > @LastHistoryID
AND EXISTS(SELECT * FROM #TablaOszlopLista t WHERE t.TablaNev = eh.C_ENTITYNAME AND t.OszlopNev = eah.C_PROPERTYNAME)

-- előző tanévek cleanup-ja
/*
DELETE FROM oh
FROM T_OSZTONDIJHISTORY oh
INNER JOIN T_TANEV t on t.ID = oh.C_TANEVID
WHERE t.C_SORSZAM < ((YEAR(GETDATE()) - 1 - 2000) * 2)
*/

GO