OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 93 | Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
83 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.SQLRowAccessor;
24
import org.openconcerto.sql.model.SQLRowValues;
25
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
26
import org.openconcerto.sql.model.SQLSelect;
27
import org.openconcerto.sql.model.SQLTable;
28
import org.openconcerto.sql.model.Where;
29
import org.openconcerto.sql.users.UserManager;
30
import org.openconcerto.utils.GestionDevise;
31
import org.openconcerto.utils.cc.ITransformer;
32
 
33
import java.text.DateFormat;
34
import java.util.ArrayList;
35
import java.util.Calendar;
36
import java.util.Date;
37
import java.util.HashMap;
38
import java.util.List;
39
import java.util.Map;
40
 
41
import org.apache.commons.dbutils.handlers.ArrayListHandler;
42
 
43
public class VentilationAnalytiqueSheetXML extends AbstractListeSheetXml {
44
 
45
    private static int debutFill, endFill;
46
    protected final static SQLTable tableAssoc = base.getTable("ASSOCIATION_ANALYTIQUE");
47
    protected final static SQLTable tablePoste = base.getTable("POSTE_ANALYTIQUE");
48
    private final static SQLTable tableEcriture = base.getTable("ECRITURE");
49
    private final static SQLTable tableJournal = base.getTable("JOURNAL");
50
    private final static SQLTable tableMvt = base.getTable("MOUVEMENT");
51
    private final static SQLTable tableCompte = base.getTable("COMPTE_PCE");
52
 
53
    private final static DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM);
54
    private final static DateFormat dateFormatEcr = DateFormat.getDateInstance(DateFormat.SHORT);
55
    private SQLRow rowSociete = ((ComptaPropsConfiguration) Configuration.getInstance()).getRowSociete();
56
 
57
    private Date dateDu, dateAu;
58
    private String compteDeb, compteEnd;
59
    private SQLRow rowPoste;
60
 
61
    public static String TEMPLATE_ID = "VentilationAnalytique";
62
    public static String TEMPLATE_PROPERTY_NAME = "LocationJournaux";
63
 
64
    @Override
65
    public String getDefaultTemplateId() {
66
        return TEMPLATE_ID;
67
    }
68
 
69
    Date date;
70
 
71
    @Override
72
    public String getName() {
73
        if (this.date == null) {
74
            this.date = new Date();
75
        }
76
        return "VentilationAnalytique" + date.getTime();
77
    }
78
 
79
    @Override
80
    protected String getStoragePathP() {
81
        return "Ventilation Analytique";
82
    }
83
 
84
    public VentilationAnalytiqueSheetXML(Date du, Date au, SQLRow rowPoste) {
85
        super();
86
        Calendar cal = Calendar.getInstance();
87
        cal.setTime(au);
88
        this.printer = PrinterNXProps.getInstance().getStringProperty("JournauxPrinter");
89
        this.dateAu = au;
90
        this.dateDu = du;
91
        this.rowPoste = rowPoste;
92
    }
93
 
94
    private String toDay = dateFormat.format(new Date());
95
    private int size;
96
 
97
    private void makeSousTotal(Map<String, Object> line, Map<Integer, String> style, int pos, long debit, long credit) {
98
        style.put(pos, "Titre 1");
99
 
100
        line.put("DATE", "");
101
        line.put("JOURNAL", "");
102
        line.put("MOUVEMENT", "");
103
        line.put("LIBELLE", "Sous total");
104
        line.put("DEBIT", Double.valueOf(GestionDevise.currencyToString(debit, false)));
105
        line.put("CREDIT", Double.valueOf(GestionDevise.currencyToString(credit, false)));
106
        line.put("SOLDE", Double.valueOf(GestionDevise.currencyToString(debit - credit, false)));
107
    }
108
 
