OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

Rev Author Line No. Line
61 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.core.finance.accounting.report;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
18
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
19
import org.openconcerto.erp.preferences.PrinterNXProps;
20
import org.openconcerto.erp.rights.ComptaUserRight;
21
import org.openconcerto.sql.Configuration;
22
import org.openconcerto.sql.model.SQLRow;
23
import org.openconcerto.sql.model.SQLRowValues;
24
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
25
import org.openconcerto.sql.model.SQLSelect;
26
import org.openconcerto.sql.model.SQLTable;
27
import org.openconcerto.sql.model.Where;
144 ilm 28
import org.openconcerto.sql.users.rights.UserRightsManager;
61 ilm 29
import org.openconcerto.utils.GestionDevise;
30
import org.openconcerto.utils.cc.ITransformer;
31
 
32
import java.text.DateFormat;
33
import java.util.ArrayList;
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.commons.dbutils.handlers.ArrayListHandler;
41
 
42
public class GrandLivreSheetXML extends AbstractListeSheetXml {
43
 
44
    private static int debutFill, endFill;
132 ilm 45
 
61 ilm 46
    private final static SQLTable tableEcriture = base.getTable("ECRITURE");
47
    private final static SQLTable tableJournal = base.getTable("JOURNAL");
48
    private final static SQLTable tableMvt = base.getTable("MOUVEMENT");
49
    private final static SQLTable tableCompte = base.getTable("COMPTE_PCE");
50
 
144 ilm 51
    private final DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
52
    private final DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);
61 ilm 53
    private SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
54
 
55
    private Date dateDu, dateAu;
56
    private String compteDeb, compteEnd;
57
    private int lettrage;
58
    private boolean cumul = false;
59
    private boolean excludeCompteSolde = true;
60
    private boolean centralClient = false;
61
    private boolean centralFourn = false;
62
    int idJrnlExclude = -1;
63
 
64
    public static String TEMPLATE_ID = "GrandLivre";
65
    public static String TEMPLATE_PROPERTY_NAME = "LocationGrandLivre";
66
 
67
    @Override
68
    public String getDefaultTemplateId() {
69
        return TEMPLATE_ID;
70
    }
71
 
72
    Date date;
73
 
74
    @Override
75
    public String getName() {
76
        if (this.date == null) {
77
            this.date = new Date();
78
        }
79
        return "GrandLivre" + date.getTime();
80
    }
81
 
67 ilm 82
    @Override
83
    protected String getStoragePathP() {
84
        return "Grand Livre";
85
    }
86
 
132 ilm 87
    public GrandLivreSheetXML(Date du, Date au, String compteDep, String compteEnd, int lettrage, boolean cumul, boolean excludeCptSolde, boolean centralClient, boolean centralFourn,
88
            int idJrnlExclude) {
61 ilm 89
        super();
90
        Calendar cal = Calendar.getInstance();
91
        cal.setTime(au);
92
        this.idJrnlExclude = idJrnlExclude;
93
        this.printer = PrinterNXProps.getInstance().getStringProperty("GrandLivrePrinter");
94
        this.dateAu = au;
95
        this.dateDu = du;
96
        this.compteDeb = compteDep.trim();
97
        this.compteEnd = compteEnd.trim();
98
        this.lettrage = lettrage;
99
        this.cumul = cumul;
100
        this.excludeCompteSolde = excludeCptSolde;
101
        this.centralClient = centralClient;
102
        this.centralFourn = centralFourn;
149 ilm 103
        Map<String, Object> map = new HashMap<>();
104
        recapSousTotaux.add(map);
105
        map.put("DATE", "Récapitulatif");
61 ilm 106
        // createMap();
107
    }
108
 
109
    private String toDay = dateFormat.format(new Date());
110
    private int size;
111
 
112
    // private void makeEntete(int rowDeb) {
113
    //
114
    // this.mCell.put("A" + rowDeb, this.rowSociete.getObject("NOM"));
