-- One test case using PROCEDURE
CREATE DEFINER=`root`@`%` PROCEDURE `EXAM_REPORT_SUMMARY2`(IN branchId BIGINT(20),sectionId BIGINT(20),termId BIGINT(20),sessionId BIGINT(20),createdBy BIGINT(20))
BEGIN
DROP TABLE IF EXISTS SUMMARY;
CREATE TEMPORARY TABLE SUMMARY (
 LEVEL_ID BIGINT(20) DEFAULT NULL, LEVEL VARCHAR(100) DEFAULT NULL, BRANCH_ID BIGINT(20) DEFAULT NULL, CLASS_ID BIGINT(20) DEFAULT NULL, CLASS VARCHAR(100) DEFAULT NULL, SECTION_ID BIGINT(20) DEFAULT NULL, SECTION VARCHAR(100) DEFAULT NULL, STD_ROLL_NUM VARCHAR(100) DEFAULT NULL, ADMISSION_NO VARCHAR(100) DEFAULT NULL, STUDENT_ID BIGINT(20) DEFAULT NULL, STUDENT VARCHAR(200) DEFAULT NULL, ACADEMIC_SESSION_ID BIGINT(20) DEFAULT NULL, ACADEMIC_SESSION VARCHAR(100) DEFAULT NULL, ACADEMIC_TERM_ID BIGINT(20) DEFAULT NULL, ACADEMIC_TERM VARCHAR(100) DEFAULT NULL, TERM_SEQUENCE BIGINT(20) DEFAULT NULL, ASSESSMENT_NAME_ID BIGINT(20) DEFAULT NULL, ASSESSMENT_NAME VARCHAR(100) DEFAULT NULL, ASSESSMENT_SEQUENCE BIGINT(20) DEFAULT NULL, ASSESSMENT_GROUP_ID BIGINT(20) DEFAULT NULL, ASSESSMENT_GROUP VARCHAR(100) DEFAULT NULL, ASSESSMENT_GROUP_SEQUENCE BIGINT(20) DEFAULT NULL, ASSESSMENT_TYPE INT(4) DEFAULT NULL, ISSA INT(5) DEFAULT NULL, SUBJECT_GROUP_ID BIGINT(20) DEFAULT NULL, SUBJECT_GROUP VARCHAR(100) DEFAULT NULL, SUBJECT_GROUP_SEQUENCE BIGINT(20) DEFAULT NULL, SUBJECT_ID BIGINT(20) DEFAULT NULL, SUBJECT VARCHAR(100) DEFAULT NULL, SUBJECT_SEQUENCE BIGINT(20) DEFAULT NULL, SUBJECT_TYPE_ID BIGINT(20) DEFAULT NULL, SUBJECT_TYPE VARCHAR(100) DEFAULT NULL, ACT_MASTER_ID BIGINT(20) DEFAULT NULL, ACTIVITY_ID BIGINT(20) DEFAULT NULL, ACTIVITY VARCHAR(100) DEFAULT NULL, ACTIVITY_SEQUENCE BIGINT(20) DEFAULT NULL, ACTIVITY_GROUP_ID BIGINT(20) DEFAULT NULL, ACTIVITY_GROUP VARCHAR(100) DEFAULT NULL, RUB_MASTER_ID BIGINT(20) DEFAULT NULL, RUBRIC_ID BIGINT(20) DEFAULT NULL, RUBRIC VARCHAR(100) DEFAULT NULL, RUBRIC_SEQUENCE BIGINT(20) DEFAULT NULL, MAX_MARKS DOUBLE DEFAULT NULL, WEIGHTAGE_MAX_MARKS DOUBLE DEFAULT NULL, PASS_MARKS DOUBLE DEFAULT NULL, WEIGHTAGE_PASS_MARKS DOUBLE DEFAULT NULL, MARKS VARCHAR(100) DEFAULT NULL, WEIGHTAGE_MARKS VARCHAR(100) DEFAULT NULL, PERCENTAGE VARCHAR(100) DEFAULT NULL, WEIGHTAGE_PERCENTAGE VARCHAR(100) DEFAULT NULL, GRADE VARCHAR(100) DEFAULT NULL, WEIGHTAGE_GRADE VARCHAR(100) DEFAULT NULL, GP DOUBLE DEFAULT NULL, WEIGHTAGE_GP DOUBLE DEFAULT NULL, DESCRIPTION VARCHAR(255) DEFAULT NULL, WEIGHTAGE_DESCRIPTION VARCHAR(255) DEFAULT NULL, DESCRIPTORS VARCHAR(512) DEFAULT NULL, MARK_STATUS INT(11) DEFAULT NULL, GRADE_IMAGE VARCHAR(255) DEFAULT NULL, WEIGHTAGE_GRADE_IMAGE VARCHAR(255) DEFAULT NULL, DISCIPLINE_ID BIGINT(20), 
 
 
 KEY IDX_SUMMARY_LEVEL_ID (LEVEL_ID), KEY IDX_SUMMARY_LEVEL (LEVEL), KEY IDX_SUMMARY_BRANCH_ID (BRANCH_ID), KEY IDX_SUMMARY_CLASS_ID (CLASS_ID), KEY IDX_SUMMARY_CLASS (CLASS), KEY IDX_SUMMARY_SECTION_ID (SECTION_ID), KEY IDX_SUMMARY_SECTION (SECTION), KEY IDX_SUMMARY_STUDENT (STUDENT), KEY IDX_SUMMARY_ACADEMIC_SESSION_ID (ACADEMIC_SESSION_ID), KEY IDX_SUMMARY_ACADEMIC_SESSION (ACADEMIC_SESSION), KEY IDX_SUMMARY_ACADEMIC_TERM_ID (ACADEMIC_TERM_ID), KEY IDX_SUMMARY_ACADEMIC_TERM (ACADEMIC_TERM), KEY IDX_SUMMARY_TERM_SEQUENCE (TERM_SEQUENCE), KEY IDX_SUMMARY_ASSESSMENT_NAME_ID (ASSESSMENT_NAME_ID), KEY IDX_SUMMARY_ASSESSMENT_NAME (ASSESSMENT_NAME), KEY IDX_SUMMARY_ASSESSMENT_SEQUENCE (ASSESSMENT_SEQUENCE), KEY IDX_SUMMARY_ASSESSMENT_GROUP_ID (ASSESSMENT_GROUP_ID), KEY IDX_SUMMARY_ASSESSMENT_GROUP (ASSESSMENT_GROUP), KEY IDX_SUMMARY_SUBJECT_GROUP_ID (SUBJECT_GROUP_ID), KEY IDX_SUMMARY_SUBJECT_GROUP_SEQUENCE (SUBJECT_GROUP_SEQUENCE), KEY IDX_SUMMARY_SUBJECT_ID (SUBJECT_ID), KEY IDX_SUMMARY_SUBJECT (SUBJECT), KEY IDX_SUMMARY_SUBJECT_SEQUENCE (SUBJECT_SEQUENCE), KEY IDX_SUMMARY_SUBJECT_TYPE_ID (SUBJECT_TYPE_ID), KEY IDX_SUMMARY_SUBJECT_TYPE (SUBJECT_TYPE), KEY IDX_SUMMARY_ACTIVITY_ID (ACTIVITY_ID), KEY IDX_SUMMARY_ACTIVITY (ACTIVITY), KEY IDX_SUMMARY_ACTIVITY_SEQUENCE (ACTIVITY_SEQUENCE), KEY IDX_SUMMARY_ACTIVITY_GROUP_ID (ACTIVITY_GROUP_ID), KEY IDX_SUMMARY_ACTIVITY_GROUP (ACTIVITY_GROUP), KEY IDX_SUMMARY_RUBRIC_ID (RUBRIC_ID), 
 KEY IDX_SUMMARY_RUBRIC_SEQUENCE (RUBRIC_SEQUENCE), KEY IDX_SUMMARY_MAX_MARKS (MAX_MARKS), KEY IDX_SUMMARY_WEIGHTAGE_MAX_MARKS (WEIGHTAGE_MAX_MARKS), KEY IDX_SUMMARY_PASS_MARKS (PASS_MARKS), KEY IDX_SUMMARY_MARKS (MARKS), KEY IDX_SUMMARY_WEIGHTAGE_MARKS (WEIGHTAGE_MARKS), KEY IDX_SUMMARY_PERCENTAGE (PERCENTAGE), KEY IDX_SUMMARY_WEIGHTAGE_PERCENTAGE (WEIGHTAGE_PERCENTAGE), KEY IDX_SUMMARY_GRADE (GRADE), KEY IDX_SUMMARY_WEIGHTAGE_GRADE (WEIGHTAGE_GRADE), KEY IDX_SUMMARY_GP (GP), KEY IDX_SUMMARY_WEIGHTAGE_GP (WEIGHTAGE_GP), KEY IDX_SUMMARY_MARK_STATUS (MARK_STATUS)
 
) ENGINE=MEMORY ; 
DROP TABLE IF EXISTS SUMMARY2;
CREATE TEMPORARY TABLE SUMMARY2 (
 LEVEL_ID BIGINT(20) DEFAULT NULL, LEVEL VARCHAR(100) DEFAULT NULL, BRANCH_ID BIGINT(20) DEFAULT NULL, CLASS_ID BIGINT(20) DEFAULT NULL, CLASS VARCHAR(100) DEFAULT NULL, SECTION_ID BIGINT(20) DEFAULT NULL, SECTION VARCHAR(100) DEFAULT NULL, STD_ROLL_NUM VARCHAR(100) DEFAULT NULL, ADMISSION_NO VARCHAR(100) DEFAULT NULL, STUDENT_ID BIGINT(20) DEFAULT NULL, STUDENT VARCHAR(200) DEFAULT NULL, ACADEMIC_SESSION_ID BIGINT(20) DEFAULT NULL, ACADEMIC_SESSION VARCHAR(100) DEFAULT NULL, ACADEMIC_TERM_ID BIGINT(20) DEFAULT NULL, ACADEMIC_TERM VARCHAR(100) DEFAULT NULL, TERM_SEQUENCE BIGINT(20) DEFAULT NULL, ASSESSMENT_NAME_ID BIGINT(20) DEFAULT NULL, ASSESSMENT_NAME VARCHAR(100) DEFAULT NULL, ASSESSMENT_SEQUENCE BIGINT(20) DEFAULT NULL, ASSESSMENT_GROUP_ID BIGINT(20) DEFAULT NULL, ASSESSMENT_GROUP VARCHAR(100) DEFAULT NULL, ASSESSMENT_GROUP_SEQUENCE BIGINT(20) DEFAULT NULL, ASSESSMENT_TYPE INT(4) DEFAULT NULL, ISSA INT(5) DEFAULT NULL, SUBJECT_GROUP_ID BIGINT(20) DEFAULT NULL, SUBJECT_GROUP VARCHAR(100) DEFAULT NULL, SUBJECT_GROUP_SEQUENCE BIGINT(20) DEFAULT NULL, SUBJECT_ID BIGINT(20) DEFAULT NULL, SUBJECT VARCHAR(100) DEFAULT NULL, SUBJECT_SEQUENCE BIGINT(20) DEFAULT NULL, SUBJECT_TYPE_ID BIGINT(20) DEFAULT NULL, SUBJECT_TYPE VARCHAR(100) DEFAULT NULL, ACT_MASTER_ID BIGINT(20) DEFAULT NULL, ACTIVITY_ID BIGINT(20) DEFAULT NULL, ACTIVITY VARCHAR(100) DEFAULT NULL, ACTIVITY_SEQUENCE BIGINT(20) DEFAULT NULL, ACTIVITY_GROUP_ID BIGINT(20) DEFAULT NULL, ACTIVITY_GROUP VARCHAR(100) DEFAULT NULL, RUB_MASTER_ID BIGINT(20) DEFAULT NULL, RUBRIC_ID BIGINT(20) DEFAULT NULL, RUBRIC VARCHAR(100) DEFAULT NULL, RUBRIC_SEQUENCE BIGINT(20) DEFAULT NULL, MAX_MARKS DOUBLE DEFAULT NULL, WEIGHTAGE_MAX_MARKS DOUBLE DEFAULT NULL, PASS_MARKS DOUBLE DEFAULT NULL, WEIGHTAGE_PASS_MARKS DOUBLE DEFAULT NULL, MARKS VARCHAR(100) DEFAULT NULL, WEIGHTAGE_MARKS VARCHAR(100) DEFAULT NULL, PERCENTAGE VARCHAR(100) DEFAULT NULL, WEIGHTAGE_PERCENTAGE VARCHAR(100) DEFAULT NULL, GRADE VARCHAR(100) DEFAULT NULL, WEIGHTAGE_GRADE VARCHAR(100) DEFAULT NULL, GP DOUBLE DEFAULT NULL, WEIGHTAGE_GP DOUBLE DEFAULT NULL, DESCRIPTION VARCHAR(255) DEFAULT NULL, WEIGHTAGE_DESCRIPTION VARCHAR(255) DEFAULT NULL, DESCRIPTORS VARCHAR(512) DEFAULT NULL, MARK_STATUS INT(11) DEFAULT NULL, GRADE_IMAGE VARCHAR(255) DEFAULT NULL, WEIGHTAGE_GRADE_IMAGE VARCHAR(255) DEFAULT NULL, DISCIPLINE_ID BIGINT(20) 
 
) ENGINE=MEMORY ;
INSERT INTO SUMMARY (LEVEL_ID, LEVEL, BRANCH_ID, CLASS_ID, CLASS, SECTION_ID, SECTION, STD_ROLL_NUM, ADMISSION_NO, STUDENT_ID, STUDENT, ACADEMIC_SESSION_ID, ACADEMIC_SESSION, ACADEMIC_TERM_ID,
 ACADEMIC_TERM, TERM_SEQUENCE, ASSESSMENT_NAME_ID, ASSESSMENT_NAME, ASSESSMENT_SEQUENCE, ASSESSMENT_GROUP_ID, ASSESSMENT_GROUP, ASSESSMENT_GROUP_SEQUENCE, ASSESSMENT_TYPE, ISSA, SUBJECT_GROUP_ID,
  SUBJECT_GROUP, SUBJECT_GROUP_SEQUENCE, SUBJECT_ID, SUBJECT, SUBJECT_SEQUENCE, SUBJECT_TYPE_ID, SUBJECT_TYPE, ACT_MASTER_ID, ACTIVITY_ID, ACTIVITY, ACTIVITY_SEQUENCE, ACTIVITY_GROUP_ID, ACTIVITY_GROUP,
   RUB_MASTER_ID, RUBRIC_ID, RUBRIC, RUBRIC_SEQUENCE,
 MAX_MARKS, WEIGHTAGE_MAX_MARKS, PASS_MARKS, WEIGHTAGE_PASS_MARKS, MARKS, WEIGHTAGE_MARKS, PERCENTAGE, WEIGHTAGE_PERCENTAGE, GRADE, WEIGHTAGE_GRADE, GP, WEIGHTAGE_GP, DESCRIPTION,
  WEIGHTAGE_DESCRIPTION, DESCRIPTORS, MARK_STATUS, GRADE_IMAGE, WEIGHTAGE_GRADE_IMAGE, DISCIPLINE_ID )
