DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALCLEVEL_kevin_01.sql

DROP PROCEDURE IF EXISTS `CALCLEVEL_kevin_01`;
delimiter ;;
CREATE PROCEDURE `CALCLEVEL_kevin_01`(IN PLAYER int, IN PCPID int)
BEGIN
    DECLARE
        FLAG,
        MLAYER,
        MUSERTYPE INT DEFAULT 0;
    DECLARE
        MUSERID,
        MBONUSID,
        MNEWBONUSID VARCHAR ( 50 );
    DECLARE
        MRATE,
        MRATE30,
        MRATE40,
        MRATE50,
        MRATE60,
        MRATE70,
        MRATE80,
        MRATE90,
        MRATE100,
        MRATE110,
        MBONUS,
        MGPV DECIMAL ( 10, 2 );
    DECLARE
        MC CURSOR FOR SELECT
                          C.USER_ID,
                          C.TEAM_GPV
                      FROM
                          AR_BS_BONUS_103_CALC_NET C
                      WHERE
                              C.USER_TYPE >= 30
                        AND LAYER = PLAYER;
    DECLARE
        CONTINUE HANDLER FOR NOT FOUND
        SET FLAG = 1;
    START TRANSACTION;
    SELECT
            T.BS_PERCENT / 100 INTO MRATE30
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = 'E121497617216708616';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE40
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE6A4D03C52288E055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE50
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE6EA2070D22EBE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE60
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE742A336F2370E055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE70
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE805032C22492E055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE80
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE82019BE524CAE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE90
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '99BE5FE7857C216AE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE100
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '99BE5FE9008C216AE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE110
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '99BE5FE7857C216AE000223AECE8644D';-- 打开游标
    OPEN MC;-- 获取结果
    L2 :
    LOOP
        FETCH MC INTO MUSERID,
            MGPV;
        IF
                FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
            LEAVE L2;

        END IF;-- 这里是为了显示获取结果

        SET MLAYER := 1;

        SET MBONUSID := MUSERID;
        OUTER_LABEL :
        WHILE
            ( MLAYER <= 9 ) DO
                IF
                    ( MLAYER > 1 ) THEN
                    CALL CALCREFEREE ( MBONUSID, MNEWBONUSID );
                    IF
                        ( ISNULL( MNEWBONUSID ) OR MNEWBONUSID = 0 ) THEN
                        LEAVE OUTER_LABEL;
                    ELSE
                        SET MBONUSID := MNEWBONUSID;

                    END IF;

                END IF;
                IF
                    ( MLAYER = 1 ) THEN

                    SET MRATE := MRATE30;

                ELSEIF ( MLAYER = 2 ) THEN

                    SET MRATE := MRATE40;

                ELSEIF ( MLAYER = 3 ) THEN

                    SET MRATE := MRATE50;

                ELSEIF ( MLAYER = 4 ) THEN

                    SET MRATE := MRATE60;

                ELSEIF ( MLAYER = 5 ) THEN

                    SET MRATE := MRATE70;

                ELSEIF ( MLAYER = 6 ) THEN

                    SET MRATE := MRATE80;

                ELSEIF ( MLAYER = 7 ) THEN

                    SET MRATE := MRATE90;

                ELSEIF ( MLAYER = 8 ) THEN

                    SET MRATE := MRATE100;

                ELSEIF ( MLAYER = 9 ) THEN

                    SET MRATE := MRATE110;

                END IF;

                SET MBONUS := MGPV * MRATE;
                IF
                    ( MLAYER = 1 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS30 = T.BONUS30 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, NULL, 30, MBONUS, MGPV, MRATE ,PCPID);

                ELSEIF ( MLAYER = 2 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS40 = T.BONUS40 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 40, MBONUS, MGPV, MRATE ,PCPID);

                ELSEIF ( MLAYER = 3 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS50 = T.BONUS50 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 50, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 4 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS60 = T.BONUS60 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 60, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 5 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS70 = T.BONUS70 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 70, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 6 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS80 = T.BONUS80 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 80, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 7 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS90 = T.BONUS90 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 90, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 8 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS100 = T.BONUS100 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 100, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 9 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS110 = T.BONUS110 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 110, MBONUS, MGPV, MRATE,PCPID );

                END IF;-- 计算会员的级别
                UPDATE AR_BS_BONUS_103_CALC_NET T
                SET T.USER_TYPE = ( MLAYER + 2 )* 10
                WHERE
                        T.USER_ID = MBONUSID
                  AND USER_TYPE <=(
                                          MLAYER + 1
                                      )* 10;

                SET MLAYER := MLAYER + 1;

            END WHILE;-- 关闭游标

    END LOOP;
    CLOSE MC;
    COMMIT;

END
;;