115
    // this.mCell.put("G" + rowDeb, "Edition du " + this.toDay);
116
    // // this.mCell.put("D" + (rowDeb + 2), "Grand livre");
117
    // // System.err.println("MAKE ENTETE");
118
    // }
119
    //
120
    // private void makePiedPage(int row, String comptes) {
121
    // this.mCell.put("A" + row, "Compte : " + comptes);
122
    // this.mCell.put("E" + row, "Période du " + dateFormatEcr.format(this.dateDu) + " au " +
123
    // dateFormatEcr.format(this.dateAu));
124
    // }
149 ilm 125
    private List<Map<String, Object>> recapSousTotaux = new ArrayList<>();
61 ilm 126
 
149 ilm 127
    private void makeSousTotal(String numCpt, String nomCpt, Map<String, Object> line, Map<Integer, String> style, int pos, long debit, long credit) {
61 ilm 128
        style.put(pos, "Titre 1");
129
 
130
        line.put("DATE", "");
131
        line.put("JOURNAL", "");
132
        line.put("MOUVEMENT", "");
133
        line.put("LIBELLE", "Sous total");
134
        line.put("DEBIT", Double.valueOf(GestionDevise.currencyToString(debit, false)));
135
        line.put("CREDIT", Double.valueOf(GestionDevise.currencyToString(credit, false)));
136
        line.put("SOLDE", Double.valueOf(GestionDevise.currencyToString(debit - credit, false)));
149 ilm 137
 
138
        Map<String, Object> lineRecap = new HashMap<>();
139
        lineRecap.putAll(line);
140
        lineRecap.put("DATE", numCpt);
141
        lineRecap.put("JOURNAL", nomCpt);
142
        lineRecap.put("LIBELLE", "");
143
        recapSousTotaux.add(lineRecap);
144
 
61 ilm 145
    }
146
 
147
    protected void createListeValues() {
148
        Date d = new Date();
149
 
150
        final SQLRowValues vals = new SQLRowValues(tableEcriture);
151
        vals.put("ID_COMPTE_PCE", null);
152
        vals.put("COMPTE_NUMERO", null);
153
        vals.put("COMPTE_NOM", null);
93 ilm 154
        if (tableEcriture.contains("NOM_PIECE")) {
155
            vals.put("NOM_PIECE", null);
156
        }
61 ilm 157
        vals.put("ID_JOURNAL", null);
158
        vals.put("JOURNAL_CODE", null);
93 ilm 159
        vals.putRowValues("ID_MOUVEMENT").put("NUMERO", null).putRowValues("ID_PIECE").put("NOM", null);
61 ilm 160
        vals.put("CREDIT", null);
161
        vals.put("DEBIT", null);
162
        vals.put("DATE", null);
163
        vals.put("NOM", null);
83 ilm 164
        vals.put("LETTRAGE", null);
165
        vals.put("POINTEE", null);
166
        vals.put("DATE_LETTRAGE", null);
167
        vals.put("DATE_POINTEE", null);
61 ilm 168
 
169
        final List<Integer> lCompteSolde;
170
        if (GrandLivreSheetXML.this.excludeCompteSolde) {
171
            lCompteSolde = getListeCompteSolde();
172
        } else {
173
            lCompteSolde = null;
174
        }
175
        Map<Integer, Long> mapCumul = getCumulsAnterieur(GrandLivreSheetXML.this.dateDu, lCompteSolde);
176
 
177
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
178
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
179
            @Override
180
            public SQLSelect transformChecked(SQLSelect sel) {
181
 
174 ilm 182
                Where w = getWhere(lCompteSolde);
61 ilm 183
 
184
                sel.setWhere(w);
65 ilm 185
                sel.addFieldOrder(tableEcriture.getField("COMPTE_NUMERO"));
186
                sel.addFieldOrder(tableEcriture.getField("DATE"));
187
                sel.addFieldOrder(tableMvt.getField("NUMERO"));
61 ilm 188
                System.err.println(sel.asString());
189
                return sel;
190
            }
191
        });
