OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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