OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 18 | Rev 25 | Go to most recent revision | 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.generationDoc.SheetXml;
19
import org.openconcerto.erp.preferences.PrinterNXProps;
20
import org.openconcerto.sql.Configuration;
21
import org.openconcerto.sql.element.SQLElement;
22
import org.openconcerto.sql.model.SQLRow;
23
import org.openconcerto.sql.model.SQLRowListRSH;
24
import org.openconcerto.sql.model.SQLSelect;
25
import org.openconcerto.sql.model.Where;
26
import org.openconcerto.utils.Tuple2;
27
 
28
import java.util.ArrayList;
29
import java.util.Calendar;
30
import java.util.Date;
31
import java.util.HashMap;
32
import java.util.List;
33
import java.util.Map;
34
 
35
public class BalanceAgeeListeSheetXML extends AbstractListeSheetXml {
36
 
37
    // private final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
38
    private Date deb, fin;
39
 
40
    public static Tuple2<String, String> getTuple2Location() {
41
        return tupleDefault;
42
    }
43
 
44
    public BalanceAgeeListeSheetXML(Date deb, Date fin) {
45
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
46
        this.deb = deb;
47
        this.fin = fin;
48
 
49
        this.locationOO = SheetXml.getLocationForTuple(tupleDefault, false);
50
        this.locationPDF = SheetXml.getLocationForTuple(tupleDefault, true);
51
    }
52
 
21 ilm 53
    @Override
54
    public String getDefaultModele() {
55
        return "BalanceAgee";
56
    }
57
 
18 ilm 58
    public String getFileName() {
59
        return getValidFileName("BalanceAgee" + new Date().getTime());
60
    }
61
 
62
    protected void createListeValues() {
63
 
64
        SQLElement ecr = Configuration.getInstance().getDirectory().getElement("ECRITURE");
65
        SQLElement cpt = Configuration.getInstance().getDirectory().getElement("COMPTE_PCE");
66
        SQLElement fact = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE");
67
 
68
        SQLSelect sel = new SQLSelect(ecr.getTable().getBase());
69
        sel.addSelectStar(ecr.getTable());
70
        Where w = new Where(cpt.getTable().getField("NUMERO"), "LIKE", "411%");
71
        if (this.deb != null) {
72
            w = w.and(new Where(ecr.getTable().getField("DATE"), ">=", this.deb));
73
        }
74
        if (this.fin != null) {
75
            w = w.and(new Where(ecr.getTable().getField("DATE"), "<=", this.fin));
76
        }
77
        // w = w.and(new Where(cpt.getTable().getField("NOM"), "LIKE", "%RIBEIRO%"));
78
        w = w.and(new Where(ecr.getTable().getField("ID_COMPTE_PCE"), "=", cpt.getTable().getKey()));
79
        w = w.and(new Where(ecr.getTable().getField("LETTRAGE"), "=", "").or(new Where(ecr.getTable().getField("LETTRAGE"), "=", (Object) null)));
80
 
81
        sel.setWhere(w);
82
        sel.addFieldOrder(ecr.getTable().getField("COMPTE_NUMERO"));
83
 
84
        System.err.println(sel.asString());
85
        List<Map<String, Object>> valuesTab = new ArrayList<Map<String, Object>>();
86
 
87
        List<SQLRow> l = (List<SQLRow>) ecr.getTable().getBase().getDataSource().execute(sel.asString(), SQLRowListRSH.createFromSelect(sel));
88
 
89
        Calendar c = Calendar.getInstance();
90
        Map<String, Map<String, Object>> vals = new HashMap<String, Map<String, Object>>();
91
 
92
        long total30 = 0;
93
        long total60 = 0;
94
        long total90 = 0;
95
        long totalPlus = 0;
96
        long totalFull = 0;
97
 
98
        for (SQLRow sqlRow : l) {
99
            long date = sqlRow.getDate("DATE").getTimeInMillis();
100
            SQLRow rowMvt = sqlRow.getForeignRow("ID_MOUVEMENT");
101
            if (rowMvt.getString("SOURCE").equalsIgnoreCase("SAISIE_VENTE_FACTURE")) {
102
                SQLRow rowFact = fact.getTable().getRow(rowMvt.getInt("IDSOURCE"));
103
                date = ModeDeReglementSQLElement.calculDate(rowFact.getForeignRow("ID_MODE_REGLEMENT"), rowFact.getDate("DATE").getTime()).getTime();
104
            }
105
 
106
            long time = c.getTimeInMillis() - date;
107
            long day = time / 86400000;
108
            if (day < 0) {
109
                // System.err.println("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
110
                continue;
111
            }
112
            final SQLRow rowCpt = sqlRow.getForeignRow("ID_COMPTE_PCE");
113
            String num = rowCpt.getString("NUMERO");
114
            Map<String, Object> m;
115
            if (vals.get(num) == null) {
116
                m = new HashMap<String, Object>();
117
                vals.put(num, m);
118
            } else {
119
                m = vals.get(num);
120
            }
121
 
122
            final long value = sqlRow.getLong("DEBIT") - sqlRow.getLong("CREDIT");
123
            totalFull += value;
124
            String key = "+90";
125
            if (day <= 30) {
126
                key = "30";
127
                total30 += value;
128
            } else if (day <= 60) {
129
                key = "60";
130
                total60 += value;
131
            } else if (day <= 90) {
132
                key = "90";
133
                total90 += value;
134
            } else {
135
                totalPlus += value;
136
            }
137
 
138
            m.put("NUMERO", num);
139
            m.put("NOM", rowCpt.getString("NOM"));
140
 
141
            if (m.get(key) == null) {
142
                m.put(key, value);
143
            } else {
144
                long cred = (Long) m.get(key);
145
                m.put(key, cred + value);
146
            }
147
 
148
            long total = 0;
149
            if (m.get("TOTAL") != null) {
150
                total = (Long) m.get("TOTAL");
151
            }
152
            m.put("TOTAL", total + value);
153
 
154
        }
155
 
156
        for (String k : vals.keySet()) {
157
            final Map<String, Object> e = vals.get(k);
158
            Long l1 = (Long) e.get("30");
159
            Long l2 = (Long) e.get("60");
160
            Long l3 = (Long) e.get("90");
161
            Long l4 = (Long) e.get("+90");
162
            Long l5 = (Long) e.get("TOTAL");
163
 
164
            if ((l1 != null && l1 != 0) || (l2 != null && l2 != 0) || (l3 != null && l3 != 0) || (l4 != null && l4 != 0)) {
165
                if (l1 != null && l1 != 0) {
166
                    e.put("30", l1 / 100.0);
167
                }
168
 
169
                if (l2 != null && l2 != 0) {
170
                    e.put("60", l2 / 100.0);
171
                }
172
 
173
                if (l3 != null && l3 != 0) {
174
                    e.put("90", l3 / 100.0);
175
                }
176
 
177
                if (l4 != null && l4 != 0) {
178
                    e.put("+90", l4 / 100.0);
179
                }
180
 
181
                if (l5 != null && l5 != 0) {
182
                    e.put("TOTAL", l5 / 100.0);
183
                }
184
 
185
                valuesTab.add(e);
186
            }
187
        }
188
        Map<String, Object> totalMap = new HashMap<String, Object>();
189
        totalMap.put("NOM", "TOTAL");
190
        totalMap.put("30", total30 / 100.0);
191
        totalMap.put("60", total60 / 100.0);
192
        totalMap.put("90", total90 / 100.0);
193
        totalMap.put("+90", totalPlus / 100.0);
194
        totalMap.put("TOTAL", totalFull / 100.0);
195
        valuesTab.add(totalMap);
196
 
197
        // Map<String, Object> values = this.mapAllSheetValues.get(0);
198
        // if (values == null) {
199
        // values = new HashMap<String, Object>();
200
        // }
201
        // valuesHA.put("TOTAL", totalHA);
202
        // valuesE.put("TOTAL_HA", totalHA);
203
        // valuesE.put("TOTAL", totalE);
204
        // valuesE.put("TOTAL_VT", totalTPVTTC);
205
        // values.put("TOTAL", totalVC);
206
        // values.put("TOTAL_MARGE", totalTPVTTC - totalTPA);
207
        //
208
        // valuesE.put("TOTAL_GLOBAL", totalTPVTTC + totalHA);
209
        // values.put("TOTAL_PA", totalTPA);
210
        // values.put("TOTAL_PV_TTC", totalTPVTTC);
211
        //
212
        // String periode = "Période Du " + dateFormat.format(this.du) + " au " +
213
        // dateFormat.format(this.au);
214
        // values.put("DATE", periode);
215
        // valuesHA.put("DATE", periode);
216
        // valuesE.put("DATE", periode);
217
 
218
        this.listAllSheetValues.put(0, valuesTab);
219
        // this.mapAllSheetValues.put(0, values);
220
 
221
    }
222
}