SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_sys_md_csharp_generate_constants] 
AS
BEGIN
	SET NOCOUNT ON;

	declare @sourceControlledObjetsHasData bit = case when exists(select 1 from nm_reference_database_objects) then 1 else 0 end 
	
	declare @tbl IdTextTableType --TABLE(const varchar(2000))
	
	print 'using System;'
	print 'using System.Collections.Generic;'
	print 'using System.Data;'
	
	
	print 'namespace Ais.Data'
	
	print '{'
	
	print Char(9) + 'public partial class DBC'
	print Char(9) + '{'
	
	-- the tables, views, stored procedures and functions
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' +	case o.type
															when 'U' then ''
															when 'V' then ''
															when 'P' then ''
															when 'FN' then ''
														end +
		upper(o.name) + ' = "' + o.name + '";' as t
	from sysobjects o
		inner join sys.schemas s on s.schema_id = o.uid
		left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = s.name and  nrdo.nrdo_ObjectName =  o.name and nrdo.nrdo_ObjectType = o.type collate database_default
	where	o.type in ('U','V','P','FN') 
	and		o.name not like 'custom%'
	and		(@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)
	
	union -- user defined table types
	
	select	
		Char(9) + char(9) + 'public const string ' +
		upper(o.name) + ' = "' + o.name + '";' as t
	from sys.table_types o 
		inner join sys.schemas s on s.schema_id = o.schema_id
		left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = s.name and nrdo.nrdo_ObjectName =  o.name and nrdo.nrdo_ObjectType = 'UD'
	where is_user_defined = 1 and is_table_type  = 1 and (@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)
	
	-- the tables, views, stored procedures and functions fields
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' +	case o.type
															when 'U' then ''
															when 'V' then ''
															when 'P' then 'PAR_'
															when 'FN' then 'PAR_'
														end +
		/*upper(o.name) + '_'*/ +	case left(c.name,1)
									when '@' then UPPER(SUBSTRING ( c.name ,2 , len(c.name) - 1 ))
									else UPPER(c.name)
								end + ' = "' + 
								case left(c.name,1)
									when '@' then SUBSTRING ( c.name ,2 , len(c.name) - 1 )
									else c.name
								end + '";'
		
		
	from sysobjects o
		inner join sys.schemas s on s.schema_id = o.uid
		inner join syscolumns c on o.id = c.id
		left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = s.name and nrdo.nrdo_ObjectName = o.name and nrdo.nrdo_ObjectType = o.type collate database_default
	where o.type in ('U','V','P','FN') and isnull(c.name,'') <> ''
	and	o.name not like 'sp_%'
	and	o.name not like 'fn_%'
	and	o.name not like 'custom%'
	and o.name not in ( 'sysdiagrams')
	 
	-- DGE 08/06/2015, 0000295: Pouvoir définir l'usage au niveau des contrats
	-- exclure les champs de compléments, sauf les champs de compléments "fixes": classier, zonnier, usage
	and (
			c.name in	(
						'pcpl_usage_id', 
						'rcpl_class_id', 'rcpl_zone_id', 'rcpl_usage_id'
						)
			OR 
			c.name not in	(
							select	distinct t.tbl_prefix + f.fld_field_name
							from	md_fields_cpl f
									inner join md_tables t on t.tbl_table_name = f.fld_table_name)
		)
	and		(@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)
	
	union -- fields of user defined table types
	
	select	
		Char(9) + char(9) + 'public const string ' +	
		/*upper(o.name) + '_'*/ +	case left(c.name,1)
									when '@' then UPPER(SUBSTRING ( c.name ,2 , len(c.name) - 1 ))
									else UPPER(c.name)
								end + ' = "' + 
								case left(c.name,1)
									when '@' then SUBSTRING ( c.name ,2 , len(c.name) - 1 )
									else c.name
								end + '";'
		
	from sys.columns c 
		inner join sys.table_types t on c.object_id = t.type_table_object_id
		inner join sys.schemas s on s.schema_id = t.schema_id
		left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = s.name and nrdo.nrdo_ObjectName = t.name and nrdo.nrdo_ObjectType = 'UD' collate database_default
	where is_user_defined = 1 and is_table_type  = 1 and (@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)
	
	-- the name_X tables fields
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' +	case o.type
															when 'U' then ''
															when 'V' then ''
															when 'P' then ''
															when 'FN' then ''
														end +
		/*upper(o.name) + '_'*/ +	case left(c.name,1)
									when '@' then UPPER(SUBSTRING ( c.name ,2 , len(c.name) - 2 ) + 'x' )
									else UPPER(SUBSTRING ( c.name ,1 , len(c.name) - 1 ) + 'x' )
								end + ' = "' + 
								case left(c.name,1)
									when '@' then SUBSTRING ( c.name ,2 , len(c.name) - 2 ) + 'x'
									else SUBSTRING ( c.name ,1 , len(c.name) - 1 ) + 'x'
								end + '";' as t
		
		
	from sysobjects o
		inner join syscolumns c on o.id = c.id 
	where o.type in ('U','V','P','FN') and isnull(c.name,'') like '%_l1'

	
	-- virtual fields not used
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' + UPPER(fld_field_name) + ' = "' + fld_field_name + '";'
	from md_fields f
	where	f.fld_virtual != 0

	-- virtual tables
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' + UPPER(tbl_table_name) + ' = "' + tbl_table_name + '";'
	from md_tables t
	where	t.tbl_virtual != 0
		
	-- the grids	
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' + upper(grd_grid_name) + ' = "' + grd_grid_name + + '";' as t
	from md_grids 
	
	-- the grids fields
	insert into @tbl (txt)
	select	
		Char(9) + char(9) + 'public const string ' /*+ upper(grdf_grid_name) + '_'*/ + upper(grdf_field_name) +  ' = "' + grdf_field_name + + '";' as t
	from md_grids_fields
	
	-- the udf types
	insert into @tbl (txt)
	SELECT Char(9) + char(9) + 'public const string ' + UPPER(typ_type_name) + ' = "' + typ_type_name + '";'
	FROM md_types
	
	-- the actor_types
	insert into @tbl (txt)
	SELECT Char(9) + char(9) + 'public const string ACTOR_TYPE_' + UPPER(at_type_code) + ' = "' + at_type_code + '";'
	FROM act_types
	where at_sys = 1
	
	-- masks
	insert into @tbl (txt)
	SELECT Char(9) + char(9) + 'public const string MASK_' + UPPER(msk_mask_code) + ' = "' + msk_mask_code + '";'
	FROM md_masks
	where msk_sys = 1	
	
	-- masks format strings 
	insert into @tbl (txt)
	SELECT Char(9) + char(9) + 'public const string MASK_FS_' + UPPER(msk_mask_code) + ' = "' + msk_format_string + '";'
	FROM md_masks
	where msk_sys = 1 AND msk_format_string IS NOT NULL 
	
	-- tables prefix
	insert into @tbl (txt)
	SELECT Char(9) + char(9) + 'public const string PREFIX_' + UPPER(tbl_table_name) + ' = "' + tbl_prefix + '";'
	FROM md_tables
	where tbl_prefix is not null
	
			
	--select distinct txt as [//constants for the database items]
	--from @tbl 
	--order by txt
	
	declare @tbl2 IdTextTableType

	insert into @tbl2(txt)
	select distinct txt
	from @tbl
	order by txt

	-- print tables, fields, etc - constants
	exec p_sys_md_csharp_print_idtexttable @tbl = @tbl2, @add_comma_after = 0
	
	-- print dictionary with link between tables prefixes and tables names
	declare @tblp IdTextTableType
	
	insert into @tblp(txt)
	select char(9) + char(9) + char(9) + '{"' + tbl_prefix + '","' + tbl_table_name + '"}'
	FROM md_tables
	where tbl_prefix is not null and tbl_table_name not in ('md_fields_cpl') -- 'md_fields_cpl' are acelasi prefix cu 'md_fields'
	order by tbl_prefix
	
	
	exec p_sys_md_csharp_print_table_dictionary @tbl = @tblp, @dictionary_name = 'TablesPrefixes'


	
	-- print dictionary with link between tables names prefixes and tables prefixes
	delete from @tblp
	
	insert into @tblp(txt)
	select char(9) + char(9) + char(9) + '{"' + tbl_table_name + '","' + tbl_prefix + '"}'
	FROM md_tables
	where tbl_prefix is not null and tbl_table_name not in ('md_fields_cpl') -- 'md_fields_cpl' are acelasi prefix cu 'md_fields'
	order by tbl_table_name
	
	exec p_sys_md_csharp_print_table_dictionary @tbl = @tblp, @dictionary_name = 'PrefixesTables'
	
	
	
	--
	-- start table types

	DECLARE @tablename sysname
	declare @tableid int
	declare @columnname sysname
	declare @typename sysname
	declare @strtables varchar(max)
	declare @strtable varchar(max)
	declare @strcol varchar(max)
	declare @idxcol int
	declare @tables_type varchar(max)
	declare @is_table_type bit

	set @strtables = char(9) + char(9) + 'private static Dictionary<string, SqlTableType> TablesTypes = new Dictionary<string, SqlTableType>()' + char(13) + char(10)
	set @strtables = @strtables + char(9) + char(9) +	'{' + char(13) + char(10)
	print @strtables
	
	set @tables_type = ''

	DECLARE cur CURSOR FOR 
	select t.name, type_table_object_id, 1
	from sys.table_types t
		inner join sys.schemas s on s.schema_id = t.schema_id 
		left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = s.name and nrdo.nrdo_ObjectName = t.name and nrdo.nrdo_ObjectType = 'UD'
	where (@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)
	union
	select o.name,type_table_object_id = o.object_id	, 0
	from sys.objects o
		inner join sys.schemas s on s.schema_id = o.schema_id 
		left join nm_reference_database_objects nrdo on nrdo.nrdo_SchemaName = s.name and nrdo.nrdo_ObjectName =  o.name and nrdo.nrdo_ObjectType = 'U'
	where type_desc in ('USER_TABLE'  /*, 'VIEW' */) and (@sourceControlledObjetsHasData = 0 or nrdo.nrdo_ObjectName is not null)


	OPEN cur

	FETCH NEXT FROM cur 
	INTO @tablename, @tableid, @is_table_type

	WHILE @@FETCH_STATUS = 0
	BEGIN
			set @tables_type = @tables_type + Char(9) + char(9) + 'public const string TABLE_TYPE_' + upper(@tablename) + ' = "' + @tablename + + '";' + char(13) + char(10) 
		
			--{ "A", new SqlTableType() { Name = "A", Columns = new Dictionary<string,SqlTableTypeColumn>() { { "C", new SqlTableTypeColumn() { Name = "C", TypeName = "string" }  }  } }}
			
			set @strtable = char(9) + char(9) + char(9) + '{ "' + @tablename + '", new SqlTableType() { Name = "' + @tablename + '", Columns = new Dictionary<string,SqlTableTypeColumn>() { '
			print @strtable
			
			set @idxcol = 1
			set @strcol = ''
		
			DECLARE curcol CURSOR FOR 
			select columnname = c.name, typename = ty.name
			from sys.columns c
			inner join sys.types ty on c.user_type_id = ty.user_type_id
			where c.object_id = @tableid
			/*
			DGE 27/02/2013
			La procédure génère la structure en tenant compte de toutes les colonnes (table sys.columns), ce qui n'est pas correct.
			Pour les tables dynamiques, il faut recupérer uniquement les colonnes fixes (ex: pour ris_cpl, prendre
			uniquement rcpl_class_id, rcpl_risk_situation_id, rcpl_usage_id, rcpl_zone_id).
			*/
			and		1 =		case
							when @is_table_type = 0 and not exists (	select	1 
																		from	md_fields 
																		where	fld_table_name = @tablename 
																		and		fld_field_name = c.name) then 0
							else 1
							end

			OPEN curcol

			FETCH NEXT FROM curcol 
			INTO @columnname, @typename

			WHILE @@FETCH_STATUS = 0
			BEGIN
			
				set @strcol = char(9) + char(9) + char(9) + char(9) + '{ "' + @columnname + '", new SqlTableTypeColumn() { Name = "' + @columnname + '", TypeName = "' + @typename + '" } } '
				
				FETCH NEXT FROM curcol 
				INTO @columnname, @typename
				
				if @@FETCH_STATUS = 0
				begin
					set @strcol = @strcol + ', '
				end 
				
				print @strcol
				
			END 
			CLOSE curcol
			DEALLOCATE curcol
		
		set @strtable = char(9) + char(9) + char(9) + '} } }' 
		
		FETCH NEXT FROM cur 
		INTO @tablename, @tableid, @is_table_type
		
		if @@FETCH_STATUS = 0
		begin
			set @strtable = @strtable + ', '
		end
		set @strtable = @strtable + char(13) + char(10) 
		
		print @strtable
		
	    
	END 
	CLOSE cur;
	DEALLOCATE cur;

	--set @strtables = @strtables + char(9) + char(9) + '};'
	set @strtables = char(9) + char(9) + '};'
	print @strtables
	
	--print @tables_type
 
	-- end table types
	
	exec p_sys_md_csharp_generate_constants_nm_pol_operations

	
	print char(9) + '}' -- end class DBC
	
	
	-- print enums
	declare @sql nvarchar(max)
	declare @table_name sysname
	declare @field_name sysname
	declare @enum_name varchar(64)
	declare @id int
	declare @min_id int
	declare @max_id int
	declare @other_fields nvarchar(max)
	
	declare @tables_enum TABLE(id int identity(1,1), table_name sysname, field_name sysname, enum_name varchar(64), other_fields nvarchar(max) null)
	
	--print enum constants
	insert into @tables_enum(table_name, field_name, enum_name,	other_fields)
				select		'pol_acts',							'pact_act_code',									'PolicyAct',			''
	union all	select		'nm_pol_operations',				'nmpop_operation_code',								'PolicyOperation',		'' --'CLIENT_QUOTATION_RENEWAL,COMPANY_QUOTATION_RENEWAL,COMPANY_TO_CLIENT_QUOTATION,CLIENT_QUOTATION_TO_SUBSCRIPTION,COMPANY_QUOTATION_TO_SUBSCRIPTION'
	union all	select		'nm_types_mvts_pol',				'nmtmpol_type_code',								'PolicyMovementsTypes',	''
	union all	select		'pol_mvts',							'pmvt_mvt_code',									'PolicyMovements',		''
	union all	select		'nm_risks_movements',				'rmvt_movement_code',								'RiskMovements',		''
	--union all	select		'nm_status_clients',				'nmscli_statut_code',								'ClientStatus',			''
	--union all	select		'nm_products_variables_types',		'nmpvt_type_code',									'ProductVariableType',	''
	--union all	select		'md_types',							'typ_type_name',									'MdTypes',				''
	union all	select		'nm_pol_renew_types',				'nmprt_renew_type_code',							'PolicyRenewTypes',		''
	union all	select		'md_grids_summaries_functions',		'gsf_function_code',								'GridSummaryFunctions',	''
	union all	select		'md_grids_summaries_positions',		'gsp_position_code',								'GridSummaryPositions',	''
	union all	select		'nm_batches_statuses',				'nmbs_batch_status_code',							'BatchStatus',			''
	union all	select		'sq_types',							'seqt_code',										'SequenceTypes',		''
	union all	select		'nm_invoices_operations',			'op_operation_code',								'InvoicesOperations',	''
	union all	select		'md_languages',						'lng_const',										'LanguagesConst',		''
	union all	select		'nm_accounts_operations',			'aco_operation_code',								'AccountOperations',	''
	union all	select		'acc_accounts_types',				'atyp_type_code',									'AccountsTypes',		''
	union all	select		'nm_positions',						'pos_position_code',								'Positions',			''
	union all	select		'acc_cts_types',					'ctstyp_type_code',									'CtsTypes',				''			
	union all	select		'act_types',						'at_type_code',										'ActorsTypes',			''			
	union all	select		'nm_actions',						'actn_action_code',									'Actions',				''		
	union all	select		'nm_types_retro',					'nmtr_type_retro_code',								'RetroType',			''
	union all	select		'pol_periods',						'per_period_code',									'PolicyPeriod',			''	
	union all	select		'nm_types_mvts_clm',				'nmtmclm_type_code',								'ClaimsMvtsTypes',		''	
	union all	select		'nm_documents_output_types',		'nmdot_output_type_code',							'DocumentsOutputTypes',	''	
	union all	select		'nm_pol_renew_types_split',			'nmprts_renew_type_code,nmprts_renew_split_code',	'PolicyRenewTypeSplit',	''	
	union all	select		'dyn_types',						'dt_type_code',										'DynTypes',				''	
	union all	select		'nm_types_pol',						'nmtp_type_pol_code',								'PolicyType',			''	
	union all	select		'grpusg_bm',						'bm_bm_code',										'UsageBM',				''	
	union all	select		'nm_formats_transformation_types',	'nftt_transformation_code',							'FormatTransformationType',				'NONE'
	union all	SELECT		'nm_bordereaux_companies_types',	'bdrciet_type_code',								'BdrCieTypes',			''
	union all	SELECT		'nm_input_data_dump_types',		   'nmiddt_input_data_dump_type_code',								'InputDataDumpTypes',			''
	UNION ALL	SELECT		'doc_collections_types',			'dct_type_code',									'DocCollectionTypes',	''
	UNION ALL	SELECT		'nm_page_breaks',					'pb_pagebreak_code',								'PageBreakTypes',		''
	UNION ALL	select		'doc_collections_docs_statuses',	'dcds_status_code',									'DocCollectionsStatuses', ''
	UNION ALL	select		'nm_yesno_values',					'nmvyn_yesno_code',									'YesNoValues',			''
	UNION ALL	select		'nm_invoices_nm_monthly_payments_statuses',					'nminmps_monthly_payment_status_code',									'InvoiceMonthlyPaymentsStatuses',			''
	UNION ALL	select		'nm_transactions_types',			'trnt_type_code',									'TransactionTypes',			''
	UNION ALL	select		'acc_mpay_bordereaux_statuses',		'mpays_status_code',								'MPayBordereauxStatuses',	''
	UNION ALL	select		'nm_types_retro_base_amount',		'nmtrba_base_amount_code',							 'RetroTypeBaseAmounts',	'NONE'
	
	select @min_id = min(id), @max_id = max(id)
	from @tables_enum
	
	set @id = @min_id
	
	while @id <= @max_id
	begin
		select	@table_name = table_name,
				@field_name = field_name,
				@enum_name = enum_name,
				@other_fields = other_fields
		from @tables_enum 
		where id = @id

		set @field_name = REPLACE(@field_name,',',' + ''_'' + ')
		
		set @sql =	'set nocount on' + char(13) + char(10) +
				'declare @tbl IdTextTableType ' + char(13) + char(10) +
				'insert into @tbl(txt) ' + char(13) + char(10) + 
				'select Char(9) + char(9) + '  + @field_name  + char(13) + char(10) +
				'from ' + @table_name  + char(13) + char(10) +
				'order by case when ' + @field_name + ' = ' + '''' + 'UNKNOWN' + '''' + ' then 0 else 1 end asc ' + char(13) + char(10) + 
				'insert into @tbl(txt) ' + char(13) + char(10) + 
				'select Char(9) + char(9) + item from dbo.f_split_to_table(' + '''' + @other_fields + '''' + ', ' + '''' +  ',' + '''' +  ')' + char(13) + char(10) + 
				'exec p_sys_md_csharp_print_table_enum @tbl = @tbl, @enum_name = ' + '''' + @enum_name + '''' + char(13) + char(10)

		--print @sql
		execute(@sql)
		
		set @id = @id + 1
	end
	
	print Char(9) + 'class SqlTableType'
	print Char(9) + '{'
	print char(9) + char(9) + 'public string Name { get; set; }'
	print char(9) + char(9) + 'public Dictionary<string, SqlTableTypeColumn> Columns { get; set; }'
	print Char(9) + '}' + char(13) + char(10) + char(13) + char(10)
	
	print Char(9) + 'class SqlTableTypeColumn'
	print Char(9) + '{'
	print char(9) + char(9) + 'public string Name { get; set; }'
	print char(9) + char(9) + 'public string TypeName { get; set; }'
	print Char(9) + '}' + char(13) + char(10) + char(13) + char(10) 
	
	

	
	/* traitement des table types */
	declare @table_types as IdTextTableType

	insert	into @table_types(txt)
	select	Char(9) + char(9) + name from sys.table_types
	order by name asc

	exec p_sys_md_csharp_print_table_enum @table_types, 'UserDefinedTableTypes'	


	exec p_sys_md_csharp_generate_tables_and_rows 

	exec p_sys_md_create_pk_fk

	
	
	
	print '}' + char(13) + char(10)
	
	
	
	
END

GO
