SQLsummary/存储过程/共享奖下拿1.1.sql

CREATE DEFINER=`bi`@`%` PROCEDURE `BONUS_GX_DOWN`( `PRM_PERIOD_NUM` INT )
BEGIN#Routine body goes here...
	DECLARE
		Done INT DEFAULT 0;
	DECLARE
		R1_USER_NAME VARCHAR ( 30 );
	DECLARE
		R1_REAL_NAME VARCHAR ( 50 );
	DECLARE
		R1_PARENT_USER_NAME VARCHAR ( 30 );
	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
		R3_USER_NAME VARCHAR ( 30 );
	DECLARE
		R3_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
		R3_PARENT_USER_NAME VARCHAR ( 30 );
	DECLARE
		R3_PARENT_REAL_NAME VARCHAR ( 50 );
	DECLARE
		XIANAJIANGJIN 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_network_new N ON N.USER_ID = B.USER_ID
		LEFT JOIN ar_user U2 ON U2.ID = N.PARENT_UID 
	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_gx_xiana;
/* 逐个取出当前记录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
				U.USER_NAME,
				U.REAL_NAME,
				IFNULL( B.ORI_BONUS_QY_BD, 0 ),
				( XIAOYEJI(U.USER_NAME, PRM_PERIOD_NUM)) AS BDYJ,
				U2.USER_NAME AS PARENT_USER_NAME,
				U2.REAL_NAME AS PARENT_REAL_NAME INTO R2_USER_NAME,
				R2_REAL_NAME,
				R2_ORI_BONUS_QY_BD,
				R2_BDYJ,
				R2_PARENT_USER_NAME,
				R2_PARENT_REAL_NAME 
			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_user_network_new N ON N.USER_ID = U.ID
				LEFT JOIN ar_user U2 ON U2.ID = N.PARENT_UID
				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;
			
			SET Done = 0;
			
			SET SHIJIDAISHU = SHIJIDAISHU + 1;-- SET SHANGDAI = SHANGDAI + 1;
			IF
				R2_USER_NAME IS NOT NULL THEN
				IF
					JIJIANGDAISHU % 2 = 0 THEN
						
						SET XIANAJIANGJIN = R1_ORI_BONUS_QY_BD * 0.05;
					ELSE 
						SET XIANAJIANGJIN = 0;
					
				END IF;
				/*IF
					R2_ORI_BONUS_QY_BD > 0 THEN
						
						SET JIJIANGDAISHU = JIJIANGDAISHU + 1;
					
				END IF;*/
				INSERT INTO ek_temp_gx_xiana ( `得奖人编号`, `得奖人姓名`, `给奖人编号`, `给奖人姓名`, `实际代数`, `计层代数`, `计奖代数`, `给奖人报团奖`, `得奖人报团奖`, `得奖人小业绩`, `奖金` )
				VALUES
					(
						R2_USER_NAME,
						R2_REAL_NAME,
						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 ) 
					);
				/*IF R1_ORI_BONUS_QY_BD IS NULL THEN
				SET JIJIANGDAISHU = JIJIANGDAISHU + 1;
				END IF;*/
			END IF;
			IF
				R2_BDYJ >= 980  THEN
				SET JIJIANGDAISHU = JIJIANGDAISHU + 1;
				
			END IF;
			
			SET R1_PARENT_USER_NAME = R2_PARENT_USER_NAME;
			UNTIL JIJIANGDAISHU >= 11 
			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