package com.bankaudit.rest.process.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;

import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.process.EmailTemplateDto;
import com.bankaudit.util.BankAuditUtil;

@Repository("emailDaoDao")
public class EmailDaoImpl extends AbstractDao implements EmailDao{
	
	static final Logger logger = Logger.getLogger(EmailDaoImpl.class);
	
	@Override
	public EmailTemplateDto getAuditEmailDetailsbyAuditId(int legalEntityCode ,String auditId) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getAuditEmailDetailsbyAuditId .. "+legalEntityCode +" :: "+auditId);
		EmailTemplateDto emailTmpDto=null;
		
		try {
			StringBuilder queryString=new StringBuilder("select  " 
					+ "   sch.legal_entity_code, sch.audit_id,sch.audit_type_code, sch.unit_id, " // 3-> unit_id
					+ "	  sch.schedule_start_date,sch.schedule_end_date,sch.actual_start_date,sch.actual_end_date,sch.report_date, " // 8 ->report_date
					+ "   sch.ir_finalisation_date,sch.report_dispatch_date,DATE_ADD(`report_dispatch_date`, INTERVAL 60 DAY) as rep_submission_from_disp_dt, " // 11->rep_submission_from_disp_dt
					+ "   sch.responded_date, sch.reviewed_date, sch.closure_date, sch.audit_serial_no, sch.financial_year,  "  // 16-> financial_year
					+ "   e.unit_code, e.unit_name, e.contact_person, e.mailid, e.pincode, e.address1, e.address2, e.address3, e.parent_unit_code, " // 25-> parent_unit_code
					+ "	  a.city_name, a.district_name, a.state_name, " // 28 ->state_name
					+ "   p.unit_name as parent_unit, p.mailid as parent_email, p.contact_person as parent_contact,  " // 31->contact_person
					+ "	  (select mailid from maint_entity where level_code='1' limit 1) as HOMailId, "
					+ "   t.user_id, ut.first_name, ut.last_name, ut.designation, " // 35->designation
					+ "   (select group_concat(concat(u.first_name,' ', u.last_name)) from audit_team t, user u where t.audit_id=sch.audit_id "
					+ "			and t.user_type='M' and u.user_id=t.user_id ) as members, " // 36->members
					+ "	  sch.responder_id, sch.reviewer_id " // 38=>reviewer_id
					+ " from audit_schedule sch " 
					+ " join maint_entity e on e.legal_entity_code=sch.legal_entity_code and e.unit_code=sch.unit_id " 
					+ " join maint_entity p on p.legal_entity_code=e.legal_entity_code and p.unit_code=e.parent_unit_code " 
					+ " join audit_team t on sch.legal_entity_code=t.legal_entity_code and sch.audit_id=t.audit_id and t.user_type='L' " 
					+ " left join user ut on ut.legal_entity_code=e.legal_entity_code and ut.user_id=t.user_id " 
					+ "	left join maint_address a on e.legal_entity_code=a.legal_entity_code and ( e.state=a.state_code OR e.state=a.state_iso_code) and e.district=a.district_code and e.city=a.city_code "
					+ " where sch.legal_entity_code=:legalEntityCode and sch.audit_id=:auditId ");
			
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							EmailTemplateDto emailDto=new EmailTemplateDto();
						int i=0;
						if(tuple[i]!=null) emailDto.setLegalEntityCode(legalEntityCode); //0
						i++; if(tuple[i]!=null) emailDto.setAuditId(auditId);  //1 
						i++; if(tuple[i]!=null) emailDto.setAuditTypeCode(tuple[i].toString()); //2
						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setFinaliseDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setDispatchDate(tuple[i].toString()); //10
						i++; if(tuple[i]!=null) emailDto.setRepSubmissionFromDispDt(tuple[i].toString());  // 11
						i++; if(tuple[i]!=null) emailDto.setRespondDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReviewDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setCloseDate(tuple[i].toString());  
						i++; if(tuple[i]!=null) emailDto.setAuditSerialNo(tuple[i].toString());  // 15
						i++; if(tuple[i]!=null) emailDto.setFyEndDate(tuple[i].toString());  // i=16

						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitContactPersonName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitPincode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress1(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress2(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress3(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentUnitCode(tuple[i].toString());  // i=25
						
						i++; if(tuple[i]!=null) emailDto.setUnitCity(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitDistrict(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitState(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setParentUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentContactPersonName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setHOEmailId(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setLeadId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(emailDto.getLeadName()+" "+tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadDesignation(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setMemberName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setBankUserId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReveiwerId(tuple[i].toString());
						
						return emailDto;
					}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode).setParameter("auditId",auditId);
			logger.info("Query .."+query.toString());
			emailTmpDto=(EmailTemplateDto)query.uniqueResult();
			if(emailTmpDto!=null)
			logger.info(""+emailTmpDto.getAuditId()+" SlNo:: "+ emailTmpDto.getAuditSerialNo()+" FyDt::: "+emailTmpDto.getFyEndDate()+" ActStDt:: "+emailTmpDto.getActualStartDate()+" RepDt:: "+emailTmpDto.getReportDate()+" DispDt:: "+emailTmpDto.getDispatchDate());
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		
		return emailTmpDto; 
	}
	
	@Override
	public EmailTemplateDto getAuditReviewEmailDetailsbyAuditId(int legalEntityCode ,String auditId) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getAuditEmailDetailsbyAuditId .. "+legalEntityCode +" :: "+auditId);
		EmailTemplateDto emailTmpDto=null;
		
		try {
			StringBuilder queryString=new StringBuilder("select  " 
					+ "   sch.legal_entity_code, sch.audit_id,sch.audit_type_code, sch.unit_id, " // 3-> unit_id
					+ "	  sch.schedule_start_date,sch.schedule_end_date,sch.actual_start_date,sch.actual_end_date,sch.report_date, " // 8 ->report_date
					+ "   sch.ir_finalisation_date,sch.report_dispatch_date,DATE_ADD(`report_dispatch_date`, INTERVAL 60 DAY) as rep_submission_from_disp_dt, " // 11->rep_submission_from_disp_dt
					+ "   sch.responded_date, sch.reviewed_date, sch.closure_date, sch.audit_serial_no, sch.financial_year,  "  // 16-> financial_year
					+ "   e.unit_code, e.unit_name, e.contact_person, e.mailid, e.pincode, e.address1, e.address2, e.address3, e.parent_unit_code, " // 25-> parent_unit_code
					+ "	  a.city_name, a.district_name, a.state_name, " // 28 ->state_name
					+ "   p.unit_name as parent_unit, p.mailid as parent_email, p.contact_person as parent_contact,  " // 31->contact_person
					+ "	  (select mailid from maint_entity where level_code='1' limit 1) as HOMailId, "
					+ "   t.user_id, ut.first_name, ut.last_name, ut.designation, ut.email_id, " // 36->designation
					+ "   (select group_concat(concat(u.first_name,' ', u.last_name)) from audit_team t, user u where t.audit_id=sch.audit_id "
					+ "			and t.user_type='M' and u.user_id=t.user_id ) as members, " // 37->members
					+ "	  sch.responder_id, sch.reviewer_id, " // 39=>reviewer_id
					+ "   (SELECT email_id FROM user u WHERE u.user_id=sch.reviewer_id) as rvwrMailId , " // 40 -> Reviewer/Scrutiniser mail_id
					+ "	  sch.allocator, sch.report_reviewer, "  // 42 => report_reviewer
					+ "   (select concat(u.first_name,' ', u.last_name) from user u where u.user_id=sch.report_reviewer ) as rptRvwName , " // 43-> rptRvwName"
					+ "   (SELECT email_id FROM user u WHERE u.user_id=sch.allocator) as allctrMailId , " // 44 -> Allocator mail_id
					+ "   (SELECT email_id FROM user u WHERE u.user_id=sch.report_reviewer) as rptRvwMailId , " // 45 -> ReportReviewer mail_id
					+ "   (SELECT GROUP_CONCAT(email_id) FROM user u,maint_entity e, user_role_mapping m WHERE u.unit_code=e.parent_unit_code AND e.unit_code=sch.unit_id AND m.user_role_id='G11' AND u.user_id=m.user_id) as allctrMailIds, " // 46 -> Allocator mail_id based on Unit
					+ "   (SELECT GROUP_CONCAT(email_id) from audit_team t, user u where t.audit_id=sch.audit_id and t.user_type='M' and u.user_id=t.user_id ) as memberEmails, " // 47-> memberEmails
					+ "   (SELECT u.email_id FROM audit_schedule_stages_status_hst h, user u WHERE stages_status='WI' AND audit_id=sch.audit_id AND u.legal_entity_code=h.legal_entity_code AND h.maker_id=u.user_id LIMIT 1) as leadScrutiniserEmail , "  // 48 -> Lead Scrutinizer email id
					+ "	  sch.plan_id " // 49
					
					+ " from audit_schedule sch " 
					+ " join maint_entity e on e.legal_entity_code=sch.legal_entity_code and e.unit_code=sch.unit_id " 
					+ " join maint_entity p on p.legal_entity_code=e.legal_entity_code and p.unit_code=e.parent_unit_code " 
					+ " join audit_team t on sch.legal_entity_code=t.legal_entity_code and sch.audit_id=t.audit_id and t.user_type='L' " 
					+ " left join user ut on ut.legal_entity_code=e.legal_entity_code and ut.user_id=t.user_id " 
					+ "	left join maint_address a on e.legal_entity_code=a.legal_entity_code and ( e.state=a.state_code OR e.state=a.state_iso_code) and e.district=a.district_code and e.city=a.city_code "
					+ " where sch.legal_entity_code=:legalEntityCode and sch.audit_id=:auditId ");
			
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							EmailTemplateDto emailDto=new EmailTemplateDto();
						int i=0;
						if(tuple[i]!=null) emailDto.setLegalEntityCode(legalEntityCode); //0
						i++; if(tuple[i]!=null) emailDto.setAuditId(auditId);  //1 
						i++; if(tuple[i]!=null) emailDto.setAuditTypeCode(tuple[i].toString()); //2
						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setFinaliseDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setDispatchDate(tuple[i].toString()); //10
						i++; if(tuple[i]!=null) emailDto.setRepSubmissionFromDispDt(tuple[i].toString());  // 11
						i++; if(tuple[i]!=null) emailDto.setRespondDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReviewDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setCloseDate(tuple[i].toString());  
						i++; if(tuple[i]!=null) emailDto.setAuditSerialNo(tuple[i].toString());  // 15
						i++; if(tuple[i]!=null) emailDto.setFyEndDate(tuple[i].toString());  // i=16
							 if(tuple[i]!=null) emailDto.setFinancialYear(tuple[i].toString());  // i=16

						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitContactPersonName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitPincode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress1(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress2(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress3(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentUnitCode(tuple[i].toString());  // i=25
						
						i++; if(tuple[i]!=null) emailDto.setUnitCity(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitDistrict(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitState(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setParentUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentContactPersonName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setHOEmailId(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setLeadId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(emailDto.getLeadName()+" "+tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadDesignation(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadEmail(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setMemberName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setBankUserId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReveiwerId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReveiwerEmail(tuple[i].toString());
						
						// 42->
						i++; if(tuple[i]!=null) emailDto.setAllocatorId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportReveiwerId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportReveiwerName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setAllocatorEmail(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportReveiwerEmail(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setAllocatorsEmail(tuple[i].toString());  // If allocator not yet used, then pick the Allocator of the Parent unit
						i++; if(tuple[i]!=null) emailDto.setMemberEmails(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadReveiwerEmail(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setPlanId(tuple[i].toString());
												
						return emailDto;
					}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode).setParameter("auditId",auditId);
			logger.info("Query .."+query.toString());
			emailTmpDto=(EmailTemplateDto)query.uniqueResult();
			if(emailTmpDto!=null)
			logger.info(""+emailTmpDto.getAuditId()+" SlNo:: "+ emailTmpDto.getAuditSerialNo()+" FyDt::: "+emailTmpDto.getFyEndDate()+" ActStDt:: "+emailTmpDto.getActualStartDate()+" RepDt:: "+emailTmpDto.getReportDate()+" DispDt:: "+emailTmpDto.getDispatchDate());
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		
		return emailTmpDto; 
	}
	
	@Override
	public List<EmailTemplateDto> getAuditEmailDetailsForNotification(int legalEntityCode ,String notification) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getAuditEmailDetailsbyAuditId .. "+legalEntityCode +" :: "+notification);
		List<EmailTemplateDto> emailTmpDtos=null;
		int startDuration=0, endDuration=0;		
		
		try {
			if("1".equalsIgnoreCase(notification)) {
				startDuration=30; endDuration=60;
			}else { startDuration=7; endDuration=7;}
			logger.info("startDuration  .. "+startDuration +" :: endDuration .. "+endDuration);
			
			StringBuilder queryString=new StringBuilder("select  " 
					+ "   sch.legal_entity_code, sch.audit_id,sch.audit_type_code, sch.unit_id, " // 3-> unit_id
					+ "	  sch.schedule_start_date,sch.schedule_end_date,sch.actual_start_date,sch.actual_end_date,sch.report_date, " // 8 ->report_date
					+ "   sch.ir_finalisation_date,sch.report_dispatch_date,DATE_ADD(`report_dispatch_date`, INTERVAL 60 DAY) as rep_submission_from_disp_dt, " // 11->rep_submission_from_disp_dt
					+ "   sch.responded_date, sch.reviewed_date, sch.closure_date, sch.audit_serial_no, sch.financial_year,  "  // 16-> financial_year
					+ "   e.unit_code, e.unit_name, e.contact_person, e.mailid, e.pincode, e.address1, e.address2, e.address3, e.parent_unit_code, " // 25-> parent_unit_code
					+ "	  a.city_name, a.district_name, a.state_name, " // 28 ->state_name
					+ "   p.unit_name as parent_unit, p.mailid as parent_email, p.contact_person as parent_contact,  " // 31->contact_person
					+ "   t.user_id, ut.first_name, ut.last_name, ut.designation, " // 35->designation
					+ "   (select group_concat(concat(u.first_name,' ', u.last_name)) from audit_team t, user u where t.audit_id=sch.audit_id "
					+ "			and t.user_type='M' and u.user_id=t.user_id ) as members, " // 36->members
					+ "   DATE_SUB(get_financial_year_endDate_forDate(:legalEntityCode, sch.schedule_start_date),INTERVAL 1 Year) as fyr " // 37
					+ " from audit_schedule sch " 
					+ " join maint_entity e on e.legal_entity_code=sch.legal_entity_code and e.unit_code=sch.unit_id " 
					+ " join maint_entity p on p.legal_entity_code=e.legal_entity_code and p.unit_code=e.parent_unit_code " 
					+ " join audit_team t on sch.legal_entity_code=t.legal_entity_code and sch.audit_id=t.audit_id and t.user_type='L' " 
					+ " left join user ut on ut.legal_entity_code=e.legal_entity_code and ut.user_id=t.user_id " 
					+ "	left join maint_address a on e.legal_entity_code=a.legal_entity_code and ( e.state=a.state_code OR e.state=a.state_iso_code) and e.district=a.district_code and e.city=a.city_code "
					+ " where sch.legal_entity_code=:legalEntityCode "
					+ "	and sch.schedule_start_date BETWEEN DATE_ADD(CURDATE(),INTERVAL :startDuration DAY) AND DATE_ADD(CURDATE(),INTERVAL :endDuration DAY) ");
			
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							EmailTemplateDto emailDto=new EmailTemplateDto();
						int i=0;
						if(tuple[i]!=null) emailDto.setLegalEntityCode(legalEntityCode); //0
						i++; if(tuple[i]!=null) emailDto.setAuditId(tuple[i].toString());  //1 
						i++; if(tuple[i]!=null) emailDto.setAuditTypeCode(tuple[i].toString()); //2
						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setFinaliseDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setDispatchDate(tuple[i].toString()); //10
						i++; if(tuple[i]!=null) emailDto.setRepSubmissionFromDispDt(tuple[i].toString());  // 11
						i++; if(tuple[i]!=null) emailDto.setRespondDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReviewDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setCloseDate(tuple[i].toString());  
						i++; if(tuple[i]!=null) emailDto.setAuditSerialNo(tuple[i].toString());  // 15
						i++; if(tuple[i]!=null) emailDto.setFyEndDate(tuple[i].toString());  // i=16

						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitContactPersonName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitPincode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress1(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress2(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress3(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentUnitCode(tuple[i].toString());  // i=25
						
						i++; if(tuple[i]!=null) emailDto.setUnitCity(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitDistrict(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitState(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setParentUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentContactPersonName(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setLeadId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(emailDto.getLeadName()+" "+tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadDesignation(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setMemberName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setFyEndDate(tuple[i].toString());
						
						return emailDto;
					}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode).setParameter("startDuration",startDuration).setParameter("endDuration", endDuration);
			logger.info("Query .."+query.toString());
			emailTmpDtos=query.list();
			//if(emailTmpDtos!=null) logger.info(""+emailTmpDto.getAuditId()+" SlNo:: "+ emailTmpDto.getAuditSerialNo()+" FyDt::: "+emailTmpDto.getFyEndDate()+" ActStDt:: "+emailTmpDto.getActualStartDate()+" RepDt:: "+emailTmpDto.getReportDate()+" DispDt:: "+emailTmpDto.getDispatchDate());
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		
		return emailTmpDtos; 
	}
	
	//@Override
	public List<EmailTemplateDto> getAuditEmailDetailsOnAuditStatus(int legalEntityCode ,String reminderType, Map<String, String> thresholdMap) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getAuditEmailDetailsbyAuditId .. "+legalEntityCode +" :: "+reminderType);
		logger.info("thresholdMap .. "+thresholdMap);
		List<EmailTemplateDto> emailTmpDtos=null;
		int startDuration=0, endDuration=0;		
		
		try {
			if("1".equalsIgnoreCase(reminderType)) {
				startDuration=30; endDuration=60;
			}else { startDuration=7; endDuration=7;}
			logger.info("startDuration  .. "+startDuration +" :: endDuration .. "+endDuration);
			
			StringBuilder queryString=new StringBuilder("select  " 
					+ "   sch.legal_entity_code, sch.audit_id,sch.audit_type_code, sch.unit_id, " // 3-> unit_id
					+ "	  sch.schedule_start_date,sch.schedule_end_date,sch.actual_start_date,sch.actual_end_date,sch.report_date, " // 8 ->report_date
					+ "   sch.ir_finalisation_date,sch.report_dispatch_date,DATE_ADD(`report_dispatch_date`, INTERVAL 60 DAY) as rep_submission_from_disp_dt, " // 11->rep_submission_from_disp_dt
					+ "   sch.responded_date, sch.reviewed_date, sch.closure_date, sch.audit_serial_no, sch.financial_year,  "  // 16-> financial_year
					+ "   e.unit_code, e.unit_name, e.contact_person, e.mailid, e.pincode, e.address1, e.address2, e.address3, e.parent_unit_code, " // 25-> parent_unit_code
					+ "	  a.city_name, a.district_name, a.state_name, " // 28 ->state_name
					+ "   p.unit_name as parent_unit, p.mailid as parent_email, p.contact_person as parent_contact,  " // 31->contact_person
					+ "   t.user_id, ut.first_name, ut.last_name, ut.designation, " // 35->designation
					+ "   (select group_concat(concat(u.first_name,' ', u.last_name)) from audit_team t, user u where t.audit_id=sch.audit_id "
					+ "			and t.user_type='M' and u.user_id=t.user_id ) as members, " // 36->members
					+ "   (select GROUP_CONCAT(u.email_id) from audit_team t, user u where t.audit_id=sch.audit_id "
					+ "			and t.user_type='M' and u.user_id=t.user_id ) as members, " // 37-> member mailid
					+ "   DATE_SUB(get_financial_year_endDate_forDate(:legalEntityCode, sch.schedule_start_date),INTERVAL 1 Year) as fyr " // 37
					+ " from audit_schedule sch " 
					+ " join maint_entity e on e.legal_entity_code=sch.legal_entity_code and e.unit_code=sch.unit_id " 
					+ " join maint_entity p on p.legal_entity_code=e.legal_entity_code and p.unit_code=e.parent_unit_code " 
					+ " join audit_team t on sch.legal_entity_code=t.legal_entity_code and sch.audit_id=t.audit_id and t.user_type='L' " 
					+ " left join user ut on ut.legal_entity_code=e.legal_entity_code and ut.user_id=t.user_id " 
					+ "	left join maint_address a on e.legal_entity_code=a.legal_entity_code and ( e.state=a.state_code OR e.state=a.state_iso_code) and e.district=a.district_code and e.city=a.city_code "
					+ " WHERE sch.legal_entity_code=:legalEntityCode "
					//+ "	and sch.schedule_start_date BETWEEN DATE_ADD(CURDATE(),INTERVAL :startDuration DAY) AND DATE_ADD(CURDATE(),INTERVAL :endDuration DAY) "
					);
			if("BDAuditExit".equalsIgnoreCase(reminderType)) {  //Actual start date +22= Business Date
				queryString.append(" AND sch.audit_status='AI' AND sch.observation_status is null ");
				queryString.append(" AND DATE_ADD(sch.actual_start_date,INTERVAL :BDRExitDate DAY) = CURDATE() ");
			}else if("ADAuditExit".equalsIgnoreCase(reminderType)) {  // Actual Start date+23 +3= Business Date (till he exits)
				queryString.append(" AND sch.audit_status='AI' AND sch.observation_status is null ");
				queryString.append(" AND DATE_ADD(sch.actual_start_date,INTERVAL :BDRExitDate DAY) = CURDATE() ");
			}else if("BDAuditExit".equalsIgnoreCase(reminderType)) {  //Actual start date +22= Business Date
				
			}
			
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							EmailTemplateDto emailDto=new EmailTemplateDto();
						int i=0;
						if(tuple[i]!=null) emailDto.setLegalEntityCode(legalEntityCode); //0
						i++; if(tuple[i]!=null) emailDto.setAuditId(tuple[i].toString());  //1 
						i++; if(tuple[i]!=null) emailDto.setAuditTypeCode(tuple[i].toString()); //2
						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setScheduleEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualStartDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setActualEndDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReportDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setFinaliseDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setDispatchDate(tuple[i].toString()); //10
						i++; if(tuple[i]!=null) emailDto.setRepSubmissionFromDispDt(tuple[i].toString());  // 11
						i++; if(tuple[i]!=null) emailDto.setRespondDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setReviewDate(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setCloseDate(tuple[i].toString());  
						i++; if(tuple[i]!=null) emailDto.setAuditSerialNo(tuple[i].toString());  // 15
						i++; if(tuple[i]!=null) emailDto.setFyEndDate(tuple[i].toString());  // i=16

						i++; if(tuple[i]!=null) emailDto.setUnitCode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitContactPersonName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitPincode(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress1(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress2(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitAddress3(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentUnitCode(tuple[i].toString());  // i=25
						
						i++; if(tuple[i]!=null) emailDto.setUnitCity(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitDistrict(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setUnitState(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setParentUnitName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentEmailId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setParentContactPersonName(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setLeadId(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadName(emailDto.getLeadName()+" "+tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setLeadDesignation(tuple[i].toString());
						
						i++; if(tuple[i]!=null) emailDto.setMemberName(tuple[i].toString());
						i++; if(tuple[i]!=null) emailDto.setMemberEmails(tuple[i].toString());//
						i++; if(tuple[i]!=null) emailDto.setFyEndDate(tuple[i].toString());
						
						return emailDto;
					}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode); //.setParameter("startDuration",startDuration).setParameter("endDuration", endDuration);
			
			if("BDAuditExit".equalsIgnoreCase(reminderType)) {  //Actual start date +22= Business Date
				query.setParameter("BDRExitDate", 22);
			}else if("ADAuditExit".equalsIgnoreCase(reminderType)) {  // Actual Start date+23 +3= Business Date (till he exits)
				
			}
			logger.info("Query .."+query.toString());
			emailTmpDtos=query.list();
			//if(emailTmpDtos!=null) logger.info(""+emailTmpDto.getAuditId()+" SlNo:: "+ emailTmpDto.getAuditSerialNo()+" FyDt::: "+emailTmpDto.getFyEndDate()+" ActStDt:: "+emailTmpDto.getActualStartDate()+" RepDt:: "+emailTmpDto.getReportDate()+" DispDt:: "+emailTmpDto.getDispatchDate());
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		
		return emailTmpDtos; 
	}


}
