procedure/ng_Director/ng二星下属业绩上推规则变更.sql

/*
MySQL Backup
Database: ng110test
Backup Time: 2023-03-21 14:24:48
*/

SET FOREIGN_KEY_CHECKS=0;
DROP PROCEDURE IF EXISTS `ng110test`.`CALC20_0`;
CREATE DEFINER=`admin`@`%` PROCEDURE `CALC20_0`(IN PLAYER INT,IN `PCPID` INT)
BEGIN
	DECLARE
		FLAG INT DEFAULT 0;
	DECLARE
		MUSERID,
		MMAXUSERID VARCHAR ( 50 );
	DECLARE
		MGPV DECIMAL ( 10, 2 );
	DECLARE
		ACHIEVEPV DECIMAL ( 10, 2 );
	DECLARE
		MC CURSOR FOR SELECT
		C.USER_ID 
	FROM
		AR_BS_BONUS_103_CALC_NET C 
	WHERE
		C.DIRECTLY_UNDER >= 2 
		AND C.USER_TYPE = 0 
		AND LAYER = PLAYER;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;-- 打开游标
	OPEN MC;-- 获取结果
	L2 :
	LOOP
			FETCH MC INTO MUSERID;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;-- 这里是为了显示获取结果
		SELECT
			MIN( GPV ) INTO MGPV 
		FROM
			AR_BS_BONUS_103_CALC_NET C 
		WHERE
			C.INTRODUCER_ID = MUSERID 
			AND C.USER_TYPE >= 10;
			
		SELECT
			MAX( USER_ID ) INTO MMAXUSERID 
		FROM
			AR_BS_BONUS_103_CALC_NET C 
		WHERE
			C.INTRODUCER_ID = MUSERID 
			AND C.GPV = MGPV;
			
		SELECT
		  ACHIEVE_PV INTO ACHIEVEPV
		FROM
		  AR_EMPLOY_LEVEL
		WHERE ID ='E121497617216708615';
			
		IF MGPV >= ACHIEVEPV THEN
		
		UPDATE AR_BS_BONUS_103_CALC_NET C 
		SET C.USER_TYPE = 20,
		C.GPV = ACHIEVEPV 
		WHERE
			USER_ID = MUSERID;
			
		UPDATE AR_BS_BONUS_103_CALC_NET C 
		SET C.GPV = MGPV - ACHIEVEPV 
		WHERE
			USER_ID = MMAXUSERID;
			
		INSERT INTO AR_CALC_BONUS_BS_DETAIL_GPV(USER_ID,INTRODUCER_ID,GPV,PERIOD_NUM)
		VALUES(MMAXUSERID,MUSERID,ACHIEVEPV,PCPID);
			
		ELSE
		
		UPDATE AR_BS_BONUS_103_CALC_NET C 
		SET C.USER_TYPE = 20,
		C.GPV = MGPV 
		WHERE
			USER_ID = MUSERID;
			
		UPDATE AR_BS_BONUS_103_CALC_NET C 
		SET C.GPV = 0 
		WHERE
			USER_ID = MMAXUSERID;
			
		INSERT INTO AR_CALC_BONUS_BS_DETAIL_GPV(USER_ID,INTRODUCER_ID,GPV,PERIOD_NUM)
		VALUES(MMAXUSERID,MUSERID,MGPV,PCPID);
			
		END IF;
			
	END LOOP;
	
	-- 关闭游标
	CLOSE MC;

END;