DS_CALC_Procedure/NGDS_CALC_PROCEDURE/CLEANACTIVE.sql

DROP PROCEDURE IF EXISTS `CLEANACTIVE`;
delimiter ;;
CREATE PROCEDURE `CLEANACTIVE`(IN `UNUMBER` VARCHAR(32))
BEGIN
DECLARE t_error INTEGER DEFAULT 0;    
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

START TRANSACTION;
  -- 备份用户的余额 奖金 兑换积分 复消积分
	INSERT INTO `AR_ACTIVED_ACCOUNT_BACK` ( `USER_ID`, `USER_NAME`, `USER_CASH`, `EXCHANGE_POINTS`, `EXCHANGE_POINTS_TOTAL`, `RECONSUME_POINTS`, `RECONSUME_POINTS_TOTAL`, `USER_BONUS`, `RECONSUME_POINTS_EXPIRED` ) SELECT
`AR_USER_BONUS`.`USER_ID`,
`AR_USER`.`USER_NAME`,
`AR_USER_WALLET`.`CASH`,
`AR_USER_BONUS`.`EXCHANGE_POINTS`,
`AR_USER_BONUS`.`EXCHANGE_POINTS_TOTAL`,
`AR_USER_BONUS`.`RECONSUME_POINTS`,
`AR_USER_BONUS`.`RECONSUME_POINTS_TOTAL`,
`AR_USER_BONUS`.`BONUS`,
`AR_USER_BONUS`.`RECONSUME_POINTS_EXPIRED` 
FROM
	`AR_USER_BONUS`
	INNER JOIN `AR_USER` ON `AR_USER`.`ID` = `AR_USER_BONUS`.`USER_ID`
	LEFT JOIN `AR_USER_WALLET` ON `AR_USER_WALLET`.`USER_ID` = `AR_USER_BONUS`.`USER_ID` 
WHERE
	`AR_USER`.`USER_NAME` = `UNUMBER`;

	-- 删除用户奖金
	DELETE  `UB`  FROM  `AR_USER_BONUS` `UB` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`UB`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	
	-- 清除用户的cash余额
	UPDATE `AR_USER_WALLET` INNER JOIN `AR_USER` ON `AR_USER`.`ID`=`AR_USER_WALLET`.`USER_ID`      SET  `AR_USER_WALLET`.`CASH`=0 WHERE `AR_USER`.`USER_NAME` = `UNUMBER`; 
	
	
	-- 删除用户累计业绩
	DELETE  `UP`  FROM  `AR_USER_PERF` `UP` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`UP`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	
	-- 删除用户总奖金发送记录
	DELETE  `CB`  FROM  `AR_CALC_BONUS` `CB` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CB`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	
	-- 删除用户各个奖金发送记录
	DELETE  `CBBD`  FROM  `AR_CALC_BONUS_BD` `CBBD` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBBD`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBBS`  FROM  `AR_CALC_BONUS_BS` `CBBS` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBBS`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBBT`  FROM  `AR_CALC_BONUS_BT` `CBBT` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBBT`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBCF`  FROM  `AR_CALC_BONUS_CF` `CBCF` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBCF`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBFL`  FROM  `AR_CALC_BONUS_FL` `CBFL` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBFL`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBFW`  FROM  `AR_CALC_BONUS_FW` `CBFW` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBFW`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBFX`  FROM  `AR_CALC_BONUS_FX` `CBFX` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBFX`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBGL`  FROM  `AR_CALC_BONUS_GL` `CBGL` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBGL`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBGX`  FROM  `AR_CALC_BONUS_GX` `CBGX` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBGX`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBLS`  FROM  `AR_CALC_BONUS_LS` `CBLS` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBLS`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBLX`  FROM  `AR_CALC_BONUS_LX` `CBLX` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBLX`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBQY`  FROM  `AR_CALC_BONUS_QY` `CBQY` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBQY`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBST`  FROM  `AR_CALC_BONUS_STANDARD` `CBST` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBST`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBTG`  FROM  `AR_CALC_BONUS_TG` `CBTG` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBTG`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBVIP`  FROM  `AR_CALC_BONUS_VIP` `CBVIP` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBVIP`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBXF`  FROM  `AR_CALC_BONUS_XF` `CBXF` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBXF`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBYC`  FROM  `AR_CALC_BONUS_YC` `CBYC` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBYC`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CBYJ`  FROM  `AR_CALC_BONUS_YJ` `CBYJ` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CBYJ`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `CMBU`  FROM  `AR_CALC_MONTH_BONUS_USER` `CMBU` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`CMBU`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FB`  FROM  `AR_FLOW_BONUS` `FB` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FB`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FC`  FROM  `AR_FLOW_CF` `FC` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FC`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FDZ`  FROM  `AR_FLOW_DEDUCT_ZR` `FDZ` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FDZ`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FEP`  FROM  `AR_FLOW_EXCHANGE_POINTS` `FEP` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FEP`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FLX`  FROM  `AR_FLOW_LX` `FLX` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FLX`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FRP`  FROM  `AR_FLOW_RECONSUME_POINTS` `FRP` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FRP`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `FW`  FROM  `AR_FLOW_WALLET` `FW` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`FW`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `HB`  FROM  `AR_HISTORY_BONUS` `HB` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`HB`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `UPEP`  FROM  `AR_USER_PERIOD_EXCHANGE_POINTS` `UPEP` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`UPEP`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `UPP`  FROM  `AR_USER_PERIOD_POINTS` `UPP` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`UPP`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `AW`  FROM  `AR_WITHDRAW` `AW` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`AW`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `AT`  FROM  `AR_TRANSFER` `AT` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`AT`.`OUT_UID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `ABA`  FROM  `AR_BALANCE_AUDIT` `ABA` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`ABA`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `R`  FROM  `AR_RECHARGE` `R` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`R`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	DELETE  `ASM`  FROM  `AR_SCORE_MONTH` `ASM` INNER JOIN `AR_USER` `U` ON `U`.`ID`=`ASM`.`USER_ID` 
	WHERE `U`.`USER_NAME` = `UNUMBER`;
	
	IF t_error = 1 THEN    
      ROLLBACK;    
  ELSE
	    COMMIT;
	END IF;
	select t_error;  -- 返回标识位的结果集
END
;;