mysql无级分销_3级分销（mysql存储过程写法）

BEGIN

DECLARE sTemp TEXT ;

DECLARE recomsTempChd TEXT ;

DECLARE v_memberno INT DEFAULT 0 ;

DECLARE v_arraymemberno INT DEFAULT 0 ;

DECLARE v_recommemberno INT DEFAULT 0 ;

DECLARE v_typeprice INT DEFAULT 0 ;

DECLARE v_avg_cash INT DEFAULT 0 ;

DECLARE v_avg_integral INT DEFAULT 0 ;

DECLARE v_count INT DEFAULT 0 ;

DECLARE v_count1 INT DEFAULT 0 ;

DECLARE v_count2 INT DEFAULT 0 ;

DECLARE v_count3 INT DEFAULT 0 ;

DECLARE v_type INT DEFAULT 0 ;

DECLARE v_jixiaoall DECIMAL DEFAULT 0 ;

DECLARE v_child1000count INT DEFAULT 0 ;

DECLARE v_node10000 TEXT DEFAULT '' ;

DECLARE v_node10000count INT DEFAULT 0 ;

DECLARE v_node20w TEXT DEFAULT '' ;

DECLARE v_node100w TEXT DEFAULT '' ;

DECLARE v_node300w TEXT DEFAULT '' ;

DECLARE v_node900w TEXT DEFAULT '' ;

DECLARE add20wprice DECIMAL DEFAULT 0 ;

DECLARE add100wprice DECIMAL DEFAULT 0 ;

DECLARE add300wprice DECIMAL DEFAULT 0 ;

DECLARE add900wprice DECIMAL DEFAULT 0 ;

DECLARE add20wchildcount INT DEFAULT 0 ;

DECLARE add100wchildcount INT DEFAULT 0 ;

DECLARE add300wchildcount INT DEFAULT 0 ;

DECLARE add900wchildcount INT DEFAULT 0 ;

DECLARE done INT DEFAULT - 1 ;

DECLARE mycur CURSOR FOR

SELECT

memberno

FROM

fathermembernos ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;

SET sTemp = `getfatherNode` (membernos) ;

SET recomsTempChd = `getRcommFather` (membernos) ;

SELECT

typeprice,

recommmemberno,

`type` INTO v_typeprice,

v_recommemberno,

v_type

FROM

member m

WHERE m.memberno IN (membernos) ;

#设置总业绩和新增业绩\会员数量

CASE

v_type

WHEN 1

THEN

UPDATE

member_achievement

SET

allachievement = allachievement + v_typeprice,

okachievement = okachievement + v_typeprice,

newachievement=newachievement+v_typeprice,

microtype = microtype + 1

WHERE FIND_IN_SET(memberno, sTemp) > 0

AND memberno != membernos ;

WHEN 2

THEN

UPDATE

member_achievement

SET

allachievement = allachievement + v_typeprice,

okachievement = okachievement + v_typeprice,

newachievement=newachievement+v_typeprice,

quicktype = quicktype + 1

WHERE FIND_IN_SET(memberno, sTemp) > 0

AND memberno != membernos ;

WHEN 3

THEN

UPDATE

member_achievement

SET

allachievement = allachievement + v_typeprice,

okachievement = okachievement + v_typeprice,

newachievement=newachievement+v_typeprice,

startype = startype + 1

WHERE FIND_IN_SET(memberno, sTemp) > 0

AND memberno != membernos ;

END CASE ;

#直接推荐奖20%

UPDATE

member_achievement

SET

notrecommend = notrecommend + (v_typeprice * 0.2),

getcash = getcash + (v_typeprice * 0.2),

newrecommend=newrecommend+(v_typeprice * 0.2)

WHERE memberno = v_recommemberno ;

#垂直推荐奖4%

UPDATE

member_achievement

SET

newverticalaward = newverticalaward + (v_typeprice * 0.04),

getcash = getcash + (v_typeprice * 0.04),

verticalaward=verticalaward++ (v_typeprice * 0.04)

WHERE memberno IN

(SELECT

recommmemberno

FROM

member

WHERE memberno = v_recommemberno) ;

#跨区推荐奖%6

UPDATE

member_achievement

SET

newcrossaward = newcrossaward + (v_typeprice * 0.06),

