ngds/sql/upgrade/qtr.sql

CREATE DEFINER=`black_tea_0309`@`%` 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;


-- 查找，所有星级用户

SELECT COUNT(*) INTO OCROWN1NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 1);
SELECT COUNT(*) INTO OCROWN2NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 2);
SELECT COUNT(*) INTO OCROWN3NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 3);
SELECT COUNT(*) INTO OCROWN4NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 4);
SELECT COUNT(*) INTO OCROWN5NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 5);
SELECT COUNT(*) INTO OCROWN6NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 6);
SELECT COUNT(*) INTO OCROWN7NUM FROM AR_CALC_BONUS_QY WHERE PERIOD_NUM = PCPID AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 7);

SET CROWN1U = OCROWN1NUM;
SET CROWN2U = OCROWN2NUM * 1.5;
SET CROWN3U = OCROWN3NUM * 2;
SET CROWN4U = OCROWN4NUM * 2.5;
SET CROWN5U = OCROWN5NUM * 3;
SET CROWN6U = OCROWN6NUM * 3.5;
SET CROWN7U = OCROWN7NUM * 4;

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

SET TOTALU = CROWN1U + CROWN2U + CROWN3U + CROWN4U + CROWN5U + CROWN6U + CROWN7U;

SET BONUSPERU = TBONUS / TOTALU; -- 算出每一份奖金的金额

IF(CROWN1U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 1);

END IF;
CALL QTRCALCRECORD(11,'计算1星奖金',PCPID,NOW());
COMMIT;

IF(CROWN2U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU * 1.5 AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 2);
	
END IF;
CALL QTRCALCRECORD(12,'计算2星奖金',PCPID,NOW());
COMMIT;

IF(CROWN3U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU * 2 AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 3);
	
END IF;
CALL QTRCALCRECORD(13,'计算3星奖金',PCPID,NOW());
COMMIT;

IF(CROWN4U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU * 2.5 AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 4);
	
END IF;
CALL QTRCALCRECORD(14,'计算4星奖金',PCPID,NOW());
COMMIT;

IF(CROWN5U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU * 3 AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 5);
	
END IF;
CALL QTRCALCRECORD(15,'计算5星奖金',PCPID,NOW());
COMMIT;

IF(CROWN6U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU * 3.5 AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 6);
	
END IF;
CALL QTRCALCRECORD(16,'计算6星奖金',PCPID,NOW());
COMMIT;

IF(CROWN7U > 0) THEN
	INSERT INTO AR_QTR_CALC_USER
		SELECT 
			NULL AS ID, 
			USER_ID, 
			BONUSPERU * 4 AS AMOUNT, 
			CALC_YEAR, 
			CALC_MONTH, 
			PERIOD_NUM
		FROM 
			AR_CALC_BONUS_QY 
		WHERE 
			PERIOD_NUM = 111 AND LAST_CROWN_LV = (SELECT ID FROM AR_CROWN_LEVEL WHERE SORT = 7);
	
END IF;
CALL QTRCALCRECORD(17,'计算7星奖金',PCPID,NOW());
COMMIT;

INSERT INTO AR_CALC_BONUS_QUARTER
	SELECT
		NULL AS ID,
		USER_ID,
		AMOUNT AS ORI_BONUS,
		0 AS MANAGE_TAX,
		AMOUNT,
		0 AS RECONSUME_POINTS,
		CALC_YEAR, 
		CALC_MONTH, 
		PERIOD_NUM,
		0 AS CREATE_AT
	FROM
		AR_QTR_CALC_USER;

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

END