OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
174 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
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
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.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.erp.panel.compta;
15
 
16
import org.openconcerto.erp.core.finance.payment.element.ModeDeReglementSQLElement;
17
import org.openconcerto.sql.model.DBRoot;
18
import org.openconcerto.sql.model.SQLRow;
19
import org.openconcerto.sql.model.SQLRowAccessor;
20
import org.openconcerto.sql.model.SQLRowValues;
21
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
22
import org.openconcerto.sql.model.SQLSelect;
23
import org.openconcerto.sql.model.SQLTable;
24
import org.openconcerto.sql.model.Where;
25
import org.openconcerto.utils.GestionDevise;
26
import org.openconcerto.utils.StringUtils;
27
import org.openconcerto.utils.cc.ITransformer;
28
 
29
import java.io.IOException;
30
import java.io.OutputStream;
31
import java.text.DateFormat;
32
import java.text.SimpleDateFormat;
33
import java.util.Arrays;
34
import java.util.Calendar;
35
import java.util.Date;
36
import java.util.HashMap;
37
import java.util.List;
38
import java.util.Map;
39
 
40
import org.apache.poi.hssf.usermodel.HSSFSheet;
41
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
42
import org.apache.poi.ss.usermodel.Cell;
43
import org.apache.poi.ss.usermodel.CellStyle;
44
import org.apache.poi.ss.usermodel.CellType;
45
import org.apache.poi.ss.usermodel.CreationHelper;
46
import org.apache.poi.ss.usermodel.Row;
47
 
48
public class ExportSageEtendu extends AbstractExport {
49
 
50
    private List<SQLRowValues> data;
51
    private Map<Integer, SQLRowValues> mapFacture = new HashMap<>();
52
    private Map<Integer, SQLRowValues> mapAvoir = new HashMap<>();
53
 
54
    public ExportSageEtendu(DBRoot rootSociete) {
55
        super(rootSociete, "Sage Etendu", ".xls");
56
    }
57
 
58
    @Override
59
    protected int fetchData(Date from, Date to, SQLRow selectedJournal, boolean onlyNew) {
60
 
61
        final SQLTable tableEcriture = getEcritureT();
62
 
63
        // Fetch facture
64
        final SQLTable tableVF = tableEcriture.getTable("SAISIE_VENTE_FACTURE");
65
        SQLRowValues rowValsVF = new SQLRowValues(tableVF);
66
        rowValsVF.put("ID", null);
67
        rowValsVF.put("DATE", null);
68
        rowValsVF.put("NOM", null);
69
        rowValsVF.put("NUMERO", null);
70
 
71
        SQLRowValues rowValsMdr = rowValsVF.putRowValues("ID_MODE_REGLEMENT");
72
 
73
        rowValsMdr.putNulls(rowValsMdr.getTable().getFieldsName());
74
        rowValsMdr.putRowValues("ID_TYPE_REGLEMENT").putNulls("NOM");
75
 
76
        if (tableVF.contains("ID_ABONNEMENT")) {
77
            rowValsVF.putRowValues("ID_ABONNEMENT").putNulls("DATE_DEBUT_FACTURE", "DATE_FIN_FACTURE");
78
        }
79
        SQLRowValuesListFetcher fetcherVF = SQLRowValuesListFetcher.create(rowValsVF);
80
        List<SQLRowValues> resultVF = fetcherVF.fetch(new Where(tableVF.getField("DATE"), from, to));
81
 
82
        for (SQLRowValues sqlRowValues : resultVF) {
83
            this.mapFacture.put(sqlRowValues.getID(), sqlRowValues);
84
        }
85
 
86
        // Fetch avoirs
87
        final SQLTable tableAvoir = tableEcriture.getTable("AVOIR_CLIENT");
88
        SQLRowValues rowValsAvoir = new SQLRowValues(tableAvoir);
89
        rowValsAvoir.put("ID", null);
90
        rowValsAvoir.put("DATE", null);
91
        rowValsAvoir.put("NOM", null);
92
        rowValsAvoir.put("NUMERO", null);
93
        SQLRowValues rowValsMdrAvoir = rowValsAvoir.putRowValues("ID_MODE_REGLEMENT");
94
 
95
        rowValsMdrAvoir.putNulls(rowValsMdrAvoir.getTable().getFieldsName());
96
        rowValsMdrAvoir.putRowValues("ID_TYPE_REGLEMENT").putNulls("NOM");
97
 
98
        SQLRowValuesListFetcher fetcherAvoir = SQLRowValuesListFetcher.create(rowValsAvoir);
99
        List<SQLRowValues> resultAvoir = fetcherAvoir.fetch(new Where(tableAvoir.getField("DATE"), from, to));
100
 
101
        for (SQLRowValues sqlRowValues : resultAvoir) {
102
            this.mapAvoir.put(sqlRowValues.getID(), sqlRowValues);
103
        }
104
 
105
        final SQLTable tableCompte = tableEcriture.getForeignTable("ID_COMPTE_PCE");
106
 
107
        SQLRowValues rowValsFetch = new SQLRowValues(tableEcriture);
108
        rowValsFetch.putNulls("NOM", "DATE", "DEBIT", "CREDIT");
109
        if (tableEcriture.contains("CODE_CLIENT")) {
110
            rowValsFetch.put("CODE_CLIENT", null);
111
        }
112
 
113
        rowValsFetch.putRowValues("ID_MOUVEMENT").putNulls("NUMERO", "SOURCE", "IDSOURCE").putRowValues("ID_PIECE").put("NOM", null);
114
 
115
        rowValsFetch.putRowValues("ID_COMPTE_PCE").putNulls("NUMERO", "NOM");
116
        rowValsFetch.putRowValues("ID_JOURNAL").putNulls("CODE", "NOM");
117
 
118
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowValsFetch);
119
        fetcher.addSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
120
 
121
            @Override
122
            public SQLSelect transformChecked(SQLSelect input) {
123
                input.setWhere(getWhere(from, to, selectedJournal, onlyNew));
124
                input.addFieldOrder(input.getAlias(tableEcriture).getField("ID_MOUVEMENT"));
125
                input.addFieldOrder(input.getAlias(tableCompte).getField("NUMERO"));
126
                return input;
127
            }
128
        }, 0);