192
 
193
        List<SQLRowValues> list = fetcher.fetch();
194
        size = list.size();
195
        System.err.println("START CREATE Grand livre, NB ecritures  " + size);
196
 
197
        long totalDebit, totalCredit, sousTotalDebit, sousTotalCredit, totalCreditAntC, totalDebitAntC, totalCreditAntF, totalDebitAntF;
198
 
199
        totalDebit = 0;
200
        totalCredit = 0;
201
        sousTotalCredit = 0;
202
        sousTotalDebit = 0;
203
        totalCreditAntC = 0;
204
        totalDebitAntC = 0;
205
        totalCreditAntF = 0;
206
        totalDebitAntF = 0;
207
        SQLRowValues rowFirstEcr = null;
208
        int idCptFirstEcr = 1;
209
 
210
        boolean setTitle = true;
211
        boolean setLine = false;
212
        boolean setCumuls = true;
213
        boolean firstEcrCentC = true;
214
        boolean firstEcrCentF = true;
215
        String numCptFirstEcr = "411";
216
        String numCptClient = "411";
217
        String nomCptClient = "Clients";
218
        String numCptFourn = "401";
219
        String nomCptFourn = "Fournisseurs";
220
        int idCptClient = ComptePCESQLElement.getId(numCptClient, nomCptClient);
221
        int idCptFourn = ComptePCESQLElement.getId(numCptFourn, nomCptFourn);
222
 
223
        final String titre3 = "Titre 3";
224
        final String cumulAntString = "Cumuls antérieurs";
225
        // int j = 0;
226
 
227
        // Valeur de la liste
228
        // listAllSheetValues ;
229
 
230
        // Style des lignes
231
        // styleAllSheetValues;
232
 
233
        // Valeur à l'extérieur de la liste
234
        // mapAllSheetValues
235
 
236
        List<Map<String, Object>> tableauVals = new ArrayList<Map<String, Object>>();
237
        this.listAllSheetValues.put(0, tableauVals);
238
 
239
        Map<Integer, String> style = new HashMap<Integer, String>();
240
        this.styleAllSheetValues.put(0, style);
241
 
242
        // Affiche le nom du compte
243
        setTitle = true;
244
        // ligne vide avant de mettre le setTitle
245
        setLine = false;
149 ilm 246
        String nomCpt = "";
247
        String numCpt = "";
