OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 142 | Rev 174 | Go to most recent revision | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 142 Rev 156
1 
/*
 1 
/*
2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 2 
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3 
 *
3 
 *
4 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 4 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5 
 *
5 
 *
6 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 6 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7 
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 7 
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8 
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 8 
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9 
 * language governing permissions and limitations under the License.
 9 
 * language governing permissions and limitations under the License.
10 
 *
10 
 *
11 
 * When distributing the software, include this License Header Notice in each file.
 11 
 * When distributing the software, include this License Header Notice in each file.
12 
 */
 12 
 */
13
13
14 
 package org.openconcerto.erp.panel.compta;
 14 
 package org.openconcerto.erp.panel.compta;
15 
 
 15 
 
16 
import org.openconcerto.sql.model.DBRoot;
 16 
import org.openconcerto.sql.model.DBRoot;
17 
import org.openconcerto.sql.model.SQLRow;
 17 
import org.openconcerto.sql.model.SQLRow;
18 
import org.openconcerto.sql.model.SQLSelect;
 18 
import org.openconcerto.sql.model.SQLSelect;
19 
import org.openconcerto.sql.model.SQLTable;
 19 
import org.openconcerto.sql.model.SQLTable;
20 
import org.openconcerto.sql.model.Where;
 20 
import org.openconcerto.sql.model.Where;
21 
import org.openconcerto.utils.StringUtils;
 21 
import org.openconcerto.utils.StringUtils;
22 
 
 22 
 
23 
import java.io.IOException;
 23 
import java.io.IOException;
24 
import java.io.OutputStream;
 24 
import java.io.OutputStream;
25 
import java.io.OutputStreamWriter;
 25 
import java.io.OutputStreamWriter;
26 
import java.io.Writer;
 26 
import java.io.Writer;
27 
import java.math.BigDecimal;
 27 
import java.math.BigDecimal;
28 
import java.text.DateFormat;
 28 
import java.text.DateFormat;
29 
import java.text.DecimalFormat;
 29 
import java.text.DecimalFormat;
30 
import java.text.DecimalFormatSymbols;
 30 
import java.text.DecimalFormatSymbols;
31 
import java.text.SimpleDateFormat;
 31 
import java.text.SimpleDateFormat;
32 
import java.util.ArrayList;
 32 
import java.util.ArrayList;
33 
import java.util.Arrays;
 33 
import java.util.Arrays;
34 
import java.util.Date;
 34 
import java.util.Date;
35 
import java.util.List;
 35 
import java.util.List;
36 
import java.util.Locale;
 36 
import java.util.Locale;
37 
 
 37 
 
38 
import javax.swing.JFrame;
 38 
import javax.swing.JFrame;
39 
import javax.swing.JOptionPane;
 39 
import javax.swing.JOptionPane;
40 
 
 40 
 
41 
import org.apache.commons.dbutils.handlers.ArrayListHandler;
 41 
import org.apache.commons.dbutils.handlers.ArrayListHandler;
42 
 
 42 
 
43 
public class ExportFEC extends AbstractExport {
 43 
public class ExportFEC extends AbstractExport {
44 
 
 44 
 
45 
    private static final char ZONE_SEPARATOR = '\t';
 45 
    private static final char ZONE_SEPARATOR = '\t';
46 
    private static final char RECORD_SEPARATOR = '\n';
 46 
    private static final char RECORD_SEPARATOR = '\n';
47 
    private static final char REPLACEMENT = ' ';
 47 
    private static final char REPLACEMENT = ' ';
48 
    static private final List<String> COLS = Arrays.asList("JournalCode", "JournalLib", "EcritureNum", "EcritureDate", "CompteNum", "CompteLib", "CompAuxNum", "CompAuxLib", "PieceRef", "PieceDate",
 48 
    static private final List<String> COLS = Arrays.asList("JournalCode", "JournalLib", "EcritureNum", "EcritureDate", "CompteNum", "CompteLib", "CompAuxNum", "CompAuxLib", "PieceRef", "PieceDate",
49 
            "EcritureLib", "Debit", "Credit", "EcritureLet", "DateLet", "ValidDate", "Montantdevise", "Idevise");
 49 
            "EcritureLib", "Debit", "Credit", "EcritureLet", "DateLet", "ValidDate", "Montantdevise", "Idevise");
50 
 
