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

最新推荐文章于 2021-01-26 07:15:28 发布

chouju6541

最新推荐文章于 2021-01-26 07:15:28 发布

阅读量261

点赞数

文章标签：数据库数据结构与算法

原文链接：https://my.oschina.net/u/3051312/blog/789642

版权

BEGIN
DECLARE sTemp TEXT ;
/*父id*/
DECLARE recomsTempChd TEXT ;
/*子id*/
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