(
SELECT DISTINCT
1 LEVEL_ID,'SubjectActivityRubricAssessmentTerm' LEVEL ,R.BRANCH_ID,R.CLASS_ID,R.CLASS,R.SECTION_ID,R.SECTION,
R.STD_ROLL_NUM,R.ADMISSION_NO,R.STD_ID, R.STUDENT, R.ACADEMIC_SESSION_ID,R.ACADEMIC_SESSION,
R.ACADEMIC_TERM_ID,R.ACADEMIC_TERM,R.TERM_SEQUENCE,R.ASSESSMENT_NAME_ID, R.ASSESSMENT_NAME,R.ASSESSMENT_SEQUENCE,
R.ASSESSMENT_GROUP_ID,R.ASSESSMENT_GROUP, R.ASSESSMENT_GROUP_SEQUENCE,R.ASSESSMENT_TYPE, R.ISSA,
R.SUBJECT_GROUP_ID, R.SUBJECT_GROUP,R.SUBJECT_GROUP_SEQUENCE, R.SUBJECT_ID,R.SUBJECT,R.SUBJECT_SEQUENCE,
R.SUBJECT_TYPE_ID, R.SUBJECT_TYPE, R.MASTER_ACT_ID ACT_MASTER_ID, R.ACTIVITY_ID, R.ACTIVITY,R.ACTIVITY_SEQUENCE, 
R.ACTIVITY_GROUP_ID, R.ACTIVITY_GROUP, R.RUB_MASTER_ID, R.RUBRIC_ID, R.RUBRIC,R.RUBRIC_SEQUENCE, 
R.MAX_MARKS, R.MAX_MARKS WEIGHTAGE_MAX_MARKS,R.PASS_MARKS,R.PASS_MARKS WEIGHTAGE_PASS_MARKS ,
R.MARKS  AS MARKS, 
R.MARKS  AS WEIGHTAGE_MARKS, 
IF(R.MAX_MARKS>0,ROUND(R.MARKS*100/R.MAX_MARKS,2),0) AS PERCENTAGE, IF(R.MAX_MARKS>0,ROUND(R.MARKS*100/R.MAX_MARKS,2),0) AS WEIGHTAGE_PERCENTAGE,
CASE WHEN R.STATUS =0 THEN '' WHEN R.STATUS=1 THEN '' WHEN R.STATUS=2 THEN IFNULL(R.GRADE,IFNULL(MG.GRADE,MG1.GRADE)) END AS GRADE,
CASE WHEN R.STATUS =0 THEN '' WHEN R.STATUS=1 THEN '' WHEN R.STATUS=2 THEN IFNULL(R.GRADE,IFNULL(MG.GRADE,MG1.GRADE)) END AS WEIGHTAGE_GRADE,
CASE WHEN R.STATUS =0 || R.STATUS=1 THEN '' WHEN R.STATUS=2 THEN IFNULL(R.CGPA, IFNULL(MG.CGPA,MG1.CGPA)) END AS GP,
CASE WHEN R.STATUS =0 || R.STATUS=1 THEN '' WHEN R.STATUS=2 THEN IFNULL(R.CGPA, IFNULL(MG.CGPA,MG1.CGPA)) END AS WEIGHTAGE_GP,
CASE WHEN R.STATUS =0 || R.STATUS=1 THEN '' WHEN R.STATUS=2 THEN IFNULL(R.DESCRIPTION, IFNULL(MG.DESCRIPTION,MG1.DESCRIPTION)) END AS DESCRIPTION,
CASE WHEN R.STATUS =0 || R.STATUS=1 THEN '' WHEN R.STATUS=2 THEN IFNULL(R.DESCRIPTION, IFNULL(MG.DESCRIPTION,MG1.DESCRIPTION)) END AS WEIGHTAGE_DESCRIPTION,
IFNULL(CD.DESCRIPTOR,IFNULL(CD1.DESCRIPTOR,'')) DESCRIPTORS, 
R.STATUS MARK_STATUS,
IFNULL(R.IMAGE_PATH,IFNULL(MG.IMAGE_PATH,MG1.IMAGE_PATH)) AS GRADE_IMAGE_PATH, IFNULL(R.IMAGE_PATH,IFNULL(MG.IMAGE_PATH,MG1.IMAGE_PATH)) AS WEIGHTAGE_GRADE_IMAGE_PATH,
DISCIPLINE_ID
FROM (SELECT 

CM.BRANCH_ID,C.CLASS_ID,C.NAME CLASS,SC.SECTION_ID,SC.NAME SECTION,
ST.STD_ROLL_NUM,ST.ADMISSION_NO ADMISSION_NO,ST.STD_ID,
CONCAT(IFNULL(ST.STD_FNAME,''),
CASE WHEN ST.STD_MNAME IS NULL THEN '' ELSE CONCAT(' ',ST.STD_MNAME) END,
CASE WHEN ST.STD_LNAME IS NULL THEN '' ELSE CONCAT(' ',ST.STD_LNAME) END ) STUDENT,
ACS.ACADEMIC_SESSION_ID,ACS.DURATION ACADEMIC_SESSION,
ACT.ACADEMIC_TERM_ID,ACT.NAME ACADEMIC_TERM,ACT.SEQUENCE TERM_SEQUENCE,
AB.ASSESSMENT_BRANCH_ID ASSESSMENT_NAME_ID,AB.NAME ASSESSMENT_NAME,AB.DISPLAY_SEQUENCE ASSESSMENT_SEQUENCE,
AG.ASSESSMENT_GROUP_ID,AG.NAME ASSESSMENT_GROUP,AG.SEQ_NO ASSESSMENT_GROUP_SEQUENCE,
AB.TYPE ASSESSMENT_TYPE,AB.IF_CONTINUOUS ISSA,
MSG.MARKSHEET_GROUP_ID SUBJECT_GROUP_ID,MSG.NAME SUBJECT_GROUP,MSG.SEQ_NO SUBJECT_GROUP_SEQUENCE,
CA.SUBJECT_ID,S.NAME SUBJECT,S.SEQ_NO SUBJECT_SEQUENCE,S.TYPE AS SUBJECT_TYPE_ID,TM.NAME AS SUBJECT_TYPE,
CA.MASTER_ID MASTER_ACT_ID, CA.CCE_ACTIVITY_ID ACTIVITY_ID,CA.NAME ACTIVITY,CA.SEQ_NO ACTIVITY_SEQUENCE,
IFNULL(ACG.ACTIVITY_GROUP_ID,0) ACTIVITY_GROUP_ID,IFNULL(ACG.NAME,'TOTAL') ACTIVITY_GROUP,CR.MASTER_ID RUB_MASTER_ID,
CR.CCE_RUBRIC_ID RUBRIC_ID, CR.DESCRIPTOR AS RUBRIC,CR.SEQ_NO RUBRIC_SEQUENCE,
 

CASE WHEN ROC.LEVEL IS NOT NULL 
THEN ROUND_OFF(CM.MARKS,ROC.ROUND_OFF_TECHNIQUE, IFNULL(ROC.PRECISION_VAL,0)) ELSE ROUND(CM.MARKS,2) END AS MARKS,
IF(CA.SUMM_TYPE IN (4,5,7),IFNULL(MG2.MAXGRADE,MG1.MAXGRADE), CR.MAX_MARKS) MAX_MARKS, 
CASE WHEN ROC.LEVEL IS NOT NULL 
THEN ROUND_OFF(CR.PASS_MARKS,ROC.ROUND_OFF_TECHNIQUE, IFNULL(ROC.PRECISION_VAL,0)) ELSE 
ROUND(CR.PASS_MARKS,2) END PASS_MARKS, 
MG.GRADE grade, MG.CGPA , MG.DESCRIPTION, MG.IMAGE_PATH,  
CM.STATUS,DS.DISCIPLINE_ID, IFNULL(MG2.MAXGRADE,IFNULL(MG1.MAXGRADE,100)) MAXGRADE
 
FROM CCE_RUB_MARKS CM 
INNER JOIN CCE_RUBRIC CR ON CR.CCE_RUBRIC_ID = CM.CCE_RUBRIC_ID AND CR.BRANCH_ID = CM.BRANCH_ID   
INNER JOIN CCE_ACTIVITY CA ON CA.CCE_ACTIVITY_ID = CR.CCE_ACTIVITY_ID AND CA.BRANCH_ID = CM.BRANCH_ID 
LEFT JOIN ACTIVITY_GROUP ACG ON ACG.ACTIVITY_GROUP_ID = CA.ACTIVITY_GROUP_ID AND ACG.BRANCH_ID=CA.BRANCH_ID AND ACG.STATUS=CA.STATUS
LEFT OUTER JOIN DISCIPLINE DS ON DS.CCE_RUBRIC_ID = CM.CCE_RUBRIC_ID AND DS.STUDENT_ID = CM.STUDENT_ID AND  DS.CCE_ACTIVITY_ID = CA.CCE_ACTIVITY_ID
AND DS.ASSESSMENT_BRANCH_ID = CA.ASSESSMENT_BRANCH_ID AND DS.BRANCH_ID = CM.BRANCH_ID  
LEFT JOIN DISCIPLINE_TYPE DT ON DT.DISCIPLINE_TYPE_ID=DS.TYPE AND DT.BRANCH_ID = DS.BRANCH_ID 
INNER JOIN ASSESSMENT_BRANCH AB ON AB.ASSESSMENT_BRANCH_ID = CA.ASSESSMENT_BRANCH_ID AND AB.BRANCH_ID = CA.BRANCH_ID AND AB.STATUS=CA.STATUS
LEFT JOIN ASSESSMENT_GROUP AG ON AB.ASSESSMENT_GROUP_ID=AG.ASSESSMENT_GROUP_ID AND AG.BRANCH_ID=AB.BRANCH_ID AND AG.STATUS=AB.STATUS
INNER JOIN STUDENT ST ON ST.STD_ID = CM.STUDENT_ID AND  ST.BRANCH_ID = CM.BRANCH_ID AND ST.STATUS =CA.STATUS AND ST.ACADEMIC_SESSION_ID=CA.ACADEMIC_SESSION_ID  
INNER JOIN CLASS C ON C.CLASS_ID=ST.STD_CLASS AND C.BRANCH_ID=ST.BRANCH_ID AND C.STATUS=ST.STATUS
INNER JOIN SECTION SC ON ST.STD_SECTION = SC.SECTION_ID AND SC.CLASS_ID=C.CLASS_ID AND SC.BRANCH_ID=C.BRANCH_ID AND SC.STATUS=C.STATUS
INNER JOIN ACADEMIC_SESSION ACS ON ST.ACADEMIC_SESSION_ID = ACS.ACADEMIC_SESSION_ID AND ACS.BRANCH_ID = ST.BRANCH_ID AND ACS.STATUS = ST.STATUS
INNER JOIN SUBJECT S ON S.SUBJECT_ID = CA.SUBJECT_ID AND S.CLASS_ID = SC.CLASS_ID AND S.ACADEMIC_SESSION_ID=ACS.ACADEMIC_SESSION_ID AND S.BRANCH_ID = SC.BRANCH_ID  AND ACS.STATUS = S.STATUS 
INNER JOIN TYPE_MASTER TM ON TM.TYPE_MASTER_ID=S.TYPE 
LEFT JOIN MARKSHEET_GROUP MSG ON MSG.MARKSHEET_GROUP_ID = S.MARKSHEET_GROUP_ID AND MSG.BRANCH_ID=S.BRANCH_ID AND MSG.STATUS = S.STATUS	
INNER JOIN ACADEMIC_TERM ACT ON ACT.ACADEMIC_TERM_ID=AB.ACADEMIC_TERM_ID AND ACT.ACADEMIC_SESSION_ID = ACS.ACADEMIC_SESSION_ID AND ACT.CLASS_ID = ST.STD_CLASS AND ACT.BRANCH_ID = ST.BRANCH_ID 
AND ST.STATUS = ACT.STATUS 
LEFT JOIN MASTER_GRADE MG ON MG.MASTER_GRADE_ID=CM.MASTER_GRADE_ID AND MG.SUBJECT_ID=CA.SUBJECT_ID AND MG.STATUS=CA.STATUS 
LEFT JOIN (SELECT BRANCH_ID,SUBJECT_ID,MAX(IF(MARK_END>0,MARK_END,MARK_END_NUMBER)) MAXGRADE FROM MASTER_GRADE 
WHERE BRANCH_ID=branchId AND STATUS=41 GROUP BY SUBJECT_ID ) MG1 ON MG1.SUBJECT_ID=CA.SUBJECT_ID
LEFT JOIN (SELECT BRANCH_ID,SUBJECT_ID,ASSESSMENT_BRANCH_ID,MAX(IF(MARK_END>0,MARK_END,MARK_END_NUMBER)) MAXGRADE FROM MASTER_GRADE 
WHERE BRANCH_ID=branchId AND STATUS=41 GROUP BY SUBJECT_ID,ASSESSMENT_BRANCH_ID ) MG2 ON MG2.SUBJECT_ID=CA.SUBJECT_ID AND MG2.ASSESSMENT_BRANCH_ID=CA.ASSESSMENT_BRANCH_ID 
LEFT JOIN ROUND_OFF_CONFIGURATION ROC ON ROC.CLASS_ID = ST.STD_CLASS  AND ROC.LEVEL=1630 AND ROC.BRANCH_ID=ST.BRANCH_ID 
WHERE  ST.STD_SECTION =sectionId AND CM.BRANCH_ID =branchId  AND AB.ACADEMIC_TERM_ID =termId
AND ACS.ACADEMIC_SESSION_ID=sessionId

GROUP BY CA.SUBJECT_ID,CA.ASSESSMENT_BRANCH_ID,CM.STUDENT_ID ,CA.CCE_ACTIVITY_ID , CR.CCE_RUBRIC_ID 
)R 
LEFT JOIN MASTER_GRADE MG ON MG.BRANCH_ID=branchId AND MG.SUBJECT_ID=R.SUBJECT_ID AND MG.STATUS=41 AND MG.ASSESSMENT_BRANCH_ID = R.ASSESSMENT_NAME_ID AND
CASE WHEN MG.MARK_END > 0 THEN (ROUND(IF(R.MAX_MARKS>0,(R.MARKS *R.MAXGRADE/R.MAX_MARKS),R.MAX_MARKS),2) BETWEEN MG.MARK_START AND MG.MARK_END) 
ELSE  (ROUND(IF(R.MAX_MARKS>0,(R.MARKS *R.MAXGRADE/R.MAX_MARKS),R.MAX_MARKS),2) BETWEEN MG.MARK_START_NUMBER AND  MG.MARK_END_NUMBER) END  
LEFT JOIN CO_SCHO_DES CD ON CD.CCE_RUBRIC_ID=R.RUBRIC_ID AND R.BRANCH_ID = CD.BRANCH_ID  AND MG.MASTER_GRADE_ID=CD.MASTER_GRADE_ID
LEFT JOIN MASTER_GRADE MG1 ON MG1.BRANCH_ID=branchId AND MG1.SUBJECT_ID=R.SUBJECT_ID AND MG1.STATUS=41 AND MG1.ASSESSMENT_BRANCH_ID IS NULL AND 
CASE WHEN MG1.MARK_END > 0 THEN (ROUND(IF(R.MAX_MARKS>0,(R.MARKS *R.MAXGRADE/R.MAX_MARKS),R.MAX_MARKS),2) BETWEEN MG1.MARK_START AND MG1.MARK_END) 
ELSE  (ROUND(IF(R.MAX_MARKS>0,(R.MARKS *R.MAXGRADE/R.MAX_MARKS),R.MAX_MARKS),2) BETWEEN MG1.MARK_START_NUMBER AND  MG1.MARK_END_NUMBER) END  
LEFT JOIN CO_SCHO_DES CD1 ON CD1.CCE_RUBRIC_ID=R.RUBRIC_ID AND R.BRANCH_ID = CD1.BRANCH_ID  AND MG1.MASTER_GRADE_ID=CD1.MASTER_GRADE_ID
)
;