129
 
130
        final List<SQLRowValues> l = fetcher.fetch();
131
        this.data = l;
132
        return l == null ? 0 : l.size();
133
    }
134
 
135
    @Override
136
    protected void export(OutputStream bufOut) throws IOException {
137
        final List<SQLRowValues> l = this.data;
138
        final boolean containsCodeClient = getEcritureT().contains("CODE_CLIENT");
139
        final int size = l.size();
140
 
141
        HSSFWorkbook workBook = new HSSFWorkbook();
142
        HSSFSheet sheet = workBook.createSheet("Ecritures");
143
        Row rowEntete = sheet.createRow(0);
144
        List<String> colName = Arrays.asList("Date d'écriture", "Code Journal", "Numéro de facture", "Référence", "Compte Général", "Compte Client", "Libellé de l'écriture", "Date d'échéance",
145
                "Mode de règlement", "Montant débiteur", "Montant Créditeur", "Date de début d'abonnement", "Date de fin d'abonnement");
146
        int colIndex = 0;
147
        for (String string : colName) {
148
            Cell cell = rowEntete.createCell(colIndex, CellType.STRING);
149
            cell.setCellValue(string);
150
            colIndex++;
151
        }
152
 
153
        CellStyle cellStyle = workBook.createCellStyle();
154
        CreationHelper createHelper = workBook.getCreationHelper();
155
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));
156
 
