recruiting networking system

/*
features:
* have DownlineCount
* have ImmediateDownlineCount
* have Treelevel

Reporting:
* have downline's proximity indicator
* can drill anywhere in the tree
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrigInsOnRecruit]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TrigInsOnRecruit]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrigDelOnRecruit]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TrigDelOnRecruit]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrigUpdOnRecruit]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TrigUpdOnRecruit]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DrillDownlines]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DrillDownlines]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Recruit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Recruit]
GO

CREATE TABLE [dbo].[Recruit] (
[DistNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sponsor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TreeLevel] [int] NOT NULL ,
[DownlineCount] [int] NOT NULL ,
[ImmediateDownlineCount] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Recruit] WITH NOCHECK ADD
CONSTRAINT [PK_Recruit] PRIMARY KEY CLUSTERED
(
[DistNo]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Recruit] WITH NOCHECK ADD
CONSTRAINT [DF_Recruit_Sponsor] DEFAULT (space(0)) FOR [Sponsor],
CONSTRAINT [DF_Recruit_TreeLevel] DEFAULT (1) FOR [TreeLevel],
CONSTRAINT [DF_Recruit_DownlineCount] DEFAULT (0) FOR [DownlineCount],
CONSTRAINT [DF_Recruit_ImmediateDownlineCount] DEFAULT (0) FOR [ImmediateDownlineCount],
CONSTRAINT [CK_Recruit] CHECK ([DistNo] <> '')
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE DrillDownlines
@RootSponsorToDrill VARCHAR(10)

AS

/* we could have opt for a simpler version, and use recursive procedure, but since there is a limit on recursive depth of ms sql server's stored proc, which is 32, we instead use explicit stack */

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

DECLARE @Drills TABLE
(
DistNo VARCHAR(10),
Sponsor VARCHAR(10),
TreeLevel INT,
DownlineCount INT,
ImmediateDownlineCount INT,
RecruitProximity INT
)


INSERT INTO @Drills(
DistNo,
Sponsor,
TreeLevel,
DownlineCount,
ImmediateDownlineCount,
RecruitProximity)
SELECT DistNo, Sponsor, TreeLevel, DownlineCount, ImmediateDownlineCount, 0 FROM Recruit WHERE DistNo = @RootSponsorToDrill



SET NOCOUNT ON

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

DECLARE @StackCounter TABLE
(
StackDepth INT,
I INT,
N INT
)



DECLARE @StackDepth INT
SET @StackDepth = 1

goDrill:

--- push all dist of sponsor, stackdepth..
DECLARE @N INT

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

DECLARE @DistNo VARCHAR(10)
DECLARE @ImmediateDownlineCount INT

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

DECLARE @I INT

SET @I = 0
INSERT INTO @Drills(
DistNo,
Sponsor,
TreeLevel,
DownlineCount,
ImmediateDownlineCount,
RecruitProximity)
SELECT DistNo, Sponsor, TreeLevel, DownlineCount, ImmediateDownlineCount, @StackDepth FROM Recruit WHERE Sponsor = @SponsorToDrill

goProcess:
WHILE @I < @N BEGIN
IF (SELECT ImmediateDownlineCount FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I) > 0 BEGIN
-- push i, n, stackdepth..
INSERT INTO @StackCounter(StackDepth, I, N) SELECT @StackDepth, @I, @N

SELECT @SponsorToDrill = DistNo FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I

SET @StackDepth = @StackDepth + 1

GOTO goDrill
-- ..push i, n, stackdepth
END
SET @I = @I + 1
END


IF @StackDepth > 1 BEGIN
DELETE FROM @StackDist WHERE StackDepth = @StackDepth


DELETE FROM @StackCounter WHERE StackDepth = @StackDepth
SET @StackDepth = @StackDepth - 1
-- pop I, N..
SELECT @I = I + 1, @N = N FROM @StackCounter WHERE StackDepth = @StackDepth

-- ..pop I, N

GOTO goProcess
END

SET NOCOUNT OFF





SELECT * FROM @Drills


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER TrigInsOnRecruit ON [dbo].[Recruit]
FOR INSERT
AS


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

SELECT @DistNo = DistNo FROM inserted
SELECT @Sponsor = Sponsor FROM inserted

IF @DistNo = @Sponsor BEGIN
RAISERROR( 'Distirbutor''s sponsor cannot be itself',16,1)
ROLLBACK TRAN
RETURN
END


