ngds/sql/coco_milk_0524_fc.sql

-- MariaDB dump 10.19  Distrib 10.7.4-MariaDB, for Linux (x86_64)
--
-- Host: ngds-db-live.cdehazle30lc.ap-east-1.rds.amazonaws.com    Database: coco_milk_0524
-- ------------------------------------------------------
-- Server version	10.6.7-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping routines for database 'coco_milk_0524'
--
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALC10` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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';
    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 < T.ACHIEVE_PERF_PV 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 >= T.ACHIEVE_PERF_PV
              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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALC20` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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';
    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 < T.ACHIEVE_PERF_PV 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 >= T.ACHIEVE_PERF_PV
              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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALC20_0` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCBLUE` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 CALCPOINT;
COMMIT;

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

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

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

CALL CALCRECORD(12,'结束',PCPID,NOW());
COMMIT;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCCHECKING` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCCLEAN` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCDELNET` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCINIT` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `CALCINIT`(IN `PCPID` INT, IN `PCALCMONTH` TINYINT, IN `PCALCYEAR` INT)
BEGIN

    DECLARE
        AC_USER_NO INT;     -- 邀请会员达标数
    DECLARE
        MLIMITPV,   -- 邀请会员不及格达标PV
        AC_PERF_PV DECIMAL ( 10, 2 );   -- 邀请会员及格达标PV

    -- 查询第一个级别的邀请会员数、达标PV值以及等奖比例
    SELECT
        T.ACHIEVE_PV,
        T.ACHIEVE_PERF_PV,
        T.ACHIEVE_MEMBER_NUM
    INTO MLIMITPV, AC_PERF_PV, AC_USER_NO
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
        T.ID = 'E121497617216708615';

    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,
        ACHIEVE_MEMBER_NUM,
        ACHIEVE_PERF_PV
    ) 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,
          IFNULL(DO.ACHIEVE_MEMBER_NUM, 0),
          IF(DO.ACHIEVE_MEMBER_NUM >= AC_USER_NO, AC_PERF_PV, MLIMITPV)
    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
    LEFT JOIN (
        SELECT
            R.PARENT_UID,
            COUNT(R.USER_ID) AS ACHIEVE_MEMBER_NUM
        FROM
            AR_PERF_ORDER O
            INNER JOIN AR_USER_RELATION_NEW R ON O.USER_ID = R.USER_ID
        WHERE
            O.DEC_TYPE = 'ZC' AND O.PERIOD_NUM IN ( SELECT PERIOD_NUM FROM AR_PERIOD WHERE CALC_YEAR = PCALCYEAR AND CALC_MONTH = PCALCMONTH )
        GROUP BY R.PARENT_UID
    ) DO ON DO.PARENT_UID = AU.ID
    WHERE
            AU.DELETED = 0;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCKEEP` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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_MNT,
        MANAGE_TAX_MNT,
        AMOUNT_MNT,
        ORI_BONUS_ABBR,
        MANAGE_TAX_ABBR,
        AMOUNT_ABBR,
        ACHIEVE_MEMBER_NUM,
        ACHIEVE_PERF_PV
    ) 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.BONUS_MNT, 0 ),
          0,
          IFNULL( A.BONUS_MNT, 0 ),
          IFNULL( A.BONUS_ABBR, 0 ),
          0,
          IFNULL( A.BONUS_ABBR, 0 ),
          A.ACHIEVE_MEMBER_NUM,
          A.ACHIEVE_PERF_PV
    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_MNT,
        MANAGE_TAX_MNT,
        AMOUNT_MNT,
        ORI_BONUS_ABBR,
        MANAGE_TAX_ABBR,
        AMOUNT_ABBR
    ) 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.BONUS_MNT, 0 ),
          0,
          IFNULL( A.BONUS_MNT, 0 ),
          IFNULL( A.BONUS_ABBR, 0 ),
          0,
          IFNULL( A.BONUS_ABBR, 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCLEVEL` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `CALCLEVEL`(IN `PCPID` INT)
BEGIN
	DECLARE
		FLAG,
		MLAYER,
		MUSERTYPE INT DEFAULT 0;
	DECLARE
		MGPV 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;



