OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 21 | Rev 58 | 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.generationDoc.gestcomm;
15
 
16
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
25 ilm 17
import org.openconcerto.erp.generationDoc.DocumentLocalStorageManager;
18 ilm 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.AliasedField;
23
import org.openconcerto.sql.model.AliasedTable;
24
import org.openconcerto.sql.model.SQLSelect;
25
import org.openconcerto.sql.model.SQLTable;
26
import org.openconcerto.sql.model.Where;
27
import org.openconcerto.utils.Tuple2;
28
 
25 ilm 29
import java.io.File;
18 ilm 30
import java.sql.Timestamp;
31
import java.text.DateFormat;
32
import java.text.SimpleDateFormat;
33
import java.util.ArrayList;
34
import java.util.Date;
35
import java.util.HashMap;
36
import java.util.List;
37
import java.util.Map;
38
 
39
import org.apache.commons.dbutils.handlers.ArrayListHandler;
40
 
41
/**
42
 * Statistique des ventes d'articles
43
 *
44
 * @author Ludo
45
 *
46
 */
47
public class EtatVentesXmlSheet extends AbstractListeSheetXml {
48
 
49
    private static final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
50
 
25 ilm 51
    public static final String TEMPLATE_ID = "EtatVentes";
52
 
53
    public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
54
 
18 ilm 55
    private Timestamp du, au;
56
 
57
    public EtatVentesXmlSheet(Date du, Date au) {
58
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
59
        du.setHours(0);
60
        du.setMinutes(0);
61
        au.setHours(23);
62
        au.setMinutes(59);
63
        this.du = new Timestamp(du.getTime());
64
        this.au = new Timestamp(au.getTime());
65
 
66
    }
67
 
25 ilm 68
 
69
 
21 ilm 70
    @Override
25 ilm 71
    public String getDefaultTemplateId() {
72
        return TEMPLATE_ID;
21 ilm 73
    }
74
 
25 ilm 75
    @Override
76
    public String getName() {
77
        return "EtatVentes" + new Date().getTime();
18 ilm 78
    }
79
 
80
    protected void createListeValues() {
81
        SQLElement elt = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE_ELEMENT");
82
        SQLElement elt2 = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE");
83
        SQLElement eltEnc = Configuration.getInstance().getDirectory().getElement("ENCAISSER_MONTANT");
84
        SQLElement elt3 = Configuration.getInstance().getDirectory().getElement("TICKET_CAISSE");
85
        SQLElement eltMod = Configuration.getInstance().getDirectory().getElement("MODE_REGLEMENT");
86
        AliasedTable table1 = new AliasedTable(eltMod.getTable(), "mod1");
87
        AliasedTable tableTicket = new AliasedTable(elt3.getTable(), "ticket");
88
        AliasedTable table2 = new AliasedTable(eltMod.getTable(), "mod2");
89
 
90
        // Caisse et facture
91
        SQLSelect sel = new SQLSelect(Configuration.getInstance().getBase());
92
        sel.addSelect(elt.getTable().getField("NOM"));
93
        sel.addSelect(elt.getTable().getField("T_PA_HT"), "SUM");
94
        sel.addSelect(elt.getTable().getField("T_PV_HT"), "SUM");
95
        sel.addSelect(elt.getTable().getField("T_PV_TTC"), "SUM");
96
 
97
        sel.addSelect(elt.getTable().getField("QTE"), "SUM");
98
        sel.addSelect(elt.getTable().getField("CODE"));
99
 
100
        Where w = new Where(elt.getTable().getField("ID_TICKET_CAISSE"), "=", 1);
101
        sel.addJoin("LEFT", elt.getTable().getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
102
 
103
        Where w2 = new Where(elt.getTable().getField("ID_SAISIE_VENTE_FACTURE"), "=", 1);
104
 
105
        sel.addJoin("LEFT", elt2.getTable().getField("ID_MODE_REGLEMENT"), "mod1");
106
 
107
        sel.addJoin("LEFT", elt.getTable().getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
108
 
109
        sel.addBackwardJoin("LEFT", "enc", eltEnc.getTable().getField("ID_TICKET_CAISSE"), "ticket");
110
        sel.addJoin("LEFT", new AliasedField(eltEnc.getTable().getField("ID_MODE_REGLEMENT"), "enc"), "mod2");
111
 
112
        sel.addRawSelect(
113
                "SUM(CASE WHEN " + table1.getField("ID_TYPE_REGLEMENT").getFieldRef() + " =2 OR " + table2.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=2 THEN "
114
                        + sel.getAlias(elt.getTable().getField("QTE")).getFieldRef() + " ELSE 0 END)", "Cheque");
115
        sel.addRawSelect(
116
                "SUM(CASE WHEN " + table1.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=3 OR " + table2.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=3 THEN "
117
                        + sel.getAlias(elt.getTable().getField("QTE")).getFieldRef() + " ELSE 0 END)", "CB");
118
        sel.addRawSelect(
119
                "SUM(CASE WHEN " + table1.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=4 OR " + table2.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=4 THEN "
120
                        + sel.getAlias(elt.getTable().getField("QTE")).getFieldRef() + " ELSE 0 END)", "Especes");
121
 
122
        Where w3 = new Where(tableTicket.getField("DATE"), this.du, this.au);
123
        Where w4 = new Where(elt2.getTable().getField("DATE"), this.du, this.au);
124
        sel.setWhere(w3.or(w4));
125
        sel.addGroupBy(elt.getTable().getField("NOM"));
126
        sel.addGroupBy(elt.getTable().getField("CODE"));
127
        System.err.println(sel.asString());
128
        List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
129
 
130
        if (listeIds == null) {
131
            return;
132
        }
133
        ArrayList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>(listeIds.size());
134
        double totalTPA = 0;
135
        double totalTPVTTC = 0;
136
        for (Object[] obj : listeIds) {
137
            Map<String, Object> mValues = new HashMap<String, Object>();
138
            mValues.put("NOM", obj[0]);
139
            mValues.put("QTE", obj[4]);
140
            final Double tPA = new Double(((Number) obj[1]).longValue() / 100.0);
141
            mValues.put("T_PA", tPA);
142
            final Double tPVHT = new Double(((Number) obj[2]).longValue() / 100.0);
143
            mValues.put("T_PV_HT", tPVHT);
144
            final Double TPVTTC = new Double(((Number) obj[3]).longValue() / 100.0);
145
            mValues.put("T_PV_TTC", TPVTTC);
146
 
147
            mValues.put("NB_CHEQUE", obj[6]);
148
            mValues.put("NB_CB", obj[7]);
149
            mValues.put("NB_ESPECES", obj[8]);
150
            totalTPA += tPA;
151
            totalTPVTTC += TPVTTC;
152
            listValues.add(mValues);
153
        }
154
 
155
        // Liste des ventes comptoirs
156
        final SQLTable venteComptoirT = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_COMPTOIR").getTable();
157
        SQLSelect selVC = new SQLSelect(venteComptoirT.getBase());
158
        selVC.addSelect(venteComptoirT.getField("NOM"));
159
        selVC.addSelect(venteComptoirT.getField("MONTANT_HT"), "SUM");
160
        selVC.addSelect(venteComptoirT.getField("MONTANT_TTC"), "SUM");
161
        selVC.addSelect(venteComptoirT.getField("NOM"), "COUNT");
162
        Where wVC = new Where(venteComptoirT.getField("DATE"), this.du, this.au);
163
        wVC = wVC.and(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
164
        selVC.setWhere(wVC);
165
        selVC.addGroupBy(venteComptoirT.getField("NOM"));
166
        List<Object[]> listVC = (List<Object[]>) venteComptoirT.getDBSystemRoot().getDataSource().execute(selVC.asString(), new ArrayListHandler());
167
        double totalVC = 0;
168
        if (listVC.size() > 0) {
169
            Map<String, Object> mValues = new HashMap<String, Object>();
170
            mValues.put("NOM", " ");
171
            listValues.add(mValues);
172
 
173
            Map<String, Object> mValues2 = new HashMap<String, Object>();
174
            if (listVC.size() > 1) {
175
                mValues2.put("NOM", "VENTES COMPTOIR");
176
            } else {
177
                mValues2.put("NOM", "VENTE COMPTOIR");
178
            }
179
            Map<Integer, String> style = styleAllSheetValues.get(0);
180
            if (style == null) {
181
                style = new HashMap<Integer, String>();
182
            }
183
 
184
            style.put(listValues.size(), "Titre 1");
185
 
186
            styleAllSheetValues.put(0, style);
187
            listValues.add(mValues2);
188
 
189
        }
190
        for (Object[] row : listVC) {
191
 
192
            Map<String, Object> mValues = new HashMap<String, Object>();
193
 
194
            mValues.put("NOM", row[0]);
195
            final Double ht = new Double(((Number) row[1]).longValue() / 100.0);
196
            final Double ttc = new Double(((Number) row[2]).longValue() / 100.0);
197
 
198
            mValues.put("QTE", row[3]);
199
            mValues.put("T_PV_HT", ht);
200
            mValues.put("T_PV_TTC", ttc);
201
 
202
            totalVC += ttc;
203
            listValues.add(mValues);
204
        }
205
 
206
        // Liste des Achats
207
        ArrayList<Map<String, Object>> listValuesHA = new ArrayList<Map<String, Object>>(listeIds.size());
208
        Map<String, Object> valuesHA = this.mapAllSheetValues.get(1);
209
        if (valuesHA == null) {
210
            valuesHA = new HashMap<String, Object>();
211
        }
212
        SQLElement eltAchat = Configuration.getInstance().getDirectory().getElement("SAISIE_ACHAT");
213
 
214
        SQLSelect selAchat = new SQLSelect(Configuration.getInstance().getBase());
215
        selAchat.addSelect(eltAchat.getTable().getField("NOM"));
216
        selAchat.addSelect(eltAchat.getTable().getField("MONTANT_HT"), "SUM");
217
        selAchat.addSelect(eltAchat.getTable().getField("MONTANT_TTC"), "SUM");
218
        Where wHA = new Where(eltAchat.getTable().getField("DATE"), this.du, this.au);
219
        selAchat.setWhere(wHA);
220
        selAchat.addGroupBy(eltAchat.getTable().getField("NOM"));
221
        List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());
222
 
223
        double totalHA = 0;
224
 
225
        for (Object[] row : listAchat) {
226
 
227
            Map<String, Object> mValues = new HashMap<String, Object>();
228
 
229
            mValues.put("NOM", row[0]);
230
            final Double ht = new Double(((Number) row[1]).longValue() / 100.0);
231
            final Double pA = new Double(((Number) row[2]).longValue() / 100.0);
232
 
233
            mValues.put("T_PV_HT", -ht);
234
            mValues.put("T_PV_TTC", -pA);
235
 
236
            totalHA -= pA;
237
            listValuesHA.add(mValues);
238
        }
239
 
240
        totalTPVTTC += totalVC;
241
 
242
        // Récapitulatif
243
        Map<String, Object> valuesE = this.mapAllSheetValues.get(2);
244
        if (valuesE == null) {
245
            valuesE = new HashMap<String, Object>();
246
        }
247
        SQLElement eltE = Configuration.getInstance().getDirectory().getElement("ENCAISSER_MONTANT");
248
        SQLElement eltM = Configuration.getInstance().getDirectory().getElement("MODE_REGLEMENT");
249
        SQLElement eltT = Configuration.getInstance().getDirectory().getElement("TYPE_REGLEMENT");
250
        SQLSelect selE = new SQLSelect(Configuration.getInstance().getBase());
251
        selE.addSelect(eltT.getTable().getField("NOM"));
252
        selE.addSelect(eltT.getTable().getField("NOM"), "COUNT");
253
        selE.addSelect(eltE.getTable().getField("MONTANT"), "SUM");
254
        Where wE = new Where(eltE.getTable().getField("DATE"), this.du, this.au);
255
        wE = wE.and(new Where(eltE.getTable().getField("ID_MODE_REGLEMENT"), "=", eltM.getTable().getKey()));
256
        wE = wE.and(new Where(eltM.getTable().getField("ID_TYPE_REGLEMENT"), "=", eltT.getTable().getKey()));
257
        selE.setWhere(wE);
258
        selE.addGroupBy(eltT.getTable().getField("NOM"));
259
        selE.addFieldOrder(eltT.getTable().getField("NOM"));
260
        List<Object[]> listE = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selE.asString(), new ArrayListHandler());
261
        ArrayList<Map<String, Object>> listValuesE = new ArrayList<Map<String, Object>>(listeIds.size());
262
        double totalE = 0;
263
 
264
        for (Object[] o : listE) {
265
            Map<String, Object> mValues = new HashMap<String, Object>();
266
 
267
            mValues.put("NOM", o[0]);
268
 
269
            final Double pA = new Double(((Number) o[2]).longValue() / 100.0);
270
            mValues.put("QTE", o[1]);
271
            mValues.put("TOTAL", pA);
272
 
273
            totalE += pA;
274
            listValuesE.add(mValues);
275
        }
276
 
277
        Map<String, Object> values = this.mapAllSheetValues.get(0);
278
        if (values == null) {
279
            values = new HashMap<String, Object>();
280
        }
281
        valuesHA.put("TOTAL", totalHA);
282
        valuesE.put("TOTAL_HA", totalHA);
283
        valuesE.put("TOTAL", totalE);
284
        valuesE.put("TOTAL_VT", totalTPVTTC);
285
        values.put("TOTAL", totalVC);
286
        values.put("TOTAL_MARGE", totalTPVTTC - totalTPA);
287
 
288
        valuesE.put("TOTAL_GLOBAL", totalTPVTTC + totalHA);
289
        values.put("TOTAL_PA", totalTPA);
290
        values.put("TOTAL_PV_TTC", totalTPVTTC);
291
 
292
        String periode = "Période Du " + dateFormat.format(this.du) + " au " + dateFormat.format(this.au);
293
        values.put("DATE", periode);
294
        valuesHA.put("DATE", periode);
295
        valuesE.put("DATE", periode);
296
        System.err.println(this.du);
297
        System.err.println(this.au);
298
        this.listAllSheetValues.put(0, listValues);
299
        this.mapAllSheetValues.put(0, values);
300
 
301
        this.listAllSheetValues.put(1, listValuesHA);
302
        this.mapAllSheetValues.put(1, valuesHA);
303
 
304
        this.listAllSheetValues.put(2, listValuesE);
305
        this.mapAllSheetValues.put(2, valuesE);
306
 
307
    }
308
 
309
}