NCDS_Procedure/CALCTOTALGPV_.sql

DROP PROCEDURE IF EXISTS `CALCTOTALGPV_`;
delimiter ;;
CREATE PROCEDURE `CALCTOTALGPV_`(IN `PLAYER` INT, -- 第几层
	IN `PMINLAYER` INT)
BEGIN
	DECLARE
		FLAG,
		MLAYER,
		MINTLAYER,
		MUSERTYPE,
		MINTTYPE INT DEFAULT 0;
	DECLARE
		MGPV DECIMAL ( 10, 2 );
	DECLARE
		MUSERID,
		MUSERINTID,
		MINTUSERID,
		MINTRODUCERID VARCHAR ( 50 );
	DECLARE
		MC CURSOR FOR SELECT
		C.USER_ID,
		C.INTRODUCER_ID,
		C.USER_TYPE,
		C.GPV 
	FROM
		(
		SELECT
			USER_ID,
			IFNULL( INTRODUCER_ID, '0' ) INTRODUCER_ID,
			USER_TYPE,
			GPV 
		FROM
			AR_BS_BONUS_103_CALC_NET 
		WHERE
			LAYER = PLAYER 
			AND GPV > 0 
			OR ( GPV = 0 AND USER_TYPE = 30 ) 
		ORDER BY
			USER_ID DESC 
		) C;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;
	START TRANSACTION;-- 打开游标
	OPEN MC;-- 获取结果
	L2 :
	LOOP
			FETCH MC INTO MUSERID,
			MUSERINTID,
			MUSERTYPE,
			MGPV;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;
		SET MLAYER = PLAYER;
		
		SET MINTUSERID = MUSERINTID;
		IF
			( MINTUSERID != '0' ) THEN
				OUTLABEL :
			WHILE
					MLAYER > PMINLAYER DO
				SELECT
					INTRODUCER_ID,
					USER_TYPE,
					LAYER INTO MINTRODUCERID,
					MINTTYPE,
					MLAYER 
				FROM
					AR_BS_BONUS_103_CALC_NET 
				WHERE
					USER_ID = MINTUSERID;
				IF
					( MINTUSERID != MUSERINTID AND MINTTYPE = 30 ) THEN
						UPDATE AR_BS_BONUS_103_CALC_NET 
						SET TEAM_GPV = TEAM_GPV + MGPV 
					WHERE
						USER_ID = MINTUSERID;
					IF
						( MUSERTYPE = 30 ) THEN
							UPDATE AR_BS_BONUS_103_CALC_NET 
							SET INTRODUCER_ID30 = MINTUSERID 
						WHERE
							USER_ID = MUSERID 
							AND INTRODUCER_ID30 IS NULL;
						
					END IF;
					LEAVE OUTLABEL;
					
				END IF;
				
				SET MINTUSERID = MINTRODUCERID;
				
			END WHILE;
			
		END IF;-- 关闭游标
		
	END LOOP;
	CLOSE MC;
	COMMIT;

END
;;