DELIMITER $$
CREATE PROCEDURE updatePeriodicGrades (
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE varSystemid CHAR(17) DEFAULT '';
	DECLARE varSubjectseqno SMALLINT DEFAULT 0;
	DECLARE varGradeperiod CHAR(1) default '';
	DECLARE varAverage DECIMAL(5,2) DEFAULT 0;
	DECLARE varEquivalent DECIMAL(5,2) DEFAULT 0;
	DECLARE varComment VARCHAR(10) DEFAULT '';

	-- declare cursor for employee email
	DECLARE curPeriodicGrade 
		CURSOR FOR 
			SELECT systemid, subjectseqno, gradeperiod, average FROM regsubjectsgrade;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

	OPEN curPeriodicGrade;

	getPeriodicGrade: LOOP
		FETCH curPeriodicGrade INTO varSystemid, varSubjectseqno, varGradeperiod, varAverage;
		IF finished = 1 THEN 
			LEAVE getPeriodicGrade;
		END IF;

		-- determine equivalent and comment
		IF varAverage > 100 THEN
			SET varEquivalent = 0.00; SET varComment = 'INVALID';
		ELSE 
			IF varAverage >= 98.5 THEN
				SET varEquivalent = 1.00; SET varComment = 'PASSED';
			ELSE
				IF varAverage >= 95.5 THEN 
					SET varEquivalent = 1.25; SET varComment = 'PASSED';
				ELSE
					IF varAverage >= 92.5 THEN 
						SET varEquivalent = 1.50; SET varComment = 'PASSED';
					ELSE 
						IF varAverage >= 89.5 THEN 
							SET varEquivalent = 1.75; SET varComment = 'PASSED';
						ELSE
							IF varAverage >= 86.5 THEN 
								SET varEquivalent = 2.00; SET varComment = 'PASSED';
							ELSE
								IF varAverage >= 83.5 THEN
									SET varEquivalent = 2.25; SET varComment = 'PASSED';
								ELSE
									IF varAverage >= 80.5 THEN 
										SET varEquivalent = 2.50; SET varComment = 'PASSED';
									ELSE
										IF varAverage >= 77.5 THEN 
											SET varEquivalent = 2.75; SET varComment = 'PASSED';
										ELSE 
											IF varAverage >= 74.5 THEN 
												SET varEquivalent = 3.00; SET varComment = 'PASSED';
											ELSE
												IF varAverage < 74.5 AND varAverage > 0 THEN 
													SET varEquivalent = 5.00; SET varComment = 'FAILED';
												ELSE 
													SET varEquivalent = 0.00; SET varComment = 'INVALID';
												END IF;
											END IF;
										END IF;
									END IF;
								END IF;
							END IF;
						END IF;
					END IF;
				END IF;
			END IF;
		END IF;
		
		UPDATE regsubjectsgrade SET equivalent = varEquivalent, comment = varComment 
		WHERE systemid = varSystemid AND subjectseqno = varSubjectseqno AND gradeperiod = varGradeperiod;
		
	END LOOP getPeriodicGrade;
	CLOSE curPeriodicGrade;

END$$
DELIMITER ;