DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CALCLEVEL_.sql

DROP PROCEDURE IF EXISTS `CALCLEVEL_`;
delimiter ;;
CREATE PROCEDURE `CALCLEVEL_`(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 <= 5 ) 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
;;