END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCLEVEL_` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `CALCLEVEL_`(IN PLAYER int, IN PCPID int)
BEGIN
	DECLARE
		FLAG,
		MLAYER,
		MUSERTYPE INT DEFAULT 0;
	DECLARE
		MUSERID,
		MBONUSID,
		MNEWBONUSID VARCHAR ( 50 );
	DECLARE
		MRATE,
		MRATE30,
		MRATE40,
		MRATE50,
		MRATE60,
		MRATE70,
		MRATE80,
		MRATE90,
		MRATE100,
		MRATE110,
		MBONUS,
		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;

	-- 取各级别蓝星奖比例【开始】
	SELECT
		T.BS_PERCENT / 100 INTO MRATE30 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = 'E121497617216708616';

	SELECT
		T.BS_PERCENT / 100 INTO MRATE40 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = '67BE6A4D03C52288E055736AECE8644D';

	SELECT
		T.BS_PERCENT / 100 INTO MRATE50 
	FROM
		AR_EMPLOY_LEVEL T 
	WHERE
		T.ID = '67BE6EA2070D22EBE055736AECE8644D';

	SELECT
		T.BS_PERCENT / 100 INTO MRATE60
	FROM
		AR_EMPLOY_LEVEL T
	WHERE
		T.ID = '67BE742A336F2370E055736AECE8644D';

	SELECT
		T.BS_PERCENT / 100 INTO MRATE70
	FROM
		AR_EMPLOY_LEVEL T
	WHERE
		T.ID = '67BE805032C22492E055736AECE8644D';

# 	SELECT
# 		T.BS_PERCENT / 100 INTO MRATE80
# 	FROM
# 		AR_EMPLOY_LEVEL T
# 	WHERE
# 		T.ID = '67BE82019BE524CAE055736AECE8644D';
#
# 	SELECT
# 		T.BS_PERCENT / 100 INTO MRATE90
# 	FROM
# 		AR_EMPLOY_LEVEL T
# 	WHERE
# 		T.ID = '99BE5FE7857C216AE055736AECE8644D';
#
# 	SELECT
# 		T.BS_PERCENT / 100 INTO MRATE100
# 	FROM
# 		AR_EMPLOY_LEVEL T
# 	WHERE
# 		T.ID = '99BE5FE9008C216AE055736AECE8644D';
#
# 	SELECT
# 		T.BS_PERCENT / 100 INTO MRATE110
# 	FROM
# 		AR_EMPLOY_LEVEL T
# 	WHERE
# 		T.ID = '99BE5FE7857C216AE000223AECE8644D';
    -- 取各级别蓝星奖比例【结束】

    -- 打开游标
	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 <= 5 ) 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
					
					SET MRATE := MRATE30;
				
				ELSEIF ( MLAYER = 2 ) THEN
				
				SET MRATE := MRATE40;
				
				ELSEIF ( MLAYER = 3 ) THEN
				
				SET MRATE := MRATE50;
				
				ELSEIF ( MLAYER = 4 ) THEN
				
				SET MRATE := MRATE60;
				
				ELSEIF ( MLAYER = 5 ) THEN
				
				SET MRATE := MRATE70;
				
				ELSEIF ( MLAYER = 6 ) THEN
				
				SET MRATE := MRATE80;
				
				ELSEIF ( MLAYER = 7 ) THEN
				
				SET MRATE := MRATE90;
				
				ELSEIF ( MLAYER = 8 ) THEN
				
				SET MRATE := MRATE100;
				
				ELSEIF ( MLAYER = 9 ) THEN
				
				SET MRATE := MRATE110;
				
			END IF;
			
			SET MBONUS := MGPV * MRATE;
			IF
				( MLAYER = 1 ) THEN
					UPDATE AR_BS_BONUS_103_CALC T 
					SET T.BONUS30 = T.BONUS30 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, NULL, 30, MBONUS, MGPV, MRATE ,PCPID);
				
				ELSEIF ( MLAYER = 2 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS40 = T.BONUS40 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 40, MBONUS, MGPV, MRATE ,PCPID);
				
				ELSEIF ( MLAYER = 3 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS50 = T.BONUS50 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 50, MBONUS, MGPV, MRATE,PCPID );
				
				ELSEIF ( MLAYER = 4 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS60 = T.BONUS60 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 60, MBONUS, MGPV, MRATE,PCPID );
				
				ELSEIF ( MLAYER = 5 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS70 = T.BONUS70 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 70, MBONUS, MGPV, MRATE,PCPID );
				
				ELSEIF ( MLAYER = 6 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS80 = T.BONUS80 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 80, MBONUS, MGPV, MRATE,PCPID );
				
				ELSEIF ( MLAYER = 7 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS90 = T.BONUS90 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 90, MBONUS, MGPV, MRATE,PCPID );
				
				ELSEIF ( MLAYER = 8 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS100 = T.BONUS100 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 100, MBONUS, MGPV, MRATE,PCPID );
				
				ELSEIF ( MLAYER = 9 ) THEN
				UPDATE AR_BS_BONUS_103_CALC T 
				SET T.BONUS110 = T.BONUS110 + MBONUS 
				WHERE
					T.USER_ID = MBONUSID;
				INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
				VALUES
					( MBONUSID, MUSERID, 110, MBONUS, MGPV, MRATE,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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCLEVEL_kevin_01` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_unicode_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `CALCLEVEL_kevin_01`(IN PLAYER int, IN PCPID int)
