NCDS_Procedure/test_test.sql

-- Active: 1688690638810@@127.0.0.1@3306
/*
 Navicat Premium Data Transfer

 Source Server         : NDS测试数据库
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : 16.163.228.151:8051
 Source Schema         : test_test

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 06/07/2023 17:24:33
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- Procedure structure for CALC10
-- ----------------------------
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
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALC20
-- ----------------------------
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
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALC20_0
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALC20_0`;
delimiter ;;
CREATE 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
		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;
		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 LOOP;
	CLOSE MC;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCBLUE
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCBLUE`;
delimiter ;;
CREATE PROCEDURE `CALCBLUE`(IN `PCPID` INT)
LABEL: BEGIN
DECLARE MCALCYEAR INT;
DECLARE MISMONTH,MCALCMONTH,MISSENT TINYINT;

START TRANSACTION;
-- 日志;
DELETE FROM AR_BS_BONUS_103_CALC_EVENT ;-- WHERE CALC_PERIOD_ID >= PCPID;
CALL CALCRECORD(-1,'开始',PCPID,NOW());
COMMIT;
-- SELECT USER_ID INTO MISMONTH FROM ZR_USER;
SELECT AP.IS_MONTH,AP.CALC_MONTH,AP.CALC_YEAR,AP.IS_SENT
INTO MISMONTH,MCALCMONTH,MCALCYEAR,MISSENT
FROM AR_PERIOD AP WHERE AP.PERIOD_NUM = PCPID;

	-- 查看是否月结
IF(MISMONTH  =  0 OR MISSENT = 1)THEN
LEAVE LABEL;
END IF;


-- 清除数据
CALL CALCRECORD(1,'清除数据',PCPID,NOW());
COMMIT;
CALL CALCCLEAN(PCPID);
COMMIT;

-- 初始化
CALL CALCRECORD(2,'初始化',PCPID,NOW());
COMMIT;
CALL CALCINIT(PCPID,MCALCMONTH,MCALCYEAR);
COMMIT;


-- 初始业绩
-- 3.蓝星奖
CALL CALCRECORD(3,'蓝星奖',PCPID,NOW());
COMMIT;
CALL CALC10(PCPID);
COMMIT;

-- 4.新网体
CALL CALCRECORD(4,'新网体',PCPID,NOW());
COMMIT;
CALL CALC20(PCPID);
COMMIT;

-- 5.升级主任
CALL CALCRECORD(5,'升级主任',PCPID,NOW());
COMMIT;
CALL CALCUP30;
COMMIT;

-- 6.计算主任自己第一层的总业绩
CALL CALCRECORD(6,'计算主任自己第一层的总业绩',PCPID,NOW());
COMMIT;
CALL CALCTOTALGPV;
COMMIT;

-- 7.计算平级奖业绩和级别
CALL CALCRECORD(7,'计算平级奖业绩和级别',PCPID,NOW());
COMMIT;
CALL CALCLEVEL(PCPID);
COMMIT;

-- 8.计算平级奖
CALL CALCRECORD(8,'计算平级奖',PCPID,NOW());
COMMIT;
CALL CALCPJ(PCPID);
COMMIT;

-- 9.积分
CALL CALCRECORD(9,'积分',PCPID,NOW());
COMMIT;
CALL CALCPOINT;
COMMIT;

-- 10.汇总
CALL CALCRECORD(10,'汇总',PCPID,NOW());
COMMIT;
CALL CALCSUMMARY;
COMMIT;

-- 11.记录总表
CALL CALCRECORD(11,'记录总表',PCPID,NOW());
COMMIT;
CALL CALCKEEP(MCALCYEAR,MCALCMONTH);
COMMIT;

-- 12.计算拨出比
CALL CALCRECORD(12,'计算拨出比',PCPID,NOW());
COMMIT;
CALL CALCSTAT(PCPID);
COMMIT;

CALL CALCRECORD(13,'结束',PCPID,NOW());
COMMIT;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCCHECKING
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCCHECKING`;
delimiter ;;
CREATE PROCEDURE `CALCCHECKING`(IN `PCPID` INT)
LABEL : BEGIN-- 遍历数据结束标志
	DECLARE
		MCOLUMNNAME,
		MVALUES,
		MVALUES0,
		MVALUES1,
		MVALUES2 VARCHAR ( 100 );-- 定义预处理SQL语句
	DECLARE
		SQL_FOR_SELECT VARCHAR ( 2000 );
	DECLARE
		MCOUNT INT DEFAULT 0;
	DECLARE
		DONE INT DEFAULT FALSE;
	DECLARE
		MISMONTH,
		MISSEND TINYINT;-- 游标
	DECLARE
		CUR_ACCOUNT CURSOR FOR SELECT
		COLUMN_NAME 
	FROM
		INFORMATION_SCHEMA.COLUMNS 
	WHERE
		TABLE_NAME = 'AR_CALC_BONUS_BS_DETAIL' 
		AND TABLE_SCHEMA = 'aikang_db_28' 
		AND COLUMN_NAME NOT IN ( 'CALC_YEAR', 'CALC_MONTH', 'P_CALC_MONTH', 'CREATED_AT', 'ID', 'LAYER' ) 
		AND DATA_TYPE IN ( 'DECIMAL', 'DOUBLE', 'INT' );-- 将结束标志绑定到游标
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET DONE = TRUE;
	START TRANSACTION;
	SELECT
		AP.IS_MONTH,
		AP.IS_SENT INTO MISMONTH,
		MISSEND 
	FROM
		AR_PERIOD AP 
	WHERE
		AP.PERIOD_NUM = PCPID;-- 查看是否月结
	IF
		( MISMONTH = 0 OR MISSEND = 1 ) THEN
			LEAVE LABEL;
		
	END IF;-- 打开游标
	DELETE 
	FROM
		AR_CALC_BONUS_BS_CHECKING 
	WHERE
		PERIOD_NUM >= PCPID;
	OPEN CUR_ACCOUNT;-- 遍历
	READ_LOOP :
	LOOP-- 取值 取多个字段
		FETCH NEXT 
		FROM
			CUR_ACCOUNT INTO MCOLUMNNAME;
		IF
			DONE THEN
				LEAVE READ_LOOP;
			
		END IF;
		
		SET SQL_FOR_SELECT = CONCAT(
			"SELECT COUNT(*) INTO @MVALUES0 FROM (SELECT IFNULL(A1.USER_ID,'-1') MAIN_ID,IFNULL(A2.USER_ID,'-1') TEST_ID,IFNULL(A1.",
			MCOLUMNNAME,
			",0) NAME1,IFNULL(A2.",
			MCOLUMNNAME,
			",0) NAME2 FROM AR_CALC_BONUS_BS_DETAIL A1 LEFT JOIN AR_CALC_BONUS_BS_DETAIL_CALC A2 ON A1.USER_ID = A2.USER_ID WHERE A1.PERIOD_NUM = ",
			PCPID,
			" AND A2.PERIOD_NUM = ",
			PCPID,
			" UNION SELECT IFNULL(A2.USER_ID,'-1') MAIN_ID,IFNULL(A1.USER_ID,'-1') TEST_ID,IFNULL(A2.",
			MCOLUMNNAME,
			",0) NAME1,IFNULL(A1.",
			MCOLUMNNAME,
			",0) NAME2 FROM AR_CALC_BONUS_BS_DETAIL_CALC A1 LEFT JOIN AR_CALC_BONUS_BS_DETAIL A2 ON A1.USER_ID = A2.USER_ID WHERE A1.PERIOD_NUM = ",
			PCPID,
			" AND A2.PERIOD_NUM = ",
			PCPID,
			") B WHERE B.MAIN_ID != B.TEST_ID OR ABS(B.NAME1-B.NAME2)>0.5" 
		);-- 拼接查询SQL语句
		
		SET @SQL = SQL_FOR_SELECT;
		PREPARE STMT 
		FROM
			@SQL;-- 预处理动态SQL语句
		EXECUTE STMT;-- 执行SQL语句
		DEALLOCATE PREPARE STMT;
		
		SET MVALUES0 := @MVALUES0;
		
		SET SQL_FOR_SELECT = CONCAT( "SELECT IFNULL(SUM(", MCOLUMNNAME, "),0) INTO @MVALUES1 FROM  AR_CALC_BONUS_BS_DETAIL  WHERE PERIOD_NUM = '", PCPID, "'" );-- 拼接查询SQL语句
		
		SET @SQL = SQL_FOR_SELECT;
		PREPARE STMT 
		FROM
			@SQL;-- 预处理动态SQL语句
		EXECUTE STMT;-- 执行SQL语句
		DEALLOCATE PREPARE STMT;
		
		SET MVALUES1 := @MVALUES1;
		
		SET SQL_FOR_SELECT = CONCAT( "SELECT IFNULL(SUM(", MCOLUMNNAME, "),0) INTO @MVALUES2 FROM  AR_CALC_BONUS_BS_DETAIL_CALC  WHERE USER_ID != '0' AND PERIOD_NUM = '", PCPID, "'" );-- 拼接查询SQL语句
		
		SET @SQL = SQL_FOR_SELECT;
		PREPARE STMT 
		FROM
			@SQL;-- 预处理动态SQL语句
		EXECUTE STMT;-- 执行SQL语句
		DEALLOCATE PREPARE STMT;
		
		SET MVALUES2 := @MVALUES2;
		INSERT INTO AR_CALC_BONUS_BS_CHECKING ( PERIOD_NUM, COLUMN_NAME, DIFF_COUNT, MAIN_SUM, CALC_SUM, DIFF_SUM )
		VALUES
			(
				PCPID,
				MCOLUMNNAME,
				MVALUES0,
				MVALUES1,
				MVALUES2,
				ABS(
					CONVERT (
						MVALUES1,
						DECIMAL ( 12, 2 ))- CONVERT (
						MVALUES2,
					DECIMAL ( 12, 2 ))));
		COMMIT;
		
	END LOOP;
	CLOSE CUR_ACCOUNT;
	SELECT
		COUNT(*) INTO MCOUNT 
	FROM
		AR_CALC_BONUS_BS_CHECKING 
	WHERE
		( DIFF_COUNT > 0 OR DIFF_SUM >= 1 ) 
		AND COLUMN_NAME IN (
			'USER_TYPE10',
			'USER_TYPE',
			'BONUS10',
			'BONUS20',
			'BONUS30',
			'BONUS40',
			'BONUS50',
			'BONUS60',
			'BONUS70',
			'BONUS80',
			'BONUS90',
			'BONUS100',
			'BONUS110',
			'ORI_BONUS',
			'AMOUNT',
			'PRODUCT_POINT' 
		);
	IF
		( MCOUNT = 0 ) THEN
			UPDATE AR_CALC_BONUS_BS_STAT T 
			SET T.CHECK_STATUS = 1 
		WHERE
			T.PERIOD_NUM = PCPID;
		
	END IF;
	COMMIT;

END
;
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCCLEAN
-- ----------------------------
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
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCINIT
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCINIT`;
delimiter ;;
CREATE PROCEDURE `CALCINIT`(IN `PCPID` INT, IN `PCALCMONTH` TINYINT, IN `PCALCYEAR` INT)
BEGIN
    INSERT INTO AR_BS_BONUS_103_CALC (
        LAST_DEC_LV,
        LAST_EMP_LV,
        LAST_STATUS,
        USER_ID,
        INTRODUCER_ID,
        LAYER,
        PV,
        GPV10,
        GPV,
        GPV_4_CALC,
        USER_TYPE10,
        USER_TYPE,
        BONUS10,
        BONUS20,
        BONUS30,
        BONUS40,
        BONUS50,
        BONUS60,
        BONUS70,
        BONUS80,
        BONUS90,
        BONUS100,
        BONUS110,
        BONUS,
        PRODUCT_POINT,
        CALC_PERIOD_ID,
				PV_ZC,
				PV_FX,
				PV_UP_ZC
    ) SELECT
    AU.LAST_DEC_LV,
    AU.EMP_LV,
    AU.STATUS,
    AU.ID,
    IFNULL( AURN.PARENT_UID, 0 ),
    AURN.TOP_DEEP,
    IFNULL( AO.PV, 0 ),
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    PCPID,
		(SELECT
            IFNULL( SUM( PV ), 0 ) PV
        FROM
            AR_PERF_ORDER 
        WHERE
            PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH)
            AND USER_ID=AU.ID AND DEC_TYPE='ZC')PV_ZC,
		(SELECT
            IFNULL( SUM( PV ), 0 ) PV
        FROM
            AR_PERF_ORDER 
        WHERE
            PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH)
            AND USER_ID=AU.ID AND DEC_TYPE='FX')PV_FX,
		(SELECT
            IFNULL( SUM( O.PV ), 0 ) PV
        FROM
            AR_PERF_ORDER O
						LEFT JOIN AR_USER_RELATION_NEW R ON R.USER_ID = O.USER_ID
        WHERE
            O.PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH)
            AND R.PARENT_UID=AU.ID AND O.DEC_TYPE='ZC')PV_UP_ZC
    FROM
        AR_USER AU
        INNER JOIN AR_USER_RELATION_NEW AURN ON AU.ID = AURN.USER_ID
        LEFT JOIN (
        SELECT
            USER_ID,
            SUM( PV ) PV 
        FROM
            AR_PERF_ORDER 
        WHERE
            PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH ) 
        GROUP BY
            USER_ID 
        ) AO ON AO.USER_ID = AU.ID 
    WHERE
        AU.DELETED = 0;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCKEEP
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCKEEP`;
delimiter ;;
CREATE PROCEDURE `CALCKEEP`(IN `PCALCYEAR` INT,IN `PCALCMONTH` INT)
BEGIN
	INSERT INTO AR_BS_BONUS_103_TEST SELECT
	* 
	FROM
		AR_BS_BONUS_103_CALC;
		
		
	INSERT INTO AR_BS_BONUS_103_TEST_NET SELECT
	* 
	FROM
		AR_BS_BONUS_103_CALC_NET;
		
		
	INSERT INTO AR_CALC_BONUS_BS (
		USER_ID,
		INTRODUCER_ID,
		LAST_DEC_LV,
		LAST_EMP_LV,
		LAST_STATUS,
		LEVEL_ID,
		ORI_BONUS,
		MANAGE_TAX,
		AMOUNT,
		PRODUCT_POINT,
		PERIOD_NUM,
		CALC_YEAR,
		CALC_MONTH,
		P_CALC_MONTH,
		CREATED_AT ,
		
		ORI_BONUS_ABBR,
        MANAGE_TAX_ABBR,
        AMOUNT_ABBR,
        ABBR_RECONSUME_POINTS,
        ORI_BONUS_MNT,
        MANAGE_TAX_MNT,
        AMOUNT_MNT,
        MNT_RECONSUME_POINTS
		
		
	) SELECT
	A.USER_ID,
	A.INTRODUCER_ID,
	A.LAST_DEC_LV,
	A.LAST_EMP_LV,
	A.LAST_STATUS,
	B.ID,
	IFNULL( A.BONUS, 0 ),
	0,
	IFNULL( A.BONUS, 0 ),
	IFNULL( A.PRODUCT_POINT, 0 ),
	CALC_PERIOD_ID,
	PCALCYEAR,
	PCALCYEAR*100+PCALCMONTH,
	NOW(),
	UNIX_TIMESTAMP(
	NOW()),
	
	IFNULL(A.BONUSYJ,0),
	0 ,
	IFNULL(A.BONUSYJ,0),
	0,
	IFNULL(A.BONUSGL,0),
	0,
	IFNULL(A.BONUSGL,0),
	0
	
	FROM
		AR_BS_BONUS_103_CALC A
		LEFT JOIN AR_EMPLOY_LEVEL B ON A.USER_TYPE = B.SORT * 10 
	WHERE
		A.BONUS > 0 
		OR A.PRODUCT_POINT > 0;
	INSERT INTO AR_CALC_BONUS_BS_DETAIL (
		USER_ID,
		INTRODUCER_ID,
		LAST_DEC_LV,
		LAST_EMP_LV,
		LAST_STATUS,
		LEVEL_ID,
		ORI_BONUS,
		MANAGE_TAX,
		AMOUNT,
		PRODUCT_POINT,
		PERIOD_NUM,
		LAYER,
		PV,
		GPV10,
		GPV,
		GPV_4_CALC,
		USER_TYPE10,
		USER_TYPE,
		BONUS10,
		BONUS20,
		BONUS30,
		BONUS40,
		BONUS50,
		BONUS60,
		BONUS70,
		BONUS80,
		BONUS90,
		BONUS100,
		BONUS110,
		CALC_YEAR,
		CALC_MONTH,
		P_CALC_MONTH,
		CREATED_AT ,
		
		ORI_BONUS_ABBR,

        AMOUNT_ABBR,
        ORI_BONUS_MNT,

        AMOUNT_MNT
		
		
	) SELECT
	USER_ID,
	INTRODUCER_ID,
	LAST_DEC_LV,
	LAST_EMP_LV,
	LAST_STATUS,
	B.ID,
	IFNULL( BONUS, 0 ),
	0,
	IFNULL( BONUS, 0 ),
	IFNULL( PRODUCT_POINT, 0 ),
	CALC_PERIOD_ID,
	LAYER,
	PV,
	GPV10,
	GPV,
	GPV_4_CALC,
	USER_TYPE10,
	USER_TYPE,
	BONUS10,
	BONUS20,
	BONUS30,
	BONUS40,
	BONUS50,
	BONUS60,
	BONUS70,
	BONUS80,
	BONUS90,
	BONUS100,
	BONUS110,
	PCALCYEAR,
	PCALCYEAR*100+PCALCMONTH,
	NOW(),
	UNIX_TIMESTAMP(
	NOW()) ,
	
	IFNULL(A.BONUSYJ,0),

	IFNULL(A.BONUSYJ,0),
	IFNULL(A.BONUSGL,0),

	IFNULL(A.BONUSGL,0)
	FROM
		AR_BS_BONUS_103_CALC A
		LEFT JOIN ( SELECT ID, SORT FROM AR_EMPLOY_LEVEL ) B ON A.USER_TYPE = B.SORT * 10;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCLEVEL
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCLEVEL`;
delimiter ;;
CREATE PROCEDURE `CALCLEVEL`(IN `PCPID` INT)
BEGIN
	DECLARE
		FLAG,
		MLAYER,
		MUSERTYPE INT DEFAULT 0;
	DECLARE
		MGPV,
		MLIMITPV DECIMAL ( 10, 2 );
	
		DECLARE
		MC CURSOR FOR SELECT
		LAYER
	FROM
		AR_BS_BONUS_103_CALC_NET T 
	WHERE
		T.USER_TYPE = 30 GROUP BY LAYER ORDER BY LAYER DESC;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;
	START TRANSACTION;-- 打开游标

	OPEN MC;-- 获取结果
	L2 :
	LOOP
			FETCH MC INTO MLAYER;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;
		
-- 计算当前层数的奖金以及向上所贡献的奖金
			CALL CALCLEVEL_ ( MLAYER ,PCPID);
	END LOOP;
	CLOSE MC;
	/*WHILE
			( MLAYER >= 0 ) DO-- 计算当前层数的奖金以及向上所贡献的奖金
			CALL CALCLEVEL_ ( MLAYER );
		
		SET MLAYER := MLAYER - 1;
		
	END WHILE;*/-- 级别更新
	

	UPDATE AR_BS_BONUS_103_CALC C1
	INNER JOIN AR_BS_BONUS_103_CALC_NET C2 ON C1.USER_ID = C2.USER_ID 
	AND C1.USER_TYPE <> C2.USER_TYPE 
	SET C1.USER_TYPE = C2.USER_TYPE;
	
	SELECT
		T.ACHIEVE_PV INTO MLIMITPV 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = 'E121497617216708615';

	UPDATE AR_BS_BONUS_103_CALC SET USER_TYPE = 0 WHERE USER_TYPE = 10;
	UPDATE AR_BS_BONUS_103_CALC SET USER_TYPE = 10 WHERE GPV10 >= MLIMITPV AND USER_TYPE = 0;
	
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCLEVEL_
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCLEVEL_`;
delimiter ;;
CREATE PROCEDURE `CALCLEVEL_`(IN `PLAYER` INT,IN `PCPID` INT)
BEGIN
    DECLARE
        FLAG,
        MLAYER,
        MUSERTYPE INT DEFAULT 0;
    DECLARE
        MUSERID,
        MBONUSID,
        MNEWBONUSID VARCHAR ( 50 );
    DECLARE
        MGPV DECIMAL ( 10, 2 );
    
    DECLARE
        MC CURSOR FOR SELECT
        C.USER_ID,
        C.TEAM_GPV 
    FROM
        AR_BS_BONUS_103_CALC_NET C 
    WHERE
        C.USER_TYPE >= 30 
        AND LAYER = PLAYER;
    
    DECLARE
        CONTINUE HANDLER FOR NOT FOUND 
        SET FLAG = 1;
    START TRANSACTION;
    
    -- 打开游标
    OPEN MC;-- 获取结果
    L2 :
    LOOP
            FETCH MC INTO MUSERID,
            MGPV;
        IF
            FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
                LEAVE L2;
            
        END IF;-- 这里是为了显示获取结果
        
        SET MLAYER := 1;
        
        SET MBONUSID := MUSERID;
        OUTER_LABEL :
        WHILE
                ( MLAYER <= 9 ) DO
            IF
                ( MLAYER > 1 ) THEN
                    CALL CALCREFEREE ( MBONUSID, MNEWBONUSID );
                IF
                    ( ISNULL( MNEWBONUSID ) OR MNEWBONUSID = 0 ) THEN
                        LEAVE OUTER_LABEL;
                    ELSE 
                        SET MBONUSID := MNEWBONUSID;
                    
                END IF;
                
            END IF;
            
            IF
                ( MLAYER = 1 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV, PERIOD_NUM )
                VALUES
                    ( MBONUSID, NULL, 30, MGPV ,PCPID);
                
                ELSEIF ( MLAYER = 2 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV, PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 40, MGPV,PCPID);
                
                ELSEIF ( MLAYER = 3 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 50, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 4 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 60, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 5 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 70, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 6 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 80, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 7 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 90, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 8 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 100, MGPV,  PCPID );
                
                ELSEIF ( MLAYER = 9 ) THEN
                INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, PV,  PERIOD_NUM )
                VALUES
                    ( MBONUSID, MUSERID, 110, MGPV,  PCPID );
                
            END IF;-- 计算会员的级别
            UPDATE AR_BS_BONUS_103_CALC_NET T 
            SET T.USER_TYPE = ( MLAYER + 2 )* 10 
            WHERE
                T.USER_ID = MBONUSID 
                AND USER_TYPE <=(
                    MLAYER + 1 
                )* 10;
            
            SET MLAYER := MLAYER + 1;
            
        END WHILE;-- 关闭游标
        
    END LOOP;
    CLOSE MC;
    COMMIT;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCMAIN
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCMAIN`;
delimiter ;;
CREATE PROCEDURE `CALCMAIN`(IN `PCPID` int,OUT `PRESULT` varchar(100))
BEGIN
	#Routine body goes here...