getcash = getcash + (v_typeprice * 0.06),

crossaward=crossaward++ (v_typeprice * 0.06)

WHERE memberno IN

(SELECT

recommmemberno

FROM

member

WHERE memberno IN

(SELECT

recommmemberno

FROM

member

WHERE memberno = v_recommemberno)) ;

SELECT

COUNT(1) INTO v_count1

FROM

member m,member_achievement a

WHERE m.bonus = 0 AND m.memberno=a.memberno

AND m.`type` = 1

AND a.marketshare < 5000

AND m.memberno IN

(SELECT

memberno

FROM

member

WHERE recommmemberno = membernos) ;

SELECT

COUNT(1) INTO v_count2

FROM

member m,member_achievement a

WHERE m.bonus = 0 AND m.memberno=a.memberno

AND m.`type` = 2

AND a.marketshare < 25000 ;

SELECT

COUNT(1) INTO v_count3

FROM

member m,member_achievement a

WHERE m.bonus = 0 AND m.memberno=a.memberno

AND m.`type` = 3

AND a.marketshare < 75000 ;

SET v_count = 1*v_count1 + 4*v_count2 + 10*v_count3 ;

IF v_count > 0

THEN

SELECT

ROUND(v_typeprice/(10*v_count * 2),1),

ROUND(v_typeprice/(10*v_count * 2),1) INTO v_avg_cash,

v_avg_integral

FROM

DUAL ;

END IF ;

##市场分红奖励

UPDATE

member_achievement a,member m

SET

a.getcash = a.getcash +

CASE

WHEN (v_avg_cash + a.marketshare) > 5000

THEN (v_avg_cash + a.marketshare) - 5000

ELSE v_avg_cash

END,

a.integral = a.integral +

CASE

WHEN (v_avg_integral + a.marketinetegral) > 5000

THEN (v_avg_integral + a.marketinetegral) - 5000

ELSE v_avg_integral

END,

a.marketinetegral =

CASE

WHEN (v_avg_integral + a.marketinetegral) > 5000

THEN 5000

ELSE v_avg_integral + a.marketinetegral

END,

a.marketshare =

CASE

WHEN (v_avg_cash + a.marketshare) > 5000

THEN 5000

ELSE v_avg_cash + a.marketshare

END,

a.newmarketinetegral =

CASE

WHEN (v_avg_integral + a.marketinetegral) > 5000

THEN 0

ELSE v_avg_integral + a.newmarketinetegral

END,

a.newmarketshare =

CASE

WHEN (v_avg_cash + a.marketshare) > 5000

THEN 0

ELSE v_avg_cash + a.newmarketshare

END

WHERE a.memberno=m.memberno AND m.bonus = 0

AND m.`type` = 1

AND a.marketshare < 5000

AND EXISTS

(SELECT

1

FROM

member

WHERE recommmemberno = a.memberno) ;

UPDATE

member_achievement a,member m

SET

a.getcash = a.getcash +

CASE

WHEN (v_avg_cash + marketshare) > 25000

THEN (v_avg_cash + marketshare) - 25000

ELSE v_avg_cash

END,

a.integral = a.integral +

CASE

WHEN (v_avg_integral + a.marketinetegral) > 25000

THEN (v_avg_integral + a.marketinetegral) - 25000

ELSE v_avg_integral

END,

a.marketinetegral =

CASE

WHEN (v_avg_integral + a.marketinetegral) > 25000

THEN 25000

ELSE v_avg_cash + a.marketinetegral

END,

a.marketshare =

CASE

WHEN (v_avg_cash + a.marketshare) > 25000

THEN 25000

ELSE v_avg_cash + a.marketshare

END ,

a.newmarketinetegral =

CASE

WHEN (v_avg_integral + a.marketinetegral) > 25000

THEN 0

ELSE v_avg_cash + a.newmarketinetegral

END,

a.newmarketshare =

CASE

WHEN (v_avg_cash + a.marketshare) > 25000

THEN 0

ELSE v_avg_cash + a.newmarketshare

END

WHERE a.memberno=m.memberno AND m.bonus = 0

AND m.`type` = 2 ;

UPDATE

member_achievement a,member m

SET

a.getcash = a.getcash +

CASE

WHEN (v_avg_cash + a.marketshare) > 75000

