/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.gouv.finances.cp.xemelios.export;

import fr.gouv.finances.cp.utils.StringUtilities;
import fr.gouv.finances.cp.xemelios.common.Constants;
import fr.gouv.finances.cp.xemelios.ui.MainWindow;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.nio.channels.FileChannel;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Vector;
import org.apache.log4j.Logger;

/**
 *
 * @author chm
 */
public class SylkExporter extends ExportUndertaker {
    private static DecimalFormat df = new DecimalFormat("#0.00");
    static {
        DecimalFormatSymbols dfs = new DecimalFormatSymbols();
        dfs.setDecimalSeparator(',');
        df.setDecimalFormatSymbols(dfs);
        df.setGroupingUsed(false);
    }
    private static SimpleDateFormat sdf = new SimpleDateFormat(System.getProperty(Constants.SYS_PROP_DATE_FORMAT,"yyyy-MM-dd"));
    
    public static final int MAX_ROWS_PER_SHEET = 65000;
    private static final Logger logger = Logger.getLogger(SylkExporter.class);
    private Hashtable<String, Vector<SylkTable>> sheets;
    private Exporter exporter;
    private String filenamePrefix;

    public SylkExporter(Exporter exporter) {
        super(exporter);
        this.exporter = exporter;
    }

    @Override
    void buildSheet( ExportElementModel eem) throws Exception {
        Vector<SylkTable> tables = sheets.get(eem.getId());
        if (tables == null) {
            tables = new Vector<SylkTable>();
            sheets.put(eem.getId(), tables);
        }
        String filename = filenamePrefix + "_" + eem.getLibelle();

        SylkTable sheet = new SylkTable(filename);
        tables.add(sheet);

        addRefInMainSheet(filename, false);

        for ( ExportElementModel fils : eem.getEnfants()) {
            buildSheet(fils);
        }

        exporter.firstSheetId = eem.getId();
    }
    
    protected void addRefInMainSheet(String filename, boolean addPrefix) {
        String ref = filename + ".slk";
        // TODO
    }

    @Override
    void flushLine( String el) {
    }

    protected void splitSheet(String sheetId) throws IOException {
        Vector<SylkTable> v = sheets.get(sheetId);
        SylkTable wsRef = v.get(0);
        SylkTable otherRef = getLastSheet(sheetId);

//        wsRef.close();
        Cell[] titleLine = otherRef.getTitleLine();
        Cell[] headerLine = otherRef.getHeaderLine();
        
        otherRef.flush();



        NumberFormat nf = NumberFormat.getInstance(); // Get Instance of NumberFormat
        nf.setMinimumIntegerDigits(2);  // The minimum Digits required is 5
        nf.setMaximumIntegerDigits(3); // The maximum Digits required is 5

        String sb = (nf.format((long) v.size()));

        String fname = wsRef.getFileName() + "_" + sb;

        SylkTable newSheet = new SylkTable(fname,titleLine, headerLine);

        addRefInMainSheet(fname, false);

        v.add(newSheet);
        //return newSheet;
    }

    @Override
    void writeCell( String sheetId,  Object o,  int line,  int col,  boolean bypassLineRenum) throws Exception {
        int newline = 0;
        if (bypassLineRenum) {
            newline = line;
        } else {
            int _ligne = exporter.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;
        }

        SylkTable sheet = getLastSheet(sheetId);
        if (line == 0) {
            sheet.addTitle(col, newline, o);
        } else if (line == 1) {
            sheet.addHeader(col, newline, o);
        } else {
            sheet.addValue(col, newline, o);
        }
    }

    private SylkTable getLastSheet(String sheetId) {
        Vector<SylkTable> v = sheets.get(sheetId);
        if (v != null) {
            return v.lastElement();
        } else {
            return null;
        }
    }

    @Override
    void mergeCells( String sheetId,  int col1,  int row1,  int col2,  int row2) {
        logger.debug("mergeCells(" + sheetId + "," + col1 + "," + row1 + "," + col2 + "," + row2 + ")");
    }

    @Override
    int getLastLineLastSheet( String sheetId) {
        SylkTable ws = getLastSheet(sheetId);
        int ret = ws.getRows() < 2 ? 2 : ws.getRows();
        logger.debug("getLastLineLastSheet(" + sheetId + ") -> " + ret);
        return ret;
    }

    @Override
    boolean sheetExists( String sheetId) {
        logger.debug("sheetExists(" + sheetId + ")");
        boolean bRet = sheets.containsKey(sheetId);
        return bRet;
    }

    @Override
    void close() {
        logger.debug("close()");
    }

    @Override
    void finish() throws Exception {
        logger.debug("finish()");
        for(String key:sheets.keySet()) {
            Vector<SylkTable> v=sheets.get(key);
            for(int i=0;i<v.size();i++) {
                v.get(i).flush();
            }
            v.clear();
        }
        sheets.clear();
    }

