/**
 * 
 */
package com.ejie.ad74a.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
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.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.ejie.ad74a.exception.GenerateExcelException;
import com.ejie.ad74a.model.Copago;
import com.ejie.ad74a.model.ReciboAyto;
import com.ejie.ad74a.model.ReciboAytoPisoTutelado;
import com.ejie.ad74a.model.ReciboAytoResidente;
import com.ejie.ad74a.service.InformeService;
import com.ejie.ad74a.util.UtilPOI;
import com.ejie.ad74a.util.UtilPrecios;
import com.ejie.ad74a.util.UtilProperties;

/**
 * Implementacion de la interfaz de servicios para realizar informes
 * @author BICUGUAL
 *
 */
@Service
public class InformeServiceImpl implements InformeService {

	private static final Logger logger = LoggerFactory.getLogger(InformeServiceImpl.class);
	
	@Override
	public void printInformeFacturacionAyto(ReciboAyto recibo) {
		logger.debug("-----------------> Inicio recibo: {}", recibo.getNombreAyto());
		logger.debug("{}", recibo.getNombreAyto());
		logger.debug("{}",recibo.getDireccion());
		logger.debug("{} {}",recibo.getCp(), recibo.getMunicipio());
		logger.debug("{}",recibo.getProvincia());
		logger.debug("CIF: {}",recibo.getCif());
		
		logger.debug("Perido: {}",recibo.getPeriodo());
		logger.debug("Recibo: {}",recibo.getRecibo());
		logger.debug("Descripcion ES: {}",recibo.getDescripcionES());
		logger.debug("Descripcion EU: {}",recibo.getDescripcionEU());
		
		for (ReciboAytoPisoTutelado piso: recibo.getLstPisosTutelados()) {
			logger.debug("Helbidea/Direccion: {}", piso.getDireccion());
			
			int countResidentes = 1; 
			for (ReciboAytoResidente residente  : piso.getLstResidentes()) {
				
				int countCopago = 0;
				for (Copago copago : residente.getLstCopagos()) {
					
					String numConResidente = countCopago == 0 ? String.valueOf(countResidentes).concat(". ").concat(residente.getResidente()) : "";
					String dni = countCopago == 0 ? residente.getDni() : "";
					String importeMensualCopago = UtilPrecios.importeToString(copago.getImporteMensual());
					String importeCopago =  UtilPrecios.importeToString(copago.getImporte());
					importeMensualCopago = copago.getImporteMensual().toString();
					importeCopago =  copago.getImporte().toString();
					
					logger.debug("{} | {} | {} | {} | {} | {} | {}", numConResidente, dni, copago.getfInicio() + " a " + copago.getfFin(), copago.getnMeses(), copago.getnDias(), importeMensualCopago, importeCopago);
					
					countCopago++;
				}
				
				countResidentes++;
			}			
		}
		
		logger.debug("Cantidad total a ingresar: {}",recibo.getCantidadAIngresar());
		logger.debug("Concepto: {}", recibo.getConcepto());
		logger.debug("<------------------ Fin recibo: {}", recibo.getNombreAyto());
	}

	@Override
	public byte[] generarInforme(ReciboAyto recibo) throws Exception {
		
		String pathPlantilla = UtilProperties.Props.PATH_TEMPLATE_EXCEL_COPAGOS_AYTO.val();

		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		Workbook workbook = null;
		InputStream templateIS = null;
		
		try {
			// Obtiene la plantilla
			templateIS = InformeServiceImpl.class.getResourceAsStream(pathPlantilla);

			// Crea recurso a partir de la plantilla
			workbook = WorkbookFactory.create(templateIS);
			
			// Obtiene la hoja cuyo nombre es el pasado como parametro dentro del workbook
			Sheet sheet = workbook.getSheetAt(0);
		
			//Fila inicial
			int rowCounter = 2;
			
			//Membrete
			rowCounter = this.writeAytoInfo(sheet, rowCounter, recibo);
			//Cabecera
			rowCounter = this.writeHeader(sheet, rowCounter, recibo);
			//Iteracion de pisos y residentes
			rowCounter = this.writeLstPisosTutelados(sheet, rowCounter, recibo);
			//Resumen
			this.writeResumen(sheet, rowCounter, recibo);
			
			// Escribe el formulario de Excel en la ruta especificada
			workbook.write(bos);

			bos.close();
			workbook.close();
			templateIS.close();

		} catch (Exception e) {
			throw new GenerateExcelException("Error en la generacion del informe Excel.", e);

		} finally {

			// Cerrar el recursos
			IOUtils.closeQuietly(workbook);
			IOUtils.closeQuietly(templateIS);
			IOUtils.closeQuietly(bos);

		}
	
		return bos.toByteArray();
	}

