DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALC20.sql

DROP PROCEDURE IF EXISTS `CALC20`;
delimiter ;;
CREATE PROCEDURE `CALC20`(IN `PCPID` INT)
BEGIN
    DECLARE
        MLAYER,
        MMAXLAYER,
        MCOUNT INT;
    DECLARE
        MRATE,
        MLIMITPV DECIMAL ( 10, 2 );
    START TRANSACTION;-- 第一个级别的限制
    SELECT
        T.ACHIEVE_PV INTO MLIMITPV
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = 'E121497617216708615';-- 第二个级别得奖的比例
    SELECT
            T.BS_PERCENT / 100 INTO MRATE
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE5FE7857C216AE055736AECE8644D';
    SELECT
        MAX( LAYER ) INTO MLAYER
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            PV > 0;

    SET MMAXLAYER := MLAYER;
    WHILE
            MLAYER >= 0 DO
            IF
                ( MLAYER < MMAXLAYER ) THEN-- 向上累计
                UPDATE AR_BS_BONUS_103_CALC T1
                    INNER JOIN (
                        SELECT
                            INTRODUCER_ID,
                            SUM( GPV ) GPV
                        FROM
                            AR_BS_BONUS_103_CALC
                        WHERE
                                LAYER = MLAYER + 1
                          AND USER_TYPE = 0
                          AND GPV > 0
                        GROUP BY
                            INTRODUCER_ID
                    ) T2 ON T1.USER_ID = T2.INTRODUCER_ID
                        AND T1.LAYER = MLAYER
                SET T1.GPV = T2.GPV;-- 没有升级的会员需要将自己的小组业绩清零
                UPDATE AR_BS_BONUS_103_CALC T
                SET T.GPV = 0
                WHERE
                        T.GPV < T.ACHIEVE_PERF_PV AND T.LAYER = MLAYER + 1 AND GPV > 0;

            END IF;
            UPDATE AR_BS_BONUS_103_CALC T1
            SET T1.GPV = T1.GPV + T1.PV
            WHERE
                    T1.LAYER = MLAYER;-- 是否达标 如果达标
            UPDATE AR_BS_BONUS_103_CALC T
            SET T.USER_TYPE = 10
            WHERE
                    T.GPV >= T.ACHIEVE_PERF_PV
              AND T.LAYER = MLAYER;

            SET MLAYER := MLAYER - 1;

        END WHILE;
    UPDATE AR_BS_BONUS_103_CALC
    SET GPV = 0
    WHERE
            USER_TYPE = 0
      AND GPV > 0;
    SELECT
        MAX( LAYER ) INTO MLAYER
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            USER_TYPE > 0;

    SET MMAXLAYER := MLAYER;-- 添加至新网体页面
    INSERT INTO AR_BS_BONUS_103_CALC_NET ( USER_ID, INTRODUCER_ID, CALC_PERIOD_ID, USER_TYPE, GPV, DIRECTLY_UNDER, LAYER, TEAM_GPV ) SELECT
                                                                                                                                         USER_ID,
                                                                                                                                         INTRODUCER_ID,
                                                                                                                                         CALC_PERIOD_ID,
                                                                                                                                         USER_TYPE,
                                                                                                                                         GPV,
                                                                                                                                         0,
                                                                                                                                         LAYER,
                                                                                                                                         0
    FROM
        AR_BS_BONUS_103_CALC
    WHERE
            LAYER <= MLAYER; -- 处理新的网体
    DELETE FROM AR_BS_BONUS_103_CALC_NET WHERE LAYER = MLAYER AND USER_TYPE = 0; SET MLAYER := MLAYER - 1; WHILE MLAYER >= 0 DO-- 计算直属宽度
    UPDATE AR_BS_BONUS_103_CALC_NET T1
        INNER JOIN ( SELECT INTRODUCER_ID, COUNT( * ) SONS FROM AR_BS_BONUS_103_CALC_NET WHERE LAYER = MLAYER + 1 GROUP BY INTRODUCER_ID ) T2 ON T1.USER_ID = T2.INTRODUCER_ID
            AND T1.LAYER = MLAYER
    SET T1.DIRECTLY_UNDER = T2.SONS;-- 本人达标并且下属满足一个及以上的达标数升级为第二个级别
    UPDATE AR_BS_BONUS_103_CALC_NET T1
    SET T1.USER_TYPE = 20
    WHERE
            T1.LAYER = MLAYER
      AND USER_TYPE = 10
      AND DIRECTLY_UNDER >= 1;-- 计算本层达标人员
    SELECT
        COUNT( * ) INTO MCOUNT
    FROM
        AR_BS_BONUS_103_CALC_NET C
    WHERE
            C.DIRECTLY_UNDER >= 2
      AND C.USER_TYPE = 0
      AND LAYER = MLAYER;
    IF
        ( MCOUNT > 0 ) THEN
        CALL CALC20_0 ( MLAYER, PCPID );

    END IF;-- 删除未达标人员
    CALL CALCDELNET ( MLAYER );

    SET MLAYER := MLAYER - 1;

        END WHILE;-- 计算GPV_4_CALC,会员级别
    UPDATE AR_BS_BONUS_103_CALC C1
        INNER JOIN AR_BS_BONUS_103_CALC_NET C2 ON C1.USER_ID = C2.USER_ID
    SET C1.GPV_4_CALC = C2.GPV,
        C1.USER_TYPE = C2.USER_TYPE;-- 计算见习奖金
    UPDATE AR_BS_BONUS_103_CALC C1
        INNER JOIN ( SELECT INTRODUCER_ID, SUM( GPV ) ALL_GPV FROM AR_BS_BONUS_103_CALC_NET GROUP BY INTRODUCER_ID ) C2 ON C1.USER_ID = C2.INTRODUCER_ID
            AND C1.USER_TYPE = 20
    SET C1.BONUS20 = C2.ALL_GPV * MRATE;
    COMMIT;
    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE, PERIOD_NUM ) SELECT
                                                                                                                           C1.USER_ID,
                                                                                                                           NULL,
                                                                                                                           20,
                                                                                                                           C2.ALL_GPV * MRATE,
                                                                                                                           C2.ALL_GPV,
                                                                                                                           MRATE,
                                                                                                                           PCPID
    FROM
        AR_BS_BONUS_103_CALC C1
            INNER JOIN ( SELECT INTRODUCER_ID, SUM( GPV ) ALL_GPV FROM AR_BS_BONUS_103_CALC_NET GROUP BY INTRODUCER_ID ) C2 ON C1.USER_ID = C2.INTRODUCER_ID
            AND C1.USER_TYPE = 20
            AND ALL_GPV > 0;

END
;;