
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[p_sys_md_create_cbo_for_table]
	@table ais_name,
	@value_field_name ais_name, 
	@create_md_grids bit = 1,
	@create_md_grids_fields bit = 1,
	@create_sp bit = 1
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @grid ais_name,
		@display_field_name ais_name,
		@sql ais_name,
		@param ais_name
	
	SELECT @grid = 'grid_' + @table,
		@display_field_name = left(@value_field_name,  CHARINDEX('_', @value_field_name, 1)) + 'lx', 
		@sql = 'p_grid_' + @table,
		@param = '@' + REPLACE( @value_field_name, left(@value_field_name,  CHARINDEX('_', @value_field_name, 1)), '')
		
	if @create_md_grids != 0
		IF EXISTS( SELECT 1 FROM md_grids WHERE grd_grid_name = @grid )
		BEGIN
			RAISERROR ('The definition md_grids already exists !', 16, 1)
			return
		END
	
	if @create_md_grids_fields != 0
		IF EXISTS( SELECT 1 FROM md_grids_fields WHERE grdf_grid_name = @grid )
		BEGIN
			RAISERROR ('The definition md_grids_fields already exists !', 16, 1)
			return
		END	

	if @create_sp != 0 
		IF EXISTS( SELECT 1 FROM sysobjects where type = 'p' and name = @sql )
		BEGIN
			RAISERROR ('The store procedure %s already exists !', 16, 1, @sql)
			return
		END	
	
	
	BEGIN TRAN
	
	
	if @create_md_grids != 0
		INSERT INTO [md_grids]
				([grd_grid_name]
				,[grd_l1]
				,[grd_l2]
				,[grd_l3]
				,[grd_value_table_name]
				,[grd_value_field_name]
				,[grd_display_table_name]
				,[grd_display_field_name]
				,[grd_sql]
				,[grd_columns_fill]
				,[grd_show_required_icon])
		SELECT	@grid,
				tbl_l1,
				tbl_l2,
				tbl_l3,
				@table,
				@value_field_name,
				@table, 
				@display_field_name,
				@sql,
				1, 
				0
		FROM	md_tables
		WHERE	tbl_table_name = @table
	
	
	if @create_md_grids_fields != 0 
	BEGIN
		INSERT INTO [md_grids_fields]
			   ([grdf_grid_name]
			   ,[grdf_table_name]
			   ,[grdf_field_name]
			   ,[grdf_order]
			   ,[grdf_width])
		 VALUES
			   (@grid
			   ,@table
			   ,@value_field_name
			   ,1
			   ,null)


		INSERT INTO [md_grids_fields]
			   ([grdf_grid_name]
			   ,[grdf_table_name]
			   ,[grdf_field_name]
			   ,[grdf_order]
			   ,[grdf_width])
		 VALUES
			   (@grid
			   ,@table
			   ,@display_field_name
			   ,2
			   ,null)
	END
	
	
	COMMIT TRAN
	
	print ''	
	print '*****************************************************'	
	print '*** rows added to md_grids and md_grids_fields OK ***'	
	print '*****************************************************'	
	print ''
	
		
	SELECT  *
	FROM    md_grids
	WHERE	grd_grid_name = @grid
	
	SELECT  *
	FROM    md_grids_fields
	WHERE	grdf_grid_name = @grid
	ORDER BY grdf_order

	
	IF @create_sp != 0 
	BEGIN
	
		declare @proc varchar(1000)
		
		select @proc =  
		'CREATE PROCEDURE ' + @sql + char(10) + 
		'	' + @param + ' AS ais_code_short  = ''%'' ' + char(10) +
		'AS' + CHAR(10) +
		'BEGIN' + CHAR(10) + 
		'	SET NOCOUNT ON' + CHAR(10) + CHAR(10) +
		'	SELECT	*' + CHAR(10) + 
		'	FROM ' + @table + CHAR(10) +
		'	WHERE ' + @param + ' = ''%'' OR ' + @value_field_name + ' = ' + @param + CHAR(10) +
		'END'
		
		
		exec(@proc)
		
		print ''	
		print '*****************************************************'	
		print '***          stored procedure created OK          ***'	
		print '*****************************************************'	
		print ''	

		print @proc
	
	END


END	
GO
