package com.bankaudit.template.rest.service;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.bankaudit.dao.*;
import com.bankaudit.model.ExcelMaintEntity;
import com.bankaudit.model.ExcelUploadDataStatus;
import com.bankaudit.model.MaintAuditTypeDesc;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.MaintEntityAuditSubgroupMappingDao;
import com.bankaudit.dao.UserDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.MaintEntityAuditSubGroupMappingDto;
import com.bankaudit.dto.process.AuditPlanDto;
import com.bankaudit.model.MaintEntityAuditSubgroupMapping;
import com.bankaudit.model.User;
import com.bankaudit.model.UserRoleMapping;
import com.bankaudit.process.model.AuditPlan;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.ChecklistOfflineInspectionTmp;
import com.bankaudit.rest.process.dao.AuditPlanDao;
import com.bankaudit.rest.process.service.AuditPlanService;
import com.bankaudit.rest.process.service.MyAuditsService;
import com.bankaudit.service.ActivityProcessFindingMappingService;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.MaintAuditTypeDescService;
import com.bankaudit.service.MaintEntityAuditSubgroupMappingService;
import com.bankaudit.service.MaintEntityService;
import com.bankaudit.service.UserService;
import com.bankaudit.spring.config.BankAuditConfig;
import com.bankaudit.template.model.MaintTemplateDtls;
import com.bankaudit.template.model.UploadStatus;
import com.bankaudit.template.rest.dao.UploadDownloadDao;
import com.bankaudit.template.rest.dao.UploadStatusDao;
import com.bankaudit.util.BankAuditUtil;
import java.util.List;
import java.util.stream.Collectors;

@Service("uploadDownloadService")
@Transactional("transactionManager")
public class UploadDownloadServiceImpl implements UploadDownloadService {
	
	@Autowired
	UserService userService;
	
	@Autowired
	UserDao userDao;
	
	@Autowired
	BusinessDateService businessDateService;
	
	@Autowired
	MaintEntityAuditSubgroupMappingService maintEntityAuditSubgroupMappingService;
	
	@Autowired
	MaintEntityAuditSubgroupMappingDao maintEntityAuditSubgroupMappingDao;
	
	@Autowired
	ActivityProcessFindingMappingService activityProcessFindingMappingService;
	
	@Autowired
	MaintEntityService maintEntityService;
	
	@Autowired
	UploadStatusDao uploadStatusDao;

	@Autowired
	ExcelMaintEntityDao excelMaintEntityDao;

	@Autowired
	MaintAddressDao maintAddressDao;

	@Autowired
	GeneralParameterDao generalParameterDao;

	@Autowired
	MaintEntityDao maintEntityDao;

	@Autowired
	ExcelUploadDataStatusDao excelUploadDataStatusDao;
	
	@Autowired
	UploadDownloadDao uploadDownloadDao;
	
	@Autowired
	MyAuditsService myAuditService;
	
	@Autowired
	AuditPlanDao auditPlanDao;
	
	@Autowired
	AuditPlanService auditPlanSerive;
	
	@Autowired
	MaintAuditTypeDescService maintAuditTypeDescService;
	
		
	static final Logger logger = Logger.getLogger(UploadDownloadServiceImpl.class);
	
	/** New method, where validate with UserID and update the other values **/
	@Override
	public String updateUserFromExcel(Integer legalEntityCode, String maker, CommonsMultipartFile userFile)  {
		
		try{
			logger.info(" updateUserFromExcel .. "+legalEntityCode +" :: "+maker +" :: "+userFile.getOriginalFilename() );
			String[] userColumnHeader = { "Sl. No.","Employee ID","Name Display","Designation","Unit Name","Dept Name","Grade",
					"Mobile Number","Email Address","Status","Reason for Failure"};			
			XSSFWorkbook validatedUserExcelSheet = new XSSFWorkbook();
			XSSFSheet userSheet = validatedUserExcelSheet.createSheet("user"); 
			Row userRowHead = userSheet.createRow(0);	
			for (int col = 0; col < 11 ; col++) {
		        Cell cell = userRowHead.createCell(col);
		        cell.setCellValue(userColumnHeader[col]);
		    }		
			
			XSSFWorkbook userExcelBook = new XSSFWorkbook(userFile.getInputStream());
			XSSFSheet worksheet = userExcelBook.getSheetAt(0);
			Iterator<Row> iterator = worksheet.iterator(); 	
			int rowIndex = 1; int successCnt=0; int failureCnt=0;       
			FormulaEvaluator formulaEvaluator=userExcelBook.getCreationHelper().createFormulaEvaluator(); 
			
			Map<String, String> unitCodeMap = maintEntityService.getUnitCodes(legalEntityCode, maker, null);
			
			while (iterator.hasNext()) {
				
	            Row currentUserRow = iterator.next();
	            logger.info("Row num .. "+currentUserRow.getRowNum());
	            
	            if(currentUserRow.getRowNum() ==0){continue;}
	            // Iterator<Cell> cellUserIterator = currentUserRow.iterator();
	             
	            Integer srNo = null; 
	            String userId = null; 
	            String firstName = null;
	            String designation = null;
	            String unitCode = null;
	            String deptCode = null;           
	            String grade  = null; //shortName
	            String mobileNumber = null;
	            String emailAddress = null;               
	            StringBuilder reasonforFailure =new StringBuilder();
	            
	            String userType=null; // New or Old user
	            
	            // int cellIndex=0;                
	            //while (cellUserIterator.hasNext()) {	
	              //Cell currentCell = cellUserIterator.next();	 
		        for(Cell currentCell: currentUserRow) {
		             int cellIndex = currentCell.getColumnIndex();
		             switch(formulaEvaluator.evaluateInCell(currentCell).getCellType())  {  
		               case Cell.CELL_TYPE_NUMERIC:   
			               
		            	   if(cellIndex==0) userId = String.valueOf((long)currentCell.getNumericCellValue());
			               if(cellIndex==1) firstName = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==2) designation = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==3) unitCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==4) deptCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==5) grade = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==6) mobileNumber = String.valueOf((long)currentCell.getNumericCellValue());
			               if(cellIndex==7) emailAddress = String.valueOf(currentCell.getNumericCellValue());
			               break;  
		               case Cell.CELL_TYPE_STRING:    //field that represents string cell type  
		            	   /*if(cellIndex==0) {
			            	   try{	srNo =  (long) Long.parseLong(currentCell.getStringCellValue()) ;
			                   }catch (Exception e) {
			                		srNo =0L;
							   }
			               }*/
		            	   if(cellIndex==0) userId = currentCell.getStringCellValue();
			               if(cellIndex==1) firstName = currentCell.getStringCellValue();
			               if(cellIndex==2) designation = currentCell.getStringCellValue();
			               if(cellIndex==3) unitCode = currentCell.getStringCellValue();
			               if(cellIndex==4) deptCode = currentCell.getStringCellValue();
			               if(cellIndex==5) grade = currentCell.getStringCellValue();
			               if(cellIndex==6) mobileNumber = currentCell.getStringCellValue();
			               if(cellIndex==7) emailAddress = currentCell.getStringCellValue();
		            	   break;  
		            	  