 50 
 
51 
    private final DecimalFormat format = new DecimalFormat("##0.00", DecimalFormatSymbols.getInstance(Locale.FRANCE));
 51 
    private final DecimalFormat format = new DecimalFormat("##0.00", DecimalFormatSymbols.getInstance(Locale.FRANCE));
52 
    private List<Object[]> data;
 52 
    private List<Object[]> data;
53 
 
 53 
 
54 
    private final boolean cloture;
 54 
    private final boolean cloture;
55 
 
 55 
 
56 
    public ExportFEC(DBRoot rootSociete, boolean cloture) {
 56 
    public ExportFEC(DBRoot rootSociete, boolean cloture) {
57 
        super(rootSociete, "FEC", ".csv");
 57 
        super(rootSociete, "FEC", ".csv");
58 
        this.cloture = cloture;
 58 
        this.cloture = cloture;
59 
    }
 59 
    }
60 
 
 60 
 
61 
    @Override
 61 
    @Override
62 
    protected int fetchData(Date from, Date to, SQLRow selectedJournal, boolean onlyNew) {
 62 
    protected int fetchData(Date from, Date to, SQLRow selectedJournal, boolean onlyNew) {
63 
        final SQLTable tableEcriture = getEcritureT();
 63 
        final SQLTable tableEcriture = getEcritureT();
64 
        final SQLTable tableMouvement = tableEcriture.getForeignTable("ID_MOUVEMENT");
 64 
        final SQLTable tableMouvement = tableEcriture.getForeignTable("ID_MOUVEMENT");
65 
        final SQLTable tableCompte = tableEcriture.getForeignTable("ID_COMPTE_PCE");
 65 
        final SQLTable tableCompte = tableEcriture.getForeignTable("ID_COMPTE_PCE");
66 
        final SQLTable tableJrnl = tableEcriture.getForeignTable("ID_JOURNAL");
 66 
        final SQLTable tableJrnl = tableEcriture.getForeignTable("ID_JOURNAL");
67 
        final SQLTable tablePiece = tableMouvement.getForeignTable("ID_PIECE");
 67 
        final SQLTable tablePiece = tableMouvement.getForeignTable("ID_PIECE");
68 
 
 68 
 
69 
        final SQLSelect sel = createSelect(from, to, selectedJournal, onlyNew);
 69 
        final SQLSelect sel = createSelect(from, to, selectedJournal, onlyNew);
70 
        sel.addSelect(tableJrnl.getField("CODE"));
 70 
        sel.addSelect(tableJrnl.getField("CODE"));
71 
        sel.addSelect(tableJrnl.getField("NOM"));
 71 
        sel.addSelect(tableJrnl.getField("NOM"));
72 
        sel.addSelect(tableMouvement.getField("NUMERO"));
 72 
        sel.addSelect(tableMouvement.getField("NUMERO"));
73 
        sel.addSelect(tableEcriture.getField("DATE"));
 73 
        sel.addSelect(tableEcriture.getField("DATE"));
74 
        sel.addSelect(tableCompte.getField("NUMERO"));
 74 
        sel.addSelect(tableCompte.getField("NUMERO"));
75 
        sel.addSelect(tableCompte.getField("NOM"));
 75 
        sel.addSelect(tableCompte.getField("NOM"));
76 
        sel.addSelect(tablePiece.getField("NOM"));
 76 
        sel.addSelect(tablePiece.getField("NOM"));
77 
        // TODO ID_MOUVEMENT_PERE* ; SOURCE.DATE
 77 
        // TODO ID_MOUVEMENT_PERE* ; SOURCE.DATE
78 
        sel.addSelect(tableEcriture.getField("NOM"));
 78 
        sel.addSelect(tableEcriture.getField("NOM"));
79 
        sel.addSelect(tableEcriture.getField("DEBIT"));
 79 
        sel.addSelect(tableEcriture.getField("DEBIT"));
80 
        sel.addSelect(tableEcriture.getField("CREDIT"));
 80 
        sel.addSelect(tableEcriture.getField("CREDIT"));
81 
        sel.addSelect(tableEcriture.getField("DATE_LETTRAGE"));
 81 
        sel.addSelect(tableEcriture.getField("DATE_LETTRAGE"));
82 
        sel.addSelect(tableEcriture.getField("LETTRAGE"));
 82 
        sel.addSelect(tableEcriture.getField("LETTRAGE"));
83 
        sel.addSelect(tableEcriture.getField("DATE_VALIDE"));
 83 
        sel.addSelect(tableEcriture.getField("DATE_VALIDE"));
84 
 
 84 
 
85 
        sel.addFieldOrder(tableEcriture.getField("DATE"));
 85 
        sel.addFieldOrder(tableEcriture.getField("DATE"));
86 
        sel.addFieldOrder(tableMouvement.getField("NUMERO"));
 86 
        sel.addFieldOrder(tableMouvement.getField("NUMERO"));
87 
        sel.setWhere(sel.getWhere().and(new Where(tableEcriture.getField("DEBIT"), "!=", tableEcriture.getField("CREDIT"))));
 87 
        final Where w = new Where(tableEcriture.getField("DEBIT"), "!=", tableEcriture.getField("CREDIT"));
- 
 
 88 
        final Where w2 = new Where(tableEcriture.getField("CLOTURE"), "!=", Boolean.TRUE);
- 
 
 89 
        sel.setWhere(sel.getWhere().and(w).and(w2));
88 
 
 90 
 
89 
        @SuppressWarnings("unchecked")
 91 
        @SuppressWarnings("unchecked")
90 
        final List<Object[]> l = (List<Object[]>) this.getRootSociete().getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
 92 
        final List<Object[]> l = (List<Object[]>) this.getRootSociete().getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
91 
        this.data = l;
 93 
        this.data = l;
92 
        return l == null ? 0 : l.size();
 94 
        return l == null ? 0 : l.size();
93 
    }
 95 
    }
94 
 
