MLM Tree in ASP.NET

Hi, I am Prabhat Sinha. I’m a Senior .NET programmer at Globussoft with 7+ years of experience. I am going to show very simple sample of MLM Tree implementation in ASP.NET with SQL server 2008.

AGENDA: EARNINGS STRUCTURE

When user invites someone to join, the two accounts become linked. Whenever the invited person (L2) makes a purchase, some percentage of their purchase is credited to the account of the user who invited them (L1). (This percentage is usually no more than 3%)
Earnings are counted by the system and displayed on the users’ account information page.
For non-members these earnings are counted on the accounts page, but cannot be cashed out until they pay for a full account.

The earnings system has 4 tiers as seen below. The up-stream payments relationship occurs only between any given user and the L2-L4 users below them. Any further tiers below L4 in their network, ieL5 (friends of L4) are ignored.

So in the pyramid above, assuming no further connections:

•When a L2 makes a purchase, 3% of the purchases are credited to the L1 user
•When a L3 makes a purchase, 3% are credited to the L2 user above them in the pyramid, and 1% is credited to the L1 user
•When a L4 makes a purchase, 3% are credited to the L3 user above them, 1% is credited to the L2 and 0.5% is credited to the L1

Therefore, the earnings system is infinite, but each pyramid within is limited to 4 levels. A given user (User X) will be L2 to another user (User Y), yet when User X invites a new user (User Z), User X remains L2 to User Y, but will become L1 in the new relationship with User Z.

Design for the module:

Solution:
Back end Table Structure:

