/*
 * Copyright 
 *   2008 axYus - www.axyus.com
 *   2008 jp.tessier - jean-philippe.tessier@axyus.com
 *   2009 c.Marchand - christophe.marchand@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.plugin.generatorEdmn;

import fr.gouv.finances.cp.utils.xml.marshal.CharacterEscaper;
import fr.gouv.finances.cp.utils.xml.marshal.escapers.DefaultCharacterEscaper;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.concurrent.CancellationException;
import java.util.concurrent.ExecutionException;
import java.util.zip.Adler32;
import java.util.zip.CheckedOutputStream;
import java.util.zip.Deflater;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.swing.JOptionPane;
import javax.swing.SwingUtilities;

import org.apache.log4j.Logger;
import org.jdesktop.swingworker.SwingWorker;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.AttributesImpl;
import org.xml.sax.helpers.DefaultHandler;

import fr.gouv.finances.dgfip.xemelios.common.FileInfo;
import fr.gouv.finances.dgfip.xemelios.common.config.Loader;
import fr.gouv.finances.dgfip.xemelios.data.impl.pool.PoolManager;
import fr.gouv.finances.dgfip.xemelios.importers.XemeliosArchiveImporter;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Hashtable;

/**
 * Classe a surcharger pour creer des importeurs 
 * @author chm
 */
public class EdmnImporteurWorker extends SwingWorker<String, String> {

    public static final transient String SEP = "|";

    public static final String INPUT_ENCODING = "latin1";
    public static final String OUTPUT_ENCODING = "ISO-8859-1";

    private static Logger logger = Logger.getLogger(EdmnImporteurWorker.class);
    private static final boolean DEBUG = false;
    public static final transient String NOT_AVAILABLE = "Libellé absent";
    public static final int MAX_SIZE_FILE = 5000;
    protected File[] filesToTreat;
    protected Exception inProcessException = null;
    protected long start;
    public final int MSG_TYPE_INFORMATION = 1;
    public final int MSG_TYPE_ERROR = 2;
    public static String TABLE_INDEX = "EDMN_INDEX_NUM_LIASSE";
    public static String TABLE_BUDGETS = "EDMN_BUDGS";
    public static String TABLE_ORDOS = "EDMN_ORDOS";
    public static String TABLE_COMPTABLES = "EDMN_COMPTAS";
    public static final transient String ENCODING = "ISO-8859-1";
    public static final String EDMN_NS_URI = "http://projets.admisource.gouv.fr/xemelios/namespaces#edmn";
    private ProgressFrame progress;
    private int progressShowing = 0;
//    private String fireName = "";
//    private Object fireValue;
    private File tempDir = null;
    private File dirEDMN = null;
    private File budgets = null, ordos = null, comptables = null, output = null;
    /** Taille du buffer pour les lectures/écritures */
    private static final int BUFFER_SIZE = 8 * 1024;
    private DlgGenEdmn dlg;
    private Connection con = null;
    final CharacterEscaper escaper = new DefaultCharacterEscaper();
    private EdmnWriter writer;
    private File patchIndex;
    DecimalFormat df = new DecimalFormat("##0.00");
//    private static long ministereError=0, programmeError=0, actionError=0, cpceError=0;
    private String AnneeGestionCourante;

    public EdmnImporteurWorker(Connection con, File budgets, File ordos, File comptables, File output, String anneeGestion, DlgGenEdmn dlg) {
        super();
        this.con = con;
        this.budgets = budgets;
        this.ordos = ordos;
        this.comptables = comptables;
        this.output = output;
        this.dlg = dlg;
        this.AnneeGestionCourante = anneeGestion.length()==2 ? anneeGestion : anneeGestion.substring(anneeGestion.length()-2);
        DecimalFormatSymbols dfs = df.getDecimalFormatSymbols();
        dfs.setDecimalSeparator('.');
        df.setDecimalFormatSymbols(dfs);
    }

    @Override
    public String doInBackground() {
        FileInfo fileInfo = new FileInfo();
        start = System.currentTimeMillis();
        this.getProgressDlg().displayProgress();


        filesToTreat[2] = budgets;
        filesToTreat[3] = ordos;
        filesToTreat[4] = comptables;
        //saveNomenclatures(filesToTreat);

        try {
            try {
                File f = filesToTreat[1];
                final String fName = f.getName();

                // Sauvegarde de l'index en base
                fireProperty("FILE_NAME", fName);
                fireProperty("initialize", getProgressDlg().nbLines[1]);
                saveIndex(f, getProgressDlg().nbLines[1]);
                fireProperty("currentTerminated", null);
            } catch (Exception ex) {
                inProcessException = ex;
                ex.printStackTrace();
            }
            try {
                File f = filesToTreat[2];
                final String fName = f.getName();

                // Sauvegarde des budgets en base
                fireProperty("FILE_NAME", fName);
                fireProperty("initialize", getProgressDlg().nbLines[2]);
                saveBudgets(f, getProgressDlg().nbLines[2]);
                fireProperty("currentTerminated", null);
            } catch (Exception ex) {
                inProcessException = ex;
                ex.printStackTrace();
            }
            try {
                File f = filesToTreat[3];
                final String fName = f.getName();

                // Sauvegarde des ordos en base
                fireProperty("FILE_NAME", fName);
                fireProperty("initialize", getProgressDlg().nbLines[3]);
                saveOrdos(f, getProgressDlg().nbLines[3]);
                fireProperty("currentTerminated", null);
            } catch (Exception ex) {
                inProcessException = ex;
                ex.printStackTrace();
            }
            try {
                File f = filesToTreat[4];
                final String fName = f.getName();

                // Sauvegarde des comptables en base
                fireProperty("FILE_NAME", fName);
                fireProperty("initialize", getProgressDlg().nbLines[4]);
                saveComptas(f, getProgressDlg().nbLines[4]);
                fireProperty("currentTerminated", null);
            } catch (Exception ex) {
                inProcessException = ex;
                ex.printStackTrace();
            }
//            generatedFiles = new ArrayList<File>();
            try {
                File f = filesToTreat[0];
                final String fName = f.getName();

                // Création du fichier EDMN
                fireProperty("FILE_NAME", fName);
                fireProperty("initialize", getProgressDlg().nbLines[0]);
                importFile(f, getProgressDlg().nbLines[0]);
                fireProperty("currentTerminated", null);
            } catch (Exception ex) {
                inProcessException = ex;
                ex.printStackTrace();
            }
        } catch (Exception ex) {
            inProcessException = ex;
            ex.printStackTrace();
        } finally {
            if (con != null) {
                try {
                    PoolManager.getInstance().releaseConnection(con);
                } catch (Exception sqlEx) {
                    // nothing to do
                }
            }
        }
        StringBuffer msg = new StringBuffer();
        if (!isCancelled()) {
            long ms = (System.currentTimeMillis() - start);
            int s = Integer.parseInt(String.valueOf(ms / 1000));
            int m = Integer.parseInt(String.valueOf(s / 60));
            int h = Integer.parseInt(String.valueOf(m / 60));


            StringBuffer sb = new StringBuffer("La génération a duré : ");
            if (Integer.parseInt(String.valueOf(h)) != 0) {
                sb.append(h + "h ");
            }
            if (Integer.parseInt(String.valueOf(m)) != 0) {
                sb.append((h != 0 ? " et " : "") + (m - (h * 60)) + "mins ");
            }
            if (Integer.parseInt(String.valueOf(s)) != 0) {
                sb.append(((h != 0 || m != 0) ? " et " : "") + (s - (m * 60)) + "s ");
            }
            if (Integer.parseInt(String.valueOf(ms)) != 0) {
                sb.append(((h != 0 || m != 0 || s != 0) ? " et " : "") + (ms - (s * 1000)) + "ms ");
            }

            msg.append("La génération du fichier est terminé.\n"/* + sb.toString()*/);
            if (false/*fileInfo.getGlobalCount() == 0*/) {
                msg.append("Pas de données générées.");
            } else {
                try {
                    msg.append(fileInfo.toString(Loader.getDocumentsInfos(null)));
                } catch (Exception ex) {
                    // on ignore tout
                }
                msg.append("");
            }
        } else {
            msg.append("La génération du fichier EDMN a été interrompue.");
        }
        logger.info("\n" + msg);
        return msg.toString();
    }

    private void fireProperty(final String name, final Object value) {
//        fireName = name;
//        fireValue = value;
        try {
            if (SwingUtilities.isEventDispatchThread()) {
                firePropertyChange(name, null, value);
            } else {
                SwingUtilities.invokeAndWait(new Runnable() {
                    public void run() {
                        firePropertyChange(name, null, value);
                    }
                });
            }
        } catch (Throwable t) {
            logger.fatal("in fire:", t);
        }
    }

