Query to implement multilevel marketing

Question

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.

Aleena Saviour · Answer

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 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 Ranpariya · Answer

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?