/*
 * Copyright 
 *   2008 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 General Public License as published by
 * the Free Software Foundation; either version 2 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 General Public License for more details.
 * 
 * You should have received a copy of the GNU 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.Range;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.CellFormat;
import jxl.write.DateFormat;
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;
import org.apache.log4j.Logger;

public class JxlExporter extends ExportUndertaker {

    private static final Logger logger = Logger.getLogger(JxlExporter.class);
    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 {
            if (o != null) {
                logger.debug(sheetId + " -> " + o.getClass().getName());
            } else {
                logger.debug("writting null data");
            }
            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
    }
}
