DROP procedure if exists estadisticas_actividades_materias_optativas;

DELIMITER $$
CREATE PROCEDURE estadisticas_actividades_materias_optativas(IN serialsbc INT,IN serialprd INT,IN serialsbp INT)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  -- Termina cambiará a True cuando lleguemos al último registro del Resultset
  -- y v_LISTAESTUDIANTES será usado para almacenar los estudiantes
  DECLARE v_termina BOOL DEFAULT FALSE;
  
  DECLARE v_TmpActivityType varchar(250) default '';
  DECLARE v_TmpNameAtt varchar(250) default '';
  DECLARE v_TmpDescriptionAtt varchar(250) default '';
  DECLARE v_TmpTotallyGraded varchar(250) default '';
  DECLARE v_TmpPartiallyGraded varchar(250) default '';
  DECLARE v_TmpNoGraded varchar(250) default '';
 
  -- Sql que obtiene los estudiantes+
	   DECLARE CR_Actividades CURSOR
					   FOR      (SELECT DISTINCT
					`att`.`serial_att`,
					`name_att`,
					`description_att`,
					COUNT(distinct tgraded.serial_opa) totally_graded,
					COUNT(distinct pgraded.serial_opa) partially_graded,
					COUNT(distinct ngraded.serial_opa) no_graded
				FROM
					`optional_subject` AS sbc
						JOIN
					`opt_subject_period_activity_type` AS `osp` ON `osp`.`serial_osb` = `sbc`.`serial_osb`
						JOIN
					`activity_type` AS `att` ON `osp`.`serial_att` = `att`.`serial_att`
						LEFT JOIN
					(SELECT 
						scg.serial_osb,
							sbp.serial_sbp,
							aac.serial_att,
							aac.serial_opa,
							COUNT(DISTINCT scg.serial_std) students_graded,
							(SELECT 
									COUNT(DISTINCT sos2.serial_std)
								FROM
									student_optional_subject sos2
								JOIN student_by_course sb ON sos2.serial_std = sb.serial_std
								JOIN optional_subject_level sbcin ON sbcin.serial_osb = sos2.serial_osb
									AND status_osl = 'ACTIVE'
								JOIN student std ON std.serial_std = sb.serial_std
									AND std.status_std = 'ACTIVE'
								JOIN opt_student_criteria_grade scg1 ON scg1.serial_osb = sos2.serial_osb
									AND scg1.serial_std = sos2.serial_std
                  AND scg1.status_osg = 'ACTIVE'
								JOIN opt_activity act1 ON act1.serial_opa = scg1.serial_opa
								WHERE
									sb.status_stc = 'ACTIVE'
										AND sos2.status_sos = 'ACTIVE'
										AND sos2.serial_prd = sbp.serial_prd
										AND sbcin.serial_osb = scg.serial_osb
                    AND act1.serial_opa = aac.serial_opa) students_course
					FROM
						opt_activity_applied_criterias aac
					JOIN opt_student_criteria_grade scg ON scg.serial_osb = aac.serial_osb
						AND aac.serial_att = scg.serial_att
						AND aac.serial_opa = scg.serial_opa
            AND scg.status_osg = 'ACTIVE'
					JOIN opt_activity act ON act.serial_opa = scg.serial_opa
					JOIN subperiod sbp ON sbp.serial_prd = scg.serial_prd
						AND DATE(act.due_date_opa) BETWEEN sbp.start_date_sbp AND sbp.end_date_sbp
					JOIN student_optional_subject sos ON sos.serial_osb = scg.serial_osb
						AND sos.status_sos = 'ACTIVE'
					JOIN student_by_course sc ON sc.serial_std = scg.serial_std
						AND sc.status_stc = 'ACTIVE'
					WHERE
						`status_oaa` = 'ACTIVE'
							AND aac.serial_osb = serialsbc
							AND ((scg.is_complete_osg = 'YES'
							AND scg.activity_grade_part_osg IS NOT NULL)
							OR scg.status_osg = 'NOT_APPLY')
							AND sbp.serial_sbp = serialsbp
					GROUP BY aac.serial_osb , aac.serial_opa
					HAVING students_graded = students_course) tgraded ON tgraded.serial_att = osp.serial_att
						AND tgraded.serial_osb = sbc.serial_osb
						LEFT JOIN
					(SELECT
						scg.serial_osb,
							aac.serial_att,
							sbp.serial_sbp,
							aac.serial_opa,
							COUNT(DISTINCT scg.serial_std) students_graded,
							(SELECT
									COUNT(DISTINCT sos2.serial_std)
								FROM
									student_optional_subject sos2
								JOIN student_by_course sb ON sos2.serial_std = sb.serial_std
								JOIN optional_subject_level sbcin ON sbcin.serial_osb = sos2.serial_osb
									AND status_osl = 'ACTIVE'
								JOIN student std ON std.serial_std = sb.serial_std
									AND std.status_std = 'ACTIVE'
								JOIN opt_student_criteria_grade scg1 ON scg1.serial_osb = sos2.serial_osb
									AND scg1.serial_std = sos2.serial_std
                  AND scg1.status_osg = 'ACTIVE'
								JOIN opt_activity act1 ON act1.serial_opa = scg1.serial_opa
								WHERE
									sb.status_stc = 'ACTIVE'
										AND sos2.status_sos = 'ACTIVE'
										AND sos2.serial_prd = sbp.serial_prd
										AND sbcin.serial_osb = scg.serial_osb
                    AND act1.serial_opa = aac.serial_opa) students_course
					FROM
						opt_activity_applied_criterias aac
					JOIN opt_student_criteria_grade scg ON scg.serial_osb = aac.serial_osb
						AND aac.serial_att = scg.serial_att
						AND aac.serial_opa = scg.serial_opa
            AND scg.status_osg = 'ACTIVE'
					JOIN opt_activity act ON act.serial_opa = scg.serial_opa
					JOIN subperiod sbp ON sbp.serial_prd = scg.serial_prd
						AND DATE(act.due_date_opa) BETWEEN sbp.start_date_sbp AND sbp.end_date_sbp
					JOIN student_optional_subject sos ON sos.serial_osb = scg.serial_osb
						AND sos.status_sos = 'ACTIVE'
					JOIN student_by_course sc ON sc.serial_std = scg.serial_std
						AND sc.status_stc = 'ACTIVE'
					WHERE
						`status_oaa` = 'ACTIVE'
							AND aac.serial_osb = serialsbc
							AND ((scg.is_complete_osg = 'YES'
							AND scg.activity_grade_part_osg IS NOT NULL)
							OR scg.status_osg = 'NOT_APPLY')
							AND sbp.serial_sbp = serialsbp
					GROUP BY aac.serial_opa
					HAVING students_graded != students_course
						AND students_graded > 0) pgraded ON pgraded.serial_att = osp.serial_att
						AND pgraded.serial_osb = sbc.serial_osb
						LEFT JOIN
					(SELECT
						scg.serial_osb,
							aac.serial_att,
							sbp.serial_sbp,
							aac.serial_opa,
							COUNT(DISTINCT scg.serial_std) students_graded,
							(SELECT
									COUNT(DISTINCT sos2.serial_std)
								FROM
									student_optional_subject sos2
								JOIN student_by_course sb ON sos2.serial_std = sb.serial_std
								JOIN optional_subject_level sbcin ON sbcin.serial_osb = sos2.serial_osb
									AND status_osl = 'ACTIVE'
								JOIN student std ON std.serial_std = sb.serial_std
									AND std.status_std = 'ACTIVE'
								JOIN opt_student_criteria_grade scg1 ON scg1.serial_osb = sos2.serial_osb
									AND scg1.serial_std = sos2.serial_std
                  AND scg1.status_osg = 'ACTIVE'
								JOIN opt_activity act1 ON act1.serial_opa = scg1.serial_opa
								WHERE
									sb.status_stc = 'ACTIVE'
										AND sos2.status_sos = 'ACTIVE'
										AND sos2.serial_prd = sbp.serial_prd
										AND sbcin.serial_osb = scg.serial_osb
                    AND act1.serial_opa = aac.serial_opa) students_course
					FROM
						opt_activity_applied_criterias aac
					JOIN opt_student_criteria_grade scg ON scg.serial_osb = aac.serial_osb
						AND aac.serial_att = scg.serial_att
						AND aac.serial_opa = scg.serial_opa
            AND scg.status_osg = 'ACTIVE'
					JOIN opt_activity act ON act.serial_opa = scg.serial_opa
					JOIN subperiod sbp ON sbp.serial_prd = scg.serial_prd
						AND DATE(act.due_date_opa) BETWEEN sbp.start_date_sbp AND sbp.end_date_sbp
					JOIN student_optional_subject sos ON sos.serial_osb = scg.serial_osb
						AND sos.status_sos = 'ACTIVE'
					JOIN student_by_course sc ON sc.serial_std = scg.serial_std
						AND sc.status_stc = 'ACTIVE'
					WHERE
						`status_oaa` = 'ACTIVE'
							AND aac.serial_osb = serialsbc
							AND ((scg.is_complete_osg = 'NO'
							AND scg.activity_grade_part_osg IS NULL)
							OR scg.status_osg = 'NOT_APPLY')
							AND sbp.serial_sbp = serialsbp
					GROUP BY aac.serial_opa
					HAVING students_graded = students_course) ngraded ON ngraded.serial_att = osp.serial_att
						AND ngraded.serial_osb = sbc.serial_osb
				WHERE
					`att`.`serial_att` <> 1
						AND `sbc`.`serial_osb` = serialsbc
						AND `osp`.`serial_prd` = serialprd
						AND `status_osa` = 'ACTIVE'
						AND `status_att` = 'ACTIVE'
						AND `status_osb` = 'ACTIVE'
				GROUP BY att.serial_att
				ORDER BY name_att);
                
               -- El handler establece a true la variable termina, que se usa a modo de bandera
               -- para saber cuando termine de recorrer el cursor            
               DECLARE CONTINUE HANDLER
                   FOR SQLSTATE '02000'
                   SET v_termina = TRUE;

              OPEN CR_Actividades;
               Recorre_Cursor: LOOP
                    FETCH CR_Actividades INTO v_TmpActivityType,v_TmpNameAtt, v_TmpDescriptionAtt,v_TmpTotallyGraded ,v_TmpPartiallyGraded,v_TmpNoGraded;
                    IF v_termina THEN
                        LEAVE Recorre_Cursor;
                    END IF;  
						-- Se busca si existe ya un registro en la tabla 
						SET @serialSas = (select serial_sas from statistics_activities_subjects where serial_sbc IS NULL and serial_osb = serialsbc and serial_att = v_TmpActivityType and serial_sbp = serialsbp);
						IF @serialSas IS NOT NULL THEN
                        -- ACTUALIZA EL REGISTRO
							UPDATE `statistics_activities_subjects` SET `num_act_no_graded`=v_TmpNoGraded,num_act_partially_graded=v_TmpPartiallyGraded,num_act_totally_graded=v_TmpTotallyGraded WHERE `serial_sas`=@serialSas;
						ELSE
                        -- INSERTA EL REGISTRO
							INSERT INTO `statistics_activities_subjects` (`serial_sbc`, `serial_osb`, `serial_sbp`, `serial_att`, `num_act_no_graded`, `num_act_partially_graded`, `num_act_totally_graded`) VALUES (NULL, serialsbc, serialsbp, v_TmpActivityType, v_TmpNoGraded, v_TmpPartiallyGraded, v_TmpTotallyGraded);
						END IF;
				END LOOP;
			  CLOSE CR_Actividades;

END $$
DELIMITER ;