THEN (v_avg_cash + a.marketshare) - 75000

ELSE v_avg_cash

END,

a.integral = a.integral +

CASE

WHEN (v_avg_integral + a.marketinetegral) > 75000

THEN (v_avg_integral + a.marketinetegral) - 75000

ELSE v_avg_integral

END,

a.marketinetegral =

CASE

WHEN (v_avg_integral + a.marketinetegral) > 75000

THEN 75000

ELSE v_avg_cash + a.marketinetegral

END,

a.marketshare =

CASE

WHEN (v_avg_cash + a.marketshare) > 75000

THEN 75000

ELSE v_avg_cash + a.marketshare

END,

a.newmarketinetegral =

CASE

WHEN (v_avg_integral + a.marketinetegral) > 75000

THEN 0

ELSE v_avg_cash + a.newmarketinetegral

END,

a.newmarketshare =

CASE

WHEN (v_avg_cash + a.marketshare) > 75000

THEN 0

ELSE v_avg_cash + a.newmarketshare

END

WHERE a.memberno=m.memberno AND m.bonus = 0

AND m.`type` = 3 ;

##绩效分红和董事分红处理

TRUNCATE TABLE `fathermembernos` ;

INSERT INTO fathermembernos (memberno)

SELECT

a.memberno

FROM

member m,

member_achievement a

WHERE m.bonus = 0

AND m.state = 1

AND a.memberno = m.memberno

AND allachievement > 200000

AND FIND_IN_SET(m.memberno, sTemp) > 0

AND a.memberno != membernos ;

SET v_node10000 = '$' ;

SET v_node10000count = 0 ;

OPEN mycur ;

myLoop :

LOOP

-- 提取游标里的数据，这里只有一个，多个的话也一样；

FETCH mycur INTO v_memberno ;

#CALL `accountAllYeji`(v_memberno,v_typeprice);

SELECT

allachievement INTO v_jixiaoall

FROM

member_achievement

WHERE memberno = v_memberno ;

IF v_jixiaoall > 10000000

THEN

SELECT

COUNT(*) INTO v_child1000count

FROM

member_achievement a,

member m

WHERE a.memberno = m.memberno

AND m.state = 1

AND a.memberno IN

(SELECT

memberno

FROM

member

WHERE recommmemberno = v_memberno)

AND allachievement > 1000000 ;

IF v_child1000count > 0

THEN SET v_node10000 = CONCAT(v_node10000, v_memberno) ;

SET v_node10000count = v_node10000count + 1 ;

END IF ;

ELSE ##处理20万

IF v_jixiaoall > 200000

AND v_jixiaoall <= 1000000

THEN

SELECT

COUNT(*) INTO add20wchildcount

FROM

member_achievement a,

member m

WHERE a.memberno = m.memberno

AND m.state = 1

AND a.memberno IN

(SELECT

memberno

FROM

member

WHERE recommmemberno = v_memberno)

AND allachievement > 2000000 ;

IF add20wchildcount = 0

THEN SET add20wprice = v_typeprice ;

IF v_jixiaoall - 200000 < v_typeprice

THEN SET add20wprice = v_jixiaoall - 200000 ;

END IF ;

IF add20wprice > 0

THEN

UPDATE

member_achievement

SET

integral = integral + add20wprice * 0.08 * 0.1,

newbmoney = newbmoney + add20wprice * 0.08 * 0.9,

bmoney = bmoney + add20wprice * 0.08 * 0.9,

getcash = getcash + add20wprice * 0.08 * 0.9 where memberno=v_memberno ;

END IF ;

END IF ;

##处理100万

ELSEIF v_jixiaoall > 1000000

AND v_jixiaoall <= 3000000

THEN

SELECT

COUNT(*) INTO add100wchildcount

FROM

member_achievement a,

member m

WHERE a.memberno = m.memberno

AND m.state = 1

AND a.memberno IN

(SELECT

memberno

FROM

member

WHERE recommmemberno = v_memberno)

AND allachievement > 10000000 ;

IF add100wchildcount = 0

THEN SET add100wprice = v_typeprice ;

IF v_jixiaoall - 1000000 < v_typeprice

THEN SET add100wprice = v_jixiaoall - 1000000 ;

END IF ;

IF add100wprice > 0

THEN IF add20wprice > 0

THEN