61 ilm 248
        for (int i = 0; i < size;) {
249
            // System.err.println(i);
250
            // // System.err.println("START NEW PAGE; POS : " + posLine);
251
            //
252
            // /***************************************************************************************
253
            // * ENTETE
254
            // **************************************************************************************/
255
            // // makeEntete(posLine);
256
            // // posLine += debutFill - 1;
257
 
258
            /***************************************************************************************
259
             * CONTENU
260
             **************************************************************************************/
261
            final Double doubleZero = Double.valueOf("0");
262
 
263
            SQLRowValues rowEcr = list.get(i);
264
 
265
            int idCpt = rowEcr.getInt("ID_COMPTE_PCE");
149 ilm 266
            nomCpt = rowEcr.getString("COMPTE_NOM");
267
            numCpt = rowEcr.getString("COMPTE_NUMERO");
61 ilm 268
 
269
            Map<String, Object> ooLine = new HashMap<String, Object>();
270
            tableauVals.add(ooLine);
271
            // Cumuls antérieurs
272
            if (setCumuls && this.cumul && !setTitle) {
273
 
274
                style.put(tableauVals.size(), titre3);
275
                ooLine.put("DATE", "");
276
                ooLine.put("JOURNAL", "");
277
                ooLine.put("MOUVEMENT", "");
278
 
279
                ooLine.put("LIBELLE", cumulAntString);
280
                Long longSolde = mapCumul.get(idCpt);
281
 
282
                if (longSolde == null) {
283
                    longSolde = Long.valueOf(0);
284
                }
285
                long debitCumulAnt = 0;
286
                long creditCumulAnt = 0;
287
 
288
                if (longSolde > 0) {
289
                    debitCumulAnt = longSolde;
290
                } else {
291
                    creditCumulAnt = -longSolde;
292
                }
293
                ooLine.put("DEBIT", (debitCumulAnt == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(debitCumulAnt, false)));
294
                ooLine.put("CREDIT", (creditCumulAnt == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(creditCumulAnt, false)));
295
                ooLine.put("SOLDE", (longSolde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(longSolde, false)));
296
 
297
                totalCredit += creditCumulAnt;
298
                totalDebit += debitCumulAnt;
299
 
300
                sousTotalCredit += creditCumulAnt;
301
                sousTotalDebit += debitCumulAnt;
302
                setCumuls = false;
303
            } else {
304
                // Titre
305
                if (setTitle) {
306
                    if (!setLine) {
307
                        style.put(tableauVals.size() - 1, "Titre 1");
308
 
309
                        // Si on centralise les comptes clients ou fournisseurs on affiche le
310
                        // compte 401 ou 411
311
                        if (this.centralClient && nomCpt.startsWith("411")) {
312
                            nomCpt = nomCptClient;
313
                            numCpt = numCptClient;
314
                            idCpt = idCptClient;
315
                        }
316
                        if (this.centralFourn && nomCpt.startsWith("401")) {
317
                            nomCpt = nomCptFourn;
318
                            numCpt = numCptFourn;
319
                            idCpt = idCptFourn;
320
                        }
321
                        ooLine.put("DATE", numCpt);
322
                        ooLine.put("JOURNAL", nomCpt);
323
                        ooLine.put("MOUVEMENT", "");
324
                        ooLine.put("LIBELLE", "");
325
                        ooLine.put("DEBIT", "");
326
                        ooLine.put("CREDIT", "");
327
                        ooLine.put("SOLDE", "");
328
                        setTitle = false;
329
                        setLine = true;
330
 
331
                        if (rowFirstEcr == null) {
332
                            rowFirstEcr = rowEcr;
333
                            idCptFirstEcr = rowEcr.getInt("ID_COMPTE_PCE");
334
                            numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
335
                        }
336
 
337
                    } else {
338
                        style.put(tableauVals.size() - 1, "Normal");
339
                        setLine = false;
340
                    }
341
                } else {
342
 
343
                    // si on change de compte alors on applique le style Titre 1
151 ilm 344
                    if (rowFirstEcr != null && !numCptFirstEcr.equals(numCpt) && (!this.centralFourn || (!(numCptFirstEcr.startsWith("401") && numCpt.startsWith("401"))))
61 ilm 345
                            && (!this.centralClient || (!(numCptFirstEcr.startsWith("411") && numCpt.startsWith("411"))))) {
346
 
151 ilm 347
                        makeSousTotal(rowFirstEcr.getString("COMPTE_NUMERO"), rowFirstEcr.getString("COMPTE_NOM"), ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
348
 
61 ilm 349
                        rowFirstEcr = rowEcr;
350
                        idCptFirstEcr = rowFirstEcr.getInt("ID_COMPTE_PCE");
351
                        numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
352
 
353
                        sousTotalCredit = 0;
354
                        sousTotalDebit = 0;
355
                        setTitle = true;
356
                        setCumuls = true;
357
                    } else {
358
                        long cred = rowEcr.getLong("CREDIT");
359
                        long deb = rowEcr.getLong("DEBIT");
360
                        // Centralisation fournisseur
361
                        if (this.centralFourn && numCpt.startsWith("401")) {
362
                            i++;
363
 
364
                            if (firstEcrCentF) {
365
                                // posLine++;
366
                                ooLine.put("LIBELLE", "Centralisation des comptes fournisseurs");
367
                                style.put(tableauVals.size() - 1, "Normal");
368
                                firstEcrCentF = false;
369
                            }
370
 
371
                            totalCreditAntF += cred;
372
                            totalDebitAntF += deb;
373
                            sousTotalCredit += cred;
374
                            sousTotalDebit += deb;
375
                            long solde = totalDebitAntF - totalCreditAntF;
376
                            ooLine.put("DEBIT", (totalDebitAntF == 0) ? doubleZero : new Double(GestionDevise.currencyToString(totalDebitAntF, false)));
377
                            ooLine.put("CREDIT", (totalCreditAntF == 0) ? doubleZero : new Double(GestionDevise.currencyToString(totalCreditAntF, false)));
378
                            ooLine.put("SOLDE", (solde == 0) ? doubleZero : new Double(GestionDevise.currencyToString(solde, false)));
379
 
380
                            continue;
381
                        }
382
                        // Centralisation client
383
                        if (this.centralClient && numCpt.startsWith("411")) {
384
                            i++;
385
                            if (firstEcrCentC) {
386
                                // posLine++;
387
                                ooLine.put("LIBELLE", "Centralisation des comptes clients");
388
                                style.put(tableauVals.size() - 1, "Normal");
389
                                firstEcrCentC = false;
390
                            }
391
 
392
                            totalCreditAntC += cred;
393
                            totalDebitAntC += deb;
394
                            sousTotalCredit += cred;
395
                            sousTotalDebit += deb;
396
                            long solde = totalDebitAntC - totalCreditAntC;
397
                            ooLine.put("DEBIT", (totalDebitAntC == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(totalDebitAntC, false)));
398
                            ooLine.put("CREDIT", (totalCreditAntC == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(totalCreditAntC, false)));
399
                            ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
400
 
401
                            continue;
402
                        }
403
 
404
                        ooLine.put("DATE", dateFormatEcr.format((Date) rowEcr.getObject("DATE")));
405
 
406
                        ooLine.put("JOURNAL", rowEcr.getString("JOURNAL_CODE"));
407
                        ooLine.put("MOUVEMENT", rowEcr.getForeign("ID_MOUVEMENT").getObject("NUMERO"));
93 ilm 408
                        ooLine.put("PIECE", rowEcr.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getObject("NOM"));
61 ilm 409
                        ooLine.put("LIBELLE", rowEcr.getObject("NOM"));
83 ilm 410
                        ooLine.put("CODE_LETTRAGE", rowEcr.getObject("LETTRAGE"));
411
                        ooLine.put("CODE_POINTAGE", rowEcr.getObject("POINTEE"));
412
                        ooLine.put("DATE_LETTRAGE", rowEcr.getObject("DATE_LETTRAGE"));
413
                        ooLine.put("DATE_POINTAGE", rowEcr.getObject("DATE_LETTRAGE"));
93 ilm 414
                        if (tableEcriture.contains("NOM_PIECE")) {
415
                            ooLine.put("NOM_PIECE", rowEcr.getObject("NOM_PIECE"));
416
                        }
61 ilm 417
 
418
                        totalCredit += cred;
419
                        totalDebit += deb;
420
 
421
                        sousTotalCredit += cred;
422
                        sousTotalDebit += deb;
423
                        long solde = sousTotalDebit - sousTotalCredit;
424
 
425
                        ooLine.put("DEBIT", (deb == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(deb, false)));
426
                        ooLine.put("CREDIT", (cred == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(cred, false)));
427
                        ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
428
 
429
                        style.put(tableauVals.size() - 1, "Normal");
430
                        i++;
431
                    }
432
 
433
                }
434
 
435
            }
436
 
437
            // if (i >= size && j < endFill - debutFill + 1) {
438
            // makeSousTotal(posLine, sousTotalDebit, sousTotalCredit);
439
            // }
440
            //
441
            // posLine = firstLine + endFill;
442
            /*
443
             * if (this.mapStyleRow.get(new Integer(posLine - 1)) != null) {
444
             * this.mapStyleRow.put(new Integer(posLine - 1), "Titre 2"); }
445
             */
446
 
447
            // Total
448
            // this.mCell.put("E" + posLine, (totalDebit == 0) ? doubleZero : new
449
            // Double(GestionDevise.currencyToString(totalDebit, false)));
450
            // this.mCell.put("F" + posLine, (totalCredit == 0) ? doubleZero : new
451
            // Double(GestionDevise.currencyToString(totalCredit, false)));
452
            // this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? doubleZero : new
453
            // Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
454
            //
455
            // posLine += 2;
456
            //
457
            // // bas de page
458
            // makePiedPage(posLine, this.compteDeb + " à " + this.compteEnd);
459
            //
460
            // posLine++;
461
            // firstLine = posLine;
462
            // this.nbPage++;
463
            //
464
            // if (i >= size && j >= (endFill - debutFill + 1)) {
465
            //
466
            // makeEntete(posLine);
467
            // posLine += debutFill - 1;
468
            // makeSousTotal(posLine, sousTotalDebit, sousTotalCredit);
469
            // this.nbPage++;
470
            // }
471
 
472
        }
473
 
474
        // Total
475
        // this.mCell.put("E" + posLine, (totalDebit == 0) ? doubleZero : new
476
        // Double(GestionDevise.currencyToString(totalDebit, false)));
477
        // this.mCell.put("F" + posLine, (totalCredit == 0) ? doubleZero : new
478
        // Double(GestionDevise.currencyToString(totalCredit, false)));
479
        // this.mCell.put("G" + posLine, (totalDebit - totalCredit == 0) ? doubleZero : new
480
        // Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
481
        Map<String, Object> sheetVals = new HashMap<String, Object>();
482
        this.mapAllSheetValues.put(0, sheetVals);
483
 
484
        if (size > 0) {
149 ilm 485
 
61 ilm 486
            Map<String, Object> ooLine = new HashMap<String, Object>();
487
            tableauVals.add(ooLine);
149 ilm 488
            makeSousTotal(numCpt, nomCpt, ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
61 ilm 489
 
490
            sheetVals.put("TOTAL_DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
491
            sheetVals.put("TOTAL_CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
492
            sheetVals.put("TOTAL_SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
149 ilm 493
 
494
            Map<String, Object> emptyLine = new HashMap<String, Object>();
495
            style.put(tableauVals.size(), "Normal");
496
            tableauVals.add(emptyLine);
497
            for (Map<String, Object> recap : recapSousTotaux) {
498
                style.put(tableauVals.size(), "Titre 1");
499
                tableauVals.add(recap);
500
            }
501
            Map<String, Object> lineRecap = new HashMap<>();
502
            lineRecap.put("DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
503
            lineRecap.put("CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
504
            lineRecap.put("SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
505
            lineRecap.put("DATE", "");
506
            lineRecap.put("JOURNAL", "");
507
            lineRecap.put("LIBELLE", "Global");
508
            style.put(tableauVals.size(), "Titre 1");
509
            tableauVals.add(lineRecap);
61 ilm 510
        }
511
 
512
        sheetVals.put("TITRE_1", "GRAND LIVRE " + this.rowSociete.getString("TYPE") + " " + this.rowSociete.getString("NOM"));
513
        sheetVals.put("DATE_EDITION", new Date());
514
        sheetVals.put("TITRE_2", "Compte : " + this.compteDeb + " à " + this.compteEnd + ". Période du " + dateFormatEcr.format(this.dateDu) + " au " + dateFormatEcr.format(this.dateAu) + ".");
515
        // on conserve la page d'origine du model
516
        // if (this.nbPage > 0) {
517
        // this.nbPage--;
518
        // }
519
 
520
        Date end = new Date();
521
        System.err.println("///////// TAKE " + (end.getTime() - d.getTime()) + " millisecondes TO CREATE MAP");
522
    }
523
 
524
    private List<Integer> getListeCompteSolde() {
525
        SQLSelect sel = new SQLSelect(base);
526
 
527
        sel.addSelect(tableCompte.getField("ID"));
528
        sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
529
        sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");
530
 
174 ilm 531
        Where w = getWhere(null);
61 ilm 532
 
533
        sel.setWhere(w);
534
 
535
        String req = sel.asString() + " GROUP BY \"COMPTE_PCE\".\"ID\"";
536
        System.err.println(req);
537
        List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler());
538
        List<Integer> list = new ArrayList<Integer>();
539
        for (Object[] o : l) {
540
            long credit = 0;
541
            if (o[2] != null) {
542
                credit = Long.valueOf(o[2].toString());
543
            }
544
 
545
            long debit = 0;
546
            if (o[1] != null) {
547
                debit = Long.valueOf(o[1].toString());
548
            }
549
 
550
            int id = Integer.valueOf(o[0].toString());
551
            long solde = debit - credit;
552
            if (solde == 0) {
553
                list.add(id);
554
            }
555
        }
556
        return list;
557
    }
558
 
174 ilm 559
    private Where getWhere(final List<Integer> lCompteSolde) {
560
        Where w = (new Where(tableEcriture.getField("DATE"), GrandLivreSheetXML.this.dateDu, GrandLivreSheetXML.this.dateAu));
561
 
562
        if (GrandLivreSheetXML.this.compteDeb.equals(GrandLivreSheetXML.this.compteEnd)) {
563
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", GrandLivreSheetXML.this.compteDeb));
564
        } else {
565
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) GrandLivreSheetXML.this.compteDeb, (Object) GrandLivreSheetXML.this.compteEnd));
566
        }
567
        w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude));
568
        w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getField("ID")));