UPDATE Recruit SET TreeLevel = RecruitSponsor.TreeLevel + 1
FROM Recruit
INNER JOIN inserted
ON Recruit.DistNo = inserted.DistNo
INNER JOIN Recruit AS RecruitSponsor
ON inserted.Sponsor = RecruitSponsor.DistNo



UPDATE Recruit SET ImmediateDownlineCount = ImmediateDownlineCount + 1
WHERE DistNo = @Sponsor


WHILE @Sponsor <&gt '' BEGIN

DECLARE @SponsorSponsor VARCHAR(10)


UPDATE Recruit SET DownlineCount = DownlineCount + 1
WHERE DistNo = @Sponsor

SELECT @SponsorSponsor = Sponsor
FROM Recruit
WHERE DistNo = @Sponsor


SET @Sponsor = @SponsorSponsor

END





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER TrigDelOnRecruit ON [dbo].[Recruit]
FOR DELETE
AS


IF EXISTS( SELECT * FROM Recruit WHERE Sponsor = (SELECT DistNo FROM deleted) ) BEGIN

RAISERROR('Have downline can not be deleted', 16, 1)
ROLLBACK
RETURN
END


DECLARE @Sponsor VARCHAR(10)

SELECT @Sponsor = Sponsor
FROM deleted



UPDATE Recruit SET TreeLevel = RecruitSponsor.TreeLevel - 1
FROM Recruit
INNER JOIN deleted
ON Recruit.DistNo = deleted.DistNo
INNER JOIN Recruit AS RecruitSponsor
ON deleted.Sponsor = RecruitSponsor.DistNo


UPDATE Recruit SET ImmediateDownlineCount = ImmediateDownlineCount - 1
WHERE DistNo = @Sponsor



WHILE @Sponsor <> '' BEGIN

DECLARE @SponsorSponsor VARCHAR(10)


UPDATE Recruit SET DownlineCount = DownlineCount - 1
WHERE DistNo = @Sponsor

SELECT @SponsorSponsor = Sponsor
FROM Recruit
WHERE DistNo = @Sponsor


SET @Sponsor = @SponsorSponsor

END






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER TrigUpdOnRecruit ON [dbo].[Recruit]
FOR UPDATE
AS


IF (SELECT DistNo FROM deleted) <&gt (SELECT DistNo FROM inserted) BEGIN
RAISERROR('Do not update this column',16,1)
ROLLBACK
RETURN
END




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

SELECT @DistNo = DistNo FROM inserted
SELECT @Sponsor = Sponsor, @DownlineCount = DownlineCount FROM deleted

DECLARE @NewSponsor VARCHAR(10)
SELECT @NewSponsor = Sponsor FROM inserted


IF @DistNo = @NewSponsor BEGIN
RAISERROR( 'Distirbutor''s sponsor cannot be itself',16,1)
ROLLBACK TRAN
RETURN
END



UPDATE Recruit SET TreeLevel = RecruitSponsor.TreeLevel + 1
FROM Recruit
INNER JOIN inserted
ON Recruit.DistNo = inserted.DistNo
INNER JOIN Recruit AS RecruitSponsor
ON inserted.Sponsor = RecruitSponsor.DistNo



UPDATE Recruit SET ImmediateDownlineCount = ImmediateDownlineCount - 1
WHERE DistNo = @Sponsor


UPDATE Recruit SET ImmediateDownlineCount = ImmediateDownlineCount + 1
WHERE DistNo = @NewSponsor



WHILE @Sponsor <> '' BEGIN

DECLARE @SponsorSponsor VARCHAR(10)


UPDATE Recruit SET DownlineCount = DownlineCount - 1 - @DownlineCount
WHERE DistNo = @Sponsor

SELECT @SponsorSponsor = Sponsor
FROM Recruit
WHERE DistNo = @Sponsor


SET @Sponsor = @SponsorSponsor

END






SELECT @Sponsor = @NewSponsor

WHILE @Sponsor <&gt '' BEGIN


UPDATE Recruit SET DownlineCount = DownlineCount + 1 + @DownlineCount
WHERE DistNo = @Sponsor

SELECT @SponsorSponsor = Sponsor
FROM Recruit
WHERE DistNo = @Sponsor


SET @Sponsor = @SponsorSponsor

END

















GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO