DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALC10.sql

DROP PROCEDURE IF EXISTS `CALC10`;
delimiter ;;
CREATE PROCEDURE `CALC10`(IN `PCPID` INT)
BEGIN
    DECLARE
        MLAYER,
        MMAXLAYER INT;
    DECLARE
        MRATE,
        MLIMITPV DECIMAL ( 10, 2 );-- 查询第一个级别的限制以及等奖比例
    SELECT
        T.ACHIEVE_PV,
        T.BS_PERCENT / 100 INTO MLIMITPV,
        MRATE
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = 'E121497617216708615';
    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( GPV10 ) GPV10
                        FROM
                            AR_BS_BONUS_103_CALC
                        WHERE
                                LAYER = MLAYER + 1
                          AND USER_TYPE10 = 0
                          AND GPV10 > 0
                        GROUP BY
                            INTRODUCER_ID
                    ) T2 ON T1.USER_ID = T2.INTRODUCER_ID
                        AND T1.LAYER = MLAYER
                SET T1.GPV10 = T2.GPV10;-- 没有升级的会员需要将自己的小组业绩清零
                UPDATE AR_BS_BONUS_103_CALC T
                SET T.GPV10 = 0
                WHERE
                        T.GPV10 < T.ACHIEVE_PERF_PV AND T.LAYER = MLAYER + 1 AND USER_TYPE10 = 0 AND T.GPV10 > 0;

            END IF;
            UPDATE AR_BS_BONUS_103_CALC T1
            SET T1.GPV10 = T1.GPV10 + T1.PV
            WHERE
                    T1.LAYER = MLAYER
              AND T1.PV > 0;-- 是否达标 如果达标
            UPDATE AR_BS_BONUS_103_CALC T
            SET T.USER_TYPE10 = 10
            WHERE
                    T.GPV10 >= T.ACHIEVE_PERF_PV
              AND T.LAYER = MLAYER
              AND T.USER_TYPE10 = 0;
            IF
                ( MLAYER > 0 ) THEN-- 升级为蓝星A
                CALL CALCUPGARDE ( MLAYER );

            END IF;

            SET MLAYER := MLAYER - 1;

        END WHILE;
    UPDATE AR_BS_BONUS_103_CALC T
    SET T.BONUS10 = T.GPV10 * MRATE
    WHERE
            T.USER_TYPE10 = 10
      AND T.GPV10 > 0;
    UPDATE AR_BS_BONUS_103_CALC
    SET USER_TYPE = 0;

    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
    SELECT USER_ID, NULL, 10, GPV10 * MRATE, GPV10, MRATE ,PCPID FROM AR_BS_BONUS_103_CALC WHERE USER_TYPE10 = 10
                                                                                             AND GPV10 > 0;
END
;;