BEGIN
    DECLARE
        FLAG,
        MLAYER,
        MUSERTYPE INT DEFAULT 0;
    DECLARE
        MUSERID,
        MBONUSID,
        MNEWBONUSID VARCHAR ( 50 );
    DECLARE
        MRATE,
        MRATE30,
        MRATE40,
        MRATE50,
        MRATE60,
        MRATE70,
        MRATE80,
        MRATE90,
        MRATE100,
        MRATE110,
        MBONUS,
        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;
    SELECT
            T.BS_PERCENT / 100 INTO MRATE30
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = 'E121497617216708616';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE40
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE6A4D03C52288E055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE50
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE6EA2070D22EBE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE60
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE742A336F2370E055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE70
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE805032C22492E055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE80
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '67BE82019BE524CAE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE90
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '99BE5FE7857C216AE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE100
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '99BE5FE9008C216AE055736AECE8644D';
    SELECT
            T.BS_PERCENT / 100 INTO MRATE110
    FROM
        AR_EMPLOY_LEVEL T
    WHERE
            T.ID = '99BE5FE7857C216AE000223AECE8644D';-- 打开游标
    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

                    SET MRATE := MRATE30;

                ELSEIF ( MLAYER = 2 ) THEN

                    SET MRATE := MRATE40;

                ELSEIF ( MLAYER = 3 ) THEN

                    SET MRATE := MRATE50;

                ELSEIF ( MLAYER = 4 ) THEN

                    SET MRATE := MRATE60;

                ELSEIF ( MLAYER = 5 ) THEN

                    SET MRATE := MRATE70;

                ELSEIF ( MLAYER = 6 ) THEN

                    SET MRATE := MRATE80;

                ELSEIF ( MLAYER = 7 ) THEN

                    SET MRATE := MRATE90;

                ELSEIF ( MLAYER = 8 ) THEN

                    SET MRATE := MRATE100;

                ELSEIF ( MLAYER = 9 ) THEN

                    SET MRATE := MRATE110;

                END IF;

                SET MBONUS := MGPV * MRATE;
                IF
                    ( MLAYER = 1 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS30 = T.BONUS30 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, NULL, 30, MBONUS, MGPV, MRATE ,PCPID);

                ELSEIF ( MLAYER = 2 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS40 = T.BONUS40 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 40, MBONUS, MGPV, MRATE ,PCPID);

                ELSEIF ( MLAYER = 3 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS50 = T.BONUS50 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 50, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 4 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS60 = T.BONUS60 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 60, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 5 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS70 = T.BONUS70 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 70, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 6 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS80 = T.BONUS80 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 80, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 7 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS90 = T.BONUS90 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 90, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 8 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS100 = T.BONUS100 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 100, MBONUS, MGPV, MRATE,PCPID );

                ELSEIF ( MLAYER = 9 ) THEN
                    UPDATE AR_BS_BONUS_103_CALC T
                    SET T.BONUS110 = T.BONUS110 + MBONUS
                    WHERE
                            T.USER_ID = MBONUSID;
                    INSERT INTO AR_CALC_BONUS_BS_DETAIL_BONUS ( USER_ID, INTRODUCER_ID, LAST_EMP_LV, ORI_BONUS, PV, RATE,PERIOD_NUM )
                    VALUES
                        ( MBONUSID, MUSERID, 110, MBONUS, MGPV, MRATE,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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCMAIN` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCPOINT` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCRECORD` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCREFEREE` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCSTAT` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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_MNT,
        BONUS_MNT_PERCENT,
        BONUS_ABBR,
        BONUS_ABBR_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(BONUS_MNT),
    0,
	SUM(BONUS_ABBR),
    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_MNT_PERCENT = BONUS_MNT / TOTAL_PV,
        BONUS_ABBR_PERCENT = BONUS_ABBR / TOTAL_PV
	WHERE
		PERIOD_NUM = PCPID AND TOTAL_PV > 0;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCSUMMARY` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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.BONUS_MNT = BONUS30 + BONUS40 + BONUS50 + BONUS60 + BONUS70 + BONUS80 + BONUS90 + BONUS100 + BONUS110,
	        T.BONUS_ABBR = BONUS10 + BONUS20
	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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCTOTALGPV` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCTOTALGPV_` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCUP30` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CALCUPGARDE` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CLEANACTIVE` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `CONFOUNDING_DATA` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `CONFOUNDING_DATA`()
BEGIN
	#Routine body goes here...
	UPDATE AR_BALANCE_AUDIT SET CREATE_REMARK = '';
	
	UPDATE AR_CALC_BONUS SET LAST_REAL_NAME = CONCAT(LAST_USER_NAME,'REAL_NAME'),LAST_REC_REAL_NAME = CONCAT(LAST_REC_USER_NAME,'REAL_NAME'),LAST_CON_REAL_NAME = CONCAT(LAST_CON_USER_NAME,'REAL_NAME');

	UPDATE AR_FLOW_BONUS SET REMARK ='';
	
	UPDATE AR_FLOW_WALLET SET REMARK ='';
	
	UPDATE AR_ORDER SET CONSIGNEE = CONCAT(USER_NAME,'CONSIGNEE_NAME'),MOBILE = '18888888888',ADDRESS = 'Virtual Address';
	
	UPDATE AR_ORDER_DEC SET MOBILE = '18888888888',CONSIGNEE = CONCAT(USER_NAME,'CONSIGNEE_NAME'),ADDRESS = 'Virtual Address';
	
	UPDATE AR_ORDER_SHOP SET MOBILE = '18888888888',CONSIGNEE = CONCAT(USER_NAME,'CONSIGNEE_NAME'),ADDRESS = 'Virtual Address';

	UPDATE AR_RECEIVE_ADDRESS SET CONSIGNEE = CONCAT(USER_NAME,'CONSIGNEE_NAME'),MOBILE = '18888888888',ADDRESS = 'Virtual Address';

	UPDATE AR_RECHARGE SET REAL_NAME = USER_ID;

	UPDATE AR_TRANSFER SET LAST_OUT_REAL_NAME = CONCAT(LAST_OUT_USER_NAME,'REAL_NAME'),LAST_IN_REAL_NAME = CONCAT(LAST_IN_USER_NAME,'REAL_NAME'),REMARK = '';

	UPDATE AR_USER SET REAL_NAME = CONCAT(USER_NAME,'REAL_NAME'),MOBILE = '18888888888',ADDRESS = 'Virtual Address';

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `QTRCALC` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `QTRCALC`(IN `PCPID` INT)
LABEL: BEGIN
-- DECLARE QTRSTARTPID, QTRSTARTMONTH, THISYEAR, THISMONTH, MISMONTH INT;

DECLARE TBONUS,BONUSPERU DECIMAL; -- 奖金总额,每份奖金
DECLARE QTRSTARTMONTH, THISYEAR, THISMONTH, MISMONTH INT; -- 这个月

-- DECLARE OCROWN1NUM,OCROWN2NUM,OCROWN3NUM,OCROWN4NUM,OCROWN5NUM,OCROWN6NUM,OCROWN7NUM INT; -- 各星级的人数，原始不加权
-- DECLARE  CROWN1U, CROWN2U, CROWN3U, CROWN4U, CROWN5U, CROWN6U, CROWN7U INT; -- 各星级的人数（加权）

DECLARE TOTALU INT; -- 加权的总份数

DECLARE MAXID, USERTBID INT;
DECLARE Q, R INT;

START TRANSACTION;
-- 日志;
DELETE FROM AR_QTR_CALC_RECORD ;
CALL QTRCALCRECORD(-1,'开始',PCPID,NOW());
COMMIT;

CALL QTRCLEAN(PCPID);
CALL QTRCALCRECORD(1,'清除数据',PCPID,NOW());
COMMIT;

SELECT
 CALC_YEAR, CALC_MONTH, IS_MONTH INTO THISYEAR, THISMONTH, MISMONTH FROM AR_PERIOD WHERE PERIOD_NUM = PCPID;

CALL QTRCALCRECORD(2,'初始化',PCPID,NOW());
COMMIT;

-- 查看是否季度结

IF(THISMONTH!=3 AND THISMONTH!=6 AND THISMONTH!=9 AND THISMONTH!=12) OR MISMONTH!=1 THEN
  CALL QTRCALCRECORD(12,'非季结，结束',PCPID,NOW());
	COMMIT;
  LEAVE LABEL;
END IF;

CALL QTRCALCRECORD(3,'计算总奖金',PCPID,NOW());
COMMIT;

-- 按照条件，查询当前季度所有月的总PV，计算总奖金
SET QTRSTARTMONTH = THISMONTH - 2;

SELECT 
   SUM(PV_PCS) * 0.02 
INTO TBONUS
  FROM AR_PERF_MONTH 
WHERE CALC_MONTH >= CONCAT(THISYEAR, lpad(QTRSTARTMONTH,2,0)) AND CALC_MONTH <= CONCAT(THISYEAR, lpad(THISMONTH,2,0));

CALL QTRCALCRECORD(4,'计算总人数及加权',PCPID,NOW());
COMMIT;


-- 查找，所有星级用户
INSERT INTO AR_QTR_CALC_WEIGHT
	SELECT 
		NULL AS ID,
		QY.USER_ID,
		MAX(CL.SORT) AS MAX_SORT,
		(MAX(CL.SORT) - 1)/2 + 1 AS WEIGHT,
		PERIOD_NUM,
		CALC_MONTH,
		CALC_YEAR
	FROM
		AR_CALC_BONUS_QY AS QY
	 INNER JOIN 
		AR_CROWN_LEVEL AS CL
	 ON QY.LAST_CROWN_LV = CL.ID
	 INNER JOIN 
		AR_USER AS U
	 ON QY.USER_ID = U.ID
	 
	WHERE
		QY.LAST_CROWN_LV != (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 0)
		AND U.DEC_LV = '67ABCE0ECE705575E055736AECE8644D'
		AND QY.CALC_MONTH >= CONCAT(THISYEAR, lpad(QTRSTARTMONTH,2,0)) 
		AND QY.CALC_MONTH <= CONCAT(THISYEAR, lpad(THISMONTH,2,0))

	GROUP BY USER_ID, CALC_MONTH;

COMMIT;

INSERT INTO AR_QTR_CALC_SCORE
	SELECT
		NULL AS ID,
		USER_ID, 
		COUNT(*) AS CNT,
		SUM(WEIGHT) AS SCORE,
		CALC_MONTH,
		CALC_YEAR
	FROM 
		AR_QTR_CALC_WEIGHT
	GROUP BY USER_ID;

COMMIT;
	
SELECT SUM(SCORE) INTO TOTALU FROM AR_QTR_CALC_SCORE;

SET BONUSPERU = TBONUS / TOTALU;


CALL QTRCALCRECORD(5,'计算每一份奖金',PCPID,NOW());
COMMIT;

INSERT INTO AR_QTR_CALC_USER
	SELECT 
		NULL AS ID,
		SC.USER_ID,
		BONUSPERU * SC.SCORE AS AMOUNT,
		CALC_YEAR,
		CALC_MONTH,
		PCPID AS PERIOD_NUM
	FROM
		AR_QTR_CALC_SCORE AS SC;

CALL QTRCALCRECORD(6,'写入user表',PCPID,NOW());

COMMIT;

INSERT INTO AR_CALC_BONUS_QUARTER
	SELECT
		NULL AS ID,
		AU.USER_ID,
		AU.AMOUNT AS ORI_BONUS,
		0 AS MANAGE_TAX,
		AU.AMOUNT,
		0 AS RECONSUME_POINTS,
		U.DEC_LV,
		AU.CALC_MONTH, 
		AU.PERIOD_NUM,
		NOW() AS CREATE_AT
	FROM
		AR_QTR_CALC_USER AS AU
	 LEFT JOIN
	  AR_USER AS U
	 ON AU.USER_ID = U.ID;

CALL QTRCALCRECORD(20,'写入季度奖金表',PCPID,NOW());
COMMIT;	

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `QTRCALCRECORD` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `QTRCALCRECORD`(IN `PID` INT, IN `PEVENT` VARCHAR ( 255 ), IN `PCPID` INT, IN `PNOW` DATETIME)
BEGIN
	INSERT INTO AR_QTR_CALC_RECORD ( ID, EVENT, CREATION_TIME, CALC_PERIOD_ID )
	VALUES
		( PID, PEVENT, NOW(), PCPID );

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `QTRCLEAN` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` PROCEDURE `QTRCLEAN`(IN `PCPID` INT)
BEGIN-- 删除当期奖金
	DELETE 
	FROM
		AR_CALC_BONUS_QUARTER
	WHERE
		PERIOD_NUM >= PCPID;
  TRUNCATE
		AR_QTR_CALC_USER;
	TRUNCATE
		AR_QTR_CALC_WEIGHT;
	TRUNCATE
		AR_QTR_CALC_SCORE;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `UPPERCASE` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`babysbreath`@`%` 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 ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-10-08  2:33:02