/*
 * 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.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.dgfip.utils.Amount;
import fr.gouv.finances.dgfip.utils.StringUtilities;
import fr.gouv.finances.dgfip.xemelios.common.Constants;
import fr.gouv.finances.dgfip.xemelios.common.config.ElementModel;
import fr.gouv.finances.dgfip.xemelios.export.ExportElementModel;
import org.apache.log4j.Logger;

/**
 * 2010-02-18 : reutilisation des WritableCellFormat car on est limité dans leur utilisation
 * @author N.Le Corre
 */
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;

    WritableCellFormat[] dateFormats = null;
    WritableCellFormat[] intFormats = null;
    WritableCellFormat[] floatFormats = null;
    WritableCellFormat[] lblFormats = null;

    public JxlExporter(Exporter caller) {
        this.caller = caller;
        dateFormats = new WritableCellFormat[3];
        DateFormat myDateFormat = new DateFormat(System.getProperty(Constants.SYS_PROP_DATE_FORMAT));
        dateFormats[0] = new WritableCellFormat(fontEntete1, myDateFormat);
        dateFormats[1] = new WritableCellFormat(fontEntete2, myDateFormat);
        dateFormats[2] = new WritableCellFormat(fontAutre, myDateFormat);

        intFormats = new WritableCellFormat[3];
        intFormats[0] = new WritableCellFormat(fontEntete1, NumberFormats.INTEGER);
        intFormats[1] = new WritableCellFormat(fontEntete2, NumberFormats.INTEGER);
        intFormats[2] = new WritableCellFormat(fontAutre, NumberFormats.INTEGER);

        floatFormats = new WritableCellFormat[3];
        floatFormats[0] = new WritableCellFormat(fontEntete1, NumberFormats.FLOAT);
        floatFormats[1] = new WritableCellFormat(fontEntete2, NumberFormats.FLOAT);
        floatFormats[2] = new WritableCellFormat(fontAutre, NumberFormats.FLOAT);

        lblFormats = new WritableCellFormat[3];
        lblFormats[0] = new WritableCellFormat(fontEntete1);
        lblFormats[1] = new WritableCellFormat(fontEntete2);
        lblFormats[2] = new WritableCellFormat(fontAutre);

    }

    // 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);
        String sheetName = StringUtilities.toAscii(eem.getLibelle(),"_");
        if(sheetName.length()>=31) {
            sheetName = sheetName.substring(sheetName.length()-30);
            if(sheetName.endsWith(".")) sheetName = sheetName.substring(0, sheetName.length()-1);
        }
//        v.add(workbook.createSheet(eem.getLibelle(), workbook.getNumberOfSheets()));
        v.add(workbook.createSheet(sheetName, 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;
        }

        WritableSheet sheet = getLastSheet(sheetId);

        if (o instanceof java.lang.Integer) {
            WritableCellFormat integerFormat = intFormats[newline == 0 ? 0 : newline == 1 ? 1 : 2];
            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 = floatFormats[newline == 0 ? 0 : newline == 1 ? 1 : 2];
            jxl.write.Number number = new jxl.write.Number(col, newline, (Float) o, floatFormat);
            sheet.addCell(number);
        } else if (o instanceof java.util.Date) {
            WritableCellFormat dateFormat = dateFormats[newline == 0 ? 0 : newline == 1 ? 1 : 2];
            jxl.write.DateTime date = new jxl.write.DateTime(col, newline, (Date) o, dateFormat);
            sheet.addCell(date);
        } else if (o instanceof Amount) {
            WritableCellFormat floatFormat = floatFormats[newline == 0 ? 0 : newline == 1 ? 1 : 2];
            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");
//            }
            WritableCellFormat format = lblFormats[newline == 0 ? 0 : newline == 1 ? 1 : 2];
            jxl.write.Label label = new jxl.write.Label(col, newline, (String) o, format);
            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
    }
}
