/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2007 N.Le Corre - nicolas.lecorre@axyus.com
 *   2008 L. Meckert - laurent.meckert@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS is free software; you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * XEMELIOS is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package fr.gouv.finances.cp.xemelios.export;

import java.io.File;
import java.util.Date;
import java.util.Hashtable;
import java.util.Vector;


import jxl.Cell;
import jxl.CellType;
import jxl.LabelCell;
import jxl.Range;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.CellFormat;
import jxl.write.Blank;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import fr.gouv.finances.cp.utils.Amount;
import fr.gouv.finances.cp.xemelios.common.Constants;
import fr.gouv.finances.cp.xemelios.common.config.ElementModel;


public class JxlExporter extends ExportUndertaker {
    private static final int MAX_ROWS_PER_SHEET = 65000 ; // test 30;
    // formattage des colonnes d'entête
    WritableFont fontEntete1 = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false);
    WritableFont fontEntete2 = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false);
    WritableFont fontAutre   = new WritableFont(WritableFont.TIMES, 10);
	
    // contiendra la liste des sheets XLS, dans l'ordre devant être créé (par ex : SHEET_Bordereau, SHEET_Titre, SHEET_Ligne)
    private Hashtable<String, Vector<WritableSheet>> sheets = new Hashtable<String, Vector<WritableSheet>>();
	
    private Exporter caller;

    private WritableWorkbook workbook = null;

 public   JxlExporter(Exporter caller) {
        this.caller = caller;

    }


	
    // Fonctions appellees par Exporter.java

    /**
     * Initialisation
     */
    void initialize()throws Exception {

        /**
         * Création du workbook
         * sur le fichier fDest
         */
        WorkbookSettings ws = new WorkbookSettings();
        ws.setUseTemporaryFileDuringWrite(true);
        ws.setTemporaryFileDuringWriteDirectory(new File("."));
        ws.setCellValidationDisabled(true);
        ws.setGCDisabled(false);
        workbook = Workbook.createWorkbook(caller.fDest, ws);
    }
   
    /**
     * Crée un <tt>WritableSheet</tt> et ajoute la liste des entetes de colonne
     * @param eem
     * @throws Exception
     */
    void buildSheet (ExportElementModel eem) throws Exception {
        
        Vector<WritableSheet> v = sheets.get(eem.getId());
        if (v == null) v = new Vector<WritableSheet>();
        sheets.put(eem.getId(), v);
        v.add (workbook.createSheet(eem.getLibelle(), workbook.getNumberOfSheets()));
			
        for (ExportElementModel e:eem.getEnfants()) {
            buildSheet (e);
        }
        caller.firstSheetId = eem.getId(); 
    }
    /**
     * Apporte des finitions 
     */
    void finish() throws Exception{
        // recopie des entêtes dans les nouvelles feuilles créées
        enteteNewSheets();
    }

    /**
     * Fermeture et nettoyage
     */
    void close(){
        if (workbook!=null) {
	    try {
		workbook.write();
		workbook.close();
                workbook = null;
	    } catch (Throwable t) {}
        }
    }
    
    /**
     * Verifie l'existence d'unr feuille dans le classeur
     * @param sheetId le nom de la feuille
     */
    boolean sheetExists(String sheetId) {
	Vector<WritableSheet> v = sheets.get (sheetId);
        if(v != null) 
            return true;
        else
            return false;
    }

	




    /**
     * renvoie le dernier numero de ligne utilisee dans la derniere feuille de nom sheetId
     */
    int getLastLineLastSheet (String sheetId) {
        // suppose qu'on a appele sheetExists(sheetId) 
        WritableSheet ws = getLastSheet(sheetId);
        return   (ws.getRows()<2 ? 2:ws.getRows());

    }



    /**
     * Remplit une cellule avec la valeur correctement formatée.
     * @param sheet
     * @param o
     * @param ligne
     * @param col
     * @throws WriteException
     */
    void writeCell (String sheetId, Object o, int line, int col, boolean bypassLineRenum) throws Exception {
        int newline = 0;
        if (bypassLineRenum) {
            newline = line;
        } else {
            int _ligne = caller.getLastLine(sheetId);
            if (_ligne>=(MAX_ROWS_PER_SHEET*sheets.get(sheetId).size())) {
                splitSheet (sheetId);
            }
            
            // renumérotation de la ligne
            newline = _ligne;
            newline=_ligne%(MAX_ROWS_PER_SHEET)+2;
            //  System.out.println("writeCell ligne = " + line + ", _ligne=" + _ligne + ",newline= " + newline + ",Col=" + col);
        }
     
        WritableSheet sheet = getLastSheet(sheetId);
        
        if (o instanceof java.lang.Integer) {
            WritableCellFormat integerFormat = new WritableCellFormat ((newline==0?fontEntete1:newline==1?fontEntete2:fontAutre), NumberFormats.INTEGER);
            jxl.write.Number number = new jxl.write.Number(col, newline, (Integer)o, integerFormat);
            sheet.addCell(number); 
        } else if (o instanceof java.lang.Float) {
            WritableCellFormat floatFormat = new WritableCellFormat ((newline==0?fontEntete1:newline==1?fontEntete2:fontAutre), NumberFormats.FLOAT);
            jxl.write.Number number = new jxl.write.Number(col, newline, (Float)o, floatFormat);
            sheet.addCell(number);						 
        } else if (o instanceof java.util.Date) {
            DateFormat myDateFormat = new DateFormat (System.getProperty(Constants.SYS_PROP_DATE_FORMAT));
            WritableCellFormat dateFormat = new WritableCellFormat ((newline==0?fontEntete1:newline==1?fontEntete2:fontAutre), myDateFormat);
            jxl.write.DateTime date = new jxl.write.DateTime(col, newline, (Date)o, dateFormat);
            sheet.addCell(date);
        } else if (o instanceof Amount) {
            WritableCellFormat floatFormat = new WritableCellFormat ((newline==0?fontEntete1:newline==1?fontEntete2:fontAutre), NumberFormats.FLOAT);
            jxl.write.Number number = new jxl.write.Number(col, newline, ((Amount)o).doubleValue(), floatFormat);
            sheet.addCell(number);
        } else {
            jxl.write.Label label = new jxl.write.Label(col, newline, (String)o, new WritableCellFormat (newline==0?fontEntete1:newline==1?fontEntete2:fontAutre));
            sheet.addCell(label);
        }		
    }

    /**
     * reunit des cellules dans la derniere des feuilles de nom sheetId
     * @param sheetId = nom de la feuille 
     * @param  col1 = colonne de depart
     * @param row1 = ligne de depart
     * @param col2 = colonne d'arrivee
     * @param row2= ligne d'arrivee
     */
    
    void mergeCells( String sheetId, int col1, int row1, int col2, int row2) 
    {
        WritableSheet _ws = getLastSheet(sheetId);

        try {
            _ws.mergeCells(col1, row1,  col2, row2);
        } catch(Exception e) {}
        
    }



    // Fonctions a usage interne mises en protected on sait jamais

    /**
     * Numero d'ordre suivant celui de la feuille de nom id
     */
    protected int afterSheet (String id) {
	String[]names = workbook.getSheetNames();
	for (int i=1; i<names.length; i++) {
	    if (names[i].equals(id)) return i+1;
	}
	return 1;
    }

    /**
     * Récupére la derniere feuille correspondant a
     * l'objet de nom id (c'est celle en cours d'edition)
     */
    protected WritableSheet getLastSheet (String sheetId) {
	Vector<WritableSheet> v = sheets.get (sheetId);
        if(v != null) 
            return v.get (v.size()-1);
        else
            return null;
    }
	
    /**
     * Récupére la premiere feuille correspondant au premier parent de
     * l'objet de nom id
     */
    protected WritableSheet getRootSheetBefore (String id) {
	WritableSheet ret = null;
	ElementModel e = caller.getEMFromId(id);
	if (e.getSimpleParent()!=null) {
	    String pName = e.getSimpleParent().getElement();
	    ret = sheets.get(pName).get(0);
	}
	return ret;
    }
	

    /**
     * creee une nouvelle feuille de nom id suivi d'un _  er d'un numero d'ordre
     * appelle lorsque la feuille 
     * precedente de meme nom "deborde"
     */
    protected WritableSheet splitSheet (String id) {
	Vector<WritableSheet> v = sheets.get (id);
	WritableSheet wsRef = v.get(0);
		
	WritableSheet ws = workbook.createSheet(wsRef.getName()+"_"+(v.size()), afterSheet (v.get(v.size()-1).getName()));
	v.add (ws);
	return workbook.getSheet(workbook.getNumberOfSheets()-1);
    }


    /**
     * Appelee en fin d'execution, retourne sur les feuilles suppelemetaires
     * creees pas splitSheet 
     * pour leur copier la ligne de titre de la premiere de leur serie
     */

    private void enteteNewSheets () throws RowsExceededException, WriteException {
	for (Vector<WritableSheet> v:sheets.values()) {
	    if (v.size()>1) {
		WritableSheet wsRef = v.get(0);
		for (int iv=1; iv<v.size(); iv++) {
		    WritableSheet ws = v.get(iv);
		    for (int i = 0 ; i < 2 ; i++)
			{
			    for (int j = 0 ; j < wsRef.getColumns() ; j++)
				{
				    WritableCell readCell = (WritableCell)wsRef.getCell(j, i);
				    WritableCell newCell = readCell.copyTo(j, i);
				    CellFormat readFormat = readCell.getCellFormat();
				    if (readFormat!=null) {
					WritableCellFormat newFormat = new WritableCellFormat(readFormat);
					newCell.setCellFormat(newFormat);
				    }
				    ws.addCell(newCell);
				}
			}
					
		    for (int mc=0; mc<wsRef.getMergedCells().length; mc++) {
			Range r = wsRef.getMergedCells()[mc];
			if (r.getTopLeft().getColumn()<r.getBottomRight().getColumn()) {
			    if (r.getTopLeft().getRow()<r.getBottomRight().getRow()) {
				ws.mergeCells(r.getTopLeft().getColumn(), r.getTopLeft().getRow(), r.getBottomRight().getColumn(), r.getBottomRight().getRow());
			    } else {
				ws.mergeCells(r.getTopLeft().getColumn(), r.getBottomRight().getRow(), r.getBottomRight().getColumn(), r.getTopLeft().getRow());
			    }
			} else {
			    if (r.getTopLeft().getRow()<r.getBottomRight().getRow()) {
				ws.mergeCells(r.getBottomRight().getColumn(), r.getTopLeft().getRow(), r.getTopLeft().getColumn(), r.getBottomRight().getRow());
			    } else {
				ws.mergeCells(r.getBottomRight().getColumn(), r.getBottomRight().getRow(), r.getTopLeft().getColumn(), r.getTopLeft().getRow());
			    }
			}
		    }
		}
	    }
	}
    }


    void flushLine(String el){
        // NOP
    }
}