    @Override
    protected void done() {
        super.done();
        if (inProcessException != null) {
            getProgressDlg().displayException(inProcessException);
        }
        String msg = "";
        try {
            msg = get();
        } catch (InterruptedException iEx) {
            iEx.printStackTrace();
        } catch (ExecutionException eEx) {
            eEx.printStackTrace();
        } catch (CancellationException cEx) {
            msg = "Génération annulée";
        }
        if (inProcessException == null) {
            //importEdmnFile();
            if (generateArchive()) {
                if (!DEBUG) {
                    cleanTempFile();
                }
            }
            JOptionPane.showMessageDialog(dlg, "Génération correctement terminée.");
            dlg.enableGenerateButton();
        } else {
            getProgressDlg().displayMessage(msg, MSG_TYPE_INFORMATION);
        }
        tempDir.delete();
        tempDir = null;
        getProgressDlg().hide();
    }

    public void setFilesToTreat(File[] fichiers) throws Exception {
        filesToTreat = fichiers;
    }

    public ProgressFrame getProgressDlg() {
        return progress;
    }

    public void setProgressDlg(ProgressFrame prog) {
        this.progress = prog;
    }

    protected void saveIndex(File index, int nbLignes) throws Exception {
        BufferedReader br;
        String line;
        PreparedStatement ps = null;
        File fSQL = new File(getTempDirectory(), "insert_index.txt");
        FileWriter fw = new FileWriter(fSQL);
        progressShowing = nbLignes / 500;

        try {

            br = new BufferedReader(new FileReader(index));
            line = br.readLine();

            int i = 0;
            int total = 0;
            Long tempsTotalTraitement = Long.parseLong("0");
            Date datedeb = new Date();
            while (line != null) {
                StringBuffer sb = new StringBuffer();
                i++;
                total++;
                if (line.length() > 15) {
                    sb.append(line.substring(0, 2)).append(SEP)
                            .append(line.substring( 2, 3)).append(SEP)
                            .append(line.substring( 3, 9)).append(SEP)
                            .append(line.substring( 9, 10)).append(SEP)
                            .append(line.substring(10, 12)).append(SEP)
                            .append(line.substring(12, 17)).append(SEP)
                            .append(line.substring(17, 21)).append(SEP)
                            .append(line.substring(21, 23)).append(SEP)
                            .append(line.substring(23, 24)).append(SEP)
                            .append(line.substring(24, 26)).append(SEP)
                            .append(line.substring(26, 31)).append(SEP)
                            .append(line.substring(31, 37));
                    if (total != nbLignes) {
                        sb.append("\n");
                    }
                }
                if (i == progressShowing) {
                    fireProperty("progress", i);
                    i = 0;
                }
                line = br.readLine();
                fw.write(sb.toString());
            }
            if (i != 0) {
                fireProperty("progress", i);
            }
            i = 0;
            fw.flush();
            fw.close();
            // Insertion en base des valeurs
            ps = con.prepareStatement("LOAD DATA LOCAL INFILE '" + fSQL.getAbsolutePath().replaceAll("\\\\", "\\\\\\\\") + "' INTO TABLE " + TABLE_INDEX + " FIELDS TERMINATED BY '"+SEP+"'");
            ps.execute();
            fSQL.delete();

            // Calcul de temps de traitement
            Date datefin = new Date();
            tempsTotalTraitement += (datefin.getTime() - datedeb.getTime());
            int minutes = Integer.parseInt(Long.toString(tempsTotalTraitement / (60 * 1000)));
            int secondes = Integer.parseInt(Long.toString(tempsTotalTraitement / (1000)));
            int millis = Integer.parseInt(Long.toString(tempsTotalTraitement));

            logger.info("Temps d'insertion de l'index = " + ((minutes != 0) ? minutes + "mins et " : "") + ((secondes) - (minutes * 60)) + "s et " + ((millis) - (secondes * 1000)) + "ms");

        } catch (IOException e) {
            if (e.getClass().equals(FileNotFoundException.class)) {
                logger.debug("Le fichier d'index n'a pas été trouvé !", e.getCause());
            } else {
                logger.debug("IOException sur le fichier " + index.getAbsolutePath() + " !", e.getCause());
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }

    protected void saveBudgets(File budgets, int nbLignes) throws Exception {
        BufferedReader br;
        String line;
        PreparedStatement ps = null;
        File fSQL = new File(getTempDirectory(), "insert_budgets.txt");
//        FileWriter fw = new FileWriter(fSQL);
        OutputStreamWriter osw = null;
        progressShowing = nbLignes / 500;

        try {
//            ResultSet rs = con.createStatement().executeQuery("show variables like 'character_set_connection'");
//            String connectionEncoding = "latin1";
//            if(rs.next()) {
//                connectionEncoding = rs.getString(2);
//            }
            Charset csReader = Charset.forName(INPUT_ENCODING);
            InputStreamReader reader = new InputStreamReader(new FileInputStream(budgets),csReader);
            br = new BufferedReader(reader);

            Charset csWriter = Charset.forName(OUTPUT_ENCODING);
            osw = new OutputStreamWriter(new FileOutputStream(fSQL), csWriter);
            line = br.readLine();

            int i = 0;
            int total = 0;
            Long tempsTotalTraitement = Long.parseLong("0");
            Date datedeb = new Date();
            while (line != null) {
                i++;
                total++;
                if (line.length() > 15) {
                    String annee = line.substring(0, 4); // Ann?e sur 4 caract?res
                    String typeEnregistrement = line.substring(4, 6); // Type e,registrement (02, 06, 10 , 15) sur 2 caract?res
                    String ministere = line.substring(6, 8); // Minist?re sur 2 caract?res
                    String programme = line.substring(8, 12); // Programme sur 4 caract?res
                    String action = line.substring(12, 14); // Action sur 2 carat?res
                    String articleRegroup = line.substring(14, 16); // Article de regroupement ou code PCE sur 2 carat?res
                    String libelle = upperize(line.substring(16, line.length() - 1).trim());//  Libell? sur + de 75 caract?res
                    osw.write(annee + SEP + typeEnregistrement + SEP + ministere + SEP + programme + SEP + action + SEP + articleRegroup + SEP + libelle);
                    if (total != nbLignes) {
                        osw.write("\n");
                    }
                }
                if (i == progressShowing) {
                    fireProperty("progress", i);
                    i = 0;
                }
                line = br.readLine();
            }
            if (i != 0) {
                fireProperty("progress", i);
            }
            i = 0;
            osw.flush();
            osw.close();
            // Insertion en base des valeurs
            ps = con.prepareStatement("LOAD DATA LOCAL INFILE '" + fSQL.getAbsolutePath().replaceAll("\\\\", "\\\\\\\\") + "' INTO TABLE " + TABLE_BUDGETS + " FIELDS TERMINATED BY '"+SEP+"'");
            ps.execute();
            fSQL.delete();

            // Calcul de temps de traitement
            Date datefin = new Date();
            tempsTotalTraitement += (datefin.getTime() - datedeb.getTime());
            int minutes = Integer.parseInt(Long.toString(tempsTotalTraitement / (60 * 1000)));
            int secondes = Integer.parseInt(Long.toString(tempsTotalTraitement / (1000)));
            int millis = Integer.parseInt(Long.toString(tempsTotalTraitement));

            logger.info("Temps d'insertion budgets = " + ((minutes != 0) ? minutes + "mins et " : "") + ((secondes) - (minutes * 60)) + "s et " + ((millis) - (secondes * 1000)) + "ms");

        } catch (IOException e) {
            if (e.getClass().equals(FileNotFoundException.class)) {
                logger.debug("Le fichier des budgets n'a pas été trouvé !", e.getCause());
            } else {
                logger.debug("IOException sur le fichier " + budgets.getAbsolutePath() + " !", e.getCause());
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }

    protected void saveOrdos(File ordos, int nbLignes) throws Exception {
        BufferedReader br;
        String line;
        PreparedStatement ps = null;
        File fSQL = new File(getTempDirectory(), "insert_ordos.txt");
//        FileWriter fw = new FileWriter(fSQL);
        OutputStreamWriter osw = null;
        progressShowing = nbLignes / 50;

        try {
            Charset csReader = Charset.forName(INPUT_ENCODING);
            InputStreamReader reader = new InputStreamReader(new FileInputStream(ordos),csReader);
            br = new BufferedReader(reader);

//            ResultSet rs = con.createStatement().executeQuery("show variables like 'character_set_connection'");
//            String connectionEncoding = "latin1";
//            if(rs.next()) {
//                connectionEncoding = rs.getString(2);
//            }
            Charset csWriter = Charset.forName(OUTPUT_ENCODING);
            osw = new OutputStreamWriter(new FileOutputStream(fSQL), csWriter);

            line = br.readLine();

            int i = 0;
            int total = 0;
            Long tempsTotalTraitement = Long.parseLong("0");
            Date datedeb = new Date();
            while (line != null) {
                //i++;
                total++;
                if (line.length() > 15) {
                    String RCCST_compteSpeTresor = line.substring(0, 5);
                    String IMSB_section = line.substring(5, 7);
                    String ORNO3_codeOrdo = line.substring(7, 10);
                    String LIORDO_libelle = upperize(line.substring(10).trim());
                    osw.write(ORNO3_codeOrdo + SEP);
                    osw.write(RCCST_compteSpeTresor + SEP);
                    osw.write(IMSB_section + SEP);
                    osw.write(LIORDO_libelle + SEP);
                    osw.write("0"+SEP+"20");
                    osw.write(AnneeGestionCourante);
                    // Dernière ligne?
                    if (total != nbLignes) {
                        osw.write("\n");
                    }
                }
                if (i == progressShowing) {
                    fireProperty("progress", i);
                    i = 0;
                }
                line = br.readLine();
            }
            if (i != 0) {
                fireProperty("progress", i);
            }
            i = 0;
            osw.flush();
            osw.close();
            // Insertion en base des valeurs
            ps = con.prepareStatement("LOAD DATA LOCAL INFILE '" + fSQL.getAbsolutePath().replaceAll("\\\\", "\\\\\\\\") + "' INTO TABLE " + TABLE_ORDOS + " FIELDS TERMINATED BY '"+SEP+"'");
            ps.execute();
            fSQL.delete();

            // Calcul de temps de traitement
            Date datefin = new Date();
            tempsTotalTraitement += (datefin.getTime() - datedeb.getTime());
            int minutes = Integer.parseInt(Long.toString(tempsTotalTraitement / (60 * 1000)));
            int secondes = Integer.parseInt(Long.toString(tempsTotalTraitement / (1000)));
            int millis = Integer.parseInt(Long.toString(tempsTotalTraitement));

            logger.info("Temps d'insertion ordos = " + ((minutes != 0) ? minutes + "mins et " : "") + ((secondes) - (minutes * 60)) + "s et " + ((millis) - (secondes * 1000)) + "ms");

        } catch (IOException e) {
            if (e.getClass().equals(FileNotFoundException.class)) {
                logger.debug("Le fichier d'ordos n'a pas été trouvé !", e.getCause());
            } else {
                logger.debug("IOException sur le fichier " + ordos.getAbsolutePath() + " !", e.getCause());
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }

    protected void saveComptas(File comptas, int nbLignes) throws Exception {
        BufferedReader br;
        String line;
        PreparedStatement ps = null;
        File fSQL = new File(getTempDirectory(), "insert_comptas.txt");
//        FileWriter fw = new FileWriter(fSQL);
        progressShowing = nbLignes / 500;
        OutputStreamWriter osw = null;

        try {
            Charset csReader = Charset.forName(INPUT_ENCODING);
            InputStreamReader reader = new InputStreamReader(new FileInputStream(comptas),csReader);
            br = new BufferedReader(reader);

//            ResultSet rs = con.createStatement().executeQuery("show variables like 'character_set_connection'");
//            String connectionEncoding = "latin1";
//            if(rs.next()) {
//                connectionEncoding = rs.getString(2);
//            }
            Charset csWriter = Charset.forName(OUTPUT_ENCODING);
            osw = new OutputStreamWriter(new FileOutputStream(fSQL), csWriter);
            line = br.readLine();

            int i = 0;
            int total = 0;
            //Long tempsTotalTraitement = new Long(0l);
            Date datedeb = new Date();
            while (line != null) {
                //i++;
                total++;
                if (line.length() > 15) {
                    // Dernière ligne?
                    if (total == nbLignes) {
                        osw.write(line.substring(0, 6) + SEP + upperize(line.substring(6, line.length() - 1)));
                    } else {
                        osw.write(line.substring(0, 6) + SEP + upperize(line.substring(6, line.length() - 1)) + "\n");
                    }
                }
                if (i == progressShowing) {
                    fireProperty("progress", i);
                    i = 0;
                }
                line = br.readLine();
            }
            if (i != 0) {
                fireProperty("progress", i);
            }
            i = 0;
            osw.flush();
            osw.close();
            // Insertion en base des valeurs
            ps = con.prepareStatement("LOAD DATA LOCAL INFILE '" + fSQL.getAbsolutePath().replaceAll("\\\\", "\\\\\\\\") + "' INTO TABLE " + TABLE_COMPTABLES + " FIELDS TERMINATED BY '"+SEP+"'");
            ps.execute();
            fSQL.delete();

            // Calcul de temps de traitement
            Date datefin = new Date();
            long tempsTotalTraitement = (datefin.getTime() - datedeb.getTime());
            int minutes = Integer.parseInt(Long.toString(tempsTotalTraitement / (60 * 1000)));
            int secondes = Integer.parseInt(Long.toString(tempsTotalTraitement / (1000)));
            int millis = Integer.parseInt(Long.toString(tempsTotalTraitement));

            logger.info("Temps d'insertion comptas = " + ((minutes != 0) ? minutes + "mins et " : "") + ((secondes) - (minutes * 60)) + "s et " + ((millis) - (secondes * 1000)) + "ms");

        } catch (IOException e) {
            if (e.getClass().equals(FileNotFoundException.class)) {
                logger.debug("Le fichier des comptas n'a pas été trouvé !", e.getCause());
            } else {
                logger.debug("IOException sur le fichier " + comptas.getAbsolutePath() + " !", e.getCause());
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
    }

    protected FileInfo importFile(File f, int nbLignes) throws Exception {
        FileInfo ret = new FileInfo();

        dirEDMN = File.createTempFile("tmp", "edmn", f.getParentFile());
        dirEDMN.delete();
        dirEDMN.mkdir();
        if (dirEDMN != null && dirEDMN.listFiles().length > 0) {
            for (File fToDelete : dirEDMN.listFiles()) {
                fToDelete.delete();
            }
        }

        generateXmlFileText(f, /*dirEDMN,*/ nbLignes);
        return ret;
    }

    protected static String fmtNumLiasse(int i) {
        String v = Integer.toString(i);
        while (v.length() < 4) {
            v = "0".concat(v);
        }
        return v;
    }


    protected String getLibelleMinistere(String ministere, String annee, Connection con) {
        PreparedStatement ps = null;
        String ret = NOT_AVAILABLE;
        try {
            ps = con.prepareStatement(
                    "SELECT BUD_LIBELLE FROM " + TABLE_BUDGETS +
                    " WHERE BUD_TYPE_ENREGISTREMENT='02' " +
                    "AND BUD_MINISTERE=? " +
                    "AND BUD_ANNEE=? ");
//                    "AND BUD_PROGRAMME='00' "+
//                    "AND BUD_ACTION='00' "+
//                    "AND BUD_ARTICLE='00'");
            ps.setString(1, ministere);
            ps.setString(2, annee);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = escape(rs.getString(1));
            } else {
//                logger.warn(++ministereError+" no ministere found for(" + ministere + ","+annee+ ")");
            }
            if (rs.next()) {
//                logger.error("getLibelleMinistere(" + ministere + ","+annee+") return more than 1 record");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    protected String getLibelleProgramme(String ministere, String annee, String programme, Connection con) {
        PreparedStatement ps = null;
        String ret = NOT_AVAILABLE;
        try {
            ps = con.prepareStatement(
                    "SELECT BUD_LIBELLE FROM " + TABLE_BUDGETS +
                    " WHERE BUD_TYPE_ENREGISTREMENT='06' " +
                    "AND BUD_MINISTERE=? " +
                    "AND BUD_ANNEE=? "+
                    "AND BUD_PROGRAMME=? " +
                    "AND BUD_ACTION='00' " +
                    "AND BUD_ARTICLE='00'");
            ps.setString(1, ministere);
            ps.setString(2, annee);
            ps.setString(3, programme);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = escape(rs.getString(1));
            } else {
  //              logger.warn(++programmeError+" no program found for(" + ministere+ ","+annee + "," + programme + ")");
            }
            if (rs.next()) {
//                logger.error("getLibelleProgramme(" + ministere+ ","+annee + "," + programme + ") return more than 1 record");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    protected String getLibelleAction(String ministere, String annee, String programme, String action, String artRegr, Connection con) {
        PreparedStatement ps = null;
        String ret = NOT_AVAILABLE;
        try {
            ps = con.prepareStatement(
                    "SELECT BUD_LIBELLE FROM " + TABLE_BUDGETS +
                    " WHERE BUD_TYPE_ENREGISTREMENT='10' " +
                    "AND BUD_MINISTERE=? "+
                    "AND BUD_ANNEE=? "+
                    "AND BUD_PROGRAMME=? " +
                    "AND BUD_ACTION=? " +
                    "AND BUD_ARTICLE=?");
            ps.setString(1, ministere);
            ps.setString(2, annee);
            ps.setString(3, programme);
            ps.setString(4, action);
            ps.setString(5, artRegr);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = escape(rs.getString(1));
//                logger.debug("action found for ("+ministere+","+programme+","+action+","+artRegr+")");
            } else {
  //              logger.warn(++actionError+" no action found for (" + ministere+ ","+annee + "," + programme + "," + action + "," + artRegr + ")");
            }
            if (rs.next()) {
//                logger.error("getLibelleAction(" + ministere+ ","+annee + "," + programme + "," + action + "," + artRegr + ") return more than 1 record");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    protected String getLibelleCPCE(String ministere, String annee, String programme, String action, String artRegr, Connection con) {
        PreparedStatement ps = null;
        String ret = NOT_AVAILABLE;
        try {
            ps = con.prepareStatement(
                    "SELECT BUD_LIBELLE FROM " + TABLE_BUDGETS +
                    " WHERE BUD_TYPE_ENREGISTREMENT='15' " +
                    "AND BUD_MINISTERE=? " +
                    "AND BUD_ANNEE=? "+
                    "AND BUD_PROGRAMME=? " +
                    "AND BUD_ACTION=? " +
                    "AND BUD_ARTICLE=?");
            ps.setString(1, ministere);
            ps.setString(2, annee);
            ps.setString(3, programme);
            ps.setString(4, action);
            ps.setString(5, artRegr);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = escape(rs.getString(1));
//                logger.debug("CPCE found for ("+ministere+","+programme+","+action+","+artRegr+")");
            } else {
//                logger.warn(++cpceError+" no CPCE found for (" + ministere+ ","+annee + "," + programme + "," + action + "," + artRegr + ")");
            }
            if (rs.next()) {
//                logger.error("getLibelleCPCE(" + ministere+ ","+annee + "," + programme + "," + action + "," + artRegr + ") return more than 1 record");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    protected String getLibelleOrdo(String orno3, String rccst, String imsb, String annee, String trimestre, Connection con) {
        PreparedStatement ps = null;
        String ret = NOT_AVAILABLE;
        try {
            ps = con.prepareStatement("SELECT ORD_LIORDO FROM " + TABLE_ORDOS +
                    " WHERE ORD_ORNO3=? " +
                    "AND ORD_RCCST=? " +
                    "AND ORD_IMSB=? " +
                    "AND ORD_ANNEE=? "
//                    "AND ORD_TRIMESTRE=?"
                    );
            ps.setString(1, orno3);
            ps.setString(2, rccst);
            ps.setString(3, imsb);
            ps.setString(4,annee);
//            ps.setString(5,trimestre);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = escape(rs.getString(1));
//                logger.debug("ordo found for ("+orno3+","+rccst+","+imsb+")");
                if (rs.next()) {
//                    logger.error("libelleOrdo(" + orno3 + "," + rccst + "," + imsb + ") returns more than 1 record");
                }
            } else {
//                logger.warn("no libelleOrdo found for (" + orno3 + "," + rccst + "," + imsb + ","+annee+")");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    protected String getLibelleCompta(String code, /*String annee, String trimestre,*/ Connection con) {
        PreparedStatement ps = null;
        String ret = NOT_AVAILABLE;
        try {
            ps = con.prepareStatement("SELECT CMP_LIBELLE FROM " + TABLE_COMPTABLES + " WHERE CMP_CODE=?");
            ps.setString(1, code);
//            ps.setString(2, annee);
//            ps.setString(3, trimestre);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = escape(rs.getString(1));
            } else {
//                logger.warn("no compta found for(" + code+ /*","+annee+ ","+trimestre +*/ ")");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    protected String getNumLiasseNationale(String annee, String trimestre, String comptable, String imputation, String ministere, String numLiasseLocaleDeb, String numLiasseLocaleFin, Connection con) {
        PreparedStatement ps = null;
        StringBuffer ret = new StringBuffer();
        StringBuffer numLiasses = new StringBuffer();
        int first = 0;
        int last = 0;
        try {
            first = Integer.parseInt(numLiasseLocaleDeb);
        } catch (NumberFormatException nfEx) {
        }
        try {
            last = Integer.parseInt(numLiasseLocaleFin);
        } catch (NumberFormatException nfEx) {
        }
        numLiasses.append("(");
        for (int i = first; i < last; i++) {
            numLiasses.append("'").append(fmtNumLiasse(i)).append("',");
        }
        numLiasses.append("'").append(fmtNumLiasse(last)).append("')");
        try {
            String sql = "SELECT IND_ANNEE_GEST, IND_TRI_GEST, IND_COMPTABLE, IND_IMPUTATION, IND_MINISTERE, IND_COMPTESPE, IND_LOCALE, IND_ANNEE_TRSP, IND_TRI_TRSP, IND_SECTION_BUDG, IND_CPTESPENAT, IND_NATIONALE FROM " + TABLE_INDEX + " WHERE IND_ANNEE_GEST=? AND IND_TRI_GEST=? AND IND_COMPTABLE=? AND IND_IMPUTATION=? AND IND_MINISTERE=? AND IND_LOCALE IN " + numLiasses.toString() + " ORDER BY IND_LOCALE";
            ps = con.prepareStatement(sql);
            ps.setString(1, annee);
            ps.setString(2, trimestre);
            ps.setString(3, comptable);
            ps.setString(4, imputation);
            ps.setString(5, ministere);
            ResultSet rs = ps.executeQuery();
            boolean alreadyNexted = false;
            do {
                if (rs.next()) {
                    // TODO
//                    ret.append("<Liasse V=\"").append(escape(rs.getString(1))).append("\"/>");
                    String anneeGestion = rs.getString(1);
                    String triGestion = rs.getString(2);
                    //String comptable = rs.getString(3);
                    //String imputation = rs.getString(4);
                    //String ministere = rs.getString(5);
                    String compteSpeTresor = rs.getString(6);
                    String liasseLocale = rs.getString(7);
                    String anneeTransp  = rs.getString(8);
                    String triTransp = rs.getString(9);
                    String sectionBudg = rs.getString(10);
                    String cpteSpeNature = rs.getString(11);
                    String liasseNat = rs.getString(12);
                    String codeBarre = anneeGestion + "." + triGestion + "." + comptable + "." + imputation+ministere +"."+compteSpeTresor+"."+liasseLocale;
                    String etq = sectionBudg + "." + anneeTransp + "." + triTransp +"."+cpteSpeNature+"."+liasseNat;
                    ret.append("<Liasse anneeGest=\"").append(anneeGestion).append("\" triGest=\"").append(triGestion).append("\" cpteSpeTres=\"").append(compteSpeTresor).append("\" local=\"").append(liasseLocale);
                    ret.append("\" anneeTrsp=\"").append(anneeTransp).append("\" triTrsp=\"").append(triTransp).append("\" sectionBudg=\"").append(sectionBudg).append("\" cpteSpeNat=\"").append(cpteSpeNature).append("\" national=\"").append(liasseNat);
                    ret.append("\" codeBarre=\"").append(codeBarre).append("\" etq=\"").append(etq).append("\"/>");
                    alreadyNexted = true;
                } else {
                    if (!alreadyNexted) {
//                        logger.warn("no liasse found for(" + annee + "," + trimestre + "," + comptable + "," + ministere + "," + numLiasseLocaleDeb + "," + numLiasseLocaleFin );
                    }
                    break;
                }
            } while (true);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret.toString();
    }

    protected void generateXmlFileText(File source, /*File dirDest,*/ int nbLignes) throws IOException, SAXException, SQLException {
        progressShowing = 500; // nbLignes / (nbLignes / 500);

//        dirDest.mkdir();

        BufferedReader br = new BufferedReader(new FileReader(source));

        String line = br.readLine();
        writer = new EdmnWriter(dirEDMN, source.getName());
        int lineCount = 0;
        int roundCount = 0;
        while(line!=null && line.length()>0) {
            LineInfo lineInfo = createLineText(line.toCharArray(), con, roundCount);
            writer.writeLine(lineInfo);
            lineCount++;
            if(lineCount == progressShowing) {
                fireProperty("progress", progressShowing);
                lineCount = 0;
                roundCount++;
            }
            line = br.readLine();
        }
        br.close();
        createRepositorys(writer);
        writer.flush();
        patchIndex = generatePatchIndex(writer.getMainTrimestre(),con);

    }

    private File generatePatchIndex(TrimestreMarker tm, Connection con) throws SQLException, IOException {
        File f = writer.getTargetFile();
        String name = "INDEX-"+writer.getMainTrimestre().toString();
        File ret = new File(f.getParentFile(),name);
        FileWriter fw = new FileWriter(ret);
        String sql = "SELECT IND_ANNEE_GEST, IND_TRI_GEST, IND_COMPTABLE, IND_IMPUTATION, IND_MINISTERE, IND_COMPTESPE, IND_LOCALE, IND_ANNEE_TRSP, IND_TRI_TRSP, IND_SECTION_BUDG, IND_CPTESPENAT, IND_NATIONALE FROM "+ TABLE_INDEX +" WHERE IND_ANNEE_GEST<>? OR IND_TRI_GEST<>?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,tm.getAnnee());
        ps.setString(2, tm.getTrimestre());
        ResultSet rs = ps.executeQuery();
        while(rs.next()) {
            fw.write(rs.getString(1));  // annee
            fw.write(";");
            fw.write(rs.getString(2));  // trimestre
            fw.write(";");
            fw.write(rs.getString(3));  // comptable
            fw.write(";");
            fw.write(rs.getString(4));  // imputation
            fw.write(";");
            fw.write(rs.getString(5));  // ministere
            fw.write(";");
            fw.write(rs.getString(6));  // compte spe tres
            fw.write(";");
            fw.write(rs.getString(7));  // local
            fw.write(";");
            fw.write(rs.getString(8));  // annee trsp
            fw.write(";");
            fw.write(rs.getString(9));  // tri trsp
            fw.write(";");
            fw.write(rs.getString(10));  // section budg
            fw.write(";");
            fw.write(rs.getString(11));  // cpte spe nat
            fw.write(";");
            fw.write(rs.getString(12));  // national
            fw.write("\n");
        }
        fw.flush();
        fw.close();
        ps.close();
        return ret;
    }

    /**
     * 
     * @param budgets : Fichier contenant les codes/libelles des budgets
     * @param ordonateurs : Fichier contenant les codes/libelles des ordonateurs
     * @param comptables : Fichier contenant les codes/libelles des comptables
     */
    private void createRepositorys(/*BufferedOutputStream writer, File budgets, File ordonateurs, File comptables*/ EdmnWriter writer) throws IOException {
        createBudgetRepository(writer.prepareRepository("EDMN_"+AnneeGestionCourante+"-NomenclatureBudget"));
        createOrdoRepository(writer.prepareRepository("EDMN_"+AnneeGestionCourante+"-NomenclatureOrdonnateur"));
        createComptaRepository(writer.prepareRepository("EDMN_"+AnneeGestionCourante+"-NomenclatureComptable"));
    }

    private void createBudgetRepository(OutputStreamWriter writer) {
        try {
//            writer.write(("<NomenclatureBudgets>\n").getBytes());
            writer.write("<NomenclatureBudgets>\n");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT BUD_ANNEE,BUD_TYPE_ENREGISTREMENT,BUD_MINISTERE,BUD_PROGRAMME,BUD_ACTION,BUD_ARTICLE,BUD_LIBELLE FROM "+TABLE_BUDGETS);
            while (rs.next()) {
                String typeEnregistrement = rs.getString(2);
                String datatype = null;
                if("02".equals(typeEnregistrement)) datatype = "MINISTERE";
                else if("06".equals(typeEnregistrement)) datatype = "PROGRAMME";
                else if("10".equals(typeEnregistrement)) datatype = "ACTION";
                else if("15".equals(typeEnregistrement)) datatype = "CPCE";

//                writer.write(("<Budget><Annee V=\"" + rs.getString(1) + "\"/><Libelle V=\"" + escape(upperize(rs.getString(7))) + "\"/><Data V=\""+datatype+"\"/><Ministere V=\""+rs.getString(3)+"\"/>").getBytes());
                writer.write("<Budget><Annee V=\"" + rs.getString(1) + "\"/><Libelle V=\"" + escape(upperize(rs.getString(7))) + "\"/><Data V=\""+datatype+"\"/><Ministere V=\""+rs.getString(3)+"\"/>");
                if(!"MINISTERE".equals(datatype) && rs.getString(4)!=null ) {
//                    writer.write(("<Programme V=\""+rs.getString(4)+"\"/>").getBytes());
                    writer.write("<Programme V=\""+rs.getString(4)+"\"/>");
                    if(!"PROGRAMME".equals(datatype) && rs.getString(5)!=null) {
//                        writer.write(("<Action V=\""+rs.getString(5)+"\"/>").getBytes());
                        writer.write("<Action V=\""+rs.getString(5)+"\"/>");
                        if(rs.getString(6)!=null) { //  &&
                            if("ACTION".equals(datatype)) {
//                                writer.write(("<ArticleRegroupt V=\""+rs.getString(6)+"\"/>").getBytes());
                                writer.write("<ArticleRegroupt V=\""+rs.getString(6)+"\"/>");
                            } else if("CPCE".equals(datatype)) {
//                                writer.write(("<Cpce V=\""+rs.getString(6)+"\"/>").getBytes());
                                writer.write("<Cpce V=\""+rs.getString(6)+"\"/>");
                            }
                        }
                    }
                }
//                writer.write("</Budget>\n".getBytes());
                writer.write("</Budget>\n");
            }
//            writer.write(("</NomenclatureBudgets>\n").getBytes());
            writer.write("</NomenclatureBudgets>\n");
            rs.close();
            st.close();
        } catch (Exception e) {
            logger.debug("Problème lors de la création de la nomenclature budget !");
            e.printStackTrace();
        }
    }

    private void createOrdoRepository(OutputStreamWriter writer) {
        try {
//            writer.write(("<NomenclatureOrdonnateurs>\n").getBytes());
            writer.write("<NomenclatureOrdonnateurs>\n");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT ORD_ORNO3, ORD_RCCST, ORD_IMSB, ORD_LIORDO, ORD_TRIMESTRE, ORD_ANNEE FROM "+TABLE_ORDOS);
            while (rs.next()) {
//                writer.write(("<Ordonnateur><CodeOrdo V=\"" + rs.getString(1).trim() + "\"/><CompteSpeTresor V=\""+rs.getString(2)+"\"/><CodeMinistere V=\""+rs.getString(3)+"\"/><Trimestre V=\""+rs.getString(5)+"\"/><Annee V=\""+rs.getString(6)+"\"/><Libelle V=\"" + escape(upperize(rs.getString(4).trim())) + "\"/></Ordonnateur>\n").getBytes());
                writer.write("<Ordonnateur><CodeOrdo V=\"" + rs.getString(1).trim() + "\"/><CompteSpeTresor V=\""+rs.getString(2)+"\"/><CodeMinistere V=\""+rs.getString(3)+"\"/><Trimestre V=\""+rs.getString(5)+"\"/><Annee V=\""+rs.getString(6)+"\"/><Libelle V=\"" + escape(upperize(rs.getString(4).trim())) + "\"/></Ordonnateur>\n");
            }
//            writer.write(("</NomenclatureOrdonnateurs>\n").getBytes());
            writer.write("</NomenclatureOrdonnateurs>\n");
            rs.close();
            st.close();
        } catch (Exception e) {
            logger.debug("Problème lors de la création de la nomenclature ordonnateurs !");
            e.printStackTrace();
        }
    }

    private void createComptaRepository(OutputStreamWriter writer) {
        try {
//            writer.write(("<NomenclatureComptables>\n").getBytes());
            writer.write("<NomenclatureComptables>\n");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT CMP_CODE,CMP_LIBELLE FROM "+TABLE_COMPTABLES);
            while (rs.next()) {
//                writer.write(("<Comptable><Annee V=\"20"+AnneeGestionCourante+"\"/><Code V=\"" + rs.getString(1) + "\"/><Libelle V=\"" + escape(upperize(rs.getString(2))) + "\"/></Comptable>\n").getBytes());
                writer.write("<Comptable><Annee V=\"20"+AnneeGestionCourante+"\"/><Code V=\"" + rs.getString(1) + "\"/><Libelle V=\"" + escape(upperize(rs.getString(2))) + "\"/></Comptable>\n");
            }
//            writer.write(("</NomenclatureComptables>\n").getBytes());
            writer.write("</NomenclatureComptables>\n");
            rs.close();
            st.close();
        } catch (Exception e) {
            logger.debug("Problème lors de la création de la nomenclature comptables !");
            e.printStackTrace();
        }
    }

    public String getAdditionalMessage() {
        return "";
    }

    private String getLine(char[] tab, int deb, int fin) {
        String s = "";
        for (int i = deb; i < fin; i++) {
            s += String.valueOf(tab[i]);
        }
        return escaper.escapeString(s.trim());
    }

    private String getCodeReferenceNomenclature(String annee,String trimestre) {
        return annee + "-" + trimestre;
    }

    private String getLibelleReferenceNomenclature(String annee,String trimestre) {
        return "Année " + annee + " - Trimestre " + trimestre;
    }

    protected void writeNode(String nodeName, String value, DefaultHandler h) throws SAXException {
        AttributesImpl attr = new AttributesImpl();
        attr.addAttribute("", "V", "V", "String", value);
        h.startElement(EDMN_NS_URI, nodeName, "n:" + nodeName, attr);
        h.endElement(EDMN_NS_URI, nodeName, "n:" + nodeName);
    }

    private LineInfo createLineText(char[] line, Connection con, int lineCount) throws SAXException {
        String comptable = getLine(line, 0, 6);     // CBLE
        String imin = getLine(line, 6, 9);            // IMIN
        String imsb = imin.substring(1);            // 2 derniers caracteres de IMIN
        String imputation = imin.substring(0,1);
        String ordo = getLine(line, 9, 15);         // CTORDO
        String typeEvent = getLine(line, 15, 18);     // TYEVEN
        String anneeGestion = getLine(line, 18, 20);  // ANGEST
        String noMdt = getLine(line, 20, 26);         // NOMDT
        String cpteSpecTres = getLine(line, 26, 31);  // RCCST
        String noOpeInv = getLine(line, 31, 39);      // NOOPIN
        String anneeEngag = getLine(line, 39, 41);    // DAENGA
        String noEngag = getLine(line, 41, 47);       // NOENG
        String programme = getLine(line, 47, 51);   // ICHAP
        String action = getLine(line, 51, 53);      // IART
        String PCE = getLine(line, 53, 55);         // IPARAG
        String mtPj = getLine(line, 55, 68);          // MTEDM
        String signeMtPj = getLine(line, 68, 69);     // COSIMO
        String trimestre = getLine(line, 69, 70);     // DATRIM
        String mois = getLine(line, 70, 72);          // DAMOIS
        String dateCrea8 = getLine(line, 72, 80);     // DACRE8
        String anneeCrea = getLine(line, 72, 76);     // SIECLE
        String moisCrea = getLine(line, 76, 78);      // COMOIS
        String jourCrea = getLine(line, 78, 80);      // JOUR
        String chapClassPJ = getLine(line, 80, 84);   // ICHACL
        String typePaiement = getLine(line, 84, 87);  // CTTYPA
        String artBudgPrev = getLine(line, 87, 89);   // CIARBP
        String dateValidCompt = getLine(line, 89, 97);// DSVAC
        String filler16 = getLine(line, 97, 113);     // WF16
        String numLiasseDeb = getLine(line, 113, 117);// NOLIA1
        String numLiasseFin = getLine(line, 117, 121);// NOLIA2
        String typeSuiviEco1 = getLine(line, 121, 123);   // COTYS1
        String typeSuiviEco2 = getLine(line, 123, 125);   // COTYS2
        String typeSuiviEco3 = getLine(line, 125, 127);   // COTYS3
        String codeCorr = getLine(line, 127, 131);    // CODACO
        String serviceExec = getLine(line, 138, 148); // COSVE
        String cleChorus = getLine(line, 148, 169);
        String chorusExerc = getLine(line, 148, 152); // DSEXCH
        String chorusSoc = getLine(line, 152, 156);   // COSOCH
        String chorusPiece = getLine(line, 156, 166); // COPICH
        String chorusPoste = getLine(line, 166, 169); // COPOC3
        String anneeCourte = anneeCrea.substring(2);
        String ctordo = getLine(line, 9, 12);       // correction pour codes ordos
        String dateCreation = anneeCrea + "-" + moisCrea + "-" + jourCrea;
        String annee = "20"+anneeGestion;
        if(Integer.parseInt(annee)>2009 || ("2009".equals(annee) && (trimestre.equals("3") || trimestre.equals("4")))) {
            throw new SAXException("Impossible de traiter les périodes postérieures à 2009 T2");
        }
        StringBuilder sb = new StringBuilder("<LigneEdmn numLigne=\""+lineCount+"\">");
        sb.append("<Comptable V=\"").append(comptable).append("\"/><LibelleComptable V=\"").append(anneeGestion.equals(AnneeGestionCourante)?getLibelleCompta(comptable, con):NOT_AVAILABLE);
        sb.append("\"/><Imputation V=\"").append(imputation).append("\"/><Ministere V=\"").append(imsb).append("\" LibelleMinistere=\"");
        sb.append(getLibelleMinistere(imsb, annee, con)).append("\"/><Ordo V=\"").append(ordo).append("\"/><LibelleOrdonnateur V=\"");
        sb.append(getLibelleOrdo(ctordo, cpteSpecTres, imsb, annee, trimestre, con));
        sb.append("\"/><TypeEvt V=\"").append(typeEvent).append("\"/><AnneeBudget V=\"20").append(anneeGestion);
        sb.append("\"/><NumMandat V=\"").append(noMdt).append("\"/><CompteSpeTresor V=\"").append(cpteSpecTres);
        sb.append("\"/><NumOpeInvest V=\"").append(noOpeInv).append("\"/><AnneeEngag V=\"").append("20" + anneeEngag);
        sb.append("\"/><NumEngag V=\"").append(noEngag).append("\"/><ChapBudg V=\"").append(programme);
        sb.append("\"/><ArtBudgExec V=\"").append(action).append("\"/><ParagBudgExec V=\"").append(PCE).append("\"/><LibelleBudget V=\"").append(getLibelleCPCE(imsb, annee, programme, action, PCE, con)).append("\"/>");
        sb.append("<LibelleProgramme V=\"").append(getLibelleProgramme(imsb, annee, programme, con)).append("\"/><LibelleAction V=\"").append(getLibelleAction(imsb, annee, programme, action, artBudgPrev, con)).append("\"/>");
        String mtedm = mtPj;
        while (mtedm.startsWith("0")) {
            mtedm = mtedm.substring(1);
        }
        if(mtedm.length()==0) mtedm="0";
        if (signeMtPj.trim().equals("-")) {
            mtedm = "-" + mtedm;
        }
        try {
            long mt = Long.parseLong(mtedm);
            mtedm = df.format(mt/100d);
        } catch(Exception ex) {
            logger.error("while dividing mt",ex);
        }
        sb.append("<MtPJ V=\"").append(mtedm).append("\"/><Trimestre V=\"").append(trimestre).append("\"/><Mois V=\"");
        sb.append(mois).append("\"/><AnneeCrea V=\"").append(anneeCrea).append("\"/><MoisCrea V=\"");
        sb.append(moisCrea).append("\"/><JourCrea V=\"").append(jourCrea).append("\"/><DateCreation V=\"").append(dateCreation).append("\"/><ChapClassPJ V=\"");
        sb.append(chapClassPJ).append("\"/><TypePaiement V=\"").append(typePaiement).append("\"/><ArtBudgPrev V=\"");
        sb.append(artBudgPrev).append("\"/><NumLiasseDeb V=\"");
        sb.append(numLiasseDeb).append("\"/><NumLiasseFin V=\"").append(numLiasseFin).append("\"/><TypeSuiviEco1 V=\"").append(typeSuiviEco1);
        sb.append("\"/><TypeSuiviEco2 V=\"").append(typeSuiviEco2).append("\"/><TypeSuiviEco3 V=\"").append(typeSuiviEco3);
        sb.append("\"/><CodeCorrection V=\"").append(codeCorr).append("\"/><NumLiasseNationale>");
        sb.append(getNumLiasseNationale(anneeCourte, trimestre, comptable, imputation, imsb, numLiasseDeb, numLiasseFin, con)).append("</NumLiasseNationale>");
        sb.append("<CodeManip V=\"").append(getLine(line, 137, 138)).append("\"/><Zone32 V=\"").append(getLine(line, 138, 170));
        sb.append("\"/>");
        sb.append("<CodeOperExc V=\"").append(getLine(line, 137, 138)).append("\"/>");
        if (Integer.parseInt(anneeGestion) < 2009) {
            sb.append("<NumPieceCompta V=\"").append(getLine(line, 148, 169)).append("\"/>");
        } else {
            sb.append("<ServiceExecutant V=\"").append(serviceExec).append("\"/>");
            sb.append("<ExerComptaChorus V=\"").append(chorusExerc).append("\"/>");
            sb.append("<SocieteChorus V=\"").append(chorusSoc).append("\"/>");
            sb.append("<NumPceChorus V=\"").append(chorusPiece).append("\"/>");
            sb.append("<NumPosteChorus V=\"").append(chorusPoste).append("\"/>");
        }
        sb.append("</LigneEdmn>\n");
        return new LineInfo(sb.toString(),anneeGestion, trimestre);
    }

    private boolean generateArchive() {
        boolean bRet = false;
        File target = output;
        try {
            ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(target));
            ArrayList<File> generatedFiles = writer.getGeneratedFiles();
            for (File f : generatedFiles) {
                ZipEntry ze = new ZipEntry("data/" + f.getName());
                zos.putNextEntry(ze);
                byte[] buffer = new byte[2048];
                BufferedInputStream bis = new BufferedInputStream(new FileInputStream(f));
                int read = bis.read(buffer);
                while (read > 0) {
                    zos.write(buffer, 0, read);
                    read = bis.read(buffer);
                }
                bis.close();
            }
            if(patchIndex!=null) {
                ZipEntry ze = new ZipEntry("patches/" + patchIndex.getName());
                zos.putNextEntry(ze);
                byte[] buffer = new byte[2048];
                BufferedInputStream bis = new BufferedInputStream(new FileInputStream(patchIndex));
                int read = bis.read(buffer);
                while (read > 0) {
                    zos.write(buffer, 0, read);
                    read = bis.read(buffer);
                }
                bis.close();
            }
            ZipEntry manifestEntry = new ZipEntry(XemeliosArchiveImporter.MANIFEST_FILENAME);
            zos.putNextEntry(manifestEntry);
            StringBuffer sb = new StringBuffer();
            sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
            sb.append("<manifeste cgIdCol=\"000000000000\" cgLibelle=\"ETAT\">\n");
            sb.append("\t<volumes>\n");
            sb.append("\t\t<volume fichier=\"").append(target.getName()).append("\" num=\"1\" dateProduction=\"").append(getFormattedCurrentDateTime()).append("\"/>\n");
            sb.append("\t</volumes>\n");
            sb.append("\t<documents>\n");
            for (File f : generatedFiles) {
                sb.append("\t\t<document volume=\"1\" path=\"data/").append(f.getName()).append("\" type=\"edmn\" buIdCol=\"000000000000\" buLibelle=\"ETAT\" buCode=\"0000\" libBudget=\"LFI\"/>\n");
            }
            sb.append("\t</documents>\n");
            if(patchIndex!=null) {
                sb.append("\t<actions>\n");
                sb.append("\t\t<action class=\"fr.gouv.finances.dgfip.xemelios.edmn.patchers.NumLiassePatcher\">\n");
                sb.append("\t\t\t<parameter type=\"java.io.File\" name=\"index.file\">patches/").append(patchIndex.getName()).append("</parameter>\n");
                sb.append("\t\t</action>\n");
                sb.append("\t</actions>\n");
            }
            sb.append("\t</manifeste>\n");
            zos.write(sb.toString().getBytes("UTF-8"));
            zos.flush();
            zos.close();
            bRet = true;
        } catch (Exception ex) {
            logger.error(null, ex);
        }
        return bRet;
    }

    private void cleanTempFile() {
        logger.info("removing temp files");
        File[] files = dirEDMN.listFiles();
        for (File f : files) {
            f.delete();
        }
        dirEDMN.delete();
    }

    private static String getFormattedCurrentDateTime() {
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        return sdf.format(new Date(System.currentTimeMillis()));
    }


    private File makeZip() throws IOException {
        //Création d'un flux d'écriture vers un fichier
        File fout = new File(dirEDMN.getParent(), "EDMN.xml.zip");
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(fout);
            //Ajout du checksum : Adler32 (plus rapide) ou CRC32
            CheckedOutputStream checksum = new CheckedOutputStream(fos, new Adler32());
            try {
                //Création d'un buffer de sortie afin d'améliorer les performances
                BufferedOutputStream bos = new BufferedOutputStream(checksum, BUFFER_SIZE);
                try {
                    //Création d'un flux d'écriture Zip vers le fichier à  travers le buffer
                    ZipOutputStream zos = new ZipOutputStream(bos);
                    try {
                        //Compression maximale
                        try {
                            zos.setMethod(ZipOutputStream.DEFLATED);
                            zos.setLevel(Deflater.BEST_COMPRESSION);
                        } catch (Exception ignor) {
                        }
                        zipDir(dirEDMN.getAbsolutePath(), null, zos);
                    } finally {
                        zos.close();
                    }
                } finally {
                    bos.close();
                }
            } finally {
                checksum.close();
            }
        } finally {
            fos.close();
        }
        return fout;
    }

    private static void zipDir(String basedir, String currentdir, ZipOutputStream zos) throws FileNotFoundException, IOException {
        //create a new File object based on the directory we have to zip
        File zipDir = (currentdir != null) ? new File(basedir, currentdir) : new File(basedir);
        //get a listing of the directory content
        String[] dirList = zipDir.list();
        byte[] readBuffer = new byte[BUFFER_SIZE];
        int bytesIn = 0;
        //On met pas File.separator, mais "/" parce que c'est le caractère utilisé
        //dans les ZIP.
        String currentdir2 = (currentdir != null) ? (currentdir + "/") : "";

        File f;
        FileInputStream fis;
        BufferedInputStream bis;
        ZipEntry anEntry;
        //Create an empty entry with just dir name, like WinZip, so unzipToDir will
        //behave correctly.
        if (currentdir2.length() > 0) {
            anEntry = new ZipEntry(currentdir2);
            zos.putNextEntry(anEntry);
            zos.closeEntry();
        }
        //loop through dirList, and zip the files
        for (int i = 0; i < dirList.length; i++) {
            f = new File(zipDir, dirList[i]);
            if (!f.exists()) {
                continue;
            }
            if (f.isDirectory()) {
                //if the File object is a directory, call this
                //function again to add its content recursively
                zipDir(basedir, currentdir2 + dirList[i], zos);
                continue;
            }
            //if we reached here, the File object f was not a directory
            //create a FileInputStream on top of f
            fis = new FileInputStream(f);
            try {
                bis = new BufferedInputStream(fis, BUFFER_SIZE);
                try {
                    //create a new zip entry
                    anEntry = new ZipEntry(currentdir2 + dirList[i]);
                    anEntry.setTime(f.lastModified());

                    //place the zip entry in the ZipOutputStream object
                    zos.putNextEntry(anEntry);
                    //now write the content of the file to the ZipOutputStream
                    while ((bytesIn = bis.read(readBuffer, 0, BUFFER_SIZE)) != -1) {
                        zos.write(readBuffer, 0, bytesIn);
                    }
                    zos.closeEntry();
                } finally {
                    bis.close();
                }
            } finally {
                //close the Stream
                fis.close();
            }
        }
    }

    public static String escape(String s) {
        StringBuffer sb = new StringBuffer();
        for (char c : s.trim().toCharArray()) {
            switch (c) {
                case '\'': {
                    sb.append("&apos;");
                    break;
                }
                case '"': {
                    sb.append("&quot;");
                    break;
                }
                case '&': {
                    sb.append("&amp;");
                    break;
                }
                case '<': {
                    sb.append("&lt;");
                    break;
                }
                case '>': {
                    sb.append("&gt;");
                    break;
                }
                default:
                    sb.append(c);
            }
        }
        return sb.toString();
    }

    private class LineInfo {
        private String xmlData;
        private TrimestreMarker trimestreMarker;
        public LineInfo(String xml, String annee, String trimestre) {
            super();
            xmlData = xml;
            trimestreMarker = new TrimestreMarker(annee,trimestre);
        }
        public String getXmlData() { return xmlData; }
        public TrimestreMarker getMarker() { return trimestreMarker; }
    }
    private class TrimestreMarker implements Comparable<TrimestreMarker> {
        private int data;
        private String annee, trimestre;
        public TrimestreMarker(String annee, String trimestre) {
            super();
            this.annee=annee;
            this.trimestre=trimestre;
            int a = Integer.parseInt(annee)*100;
            int t = Integer.parseInt(trimestre);
            data = 100*a + t;
        }
        public long getMarker() { return data; }
        @Override
        public int hashCode() {
            return data;
        }
        @Override
        public boolean equals(Object obj) {
            if(obj==null) return true;
            if(obj instanceof TrimestreMarker) {
                TrimestreMarker t = (TrimestreMarker)obj;
                return data==t.getMarker();
            }
            return false;
        }
        public int compareTo(TrimestreMarker o) {
            if(o==null) return 1;
            return (int)(data - o.getMarker());
        }
        public String getAnnee() { return annee; }
        public String getTrimestre() { return trimestre; }
        @Override
        public String toString() { return annee+"-"+trimestre; }
    }
    private class EdmnWriter {
        private File destDir;
        private String baseName;
        private int fileCount = 0;
        private Hashtable<TrimestreMarker,LineWriter> writers;
        public EdmnWriter(File destDir, String baseName) {
            super();
            this.destDir=destDir;
            this.baseName=baseName;
            writers = new Hashtable<TrimestreMarker, LineWriter>();
        }
        public void writeLine(LineInfo line) throws IOException {
            LineWriter writer = writers.get(line.getMarker());
            if(writer==null) {
                writer = new LineWriter(this,line.getMarker());
                writers.put(line.getMarker(), writer);
            }
            writer.writeLine(line);
        }
        public TrimestreMarker getMainTrimestre() {
            TrimestreMarker m = null;
            for(TrimestreMarker tm:writers.keySet()) {
                if(tm.compareTo(m)>0) m=tm;
            }
            if(m==null) {
                m = new TrimestreMarker(AnneeGestionCourante, "1");
            }
            return m;
        }
        public OutputStreamWriter prepareRepository(String fileName) throws IOException {
            TrimestreMarker tm = getMainTrimestre();
            LineWriter writer = writers.get(tm);
            if(writer==null) {
                writer = new LineWriter(this,tm);
                writers.put(tm, writer);
            }
            return writer.prepareRepository(fileName);
        }
        protected File getTargetFile() {
            return new File(destDir,baseName+"-"+Integer.toString(++fileCount)+".xml");
        }
        protected File getTargetFile(String fileName) {
            return new File(destDir,fileName+".xml");
        }
        public void flush() throws IOException {
            for(LineWriter writer:writers.values()) {
                writer.closeFile();
            }
        }
        public ArrayList<File> getGeneratedFiles() {
            ArrayList<File> ret = new ArrayList<File>();
//            File repository = null;
            for(LineWriter writer: writers.values()) {
                ret.addAll(writer.getGeneratedFiles());
//                if(writer.getRepositoryFile()!=null) {
//                    repository = writer.getRepositoryFile();
//                }
            }
//            if(repository!=null) {
//                ArrayList<File> tmp = new ArrayList<File>();
//                tmp.add(repository);
//                for(File f:ret) {
//                    if(!f.equals(repository)) tmp.add(f);
//                }
//                ret = tmp;
//            }
            return ret;
        }
    }
    /**
     * suite a decision du 04/03/2010, on ne modifie plus les libelles
     * @param s
     * @return
     */
    protected static String upperize(String s) {
        return s;
//        StringBuilder sb = new StringBuilder();
//        for(char c:s.toCharArray()) {
//            switch(c) {
//                case 'à':
//                case 'ä':
//                case 'â': { sb.append("A"); break; }
//                case 'ç': { sb.append("C"); break; }
//                case 'é':
//                case 'è':
//                case 'ë':
//                case 'ê': { sb.append("E"); break; }
//                case 'ï':
//                case 'î': { sb.append("I"); break; }
//                case 'ö':
//                case 'ô': { sb.append("O"); break; }
//                case 'ù':
//                case 'ü':
//                case 'û': { sb.append("U"); break; }
//                case 'ÿ':
//                case '?': { sb.append("Y"); break; }
//                default: sb.append(String.valueOf(c).toUpperCase());
//            }
//        }
//        return sb.toString();
    }
    private class LineWriter {
        private EdmnWriter writerController;
        private File currentFile/*, repository*/;
        private OutputStreamWriter bos;
        private int writedLines = 0;
        private TrimestreMarker marker;
        ArrayList<File> generatedFiles;
        boolean isRepository = false;
        public LineWriter(EdmnWriter writerController, TrimestreMarker marker) {
            super();
            this.writerController = writerController;
            this.marker=marker;
            generatedFiles = new ArrayList<File>();
        }
        public void writeLine(LineInfo line) throws IOException {
            if(currentFile==null) {
                initializeNewFile();
            }
//            byte[] data = line.getXmlData().getBytes(ENCODING);
//            bos.write(data, 0, data.length);
            bos.write(line.getXmlData());
            writedLines++;
            if(writedLines==MAX_SIZE_FILE) {
                closeFile();
            }
        }
        protected void initializeNewFile() throws IOException {
            currentFile = writerController.getTargetFile();
            generatedFiles.add(currentFile);
            bos = new OutputStreamWriter(new FileOutputStream(currentFile),ENCODING);
            writedLines = 0;
            StringBuilder sb = new StringBuilder();
            sb.append("<?xml version=\"1.0\" encoding=\"").append(ENCODING).append("\"?>\n<Edmn xmlns=\"").append(EDMN_NS_URI).append("\" xmlns:n=\"").append(EDMN_NS_URI).append("\">\n");
            sb.append("<ReferenceNomenclature>\n<Code V=\"").append(getCodeReferenceNomenclature(marker.getAnnee(),marker.getTrimestre())).append("\"/>\n<Libelle V=\"").append(getLibelleReferenceNomenclature(marker.getAnnee(),marker.getTrimestre())).append("\"/>\n</ReferenceNomenclature>\n");
            sb.append("<Budget><Code V=\"0000\"/><Libelle V=\"LFI\"/></Budget>\n");
            sb.append("<Collectivite><Code V=\"000000000000\"/><Libelle V=\"ETAT\"/></Collectivite>\n");
            sb.append("<EtatEdmn>\n");
//            byte[] b = sb.toString().getBytes(ENCODING);
            bos.write(sb.toString());
            isRepository = false;
        }
        protected void initializeNewFile(String fileName) throws IOException {
            currentFile = writerController.getTargetFile(fileName);
            generatedFiles.add(currentFile);
            bos = new OutputStreamWriter(new FileOutputStream(currentFile),ENCODING);
            writedLines = 0;
            StringBuilder sb = new StringBuilder();
            sb.append("<?xml version=\"1.0\" encoding=\"").append(ENCODING).append("\"?>\n<Edmn xmlns=\"").append(EDMN_NS_URI).append("\" xmlns:n=\"").append(EDMN_NS_URI).append("\">\n");
            sb.append("<ReferenceNomenclature>\n<Code V=\"").append(getCodeReferenceNomenclature(marker.getAnnee(),marker.getTrimestre())).append("\"/>\n<Libelle V=\"").append(getLibelleReferenceNomenclature(marker.getAnnee(),marker.getTrimestre())).append("\"/>\n</ReferenceNomenclature>\n");
            sb.append("<Budget><Code V=\"0000\"/><Libelle V=\"LFI\"/></Budget>\n");
            sb.append("<Collectivite><Code V=\"000000000000\"/><Libelle V=\"ETAT\"/></Collectivite>\n");
            sb.append("<EtatEdmn>\n");
//            byte[] b = sb.toString().getBytes(ENCODING);
            bos.write(sb.toString());
            isRepository = true;
        }
        protected void closeFile() throws IOException {
            if(currentFile!=null) {
                StringBuilder sb = new StringBuilder();
//                if(currentFile==repository) {
                    //sb.append("</Nomenclatures>\n");
//                } else {
                if(!isRepository)
                    sb.append("</EtatEdmn>\n");
//                }
                sb.append("</Edmn>\n");
//                bos.write(sb.toString().getBytes(ENCODING));
                bos.write(sb.toString());
                bos.flush();
                bos.close();
                bos = null;
                currentFile = null;
            }
        }
        public OutputStreamWriter prepareRepository(String fileName) throws IOException {
            closeFile();
            initializeNewFile(fileName);
//            repository = currentFile;
//            bos.write("</EtatEdmn>\n".getBytes(ENCODING));
            bos.write("</EtatEdmn>\n");
            return bos;
        }
//        public File getRepositoryFile() { return repository; }
        public ArrayList<File> getGeneratedFiles() { return generatedFiles; }
    }

    protected File getTempDirectory() {
        if(tempDir==null) {
            File home = new File(System.getProperty("user.home"));
            tempDir = new File(home,"edmn-generator");
            if(tempDir.exists()) throw new RuntimeException("Un autre processus de génération est probablement en cours sur cette machine");
            tempDir.mkdirs();
        }
        return tempDir;
    }
}