569
 
570
        if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODELETTREE) {
571
            Object o = null;
572
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o));
573
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", ""));
574
            w = w.and(new Where(tableEcriture.getField("DATE_LETTRAGE"), "<=", GrandLivreSheetXML.this.dateAu));
575
        } else if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODENONLETTREE_PERIODE) {
576
            Object o = null;
577
            Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
578
            Where wSTTC = new Where(tableEcriture.getField("DATE_LETTRAGE"), "<>", o);
579
            wSTTC = wSTTC.and(new Where(tableEcriture.getField("DATE_LETTRAGE"), ">", GrandLivreSheetXML.this.dateAu));
580
 
581
            w2 = w2.or(wSTTC);
582
            w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
583
        } else if (GrandLivreSheetXML.this.lettrage == GrandLivreSheet.MODENONLETTREE_ALL) {
584
            Object o = null;
585
            Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
586
            w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
587
        }
588
 
589
        if (GrandLivreSheetXML.this.excludeCompteSolde) {
590
            System.err.println("Exclude compte");
591
 
592
            if (lCompteSolde != null) {
593
                w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), lCompteSolde).not());
594
            }
595
        }
596
        w = w.and(new Where(tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte%"));
597
 
598
        if (!UserRightsManager.getCurrentUserRights().haveRight(ComptaUserRight.ACCES_NOT_RESCTRICTED_TO_411)) {
599
            // TODO Show Restricted acces in UI
600
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "LIKE", "411%"));
601
        }
