NCDS_Procedure/CALCMAIN.sql

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

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

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


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

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

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

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

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

-- 
UPDATE AR_BS_BONUS_103_CALC SET ORI_RATE = RATE;

OPEN CUR;

WHILE DONE DO

FETCH CUR INTO USERID;

IF DONE THEN

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

END IF;

END WHILE;

CLOSE CUR;

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

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

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

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

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

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

SET DONE = TRUE;

OPEN CUR2;

WHILE DONE DO

FETCH CUR2 INTO USERID;

IF DONE THEN

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

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

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

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

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

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

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

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

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

END IF;

END WHILE;

CLOSE CUR2;

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

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

END
;;