-- 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