package com.bizofficer.apiweb.analytics; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.List; import javax.persistence.EntityManagerFactory; import javax.sql.DataSource; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.crossstore.ChangeSetPersister.NotFoundException; import org.springframework.stereotype.Service; import com.bizofficer.util.module.MysqlTableNames; import com.bizofficer.util.system.MakeDate; @Service public class AnalyticsServiceBk5{ private static final Logger logger = Logger.getLogger(AnalyticsServiceBk5.class); @Autowired EntityManagerFactory entityManagerFactory; @Autowired DataSource appDataSource; Connection conn; PreparedStatement ps; ResultSet rs; public List doExecute(Object obj) throws NotFoundException{ AnalyticsBean objBean = (AnalyticsBean) obj; List dataList = new ArrayList(); try { if(objBean.getTestSession()==null) { return dataList; } AnalyticsResponseBean analyticsResponseBeanObj = new AnalyticsResponseBean(); List sectionSetList = new ArrayList(); SectionSetBean sectionSetBeanObj = null; List sectionList = new ArrayList(); List topperCompareBeanList = new ArrayList(); TopperCompareBean topperCompareBeanObj = null; List fullMarksList = new ArrayList(); List obtainedMarksList = new ArrayList(); Integer timeSpentSeconds = null; Double percentVal = 0.0; String str = null; MakeDate makeDateObj = new MakeDate(); DecimalFormat df = new DecimalFormat("#.##"); logger.info("Test Session: " + objBean.getTestSession() ); logger.info("Student Id: " + objBean.getStudentId() ); String whereSql=""; conn = appDataSource.getConnection(); if (conn != null) { if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { whereSql=" AND s.student_id=? "; } ps = conn.prepareStatement("SELECT s.test_root_id,s.test_node_id,s.test_type,s.test_option,s.test_title,s.total_question,s.last_visited_question,s.total_time,s.marks_division,s.answer_division answerStatus,s.time_spent timeSpent,DATE(s.start_date) startDate,s.full_marks,s.marks_obtain FROM "+MysqlTableNames.getPgprepTestSummary()+" s where s.test_status=1 AND s.test_session=? "+whereSql); ps.setString(1, objBean.getTestSession()); if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { ps.setString(2, objBean.getStudentId()); } logger.info("One Query Analytics: " + ps); rs = ps.executeQuery(); if (rs.next()) { logger.info("Test Title: " + rs.getString("test_title") ); analyticsResponseBeanObj.setTestSession(objBean.getTestSession()); analyticsResponseBeanObj.setTestName(rs.getString("test_title")); analyticsResponseBeanObj.setTestType(rs.getInt("test_type")); logger.info("aaaaaaaa *********************************"); analyticsResponseBeanObj.setStartDate(makeDateObj.changeMysqlFormat(rs.getString("startDate"))); logger.info("bbbbbbbbb ************************************ marks_division: "+rs.getString("marks_division")); analyticsResponseBeanObj.setMarksDivision(rs.getString("marks_division")); if(rs.getInt("test_type")!=3) { analyticsResponseBeanObj.setCorrectMarks(Double.valueOf(rs.getString("marks_division").split(";")[0])); analyticsResponseBeanObj.setIncorrectMarks(Double.valueOf(rs.getString("marks_division").split(";")[1])); analyticsResponseBeanObj.setNotAnsweredMarks(Double.valueOf(rs.getString("marks_division").split(";")[2])); } logger.info("ccc ************************************"); analyticsResponseBeanObj.setTotalQuestion(rs.getInt("total_question")); analyticsResponseBeanObj.setAnswerStatus(rs.getString("answerStatus")); if(analyticsResponseBeanObj.getAnswerStatus()!=null) { String[] answerStatusArr = analyticsResponseBeanObj.getAnswerStatus().split("\\|"); analyticsResponseBeanObj.setCorrectAnswers(Integer.valueOf(answerStatusArr[0])); analyticsResponseBeanObj.setPercentageCorrectAnswers((analyticsResponseBeanObj.getCorrectAnswers()*100)/analyticsResponseBeanObj.getTotalQuestion()); analyticsResponseBeanObj.setIncorrectAnswers(Integer.valueOf(answerStatusArr[1])); analyticsResponseBeanObj.setPercentageIncorrectAnswers((analyticsResponseBeanObj.getIncorrectAnswers()*100)/analyticsResponseBeanObj.getTotalQuestion()); analyticsResponseBeanObj.setNotAnswers(Integer.valueOf(answerStatusArr[2])); analyticsResponseBeanObj.setPercentageNotAnswers((analyticsResponseBeanObj.getNotAnswers()*100)/analyticsResponseBeanObj.getTotalQuestion()); analyticsResponseBeanObj.setAttemptedQuestions(Integer.valueOf(answerStatusArr[0])+Integer.valueOf(answerStatusArr[1])); logger.info("dddd ************************************ AttemptedQuestions >> " +analyticsResponseBeanObj.getAttemptedQuestions()); if(analyticsResponseBeanObj.getAttemptedQuestions()!=null && analyticsResponseBeanObj.getAttemptedQuestions()>0) { percentVal = Double.valueOf((analyticsResponseBeanObj.getCorrectAnswers()*100)/analyticsResponseBeanObj.getAttemptedQuestions()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setPercentageCorrectAttemptedQuestions(Double.valueOf(str)); logger.info("eee ************************************"); percentVal = Double.valueOf((analyticsResponseBeanObj.getIncorrectAnswers()*100)/analyticsResponseBeanObj.getAttemptedQuestions()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setPercentageIncorrectAttemptedQuestions(Double.valueOf(str)); } logger.info("fff ************************************"); percentVal = Double.valueOf(((analyticsResponseBeanObj.getTotalQuestion()-analyticsResponseBeanObj.getCorrectAnswers())*100)/analyticsResponseBeanObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setQuestionWiseKnowledgeUnknown(Double.valueOf(str)); logger.info("ggg ************************************"); percentVal = Double.valueOf((analyticsResponseBeanObj.getCorrectAnswers()*100)/analyticsResponseBeanObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setQuestionWiseKnowledgeKnown(Double.valueOf(str)); } logger.info("hhh ************************************"); analyticsResponseBeanObj.setTimeSpent(rs.getInt("timeSpent")); analyticsResponseBeanObj.setTotalTime(rs.getInt("total_time")); analyticsResponseBeanObj.setTotalTimeLabel(this.convertSecondsToTimeLabel(rs.getInt("total_time")*60)); analyticsResponseBeanObj.setSpentTimeLabel(this.convertSecondsToTimeLabel(rs.getInt("timeSpent"))); analyticsResponseBeanObj.setRemainingTimeLabel(this.convertSecondsToTimeLabel((rs.getInt("total_time")*60)-rs.getInt("timeSpent")));; analyticsResponseBeanObj.setTotalTimeSeconds(rs.getInt("total_time")*60); analyticsResponseBeanObj.setTimeRemaining(analyticsResponseBeanObj.getTotalTimeSeconds()-analyticsResponseBeanObj.getTimeSpent()); if(analyticsResponseBeanObj.getTotalTimeSeconds()!=null && analyticsResponseBeanObj.getTotalTimeSeconds()>0) { percentVal = Double.valueOf((analyticsResponseBeanObj.getTimeSpent()*100)/analyticsResponseBeanObj.getTotalTimeSeconds()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setTimeTakenPercent(Double.valueOf(str)); percentVal = Double.valueOf(((analyticsResponseBeanObj.getTotalTimeSeconds()-analyticsResponseBeanObj.getTimeSpent())*100)/analyticsResponseBeanObj.getTotalTimeSeconds()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setTimeRemainingPercent(Double.valueOf(str)); } ///analyticsResponseBeanObj.setTotalTimeSeconds(analyticsResponseBeanObj.getTotalTime()*60); analyticsResponseBeanObj.setPercentageTime((analyticsResponseBeanObj.getTimeSpent()*100)/(analyticsResponseBeanObj.getTotalTime()*60)); if(analyticsResponseBeanObj.getTimeSpent()!=null && analyticsResponseBeanObj.getTimeSpent()>=60) { analyticsResponseBeanObj.setTimeSpentMinutes(Math.round(Math.floor(analyticsResponseBeanObj.getTimeSpent()/60))+":"+(analyticsResponseBeanObj.getTimeSpent()%60)); }else if(analyticsResponseBeanObj.getTimeSpent()!=null && analyticsResponseBeanObj.getTimeSpent()>0) { analyticsResponseBeanObj.setTimeSpentMinutes("00:"+analyticsResponseBeanObj.getTimeSpent()); }else { analyticsResponseBeanObj.setTimeSpentMinutes("00:00"); } Integer remainingTime = (rs.getInt("total_time")*60)-rs.getInt("timeSpent"); if(remainingTime>=60) { analyticsResponseBeanObj.setRemainingTime(Math.round(Math.floor(remainingTime/60))+":"+(remainingTime%60)); }else if(remainingTime>0) { analyticsResponseBeanObj.setRemainingTime("00:"+remainingTime); }else { analyticsResponseBeanObj.setRemainingTime("00:00"); } analyticsResponseBeanObj.setAverageTimePerQuestion(Math.round(analyticsResponseBeanObj.getTotalTimeSeconds()/analyticsResponseBeanObj.getTotalQuestion())); analyticsResponseBeanObj.setAverageTimePerQuestionLabel(this.convertSecondsToTimeLabel(analyticsResponseBeanObj.getAverageTimePerQuestion())); analyticsResponseBeanObj.setAverageSpeedPerQuestion(Math.round(analyticsResponseBeanObj.getTimeSpent()/analyticsResponseBeanObj.getTotalQuestion())); analyticsResponseBeanObj.setAverageSpeedPerQuestionLabel(this.convertSecondsToTimeLabel(analyticsResponseBeanObj.getAverageSpeedPerQuestion())); analyticsResponseBeanObj.setFullMarks(Double.valueOf(df.format(rs.getDouble("full_marks")))); analyticsResponseBeanObj.setMarksObtain(Double.valueOf(df.format(rs.getDouble("marks_obtain")))); analyticsResponseBeanObj.setPercentageMarks(Math.round((analyticsResponseBeanObj.getMarksObtain()*100)/analyticsResponseBeanObj.getFullMarks())); percentVal = Double.valueOf(((analyticsResponseBeanObj.getFullMarks()-analyticsResponseBeanObj.getMarksObtain())*100)/analyticsResponseBeanObj.getFullMarks()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setMarksWiseKnowledgeUnknown(Double.valueOf(str)); percentVal = Double.valueOf((analyticsResponseBeanObj.getMarksObtain()*100)/analyticsResponseBeanObj.getFullMarks()); str = String.format("%1.2f", percentVal); analyticsResponseBeanObj.setMarksWiseKnowledgeKnown(Double.valueOf(str)); if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { PreparedStatement psSD = conn.prepareStatement("select sname FROM "+MysqlTableNames.getPgprepStudentDetails()+" WHERE student_id=?"); psSD.setString(1, objBean.getStudentId()); logger.info("Two Query Student details for analytics: " + psSD); ResultSet rsSD = psSD.executeQuery(); if (rsSD.next()) { analyticsResponseBeanObj.setStudentName(rsSD.getString("sname")); } } /********************************************* * FOR MOCKTEST **/ if(analyticsResponseBeanObj.getAnswerStatus()!=null) { String[] answerStatusArr = analyticsResponseBeanObj.getAnswerStatus().split("\\|"); List overallPerformanceList = new ArrayList(); percentVal = (Double.valueOf(answerStatusArr[0])*100)/rs.getInt("total_question"); str = String.format("%1.2f", percentVal); OverallPerformanceBean opbObj = new OverallPerformanceBean(); opbObj.setY(Double.valueOf(str)); opbObj.setName("Correct: "+answerStatusArr[0]+"/"+rs.getInt("total_question")); opbObj.setColor("#46d39a"); overallPerformanceList.add(opbObj); percentVal = (Double.valueOf(answerStatusArr[1])*100)/rs.getInt("total_question"); str = String.format("%1.2f", percentVal); opbObj = new OverallPerformanceBean(); opbObj.setY(Double.valueOf(str)); opbObj.setName("Incorrect: "+answerStatusArr[1]+"/"+rs.getInt("total_question")); opbObj.setColor("#e55759"); overallPerformanceList.add(opbObj); percentVal = (Double.valueOf(answerStatusArr[2])*100)/rs.getInt("total_question"); str = String.format("%1.2f", percentVal); opbObj = new OverallPerformanceBean(); opbObj.setY(Double.valueOf(str)); opbObj.setName("Unattempted: "+answerStatusArr[2]+"/"+rs.getInt("total_question")); opbObj.setColor("#4198d7"); overallPerformanceList.add(opbObj); analyticsResponseBeanObj.setOverallPerformanceList(overallPerformanceList); } //get Section Info if section is allowd for the complete mocktest if (rs.getInt("test_option") == 3) { if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { whereSql=" AND student_id=? "; } PreparedStatement psAA = conn.prepareStatement("SELECT section_name FROM "+MysqlTableNames.getPgprepTestDetails()+" WHERE section_name is not null AND LENGTH(section_name)>0 AND test_session=? "+whereSql); psAA.setString(1, objBean.getTestSession()); if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { psAA.setString(2, objBean.getStudentId()); } logger.info("Three Query > Fetching mock section exam info: " + psAA); ResultSet rsAA = psAA.executeQuery(); /// CHECK IF SECTION IS AVAILABLE ******************************************* if(rsAA.next()) { PreparedStatement ps1 = conn.prepareStatement("SELECT section_name,count(id) totalQuestion, ROUND(SUM(total_marks),2) totalScore, ROUND(SUM(marks),2) obtainMarks, IFNULL(SUM(CASE WHEN status='Correct' THEN 1 END),0) num_correct,IFNULL(SUM(CASE WHEN status='Incorrect' THEN 1 END),0) num_incorrect,IFNULL(SUM(CASE WHEN status='NotAnswer' THEN 1 END),0) num_not_answer,IFNULL(SUM(time_spent),0) timeSpent FROM "+MysqlTableNames.getPgprepTestDetails()+" WHERE test_session=? and student_id=? group by section_name"); ps1.setString(1, objBean.getTestSession()); ps1.setString(2, objBean.getStudentId()); logger.info("Four Query > Fetching mock section exam info: " + ps1); ResultSet rs1 = ps1.executeQuery(); while (rs1.next()) { sectionList.add(rs1.getString("section_name")); sectionSetBeanObj = new SectionSetBean(); sectionSetBeanObj.setSectionName(rs1.getString("section_name")); sectionSetBeanObj.setTotalQuestion(rs1.getInt("totalQuestion")); sectionSetBeanObj.setSectionTotalScore(rs1.getDouble("totalScore")); sectionSetBeanObj.setCorrectQuestions(rs1.getInt("num_correct")); sectionSetBeanObj.setIncorrectQuestions(rs1.getInt("num_incorrect")); sectionSetBeanObj.setUnattemptedQuestions(rs1.getInt("num_not_answer")); sectionSetBeanObj.setSectionScore(rs1.getDouble("obtainMarks")); percentVal = (sectionSetBeanObj.getSectionScore()*100)/sectionSetBeanObj.getSectionTotalScore(); str = String.format("%1.2f", percentVal); sectionSetBeanObj.setPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanObj.getCorrectQuestions()*100)/sectionSetBeanObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanObj.setCorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanObj.getIncorrectQuestions()*100)/sectionSetBeanObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanObj.setIncorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanObj.getUnattemptedQuestions()*100)/sectionSetBeanObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanObj.setUnattemptedQuestionsPercentage(Double.valueOf(str)); timeSpentSeconds = rs1.getInt("timeSpent"); if(timeSpentSeconds!=null && timeSpentSeconds>=60) { sectionSetBeanObj.setTimeSpent(Math.round(Math.floor(timeSpentSeconds/60))+":"+(timeSpentSeconds%60)); }else if(timeSpentSeconds!=null && timeSpentSeconds>0) { sectionSetBeanObj.setTimeSpent("00:"+timeSpentSeconds); }else { sectionSetBeanObj.setTimeSpent("00:00"); } fullMarksList.add(Double.valueOf(sectionSetBeanObj.getSectionTotalScore())); obtainedMarksList.add(Double.valueOf(sectionSetBeanObj.getSectionScore())); logger.info("**************************************************************************"); logger.info("************ FIND SECTION SUB TOPICS **************************"); logger.info("**************************************************************************"); List childList = new ArrayList(); PreparedStatement psAAAA = conn.prepareStatement("SELECT substring_index(th.path,'.',1) topPath FROM "+MysqlTableNames.getParentTopicHierarchy()+" th, "+MysqlTableNames.getPgprepTestDetails()+" td WHERE th.topic_id=td.topic_id and td.test_session=? and td.section_name=? and td.student_id=? group by substring_index(th.path,'.',1) "); psAAAA.setString(1, objBean.getTestSession()); psAAAA.setString(2, rs1.getString("section_name")); psAAAA.setString(3, objBean.getStudentId()); logger.info("Top Node Query: " + psAAAA); ResultSet rsMyPath = psAAAA.executeQuery(); while (rsMyPath.next()) { logger.info("Section Name >>> " + rs1.getString("section_name") ); logger.info("Top Node ID: " + rsMyPath.getString("topPath") ); PreparedStatement psBBBB = conn.prepareStatement("SELECT topic_id,topic_title FROM "+MysqlTableNames.getParentTopicHierarchy()+" WHERE path='"+rsMyPath.getString("topPath")+"'"); ResultSet rsMyTopView = psBBBB.executeQuery(); if (rsMyTopView.next()) { logger.info("Top Node Topic ID: " + rsMyTopView.getString("topic_id") ); logger.info("Top Node Topic Title: " + rsMyTopView.getString("topic_title") ); logger.info("Section Name >>> " + rs1.getString("section_name") ); List topicChildrenList = this.getSubTopicTreeIds(new ArrayList(), rsMyTopView.getString("topic_title")); logger.info("Top Node topicChildrenList >>> " + topicChildrenList ); if(topicChildrenList!=null && topicChildrenList.size()>0) { PreparedStatement psMyTD = conn.prepareStatement("SELECT count(id) totalQuestion, ROUND(SUM(total_marks),2) totalScore, ROUND(SUM(marks),2) obtainMarks, IFNULL(SUM(CASE WHEN status='Correct' THEN 1 END),0) num_correct,IFNULL(SUM(CASE WHEN status='Incorrect' THEN 1 END),0) num_incorrect,IFNULL(SUM(CASE WHEN status='NotAnswer' THEN 1 END),0) num_not_answer,IFNULL(SUM(time_spent),0) timeSpent FROM "+MysqlTableNames.getPgprepTestDetails()+" WHERE test_session=? and section_name=? and student_id=? and topic_id IN ("+topicChildrenList.toString().substring(1,topicChildrenList.toString().length()-1)+")"); psMyTD.setString(1, objBean.getTestSession()); psMyTD.setString(2, rs1.getString("section_name")); psMyTD.setString(3, objBean.getStudentId()); logger.info("MyTestDetail Query: " + psMyTD); ResultSet rsMyTD = psMyTD.executeQuery(); if(rsMyTD.next()) { if(rsMyTD.getInt("totalQuestion")>0) { logger.info("Question Count Found topic_title: " + rsMyTopView.getString("topic_title") ); logger.info("Total Questions: "+rs1.getString("section_name")+" >> " + rsMyTD.getInt("totalQuestion") ); SectionSetBean sectionSetBeanOneObj = new SectionSetBean(); sectionSetBeanOneObj.setSectionName(rsMyTopView.getString("topic_title")); sectionSetBeanOneObj.setTotalQuestion(rsMyTD.getInt("totalQuestion")); sectionSetBeanOneObj.setSectionTotalScore(rsMyTD.getDouble("totalScore")); sectionSetBeanOneObj.setCorrectQuestions(rsMyTD.getInt("num_correct")); sectionSetBeanOneObj.setIncorrectQuestions(rsMyTD.getInt("num_incorrect")); sectionSetBeanOneObj.setUnattemptedQuestions(rsMyTD.getInt("num_not_answer")); sectionSetBeanOneObj.setSectionScore(rsMyTD.getDouble("obtainMarks")); percentVal = (sectionSetBeanOneObj.getSectionScore()*100)/sectionSetBeanOneObj.getSectionTotalScore(); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanOneObj.getCorrectQuestions()*100)/sectionSetBeanOneObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setCorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanOneObj.getIncorrectQuestions()*100)/sectionSetBeanOneObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setIncorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanOneObj.getUnattemptedQuestions()*100)/sectionSetBeanOneObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setUnattemptedQuestionsPercentage(Double.valueOf(str)); timeSpentSeconds = rsMyTD.getInt("timeSpent"); if(timeSpentSeconds!=null && timeSpentSeconds>=60) { sectionSetBeanOneObj.setTimeSpent(Math.round(Math.floor(timeSpentSeconds/60))+":"+(timeSpentSeconds%60)); }else if(timeSpentSeconds!=null && timeSpentSeconds>0) { sectionSetBeanOneObj.setTimeSpent("00:"+timeSpentSeconds); }else { sectionSetBeanOneObj.setTimeSpent("00:00"); } childList.add(sectionSetBeanOneObj); logger.info("****************************************************************"); logger.info("************** CHECK CHILD NODES *******************"); logger.info("****************************************************************"); PreparedStatement psTID = conn.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE parentnode_name=? LIMIT 1 "); psTID.setString(1, rsMyTopView.getString("topic_title")); logger.info("getTopicIds query: "+psTID); ResultSet rsTID = psTID.executeQuery(); if(rsTID.next()){ logger.info("****************************************************************"); logger.info("************** CHILD HIERARCHY START *******************"); logger.info("****************************************************************"); this.getDrillDownTree(rsMyTopView.getString("topic_title"), sectionSetBeanOneObj, objBean.getTestSession(), rs1.getString("section_name"), objBean.getStudentId()); } rsTID.close(); psTID.close(); } //************************************************************** } rsMyTD.close(); psMyTD.close(); } //close topicChildrenList } psBBBB.close(); rsMyTopView.close(); sectionSetBeanObj.setChildren(childList); //********************************************************************** } psAAAA.close(); rsMyPath.close(); sectionSetList.add(sectionSetBeanObj); } rs1.close(); ps1.close(); /// IF SECTION IS NOT AVAILABLE ******************************************* }else { /*********************************************************************** * IF SECTION IS NOT AVAILABLE STARTING * *********************************************************************/ logger.info("IF SECTION IS NOT AVAILABLE STARTING: " + objBean.getTestSession() ); if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { whereSql=" and td.student_id=? "; } PreparedStatement psAAAA = conn.prepareStatement("SELECT substring_index(th.path,'.',1) topPath FROM "+MysqlTableNames.getParentTopicHierarchy()+" th, "+MysqlTableNames.getPgprepTestDetails()+" td WHERE th.topic_id=td.topic_id and td.test_session=? "+whereSql+" group by substring_index(th.path,'.',1) "); psAAAA.setString(1, objBean.getTestSession()); if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { psAAAA.setString(2, objBean.getStudentId()); } logger.info("psAAAA Topics: " + psAAAA); ResultSet rsMyPath = psAAAA.executeQuery(); while (rsMyPath.next()) { logger.info("psAAAA topPath: " + rsMyPath.getString("topPath") ); PreparedStatement psBBBB = conn.prepareStatement("SELECT topic_title FROM "+MysqlTableNames.getParentTopicHierarchy()+" WHERE path='"+rsMyPath.getString("topPath")+"'"); ResultSet rsMyTopView = psBBBB.executeQuery(); if (rsMyTopView.next()) { logger.info("psAAAA topic_title: " + rsMyTopView.getString("topic_title") ); ///logger.info("TopicIds >>> " + this.getTopicIds(rsMyPath.getString("topPath")) ); List topicChildrenList = this.getSubTopicTreeIds(new ArrayList(), rsMyTopView.getString("topic_title")); if(topicChildrenList!=null && topicChildrenList.size()>0) { // if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { // whereSql=" and student_id=? "; // } ///"+whereSql+" PreparedStatement psMyTD = conn.prepareStatement("SELECT count(id) totalQuestion, ROUND(SUM(total_marks),2) totalScore, ROUND(SUM(marks),2) obtainMarks, IFNULL(SUM(CASE WHEN status='Correct' THEN 1 END),0) num_correct,IFNULL(SUM(CASE WHEN status='Incorrect' THEN 1 END),0) num_incorrect,IFNULL(SUM(CASE WHEN status='NotAnswer' THEN 1 END),0) num_not_answer,IFNULL(SUM(time_spent),0) timeSpent FROM "+MysqlTableNames.getPgprepTestDetails()+" WHERE test_session=? and topic_id IN ("+topicChildrenList.toString().substring(1,topicChildrenList.toString().length()-1)+")"); psMyTD.setString(1, objBean.getTestSession()); logger.info("psMyTD: " + psMyTD); // if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { // psMyTD.setString(2, objBean.getStudentId()); // } logger.info("MyTestDetail Query: " + psMyTD); ResultSet rsMyTD = psMyTD.executeQuery(); if(rsMyTD.next()) { if(rsMyTD.getInt("totalQuestion")>0) { SectionSetBean sectionSetBeanTwoObj = new SectionSetBean(); sectionList.add(rsMyTopView.getString("topic_title")); sectionSetBeanTwoObj.setSectionName(rsMyTopView.getString("topic_title")); sectionSetBeanTwoObj.setTotalQuestion(rsMyTD.getInt("totalQuestion")); sectionSetBeanTwoObj.setSectionTotalScore(rsMyTD.getDouble("totalScore")); sectionSetBeanTwoObj.setCorrectQuestions(rsMyTD.getInt("num_correct")); sectionSetBeanTwoObj.setIncorrectQuestions(rsMyTD.getInt("num_incorrect")); sectionSetBeanTwoObj.setUnattemptedQuestions(rsMyTD.getInt("num_not_answer")); sectionSetBeanTwoObj.setSectionScore(rsMyTD.getDouble("obtainMarks")); percentVal = (sectionSetBeanTwoObj.getSectionScore()*100)/sectionSetBeanTwoObj.getSectionTotalScore(); str = String.format("%1.2f", percentVal); sectionSetBeanTwoObj.setPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanTwoObj.getCorrectQuestions()*100)/sectionSetBeanTwoObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanTwoObj.setCorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanTwoObj.getIncorrectQuestions()*100)/sectionSetBeanTwoObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanTwoObj.setIncorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanTwoObj.getUnattemptedQuestions()*100)/sectionSetBeanTwoObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanTwoObj.setUnattemptedQuestionsPercentage(Double.valueOf(str)); timeSpentSeconds = rsMyTD.getInt("timeSpent"); if(timeSpentSeconds!=null && timeSpentSeconds>=60) { sectionSetBeanTwoObj.setTimeSpent(Math.round(Math.floor(timeSpentSeconds/60))+":"+(timeSpentSeconds%60)); }else if(timeSpentSeconds!=null && timeSpentSeconds>0) { sectionSetBeanTwoObj.setTimeSpent("00:"+timeSpentSeconds); }else { sectionSetBeanTwoObj.setTimeSpent("00:00"); } fullMarksList.add(Double.valueOf(sectionSetBeanTwoObj.getSectionTotalScore())); obtainedMarksList.add(Double.valueOf(sectionSetBeanTwoObj.getSectionScore())); logger.info("****************************************************************"); logger.info("************** CHECK CHILD NODES *******************"); logger.info("****************************************************************"); PreparedStatement psTID = conn.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE parentnode_name=? LIMIT 1 "); psTID.setString(1, rsMyTopView.getString("topic_title")); logger.info("getTopicIds query: "+psTID); ResultSet rsTID = psTID.executeQuery(); if(rsTID.next()){ logger.info("****************************************************************"); logger.info("************** CHILD HIERARCHY START *******************"); logger.info("****************************************************************"); this.getDrillDownTree(rsMyTopView.getString("topic_title"), sectionSetBeanTwoObj, objBean.getTestSession(), null, objBean.getStudentId()); } rsTID.close(); psTID.close(); sectionSetList.add(sectionSetBeanTwoObj); } //************************************************************** } rsMyTD.close(); psMyTD.close(); } //close topicChildrenList } psBBBB.close(); rsMyTopView.close(); //********************************************************************** } psAAAA.close(); rsMyPath.close(); } /// IF SECTION IS NOT AVAILABLE CONDITION rsAA.close(); psAA.close(); /// NOT MOCK TEST ************************************ }else{ if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { whereSql=" AND student_id=? "; } PreparedStatement ps6 = this.conn.prepareStatement("SELECT GROUP_CONCAT(DISTINCT topic_id) detail_ids FROM "+MysqlTableNames.getPgprepTestDetails()+" WHERE test_session=? "+whereSql); ps6.setString(1, objBean.getTestSession()); if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) { ps6.setString(2, objBean.getStudentId()); } ResultSet rs6 = ps6.executeQuery(); if(rs6.next()) { analyticsResponseBeanObj.setTopicIds(rs6.getString("detail_ids")); } rs6.close(); ps6.close(); } logger.info("Calculate Student Test Rank and Percentile"); logger.info("test_root_id="+rs.getInt("test_root_id")); logger.info("test_node_id="+rs.getInt("test_node_id")); logger.info("test_type="+rs.getInt("test_type")); //Calculate Student Test Rank and Percentile int score_percentage = (int) ((rs.getDouble("marks_obtain") * 100) / rs.getDouble("full_marks")); PreparedStatement ps2 = conn.prepareStatement("select count(*) num_test,IFNULL(SUM(CASE WHEN ROUND(marks_obtain*100/full_marks)>" + score_percentage + " THEN 1 END),0)+1 rank,IFNULL(SUM(CASE WHEN ROUND(marks_obtain*100/full_marks)<=" + score_percentage + " THEN 1 END),0) less FROM "+MysqlTableNames.getPgprepTestSummary()+" where test_root_id=? AND test_node_id=? AND test_type=? AND test_status=1"); ps2.setInt(1, rs.getInt("test_root_id")); ps2.setInt(2, rs.getInt("test_node_id")); ps2.setInt(3, rs.getInt("test_type")); logger.info("ELEVEN query: " + ps2); ResultSet rs2 = ps2.executeQuery(); if (rs2.next()) { int percentile = Math.round((rs2.getInt("less") * 100) / rs2.getInt("num_test")); analyticsResponseBeanObj.setRank(rs2.getInt("rank")); analyticsResponseBeanObj.setPercentile(percentile); analyticsResponseBeanObj.setTotalStudent(rs2.getInt("num_test")); } rs2.close(); ps2.close(); logger.info("Get topper and Average Data testType: "+rs.getInt("test_type")); if(rs.getInt("test_type")==3) { topperCompareBeanObj = new TopperCompareBean(); PreparedStatement ps4 = this.conn.prepareStatement("select t.answer_division,t.time_spent,s.sname,t.marks_obtain FROM "+MysqlTableNames.getPgprepTestSummary()+" t, "+MysqlTableNames.getPgprepStudentDetails()+" s WHERE t.student_id=s.student_id AND t.marks_obtain >= (SELECT MAX(marks_obtain) FROM "+MysqlTableNames.getPgprepTestSummary()+" WHERE test_root_id=? AND test_node_id=? AND test_status=1)"); ps4.setInt(1, rs.getInt("test_root_id")); ps4.setInt(2, rs.getInt("test_node_id")); logger.info("TWELVE topper data query: " + ps4); ResultSet rs4 = ps4.executeQuery(); if(rs4.next()) { logger.info("TWELVE topper Student Name: " + rs4.getString("sname")); topperCompareBeanObj = new TopperCompareBean(); String[] answerDivision = rs4.getString("answer_division").split("\\|"); topperCompareBeanObj.setTitle("Topper"); //rs4.getString("sname") topperCompareBeanObj.setScore(rs4.getDouble("marks_obtain")); topperCompareBeanObj.setScorePercentage(Math.round((topperCompareBeanObj.getScore()*100)/analyticsResponseBeanObj.getFullMarks())); topperCompareBeanObj.setCorrectQuestions(Integer.parseInt(answerDivision[0])); topperCompareBeanObj.setIncorrectQuestions(Integer.parseInt(answerDivision[1])); topperCompareBeanObj.setUnattemptedQuestions(Integer.parseInt(answerDivision[2])); timeSpentSeconds = rs4.getInt("time_spent"); if(timeSpentSeconds!=null && timeSpentSeconds>=60) { topperCompareBeanObj.setTimeSpent(Math.round(Math.floor(timeSpentSeconds/60))+":"+(timeSpentSeconds%60)); }else if(timeSpentSeconds!=null && timeSpentSeconds>0) { topperCompareBeanObj.setTimeSpent("00:"+timeSpentSeconds); }else { topperCompareBeanObj.setTimeSpent("00:00"); } topperCompareBeanList.add(topperCompareBeanObj); } rs4.close(); ps4.close(); PreparedStatement ps5 = this.conn.prepareStatement("SELECT ROUND(score/num_row) avg_score,ROUND(timeSpent/num_row) avg_time,ROUND(num_correct/num_row) avg_correct,ROUND(num_incorrect/num_row) avg_incorrect,(num_not_answer/num_row) avg_noans FROM (select IFNULL(SUM(d.marks),0) score,IFNULL(SUM(d.time_spent),0) timeSpent,IFNULL(SUM(CASE WHEN d.status='Correct' THEN 1 END),0) num_correct,IFNULL(SUM(CASE WHEN d.status='Incorrect' THEN 1 END),0) num_incorrect,IFNULL(SUM(CASE WHEN d.status IS NULL OR status='NotAnswer' OR status='MarkForReview' THEN 1 END),0) num_not_answer,(SELECT count(*) FROM "+MysqlTableNames.getPgprepTestSummary()+" WHERE test_root_id=s.test_root_id AND test_node_id=s.test_node_id) num_row FROM "+MysqlTableNames.getPgprepTestDetails()+" d, "+MysqlTableNames.getPgprepTestSummary()+" s WHERE d.student_id=s.student_id AND d.test_session=s.test_session AND s.test_root_id=? AND s.test_node_id=? AND s.test_status=1) a"); ps5.setInt(1, rs.getInt("test_root_id")); ps5.setInt(2, rs.getInt("test_node_id")); logger.info("THIRTEEN topper data query: " + ps5); ResultSet rs5 = ps5.executeQuery(); if(rs5.next()) { topperCompareBeanObj = new TopperCompareBean(); topperCompareBeanObj.setTitle("Avg"); topperCompareBeanObj.setScore(rs5.getDouble("avg_score")); topperCompareBeanObj.setScorePercentage(Math.round((topperCompareBeanObj.getScore()*100)/analyticsResponseBeanObj.getFullMarks())); topperCompareBeanObj.setCorrectQuestions(rs5.getInt("avg_correct")); topperCompareBeanObj.setIncorrectQuestions(rs5.getInt("avg_incorrect")); topperCompareBeanObj.setUnattemptedQuestions(rs5.getInt("avg_noans")); timeSpentSeconds = rs5.getInt("avg_time"); if(timeSpentSeconds!=null && timeSpentSeconds>=60) { topperCompareBeanObj.setTimeSpent(Math.round(Math.floor(timeSpentSeconds/60))+":"+(timeSpentSeconds%60)); }else if(timeSpentSeconds!=null && timeSpentSeconds>0) { topperCompareBeanObj.setTimeSpent("00:"+timeSpentSeconds); }else { topperCompareBeanObj.setTimeSpent("00:00"); } topperCompareBeanList.add(topperCompareBeanObj); } rs5.close(); ps5.close(); } } rs.close(); ps.close(); conn.close(); } List sectionPerformanceList = new ArrayList(); SectionPerformanceBean sectionPerformanceBeanObj = null; sectionPerformanceList.add(new SectionPerformanceBean()); sectionPerformanceBeanObj = new SectionPerformanceBean(); sectionPerformanceBeanObj.setName("Full Marks"); sectionPerformanceBeanObj.setColor("#5fecb3"); sectionPerformanceBeanObj.setData(fullMarksList); sectionPerformanceList.add(sectionPerformanceBeanObj); sectionPerformanceBeanObj = new SectionPerformanceBean(); sectionPerformanceBeanObj.setName("Obtained Marks"); sectionPerformanceBeanObj.setColor("#fe7072"); sectionPerformanceBeanObj.setData(obtainedMarksList); sectionPerformanceList.add(sectionPerformanceBeanObj); analyticsResponseBeanObj.setSectionPerformanceList(sectionPerformanceList); analyticsResponseBeanObj.setSectionList(sectionList); analyticsResponseBeanObj.setTopperCompare(topperCompareBeanList); analyticsResponseBeanObj.setSectionSet(sectionSetList); dataList.add(analyticsResponseBeanObj); }catch(Exception e) { e.getStackTrace(); } ///logger.info( "dataList >>> " + dataList ); return dataList; } private String convertSecondsToTimeLabel(Integer time) { String hourStr="", minsStr="", secsStr=""; Integer hours = time/3600; if(hours<10){ hourStr="0"+hours; }else { hourStr= String.valueOf(hours); } Integer mins = time % 3600 / 60; if(mins<10){ minsStr="0"+mins; }else { minsStr= String.valueOf(mins); } Integer secs = time % 3600 % 60; if(secs<10){ secsStr="0"+secs; }else { secsStr= String.valueOf(secs); } return hourStr+':'+minsStr+':'+secsStr; } private List getSubTopicTreeIds(List topicList, String topicName) { ///logger.info("getSubTopicTreeIds: "+topicName); try { if(topicName!=null && topicName.length()>0) { PreparedStatement psMMM = conn.prepareStatement("SELECT childnode_id, childnode_name FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE parentnode_name=? "); psMMM.setString(1, topicName); ///logger.info("getSubTopicTreeIds : "+psMMM); ResultSet rsMMM = psMMM.executeQuery(); while(rsMMM.next()) { if(Integer.valueOf(rsMMM.getInt("childnode_id"))>0) { if(topicList.indexOf(Integer.valueOf(rsMMM.getInt("childnode_id")))==-1) { topicList.add(Integer.valueOf(rsMMM.getInt("childnode_id"))); } } PreparedStatement psNNN = conn.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE parentnode_name=? LIMIT 1 "); psNNN.setString(1, rsMMM.getString("childnode_name")); ///logger.info("getSubTopicTreeIds: "+psNNN); ResultSet rsNNN = psNNN.executeQuery(); if(rsNNN.next()) { //**************************************************************** this.getSubTopicTreeIds(topicList, rsMMM.getString("childnode_name")); //**************************************************************** } // close if(myObj rsNNN.close(); psNNN.close(); } // close while(iterator rsMMM.close(); psMMM.close(); } }catch(Exception e) { e.printStackTrace(); } return topicList; } // makeTree Method close private void getDrillDownTree(String childNodeName , SectionSetBean topicBeanChainObj, String testSession, String sectionName, String studentId) { try { logger.info("XXXXX addChildHierarchy childNodeName: " + childNodeName); logger.info("XXXXX addChildHierarchy testSession: " + testSession); logger.info("XXXXX addChildHierarchy Section Name: " + sectionName); logger.info("XXXXX addChildHierarchy studentId: " + studentId); Double percentVal = 0.0; String str = null; String whereSql = ""; List childList = new ArrayList(); PreparedStatement psChildnode = conn.prepareStatement("SELECT childnode_id,childnode_name FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE parentnode_name=? "); psChildnode.setString(1, childNodeName); logger.info("XXXXX addChildHierarchy One Query: " + psChildnode); ResultSet myChildnode = psChildnode.executeQuery(); while (myChildnode.next()) { List myTopicChildrenList = new ArrayList(); myTopicChildrenList.add(myChildnode.getInt("childnode_id")); List topicChildrenList = this.getSubTopicTreeIds(myTopicChildrenList, myChildnode.getString("childnode_name")); logger.info("XXXXX addChildHierarchy childnode_id: " + myChildnode.getInt("childnode_id") ); logger.info("XXXXX addChildHierarchy childnode_name: " + myChildnode.getString("childnode_name") ); logger.info("XXXXX addChildHierarchy topicChildrenList: " + topicChildrenList ); if(topicChildrenList!=null && topicChildrenList.size()>0) { if(sectionName!=null && sectionName.length()>0) { whereSql=" and section_name=? "; } PreparedStatement psMyTD = conn.prepareStatement("SELECT count(id) totalQuestion, ROUND(SUM(total_marks),2) totalScore, ROUND(SUM(marks),2) obtainMarks, IFNULL(SUM(CASE WHEN status='Correct' THEN 1 END),0) num_correct,IFNULL(SUM(CASE WHEN status='Incorrect' THEN 1 END),0) num_incorrect,IFNULL(SUM(CASE WHEN status='NotAnswer' THEN 1 END),0) num_not_answer,IFNULL(SUM(time_spent),0) timeSpent FROM "+MysqlTableNames.getPgprepTestDetails()+" WHERE test_session=? and student_id=? "+whereSql+" and topic_id IN ("+topicChildrenList.toString().substring(1,topicChildrenList.toString().length()-1)+")"); psMyTD.setString(1, testSession); psMyTD.setString(2, studentId); if(sectionName!=null && sectionName.length()>0) { psMyTD.setString(3, sectionName); } logger.info("MyTestDetail Query: " + psMyTD); ResultSet rsMyTD = psMyTD.executeQuery(); if(rsMyTD.next()) { if(rsMyTD.getInt("totalQuestion")>0) { logger.info("A addChildHierarchy topic title: " + myChildnode.getString("childnode_name") ); logger.info("B addChildHierarchy total Question: " + rsMyTD.getInt("totalQuestion") ); SectionSetBean sectionSetBeanOneObj = new SectionSetBean(); sectionSetBeanOneObj.setSectionName(myChildnode.getString("childnode_name")); sectionSetBeanOneObj.setTotalQuestion(rsMyTD.getInt("totalQuestion")); sectionSetBeanOneObj.setSectionTotalScore(rsMyTD.getDouble("totalScore")); sectionSetBeanOneObj.setCorrectQuestions(rsMyTD.getInt("num_correct")); sectionSetBeanOneObj.setIncorrectQuestions(rsMyTD.getInt("num_incorrect")); sectionSetBeanOneObj.setUnattemptedQuestions(rsMyTD.getInt("num_not_answer")); sectionSetBeanOneObj.setSectionScore(rsMyTD.getDouble("obtainMarks")); percentVal = (sectionSetBeanOneObj.getSectionScore()*100)/sectionSetBeanOneObj.getSectionTotalScore(); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanOneObj.getCorrectQuestions()*100)/sectionSetBeanOneObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setCorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanOneObj.getIncorrectQuestions()*100)/sectionSetBeanOneObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setIncorrectQuestionsPercentage(Double.valueOf(str)); percentVal = Double.valueOf((sectionSetBeanOneObj.getUnattemptedQuestions()*100)/sectionSetBeanOneObj.getTotalQuestion()); str = String.format("%1.2f", percentVal); sectionSetBeanOneObj.setUnattemptedQuestionsPercentage(Double.valueOf(str)); Integer timeSpentSeconds = rsMyTD.getInt("timeSpent"); if(timeSpentSeconds!=null && timeSpentSeconds>=60) { sectionSetBeanOneObj.setTimeSpent(Math.round(Math.floor(timeSpentSeconds/60))+":"+(timeSpentSeconds%60)); }else if(timeSpentSeconds!=null && timeSpentSeconds>0) { sectionSetBeanOneObj.setTimeSpent("00:"+timeSpentSeconds); }else { sectionSetBeanOneObj.setTimeSpent("00:00"); } //************************************************************** childList.add(sectionSetBeanOneObj); //********************************************************* PreparedStatement psTID = conn.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE parentnode_name=? LIMIT 1 "); psTID.setString(1, myChildnode.getString("childnode_name")); logger.info("addChildHierarchy Two Query: " + psTID); ResultSet rsTID = psTID.executeQuery(); if(rsTID.next()){ this.getDrillDownTree(myChildnode.getString("childnode_name"), sectionSetBeanOneObj, testSession, sectionName, studentId); } rsTID.close(); psTID.close(); } } rsMyTD.close(); psMyTD.close(); } } // close while(iterator myChildnode.close(); psChildnode.close(); topicBeanChainObj.setChildren(childList); }catch(Exception e) { e.getStackTrace(); } } // makeTree Method close }