/*
 * 
 */
package com.bankaudit.service;

import java.io.ByteArrayOutputStream;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.ChecklistDao;
import com.bankaudit.dao.GeneralParameterDao;
import com.bankaudit.dto.ChecklistDto;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AuditObservationDto;
import com.bankaudit.model.GeneralParameter;
import com.bankaudit.process.model.AuditDocumentDetails;
import com.bankaudit.process.model.AuditDocumentDetailsTemp;
import com.bankaudit.process.model.AuditObservation;
import com.bankaudit.process.model.AuditObservationHst;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.rest.process.dao.AuditObservationDao;
import com.bankaudit.rest.process.service.MyAuditsService;
import com.bankaudit.util.BankAuditUtil;

/**
 * The Interface {@link ChecklistServiceImpl} define the methods
 * that can be exposed to controller layer in order to implements rest
 * end-points and define the business logic.
 *
 * @author amit.patel
 * @version 1.0
 */

@Service("checklistService")
@Transactional("transactionManager")
public class ChecklistServiceImpl implements ChecklistService{

	@Autowired
	ChecklistDao checklistDao;
	
	@Autowired(required=true)
	AuditObservationDao auditObservationDao;
	
	@Autowired
	BusinessDateService businessDateService;	
	
	@Autowired
    SequenceAppenderService sequenceAppenderService;
	
	@Autowired
	MyAuditsService  myAuditsService;
	
	@Autowired
	GeneralParameterDao generalParameterDao;
	
	@Override
	public List<String> getChapter(String legalEntityCodeStr, String auditId, String auditTypeCode) {

		return checklistDao.getChapter(legalEntityCodeStr, auditId, auditTypeCode);
	}

	@Override
	public List<String> getSection(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode) {
		// TODO Auto-generated method stub
		return checklistDao.getSection(legalEntityCodeStr, auditId, auditTypeCode, auditGroupCode);
	}

	@Override
	public List<String> getProduct(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode) {
		// TODO Auto-generated method stub
		return checklistDao.getProduct(legalEntityCodeStr, auditId, auditTypeCode, auditGroupCode, auditSubGroupCode);
	}

	@Override
	public List<String> getProcess(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode, 
			String activityCode) {
		// TODO Auto-generated method stub
		return checklistDao.getProcess(legalEntityCodeStr, auditId, auditTypeCode, auditGroupCode, auditSubGroupCode, activityCode);
	}

	@Override
	public DataTableResponse getChecklist(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode,
			String activityCode , String processCode, String search, Integer orderColumn, String orderDirection,  Integer page, Integer size,
			String complianceCategory, String submissionStatus, String responseStatus, String reviewStatus, String flag, String criticalityToggle,
			String reportRvwFedbk, String role, String documentType, String documentSubType, String nonCompliancePersistence) {
		// TODO Auto-generated method stub
		return checklistDao.getChecklist(legalEntityCodeStr, auditId, auditTypeCode, auditGroupCode, auditSubGroupCode,  
				activityCode, processCode, search, orderColumn, orderDirection, page, size, complianceCategory,
				submissionStatus, responseStatus, reviewStatus, flag, criticalityToggle, reportRvwFedbk, role, documentType, documentSubType, nonCompliancePersistence) ;
	}

	/*@Override
	public List<AuditDocumentDetails> getAuditDocumentDetails(Integer legalEntityCode, String auditTypeCode,
			String planId, String auditId, String observationId, String documentType) {
		return checklistDao.getAuditDocumentDetails(legalEntityCode, auditTypeCode, planId, auditId, observationId, documentType);
	}*/
	
	/*@Override
	public  AuditObservation getAuditObservation(String legalEntityCode, String auditTypeCode, String groupObservationId){		 	
		return checklistDao.getAuditObservation(legalEntityCode, auditTypeCode, groupObservationId);
	}*/
	 
