package it.abaco.dla.report;

import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellReference;

import it.abaco.dla.report.util.XlsUtil;
import it.abaco.dla.tables.DatabaseAccess;
import it.abaco.dla.tables.TbBLCentriCosto;
import it.abaco.dla.tables.TbBLInvoiceDetails;
import it.abaco.dla.tables.TbBLPriceListStandard;
import it.abaco.dla.tables.TbBLUsageDetails;
import it.abaco.sc.Util;
import it.abaco.sc.email.Report;

public class BLSintesi_VerReport {
	static Logger logger = Logger.getLogger(BLSintesi_VerReport.class.getName());

	BLSintesi_XlsStyles styles;
	Hashtable<String, TbBLInvoiceDetails> invoiceDetails = new Hashtable<String, TbBLInvoiceDetails>();
	Hashtable<String, TbBLPriceListStandard> priceStandard = new Hashtable<String, TbBLPriceListStandard>();
	Hashtable<String, Integer> reqName_row = new Hashtable<String, Integer>();
	Hashtable<String, Integer> intestazione_col = new Hashtable<String, Integer>();
	
	public void createReport(String cliente, String userBL, String meseRif, String outPath) {
		logger.info("Start Report creation...");
		
//		String cliente = "Mediobanca Spa";
//		String userBL = "30241168";
//		String meseRif = "201510";
		
		try {
			TbBLUsageDetails details = new TbBLUsageDetails();
			String queryBillable = "WHERE utenza = '" + userBL + "' AND meseRiferimento = '" + meseRif + "' " +
									"UNION " + 
									"SELECT DISTINCT RTRIM(BILLABLE_PRODUCT) + ' Maint.ce' AS BILLABLE_PRODUCT " +
									"FROM BLUsageDetails " +
									"WHERE utenza = '" + userBL + "' AND meseRiferimento = '" + meseRif + "' AND CTRB_MAIN = 'Y' " +
									"ORDER BY BILLABLE_PRODUCT";
			logger.info("Individuo i billable product: SELECT DISTINCT BILLABLE_PRODUCT FROM BLUsageDetails " + queryBillable);
			Vector<TbBLUsageDetails> billableRow = details.retrieveDistinct(queryBillable, "BILLABLE_PRODUCT");
			logger.info("Load billable product...");
			
			String queryReq = "WHERE utenza = '" + userBL + "' AND meseRiferimento = '" + meseRif + "' ORDER BY req_name";
			logger.info("Individuo le richieste: SELECT DISTINCT req_name FROM BLUsageDetails " + queryReq);
			Vector<TbBLUsageDetails> reqColl = details.retrieveDistinct(queryReq, "req_name");
			logger.info("Load request name...");
			
			TbBLInvoiceDetails tbID = new TbBLInvoiceDetails();
			Vector<TbBLInvoiceDetails> vID =  tbID.retrieveAll("WHERE utenza = '" + userBL + "' AND startDate <= '" + meseRif + "01' AND endDate >= '" + meseRif + "01'");
			for (int i=0; i<vID.size(); i++)
				invoiceDetails.put(vID.elementAt(i).getBillableProduct(), vID.elementAt(i));
			
			TbBLPriceListStandard tbPL = new TbBLPriceListStandard();
			Vector<TbBLPriceListStandard> vPL =  tbPL.retrieveAll();
			for (int i=0; i<vPL.size(); i++)
				priceStandard.put(vPL.elementAt(i).getBillingItem(), vPL.elementAt(i));
			
			// Crea documento Excel
			HSSFWorkbook workbook = new HSSFWorkbook();
			styles = new BLSintesi_XlsStyles(workbook);
			
			Hashtable<String, Integer> rowIndex = new Hashtable<String, Integer>();
			Hashtable<String, Integer> colIndex = new Hashtable<String, Integer>();
			
			/*
			 * POI ha un bug per il quale se delle celle contengono delle formule che si riferiscono ad altri fogli _E_ viene modificato
			 * l'ordine dei fogli, la cella con le formule punta ad altri fogli.
			 * https://bz.apache.org/bugzilla/show_bug.cgi?id=58746
			 * A questo punto, creo fin da subito i fogli nell'ordine corretto e inserisco successivamente i valori.
			 */
			HSSFSheet centriDiCostoSheet = workbook.createSheet("CentriDiCosto_" + userBL + "_" + meseRif);
			HSSFSheet summarySheet = workbook.createSheet("Summary_" + userBL + "_" + meseRif);
			HSSFSheet initChargeSheet = workbook.createSheet("InitCharge");
			HSSFSheet multiRequestSheet = workbook.createSheet("MultiRequest");
			
			// imposto il foglio MULTIPLE REQUEST
			logger.info("Creo il foglio 'MultiRequest'");
			details_sheetTemplate(multiRequestSheet, billableRow, reqColl, rowIndex, colIndex);
			String query = "SELECT req_name, BILLABLE_PRODUCT, COUNT(*) AS tot " +
							"FROM BLUsageDetails " +
							"WHERE (utenza = '" + userBL + "') AND (meseRiferimento = '" + meseRif + "') AND (INIT_CHARGE = 'N') AND (CTRB_MH = 'Y') " +
							"GROUP BY req_name, BILLABLE_PRODUCT " +
							"ORDER BY req_name, BILLABLE_PRODUCT";
			details_sheetValues(query, multiRequestSheet, reqColl, rowIndex, colIndex);
			
			// Crea il foglio INITIAL CHARGE
			logger.info("Creo il foglio 'InitCharge'");
			details_sheetTemplate (initChargeSheet, billableRow, reqColl, rowIndex, colIndex);
			query = "SELECT req_name, BILLABLE_PRODUCT, COUNT(*) AS tot " +
					"FROM BLUsageDetails " + 
					"WHERE (utenza = '" + userBL + "') AND (meseRiferimento = '" + meseRif + "') AND (INIT_CHARGE = 'Y') AND (CTRB_MH = 'N') AND (CTRB_MAIN = 'N') " +
					"GROUP BY req_name, BILLABLE_PRODUCT " +

					"UNION " +

					"SELECT req_name, RTRIM(BILLABLE_PRODUCT) + ' Maint.ce' AS BILLABLE_PRODUCT, COUNT(*) AS tot " + 
					"FROM BLUsageDetails " + 
					"WHERE (utenza = '" + userBL + "') AND (meseRiferimento = '" + meseRif + "') AND (INIT_CHARGE = 'Y') AND (CTRB_MH = 'N') AND (CTRB_MAIN = 'Y') " + 
					"GROUP BY req_name, BILLABLE_PRODUCT " +
					
					"ORDER BY req_name, BILLABLE_PRODUCT";
			details_sheetValues(query, initChargeSheet, reqColl, rowIndex, colIndex);
			
			// Crea il foglio di SINTESI
			logger.info("Creo il foglio di Sintesi");
			summary_sheetTemplate (summarySheet, billableRow, reqColl, cliente, userBL, meseRif);
			
			// crea il foglio CENTRI DI COSTO
			centriDiCosto_template(centriDiCostoSheet, summarySheet, cliente, userBL, meseRif);
			centriDiCosto_values(centriDiCostoSheet, cliente, userBL, meseRif);
			
//			nomeFile[u] = getConfig().getChild("nomeFile").replaceAll("USER", blUser[u]).replaceAll(dateFormat, df.format(dataRiferimento.getTime()));
			FileOutputStream report = new FileOutputStream(outPath+"/MB_Report_" + userBL + "_" + meseRif + ".xls");
			workbook.write(report);
			report.close();
			logger.info("Creazione report terminata con successo!");
			
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "Sintesi Report", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}

		// Invio per email il report
//		sendEmail(nomeFile, dataRiferimento);
	}
	
