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.
1
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.
2

Design for the module:
3
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.

SQL SERVER – Simple Example of Cursor

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:
4
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:
5
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.

blog lam dep | toc dep | giam can nhanh

|

toc ngan dep 2016 | duong da dep | 999+ kieu vay dep 2016

| toc dep 2016 | du lichdia diem an uong

xem hai

the best premium magento themes

dat ten cho con

áo sơ mi nữ

giảm cân nhanh

kiểu tóc đẹp

đặt tên hay cho con

xu hướng thời trangPhunuso.vn

shop giày nữ

giày lười nữgiày thể thao nữthời trang f5Responsive WordPress Themenha cap 4 nong thonmau biet thu deptoc dephouse beautifulgiay the thao nugiay luoi nutạp chí phụ nữhardware resourcesshop giày lườithời trang nam hàn quốcgiày hàn quốcgiày nam 2015shop giày onlineáo sơ mi hàn quốcshop thời trang nam nữdiễn đàn người tiêu dùngdiễn đàn thời tranggiày thể thao nữ hcmphụ kiện thời trang giá rẻ