	/**
	 * Escribe en el Excel el apartado de datos de Ayuntamiento devolviendo el numero de fila actual
	 * @param sheet
	 * @param rowCounter
	 * @param recibo
	 * @return
	 */
	private int writeAytoInfo(Sheet sheet, int rowCounter, ReciboAyto recibo) {
		
		int cellDatosAyto = 8;
		
		final Font fontBoldUnderline12 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_SINGLE, 12);
		final Font fontBold12 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_NONE, 12);
		final CellStyle styleBoldUnderline12Center = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, fontBoldUnderline12, null, null);
		final CellStyle styleBold12Center = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, fontBold12, null, null);		

		sheet.addMergedRegion(CellRangeAddress.valueOf("I3:M3"));
		Cell aytoCell = sheet.getRow(rowCounter++).getCell(cellDatosAyto);
		aytoCell.setCellStyle(styleBoldUnderline12Center);
		aytoCell.setCellValue( StringUtils.upperCase(recibo.getNombreAyto()) );

		//fila en blanco
		rowCounter++;
		
		sheet.addMergedRegion(CellRangeAddress.valueOf("I5:M5"));
		Cell direccionCell = sheet.getRow(rowCounter++).getCell(cellDatosAyto);
		direccionCell.setCellStyle(styleBold12Center);
		direccionCell.setCellValue( recibo.getDireccion());
		
		sheet.addMergedRegion(CellRangeAddress.valueOf("I6:M6"));
		Cell cpCell = sheet.getRow(rowCounter++).getCell(cellDatosAyto);
		cpCell.setCellStyle(styleBold12Center);
		cpCell.setCellValue( recibo.getCp() + " " + recibo.getMunicipio());

		sheet.addMergedRegion(CellRangeAddress.valueOf("I7:M7"));
		Cell provinciaCell = sheet.getRow(rowCounter++).getCell(cellDatosAyto);
		provinciaCell.setCellStyle(styleBold12Center);
		provinciaCell.setCellValue(recibo.getProvincia());
		
		sheet.addMergedRegion(CellRangeAddress.valueOf("I8:M8"));
		Cell cifCell = sheet.getRow(rowCounter++).getCell(cellDatosAyto);
		cifCell.setCellStyle(styleBold12Center);
		cifCell.setCellValue("CIF: " + recibo.getCif());
		
		return rowCounter;
	}
	
	/**
	 * Escribe en el Excel la cabecera del informe devolviendo el numero de fila actual
	 * 
	 * @param sheet
	 * @param rowCounter
	 * @param recibo
	 * @return
	 */
	private int writeHeader(Sheet sheet, int rowCounter, ReciboAyto recibo) {

		final Font fontBold10 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_NONE, 10);
		final CellStyle styleBold10BorderCenter = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER,
				fontBold10, null, BorderStyle.THIN);

		// filas en blanco. Empezar en la fila 12
		rowCounter = rowCounter + 4;

		Cell fReciboCell = sheet.getRow(rowCounter).getCell(3);
		fReciboCell.setCellStyle(styleBold10BorderCenter);
		fReciboCell.setCellValue(recibo.getFechaRecibo());

		sheet.addMergedRegion(CellRangeAddress.valueOf("G13:I13"));
		Cell periodoCell = sheet.getRow(rowCounter).getCell(6);
		periodoCell.setCellStyle(styleBold10BorderCenter);
		periodoCell.setCellValue(recibo.getPeriodo());

		sheet.addMergedRegion(CellRangeAddress.valueOf("L13:M13"));
		Cell reciboCell = sheet.getRow(rowCounter).getCell(11);
		reciboCell.setCellStyle(styleBold10BorderCenter);
		reciboCell.setCellValue(recibo.getRecibo());

		return rowCounter;
	}
	
	/**
	 * Escribe en el Excel la tabla de los pisos con sus residentes devolviendo el numero de fila actual
	 * @param sheet
	 * @param rowCounter
	 * @param recibo
	 * @return
	 */
	private int writeLstPisosTutelados(Sheet sheet, int rowCounter, ReciboAyto recibo) {
		
		//Separacion de filas antes de pisos
		rowCounter = rowCounter + 6;
		
		for (ReciboAytoPisoTutelado piso: recibo.getLstPisosTutelados()) {
			//Cabecera pisos
			rowCounter = this.writePisoInfo(sheet, rowCounter, piso);
			
			//Cabecera residentes
			rowCounter = this.writeResidenteHeader(sheet, rowCounter);
			//Listado de residentes y copagos
			rowCounter = this.writeLstResidentes(sheet, rowCounter, piso);
		}

		return rowCounter;
	}
	
	/**
	 * Escribe en el Excel el apartado de cabecera de piso tutelado devolviendo el numero de fila actual
	 * @param sheet
	 * @param rowCounter
	 * @param piso
	 */
	private int writePisoInfo(Sheet sheet, int rowCounter, ReciboAytoPisoTutelado piso) {
		
		final Font fontBold10 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_NONE, 10);
		final CellStyle styleBold10BgCenter = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, fontBold10, IndexedColors.PALE_BLUE, null);
		final CellStyle styleBold10BgLeft = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.LEFT, fontBold10, IndexedColors.PALE_BLUE, null);
		
		Row rowPisoTitle = sheet.createRow(++rowCounter);
		
		CellRangeAddress cellRangePisoLabel = new CellRangeAddress(rowCounter, rowCounter, 1, 3);
		sheet.addMergedRegion(cellRangePisoLabel);
		
		Cell pisoLabelCell = rowPisoTitle.createCell(1);
		pisoLabelCell.setCellStyle(styleBold10BgLeft);
		pisoLabelCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_DIRECCION.val());
		
		CellRangeAddress cellRangePisoDesc = new CellRangeAddress(rowCounter, rowCounter, 4, 12);
		sheet.addMergedRegion(cellRangePisoDesc);
		
		Cell pisoValueCell = rowPisoTitle.createCell(4);
		pisoValueCell.setCellStyle(styleBold10BgCenter);
		pisoValueCell.setCellValue(piso.getDireccion());
		
		return rowCounter;
	}
	
	/**
	 * Escribe en el Excel el apartado de cabecera para la tabla de residentes devolviendo el numero de fila actual
	 * @param sheet
	 * @param rowCounter
	 * @return
	 */
	private int writeResidenteHeader(Sheet sheet, int rowCounter) {
		
		final Font fontBold8 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_NONE, 8);
		final Font fontBold7 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_NONE, 7);
		final CellStyle styleBold8BorderCenter = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, fontBold8, null, BorderStyle.THIN);
		final CellStyle styleBold7BorderCenter = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, fontBold7, null, BorderStyle.THIN);
		
		//Separacion de fila
		rowCounter = rowCounter + 2;
		
		Row rowResidenteTitle = sheet.createRow(rowCounter);
		rowResidenteTitle.setHeight((short) (20 * 30));

		Cell residenteTitleCell = rowResidenteTitle.createCell(2);
		residenteTitleCell.setCellStyle(styleBold8BorderCenter);
		residenteTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_RESIDENTES.val());
		CellRangeAddress cellRangeResidenteTitle = new CellRangeAddress(rowCounter, rowCounter, 2, 4);
		UtilPOI.frame(cellRangeResidenteTitle, sheet);
		
		Cell dniTitleCell = rowResidenteTitle.createCell(5);
		dniTitleCell.setCellStyle(styleBold8BorderCenter);
		dniTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_DNI.val());
		
		Cell periodoTitleCell = rowResidenteTitle.createCell(6);
		periodoTitleCell.setCellStyle(styleBold8BorderCenter);
		//periodoTitleCell.setCellValue("Fakturatze aldia/ \r\n" +"Periodo a facturar:");
		periodoTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_PERIODO.val());
		CellRangeAddress cellRangePeriodoTitle = new CellRangeAddress(rowCounter, rowCounter, 6, 8);				
		UtilPOI.frame(cellRangePeriodoTitle, sheet);
		
		Cell nMesesTitleCell = rowResidenteTitle.createCell(9);
		nMesesTitleCell.setCellStyle(styleBold7BorderCenter);
		nMesesTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_MESES.val());
		
		Cell nDiasTitleCell = rowResidenteTitle.createCell(10);
		nDiasTitleCell.setCellStyle(styleBold8BorderCenter);
		nDiasTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_DIAS.val());
		
		Cell copagoTitleCell = rowResidenteTitle.createCell(11);
		copagoTitleCell.setCellStyle(styleBold8BorderCenter);
		copagoTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_PRECIO.val());
		
		Cell totalTitleCell = rowResidenteTitle.createCell(12);
		totalTitleCell.setCellStyle(styleBold8BorderCenter);
		totalTitleCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_TOTAL.val());
		
		return rowCounter;
		
	}
	
	/**
	 * Escribe en el Excel las lineas de los residentes con sus respectivos copagos y devuelve el numero de linea actual
	 * @param sheet
	 * @param rowCounter
	 * @param nResidente
	 * @param residente
	 * @return
	 */
	private int writeLstResidentes(Sheet sheet, int rowCounter, ReciboAytoPisoTutelado piso) {
		
		final Font font7 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.FALSE, Font.U_NONE, 7);
		final Font font8 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.FALSE, Font.U_NONE, 8);
		final CellStyle style8Center = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, font8, null, BorderStyle.NONE);
		final CellStyle style7Left = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.LEFT, font7, null, BorderStyle.NONE);
		final CellStyle style8Right= UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.RIGHT, font8, null, BorderStyle.NONE);
		
		int contResidentes = 0; 
		
		for (ReciboAytoResidente residente: piso.getLstResidentes()) {
			
			contResidentes++;
			int nLineaCopago = 1;
			
			for (Copago copago: residente.getLstCopagos()) {
				rowCounter++;
				
				Row rowCopago = sheet.createRow(rowCounter);
				Cell residenteCounterCell = null;
				
				if (nLineaCopago == 1) {
				
					residenteCounterCell = rowCopago.createCell(1);
					residenteCounterCell.setCellStyle(style8Right);
					residenteCounterCell.setCellValue(contResidentes);
					nLineaCopago++;

					Cell residenteCell = rowCopago.createCell(2);
					residenteCell.setCellStyle(style7Left);
					residenteCell.setCellValue(residente.getResidente());
					
					CellRangeAddress cellRangeResidente = new CellRangeAddress(rowCounter, rowCounter, 2, 4);
					sheet.addMergedRegion(cellRangeResidente);
					
					Cell dniCell = rowCopago.createCell(5);
					dniCell.setCellStyle(style8Center);
					dniCell.setCellValue(residente.getDni());

				}
				
				Cell copagoPeridoCell = rowCopago.createCell(6);
				copagoPeridoCell.setCellStyle(style8Center);
				copagoPeridoCell.setCellValue(copago.getDescripcionCopago());
				CellRangeAddress cellRangePeriodo = new CellRangeAddress(rowCounter, rowCounter, 6, 8);				
				sheet.addMergedRegion(cellRangePeriodo);
				
				Cell nMesesCell = rowCopago.createCell(9);
				nMesesCell.setCellStyle(style8Center);
				nMesesCell.setCellValue(0 == copago.getnMeses() ? "" : String.valueOf(copago.getnMeses()));
				
				Cell nDiasCell = rowCopago.createCell(10);
				nDiasCell.setCellStyle(style8Center);
				nDiasCell.setCellValue(0 == copago.getnDias() ? "" : String.valueOf(copago.getnDias()));
				
				Cell copagoCell = rowCopago.createCell(11);
				copagoCell.setCellStyle(style8Right);
				//copagoCell.setCellValue(UtilPrecios.importeToString(copago.getImporteMensual()));
				copagoCell.setCellValue(UtilPrecios.importeToStringSinEur(copago.getImporteMensual(), false));
				
				Cell totalCell = rowCopago.createCell(12);
				totalCell.setCellStyle(style8Right);
				//totalCell.setCellValue(UtilPrecios.importeToString(copago.getImporte()));
				totalCell.setCellValue(UtilPrecios.importeToStringSinEur(copago.getImporte(), true));
			}
		}
		
		return ++rowCounter;
	}
	
	/**
	 * Escribe en el Excel el apartado de resumen
	 * @param sheet
	 * @param rowCounter
	 * @param recibo
	 * @return
	 */
	private void writeResumen(Sheet sheet, int rowCounter, ReciboAyto recibo) {
		
		final Font fontBold10 = UtilPOI.getFont(sheet.getWorkbook(), Boolean.TRUE, Font.U_NONE, 10);
		final CellStyle styleBold10BgBorderRight = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.RIGHT, fontBold10, IndexedColors.PALE_BLUE, BorderStyle.THIN);
		final CellStyle styleBold10BgBorderCenter = UtilPOI.getCellStyle(sheet.getWorkbook(), HorizontalAlignment.CENTER, fontBold10, IndexedColors.PALE_BLUE, BorderStyle.THIN);
		
		Row rowTotal = sheet.createRow(++rowCounter);
					
		CellRangeAddress cellRangeTotalLabel = new CellRangeAddress(rowCounter, rowCounter, 1, 11);
		UtilPOI.frame(cellRangeTotalLabel, sheet);
		
		Cell totalLabelCell = rowTotal.createCell(1);
		totalLabelCell.setCellStyle(styleBold10BgBorderCenter);
		totalLabelCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_CANTIDAD.val());
		
		Cell totalCell = rowTotal.createCell(12);
		totalCell.setCellStyle(styleBold10BgBorderRight);
		totalCell.setCellValue(recibo.getCantidadAIngresar());
		
		//Filas en blanco
		rowCounter = rowCounter+ 2;
		
		Row rowConcepto = sheet.createRow(rowCounter);
		CellRangeAddress cellRangeConceptoLabel = new CellRangeAddress(rowCounter, rowCounter, 1, 12);
		UtilPOI.frame(cellRangeConceptoLabel, sheet);
		
		Cell conceptoLabelCell = rowConcepto.createCell(1);
		conceptoLabelCell.setCellStyle(styleBold10BgBorderCenter);
		conceptoLabelCell.setCellValue(UtilProperties.Props.COPAGO_AYTOS_INFOR_CONCEPTO.val() + " " + recibo.getConcepto());
		
	}
	
}
