SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[p_sys_md_create]
	@for_install BIT = 0
AS 
    BEGIN
    
		set nocount on;


		declare @db_name nvarchar(128)
		set @db_name = DB_NAME()

		select @db_name

		exec p_sys_database_objects_create @db_name = @db_name

		-- construire les FK pour les tables de cpl
		exec p_sys_md_create_fk_cpl

		
		-- présence des champs de type identity - non autorisée
		IF @for_install = 0 and EXISTS	(
					SELECT	[schema] = s.name,
							[table] = t.name
					FROM	sys.schemas AS s
							INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
					WHERE	EXISTS	(
									SELECT	1 
									FROM	sys.identity_columns
									WHERE	[object_id] = t.[object_id]
									)
					and		t.name != 'sysdiagrams'
					)	
		BEGIN
			RAISERROR('Identity fields are not authorised ! ', 16, 1)		
		
			RETURN
		END	
		-- utilisation des types de champs qui ne sont pas autorisés

		IF EXISTS (
					select	1 
					FROM	sysobjects o
							INNER JOIN syscolumns c ON o.id = c.id
							INNER JOIN systypes t ON c.xusertype = t.xusertype
							LEFT JOIN syscomments cmt ON c.cdefault = cmt.id
					WHERE	o.type = 'U'
					AND		o.name NOT IN ( 'dtproperties', 'sysdiagrams', 'act_cpl', 'pol_cpl', 'claim_cpl', 'ris_cpl', 'claim_actors_cpl' )	
					AND		c.iscomputed = 0
					AND		not exists ( SELECT 1 FROM md_types where typ_type_name = t.name and (typ_authorised != 0 OR @for_install = 1)	) 	
					)   
		BEGIN

				SELECT	TABLE_NAME = o.name ,
						FIELD_NAME = c.name ,
						LENGTH = c.length , 
						TYPE = t.name ,
						NULLABLE = c.isnullable
				FROM	sysobjects o
						INNER JOIN syscolumns c ON o.id = c.id
						INNER JOIN systypes t ON c.xusertype = t.xusertype
						LEFT JOIN syscomments cmt ON c.cdefault = cmt.id
				WHERE	o.type = 'U'
				AND		o.name NOT IN ( 'dtproperties', 'sysdiagrams', 'act_cpl', 'pol_cpl', 'claim_cpl', 'ris_cpl', 'claim_actors_cpl' )	
				AND		c.iscomputed = 0
				AND		not exists ( SELECT 1 FROM md_types where typ_type_name = t.name and (typ_authorised != 0 OR @for_install = 1)) 
				
			RAISERROR('Unauthorised type for the fields listed above this message ! ', 16, 1)
			
			RETURN				
		
		END
            
		
		
    
		--grids without details?
		IF EXISTS (
					select	1 
					from	md_grids
					where not exists (select 1 from md_grids_fields where grdf_grid_name = grd_grid_name)		
					)    
		BEGIN
		
			select	* 
			from	md_grids
			where not exists (select 1 from md_grids_fields where grdf_grid_name = grd_grid_name)		
		
			RAISERROR('No fields (md_grids_fields) for the grids listed above this message ! ', 16, 1)
			
			RETURN
		
		END
    
		
		select 'Fields that must be deleted but still used in grids - you must delete grids first'
		select	* 
		from	md_fields
		where	fld_virtual = 0
		and		not exists ( select 1 from sysobjects o where o.name = fld_table_name ) -- and o.type = 'u')
		and (	exists(select 1 from md_grids where fld_table_name = grd_value_table_name and fld_field_name = grd_value_field_name)
				or exists(select 1 from md_grids where fld_table_name = grd_display_table_name and fld_field_name = grd_display_field_name)
				or exists(select 1 from md_grids_fields where fld_table_name = grdf_table_name and fld_field_name = grdf_field_name)
			)
			
		select 'Tables that must be deleted but still used in grids - you must delete grids first'
		select	* 
		from	md_tables
		where	tbl_virtual = 0
		and		not exists ( select 1 from sysobjects o where o.name = tbl_table_name and o.type = 'u')
		and (	exists(select 1 from md_grids where tbl_table_name = grd_value_table_name)
				or exists(select 1 from md_grids where tbl_table_name = grd_display_table_name)
				or exists(select 1 from md_grids_fields where tbl_table_name = grdf_table_name)
			)
			
		select 'Fields that must be deleted but still used in grids - you must delete grids first'
		select grdf_grid_name,grdf_table_name,grdf_field_name, *
		from md_fields 
			inner join md_grids_fields on fld_table_name = grdf_table_name and fld_field_name = grdf_field_name
			inner join md_tables on tbl_table_name = fld_table_name
        WHERE   fld_virtual = 0
				AND tbl_virtual = 0
                AND fld_table_name NOT IN ( 'act_cpl', 'pol_cpl', 'claim_cpl',
                                            'ris_cpl', 'claim_actors_cpl' )
                AND NOT EXISTS ( SELECT 1
                                 FROM   sysobjects o
                                        INNER JOIN syscolumns c ON o.id = c.id
                                 WHERE  o.name = fld_table_name
                                        AND c.name = fld_field_name )
	
	-- add tables
	
		-- ATENTIE
		-- in cazul in care o tabela X contine campuri cu mai multe prefixuri, 
		-- insert-ul de mai jos o sa genereze o eroare (multiplicarea liniilor in join-ul o cu t)
        INSERT  INTO md_tables
                ( tbl_table_name ,
                  tbl_l1,
                  tbl_prefix
                )
                SELECT  name ,
                        '?',
                        t.PREFIX
                FROM    sysobjects o
						INNER JOIN (
									select	distinct TBL = o.name, PREFIX = substring(c.name, 1, CHARINDEX('_', c.name)) from sysobjects o 
											inner join syscolumns c on o.id = c.id
									where	o.type = 'U'
									AND		o.name NOT IN ( 'dtproperties', 'sysdiagrams' )
									) as t on o.name = t.TBL					
                WHERE   type = 'U'
                        AND name NOT IN ( 'dtproperties', 'sysdiagrams' )
                        AND NOT EXISTS ( SELECT 1
                                         FROM   md_tables
                                         WHERE  tbl_table_name = name )
                                         
		/*
		update prefix
		executia update-ului de mai jos a fost necesara o singura data, la adaugarea campului tbl_prefix
		tabelele adaugate ulterior vor fi inserate in tabela md_tables cu valoarea corecta a tbl_prefix
		                  
		UPDATE	md_tables
		set		tbl_prefix = PREFIX
		FROM	md_tables t
				INNER JOIN (
							select	distinct TBL = o.name, PREFIX = substring(c.name, 1, CHARINDEX('_', c.name)) from sysobjects o 
									inner join syscolumns c on o.id = c.id
									inner join md_tables t on t.tbl_table_name = o.name
							) as tmp on t.tbl_table_name = tmp.TBL
         */                                

	
	-- update fields
        UPDATE  md_fields
        SET     fld_type = T.TYPE ,
                fld_size = T.LENGTH ,
                fld_nullable = T.NULLABLE ,
                fld_virtual = 0
        FROM    md_fields f
                INNER JOIN ( SELECT TABLE_NAME = o.name ,
                                    FIELD_NAME = c.name ,
                                    LENGTH = c.length , 
					--DEF = dbo.DefaultToString(cmt.text), 
                                    TYPE = t.name ,
                                    NULLABLE = c.isnullable
                             FROM   sysobjects o
                                    INNER JOIN syscolumns c ON o.id = c.id
                                    INNER JOIN systypes t ON c.xusertype = t.xusertype
                                    LEFT JOIN syscomments cmt ON c.cdefault = cmt.id
                             WHERE  o.type = 'U'
                             AND	c.iscomputed = 0
                             AND	o.name NOT IN ( 'dtproperties', 'sysdiagrams' )
							 AND	o.name NOT IN ( 'act_cpl', 'pol_cpl', 'claim_cpl', 'ris_cpl', 'claim_actors_cpl' )
                           ) AS T ON f.fld_table_name = t.TABLE_NAME
                                     AND f.fld_field_name = t.FIELD_NAME
	





	-- delete fields

		-- first delete from md_dictionaries_fields
		delete mdf
		from md_dictionaries_fields mdf
			inner join md_fields on		mdf.dictf_table_name = fld_table_name
									and mdf.dictf_field_name = fld_field_name
		WHERE   fld_virtual = 0
                AND fld_table_name NOT IN ( 'act_cpl', 'pol_cpl', 'claim_cpl', 'ris_cpl', 'claim_actors_cpl' )
                AND NOT EXISTS ( SELECT 1
                                 FROM   sysobjects o
           INNER JOIN syscolumns c ON o.id = c.id
                                 WHERE  o.name = fld_table_name
                                        AND c.name = fld_field_name )

		-- then from md_fields
        DELETE  md_fields
        WHERE   fld_virtual = 0
                AND fld_table_name NOT IN ( 'act_cpl', 'pol_cpl', 'claim_cpl',
                                            'ris_cpl', 'claim_actors_cpl' )
                AND NOT EXISTS ( SELECT 1
                                 FROM   sysobjects o
                                        INNER JOIN syscolumns c ON o.id = c.id
                                 WHERE  o.name = fld_table_name
                                        AND c.name = fld_field_name )
	

	-- add fields
        INSERT  INTO md_fields
                ( fld_table_name ,
                  fld_field_name ,
                  fld_l1 ,
                  fld_l2 ,
                  fld_l3 ,
                  fld_dsc_l1 ,
                  fld_dsc_l2 ,
                  fld_dsc_l3 ,
                  fld_type ,
                  fld_size ,
                  fld_nullable ,
                  fld_read_only ,
                  fld_virtual ,
                  fld_ordinal_position
                )
                SELECT  o.name ,
                   c.name ,
                        '?' ,
                        NULL ,
                        NULL ,
         NULL ,
                        NULL ,
                        NULL ,
                        t.name ,
                        c.length ,
                        c.isnullable ,
                        0 ,
                        0 ,
                        c.colorder
                FROM    sysobjects o
                        INNER JOIN syscolumns c ON o.id = c.id
                        INNER JOIN systypes t ON c.xusertype = t.xusertype
                        LEFT JOIN syscomments cmt ON c.cdefault = cmt.id
                WHERE   o.type = 'U'
                        AND o.name NOT IN ( 'dtproperties', 'sysdiagrams' )
                        AND o.name NOT IN ( 'act_cpl', 'pol_cpl', 'claim_cpl',
                                            'ris_cpl', 'claim_actors_cpl' )
         AND NOT EXISTS ( SELECT 1
                                         FROM   md_fields
                                         WHERE  fld_table_name = o.name
                                                AND fld_field_name = c.name )		
          
                          
                                                
	-- add fields lx
        INSERT  INTO md_fields
                ( fld_table_name ,
    fld_field_name ,
                  fld_l1 ,
                  fld_l2 ,
                  fld_l3 ,
                  fld_dsc_l1 ,
                  fld_dsc_l2 ,
                  fld_dsc_l3 ,
                  fld_type ,
                  fld_size ,
                  fld_nullable ,
                  fld_read_only ,
                  fld_virtual ,
                  fld_ordinal_position
                )
                SELECT  o.name ,
                        LEFT(c.name, LEN(c.name) - 3) + '_lx' ,
                        '?' ,
                        NULL ,
                        NULL ,
                        NULL ,
                        NULL ,
                        NULL ,
                        t.name ,
                        c.length ,
                        c.isnullable ,
                        0 ,
                        1 ,
                        99
                FROM    sysobjects o
                        INNER JOIN syscolumns c ON o.id = c.id
                        INNER JOIN systypes t ON c.xusertype = t.xusertype
                        LEFT JOIN syscomments cmt ON c.cdefault = cmt.id
                WHERE   o.type = 'U'
                        AND o.name NOT IN ( 'dtproperties', 'sysdiagrams' )
                        AND o.name NOT IN ( 'act_cpl', 'pol_cpl', 'claim_cpl', 'ris_cpl', 'claim_actors_cpl' )
                        AND RIGHT(c.name, 3) = '_l1'
                        AND NOT EXISTS ( SELECT 1
                                         FROM   md_fields
                                         WHERE  fld_table_name = o.name
                                                AND fld_field_name = LEFT(c.name,
                                                              LEN(c.name) - 3)
                                                + '_lx' )	                                               


	-- maj pk
        UPDATE  md_fields
        SET     fld_pk = 0
	
	
        UPDATE  md_fields
        SET     fld_pk = 1
        FROM    md_fields f
                INNER JOIN ( SELECT	DISTINCT
                                    pk.table_name ,
                                    c.column_name
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
                                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON c.TABLE_NAME = pk.TABLE_NAME
 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
                             WHERE  pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
                           ) AS pk ON f.fld_table_name = pk.table_name
                                      AND f.fld_field_name = pk.column_name


	-- maj defaults
        UPDATE  dbo.md_fields
        SET     fld_default = '0'
		WHERE   fld_type = 'ais_bit'

        UPDATE  dbo.md_fields
        SET     fld_default = REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '''', ''),
                                              '(', ''), ')', '')
        FROM    dbo.md_fields F
                INNER JOIN ( SELECT *
                             FROM   INFORMATION_SCHEMA.COLUMNS
                             WHERE  COLUMN_DEFAULT IS NOT NULL
                                    AND COLUMN_DEFAULT NOT LIKE 'create default%'
                           ) AS T ON F.fld_table_name = T.TABLE_NAME
                                     AND F.fld_field_name = T.COLUMN_NAME
                                     

	-- nvarchar
	
        UPDATE  dbo.md_fields
        SET     fld_size = t.prec
        FROM    dbo.md_fields F
                INNER JOIN ( SELECT TBL = o.name ,
                                    COL = c.name ,
                                    c.prec
                             FROM   sysobjects o
                                    INNER JOIN syscolumns c ON o.id = c.id
                           ) AS T ON F.fld_table_name = T.TBL
                                     AND F.fld_field_name = t.COL
   WHERE   fld_type = 'nvarchar'
        
        
     -- 
     
		delete from md_fields 
		where not exists ( select 1 from sysobjects o where o.name = fld_table_name and o.type = 'u')
		and	fld_virtual = 0
		
		--/*
		delete from md_fields 
		where fld_virtual = 0
		and not exists (select 1 from syscolumns where name = fld_field_name) 
		and	fld_field_name not like '%lx'
		--*/
		
		delete from md_tables
		where		not exists ( select 1 from sysobjects o where o.name = tbl_table_name and o.type = 'u')
				and tbl_virtual = 0

		-- begin creare campuri virtuale pentru view-urile TT
		declare @FIELDS TABLE(TABLE_NAME sysname, COLUMN_NAME sysname, ORIGINAL_COLUMN_NAME sysname, TABLE_PREFIX sysname)

		insert into @FIELDS(	TABLE_NAME, COLUMN_NAME, ORIGINAL_COLUMN_NAME,
								TABLE_PREFIX) 
		select					TABLE_NAME, COLUMN_NAME, right(COLUMN_NAME, len(COLUMN_NAME) - charindex('__',COLUMN_NAME) - 1 ),
								left(COLUMN_NAME, charindex('__',COLUMN_NAME) + 1)
		from INFORMATION_SCHEMA.COLUMNS
		where TABLE_NAME like 'vw_tt_%' and charindex('__',COLUMN_NAME) between 1 and 7 -- nu le luam pe cele care au '__' mai la coada

		--inseram lx-urile
		insert into @FIELDS(	TABLE_NAME, COLUMN_NAME, ORIGINAL_COLUMN_NAME,
								TABLE_PREFIX)
		select					TABLE_NAME, replace(COLUMN_NAME,'_l1','_lx'), replace(right(COLUMN_NAME, len(COLUMN_NAME) - charindex('__',COLUMN_NAME) - 1 ),'_l1','_lx'),
								left(COLUMN_NAME, charindex('__',COLUMN_NAME) + 1)
		from @FIELDS
		where right(COLUMN_NAME,3) = '_l1'


		insert into md_tables(	tbl_table_name, tbl_l1, tbl_l2, tbl_l3, tbl_prefix,		tbl_virtual)
		select					TABLE_NAME,		'?',	null,	null,	TABLE_PREFIX,	1
		from 
			(
				select distinct		TABLE_NAME, TABLE_PREFIX
				from @FIELDS
				where not exists(select 1 from md_tables where tbl_table_name = TABLE_NAME)
			) as t
			

		insert into md_fields (
			fld_table_name,		fld_field_name,	fld_l1,					fld_l2,			fld_l3,
			fld_dsc_l1,			fld_dsc_l2,		fld_dsc_l3,				fld_type,		fld_size,
			fld_nullable,		fld_read_only,	fld_virtual,			fld_mask_code,	fld_default,
			fld_link_table_name,fld_pk,			fld_ordinal_position,	fld_grid_name,	fld_grid_hidden )
		select
			TABLE_NAME,			COLUMN_NAME,	fld_l1,					fld_l2,			fld_l3,
			fld_dsc_l1,			fld_dsc_l2,		fld_dsc_l3,				fld_type,		fld_size,
			fld_nullable,		fld_read_only,	1,						fld_mask_code,	fld_default,
			fld_link_table_name,fld_pk,			fld_ordinal_position,	fld_grid_name,	fld_grid_hidden
		from @FIELDS
			inner join md_fields on fld_field_name = ORIGINAL_COLUMN_NAME
		where not exists(select 1 from md_fields f where f.fld_table_name = TABLE_NAME and f.fld_field_name = COLUMN_NAME)
		
		-- end creare campuri virtuale pentru view-urile TT
		
		-- date fields
		UPDATE	md_fields
		set		fld_mask_code = 'date'
		where	fld_type = 'date' 
		and		fld_mask_code is null
		
		-- datetime fields
		UPDATE	md_fields
		set		fld_mask_code = 'dt'
		where	fld_type = 'datetime' 
		and		fld_mask_code is null
		
		--creare campuri virtuale pentru sume <nume_camp> + '_sum' pentru toate campurile ais_decimal2
		-- 14.08.2014 - Nu mai facem asta, erau nevoie pentru fuziunea Word, dar nu le mai putem folosi din cauza regiunilor imbricate
		/*
		insert into md_fields(
				fld_table_name,			fld_field_name,			/*fld_rowver,*/	fld_l1,			fld_l2,				fld_l3,
				fld_dsc_l1,				fld_dsc_l2,				fld_dsc_l3,		fld_type,		fld_size,			fld_nullable,
				fld_read_only,			fld_virtual,			fld_mask_code,	fld_default,	fld_link_table_name,fld_pk,
				fld_ordinal_position,	fld_grid_name,			fld_grid_hidden )


		select	fld_table_name,			fld_field_name + '_sum',/*fld_rowver,*/	fld_l1 + ' sum','Cumul ' + fld_l2,	fld_l3,
				fld_dsc_l1,				fld_dsc_l2,				fld_dsc_l3,		fld_type,		fld_size,			fld_nullable,
				fld_read_only,			1,						fld_mask_code,	fld_default,	fld_link_table_name,fld_pk,
				fld_ordinal_position,	fld_grid_name,			fld_grid_hidden
		from md_fields d
		where		fld_type = 'ais_decimal2' 
				and charindex('total', fld_table_name) < 1 
				and charindex('total', fld_field_name) < 1
				and fld_field_name not like '%_sum'
				and not exists (	select 1 
									from md_fields s 
									where		s.fld_table_name = d.fld_table_name 
											and s.fld_field_name = d.fld_field_name + '_sum' )
		*/
		
		--end creare campuri virtuale pentru sume <nume_camp> + '_sum' pentru toate campurile ais_decimal2
		
		
		
		-- MAJ ordinal position
		UPDATE	f
		set		f.fld_ordinal_position = c.colorder
		from	md_fields f
				inner join sysobjects o on o.name = f.fld_table_name
                inner join syscolumns c ON o.id = c.id and c.name = f.fld_field_name

		-- mise à jour de l'expression d'affichage pour les liste derroulantes
		update mg 
			set grd_display_combo_expression = grd_value_field_name + ' + ' + '''' + ' - ' +'''' + ' + ' + grd_display_field_name
			--select grd_value_field_name + ' + ' + '''' + ' - ' +'''' + ' + ' + grd_display_field_name, *
		from md_grids mg
		where		grd_display_combo_expression is null 
				and grd_display_field_name is not null 
				and grd_value_field_name is not null
				and isnull(grd_value_field_name,'') <> isnull(grd_display_field_name,'')
                      
		
		
		exec p_sys_md_generate_bit_defaults
		
		exec dbo.p_sys_md_maj_labels
		
		exec p_sys_md_create_ck_string_pk 1, 1

		exec p_sys_tables_create_view_without_rowver_computed
		
		exec p_sys_refresh_all_views
		
		exec p_sys_RefreshSysDepends
		
		
		
		exec p_sys_refresh_sp
    END










GO
