synch distibutor to recruit tree order, stack-based traversing

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




alter PROCEDURE SynchRecruitTreeStartFromDistRootAll

AS




 truncate table recruit

 DECLARE @Counter INT



 declare @RootSponsorToDrill VARCHAR(10)

 select @RootSponsorToDrill = (SELECT DistNo FROM Dist WHERE rtrim(Sponsor) = '')


 SET @Counter = 1
 INSERT INTO Recruit(DistNo, Sponsor, TreeLevel, TreeOrder, HiOrder)
 SELECT DistNo, Sponsor, 1, @Counter, 0 FROM Dist WHERE rtrim(Sponsor) = ''


 DECLARE @SponsorToDrill VARCHAR(10)
 SET @SponsorToDrill = @RootSponsorToDrill





 SET NOCOUNT ON

 DECLARE @StackDist TABLE
 (
 StackDepth INT,
 Ndx INT,
 DistNo VARCHAR(10),
 Sponsor VARCHAR(10),
 ImmediateDownlineCount INT,
 TreeOrder INT,
 HiOrder INT
 )

 DECLARE @StackCounter TABLE
 (
 ParentSponsor VARCHAR(10),
 StackDepth INT,
 I INT,
 N INT
 )




 DECLARE @StackDepth INT
 SET @StackDepth = 1
 INSERT INTO @StackCounter(StackDepth, I, N, ParentSponsor) SELECT @StackDepth, 0, 0, @SponsorToDrill


 goDrill:

 --- push all dist of sponsor, stackdepth..


 DECLARE @N INT

 SET @N = 0
 DECLARE @c CURSOR
 SET @C = CURSOR FOR
 SELECT DistNo, Sponsor FROM Dist WHERE Sponsor = @SponsorToDrill
 OPEN @C

 DECLARE @DistNo VARCHAR(10)
 DECLARE @Sponsor VARCHAR(10)
 DECLARE @ImmediateDownlineCount INT

 FETCH NEXT FROM @C INTO @DistNo, @Sponsor
 WHILE @@FETCH_STATUS = 0 BEGIN
  INSERT INTO @StackDist(StackDepth, Ndx, DistNo, Sponsor)
  SELECT @StackDepth, @N, @DistNo, @Sponsor
  SET @N = @N + 1
  FETCH NEXT FROM @C INTO @DistNo, @Sponsor
 END
 CLOSE @C
 DEALLOCATE @C
 -- ..push all dist of sponsor, stackdepth

 DECLARE @I INT

 SET @I = 0
 goProcess:
 WHILE @I < @N BEGIN 

  /* declare @s varchar(800) select @s = REPLICATE('-',@StackDepth * 2) + DistNo FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I -- print @s -- INSERT INTO @X(S) SELECT @S */ 
  
  
  SET @Counter = @Counter + 1 
  
  INSERT INTO Recruit(DistNo, Sponsor, TreeOrder, TreeLevel) 
  SELECT DistNo, Sponsor, @Counter, @StackDepth + 1 
  FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I 

  /*UPDATE @StackDist SET TreeOrder = @Counter WHERE DistNo = (SELECT DistNo FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I)*/ 
  
  
  IF EXISTS( SELECT * FROM Dist WHERE Sponsor = 
   (SELECT Distno FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I)) 
  BEGIN 
   -- push i, n, stackdepth.. 
   SELECT @SponsorToDrill = DistNo FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I 
   
   
   SET @StackDepth = @StackDepth + 1 
   
   INSERT INTO @StackCounter(StackDepth, I, N, ParentSponsor) 
   SELECT @StackDepth, @I, @N, @SponsorToDrill 
   GOTO goDrill -- ..push i, n, stackdepth 
   
  END 
  
  SET @I = @I + 1 
  
 END 
 
 
 ----------------------------- 
  
 
  
 IF @SponsorToDrill = '00000000' BEGIN 
  SELECT 'MICHAEL ' + CONVERT(VARCHAR, @Counter) 
 END 
 
 -- pop I, N.. 
 
 SELECT @I = I + 1, @N = N, @SponsorToDrill = ParentSponsor 
 FROM @StackCounter WHERE StackDepth = @StackDepth 
 
 -- ..pop I, N 
 
 UPDATE Recruit SET HiOrder = @Counter 
 WHERE DistNo = @SponsorToDrill 
 
 DELETE FROM @StackDist 
 WHERE StackDepth = @StackDepth 
 
 DELETE FROM @StackCounter 
 WHERE StackDepth = @StackDepth 
 
 SET @StackDepth = @StackDepth - 1 
 
 IF @StackDepth > 0 BEGIN
  GOTO goProcess
 END


 SET NOCOUNT OFF




 --SELECT * FROM @Drills order by sponsor

 PRINT 'Finished synching'