INSERT INTO SUMMARY2 (LEVEL_ID, LEVEL, BRANCH_ID, CLASS_ID, CLASS, SECTION_ID, SECTION, STD_ROLL_NUM, ADMISSION_NO, STUDENT_ID, STUDENT, ACADEMIC_SESSION_ID, ACADEMIC_SESSION, ACADEMIC_TERM_ID,
 ACADEMIC_TERM, TERM_SEQUENCE, ASSESSMENT_NAME_ID, ASSESSMENT_NAME, ASSESSMENT_SEQUENCE, ASSESSMENT_GROUP_ID, ASSESSMENT_GROUP, ASSESSMENT_GROUP_SEQUENCE, ASSESSMENT_TYPE, ISSA, SUBJECT_GROUP_ID,
  SUBJECT_GROUP, SUBJECT_GROUP_SEQUENCE, SUBJECT_ID, SUBJECT, SUBJECT_SEQUENCE, SUBJECT_TYPE_ID, SUBJECT_TYPE, ACT_MASTER_ID, ACTIVITY_ID, ACTIVITY, ACTIVITY_SEQUENCE, ACTIVITY_GROUP_ID, ACTIVITY_GROUP,
   RUB_MASTER_ID, RUBRIC_ID, RUBRIC, RUBRIC_SEQUENCE,
 MAX_MARKS, WEIGHTAGE_MAX_MARKS, PASS_MARKS, WEIGHTAGE_PASS_MARKS, MARKS, WEIGHTAGE_MARKS, PERCENTAGE, WEIGHTAGE_PERCENTAGE, GRADE, WEIGHTAGE_GRADE, GP, WEIGHTAGE_GP, DESCRIPTION,
  WEIGHTAGE_DESCRIPTION, DESCRIPTORS, MARK_STATUS, GRADE_IMAGE, WEIGHTAGE_GRADE_IMAGE, DISCIPLINE_ID )
