/*
grobillard 2007-07-27  version 1.0

purpose: populate drug tables with ordered strings for matching by active ingredient
Current string parser breaks up active ingredients by ';' or ' and ' strings

A SQL stored procedure to sort items in a comma-delimited list within a record. Any table with this design fails the ACID test, but sometimes people design databases this way and you are stuck with their design. This was written due to a poorly-designed drug ingredient table provided
by the FDA. 

Usage: #exec dbo.updateDrugActiveIngredSort @table_name='fda_drug'
*/

CREATE  PROC [dbo].[updateDrugActiveIngredSort]
	@table_name varchar(300) 

AS
begin

create table #activeIngredSort( 
  did int, 
  activeIngred VARCHAR(500) 
)

create table #drug_ingred( 
  id int identity, 
  drug_id int,
  ingred VARCHAR(230) 
)
--following set of if statements based on tables in database that use this stored procedure. 
if @table_name = 'drug'
   insert into #drug_ingred(drug_id,ingred) select drug_id,activeIngred from dbo.drug where charindex(';',activeingred)> 0 or charindex(' and ',activeingred)> 0
else if @table_name = 'drug_streetaccount_last'
   insert into #drug_ingred(drug_id,ingred) select drug_id,activeIngred from dbo.drug_streetaccount_last where charindex(';',activeingred)> 0 or charindex(' and ',activeingred)> 0
else if @table_name = 'drug_from_FDA_last'
   insert into #drug_ingred(drug_id,ingred) select drug_id,activeIngred from dbo.drug_from_FDA_last where charindex(';',activeingred)> 0 or charindex(' and ',activeingred)> 0 
else if @table_name = 'drug_from_FDA'
   insert into #drug_ingred(drug_id,ingred) select drug_id,activeIngred from dbo.drug_from_FDA where charindex(';',activeingred)> 0 or charindex(' and ',activeingred)> 0

declare @rc int 
select @rc = count(id) from #drug_ingred
set @rc = @rc +1


declare @rowNum int,@did int,@bigset varchar(500),@tvar varchar(230),@ltvar int,@ctr int,@id int,@delim char(1)
set @rowNum = 1
while @rowNum < @rc
  begin
  select top 1  @did = drug_id,@bigset =ingred from #drug_ingred where id =@rownum
  set @rowNum = @rowNum + 1

  declare @ingredStore TABLE( 
    id int, 
    ingred VARCHAR(230), 
    delimiter char(1)
  )
  set @id =1
 -- if/else loop for charindex type: either ';' or ' and '
 if charindex(';',@bigset)>0
  begin
  while charindex(';',@bigset)>0
   begin
     set @tvar = substring(@bigset,0,charindex(';',@bigset))
     set @ltvar = len(@tvar)+1
     set @bigset = right(@bigset,len(@bigset)-@ltvar)
     insert into @ingredStore(id,ingred,delimiter)values(@id,ltrim(@tvar),';')
     set @id = @id +1
   end
--do final insert after last ';'
   insert into @ingredStore(id,ingred,delimiter)values(@id,ltrim(@bigset),';')
 end -- if delimiter is ';'
 else -- if delimeter is ' and '
  begin 
  while charindex(' and ',@bigset)>0
   begin
     set @tvar = substring(@bigset,0,charindex('and',@bigset))
     set @ltvar = len(@tvar)+4
     set @bigset = right(@bigset,len(@bigset)-@ltvar)
     insert into @ingredStore(id,ingred,delimiter)values(@id,ltrim(@tvar),'&')
     set @id = @id +1
   end
--do final insert after last ';'
   insert into @ingredStore(id,ingred,delimiter)values(@id,ltrim(@bigset),'&')
   select * from @ingredStore
  end --delimeter is ' and '
--declare temp table
   create table #ingredSort( 
    id int identity, 
    ingred VARCHAR(230), 
    delimiter char(1)
   )
   insert into #ingredSort(ingred,delimiter) select ingred,delimiter from @ingredStore order by ingred
     --grab counter, iterate, reorder string;
   declare @ingredStr varchar(3000),@ingr varchar(250)
   select * from #ingredsort
   select @ctr= count(ingred) from #ingredSort
   declare @x int
   set @x = 1
   while @x < @ctr + 1
     begin
     select @ingr = ingred, @delim=delimiter from #ingredSort where id = @x
     if @x = 1
      set @ingredStr = @ingr
     else
      begin
       if @delim = ';'
        set @ingredStr = @ingredStr + '; ' + @ingr
       else
        set @ingredStr = @ingredStr + 'and ' + @ingr
       end
      set @x = @x +1
   end --go through while lop
  --insert into the tbl
  insert into #activeIngredSort(did,activeIngred)values(@did,@ingredstr)
  --garbage collection for reiteration
  set @ingredstr = ''
  set @ingr = ''
  delete from @ingredStore
  drop table #ingredSort
  
end --end of outer while loop

--update drug tables from #activeIngreSort
if @table_name = 'drug'
   update dbo.drug  set ordered_active_ingred = a.activeIngred from #activeIngredSort a where drug_id = a.did
else if @table_name = 'drug_streetaccount_last'
   update dbo.drug_streetaccount_last set ordered_active_ingred = a.activeIngred from #activeIngredSort a where drug_id = a.did
else if @table_name = 'drug_from_FDA_last'
   update dbo.drug_from_FDA_last set ordered_active_ingred = a.activeIngred from #activeIngredSort a where drug_id = a.did
else if @table_name = 'drug_from_FDA'
   update dbo.drug_from_FDA set ordered_active_ingred = a.activeIngred from #activeIngredSort a where drug_id = a.did
--clean temp tables
drop table #activeIngredSort
drop table #drug_ingred

END