OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

Rev Author Line No. Line
18 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.core.finance.payment.element.ModeDeReglementSQLElement;
17
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
18
import org.openconcerto.erp.preferences.PrinterNXProps;
19
import org.openconcerto.sql.Configuration;
20
import org.openconcerto.sql.element.SQLElement;
21
import org.openconcerto.sql.model.SQLRow;
73 ilm 22
import org.openconcerto.sql.model.SQLRowAccessor;
23
import org.openconcerto.sql.model.SQLRowValues;
24
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
18 ilm 25
import org.openconcerto.sql.model.SQLSelect;
73 ilm 26
import org.openconcerto.sql.model.SQLTable;
18 ilm 27
import org.openconcerto.sql.model.Where;
73 ilm 28
import org.openconcerto.utils.cc.ITransformer;
18 ilm 29
 
30
import java.util.ArrayList;
73 ilm 31
import java.util.Arrays;
18 ilm 32
import java.util.Date;
33
import java.util.HashMap;
73 ilm 34
import java.util.HashSet;
35
import java.util.LinkedHashMap;
18 ilm 36
import java.util.List;
37
import java.util.Map;
38
 
39
public class BalanceAgeeListeSheetXML extends AbstractListeSheetXml {
40
 
41
    private Date deb, fin;
25 ilm 42
    public static String TEMPLATE_ID = "Balance agée";
73 ilm 43
    private boolean excludeCloture;
18 ilm 44
 
73 ilm 45
    public BalanceAgeeListeSheetXML(Date deb, Date fin, boolean excludeClotureEcr) {
67 ilm 46
        super();
18 ilm 47
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
48
        this.deb = deb;
49
        this.fin = fin;
73 ilm 50
        this.excludeCloture = excludeClotureEcr;
18 ilm 51
    }
52
 
21 ilm 53
    @Override
25 ilm 54
    public String getDefaultTemplateId() {
21 ilm 55
        return "BalanceAgee";
56
    }
57
 
67 ilm 58
    Date d;
59
 
25 ilm 60
    @Override
61
    public String getName() {
67 ilm 62
        if (d == null) {
63
            d = new Date();
64
        }
65
        return "BalanceAgee" + d.getTime();
18 ilm 66
    }
67
 
67 ilm 68
    @Override
69
    protected String getStoragePathP() {
70
        return "Balance";
71
    }
72
 
18 ilm 73
    protected void createListeValues() {
73 ilm 74
        final SQLElement ecr = Configuration.getInstance().getDirectory().getElement("ECRITURE");
75
        // SQLElement cpt = Configuration.getInstance().getDirectory().getElement("COMPTE_PCE");
76
        final SQLElement fact = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE");
18 ilm 77
 
73 ilm 78
        SQLRowValues rowValsEcr = new SQLRowValues(ecr.getTable());
79
        rowValsEcr.put("COMPTE_NUMERO", null);
80
        rowValsEcr.put("COMPTE_NOM", null);
81
        rowValsEcr.put("DATE_LETTRAGE", null);
82
        rowValsEcr.put("NOM", null);
83
        rowValsEcr.put("DEBIT", null);
84
        rowValsEcr.put("DATE", null);
85
        rowValsEcr.put("CREDIT", null);
86
 
87
        SQLRowValues rowValsMvt = new SQLRowValues(ecr.getTable().getForeignTable("ID_MOUVEMENT"));
88
        rowValsMvt.put("IDSOURCE", null);
89
        rowValsMvt.put("SOURCE", null);
90
        rowValsEcr.put("ID_MOUVEMENT", rowValsMvt);
91
 
92
        // Liste des codes de lettrage hors période
93
        SQLSelect sel = new SQLSelect();
94
        sel.addSelect(ecr.getTable().getField("LETTRAGE"));
95
        Where w = new Where(ecr.getTable().getField("LETTRAGE"), "IS NOT", (Object) null);
96
        w = w.and(new Where(ecr.getTable().getField("LETTRAGE"), "!=", ""));
142 ilm 97
        if (this.fin == null) {
98
            this.fin = new Date();
99
        }
73 ilm 100
 
142 ilm 101
        w = w.and(new Where(ecr.getTable().getField("DATE"), "<=", fin));
73 ilm 102
        sel.setWhere(w);
103
 
104
        final List<String> lettrageList = (List<String>) Configuration.getInstance().getBase().getDataSource().executeCol(sel.asString());
105
 
106
        // Liste des codes de lettrage hors période
107
        SQLSelect sel2 = new SQLSelect();
108
        sel2.addSelect(ecr.getTable().getField("LETTRAGE"));
109
        Where w2 = new Where(ecr.getTable().getField("LETTRAGE"), "IS NOT", (Object) null);
110
        w2 = w2.and(new Where(ecr.getTable().getField("LETTRAGE"), "!=", ""));
111
        if (deb != null) {
112
            w2 = w2.and(new Where(ecr.getTable().getField("DATE"), ">=", deb));
18 ilm 113
        }
142 ilm 114
 
115
        w2 = w2.and(new Where(ecr.getTable().getField("DATE"), "<=", fin));
116
 
73 ilm 117
        sel2.addGroupBy(ecr.getTable().getField("LETTRAGE"));
118
        sel2.setHaving(Where.createRaw("SUM(\"DEBIT\") != SUM(\"CREDIT\")", Arrays.asList(ecr.getTable().getField("DEBIT"), ecr.getTable().getField("CREDIT"))));
119
        sel2.setWhere(w2);
120
        System.err.println(sel2.asString());
121
        lettrageList.addAll((List<String>) Configuration.getInstance().getBase().getDataSource().executeCol(sel2.asString()));
18 ilm 122
 
73 ilm 123
        final HashSet<String> lettrageToExclude = new HashSet<String>();
124
        lettrageToExclude.addAll(lettrageList);
18 ilm 125
 
73 ilm 126
        SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(rowValsEcr);
127
 
128
        // SQLSelect sel = new SQLSelect();
129
        // sel.addSelectStar(ecr.getTable());
130
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
131
 
132
            @Override
133
            public SQLSelect transformChecked(SQLSelect input) {
134
                final SQLTable tableEcriture = ecr.getTable();
135
                Where w = new Where(tableEcriture.getField("COMPTE_NUMERO"), "LIKE", "411%");
136
                if (deb != null) {
137
                    w = w.and(new Where(tableEcriture.getField("DATE"), ">=", deb));
138
                }
142 ilm 139
                w = w.and(new Where(tableEcriture.getField("DATE"), "<=", fin));
140
 
73 ilm 141
                // w = w.and(new Where(cpt.getTable().getField("NOM"), "LIKE", "%RIBEIRO%"));
142
                // w = w.and(new Where(ecr.getTable().getField("ID_COMPTE_PCE"), "=",
143
                // cpt.getTable().getKey()));
144
                // Where whereLettrage = new Where(ecr.getTable().getField("LETTRAGE"), "=",
145
                // "").or(new
146
                // Where(ecr.getTable().getField("LETTRAGE"), "=", (Object) null));
147
                // whereLettrage = whereLettrage.or(new Where(ecr.getTable().getField("LETTRAGE"),
148
                // "!=",
149
                // "").and(new Where(ecr.getTable().getField("LETTRAGE"), "!=", (Object) null)).and(
150
                // new Where(ecr.getTable().getField("DATE_LETTRAGE"), ">=", this.fin)));
151
 
152
                // Where whereLettrage = new Where(tableEcriture.getField("DATE_LETTRAGE"), "IS",
153
                // (Object) null).or(new Where(tableEcriture.getField("DATE_LETTRAGE"), ">", fin));
154
                // w = w.and(whereLettrage);
155
                if (excludeCloture) {
156
                    w = w.and(new Where(tableEcriture.getField("NOM"), "NOT LIKE", "Fermeture du compte %"));
157
                    w = w.and(new Where(ecr.getTable().getField("NOM"), "!=", "A nouveaux"));
158
                }
159
 
160
                Where wLettrage = new Where(tableEcriture.getField("LETTRAGE"), "IS", (Object) null);
161
                wLettrage = wLettrage.or(new Where(tableEcriture.getField("LETTRAGE"), "=", ""));
162
                String aliasEcr = input.getAlias(tableEcriture.getField("LETTRAGE")).getAlias();
163
                // DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
164
                // wLettrage = wLettrage.or(Where.createRaw("(SELECT COUNT(*) FROM " +
165
                // tableEcriture.getSQLName().quote() + " e WHERE e.\"LETTRAGE\"=\"" + aliasEcr +
166
                // "\".\"LETTRAGE\" AND e.\"DATE\"<='"
167
                // + format.format(fin) + "' HAVING SUM(e.\"DEBIT\") != SUM (e.\"CREDIT\") )>0",
168
                // tableEcriture.getField("LETTRAGE")));
169
 
170
                wLettrage = wLettrage.or(new Where(tableEcriture.getField("LETTRAGE"), true, lettrageToExclude));
171
                input.setWhere(w.and(wLettrage));
172
 
173
                input.addFieldOrder(tableEcriture.getField("COMPTE_NUMERO"));
174
 
175
                System.err.println(input.asString());
176
                return input;
177
            }
178
        });
