SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_qry_fix_cpl_fields_prc]
	@execute bit
as


	/*
	DGE 09/12/2016
	Attention ! Exécutez cette procédure uniquement si vous comprenez ce qu'elle fait et APRS avoir effectué une sauvegarde de la base de données.

	Explication: le procedure recherche les champs de compléments avec un masque PRC (pourcentage) qui n'est pas une précision 4 (ais_decimal4).

	Ces champs sont par la suite créés dans la table correspondante.


	*/

	set nocount on 


	update	md_fields_cpl
	set		fld_type = 'ais_decimal4', fld_size = 13
	where	fld_mask_code = 'prc' and fld_type != 'ais_decimal4'



	select @execute = 1

	declare		@table_name			sysname,
				@field_name			sysname,
				@new_field_name		sysname,

				@sql				varchar(4000)


	declare	c_fields insensitive cursor 
	for		
			select	o.name, c.name, c.name + '_new'
			from	sysobjects o 
					inner join syscolumns c on o.id = c.id
					--inner join systypes t on t.xtype = c.type
			where	c.name in	(
								select	case	
										when fld_table_name = 'ris_cpl' then 'rcpl_' 
										when fld_table_name = 'pol_cpl' then 'pcpl_' 
										when fld_table_name = 'claim_cpl' then 'ccpl_' 
										when fld_table_name = 'act_cpl' then 'acpl_' 
										when fld_table_name = 'claim_actors_cpl' then 'clactcpl_' 
										end + fld_field_name 
								from	md_fields_cpl 
								where	fld_mask_code = 'prc'
								and		fld_table_name in ('ris_cpl', 'pol_cpl', 'claim_cpl', 'act_cpl', 'claim_actors_cpl'))
			and		c.xscale != 4
			order by o.name, c.name



	open c_fields

	fetch next from c_fields into @table_name, @field_name, @new_field_name

	while @@FETCH_STATUS = 0
	begin
	
		print '*** ' + @table_name + '.' + @field_name + ' ***'


		select	@sql = 'select distinct  ' + @field_name + ' from ' + @table_name
		if @execute != 0
			execute(@sql)
		else
			print @sql


		select	@sql = 'alter table ' + @table_name + ' add ' + @new_field_name + ' decimal(9,4) null '

		if @execute != 0
			execute(@sql)
		else
			print @sql


		select	@sql = 'update ' + @table_name + ' set ' + @new_field_name + ' = ' + @field_name + ' WHERE ISNUMERIC(' + @field_name + ') != 0'
		if @execute != 0
			execute(@sql)
		else
			print @sql


		select	@sql = 'alter table ' + @table_name + ' drop column ' + @field_name
		if @execute != 0
			execute(@sql)
		else
			print @sql


		select	@sql = 'alter table ' + @table_name + ' add ' + @field_name + ' decimal(9,4) null '
		if @execute != 0
			execute(@sql)
		else
			print @sql


		select	@sql = 'update ' + @table_name + ' set ' + @field_name + ' = ' + @new_field_name
		if @execute != 0
			execute(@sql)
		else
			print @sql


		select	@sql = 'alter table ' + @table_name + ' drop column ' + @new_field_name
		if @execute != 0
			execute(@sql)
		else
			print @sql

		select	@sql = 'select distinct  ' + @field_name + ' from ' + @table_name
		if @execute != 0
			execute(@sql)
		else
			print @sql



		fetch next from c_fields into @table_name, @field_name, @new_field_name
	end


	close c_fields
	deallocate c_fields

	


		





 
GO