 96 
 
95 
    private final void addEmptyField(final List<String> line) {
 97 
    private final void addEmptyField(final List<String> line) {
96 
        line.add(null);
 98 
        line.add(null);
97 
    }
 99 
    }
98 
 
 100 
 
99 
    private final void addAmountField(final List<String> line, final Number cents) {
 101 
    private final void addAmountField(final List<String> line, final Number cents) {
100 
        final String formattedAmount = format.format(BigDecimal.valueOf(cents.longValue()).movePointLeft(2));
 102 
        final String formattedAmount = format.format(BigDecimal.valueOf(cents.longValue()).movePointLeft(2));
101 
        line.add(formattedAmount);
 103 
        line.add(formattedAmount);
102 
    }
 104 
    }
103 
 
 105 
 
104 
    private final void addField(final List<String> line, final String s) {
 106 
    private final void addField(final List<String> line, final String s) {
105 
        if (s == null) {
 107 
        if (s == null) {
106 
            throw new NullPointerException("Valeur manquante pour remplir la ligne : " + line);
 108 
            throw new NullPointerException("Valeur manquante pour remplir la ligne : " + line);
107 
        }
 109 
        }
108 
        // TODO remove \r
 110 
        final String escapedString = StringUtils.toAsciiString(s).trim();
109 
        line.add(s.trim().replace(ZONE_SEPARATOR, REPLACEMENT).replace(RECORD_SEPARATOR, REPLACEMENT));
 111 
        line.add(escapedString.replace(ZONE_SEPARATOR, REPLACEMENT).replace(RECORD_SEPARATOR, REPLACEMENT));
110 
    }
 112 
    }
111 
 
