package com.bankaudit.rest.process.service;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
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.ChecklistDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.process.model.AuditLevelDocumentDetails;
import com.bankaudit.process.model.AuditObservation;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.ChecklistOfflineInspection;
import com.bankaudit.process.model.IfcEnsureBaasDataTmp;
import com.bankaudit.process.model.IfcRatingDataTmp;
import com.bankaudit.report.v3.dao.AuditBookReportDaoV3;
import com.bankaudit.rest.process.dao.AuditObservationDao;
import com.bankaudit.rest.process.dao.AuditRatingDao;
import com.bankaudit.rest.process.dao.IfcEnsureBaasDataDao;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.spring.config.BankAuditConfig;
import com.bankaudit.util.BankAuditUtil;
import com.opencsv.CSVReader;

import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.ExcelDataSource;

@Service("ifcEnsureBaasDataService")
@Transactional("transactionManager")
public class IfcEnsureBaasDataServiceImpl  implements IfcEnsureBaasDataService{
	
	@Autowired 
	BusinessDateService businessDateService;
	
	@Autowired 
	IfcEnsureBaasDataDao ifcEnsureBaasDataDao;
	
	@Autowired 
	AuditObservationDao auditObservationDao;
	
	@Autowired
	AuditBookReportDaoV3 auditBookReportDaoV3;

	@Autowired
	ChecklistDao checklistDao;
	
	@Autowired
	AuditRatingDao auditRatingDao;
	
	static final Logger logger = Logger.getLogger(IfcEnsureBaasDataServiceImpl.class);