	private void details_sheetTemplate(HSSFSheet sheet, Vector<TbBLUsageDetails> billableRow, Vector<TbBLUsageDetails> reqColl, Hashtable<String, Integer> rowIndex, Hashtable<String, Integer> colIndex) {
		try {
			int colIndex_costoUnitario = reqColl.size() + 6;
			// Crea nuovo foglio nel documento Excel (InitCharge)
			int r = 0;
			for (; r<billableRow.size(); r++) {
				// Identifico l'InvoiceDetails di riferimento
				String billableProd;
				if (billableRow.elementAt(r).getBILLABLE_PRODUCT().contains("Maint.ce")) {
					if (sheet.getSheetName().startsWith("Multi"))
						billableProd = "";
					else
						billableProd = "(Mtd) " + billableRow.elementAt(r).getBILLABLE_PRODUCT().replaceAll(" Maint.ce", "");
				} else if (sheet.getSheetName().contains("Init"))
					billableProd = "(Initial) " + billableRow.elementAt(r).getBILLABLE_PRODUCT();
				else
					billableProd = "(Multi) " + billableRow.elementAt(r).getBILLABLE_PRODUCT();
				TbBLInvoiceDetails invDet = invoiceDetails.get(billableProd);
				
				rowIndex.put(billableRow.elementAt(r).getBILLABLE_PRODUCT().trim(), new Integer(r+1));
				XlsUtil.addCell(sheet, r+1, 0, billableRow.elementAt(r).getBILLABLE_PRODUCT(), styles.formatoIntestazioneRow);
				// Totale Files
				XlsUtil.addCellFormula(sheet, r+1, reqColl.size()+2, "SUM(B" + (r+2) + ":" + XlsUtil.findColumnName(reqColl.size()) + (r+2) + ")", styles.formatoRigaTab_int);
				if (invDet == null || invDet.getPrice() == 0.0) {
					logger.info("Manca InvoiceDetails per '" + billableProd + "' ... prendo il prezzo standard");
					TbBLPriceListStandard ps = priceStandard.get(billableProd);
					if (ps != null)
						XlsUtil.addCell(sheet, r+1, colIndex_costoUnitario, ps.getCosto(), styles.formatoRigaTab_double);
					else
						logger.info("Manca PriceStandard per '" + billableProd + "'");
				} else
					XlsUtil.addCell(sheet, r+1, colIndex_costoUnitario, invDet.getPrice(), styles.formatoRigaTab_double);
				if (invDet != null) {
					XlsUtil.addCell(sheet, r+1, reqColl.size()+3, invDet.getQuantity(), styles.formatoRigaTab_int);
					XlsUtil.addCellFormula(sheet, r+1, reqColl.size()+4, XlsUtil.findColumnName(reqColl.size()+2) + (r+2) + "-" + XlsUtil.findColumnName(reqColl.size()+3) + (r+2), styles.formatoRigaTab_int);
				}
				
				// Costo Totale
				XlsUtil.addCellFormula(sheet, r+1, reqColl.size()+7, XlsUtil.findColumnName(reqColl.size()+2) + (r+2) + "*" + XlsUtil.findColumnName(colIndex_costoUnitario) + (r+2), styles.formatoRigaTab_double);
			}
			XlsUtil.addCell(sheet, ++r, 0, "Totale complessivo", styles.formatoIntestazioneRow);
			
			int c = 0;
			for (; c<=reqColl.size(); c++) {
				if (c==0)
					sheet.setColumnWidth(c, 27*256);
				else {
					colIndex.put(reqColl.elementAt(c-1).getReq_name().trim(), new Integer(c));
					sheet.setColumnWidth(c, 7*256);
					XlsUtil.addCell(sheet, 0, c, reqColl.elementAt(c-1).getReq_name(), styles.formatoIntestazioneColl);
					String nomeColonna = XlsUtil.findColumnName(c);
					XlsUtil.addCellFormula(sheet, r, c, "SUM(" + nomeColonna + "1:" + nomeColonna + r + ")", styles.formatoResult_int);
					
					// Creo le formule per il calcolo dei costi richiesta per richiesta
					int colIndex_costo = c + reqColl.size() + 8;
					sheet.setColumnWidth(colIndex_costo, 10*256);
					XlsUtil.addCell(sheet, 0, colIndex_costo, reqColl.elementAt(c-1).getReq_name(), styles.formatoIntestazioneColl);
					for (int rCosto=0; rCosto<billableRow.size(); rCosto++)
						XlsUtil.addCellFormula(sheet, rCosto+1, colIndex_costo, XlsUtil.findColumnName(c) + (rCosto+2) + "*" + XlsUtil.findColumnName(colIndex_costoUnitario) + (rCosto+2), styles.formatoRigaTab_double);
					XlsUtil.addCellFormula(sheet, r, colIndex_costo, "SUM(" + XlsUtil.findColumnName(colIndex_costo) + "1:" + XlsUtil.findColumnName(colIndex_costo) + r + ")", styles.formatoResult_double);
				}
			}
			
			XlsUtil.addCell(sheet, 0, ++c, "Totale Files", styles.formatoIntestazioneColl);
			XlsUtil.addCellFormula(sheet, r, c, "SUM(" + XlsUtil.findColumnName(c) + "1:" + XlsUtil.findColumnName(c) + r + ")", styles.formatoResult_int);
			XlsUtil.addCell(sheet, 0, ++c, "Totale Fattura", styles.formatoIntestazioneColl);
			XlsUtil.addCellFormula(sheet, r, c, "SUM(" + XlsUtil.findColumnName(c) + "1:" + XlsUtil.findColumnName(c) + r + ")", styles.formatoResult_int);
			XlsUtil.addCell(sheet, 0, ++c, "Delta", styles.formatoIntestazioneColl);
			XlsUtil.addCellFormula(sheet, r, c, "SUM(" + XlsUtil.findColumnName(c) + "1:" + XlsUtil.findColumnName(c) + r + ")", styles.formatoResult_int);
			c ++;
			XlsUtil.addCell(sheet, 0, ++c, "Costo Unitario", styles.formatoIntestazioneColl);
			sheet.setColumnWidth(++c, 12*256);
			XlsUtil.addCell(sheet, 0, c, "Costo Totale", styles.formatoIntestazioneColl);
			XlsUtil.addCellFormula(sheet, r, c, "SUM(" + XlsUtil.findColumnName(c) + "1:" + XlsUtil.findColumnName(c) + r + ")", styles.formatoResult_double);
			
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "Sintesi Report", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}
		
		sheet.setZoom(85, 100);
		sheet.createFreezePane(1, 1);
	}
	
