Dépôt officiel du code source de l'ERP OpenConcerto

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.erp.panel.compta;

import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.StringUtils;

import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class ExportFEC extends AbstractExport {

    public static final char ZONE_SEPARATOR = '\t';
    public static final char RECORD_SEPARATOR = '\n';
    
    public static final List<String> COLS = Arrays.asList("JournalCode", "JournalLib", "EcritureNum", "EcritureDate", "CompteNum", "CompteLib", "CompAuxNum", "CompAuxLib", "PieceRef", "PieceDate",
            "EcritureLib", "Debit", "Credit", "EcritureLet", "DateLet", "ValidDate", "Montantdevise", "Idevise");

    private final DecimalFormat format = new DecimalFormat("##0.00", DecimalFormatSymbols.getInstance(Locale.FRANCE));
    private List<Object[]> data;

    private final boolean cloture;

    public ExportFEC(DBRoot rootSociete, boolean cloture) {
        super(rootSociete, "FEC", ".csv");
        this.cloture = cloture;
    }

    @Override
    protected int fetchData(Date from, Date to, SQLRow selectedJournal, boolean onlyNew) {
        final SQLTable tableEcriture = getEcritureT();
        final SQLTable tableMouvement = tableEcriture.getForeignTable("ID_MOUVEMENT");
        final SQLTable tableCompte = tableEcriture.getForeignTable("ID_COMPTE_PCE");
        final SQLTable tableJrnl = tableEcriture.getForeignTable("ID_JOURNAL");
        final SQLTable tablePiece = tableMouvement.getForeignTable("ID_PIECE");

        final SQLSelect sel = createSelect(from, to, selectedJournal, onlyNew);
        sel.addSelect(tableJrnl.getField("CODE"));
        sel.addSelect(tableJrnl.getField("NOM"));
        sel.addSelect(tableMouvement.getField("NUMERO"));
        sel.addSelect(tableEcriture.getField("DATE"));
        sel.addSelect(tableCompte.getField("NUMERO"));
        sel.addSelect(tableCompte.getField("NOM"));
        sel.addSelect(tablePiece.getField("NOM"));
        // TODO ID_MOUVEMENT_PERE* ; SOURCE.DATE
        sel.addSelect(tableEcriture.getField("NOM"));
        sel.addSelect(tableEcriture.getField("DEBIT"));
        sel.addSelect(tableEcriture.getField("CREDIT"));
        sel.addSelect(tableEcriture.getField("DATE_LETTRAGE"));
        sel.addSelect(tableEcriture.getField("LETTRAGE"));
        sel.addSelect(tableEcriture.getField("DATE_VALIDE"));

        sel.addFieldOrder(tableEcriture.getField("DATE"));
        sel.addFieldOrder(tableMouvement.getField("NUMERO"));
        final Where w = new Where(tableEcriture.getField("DEBIT"), "!=", tableEcriture.getField("CREDIT"));
        final Where w2 = new Where(tableEcriture.getField("CLOTURE"), "!=", Boolean.TRUE);
        sel.setWhere(sel.getWhere().and(w).and(w2));

        @SuppressWarnings("unchecked")
        final List<Object[]> l = (List<Object[]>) this.getRootSociete().getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
        this.data = l;
        return l == null ? 0 : l.size();
    }

    private final void addEmptyField(final List<String> line) {
        line.add(null);
    }

    private final void addAmountField(final List<String> line, final Number cents) {
        final String formattedAmount = this.format.format(BigDecimal.valueOf(cents.longValue()).movePointLeft(2));
        line.add(formattedAmount);
    }

    private final void addField(final List<String> line, final String s) {
        if (s == null) {
            throw new NullPointerException("Valeur manquante pour remplir la ligne : " + line);
        }
        final String escapedString = strongEscape(s).trim();
        line.add(escapedString);
    }

    private final String strongEscape(String str) {
        if (str == null) {
            return "";
        }
        final int length = str.length();
        final StringBuilder b = new StringBuilder(length);
        for (int i = 0; i < length; i++) {
            final char c = str.charAt(i);
            final char newChar;
            if (c > 31 && c < 126 && c != ';') {
                // ';' is escaped to avoid issue with Excel
                newChar = c;
            } else if (c == 'é' || c == 'è' || c == 'ê') {
                newChar = 'e';
            } else if (c == 'â' || c == 'à') {
                newChar = 'a';
            } else if (c == 'î') {
                newChar = 'i';
            } else if (c == 'ù' || c == 'û') {
                newChar = 'u';
            } else if (c == 'ô') {
                newChar = 'o';
            } else if (c == 'ç') {
                newChar = 'c';
            } else {
                newChar = ' ';
            }
            b.append(newChar);
        }
        return b.toString();
    }

    @Override
    protected void export(OutputStream out) throws IOException {
        final Writer bufOut = new OutputStreamWriter(out, StringUtils.ISO8859_15);
        final DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        final int fieldsCount = COLS.size();

        for (final String colName : COLS) {
            bufOut.write(colName);
            bufOut.write(ZONE_SEPARATOR);
        }
        bufOut.write(RECORD_SEPARATOR);

        final List<String> line = new ArrayList<String>(fieldsCount);
        for (final Object[] array : this.data) {
            line.clear();

            // JournalCode
            addField(line, (String) array[0]);
            // JournalLib
            addField(line, (String) array[1]);
            // EcritureNum
            addField(line, String.valueOf(array[2]));
            // EcritureDate
            final String ecritureDate = dateFormat.format(array[3]);
            line.add(ecritureDate);
            // CompteNum
            if (array[4] != null) {
                addField(line, (String) array[4]);
            } else {
                bufOut.close();
                JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'a pas de numéro de compte :\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
                return;
            }
            // CompteLib
            if (array[5] != null) {
                addField(line, (String) array[5]);
            } else {
                bufOut.close();
                JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'a pas de libellé de compte pour le compte " + array[4].toString() + " :\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
                return;
            }
            // CompAuxNum
            addEmptyField(line);
            // CompAuxLib
            addEmptyField(line);
            // PieceRef
            addField(line, (String) array[6]);
            // PieceDate TODO ID_MOUVEMENT_PERE* ; SOURCE.DATE
            line.add(ecritureDate);
            // EcritureLib
            String s = (String) array[7];
            if (s == null || s.trim().length() == 0) {
                s = "Sans libellé";
            }
            addField(line, s);
            // Debit
            addAmountField(line, (Number) array[8]);
            // Credit
            addAmountField(line, (Number) array[9]);
            // EcritureLet
            addField(line, (String) array[11]);

            // DateLet
            if (array[10] != null) {
                final String ecritureDateLettrage = dateFormat.format(array[10]);
                line.add(ecritureDateLettrage);
            } else {
                line.add("");
            }
            // ValidDate
            if (array[12] != null) {
                final String ecritureDateValid = dateFormat.format(array[12]);
                line.add(ecritureDateValid);
            } else {
                line.add("");
                if (this.cloture) {
                    bufOut.close();
                    JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'est pas validée (pas de date):\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
                    return;
                }
            }
            // Montantdevise
            addAmountField(line, ((Number) array[8]).longValue() + ((Number) array[9]).longValue());
            // Idevise
            line.add("EUR");

            assert line.size() == fieldsCount;
            for (int i = 0; i < fieldsCount; i++) {
                final String zone = line.get(i);
                // blank field
                if (zone != null)
                    bufOut.write(zone);
                bufOut.write(ZONE_SEPARATOR);
            }
            bufOut.write(RECORD_SEPARATOR);
        }
        bufOut.close();
    }
}