179
 
18 ilm 180
        List<Map<String, Object>> valuesTab = new ArrayList<Map<String, Object>>();
181
 
73 ilm 182
        // List<SQLRow> l = (List<SQLRow>)
183
        // ecr.getTable().getBase().getDataSource().execute(sel.asString(),
184
        // SQLRowListRSH.createFromSelect(sel));
18 ilm 185
 
73 ilm 186
        List<SQLRowValues> l = fetcher.fetch();
18 ilm 187
 
73 ilm 188
        Map<String, Map<String, Object>> vals = new LinkedHashMap<String, Map<String, Object>>();
189
 
190
        long total0 = 0;
18 ilm 191
        long total30 = 0;
192
        long total60 = 0;
193
        long total90 = 0;
194
        long totalPlus = 0;
195
        long totalFull = 0;
73 ilm 196
        long totalEchue = 0;
18 ilm 197
 
73 ilm 198
        // Calendar c = Calendar.getInstance();
199
        // final long timeInMillis = c.getTimeInMillis();
200
        final long timeInMillis = this.fin.getTime();
201
        for (SQLRowValues sqlRow : l) {
18 ilm 202
            long date = sqlRow.getDate("DATE").getTimeInMillis();
73 ilm 203
            SQLRowAccessor rowMvt = sqlRow.getForeign("ID_MOUVEMENT");
18 ilm 204
            if (rowMvt.getString("SOURCE").equalsIgnoreCase("SAISIE_VENTE_FACTURE")) {
205
                SQLRow rowFact = fact.getTable().getRow(rowMvt.getInt("IDSOURCE"));
206
                date = ModeDeReglementSQLElement.calculDate(rowFact.getForeignRow("ID_MODE_REGLEMENT"), rowFact.getDate("DATE").getTime()).getTime();
207
            }
208
 
73 ilm 209
            long time = timeInMillis - date;
18 ilm 210
            long day = time / 86400000;
73 ilm 211
            // if (day < 0) {
212
            // continue;
213
            // }
214
            // final SQLRow rowCpt = sqlRow.getForeignRow("ID_COMPTE_PCE");
215
            // String num = rowCpt.getString("NUMERO");
216
            String num = sqlRow.getString("COMPTE_NUMERO");
18 ilm 217
            Map<String, Object> m;
218
            if (vals.get(num) == null) {
219
                m = new HashMap<String, Object>();
220
                vals.put(num, m);
221
            } else {
222
                m = vals.get(num);
223
            }
224
 
225
            final long value = sqlRow.getLong("DEBIT") - sqlRow.getLong("CREDIT");
226
            totalFull += value;
227
            String key = "+90";
73 ilm 228
            if (day < 0) {
229
                key = "0";
230
                total0 += value;
18 ilm 231
            } else {
73 ilm 232
                totalEchue += value;
233
                if (day <= 30) {
234
                    key = "30";
235
                    total30 += value;
236
                } else if (day <= 60) {
237
                    key = "60";
238
                    total60 += value;
239
                } else if (day <= 90) {
240
                    key = "90";
241
                    total90 += value;
242
                } else {
243
                    totalPlus += value;
244
                }
18 ilm 245
            }
246
            m.put("NUMERO", num);
73 ilm 247
            m.put("NOM", sqlRow.getString("COMPTE_NOM"));
18 ilm 248
 
249
            if (m.get(key) == null) {
250
                m.put(key, value);
251
            } else {
252
                long cred = (Long) m.get(key);
253
                m.put(key, cred + value);
254
            }
255
 
256
            long total = 0;
257
            if (m.get("TOTAL") != null) {
258
                total = (Long) m.get("TOTAL");
259
            }
260
            m.put("TOTAL", total + value);
261
 
262
        }