	private void details_sheetValues (String query, HSSFSheet sheet, Vector<TbBLUsageDetails> reqColl, Hashtable<String, Integer> rowIndex, Hashtable<String, Integer> colIndex) {
		try {
			DatabaseAccess db = new DatabaseAccess();
			ResultSet rs = null;
			boolean isConnected = false;
			try {
				isConnected = db.connect();
				logger.info("Load " + sheet.getSheetName() + " details: " + query);
				rs = db.executeQuery(query);
				logger.info(sheet.getSheetName() + " details loaded...");
				
				Hashtable<String, String> billebleProduct_valorizzate = new Hashtable<String, String>();
				while (rs.next()) {
					String colonna = rs.getString("req_name").trim();
					String riga = rs.getString("BILLABLE_PRODUCT").trim();
					billebleProduct_valorizzate.put(riga, riga);
					if (rowIndex.get(riga)!=null && colIndex.get(colonna)!= null)
						XlsUtil.addCell(sheet, rowIndex.get(riga), colIndex.get(colonna), rs.getInt("tot"), styles.formatoRigaTab_int);
					else
						logger.info("Verificare: " + riga + "= " + rowIndex.get(riga) + " - " + colonna + "= " + colIndex.get(colonna));
				}
				
				Enumeration<String> keys = rowIndex.keys();
				String billP = null;
				while (keys.hasMoreElements()) {
					billP = keys.nextElement();
					HSSFCell deltaCell = sheet.getRow(rowIndex.get(billP)).getCell(reqColl.size()+4);
					double delta = 0;
					if (deltaCell != null)
						delta = deltaCell.getNumericCellValue();
					if (!billebleProduct_valorizzate.containsKey(billP) && delta == 0)
						sheet.getRow(rowIndex.get(billP)).setZeroHeight(true);
				}
			} catch (Exception e) {
				logger.error("Eccezione: ", e);
				Report.sendReport("ErroreGenerico", "Sintesi Report", "InternalReport", "Eccezione", Util.exceptionToString(e));
			} finally {
				if (rs != null)
					rs.close();
				if (isConnected && db != null)
					db.disconnect();
			}
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "Sintesi Report", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}
	}
	
