DROP procedure if exists estadisticas_actividades_materias_obligatorias;

DELIMITER $$
CREATE PROCEDURE estadisticas_actividades_materias_obligatorias(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_act) totally_graded,
    COUNT(DISTINCT pgraded.serial_act) partially_graded,
    COUNT(DISTINCT ngraded.serial_act) no_graded
FROM
    `subject_course` AS sbc
        JOIN
    `subject_period_activity_type` AS `spa` ON `spa`.`serial_sbc` = `sbc`.`serial_sbc`
        JOIN
    `activity_type` AS `att` ON `spa`.`serial_att` = `att`.`serial_att`
        LEFT JOIN
    (SELECT 
        scg.serial_sbc,
            sbp.serial_sbp,
            aac.serial_att,
            aac.serial_act,
            COUNT(DISTINCT scg.serial_std) students_graded,
            (SELECT 
                    COUNT(DISTINCT sb.serial_std)
                FROM
                student_by_course sb
                JOIN subject_course sbcin ON sb.serial_crs = sbcin.serial_crs
				        JOIN student std ON std.serial_std = sb.serial_std
					        AND std.status_std = 'ACTIVE'
                JOIN student_criteria_grade scg1 ON scg1.serial_crs = sb.serial_crs
					        AND scg1.serial_std = sb.serial_std
					        AND scg1.status_scg = 'ACTIVE'
                JOIN activity act1 ON act1.serial_act = scg1.serial_act
				        WHERE sb.status_stc = 'ACTIVE' AND sbcin.serial_sbc = scg.serial_sbc
					        AND act1.serial_act = aac.serial_act) students_course
    FROM
        activity_applied_criterias aac
    JOIN student_criteria_grade scg ON scg.serial_sbc = aac.serial_sbc
        AND aac.serial_att = scg.serial_att
        AND aac.serial_act = scg.serial_act
        AND scg.status_scg = 'ACTIVE'
    JOIN activity act ON act.serial_act = scg.serial_act
    JOIN subperiod sbp ON sbp.serial_prd = scg.serial_prd
        AND DATE(act.due_date_act) BETWEEN sbp.start_date_sbp AND sbp.end_date_sbp
    JOIN student_by_course sc ON sc.serial_std = scg.serial_std
        AND sc.status_stc = 'ACTIVE'
	JOIN subject_course sbc1 ON scg.serial_sbc = sbc1.serial_sbc
		AND sc.serial_crs = sbc1.serial_crs
    WHERE
        `status_aac` = 'ACTIVE'
            AND aac.serial_sbc = serialsbc
            AND ((scg.is_complete_scg = 'YES'
            AND scg.activity_grade_part_scg IS NOT NULL)
            OR scg.status_scg = 'NOT_APPLY')
            AND sbp.serial_sbp = serialsbp
    GROUP BY aac.serial_sbc , aac.serial_act
    HAVING students_graded = students_course) tgraded ON tgraded.serial_att = spa.serial_att
        AND tgraded.serial_sbc = sbc.serial_sbc
        LEFT JOIN
    (SELECT
        scg.serial_sbc,
            aac.serial_att,
            sbp.serial_sbp,
            aac.serial_act,
            COUNT(DISTINCT scg.serial_std) students_graded,
            (SELECT
                    COUNT(DISTINCT sb.serial_std)
                FROM
                student_by_course sb
                JOIN subject_course sbcin ON sb.serial_crs = sbcin.serial_crs
				        JOIN student std ON std.serial_std = sb.serial_std
					        AND std.status_std = 'ACTIVE'
                JOIN student_criteria_grade scg1 ON scg1.serial_crs = sb.serial_crs
					        AND scg1.serial_std = sb.serial_std
                  AND scg1.status_scg = 'ACTIVE'
                JOIN activity act1 ON act1.serial_act = scg1.serial_act
				        WHERE sb.status_stc = 'ACTIVE' AND sbcin.serial_sbc = scg.serial_sbc
					      AND act1.serial_act = aac.serial_act) students_course
    FROM
        activity_applied_criterias aac
    JOIN student_criteria_grade scg ON scg.serial_sbc = aac.serial_sbc
        AND aac.serial_att = scg.serial_att
        AND aac.serial_act = scg.serial_act
        AND scg.status_scg = 'ACTIVE'
    JOIN activity act ON act.serial_act = scg.serial_act
    JOIN subperiod sbp ON sbp.serial_prd = scg.serial_prd
        AND DATE(act.due_date_act) BETWEEN sbp.start_date_sbp AND sbp.end_date_sbp
    JOIN student_by_course sc ON sc.serial_std = scg.serial_std
        AND sc.status_stc = 'ACTIVE'
	JOIN subject_course sbc1 ON scg.serial_sbc = sbc1.serial_sbc
		AND sc.serial_crs = sbc1.serial_crs
    WHERE
        `status_aac` = 'ACTIVE'
            AND aac.serial_sbc = serialsbc
            AND ((scg.is_complete_scg = 'YES'
            AND scg.activity_grade_part_scg IS NOT NULL)
            OR scg.status_scg = 'NOT_APPLY')
            AND sbp.serial_sbp = serialsbp
    GROUP BY aac.serial_act
    HAVING students_graded != students_course
        AND students_graded > 0) pgraded ON pgraded.serial_att = spa.serial_att
        AND pgraded.serial_sbc = sbc.serial_sbc
        LEFT JOIN
    (SELECT
        scg.serial_sbc,
            aac.serial_att,
            sbp.serial_sbp,
            aac.serial_act,
            COUNT(DISTINCT scg.serial_std) students_graded,
             (SELECT
                    COUNT(DISTINCT sb.serial_std)
                FROM
                student_by_course sb
                JOIN subject_course sbcin ON sb.serial_crs = sbcin.serial_crs
				        JOIN student std ON std.serial_std = sb.serial_std
					        AND std.status_std = 'ACTIVE'
                JOIN student_criteria_grade scg1 ON scg1.serial_crs = sb.serial_crs
					        AND scg1.serial_std = sb.serial_std
                  AND scg1.status_scg = 'ACTIVE'
                JOIN activity act1 ON act1.serial_act = scg1.serial_act
				        WHERE sb.status_stc = 'ACTIVE' AND sbcin.serial_sbc = scg.serial_sbc
					        AND act1.serial_act = aac.serial_act) students_course
    FROM
        activity_applied_criterias aac
    JOIN student_criteria_grade scg ON scg.serial_sbc = aac.serial_sbc
        AND aac.serial_att = scg.serial_att
        AND aac.serial_act = scg.serial_act
        AND scg.status_scg = 'ACTIVE'
    JOIN activity act ON act.serial_act = scg.serial_act
    JOIN subperiod sbp ON sbp.serial_prd = scg.serial_prd
        AND DATE(act.due_date_act) BETWEEN sbp.start_date_sbp AND sbp.end_date_sbp
    JOIN student_by_course sc ON sc.serial_std = scg.serial_std
        AND sc.status_stc = 'ACTIVE'
	JOIN subject_course sbc1 ON scg.serial_sbc = sbc1.serial_sbc
		AND sc.serial_crs = sbc1.serial_crs
    WHERE
        `status_aac` = 'ACTIVE'
            AND aac.serial_sbc = serialsbc
            AND ((scg.is_complete_scg = 'NO'
            AND scg.activity_grade_part_scg IS NULL)
            OR scg.status_scg = 'NOT_APPLY')
            AND sbp.serial_sbp = serialsbp
    GROUP BY aac.serial_act
    HAVING students_graded = students_course) ngraded ON ngraded.serial_att = spa.serial_att
        AND ngraded.serial_sbc = sbc.serial_sbc
WHERE
    `att`.`serial_att` <> 1
        AND `sbc`.`serial_sbc` = serialsbc
        AND `spa`.`serial_prd` = serialprd
        AND `status_spa` = 'ACTIVE'
        AND `status_att` = 'ACTIVE'
        AND `status_sbc` = '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 = serialsbc and serial_osb is null 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 (serialsbc, NULL, serialsbp, v_TmpActivityType, v_TmpNoGraded, v_TmpPartiallyGraded, v_TmpTotallyGraded);
						END IF;					
				END LOOP;
			  CLOSE CR_Actividades;
END $$
DELIMITER ;