109
    protected void createListeValues() {
110
        final SQLRowValues vals = new SQLRowValues(tableEcriture);
111
 
112
        vals.put("ID_JOURNAL", null);
113
        vals.put("ID_COMPTE_PCE", null);
114
        vals.put("COMPTE_NUMERO", null);
115
        vals.put("COMPTE_NOM", null);
116
        vals.put("JOURNAL_CODE", null);
117
        vals.put("JOURNAL_NOM", null);
118
        vals.putRowValues("ID_MOUVEMENT").put("NUMERO", null);
119
 
120
        vals.put("CREDIT", null);
121
        vals.put("DEBIT", null);
122
        vals.put("DATE", null);
123
        vals.put("NOM", null);
124
 
125
        final SQLRowValues valsAnalytique = new SQLRowValues(tableAssoc);
126
        valsAnalytique.put("ID_ECRITURE", vals);
127
        valsAnalytique.putRowValues("ID_POSTE_ANALYTIQUE").put("NOM", null);
128
        valsAnalytique.put("POURCENT", null);
129
        valsAnalytique.put("MONTANT", null);
130
        valsAnalytique.put("ID_ECRITURE", vals);
131
 
132
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(valsAnalytique);
133
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
134
            @Override
135
            public SQLSelect transformChecked(SQLSelect sel) {
136
 
137
                Where w = (new Where(sel.getJoinFromField(tableAssoc.getField("ID_ECRITURE")).getJoinedTable().getField("DATE"), VentilationAnalytiqueSheetXML.this.dateDu,
138
                        VentilationAnalytiqueSheetXML.this.dateAu));
139
 
140
                // if (rowPoste != null && !rowPoste.isUndefined()) {
141
 
142
                w = w.and(new Where(tableAssoc.getField("ID_POSTE_ANALYTIQUE"), "=", rowPoste.getID()));
143
                // }
144
 
145
                sel.setWhere(w);
146
                sel.addFieldOrder(tableAssoc.getField("ID_POSTE_ANALYTIQUE"));
147
                sel.addFieldOrder(sel.getJoinFromField(tableAssoc.getField("ID_ECRITURE")).getJoinedTable().getField("COMPTE_NUMERO"));
148
 
149
                return sel;
150
            }
151
        });
152
 
153
        List<SQLRowValues> list = fetcher.fetch();
154
        size = list.size();
155
 
156
        long totalDebit, totalCredit, sousTotalDebit, sousTotalCredit, totalCreditAntC, totalDebitAntC, totalCreditAntF, totalDebitAntF;
157
 
158
        totalDebit = 0;
159
        totalCredit = 0;
160
        sousTotalCredit = 0;
161
        sousTotalDebit = 0;
162
        totalCreditAntC = 0;
163
        totalDebitAntC = 0;
164
        totalCreditAntF = 0;
165
        totalDebitAntF = 0;
166
        SQLRowAccessor rowFirstEcr = null;
167
        int idCptFirstEcr = 1;
168
 
169
        boolean setTitle = true;
170
        boolean setLine = false;
171
        boolean setCumuls = true;
172
        boolean firstEcrCentC = true;
173
        boolean firstEcrCentF = true;
174
        String numCptFirstEcr = "";
175
 
176
        final String titre3 = "Titre 3";
177
        // int j = 0;
178
 
179
        // Valeur de la liste
180
        // listAllSheetValues ;
181
 
182
        // Style des lignes
183
        // styleAllSheetValues;
184
 
185
        // Valeur à l'extérieur de la liste
186
        // mapAllSheetValues
187
 
188
        List<Map<String, Object>> tableauVals = new ArrayList<Map<String, Object>>();
189
        this.listAllSheetValues.put(0, tableauVals);
190
 
191
        Map<Integer, String> style = new HashMap<Integer, String>();
192
        this.styleAllSheetValues.put(0, style);
193
 
194
        // Affiche le nom du compte
195
        setTitle = true;
196
        // ligne vide avant de mettre le setTitle
197
        setLine = false;
