﻿exec sp_Global_DeleteViews null, '<ViewNames><ViewName>T_KEPESITES</ViewName><ViewName>T_KEPESITES_OSSZES</ViewName></ViewNames>'

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
PRINT N'Dropping [dbo].[T_VEGZETTSEG].[I_T_4028287_4028289]...';


GO
DROP INDEX [I_T_4028287_4028289]
    ON [dbo].[T_VEGZETTSEG];


GO
PRINT N'Dropping [dbo].[FK_4028287_4028289]...';


GO
ALTER TABLE [dbo].[T_VEGZETTSEG] DROP CONSTRAINT [FK_4028287_4028289];


GO
PRINT N'Altering [dbo].[T_VEGZETTSEG]...';


GO
ALTER TABLE [dbo].[T_VEGZETTSEG] DROP COLUMN [C_KEPESITESTIPUSID];


GO
PRINT N'Creating [dbo].[T_KEPESITES]...';


GO
CREATE TABLE [dbo].[T_KEPESITES] (
    [ID]                  INT      IDENTITY (1, 1) NOT NULL,
    [C_KEPESITESTIPUSID]  INT      NULL,
    [C_INTEZMENYID]       INT      NOT NULL,
    [C_TANEVID]           INT      NOT NULL,
    [TOROLT]              CHAR (1) NOT NULL,
    [SERIAL]              INT      NOT NULL,
    [LASTCHANGED]         DATETIME NOT NULL,
    [CREATED]             DATETIME NOT NULL,
    [MODIFIER]            INT      NULL,
    [CREATOR]             INT      NULL,
    [ELOZOTANEVIREKORDID] INT      NULL,
    [NNID]                INT      NULL,
    CONSTRAINT [PK_T_4028294] PRIMARY KEY CLUSTERED ([ID] ASC)
);


GO
PRINT N'Creating [dbo].[T_KEPESITES].[I_T_4028294_4028295]...';


GO
CREATE NONCLUSTERED INDEX [I_T_4028294_4028295]
    ON [dbo].[T_KEPESITES]([C_INTEZMENYID] ASC, [C_TANEVID] ASC, [C_KEPESITESTIPUSID] ASC);


GO
PRINT N'Creating [dbo].[T_KEPESITES].[I_T_402829402_402829400]...';


GO
CREATE NONCLUSTERED INDEX [I_T_402829402_402829400]
    ON [dbo].[T_KEPESITES]([C_INTEZMENYID] ASC);


GO
PRINT N'Creating [dbo].[T_KEPESITES].[I_T_402829405_402829403]...';


GO
CREATE NONCLUSTERED INDEX [I_T_402829405_402829403]
    ON [dbo].[T_KEPESITES]([C_TANEVID] ASC);


GO
PRINT N'Creating [dbo].[T_KEPESITES_VEGZETTSEG]...';


GO
CREATE TABLE [dbo].[T_KEPESITES_VEGZETTSEG] (
    [C_KEPESITESID]       INT NOT NULL,
    [C_VEGZETTSEGID]      INT NOT NULL,
    [ELOZOTANEVIREKORDID] INT NULL,
    [NNID]                INT NULL,
    CONSTRAINT [PK_T_T_KEPESITES_VEGZETTSEG] PRIMARY KEY CLUSTERED ([C_KEPESITESID] ASC, [C_VEGZETTSEGID] ASC)
);


GO
PRINT N'Creating [dbo].[T_KEPESITES_VEGZETTSEG].[I_T_4028297_4028294]...';


GO
CREATE NONCLUSTERED INDEX [I_T_4028297_4028294]
    ON [dbo].[T_KEPESITES_VEGZETTSEG]([C_KEPESITESID] ASC);


GO
PRINT N'Creating [dbo].[T_KEPESITES_VEGZETTSEG].[I_T_4028298_4028287]...';


GO
CREATE NONCLUSTERED INDEX [I_T_4028298_4028287]
    ON [dbo].[T_KEPESITES_VEGZETTSEG]([C_VEGZETTSEGID] ASC);


GO
PRINT N'Creating unnamed constraint on [dbo].[T_KEPESITES]...';


GO
ALTER TABLE [dbo].[T_KEPESITES]
    ADD DEFAULT ('F') FOR [TOROLT];


