Query to implement multilevel marketing

Help me with the query to implement ,multilevel marketing.eg:Amway. Each user is restricted to be reference of 4 users.the root node is registered by giving registration fee of 100 rupees. And he can add 4 users under his reference. And he will get 10% as reference fee and 10% as loyalty.so by adding 4 users the root node will get 80(40 as loyalty+ 40 as reference fee).remaining 320 out of 400 will go to admin.like wise the tree will grows.only the upper node will get 10% reference and 10% loyalty.next higher level get only the loyalty while adding a user.

im creating a web application.when we create a new user under the reference of existing user ,the query should return the commision to admin after giving all parent nodes their commision share.

my query is given below....its only return hirarchy from the current user to root...need to add commisiondivision between nodes...

ALTER PROCEDURE [dbo].[spUser_Save]

@PKUserId int,

@FirstName varchar(100),

@LastName varchar(100),

@DOB datetime,

@ReferId int,

@Count int,

@EmailId varchar(100),

@Password varchar(100),

@Address varchar(250),

@Landmark varchar(100),

@City varchar(100),

@PostOffice varchar(100),

@District varchar(100),

@PinCode varchar(10),

@State varchar(100),

@PhoneNum varchar(20),

@MobileNum varchar(20),

@FKRowStatusID int

BEGIN

SET NOCOUNT ON;

DECLARE @referCount int;

DECLARE @Mytable table(UserId int,ReferId int,Username varchar(100),Depth int);

SELECT @referCount= [COUNT] FROM tblUser U WHERE U.UserId=CASE

WHEN @ReferId =0

THEN U.UserId

ELSE @ReferId

END

IF(@referCount<4)

BEGIN

INSERT INTO tblUser VALUES(@FirstName,@LastName,@DOB,@ReferId,@Count,@EmailId,@Password,@Address,@Landmark,

@City,@PostOffice,@District,@PinCode,@State,@PhoneNum,@MobileNum,@FKRowStatusID,GETDATE());

UPDATE tblUser SET Count=tblUser.Count +1

WHERE tblUser.UserId=CASE

WHEN @ReferId =0

THEN tblUser.UserId

ELSE @ReferId

END ;

END

ELSE IF(@referCount>=4)

BEGIN

DECLARE @StartID INT = 11

;WITH c

(

SELECT UserId,ReferId,FirstName, 1 AS Depth

FROM tblUser

WHERE ReferId=@ReferId

UNION ALL

SELECT t.UserId,t.ReferId,t.FirstName,c.Depth + 1 AS 'Level'

FROM tblUser T

INNER JOIN c ON t.UserId = c.ReferId

)

INSERT INTO @Mytable SELECT *

FROM c Group BY c.UserId,c.ReferId,c.FirstName,c.Depth ORDER BY Depth;

SELECT * FROM @Mytable;

END

SELECT SCOPE_IDENTITY();

END

the attachment contains manual calculation

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aleena Saviour9y ago
im creating a web application.when we create a new user under the reference of existing user ,the query should return the commision to admin after giving all parent nodes their commision share.

my query is given below....its only return hirarchy from the current user to root...need to add commisiondivision between nodes...

ALTER PROCEDURE [dbo].[spUser_Save]
@PKUserId int,
@FirstName varchar(100),
@LastName varchar(100),
@DOB datetime,
@ReferId int,
@Count int,
@EmailId varchar(100),
@Password varchar(100),
@Address varchar(250),
@Landmark varchar(100),
@City varchar(100),
@PostOffice varchar(100),
@District varchar(100),
@PinCode varchar(10),
@State varchar(100),
@PhoneNum varchar(20),
@MobileNum varchar(20),
@FKRowStatusID int

AS
BEGIN
SET NOCOUNT ON;
DECLARE @referCount int;
DECLARE @Mytable table(UserId int,ReferId int,Username varchar(100),Depth int);

SELECT @referCount= [COUNT] FROM tblUser U WHERE U.UserId=CASE
WHEN @ReferId =0
THEN U.UserId
ELSE @ReferId
END
IF(@referCount<4)
BEGIN
INSERT INTO tblUser VALUES(@FirstName,@LastName,@DOB,@ReferId,@Count,@EmailId,@Password,@Address,@Landmark,
@City,@PostOffice,@District,@PinCode,@State,@PhoneNum,@MobileNum,@FKRowStatusID,GETDATE());
UPDATE tblUser SET Count=tblUser.Count +1
WHERE tblUser.UserId=CASE
WHEN @ReferId =0
THEN tblUser.UserId
ELSE @ReferId
END ;

END
ELSE IF(@referCount>=4)
BEGIN
DECLARE @StartID INT = 11
;WITH c
AS
(
  SELECT UserId,ReferId,FirstName, 1 AS Depth
  FROM tblUser
WHERE ReferId=@ReferId
UNION ALL
  SELECT t.UserId,t.ReferId,t.FirstName,c.Depth + 1 AS 'Level'
  FROM tblUser T
  INNER JOIN c ON t.UserId = c.ReferId
)
INSERT INTO @Mytable SELECT *
FROM c Group BY c.UserId,c.ReferId,c.FirstName,c.Depth ORDER BY Depth;
SELECT * FROM @Mytable;

END
SELECT SCOPE_IDENTITY();
END
the attachment contains manual calculation
Chetan Ranpariya9y ago
You have written any code for this? Are you facing any issue with that code? What error or exception tou are getting? You are creating web application or windows application?