-- 存储过程 (多个)游标的使用  临时表的使用(让执行时间从一个小时降低到5分钟)
DELIMITER $$

DROP PROCEDURE IF EXISTS `eval_calc_badge_achivement`$$

CREATE DEFINER=`dbmaster`@`%` PROCEDURE `eval_calc_badge_achivement`()

proc_label:BEGIN

    DECLARE v_currentPeriodId VARCHAR(100);  -- 当前学期ID

    DECLARE v_studentId VARCHAR(100);

    DECLARE v_studentName TEXT CHARACTER SET utf8 DEFAULT '';

    DECLARE v_studyCode VARCHAR(100);

    DECLARE v_groupName TEXT CHARACTER SET utf8 DEFAULT '';

    DECLARE v_gradeName TEXT CHARACTER SET utf8 DEFAULT '';

    DECLARE v_periodCountBagdeSum INT;

    DECLARE v_periodPCBagdeSum INT;

    DECLARE v_periodGPA DOUBLE;

    DECLARE v_badgeAchivement TEXT CHARACTER SET utf8 DEFAULT '';

    DECLARE v_requireCountBadgeSum INT;

    DECLARE v_requirePCBagdeSum INT;

    DECLARE v_requireGPA DOUBLE;

    DECLARE done INT DEFAULT 0;

    DECLARE cur_stu CURSOR FOR SELECT bas.id,bas.name,bas.studyCode,g.Name,grade.Name FROM bas_Student bas LEFT JOIN bas_group g ON bas.groupId=g.id LEFT JOIN bas_grade grade ON bas.gradeId=grade.id WHERE bas.IsLeaved=0;

    DECLARE cur_rule CURSOR FOR SELECT BadgeRequiredNumber,PCBadgeRequiredNumber,GPARequired,AchievementName FROM eval_badgestatsrule WHERE periodid=v_currentPeriodId ORDER BY BadgeRequiredNumber DESC,PCBadgeRequiredNumber DESC,GPARequired DESC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SELECT id INTO v_currentPeriodId FROM bas_schoolperiod WHERE IsCurrentPeriod=1;

    DROP TABLE IF EXISTS eval_processevaldetail_tmp;

    CREATE TEMPORARY TABLE IF NOT EXISTS  eval_processevaldetail_tmp (

    StudentId VARCHAR(50) NOT NULL,

    totalBadgeCount INT

    );

    CREATE INDEX eval_processevaldetail_tmp_stuid ON eval_processevaldetail_tmp (StudentId);

    TRUNCATE TABLE eval_processevaldetail_tmp;

    INSERT INTO eval_processevaldetail_tmp SELECT studentId,SUM(CalculateBadgeCount+ExtraBadgeCount) AS totalBadgeCount FROM eval_processevaldetail WHERE  CourseGroupid IN (SELECT id FROM `csh_coursegroup` WHERE schoolperiodid=v_currentPeriodId) GROUP BY studentId;

    OPEN cur_stu;

         REPEAT

            FETCH cur_stu INTO v_studentId, v_studentName,v_studyCode,v_groupName,v_gradeName;

            IF NOT done THEN

                SELECT SUM(badgeCount) INTO v_periodPCBagdeSum FROM `cp_studentscore` WHERE studentid=v_studentId AND  cpperiodCourseid IN (SELECT id FROM `cp_periodcourse` WHERE schoolperiodid=v_currentPeriodId);

                SELECT (v_periodPCBagdeSum+(SELECT totalBadgeCount FROM eval_processevaldetail_tmp WHERE studentid=v_studentId )) INTO v_periodCountBagdeSum;

                SELECT ROUND(SUM(ScoreGPA)/ SUM(Credit),2) INTO v_periodGPA FROM Eval_SectionEval WHERE StudentId=v_studentId AND SchoolPeriodId=v_currentPeriodId;

                IF (v_periodCountBagdeSum IS NULL)THEN SELECT 0 INTO v_periodCountBagdeSum;END IF;

                IF (v_periodPCBagdeSum IS NULL)THEN SELECT 0 INTO v_periodPCBagdeSum;END IF;

                IF (v_periodGPA IS NULL)THEN SELECT 0 INTO v_periodGPA;END IF;

                OPEN cur_rule;

                REPEAT

                FETCH cur_rule INTO v_requireCountBadgeSum, v_requirePCBagdeSum,v_requireGPA,v_badgeAchivement; 

                IF (v_periodCountBagdeSum>=v_requireCountBadgeSum && v_periodPCBagdeSum>=v_requirePCBagdeSum && v_periodGPA>=v_requireGPA)THEN

                INSERT INTO `eval_studentachievement_history` (`Id`,`PeriodId`,`StudentId`,`Grade`,`StudentGroup`,`StudyCode`,`StudentName`,`BadgeNumber`,`PCBadgeNumber`,`ScoreGPA`,`AchievementName`,`CreatedTime`,`UpdateTime`) VALUES(UUID(),v_currentPeriodId,v_studentId,v_gradeName,v_groupName,v_studyCode,v_studentName,v_periodCountBagdeSum,v_periodPCBagdeSum,v_periodGPA,v_badgeAchivement,NOW(),NOW());

                    INSERT INTO dolog (id) VALUES (v_studentId);

                IF (SELECT COUNT(*) FROM eval_studentachievement WHERE StudentId=v_studentId)>0 THEN

                UPDATE eval_studentachievement SET PeriodId=v_currentPeriodId,Grade=v_gradeName,StudentGroup=v_groupName,BadgeNumber=v_periodCountBagdeSum,PCBadgeNumber=v_periodPCBagdeSum,ScoreGPA=v_periodGPA,AchievementName=v_badgeAchivement,UpdateTime=NOW() WHERE StudentId=v_studentId;

                ELSE

                INSERT INTO eval_studentachievement (`Id`,`PeriodId`,`StudentId`,`Grade`,`StudentGroup`,`StudyCode`,`StudentName`,`BadgeNumber`,`PCBadgeNumber`,`ScoreGPA`,`AchievementName`,`CreatedTime`,`UpdateTime`) VALUES(UUID(),v_currentPeriodId,v_studentId,v_gradeName,v_groupName,v_studyCode,v_studentName,v_periodCountBagdeSum,v_periodPCBagdeSum,v_periodGPA,v_badgeAchivement,NOW(),NOW());

                END IF;        

                SET done=1;

                END IF;

                UNTIL done END REPEAT;

                CLOSE cur_rule;

            SET done=0;

            END IF;

          UNTIL done END REPEAT;

         CLOSE cur_stu;

          END proc_label$$

DELIMITER ;

CALL eval_calc_badge_achivement();

SELECT @@event_scheduler; -- 查看事件调度器状态

SET GLOBAL event_scheduler = 0; -- 关闭

SET GLOBAL event_scheduler = 1; -- 打开

CREATE EVENT IF NOT EXISTS event_eval_stu_Achievement

ON SCHEDULE

EVERY 1 DAY STARTS '2016-07-01 0:30:00'

DO CALL eval_calc_badge_achivement(); -- 创建

DROP EVENT IF EXISTS event_eval_stu_Achievement; -- 删除