(SELECT 2 LEVEL_ID, "SubjectActivityRubricTermFA&SA" LEVEL, O.BRANCH_ID, O.CLASS_ID, O.CLASS, O.SECTION_ID, O.SECTION, O.STD_ROLL_NUM, O.ADMISSION_NO, O.STUDENT_ID, O.STUDENT, O.ACADEMIC_SESSION_ID, O.ACADEMIC_SESSION,
 O.ACADEMIC_TERM_ID, O.ACADEMIC_TERM, O.TERM_SEQUENCE, 0 ASSESSMENT_NAME_ID, IF(ISSA=0,'FA','SA') ASSESSMENT_NAME, 0 ASSESSMENT_SEQUENCE, 0 ASSESSMENT_GROUP_ID, 'TOTAL' ASSESSMENT_GROUP, 0 ASSESSMENT_GROUP_SEQUENCE, 
 O.ASSESSMENT_TYPE, O.ISSA, O.SUBJECT_GROUP_ID,
 O.SUBJECT_GROUP, O.SUBJECT_GROUP_SEQUENCE, O.SUBJECT_ID, O.SUBJECT, O.SUBJECT_SEQUENCE, O.SUBJECT_TYPE_ID, O.SUBJECT_TYPE, O.ACT_MASTER_ID, 0 ACTIVITY_ID, O.ACTIVITY, O.ACTIVITY_SEQUENCE, 
 O.ACTIVITY_GROUP_ID, O.ACTIVITY_GROUP, O.RUB_MASTER_ID, 0 RUBRIC_ID, O.RUBRIC, O.RUBRIC_SEQUENCE,

O.MAX_MARKS, O.WEIGHTAGE_MAX_MARKS, O.PASS_MARKS, O.WEIGHTAGE_PASS_MARKS, O.MARKS, O.WEIGHTAGE_MARKS, ROUND_OFF(IF(MAX_MARKS>0, MARKS*100/MAX_MARKS,0),0,2) PERCENTAGE,
 ROUND_OFF(IF(WEIGHTAGE_MAX_MARKS>0, WEIGHTAGE_MARKS*100/WEIGHTAGE_MAX_MARKS,0),0,2) WEIGHTAGE_PERCENTAGE,
CASE WHEN O.MARK_STATUS < 2 THEN '' WHEN O.MARK_STATUS=2 THEN IFNULL(MG1.GRADE,'') END AS GRADE,
CASE WHEN O.MARK_STATUS < 2 THEN '' WHEN O.MARK_STATUS=2 THEN IFNULL(MG2.GRADE,'') END AS WEIGHTAGE_GRADE,
CASE WHEN O.MARK_STATUS < 2 THEN '' WHEN O.MARK_STATUS=2 THEN MG1.CGPA END AS GP,
CASE WHEN O.MARK_STATUS < 2 THEN '' WHEN O.MARK_STATUS=2 THEN MG2.CGPA END AS WEIGHTAGE_GP,
CASE WHEN O.MARK_STATUS < 2 THEN '' WHEN O.MARK_STATUS=2 THEN IFNULL(MG1.DESCRIPTION,'') END AS DESCRIPTION,
CASE WHEN O.MARK_STATUS < 2 THEN '' WHEN O.MARK_STATUS=2 THEN IFNULL(MG2.DESCRIPTION,'') END AS WEIGHTAGE_DESCRIPTION,
O.DESCRIPTORS, O.MARK_STATUS MARK_STATUS,
IFNULL(MG1.IMAGE_PATH,'') AS GRADE_IMAGE_PATH, 
IFNULL(MG2.IMAGE_PATH,'') AS WEIGHTAGE_GRADE_IMAGE_PATH, 
DISCIPLINE_ID
FROM(
SELECT R.BRANCH_ID, R.CLASS_ID, R.CLASS, R.SECTION_ID, R.SECTION, R.STD_ROLL_NUM, R.ADMISSION_NO, R.STUDENT_ID, R.STUDENT, R.ACADEMIC_SESSION_ID, R.ACADEMIC_SESSION, R.ACADEMIC_TERM_ID, R.ACADEMIC_TERM, 
R.TERM_SEQUENCE, R.ASSESSMENT_NAME_ID, R.ASSESSMENT_NAME, R.ASSESSMENT_SEQUENCE, R.ASSESSMENT_GROUP_ID, R.ASSESSMENT_GROUP, R.ASSESSMENT_GROUP_SEQUENCE, R.ASSESSMENT_TYPE, R.ISSA, R.SUBJECT_GROUP_ID,
 R.SUBJECT_GROUP, R.SUBJECT_GROUP_SEQUENCE, R.SUBJECT_ID, R.SUBJECT, R.SUBJECT_SEQUENCE,
 R.SUBJECT_TYPE_ID, R.SUBJECT_TYPE, R.ACT_MASTER_ID, R.ACTIVITY_ID, R.ACTIVITY, R.ACTIVITY_SEQUENCE, R.ACTIVITY_GROUP_ID, R.ACTIVITY_GROUP, R.RUB_MASTER_ID, R.RUBRIC_ID, R.RUBRIC, R.RUBRIC_SEQUENCE,
 

SUM(R.MAX_MARKS) MAX_MARKS,


 SUM(R.WEIGHTAGE_MAX_MARKS) WEIGHTAGE_MAX_MARKS, 


CASE WHEN ROC.LEVEL IS NOT NULL 
	THEN ROUND_OFF(SUM(R.MARKS),ROC.ROUND_OFF_TECHNIQUE, IFNULL(ROC.PRECISION_VAL,0)) 
	ELSE ROUND(SUM(R.MARKS),2) END AS MARKS,
 
 CASE WHEN ROC.LEVEL IS NOT NULL 
		THEN ROUND_OFF(SUM(R.PASS_MARKS),ROC.ROUND_OFF_TECHNIQUE, IFNULL(ROC.PRECISION_VAL,0))
		ELSE ROUND(SUM(R.PASS_MARKS),2) END  PASS_MARKS,
 
 
 

 CASE WHEN ROC.LEVEL IS NOT NULL 
		THEN ROUND_OFF(SUM(R.WEIGHTAGE_MARKS),ROC.ROUND_OFF_TECHNIQUE, IFNULL(ROC.PRECISION_VAL,0)) ELSE ROUND(SUM(R.WEIGHTAGE_MARKS),2) END
		AS  WEIGHTAGE_MARKS, 
 
 
 CASE WHEN ROC.LEVEL IS NOT NULL 
		THEN ROUND_OFF(SUM(R.WEIGHTAGE_PASS_MARKS),ROC.ROUND_OFF_TECHNIQUE, IFNULL(ROC.PRECISION_VAL,0)) ELSE ROUND(SUM(R.WEIGHTAGE_PASS_MARKS),2) END
		AS  WEIGHTAGE_PASS_MARKS, 
 
REPLACE(GROUP_CONCAT(IFNULL(R.DESCRIPTORS,'')),",,",",") DESCRIPTORS,
  
CASE  WHEN SUM(IF(R.MARK_STATUS=0,1,0))=SUM(1) AND SUM(IF(R.MARK_STATUS=0,1,0))+SUM(IF(R.MARK_STATUS=1,1,0))=SUM(1) THEN 0
	WHEN SUM(IF(R.MARK_STATUS=1,1,0))=SUM(1) THEN 1
	ELSE 2
END AS MARK_STATUS, 
R.MAXGRADE,
CASE WHEN SUM(IF(R.MARK_STATUS=0,1,0))+SUM(IF(R.MARK_STATUS=1,1,0))=SUM(1) THEN  R.DISCIPLINE_ID 
	ELSE NULL 
END AS  DISCIPLINE_ID
FROM
  
(
	SELECT S.*, IFNULL(MG.MAXGRADE,100) MAXGRADE FROM SUMMARY S
		LEFT JOIN DISCIPLINE DS ON DS.DISCIPLINE_ID=S.DISCIPLINE_ID AND DS.BRANCH_ID=S.BRANCH_ID
		LEFT JOIN DISCIPLINE_TYPE DT ON DT.DISCIPLINE_TYPE_ID = DS.TYPE
		LEFT JOIN (SELECT BRANCH_ID,SUBJECT_ID,MAX(IF(MARK_END>0,MARK_END,MARK_END_NUMBER)) MAXGRADE FROM MASTER_GRADE 	WHERE BRANCH_ID=branchId AND STATUS=41 GROUP BY SUBJECT_ID ) MG 
				ON MG.SUBJECT_ID=S.SUBJECT_ID
		WHERE S.LEVEL_ID=1 AND S.SECTION_ID =sectionId AND S.BRANCH_ID =branchId  AND S.ACADEMIC_TERM_ID =termId	AND S.ACADEMIC_SESSION_ID=sessionId
		ORDER BY S.STUDENT_ID,S.ASSESSMENT_NAME_ID,S.SUBJECT_ID,S.ACTIVITY_ID,S.RUBRIC_ID, S.WEIGHTAGE_MARKS DESC,S.MARK_STATUS, DS.REMARK DESC
) R 
LEFT JOIN ROUND_OFF_CONFIGURATION ROC ON ROC.CLASS_ID = R.CLASS_ID AND ROC.LEVEL=1634 AND ROC.BRANCH_ID=R.BRANCH_ID 
LEFT JOIN DISCIPLINE DS ON DS.DISCIPLINE_ID=R.DISCIPLINE_ID AND DS.BRANCH_ID=R.BRANCH_ID
LEFT JOIN DISCIPLINE_TYPE DT ON DT.DISCIPLINE_TYPE_ID = DS.TYPE
GROUP BY R.STUDENT_ID, R.SUBJECT_ID, R.ACADEMIC_TERM_ID,ASSESSMENT_TYPE,ISSA, R.ACT_MASTER_ID, R.RUB_MASTER_ID
ORDER BY R.STUDENT_ID,R.SUBJECT_ID, R.ACADEMIC_TERM_ID,ASSESSMENT_TYPE,ISSA,R.ACT_MASTER_ID, R.RUB_MASTER_ID, R.MARK_STATUS, DS.REMARK DESC
) O
LEFT JOIN MASTER_GRADE MG1 ON MG1.BRANCH_ID=branchId AND MG1.SUBJECT_ID=O.SUBJECT_ID AND MG1.STATUS=41 AND MG1.ASSESSMENT_BRANCH_ID IS NULL AND 
CASE WHEN MG1.MARK_END > 0 THEN (ROUND(IF(O.MAX_MARKS>0,(O.MARKS *O.MAXGRADE/O.MAX_MARKS),O.MAX_MARKS),2) BETWEEN MG1.MARK_START AND MG1.MARK_END) 
ELSE  (ROUND(IF(O.MAX_MARKS>0,(O.MARKS *O.MAXGRADE/O.MAX_MARKS),O.MAX_MARKS),2) BETWEEN MG1.MARK_START_NUMBER AND  MG1.MARK_END_NUMBER) END  
LEFT JOIN MASTER_GRADE MG2 ON  MG2.BRANCH_ID=branchId AND  MG2.SUBJECT_ID=O.SUBJECT_ID AND  MG2.STATUS=41 AND  MG2.ASSESSMENT_BRANCH_ID IS NULL AND 
CASE WHEN  MG2.MARK_END > 0 THEN (ROUND(IF(O.WEIGHTAGE_MAX_MARKS>0,(O.WEIGHTAGE_MARKS *O.MAXGRADE/O.WEIGHTAGE_MAX_MARKS),O.WEIGHTAGE_MAX_MARKS),2) BETWEEN  MG2.MARK_START AND  MG2.MARK_END) 
ELSE  (ROUND(IF(O.WEIGHTAGE_MAX_MARKS>0,(O.WEIGHTAGE_MARKS *O.MAXGRADE/O.WEIGHTAGE_MAX_MARKS),O.MAX_MARKS),2) BETWEEN  MG2.MARK_START_NUMBER AND   MG2.MARK_END_NUMBER) END  
);
INSERT INTO SUMMARY
( SELECT * FROM SUMMARY2);
TRUNCATE SUMMARY2;

