
-- ONE more DDL TEST FOR PROCEDURE
CREATE DEFINER=`USER`@`%` PROCEDURE `PUBLISH_ASSESSMENT_PERCENTAGE`(IN branchId BIGINT(20),sectionId BIGINT(20),termId BIGINT(20),sessionId BIGINT(20),createdBy BIGINT(20))
BEGIN
    
 
 INSERT INTO REPORT_CARD_SUMM(SUBJECT_ID,ASSESSMENT_BRANCH_ID,IF_RUBRIC_BASED,RUBRIC_ID,STUDENT_ID,MARKS,CCE_ACTIVITY_GROUP_ID,GRADE,IF_UPGRADED,
 BRANCH_ID,DESCRIPTOR,STATUS,CREATED_ON,CREATED_BY,DISCIPLINE_ID,MODIFIED_ON,MODIFIED_BY,CCE_ACTIVITY_ID,MAX_MARKS,PASS_MARKS) 
  
  ( SELECT r.SUBJECT_ID,r.ASSESSMENT_BRANCH_ID ,FALSE, NULL, r.STUDENT_ID, ROUND((marksSum/maxMarks)*MAXGRADE,2) AS Marks, r.CCE_ACTIVITY_GROUP_ID, IF(marksSum = 0 AND DS.STUDENT_ID > 0 AND DS.ASSESSMENT_BRANCH_ID > 0,DS.REMARK, MG.GRADE )grade, FALSE, r.BRANCH_ID, "", 41, Now(), (SELECT createdBy) createdBy, IF(marksSum = 0 AND DS.STUDENT_ID > 0 AND DS.ASSESSMENT_BRANCH_ID > 0,DS.DISCIPLINE_ID, 0) AS DISCIPLINE_ID, NOW(), (SELECT createdBy)  modifiedBy, NULL, r.maxMarks, r.passMarks FROM ( 
 SELECT CA.SUBJECT_ID,CA.ASSESSMENT_BRANCH_ID ,CM.STUDENT_ID,CM.CCE_ACTIVITY_ID,CM.MARKS,CA.WEIGHTAGE, 
 SUM(CM.MARKS) AS marksSum, SUM(IF(CM.STATUS = 41, CA.MAX_MARKS, 0)) AS maxMarks,SUM(CA.PASS_MARKS) AS passMarks,CA.CCE_ACTIVITY_GROUP_ID , 
 CONCAT(CM.STUDENT_ID,'_',CA.SUBJECT_ID,'_',CA.ASSESSMENT_BRANCH_ID,IF(CA.CCE_ACTIVITY_GROUP_ID IS NULL,'',CONCAT('_',CA.CCE_ACTIVITY_GROUP_ID))) GROUPINGCOL 
 ,CM.BRANCH_ID, IFNULL(MG1.MAXGRADE,100) MAXGRADE FROM CCE_MARKS CM 
 INNER JOIN CCE_ACTIVITY CA ON CA.CCE_ACTIVITY_ID = CM.CCE_ACTIVITY_ID AND CA.BRANCH_ID = CM.BRANCH_ID 
 INNER JOIN ASSESSMENT_BRANCH AB ON AB.ASSESSMENT_BRANCH_ID = CA.ASSESSMENT_BRANCH_ID AND AB.BRANCH_ID = CA.BRANCH_ID 
 INNER JOIN STUDENT S ON S.STD_ID = CM.STUDENT_ID AND  S.BRANCH_ID = CM.BRANCH_ID AND S.STATUS =41 
 LEFT OUTER JOIN RESULT_WEIGHTAGE RW ON RW.BRANCH_ID = CA.BRANCH_ID AND RW.SUBJECT_ID = CA.SUBJECT_ID AND RW.ASSESSMENT_BRANCH_ID = CA.ASSESSMENT_BRANCH_ID
 INNER JOIN (
 SELECT NCM.STUDENT_ID,NCA.SUBJECT_ID,NCA.ASSESSMENT_BRANCH_ID,SUM(NCA.WEIGHTAGE) WT
 FROM CCE_ACTIVITY NCA
 INNER JOIN CCE_MARKS NCM ON NCM.CCE_ACTIVITY_ID=NCA.CCE_ACTIVITY_ID AND NCA.BRANCH_ID=NCM.BRANCH_ID
 INNER JOIN STUDENT NS ON NS.STD_ID = NCM.STUDENT_ID AND NS.BRANCH_ID = NCA.BRANCH_ID AND NS.STATUS != 1444
 WHERE NS.STD_SECTION =sectionId AND NCA.BRANCH_ID =branchId
  GROUP BY NCM.STUDENT_ID,NCA.SUBJECT_ID,NCA.ASSESSMENT_BRANCH_ID
  ) IQ ON IQ.STUDENT_ID = S.STD_ID AND IQ.SUBJECT_ID = CA.SUBJECT_ID AND IQ.ASSESSMENT_BRANCH_ID = CA.ASSESSMENT_BRANCH_ID
 LEFT JOIN (SELECT BRANCH_ID,SUBJECT_ID,MAX(IF(MARK_END>0,MARK_END,MARK_END_NUMBER)) MAXGRADE FROM MASTER_GRADE M
 WHERE M.BRANCH_ID=branchId AND M.STATUS=41 GROUP BY M.SUBJECT_ID ) MG1 ON MG1.SUBJECT_ID=CA.SUBJECT_ID 
 
 
 WHERE S.STD_SECTION =sectionId AND CM.BRANCH_ID =branchId AND AB.ACADEMIC_TERM_ID =termId 
 AND (RW.RESULT_WEIGHTAGE_ID IS NULL OR (IQ.WT = 0 AND RW.RULE_ID IS NULL)) AND CA.SUMM_TYPE IN (1)   
 GROUP BY 11) r 
 LEFT OUTER JOIN DISCIPLINE DS ON  DS.BRANCH_ID = r.BRANCH_ID AND DS.STUDENT_ID = r.STUDENT_ID AND DS.ASSESSMENT_BRANCH_ID = r.ASSESSMENT_BRANCH_ID AND DS.CCE_ACTIVITY_ID = r.CCE_ACTIVITY_ID
 
 LEFT JOIN MASTER_GRADE MG ON MG.BRANCH_ID=branchId AND MG.SUBJECT_ID=r.SUBJECT_ID AND MG.STATUS=41 AND 
 CASE WHEN MG.MARK_END > 0 THEN (ROUND((marksSum/maxMarks)*MAXGRADE,2) BETWEEN MG.MARK_START AND MG.MARK_END) 
 ELSE  (ROUND((marksSum/maxMarks)*MAXGRADE,2) BETWEEN MG.MARK_START_NUMBER AND  MG.MARK_END_NUMBER) END 
 
 GROUP BY r.GROUPINGCOL  );
 
 
    END