CREATE TABLE [Bridgin].[MlmUsers](
    [Email] [varchar](50) NOT NULL,
    [Password] [varchar](50) NOT NULL,
    [Guid] [varchar](100) NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [LastLogin] [datetime] NULL,
    [Mobile] [varchar](50) NULL,
    [EmailVerified] [bit] NULL,
    [PhoneVerified] [varchar](50) NULL,
    [AccountCreated] [datetime] NULL,
    [JoinUnder] [varchar](100) NULL,
    [Member] [bit] NULL,
    [PackageType] [char](1) NULL,
    [IsActive] [bit] NOT NULL,
    [RegistrationMethod] [varchar](50) NULL,
    [Subscribe] [varchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [PaymentDate] [datetime] NULL,
    [PaymentStatus] [bit] NULL,
    [PayRef] [varchar](50) NULL,
    [BankHostStatusCode] [varchar](50) NULL,
    [BankRefNo] [varchar](50) NULL,
    [AcountHolder] [varchar](50) NULL,
    CONSTRAINT [PK_MlmUsers_1] PRIMARY KEY CLUSTERED( [Email] ASC
        ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

___________________________________________________________________________________________________

Used Function Structure:
1.

UserDefinedFunction [Bridgin].[funGetT4Tot
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Bridgin].[funGetT4Tot]
( @T3Emailvarchar(50))
RETURNS Int
AS
BEGIN
DECLARE @TotT3 INT
SELECT @TotT3 = COUNT(*) FROM MlmUsers WHERE (JoinUnder = (SELECT Guid FROM MlmUsers
WHERE (Email = @T3Email)))
RETURN (@TotT3)
END
GO

2.

UserDefinedFunction [Bridgin].[funGetT3Tot]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Bridgin].[funGetT3Tot]
(
@T2Email varchar(50)
)
RETURNS Int
AS
BEGIN
DECLARE @TotT2 INT

SELECT @TotT2 = COUNT(*) FROM MlmUsers WHERE (JoinUnder = (SELECT Guid FROM MlmUsers
WHERE (Email = @T2Email)))

RETURN (@TotT2)
END

3.

UserDefinedFunction [Bridgin].[funGetT3T4] (Main function for call)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Bridgin].[funGetT3T4]
(@T1Emailvarchar(50))
RETURNS
@tblGetT3T4 TABLE (Id Int,Emailvarchar(50) ,
MemberNamevarchar(50),
MemberStausvarchar(20),
JoiningDatedatetime ,
networkvarchar(10))

AS
BEGIN
Declare @T3tot int , @T4tot int,@ID INT
Declare @EmailT3 varchar(50),
@EmailT4 varchar(50),
@MemberNamevarchar(50),
@MemberStausvarchar(20),
@JoiningDatedatetime ,
@network varchar(10)

SET @T3tot=0
SET @T4tot=0
Declare @MemberTreeT3 CURSOR
Declare @MemberTreeT4 CURSOR
SET @MemberTreeT3 =CURSOR FOR SELECT MemberTreeDetails.ID,
MemberTreeDetails.InvitedEmail,
MemberTreeDetails.Name,
MemberTreeDetails.Status,
MemberTreeDetails.InviteDate
FROM [Bridgin].MemberTree INNER JOIN [Bridgin].MemberTreeDetails ON [Bridgin].MemberTree.ID = [Bridgin].MemberTreeDetails.InvitedBy WHERE ([Bridgin].MemberTree.InvitedFrom = @T1Email) AND ([Bridgin].MemberTreeDetails.MemberStatus=1)

OPEN @MemberTreeT3
FETCH NEXT FROM @MemberTreeT3 INTO @ID,@EmailT3,@MemberName,@MemberStaus,@JoiningDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @T3tot=Bridgin.funGetT3Tot(@EmailT3)
SET @MemberTreeT4 =CURSOR FOR Select Email FROM [Bridgin].MLMUsers
WHERE (JoinUnder = (SELECT GuidFROM [Bridgin].MlmUsers
WHERE (Email = @EmailT3)))

OPEN @MemberTreeT4
FETCH NEXT FROM @MemberTreeT4 INTO @EmailT4
WHILE @@FETCH_STATUS = 0
BEGIN
SET @T4tot=Bridgin.funGetT4Tot(@EmailT4)
FETCH NEXT FROM @MemberTreeT4 INTO @EmailT4
End
INSERT INTO @tblGetT3T4 (Id,Email, MemberName, MemberStaus,JoiningDate, network )
VALUES (@ID, @EmailT3,@MemberName,@MemberStaus,@JoiningDate,CAST(@T3tot AS VARCHAR(5))+ '(' + CAST(@T4tot AS VARCHAR(5)) + ')')
SET @T3tot=0
SET @T4tot=0
FETCH NEXT FROM @MemberTreeT3 INTO @ID,@EmailT3,@MemberName,@MemberStaus,@JoiningDate
END
RETURN
END

___________________________________________________________________________________________________

About Function in SQL:

In SQL databases, a user-defined function provides a mechanism for extending the functionality of the Database Server by adding a function that can be evaluated in SQL statements. The SQL standard distinguishes between Scalar and table functions. A scalar function returns only a single value (or NULL), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.

http://www.dotnet-tricks.com/Tutorial/sqlserver/KY3T010412-Different-Types-of-SQL-Server-Functions.html
http://technet.microsoft.com/en-us/library/ms191320.aspx

About Cursor in Sql:

A cursor is a set of rows together with a pointer that identifies a current row.
In other words, cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, it’s like record set in ASP and Visual Basic.

http://technet.microsoft.com/en-us/library/ms180169.aspx/
http://www.dotnet-tricks.com/Tutorial/sqlserver/4L7I050512-SQL-Server-Basics-of-Cursors.html

Procedure for Front End:
Added function on dbml file in asp.net:

Code on page:
To call MLM Tree function:

privatevoidGridBind()
{
Member = User.Identity.Name;
try{
GVNetworkMemberStatus.DataSource = DbContext.funGetT3T4(Member);
GVNetworkMemberStatus.DataBind();
}
catch (Exception)
{
throw;
}
}

Finally,the screen form will look like:

Conclusion: In UDF:
Advantages:
• They allow modular programming.
• They allow faster execution.
• They can reduce network traffic.

Disadvantages:
The main “disadvantage” of user-defined functions is that they are called for each row. So, if you have such a function in the SELECT list and you’re operating on larger sets, there are good chances that your performance will suffer.