/*
Utility function to generate the where clause match of the generated
evaluator
*/
drop FUNCTION uf_profile_match_clause
go
CREATE FUNCTION uf_profile_match_clause  
   (@p_name varchar(255))
RETURNS varchar(500)
AS 
BEGIN
   return '(p.' + @p_name + ' is null or' + char(13) + char(10) +
'(left(p.' + @p_name + ',2) = ''=='' and ' + char(13) + char(10) +
'   substring(p.' + @p_name + ',4,len(p.' + @p_name + 
     ')-3) = m.' + @p_name + ') or ' + char(13) + char(10) +
'(left(p.' + @p_name + ',2) = ''!='' and ' + char(13) + char(10) +
'   substring(p.' + @p_name + ',4,len(p.' + @p_name + 
     ')-3) != m.' + @p_name + ') ' + char(13) + char(10) +
')'
END
go

drop procedure up_populate_column_values
go
/*
In order to generate profile clauses, we need a set of
possible values to match against columns.  This procedure builds
this set
*/
create procedure up_populate_column_values
as
declare
   @sql varchar(2000),
   @next_column smallint
begin

select * into #t1
from v_columns


while exists(select * from #t1)
begin
   select @next_column = max(colorder) from #t1 
   select @sql = '   insert t_column_values ' + char(13) + char(10)
   select @sql = @sql + 
      'select distinct ' + '''' + name + '''' +
      ' as c_name, ' +  name + ' as c_value ' + char(13) + char(10)
   from #t1
   where colorder = @next_column
   select @sql = @sql + 'from t_mine_relations' + char(13) + char(10)
   exec(@sql)     
   --select @next_column
   delete from #t1 where colorder = @next_column   
end

end
go
/*
Populate t_profile_column_values
*/
exec up_populate_column_values
go

/*
Utility function to generate the where clause match of the generated
evaluator.  This is used by the profile evaluator procedure.

Note:  an attempt was made to substitue a user function for these 
clauses,in order to simplify the generated evaluatire.  However, 
it ran MUCH more slowly, because user functions are always treated
as cursore.
*/
drop FUNCTION uf_profile_match_clause
go
CREATE FUNCTION uf_profile_match_clause  
   (@p_name varchar(255))
RETURNS varchar(500)
AS 
BEGIN
   return '(p.' + @p_name + ' is null or' + char(13) + char(10) +
'(left(p.' + @p_name + ',2) = ''=='' and ' + char(13) + char(10) +
'   substring(p.' + @p_name + ',4,len(p.' + @p_name + 
     ')-3) = m.' + @p_name + ') or ' + char(13) + char(10) +
'(left(p.' + @p_name + ',2) = ''!='' and ' + char(13) + char(10) +
'   substring(p.' + @p_name + ',4,len(p.' + @p_name + 
     ')-3) != m.' + @p_name + ') ' + char(13) + char(10) +
')'
END
go
drop procedure up_generate_profile_Evaluator
go
/*
We need to generate an evaluator procedure, that
will compute t_value_results by profile.
*/
create procedure up_generate_profile_Evaluator
as
declare
   @clause varchar(255),
   @sqlfixed varchar(8000),
   @sql varchar(8000),
   @next uniqueidentifier,
   @sql1 varchar(8000),
   @sql2 varchar(8000),
   @sql_group_by varchar(8000),
   @where_clauses_inserted int

begin

select 
   newid() as id, .dbo.uf_profile_match_clause(name) as clause
into #t1
from v_columns
order by colorder
--select * from #t1

select @sqlfixed = 'drop procedure up_generated_profile_Evaluator'
exec(@sqlfixed)

select @sqlfixed = '', @sql1 = '', @sql2 = ''

if exists( select * from sysobjects where name = 'up_generated') 
   drop procedure up_generated
select @sqlfixed = @sqlfixed + 
'create procedure up_generated_profile_Evaluator' + char(13) + char(10)
select @sqlfixed = @sqlfixed + 'as ' + char(13) + char(10)
select @sqlfixed = @sqlfixed + 'exec up_timing_start ' + 
'''up_generated_profile_Evaluator''' + ' ' + char(13) + char(10)
select @sqlfixed = @sqlfixed + 
'delete from t_value_results' + char(13) + char(10)
select @sqlfixed = @sqlfixed + 
'insert t_value_results' + char(13) + char(10)
select @sqlfixed = @sqlfixed + 
  'select p.id as id, m.' + name + 
' as target_value,count(*) as number_selected' 
  + char(13) + char(10)
from v_target
select @sqlfixed = @sqlfixed + 
'from  .dbo.uf_generated_profile_table() p ' +
'left outer join ' +
't_mine_relations m ' +  + char(13) + char(10) 
select @sqlfixed = @sqlfixed + 'on ' + char(13) + char(10) 

select @sql = ''
select @where_clauses_inserted = 0
while ( exists (select * from  #t1))
begin
    select top 1 @next = id
    from #t1
    select @clause = clause
    from #t1
    where @next = id
    
    select  @sql = @sql + @clause 
    delete from #t1 where id = @next
    if exists (select * from  #t1)
        select  @sql = @sql + ' and ' + char(13) +char(10)
    else
        select  @sql = @sql + char(13) +char(10)

    select @where_clauses_inserted = @where_clauses_inserted + 1
    if @where_clauses_inserted = 30
       begin
         select @sql1 = @sql
         select @sql = ''
       end
    if @where_clauses_inserted = 60
       begin
         select @sql2 = @sql
         select @sql = ''
       end
end

select @sql_group_by =  
' group by id, m.' + name  + char(13) +char(10) +
    ' order by id, m.' + name + char(13) + char(10)
from v_target
select @sql_group_by = @sql_group_by + 'exec up_timing_end ' + 
'''up_generated_profile_Evaluator''' +  char(13) + char(10)

exec(@sqlfixed + @sql1 + @sql2 + @sql + @sql_group_by)
end
go
/*
Compile he evaluation procedure
*/
exec up_generate_profile_Evaluator
go
