NCDS_Procedure/CALCCLEAN.sql

DROP PROCEDURE IF EXISTS `CALCCLEAN`;
delimiter ;;
CREATE PROCEDURE `CALCCLEAN`(IN `PCPID` INT)
BEGIN-- 删除当期奖金
	DELETE 
	FROM
		AR_BS_BONUS_103_CALC;
	DELETE 
	FROM
		AR_BS_BONUS_103_TEST 
	WHERE
		CALC_PERIOD_ID >= PCPID;-- 使用的奖金表
	DELETE 
	FROM
		AR_CALC_BONUS_BS 
	WHERE
		PERIOD_NUM >= PCPID;
	DELETE 
	FROM
		AR_CALC_BONUS_BS_DETAIL 
	WHERE
		PERIOD_NUM >= PCPID;-- 删除当期网体
	DELETE 
	FROM
		AR_BS_BONUS_103_CALC_NET;
	DELETE 
	FROM
		AR_BS_BONUS_103_TEST_NET 
	WHERE
		CALC_PERIOD_ID >= PCPID;-- 删除拨出表
	DELETE 
	FROM
		AR_CALC_BONUS_BS_STAT 
	WHERE
		PERIOD_NUM >= PCPID;-- 删除明细表
	DELETE 
	FROM
		AR_CALC_BONUS_BS_DETAIL_BONUS 
	WHERE
		PERIOD_NUM >= PCPID;-- 删除日志
	DELETE 
	FROM
		AR_CALC_BONUS_BS_DETAIL_GPV 
	WHERE
		PERIOD_NUM >= PCPID;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCDELNET
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCDELNET`;
delimiter ;;
CREATE PROCEDURE `CALCDELNET`(IN `PLAYER` INT)
BEGIN
	DECLARE
		FLAG INT DEFAULT 0;
	DECLARE
		MUSERID,
		MMAXUSERID,
		MINTRODUCERID VARCHAR ( 50 );
	/*DECLARE
		MGPV DECIMAL ( 10, 2 );
	DECLARE
		DN CURSOR FOR SELECT
		USER_ID 
	FROM
		AR_BS_BONUS_103_CALC_NET 
	WHERE
		LAYER = PLAYER 
		AND USER_TYPE = 0;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;*/
	START TRANSACTION;-- 打开游标
	/*OPEN DN;-- 获取结果
	L2 :
	LOOP
			FETCH DN INTO MUSERID;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;-- 查询需要删除会员的推荐人ID
		SELECT
			T.INTRODUCER_ID INTO MINTRODUCERID 
		FROM
			AR_BS_BONUS_103_CALC_NET T 
		WHERE
			T.USER_ID = MUSERID;-- 更改需要删除会员第一代的推荐人ID以及层数
		UPDATE AR_BS_BONUS_103_CALC_NET T 
		SET T.INTRODUCER_ID = MINTRODUCERID,
		T.LAYER = T.LAYER - 1 
		WHERE
			T.INTRODUCER_ID = MUSERID;-- 关闭游标
		
	END LOOP;
	CLOSE DN;*/
	UPDATE AR_BS_BONUS_103_CALC_NET
	SET DELETED = 1
	WHERE
		LAYER = PLAYER 
		AND USER_TYPE = 0;
	UPDATE AR_BS_BONUS_103_CALC_NET T1
	INNER JOIN AR_BS_BONUS_103_CALC_NET T2
	ON T1.INTRODUCER_ID = T2.USER_ID AND T1.LAYER = PLAYER+1 AND T2.LAYER =  PLAYER AND T2.USER_TYPE = 0
	SET T1.INTRODUCER_ID = T2.INTRODUCER_ID,T1.LAYER = PLAYER;
	DELETE
	FROM
		AR_BS_BONUS_103_CALC_NET 
	WHERE
		LAYER = PLAYER 
		AND USER_TYPE = 0
		AND DELETED = 1;
	COMMIT;

END
;;