BEGIN 
	DECLARE estatus varchar(60);
	DECLARE esfinal INTEGER;
                   DECLARE esextra INTEGER;
	DECLARE calific float;
	DECLARE ex_parciales float;
	DECLARE ex_final float;
	DECLARE ex_extra float;
	DECLARE materia_id INTEGER;


                     #DEVUELVE EL ID DE LA MATERIA
                 SELECT s.course INTO materia_id FROM mdl_scorm_scoes_track sst inner join mdl_scorm s on s.id=sst.scormid WHERE sst.element="cmi.core.lesson_status" AND  sst.id=old.id limit 1; 

	#DEVUELVE 0 O 1 EN LA VARIABLE SI ES EXAMEN FINAL O NO
	SELECT count(cm.id)  INTO esfinal
	FROM  mdl_scorm s
	INNER JOIN mdl_course_modules cm on s.id=cm.instance
	INNER JOIN mdl_course_sections cs on cs.id=cm.section WHERE cm.module=18 and cm.course=s.course and s.course=materia_id  and s.id=old.scormid and cs.section=4; 
	

	#DEVUELVE 0 O 1 EN LA VARIABLE SI ES EXAMEN EXRAORDINARIO O NO
	SELECT count(cm.id)  INTO esextra
	FROM  mdl_scorm s
	INNER JOIN mdl_course_modules cm on s.id=cm.instance
	INNER JOIN mdl_course_sections cs on cs.id=cm.section WHERE cm.module=18 and cm.course=s.course and s.course=materia_id  and s.id=old.scormid and cs.section=5; 


	IF esfinal=1 THEN


		#DEVUELVE EL RESULTADO DEL STATUS DEL SCORM
		SELECT sst.value INTO estatus FROM mdl_scorm_scoes_track sst WHERE sst.element="cmi.core.lesson_status" AND  sst.id=old.id ; 

		IF estatus='passed' OR estatus='failed' THEN

			#DEVUELVE total de calificaciones examenes parciales
			SELECT round((SUM(sst.value)/count(sst.id))*0.4,2) INTO ex_parciales FROM mdl_scorm_scoes_track sst inner join mdl_course_modules cm on cm.instance=sst.scormid inner join mdl_scorm s on sst.scormid=s.id inner join mdl_course_sections cs on cs.id=cm.section  
WHERE sst.element="cmi.core.score.raw" AND cm.course=s.course and  s.course=materia_id  and cm.module=18 AND sst.userid=old.userid and cs.section=3;


			#DEVUELVE total de examene final
			SELECT round(SUM(sst.value)*0.6,2) INTO ex_final FROM mdl_scorm_scoes_track sst inner join mdl_course_modules cm on cm.instance=sst.scormid inner join mdl_scorm s on sst.scormid=s.id inner join mdl_course_sections cs on cs.id=cm.section  
WHERE sst.element="cmi.core.score.raw" AND cm.course=s.course and  s.course=materia_id and cm.module=18 AND sst.userid=old.userid and cs.section=4;

                                                       set calific=ex_parciales+ex_final;


			

			IF (SELECT count(ac.id) from ag_calificaciones ac where ac.id_alumno=old.userid and ac.id_materia=materia_id ) = 0 THEN
				INSERT INTO ag_calificaciones(id_alumno,id_materia,calificacion,id_tipo_examen) VALUES (old.userid,materia_id,calific,1) ;
				#ESTO YA LO HACE EL TRIGGER DE AG_CALIFICACIONES | UPDATE mdl_user_enrolments ue SET ue.status=1 WHERE ue.userid=old.userid AND (SELECT en.course FROM mdl_enrol en WHERE en.id=ue.enrolid limit 1)=materia_id limit 1; 
			ELSE 
				UPDATE ag_calificaciones c SET c.calificacion=calific WHERE c.id_alumno=old.userid and materia_id=c.id_materia limit 1;
				#ESTO YA LO HACE EL TRIGGER DE AG_CALIFICACIONES | UPDATE mdl_user_enrolments ue SET ue.status=1 WHERE ue.userid=old.userid AND (SELECT en.course FROM mdl_enrol en WHERE en.id=ue.enrolid limit 1)=materia_id limit 1 ; 

			END if;
		END IF;

	END IF;

	IF esextra=1 THEN

		#DEVUELVE EL RESULTADO DEL STATUS DEL SCORM
		SELECT sst.value INTO estatus FROM mdl_scorm_scoes_track sst WHERE sst.element="cmi.core.lesson_status" AND  sst.id=old.id ; 

		IF estatus='passed' OR estatus='failed' THEN

			#DEVUELVE Calificacion de examen Extra
			SELECT round(SUM(sst.value)*1,2) INTO ex_extra FROM mdl_scorm_scoes_track sst inner join mdl_course_modules cm on cm.instance=sst.scormid inner join mdl_scorm s on sst.scormid=s.id inner join mdl_course_sections cs on cs.id=cm.section  
WHERE sst.element="cmi.core.score.raw" AND cm.course=s.course and  s.course=materia_id and cm.module=18 AND sst.userid=old.userid and cs.section=5;

                                                                         set calific=ex_extra;

			IF (SELECT count(ac.id) from ag_calificaciones ac where ac.id_alumno=old.userid and ac.id_materia=materia_id ) = 0 THEN
				INSERT INTO ag_calificaciones(id_alumno,id_materia,calificacion,id_tipo_examen) VALUES (old.userid,materia_id,calific,1) ;
				#ESTO YA LO HACE EL TRIGGER DE AG_CALIFICACIONES | UPDATE mdl_user_enrolments ue SET ue.status=1 WHERE ue.userid=old.userid AND (SELECT en.course FROM mdl_enrol en WHERE en.id=ue.enrolid limit 1)=materia_id limit 1; 
			ELSE 
				UPDATE ag_calificaciones c SET c.calificacion=calific,c.id_tipo_examen =2,c.fecha_ordinario = now() WHERE c.id_alumno=old.userid and materia_id=c.id_materia limit 1;
				#ESTO YA LO HACE EL TRIGGER DE AG_CALIFICACIONES | UPDATE mdl_user_enrolments ue SET ue.status=1 WHERE ue.userid=old.userid AND (SELECT en.course FROM mdl_enrol en WHERE en.id=ue.enrolid limit 1)=materia_id limit 1 ; 

			END if;

		END IF;
	END IF;

END