73 ilm 263
        System.err.println("INTERMED BALANCE");
18 ilm 264
        for (String k : vals.keySet()) {
265
            final Map<String, Object> e = vals.get(k);
73 ilm 266
            Long l0 = (Long) e.get("0");
18 ilm 267
            Long l1 = (Long) e.get("30");
268
            Long l2 = (Long) e.get("60");
269
            Long l3 = (Long) e.get("90");
270
            Long l4 = (Long) e.get("+90");
271
            Long l5 = (Long) e.get("TOTAL");
272
 
73 ilm 273
            if ((l0 != null && l0 != 0) || (l1 != null && l1 != 0) || (l2 != null && l2 != 0) || (l3 != null && l3 != 0) || (l4 != null && l4 != 0)) {
274
 
275
                if (l0 != null && l0 != 0) {
276
                    e.put("0", l0 / 100.0);
277
                }
278
 
18 ilm 279
                if (l1 != null && l1 != 0) {
280
                    e.put("30", l1 / 100.0);
281
                }
282
 
283
                if (l2 != null && l2 != 0) {
284
                    e.put("60", l2 / 100.0);
285
                }
286
 
287
                if (l3 != null && l3 != 0) {
288
                    e.put("90", l3 / 100.0);
289
                }
290
 
291
                if (l4 != null && l4 != 0) {
292
                    e.put("+90", l4 / 100.0);
293
                }
294
 
295
                if (l5 != null && l5 != 0) {
296
                    e.put("TOTAL", l5 / 100.0);
297
                }
298
 
299
                valuesTab.add(e);
300
            }
301
        }
25 ilm 302
        final Map<String, Object> totalMap = new HashMap<String, Object>();
18 ilm 303
        totalMap.put("NOM", "TOTAL");
73 ilm 304
        totalMap.put("0", total0 / 100.0);
18 ilm 305
        totalMap.put("30", total30 / 100.0);
306
        totalMap.put("60", total60 / 100.0);
307
        totalMap.put("90", total90 / 100.0);
308
        totalMap.put("+90", totalPlus / 100.0);
309
        totalMap.put("TOTAL", totalFull / 100.0);
73 ilm 310
        totalMap.put("TOTAL_ECHUE", totalEchue / 100.0);
18 ilm 311
        valuesTab.add(totalMap);
73 ilm 312
        System.err.println("FIN BALANCE");
18 ilm 313
        this.listAllSheetValues.put(0, valuesTab);
314
 
315
    }
316
}