GO
PRINT N'Creating unnamed constraint on [dbo].[T_KEPESITES]...';


GO
ALTER TABLE [dbo].[T_KEPESITES]
    ADD DEFAULT (getdate()) FOR [CREATED];


GO
PRINT N'Creating unnamed constraint on [dbo].[T_KEPESITES]...';


GO
ALTER TABLE [dbo].[T_KEPESITES]
    ADD DEFAULT ((0)) FOR [SERIAL];


GO
PRINT N'Creating unnamed constraint on [dbo].[T_KEPESITES]...';


GO
ALTER TABLE [dbo].[T_KEPESITES]
    ADD DEFAULT (getdate()) FOR [LASTCHANGED];


GO
PRINT N'Creating [dbo].[FK_4028294_4028295]...';


GO
ALTER TABLE [dbo].[T_KEPESITES] WITH NOCHECK
    ADD CONSTRAINT [FK_4028294_4028295] FOREIGN KEY ([C_KEPESITESTIPUSID], [C_INTEZMENYID], [C_TANEVID]) REFERENCES [dbo].[T_TARGYKATEGORIATIPUS] ([ID], [C_ALINTEZMENYID], [C_ALTANEVID]);


GO
PRINT N'Creating [dbo].[FK_402829402_402829400]...';


GO
ALTER TABLE [dbo].[T_KEPESITES] WITH NOCHECK
    ADD CONSTRAINT [FK_402829402_402829400] FOREIGN KEY ([C_INTEZMENYID]) REFERENCES [dbo].[T_INTEZMENY] ([ID]);


GO
PRINT N'Creating [dbo].[FK_402829405_402829403]...';


GO
ALTER TABLE [dbo].[T_KEPESITES] WITH NOCHECK
    ADD CONSTRAINT [FK_402829405_402829403] FOREIGN KEY ([C_TANEVID]) REFERENCES [dbo].[T_TANEV] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4028297_4028296]...';


GO
ALTER TABLE [dbo].[T_KEPESITES_VEGZETTSEG] WITH NOCHECK
    ADD CONSTRAINT [FK_4028297_4028296] FOREIGN KEY ([C_KEPESITESID]) REFERENCES [dbo].[T_KEPESITES] ([ID]);


GO
PRINT N'Creating [dbo].[FK_4028298_4028296]...';


GO
ALTER TABLE [dbo].[T_KEPESITES_VEGZETTSEG] WITH NOCHECK
    ADD CONSTRAINT [FK_4028298_4028296] FOREIGN KEY ([C_VEGZETTSEGID]) REFERENCES [dbo].[T_VEGZETTSEG] ([ID]);


GO
PRINT N'Refreshing [dbo].[T_VEGZETTSEG_OSSZES]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[T_VEGZETTSEG_OSSZES]';


GO
PRINT N'Creating [dbo].[T_KEPESITES_OSSZES]...';


GO
PRINT N'Checking existing data against newly created constraints';


GO
ALTER TABLE [dbo].[T_KEPESITES] WITH CHECK CHECK CONSTRAINT [FK_4028294_4028295];

ALTER TABLE [dbo].[T_KEPESITES] WITH CHECK CHECK CONSTRAINT [FK_402829402_402829400];

ALTER TABLE [dbo].[T_KEPESITES] WITH CHECK CHECK CONSTRAINT [FK_402829405_402829403];

ALTER TABLE [dbo].[T_KEPESITES_VEGZETTSEG] WITH CHECK CHECK CONSTRAINT [FK_4028297_4028296];

ALTER TABLE [dbo].[T_KEPESITES_VEGZETTSEG] WITH CHECK CHECK CONSTRAINT [FK_4028298_4028296];


GO
PRINT N'Update complete.';


GO


CREATE VIEW [dbo].T_KEPESITES_OSSZES
        AS
        SELECT *
        FROM dbo.T_KEPESITES
GO


--------------------


CREATE PROCEDURE [dbo].[sp_AddNewSchemaViews]
		@IntezmenyId int,
		@IntezmenyAzonosito nvarchar(30),
		@TanevId int
AS
BEGIN