157
        for (int i = 0; i < size; i++) {
158
 
159
            Row row = sheet.createRow(i + 1);
160
 
161
            final SQLRowValues rowLine = l.get(i);
162
 
163
            // Date
164
            final Date d = rowLine.getDate("DATE").getTime();
165
            Cell cellDate = row.createCell(0);
166
            cellDate.setCellStyle(cellStyle);
167
            cellDate.setCellValue(d);
168
            // Jrnl
169
            SQLRowAccessor rowJrnl = rowLine.getForeign("ID_JOURNAL");
170
            Cell cellJrnl = row.createCell(1);
171
            cellJrnl.setCellValue(StringUtils.toAsciiString(rowJrnl.getString("CODE").trim()));
172
 
173
            SQLRowAccessor rowValsSource = null;
174
            SQLRowAccessor rowMvt = rowLine.getForeign("ID_MOUVEMENT");
175
            if (rowMvt.getString("SOURCE").equals("AVOIR_CLIENT")) {
176
                rowValsSource = this.mapAvoir.get(rowMvt.getInt("IDSOURCE"));
177
            } else if (rowMvt.getString("SOURCE").equals("SAISIE_VENTE_FACTURE")) {
178
                rowValsSource = this.mapFacture.get(rowMvt.getInt("IDSOURCE"));
179
            }
180
 
181
            // Numero Piece
182
            Cell cellPiece = row.createCell(2);
183
            if (rowValsSource != null) {
184
                cellPiece.setCellValue(StringUtils.toAsciiString(rowValsSource.getString("NUMERO")));
185
            }
186
 
187
            // Ref vide
188
            Cell cellRef = row.createCell(3);
189
            cellRef.setCellValue("");
190
 
191
            // N° Compte
192
            final String cpt = rowLine.getForeign("ID_COMPTE_PCE").getString("NUMERO").trim();
193
            Cell cellCpt = row.createCell(4, CellType.STRING);
194
            cellCpt.setCellValue(getFormattedCompte(cpt));
195
 
196
            Cell cellCodeClient = row.createCell(5);
197
            if (containsCodeClient) {
198
                // Code Client
199
                String codeClient = "";
200
                if (cpt.startsWith("41")) {
201
                    codeClient = rowLine.getString("CODE_CLIENT");
202
                }
203
                cellCodeClient.setCellValue(StringUtils.toAsciiString(codeClient));
204
            }
205
 
206
            // Libellé
207
            Cell cellLib = row.createCell(6);
208
            cellLib.setCellValue(StringUtils.toAsciiString(rowLine.getString("NOM").trim()));
209
 
210
            Cell cellDateEch = row.createCell(7);
211
            Cell cellTypeRegl = row.createCell(8);
212
            if (rowValsSource != null && cpt.startsWith("41")) {
213
                final SQLRowAccessor foreignMdr = rowValsSource.getForeign("ID_MODE_REGLEMENT");
214
                Date ech = ModeDeReglementSQLElement.calculDate(foreignMdr, rowValsSource.getDate("DATE").getTime());
215
                cellDateEch.setCellValue(ech);
216
                cellDateEch.setCellStyle(cellStyle);
217
                cellTypeRegl.setCellValue(foreignMdr.getForeign("ID_TYPE_REGLEMENT").getString("NOM"));
218
            }
219
 
220
            // Debit
221
            Cell cellDebit = row.createCell(9);
222
            final Long debit = rowLine.getLong("DEBIT");
223
            cellDebit.setCellValue(debit / 100.0D);
224
            // Credit
225
            final Long credit = rowLine.getLong("CREDIT");
226
            Cell cellCredit = row.createCell(10);
227
            cellCredit.setCellValue(credit / 100.0D);
228
 
229
            Cell cellDebAbo = row.createCell(11);
230
            Cell cellFinAbo = row.createCell(12);
231
 
177 ilm 232
            if (rowValsSource != null && rowValsSource.contains("ID_ABONNEMENT") && rowValsSource.getTable().getName().equals("SAISIE_VENTE_FACTURE") && cpt.startsWith("41")) {
174 ilm 233
                SQLRowAccessor rowValsAbo = rowValsSource.getForeign("ID_ABONNEMENT");
234
                if (rowValsAbo != null && !rowValsAbo.isUndefined()) {
235
                    final Calendar calDeb = rowValsAbo.getDate("DATE_DEBUT_FACTURE");
236
                    if (calDeb != null) {
237
                        cellDebAbo.setCellValue(calDeb.getTime());
238
                        cellDebAbo.setCellStyle(cellStyle);
239
                    }
240
 
241
                    final Calendar calFin = rowValsAbo.getDate("DATE_FIN_FACTURE");
242
                    if (calFin != null) {
243
                        cellFinAbo.setCellValue(calFin.getTime());
244
                        cellFinAbo.setCellStyle(cellStyle);
245
                    }
246
                }
247
            }
248
 
249
        }
250
        workBook.write(bufOut);
251
        workBook.close();
252
    }
253
 
