CREATE DEFINER=`bi`@`%` PROCEDURE `BONUS_GL`( `PRM_PERIOD_NUM` INT ) BEGIN DECLARE Done INT DEFAULT 0; DECLARE R1_USER_NAME VARCHAR ( 30 ); DECLARE R1_REAL_NAME VARCHAR ( 50 ); DECLARE R1_USER_LEVEL VARCHAR ( 10 ); DECLARE R1_PARENT_LEVEL VARCHAR ( 10 ); DECLARE R1_PARENT_USER_NAME VARCHAR ( 30 ); DECLARE R3_USER_NAME VARCHAR ( 30 ); DECLARE R3_REAL_NAME VARCHAR ( 50 ); DECLARE R3_PARENT_USER_NAME VARCHAR ( 30 ); DECLARE R3_PARENT_REAL_NAME VARCHAR ( 50 ); DECLARE R1_PARENT_REAL_NAME VARCHAR ( 50 ); DECLARE SHIJIDAISHU INT DEFAULT 0; /*DECLARE JICENGDAISHU INT DEFAULT 0;*/ DECLARE JIJIANGDAISHU INT DEFAULT 0; DECLARE R1_ORI_BONUS_QY_BD DECIMAL ( 16, 3 ); DECLARE R2_USER_NAME VARCHAR ( 30 ); DECLARE R2_REAL_NAME VARCHAR ( 50 ); DECLARE R2_ORI_BONUS_QY_BD DECIMAL ( 16, 3 ); DECLARE R2_BDYJ DECIMAL ( 16, 3 ); DECLARE R3_BDYJ DECIMAL ( 16, 3 ); DECLARE R2_PARENT_USER_NAME VARCHAR ( 30 ); DECLARE R2_PARENT_REAL_NAME VARCHAR ( 50 ); DECLARE XIANAJIANGJIN DECIMAL ( 16, 3 ); DECLARE TUIGUANGRENSHU INT; DECLARE DAISHUXIANZHI INT; DECLARE JIANGJINBILI DECIMAL ( 16, 3 ); /* 声明游标 */ DECLARE rs CURSOR FOR SELECT U.USER_NAME , U.REAL_NAME , B.ORI_BONUS_QY_BD , U2.USER_NAME AS PARENT_USER_NAME , U2.REAL_NAME AS PARENT_REAL_NAME FROM ar_calc_bonus B LEFT JOIN ar_user U ON U.ID = B.USER_ID LEFT JOIN ar_user_relation_new R ON R.USER_ID = B.USER_ID LEFT JOIN ar_user U2 ON U2.ID = R.PARENT_UID LEFT JOIN ar_perf_period P ON P.USER_ID = U.ID AND P.PERIOD_NUM = PRM_PERIOD_NUM WHERE B.PERIOD_NUM = PRM_PERIOD_NUM AND B.ORI_BONUS_QY_BD > 0 ; /* 异常处理 */ DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; /* 打开游标 */ OPEN rs; DELETE FROM ek_temp_gl_cs ; /* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */ FETCH NEXT FROM rs INTO R1_USER_NAME , R1_REAL_NAME , R1_ORI_BONUS_QY_BD , R1_PARENT_USER_NAME , R1_PARENT_REAL_NAME ; /* 遍历数据表 */ REPEAT SET JIJIANGDAISHU = 1; -- SET JICENGDAISHU = 1; SET SHIJIDAISHU = 0;-- INSERT INTO ORI_BONUS_GX_CS(USER_NAME,REAL_NAME,PARENT_NAME,ORI_BONUS_GX) VALUES (USER_NAME,REAL_NAME,PARENT_NAME,ORI_BONUS_GX); REPEAT SET R2_USER_NAME = NULL; SELECT A.USER_NAME , A.REAL_NAME , A.LEVEL_NAME , A.ORI_BONUS_QY_BD , A.PARENT_USER_NAME , A.PARENT_REAL_NAME , A.PARENT_LEVEL_NAME , A.BDYJ , A.TUIGUANGRENSHU , G.`实际代数` INTO R2_USER_NAME , R2_REAL_NAME , R1_USER_LEVEL , R2_ORI_BONUS_QY_BD , R2_PARENT_USER_NAME , R2_PARENT_REAL_NAME , R1_PARENT_LEVEL , R2_BDYJ , TUIGUANGRENSHU , DAISHUXIANZHI FROM ( SELECT U.USER_NAME , U.REAL_NAME , D.LEVEL_NAME , IFNULL( B.ORI_BONUS_QY_BD, 0 ) AS ORI_BONUS_QY_BD , U2.USER_NAME AS PARENT_USER_NAME , U2.REAL_NAME AS PARENT_REAL_NAME , D2.LEVEL_NAME AS PARENT_LEVEL_NAME ,( XIAOYEJI ( R1_PARENT_USER_NAME, PRM_PERIOD_NUM )) AS BDYJ , ( SELECT COUNT(*) FROM zr_user_vw R WHERE R.`推荐编号` = U.USER_NAME ) AS TUIGUANGRENSHU FROM ar_user U LEFT JOIN ar_calc_bonus B ON B.USER_ID = U.ID AND B.PERIOD_NUM = PRM_PERIOD_NUM LEFT JOIN ar_declaration_level D ON D.ID = U.DEC_LV LEFT JOIN ar_user_relation_new R ON R.USER_ID = U.ID LEFT JOIN ar_user U2 ON U2.ID = R.PARENT_UID LEFT JOIN ar_declaration_level D2 ON D2.ID = U2.DEC_LV LEFT JOIN ar_perf_period P ON P.USER_ID = U.ID AND P.PERIOD_NUM = PRM_PERIOD_NUM WHERE U.USER_NAME = R1_PARENT_USER_NAME ) A LEFT JOIN ek_temp_glds G ON G.`会员级别` = A.LEVEL_NAME AND G.`推广人数` = IF ( A.TUIGUANGRENSHU > 3, 3, A.TUIGUANGRENSHU ) ; SET Done = 0; SET SHIJIDAISHU = SHIJIDAISHU + 1;-- SET SHANGDAI = SHANGDAI + 1; IF R2_USER_NAME IS NOT NULL THEN SET JIANGJINBILI = 0; SET XIANAJIANGJIN = 0; IF JIJIANGDAISHU <= DAISHUXIANZHI AND JIJIANGDAISHU%2 = 1 AND R1_ORI_BONUS_QY_BD > 0 THEN IF JIJIANGDAISHU = 1 OR JIJIANGDAISHU = 3 OR JIJIANGDAISHU = 5 THEN SET JIANGJINBILI = 0.05; ELSEIF JIJIANGDAISHU = 7 OR JIJIANGDAISHU = 9 OR JIJIANGDAISHU = 11 THEN SET JIANGJINBILI = 0.04; ELSEIF JIJIANGDAISHU = 13 OR JIJIANGDAISHU = 15 OR JIJIANGDAISHU = 17 THEN SET JIANGJINBILI = 0.03; ELSE SET JIANGJINBILI = 0; END IF; SET XIANAJIANGJIN = R1_ORI_BONUS_QY_BD * JIANGJINBILI; END IF; /*IF R2_ORI_BONUS_QY_BD > 0 THEN SET JICENGDAISHU = JICENGDAISHU + 1; END IF;*/ INSERT INTO ek_temp_gl_cs ( `得奖人编号` , `得奖人姓名` , `得奖人级别` , `给奖人编号` , `给奖人姓名` , `实际代数` , `计层代数` , `计奖代数` , `给奖人报团奖` , `得奖人报团奖` , `得奖人小业绩` , `奖金` , `推广人数` , `代数限制` , `奖金比例` ) VALUES ( R2_USER_NAME , R2_REAL_NAME , R1_USER_LEVEL , R1_USER_NAME , R1_REAL_NAME , SHIJIDAISHU , NULL , IF(r2_bdyj <980,NULL,JIJIANGDAISHU) , R1_ORI_BONUS_QY_BD , R2_ORI_BONUS_QY_BD , R2_BDYJ , if(r2_bdyj <980,0,XIANAJIANGJIN) , TUIGUANGRENSHU , DAISHUXIANZHI , JIANGJINBILI ) ; -- SET JIJIANGDAISHU = JICENGDAISHU; END IF; IF R2_BDYJ >= 980 THEN SET JIJIANGDAISHU = JIJIANGDAISHU + 1; END IF; SET R1_PARENT_USER_NAME = R2_PARENT_USER_NAME; UNTIL JIJIANGDAISHU >= 18 OR R2_USER_NAME IS NULL END REPEAT; FETCH NEXT FROM rs INTO R1_USER_NAME , R1_REAL_NAME , R1_ORI_BONUS_QY_BD , R1_PARENT_USER_NAME , R1_PARENT_REAL_NAME ; UNTIL Done END REPEAT; /* 关闭游标 */ CLOSE rs; END