DECLARE @IntezmenyTemplate nvarchar(50) = 'KR_'+@IntezmenyAzonosito
DECLARE @Schema nvarchar(50) = @IntezmenyTemplate+'_Schema'
DECLARE @SchemaUser nvarchar(50) = @IntezmenyTemplate + '_user'
DECLARE @AktivTanev nvarchar(500)
DECLARE @Sql nvarchar(max)

/* VIEW: T_KEPESITES_VEGZETTSEG */

/*============================================================================*/
/* View : T_KEPESITES_VEGZETTSEG                                                 */
/*============================================================================*/
IF OBJECT_ID('['+@Schema+'].T_KEPESITES_VEGZETTSEG', 'V') IS NOT NULL BEGIN
    SET @Sql = N'EXEC sp_refreshview ''['+@Schema+'].T_KEPESITES_VEGZETTSEG'''
    EXEC sp_executesql @Sql

    IF OBJECT_ID('['+@Schema+'].T_KEPESITES_VEGZETTSEG_OSSZES', 'V') IS NOT NULL BEGIN
        SET @Sql = N'EXEC sp_refreshview ''['+@Schema+'].T_KEPESITES_VEGZETTSEG_OSSZES'''
        EXEC sp_executesql @Sql
    END
END
ELSE BEGIN
    /* Ha nem létezik a view, akkor létrehozzuk */
    IF (SELECT COUNT(1)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'T_KEPESITES_VEGZETTSEG' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME IN ( 'C_INTEZMENYID', 'C_TANEVID' )
            ) > 0
    BEGIN
        SET @AktivTanev = N' C_TANEVID = '+ CAST(@TanevId as nvarchar) +' AND '

        /* T_KEPESITES_VEGZETTSEG */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES_VEGZETTSEG
        AS
        SELECT *
        FROM dbo.T_KEPESITES_VEGZETTSEG
        WHERE '+@AktivTanev+' C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
        EXECUTE sp_executesql @Sql

        /* T_KEPESITES_VEGZETTSEG_OSSZES */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES_VEGZETTSEG_OSSZES
        AS
        SELECT *
        FROM dbo.T_KEPESITES_VEGZETTSEG
        WHERE C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
        EXECUTE sp_executesql @Sql
    END
    ELSE 
    BEGIN
        /* T_KEPESITES_VEGZETTSEG */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES_VEGZETTSEG
        AS
        SELECT *
        FROM dbo.T_KEPESITES_VEGZETTSEG'
        EXECUTE sp_executesql @Sql
    END
END

/*============================================================================*/
/* View : T_KEPESITES                                                             */
/*============================================================================*/
/* Package: Kreta */
IF OBJECT_ID('['+@Schema+'].T_KEPESITES', 'V') IS NOT NULL BEGIN
    SET @Sql = N'EXEC sp_refreshview ''['+@Schema+'].T_KEPESITES'''
    EXEC sp_executesql @Sql

    IF OBJECT_ID('['+@Schema+'].T_KEPESITES_OSSZES', 'V') IS NOT NULL BEGIN
        SET @Sql = N'EXEC sp_refreshview ''['+@Schema+'].T_KEPESITES_OSSZES'''
        EXEC sp_executesql @Sql
    END
END
ELSE BEGIN
    /* Ha nem létezik a view, akkor létrehozzuk */
    IF (SELECT COUNT(1)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = N'T_KEPESITES' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'C_TANEVID' 
            ) > 0
    BEGIN
        SET @AktivTanev = N' C_TANEVID = '+ CAST(@TanevId as nvarchar) +' AND '
    END
    ELSE 
    BEGIN
        SET @AktivTanev = N''
    END

    IF (SELECT COUNT(1)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = N'T_KEPESITES' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'C_INTEZMENYID' 
            ) > 0
    BEGIN
        /* T_KEPESITES */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES
        AS
        SELECT *
        FROM dbo.T_KEPESITES
        WHERE '+@AktivTanev+' (TOROLT = ''F'') AND C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
        EXECUTE sp_executesql @Sql

        /* T_KEPESITES_OSSZES */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES_OSSZES
        AS
        SELECT *
        FROM dbo.T_KEPESITES
        WHERE C_INTEZMENYID = '+CAST(@IntezmenyId as nvarchar)
        EXECUTE sp_executesql @Sql
    END
    ELSE 
    BEGIN
        /* T_KEPESITES */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES
        AS
        SELECT *
        FROM dbo.T_KEPESITES
        WHERE '+@AktivTanev+' (TOROLT = ''F'')'
        EXECUTE sp_executesql @Sql

        /* T_KEPESITES_OSSZES */
        SET @Sql = N'CREATE VIEW ['+@Schema+'].T_KEPESITES_OSSZES
        AS
        SELECT *
        FROM dbo.T_KEPESITES'
        EXECUTE sp_executesql @Sql
    END