	private void summary_sheetTemplate (HSSFSheet sheet, Vector<TbBLUsageDetails> billableRow, Vector<TbBLUsageDetails> reqColl, String cliente, String user, String meseRif) {
		try {
			// Titolo
			XlsUtil.addMergedCell(sheet, 0, 0, 1, 10, cliente, styles.formatoIntestazioneP);
			XlsUtil.addMergedCell(sheet, 1, 1, 1, 10, "Analisi del BBG DL Per Security Verification Report", styles.formatoTitolo);
			logger.info("meseRif "+meseRif);
			XlsUtil.addMergedCell(sheet, 2, 2, 1, 10, "Account BBG DL nr: " + user + " - " + meseRif.substring(4) + "/" + meseRif.substring(0, 4), styles.formatoTitolo);
			
			int rigaInizioTabulato = 4;
			// Testata principale
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato, rigaInizioTabulato, 1, 4, "ITEM RICHIESTI", styles.formatoIntestazioneP);
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato, rigaInizioTabulato, 5, 10, "ATTRIBUZIONE COSTI", styles.formatoIntestazioneP);
			
			// Testata secondaria
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato+1, rigaInizioTabulato+1, 1, 2, "INITIAL", styles.formatoIntestazioneS);
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato+1, rigaInizioTabulato+1, 3, 4, "MULTI", styles.formatoIntestazioneS);
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato+1, rigaInizioTabulato+1, 5, 6, "INITIAL", styles.formatoIntestazioneS);
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato+1, rigaInizioTabulato+1, 7, 8, "MULTI", styles.formatoIntestazioneS);
			XlsUtil.addMergedCell(sheet, rigaInizioTabulato+1, rigaInizioTabulato+1, 9, 10, "TOTALE", styles.formatoIntestazioneS);
			
			// Testata
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 1, "Numero", styles.formatoIntestazione);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 2, "% su tot", styles.formatoIntestazione);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 3, "Numero", styles.formatoIntestazione);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 4, "% su tot", styles.formatoIntestazione);
			sheet.setColumnWidth(5, 15*256);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 5, "Costo ($/mese)", styles.formatoIntestazione);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 6, "% su tot", styles.formatoIntestazione);
			sheet.setColumnWidth(7, 15*256);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 7, "Costo ($/mese)", styles.formatoIntestazione);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 8, "% su tot", styles.formatoIntestazione);
			sheet.setColumnWidth(9, 15*256);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 9, "Costo ($/mese)", styles.formatoIntestazione);
			XlsUtil.addCell(sheet, rigaInizioTabulato+2, 10, "% su tot", styles.formatoIntestazione);
			
			// Intestazione di riga
			sheet.setColumnWidth(0, 20*256);
			int rowIndex_totale = rigaInizioTabulato + reqColl.size() + 4;
			for (int r=0; r<reqColl.size(); r++) {
				XlsUtil.addCell(sheet, rigaInizioTabulato+r+3, 0, reqColl.elementAt(r).getReq_name(), styles.formatoIntestazioneRow);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 1, "InitCharge!" + XlsUtil.findColumnName(r+1) + (billableRow.size()+2), styles.formatoRigaTab_int);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 2, "(B" + (rigaInizioTabulato+r+4) + "/B" + (rowIndex_totale+1) + ")*100", styles.formatoRigaTab_percent);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 3, "MultiRequest!" + XlsUtil.findColumnName(r+1) + (billableRow.size()+2), styles.formatoRigaTab_int);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 4, "(D" + (rigaInizioTabulato+r+4) + "/D" + (rowIndex_totale+1) + ")*100", styles.formatoRigaTab_percent);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 5, "InitCharge!" + XlsUtil.findColumnName(r+1 + reqColl.size() + 8) + (billableRow.size()+2), styles.formatoRigaTab_double);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 6, "(F" + (rigaInizioTabulato+r+4) + "/F" + (rowIndex_totale+1) + ")*100", styles.formatoRigaTab_percent);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 7, "MultiRequest!" + XlsUtil.findColumnName(r+1 + reqColl.size() + 8) + (billableRow.size()+2), styles.formatoRigaTab_double);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 8, "(H" + (rigaInizioTabulato+r+4) + "/H" + (rowIndex_totale+1) + ")*100", styles.formatoRigaTab_percent);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 9, "F" + (rigaInizioTabulato+r+4) + "+H" + (rigaInizioTabulato+r+4), styles.formatoRigaTab_double);
				XlsUtil.addCellFormula(sheet, rigaInizioTabulato+r+3, 10, "(J" + (rigaInizioTabulato+r+4) + "/J" + (rowIndex_totale+1) + ")*100", styles.formatoRigaTab_percent);
			}
			
			// Totale
			XlsUtil.addCell(sheet, rowIndex_totale, 0, "TOTALE", styles.formatoIntestazione);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 1, "SUM(B3:B" + (rowIndex_totale-1) + ")", styles.formatoResult_int);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 2, "SUM(C3:C" + (rowIndex_totale-1) + ")", styles.formatoResult_percent);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 3, "SUM(D3:D" + (rowIndex_totale-1) + ")", styles.formatoResult_int);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 4, "SUM(E3:E" + (rowIndex_totale-1) + ")", styles.formatoResult_percent);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 5, "SUM(F3:F" + (rowIndex_totale-1) + ")", styles.formatoResult_double);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 6, "SUM(G3:G" + (rowIndex_totale-1) + ")", styles.formatoResult_percent);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 7, "SUM(H3:H" + (rowIndex_totale-1) + ")", styles.formatoResult_double);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 8, "SUM(I3:I" + (rowIndex_totale-1) + ")", styles.formatoResult_percent);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 9, "SUM(J3:J" + (rowIndex_totale-1) + ")", styles.formatoResult_double);
			XlsUtil.addCellFormula(sheet, rowIndex_totale, 10, "SUM(K3:K" + (rowIndex_totale-1) + ")", styles.formatoResult_percent);
		
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "Sintesi Report", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}
	}

	private void centriDiCosto_template (HSSFSheet sheet, HSSFSheet summarySheet, String cliente, String user, String meseRif) {
		try {
			// Titolo
			XlsUtil.addMergedCell(sheet, 0, 0, 1, 10, cliente, styles.formatoIntestazioneP);
			XlsUtil.addMergedCell(sheet, 1, 1, 1, 10, "Analisi del BBG DL Per Security - Ripartizione costi per Centro di Costo", styles.formatoTitolo);
			XlsUtil.addMergedCell(sheet, 2, 2, 1, 10, "Account BBG DL nr: " + user + " - " + meseRif.substring(4) + "/" + meseRif.substring(0, 4), styles.formatoTitolo);
			
			// ***** Impostazione righe *****
			int inizioDati_summary = -1;
			int inizioDati = 6;
			String reqName = "";
			int r = 0;
			while (!"TOTALE".equals(reqName)) {
				if (summarySheet.getRow(r) != null && summarySheet.getRow(r).getCell(0) != null) {
					reqName = summarySheet.getRow(r).getCell(0).getStringCellValue();
					if (inizioDati_summary < 0)
						inizioDati_summary = r;
					reqName_row.put(reqName, inizioDati + r - inizioDati_summary);
					XlsUtil.addCell(sheet, inizioDati + r - inizioDati_summary, 0, reqName, "TOTALE".equals(reqName) ? styles.formatoIntestazione : styles.formatoIntestazioneRow);
				}
				r++;
			}
						
			// ***** Impostazione colonne *****
			
			// TABELLA 1
			sheet.setColumnWidth(0, 25*256);
			XlsUtil.addMergedCell(sheet, 4, 5, 0, 0, "FILE OUTPUT", styles.formatoIntestazioneS);
			intestazione_col.put("reqName", 0);
			sheet.setColumnWidth(1, 25*256);
			XlsUtil.addMergedCell(sheet, 4, 5, 1, 1, "PROCESSI RICHIEDENTI", styles.formatoIntestazioneS);
			intestazione_col.put("processi", 1);
			int maxCentriCosto = maxCentriCosto(user);
			XlsUtil.addMergedCell(sheet, 4, 4, 2, 1+ 2*maxCentriCosto, "CENTRI DI COSTO ASSOCIATI", styles.formatoIntestazioneS);
			int c = 2;
			for (; c<2+ 2*maxCentriCosto; c+=2) {
				intestazione_col.put("CentroCosto_" + c/2, c);
				XlsUtil.addCell(sheet, 5, c, c/2, styles.formatoIntestazione);
				intestazione_col.put("CentroCosto_" + c/2 + "%", c+1);
				XlsUtil.addCell(sheet, 5, c+1, "%", styles.formatoIntestazione);
			}
			
			// TABELLA 2
			c ++;
			intestazione_col.put("FATTURA", c);
			XlsUtil.addCell(sheet, 4, c, "FATTURA", styles.formatoIntestazioneS);
			XlsUtil.addCell(sheet, 5, c, "%", styles.formatoIntestazione);
			XlsUtil.addCellFormula(sheet, reqName_row.get("TOTALE"), c, "SUM(" + XlsUtil.findColumnName(c) + (inizioDati+1) + ":" + XlsUtil.findColumnName(c) + (reqName_row.get("TOTALE")-1) + ")", styles.formatoResult_double);
			
			reqName = null;
			r = inizioDati_summary;
			while(summarySheet.getRow(r) != null) {
				reqName = summarySheet.getRow(r).getCell(0).getStringCellValue();
				XlsUtil.addCellFormula(sheet, reqName_row.get(reqName), c, "'" + summarySheet.getSheetName() + "'!K" + (r+1), styles.formatoRigaTab_double);
				r++;
			}

			// TABELLA 3
			c += 2;
			TbBLCentriCosto centriCosto = new TbBLCentriCosto();
			Vector<TbBLCentriCosto> vCentriCosto = centriCosto.retrieveAll("WHERE userBL = '" + user + "' ORDER BY centroCosto");
			XlsUtil.addMergedCell(sheet, 4, 4, c, c+vCentriCosto.size()-1, "Ripatizione % del total billing legata all'usage effettivo", styles.formatoIntestazioneS);
			for (int i=0; i<vCentriCosto.size(); i++) {
				centriCosto = vCentriCosto.elementAt(i);
				intestazione_col.put(centriCosto.getCentroCosto(), c+i);
				XlsUtil.addCell(sheet, 5, c+i, centriCosto.getCentroCosto(), styles.formatoIntestazione);
				XlsUtil.addCellFormula(sheet, reqName_row.get("TOTALE"), c+i, "SUM(" + XlsUtil.findColumnName(c+i) + (inizioDati+1) + ":" + XlsUtil.findColumnName(c+i) + (reqName_row.get("TOTALE")-1) + ")", styles.formatoResult_double);
			}
			XlsUtil.addCellFormula(sheet, reqName_row.get("TOTALE")+1, c+vCentriCosto.size()-1, "SUM(" + XlsUtil.findColumnName(c) + (reqName_row.get("TOTALE")+1) + ":" + XlsUtil.findColumnName(c+vCentriCosto.size()-1) + (reqName_row.get("TOTALE")+1) + ")", styles.formatoResult_double);
			
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "BLSintesi_CentriDiCosto", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}
		
		sheet.setZoom(85, 100);
	}
	
	private void centriDiCosto_values (HSSFSheet sheet, String cliente, String user, String meseRif) {
		try {
			DatabaseAccess db = new DatabaseAccess();
			ResultSet rs = null;
			boolean isConnected = false;
			try {
				isConnected = db.connect();
				String query = "SELECT req.req_name, pr.nomeProcesso, cc.centroCosto, pcc.percentuale " +
								"FROM BLRequest req INNER JOIN " +
										"BLProcessiRichiedenti pr ON req.processoRichiedente = pr.id AND req.userBL = '" + user + "' INNER JOIN " +
										"BLProcesso_CentroCosto pcc ON pcc.processoRichiedente = pr.id AND pr.userBL = '" + user + "' INNER JOIN " +
										"BLCentriCosto cc ON cc.id = pcc.centroCosto AND cc.userBL = '" + user + "' " +
								"WHERE (req.processoRichiedente IS NOT NULL) " +
								"ORDER BY req.req_name, pcc.percentuale DESC, cc.centroCosto";
				logger.info("Load ripartizione per centro di costo: " + query);
				rs = db.executeQuery(query);
				logger.info("Ripartizione loaded...");
				
				String reqName = null, processo = null, centroCosto = null, reqName_precedente = null;
				Double percentuale = null;
				int conta_centroCosto = 1;
				while (rs.next()) {
					reqName_precedente = reqName;
					reqName = rs.getString("req_name");
					if (reqName_row.get(reqName) == null)
						continue;
					processo = rs.getString("nomeProcesso");
					centroCosto = rs.getString("centroCosto");
					percentuale = rs.getDouble("percentuale");
					if ((reqName==null && reqName_precedente==null) || !reqName.equals(reqName_precedente))
						conta_centroCosto = 1;
					else
						conta_centroCosto ++;
					
					XlsUtil.addCell(sheet, reqName_row.get(reqName), intestazione_col.get("processi"), processo, styles.arial10BoldDarkRed);
					XlsUtil.addCell(sheet, reqName_row.get(reqName), intestazione_col.get("CentroCosto_" + conta_centroCosto), centroCosto, styles.arial10BoldDarkRed);
					XlsUtil.addCell(sheet, reqName_row.get(reqName), intestazione_col.get("CentroCosto_" + conta_centroCosto + "%"), percentuale, styles.formatoRigaTab_double);
					XlsUtil.addCellFormula(sheet, reqName_row.get(reqName), intestazione_col.get(centroCosto), (XlsUtil.findColumnName(intestazione_col.get("FATTURA")) + (reqName_row.get(reqName)+1) + "*" + percentuale/100), styles.formatoRigaTab_double);
				}
			} catch (Exception e) {
				logger.error("Eccezione: ", e);
				Report.sendReport("ErroreGenerico", "BLSintesi_CentriDiCosto", "InternalReport", "Eccezione", Util.exceptionToString(e));
			} finally {
				if (rs != null)
					rs.close();
				if (isConnected && db != null)
					db.disconnect();
			}
			
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "BLSintesi_CentriDiCosto", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}
	}

	private int maxCentriCosto(String user) {
		int maxCentriCosto = 0; 
		try {
			DatabaseAccess db = new DatabaseAccess();
			ResultSet rs = null;
			boolean isConnected = false;
			try {
				isConnected = db.connect();
				String query = "SELECT MAX(nCentriCosto) AS maxCentriCosto " +
								"FROM (SELECT p.nomeProcesso, COUNT(*) AS nCentriCosto " +
										"FROM BLProcesso_CentroCosto pcc INNER JOIN " +
											"BLProcessiRichiedenti p ON pcc.processoRichiedente = p.id " + 
										"WHERE (p.userBL = '" + user + "') " +
										"GROUP BY p.nomeProcesso) tb1";
				logger.info("Load maxCentriCosto: " + query);
				rs = db.executeQuery(query);
				logger.info("maxCentriCosto loaded...");
				
				while (rs.next())
					maxCentriCosto = rs.getInt("maxCentriCosto");
			} catch (Exception e) {
				logger.error("Eccezione: ", e);
				Report.sendReport("ErroreGenerico", "BLSintesi_CentriDiCosto", "InternalReport", "Eccezione", Util.exceptionToString(e));
			} finally {
				if (rs != null)
					rs.close();
				if (isConnected && db != null)
					db.disconnect();
			}
			
		} catch (Exception e) {
			logger.error("Eccezione: ", e);
			Report.sendReport("ErroreGenerico", "BLSintesi_CentriDiCosto", "InternalReport", "Eccezione", Util.exceptionToString(e));
		}
		return maxCentriCosto;
	}

