NCDS_Procedure/CALCLEVEL.sql

DROP PROCEDURE IF EXISTS `CALCLEVEL`;
delimiter ;;
CREATE PROCEDURE `CALCLEVEL`(IN `PCPID` INT)
BEGIN
	DECLARE
		FLAG,
		MLAYER,
		MUSERTYPE INT DEFAULT 0;
	DECLARE
		MGPV,
		MLIMITPV DECIMAL ( 10, 2 );
	
		DECLARE
		MC CURSOR FOR SELECT
		LAYER
	FROM
		AR_BS_BONUS_103_CALC_NET T 
	WHERE
		T.USER_TYPE = 30 GROUP BY LAYER ORDER BY LAYER DESC;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;
	START TRANSACTION;-- 打开游标

	OPEN MC;-- 获取结果
	L2 :
	LOOP
			FETCH MC INTO MLAYER;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;
		
-- 计算当前层数的奖金以及向上所贡献的奖金
			CALL CALCLEVEL_ ( MLAYER ,PCPID);
	END LOOP;
	CLOSE MC;
	/*WHILE
			( MLAYER >= 0 ) DO-- 计算当前层数的奖金以及向上所贡献的奖金
			CALL CALCLEVEL_ ( MLAYER );
		
		SET MLAYER := MLAYER - 1;
		
	END WHILE;*/-- 级别更新
	

	UPDATE AR_BS_BONUS_103_CALC C1
	INNER JOIN AR_BS_BONUS_103_CALC_NET C2 ON C1.USER_ID = C2.USER_ID 
	AND C1.USER_TYPE <> C2.USER_TYPE 
	SET C1.USER_TYPE = C2.USER_TYPE;
	
	SELECT
		T.ACHIEVE_PV INTO MLIMITPV 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = 'E121497617216708615';

	UPDATE AR_BS_BONUS_103_CALC SET USER_TYPE = 0 WHERE USER_TYPE = 10;
	UPDATE AR_BS_BONUS_103_CALC SET USER_TYPE = 10 WHERE GPV10 >= MLIMITPV AND USER_TYPE = 0;
	
END
;;