END

END

GO


--------------------


DECLARE intezmenytanevekCursor CURSOR LOCAL FOR 
	SELECT T_INTEZMENY.ID, T_INTEZMENY.C_AZONOSITO, T_TANEV.ID
		FROM T_INTEZMENY
		INNER JOIN T_TANEV ON T_TANEV.C_INTEZMENYID = T_INTEZMENY.ID
		WHERE T_TANEV.C_AKTIV = 'T' AND T_INTEZMENY.TOROLT = 'F'
		ORDER BY T_INTEZMENY.ID

DECLARE @intezmenyId int, @intezmenyAzonosito nvarchar(30), @tanevId int

OPEN intezmenytanevekCursor  
FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @intezmenyAzonosito, @tanevId

WHILE @@FETCH_STATUS = 0 
BEGIN  
	PRINT 'INTÉZMÉNY ID: ' + CAST(@intezmenyId AS NVARCHAR(10)) + ', INTÉZMÉNY AZONOSÍTÓ: ' + CAST(@intezmenyAzonosito AS NVARCHAR(30)) + ', TANÉV ID: ' + CAST(@tanevId AS NVARCHAR(10))
    EXEC sp_AddNewSchemaViews @IntezmenyId = @intezmenyId, @IntezmenyAzonosito = @intezmenyAzonosito, @TanevId = @tanevId

	FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @intezmenyAzonosito, @tanevId
END  

CLOSE intezmenytanevekCursor  
DEALLOCATE intezmenytanevekCursor 

GO


--------------------


PRINT '--- intézményi view-k (dbo táblákkal megegyezõ nevû és a tábla_OSSZES nevûek) frissítése ---'

DECLARE  @viewname nvarchar(100), @pureview nvarchar(100)

DECLARE semaviewk CURSOR LOCAL FOR 
  SELECT '['+TABLE_SCHEMA + '].[' + TABLE_NAME + ']', TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'VIEW' and TABLE_SCHEMA LIKE 'KR[_]%[_]Schema'
  AND TABLE_NAME in ('T_VEGZETTSEG', 'T_VEGZETTSEG_OSSZES') -- ha csak néhány VIEW frissült, akkor a gyorsabb futásért ide fel lehet sorolni
  ORDER BY TABLE_SCHEMA, TABLE_NAME

OPEN semaviewk
FETCH NEXT FROM semaviewk INTO @viewname, @pureview

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @viewname
    EXEC sp_refreshview @viewname

    FETCH NEXT FROM semaviewk INTO @viewname, @pureview
END             

CLOSE semaviewk
DEALLOCATE semaviewk
GO


--------------------


DROP PROCEDURE [dbo].[sp_AddNewSchemaViews]
GO


EXEC dev.sp_Global_GenerateAsyncAuditTriggerAll
GO

EXEC dev.sp_Global_GenerateAsyncAuditProcedureAll
GO

EXEC dev.sp_Global_GenerateAsyncAuditAuditPocessing
GO

ALTER QUEUE auditLog.[AuditQueue]
WITH ACTIVATION
( 
    STATUS = ON,
    PROCEDURE_NAME = auditLog.usp_AuditProcessing,
    MAX_QUEUE_READERS = 10,
    EXECUTE AS 'Kreta_tech_user'
);

GO


INSERT INTO T_KEPESITES (C_KEPESITESTIPUSID, C_INTEZMENYID, C_TANEVID, TOROLT)
SELECT
   ID
  ,C_INTEZMENYID
  ,C_TANEVID
  ,TOROLT 
FROM T_DICTIONARYITEMBASE 
WHERE C_TYPE = 'TargykategoriaTipus'

GO