UPDATE

member_achievement

SET

integral = integral + add20wprice * 0.04 * 0.1,

newbmoney = newbmoney + add20wprice * 0.04 * 0.9,

bmoney = bmoney + add20wprice * 0.04 * 0.9,

getcash = getcash + add20wprice * 0.04 * 0.9 where memberno=v_memberno ;

ELSE

UPDATE

member_achievement

SET

integral =  integral + add20wprice * 0.12 * 0.1,

newbmoney = newbmoney + add20wprice * 0.12 * 0.9,

bmoney = bmoney + add20wprice * 0.12 * 0.9,

getcash = getcash + add20wprice * 0.12 * 0.9 where memberno=v_memberno ;

END IF ;

END IF ;

END IF ;

##处理300万

ELSEIF v_jixiaoall > 3000000

AND v_jixiaoall <= 9000000

THEN

SELECT

COUNT(*) INTO add300wchildcount

FROM

member_achievement a,

member m

WHERE a.memberno = m.memberno

AND m.state = 1

AND a.memberno IN

(SELECT

memberno

FROM

member

WHERE recommmemberno = v_memberno)

AND allachievement > 30000000 ;

IF add300wchildcount = 0

THEN SET add300wprice = v_typeprice ;

IF v_jixiaoall - 3000000 < v_typeprice

THEN SET add300wprice = v_jixiaoall - 3000000 ;

END IF ;

IF add300wprice > 0

THEN IF add20wprice > 0

OR add100wprice > 0

THEN

UPDATE

member_achievement

SET

integral = integral + add20wprice * 0.04 * 0.1,

newbmoney = newbmoney + add20wprice * 0.04 * 0.9,

bmoney = bmoney + add20wprice * 0.04 * 0.9,

getcash = getcash + add20wprice * 0.04 * 0.9 where memberno=v_memberno ;

ELSE

UPDATE

member_achievement

SET

integral = integral + add20wprice * 0.16 * 0.1,

newbmoney = newbmoney + add20wprice * 0.16 * 0.9,

bmoney = bmoney + add20wprice * 0.16 * 0.9,

getcash = getcash + add20wprice * 0.16 * 0.9  where memberno=v_memberno ;

END IF ;

END IF ;

END IF ;

##处理900万

ELSEIF v_jixiaoall > 9000000

THEN

SELECT

COUNT(*) INTO add900wchildcount

FROM

member_achievement a,

member m

WHERE a.memberno = m.memberno

AND m.state = 1

AND a.memberno IN

(SELECT

memberno

FROM

member

WHERE recommmemberno = v_memberno)

AND allachievement > 90000000 ;

IF add900wchildcount = 0

THEN SET add900wprice = v_typeprice ;

IF v_jixiaoall - 3000000 < v_typeprice

THEN SET add900wprice = v_jixiaoall - 9000000 ;

END IF ;

IF add900wprice > 0

THEN IF add20wprice > 0

OR add100wprice > 0

OR add300wprice > 0

THEN

UPDATE

member_achievement

SET

integral = integral + add20wprice * 0.04 * 0.1,

newbmoney = newbmoney + add20wprice * 0.04 * 0.9,

bmoney = bmoney + add20wprice * 0.04 * 0.9,

getcash = getcash + add20wprice * 0.04 * 0.9 where memberno=v_memberno ;

ELSE

UPDATE

member_achievement

SET

integral = integral + add20wprice * 0.2 * 0.1,

newbmoney = newbmoney + add20wprice * 0.2 * 0.9,

bmoney = bmoney + add20wprice * 0.2 * 0.9,

getcash = getcash + add20wprice * 0.2 * 0.9 where memberno=v_memberno ;

END IF ;

END IF ;

END IF ;

END IF ;

END IF ;

-- 声明结束的时候

IF done = 1

THEN LEAVE myLoop ;

END IF ;

-- 这里做你想做的循环的事件

END LOOP myLoop ;

-- 关闭游标

CLOSE mycur ;

UPDATE

member_achievement

SET

getcash = getcash + ROUND((v_typeprice * 0.03) / v_node10000count,1),

newdongshimoney = newdongshimoney + ROUND((v_typeprice * 0.03) / v_node10000count,1)

WHERE FIND_IN_SET(memberno, v_node10000) > 0 ;

END