INSERT INTO EXAM_SUMMARY (LEVEL_ID, LEVEL, BRANCH_ID, CLASS_ID, CLASS, SECTION_ID, SECTION, STD_ROLL_NUM, ADMISSION_NO, STUDENT_ID, STUDENT, ACADEMIC_SESSION_ID, ACADEMIC_SESSION, ACADEMIC_TERM_ID,
 ACADEMIC_TERM, TERM_SEQUENCE, ASSESSMENT_NAME_ID, ASSESSMENT_NAME, ASSESSMENT_SEQUENCE, ASSESSMENT_GROUP_ID, ASSESSMENT_GROUP, ASSESSMENT_GROUP_SEQUENCE, ASSESSMENT_TYPE, ISSA, SUBJECT_GROUP_ID,
  SUBJECT_GROUP, SUBJECT_GROUP_SEQUENCE, SUBJECT_ID, SUBJECT, SUBJECT_SEQUENCE, SUBJECT_TYPE_ID, SUBJECT_TYPE, ACT_MASTER_ID, ACTIVITY_ID, ACTIVITY, ACTIVITY_SEQUENCE, ACTIVITY_GROUP_ID, ACTIVITY_GROUP,
   RUB_MASTER_ID, RUBRIC_ID, RUBRIC, RUBRIC_SEQUENCE,
 MAX_MARKS, WEIGHTAGE_MAX_MARKS, PASS_MARKS, WEIGHTAGE_PASS_MARKS, MARKS, WEIGHTAGE_MARKS, PERCENTAGE, WEIGHTAGE_PERCENTAGE, GRADE, WEIGHTAGE_GRADE, GP, WEIGHTAGE_GP, DESCRIPTION,
  WEIGHTAGE_DESCRIPTION, DESCRIPTORS, MARK_STATUS, GRADE_IMAGE, WEIGHTAGE_GRADE_IMAGE, DISCIPLINE_ID,CREATED_BY,CREATED_ON )
