NCDS_Procedure/CALC20.sql

DROP PROCEDURE IF EXISTS `CALC20`;
delimiter ;;
CREATE PROCEDURE `CALC20`(IN `PCPID` INT)
BEGIN
	DECLARE
		MLAYER,
		MMAXLAYER,
		MCOUNT INT;
	DECLARE
		MRATE,
		MLIMITPV DECIMAL ( 10, 2 );
	START TRANSACTION;-- 第一个级别的限制
	SELECT
		T.ACHIEVE_PV INTO MLIMITPV 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = 'E121497617216708615';-- 第二个级别得奖的比例
	SELECT
		T.BS_PERCENT / 100 INTO MRATE 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = '67BE5FE7857C216AE055736AECE8644D';
		
 	UPDATE AR_BS_BONUS_103_CALC SET PV = PV_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( GPV ) GPV 
				FROM
					AR_BS_BONUS_103_CALC 
				WHERE
					LAYER = MLAYER + 1 
					AND USER_TYPE = 0 
					AND GPV > 0 
				GROUP BY
					INTRODUCER_ID 
				) T2 ON T1.USER_ID = T2.INTRODUCER_ID 
				AND T1.LAYER = MLAYER 
				SET T1.GPV = T2.GPV;-- 没有升级的会员需要将自己的小组业绩清零
			UPDATE AR_BS_BONUS_103_CALC T 
			SET T.GPV = 0 
			WHERE
				T.GPV < MLIMITPV AND T.LAYER = MLAYER + 1 AND GPV > 0;
			
		END IF;
		UPDATE AR_BS_BONUS_103_CALC T1 
		SET T1.GPV = T1.GPV + T1.PV 
		WHERE
			T1.LAYER = MLAYER;-- 是否达标 如果达标
		UPDATE AR_BS_BONUS_103_CALC T 
		SET T.USER_TYPE = 10 
		WHERE
			T.GPV >= MLIMITPV 
			AND T.LAYER = MLAYER;
		
		SET MLAYER := MLAYER - 1;
		
	END WHILE;
	UPDATE AR_BS_BONUS_103_CALC 
	SET GPV = 0 
	WHERE
		USER_TYPE = 0 
		AND GPV > 0;
	SELECT
		MAX( LAYER ) INTO MLAYER 
	FROM
		AR_BS_BONUS_103_CALC 
	WHERE
		USER_TYPE > 0;
	
	SET MMAXLAYER := MLAYER;-- 添加至新网体页面
	INSERT INTO AR_BS_BONUS_103_CALC_NET ( USER_ID, INTRODUCER_ID, CALC_PERIOD_ID, USER_TYPE, GPV, DIRECTLY_UNDER, LAYER, TEAM_GPV ) SELECT
	USER_ID,
	INTRODUCER_ID,
	CALC_PERIOD_ID,
	USER_TYPE,
	GPV,
	0,
	LAYER,
	0 
	FROM
		AR_BS_BONUS_103_CALC 
	WHERE
		LAYER <= MLAYER; -- 处理新的网体
	DELETE FROM AR_BS_BONUS_103_CALC_NET WHERE LAYER = MLAYER AND USER_TYPE = 0; SET MLAYER := MLAYER - 1; WHILE MLAYER >= 0 DO-- 计算直属宽度
		UPDATE AR_BS_BONUS_103_CALC_NET T1
		INNER JOIN ( SELECT INTRODUCER_ID, COUNT( * ) SONS FROM AR_BS_BONUS_103_CALC_NET WHERE LAYER = MLAYER + 1 GROUP BY INTRODUCER_ID ) T2 ON T1.USER_ID = T2.INTRODUCER_ID 
		AND T1.LAYER = MLAYER 
		SET T1.DIRECTLY_UNDER = T2.SONS;-- 本人达标并且下属满足一个及以上的达标数升级为第二个级别
		UPDATE AR_BS_BONUS_103_CALC_NET T1 
		SET T1.USER_TYPE = 20 
		WHERE
			T1.LAYER = MLAYER 
			AND USER_TYPE = 10 
			AND DIRECTLY_UNDER >= 1;-- 计算本层达标人员
		SELECT
			COUNT( * ) INTO MCOUNT 
		FROM
			AR_BS_BONUS_103_CALC_NET C 
		WHERE
			C.DIRECTLY_UNDER >= 2 
			AND C.USER_TYPE = 0 
			AND LAYER = MLAYER;
		IF
			( MCOUNT > 0 ) THEN
				CALL CALC20_0 ( MLAYER, PCPID );
			
		END IF;-- 删除未达标人员
		CALL CALCDELNET ( MLAYER );
		
		SET MLAYER := MLAYER - 1;
		
	END WHILE;-- 计算GPV_4_CALC,会员级别
	UPDATE AR_BS_BONUS_103_CALC C1
	INNER JOIN AR_BS_BONUS_103_CALC_NET C2 ON C1.USER_ID = C2.USER_ID 
	SET C1.GPV_4_CALC = C2.GPV,
	C1.USER_TYPE = C2.USER_TYPE;-- 计算见习奖金
	UPDATE AR_BS_BONUS_103_CALC C1
	INNER JOIN ( SELECT INTRODUCER_ID, SUM( GPV ) ALL_GPV FROM AR_BS_BONUS_103_CALC_NET GROUP BY INTRODUCER_ID ) C2 ON C1.USER_ID = C2.INTRODUCER_ID 
	AND C1.USER_TYPE = 20 
	SET C1.BONUS20 = C2.ALL_GPV * MRATE;
	COMMIT;
	INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE, PERIOD_NUM ) SELECT
	C1.USER_ID,
	NULL,
	20,
	C2.ALL_GPV * MRATE,
	C2.ALL_GPV,
	MRATE,
	PCPID 
	FROM
		AR_BS_BONUS_103_CALC C1
		INNER JOIN ( SELECT INTRODUCER_ID, SUM( GPV ) ALL_GPV FROM AR_BS_BONUS_103_CALC_NET GROUP BY INTRODUCER_ID ) C2 ON C1.USER_ID = C2.INTRODUCER_ID 
		AND C1.USER_TYPE = 20 
		AND ALL_GPV > 0;

END
;;