602
        return w;
603
    }
604
 
61 ilm 605
    /**
606
     * @param d date limite des cumuls
607
     * @return Map<Integer id compte, Long solde(debit-credit)>
608
     */
609
    private Map<Integer, Long> getCumulsAnterieur(Date d, List<Integer> listCompteSolde) {
610
        SQLSelect sel = new SQLSelect(base);
611
 
612
        sel.addSelect(tableEcriture.getField("ID_COMPTE_PCE"));
613
        sel.addSelect(tableEcriture.getField("DEBIT"), "SUM");
614
        sel.addSelect(tableEcriture.getField("CREDIT"), "SUM");
615
        sel.addSelect(tableEcriture.getField("COMPTE_NUMERO"));
616
        // sel.addSelect(tableEcriture.getField("ID_MOUVEMENT"));
617
        Where w = (new Where(tableEcriture.getField("DATE"), "<", d));
618
        w = w.and(new Where(tableEcriture.getField("ID_MOUVEMENT"), "=", tableMvt.getKey()));
619
 
620
        if (this.compteDeb.equals(this.compteEnd)) {
621
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), "=", this.compteDeb));
622
        } else {
623
            w = w.and(new Where(tableEcriture.getField("COMPTE_NUMERO"), (Object) this.compteDeb, (Object) this.compteEnd));
624
        }