254
    private void exportTAB(OutputStream bufOut) throws IOException {
255
        final List<SQLRowValues> l = this.data;
256
        final boolean containsCodeClient = getEcritureT().contains("CODE_CLIENT");
257
        final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
258
        final int size = l.size();
259
        for (int i = 0; i < size; i++) {
260
 
261
            // Ligne à insérer dans le fichier
262
            final StringBuilder line = new StringBuilder();
263
 
264
            final SQLRowValues rowLine = l.get(i);
265
 
266
            // Date
267
            final Date d = rowLine.getDate("DATE").getTime();
268
            line.append(dateFormat.format(d));
269
            line.append('\t');
270
            // Jrnl
271
            SQLRowAccessor rowJrnl = rowLine.getForeign("ID_JOURNAL");
272
            line.append(StringUtils.toAsciiString(rowJrnl.getString("CODE").trim()));
273
            line.append('\t');
274
 
275
            SQLRowAccessor rowValsSource = null;
276
            SQLRowAccessor rowMvt = rowLine.getForeign("ID_MOUVEMENT");
277
            if (rowMvt.getString("SOURCE").equals("AVOIR_CLIENT")) {
278
                rowValsSource = this.mapAvoir.get(rowMvt.getInt("IDSOURCE"));
279
            } else if (rowMvt.getString("SOURCE").equals("SAISIE_VENTE_FACTURE")) {
280
                rowValsSource = this.mapFacture.get(rowMvt.getInt("IDSOURCE"));
281
            }
282
 
283
            // Numero Piece
284
            if (rowValsSource != null) {
285
                line.append(StringUtils.toAsciiString(rowValsSource.getString("NUMERO")));
286
            }
287
            line.append('\t');
288
 
289
            // Référence
290
            // if (rowValsSource != null) {
291
            // line.append(StringUtils.toAsciiString(rowValsSource.getString("NOM")));
292
            // }
293
            line.append('\t');
294
 
295
            // N° Compte
296
            final String cpt = rowLine.getForeign("ID_COMPTE_PCE").getString("NUMERO").trim();
297
            line.append(getFormattedCompte(cpt));
298
            line.append('\t');
299
 
300
            if (containsCodeClient) {
301
                // Code Client
302
                String codeClient = "";
303
                if (cpt.startsWith("41")) {
304
                    codeClient = rowLine.getString("CODE_CLIENT");
305
                }
306
                line.append(StringUtils.toAsciiString(codeClient));
307
                line.append('\t');
308
            }
309
 
310
            // Libellé
311
            line.append(StringUtils.toAsciiString(rowLine.getString("NOM").trim()));
312
            line.append('\t');
313
 
314
            if (rowValsSource != null && cpt.startsWith("41")) {
315
                final SQLRowAccessor foreignMdr = rowValsSource.getForeign("ID_MODE_REGLEMENT");
316
                Date ech = ModeDeReglementSQLElement.calculDate(foreignMdr, rowValsSource.getDate("DATE").getTime());
317
                line.append(dateFormat.format(ech));
318
                line.append('\t');
319
                line.append(foreignMdr.getForeign("ID_TYPE_REGLEMENT").getString("NOM"));
320
                line.append('\t');
321
            } else {
322
                line.append('\t');
323
                line.append('\t');
324
            }
325
 
326
            // Debit
327
            final Long debit = rowLine.getLong("DEBIT");
328
            line.append(GestionDevise.currencyToString(debit.longValue(), false));
329
            line.append('\t');
330
            // Credit
331
            final Long credit = rowLine.getLong("CREDIT");
332
            line.append(GestionDevise.currencyToString(credit.longValue(), false));
333
            line.append('\t');
334
 
335
            if (rowValsSource != null && rowValsSource.contains("ID_ABONNEMENT") && rowValsSource.getTable().getName().equals("ID_SAISIE_VENTE_FACTURE") && cpt.startsWith("41")) {
336
                SQLRowAccessor rowValsAbo = rowValsSource.getForeign("ID_ABONNEMENT");
337
                if (rowValsAbo != null && !rowValsAbo.isUndefined()) {
338
                    final Calendar calDeb = rowValsAbo.getDate("DATE_DEBUT_FACTURE");
339
                    if (calDeb != null) {
340
                        line.append(dateFormat.format(calDeb.getTime()));
341
                    }
342
                    line.append('\t');
343
                    final Calendar calFin = rowValsAbo.getDate("DATE_FIN_FACTURE");
344
                    if (calFin != null) {
345
                        line.append(dateFormat.format(calFin.getTime()));
346
                    }
347
                    line.append('\t');
348
                }
349
            } else {
350
                line.append('\t');
351
                line.append('\t');
352
            }
353
 
354
            line.append('\r');
355
            line.append('\n');
356
            bufOut.write(line.toString().getBytes(StringUtils.Cp1252));
357
        }
358
    }
359
}