	@Override  
	public String uploadIndividualAuditLevelCSVDocument( AuditLevelDocumentDetails auditLevelDocumentDetails, CommonsMultipartFile[] files, String fileName)  {
		
		Integer uploadId=0;		 
		
		for (CommonsMultipartFile commonsMultipartFile : files) {
			AuditLevelDocumentDetails auditLevelDocumentDetailsToSave = new AuditLevelDocumentDetails(); 
			
			try{  
				BeanUtils.copyProperties(auditLevelDocumentDetailsToSave, auditLevelDocumentDetails); 
				String pattern = "yyyyMMddHHmmssSSS"; 
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);  
				String referencePath =  BankAuditUtil.uploadMaterial(commonsMultipartFile, BankAuditConstant.DIR_DOC_UPLOAD, simpleDateFormat.format(new Date())+"_"+fileName);
				auditLevelDocumentDetailsToSave.setReferencePath(referencePath);  
				auditLevelDocumentDetailsToSave.setFileName(commonsMultipartFile.getOriginalFilename()); 
				auditLevelDocumentDetailsToSave.setReferenceNo(ifcEnsureBaasDataDao.getMaxReferenceNumber()); 
				ifcEnsureBaasDataDao.save(auditLevelDocumentDetailsToSave); 
				uploadId = auditLevelDocumentDetailsToSave.getId(); 
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
				System.out.println(e );
				return  BankAuditConstant.FAILED;
			}				
		
			if(uploadId>0){ 
				Integer legalEntityCode = auditLevelDocumentDetailsToSave.getLegalEntityCode();
				String auditId = auditLevelDocumentDetailsToSave.getAuditId();
				String auditTypeCode = auditLevelDocumentDetailsToSave.getAuditTypeCode();
				Integer referenceNo = auditLevelDocumentDetailsToSave.getReferenceNo();
				String unitCode = auditLevelDocumentDetailsToSave.getUnitCode();
				String maker =  auditLevelDocumentDetailsToSave.getMaker();
				Date makerTimestamp = auditLevelDocumentDetailsToSave.getMakerTimestamp();
				
				if(BankAuditConstant.DOCUMENT_TYPE_IR_PART_AB.equalsIgnoreCase(auditLevelDocumentDetailsToSave.getDocumentType())){
		 			try(CSVReader csvReader = new CSVReader(new BufferedReader(new InputStreamReader(commonsMultipartFile.getInputStream())))){ 				 
				        String[] baasData =null;		        
				       
				        while ((baasData = csvReader.readNext()) != null) {
				        	if(baasData[0].startsWith("BOM") || baasData[0].startsWith("BR")){  // if(baasData[0].startsWith("BOM-")){  // Included BR as Branch inspection added
				        		IfcEnsureBaasDataTmp ifcEnsureBaasDataTmp  = new IfcEnsureBaasDataTmp();
				        		
				        		String parameterNameBaas = ifcEnsureBaasDataDao.getBaasParameterName(legalEntityCode, 	auditTypeCode, baasData[0]);
				        		/*if(BankAuditUtil.isEmptyString(parameterNameBaas)){
				        			auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
			    					ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
			    					ifcEnsureBaasDataDao.deleteDataFromTmp(auditLevelDocumentDetailsToSave);
			    					return BankAuditConstant.FAILED; 
				        		}*/
				        		ifcEnsureBaasDataTmp.setLegalEntityCode(legalEntityCode);
				        		ifcEnsureBaasDataTmp.setAuditId(auditId);
				        		ifcEnsureBaasDataTmp.setAuditTypeCode(auditTypeCode);
				        		ifcEnsureBaasDataTmp.setMaker(maker);
				        		ifcEnsureBaasDataTmp.setMakerTimestamp(makerTimestamp);
				        		ifcEnsureBaasDataTmp.setReferenceNo(referenceNo);
				        		ifcEnsureBaasDataTmp.setUnitCode(unitCode);
				        		ifcEnsureBaasDataTmp.setStageOfFile(BankAuditConstant.STAGE_OF_FILE_LATEST);		                	
				        		ifcEnsureBaasDataTmp.setParameterNameBaas(parameterNameBaas);  
				        		ifcEnsureBaasDataTmp.setParameterName(parameterNameBaas);  
			                	ifcEnsureBaasDataTmp.setParameterNameNabard(baasData[0]);
			                	
			                	ifcEnsureBaasDataTmp.setSubmittedValueOldest(baasData[1]);				                	
			                	ifcEnsureBaasDataTmp.setSubmittedValueMiddest(baasData[2]);
			                	ifcEnsureBaasDataTmp.setSubmittedValueLatest(baasData[3]);	
			                	String statusMessage = ifcEnsureBaasDataDao.copyDataToTempFromCSV( ifcEnsureBaasDataTmp);
			                	if(statusMessage.equalsIgnoreCase(BankAuditConstant.FAILED)){
			                		auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
			    					ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
			    					ifcEnsureBaasDataDao.deleteDataFromTmp(auditLevelDocumentDetailsToSave);
			    					return BankAuditConstant.FAILED; 
			                	}
				        	} 	
				        	
				        }			        
					}catch (Exception e) {
						logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
						System.out.println(e );
						return	BankAuditConstant.FAILED;
					}
					
					String status = ifcEnsureBaasDataDao.copyDataToMasterFromTemp(auditLevelDocumentDetailsToSave);
					if(status.equalsIgnoreCase(BankAuditConstant.SUCCESS)){
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.SUCCESS);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						
						//Store SupervisedRating
											
						/*Map<String, String> profileParameterMap =  auditBookReportDaoV3.getProfileParameterMap(legalEntityCode, auditId, auditTypeCode); 
						if(profileParameterMap!=null){
							
							AuditSchedule auditSchedule = auditRatingDao.getAuditByAuditId(legalEntityCode, auditId);							 
							
							if(BankAuditConstant.INSPECTION_TYPE_DCCB.equalsIgnoreCase(auditTypeCode)){
								auditSchedule.setEffectiveRating(profileParameterMap.get("3BOMD638"));		// ABCD .. catagory  
								auditSchedule.setEffectiveRiskPrcntg(profileParameterMap.get("3BOMD637"));   //marks								
								//auditSchedule.setAssessedRiskPrcntg(profileParameterMap.get(""));											
								//auditSchedule.setAssessedRating(profileParameterMap.get(""));
							}else if(BankAuditConstant.INSPECTION_TYPE_StCBI.equalsIgnoreCase(auditTypeCode)){
								auditSchedule.setEffectiveRating(profileParameterMap.get("3BOMS651"));		 // ABCD .. catagory  
								auditSchedule.setEffectiveRiskPrcntg(profileParameterMap.get("3BOMS650"));   //marks								
								//auditSchedule.setAssessedRiskPrcntg(profileParameterMap.get(""));											
								//auditSchedule.setAssessedRating(profileParameterMap.get(""));						
							}else if(BankAuditConstant.INSPECTION_TYPE_RRB.equalsIgnoreCase(auditTypeCode)){
								auditSchedule.setEffectiveRating(profileParameterMap.get("3BOM-652"));		
								auditSchedule.setEffectiveRiskPrcntg(profileParameterMap.get("3BOM-653"));								
								// 652 and 653
								//auditSchedule.setAssessedRiskPrcntg(profileParameterMap.get(""));											
								//auditSchedule.setAssessedRating(profileParameterMap.get(""));						
							}else{
								//SCARDB - audit
							}
							 
							auditRatingDao.flushSession();
							auditRatingDao.saveOrUpdate(auditSchedule);
							 
						}*/
						
						return BankAuditConstant.SUCCESS;
					}else{
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						ifcEnsureBaasDataDao.deleteDataFromTmp(auditLevelDocumentDetailsToSave);
						return BankAuditConstant.FAILED;
					}
				//}else{
				}else if(BankAuditConstant.DOCUMENT_TYPE_RATING_CSV.equalsIgnoreCase(auditLevelDocumentDetailsToSave.getDocumentType())){
					//Read rating from CSV
					logger.info("Inside Rating CSV Upload .. ");
					try(CSVReader csvReader = new CSVReader(new BufferedReader(new InputStreamReader(commonsMultipartFile.getInputStream())))){ 				 
				        String[] ratingData =null;	
				        while ((ratingData = csvReader.readNext()) != null) {				        	   
			        		IfcRatingDataTmp ifcRatingDataTmp  = new IfcRatingDataTmp();	
			        		ifcRatingDataTmp.setLegalEntityCode(legalEntityCode);
			        		ifcRatingDataTmp.setAuditId(auditId);
			        		ifcRatingDataTmp.setAuditTypeCode(auditTypeCode);
			        		ifcRatingDataTmp.setInputBaas(ratingData[0]);
			        		ifcRatingDataTmp.setInputExt(ratingData[0]);
			        		ifcRatingDataTmp.setInputValue(ratingData[1]);			                	 
		                	ifcEnsureBaasDataDao.save(ifcRatingDataTmp);  	
				        }
					}catch (Exception e) {
						logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
						e.printStackTrace(System.out);
                		auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
    					ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
    					ifcEnsureBaasDataDao.deleteRatingDataFromTmp(auditLevelDocumentDetailsToSave);	    					 
						return	BankAuditConstant.FAILED;
					}
										
					//Store Rating data to master table
					String status = ifcEnsureBaasDataDao.copyRatingDataToMasterFromTemp(auditLevelDocumentDetailsToSave);
					if(status.equalsIgnoreCase(BankAuditConstant.SUCCESS)){
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.SUCCESS);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						return BankAuditConstant.SUCCESS; 
					}else{
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						ifcEnsureBaasDataDao.deleteRatingDataFromTmp(auditLevelDocumentDetailsToSave);
						return BankAuditConstant.FAILED;
					}	
				}				
			}
		}
		return  BankAuditConstant.FAILED;
	} 

	@Override  
	public String uploadIndividualAuditLevelPdfDocument( AuditLevelDocumentDetails auditLevelDocumentDetails, CommonsMultipartFile[] files, String fileName)  {
		
		Integer uploadId=0;		 
		
		for (CommonsMultipartFile commonsMultipartFile : files) {
			AuditLevelDocumentDetails auditLevelDocumentDetailsToSave = new AuditLevelDocumentDetails(); 
			
			try{ 
				BeanUtils.copyProperties(auditLevelDocumentDetailsToSave, auditLevelDocumentDetails); 
				String pattern = "yyyyMMddHHmmssSSS";
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern); 
				String referencePath =  BankAuditUtil.uploadMaterial(commonsMultipartFile, BankAuditConstant.DIR_DOC_UPLOAD, simpleDateFormat.format(new Date())+"_"+fileName);
				auditLevelDocumentDetailsToSave.setReferencePath(referencePath); 
				auditLevelDocumentDetailsToSave.setFileName(commonsMultipartFile.getOriginalFilename());
				auditLevelDocumentDetailsToSave.setReferenceNo(ifcEnsureBaasDataDao.getMaxReferenceNumber());
				auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.SUCCESS);
				ifcEnsureBaasDataDao.save(auditLevelDocumentDetailsToSave);
				uploadId = auditLevelDocumentDetailsToSave.getId();
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
				e.printStackTrace(System.out);
				return  BankAuditConstant.FAILED;
			}
			return BankAuditConstant.SUCCESS;
		}
		return  BankAuditConstant.FAILED;
	} 
	
	@Override  
	public String saveFetchedIndividualAuditLevelPdfDocument(AuditLevelDocumentDetails auditLevelDocumentDetails){
		try{
			String referencePath = BankAuditConfig.getInstance().getConfigValue(BankAuditConstant.DIR_DOC_ENSURE_INTERFACE)+"/"+auditLevelDocumentDetails.getFileName(); 
			auditLevelDocumentDetails.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
			auditLevelDocumentDetails.setReferenceNo(ifcEnsureBaasDataDao.getMaxReferenceNumber());
			auditLevelDocumentDetails.setReferencePath(referencePath); 
			auditLevelDocumentDetails.setUploadStatus(BankAuditConstant.SUCCESS);
			ifcEnsureBaasDataDao.save(auditLevelDocumentDetails);
			return BankAuditConstant.SUCCESS;
		}catch (Exception e) {
			logger.error("Error .." + e); 
			return  BankAuditConstant.FAILED;
		}
		 
	}
	
	@Override  
	public String saveFetchedIndividualAuditLevelCSVDocument(AuditLevelDocumentDetails auditLevelDocumentDetails){
	
		AuditLevelDocumentDetails auditLevelDocumentDetailsToSave = new AuditLevelDocumentDetails(); 
		Integer uploadId = 0;
		try{  
			BeanUtils.copyProperties(auditLevelDocumentDetailsToSave, auditLevelDocumentDetails); 
			String referencePath = BankAuditConfig.getInstance().getConfigValue(BankAuditConstant.DIR_DOC_ENSURE_INTERFACE)+"/"+auditLevelDocumentDetails.getFileName(); 
			auditLevelDocumentDetailsToSave.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
			auditLevelDocumentDetailsToSave.setReferenceNo(ifcEnsureBaasDataDao.getMaxReferenceNumber());
			auditLevelDocumentDetailsToSave.setReferencePath(referencePath); 
			auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.STATUS_INACTIVE);			
			ifcEnsureBaasDataDao.save(auditLevelDocumentDetailsToSave); 
			uploadId = auditLevelDocumentDetailsToSave.getId(); 
		}catch (Exception e) {
			logger.error("Error .." + e); 
			return  BankAuditConstant.FAILED;
		}
		
		if(uploadId>0){
			try{			 
				Integer legalEntityCode = auditLevelDocumentDetailsToSave.getLegalEntityCode();
				String auditId = auditLevelDocumentDetailsToSave.getAuditId();
				String auditTypeCode = auditLevelDocumentDetailsToSave.getAuditTypeCode();
				Integer referenceNo = auditLevelDocumentDetailsToSave.getReferenceNo();
				String unitCode = auditLevelDocumentDetailsToSave.getUnitCode();
				String maker =  auditLevelDocumentDetailsToSave.getMaker();
				Date makerTimestamp = auditLevelDocumentDetailsToSave.getMakerTimestamp();
				
				if(BankAuditConstant.DOCUMENT_TYPE_IR_PART_AB.equalsIgnoreCase(auditLevelDocumentDetailsToSave.getDocumentType())){
		 			try(CSVReader csvReader = new CSVReader(new BufferedReader(new InputStreamReader(new FileInputStream(auditLevelDocumentDetailsToSave.getReferencePath()))))){ 				 
				        String[] baasData =null;		        
				       
				        while ((baasData = csvReader.readNext()) != null) {
				        	if(baasData[0].startsWith("BOM") || baasData[0].startsWith("BR")){  // if(baasData[0].startsWith("BOM-")){  // Included BR as Branch inspection added
				        		IfcEnsureBaasDataTmp ifcEnsureBaasDataTmp  = new IfcEnsureBaasDataTmp();
				        		
				        		String parameterNameBaas = ifcEnsureBaasDataDao.getBaasParameterName(legalEntityCode, 	auditTypeCode, baasData[0]);
	 			        		ifcEnsureBaasDataTmp.setLegalEntityCode(legalEntityCode);
				        		ifcEnsureBaasDataTmp.setAuditId(auditId);
				        		ifcEnsureBaasDataTmp.setAuditTypeCode(auditTypeCode);
				        		ifcEnsureBaasDataTmp.setMaker(maker);
				        		ifcEnsureBaasDataTmp.setMakerTimestamp(makerTimestamp);
				        		ifcEnsureBaasDataTmp.setReferenceNo(referenceNo);
				        		ifcEnsureBaasDataTmp.setUnitCode(unitCode);
				        		ifcEnsureBaasDataTmp.setStageOfFile(BankAuditConstant.STAGE_OF_FILE_LATEST);		                	
				        		ifcEnsureBaasDataTmp.setParameterNameBaas(parameterNameBaas);  
				        		ifcEnsureBaasDataTmp.setParameterName(parameterNameBaas);  
			                	ifcEnsureBaasDataTmp.setParameterNameNabard(baasData[0]);
			                	
			                	ifcEnsureBaasDataTmp.setSubmittedValueOldest(baasData[1]);				                	
			                	ifcEnsureBaasDataTmp.setSubmittedValueMiddest(baasData[2]);
			                	ifcEnsureBaasDataTmp.setSubmittedValueLatest(baasData[3]);	
			                	String statusMessage = ifcEnsureBaasDataDao.copyDataToTempFromCSV( ifcEnsureBaasDataTmp);
			                	if(statusMessage.equalsIgnoreCase(BankAuditConstant.FAILED)){
			                		auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
			    					ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
			    					ifcEnsureBaasDataDao.deleteDataFromTmp(auditLevelDocumentDetailsToSave);
			    					return BankAuditConstant.FAILED; 
			                	}
				        	} 			        	
				        }			        
					}catch (Exception e) {
						logger.error("Error .." + e); 
						return	BankAuditConstant.FAILED;
					}
					
					String status = ifcEnsureBaasDataDao.copyDataToMasterFromTemp(auditLevelDocumentDetailsToSave);
					if(status.equalsIgnoreCase(BankAuditConstant.SUCCESS)){
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.SUCCESS);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);					 				
						return BankAuditConstant.SUCCESS;
					}else{
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						ifcEnsureBaasDataDao.deleteDataFromTmp(auditLevelDocumentDetailsToSave);
						return BankAuditConstant.FAILED;
					}			 
				}else if(BankAuditConstant.DOCUMENT_TYPE_RATING_CSV.equalsIgnoreCase(auditLevelDocumentDetailsToSave.getDocumentType())){
					//Read rating from CSV
					logger.info("Inside Rating CSV Upload .. ");
					try(CSVReader csvReader = new CSVReader(new BufferedReader(new InputStreamReader(new FileInputStream(auditLevelDocumentDetailsToSave.getReferencePath()))))){ 				 
				        String[] ratingData =null;	
				        while ((ratingData = csvReader.readNext()) != null) {				        	   
			        		IfcRatingDataTmp ifcRatingDataTmp  = new IfcRatingDataTmp();	
			        		ifcRatingDataTmp.setLegalEntityCode(legalEntityCode);
			        		ifcRatingDataTmp.setAuditId(auditId);
			        		ifcRatingDataTmp.setAuditTypeCode(auditTypeCode);
			        		ifcRatingDataTmp.setInputBaas(ratingData[0]);
			        		ifcRatingDataTmp.setInputExt(ratingData[0]);
			        		ifcRatingDataTmp.setInputValue(ratingData[1]);			                	 
		                	ifcEnsureBaasDataDao.save(ifcRatingDataTmp);  	
				        }
					}catch (Exception e) {
						logger.error("Error .." + e); 
	            		auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						ifcEnsureBaasDataDao.deleteRatingDataFromTmp(auditLevelDocumentDetailsToSave);	    					 
						return	BankAuditConstant.FAILED;
					}
										
					//Store Rating data to master table
					String status = ifcEnsureBaasDataDao.copyRatingDataToMasterFromTemp(auditLevelDocumentDetailsToSave);
					if(status.equalsIgnoreCase(BankAuditConstant.SUCCESS)){
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.SUCCESS);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						return BankAuditConstant.SUCCESS; 
					}else{
						auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.FAILED);
						ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetailsToSave);
						ifcEnsureBaasDataDao.deleteRatingDataFromTmp(auditLevelDocumentDetailsToSave);
						return BankAuditConstant.FAILED;
					}	
				}		
			}catch (Exception e) {
				logger.error("Error .." + e); 
				return  BankAuditConstant.FAILED;
			}
		}
		return  BankAuditConstant.FAILED;
	}
	
	@Override
	public List<AuditLevelDocumentDetails> getAuditLevelDocuments(Integer legalEntityCode, String auditTypeCode,
			  String auditId, String planId,  String documentType, String role){
		return ifcEnsureBaasDataDao.getAuditLevelDocuments(legalEntityCode, auditTypeCode, auditId, planId, documentType, role);
	 }

	@Override
	public List<String>  checkDuplicateAuditLevelDocuments(Integer legalEntityCode, String auditTypeCode,
			  String auditId, String planId,  String documentType, String documentSubType, String documentFetchType, List<String> filesNamesList) {
		return ifcEnsureBaasDataDao.checkDuplicateAuditLevelDocuments(legalEntityCode, auditTypeCode, auditId, planId, documentType, 
				documentSubType, documentFetchType, filesNamesList);
	 }
	@Override
	public 	String deleteDuplicateDocument(Integer legalEntityCode, String auditTypeCode,
			  String auditId, String planId,  String documentType, String documentSubType, String documentFetchType, List<String> filesNamesList ){
		return ifcEnsureBaasDataDao.deleteDuplicateDocument(legalEntityCode, auditTypeCode, auditId, planId, documentType, 
				documentSubType, documentFetchType, filesNamesList);
	}
	
	@Override
	public String deletePdfDocument(Integer legalEntityCode, String auditTypeCode,  String auditId,  String planId, String documentType, Integer id ){
		return ifcEnsureBaasDataDao.deletePdfDocument(legalEntityCode, auditTypeCode, auditId, planId, documentType, id);
	}
	
	@Override
	public String getDocReferencePath(Integer legalEntityCode, String auditTypeCode,  String auditId, String documentType, Integer id){		
		  return ifcEnsureBaasDataDao.getDocReferencePath(legalEntityCode, auditTypeCode, auditId, documentType, id);
	}

	@Override
	public String updatePdfDocumentSequence(List<AuditLevelDocumentDetails> listAuditLevelDocumentDetails){		
		 for (AuditLevelDocumentDetails auditLevelDocumentDetails : listAuditLevelDocumentDetails) {		 
			 ifcEnsureBaasDataDao.saveOrUpdate(auditLevelDocumentDetails);			 
		 }		 
		 return BankAuditConstant.SUCCESS;
	}
	
	/******** Upload offline inspection and View Start **********/	
	@Override
	public String uploadOfflineInspection( AuditLevelDocumentDetails auditLevelDocumentDetails, CommonsMultipartFile[] files, String fileName, String reportLanguage)  {
		  	  
		for (CommonsMultipartFile commonsMultipartFile : files) {
			AuditLevelDocumentDetails auditLevelDocumentDetailsToSave = new AuditLevelDocumentDetails(); 
			
			try{  
				BeanUtils.copyProperties(auditLevelDocumentDetailsToSave, auditLevelDocumentDetails); 
				String pattern = "yyyyMMddHHmmssSSS"; 
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);  
				String referencePath =  BankAuditUtil.uploadMaterial(commonsMultipartFile, BankAuditConstant.DIR_DOC_UPLOAD, simpleDateFormat.format(new Date())+"_"+fileName);
				auditLevelDocumentDetailsToSave.setReferencePath(referencePath);  
				auditLevelDocumentDetailsToSave.setFileName(commonsMultipartFile.getOriginalFilename()); 
				auditLevelDocumentDetailsToSave.setReferenceNo(ifcEnsureBaasDataDao.getMaxReferenceNumber()); 
				/*ifcEnsureBaasDataDao.save(auditLevelDocumentDetailsToSave); 
				uploadId = auditLevelDocumentDetailsToSave.getId(); */
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());  
				return BankAuditConstant.FAILED;
			}
			
			Integer legalEntityCode = auditLevelDocumentDetailsToSave.getLegalEntityCode();
			String auditId = auditLevelDocumentDetailsToSave.getAuditId();
			String auditTypeCode = auditLevelDocumentDetailsToSave.getAuditTypeCode();				 
			String maker =  auditLevelDocumentDetailsToSave.getMaker();
			Date makerTimestamp = auditLevelDocumentDetailsToSave.getMakerTimestamp();
			
			/****  read excel Start  ********/
			List<ChecklistOfflineInspection> checklistOfflineInspectionList = new ArrayList<ChecklistOfflineInspection>();
			try(XSSFWorkbook uploadExcel = new XSSFWorkbook(commonsMultipartFile.getInputStream())){
				//XSSFWorkbook uploadExcel = new XSSFWorkbook(commonsMultipartFile.getInputStream());
				XSSFSheet sheet = uploadExcel.getSheetAt(0);
				sheet.removeRow(sheet.getRow(0));
				
				//Validate AuditId and AuditType Code
				Row firstRow = sheet.getRow(1);
				String auditIdExcel = firstRow.getCell(1).getStringCellValue();
				String auditTypeCodeExcel =firstRow.getCell(2).getStringCellValue();
				if(!auditIdExcel.equalsIgnoreCase(auditId) || !auditTypeCodeExcel.equalsIgnoreCase(auditTypeCode)){ 
					return BankAuditConstant.VALIDATION_FAILED;
				}
				
				Iterator<Row> rowIterator = sheet.iterator();				
		        while (rowIterator.hasNext()) {
		        	ChecklistOfflineInspection checklistOfflineInspection =  new ChecklistOfflineInspection();
		        	Row row = rowIterator.next();  
		        	  
		        	checklistOfflineInspection.setLegalEntityCode(legalEntityCode);
		        	checklistOfflineInspection.setObservationId(row.getCell(0).getStringCellValue()); 
		        	checklistOfflineInspection.setAuditId(row.getCell(1).getStringCellValue());
		        	checklistOfflineInspection.setAuditTypeCode(row.getCell(2).getStringCellValue());
		        	checklistOfflineInspection.setUnitCode(row.getCell(3).getStringCellValue());
		        	checklistOfflineInspection.setChapter(row.getCell(4).getStringCellValue());
		        	checklistOfflineInspection.setSection(row.getCell(5).getStringCellValue());
		        	checklistOfflineInspection.setProduct(row.getCell(6).getStringCellValue());
		        	checklistOfflineInspection.setProcess(row.getCell(7).getStringCellValue());
		        	checklistOfflineInspection.setChecklist(row.getCell(8).getStringCellValue());
		        	checklistOfflineInspection.setChecklistCode(row.getCell(9).getStringCellValue());
		        	checklistOfflineInspection.setMappingId(row.getCell(10).getStringCellValue());
		        	checklistOfflineInspection.setSubmissionStatus(row.getCell(11).getStringCellValue());
		        	checklistOfflineInspection.setComplianceStatus(row.getCell(12).getStringCellValue());
		        	checklistOfflineInspection.setCriticality(row.getCell(13).getStringCellValue());
		        	checklistOfflineInspection.setIoComments(row.getCell(14).getStringCellValue());
		            
		        	checklistOfflineInspectionList.add(checklistOfflineInspection);
		        }
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());  
				return BankAuditConstant.FAILED;
			}
			/****  read excel End  ********/
			/****  read excel End  ********/
			/******Read CSV Start********/
			/*List<ChecklistOfflineInspection> checklistOfflineInspectionList = new ArrayList<ChecklistOfflineInspection>();
			try(CSVReader csvReader = new CSVReader(new BufferedReader(new InputStreamReader(commonsMultipartFile.getInputStream())))){ 				 
		        String[] checklistData =null; 
		        csvReader.readNext();
		        while ((checklistData = csvReader.readNext()) != null) {
		        	
		        	ChecklistOfflineInspection checklistOfflineInspection =  new ChecklistOfflineInspection(); 
		        	
		        	checklistOfflineInspection.setLegalEntityCode(legalEntityCode);
		        	checklistOfflineInspection.setObservationId(checklistData[0]); 
		        	checklistOfflineInspection.setAuditId(checklistData[1]);
		        	checklistOfflineInspection.setAuditTypeCode(checklistData[2]);
		        	checklistOfflineInspection.setUnitCode(checklistData[3]);
		        	checklistOfflineInspection.setChapter(checklistData[4]);
		        	checklistOfflineInspection.setSection(checklistData[5]);
		        	checklistOfflineInspection.setProduct(checklistData[6]);
		        	checklistOfflineInspection.setProcess(checklistData[7]);
		        	checklistOfflineInspection.setChecklist(checklistData[8]);
		        	checklistOfflineInspection.setChecklistCode(checklistData[9]);
		        	checklistOfflineInspection.setMappingId(checklistData[10]);
		        	checklistOfflineInspection.setSubmissionStatus(checklistData[11]);
		        	checklistOfflineInspection.setComplianceStatus(checklistData[12]);
		        	checklistOfflineInspection.setCriticality(checklistData[13]);
		        	checklistOfflineInspection.setIoComments(checklistData[14]);
		        	
		        	checklistOfflineInspectionList.add(checklistOfflineInspection);       	         	 
		        	
		        }			        
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());  
				return BankAuditConstant.FAILED;
			}*/			
			/******Read CSV End **********/
			  
			/****  update observation detail start *********/ 			 
			List<ChecklistOfflineInspection> checklistOfflineInspectionStatusList = new ArrayList<ChecklistOfflineInspection>();
			try{
				for (ChecklistOfflineInspection checklistOfflineInspection : checklistOfflineInspectionList) {
					
					if(BankAuditUtil.isEmptyString(checklistOfflineInspection.getComplianceStatus()) ){
						checklistOfflineInspection.setUpdateStatus(BankAuditConstant.NOT_APPLICABLE);
						checklistOfflineInspection.setUpdateStatusMessage("Not attended"); 
					}
					else if( BankAuditConstant.COMPLIANCE_YES.equalsIgnoreCase(checklistOfflineInspection.getComplianceStatus())
									|| BankAuditConstant.COMPLIANCE_NO.equalsIgnoreCase(checklistOfflineInspection.getComplianceStatus())
									|| BankAuditConstant.COMPLIANCE_NA.equalsIgnoreCase(checklistOfflineInspection.getComplianceStatus()) ){
						
						if(BankAuditConstant.COMPLIANCE_NO.equalsIgnoreCase(checklistOfflineInspection.getComplianceStatus())
								&& ( BankAuditUtil.isEmptyString(checklistOfflineInspection.getIoComments()) 
										||	BankAuditUtil.isEmptyString(checklistOfflineInspection.getCriticality()))  ){
							
							checklistOfflineInspection.setUpdateStatus(BankAuditConstant.FAILED);
							if(BankAuditUtil.isEmptyString(checklistOfflineInspection.getIoComments()) 
									&&	BankAuditUtil.isEmptyString(checklistOfflineInspection.getCriticality())){
								checklistOfflineInspection.setUpdateStatusMessage("IO remarks and Criticality not given"); 
							}
							else if(BankAuditUtil.isEmptyString(checklistOfflineInspection.getIoComments()) ){
								checklistOfflineInspection.setUpdateStatusMessage("IO remarks not given"); 
							}else{ 
								checklistOfflineInspection.setUpdateStatusMessage("Criticality  not given"); 								 
							}
						}/*
						else if(!BankAuditUtil.isEmptyString(checklistOfflineInspection.getCriticality())
								&& !checklistOfflineInspection.getCriticality().equalsIgnoreCase("major") 
										&& !checklistOfflineInspection.getCriticality().equalsIgnoreCase("minor") ){
							checklistOfflineInspection.setUpdateStatus(BankAuditConstant.FAILED);
							checklistOfflineInspection.setUpdateStatusMessage("Invalid criticality ");
						}*/
						else{ 							
							AuditObservation auditObservation =  checklistDao.getAuditObservation(checklistOfflineInspection);
							if(auditObservation!=null && !BankAuditUtil.isEmptyString(auditObservation.getObservationId())){
								 
								String checklistStatus = auditObservation.getSubmissionStatus();
								if(BankAuditUtil.isEmptyString(checklistStatus) || 
										!checklistStatus.equalsIgnoreCase(BankAuditConstant.STATUS_UNAUTH)){
									checklistOfflineInspection.setUpdateStatus(BankAuditConstant.NOT_APPLICABLE);
									checklistOfflineInspection.setUpdateStatusMessage("Already processed"); 
								}							
								else{
									auditBookReportDaoV3.updateChecklistForReportTable(auditObservation.getLegalEntityCode(), 
											auditObservation.getMappingId(),
											auditObservation.getAuditId(), auditObservation.getAuditTypeCode(),
											auditObservation.getAuditGroupCode(), auditObservation.getAuditSubGroupCode(), 
											auditObservation.getActivityCode(), auditObservation.getProcessCode(),
											auditObservation.getFindingCode(), checklistOfflineInspection.getComplianceStatus(), reportLanguage,
											auditObservation.getNonCompliancePersistence(), auditObservation.getObservationCriticality());
									
									auditBookReportDaoV3.updateRemarksForReportTable(auditObservation.getLegalEntityCode(), 
											auditObservation.getMappingId(),
											auditObservation.getAuditId(), auditObservation.getAuditTypeCode(),
											auditObservation.getAuditGroupCode(), auditObservation.getAuditSubGroupCode(), 
											auditObservation.getActivityCode(), auditObservation.getProcessCode(),
											auditObservation.getFindingCode(),checklistOfflineInspection.getComplianceStatus(),
											checklistOfflineInspection.getIoComments(), reportLanguage );
									 
									 
									String standardStmt = checklistDao.getStandardStatementForReportTable(checklistOfflineInspection, reportLanguage);
									standardStmt = BankAuditUtil.isEmptyString(standardStmt)?" #::# ":standardStmt; 								
									if(!BankAuditConstant.FAILED.equals(standardStmt)){
										String[] standardStmtArr = standardStmt.split("#::#");
										standardStmt  = standardStmtArr[0].trim();
										String standardStmtHindi = "";
										if(BankAuditConstant.REPORT_LANGUAGE_HI.equalsIgnoreCase(reportLanguage)){
											standardStmtHindi = standardStmtArr[1].trim();
										}
										//if(!BankAuditUtil.isEmptyString(standardStmt)){
											auditBookReportDaoV3.updateStandardStatementForReportTable(auditObservation.getLegalEntityCode(), 
													auditObservation.getMappingId(),
													auditObservation.getAuditId(), auditObservation.getAuditTypeCode(),
													auditObservation.getAuditGroupCode(), auditObservation.getAuditSubGroupCode(), 
													auditObservation.getActivityCode(), auditObservation.getProcessCode(),
													auditObservation.getFindingCode(),checklistOfflineInspection.getComplianceStatus(),
													standardStmt,standardStmtHindi, reportLanguage);
										/*}else{
											auditBookReportDaoV3.updateStandardStatementForReportTable(auditObservation.getLegalEntityCode(), 
													auditObservation.getMappingId(),
													auditObservation.getAuditId(), auditObservation.getAuditTypeCode(),
													auditObservation.getAuditGroupCode(), auditObservation.getAuditSubGroupCode(), 
													auditObservation.getActivityCode(), auditObservation.getProcessCode(),
													auditObservation.getFindingCode(),checklistOfflineInspection.getComplianceStatus(),
													"","", reportLanguage);
										}*/
										 
										//update audit observation
										String status = checklistDao.updateOfflineInspection(checklistOfflineInspection); 
										if(status.equalsIgnoreCase(BankAuditConstant.FAILED)){
											checklistOfflineInspection.setUpdateStatus(BankAuditConstant.FAILED);
											checklistOfflineInspection.setUpdateStatusMessage("Failed to update checklist"); 
										}else{
											checklistOfflineInspection.setUpdateStatus(BankAuditConstant.SUCCESS);
											checklistOfflineInspection.setUpdateStatusMessage("Success");			 
										}
									}else{
										checklistOfflineInspection.setUpdateStatus(BankAuditConstant.FAILED);
										checklistOfflineInspection.setUpdateStatusMessage("Failed to get standard statement"); 
									}
								}
							}else{
								checklistOfflineInspection.setUpdateStatus(BankAuditConstant.FAILED);
								checklistOfflineInspection.setUpdateStatusMessage("No observation(checklist) found"); 
							}							
						}			
						
					}else{
						checklistOfflineInspection.setUpdateStatus(BankAuditConstant.FAILED);
						checklistOfflineInspection.setUpdateStatusMessage("Compliance category not matched"); 
					}
					checklistOfflineInspectionStatusList.add(checklistOfflineInspection); 
					auditLevelDocumentDetailsToSave.setChecklistOfflineInspection(checklistOfflineInspectionStatusList);
				}				
				auditLevelDocumentDetailsToSave.setUploadStatus(BankAuditConstant.SUCCESS);
				ifcEnsureBaasDataDao.save(auditLevelDocumentDetailsToSave); 
				//uploadId = auditLevelDocumentDetailsToSave.getId();
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause()); 
				return BankAuditConstant.FAILED;
			}			
			 		
			return   BankAuditConstant.SUCCESS;
			/****  update observation detail End *********/				 
		}
		
		return BankAuditConstant.FAILED;
	} 
		
	@Override
	public DataTableResponse viewOfflineInspection(Integer id, Integer legalEntityCode, String auditTypeCode,  String auditId, String search,
 			Integer orderColumn, String orderDirection, Integer page, Integer size){
		return ifcEnsureBaasDataDao.viewOfflineInspection(id, legalEntityCode, auditTypeCode, auditId, search, orderColumn, orderDirection, page, size);
	}
	
	@Override
	public ByteArrayOutputStream downloadOfflineInspection(Integer id, Integer legalEntityCode, String auditTypeCode,  String auditId, String role){
		
			try(XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) { 
			
			DataTableResponse dataTableResponse = 	ifcEnsureBaasDataDao.viewOfflineInspection(id, legalEntityCode, auditTypeCode, auditId, null, null, null, 0, 10000);
			@SuppressWarnings("unchecked")
			List<ChecklistOfflineInspection> checklistData =  (List<ChecklistOfflineInspection>) dataTableResponse.getData();	
			if(checklistData==null || checklistData.isEmpty() || checklistData.size()==0){
				return null;
			}
			 
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(",")); 
			
			int rowNumber=0;
			XSSFSheet sheet = workbook.createSheet(auditId+"_"+auditTypeCode+"_"+legalEntityCode);  
			sheet.setDefaultColumnWidth(30); 			
			sheet.createFreezePane(4, 1); 
			sheet.protectSheet(System.nanoTime()+"legalEntityauditIdauditType_"+auditId+"_"+auditTypeCode+"_"+legalEntityCode); 
		
			CellStyle cellStyleWordWrap = workbook.createCellStyle(); //Create new style
			cellStyleWordWrap.setWrapText(true);
			cellStyleWordWrap.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
						
			Font font = workbook.createFont();
			font.setBold(true);
			CellStyle cellStyleHeaderWrap = workbook.createCellStyle(); //Create new style
			cellStyleHeaderWrap.setWrapText(true);
			cellStyleHeaderWrap.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			cellStyleHeaderWrap.setAlignment(CellStyle.ALIGN_CENTER);
			cellStyleHeaderWrap.setFont(font);			 
			
			//Create Header of Excel
			XSSFRow rowhead = sheet.createRow(rowNumber++); 
		    BankAuditUtil.createCell(sheet, rowhead, 0, "Observation Id", cellStyleHeaderWrap, 12*256);		    
		    BankAuditUtil.createCell(sheet, rowhead, 1, "Inspection Type", cellStyleHeaderWrap, 12*256);         	 
	        BankAuditUtil.createCell(sheet, rowhead, 2, "Inspection Id", cellStyleHeaderWrap, 12*256);	         
	        BankAuditUtil.createCell(sheet, rowhead, 3, "Unit Code", cellStyleHeaderWrap, 15*256);
	        BankAuditUtil.createCell(sheet, rowhead, 4, "Chapter", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 5, "Section", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 6, "Product", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 7, "Process", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 8, "Checklist", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 9, "Checklist Code", cellStyleHeaderWrap, 1);
	        BankAuditUtil.createCell(sheet, rowhead, 10, "Mapping Id", cellStyleHeaderWrap, 1);
	        BankAuditUtil.createCell(sheet, rowhead, 11, "Record Status", cellStyleHeaderWrap, 10*256);
	        BankAuditUtil.createCell(sheet, rowhead, 12, "Compliance Status", cellStyleHeaderWrap, 12*256);
	        BankAuditUtil.createCell(sheet, rowhead, 13, "Criticality", cellStyleHeaderWrap, 12*256);
	        BankAuditUtil.createCell(sheet, rowhead, 14, "IO Comments", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 15, "Bank Remarks", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 16, "Scrutiny Status", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 17, "Scrutiny Remarks", cellStyleHeaderWrap, 0);
	        BankAuditUtil.createCell(sheet, rowhead, 18, "Status", cellStyleHeaderWrap, 10*256);
	        BankAuditUtil.createCell(sheet, rowhead, 19, "Message", cellStyleHeaderWrap, 12*256);
	        
			for (ChecklistOfflineInspection checklist: checklistData) {
				XSSFRow row = sheet.createRow(rowNumber++);				
				BankAuditUtil.createCell(sheet, row, 0, checklist.getObservationId(), cellStyleWordWrap, 0);				
				BankAuditUtil.createCell(sheet, row, 1, checklist.getAuditTypeCode(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 2, checklist.getAuditId(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 3, checklist.getUnitCode(), cellStyleWordWrap,0);
				BankAuditUtil.createCell(sheet, row, 4, checklist.getChapter(), cellStyleWordWrap,0);
				BankAuditUtil.createCell(sheet, row, 5, checklist.getSection(), cellStyleWordWrap,0);
				BankAuditUtil.createCell(sheet, row, 6, checklist.getProduct(), cellStyleWordWrap,0);
				BankAuditUtil.createCell(sheet, row, 7, checklist.getProcess(), cellStyleWordWrap,0);
				BankAuditUtil.createCell(sheet, row, 8, checklist.getChecklist(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 9, checklist.getChecklistCode(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 10, checklist.getMappingId(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 11, checklist.getSubmissionStatus(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 12, checklist.getComplianceStatus(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 13, checklist.getCriticality(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 14, checklist.getIoComments(), cellStyleWordWrap, 0);
				
				if(rolesListCompliance.contains(role)){
		        	 BankAuditUtil.createCell(sheet, row, 15, checklist.getResponseReviewRemarks() , cellStyleHeaderWrap, 0);
		        	 BankAuditUtil.createCell(sheet, row, 16, checklist.getReviewStatusAttribute(), cellStyleHeaderWrap, 0);
		        	 BankAuditUtil.createCell(sheet, row, 17, checklist.getLastResponseReviewRemarks(), cellStyleHeaderWrap, 0);
		        }else if(rolesListReviewer.contains(role)){
		        	 BankAuditUtil.createCell(sheet, row, 15, checklist.getLastResponseReviewRemarks(), cellStyleHeaderWrap, 0);
		        	 BankAuditUtil.createCell(sheet, row, 16, checklist.getReviewStatusAttribute(), cellStyleHeaderWrap, 0);
		        	 BankAuditUtil.createCell(sheet, row, 17, checklist.getResponseReviewRemarks(), cellStyleHeaderWrap, 0);
		        }
				
				BankAuditUtil.createCell(sheet, row, 18, checklist.getUpdateStatus(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 19, checklist.getUpdateStatusMessage(), cellStyleWordWrap, 0);
			}           
			
            workbook.write(out);
            out.toByteArray();
            return out; 
            
        } catch ( Exception ex ) {
            System.out.println(ex);
        }return null;
	}


	/******** Upload offline inspection and View End **********/
	
	/******************/
	@Override
	public List<AuditLevelDocumentDetails> getAuditLevelCSVDocument(Integer legalEntityCode, String auditTypeCode,
			  String auditId,  String documentType){
		return ifcEnsureBaasDataDao.getAuditLevelCSVDocument(legalEntityCode, auditTypeCode, auditId, documentType);
	}
	
	@Override
	public DataTableResponse viewAuditLevelCSVDocument(Integer legalEntityCode, String auditTypeCode,  String auditId, String stageOfFile, Integer referenceNo,
	    		String search, Integer orderColumn, String orderDirection, Integer page, Integer size){
		return  ifcEnsureBaasDataDao.viewAuditLevelCSVDocument(legalEntityCode, auditTypeCode, auditId, stageOfFile, referenceNo, search, orderColumn, orderDirection, page, size);
	 }	 
	
	@Override
	public List<AuditLevelDocumentDetails> getAuditLevelPdfDocument(Integer legalEntityCode, String auditTypeCode,
			  String auditId,  String documentType){
		return ifcEnsureBaasDataDao.getAuditLevelPdfDocument(legalEntityCode, auditTypeCode, auditId, documentType);
	}	 
	
}