3级分销(mysql存储过程写法)
博客专区 > hahass 的博客 > 博客详情
3级分销(mysql存储过程写法)
hahass 发表于1年前
3级分销(mysql存储过程写法)
  • 发表于 1年前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

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

  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 0
博文 1
码字总数 1463
×
hahass
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: