DROP VIEW if exists `view_activity_grades`;
CREATE VIEW `view_activity_grades` AS SELECT scy.serial_scy, scy.name_scy ,crs.serial_crs, stc.serial_std,std.first_name_std,std.last_name_std,sbc.serial_sbc,CONCAT(IF((csb2.name_csb IS NULL),'',CONCAT(csb2.name_csb,'-')),csb.name_csb) as subject_name, prd.serial_prd, prd.name_prd, scg.serial_act,act.due_date_act,scg.serial_att,SUM(scg.activity_grade_part_scg) as act_grade
FROM school_year scy 
JOIN course crs ON crs.serial_scy = scy.serial_scy AND crs.status_crs = 'ACTIVE' 
JOIN student_by_course stc ON stc.serial_crs = crs.serial_crs AND stc.status_stc = 'ACTIVE'
JOIN subject_course sbc ON sbc.serial_crs = crs.serial_crs AND sbc.status_sbc = 'ACTIVE' 
JOIN subject sbj ON sbj.serial_sbj= sbc.serial_sbj AND sbj.status_sbj='ACTIVE'
JOIN cat_subject csb ON csb.serial_csb = sbj.serial_csb AND csb.status_csb = 'ACTIVE' 
LEFT JOIN cat_subject csb2 ON csb.cat_serial_csb = csb2.serial_csb
JOIN subject_course_period scp ON scp.serial_sbc = sbc.serial_sbc AND scp.status_scp = 'ACTIVE'
JOIN period prd ON prd.serial_prd = scp.serial_prd AND prd.status_prd = 'ACTIVE'
JOIN student std ON std.serial_std = stc.serial_std
JOIN student_criteria_grade scg ON scg.serial_prd=scp.serial_prd AND scg.serial_sbc = sbc.serial_sbc AND scg.serial_std=std.serial_std AND scg.status_scg = 'ACTIVE' AND scg.is_complete_scg = 'YES'
JOIN activity act ON act.serial_act = scg.serial_act
WHERE scy.status_scy NOT LIKE '%INACTIVE%'
AND scg.serial_att != 1
GROUP BY scg.serial_std,scg.serial_act,scg.serial_crs,scg.serial_sbc
ORDER BY scy.serial_scy,crs.serial_crs, stc.serial_std,subject_name,prd.serial_prd,serial_act,serial_att;

DROP VIEW if exists `view_opt_activity_grades`;
CREATE VIEW `view_opt_activity_grades` AS SELECT scy.serial_scy,scy.name_scy,osg.serial_opa,opa.due_date_opa,osg.serial_osb,CONCAT(IF((csb2.name_csb IS NULL),'',CONCAT(csb2.name_csb,'-')),csb.name_csb) as subject_name,osg.serial_att,osg.serial_prd,prd.name_prd,osg.serial_std,std.first_name_std,std.last_name_std,sum(activity_grade_part_osg) as act_grade
FROM opt_student_criteria_grade osg
JOIN opt_activity opa ON osg.serial_opa = opa.serial_opa AND opa.status_opa = 'ACTIVE'
JOIN student_optional_subject sos ON sos.serial_osb = osg.serial_osb AND sos.serial_std = osg.serial_std AND sos.serial_prd = osg.serial_prd AND sos.status_sos = 'ACTIVE'
JOIN student std ON std.serial_std = sos.serial_std AND std.status_std = 'ACTIVE'
JOIN optional_subject osb ON osb.serial_osb = osg.serial_osb AND osb.status_osb = 'ACTIVE'
JOIN cat_subject csb ON csb.serial_csb = osb.serial_csb AND csb.status_csb = 'ACTIVE' 
LEFT JOIN cat_subject csb2 ON csb.cat_serial_csb = csb2.serial_csb
JOIN period_group_year pgy ON pgy.serial_pgy = osb.serial_pgy AND pgy.status_pgy = 'ACTIVE'
JOIN school_year scy ON scy.serial_scy = pgy.serial_scy AND scy.status_scy NOT LIKE '%INACTIVE%'
JOIN period prd ON prd.serial_prd = osg.serial_prd AND prd.status_prd = 'ACTIVE'
WHERE osg.status_osg = 'ACTIVE' 
AND is_complete_osg = 'YES'
GROUP BY osg.serial_opa,osg.serial_osb,osg.serial_att,osg.serial_prd,osg.serial_std
ORDER BY scy.serial_scy,osg.serial_std,subject_name,osg.serial_prd,osg.serial_opa,osg.serial_att;