package com.bizofficer.apiweb.testdetail;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.TypedQuery;
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.hibernate.entity.AssessmentTreeview;
import com.bizofficer.hibernate.entity.OrganisationTemplateAssociation;
import com.bizofficer.hibernate.entity.PgprepCacheTemplateTreeview;
import com.bizofficer.hibernate.entity.QbGroupedItem;
import com.bizofficer.hibernate.repository.PgprepTestSummaryRepository;
import com.bizofficer.module.questioncd.QuestionMapperBeanCD;
import com.bizofficer.module.questioncd.XmlToJsonCD;
import com.bizofficer.pojo.response.ListResponsePojo;
import com.bizofficer.util.module.BeanQuestion;
import com.bizofficer.util.module.MysqlTableNames;
import com.bizofficer.util.module.QuestionHtml;
import com.bizofficer.util.system.General;



@Service
public class SelfTestAPIService{
	
	private static final Logger logger = Logger.getLogger(SelfTestAPIService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	@Autowired
	PgprepTestSummaryRepository pgprepTestSummaryRepoObj; 
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
    
	public Object doExecute(Object obj) throws NotFoundException{
		BeanTesting objBean = (BeanTesting) obj; 	
		ListResponsePojo responseObj = new ListResponsePojo();
		
		try {
		
			responseObj.setResponseTxt("failed");
			
			if(objBean.getStudentId()==null || objBean.getOrganisationId()==null) {
				return responseObj;
			}
			
			logger.info("*****************************************************************************");
			logger.info("SELF TEST QUESTION START");
			logger.info("*****************************************************************************");
			
			logger.info("Question Type: " + objBean.getQuestionType());
			logger.info("Student_Id: " + objBean.getStudentId());
			logger.info("Organisation_Id: " + objBean.getOrganisationId());
			
			logger.info("SearchFlag: " + objBean.getSearchFlag());
			logger.info("SearchQuery: " + objBean.getSearchQuery());
			
			List<TestingPageBean> dataList = new ArrayList<TestingPageBean>();
			
			Integer maxQuestions = 30;
			/// CHECK TEST PARAMETERS ******************************************
			if(objBean.getOrganisationId()!=null && objBean.getStudentId()!=null && objBean.getStudentId().length()>0) {
				conn = appDataSource.getConnection();
	            if(conn != null){
	            	String sqlPPP = "SELECT parameter_id FROM "+MysqlTableNames.getOrganisationParameterAssociation()+" WHERE organisation_id=? LIMIT 1 ";
	            	PreparedStatement psPPP = conn.prepareStatement(sqlPPP);
                    psPPP.setString(1, String.valueOf(objBean.getOrganisationId()));
                    logger.info(this.getClass().getName()+": " + psPPP);
                    ResultSet rsPP = psPPP.executeQuery();
                    if (rsPP.next()) {                    	
                    	String sqlQQQ = "SELECT max_questions,max_test,min_time,max_time FROM "+MysqlTableNames.getSelftestParameterManagement()+" WHERE max_test>0 AND id1=? LIMIT 1 ";
    	            	PreparedStatement psQQQ = conn.prepareStatement(sqlQQQ);
    	            	psQQQ.setInt(1, Integer.valueOf(rsPP.getString("parameter_id")));
                        logger.info(this.getClass().getName()+": " + psQQQ);
                        ResultSet rsQQQ = psQQQ.executeQuery();
                        if (rsQQQ.next()) {
                        	Integer totalTestGivenToday = pgprepTestSummaryRepoObj.countTestGivenToday(objBean.getStudentId(),1,objBean.getOrganisationId());
                        	logger.info("totalTestGivenToday: " + totalTestGivenToday);
                        	if(totalTestGivenToday>0 && rsQQQ.getInt("max_test")<=totalTestGivenToday) {
	                        	TestingPageBean testingPageBeanObj = new TestingPageBean();
								testingPageBeanObj.setResponseTxt("Self Assessor limit has been exceeded for today.");
								
//								String sqlOrg = "SELECT organisation_name FROM "+MysqlTableNames.getOrganisationInfo()+" WHERE organisation_id=? LIMIT 1 ";
//            	            	PreparedStatement psOrg = conn.prepareStatement(sqlOrg);
//            	            	psOrg.setString(1, String.valueOf(objBean.getOrganisationId()));
//                              ResultSet rsOrg = psOrg.executeQuery();
//                              if (rsOrg.next()) {
//                              	if(rsOrg.getString("organisation_name").indexOf("FREE")>-1) {
//                                		testingPageBeanObj.setResponseTxt("Self Assessor limit has been exceeded. You are registered as free account. Contact us to upgrade your account to increase test limit.");
//                                	}
//                              }	
								
								dataList.add(testingPageBeanObj);
								
								responseObj.setList(dataList);
								
								return responseObj;
								
                        	}
                        	
                        	maxQuestions = rsQQQ.getInt("max_questions");
                        	
                        	logger.info("Self Test min_time: " + rsQQQ.getInt("min_time") );
                        	logger.info("Self Test max_time: " + rsQQQ.getInt("max_time") );
                        	
                        	if(objBean.getTotalTime()!=null && rsQQQ.getInt("min_time")>0 && objBean.getTotalTime()<rsQQQ.getInt("min_time")) {
                        		objBean.setTotalTime(rsQQQ.getInt("min_time"));
                        	}else if(objBean.getTotalTime()!=null && rsQQQ.getInt("max_time")>0 && objBean.getTotalTime()>rsQQQ.getInt("max_time")) {
                        		objBean.setTotalTime(rsQQQ.getInt("max_time"));
                        	}else if(objBean.getTotalTime()==null) {
                        		objBean.setTotalTime(rsQQQ.getInt("max_time"));
                        	}
                        	
                        }
                        psQQQ.close();
                        rsQQQ.close();
					}
                    psPPP.close();
                    rsPP.close();
	            }
	            conn.close();
			}
			
			logger.info("Self Test Total Time: " + objBean.getTotalTime());
			
			List<QuestionNumbersBean> questionNumbersList = new ArrayList<QuestionNumbersBean>();
			List<SectionSetBean> sectionSetList = new ArrayList<SectionSetBean>();
			SectionSetBean sectionSetBeanObj = new SectionSetBean();
			List<QuestionSetBean> questionSetList = new ArrayList<QuestionSetBean>();
			
			if(objBean.getSearchQuery()==null || objBean.getSearchFlag()==null) {

				responseObj.setList(dataList);
				
				return responseObj;
			}
			
			if(objBean.getTotalQuestion()==null || (objBean.getTotalQuestion()!=null && objBean.getTotalQuestion()>maxQuestions)) {
				objBean.setTotalQuestion(maxQuestions);
			}
			
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
			
			General gen = new General();
			
			TestingPageBean testingPageBeanObj = new TestingPageBean();
			
			int questionIndex = 0;
			Integer countTotalQuestion = objBean.getTotalQuestion();
			
				testingPageBeanObj.setTestId(gen.getRandomNumber(999, 999999)+gen.getRandomNumber(999, 999999));
				testingPageBeanObj.setTestName(objBean.getSearchQuery());	
				testingPageBeanObj.setTestType(1);
				testingPageBeanObj.setModelTestId(0);
				testingPageBeanObj.setSectionLock("No_Lock");
				testingPageBeanObj.setLastQuestionIndex(0);
				testingPageBeanObj.setTotalTime(objBean.getTotalTime());
				testingPageBeanObj.setRemainingTime(objBean.getTotalTime()*60);
				sectionSetBeanObj = new SectionSetBean();
				
				String whereQry="";
				if(objBean.getSearchFlag()!=null && "Topic".equals(objBean.getSearchFlag()) && objBean.getSearchQuery()!=null && objBean.getSearchQuery().length()>0 ) {
					
					logger.info("*************************************************************");
					logger.info( "SearchQuery Topic: "+objBean.getSearchQuery());
					logger.info("*************************************************************");

					TypedQuery<AssessmentTreeview> query = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where childnodeName=:childnodeName ORDER BY childnodeId ", AssessmentTreeview.class);
					query.setParameter("childnodeName", objBean.getSearchQuery());				
					query.setFirstResult(0);
					query.setMaxResults(1);				
					List<?> resultList = query.getResultList();
					Iterator<?> iterator=resultList.iterator();
					if(iterator.hasNext()){
						AssessmentTreeview myObj = (AssessmentTreeview)iterator.next();				

						logger.info("AssessmentTreeview SearchQuery ChildnodeId: "+myObj.getChildnodeId());
						
						String subTopicIdCache = this.getSubTopicIdFromCache(myObj.getChildnodeId(), objBean.getOrganisationId());
						if(subTopicIdCache!=null && subTopicIdCache.length()>0 ) {
							logger.info("Template Cache Topics");
							whereQry+=" AND t.qb_id IN ("+subTopicIdCache+") ";
						}else {
							List<Integer> templateTopics = this.getTopicIds(myObj.getChildnodeId(),objBean.getOrganisationId());
							if(templateTopics.size()>0) {
								logger.info( "Template Topics: " +   templateTopics.toString() );
								whereQry+=" AND t.qb_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
							}
						}	

					}

				}else if(objBean.getTopicId()!=null && objBean.getTopicId()>0) {
					
					logger.info("*************************************************************");
					logger.info( "Topic Id: "+objBean.getTopicId());
					logger.info("*************************************************************");
					String subTopicIdCache = this.getSubTopicIdFromCache(objBean.getTopicId(), objBean.getOrganisationId());
					if(subTopicIdCache!=null && subTopicIdCache.length()>0 ) {
						logger.info("Template Cache Topics");
						whereQry+=" AND t.qb_id IN ("+subTopicIdCache+") ";
					}else {
						List<Integer> templateTopics = this.getTopicIds(objBean.getTopicId(),objBean.getOrganisationId());
						if(templateTopics.size()>0) {
							logger.info( "Template Topics: " +   templateTopics.toString() );
							whereQry+=" AND t.qb_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
						}
					}					
				 
				}else if(objBean.getSearchFlag()!=null && "Keyword".equals(objBean.getSearchFlag()) && objBean.getSearchQuery()!=null && objBean.getSearchQuery().length()>0 ) {
//					conn = appDataSource.getConnection();
//		            if(conn != null){
//		            	List<Integer> templateTopics = new ArrayList<Integer>();
//						String sql = "SELECT q.assess_id,t.qb_id FROM "+MysqlTableNames.getQbAssessmentItem()+" q, "+MysqlTableNames.getQbItems()+" t, "+MysqlTableNames.getAssessmentItemTopicAssociation()+" k WHERE q.assess_id=t.item_id AND q.assess_id=k.assess_id AND k.assess_topic=? AND q.exam_type='objective' AND (assess_title='Multiple Choice' OR assess_title='Multiple Choice (5)')   ORDER BY RAND() LIMIT " + objBean.getTotalQuestion();
//	                    ps = conn.prepareStatement(sql);
//	                    ps.setString(1, objBean.getSearchQuery());
//	                    logger.info("quesions query: " + ps);
//	                    rs = ps.executeQuery();
//	                    while (rs.next()) {
//	                    	templateTopics.add(rs.getInt("qb_id"));
//	                    }
//	                    rs.close();
//	                    ps.close();
//	                    conn.close();
//						if(templateTopics.size()>0) {
//							whereQry+=" AND t.qb_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
//						}
//		            }
					
					///whereQry+=" AND assess_topic=? ";
					
					whereQry+=" AND q.assess_id IN (SELECT aa.assess_id FROM "+MysqlTableNames.getAssessmentItemTopicAssociation()+" aa WHERE LENGTH(aa.assess_topic)>0 AND aa.assess_topic=?) ";
					
				}
				
				logger.info( "AAA Self Test Topic: " +   objBean.getSearchQuery() );
				
				if(whereQry.length()<10) {
					
					responseObj.setList(dataList);
					
					return responseObj;
				}
				
				logger.info("Question Type: " + objBean.getQuestionType());
				
				String questionTypeSql="";			
				if(objBean.getQuestionType()!=null && !"Any".equalsIgnoreCase(objBean.getQuestionType()) ) {
					questionTypeSql=" and q.assess_title=? ";
				}
				
				conn = appDataSource.getConnection();
	            if(conn != null){	
				String sqlAA = "SELECT q.assess_id,t.qb_id,q.assess_title,q.assess_text,q.display_id FROM "+MysqlTableNames.getQbAssessmentItem()+" q, "+MysqlTableNames.getQbItems()+" t WHERE q.assess_id=t.item_id "+whereQry+" "+questionTypeSql+" AND q.exam_type='objective'    ORDER BY RAND() LIMIT " + objBean.getTotalQuestion();
				PreparedStatement psAA = conn.prepareStatement(sqlAA);	
				int i = 1;
				if(objBean.getQuestionType()!=null && !"Any".equalsIgnoreCase(objBean.getQuestionType()) ) {
					psAA.setString(i++, objBean.getQuestionType() );					
				}
				if(objBean.getSearchFlag()!=null && "Keyword".equals(objBean.getSearchFlag()) && objBean.getSearchQuery()!=null && objBean.getSearchQuery().length()>0 ) {
					psAA.setString(i++, objBean.getSearchQuery() );					
				}
				
				logger.info("Quesions query: " + psAA);
				ResultSet rsAA = psAA.executeQuery();
                while (rsAA.next()) {
					
					logger.info("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
					
						logger.info("QbAssessmentItem Assess Id: "+rsAA.getInt("assess_id"));
						
						//*******************************************************************************
						//	COMMON DATA QUESTION TYPE
						//*******************************************************************************
						if( countTotalQuestion>0 && ("Common Data".equals(rsAA.getString("assess_title")) || "Common Data (5)".equals(rsAA.getString("assess_title")))  ) {

							QuestionMapperBeanCD questionObj = (QuestionMapperBeanCD) new XmlToJsonCD().parse(rsAA.getString("assess_text"));
							
								TypedQuery<QbGroupedItem> queryGBGI = (TypedQuery<QbGroupedItem>) entityManager.createQuery("from "+QbGroupedItem.class.getName()+" where assessId="+rsAA.getInt("assess_id"), QbGroupedItem.class);
								queryGBGI.setFirstResult(0);
								queryGBGI.setMaxResults(1);
								List<?> resultListGBGI = queryGBGI.getResultList();
								Iterator<?> iteratorGBGI = resultListGBGI.iterator();
								if(iteratorGBGI.hasNext()){
									QbGroupedItem myObjGBGI = (QbGroupedItem)iteratorGBGI.next();
									///logger.info("Item ID >> " + myObjGBGI.getItemId()  );
									
									//*******************************************************************************
									//	QUESTION SET START
									//*******************************************************************************
									QuestionSetBean questionSetBeanObj = new QuestionSetBean();
									
									logger.info("Common Data Assess Id: "+myObjGBGI.getItemId()*10000);
									logger.info("Common Data Assess Title: "+myObjGBGI.getItemTitle());
									logger.info(" PASSING TO PARSER ");
									logger.info("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");						
									
									BeanQuestion bqObj = new BeanQuestion();
									bqObj.setQuestionId(myObjGBGI.getItemId()*10000);
									bqObj.setItemId(myObjGBGI.getItemId());
									bqObj.setQuestionDisplayId(String.valueOf(myObjGBGI.getDisplayId()));
									bqObj.setQuestionType(myObjGBGI.getItemTitle());
									bqObj.setQuestionTypeLabel(myObjGBGI.getItemTitle());
									bqObj.setQuestionBody(myObjGBGI.getItemText());
									bqObj.setCommonDataDescription(questionObj.getItem().getDescription());
									////bqObj.setCommonDataDescription("Consider an ionic solid that dissolves in water according");

									questionSetBeanObj = new QuestionHtml().getXmlParser(bqObj,false);
									
									questionSetBeanObj.setTopicId(String.valueOf(rsAA.getInt("qb_id")));
									questionSetBeanObj.setTimeSpent(0);
									questionSetBeanObj.setCssName("qbg-not-visited");
									questionSetBeanObj.setBackgroundImg("url('/not-visited.png')");
									
									questionSetBeanObj.setCorrectAnswerMarks(objBean.getMarksCorrectAnswer());
									questionSetBeanObj.setIncorrectAnswerMarks(objBean.getMarksIncorrectAnswer());
									questionSetBeanObj.setNotAnswerMarks(objBean.getMarksNotAttempted());
									
									QuestionNumbersBean questionNumbersBeanObj = new QuestionNumbersBean();
									questionNumbersBeanObj.setQuestionIndex(questionIndex);
									questionNumbersBeanObj.setQuestionId(myObjGBGI.getItemId()*10000);
									questionNumbersList.add(questionNumbersBeanObj);
									
									questionSetList.add(questionSetBeanObj);
									
									logger.info("questionIndex: "+questionIndex);
									
									questionIndex++;
									
									//*******************************************************************************
									//	QUESTION SET END
									//*******************************************************************************

									countTotalQuestion--;
									
								}
								
						//*******************************************************************************
						//	OTHER QUESTION TYPES THAN COMMON DATA
						//*******************************************************************************
						}else if(countTotalQuestion>0) {							
							
							
							//*******************************************************************************
							//	QUESTION SET START
							//*******************************************************************************
							QuestionSetBean questionSetBeanObj = new QuestionSetBean();
							
							logger.info("QbGroupedItem Assess Id: "+rsAA.getInt("assess_id"));
							logger.info("QbGroupedItem Assess Title: "+rsAA.getString("assess_title"));
							logger.info(" PASSING TO PARSER ");
							logger.info("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");						
							
							BeanQuestion bqObj = new BeanQuestion();
							bqObj.setQuestionId(rsAA.getInt("assess_id"));
							bqObj.setItemId(null); 
							bqObj.setQuestionDisplayId(String.valueOf(rsAA.getInt("display_id")));
							bqObj.setQuestionType(rsAA.getString("assess_title"));
							bqObj.setQuestionTypeLabel(rsAA.getString("assess_title"));
							bqObj.setQuestionBody(rsAA.getString("assess_text"));
							bqObj.setCommonDataDescription(null);

							questionSetBeanObj = new QuestionHtml().getXmlParser(bqObj,false);
							
							questionSetBeanObj.setTopicId(String.valueOf(rsAA.getInt("qb_id")));
							questionSetBeanObj.setTimeSpent(0);
							questionSetBeanObj.setCssName("qbg-not-visited");
							questionSetBeanObj.setBackgroundImg("url('/not-visited.png')");
							
							questionSetBeanObj.setCorrectAnswerMarks(objBean.getMarksCorrectAnswer());
							questionSetBeanObj.setIncorrectAnswerMarks(objBean.getMarksIncorrectAnswer());
							questionSetBeanObj.setNotAnswerMarks(objBean.getMarksNotAttempted());
							
							QuestionNumbersBean questionNumbersBeanObj = new QuestionNumbersBean();
							questionNumbersBeanObj.setQuestionIndex(questionIndex);
							questionNumbersBeanObj.setQuestionId(rsAA.getInt("assess_id"));
							questionNumbersList.add(questionNumbersBeanObj);
							
							questionSetList.add(questionSetBeanObj);
							
							logger.info("questionIndex: "+questionIndex);
							
							questionIndex++;							
							//*******************************************************************************
							//	QUESTION SET END
							//*******************************************************************************
						
							countTotalQuestion--;
							
						} /// ELSE CLOSE OF OTHER QUESITION TYPES
						
						
				}	
	            
                rsAA.close();
                psAA.close();
                conn.close();
                
	            }
				sectionSetBeanObj.setQuestionNumbers(questionNumbersList);
				sectionSetList.add(sectionSetBeanObj);
				
			
			testingPageBeanObj.setSectionSet(sectionSetList);
			testingPageBeanObj.setQuestionSet(questionSetList);
			dataList.add(testingPageBeanObj);
			
			entityManager.getTransaction().commit();
			entityManager.close();
			
			logger.info("**************************************************************************");
			
			if(sectionSetList!=null) {
				logger.info("sectionSetList Count: "+sectionSetList.size());
			}
			
			if(questionSetList!=null) {
				logger.info("questionSetList Count: "+questionSetList.size());
			}
			
			logger.info("TEST API CREATED");
			
			logger.info("*****************************************************************************");
			logger.info("SELF TEST QUESTION END");
			logger.info("*****************************************************************************");
			
			responseObj.setList(dataList);
			
			responseObj.setResponseTxt("success");
            
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		////logger.info( "dataList >>> " +  dataList );
			
		return responseObj;
	}
	
	
//	public List<Integer> getTopicIds(Integer topicId)
//    {
//        List<Integer> topicList = new ArrayList<Integer>();
//        
//        try{
//
//        	conn = appDataSource.getConnection();
//            if(conn != null)
//            {
//	        	PreparedStatement psFT = conn.prepareStatement("SELECT path FROM "+MysqlTableNames.getParentTopicHierarchy()+" WHERE topic_id=?");
//	        	psFT.setString(1, String.valueOf(topicId));
//	        	logger.info("getTopicIds query: "+psFT);
//	            ResultSet rsFT = psFT.executeQuery();
//	            if(rsFT.next())
//	            {
//	        	
//	            	PreparedStatement psTID = conn.prepareStatement("SELECT topic_id FROM "+MysqlTableNames.getParentTopicHierarchy()+" WHERE path like '"+rsFT.getString("path")+".%' ");
//	                logger.info("getTopicIds query: "+psTID);
//	                ResultSet rsTID = psTID.executeQuery();
//	                while(rsTID.next())
//	                {
//	                	if(Integer.valueOf(rsTID.getString("topic_id"))>0) {
//	    					if(topicList.indexOf(Integer.valueOf(rsTID.getString("topic_id")))==-1) {
//	    						topicList.add(Integer.valueOf(rsTID.getString("topic_id")));
//	    						
//	    					}
//	    				}
//	                }
//	                rsTID.close();
//	                psTID.close();
//	
//	                logger.info( "getTopicIds Method Return >>>> " + topicList);
//	            }
//	            rsFT.close();
//	            psFT.close();
//	            conn.close();
//            }    
//            
//  	    }catch(Exception ex){
//    	   ex.printStackTrace();
//   	    }
//
//        
//        return topicList;
//        
//  }
	
	public String getSubTopicIdFromCache(Integer topicId, Integer organisationId)
    {
		
		logger.info("***********************************");
		logger.info("OrganisationId ========>>>>>>> "+organisationId);
		logger.info("***********************************");
		
        String downline = "";
        
        try{

				EntityManager entityManager = entityManagerFactory.createEntityManager();
				entityManager.getTransaction().begin();

        		Integer templateId = 0;
	        	if(organisationId!=null && organisationId>0) {
	    			TypedQuery<OrganisationTemplateAssociation> query = (TypedQuery<OrganisationTemplateAssociation>) entityManager.createQuery("from "+OrganisationTemplateAssociation.class.getName()+" where organisationId=:organisationId ", OrganisationTemplateAssociation.class);
	    			query.setParameter("organisationId", String.valueOf(organisationId));
	    			query.setFirstResult(0);
	    			query.setMaxResults(1);
	    			List<?> resultList = query.getResultList();
	    			Iterator<?> iterator=resultList.iterator();
	    			if(iterator.hasNext()){
	    				OrganisationTemplateAssociation myObj = (OrganisationTemplateAssociation)iterator.next();			
	    				templateId = myObj.getTemplateId();
	    			}
	        	}
        	
        	
			TypedQuery<PgprepCacheTemplateTreeview> query = (TypedQuery<PgprepCacheTemplateTreeview>) entityManager.createQuery("from "+PgprepCacheTemplateTreeview.class.getName()+" where templateId=:templateId AND childnodeId=:childnodeId ", PgprepCacheTemplateTreeview.class);
			query.setParameter("templateId", templateId);
			query.setParameter("childnodeId", topicId);
			query.setFirstResult(0);
			query.setMaxResults(1);
			List<?> resultList = query.getResultList();
			Iterator<?> iterator=resultList.iterator();
			if(iterator.hasNext()){
				PgprepCacheTemplateTreeview myObj = (PgprepCacheTemplateTreeview)iterator.next();			
				downline = myObj.getDownline();
			}
			
			entityManager.getTransaction().commit();
			entityManager.close();
        	
        }catch(Exception ex){
     	   ex.printStackTrace();
   	 	}
        
        return downline;
   }    


	public List<Integer> getTopicIds(Integer topicId, Integer organisationId)
    {
        List<Integer> topicList = new ArrayList<Integer>();
        
        try{
        	
        	logger.info( "SelfTestService getTopicId OKKKK >> " + topicId );
        	
			Connection connGGG = appDataSource.getConnection();
            if(connGGG != null)
            {
            	
            	String topicName="";
            	Integer templateId = 0;
            	if(topicId!=null && topicId>0) {
            		topicList.add(topicId);
                	PreparedStatement psHHH = connGGG.prepareStatement("SELECT childnode_id,childnode_name FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND childnode_id=? LIMIT 0,1 ");
                	psHHH.setInt(1, topicId);
                    logger.info("getTopicIds: "+psHHH);
                    ResultSet rsHHH = psHHH.executeQuery();
                    if(rsHHH.next())
                    {
                    	topicName = rsHHH.getString("childnode_name");
                    }
                    rsHHH.close();
                    rsHHH.close();
            	}
            	
            	if(organisationId!=null && organisationId>0) {
            		PreparedStatement psOTA = connGGG.prepareStatement("SELECT template_id FROM "+MysqlTableNames.getOrganisationTemplateAssociation()+" WHERE organisation_id=? LIMIT 0,1 ");
            		psOTA.setInt(1, organisationId);
                    logger.info("getTopicIds: "+psOTA);
                    ResultSet rsOTA = psOTA.executeQuery();
                    if(rsOTA.next())
                    {
                    	templateId = rsOTA.getInt("template_id");
                    }	
                    rsOTA.close();
                    rsOTA.close();
            	}
            	
            	if(templateId>0 && topicName!=null && topicName.length()>0) {
					PreparedStatement psGGG = connGGG.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=?  " );
					psGGG.setInt(1, templateId);
					psGGG.setString(2, topicName);
	                logger.info("getTopicIds: "+psGGG);
	                ResultSet rsGGG = psGGG.executeQuery();
	                while(rsGGG.next())
	                {
	                	if(rsGGG.getInt("childnode_id")>0 && rsGGG.getString("childnode_name").length()>0) {
	    					if(topicList.indexOf(rsGGG.getInt("childnode_id"))==-1) {
	    						topicList.add(rsGGG.getInt("childnode_id"));
	
	    						PreparedStatement psKKK = connGGG.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=? LIMIT 0,1  " );
	    		            	psKKK.setInt(1, templateId);
	    		            	psKKK.setString(2, rsGGG.getString("childnode_name"));
	    		                logger.info("getTopicIds: "+psKKK);
	    		                ResultSet rsKKK = psKKK.executeQuery();
	    		                if(rsKKK.next()) {
	    							//****************************************************************
	    							topicList = this.subTopicsList(connGGG, topicList, rsGGG.getString("childnode_name"), templateId);
	    							//****************************************************************
	    						} // close if(myObj
	    						rsKKK.close();
	    						psKKK.close();
	    					}
	    				}
	                }
	                rsGGG.close();
	                psGGG.close();
            	}
                
            }
        	connGGG.close();
        	
			logger.info( "getTopicIds topicList >>>> " + topicList.size());
			
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
  }


	private List<Integer> subTopicsList(Connection connGGG, List<Integer> topicList, String topicName, Integer templateId) {
		
		logger.info( "subTopicsList: "+topicName);
		
		try {
			
			if(templateId>0 && topicName!=null && topicName.length()>0) {
				
				PreparedStatement psMMM = connGGG.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=?  " );
				psMMM.setInt(1, templateId);
            	psMMM.setString(2, topicName);
                logger.info("subTopicsList: "+psMMM);
                ResultSet rsMMM = psMMM.executeQuery();
                while(rsMMM.next()) {
				
					if(rsMMM.getInt("childnode_id")>0 && rsMMM.getString("childnode_name").length()>0) {
						
						topicList.add(rsMMM.getInt("childnode_id"));
						
						PreparedStatement psNNN = connGGG.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=? LIMIT 0,1  " );
						psNNN.setInt(1, templateId);
		            	psNNN.setString(2, rsMMM.getString("childnode_name"));
		                logger.info("subTopicsList: "+psNNN);
		                ResultSet rsNNN = psNNN.executeQuery();
		                if(rsNNN.next()) {
							//****************************************************************
						    
							this.subTopicsList(connGGG, topicList, rsMMM.getString("childnode_name"), templateId);
							
							//****************************************************************
						} // close if(myObj
		                rsNNN.close();
		                psNNN.close();
					}
					
				} // close while(iterator	
                rsMMM.close();
                psMMM.close();
                
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return topicList;

	} // makeTree Method close
	
	
	
}