		               case Cell.CELL_TYPE_BOOLEAN:     
		            	   //if(cellIndex==1) userId = currentCell.getBooleanCellValue()+"";
		            	   if(cellIndex==0) userId = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==1) firstName = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==2) designation = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==3) unitCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==4) deptCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==5) grade = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==6) mobileNumber = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==7) emailAddress = String.valueOf(currentCell.getNumericCellValue());
		            	   break;  
		            	   
		               case Cell.CELL_TYPE_BLANK:     
		            	   if(cellIndex==0) userId = "";
			               if(cellIndex==1) firstName = "";
			               if(cellIndex==2) designation =  "";
			               if(cellIndex==3) unitCode =  "";
			               if(cellIndex==4) deptCode =  "";
			               if(cellIndex==5) grade =  "";
			               if(cellIndex==6) mobileNumber =  "";
			               if(cellIndex==7) emailAddress =  "";
		            	   break;  
		             }
		               //cellIndex++;
		        }srNo=rowIndex;
	            
	            logger.info(" srNo :: "+srNo +" :: "+ userId+" :: "+firstName + " :: "+ designation +" UnitCode:: "+unitCode );
	            logger.info(" deptCode :: "+deptCode +" :: "+ grade+" FrmMobile:: "+mobileNumber +" :: "+emailAddress);
	            
		        logger.info("ROW INDEX ====================== "+ rowIndex);
		        logger.info("reasonforFailure ====================== "+ reasonforFailure);
		        Row row = userSheet.createRow(rowIndex++);	 
	            if(userId!=null  && !userId.trim().equals("")){
		            //Row row = userSheet.createRow(rowIndex++);	 
		            User user = userService.getUserByLegalEntityCodeAndUserId(legalEntityCode, userId+"");
		            if(user !=null)logger.info(" user dtls :: "+user.getUnitCode() +" :: "+ user.getShortName()+" Mobile:: "+user.getMobile1());
		            boolean isValidated = true;
		            
		            // User validation
		            if(user !=null){
		            	userType= "Old";
		            	if(firstName != null && !"".equalsIgnoreCase(firstName)){
			            	//isValidated = true;
		            	}else {
			            	isValidated = false;
			            	reasonforFailure.append(" \n User name shoudn't be left blank for the user.");
		            	}
		            	
		            	// Grade validation only for New Users  
			            if(grade != null && !"".equalsIgnoreCase(grade)){
				           	//isValidated = true;
				        } else {
				           	isValidated = false;
				           	reasonforFailure.append(" \n Grade shouldn't be left blank.");
				        }
		            }else { 
		            	userType= "New";
		            	if(firstName != null && !"".equalsIgnoreCase(firstName)){
			            	//isValidated = true;
		            	}else {
			            	isValidated = false;
			            	reasonforFailure.append(" \n User name shoudn't be left blank for the new user.");
		            	}
		            }	
		            
		            // Designation validation needed only for New 
		            if(designation != null && !"".equalsIgnoreCase(designation)){
			            //isValidated = true;
			        }else {
			            isValidated = false;
			            reasonforFailure.append(" \n Designation shoudn't be left blank.");
			        }
		               
		            // Unit code validation
		            if(unitCode != null && !"".equalsIgnoreCase(unitCode)){
			            	//isValidated = true;
		            	if(unitCodeMap.containsKey(unitCode));
		            	else {
		            		isValidated = false;
				           	reasonforFailure.append(" \n Unit code doesn't exists.");
		            	} 
			        }  else if("New".equalsIgnoreCase(userType)) {
			           	isValidated = false;
			           	reasonforFailure.append(" \n Unit code shoudn't be left blank for new users.");
			        }
		              
		            // Department related
		            if("HO".equalsIgnoreCase(unitCode) && "Old".equalsIgnoreCase(userType)) {
		               if(deptCode != null ){
			            	//isValidated = true;
		            	   // List<MaintEntityAuditSubgroupMapping> maintEntityAuditSubgroupMappings= maintEntityAuditSubgroupMappingService.getMaintEntityAuditSubgroupMapping(legalEntityCode, "U", userId, "A");
		            	   HashMap<String, String> map = activityProcessFindingMappingService.getAnyOneAuditGrpAndSubGrpForAuditType(legalEntityCode, deptCode, "A");
		            	   
		            	   if(map !=null) {
		            	   	   /*if(!maintEntityAuditSubgroupMappings.isEmpty() && maintEntityAuditSubgroupMappings.size() > 1) {
			            		   isValidated = false;
					               reasonforFailure.append(" \n There are more than one Department mapped to the User, unable to decide which one to update.");
			            	   }else if(maintEntityAuditSubgroupMappings.isEmpty()){ // If no Dept there Add department to the User
			            		   saveOrUpdateUserMapping(legalEntityCode, maker, userId, deptCode, map, "SAVE" );
			            	   }else { // If one Dept mapped, then update department to the User
			            		   saveOrUpdateUserMapping(legalEntityCode, maker, userId, deptCode, map, "UPDATE" );
			            	   }*/
		            		   deleteUserMapping(legalEntityCode, maker, userId, deptCode, null, "DELETE" );
		            		   saveOrUpdateUserMapping(legalEntityCode, maker, userId, deptCode, map, "SAVE" );
		            		   
		            	   }else {
		            		   isValidated = false;
				               reasonforFailure.append(" \n The Department is not avialable or not mapped to business line/ product.");
		            	   }
			           }else {
			        	   deleteUserMapping(legalEntityCode, maker, userId, deptCode, null, "DELETE" );
			           }
		          }   
		                             
			      // Mobile number validation	
		          if(mobileNumber != null && !mobileNumber.trim().equals("") && !mobileNumber.contains("+") && mobileNumber.length()==10){
		           	//isValidated = true;
		          } else {
		            	isValidated = false;
		            	if(mobileNumber ==null || ( mobileNumber!=null && mobileNumber.trim().equals(""))) {
		            		if("New".equalsIgnoreCase(userType)) reasonforFailure.append(" \n Mobile number shouldn't be left blank for new users.");
		            	}else { 
		            		reasonforFailure.append(" \n Mobile number format is not correct.");
		            	}
		          } 
		            
	              /*if(emailAddress != null && emailAddress.contains("@") ){
		          	  //isValidated = true;
	              } else if("New".equalsIgnoreCase(userType)) {
	            	  isValidated = false;
	            	  reasonforFailure.append(" \n Email shouldn't be left blank for new users.");
	              } */
		          
		          
		          if("Old".equalsIgnoreCase(userType)) {
	            	 if(!BankAuditUtil.isEmailAddressValid(emailAddress)) {
	            		 isValidated = false;
	            		 reasonforFailure.append(" \n Email address is not proper.");
	            	 }
	              }else { // for New users
	            	  if(emailAddress ==null || (emailAddress != null && "".equals(emailAddress.trim()))){
	            		  isValidated = false;
		            	  reasonforFailure.append(" \n Email shouldn't be left blank for new users.");
	            	  }else if(!BankAuditUtil.isEmailAddressValid(emailAddress)) {
		            		 isValidated = false;
		            		 reasonforFailure.append(" \n Email address is not proper.");
		            	 }
	              } 
		          
		          
		            
	              logger.info(" reasonforFailure ..@End .." +reasonforFailure );
		            
		            /*if(user!=null && 
		            		(fromUnitCode.equalsIgnoreCase(user.getUnitCode()))
		            		&&  fromGrade.equalsIgnoreCase(user.getShortName())
		            		&&  fromMobileNumber.equalsIgnoreCase(user.getMobile1())){
		            	*/
		             if(isValidated){
		            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(srNo);
		            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(userId);
		            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(firstName);
		            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(designation);
		            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(unitCode);
		            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(deptCode);
		            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(grade);
		            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(mobileNumber);
		            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(emailAddress);
		            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue("Success");
		            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(reasonforFailure.toString());
		            			            			             	
		             	if("New".equalsIgnoreCase(userType)) {
		             		user= new User();
		             		user.setLegalEntityCode(legalEntityCode);
		             		user.setUserId(userId);
		             		user.setUnitCode(unitCode);
		             		user.setFirstName(firstName); 
		             		user.setPassword("xxxxxxxxxx");
			             	user.setUnitCode(unitCode);
			             	user.setDesignation(designation);
			             	user.setGrade(grade);
			             	user.setDepartment(deptCode);
			             	user.setMobile1(mobileNumber);
			             	user.setEmailId(emailAddress);
		             		user.setSalutation("NA");
		             		user.setGender("N");
		             		user.setStatus("A");
		             		user.setEntityStatus("A");
		             		user.setMaker(maker);
		             		user.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));	
		             		user.setCreatedDate(new Timestamp(System.currentTimeMillis()));
		             	} else {
		             		
		             		if(firstName !=null && !firstName.trim().equalsIgnoreCase("")) user.setFirstName(firstName); 
		             		if(unitCode !=null && !unitCode.trim().equalsIgnoreCase("")) user.setUnitCode(unitCode);
		             		if(designation !=null && !designation.trim().equalsIgnoreCase("")) user.setDesignation(designation);
		             		if(grade !=null && !grade.trim().equalsIgnoreCase("")) user.setShortName(grade);
		             		if(mobileNumber !=null && !mobileNumber.trim().equalsIgnoreCase("")) user.setMobile1(mobileNumber);
		             		if(emailAddress !=null && !emailAddress.trim().equalsIgnoreCase("")) user.setEmailId(emailAddress);
		             		user.setMaker(maker);
		             		user.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
		             	}
		             	
		             	logger.info("user ");
		             	userDao.save(user);
		             	
		             	// To map the dept to the new User, as the user not created so need to do the mapping post creation of the User 
		             	try {
			             	if("HO".equalsIgnoreCase(unitCode) && "New".equalsIgnoreCase(userType)) {
			             		HashMap<String, String> map= activityProcessFindingMappingService.getAnyOneAuditGrpAndSubGrpForAuditType(legalEntityCode, deptCode, "A");
				            	   if(map !=null) {
				            		   saveOrUpdateUserMapping(legalEntityCode, maker, userId, deptCode, map, "SAVE" );
				            	   }
			             	}
		             	}catch(Exception e) {}
		             	// end here
		             	
		             	successCnt++;
		            }else{
		            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(srNo);
		            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(userId);
		            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(firstName);
		            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(designation);
		            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(unitCode);
		            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(deptCode);
		            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(grade);
		            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(mobileNumber);
		            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(emailAddress);
		            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue("Failed");
		            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(reasonforFailure.toString());
		            	failureCnt++;
		            }  
	            }else{
	            	// If User id is Blank
	            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(srNo);
	            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(userId);
	            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(firstName);
	            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(designation);
	            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(unitCode);
	            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(deptCode);
	            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(grade);
	            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(mobileNumber);
	            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(emailAddress);
	            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue("Failed");
	            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue("User/Employee Id shoudn't be left blank for the user.");
	            	failureCnt++;
	            }
			 }
			
			logger.info("Success :: "+successCnt +" Failure :: "+failureCnt);
			 //Need to store excel with status 
			 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
			 String uploadFileName = userFile.getOriginalFilename()+"_"+simpleDateFormat.format(new Date())+".xlsx";    		 
			 String uploadPath = BankAuditConfig.getInstance().getConfigValue(BankAuditConstant.DIR_USER_UPLOAD)+ "/"+uploadFileName;
			 
			 FileOutputStream userFileOutputStream = new FileOutputStream(uploadPath);
			 validatedUserExcelSheet.write(userFileOutputStream);			  
			 userFileOutputStream.flush();
			 userFileOutputStream.close();	
			 
			 userExcelBook.close();
			 validatedUserExcelSheet.close(); 
			 
			 //************************* Updating the UploadStatus  ****************************/
			 logger.info("Bfr calling the updateUploadStaus .."+ successCnt);
			 updateUploadStaus( legalEntityCode, "User",  uploadPath, uploadFileName, maker, rowIndex-1, successCnt, failureCnt);
			 // End Here //
		} 
		catch (IOException e) {
			e.printStackTrace(System.out);
			return "failure";
		}
		return "success";
	}
	
	public String updateUserFromExcelValidateFromTo(Integer legalEntityCode, String maker, CommonsMultipartFile userFile)  {
		
		try{
			logger.info(" updateUserFromExcel .. "+legalEntityCode +" :: "+maker +" :: "+userFile.getOriginalFilename() );
			String[] userColumnHeader = { "S.N.","Employee ID","Name","From Unit","To Unit","From Grade","To Grade",
					"From Mobile Number","To Mobile Number","Status","Reason for Failure"};			
			XSSFWorkbook validatedUserExcelSheet = new XSSFWorkbook();
			XSSFSheet userSheet = validatedUserExcelSheet.createSheet("user"); 
			Row userRowHead = userSheet.createRow(0);	
			for (int col = 0; col < 11 ; col++) {
		        Cell cell = userRowHead.createCell(col);
		        cell.setCellValue(userColumnHeader[col]);
		    }		
			
			XSSFWorkbook userExcelBook = new XSSFWorkbook(userFile.getInputStream());
			XSSFSheet worksheet = userExcelBook.getSheetAt(0);
			Iterator<Row> iterator = worksheet.iterator(); 	
			int rowIndex = 1; int successCnt=0; int failureCnt=0;       
			FormulaEvaluator formulaEvaluator=userExcelBook.getCreationHelper().createFormulaEvaluator();  
			while (iterator.hasNext()) {
				
	            Row currentUserRow = iterator.next();
	            if(currentUserRow.getRowNum() ==0){continue;}
	           // Iterator<Cell> cellUserIterator = currentUserRow.iterator();
	             
	            Integer srNo = null; 
	            String userId = null; 
	            String firstName = null;
	            String fromUnitCode = null;
	            String toUnitCode = null;           
	            String fromGrade  = null; //shortName
	            String toGrade = null; 
	            String fromMobileNumber = null;
	            String toMobileNumber = null;               
	            StringBuilder reasonforFailure =new StringBuilder();
	            
	           // int cellIndex=0;                
	            //while (cellUserIterator.hasNext()) {	
	              //Cell currentCell = cellUserIterator.next();	 
		        for(Cell currentCell: currentUserRow) {
		             int cellIndex = currentCell.getColumnIndex();
		             switch(formulaEvaluator.evaluateInCell(currentCell).getCellType())  {  
		               case Cell.CELL_TYPE_NUMERIC:   
			               
		            	   if(cellIndex==1) userId = String.valueOf((long)currentCell.getNumericCellValue());
			               if(cellIndex==2) firstName = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==3) fromUnitCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==4) toUnitCode = String.valueOf(currentCell.getNumericCellValue()) ;
			               if(cellIndex==5) fromGrade = String.valueOf(currentCell.getNumericCellValue()) ;
			               if(cellIndex==6) toGrade = String.valueOf(currentCell.getNumericCellValue()) ;
			               if(cellIndex==7) fromMobileNumber = String.valueOf((long)currentCell.getNumericCellValue()) ;
			               if(cellIndex==8) toMobileNumber = String.valueOf((long)currentCell.getNumericCellValue()) ;
			               break;  
		               case Cell.CELL_TYPE_STRING:    //field that represents string cell type  
		            	   /*if(cellIndex==0) {
			            	   try{	srNo =  (long) Long.parseLong(currentCell.getStringCellValue()) ;
			                   }catch (Exception e) {
			                		srNo =0L;
							   }
			               }*/
		            	   if(cellIndex==1) userId = currentCell.getStringCellValue() ;
			               if(cellIndex==2) firstName = currentCell.getStringCellValue();
			               if(cellIndex==3) fromUnitCode = currentCell.getStringCellValue() ;
			               if(cellIndex==4) toUnitCode = currentCell.getStringCellValue() ;
			               if(cellIndex==5) fromGrade = currentCell.getStringCellValue() ;
			               if(cellIndex==6) toGrade = currentCell.getStringCellValue() ;
			               if(cellIndex==7) fromMobileNumber = currentCell.getStringCellValue() ;
			               if(cellIndex==8) toMobileNumber = currentCell.getStringCellValue() ;
		            	   break;  
		            	  
		               case Cell.CELL_TYPE_BOOLEAN:     
		            	   if(cellIndex==1) userId = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==2) firstName = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==3) fromUnitCode = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==4) toUnitCode = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==5) fromGrade = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==6) toGrade = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==7) fromMobileNumber = currentCell.getBooleanCellValue()+"";
			               if(cellIndex==8) toMobileNumber = currentCell.getBooleanCellValue()+"";
		            	   break;  
		            	   
		               case Cell.CELL_TYPE_BLANK:     
		            	   if(cellIndex==1) userId = "";
			               if(cellIndex==2) firstName = "";
			               if(cellIndex==3) fromUnitCode =  "";
			               if(cellIndex==4) toUnitCode =  "";
			               if(cellIndex==5) fromGrade =  "";
			               if(cellIndex==6) toGrade =  "";
			               if(cellIndex==7) fromMobileNumber =  "";
			               if(cellIndex==8) toMobileNumber =  "";
		            	   break;  
		             }
		               //cellIndex++;
		        }srNo=rowIndex;
	            
	            logger.info(" srNo :: "+srNo +" :: "+ userId+" :: "+firstName +" FrmUCode:: "+fromUnitCode +" :: "+ toUnitCode);
	            logger.info(" fromGrade :: "+fromGrade +" :: "+ toGrade+" FrmMobile:: "+fromMobileNumber +" :: "+toMobileNumber);
	            
		        logger.info("ROW INDEX ====================== "+ rowIndex);
		        logger.info("reasonforFailure ====================== "+ reasonforFailure);
		        Row row = userSheet.createRow(rowIndex++);
	            if(userId!=null  && !userId.trim().equals("")){
		            //Row row = userSheet.createRow(rowIndex++);	 
		            User user = userService.getUserByLegalEntityCodeAndUserId(legalEntityCode, userId+"");
		            logger.info(" user dtls :: "+user.getUnitCode() +" :: "+ user.getShortName()+" Mobile:: "+user.getMobile1());
		            boolean isValidated = true;
		            if(user==null){
		            	isValidated = false;
		            	reasonforFailure.append("The UserId dpesn't exist. ");
		            }else {            
		               if(fromUnitCode != null && toUnitCode !=null && fromUnitCode.equalsIgnoreCase(user.getUnitCode())){
			            	//isValidated = true;
			            }  else {
			            	isValidated = false;
			            	if(fromUnitCode == null) { 
			            		reasonforFailure.append(" \n From Unit Code shoudn't be null.");
			            	}else if(!fromUnitCode.equalsIgnoreCase(user.getUnitCode())) {
			            		reasonforFailure.append(" \n From Unit Code is not matching to the existing code.");
			            	}
			            	if(toUnitCode == null) reasonforFailure.append(" \n To Unit Code shoudn't be left blank.");
			            }
			            		            
		               //(fromGrade == null || fromGrade.trim().equals("")) && (toGrade==null  || toGrade.trim().equals(""))
			            if(fromGrade != null && fromGrade.equalsIgnoreCase(user.getShortName())){
			            	//isValidated = true;
			            } else {
			            	isValidated = false;
			            	if(!fromGrade.equalsIgnoreCase(user.getShortName()))
			            		reasonforFailure.append(" \n From Grade is not matching to the existing Grade.");
			            }
			            		            
			            //if(((fromMobileNumber == null || fromMobileNumber.trim().equals("")) && (toMobileNumber==null  || toMobileNumber.trim().equals(""))) ||  (fromMobileNumber != null && !fromMobileNumber.trim().equals("") && fromMobileNumber.equalsIgnoreCase(user.getMobile1()))){
			            if(fromMobileNumber != null && (toMobileNumber!=null && !toMobileNumber.trim().equals(""))
			            		&& fromMobileNumber.equalsIgnoreCase(user.getMobile1())){
			            	//isValidated = true;
			            } else {
			            	//isValidated = false;
			            	if(fromMobileNumber != null && !fromMobileNumber.equalsIgnoreCase(user.getMobile1()))
			            		reasonforFailure.append(" \n From Mobile no is not matching to the existing Mobile no.");
			            	if(toMobileNumber!=null && toMobileNumber.trim().equals(""))
			            		reasonforFailure.append(" \n To Mobile no can't be left blank.");
			            } 
		            
		            }
		            /*if(user!=null && 
		            		(fromUnitCode.equalsIgnoreCase(user.getUnitCode()))
		            		&&  fromGrade.equalsIgnoreCase(user.getShortName())
		            		&&  fromMobileNumber.equalsIgnoreCase(user.getMobile1())){
		            	*/
		             if(isValidated){
		            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(srNo);
		            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(userId);
		            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(firstName);
		            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(fromUnitCode);
		            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(toUnitCode);
		            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(fromGrade);
		            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(toGrade);
		            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(fromMobileNumber);
		            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(toMobileNumber);
		            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue("Success");
		            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(reasonforFailure.toString());
		            	
		            	user.setFirstName(firstName); 
		             	user.setUnitCode(toUnitCode);
		             	user.setShortName(toGrade);
		             	user.setMobile1(toMobileNumber);
		             	logger.info("user in success ..");
		             	userDao.save(user);
		             	successCnt++;
		            }else{
		            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.createCell(0).setCellValue(srNo);
		            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(userId);
		            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(firstName);
		            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(fromUnitCode);
		            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(toUnitCode);
		            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(fromGrade);
		            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(toGrade);
		            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.createCell(7).setCellValue(fromMobileNumber);
		            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(toMobileNumber);
		            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue("Failed");
		            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(reasonforFailure.toString());
		            	failureCnt++;
		            }  
	            }else{
	            	// If User id is Blank
	            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.createCell(0).setCellValue(srNo);
	            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(userId);
	            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(firstName);
	            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(fromUnitCode);
	            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(toUnitCode);
	            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(fromGrade);
	            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(toGrade);
	            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.createCell(7).setCellValue(fromMobileNumber);
	            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(toMobileNumber);
	            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue("Failed");
	            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue("User/Employee Id shoudn't be left blank for the user."); 
	            	failureCnt++;
	            }
			 }
			 //Need to store excel with status 
			 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
			 String uploadFileName = userFile.getOriginalFilename()+"_"+simpleDateFormat.format(new Date())+".xlsx";    		 
			 String uploadPath = BankAuditConfig.getInstance().getConfigValue(BankAuditConstant.DIR_USER_UPLOAD)+ "/"+uploadFileName;
			 
			 FileOutputStream userFileOutputStream = new FileOutputStream(uploadPath);
			 validatedUserExcelSheet.write(userFileOutputStream);			  
			 userFileOutputStream.flush();
			 userFileOutputStream.close();	
			 
			 userExcelBook.close();
			 validatedUserExcelSheet.close(); 
			 
			 //************************* Updating the UploadStatus  ****************************/
			 updateUploadStaus( legalEntityCode, "User",  uploadPath, uploadFileName, maker, rowIndex-1, successCnt, failureCnt);
			 // End Here //
		} 
		catch (IOException e) {
			return "failure";
		}
		return "success";
	}
	
	public void updateUploadStaus(Integer legalEntityCode, String type,  String uploadPath, String uploadFileName, 
			String maker, int total, int success, int failure)  {
		try {
			logger.info("Inside updateUploadStaus .. "+ legalEntityCode+" :: "+type+" :: "+maker+" :: "+total+" :: "+success+" :: "+failure);
			logger.info("Upload .. "+uploadPath+" :: "+uploadFileName);
			UploadStatus uploadStatus = new UploadStatus();
			uploadStatus.setLegalEntityCode(legalEntityCode);
			uploadStatus.setType(type);
			uploadStatus.setFileName(uploadFileName);
			uploadStatus.setReferencePath(uploadPath);
			//uploadStatus.setUploadStatus("");
			uploadStatus.setUploadDate(new Date());
			uploadStatus.setTotalRecords(total);
			uploadStatus.setSuccessRecords(success);
			uploadStatus.setFailedRecords(failure);
			uploadStatus.setMaker(maker);
			uploadStatus.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
			uploadStatus.setBusinessdateTimestamp(new Timestamp(System.currentTimeMillis()));
			uploadStatus.setSysdateTimestamp(new Timestamp(System.currentTimeMillis()));
			uploadStatusDao.save(uploadStatus);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		
	}
	
	@Override
	public DataTableResponse getAllUploadStatus(Integer legalEntityCode, String search, Integer orderColumn, 
			String orderDirection, Integer page, Integer size, String type) {
		return uploadStatusDao.getAllUploadStatus( legalEntityCode ,  search,  orderColumn, orderDirection,  page,  size, type);
	}
	
	@Override
	public UploadStatus getUploadStatusById(Integer legalEntityCode, Integer id) {
		return (UploadStatus)uploadStatusDao.getUploadStatusById(legalEntityCode, id);
	}
	
	public void saveOrUpdateUserMapping(Integer legalEntityCode, String maker, String userId, String deptCode, HashMap<String, String> map, String saveOrUpdate ) {
		if("SAVE".equalsIgnoreCase(saveOrUpdate)){
			MaintEntityAuditSubgroupMapping maintEntityAuditSubgroupMapping = new MaintEntityAuditSubgroupMapping();
			maintEntityAuditSubgroupMapping.setLegalEntityCode(legalEntityCode);
			maintEntityAuditSubgroupMapping.setMappingType("U");
			maintEntityAuditSubgroupMapping.setId(userId);
			maintEntityAuditSubgroupMapping.setAuditTypeCode(map.get("AuditTypeCode"));
			maintEntityAuditSubgroupMapping.setAuditGroupCode(map.get("AuditGroupCode"));
			maintEntityAuditSubgroupMapping.setAuditSubGroupCode(map.get("AuditSubGroupCode"));
			maintEntityAuditSubgroupMapping.setStatus("A");
			maintEntityAuditSubgroupMapping.setMaker(maker);
			maintEntityAuditSubgroupMapping.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
			maintEntityAuditSubgroupMapping.setSysdateTimestamp(new Timestamp(System.currentTimeMillis()));
			maintEntityAuditSubgroupMapping.setBusinessdateTimestamp(new Timestamp(System.currentTimeMillis()));
			maintEntityAuditSubgroupMappingDao.save(maintEntityAuditSubgroupMapping);
			
		}else if("UPDATE".equalsIgnoreCase(saveOrUpdate)){
			Map<String, Object> properties=new HashMap<String,Object>();
			properties.put("legalEntityCode", legalEntityCode);
			properties.put("mappingType", "U");
			properties.put("id", userId);
			List<MaintEntityAuditSubgroupMapping> maintEntityAuditSubgroupMappings = 
					maintEntityAuditSubgroupMappingDao.getEntitiesByMatchingProperties(MaintEntityAuditSubgroupMapping.class, properties);
			MaintEntityAuditSubgroupMapping maintEntityAuditSubgroupMapping=maintEntityAuditSubgroupMappings.get(0);
			
			maintEntityAuditSubgroupMapping.setAuditTypeCode(map.get("AuditTypeCode"));
			maintEntityAuditSubgroupMapping.setAuditGroupCode(map.get("AuditGroupCode"));
			maintEntityAuditSubgroupMapping.setAuditSubGroupCode(map.get("AuditSubGroupCode"));
			maintEntityAuditSubgroupMappingDao.update(maintEntityAuditSubgroupMapping);
			
		}
	}
	
	public void deleteUserMapping(Integer legalEntityCode, String maker, String userId, String deptCode, HashMap<String, String> map, String saveOrUpdate ) {
		Map<String, Object> properties=new HashMap<String,Object>();
		properties.put("legalEntityCode", legalEntityCode);
		properties.put("mappingType", "U");
		properties.put("id", userId);
		
		List<MaintEntityAuditSubgroupMapping> maintEntityAuditSubgroupMappings = 
				maintEntityAuditSubgroupMappingDao.getEntitiesByMatchingProperties(MaintEntityAuditSubgroupMapping.class, properties);
		logger.info("Size bfr delete .. "+ maintEntityAuditSubgroupMappings.size());
		
		for(MaintEntityAuditSubgroupMapping maintEntityAuditSubgroupMapping : maintEntityAuditSubgroupMappings) {
			maintEntityAuditSubgroupMappingDao.delete(maintEntityAuditSubgroupMapping);
		}
	}

	@Override
	public ExcelUploadDataStatus uploadEntityExcel(String userId, String dataType, CommonsMultipartFile file, Integer legalEntityCode) {
		Integer errorCount = 0;
		Integer successCount = 0;
		excelUploadDataStatusDao.updatePreviousExcelUploadObject();
		ExcelUploadDataStatus excelUploadDataStatus = new ExcelUploadDataStatus();
		Date currentDate=new Timestamp(System.currentTimeMillis());
//		String excelUploadDocumentId = legalEntityCode + file.getOriginalFilename() + userId + currentDate;
		logger.info(" uploadEntityExcel .. " + legalEntityCode + " :: " + dataType + " :: " + file.getOriginalFilename() + " :: " + userId);
		try {
			XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
			logger.info("workbook .. "+workbook);
			XSSFSheet sheet = workbook.getSheetAt(0);
			if (dataType.equalsIgnoreCase("entity")) {
				int rowIndex = 0;
				//delete all before saving to db
				excelMaintEntityDao.deletePreviousExcelEntityData();
				
				Iterator<Row> rowIterator = sheet.iterator();				
		        while (rowIterator.hasNext()) {
		        	Row row = rowIterator.next(); 
				
				//for (Row row : sheet) {
					logger.info("rowIndex .."+rowIndex);
					if (rowIndex == 0 || rowIndex == 1) {
						rowIndex++;
						continue;
					}
					logger.info("before reading the data ..");
					String levelCode = readCellValue(row.getCell(1));
					logger.info("levelCode .."+ row.getCell(1)+" :: "+levelCode);
					String parentUnitCode = readCellValue(row.getCell(2));
					String unitCode = readCellValue(row.getCell(3));
					logger.info("unitCode .."+ unitCode+"::"+unitCode.trim()+"::");
					if(!unitCode.trim().equals("")) {
						String unitName = readCellValue(row.getCell(4));
						String address1 = readCellValue(row.getCell(5));
						String address2 = readCellValue(row.getCell(6));
						String address3 = readCellValue(row.getCell(7));
						String city = readCellValue(row.getCell(8));
						logger.info("city .."+ row.getCell(8)+" :: "+city);
						Integer pincode =000000;
						logger.info(" .."+ readCellValue(row.getCell(9)));
						try {
							if(readCellValue(row.getCell(9))!=" ") pincode = Integer.valueOf(readCellValue(row.getCell(9)));
						}catch(Exception e) {logger.error("Error .." + e.getMessage() + " :: " + e.getCause());}
						logger.info("pincode .."+ row.getCell(9)+" :: "+pincode);
						String district = readCellValue(row.getCell(10));
						String state = readCellValue(row.getCell(11));
						String country = readCellValue(row.getCell(12));
						String contactPerson = readCellValue(row.getCell(13));
						logger.info("contactPerson .."+ row.getCell(13)+" :: "+contactPerson);
						String landline1 = readCellValue(row.getCell(14));
						String landline2 = readCellValue(row.getCell(15));
						String mobile1 = readCellValue(row.getCell(16));
						String mobile2 = readCellValue(row.getCell(17));
						String mailid = readCellValue(row.getCell(18));
						logger.info("mailid .."+ row.getCell(18)+" :: "+mailid);
						String uCriticality = readCellValue(row.getCell(19));
						String unitType = readCellValue(row.getCell(20));
						String classification = readCellValue(row.getCell(21));
						String weeklyHoliday = readCellValue(row.getCell(22));
						String fortnightlyHoliday = readCellValue(row.getCell(23));
						logger.info("fortnightlyHoliday .."+ row.getCell(23)+" :: "+fortnightlyHoliday);
						Date unitOpenDate=null;
						try {
							unitOpenDate = new SimpleDateFormat("dd-MMM-yyyy").parse(String.valueOf(row.getCell(24)));
						}catch(Exception e) {logger.error("Error .." + e.getMessage() + " :: " + e.getCause());}
						
						String unitHeadUser = readCellValue(row.getCell(25));
						String typeOfEntity = readCellValue(row.getCell(27));
						String entityStatus = readCellValue(row.getCell(28));
						logger.info("entityStatus .."+ row.getCell(28)+" :: "+entityStatus);
						String error = "";
						int errorFlag = 0;
						logger.info("Values .."+ levelCode+" :: "+parentUnitCode+" :: "+unitCode+" :: "+unitName);
						if(maintEntityDao.isMaintEntity(legalEntityCode, unitCode)) {
							error += "<Unit Code Already Exists>";
							errorFlag = 1;
						}
						if (city != null && maintAddressDao.getCity(legalEntityCode, city) == null) {
							error += "<City Not Found>";
							errorFlag = 1;
						}
						if (district != null && maintAddressDao.getDistrict(legalEntityCode, district) == null) {
							error += "<District Not Found>";
							errorFlag = 1;
						}
						if (state != null && maintAddressDao.getState(legalEntityCode, state) == null) {
							error += "<State Not Found>";
							errorFlag = 1;
						}
						if (country != null && maintAddressDao.getCountry(legalEntityCode, country) == null) {
							error += "<Country Not Found>";
							errorFlag = 1;
						}
						if(!BankAuditUtil.isEmailAddressValid(mailid)) {
							error += "<Email Address is Invalid>";
							errorFlag = 1;
						}
						if (unitType != null && generalParameterDao.getParameterValue(legalEntityCode, "UNIT_TYPE", null, unitType) == null) {
							error += "<Unit Type Not Found>";
							errorFlag = 1;
						}
						if (classification != null && generalParameterDao.getParameterValue(legalEntityCode, "CLASSIFICATION", null, classification) == null) {
							error += "<Classification Not Found>";
							errorFlag = 1;
						}
						if (weeklyHoliday != null && generalParameterDao.getParameterValue(legalEntityCode, "FORTNIGHT", null, weeklyHoliday) == null) {
							error += "<Weekly Holiday Not Found>";
							errorFlag = 1;
						}
						if (fortnightlyHoliday != null && generalParameterDao.getParameterValue(legalEntityCode, "FORTNIGHT", null, fortnightlyHoliday) == null) {
							error += "<Forthnight Holiday Not Found>";
							errorFlag = 1;
						}
						if (typeOfEntity != null && generalParameterDao.getParameterValue(legalEntityCode, "TYPE_OF", "ENTITY", typeOfEntity) == null) {
							error += "<Type of Entity Not Found>";
							errorFlag = 1;
						}
						if (entityStatus != null && generalParameterDao.getParameterValue(legalEntityCode, "ENTITY_STATUS", null, entityStatus) == null) {
							error += "<Entity Status Not Found>";
							errorFlag = 1;
						}
						ExcelMaintEntity excelMaintEntity = new ExcelMaintEntity();
						if (errorFlag == 0) {
							successCount += 1;
							excelMaintEntity.setStatus("Success");
						} else {
							errorCount += 1;
							excelMaintEntity.setStatus("Failed");
						}
						// verify unitcode in maint_entity table-> if present delete To do
						excelMaintEntity.setLegalEntityCode(legalEntityCode);
						excelMaintEntity.setLevelCode(levelCode);
						excelMaintEntity.setParentUnitCode(parentUnitCode);
						excelMaintEntity.setUnitCode(unitCode);
						excelMaintEntity.setUnitName(unitName);
						excelMaintEntity.setAddress1(address1);
						excelMaintEntity.setAddress2(address2);
						excelMaintEntity.setAddress3(address3);
						excelMaintEntity.setCity(city);
						excelMaintEntity.setPincode(pincode);
						excelMaintEntity.setDistrict(district);
						excelMaintEntity.setState(state);
						excelMaintEntity.setCountry(country);
						excelMaintEntity.setContactPerson(contactPerson);
						excelMaintEntity.setLandline1(landline1);
						excelMaintEntity.setLandline2(landline2);
						excelMaintEntity.setMobile1(mobile1);
						excelMaintEntity.setMobile2(mobile2);
						excelMaintEntity.setMailid(mailid);
						excelMaintEntity.setuCriticality(uCriticality);
						excelMaintEntity.setUnitType(unitType);
						excelMaintEntity.setClassification(classification);
						excelMaintEntity.setWeeklyHoliday(weeklyHoliday);
						excelMaintEntity.setFortnightlyHoliday(fortnightlyHoliday);
						if(unitOpenDate!=null) {
							Calendar cal = Calendar.getInstance();
							cal.setTime(unitOpenDate);
							unitOpenDate = cal.getTime();
							excelMaintEntity.setUnitOpenDate(unitOpenDate);
						}
						excelMaintEntity.setUnitHeadUser(unitHeadUser);
	//					Date unitHeadInceptionDate = new SimpleDateFormat("dd-MMM-yyyy").parse(row.getCell(26) == null || row.getCell(26).getCellType() == Cell.CELL_TYPE_BLANK ? String.valueOf(new Date()) : String.valueOf(row.getCell(26)));
	//					Calendar cal1 = Calendar.getInstance();
	//					cal1.setTime(unitHeadInceptionDate);
	//					unitHeadInceptionDate = cal1.getTime();
	//					excelMaintEntity.setUnitHeadInceptionDate(unitHeadInceptionDate);
						excelMaintEntity.setTypeOfEntity(typeOfEntity);
						excelMaintEntity.setEntityStatus(entityStatus);
						excelMaintEntity.setError(error);
						excelMaintEntityDao.save(excelMaintEntity);
		        	}
				}

			}
			logger.info("After reading the data ....");
//			excelUploadDataStatus.setUploadId(excelUploadDocumentId);
			excelUploadDataStatus.setDataType(dataType);
			excelUploadDataStatus.setFileName(file.getOriginalFilename());
			excelUploadDataStatus.setUploadedBy(userId);
			excelUploadDataStatus.setUploadedTime(currentDate);
			excelUploadDataStatus.setMaker(userId);
			excelUploadDataStatus.setSuccessCount(successCount);
			excelUploadDataStatus.setFailureCount(errorCount);
			excelUploadDataStatus.setTotalCount(successCount + errorCount);
			String pattern = "yyyyMMddHHmmssSSS";
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
			String referencePath =  BankAuditUtil.uploadMaterial(file, BankAuditConstant.DIR_DOC_UPLOAD, simpleDateFormat.format(new Date())+"_"+file.getOriginalFilename()+"_"+userId);

			excelUploadDataStatus.setFilePath(referencePath);
			excelUploadDataStatus.setDeleted(false);
			excelUploadDataStatusDao.save(excelUploadDataStatus);

		} catch (Exception e) {
			logger.error("Error .." + e.getMessage() + " :: " + e.getCause());
			e.printStackTrace(System.out);
		}
		return excelUploadDataStatus;
	}

	@Override
	public ByteArrayOutputStream exportUploadedDataToExcel(String legalEntityCodeStr, String userId, String fileName) {
		try {
			XSSFWorkbook workbook = new XSSFWorkbook();
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			List<ExcelMaintEntity> excelMaintEntityList = excelMaintEntityDao.getExcelMaintData(legalEntityCodeStr, userId);
			XSSFSheet sheet = workbook.createSheet(fileName +"_"+legalEntityCodeStr);

			int rowNumber=0;
			XSSFRow rowHeader = sheet.createRow(0);
			rowHeader.createCell(0).setCellValue("legal_entity_code");
			rowHeader.createCell(1).setCellValue("level_code");
			rowHeader.createCell(2).setCellValue("parent_unit_code");
			rowHeader.createCell(3).setCellValue("unit_code");
			rowHeader.createCell(4).setCellValue("unit_name");
			rowHeader.createCell(5).setCellValue("address1");
			rowHeader.createCell(6).setCellValue("address2");
			rowHeader.createCell(7).setCellValue("address3");
			rowHeader.createCell(8).setCellValue("city");
			rowHeader.createCell(9).setCellValue("pincode");
			rowHeader.createCell(10).setCellValue("district");
			rowHeader.createCell(11).setCellValue("state");
			rowHeader.createCell(12).setCellValue("country");
			rowHeader.createCell(13).setCellValue("contact_person");
			rowHeader.createCell(14).setCellValue("landline1");
			rowHeader.createCell(15).setCellValue("landline2");
			rowHeader.createCell(16).setCellValue("mobile1");
			rowHeader.createCell(17).setCellValue("mobile2");
			rowHeader.createCell(18).setCellValue("mailid");
			rowHeader.createCell(19).setCellValue("u_criticality");
			rowHeader.createCell(20).setCellValue("unit_type");
			rowHeader.createCell(21).setCellValue("classification");
			rowHeader.createCell(22).setCellValue("weekly_holiday");
			rowHeader.createCell(23).setCellValue("fortnightly_holiday");
			rowHeader.createCell(24).setCellValue("unit_open_date");
			rowHeader.createCell(25).setCellValue("unit_head_user");
			rowHeader.createCell(26).setCellValue("unit_head_inception_date");
			rowHeader.createCell(27).setCellValue("type_of_entity");
			rowHeader.createCell(28).setCellValue("entity_status");
			rowHeader.createCell(29).setCellValue("status");
			rowHeader.createCell(30).setCellValue("error");

			int rowIdx = 1;
			for (ExcelMaintEntity excelMaintEntity : excelMaintEntityList) {
				Row row = sheet.createRow(rowIdx++);

				row.createCell(0).setCellValue(excelMaintEntity.getLegalEntityCode());
				row.createCell(1).setCellValue(excelMaintEntity.getLevelCode());
				row.createCell(2).setCellValue(excelMaintEntity.getParentUnitCode());
				row.createCell(3).setCellValue(excelMaintEntity.getUnitCode());
				row.createCell(4).setCellValue(excelMaintEntity.getUnitName());
				row.createCell(5).setCellValue(excelMaintEntity.getAddress1());
				row.createCell(6).setCellValue(excelMaintEntity.getAddress2());
				row.createCell(7).setCellValue(excelMaintEntity.getAddress3());
				row.createCell(8).setCellValue(excelMaintEntity.getCity());
				row.createCell(9).setCellValue(excelMaintEntity.getPincode());
				row.createCell(10).setCellValue(excelMaintEntity.getDistrict());
				row.createCell(11).setCellValue(excelMaintEntity.getState());
				row.createCell(12).setCellValue(excelMaintEntity.getCountry());
				row.createCell(13).setCellValue(excelMaintEntity.getContactPerson());
				row.createCell(14).setCellValue(excelMaintEntity.getLandline1());
				row.createCell(15).setCellValue(excelMaintEntity.getLandline2());
				row.createCell(16).setCellValue(excelMaintEntity.getMobile1());
				row.createCell(17).setCellValue(excelMaintEntity.getMobile2());
				row.createCell(18).setCellValue(excelMaintEntity.getMailid());
				row.createCell(19).setCellValue(excelMaintEntity.getuCriticality());
				row.createCell(20).setCellValue(excelMaintEntity.getUnitType());
				row.createCell(21).setCellValue(excelMaintEntity.getClassification());
				row.createCell(22).setCellValue(excelMaintEntity.getWeeklyHoliday());
				row.createCell(23).setCellValue(excelMaintEntity.getFortnightlyHoliday());
				row.createCell(24).setCellValue(excelMaintEntity.getUnitOpenDate());
				row.createCell(25).setCellValue(excelMaintEntity.getUnitHeadUser());
//				row.createCell(26).setCellValue(excelMaintEntity.getUnitHeadInceptionDate());
				row.createCell(27).setCellValue(excelMaintEntity.getTypeOfEntity());
				row.createCell(28).setCellValue(excelMaintEntity.getEntityStatus());
				row.createCell(29).setCellValue(excelMaintEntity.getStatus());
				row.createCell(30).setCellValue(excelMaintEntity.getError());
			}
			workbook.write(out);
			out.toByteArray();
			return out;

		} catch ( Exception ex ) {
			System.out.println("Exception" + ex);
		}
		return null;
	}

	/*********** Method for Update Plan ********************/
	@Override
	public String updatePlanFromExcel(Integer legalEntityCode, String maker, CommonsMultipartFile planFile)  {
		
		try{
			logger.info(" updatePlanFromExcel .. "+legalEntityCode +" :: "+maker +" :: "+planFile.getOriginalFilename() );
			String[] planColumnHeader = { "Sl. No.","Inspection Type","Plan Id","Inspection Id","Unit Name","Plan End Date",
					"Schedule Start Date","Schedule End Date","PIO","AIO1","AIO2","AIO3","Reason for Failure"};			
			XSSFWorkbook validatedPlanExcelSheet = new XSSFWorkbook();
			XSSFSheet planSheet = validatedPlanExcelSheet.createSheet("user"); 
			Row planRowHead = planSheet.createRow(0);	
			for (int col = 0; col < 11 ; col++) {
		        Cell cell = planRowHead.createCell(col);
		        cell.setCellValue(planColumnHeader[col]);
		    }		
			
			XSSFWorkbook planExcelBook = new XSSFWorkbook(planFile.getInputStream());
			XSSFSheet worksheet = planExcelBook.getSheetAt(0);
			Iterator<Row> iterator = worksheet.iterator(); 	
			int rowIndex = 1; int successCnt=0; int failureCnt=0;       
			FormulaEvaluator formulaEvaluator=planExcelBook.getCreationHelper().createFormulaEvaluator(); 
			DateFormat inputFormatter = new SimpleDateFormat("dd-MM-yyyy");
		    			
			Map<String, String> unitCodeMap = maintEntityService.getUnitCodes(legalEntityCode, maker, null);
			List<AuditSchedule> lstAudits=myAuditService.getAuditSchedules(legalEntityCode, null); // auditTypeCode =null
			List<AuditPlan> lstPlans=auditPlanDao.getAuditPlans(legalEntityCode, null); // auditTypeCode =null
			List<String> unitsBelongsTo=maintEntityService.getSubBranchesGyUserIdOrUnitId(legalEntityCode,"user", maker); // to get the Units for which the Maker allowed to plan
			List<MaintAuditTypeDesc> lstAuditTypes=maintAuditTypeDescService.getMaintAuditTypeDescByLegalEntityCode(legalEntityCode); // to get all the Inspection types
			List<UserRoleMapping> auditUsers=userDao.getUsersWithRole(legalEntityCode, null);  // G8-PIO and G2-AIO
			List<MaintEntityAuditSubGroupMappingDto> unitAdtTypeLst=maintEntityAuditSubgroupMappingService.getEntityAuditTypeMappingDtls(legalEntityCode, "E", "A");  // mappingType -> E
			
			Map<String, String> planAuditTypeMap=lstPlans.stream().collect(Collectors.toMap(AuditPlan::getPlanId, AuditPlan::getAuditTypeCode));
			Map<String, String> auditPlanMap=lstAudits.stream().collect(Collectors.toMap(AuditSchedule::getAuditId, AuditSchedule::getPlanId));
			Map<String, String> auditUnitMap=lstAudits.stream().collect(Collectors.toMap(AuditSchedule::getAuditId, AuditSchedule::getUnitId));
			Map<String, String> auditTypeSlNoMap=lstAuditTypes.stream().collect(Collectors.toMap(MaintAuditTypeDesc::getAuditTypeCode, MaintAuditTypeDesc::getSlNo));
			logger.info("planAuditTypeMap .."+planAuditTypeMap);
			logger.info("auditPlanMap .."+auditPlanMap);
			logger.info("auditUnitMap .."+auditUnitMap);
			logger.info("unitCodeMap .."+unitCodeMap);
			
			logger.info("============================================================= RECORD PROCESSING, STARTS HERE =======================================================");
			while (iterator.hasNext()) {
				
	            Row currentPlanRow = iterator.next();
	            logger.info("Row num .. "+currentPlanRow.getRowNum());
	            
	            if(currentPlanRow.getRowNum() ==0){continue;}
	            // Iterator<Cell> cellUserIterator = currentUserRow.iterator();
	            
	            AuditPlanDto apDto=new AuditPlanDto();
	            Integer slNo = null; 
	            String auditTypeCode = null; 
	            String planId = null;
	            String auditId = null;
	            String unitCode = null;
	            String parentUnitCode = null;           
	            String planEndDate  = null; //shortName
	            String scheduleStartDate = null;
	            String scheduleEndDate = null;  
	            String teamLead = null;  String teamMember1 = null, teamMember2 = null, teamMember3 = null;
	            StringBuilder reasonforFailure =new StringBuilder();
	            boolean schStDt=false, schEndDt=false, plnEndDt=false;
	            boolean planIdIsNull=true, auditIdIsNull=true, planEndDtIsNull=true;
	            
	            String userType=null; // New or Old user
	            
	            // int cellIndex=0;                
	            //while (cellUserIterator.hasNext()) {	
	              //Cell currentCell = cellUserIterator.next();	 
		        for(Cell currentCell: currentPlanRow) {
		             int cellIndex = currentCell.getColumnIndex();
		             logger.info("CELL TYPE ======:: "+cellIndex+" ==> "+currentCell.CELL_TYPE_NUMERIC );
		            switch(formulaEvaluator.evaluateInCell(currentCell).getCellType())  {  
		              case Cell.CELL_TYPE_NUMERIC:   			               
		            	   if(cellIndex==1) auditTypeCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==2) planId = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==3) auditId = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==4) unitCode = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==5) planEndDate = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==6) scheduleStartDate = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==7) scheduleEndDate = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==8) teamLead = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==9) teamMember1 = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==10) teamMember2 = String.valueOf(currentCell.getNumericCellValue());
			               if(cellIndex==11) teamMember3 = String.valueOf(currentCell.getNumericCellValue());
			               break;
		            
		               case Cell.CELL_TYPE_STRING:    //field that represents string cell type  
		            	   //if(cellIndex==0) slNo = currentCell.getStringCellValue();
		            	   if(cellIndex==1) auditTypeCode = currentCell.getStringCellValue();
			               if(cellIndex==2) planId = currentCell.getStringCellValue();
			               if(cellIndex==3) auditId = currentCell.getStringCellValue();
			               if(cellIndex==4) unitCode = currentCell.getStringCellValue();
			               if(cellIndex==5) planEndDate = currentCell.getStringCellValue();
			               if(cellIndex==6) scheduleStartDate = currentCell.getStringCellValue();
			               if(cellIndex==7) scheduleEndDate = currentCell.getStringCellValue();
			               if(cellIndex==8) teamLead = currentCell.getStringCellValue();
			               if(cellIndex==9) teamMember1 = currentCell.getStringCellValue();
			               if(cellIndex==10) teamMember2 = currentCell.getStringCellValue();
			               if(cellIndex==11) teamMember3 = currentCell.getStringCellValue();
		            	   break;  
		            }
		                 
		            //cellIndex++;
		        }slNo=rowIndex;
		        apDto.setAuditTypeCode(auditTypeCode);
		        apDto.setPlanId(planId);  apDto.setAuditId(auditId);  apDto.setUnitId(unitCode);  apDto.setPlanMaker(maker);
		        apDto.setPlanEndDate(planEndDate); apDto.setScheduleStartDate(scheduleStartDate); apDto.setScheduleEndDate(scheduleEndDate);
		        apDto.setTeamLead(teamLead);  apDto.setTeamMember1(teamMember1);  apDto.setTeamMember2(teamMember2);  apDto.setTeamMember3(teamMember3);
		        apDto.setAuditTypeSlNoMap(auditTypeSlNoMap);
		        	        
	            logger.info(" slNo :: "+slNo +" AuditType:: "+ auditTypeCode+" :: "+planId + " :: "+ auditId +" UnitCode:: "+unitCode );
	            logger.info(" PlanEndDate :: "+planEndDate +" :: "+ scheduleStartDate+" :: "+scheduleEndDate +" :: "+teamLead+" :: "+teamMember1+" :: "+teamMember2+" :: "+teamMember3);
	            
	            final String auditTypeCodeFnl=auditTypeCode;
	            final String unitCodeFnl=unitCode;
		        logger.info("ROW INDEX ====================== "+ rowIndex);
		        logger.info("reasonforFailure ====================== "+ reasonforFailure);
		        Row row = planSheet.createRow(rowIndex++);	 
	            
		        if(auditTypeCode!=null  && !auditTypeCode.trim().equals("")){
		        	boolean isValidated = true;
		        	try {
			            //Row row = userSheet.createRow(rowIndex++);			            	
		            	if (lstAuditTypes.stream().anyMatch(at -> at.getAuditTypeCode().equals(auditTypeCodeFnl))) {
		            		logger.info("AuditTypeCode Present.");
		            	}else {
			            	isValidated = false;
			            	reasonforFailure.append(" \n Inspection type "+auditTypeCode+" not present.");
			            }
		            	
		            	// ********************************* Plan Id validation =====================================
			            if(planId != null && !"".equalsIgnoreCase(planId)){
			            	planIdIsNull=false;
				            if(!planAuditTypeMap.get(planId).equalsIgnoreCase(auditTypeCode)) {
				            	isValidated = false;
				            	reasonforFailure.append(" \n Plan id mentioned is used for different Inspection type.");
				            }else if(planEndDate != null && !"".equalsIgnoreCase(planEndDate)) {
				            	isValidated = false;
				            	reasonforFailure.append(" \n Both Plan id and Plan end date shouldn't be present.");
				            }else {
				            	apDto=auditPlanSerive.getPlanDetailsByPlanId(legalEntityCode,auditTypeCode, planId, apDto);	
				            }
				        }else {
				        	logger.info("rowId .."+rowIndex+"planId .."+planId+" planEndDate.."+planEndDate);
				            //logic to Get or Create a new Plan
				        	if(planEndDate != null && !"".equalsIgnoreCase(planEndDate)) {	
				        		if(!BankAuditUtil.parseStringToDateddMMyyyyChk(planEndDate).equalsIgnoreCase("valid")) {
				        			logger.info("Invalid .. ");
				        			isValidated = false;
					            	reasonforFailure.append(" \n Plan end date is not a valid date.");
				        		}				        		
				        	}else {}
				        	logger.info("isValidated .."+isValidated);
				        	if(isValidated) {
				        		apDto=auditPlanSerive.getPlanDetails(legalEntityCode, apDto);
				        	}
				        }
		            	
			            // ****************************** Audit Id validation =======================================
			            if(auditId != null && !"".equalsIgnoreCase(auditId)){
			            	auditIdIsNull=false;
			            	if(planId != null && !"".equalsIgnoreCase(planId)){
			            		 if(!auditPlanMap.get(auditId).equalsIgnoreCase(planId)) {
						            	isValidated = false;
						            	reasonforFailure.append(" \n Audit id mentioned is mapped to "+auditPlanMap.get(auditId) +" Plan id");
						            }
			            	}else {
			            		logger.info("PlanId::"+planId+" auditId::"+auditId+"::" );
			            		isValidated = false;
			            		reasonforFailure.append(" \n Plan id shouldn't be left blank, when Audit id is present.");
			            	}			            	
				        }else {
				        	//logic to create a new Audit
				        }
			            
			            // **************************** Unit Code validation =========================================
			            if(unitCode != null && !"".equalsIgnoreCase(unitCode)){
			            	if(!unitCodeMap.containsKey(unitCode)){
			            		isValidated = false;
				            	reasonforFailure.append(" \n The Unit code mentioned is not present in the system.");
			            	}else if(!unitsBelongsTo.contains(unitCode)) {
			            		isValidated = false;
				            	reasonforFailure.append(" \n The user in not entitled to Plan for this Unit.");
			            	}else if (!unitAdtTypeLst.stream().anyMatch(um -> um.getEntityOrUserName().equals(unitCodeFnl) && um.getAuditTypeCode().equals(auditTypeCodeFnl) )) {
			            		isValidated = false;
				            	reasonforFailure.append(" \n The Unit is not mapped to the "+auditTypeCode +" inspection type");
			            	}else if(auditId != null && !"".equalsIgnoreCase(auditId)){
			            		 if(!auditUnitMap.get(auditId).equalsIgnoreCase(unitCode)) {
						            	isValidated = false;
						            	reasonforFailure.append(" \n Audit id mentioned is mapped to "+auditPlanMap.get(auditId) +" Unit.");
						            }
			            	}			            	
				        }else {
				        	isValidated = false;
		            		reasonforFailure.append(" \n Unit Code shouldn't be left blank.");
				        }
			            
			            // *********************** Plan End date validation ====================================
			            if(planEndDate != null && !"".equalsIgnoreCase(planEndDate)){
			            	planEndDtIsNull=false;
			            	if(BankAuditUtil.parseStringToDateddMMyyyyChk(planEndDate).equalsIgnoreCase("valid")) {
			            		plnEndDt=true;
				            	if(planId != null && !"".equalsIgnoreCase(planId)){			            		 
				            	}
			            	}else {
			            		isValidated = false;
			            		reasonforFailure.append(" \n Plan End Date format should be in dd-MM-yyyy.");
			            	}
				        }else {
				        	//logic to create a new Audit
				        }
			            
			            // ************************ Schedule Start date validation ================================
			            if(scheduleStartDate != null && !"".equalsIgnoreCase(scheduleStartDate)){
			            	if(BankAuditUtil.parseStringToDateddMMyyyyChk(scheduleStartDate).equalsIgnoreCase("valid")) {
			            		schStDt=true;
			            	}else {
			            		isValidated = false;
			            		reasonforFailure.append(" \n Schedule Start Date format should be in dd-MM-yyyy.");
			            	}		            	
				        }else {
				        	isValidated = false;
		            		reasonforFailure.append(" \n Schedule start date shouldn't be left blank");
				        }
			            
			            // ************************ Schedule End date validation ==================================
			            if(scheduleEndDate != null && !"".equalsIgnoreCase(scheduleEndDate)){
			            	if(BankAuditUtil.parseStringToDateddMMyyyyChk(scheduleStartDate).equalsIgnoreCase("valid")) {
			            		schEndDt=true;
			            	}else {
			            		isValidated = false;
			            		reasonforFailure.append(" \n Schedule End Date format should be in dd-MM-yyyy.");
			            	}	 		            	
				        }else {
				        	isValidated = false;
		            		reasonforFailure.append(" \n Schedule end date shouldn't be left blank");
				        }
			            
			            // *********************** PIO validation ==================================================
			            final String teamLeadF=teamLead;
			            if(teamLead != null && !"".equalsIgnoreCase(teamLead)){
			            	if (!auditUsers.stream().anyMatch(au -> au.getUserRoleId().equals("G8") && au.getUserId().equals(teamLeadF) )) {
			            		isValidated = false;
			            		reasonforFailure.append(" \n User mentioned is not a PIO. ");
			            	}		            	
				        }
			            
			            // AIO-1 validation
			            final String teamMember1F=teamMember1;
			            if(teamMember1 != null && !"".equalsIgnoreCase(teamMember1)){
			            	if (!auditUsers.stream().anyMatch(au -> au.getUserRoleId().equals("G2") && au.getUserId().equals(teamMember1F) )) {
			            		isValidated = false;
			            		reasonforFailure.append(" \n User mentioned is not an AIO. ");
			            	}		            	
				        }
			            
			            // AIO-2 validation
			            final String teamMember2F=teamMember2;
			            if(teamMember2 != null && !"".equalsIgnoreCase(teamMember2)){
			            	if (!auditUsers.stream().anyMatch(au -> au.getUserRoleId().equals("G2") && au.getUserId().equals(teamMember2F) )) {
			            		isValidated = false;
			            		reasonforFailure.append(" \n User mentioned is not an AIO. ");
			            	}		            	
				        }
			            
			            // AIO-3 validation
			            final String teamMember3F=teamMember3;
			            if(teamMember3 != null && !"".equalsIgnoreCase(teamMember3)){
			            	if (!auditUsers.stream().anyMatch(au -> au.getUserRoleId().equals("G2")  && au.getUserId().equals(teamMember3F) )) {
			            		isValidated = false;
			            		reasonforFailure.append(" \n User mentioned is not an AIO.");
			            	}		            	
				        }	   
			            
			            // ************************ Verify the Schedule start and end dates
			            if(schStDt && schEndDt) {
			            	/*int cmp=BankAuditUtil.compareDates(scheduleStartDate, scheduleEndDate);
			            	if(cmp >0) {
			            		isValidated = false;
			            		reasonforFailure.append("\n Schedule start date should be less than or equal to Schedule end date.");
			            	}*/
			            	
			            	// Date comparisons 
			            	if(isValidated) {
			            		// Date1 > Date2 =>1, Date1 <Date2 =>-1, Date1=Date2 =>0
			            		Date schStartDateD= BankAuditUtil.parseStringToDateddMMyyyy(scheduleStartDate);
			            		Date schEndDateD= BankAuditUtil.parseStringToDateddMMyyyy(scheduleEndDate);
			            		Date planStartDateD=apDto.getPlanStartDateD();
			            		Date planEndDateD=apDto.getPlanEndDateD();
			            		int cmpSchDt=schStartDateD.compareTo(schEndDateD);  		// Sch Start Date Vs Sch End Date
			            		int cmpPlanSTDtSchST=schStartDateD.compareTo(schEndDateD);  // PlanStartDate Vs Sch Start Date
			            		int cmpSchENDPlanENDDt=schStartDateD.compareTo(schEndDateD);// Sch End Date Vs Plan End Date
			            		
			            		if(cmpSchDt >0) {
				            		isValidated = false;
				            		reasonforFailure.append(" \n Schedule start date should be less than or equal to Schedule end date.");
				            	}
			            		if(cmpPlanSTDtSchST >0) {
				            		isValidated = false;
				            		reasonforFailure.append(" \n Schedule start date should be less than or equal to Plan start date.");
				            	}
			            		if(cmpSchENDPlanENDDt >0) {
				            		isValidated = false;
				            		reasonforFailure.append(" \n Schedule end date should be less than to Plan end date.");
				            	}
			            		
			            		if( planIdIsNull && auditIdIsNull && planEndDtIsNull) {
			            			isValidated = false;
				            		reasonforFailure.append(" \n Plan id, Audit id and Plan end date all shouldn't be null.");
			            		}
			            	}
			            }
		        	}catch(Exception e) {
		        		e.printStackTrace(System.out);
		        		isValidated = false;
	            		reasonforFailure.append(" \n The data for this record is invalid.");
		        	}
		            
		            
	              logger.info(" reasonforFailure ..@End .." +reasonforFailure );
		            
		            /*if(user!=null && 
		            		(fromUnitCode.equalsIgnoreCase(user.getUnitCode()))
		            		&&  fromGrade.equalsIgnoreCase(user.getShortName())
		            		&&  fromMobileNumber.equalsIgnoreCase(user.getMobile1())){
		            	*/
		             if(isValidated){
		            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(slNo);
		            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(auditTypeCode);
		            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(planId);
		            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(auditId);
		            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(unitCode);
		            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(planEndDate);
		            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(scheduleStartDate);
		            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(scheduleEndDate);
		            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(teamLead);
		            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue(teamMember1);
		            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(teamMember2);
		            	row.createCell(11).setCellType(Cell.CELL_TYPE_STRING);row.createCell(11).setCellValue(teamMember3);
		            	row.createCell(12).setCellType(Cell.CELL_TYPE_STRING);row.createCell(12).setCellValue("Success");
		            	row.createCell(13).setCellType(Cell.CELL_TYPE_STRING);row.createCell(13).setCellValue(reasonforFailure.toString());
		            			            			             	
		            	if(auditId != null && !"".equalsIgnoreCase(auditId)) {
		            		auditPlanSerive.updateAuditScheduleAndTeam(legalEntityCode, apDto);
		             	} else {
		             		String auditIdNew=auditPlanSerive.createAndGetAuditSchedule(legalEntityCode,apDto);
		             		apDto.setAuditId(auditIdNew);
		             		if(teamLead != null && !"".equalsIgnoreCase(teamLead)) {
		             			//auditPlanSerive.createAuditTeam(legalEntityCode,apDto);
		             		}
		             	}
		             	
		             	logger.info("user ");
		             			             	
		             	// To map the dept to the new User, as the user not created so need to do the mapping post creation of the User 
		             	/*try {
			             	if("HO".equalsIgnoreCase(unitCode) && "New".equalsIgnoreCase(userType)) {
			             		HashMap<String, String> map= activityProcessFindingMappingService.getAnyOneAuditGrpAndSubGrpForAuditType(legalEntityCode, deptCode, "A");
				            	   if(map !=null) {
				            		   saveOrUpdateUserMapping(legalEntityCode, maker, userId, deptCode, map, "SAVE" );
				            	   }
			             	}
		             	}catch(Exception e) {}*/
		             	// end here
		             	
		             	successCnt++;
		            }else{
		            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(slNo);
		            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(auditTypeCode);
		            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(planId);
		            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(auditId);
		            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(unitCode);
		            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(planEndDate);
		            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(scheduleStartDate);
		            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(scheduleEndDate);
		            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(teamLead);
		            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue(teamMember1);
		            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(teamMember2);
		            	row.createCell(11).setCellType(Cell.CELL_TYPE_STRING);row.createCell(11).setCellValue(teamMember3);
		            	row.createCell(12).setCellType(Cell.CELL_TYPE_STRING);row.createCell(12).setCellValue("Failed");
		            	row.createCell(13).setCellType(Cell.CELL_TYPE_STRING);row.createCell(13).setCellValue(reasonforFailure.toString());
		            	failureCnt++;
		            }  
	            }else{
	            	// If Inspection Type/Audit Type is Blank
	            	row.createCell(0).setCellType(Cell.CELL_TYPE_STRING);row.getCell(0).setCellValue(slNo);
	            	row.createCell(1).setCellType(Cell.CELL_TYPE_STRING);row.createCell(1).setCellValue(auditTypeCode);
	            	row.createCell(2).setCellType(Cell.CELL_TYPE_STRING);row.createCell(2).setCellValue(planId);
	            	row.createCell(3).setCellType(Cell.CELL_TYPE_STRING);row.createCell(3).setCellValue(auditId);
	            	row.createCell(4).setCellType(Cell.CELL_TYPE_STRING);row.createCell(4).setCellValue(unitCode);
	            	row.createCell(5).setCellType(Cell.CELL_TYPE_STRING);row.createCell(5).setCellValue(planEndDate);
	            	row.createCell(6).setCellType(Cell.CELL_TYPE_STRING);row.createCell(6).setCellValue(scheduleStartDate);
	            	row.createCell(7).setCellType(Cell.CELL_TYPE_STRING);row.getCell(7).setCellValue(scheduleEndDate);
	            	row.createCell(8).setCellType(Cell.CELL_TYPE_STRING);row.createCell(8).setCellValue(teamLead);
	            	row.createCell(9).setCellType(Cell.CELL_TYPE_STRING);row.createCell(9).setCellValue(teamMember1);
	            	row.createCell(10).setCellType(Cell.CELL_TYPE_STRING);row.createCell(10).setCellValue(teamMember2);
	            	row.createCell(11).setCellType(Cell.CELL_TYPE_STRING);row.createCell(11).setCellValue(teamMember3);
	            	row.createCell(12).setCellType(Cell.CELL_TYPE_STRING);row.createCell(12).setCellValue("Failed");
	            	row.createCell(13).setCellType(Cell.CELL_TYPE_STRING);row.createCell(13).setCellValue(" Inspection type shoudn't be left blank.");
	            	failureCnt++;	            	
	            }
	            apDto=null;
			 }
			
			logger.info("Success :: "+successCnt +" Failure :: "+failureCnt);
			 //Need to store excel with status 
			 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
			 String uploadFileName = planFile.getOriginalFilename()+"_"+simpleDateFormat.format(new Date())+".xlsx";    		 
			 String uploadPath = BankAuditConfig.getInstance().getConfigValue(BankAuditConstant.DIR_USER_UPLOAD)+ "/"+uploadFileName;
			 
			 FileOutputStream userFileOutputStream = new FileOutputStream(uploadPath);
			 validatedPlanExcelSheet.write(userFileOutputStream);			  
			 userFileOutputStream.flush();
			 userFileOutputStream.close();	
			 
			 planExcelBook.close();
			 validatedPlanExcelSheet.close(); 
			 
			 //************************* Updating the UploadStatus  ****************************/
			 logger.info("Bfr calling the updateUploadStaus Plan .."+ successCnt);
			 updateUploadStaus( legalEntityCode, "Plan",  uploadPath, uploadFileName, maker, rowIndex-1, successCnt, failureCnt);
			 // End Here //
			 logger.info("============================================================= RECORD PROCESSING, END HERE =======================================================");
		} 
		catch (IOException e) {
			e.printStackTrace(System.out);
			return "failure";
		}
		return "success";
	}
	
	
	
	
	
	
	
	
	
	
	
	/************ End, Update Plan *****************/
	
	
	
	
	
	
	
	
	
	
	
	
	private String readCellValue(Cell cell) {
		String cellValue;
		if (cell != null) {
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					cellValue = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					cellValue = (int) cell.getNumericCellValue() + "";
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					cellValue = cell.getBooleanCellValue() + "";
					break;
				default:
					cell.setCellType(Cell.CELL_TYPE_STRING);
					cellValue = cell.getStringCellValue();
					break;
			}
			return cellValue.trim();
		} else {
			return null;
		}
	}
	
	@Override
	public List<MaintTemplateDtls> getTemplateDetails(Integer legalEntityCode, String role) {
		return uploadDownloadDao.getTemplateDetails(legalEntityCode, role);
	}

	@Override
	public String getTemplateReferencePath(Integer legalEntityCode, String templateCode){	
		return uploadDownloadDao.getTemplateReferencePath(legalEntityCode, templateCode);
	}
	
	
}
