/**************************************************************/
/*     PROCEDURE sp_GetSubSites                               */
/*                                                            */
/*     1/30/95: Returns the list of sub sites for a site      */
/*                                                            */
/**************************************************************/

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp_GetSubSites'))
 BEGIN
   PRINT 'Dropping old version of dbo.sp_GetSubSites'
   DROP procedure dbo.sp_GetSubSites
 END
go

create table #Sites (SiteCode char(3))
go

CREATE PROCEDURE sp_GetSubSites @SiteCode char(3) AS

 truncate table #Sites


 create table #critSites (SiteCode char(3))

 declare @RowsAffected int

 insert 	#Sites
 select 	@SiteCode

 insert	#critSites
 select	SiteCode
 from	Sites
 where	ReportToSite = @SiteCode

 select	@RowsAffected = @@rowcount


 if @RowsAffected > 0
 begin

 create table #SiteHolder (
  SiteCode char(3)
 ) 

 while	@RowsAffected !=0
   begin
     insert	#SiteHolder
     select	SiteCode
     from	Sites
     where	ReportToSite
     in		(select SiteCode from #critSites)

     select	@RowsAffected = @@rowcount

	if @RowsAffected > 0
	  begin
	   insert #Sites select * from #critSites
	   delete #critSites
	   insert #critSites select * from #SiteHolder
	   delete #SiteHolder
	  end
   end
  insert #Sites select * from #critSites
 end
go

/**************************************************************/
/*     PROCEDURE sp_GetSubGroupSites                          */
/*                                                            */
/*     1/30/95: Returns the list of sub sites for a site grp  */
/*                                                            */
/**************************************************************/


IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp_GetSubGroupSites'))
 BEGIN
   PRINT 'Dropping old version of dbo.sp_GetSubGroupSites'
   DROP procedure dbo.sp_GetSubGroupSites
 END
go


CREATE PROCEDURE sp_GetSubGroupSites @SiteGroup char(50) AS

 truncate table #Sites

 declare @RowsAffected int

/***** First, get all the subgroups for this group *******/

 create table #SiteGroups (SiteGroup char(50))

 create table #critGrps (SiteGroup char(50))

 insert #SiteGroups select @SiteGroup

 insert	#critGrps
 select	SubgroupName
 from	SiteGroupDetails
 where	Name = @SiteGroup
 and	SubgroupName is not null
 
 select	@RowsAffected = @@rowcount

 create table #SiteGroupHolder (
  SiteGroup char(50)
  )


 if @RowsAffected > 0
  begin
 
   while	@RowsAffected !=0
    begin
     insert	#SiteGroupHolder
     select	SubgroupName
     from	SiteGroupDetails
     where	Name
     in		(select SiteGroup from #critGrps)
     and	SubgroupName is not null

     select	@RowsAffected = @@rowcount

	if @RowsAffected > 0
	  begin
	   insert #SiteGroups select * from #critGrps
	   delete #critGrps
	   insert #critGrps select * from #SiteGroupHolder
	   delete #SiteGroupHolder
	  end
   end
  
  end

 insert #SiteGroups select * from #critGrps


/**** beginning of SiteCode analysis ******/

 create table #critSites (SiteCode char(50))


 insert	#critSites
 select	SiteCode
 from	SiteGroupDetails
 where	Name in (select SiteGroup from #SiteGroups)
 and	SiteCode is not null
 
 select	@RowsAffected = @@rowcount
 
 
 if @RowsAffected > 0
  begin
 
  create table #SiteHolder (
   SiteCode char(3)
 )  
  
   while	@RowsAffected !=0
   begin
     insert	#SiteHolder
     select	SiteCode
     from	Sites
     where	ReportToSite
     in		(select SiteCode from #critSites)

     select	@RowsAffected = @@rowcount

	if @RowsAffected > 0
	  begin
	   insert #Sites select * from #critSites
	   delete #critSites
	   insert #critSites select * from #SiteHolder
	   delete #SiteHolder
	  end
   end
 end

  insert #Sites select * from #critSites

go

drop table #Sites
go

grant execute on sp_GetSubSites to public
go

grant execute on sp_GetSubGroupSites to public
go