625
 
132 ilm 626
        if (this.lettrage == GrandLivreSheet.MODELETTREE) {
61 ilm 627
            Object o = null;
628
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "<>", o));
629
            w = w.and(new Where(tableEcriture.getField("LETTRAGE"), "!=", ""));
630
        } else {
132 ilm 631
            if (this.lettrage == GrandLivreSheet.MODENONLETTREE_ALL || this.lettrage == GrandLivreSheet.MODENONLETTREE_PERIODE) {
61 ilm 632
                Object o = null;
633
                Where w2 = new Where(tableEcriture.getField("LETTRAGE"), "=", o);
634
                w = w.and(w2.or(new Where(tableEcriture.getField("LETTRAGE"), "=", "")));
635
            }
636
        }
637
 
638
        w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), "=", tableCompte.getField("ID")));
639
        w = w.and(new Where(tableEcriture.getField("ID_JOURNAL"), "!=", idJrnlExclude));
640
        if (listCompteSolde != null) {
641
            w = w.and(new Where(tableEcriture.getField("ID_COMPTE_PCE"), listCompteSolde).not());
642
        }
643
 
644
        sel.setWhere(w);
645
 
646
        String req = sel.asString() + " GROUP BY \"ECRITURE\".\"ID_COMPTE_PCE\", \"ECRITURE\".\"COMPTE_NUMERO\"";