DECLARE EXIT HANDLER FOR SQLEXCEPTION  
SET PRESULT = 'FAIL';
 CALL CalcBlue(PCPID);
SET PRESULT = 'SUCCESS';
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCPJ
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCPJ`;
delimiter ;;
CREATE PROCEDURE `CALCPJ`(IN `PCPID` int)
BEGIN
DECLARE USERID VARCHAR (100);
DECLARE TOTALPV,
PV3,
PV4,
PV5,
PV6,
PV7,
PV8,
PV9,
PV10,
PV11,
PERCENT1,
PERCENT2,
REWARD_BONUS1,
REWARD_BONUS2,
REWARD_BONUS_TOTAL DECIMAL (16,2);
DECLARE RATE DECIMAL (16,8);
DECLARE RATE2 DECIMAL (16,8);
DECLARE DONE INT DEFAULT TRUE;
DECLARE DONE2 INT DEFAULT TRUE;

DECLARE CUR CURSOR FOR SELECT DISTINCT(USER_ID) FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND LAST_EMP_LV>=30 AND PV>0;
DECLARE CUR2 CURSOR FOR SELECT DISTINCT(USER_ID) FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND LAST_EMP_LV>=30 AND PV>0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = FALSE;


SELECT IFNULL(MAX(INCREASE_PERCENT),0) INTO PERCENT1 FROM AR_BS_BONUS_REWARD_PARAMETER WHERE LEVEL_ID='67BE5FE7857C216AE055736AECE8644D';

SELECT IFNULL(MAX(INCREASE_PERCENT),0) INTO PERCENT2 FROM AR_BS_BONUS_REWARD_PARAMETER WHERE LEVEL_ID='E121497617216708616';

-- 备份AR_BS_BONUS_REWARD_PARAMETER
DELETE FROM AR_BS_BONUS_REWARD_PARAMETER_HISTROY WHERE PERIOD_NUM=PCPID;
INSERT INTO AR_BS_BONUS_REWARD_PARAMETER_HISTROY (LEVEL_ID,LEVEL_NAME,INCREASE_PERCENT,PERIOD_NUM) (SELECT LEVEL_ID,LEVEL_NAME,INCREASE_PERCENT,PCPID FROM AR_BS_BONUS_REWARD_PARAMETER);

-- 平级奖计奖业绩
SELECT SUM(PV) INTO TOTALPV FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND LAST_EMP_LV>=30;

-- 平级奖奖金和业绩占比
SELECT (SUM(PV)*0.15)/TOTALPV INTO RATE FROM AR_BS_BONUS_103_CALC;

OPEN CUR;

WHILE DONE DO

FETCH CUR INTO USERID;

IF DONE THEN

SELECT SUM(PV) INTO PV3 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=30;
UPDATE AR_BS_BONUS_103_CALC SET BONUS30 = PV3 * RATE WHERE USER_ID=USERID;

END IF;

END WHILE;

CLOSE CUR;

SELECT SUM(BONUS20)*PERCENT1 INTO REWARD_BONUS1 FROM AR_BS_BONUS_103_CALC WHERE USER_TYPE=20;

SELECT SUM(BONUS30)*PERCENT2 INTO REWARD_BONUS2 FROM AR_BS_BONUS_103_CALC WHERE USER_TYPE=30;

-- 备份高级专员的加发奖金
UPDATE AR_BS_BONUS_103_CALC SET REWARD20 = BONUS20*PERCENT1 WHERE USER_TYPE=20;

-- 备份主任的加发奖金
UPDATE AR_BS_BONUS_103_CALC SET REWARD30 = BONUS30*PERCENT2 WHERE USER_TYPE=30;

-- 特别奖励加发总奖金
SET REWARD_BONUS_TOTAL=REWARD_BONUS1+REWARD_BONUS2;

-- 平级奖奖金和业绩占比
SELECT (SUM(PV)*0.15 -REWARD_BONUS_TOTAL)/TOTALPV INTO RATE2 FROM AR_BS_BONUS_103_CALC;
-- 备份平级奖比例
UPDATE AR_BS_BONUS_103_CALC SET RATE = RATE2;

SET DONE = TRUE;

OPEN CUR2;

WHILE DONE DO

FETCH CUR2 INTO USERID;

IF DONE THEN

SELECT SUM(PV) INTO PV3 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=30;
UPDATE AR_BS_BONUS_103_CALC SET BONUS30 = PV3 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV4 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=40;
UPDATE AR_BS_BONUS_103_CALC SET BONUS40 = PV4 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV5 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=50;
UPDATE AR_BS_BONUS_103_CALC SET BONUS50 = PV5 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV6 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=60;
UPDATE AR_BS_BONUS_103_CALC SET BONUS60 = PV6 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV7 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=70;
UPDATE AR_BS_BONUS_103_CALC SET BONUS70 = PV7 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV8 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=80;
UPDATE AR_BS_BONUS_103_CALC SET BONUS80 = PV8 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV9 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=90;
UPDATE AR_BS_BONUS_103_CALC SET BONUS90 = PV9 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV10 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=100;
UPDATE AR_BS_BONUS_103_CALC SET BONUS100 = PV10 * RATE2 WHERE USER_ID=USERID;

SELECT SUM(PV) INTO PV11 FROM AR_CALC_BONUS_BS_DETAIL_BONUS WHERE PERIOD_NUM=PCPID AND USER_ID=USERID AND LAST_EMP_LV=110;
UPDATE AR_BS_BONUS_103_CALC SET BONUS110 = PV11 * RATE2 WHERE USER_ID=USERID;

END IF;

END WHILE;

CLOSE CUR2;

-- 达到高级专员加发高级专员奖
UPDATE AR_BS_BONUS_103_CALC SET BONUS20 = REWARD20 + BONUS20 WHERE USER_TYPE=20;

-- 达到主任加发主任奖
UPDATE AR_BS_BONUS_103_CALC SET BONUS30 = REWARD30 + BONUS30 WHERE USER_TYPE=30;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCPOINT
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCPOINT`;
delimiter ;;
CREATE PROCEDURE `CALCPOINT`()
BEGIN
	DECLARE
		MPOINTRATE DECIMAL ( 10, 2 );
	SELECT 
	VALUE
		/ 100 INTO MPOINTRATE 
	FROM
		AR_CONFIG 
	WHERE
		CONFIG_NAME = 'bsNoPvPointsPercent';
	UPDATE AR_BS_BONUS_103_CALC 
	SET PRODUCT_POINT = PV * MPOINTRATE 
	WHERE
		USER_TYPE10 = 0 
		AND USER_TYPE = 0 
		AND PV > 0;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCRECORD
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCRECORD`;
delimiter ;;
CREATE PROCEDURE `CALCRECORD`(IN `PID` INT, IN `PEVENT` VARCHAR ( 255 ), IN `PCPID` INT, IN `PNOW` DATETIME)
BEGIN
	INSERT INTO AR_BS_BONUS_103_CALC_EVENT ( ID, EVENT, CREATION_TIME, CALC_PERIOD_ID )
	VALUES
		( PID, PEVENT, NOW(), PCPID );

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCREFEREE
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCREFEREE`;
delimiter ;;
CREATE PROCEDURE `CALCREFEREE`(IN `PBONUSID` VARCHAR ( 50 ),
	OUT `PNEWBONUSID` VARCHAR ( 50 ))
