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.