647
        System.err.println(req);
648
        List<Object[]> l = (List) base.getDataSource().execute(req, new ArrayListHandler());
649
        Map<Integer, Long> map = new HashMap<Integer, Long>();
650
 
651
        int idCptFourn = ComptePCESQLElement.getId("401", "Fournisseurs");
652
        int idCptClient = ComptePCESQLElement.getId("411", "Clients");
653
 
654
        for (Object[] o : l) {
655
 
656
            long credit = 0;
657
            if (o[2] != null) {
658
                credit = Long.valueOf(o[2].toString());
659
            }
660
 
661
            long debit = 0;
662
            if (o[1] != null) {
663
                debit = Long.valueOf(o[1].toString());
664
            }
665
 
666
            int id = Integer.valueOf(o[0].toString());
667
            long solde = debit - credit;
668
            map.put(id, solde);
669
            if (o[3] != null) {
670
                String numero = o[3].toString();
671
                if (this.centralFourn && numero.startsWith("401")) {
672
                    Long lS = map.get(idCptFourn);
673
                    if (lS != null) {
674
                        lS += solde;
675
                    } else {
676
                        lS = new Long(solde);
677
                    }
678
                    map.put(idCptFourn, lS);
679
                }
680
                if (this.centralClient && numero.startsWith("411")) {
681
                    Long lS = map.get(idCptClient);
682
                    if (lS != null) {
683
                        lS += solde;
684
                    } else {
685
                        lS = new Long(solde);
686
                    }
687
                    map.put(idCptClient, lS);
688
                }
689
            }
690
        }
691
 
692
        return map;
693
    }
694
 
695
    @Override
696
    public String getTemplateId() {
697
        return TEMPLATE_ID;
698
    }
699
 
700
    public int getSize() {
701
        return size;
702
    }
703
}