BEGIN
	DECLARE
		MUSERID,
		MBONUSID VARCHAR ( 50 );
	DECLARE
		MUSERTYPE INT;
	
	/*SET MUSERID := PBONUSID;
	OUTER_LABEL :
	WHILE
			(
			ISNULL( MBONUSID )) DO
		SELECT
			INTRODUCER_ID INTO MUSERID 
		FROM
			AR_BS_BONUS_103_CALC_NET T 
		WHERE
			T.USER_ID = MUSERID;
		IF
			( MUSERID = 0 ) THEN
				
				SET PNEWBONUSID := NULL;
			LEAVE OUTER_LABEL;
			
		END IF;
		SELECT
			USER_TYPE INTO MUSERTYPE 
		FROM
			AR_BS_BONUS_103_CALC_NET T 
		WHERE
			T.USER_ID = MUSERID;
		IF
			( MUSERTYPE >= 30 ) THEN
				
				SET PNEWBONUSID := MUSERID;
			LEAVE OUTER_LABEL;
			
		END IF;
		
	END WHILE;*/
  SELECT INTRODUCER_ID30 INTO PNEWBONUSID FROM AR_BS_BONUS_103_CALC_NET WHERE USER_ID = PBONUSID;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCSTAT
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCSTAT`;
delimiter ;;
CREATE PROCEDURE `CALCSTAT`(IN `PCPID` INT)
BEGIN
	INSERT INTO AR_CALC_BONUS_BS_STAT (
		PERIOD_NUM,
		TOTAL_PV,
		BONUS10,
		PERCENT10,
		BONUS20,
		PERCENT20,
		BONUS30,
		PERCENT30,
		BONUS40,
		PERCENT40,
		BONUS50,
		PERCENT50,
		BONUS60,
		PERCENT60,
		BONUS70,
		PERCENT70,
		BONUS80,
		PERCENT80,
		BONUS90,
		PERCENT90,
		BONUS100,
		PERCENT100,
		BONUS110,
		PERCENT110,
		BONUS,
		PERCENT,
		CHECK_STATUS ,
		BONUS_ABBR,
        BONUS_ABBR_PERCENT,
        BONUS_MNT,
        BONUS_MNT_PERCENT
	) SELECT
	PCPID,
	SUM( PV ),
	SUM( BONUS10 ),
	0,
	SUM( BONUS20 ),
	0,
	SUM( BONUS30 ),
	0,
	SUM( BONUS40 ),
	0,
	SUM( BONUS50 ),
	0,
	SUM( BONUS60 ),
	0,
	SUM( BONUS70 ),
	0,
	SUM( BONUS80 ),
	0,
	SUM( BONUS90 ),
	0,
	SUM( BONUS100 ),
	0,
	SUM( BONUS110 ),
	0,
	SUM( BONUS ),
	0,
	0 ,
	SUM( BONUSYJ),
	0,
	SUM(BONUSGL),
	0
	FROM
		AR_BS_BONUS_103_CALC;
	UPDATE AR_CALC_BONUS_BS_STAT 
	SET PERCENT10 = BONUS10 / TOTAL_PV,
	PERCENT20 = BONUS20 / TOTAL_PV,
	PERCENT30 = BONUS30 / TOTAL_PV,
	PERCENT40 = BONUS40 / TOTAL_PV,
	PERCENT50 = BONUS50 / TOTAL_PV,
	PERCENT60 = BONUS60 / TOTAL_PV,
	PERCENT70 = BONUS70 / TOTAL_PV,
	PERCENT80 = BONUS80 / TOTAL_PV,
	PERCENT90 = BONUS90 / TOTAL_PV,
	PERCENT100 = BONUS100 / TOTAL_PV,
	PERCENT110 = BONUS110 / TOTAL_PV,
	PERCENT = BONUS / TOTAL_PV,
	BONUS_ABBR_PERCENT = BONUS_ABBR / TOTAL_PV,
	BONUS_MNT_PERCENT = BONUS_MNT /TOTAL_PV
	WHERE
		PERIOD_NUM = PCPID 
		AND TOTAL_PV > 0;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCSUMMARY
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCSUMMARY`;
delimiter ;;
CREATE PROCEDURE `CALCSUMMARY`()
BEGIN
	UPDATE AR_BS_BONUS_103_CALC T 
	SET T.BONUS = BONUS10 + BONUS20 + BONUS30 + BONUS40 + BONUS50 + BONUS60 + BONUS70 + BONUS80 + BONUS90 + BONUS100 + BONUS110,T.BONUSYJ = BONUS10 + BONUS20,T.BONUSGL = BONUS30 + BONUS40 +BONUS50 + BONUS60 + BONUS70 +BONUS80 +BONUS90 +BONUS100 + BONUS110 
	WHERE
		T.BONUS10 > 0 
		OR T.BONUS20 > 0 
		OR T.BONUS30 > 0 
		OR T.BONUS40 > 0 
		OR T.BONUS50 > 0 
		OR T.BONUS60 > 0 
		OR T.BONUS70 > 0 
		OR T.BONUS80 > 0 
		OR T.BONUS90 > 0 
		OR T.BONUS100 > 0 
		OR T.BONUS110 > 0;
	UPDATE AR_BS_BONUS_103_CALC T 
	SET GPV = 0 
	WHERE
		T.INTRODUCER_ID = '0' 
		AND USER_TYPE = 0;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCTOTALGPV
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCTOTALGPV`;
delimiter ;;
CREATE PROCEDURE `CALCTOTALGPV`()
BEGIN
	DECLARE
		MLAYER,
		MMINLAYER,
		MUSERTYPE INT DEFAULT 0;
	DECLARE
		MUSERID,
		MMAXUSERID VARCHAR ( 50 );
	DECLARE
		MGPV DECIMAL ( 10, 2 );
	SELECT
		MAX( LAYER ) INTO MLAYER 
	FROM
		AR_BS_BONUS_103_CALC_NET 
	WHERE
		GPV > 0;
	SELECT
		MIN( LAYER ) INTO MMINLAYER 
	FROM
		AR_BS_BONUS_103_CALC_NET 
	WHERE
		USER_TYPE = 30;
	UPDATE AR_BS_BONUS_103_CALC_NET T1
	INNER JOIN AR_BS_BONUS_103_CALC_NET T2 ON IFNULL( T1.INTRODUCER_ID, '0' ) = T2.USER_ID 
	AND T1.USER_TYPE = 30 
	AND T2.USER_TYPE = 30 
	SET T1.INTRODUCER_ID30 = T2.USER_ID;
	WHILE
			MLAYER > MMINLAYER DO
			CALL CALCTOTALGPV_ ( MLAYER, MMINLAYER );
		
		SET MLAYER = MLAYER - 1;
		
	END WHILE;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCTOTALGPV_
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCTOTALGPV_`;
delimiter ;;
CREATE PROCEDURE `CALCTOTALGPV_`(IN `PLAYER` INT, -- 第几层
	IN `PMINLAYER` INT)