198
        for (int i = 0; i < size;) {
199
            // System.err.println(i);
200
            // // System.err.println("START NEW PAGE; POS : " + posLine);
201
            //
202
            // /***************************************************************************************
203
            // * ENTETE
204
            // **************************************************************************************/
205
            // // makeEntete(posLine);
206
            // // posLine += debutFill - 1;
207
 
208
            /***************************************************************************************
209
             * CONTENU
210
             **************************************************************************************/
211
            final Double doubleZero = Double.valueOf("0");
212
 
213
            final SQLRowValues sqlRowValuesAnalytique = list.get(i);
214
            SQLRowAccessor rowEcr = sqlRowValuesAnalytique.getForeign("ID_ECRITURE");
215
 
216
            int idCpt = rowEcr.getInt("ID_COMPTE_PCE");
217
            String nomCpt = rowEcr.getString("COMPTE_NOM");
218
            String numCpt = rowEcr.getString("COMPTE_NUMERO");
219
 
220
            Map<String, Object> ooLine = new HashMap<String, Object>();
221
            tableauVals.add(ooLine);
222
 
223
            // Titre
224
            if (setTitle) {
225
                if (!setLine) {
226
                    style.put(tableauVals.size() - 1, "Titre 1");
227
 
228
                    ooLine.put("DATE", numCpt);
229
                    ooLine.put("CODE_JOURNAL", nomCpt);
230
                    ooLine.put("JOURNAL", "");
231
                    ooLine.put("NUMERO_COMPTE", "");
232
                    ooLine.put("LIBELLE_COMPTE", "");
233
                    ooLine.put("NUMERO_MOUVEMENT", "");
234
                    ooLine.put("LIBELLE", "");
235
                    ooLine.put("DEBIT", "");
236
                    ooLine.put("CREDIT", "");
237
                    ooLine.put("SOLDE", "");
238
                    setTitle = false;
239
                    setLine = true;
240
 
241
                    if (rowFirstEcr == null) {
242
                        rowFirstEcr = rowEcr;
243
                        idCptFirstEcr = rowEcr.getInt("ID_COMPTE_PCE");
244
                        numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
245
                    }
246
 
247
                } else {
248
                    style.put(tableauVals.size() - 1, "Normal");
249
                    setLine = false;
250
                }
251
            } else {
252
 
253
                // si on change de compte alors on applique le style Titre 1
254
                if (rowFirstEcr != null && idCptFirstEcr != idCpt) {
255
 
256
                    rowFirstEcr = rowEcr;
257
                    idCptFirstEcr = rowFirstEcr.getInt("ID_COMPTE_PCE");
258
                    numCptFirstEcr = rowEcr.getString("COMPTE_NUMERO");
259
                    makeSousTotal(ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
260
 
261
                    sousTotalCredit = 0;
262
                    sousTotalDebit = 0;
263
                    setTitle = true;
264
                    setCumuls = true;
265
                } else {
266
                    long cred = rowEcr.getLong("CREDIT");
267
                    long deb = rowEcr.getLong("DEBIT");
268
                    // Centralisation fournisseur
269
 
270
                    ooLine.put("DATE", dateFormatEcr.format((Date) rowEcr.getObject("DATE")));
271
 
272
                    ooLine.put("CODE_JOURNAL", rowEcr.getString("JOURNAL_CODE"));
273
                    ooLine.put("JOURNAL", rowEcr.getString("JOURNAL_NOM"));
274
                    ooLine.put("NUMERO_MOUVEMENT", rowEcr.getForeign("ID_MOUVEMENT").getObject("NUMERO"));
275
                    ooLine.put("LIBELLE", rowEcr.getObject("NOM"));
276
                    ooLine.put("CODE_LETTRAGE", rowEcr.getObject("LETTRAGE"));
277
                    ooLine.put("CODE_POINTAGE", rowEcr.getObject("POINTEE"));
278
                    ooLine.put("DATE_LETTRAGE", rowEcr.getObject("DATE_LETTRAGE"));
279
                    ooLine.put("DATE_POINTAGE", rowEcr.getObject("DATE_LETTRAGE"));
280
 
281
                    totalCredit += cred;
282
                    totalDebit += deb;
283
 
284
                    sousTotalCredit += cred;
285
                    sousTotalDebit += deb;
286
                    long solde = sousTotalDebit - sousTotalCredit;
287
 
288
                    ooLine.put("DEBIT", (deb == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(deb, false)));
289
                    ooLine.put("CREDIT", (cred == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(cred, false)));
290
                    ooLine.put("SOLDE", (solde == 0) ? doubleZero : Double.valueOf(GestionDevise.currencyToString(solde, false)));
291
 
292
                    style.put(tableauVals.size() - 1, "Normal");
293
                    i++;
294
                }
295
 
296
            }
297
 
298
        }
299
 
300
        Map<String, Object> sheetVals = new HashMap<String, Object>();
301
        this.mapAllSheetValues.put(0, sheetVals);
302
 
303
        if (size > 0) {
304
            Map<String, Object> ooLine = new HashMap<String, Object>();
305
            tableauVals.add(ooLine);
306
            makeSousTotal(ooLine, style, tableauVals.size() - 1, sousTotalDebit, sousTotalCredit);
307
 
308
            sheetVals.put("TOTAL_DEBIT", (totalDebit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit, false)));
309
            sheetVals.put("TOTAL_CREDIT", (totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalCredit, false)));
310
            sheetVals.put("TOTAL_SOLDE", (totalDebit - totalCredit == 0) ? 0 : new Double(GestionDevise.currencyToString(totalDebit - totalCredit, false)));
311
        }
312
 
313
        sheetVals.put("TITRE_1", "Ventilation analytique " + this.rowSociete.getString("TYPE") + " " + this.rowSociete.getString("NOM"));
314
        sheetVals.put("DATE_EDITION", new Date());
315
        sheetVals.put("TITRE_2", "Poste analytique : " + this.rowPoste.getString("NOM") + ". Période du " + dateFormatEcr.format(this.dateDu) + " au " + dateFormatEcr.format(this.dateAu) + ".");
316
 
317
    }
318
 
319
    @Override
320
    public String getTemplateId() {
321
        return TEMPLATE_ID;
322
    }
323
 
324
    public int getSize() {
325
        return size;
326
    }
327
}