NCDS_Procedure/CALCLEVEL_.sql

DROP PROCEDURE IF EXISTS `CALCLEVEL_`;
CREATE PROCEDURE `CALCLEVEL_`(IN `PLAYER` INT,IN `PCPID` INT)
BEGIN
    DECLARE
        FLAG,
        MLAYER,
        MUSERTYPE INT DEFAULT 0;
    DECLARE
        MUSERID,
        MBONUSID,
        MNEWBONUSID VARCHAR ( 50 );
    DECLARE
        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;
    
    -- 打开游标
    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
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV, PERIOD_NUM )
                VALUES
                    ( MBONUSID, NULL, 30, MGPV ,PCPID);
                
                ELSEIF ( MLAYER = 2 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV, PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 40, MGPV,PCPID);
                
                ELSEIF ( MLAYER = 3 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 50, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 4 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 60, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 5 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 70, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 6 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 80, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 7 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 90, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 8 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 100, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 9 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 110, MGPV,  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
;;