	@Override
	public String updateObservation(AuditObservationDto auditObservationDto) {
		
		AuditDocumentDetailsTemp auditDocumentDetailsTemp=null;
		List<AuditObservation> auditObservations=auditObservationDto.getAuditObservations();		

		String groupObservationId="";
		if(BankAuditUtil.isEmptyString(auditObservationDto.getGroupObservationId())){
			groupObservationId= sequenceAppenderService.getAutoSequenceId();
		}else {
			groupObservationId=auditObservationDto.getGroupObservationId();
		}
		
		Map<String,Object> properties=new HashMap<String, Object>();
		properties.put("groupObservationId", groupObservationId);
        List<AuditObservation> auditObservationsStored=
        		auditObservationDao.getEntitiesByMatchingProperties(AuditObservation.class, properties);
        
        if(auditObservationsStored!=null){
        	for (AuditObservation auditObservation : auditObservationsStored) {
        		auditObservationDao.save(new AuditObservationHst(auditObservation));
			}
        }
		       
		auditObservationDao.deleteObservationByGrpId(groupObservationId);
		
		for (AuditObservation auditObservation : auditObservations) {
			auditObservation.setBusinessdateTimestamp(businessDateService.getBusinessDateTimestampByLegalEntityCode(auditObservation.getLegalEntityCode()));
			auditObservation.setSysdateTimestamp(new Timestamp(System.currentTimeMillis()));
			if(auditObservation.getObservationId()==null){
				auditObservation.setObservationId(sequenceAppenderService.getAutoSequenceId());
			}
			auditObservation.setGroupObservationId(groupObservationId);
			auditObservationDao.flushSession();
			auditObservationDao.save(auditObservation);
			
			
			if(auditObservation.getDuplicateDocIds()!=null){
							
							for (Integer  duplicateDocId : auditObservation.getDuplicateDocIds()) {
								auditObservationDao.deleteById(AuditDocumentDetails.class, duplicateDocId);
							}
							
						}
			
			if(auditObservation.getDocumentUploadId()!=null){
				auditDocumentDetailsTemp=(AuditDocumentDetailsTemp) auditObservationDao.get(AuditDocumentDetailsTemp.class, 
						auditObservation.getDocumentUploadId());
				
				
				for (String url : auditDocumentDetailsTemp.getUrls().split(BankAuditConstant.DELIMITER_URL)) {

					AuditDocumentDetails auditDocumentDetails=new AuditDocumentDetails();
					auditDocumentDetails.setLegalEntityCode(auditObservation.getLegalEntityCode());
					auditDocumentDetails.setAuditTypeCode(auditObservation.getAuditTypeCode());
					auditDocumentDetails.setPlanId(auditObservation.getPlanId());
					auditDocumentDetails.setAuditId(auditObservation.getAuditId());
					auditDocumentDetails.setDocumentType(auditDocumentDetailsTemp.getRole()+"-S");
					auditDocumentDetails.setEntityStatus(auditObservation.getEntityStatus());
					auditDocumentDetails.setStatus("A");
					auditDocumentDetails.setMaker(auditObservation.getMaker());
		            auditDocumentDetails.setChecker(auditObservation.getChecker());
					auditDocumentDetails.setBusinessdateTimestamp(auditObservation.getBusinessdateTimestamp());
					auditDocumentDetails.setSysdateTimestamp(new Timestamp(System.currentTimeMillis()));
					auditDocumentDetails.setMakerTimestamp(auditDocumentDetails.getBusinessdateTimestamp());
					auditDocumentDetails.setReferencePath(url);
					auditDocumentDetails.setObservationId(auditObservation.getObservationId());
					auditObservationDao.save(auditDocumentDetails);
				}
				auditObservationDao.delete(auditDocumentDetailsTemp);
			}			
		}
		
	    if(auditObservationDto.getDuplicateDocIds()!=null){
			
				for (Integer  duplicateDocId : auditObservationDto.getDuplicateDocIds()) {
					auditObservationDao.deleteById(AuditDocumentDetails.class, duplicateDocId);
				}
				
			}
	    
		if(auditObservationDto.getDocumentUploadId()!=null){
			auditDocumentDetailsTemp=(AuditDocumentDetailsTemp) auditObservationDao.get(AuditDocumentDetailsTemp.class, 
					auditObservationDto.getDocumentUploadId());
			AuditDocumentDetails auditDocumentDetails=null;
			
			for (String url : auditDocumentDetailsTemp.getUrls().split(BankAuditConstant.DELIMITER_URL)) {
				auditDocumentDetails=new AuditDocumentDetails();
				auditDocumentDetails.setLegalEntityCode(auditObservationDto.getAuditObservations().get(0).getLegalEntityCode());
				auditDocumentDetails.setAuditTypeCode(auditObservationDto.getAuditObservations().get(0).getAuditTypeCode());
				auditDocumentDetails.setPlanId(auditObservationDto.getAuditObservations().get(0).getPlanId());
				auditDocumentDetails.setAuditId(auditObservationDto.getAuditObservations().get(0).getAuditId());
				auditDocumentDetails.setDocumentType(auditDocumentDetailsTemp.getRole()+"-G");
				auditDocumentDetails.setEntityStatus(auditObservationDto.getAuditObservations().get(0).getEntityStatus());
				auditDocumentDetails.setStatus("A");
				auditDocumentDetails.setMaker(auditObservationDto.getAuditObservations().get(0).getMaker());
	            auditDocumentDetails.setChecker(auditObservationDto.getAuditObservations().get(0).getChecker());
				auditDocumentDetails.setBusinessdateTimestamp(auditObservationDto.getAuditObservations().get(0).getBusinessdateTimestamp());
				auditDocumentDetails.setSysdateTimestamp(new Timestamp(System.currentTimeMillis()));
				auditDocumentDetails.setMakerTimestamp(auditDocumentDetails.getBusinessdateTimestamp());
				auditDocumentDetails.setReferencePath(url);
				
				auditDocumentDetails.setObservationId(groupObservationId);
				
				
				auditObservationDao.save(auditDocumentDetails);
			}
			auditObservationDao.delete(auditDocumentDetailsTemp);
		}
				
		AuditObservation auditObservation=auditObservations.get(0);
		if(auditObservation.getSubmissionStatus().equalsIgnoreCase("A")
				/*|| auditObservation.getReviewStatus().equalsIgnoreCase("A")
				|| auditObservation.getResponseStatus().equalsIgnoreCase("A")*/){
			AuditSchedule auditSchedule=getObservationCounts(auditObservation.getLegalEntityCode(), auditObservation.getAuditTypeCode(), auditObservation.getPlanId(), auditObservation.getAuditId());
			auditSchedule.setLegalEntityCode(auditObservation.getLegalEntityCode());
			auditSchedule.setAuditTypeCode(auditObservation.getAuditTypeCode());
			auditSchedule.setPlanId(auditObservation.getPlanId());
			auditSchedule.setAuditId(auditObservation.getAuditId());
			myAuditsService.updateAuditScheduleCounts(auditSchedule);
		}
		return groupObservationId;
	}
	 