BEGIN
	DECLARE
		FLAG,
		MLAYER,
		MINTLAYER,
		MUSERTYPE,
		MINTTYPE INT DEFAULT 0;
	DECLARE
		MGPV DECIMAL ( 10, 2 );
	DECLARE
		MUSERID,
		MUSERINTID,
		MINTUSERID,
		MINTRODUCERID VARCHAR ( 50 );
	DECLARE
		MC CURSOR FOR SELECT
		C.USER_ID,
		C.INTRODUCER_ID,
		C.USER_TYPE,
		C.GPV 
	FROM
		(
		SELECT
			USER_ID,
			IFNULL( INTRODUCER_ID, '0' ) INTRODUCER_ID,
			USER_TYPE,
			GPV 
		FROM
			AR_BS_BONUS_103_CALC_NET 
		WHERE
			LAYER = PLAYER 
			AND GPV > 0 
			OR ( GPV = 0 AND USER_TYPE = 30 ) 
		ORDER BY
			USER_ID DESC 
		) C;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET FLAG = 1;
	START TRANSACTION;-- 打开游标
	OPEN MC;-- 获取结果
	L2 :
	LOOP
			FETCH MC INTO MUSERID,
			MUSERINTID,
			MUSERTYPE,
			MGPV;
		IF
			FLAG = 1 THEN-- 当无法FETCH会触发HANDLER CONTINUE
				LEAVE L2;
			
		END IF;
		SET MLAYER = PLAYER;
		
		SET MINTUSERID = MUSERINTID;
		IF
			( MINTUSERID != '0' ) THEN
				OUTLABEL :
			WHILE
					MLAYER > PMINLAYER DO
				SELECT
					INTRODUCER_ID,
					USER_TYPE,
					LAYER INTO MINTRODUCERID,
					MINTTYPE,
					MLAYER 
				FROM
					AR_BS_BONUS_103_CALC_NET 
				WHERE
					USER_ID = MINTUSERID;
				IF
					( MINTUSERID != MUSERINTID AND MINTTYPE = 30 ) THEN
						UPDATE AR_BS_BONUS_103_CALC_NET 
						SET TEAM_GPV = TEAM_GPV + MGPV 
					WHERE
						USER_ID = MINTUSERID;
					IF
						( MUSERTYPE = 30 ) THEN
							UPDATE AR_BS_BONUS_103_CALC_NET 
							SET INTRODUCER_ID30 = MINTUSERID 
						WHERE
							USER_ID = MUSERID 
							AND INTRODUCER_ID30 IS NULL;
						
					END IF;
					LEAVE OUTLABEL;
					
				END IF;
				
				SET MINTUSERID = MINTRODUCERID;
				
			END WHILE;
			
		END IF;-- 关闭游标
		
	END LOOP;
	CLOSE MC;
	COMMIT;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCUP30
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCUP30`;
delimiter ;;
CREATE PROCEDURE `CALCUP30`()
BEGIN
	START TRANSACTION;
	UPDATE AR_BS_BONUS_103_CALC_NET T1 
	SET T1.USER_TYPE = 30 
	WHERE
		USER_TYPE >= 10 
		AND DIRECTLY_UNDER >= 3;
	COMMIT;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CALCUPGARDE
-- ----------------------------
DROP PROCEDURE IF EXISTS `CALCUPGARDE`;
delimiter ;;
CREATE PROCEDURE `CALCUPGARDE`(IN `PLAYER` INT)
BEGIN
	DECLARE
		MLAYER,
		MCOUNT,
		MCOUNT1,
		MCOUNT2 INT;
	
	SET MLAYER := PLAYER;
	SELECT
		COUNT(*) INTO MCOUNT 
	FROM
		AR_BS_BONUS_103_CALC T 
	WHERE
		T.LAYER = MLAYER 
		AND USER_TYPE10 = 10 
		AND USER_TYPE = 0;
	IF
		( MCOUNT > 0 ) THEN
			BREAKLABLE :
		WHILE
				MLAYER > 0 DO
			SELECT
				COUNT(*) INTO MCOUNT1 
			FROM
				AR_BS_BONUS_103_CALC T1 
			WHERE
				T1.USER_TYPE10 = 10 
				AND T1.LAYER = MLAYER - 1;
			UPDATE AR_BS_BONUS_103_CALC T1
			INNER JOIN ( SELECT INTRODUCER_ID FROM AR_BS_BONUS_103_CALC WHERE LAYER = MLAYER AND USER_TYPE10 = 10 GROUP BY INTRODUCER_ID ) T2 ON T1.USER_ID = T2.INTRODUCER_ID 
			AND T1.USER_TYPE10 = 0 
			AND T1.LAYER = MLAYER - 1 
			SET T1.USER_TYPE10 = 10,
			T1.USER_TYPE = 10;
			SELECT
				COUNT(*) INTO MCOUNT2 
			FROM
				AR_BS_BONUS_103_CALC T1 
			WHERE
				T1.USER_TYPE10 = 10 
				AND T1.LAYER = MLAYER - 1;
			IF
				( MCOUNT1 = MCOUNT2 ) THEN
					LEAVE BREAKLABLE;
				
			END IF;
			
			SET MLAYER := MLAYER - 1;
			
		END WHILE;
		
	END IF;

END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for UPPERCASE
-- ----------------------------
DROP PROCEDURE IF EXISTS `UPPERCASE`;
delimiter ;;
CREATE PROCEDURE `UPPERCASE`(IN DBNAME VARCHAR(200))
BEGIN 
  
DECLARE DONE INT DEFAULT 0;  
  
DECLARE OLDNAME VARCHAR(200);  
  
DECLARE CUR CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DBNAME;  
  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;  
  
OPEN CUR;  
  
REPEAT  
  
FETCH CUR INTO OLDNAME;  
  
SET @NEWNAME = UPPER(OLDNAME);  
SET @ISNOTSAME = @NEWNAME <> BINARY OLDNAME;   
IF NOT DONE && @ISNOTSAME THEN   
SET @SQL = CONCAT('RENAME TABLE `',OLDNAME,'` TO `', LOWER(@NEWNAME), '_TMP` ');   
PREPARE TMPSTMT FROM @SQL;   
EXECUTE TMPSTMT;  

SET @SQL = CONCAT('RENAME TABLE `',LOWER(@NEWNAME),'_TMP` TO `',@NEWNAME, '`');   
PREPARE TMPSTMT FROM @SQL;   
EXECUTE TMPSTMT; 
  
DEALLOCATE PREPARE TMPSTMT;  
  
END IF;   
UNTIL DONE END REPEAT;   
CLOSE CUR;   
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;