ALTER PROCEDURE [dbo].[p_member_downline]
@mem_id varchar(20)
AS
BEGIN
declare @all_mem table (
[MEMBER_CODE] varchar(20),
[RANK_POS_ID] int,
[INTRODUCER_CODE] varchar(20),
[INTRODUCER_RANK] int,
[BRANCH_CODE] varchar(20),
[INTRO_NAME] VARCHAR(50),
[MEMBER_NAME] varchar(100),
[lbl_id] int);
declare @MEMBER_CODE varchar(20),
@RANK_POS_ID int,
@INTRODUCER_CODE varchar(20),
@INTRODUCER_RANK int,
@BRANCH_CODE varchar(20),
@JOINING_DATE VARCHAR(50),
@MEMBER_NAME varchar(100);
declare @t table(
[MEMBER_CODE] varchar(20),
[RANK_POS_ID] int,
[INTRODUCER_CODE] varchar(20),
[INTRODUCER_RANK] int,
[BRANCH_CODE] varchar(20),
[JOINING_DATE] VARCHAR(50),
[MEMBER_NAME] varchar(100));
declare @temp table(
[MEMBER_CODE] varchar(20),
[RANK_POS_ID] int,
[INTRODUCER_CODE] varchar(20),
[INTRODUCER_RANK] int,
[BRANCH_CODE] varchar(20),
[JOINING_DATE] VARCHAR(50),
[MEMBER_NAME] varchar(100));
declare @max_lbl int;
set @max_lbl=(select isnull(max(lbl_id),0) from @all_mem);
while(@max_lbl!=16)
begin
if(@max_lbl=0)
begin
insert into @t
select MEMBER_CODE,RANK_POS_ID,INTRODUCER_CODE,INTRODUCER_RANK,
BRANCH_CODE,JOINING_DATE,MEMBER_NAME
from MEMBERSHIP where INTRODUCER_CODE=@mem_id;
while((select count(*) from @t)!=0)
begin
declare @m_code varchar(20);
set @m_code = (select top 1 MEMBER_CODE from @t);
select @MEMBER_CODE=MEMBER_CODE,@RANK_POS_ID=RANK_POS_ID,
@INTRODUCER_CODE=INTRODUCER_CODE,@INTRODUCER_RANK=INTRODUCER_RANK,
@BRANCH_CODE=BRANCH_CODE,@JOINING_DATE=JOINING_DATE,
@MEMBER_NAME=MEMBER_NAME
from @t where MEMBER_CODE=@m_code;
insert into @all_mem values(
@MEMBER_CODE,@RANK_POS_ID,@INTRODUCER_CODE,@INTRODUCER_RANK,
@BRANCH_CODE,@JOINING_DATE,@MEMBER_NAME,1)
delete from @t where MEMBER_CODE=@m_code;
end
end--end of lbl_max
else
begin
insert into @temp
select MEMBER_CODE,RANK_POS_ID,INTRODUCER_CODE,INTRODUCER_RANK,
BRANCH_CODE,MEMBER_NAME,NULL
from @all_mem where lbl_id=@max_lbl;
while((select count(*) from @temp)!=0)
begin
declare @m_id varchar(20);
set @m_id=(select top 1 MEMBER_CODE from @temp);
insert into @t
select MEMBER_CODE,RANK_POS_ID,INTRODUCER_CODE,INTRODUCER_RANK,
BRANCH_CODE,JOINING_DATE,MEMBER_NAME
from MEMBERSHIP where INTRODUCER_CODE=@m_id;
while((select count(*) from @t)!=0)
begin
set @m_code = (select top 1 MEMBER_CODE from @t);
select @MEMBER_CODE=MEMBER_CODE,@RANK_POS_ID=RANK_POS_ID,
@INTRODUCER_CODE=INTRODUCER_CODE,@INTRODUCER_RANK=INTRODUCER_RANK,
@BRANCH_CODE=BRANCH_CODE,@JOINING_DATE=JOINING_DATE,
@MEMBER_NAME=MEMBER_NAME
from @t where MEMBER_CODE=@m_code;
insert into @all_mem values(
@MEMBER_CODE,@RANK_POS_ID,@INTRODUCER_CODE,@INTRODUCER_RANK,
@BRANCH_CODE,@JOINING_DATE,@MEMBER_NAME,(@max_lbl+1));
--select * from @all_mem;
delete from @t where MEMBER_CODE=@m_code;
end
--for temp
delete from @temp where MEMBER_CODE=@m_id;
end
end--end of else lbl_id max
set @max_lbl=@max_lbl+1;
end --while ibi_id max
insert into @all_mem
select
A.[MEMBER_CODE],
A.[RANK_POS_ID],
A.[INTRODUCER_CODE],
A.[INTRODUCER_RANK],
B.BRANCH_NAME AS BRANCH_CODE,
A.MEMBER_CODE,
A.[MEMBER_NAME],
A.id
from MEMBERSHIP A INNER JOIN BRANCH_MASTER B ON A.BRANCH_CODE=B.BRANCH_CODE where A.MEMBER_CODE=@mem_id
select A.[MEMBER_CODE],
A.[RANK_POS_ID],
A.[INTRODUCER_CODE],
A.[INTRODUCER_RANK],
B.BRANCH_NAME AS BRANCH_CODE,
A.[MEMBER_NAME]
from @all_mem A INNER JOIN BRANCH_MASTER B ON A.BRANCH_CODE=B.BRANCH_CODE
END