 113 
 
112 
    @Override
 114 
    @Override
113 
    protected void export(OutputStream out) throws IOException {
 115 
    protected void export(OutputStream out) throws IOException {
114 
        final Writer bufOut = new OutputStreamWriter(out, StringUtils.ISO8859_15);
 116 
        final Writer bufOut = new OutputStreamWriter(out, StringUtils.ISO8859_15);
115 
        final DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
 117 
        final DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
116 
        final int fieldsCount = COLS.size();
 118 
        final int fieldsCount = COLS.size();
117 
 
 119 
 
118 
        for (final String colName : COLS) {
 120 
        for (final String colName : COLS) {
119 
            bufOut.write(colName);
 121 
            bufOut.write(colName);
120 
            bufOut.write(ZONE_SEPARATOR);
 122 
            bufOut.write(ZONE_SEPARATOR);
121 
        }
 123 
        }
122 
        bufOut.write(RECORD_SEPARATOR);
 124 
        bufOut.write(RECORD_SEPARATOR);
123 
 
 125 
 
124 
        final List<String> line = new ArrayList<String>(fieldsCount);
 126 
        final List<String> line = new ArrayList<String>(fieldsCount);
125 
        for (final Object[] array : this.data) {
 127 
        for (final Object[] array : this.data) {
126 
            line.clear();
 128 
            line.clear();
127 
 
 129 
 
128 
            // JournalCode
 130 
            // JournalCode
129 
            addField(line, (String) array[0]);
 131 
            addField(line, (String) array[0]);
130 
            // JournalLib
 132 
            // JournalLib
131 
            addField(line, (String) array[1]);
 133 
            addField(line, (String) array[1]);
132 
            // EcritureNum
 134 
            // EcritureNum
133 
            addField(line, String.valueOf(array[2]));
 135 
            addField(line, String.valueOf(array[2]));
134 
            // EcritureDate
 136 
            // EcritureDate
135 
            final String ecritureDate = dateFormat.format(array[3]);
 137 
            final String ecritureDate = dateFormat.format(array[3]);
136 
            line.add(ecritureDate);
 138 
            line.add(ecritureDate);
137 
            // CompteNum
 139 
            // CompteNum
138 
            if (array[4] != null) {
 140 
            if (array[4] != null) {
139 
                addField(line, (String) array[4]);
 141 
                addField(line, (String) array[4]);
140 
            } else {
 142 
            } else {
141 
                bufOut.close();
 143 
                bufOut.close();
142 
                JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'a pas de numéro de compte :\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
 144 
                JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'a pas de numéro de compte :\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
143 
                return;
 145 
                return;
144 
            }
 146 
            }
145 
            // CompteLib
 147 
            // CompteLib
146 
            if (array[5] != null) {
 148 
            if (array[5] != null) {
147 
                addField(line, (String) array[5]);
 149 
                addField(line, (String) array[5]);
148 
            } else {
 150 
            } else {
149 
                bufOut.close();
 151 
                bufOut.close();
150 
                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);
 152 
                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);
151 
                return;
 153 
                return;
152 
            }
 154 
            }
153 
            // CompAuxNum
 155 
            // CompAuxNum
154 
            addEmptyField(line);
 156 
            addEmptyField(line);
155 
            // CompAuxLib
 157 
            // CompAuxLib
156 
            addEmptyField(line);
 158 
            addEmptyField(line);
157 
            // PieceRef
 159 
            // PieceRef
158 
            addField(line, (String) array[6]);
 160 
            addField(line, (String) array[6]);
159 
            // PieceDate TODO ID_MOUVEMENT_PERE* ; SOURCE.DATE
 161 
            // PieceDate TODO ID_MOUVEMENT_PERE* ; SOURCE.DATE
160 
            line.add(ecritureDate);
 162 
            line.add(ecritureDate);
161 
            // EcritureLib
 163 
            // EcritureLib
162 
            String s = (String) array[7];
 164 
            String s = (String) array[7];
163 
            if (s == null || s.trim().length() == 0) {
 165 
            if (s == null || s.trim().length() == 0) {
164 
                s = "Sans libellé";
 166 
                s = "Sans libellé";
165 
            }
 167 
            }
166 
            addField(line, s);
 168 
            addField(line, s);
167 
            // Debit
 169 
            // Debit
168 
            addAmountField(line, (Number) array[8]);
 170 
            addAmountField(line, (Number) array[8]);
169 
            // Credit
 171 
            // Credit
170 
            addAmountField(line, (Number) array[9]);
 172 
            addAmountField(line, (Number) array[9]);
171 
            // EcritureLet
 173 
            // EcritureLet
172 
            addField(line, (String) array[11]);
 174 
            addField(line, (String) array[11]);
173 
 
 175 
 
174 
            // DateLet
 176 
            // DateLet
175 
            if (array[10] != null) {
 177 
            if (array[10] != null) {
176 
                final String ecritureDateLettrage = dateFormat.format(array[10]);
 178 
                final String ecritureDateLettrage = dateFormat.format(array[10]);
177 
                line.add(ecritureDateLettrage);
 179 
                line.add(ecritureDateLettrage);
178 
            } else {
 180 
            } else {
179 
                line.add("");
 181 
                line.add("");
180 
            }
 182 
            }
181 
            // ValidDate
 183 
            // ValidDate
182 
            if (array[12] != null) {
 184 
            if (array[12] != null) {
183 
                final String ecritureDateValid = dateFormat.format(array[12]);
 185 
                final String ecritureDateValid = dateFormat.format(array[12]);
184 
                line.add(ecritureDateValid);
 186 
                line.add(ecritureDateValid);
185 
            } else {
 187 
            } else {
186 
                line.add("");
 188 
                line.add("");
187 
                if (cloture) {
 189 
                if (cloture) {
188 
                    bufOut.close();
 190 
                    bufOut.close();
189 
                    JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'est pas validée (pas de date):\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
 191 
                    JOptionPane.showMessageDialog(new JFrame(), "Une écriture n'est pas validée (pas de date):\n" + line, "Erreur FEC", JOptionPane.ERROR_MESSAGE);
190 
                    return;
 192 
                    return;
191 
                }
 193 
                }
192 
            }
 194 
            }
193 
            // Montantdevise
 195 
            // Montantdevise
194 
            addAmountField(line, ((Number) array[8]).longValue() + ((Number) array[9]).longValue());
 196 
            addAmountField(line, ((Number) array[8]).longValue() + ((Number) array[9]).longValue());
195 
            // Idevise
 197 
            // Idevise
196 
            line.add("EUR");
 198 
            line.add("EUR");
197 
 
 199 
 
198 
            assert line.size() == fieldsCount;
 200 
            assert line.size() == fieldsCount;
199 
            for (int i = 0; i < fieldsCount; i++) {
 201 
            for (int i = 0; i < fieldsCount; i++) {
200 
                final String zone = line.get(i);
 202 
                final String zone = line.get(i);
201 
                // blank field
 203 
                // blank field
202 
                if (zone != null)
 204 
                if (zone != null)
203 
                    bufOut.write(zone);
 205 
                    bufOut.write(zone);
204 
                bufOut.write(ZONE_SEPARATOR);
 206 
                bufOut.write(ZONE_SEPARATOR);
205 
            }
 207 
            }
206 
            bufOut.write(RECORD_SEPARATOR);
 208 
            bufOut.write(RECORD_SEPARATOR);
207 
        }
 209 
        }
208 
        bufOut.close();
 210 
        bufOut.close();
209 
    }
 211 
    }
210 
}
 212 
}