(
SELECT LEVEL_ID, LEVEL, BRANCH_ID, CLASS_ID, CLASS, SECTION_ID, SECTION, STD_ROLL_NUM, ADMISSION_NO, STUDENT_ID, STUDENT, ACADEMIC_SESSION_ID, ACADEMIC_SESSION, ACADEMIC_TERM_ID,
 ACADEMIC_TERM, TERM_SEQUENCE, ASSESSMENT_NAME_ID, ASSESSMENT_NAME, ASSESSMENT_SEQUENCE, ASSESSMENT_GROUP_ID, ASSESSMENT_GROUP, ASSESSMENT_GROUP_SEQUENCE, ASSESSMENT_TYPE, ISSA, SUBJECT_GROUP_ID,
  SUBJECT_GROUP, SUBJECT_GROUP_SEQUENCE, SUBJECT_ID, SUBJECT, SUBJECT_SEQUENCE, SUBJECT_TYPE_ID, SUBJECT_TYPE, ACT_MASTER_ID, ACTIVITY_ID, ACTIVITY, ACTIVITY_SEQUENCE, ACTIVITY_GROUP_ID, ACTIVITY_GROUP,
   RUB_MASTER_ID, RUBRIC_ID, RUBRIC, RUBRIC_SEQUENCE,
 MAX_MARKS, WEIGHTAGE_MAX_MARKS, PASS_MARKS, WEIGHTAGE_PASS_MARKS, MARKS, WEIGHTAGE_MARKS, PERCENTAGE, WEIGHTAGE_PERCENTAGE, GRADE, WEIGHTAGE_GRADE, GP, WEIGHTAGE_GP, DESCRIPTION,
  WEIGHTAGE_DESCRIPTION, DESCRIPTORS, MARK_STATUS, GRADE_IMAGE, WEIGHTAGE_GRADE_IMAGE, DISCIPLINE_ID,1,NOW() 
  FROM SUMMARY  
  
);
DROP TABLE SUMMARY;
DROP TABLE SUMMARY2;
END 