//		private void sendEmail(String nomeFile[], Calendar dataRiferimento) {
//		String mese = "";
//
//		switch (dataRiferimento.get(Calendar.MONTH)) {
//			case 0: mese = " GENNAIO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 1: mese = " FEBBRAIO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 2: mese = " MARZO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 3: mese = " APRILE " + dataRiferimento.get(Calendar.YEAR); break;
//			case 4: mese = " MAGGIO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 5: mese = " GIUGNO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 6: mese = " LUGLIO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 7: mese = " AGOSTO " + dataRiferimento.get(Calendar.YEAR); break;
//			case 8: mese = " SETTEMBRE " + dataRiferimento.get(Calendar.YEAR); break;
//			case 9: mese = " OTTOBRE " + dataRiferimento.get(Calendar.YEAR); break;
//			case 10: mese = " NOVEMBRE " + dataRiferimento.get(Calendar.YEAR); break;
//			case 11: mese = " DICEMBRE " + dataRiferimento.get(Calendar.YEAR); break;
//			default: mese = "ERRORE!"; break;
//		}
//
//		// Invio per email il report
//		Report.sendReport("ErroreGenerico", getTaskName(), getConfig().getChild("mailinglistname"), getConfig().getChild("subject") + mese, getConfig().getChild("text") + mese, nomeFile);
//	}
}