package com.bizofficer.admin.students;



import java.io.FileWriter;
import java.util.Iterator;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.TypedQuery;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.data.crossstore.ChangeSetPersister.NotFoundException;
import org.springframework.stereotype.Service;

import com.bizofficer.hibernate.entity.PgprepAdministrator;
import com.bizofficer.hibernate.entity.PgprepStudentCourses;
import com.bizofficer.hibernate.entity.PgprepStudentDetails;
import com.bizofficer.hibernate.repository.PgprepAdministratorRepository;
import com.bizofficer.util.system.MakeDate;

import au.com.bytecode.opencsv.CSVWriter;

@Service
public class DownloadStudentsAdminService{
	
	private static final Logger logger = Logger.getLogger(DownloadStudentsAdminService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	@Autowired
	PgprepAdministratorRepository adminRepo;
	
	@Autowired
	private Environment env;
	
	public Object execute(Object obj) throws NotFoundException{
		StudentAdminBean objBean = (StudentAdminBean) obj;
		DownloadStudentsAdminResponseBean responseObj = new DownloadStudentsAdminResponseBean();
		
		try {
			
			responseObj.setResponseTxt("failed");
			
			logger.info("EmailId: " + objBean.getEmailId() );
			logger.info("Token: " + objBean.getToken() );
			logger.info("Keyword: " + objBean.getKeyword() );
			logger.info("FromDate: " + objBean.getFromDate() );
			logger.info("ToDate: " + objBean.getToDate() );
			
			if(objBean.getEmailId()==null || objBean.getToken()==null) {
				return responseObj;
			}
			
			PgprepAdministrator adminObj = (PgprepAdministrator) adminRepo.findByEmailIdAndLoginToken(objBean.getEmailId(), objBean.getToken());
			if(adminObj==null) {
				logger.info("InvalidToken");
				responseObj.setResponseTxt("InvalidToken");
				return responseObj;
			}

			logger.info("Verify: " + adminObj.getId() );
			
			MakeDate mkdateObj = new MakeDate();
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
			
			String whereQry =" WHERE 1=1 ";			
			if(objBean.getKeyword()!=null && objBean.getKeyword().length()>0 ) {
				whereQry +=" AND (studentId LIKE :keyword OR emailId LIKE :keyword OR sname LIKE :keyword OR accountType LIKE :keyword OR schoolName LIKE :keyword OR className LIKE :keyword ) "; 
			}
			if(objBean.getFromDate()!=null && objBean.getFromDate().length()>0) {
				whereQry+=" AND date(registrationDate)>=:fromDate ";
			}
			if(objBean.getToDate()!=null && objBean.getToDate().length()>0) {
				whereQry+=" AND date(registrationDate)<=:toDate ";
			}
			
			String fileName=env.getProperty("absolutePathContent")+"download-students.csv"; 
			responseObj.setDownloadUrl(env.getProperty("virtualUrlContent")+"/download-students.csv");
			
			logger.info("fileName: " + fileName );
			logger.info("DownloadUrl: " + responseObj.getDownloadUrl() );
			
		    CSVWriter writer = new CSVWriter(new FileWriter(fileName));
		    String [] record = {"id","LoginId","Email","Name","Mobile","Date","School","Course","Course","Course","Course","Course"};
		    writer.writeNext(record);
			
			TypedQuery<PgprepStudentDetails> query = (TypedQuery<PgprepStudentDetails>) entityManager.createQuery("from "+PgprepStudentDetails.class.getName()+whereQry+" order by id ", PgprepStudentDetails.class);
			if(objBean.getKeyword()!=null && objBean.getKeyword().length()>0 ) {
				query.setParameter("keyword", "%"+objBean.getKeyword()+"%");
			}
			if(objBean.getFromDate()!=null && objBean.getFromDate().length()>0) {
				query.setParameter("fromDate", mkdateObj.getStringToDate(objBean.getFromDate(), "yyyy-MM-dd") );
			}
			if(objBean.getToDate()!=null && objBean.getToDate().length()>0) {
				query.setParameter("toDate", mkdateObj.getStringToDate(objBean.getToDate(), "yyyy-MM-dd") );
			}
			query.setFirstResult(0);
			query.setMaxResults(20000);
			List<?> resultListCat = query.getResultList();
			Iterator<?> iteratorCat=resultListCat.iterator();
			int i=0;
			String records = "", courses="";
			while(iteratorCat.hasNext()){
				PgprepStudentDetails myObj = (PgprepStudentDetails)iteratorCat.next();		

				records = String.valueOf(myObj.getId())+"#####";
				records += myObj.getStudentId()+"#####";
				records += myObj.getEmailId()+"#####";
				records += myObj.getSname()+"#####";
				records += myObj.getPhoneNo()+"#####";
				if(myObj.getRegistrationDate()!=null) {
					records += String.valueOf(myObj.getRegistrationDate()).split(" ")[0]+"#####";
				}else {
					records += " #####";
				}
				records += myObj.getSchoolName()+"#####";

				courses="";
				TypedQuery<PgprepStudentCourses> querySC = (TypedQuery<PgprepStudentCourses>) entityManager.createQuery("from "+PgprepStudentCourses.class.getName()+" where studentId=:studentId AND validTillDate is not null order by id ", PgprepStudentCourses.class);
				querySC.setParameter("studentId", myObj.getStudentId());
				List<?> resultSC = querySC.getResultList();
				Iterator<?> iteratorSC=resultSC.iterator();	
				while(iteratorSC.hasNext()){
					PgprepStudentCourses myObjSC = (PgprepStudentCourses)iteratorSC.next();
					if(myObjSC.getValidTillDate()!=null) {
						courses += myObjSC.getCourseTitle()+"("+myObjSC.getSubscriptionType()+")#####";
					}
				}
				
				records += courses;
				records = records.substring(0, records.length()-5);
			    writer.writeNext(records.split("#####"));
				
				i++;
			}

			if(i>0) {
				responseObj.setResponseTxt("success");
			}
			
			writer.close();
			entityManager.getTransaction().commit();
			entityManager.close();
		        
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		
		return responseObj;
	}
	

	
	

	
	
}