package com.ihg.report;

import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.ihg.controllers.reports.SegmentPerformanceData;
import com.ihg.controllers.reports.TierPerformanceData;
import com.lowagie.text.DocumentException;


 public class IndividualPerformanceExcelReportGenerator extends AbsExcelReportGenerator  {
	 
	 
	 private static Logger log = Logger.getLogger(IndividualPerformanceExcelReportGenerator.class);

		private static final String REPORT_NAME = "IndividualPerformanceExcelReport";

		public HSSFWorkbook generateReportExcel(String reportName, String reportType,
				Map<?, ?> params, OutputStream out) throws ReportException {

			if(!REPORT_NAME.equals(reportName) && !REPORT_TYPE_EXCEL.equals(reportType)){
				throw new ReportException(reportName+" with type "+reportType+" not supported");
			}
			HSSFWorkbook wb = null;
			
			try {
				
				HttpServletRequest request = null;
				HttpServletResponse response = null;
				wb = createExcelReport(reportName, reportType, params, out,request,response);
			} catch (DocumentException e) {
				e.printStackTrace();
				log.error(e);
				throw new ReportException(e);
			}
			catch(IOException e)
			{
				e.printStackTrace();
				log.error(e);
				throw new ReportException(e);
			}
			return wb;
		}
	 
	
	public HSSFWorkbook createExcelReport(String reportName, String reportType,
				Map<?, ?> params, OutputStream out,
				HttpServletRequest request, HttpServletResponse response) throws ReportException, DocumentException,IOException {
		 HSSFWorkbook wb=createWorkbook();

	//	CreationHelper createHelper = wb.getCreationHelper();
		 HSSFSheet sheet = wb.createSheet("FS-Individual_Performance");
		 sheet.setMargin(HSSFSheet.LeftMargin, 0); 
		 sheet.setMargin(HSSFSheet.RightMargin, 0); 
		 HSSFPrintSetup ps = (HSSFPrintSetup) sheet.getPrintSetup();
		 ps.setPaperSize(HSSFPrintSetup.LETTER_PAPERSIZE);
		// sheet.setAutobreaks(true);
		 ps.setLandscape(true);
		
		 sheet.setColumnWidth(0, 0);
		   sheet.setColumnWidth(1, 3500);
		   sheet.setColumnWidth(2, 2250);
		   sheet.setColumnWidth(3, 2250);
		   sheet.setColumnWidth(4, 2250);
		   sheet.setColumnWidth(5, 2250);
		   sheet.setColumnWidth(6, 2250);
		   sheet.setColumnWidth(7, 2250);
		   sheet.setColumnWidth(8, 2250);
		   sheet.setColumnWidth(9, 2250);
		   sheet.setColumnWidth(10, 2250);
		   sheet.setColumnWidth(11, 2250);
		   sheet.setColumnWidth(12, 2250);
		   sheet.setColumnWidth(13, 2250);
		   sheet.setColumnWidth(14, 2250);
		   sheet.setColumnWidth(15,2250);
		   sheet.setColumnWidth(16, 2250);
		   sheet.setColumnWidth(17, 3000);
	//	   sheet.setFitToPage(true);
		  
		   HSSFFooter footer=sheet.getFooter();
		   footer.setCenter(params.get("year")+" "+params.get("userName")+" F/S-Individual Performance "+params.get("hotelName")+"                              Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );
			 HSSFCellStyle style = wb.createCellStyle();
			 HSSFCellStyle styleWithNoDecimals = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldTopBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldBottomBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldLeftBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldRightBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldTopLeftBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldBottomLeftBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldTopRightBorder = wb.createCellStyle();
			 HSSFCellStyle styleWithBoldBottomRightBorder = wb.createCellStyle();
			 HSSFCellStyle styleAlignRight = wb.createCellStyle();
			 HSSFCellStyle styleAlignRightWithBoldAndBorder = wb.createCellStyle();
			 HSSFCellStyle style1 = wb.createCellStyle();
			 HSSFCellStyle style2 = wb.createCellStyle();
			 HSSFCellStyle percentStyle = wb.createCellStyle();
			 HSSFCellStyle percentStyleWithNoDecimals = wb.createCellStyle();
			 percentStyle.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
			 percentStyleWithNoDecimals.setDataFormat(wb.createDataFormat().getFormat("0%"));
			 style.setDataFormat(wb.createDataFormat().getFormat("#,##0.00"));
			 styleWithNoDecimals.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
		
		 
		     HSSFFont font=wb.createFont();
		     HSSFFont fontSize=wb.createFont();
		     fontSize.setFontHeightInPoints((short)8);
		     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		     font.setFontHeightInPoints((short)8);
		     
		     styleWithBoldTopBorder.setFont(font);
		     styleWithBoldBottomBorder.setFont(font);
		     styleWithBoldLeftBorder.setFont(font);
		     styleWithBoldRightBorder.setFont(font);
		     styleWithBoldTopLeftBorder.setFont(font);
		     styleWithBoldBottomLeftBorder.setFont(font);
		     styleWithBoldTopRightBorder.setFont(font);
		     styleWithBoldBottomRightBorder.setFont(font);
		     
		     styleWithBoldTopBorder.setBorderTop((short) 1);
		     styleWithBoldBottomBorder.setBorderBottom((short) 1);
		     styleWithBoldLeftBorder.setBorderLeft((short) 1);
		     styleWithBoldRightBorder.setBorderRight((short)1);
		     
		     styleWithBoldTopLeftBorder.setBorderLeft((short) 1);
		     styleWithBoldTopLeftBorder.setBorderTop((short) 1);
		     styleWithBoldBottomLeftBorder.setBorderLeft((short) 1);
		     styleWithBoldBottomLeftBorder.setBorderBottom((short) 1);
		     
		     styleWithBoldTopRightBorder.setBorderRight((short) 1);
		     styleWithBoldTopRightBorder.setBorderTop((short) 1);
		     styleWithBoldBottomRightBorder.setBorderRight((short) 1);
		     styleWithBoldBottomRightBorder.setBorderBottom((short) 1);
		     styleWithBoldTopBorder.setWrapText(true);
		     
		     style1.setFont(font);
		     style2.setFont(font);
		     style.setFont(fontSize);
             styleWithNoDecimals.setFont(fontSize);
		     percentStyle.setFont(fontSize);
		     percentStyleWithNoDecimals.setFont(fontSize);
		     styleAlignRight.setFont(fontSize);
		    style2.setWrapText(true);
		    style2.setBorderBottom((short) 1);
			style2.setBorderTop((short) 1);
			style2.setBorderRight((short) 1);
			style2.setBorderLeft((short) 1);
		    style.setWrapText(true);
		    styleWithNoDecimals.setWrapText(true);
		    percentStyle.setWrapText(true);
		    percentStyleWithNoDecimals.setWrapText(true);
		    styleAlignRight.setWrapText(true);
		    styleAlignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		    
		    styleAlignRightWithBoldAndBorder.setWrapText(true);
		    styleAlignRightWithBoldAndBorder.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		    styleAlignRightWithBoldAndBorder.setFont(font);
		    styleAlignRightWithBoldAndBorder.setBorderBottom((short) 1);
		    styleAlignRightWithBoldAndBorder.setBorderTop((short) 1);
	     //First Row
		 // Create a row and put some cells in it. Rows are 0 based.
		 HSSFRow row0 = sheet.createRow(0);	
		 row0 = sheet.createRow(1);
		 HSSFCell cell=row0.createCell(1);
		 
		  cell.setCellValue(new HSSFRichTextString("F/S-Individual Performance "+String.valueOf(params.get("hotelName"))+" "+(Integer)params.get("year")));   
		  cell.setCellStyle(style1);
		 // Create a cell and put a value in it.
		 
		  //Second Row
			 HSSFRow row1 = sheet.createRow(3);
			 HSSFCell cell0r1 = row1.createCell(0);
			 HSSFCell cell1r1 = row1.createCell(1);
			 HSSFCell cell2r1 = row1.createCell(2);
			 HSSFCell cell3r1 = row1.createCell(3);
			 HSSFCell cell4r1 = row1.createCell(4);
			 HSSFCell cell5r1 = row1.createCell(5);
			 HSSFCell cell6r1 = row1.createCell(6);
			 cell0r1.setCellValue(new HSSFRichTextString(""));
			 cell0r1.setCellStyle(style);
		     cell1r1.setCellValue(new HSSFRichTextString("Hotel"));
		     cell1r1.setCellStyle(styleWithBoldTopLeftBorder);
		     cell2r1.setCellValue(new HSSFRichTextString((String)params.get("hotelName")));
		     cell2r1.setCellStyle(styleWithBoldTopBorder);
		     cell3r1.setCellValue(new HSSFRichTextString(""));
			 cell3r1.setCellStyle(styleWithBoldTopBorder);
			 cell4r1.setCellValue(new HSSFRichTextString(""));
			sheet.addMergedRegion(new CellRangeAddress(3,3,2,4));
			 cell4r1.setCellStyle(styleWithBoldTopBorder);
			 cell5r1.setCellValue(new HSSFRichTextString("Year"));
			 cell5r1.setCellStyle(styleWithBoldTopBorder);
			 cell6r1.setCellValue(new HSSFRichTextString(String.valueOf(params.get("year"))));
			 cell6r1.setCellStyle(styleWithBoldTopRightBorder);
		     
		     
		        //Third Row
		        HSSFRow row2 = sheet.createRow(4);
		        HSSFCell cell0r2 = row2.createCell(0);
				HSSFCell cell1r2 = row2.createCell(1);
				HSSFCell cell2r2 = row2.createCell(2);
				HSSFCell cell3r2 = row2.createCell(3);
				HSSFCell cell4r2 = row2.createCell(4);
				HSSFCell cell5r2 = row2.createCell(5);
				HSSFCell cell6r2 = row2.createCell(6);
				cell0r2.setCellValue(new HSSFRichTextString(""));
				cell0r2.setCellStyle(style);
				
				if (params.get("userName").equals("Sales Team") || params.get("userName").equals("Catering Team")) {
					  cell1r2.setCellValue(new HSSFRichTextString("Team"));
					    cell1r2.setCellStyle(styleWithBoldBottomLeftBorder);
					    cell2r2.setCellValue(new HSSFRichTextString(String.valueOf(params.get("userName"))));
					    cell2r2.setCellStyle(styleWithBoldBottomBorder);
									} else {
										cell1r2.setCellValue(new HSSFRichTextString("User Name"));
									    cell1r2.setCellStyle(styleWithBoldBottomLeftBorder);
									    cell2r2.setCellValue(new HSSFRichTextString(String.valueOf(params.get("userName"))));
									    cell2r2.setCellStyle(styleWithBoldBottomBorder);
									    }
	
			    cell3r2.setCellValue(new HSSFRichTextString(""));
				cell3r2.setCellStyle(styleWithBoldBottomBorder);
				cell4r2.setCellValue(new HSSFRichTextString(""));
				cell4r2.setCellStyle(styleWithBoldBottomBorder);
				cell5r2.setCellValue(new HSSFRichTextString(""));
				cell5r2.setCellStyle(styleWithBoldBottomBorder);
				cell5r2.setCellValue(new HSSFRichTextString(""));
				cell5r2.setCellStyle(styleWithBoldBottomBorder);
			    cell6r2.setCellValue(new HSSFRichTextString(""));
			    cell6r2.setCellStyle(styleWithBoldBottomRightBorder);
			    int count=6;
		
			    
				List<SegmentPerformanceData> values = (List<SegmentPerformanceData>) params.get("segmentDataList");
				List<SegmentPerformanceData> valuesForBonus = (List<SegmentPerformanceData>) params.get("bonusRewarded");
				List<TierPerformanceData> teamValues = (List<TierPerformanceData>) params.get("teamDataList");
				
				log.info("isTeamSelected"+(Boolean) params.get("isTeamSelected"));
				if((Boolean) params.get("isTeamSelected")){
					count = addTeamUserDetails(count, null, null, teamValues, params, wb, sheet);
				}

				count = addTeamUserDetails(count, valuesForBonus, values, null, params, wb, sheet);
			    
				short numberOfPagesDataSpans = (short) (count / 27); 
				ps.setFitHeight( numberOfPagesDataSpans ); 
				ps.setScale((short) 75);
		 			
			return wb;
			
	}

	private int addTeamUserDetails(int count, List<SegmentPerformanceData> valuesForBonus, List<SegmentPerformanceData> values, List<TierPerformanceData> teamValues, Map<?, ?> params,HSSFWorkbook wb,HSSFSheet sheet){
		HSSFCellStyle style = wb.createCellStyle();
		 HSSFCellStyle styleWithNoDecimals = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldTopBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldBottomBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldLeftBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldRightBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldTopLeftBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldBottomLeftBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldTopRightBorder = wb.createCellStyle();
		 HSSFCellStyle styleWithBoldBottomRightBorder = wb.createCellStyle();
		 HSSFCellStyle styleAlignRight = wb.createCellStyle();
		 HSSFCellStyle styleAlignRightWithBoldAndBorder = wb.createCellStyle();
		 HSSFCellStyle style1 = wb.createCellStyle();
		 HSSFCellStyle style2 = wb.createCellStyle();
		 HSSFCellStyle percentStyle = wb.createCellStyle();
		 HSSFCellStyle percentStyleWithNoDecimals = wb.createCellStyle();
		 percentStyle.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
		 percentStyleWithNoDecimals.setDataFormat(wb.createDataFormat().getFormat("0%"));
		 style.setDataFormat(wb.createDataFormat().getFormat("#,##0.00"));
		 styleWithNoDecimals.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
	
	 
	     HSSFFont font=wb.createFont();
	     HSSFFont fontSize=wb.createFont();
	     fontSize.setFontHeightInPoints((short)8);
	     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	     font.setFontHeightInPoints((short)8);
	     
	     styleWithBoldTopBorder.setFont(font);
	     styleWithBoldBottomBorder.setFont(font);
	     styleWithBoldLeftBorder.setFont(font);
	     styleWithBoldRightBorder.setFont(font);
	     styleWithBoldTopLeftBorder.setFont(font);
	     styleWithBoldBottomLeftBorder.setFont(font);
	     styleWithBoldTopRightBorder.setFont(font);
	     styleWithBoldBottomRightBorder.setFont(font);
	     
	     styleWithBoldTopBorder.setBorderTop((short) 1);
	     styleWithBoldBottomBorder.setBorderBottom((short) 1);
	     styleWithBoldLeftBorder.setBorderLeft((short) 1);
	     styleWithBoldRightBorder.setBorderRight((short)1);
	     
	     styleWithBoldTopLeftBorder.setBorderLeft((short) 1);
	     styleWithBoldTopLeftBorder.setBorderTop((short) 1);
	     styleWithBoldBottomLeftBorder.setBorderLeft((short) 1);
	     styleWithBoldBottomLeftBorder.setBorderBottom((short) 1);
	     
	     styleWithBoldTopRightBorder.setBorderRight((short) 1);
	     styleWithBoldTopRightBorder.setBorderTop((short) 1);
	     styleWithBoldBottomRightBorder.setBorderRight((short) 1);
	     styleWithBoldBottomRightBorder.setBorderBottom((short) 1);
	     styleWithBoldTopBorder.setWrapText(true);
	     
	     style1.setFont(font);
	     style2.setFont(font);
	     style.setFont(fontSize);
        styleWithNoDecimals.setFont(fontSize);
	     percentStyle.setFont(fontSize);
	     percentStyleWithNoDecimals.setFont(fontSize);
	     styleAlignRight.setFont(fontSize);
	    style2.setWrapText(true);
	    style2.setBorderBottom((short) 1);
		style2.setBorderTop((short) 1);
		style2.setBorderRight((short) 1);
		style2.setBorderLeft((short) 1);
	    style.setWrapText(true);
	    styleWithNoDecimals.setWrapText(true);
	    percentStyle.setWrapText(true);
	    percentStyleWithNoDecimals.setWrapText(true);
	    styleAlignRight.setWrapText(true);
	    styleAlignRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	    
	    styleAlignRightWithBoldAndBorder.setWrapText(true);
	    styleAlignRightWithBoldAndBorder.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	    styleAlignRightWithBoldAndBorder.setFont(font);
	    styleAlignRightWithBoldAndBorder.setBorderBottom((short) 1);
	    styleAlignRightWithBoldAndBorder.setBorderTop((short) 1);
	    
	    HSSFCell cell=sheet.createRow(count).createCell(1);
	    cell.setCellValue(new HSSFRichTextString((valuesForBonus != null ) ? "User Details":"Team Totals"));
		HSSFCellStyle boldTxtStyle = getCellStyleSheet(wb, (short)12, true, "l", false, (short)0, (short)0, (short)0, (short)0, "");;
		cell.setCellStyle(boldTxtStyle); 
		count++;
		log.info("count value--------->"+count);
		
		if(values != null && values.size() > 0){
			Iterator<SegmentPerformanceData> iter = values.iterator();
			log.info("entered in values"+values.size());
			while(iter.hasNext()) {
				SegmentPerformanceData segmentPerformanceData = iter.next();
				if (segmentPerformanceData.getSegmentName().equals("All Segments")) {
					
					if (params.get("userName").equals("Sales Team") || params.get("userName").equals("Catering Team")) {
						try{
						if(count!=6)
						{
						 count--;
						 sheet.setRowBreak(count-1);  
					     count++;
						}
					 	 //at 480
						/*if(count==480){
							count++;
						}*/
						 HSSFRow row = sheet.createRow(count);
						 cell = row.createCell(1);
						 cell.setCellValue(new HSSFRichTextString("User"));
						 cell.setCellStyle(styleWithBoldTopLeftBorder);
						 
						 cell = row.createCell(2);
						 cell.setCellValue(new HSSFRichTextString(segmentPerformanceData.getUserName()));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(3);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(3);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(4);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(5);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(6);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(7);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(8);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(9);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(10);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(11);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(12);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(13);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(14);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(15);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(16);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(17);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopBorder);
						 
						 cell = row.createCell(18);
						 cell.setCellValue(new HSSFRichTextString(""));
						 cell.setCellStyle(styleWithBoldTopRightBorder);
						 sheet.addMergedRegion(new CellRangeAddress(count,count,2,18));
						
						 						
						
					}catch(Exception ex){
					ex.printStackTrace();	
					}
					}
				}
				
				List<Double> bonusDataList = null;
				if(segmentPerformanceData.getSegmentName().equalsIgnoreCase("All Segments")){
			if(valuesForBonus != null && valuesForBonus.size()>0){
				if(valuesForBonus  !=null && valuesForBonus.get(0) instanceof SegmentPerformanceData){
				Iterator<SegmentPerformanceData> iterBonus = valuesForBonus.iterator();
				bonusDataList = new ArrayList<Double>();
				while(iterBonus.hasNext()){
					Object obj = iterBonus.next();
					if(obj instanceof SegmentPerformanceData){
						SegmentPerformanceData dataBonus = (SegmentPerformanceData)obj;
						bonusDataList.add(dataBonus.getBonusData());
			
					}
				}
		
				
				}else{
					Iterator<SegmentPerformanceData> iterBonus = valuesForBonus.iterator();
					 bonusDataList = new ArrayList<Double>();
					while(iterBonus.hasNext()){
						List bonusList = (List)iterBonus.next();
						Iterator iterBonusList = bonusList.iterator();
						List<Double> temp = new ArrayList<Double>();
						while(iterBonusList.hasNext()){
							
							SegmentPerformanceData dataBonus = (SegmentPerformanceData)iterBonusList.next();
							if(segmentPerformanceData.getUserId().equals(dataBonus.getUserId())){
								bonusDataList.add(dataBonus.getBonusData());
							}
						
						}
						
					}
					
				}
			}
			}
				
				
				count++;
				HSSFRow row = sheet.createRow(count);
				cell =row.createCell(1);
				cell.setCellValue(new HSSFRichTextString(segmentPerformanceData.getSegmentName()));
			    cell.setCellStyle(style2);
				 
				cell = row.createCell(2);
				cell.setCellValue(new HSSFRichTextString("Jan"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(3);
				cell.setCellValue(new HSSFRichTextString("Feb"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(4);
				cell.setCellValue(new HSSFRichTextString("Mar"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(5);
				cell.setCellValue(new HSSFRichTextString("Qtr1"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(6);
				cell.setCellValue(new HSSFRichTextString("Apr"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(7);
				cell.setCellValue(new HSSFRichTextString("May"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(8);
				cell.setCellValue(new HSSFRichTextString("Jun"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(9);
				cell.setCellValue(new HSSFRichTextString("Qtr2"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(10);
				cell.setCellValue(new HSSFRichTextString("Jul"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(11);
				cell.setCellValue(new HSSFRichTextString("Aug"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(12);
				cell.setCellValue(new HSSFRichTextString("Sept"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(13);
				cell.setCellValue(new HSSFRichTextString("Qtr3"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(14);
				cell.setCellValue(new HSSFRichTextString("Oct"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(15);
				cell.setCellValue(new HSSFRichTextString("Nov"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(16);
				cell.setCellValue(new HSSFRichTextString("Dec"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(17);
				cell.setCellValue(new HSSFRichTextString("Qtr4"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(18);
				cell.setCellValue(new HSSFRichTextString("Total"));
				cell.setCellStyle(style2);
			
				count++;
				
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Revenue Goals"));
				cell.setCellStyle(style1);
				addCells(2,segmentPerformanceData.getRevenueGoalsDataList(),row,style,styleWithNoDecimals);
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Actual Revenue"));
				cell.setCellStyle(style1);
				addCells(2,segmentPerformanceData.getActualRevenueDataList(),row,style,styleWithNoDecimals);
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Variance"));
				cell.setCellStyle(style1);
				addCells(2,segmentPerformanceData.getVarianceDataList(),row,style,styleWithNoDecimals);
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Performance %"));
				cell.setCellStyle(style1);
				addCell(2,segmentPerformanceData.getVarianceDataListInPercentage(),row,percentStyle,percentStyleWithNoDecimals);
				if(bonusDataList != null){
					
					count++;
					row = sheet.createRow(count);
					cell = row.createCell(1);
					cell.setCellValue(new HSSFRichTextString("Individual Bonus"));
					cell.setCellStyle(style1);
					addCells(2,bonusDataList,row,style,styleWithNoDecimals);
				}
				
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Qrtr%-Goal"));
				cell.setCellStyle(style1);
				addCell(2,segmentPerformanceData.getQuarterGoalsPercent(),row,style,percentStyleWithNoDecimals);
				
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Qrtr%-Actual"));
				cell.setCellStyle(style1);
				addCell(2,segmentPerformanceData.getQuarterActualPercent(),row,style,percentStyleWithNoDecimals);
		
				count++;
				row = sheet.createRow(count);
			}			
			count++;
		}
		
		if(teamValues != null && teamValues.size() > 0){			
			Iterator<TierPerformanceData> iter = teamValues.iterator();
			while(iter.hasNext()) {
				TierPerformanceData tierPerformanceData = iter.next();
				HSSFRow row = sheet.createRow(count);
				cell =row.createCell(1);
				cell.setCellValue(new HSSFRichTextString(tierPerformanceData.getTierName()));
			    cell.setCellStyle(style2);
				 
				cell = row.createCell(2);
				cell.setCellValue(new HSSFRichTextString("Jan"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(3);
				cell.setCellValue(new HSSFRichTextString("Feb"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(4);
				cell.setCellValue(new HSSFRichTextString("Mar"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(5);
				cell.setCellValue(new HSSFRichTextString("Qtr1"));
				cell.setCellStyle(style2);
									
				cell = row.createCell(6);
				cell.setCellValue(new HSSFRichTextString("Apr"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(7);
				cell.setCellValue(new HSSFRichTextString("May"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(8);
				cell.setCellValue(new HSSFRichTextString("Jun"));
				cell.setCellStyle(style2);
								
				cell = row.createCell(9);
				cell.setCellValue(new HSSFRichTextString("Qtr2"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(10);
				cell.setCellValue(new HSSFRichTextString("Jul"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(11);
				cell.setCellValue(new HSSFRichTextString("Aug"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(12);
				cell.setCellValue(new HSSFRichTextString("Sept"));
				cell.setCellStyle(style2);
						
				cell = row.createCell(13);
				cell.setCellValue(new HSSFRichTextString("Qtr3"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(14);
				cell.setCellValue(new HSSFRichTextString("Oct"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(15);
				cell.setCellValue(new HSSFRichTextString("Nov"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(16);
				cell.setCellValue(new HSSFRichTextString("Dec"));
				cell.setCellStyle(style2);
							
				cell = row.createCell(17);
				cell.setCellValue(new HSSFRichTextString("Qtr4"));
				cell.setCellStyle(style2);
				
				cell = row.createCell(18);
				cell.setCellValue(new HSSFRichTextString("Total"));
				cell.setCellStyle(style2);
				
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Revenue Goals"));
				cell.setCellStyle(style1);
				addCells(2,tierPerformanceData.getRevenueGoalsDataList(),row,style,styleWithNoDecimals);
				
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Actual Revenue"));
				cell.setCellStyle(style1);
				addCells(2,tierPerformanceData.getActualRevenueDataList(),row,style,styleWithNoDecimals);
				
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Variance"));
				cell.setCellStyle(style1);
				addCells(2,tierPerformanceData.getVarianceDataList(),row,style,styleWithNoDecimals);
				
				count++;
				row = sheet.createRow(count);
				cell = row.createCell(1);
				cell.setCellValue(new HSSFRichTextString("Performance %"));
				cell.setCellStyle(style1);
				addCell(2,tierPerformanceData.getVarianceDataListInPercentage(),row,percentStyle,percentStyleWithNoDecimals);
				
				if(tierPerformanceData.getTierName().equalsIgnoreCase("All")){
					if(tierPerformanceData.getMonthlyBonusList() != null && tierPerformanceData.getMonthlyBonusList().size() > 0){
						count++;
						row = sheet.createRow(count);
						cell = row.createCell(1);
						cell.setCellValue(new HSSFRichTextString("Individual Bonus"));
						cell.setCellStyle(style1);
						addCells(2,tierPerformanceData.getMonthlyBonusList(),row,style,styleWithNoDecimals);
					}
				}
				
				try{
					count++;
					row = sheet.createRow(count);
					cell = row.createCell(1);
					cell.setCellValue(new HSSFRichTextString("Qrtr%-Goal"));
					cell.setCellStyle(style1);
					addCell(2,tierPerformanceData.getQuarterGoalsPercent(),row,percentStyle,percentStyleWithNoDecimals);
					
					count++;
					row = sheet.createRow(count);
					cell = row.createCell(1);
					cell.setCellValue(new HSSFRichTextString("Qrtr%-Actual"));
					cell.setCellStyle(style1);
					addCell(2,tierPerformanceData.getQuarterActualPercent(),row,percentStyle,percentStyleWithNoDecimals);
			
				}catch (Exception e) {
					e.printStackTrace();
				}
				
				count++;
				row = sheet.createRow(count);
				count++;
			}
		}
		
		return count;
	}

	private void addCells(int cellCount,List<Double> values,HSSFRow row,HSSFCellStyle style,HSSFCellStyle styleWithNoDecimals) {
		int listCount=0;
	//	DecimalFormat twoDForm = new DecimalFormat("#.##");
		for (Double val : values) {
			listCount++;
		
			HSSFCell cell = row.createCell(cellCount);
	
	/*		if(listCount==values.size() || listCount==4 || listCount==8 || listCount==12 || listCount==16)
			{
				cell.setCellValue(val);
				cell.setCellStyle(styleWithNoDecimals);
			}
			
			else
			{
			cell.setCellValue(Double.valueOf(twoDForm.format(val)));
			cell.setCellStyle(style); 
			}
			*/
			
			cell.setCellValue(val);
			cell.setCellStyle(styleWithNoDecimals);
		
			
			cellCount++;

		}
	}
		
		private void addCell(int cellCount,List<Double> values,HSSFRow row,HSSFCellStyle percentStyle,HSSFCellStyle percentStyleWithNoDecimals) {
			int listCount=0;
			for (Double val : values) {
				listCount++;
				HSSFCell cell = row.createCell(cellCount);
	/*			if(listCount==values.size() || listCount==4 || listCount==8 || listCount==12 || listCount==16) logic for total only
				{
					val=(double) Math.round(val);
				cell.setCellValue(val/100);
				cell.setCellStyle(percentStyleWithNoDecimals);
				}
				else
				{
					cell.setCellValue(val/100);
					cell.setCellStyle(percentStyle);
				}*/
				//val= (val== null) ? 0.0 : (double) Math.round(val);
				if(val!=null){
					val= (double) Math.round(val);
					cell.setCellValue(val/100);
					cell.setCellStyle(percentStyleWithNoDecimals);
				}else{
					
				}
				
				
				
				
				cellCount++;

			}
		// TODO Auto-generated method stub
		
	}
}