NCDS_Procedure/CALC10.sql

DROP PROCEDURE IF EXISTS `CALC10`;
delimiter ;;
CREATE PROCEDURE `CALC10`(IN `PCPID` INT)
BEGIN
	DECLARE
		MLAYER,
		MMAXLAYER INT;
	DECLARE
		MRATE,
		MLIMITPV DECIMAL ( 10, 2 );-- 查询第一个级别的限制以及等奖比例
	SELECT
		T.ACHIEVE_PV,
		T.BS_PERCENT / 100 INTO MLIMITPV,
		MRATE 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = 'E121497617216708615';
		
 UPDATE AR_BS_BONUS_103_CALC SET PV = PV_UP_ZC + PV_FX;
	
	SELECT
		MAX( LAYER ) INTO MLAYER 
	FROM
		AR_BS_BONUS_103_CALC 
	WHERE
		PV > 0;
	
	SET MMAXLAYER := MLAYER;
	WHILE
			MLAYER >= 0 DO
		IF
			( MLAYER < MMAXLAYER ) THEN-- 向上累计
				UPDATE AR_BS_BONUS_103_CALC T1
				INNER JOIN (
				SELECT
					INTRODUCER_ID,
					SUM( GPV10 ) GPV10 
				FROM
					AR_BS_BONUS_103_CALC 
				WHERE
					LAYER = MLAYER + 1 
					AND USER_TYPE10 = 0 
					AND GPV10 > 0 
				GROUP BY
					INTRODUCER_ID 
				) T2 ON T1.USER_ID = T2.INTRODUCER_ID 
				AND T1.LAYER = MLAYER 
				SET T1.GPV10 = T2.GPV10;-- 没有升级的会员需要将自己的小组业绩清零
			UPDATE AR_BS_BONUS_103_CALC T 
			SET T.GPV10 = 0 
			WHERE
				T.GPV10 < MLIMITPV AND T.LAYER = MLAYER + 1 AND USER_TYPE10 = 0 AND T.GPV10 > 0;
			
		END IF;
		UPDATE AR_BS_BONUS_103_CALC T1 
		SET T1.GPV10 = T1.GPV10 + T1.PV 
		WHERE
			T1.LAYER = MLAYER 
			AND T1.PV > 0;-- 是否达标 如果达标
		UPDATE AR_BS_BONUS_103_CALC T 
		SET T.USER_TYPE10 = 10 
		WHERE
			T.GPV10 >= MLIMITPV 
			AND T.LAYER = MLAYER 
			AND T.USER_TYPE10 = 0;
		IF
			( MLAYER > 0 ) THEN-- 升级为蓝星A
				CALL CALCUPGARDE ( MLAYER );
			
		END IF;
		
		SET MLAYER := MLAYER - 1;
		
	END WHILE;
	UPDATE AR_BS_BONUS_103_CALC T 
	SET T.BONUS10 = T.GPV10 * MRATE 
	WHERE
		T.USER_TYPE10 = 10 
		AND T.GPV10 > 0;
	UPDATE AR_BS_BONUS_103_CALC 
	SET USER_TYPE = 0;
	
	INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				SELECT USER_ID, NULL, 10, GPV10 * MRATE, GPV10, MRATE ,PCPID FROM AR_BS_BONUS_103_CALC WHERE USER_TYPE10 = 10 
		AND GPV10 > 0;
END
;;