    @Override
    void initialize() throws Exception {
        logger.debug("initialize()");
        String dirRessources = MainWindow.instance.getEnvProperties().getProperty(Constants.SYS_PROP_RESOURCES_LOCATION);
        //String dirRessources = System.getProperty(Constants.SYS_PROP_RESOURCES_LOCATION);
        recopieFichier(dirRessources + "/modeleExportXemelios-97.xls", exporter.fDest.getPath());

        sheets = new Hashtable<String, Vector<SylkTable>>();
        filenamePrefix = StringUtilities.removeFileNameSuffix(exporter.fDest.getPath());
    }
    /**
     * Copie le fichier src vers dest
     * @param src
     * @param dst
     */
    private void recopieFichier(String src, String dst) {
        FileChannel in = null; // canal d'entrée
        FileChannel out = null; // canal de sortie

        try {
            in = new FileInputStream(src).getChannel();
            out = new FileOutputStream(dst).getChannel();
            in.transferTo(0, in.size(), out);
        } catch (Exception e) {
            e.printStackTrace(); // n'importe quelle exception
        } finally { // finalement on ferme
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) { }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) { }
            }
        }
    }

    private class SylkTable {
        
        public static final transient String SYLK_HEADER = "ID;PWXL;N;E\n"+
            "P;PGeneral\n"+
            "P;P0\n"+
            "P;P0.00\n"+
            "P;P#,##0\n"+
            "P;P#,##0.00\n"+
            "P;P#,##0\\ _(0;;\\-#,##0\\ _(0\n"+
            "P;P#,##0\\ _(0;;[Red]\\-#,##0\\ _(0\n"+
            "P;P#,##0.00\\ _(0;;\\-#,##0.00\\ _(0\n"+
            "P;P#,##0.00\\ _(0;;[Red]\\-#,##0.00\\ _(0\n"+
            "P;P#,##0\\ \"$\";;\\-#,##0\\ \"$\"\n"+
            "P;P#,##0\\ \"$\";;[Red]\\-#,##0\\ \"$\"\n"+
            "P;P#,##0.00\\ \"$\";;\\-#,##0.00\\ \"$\"\n"+
            "P;P#,##0.00\\ \"$\";;[Red]\\-#,##0.00\\ \"$\"\n"+
            "P;P0%\n"+
            "P;P0.00%\n"+
            "P;P0.00E+00\n"+
            "P;P##0.0E+0\n"+
            "P;P#\" \"?/?\n"+
            "P;P#\" \"??/??\n"+
            "P;Pdd/mm/yyyy\n"+
            "P;Pdd\\-mmm\\-yy\n"+
            "P;Pdd\\-mmm\n"+
            "P;Pmmm\\-yy\n"+
            "P;Ph:mm\\ AM/PM\n"+
            "P;Ph:mm:ss\\ AM/PM\n"+
            "P;Phh:mm\n"+
            "P;Phh:mm:ss\n"+
            "P;Pdd/mm/yyyy\\ hh:mm\n"+
            "P;Pmm:ss\n"+
            "P;Pmm:ss.0\n"+
            "P;P@\n"+
            "P;P[h]:mm:ss\n"+
            "P;P_-* #,##0\\ \"$\"_-;;\\-* #,##0\\ \"$\"_-;;_-* \"-\"\\ \"$\"_-;;_-@_-\n"+
            "P;P_-* #,##0\\ _(0_-;;\\-* #,##0\\ _(0_-;;_-* \"-\"\\ _(0_-;;_-@_-\n"+
            "P;P_-* #,##0.00\\ \"$\"_-;;\\-* #,##0.00\\ \"$\"_-;;_-* \"-\"??\\ \"$\"_-;;_-@_-\n"+
            "P;P_-* #,##0.00\\ _(0_-;;\\-* #,##0.00\\ _(0_-;;_-* \"-\"??\\ _(0_-;;_-@_-\n"+
            "P;P\"Vrai\";;\"Vrai\";;\"Faux\"\n"+
            "P;P\"Actif\";;\"Actif\";;\"Inactif\"\n"+
            "P;FArial;M200\n"+
            "P;FArial;M200\n"+
            "P;FArial;M200\n"+
            "P;FArial;M200\n"+
            "P;EArial;M200\n"+
            "P;EArial;M160\n"+
            "P;EArial;M200;SU;L13\n"+
            "P;EArial;M200;SU;L21\n"+
            "P;EArial;M280;SB\n"+
            "P;EArial;M240;SB\n"+
            "F;P0;DG0G10;G;M255\n";
        private String filename;
        private ArrayList<Cell[]> rowArray;
        private boolean written = false;

        public SylkTable(String filename) {
            super();
            this.filename = filename;
            rowArray = new ArrayList<Cell[]>();
        }
        public SylkTable(String filename, Cell[] titleLine, Cell[] headerLine) {
            this(filename);
            rowArray.add(titleLine);
            rowArray.add(headerLine);
        }

        public void addTitle(int col, int row, Object data) {
            ensureCapacity(row);
            Cell[] line = rowArray.get(row);
            line[col] = new Cell(data);
            line[col].setStyle(Cell.CELL_FMT_TITLE);
        }

        public void addHeader(int col, int row, Object data) {
            ensureCapacity(row);
            Cell[] line = rowArray.get(row);
            line[col] = new Cell(data);
            line[col].setStyle(Cell.CELL_FMT_HEADER);
        }

        public void addValue(int col, int row, Object data) {
            ensureCapacity(row);
            Cell[] line = rowArray.get(row);
            line[col] = new Cell(data);
        }

        public String getFileName() {
            return filename;
        }

        public int getRows() {
            return rowArray.size();
        }
        
        private void ensureCapacity(int row) {
            while(rowArray.size()<(row+1)) 
                rowArray.add(new Cell[256]);
        }
        
        private void writeTable(Writer w) throws IOException {
            if(written) return;
            w.write(SYLK_HEADER);
            for(int y=0;y<rowArray.size();y++) {
                Cell[] line = rowArray.get(y);
                for(int x=0;x<line.length;x++) {
                    if(line[x]!=null) w.write(line[x].getSylkRepresentation(y,x));
                }
            }
            w.write("E\n");
            written = true;
        }
        
        public void flush() throws IOException {
            if(rowArray.isEmpty()) return;
            logger.debug("writting "+getFileName()+".slk");
            File f = new File(getFileName()+".slk");
            FileWriter fw = new FileWriter(f);
            writeTable(fw);
            fw.flush(); fw.close();
            // empty the data
            clear();
        }
        
        private void clear() {
            for(Cell[] line:rowArray) {
                for(int i=0;i<line.length;i++) line[i]=null;
            }
            rowArray.clear();
        }
        
        public Cell[] getTitleLine() { return rowArray.get(0); }
        public Cell[] getHeaderLine() { return rowArray.get(1); }
    }

    private class Cell {
        public static final int CELL_VALUE = 0;
        public static final int CELL_FORMULA = 1;
        public static final int CELL_FMT_STD = 0;
        public static final int CELL_FMT_TITLE = 1;
        public static final int CELL_FMT_HEADER = 2;
        public static final int ALIGN_DEFAULT = 0;
        public static final int ALIGN_RIGHT = 1;
        public static final int ALIGN_CENTER = 2;
        public static final int ALIGN_LEFT = 3;
        private int datatype;
        private int format;
        private int style;
        private Object value;

        public Cell(Object o) {
            super();
            value = o;
            datatype = CELL_VALUE;
            if (o instanceof Number) {
                format = ALIGN_RIGHT;
            } else if(value instanceof java.util.Date) {
                format = ALIGN_CENTER;
            } else {
                format = ALIGN_LEFT;
            }
        }
        public void setStyle(int style) {
            this.style=style;
        }

        public String getSylkRepresentation(int i, int j) {
            StringBuilder sb = new StringBuilder();
            if (value != null) {
                if (datatype == CELL_VALUE) {
                    sb.append("C;Y").append(i + 1).append(";X").append(j + 1).append(";K").append(getFormattedValue()).append("\n");
                } else {
                    sb.append("C;Y").append(i + 1).append(";X").append(j + 1).append(";E").append(getFormattedValue()).append("\n");
                }

                sb.append("F;P0;Y").append(i + 1).append(";X").append(j + 1);
                if (format == ALIGN_RIGHT) {
                    sb.append(";FG0R");
                } else if (format == ALIGN_CENTER) {
                    sb.append(";FG0C");
                } else if (format == ALIGN_LEFT) {
                    sb.append(";FG0L");
                }
                
                if(style==CELL_FMT_TITLE) sb.append(";SLM0"); //sb.append(";SDLTM15");
                else if(style==CELL_FMT_HEADER) sb.append(";SDLM10");
                else sb.append(";SLM0");
                
                sb.append("\n");
            }
            return sb.toString();
        }
        
        public String getFormattedValue() {
            String ret = "";
            if(value instanceof Integer || value instanceof Long || value instanceof Short) {
                ret = value.toString();
            } else if(value instanceof Number) {
//                ret = "\""+df.format(value)+"\"";
                ret = df.format(value);
            } else if(value instanceof java.util.Date) {
                ret = "\""+sdf.format((java.util.Date)value)+"\"";
            } else {
                ret = "\""+((String)value).replaceAll(";", ";;")+"\"";
            }
            return ret;
        }
    }
}