	private AuditSchedule getObservationCounts(Integer legalEntityCode, String auditTypeCode, String planId, String auditId) {		
		return auditObservationDao.getObservationCounts( legalEntityCode,  auditTypeCode,  planId,	 auditId);
	}
	
	public ChecklistDto getChapterDetailsOfChecklist(String legalEntityCodeStr, String auditTypeCode ,  String auditId, 
			String observationId , String groupObservationId ){
		return checklistDao.getChapterDetailsOfChecklist(legalEntityCodeStr, auditTypeCode, auditId, observationId, groupObservationId);
	}
	 
	
	@Override
	public ByteArrayOutputStream  exportChecklistToExcel(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode,
			 String activityCode, String processCode, String search,String complianceCategory, String submissionStatus, String responseStatus, String reviewStatus, 
			 String flag, String criticalityToggle,	String reportRvwFedbk, String role, String documentType, String documentSubType, String nonCompliancePersistence,
			 String reportLanguage){
		
		try(XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) { 
			
			List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny			 
			
			Boolean roleInspector = false;
			Boolean roleCompliance = false;
			Boolean roleReviewer = false;
			
			if(rolesListInspector.contains(role)){
				roleInspector = true;
			}else if(rolesListCompliance.contains(role)){
				roleCompliance = true;
			}else if(rolesListReviewer.contains(role)){
				roleReviewer = true;
			}
			
			Map<String , Object> properties = new HashMap<String, Object>();
			properties.put("legalEntityCode", Integer.parseInt(legalEntityCodeStr));
			properties.put("auditTypeCode", auditTypeCode);
			properties.put("auditId", auditId);			
			AuditSchedule auditSchedule = (AuditSchedule) auditObservationDao.getUniqueEntityByMatchingProperties(AuditSchedule.class, properties);
			
			DataTableResponse dataTableResponse = checklistDao.getAllChecklistForDownload(legalEntityCodeStr, auditId, auditTypeCode, auditGroupCode, auditSubGroupCode,
					 activityCode, processCode, search, complianceCategory, submissionStatus, responseStatus, reviewStatus, flag, criticalityToggle,
						reportRvwFedbk, role, documentType, documentSubType, nonCompliancePersistence, auditSchedule) ;		
			@SuppressWarnings("unchecked")
			List<ChecklistDto> checklistData =  (List<ChecklistDto>) dataTableResponse.getData();	
			
			int rowNumber=0;
			XSSFSheet sheet = workbook.createSheet(auditId+"_"+auditTypeCode+"_"+legalEntityCodeStr);  
			sheet.setDefaultColumnWidth(30);
			//sheet.setColumnWidth(0, 10);
			sheet.protectSheet(System.nanoTime()+"legalEntityauditIdauditType_"+auditId+"_"+auditTypeCode+"_"+legalEntityCodeStr); 
			CellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setLocked(false);	 
			cellStyle.setWrapText(true);
			cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			
			CellStyle cellStyleHeader = workbook.createCellStyle();  
			Font font = workbook.createFont();
			font.setBold(true);
			cellStyleHeader.setFont(font);
			cellStyleHeader.setWrapText(true);
			cellStyleHeader.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			cellStyleHeader.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			
			CellStyle cellStyleWordWrap = workbook.createCellStyle(); //Create new style
			cellStyleWordWrap.setWrapText(true);
			cellStyleWordWrap.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			
			CellStyle cellStyleNoWordWrap = workbook.createCellStyle();  
			cellStyleNoWordWrap.setWrapText(false);
			cellStyleNoWordWrap.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			
			//Create Header of Excel			
			XSSFRow rowhead = sheet.createRow(rowNumber++);
		    
		    BankAuditUtil.createCell(sheet, rowhead, 0, "Observation Id", cellStyleHeader, 15*256);
		    BankAuditUtil.createCell(sheet, rowhead, 1, "Inspection Id", cellStyleHeader, 15*256);
		    BankAuditUtil.createCell(sheet, rowhead, 2, "Inspection Type", cellStyleHeader, 15*256);       
		    BankAuditUtil.createCell(sheet, rowhead, 3, "Unit Code", cellStyleHeader, 15*256); 
		    
		    BankAuditUtil.createCell(sheet, rowhead, 4, "Chapter", cellStyleHeader, 30*256); 
		    BankAuditUtil.createCell(sheet, rowhead, 5, "Section", cellStyleHeader, 30*256); 
		    BankAuditUtil.createCell(sheet, rowhead, 6, "Product", cellStyleHeader, 30*256); 
		    BankAuditUtil.createCell(sheet, rowhead, 7, "Process", cellStyleHeader, 30*256); 
		    BankAuditUtil.createCell(sheet, rowhead, 8, "Checklist", cellStyleHeader, 45*256); 
		    
		    BankAuditUtil.createCell(sheet, rowhead, 9, "Checklist Code", cellStyleHeader, 1); //1
		    BankAuditUtil.createCell(sheet, rowhead, 10, "Mapping Id", cellStyleHeader, 1); //1
		    
		    if(roleInspector){
		    	BankAuditUtil.createCell(sheet, rowhead, 11, "Record Status", cellStyleHeader, 10*256);
		    }else{
		    	BankAuditUtil.createCell(sheet, rowhead, 11, "Record Status", cellStyleNoWordWrap, 1);//1
		    }
		    
		    BankAuditUtil.createCell(sheet, rowhead, 12, "Compliance Category", cellStyleHeader, 10*256);
		    BankAuditUtil.createCell(sheet, rowhead, 13, "Criticality", cellStyleHeader, 10*256);
		    BankAuditUtil.createCell(sheet, rowhead, 14, "IO Comments", cellStyleHeader, 40*256);
		     	        
	        if(roleCompliance || roleReviewer){
	        	BankAuditUtil.createCell(sheet, rowhead, 15, "Response Status", cellStyleNoWordWrap, 1); //1
			    BankAuditUtil.createCell(sheet, rowhead, 16, "Review Status", cellStyleNoWordWrap, 1);	  //1
			    
			    if(auditSchedule.getRespondStatus().equalsIgnoreCase(BankAuditConstant.STATUS_RESPONSE_INITIATED)){
			    	BankAuditUtil.createCell(sheet, rowhead, 17, "Scrutiny Status", cellStyleHeader, 1);// Further compliance status 		
			    }else{
			    	BankAuditUtil.createCell(sheet, rowhead, 17, "Scrutiny Status", cellStyleHeader, 15*256);// Further compliance status 		
			    }
			    	    
			    if(roleCompliance){ 
			    	 if(auditSchedule.getRespondStatus().equalsIgnoreCase(BankAuditConstant.STATUS_RESPONSE_INITIATED)){
			    		 BankAuditUtil.createCell(sheet, rowhead, 18, "Scrutiny Remark", cellStyleHeader, 1); // given latest remarks by scrutinizer 
			    	 }else{
			    		 BankAuditUtil.createCell(sheet, rowhead, 18, "Scrutiny Remark", cellStyleHeader, 45*256); // given latest remarks by scrutinizer 
			    	 }			    	
			    	BankAuditUtil.createCell(sheet, rowhead, 19, "Compliance Remark", cellStyleHeader, 45*256);
			    	BankAuditUtil.createCell(sheet, rowhead, 20, "", cellStyleHeader, 1); // Desk Rating
			    	BankAuditUtil.createCell(sheet, rowhead, 21, "", cellStyleHeader, 1); // Desk Remarks
			    }else if(roleReviewer){ 
			    	BankAuditUtil.createCell(sheet, rowhead, 18, "Compliance Remark", cellStyleHeader, 45*256); // given latest remarks by bank
			    	BankAuditUtil.createCell(sheet, rowhead, 19, "Scrutiny Remark", cellStyleHeader, 45*256);
			    	
			    	BankAuditUtil.createCell(sheet, rowhead, 20, "Desk Compliance Score", cellStyleHeader, 15*256); // Desk Rating
			    	BankAuditUtil.createCell(sheet, rowhead, 21, "Desk Compliance Remark", cellStyleHeader, 45*256);  // Desk Remarks
			    } 			   
		    }	 	    
	        	         
			for (ChecklistDto checklist: checklistData) {
				XSSFRow row = sheet.createRow(rowNumber++);			
				
				BankAuditUtil.createCell(sheet, row, 0, checklist.getObservationId(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 1, checklist.getAuditId(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 2, checklist.getAuditTypeCode(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 3, checklist.getUnitId()+"-"+checklist.getUnitName(), cellStyleWordWrap,0);
				
				if(BankAuditConstant.REPORT_LANGUAGE_EN.equalsIgnoreCase(reportLanguage)){
					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.getFindingName(), cellStyleWordWrap, 0);
				}else{
					BankAuditUtil.createCell(sheet, row, 4, checklist.getChapterHindi(), cellStyleWordWrap,0);
					BankAuditUtil.createCell(sheet, row, 5, checklist.getSectionHindi(), cellStyleWordWrap,0);
					BankAuditUtil.createCell(sheet, row, 6, checklist.getProductHindi(), cellStyleWordWrap,0);
					BankAuditUtil.createCell(sheet, row, 7, checklist.getProcessHindi(), cellStyleWordWrap,0);
					BankAuditUtil.createCell(sheet, row, 8, checklist.getFindingNameHindi(), cellStyleWordWrap, 0);
				}
				
				BankAuditUtil.createCell(sheet, row, 9, checklist.getFindingCode(), cellStyleWordWrap, 0);
				BankAuditUtil.createCell(sheet, row, 10, checklist.getMappingId(), cellStyleWordWrap, 0); 
								
				String observationSubmissionStatus = checklist.getSubmissionStatus();
				/*if(!BankAuditUtil.isEmptyString(checklist.getComplianceCategory())){
					observationSubmissionStatus = BankAuditConstant.STATUS_PENDING_AUTH;
				}*/			  				
				String remarks = BankAuditUtil.isEmptyString(checklist.getFindingRemarks())?"":checklist.getFindingRemarks().replaceAll("\\<.*?\\>", ""); 				 
				if(roleInspector  && !BankAuditConstant.STATUS_PENDING_AUTH.equalsIgnoreCase(observationSubmissionStatus)){
					BankAuditUtil.createCell(sheet, row, 12, checklist.getComplianceCategory(), cellStyle, 0); //12
					BankAuditUtil.createCell(sheet, row, 13, checklist.getObservationCriticality(), cellStyle, 0);//13
					BankAuditUtil.createCell(sheet, row, 14, remarks, cellStyle, 0);
				}else{
					BankAuditUtil.createCell(sheet, row, 12, checklist.getComplianceCategory(), cellStyleWordWrap, 0); //12
					BankAuditUtil.createCell(sheet, row, 13, checklist.getObservationCriticality(), cellStyleWordWrap, 0);//13
					BankAuditUtil.createCell(sheet, row, 14, remarks, cellStyleWordWrap, 0);
				}				
				
				//Start from here
				if(roleCompliance || roleReviewer){
					BankAuditUtil.createCell(sheet, row, 11, observationSubmissionStatus, cellStyleNoWordWrap, 0);				
				    BankAuditUtil.createCell(sheet, row, 15, checklist.getResponseStatus(), cellStyleNoWordWrap, 0);
				    BankAuditUtil.createCell(sheet, row, 16, checklist.getReviewStatus(), cellStyleNoWordWrap, 0);	
				    
				    if(roleCompliance){
				    	BankAuditUtil.createCell(sheet, row, 17, checklist.getReviewStatusAttribute(), cellStyleWordWrap , 0); 
				    	
				    	if(BankAuditConstant.STATUS_REJ.equalsIgnoreCase(checklist.getResponseStatus())
				    				&& (BankAuditConstant.STATUS_UNAUTH.equalsIgnoreCase(checklist.getReviewStatus())
				    						|| BankAuditConstant.STATUS_PENDING_AUTH.equalsIgnoreCase(checklist.getReviewStatus()))){
				    		BankAuditUtil.createCell(sheet, row, 19,  "", cellStyleWordWrap, 0); 
				    	}else if(BankAuditConstant.STATUS_REJ.equalsIgnoreCase(checklist.getResponseStatus()) 
				    				&& BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(checklist.getReviewStatus())){
				    		BankAuditUtil.createCell(sheet, row, 19,  "", cellStyle, 0);
				    	}else if(BankAuditConstant.STATUS_UNAUTH.equalsIgnoreCase(checklist.getResponseStatus())
				    				|| BankAuditConstant.STATUS_PENDING_AUTH.equalsIgnoreCase(checklist.getResponseStatus())){
				    		BankAuditUtil.createCell(sheet, row, 19,  BankAuditUtil.isEmptyString(checklist.getResponseRemarks())?"":checklist.getResponseRemarks().replaceAll("\\<.*?\\>", ""), cellStyle, 0);
				    	}else if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(checklist.getResponseStatus())){
				    		BankAuditUtil.createCell(sheet, row, 19,  BankAuditUtil.isEmptyString(checklist.getResponseRemarks())?"":checklist.getResponseRemarks().replaceAll("\\<.*?\\>", ""), cellStyleWordWrap, 0);
				    	}
				    }else{ 			    	
				    	
				    	if(!BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(checklist.getReviewStatus())){
				    		BankAuditUtil.createCell(sheet, row, 17, checklist.getReviewStatusAttribute(), cellStyle , 0);
				    		BankAuditUtil.createCell(sheet, row, 19,  BankAuditUtil.isEmptyString(checklist.getReviewRemarks())?"":checklist.getReviewRemarks().replaceAll("\\<.*?\\>", ""), cellStyle, 0);
				    		
				    		BankAuditUtil.createCell(sheet, row, 20, checklist.getDeskEffectiveScore(), cellStyle, 0);
					    	BankAuditUtil.createCell(sheet, row, 21, BankAuditUtil.isEmptyString(checklist.getDeskReviewerMakerRemarks())?"":checklist.getDeskReviewerMakerRemarks().replaceAll("\\<.*?\\>", ""), cellStyle, 0);				    		
				    	}else if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(checklist.getReviewStatus())
				    			&& BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(checklist.getResponseStatus())
				    			&& BankAuditConstant.STATUS_FURTHER_COMPLIANCE_WITH_DESCRIPTION.equalsIgnoreCase(checklist.getReviewStatusAttribute())){
				    		BankAuditUtil.createCell(sheet, row, 17, checklist.getReviewStatusAttribute(), cellStyle , 0);
				    		BankAuditUtil.createCell(sheet, row, 19,  "", cellStyle, 0);
				    		
				    		BankAuditUtil.createCell(sheet, row, 20, checklist.getDeskEffectiveScore(), cellStyleWordWrap, 0);
					    	BankAuditUtil.createCell(sheet, row, 21, BankAuditUtil.isEmptyString(checklist.getDeskReviewerMakerRemarks())?"":checklist.getDeskReviewerMakerRemarks().replaceAll("\\<.*?\\>", ""), cellStyleWordWrap, 0);
				    	}else{
				    		BankAuditUtil.createCell(sheet, row, 17, checklist.getReviewStatusAttribute(), cellStyleWordWrap , 0);
				    		BankAuditUtil.createCell(sheet, row, 19,  BankAuditUtil.isEmptyString(checklist.getReviewRemarks())?"":checklist.getReviewRemarks().replaceAll("\\<.*?\\>", ""), cellStyleWordWrap, 0);
				    		
				    		BankAuditUtil.createCell(sheet, row, 20, checklist.getDeskEffectiveScore(), cellStyleWordWrap, 0);
					    	BankAuditUtil.createCell(sheet, row, 21, BankAuditUtil.isEmptyString(checklist.getDeskReviewerMakerRemarks())?"":checklist.getDeskReviewerMakerRemarks().replaceAll("\\<.*?\\>", ""), cellStyleWordWrap, 0);
				    	}
				    }
				    String latestResponseReviewRemarks = BankAuditUtil.isEmptyString(checklist.getLastResponseReviewRemarks())?"":checklist.getLastResponseReviewRemarks().replaceAll("\\<.*?\\>", "");
				    BankAuditUtil.createCell(sheet, row, 18, latestResponseReviewRemarks, cellStyleHeader, 0);
				   
			    }else{
			    	BankAuditUtil.createCell(sheet, row, 11, observationSubmissionStatus, cellStyleWordWrap, 0);
			    }			
			}           
	            
			rowNumber++;
	        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); 
            DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(new String[] { "C", "N", "NA" }) ; 
            
            XSSFDataValidation validation = (XSSFDataValidation) dataValidationHelper.createValidation(constraint,
            		new CellRangeAddressList(1, rowNumber, 12, 12));	            
            /*validation.createPromptBox("Error", "Valid values are -> C, N or NA");
            validation.setShowPromptBox(true);
 			validation.setSuppressDropDownArrow(false);*/
            validation.createErrorBox("Error", "Valid values are -> C, N or NA");
            validation.setShowErrorBox(true);	            
            
            DataValidationConstraint constraint1 = dataValidationHelper.createExplicitListConstraint(new String[] { "Major", "Minor" }) ;
            XSSFDataValidation validation1 = (XSSFDataValidation) dataValidationHelper.createValidation(constraint1, 
            		new CellRangeAddressList(1, rowNumber, 13, 13));
            validation1.createErrorBox("Error", "Valid values are -> Major, Minor");
            validation1.setShowErrorBox(true);	  
            
            if(roleReviewer){            	
            	List<GeneralParameter> reviewAttribList = generalParameterDao.getGeneralParameter(Integer.parseInt(legalEntityCodeStr), null, null, "STATUS_REVIEW", "REVIEW_ATTRIBUTE", 
						null, null);
            	 String[] reviewAttribArry =  reviewAttribList.stream().map(e -> (e.getValue()+"-"+e.getDescription())).toArray (String[]::new);  
            	
            	 DataValidationConstraint constraintReviewAttrib = dataValidationHelper.createExplicitListConstraint(reviewAttribArry) ;
                 XSSFDataValidation validationReviewAttrib = (XSSFDataValidation) dataValidationHelper.createValidation(constraintReviewAttrib, 
                 		new CellRangeAddressList(1, rowNumber, 17, 17));
                 validationReviewAttrib.createErrorBox("Error", "Valid values are ->"+String.join(", ", reviewAttribArry));
                 validationReviewAttrib.setShowErrorBox(true);
                 sheet.addValidationData(validationReviewAttrib);
                 
                 DataValidationConstraint constraintReviewAttribCondition = dataValidationHelper.createCustomConstraint("AND($R"+2+"<>\"A-Accepted\", AND($R"+2+"<>\"R-Carry Forward\", $T"+2+"<>\"\"))");
                 XSSFDataValidation validationReviewAttribCondition = (XSSFDataValidation) dataValidationHelper.createValidation(constraintReviewAttribCondition, 
                  		new CellRangeAddressList(1, rowNumber, 19, 19));
                 validationReviewAttribCondition.createErrorBox("Error", "Remarks are applicable in case of further compliance only.......");
                 validationReviewAttribCondition.setShowErrorBox(true);
                 sheet.addValidationData(validationReviewAttribCondition);
                 
                 
                 String[] deskRatingArry = new  String[]{"0","1","2","3","4","5","NA"};
                 DataValidationConstraint constraintDeskRatingRemarks = dataValidationHelper.createExplicitListConstraint(deskRatingArry) ;
                 XSSFDataValidation validationDeskRating = (XSSFDataValidation) dataValidationHelper.createValidation(constraintDeskRatingRemarks, 
                 		new CellRangeAddressList(1, rowNumber, 20, 20));
                 validationDeskRating.createErrorBox("Error", "Valid values are ->"+String.join(", ", deskRatingArry));
                 validationDeskRating.setShowErrorBox(true);
                 sheet.addValidationData(validationDeskRating);
                 
            } 
            
            sheet.addValidationData(validation);
            sheet.addValidationData(validation1);	            

            workbook.write(out);
            out.toByteArray();
            return out; 
            
        } catch ( Exception ex ) {
            System.out.println(ex);
        }return null;
	}
	
	/*private void createCell(XSSFSheet sheet, XSSFRow row, int cellNumber, String value, CellStyle style, int cellWidth) { 
		//sheet.setColumnWidth(cellNumber, cellWidth);
        Cell cell = row.createCell(cellNumber, Cell.CELL_TYPE_STRING);
        cell.setCellValue((String) value);        
        cell.setCellStyle(style);
    }*/
	
	
}
