OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 149 | 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
 
142 ilm 16
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
17
import org.openconcerto.erp.core.finance.tax.model.TaxeCache;
18 ilm 18
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
19
import org.openconcerto.erp.preferences.PrinterNXProps;
20
import org.openconcerto.sql.Configuration;
21
import org.openconcerto.sql.element.SQLElement;
58 ilm 22
import org.openconcerto.sql.element.SQLElementDirectory;
18 ilm 23
import org.openconcerto.sql.model.AliasedField;
24
import org.openconcerto.sql.model.AliasedTable;
142 ilm 25
import org.openconcerto.sql.model.SQLRow;
26
import org.openconcerto.sql.model.SQLRowAccessor;
174 ilm 27
import org.openconcerto.sql.model.SQLRowValues;
28
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
18 ilm 29
import org.openconcerto.sql.model.SQLSelect;
149 ilm 30
import org.openconcerto.sql.model.SQLSelectJoin;
18 ilm 31
import org.openconcerto.sql.model.SQLTable;
32
import org.openconcerto.sql.model.Where;
149 ilm 33
import org.openconcerto.utils.Tuple2;
18 ilm 34
 
67 ilm 35
import java.math.BigDecimal;
149 ilm 36
import java.math.RoundingMode;
18 ilm 37
import java.sql.Timestamp;
38
import java.text.DateFormat;
39
import java.text.SimpleDateFormat;
40
import java.util.ArrayList;
58 ilm 41
import java.util.Calendar;
18 ilm 42
import java.util.Date;
43
import java.util.HashMap;
142 ilm 44
import java.util.HashSet;
18 ilm 45
import java.util.List;
46
import java.util.Map;
142 ilm 47
import java.util.Set;
18 ilm 48
 
49
import org.apache.commons.dbutils.handlers.ArrayListHandler;
50
 
51
/**
52
 * Statistique des ventes d'articles
53
 *
54
 */
55
public class EtatVentesXmlSheet extends AbstractListeSheetXml {
56
 
58 ilm 57
    private static final String MODE2 = "mod2";
18 ilm 58
 
58 ilm 59
    private static final String MODE1 = "mod1";
60
 
25 ilm 61
    public static final String TEMPLATE_ID = "EtatVentes";
62
 
63
    public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
64
 
18 ilm 65
    private Timestamp du, au;
149 ilm 66
    public boolean ticketCaisse = false;
174 ilm 67
    public boolean facture = false;
18 ilm 68
 
174 ilm 69
    public EtatVentesXmlSheet(Date du, Date au, boolean ticketCaisse, boolean facture) {
67 ilm 70
        super();
18 ilm 71
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
149 ilm 72
        this.ticketCaisse = ticketCaisse;
174 ilm 73
        this.facture = facture;
80 ilm 74
        if (du != null) {
75
            final Calendar c1 = Calendar.getInstance();
76
            c1.setTime(du);
77
            c1.set(Calendar.HOUR_OF_DAY, 0);
78
            c1.set(Calendar.MINUTE, 0);
79
            c1.set(Calendar.SECOND, 0);
80
            this.du = new Timestamp(c1.getTimeInMillis());
81
        }
82
        if (au != null) {
83
            final Calendar c2 = Calendar.getInstance();
84
            c2.setTime(au);
85
            c2.set(Calendar.HOUR_OF_DAY, 23);
86
            c2.set(Calendar.MINUTE, 59);
87
            c2.set(Calendar.SECOND, 59);
88
            this.au = new Timestamp(c2.getTimeInMillis());
89
        }
18 ilm 90
    }
91
 
21 ilm 92
    @Override
25 ilm 93
    public String getDefaultTemplateId() {
94
        return TEMPLATE_ID;
21 ilm 95
    }
96
 
25 ilm 97
    @Override
67 ilm 98
    protected String getStoragePathP() {
99
        return "Etat Ventes";
100
    }
101
 
102
    Date d;
103
 
104
    @Override
25 ilm 105
    public String getName() {
67 ilm 106
        if (d == null) {
107
            d = new Date();
108
        }
109
        return "EtatVentes" + d.getTime();
18 ilm 110
    }
111
 
112
    protected void createListeValues() {
149 ilm 113
        Map<Integer, Tuple2<BigDecimal, BigDecimal>> mapTVAVT = new HashMap<Integer, Tuple2<BigDecimal, BigDecimal>>();
114
        Map<Integer, Tuple2<BigDecimal, BigDecimal>> mapTVAHA = new HashMap<Integer, Tuple2<BigDecimal, BigDecimal>>();
58 ilm 115
        final SQLElementDirectory directory = Configuration.getInstance().getDirectory();
116
        final SQLElement eltVenteFacutreElement = directory.getElement("SAISIE_VENTE_FACTURE_ELEMENT");
117
        final SQLElement eltVenteFacture = directory.getElement("SAISIE_VENTE_FACTURE");
118
        final SQLElement eltEncaissement = directory.getElement("ENCAISSER_MONTANT");
119
        final SQLElement eltTicketCaisse = directory.getElement("TICKET_CAISSE");
120
        final SQLElement eltModeReglement = directory.getElement("MODE_REGLEMENT");
121
        final SQLTable tableModeReglement = eltModeReglement.getTable();
122
        final SQLTable tableFactureElement = eltVenteFacutreElement.getTable();
123
        final SQLTable tableFacture = eltVenteFacture.getTable();
149 ilm 124
        BigDecimal totalTPVTTC = BigDecimal.ZERO;
125
        BigDecimal totalTPA = BigDecimal.ZERO;
18 ilm 126
 
149 ilm 127
        // Liste des valeurs de la feuille OO
128
        ArrayList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>();
129
        Map<Integer, String> style = styleAllSheetValues.get(0);
130
        if (style == null) {
131
            style = new HashMap<Integer, String>();
132
            styleAllSheetValues.put(0, style);
133
        }
18 ilm 134
 
174 ilm 135
        final ArrayList<Map<String, Object>> listValuesStock = new ArrayList<>();
136
        final Map<Integer, String> styleStock = new HashMap<>();
137
 
149 ilm 138
        // Ventes
174 ilm 139
        final SQLTable foreignTableArticle = tableFactureElement.getForeignTable("ID_ARTICLE");
149 ilm 140
        {
18 ilm 141
 
149 ilm 142
            final AliasedTable tableModeReglement1 = new AliasedTable(tableModeReglement, MODE1);
143
            final AliasedTable tableModeReglement2 = new AliasedTable(tableModeReglement, MODE2);
144
            final AliasedTable tableTicket = new AliasedTable(eltTicketCaisse.getTable(), "ticket");
18 ilm 145
 
174 ilm 146
            // Stock
147
            SQLRowValues rowValsArtStock = new SQLRowValues(foreignTableArticle);
148
            rowValsArtStock.putNulls("ID", "CODE", "NOM");
149
            rowValsArtStock.putRowValues("ID_STOCK").putNulls("QTE_REEL", "QTE_TH", "QTE_MIN", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
150
            SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(rowValsArtStock);
151
            List<SQLRowValues> resultStock = fetcherStock.fetch();
152
            Map<Integer, SQLRowValues> mapStock = new HashMap<>();
153
            for (SQLRowValues sqlRowValues : resultStock) {
154
                mapStock.put(sqlRowValues.getID(), sqlRowValues);
155
            }
156
 
149 ilm 157
            // Requete Pour obtenir les quantités pour chaque type de réglement
158
            SQLSelect sel = new SQLSelect();
18 ilm 159
 
149 ilm 160
            sel.addSelect(tableFactureElement.getField("CODE"));
161
            sel.addSelect(tableFactureElement.getField("NOM"));
162
            sel.addSelect(tableFactureElement.getField("ID_ARTICLE"));
163
            sel.addSelect(tableFactureElement.getField("ID_TAXE"));
18 ilm 164
 
149 ilm 165
            // Elements assosciés à une facture
166
            Where w = new Where(tableFactureElement.getField("ID_TICKET_CAISSE"), "=", tableTicket.getTable().getUndefinedID());
167
            sel.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
168
            SQLSelectJoin joinArt = sel.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
169
            SQLSelectJoin joinFamArt = sel.addJoin("LEFT", joinArt.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
170
            sel.addSelect(joinFamArt.getJoinedTable().getField("NOM"));
18 ilm 171
 
149 ilm 172
            // Elements associés à un ticket de caisse
173
            Where w2 = new Where(tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE"), "=", 1);
174
            sel.addJoin("LEFT", tableFacture.getField("ID_MODE_REGLEMENT"), MODE1);
175
            sel.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
18 ilm 176
 
149 ilm 177
            sel.addBackwardJoin("LEFT", "enc", eltEncaissement.getTable().getField("ID_TICKET_CAISSE"), "ticket");
178
            sel.addJoin("LEFT", new AliasedField(eltEncaissement.getTable().getField("ID_MODE_REGLEMENT"), "enc"), MODE2);
58 ilm 179
 
149 ilm 180
            final String idTypeReglement1 = tableModeReglement1.getField("ID_TYPE_REGLEMENT").getFieldRef();
181
            final String idTypeReglement2 = tableModeReglement2.getField("ID_TYPE_REGLEMENT").getFieldRef();
58 ilm 182
 
149 ilm 183
            final String qte = sel.getAlias(tableFactureElement.getField("QTE")).getFieldRef();
184
            sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=2 OR " + idTypeReglement2 + "=2 THEN " + qte + " ELSE 0 END)", "Cheque");
185
            sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=3 OR " + idTypeReglement2 + "=3 THEN " + qte + " ELSE 0 END)", "CB");
186
            sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=4 OR " + idTypeReglement2 + "=4 THEN " + qte + " ELSE 0 END)", "Especes");
58 ilm 187
 
149 ilm 188
            Where w3 = new Where(tableTicket.getField("DATE"), this.du, this.au);
189
            Where w4 = new Where(tableFacture.getField("DATE"), this.du, this.au);
190
            Where wN = new Where(tableFactureElement.getField("NIVEAU"), "=", 1);
191
            if (this.du != null && this.au != null) {
192
                sel.setWhere(w3.or(w4).and(wN));
193
            } else {
194
                sel.setWhere(wN);
195
            }
196
            // FIXME traiter le cas du!=null et au==null et vice versa
197
            sel.addGroupBy(tableFactureElement.getField("NOM"));
198
            sel.addGroupBy(tableFactureElement.getField("CODE"));
199
            sel.addGroupBy(tableFactureElement.getField("ID_ARTICLE"));
200
            sel.addGroupBy(tableFactureElement.getField("ID_TAXE"));
201
            sel.addGroupBy(joinFamArt.getJoinedTable().getField("NOM"));
202
            sel.addFieldOrder(joinFamArt.getJoinedTable().getField("NOM"));
203
            System.err.println(sel.asString());
58 ilm 204
 
149 ilm 205
            // Requete pour obtenir les quantités vendus
206
            SQLSelect selQte = new SQLSelect();
207
            selQte.addSelect(tableFactureElement.getField("CODE"));
208
            selQte.addSelect(tableFactureElement.getField("NOM"));
209
            selQte.addSelect(tableFactureElement.getField("QTE"), "SUM");
210
            selQte.addSelect(tableFactureElement.getField("T_PA_HT"), "SUM");
211
            selQte.addSelect(tableFactureElement.getField("T_PV_HT"), "SUM");
212
            selQte.addSelect(tableFactureElement.getField("T_PV_TTC"), "SUM");
213
            selQte.addSelect(tableFactureElement.getField("ID_TAXE"));
214
            selQte.addSelect(tableFactureElement.getField("ID_ARTICLE"));
174 ilm 215
            if (this.ticketCaisse) {
216
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(Where.FALSE);
217
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
218
            } else if (this.facture) {
149 ilm 219
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
174 ilm 220
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(Where.FALSE);
149 ilm 221
            } else {
174 ilm 222
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
223
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
149 ilm 224
            }
225
            SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
226
            SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
227
            selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
228
            // FIXME traiter le cas du!=null et au==null et vice
229
            // versa
230
            if (this.du != null && this.au != null) {
231
                selQte.setWhere(w3.or(w4).and(wN));
232
            } else {
233
                selQte.setWhere(wN);
234
            }
235
            selQte.addGroupBy(tableFactureElement.getField("NOM"));
236
            selQte.addGroupBy(tableFactureElement.getField("CODE"));
237
            selQte.addGroupBy(tableFactureElement.getField("ID_TAXE"));
238
            selQte.addGroupBy(tableFactureElement.getField("ID_ARTICLE"));
239
            selQte.addGroupBy(joinFamArt2.getJoinedTable().getField("NOM"));
18 ilm 240
 
149 ilm 241
            List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selQte.asString(), new ArrayListHandler());
58 ilm 242
 
149 ilm 243
            // Récupération des quantités et des montant totaux pour chaque article
244
            Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>();
245
            for (Object[] sqlRow : listeQte) {
246
                String code = (String) sqlRow[0];
247
                String nom = (String) sqlRow[1];
248
                Number qteVendu = (Number) sqlRow[2];
249
                Number ha = (Number) sqlRow[3];
250
                Number ht = (Number) sqlRow[4];
251
                BigDecimal ttc = (BigDecimal) sqlRow[5];
252
                Number tvaID = (Number) sqlRow[6];
253
                if (!mapTVAVT.containsKey(tvaID.intValue())) {
254
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create((BigDecimal) ht, ttc));
255
                } else {
256
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
257
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().add((BigDecimal) ht), t.get1().add(ttc)));
258
                }
259
                Number articleID = (Number) sqlRow[7];
174 ilm 260
                ArticleVendu a = new ArticleVendu(code, nom, qteVendu.intValue(), (BigDecimal) ht, (BigDecimal) ha, ttc, tvaID.intValue(), foreignTableArticle.getRow(articleID.intValue()));
261
 
149 ilm 262
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
58 ilm 263
 
149 ilm 264
            }
58 ilm 265
 
149 ilm 266
            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
18 ilm 267
 
149 ilm 268
            if (listeIds == null) {
269
                return;
270
            }
58 ilm 271
 
149 ilm 272
            String famille = null;
273
 
274
            for (Object[] obj : listeIds) {
275
                final Object valueFam = obj[4];
276
                if ((valueFam == null && famille == null) || (famille != null && !famille.equalsIgnoreCase("Non classés") && (valueFam == null || valueFam.toString().trim().length() == 0))) {
277
                    famille = "Non classés";
278
                    Map<String, Object> mValues = new HashMap<String, Object>();
279
                    mValues.put("NOM", famille);
280
                    style.put(listValues.size(), "Titre 1");
281
                    listValues.add(mValues);
174 ilm 282
                    styleStock.put(listValuesStock.size(), "Titre 1");
283
                    listValuesStock.add(mValues);
149 ilm 284
                } else if (valueFam != null && !valueFam.toString().equalsIgnoreCase(famille)) {
285
                    famille = valueFam.toString();
286
                    Map<String, Object> mValues = new HashMap<String, Object>();
287
                    mValues.put("NOM", famille);
288
                    style.put(listValues.size(), "Titre 1");
289
                    listValues.add(mValues);
174 ilm 290
                    styleStock.put(listValuesStock.size(), "Titre 1");
291
                    listValuesStock.add(mValues);
149 ilm 292
                }
293
 
294
                Map<String, Object> mValues = new HashMap<String, Object>();
295
 
296
                String code = (String) obj[0];
297
                String nom = (String) obj[1];
298
                Number articleID = (Number) obj[2];
299
                Number taxeID = (Number) obj[3];
300
                ArticleVendu a = map.get(articleID + "##" + code + "##" + nom + "##" + taxeID);
301
                if (a == null) {
302
                    System.err.println("Aucune correspondance pour l'article " + articleID + "##" + code + "##" + nom + "##" + taxeID);
303
                }
304
                if (a != null && a.ttc != null && (a.ttc.signum() != 0 || (articleID != null && articleID.intValue() > 1))) {
305
                    mValues.put("CODE", code);
306
                    mValues.put("NOM", nom);
307
                    mValues.put("QTE", a.qte);
308
                    mValues.put("T_PA", a.ha);
309
                    mValues.put("T_PV_HT", a.ht);
310
                    mValues.put("TVA_TAUX", a.tva);
311
                    mValues.put("NUMERO_COMPTE", a.numeroCompte);
312
                    mValues.put("T_PV_TTC", a.ttc);
313
                    mValues.put("NB_CHEQUE", obj[5]);
314
                    mValues.put("NB_CB", obj[6]);
315
                    mValues.put("NB_ESPECES", obj[7]);
316
                    mValues.put("FAMILLE", valueFam);
317
                    totalTPA = totalTPA.add(a.ha);
318
                    totalTPVTTC = totalTPVTTC.add(a.ttc);
319
                    style.put(listValues.size(), "Normal");
320
                    listValues.add(mValues);
174 ilm 321
 
322
                    Map<String, Object> mValuesStock = new HashMap<String, Object>();
323
                    mValuesStock.put("CODE", code);
324
                    mValuesStock.put("NOM", nom);
325
                    mValuesStock.put("QTE", a.qte);
326
                    if (mapStock.containsKey(articleID)) {
327
                        SQLRowValues rowValsArt = mapStock.get(articleID);
328
                        if (rowValsArt.getObject("ID_STOCK") != null && !rowValsArt.isForeignEmpty("ID_STOCK")) {
329
                            SQLRowAccessor rowValsStock = rowValsArt.getForeign("ID_STOCK");
330
                            mValuesStock.put("QTE_TH", rowValsStock.getObject("QTE_TH"));
331
                            mValuesStock.put("QTE_REEL", rowValsStock.getObject("QTE_REEL"));
332
                            mValuesStock.put("QTE_MIN", rowValsStock.getObject("QTE_MIN"));
333
                            mValuesStock.put("QTE_RECEPT_ATTENTE", rowValsStock.getObject("QTE_RECEPT_ATTENTE"));
334
                            mValuesStock.put("QTE_LIV_ATTENTE", rowValsStock.getObject("QTE_LIV_ATTENTE"));
335
                        }
336
                        styleStock.put(listValuesStock.size(), "Normal");
337
                        listValuesStock.add(mValuesStock);
338
                    }
339
 
149 ilm 340
                }
341
                // System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
342
            }
18 ilm 343
        }
344
        // Liste des ventes comptoirs
58 ilm 345
        final SQLTable venteComptoirT = directory.getElement("SAISIE_VENTE_COMPTOIR").getTable();
149 ilm 346
        SQLSelect selVC = new SQLSelect();
18 ilm 347
        selVC.addSelect(venteComptoirT.getField("NOM"));
348
        selVC.addSelect(venteComptoirT.getField("MONTANT_HT"), "SUM");
349
        selVC.addSelect(venteComptoirT.getField("MONTANT_TTC"), "SUM");
350
        selVC.addSelect(venteComptoirT.getField("NOM"), "COUNT");
149 ilm 351
        selVC.addSelect(venteComptoirT.getField("ID_TAXE"));
80 ilm 352
 
353
        if (this.du != null && this.au != null) {
354
            Where wVC = new Where(venteComptoirT.getField("DATE"), this.du, this.au);
355
            wVC = wVC.and(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
356
            selVC.setWhere(wVC);
357
        } else {
358
            selVC.setWhere(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
359
        }
360
        // FIXME traiter le cas du!=null et au==null et vice versa
18 ilm 361
        selVC.addGroupBy(venteComptoirT.getField("NOM"));
149 ilm 362
        selVC.addGroupBy(venteComptoirT.getField("ID_TAXE"));
18 ilm 363
        List<Object[]> listVC = (List<Object[]>) venteComptoirT.getDBSystemRoot().getDataSource().execute(selVC.asString(), new ArrayListHandler());
58 ilm 364
        long totalVCInCents = 0;
18 ilm 365
        if (listVC.size() > 0) {
366
            Map<String, Object> mValues = new HashMap<String, Object>();
367
            mValues.put("NOM", " ");
368
            listValues.add(mValues);
369
 
370
            Map<String, Object> mValues2 = new HashMap<String, Object>();
371
            if (listVC.size() > 1) {
372
                mValues2.put("NOM", "VENTES COMPTOIR");
373
            } else {
374
                mValues2.put("NOM", "VENTE COMPTOIR");
375
            }
376
 
377
            style.put(listValues.size(), "Titre 1");
378
 
379
            listValues.add(mValues2);
380
 
381
        }
58 ilm 382
        for (Object[] rowVenteComptoir : listVC) {
383
            final Map<String, Object> mValues = new HashMap<String, Object>();
384
            // Nom
385
            mValues.put("NOM", rowVenteComptoir[0]);
386
            // HT
149 ilm 387
            final long ht = ((Number) rowVenteComptoir[1]).longValue();
388
            mValues.put("T_PV_HT", ht / 100.0D);
58 ilm 389
            // TTC
390
            final long ttcInCents = ((Number) rowVenteComptoir[2]).longValue();
391
            mValues.put("T_PV_TTC", ttcInCents / 100.0D);
392
            totalVCInCents += ttcInCents;
149 ilm 393
 
394
            final Integer idTaxe = ((Number) rowVenteComptoir[4]).intValue();
395
            mValues.put("ID_TAXE", idTaxe);
396
            if (!mapTVAVT.containsKey(idTaxe)) {
397
                mapTVAVT.put(idTaxe, Tuple2.create(new BigDecimal(ht).movePointLeft(2), new BigDecimal(ttcInCents).movePointLeft(2)));
398
            } else {
399
                Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(idTaxe);
400
                mapTVAVT.put(idTaxe, Tuple2.create(t.get0().add(new BigDecimal(ht).movePointLeft(2)), t.get1().add(new BigDecimal(ttcInCents).movePointLeft(2))));
401
            }
402
 
58 ilm 403
            // Quantité
404
            mValues.put("QTE", rowVenteComptoir[3]);
18 ilm 405
            listValues.add(mValues);
406
        }
149 ilm 407
        // Avoir
408
        {
18 ilm 409
 
149 ilm 410
            SQLTable tableAvoirElement = directory.getElement("AVOIR_CLIENT_ELEMENT").getTable();
411
 
412
            // Requete Pour obtenir les quantités pour chaque type de réglement
413
            SQLSelect sel = new SQLSelect();
414
 
415
            sel.addSelect(tableAvoirElement.getField("CODE"));
416
            sel.addSelect(tableAvoirElement.getField("NOM"));
417
            sel.addSelect(tableAvoirElement.getField("ID_ARTICLE"));
418
            sel.addSelect(tableAvoirElement.getField("ID_TAXE"));
419
 
420
            // Elements assosciés à une facture
421
            sel.addJoin("LEFT", tableAvoirElement.getField("ID_AVOIR_CLIENT"));
422
            SQLSelectJoin joinArt = sel.addJoin("LEFT", tableAvoirElement.getField("ID_ARTICLE"));
423
            SQLSelectJoin joinFamArt = sel.addJoin("LEFT", joinArt.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
424
            sel.addSelect(joinFamArt.getJoinedTable().getField("NOM"));
425
 
426
            Where w4 = new Where(tableAvoirElement.getTable("AVOIR_CLIENT").getField("DATE"), this.du, this.au);
427
            Where wN = new Where(tableAvoirElement.getField("NIVEAU"), "=", 1);
428
            if (this.du != null && this.au != null) {
429
                sel.setWhere(w4.and(wN));
430
            } else {
431
                sel.setWhere(wN);
432
            }
433
            // FIXME traiter le cas du!=null et au==null et vice versa
434
            sel.addGroupBy(tableAvoirElement.getField("NOM"));
435
            sel.addGroupBy(tableAvoirElement.getField("CODE"));
436
            sel.addGroupBy(tableAvoirElement.getField("ID_ARTICLE"));
437
            sel.addGroupBy(tableAvoirElement.getField("ID_TAXE"));
438
            sel.addGroupBy(joinFamArt.getJoinedTable().getField("NOM"));
439
            sel.addFieldOrder(joinFamArt.getJoinedTable().getField("NOM"));
440
            System.err.println(sel.asString());
441
 
442
            // Requete pour obtenir les quantités vendus
443
            SQLSelect selQte = new SQLSelect();
444
            selQte.addSelect(tableAvoirElement.getField("CODE"));
445
            selQte.addSelect(tableAvoirElement.getField("NOM"));
446
            selQte.addSelect(tableAvoirElement.getField("QTE"), "SUM");
447
            selQte.addSelect(tableAvoirElement.getField("T_PA_HT"), "SUM");
448
            selQte.addSelect(tableAvoirElement.getField("T_PV_HT"), "SUM");
449
            selQte.addSelect(tableAvoirElement.getField("T_PV_TTC"), "SUM");
450
            selQte.addSelect(tableAvoirElement.getField("ID_TAXE"));
451
            selQte.addSelect(tableAvoirElement.getField("ID_ARTICLE"));
452
            selQte.addJoin("LEFT", tableAvoirElement.getField("ID_AVOIR_CLIENT"));
453
            SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableAvoirElement.getField("ID_ARTICLE"));
454
            SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
455
            selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
456
            // FIXME traiter le cas du!=null et au==null et vice
457
            // versa
458
            if (this.du != null && this.au != null) {
459
                selQte.setWhere(w4.and(wN));
460
            } else {
461
                selQte.setWhere(wN);
462
            }
463
            selQte.addGroupBy(tableAvoirElement.getField("NOM"));
464
            selQte.addGroupBy(tableAvoirElement.getField("CODE"));
465
            selQte.addGroupBy(tableAvoirElement.getField("ID_TAXE"));
466
            selQte.addGroupBy(tableAvoirElement.getField("ID_ARTICLE"));
467
            selQte.addGroupBy(joinFamArt2.getJoinedTable().getField("NOM"));
468
 
469
            List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selQte.asString(), new ArrayListHandler());
470
 
471
            // Récupération des quantités et des montant totaux pour chaque article
472
            Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>();
473
            for (Object[] sqlRow : listeQte) {
474
                String code = (String) sqlRow[0];
475
                String nom = (String) sqlRow[1];
476
                Number qteVendu = (Number) sqlRow[2];
477
                Number ha = (Number) sqlRow[3];
478
                Number ht = (Number) sqlRow[4];
479
                BigDecimal ttc = (BigDecimal) sqlRow[5];
480
                Number tvaID = (Number) sqlRow[6];
481
                if (!mapTVAVT.containsKey(tvaID.intValue())) {
482
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(((BigDecimal) ht).negate(), ttc.negate()));
483
                } else {
484
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
485
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().subtract((BigDecimal) ht), t.get1().subtract(ttc)));
486
                }
487
                Number articleID = (Number) sqlRow[7];
488
                ArticleVendu a = new ArticleVendu(code, nom, -qteVendu.intValue(), ((BigDecimal) ht).negate(), ((BigDecimal) ha).negate(), ttc.negate(), tvaID.intValue(),
174 ilm 489
                        foreignTableArticle.getRow(articleID.intValue()));
149 ilm 490
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
491
 
492
            }
493
 
494
            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
495
 
496
            if (listeIds == null) {
497
                return;
498
            }
499
 
500
            Map<String, Object> mValuesA = new HashMap<String, Object>();
501
            mValuesA.put("NOM", "AVOIRS");
502
            style.put(listValues.size(), "Titre 1");
503
            listValues.add(mValuesA);
504
 
505
            for (Object[] obj : listeIds) {
506
                final Object valueFam = obj[4];
507
 
508
                Map<String, Object> mValues = new HashMap<String, Object>();
509
 
510
                String code = (String) obj[0];
511
                String nom = (String) obj[1];
512
                Number articleID = (Number) obj[2];
513
                Number taxeID = (Number) obj[3];
514
                ArticleVendu a = map.get(articleID + "##" + code + "##" + nom + "##" + taxeID);
515
                if (a.ttc != null && a.ttc.signum() != 0) {
516
                    mValues.put("CODE", code);
517
                    mValues.put("NOM", nom);
518
                    mValues.put("QTE", a.qte);
519
                    mValues.put("T_PA", a.ha);
520
                    mValues.put("T_PV_HT", a.ht);
521
                    mValues.put("TVA_TAUX", a.tva);
522
                    mValues.put("NUMERO_COMPTE", a.numeroCompte);
523
                    mValues.put("T_PV_TTC", a.ttc);
524
                    mValues.put("FAMILLE", valueFam);
525
                    totalTPA = totalTPA.add(a.ha);
526
                    totalTPVTTC = totalTPVTTC.add(a.ttc);
527
                    style.put(listValues.size(), "Normal");
528
                    listValues.add(mValues);
529
                }
530
                // System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
531
            }
532
        }
533
        totalTPVTTC = totalTPVTTC.add(new BigDecimal(totalVCInCents).movePointLeft(2));
534
 
18 ilm 535
        // Liste des Achats
149 ilm 536
        final ArrayList<Map<String, Object>> listValuesAchat = new ArrayList<Map<String, Object>>();
58 ilm 537
        Map<String, Object> valuesAchat = this.mapAllSheetValues.get(1);
538
        if (valuesAchat == null) {
539
            valuesAchat = new HashMap<String, Object>();
18 ilm 540
        }
149 ilm 541
        // Saisie Achat
542
        long totalAchatInCents = 0;
543
        {
544
            final SQLElement eltAchat = directory.getElement("SAISIE_ACHAT");
545
            final SQLTable tableAchat = eltAchat.getTable();
546
            final SQLSelect selAchat = new SQLSelect();
18 ilm 547
 
149 ilm 548
            selAchat.addSelect(tableAchat.getField("NOM"));
549
            selAchat.addSelect(tableAchat.getField("MONTANT_HT"), "SUM");
550
            selAchat.addSelect(tableAchat.getField("MONTANT_TTC"), "SUM");
551
            selAchat.addSelect(tableAchat.getField("ID_TAXE"));
552
            final Where wHA = new Where(tableAchat.getField("DATE"), this.du, this.au);
553
            selAchat.setWhere(wHA);
554
            selAchat.addGroupBy(tableAchat.getField("NOM"));
555
            selAchat.addGroupBy(tableAchat.getField("ID_TAXE"));
556
            List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());
18 ilm 557
 
149 ilm 558
            for (Object[] row : listAchat) {
559
                Map<String, Object> mValues = new HashMap<String, Object>();
560
                mValues.put("NOM", row[0]);
561
                final Integer tvaID = ((Number) row[3]).intValue();
562
                mValues.put("TAXE", tvaID);
563
                long ht = ((Number) row[1]).longValue();
564
                long pA = ((Number) row[2]).longValue();
565
                if (!mapTVAHA.containsKey(tvaID.intValue())) {
566
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(new BigDecimal(ht).movePointLeft(2), new BigDecimal(pA).movePointLeft(2)));
567
                } else {
568
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAHA.get(tvaID.intValue());
569
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(t.get0().add(new BigDecimal(ht).movePointLeft(2)), t.get1().add(new BigDecimal(pA).movePointLeft(2))));
570
                }
571
                mValues.put("T_PV_HT", -ht / 100.0D);
572
                mValues.put("T_PV_TTC", -pA / 100.0D);
573
                totalAchatInCents -= pA;
574
                listValuesAchat.add(mValues);
575
            }
576
        }
577
        // Facture Fournisseur
578
        {
579
            final SQLElement eltFactElt = directory.getElement("FACTURE_FOURNISSEUR_ELEMENT");
580
            final SQLTable tableFactElt = eltFactElt.getTable();
581
            final SQLTable tableFactF = eltFactElt.getTable().getForeignTable("ID_FACTURE_FOURNISSEUR");
582
            final SQLSelect selAchat = new SQLSelect();
583
            selAchat.addSelect(tableFactElt.getField("ID_ARTICLE"));
584
            selAchat.addSelect(tableFactElt.getField("CODE"));
585
            selAchat.addSelect(tableFactElt.getField("NOM"));
586
            selAchat.addSelect(tableFactElt.getField("T_PA_HT"), "SUM");
587
            selAchat.addSelect(tableFactElt.getField("T_PA_TTC"), "SUM");
588
            selAchat.addSelect(tableFactElt.getField("ID_TAXE"));
589
            selAchat.addSelect(tableFactElt.getField("QTE"), "SUM");
590
            selAchat.addJoin("LEFT", tableFactElt.getField("ID_FACTURE_FOURNISSEUR"));
591
            selAchat.addGroupBy(tableFactElt.getField("ID_ARTICLE"));
592
            selAchat.addGroupBy(tableFactElt.getField("CODE"));
593
            selAchat.addGroupBy(tableFactElt.getField("NOM"));
594
            selAchat.addGroupBy(tableFactElt.getField("ID_TAXE"));
18 ilm 595
 
149 ilm 596
            Where wFactF = new Where(tableFactF.getField("DATE"), this.du, this.au);
597
            selAchat.setWhere(wFactF.and(new Where(tableFactElt.getField("NIVEAU"), "=", 1)));
598
            List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());
599
 
600
            for (Object[] row : listAchat) {
601
                Map<String, Object> mValues = new HashMap<String, Object>();
602
                mValues.put("NOM", row[2]);
603
                final Integer tvaID = ((Number) row[5]).intValue();
604
                mValues.put("TAXE", tvaID);
605
                BigDecimal ht = ((BigDecimal) row[3]);
606
                BigDecimal pA = ((BigDecimal) row[4]);
607
                if (!mapTVAHA.containsKey(tvaID.intValue())) {
608
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(ht, pA));
609
                } else {
610
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAHA.get(tvaID.intValue());
611
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(t.get0().add(ht), t.get1().add(pA)));
612
                }
613
                mValues.put("T_PV_HT", ht.negate());
614
                mValues.put("T_PV_TTC", pA.negate());
615
                mValues.put("QTE", row[6]);
616
                totalAchatInCents -= pA.movePointRight(2).setScale(0, RoundingMode.HALF_UP).longValue();
617
                listValuesAchat.add(mValues);
618
            }
18 ilm 619
        }
620
 
621
        // Récapitulatif
622
        Map<String, Object> valuesE = this.mapAllSheetValues.get(2);
623
        if (valuesE == null) {
624
            valuesE = new HashMap<String, Object>();
625
        }
58 ilm 626
        SQLElement eltE = directory.getElement("ENCAISSER_MONTANT");
627
        SQLElement eltM = directory.getElement("MODE_REGLEMENT");
628
        SQLElement eltT = directory.getElement("TYPE_REGLEMENT");
149 ilm 629
        SQLSelect selE = new SQLSelect();
18 ilm 630
        selE.addSelect(eltT.getTable().getField("NOM"));
631
        selE.addSelect(eltT.getTable().getField("NOM"), "COUNT");
632
        selE.addSelect(eltE.getTable().getField("MONTANT"), "SUM");
633
        Where wE = new Where(eltE.getTable().getField("DATE"), this.du, this.au);
634
        wE = wE.and(new Where(eltE.getTable().getField("ID_MODE_REGLEMENT"), "=", eltM.getTable().getKey()));
635
        wE = wE.and(new Where(eltM.getTable().getField("ID_TYPE_REGLEMENT"), "=", eltT.getTable().getKey()));
636
        selE.setWhere(wE);
637
        selE.addGroupBy(eltT.getTable().getField("NOM"));
638
        selE.addFieldOrder(eltT.getTable().getField("NOM"));
639
        List<Object[]> listE = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selE.asString(), new ArrayListHandler());
149 ilm 640
        ArrayList<Map<String, Object>> listValuesE = new ArrayList<Map<String, Object>>();
58 ilm 641
        long totalEInCents = 0;
18 ilm 642
 
643
        for (Object[] o : listE) {
644
            Map<String, Object> mValues = new HashMap<String, Object>();
645
 
646
            mValues.put("NOM", o[0]);
647
 
58 ilm 648
            final long pA = ((Number) o[2]).longValue();
18 ilm 649
            mValues.put("QTE", o[1]);
58 ilm 650
            mValues.put("TOTAL", pA / 100.0D);
18 ilm 651
 
58 ilm 652
            totalEInCents += pA;
18 ilm 653
            listValuesE.add(mValues);
654
        }
655
 
656
        Map<String, Object> values = this.mapAllSheetValues.get(0);
657
        if (values == null) {
658
            values = new HashMap<String, Object>();
659
        }
58 ilm 660
        valuesAchat.put("TOTAL", totalAchatInCents / 100f);
661
        valuesE.put("TOTAL_HA", totalAchatInCents / 100f);
662
        valuesE.put("TOTAL", totalEInCents / 100f);
67 ilm 663
        valuesE.put("TOTAL_VT", totalTPVTTC);
58 ilm 664
        values.put("TOTAL", totalVCInCents / 100f);
67 ilm 665
        values.put("TOTAL_MARGE", totalTPVTTC.subtract(totalTPA));
666
        valuesE.put("TOTAL_GLOBAL", totalTPVTTC.add(new BigDecimal(totalAchatInCents).movePointLeft(2)));
667
        values.put("TOTAL_PA", totalTPA);
668
        values.put("TOTAL_PV_TTC", totalTPVTTC);
80 ilm 669
        String periode = "";
93 ilm 670
        final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
80 ilm 671
        if (this.du != null && this.au != null) {
93 ilm 672
            periode = "Période du " + dateFormat.format(this.du) + " au " + dateFormat.format(this.au);
80 ilm 673
        } else if (du == null && au != null) {
93 ilm 674
            periode = "Période jusqu'au " + dateFormat.format(this.au);
675
        } else if (du != null && au == null) {
676
            periode = "Période depuis le " + dateFormat.format(this.du);
80 ilm 677
        }
18 ilm 678
 
679
        values.put("DATE", periode);
58 ilm 680
        valuesAchat.put("DATE", periode);
18 ilm 681
        valuesE.put("DATE", periode);
149 ilm 682
 
683
        Map<String, Object> valuesTotalVT = new HashMap<String, Object>();
684
        valuesTotalVT.put("T_MARGE", "Total");
685
        valuesTotalVT.put("T_PV_TTC", totalTPVTTC);
686
        style.put(listValues.size(), "Titre 2");
687
        listValues.add(valuesTotalVT);
18 ilm 688
        System.err.println(this.du);
689
        System.err.println(this.au);
690
        this.listAllSheetValues.put(0, listValues);
691
        this.mapAllSheetValues.put(0, values);
692
 
58 ilm 693
        this.listAllSheetValues.put(1, listValuesAchat);
694
        this.mapAllSheetValues.put(1, valuesAchat);
18 ilm 695
 
696
        this.listAllSheetValues.put(2, listValuesE);
697
        this.mapAllSheetValues.put(2, valuesE);
698
 
149 ilm 699
        // Recap TVA
700
        Map<Integer, String> styleTVA = styleAllSheetValues.get(3);
701
        if (styleTVA == null) {
702
            styleTVA = new HashMap<Integer, String>();
703
            styleAllSheetValues.put(3, styleTVA);
704
        }
705
        ArrayList<Map<String, Object>> listValuesTVA = new ArrayList<Map<String, Object>>();
706
        BigDecimal totalHT = BigDecimal.ZERO;
707
        BigDecimal totalTTC = BigDecimal.ZERO;
708
        BigDecimal totalHTHA = BigDecimal.ZERO;
709
        BigDecimal totalTTCHA = BigDecimal.ZERO;
710
        for (Integer idTaxe : mapTVAVT.keySet()) {
711
            Map<String, Object> vals = new HashMap<String, Object>();
712
            vals.put("VT_TAUX_TVA", TaxeCache.getCache().getTauxFromId(idTaxe));
713
            BigDecimal ht = mapTVAVT.get(idTaxe).get0();
714
            BigDecimal ttc = mapTVAVT.get(idTaxe).get1();
715
            totalHT = totalHT.add(ht);
716
            totalTTC = totalTTC.add(ttc);
717
            vals.put("VT_HT", ht);
718
            vals.put("VT_TVA", ttc.subtract(ht));
719
            vals.put("VT_TTC", ttc);
720
            if (mapTVAHA.containsKey(idTaxe)) {
721
                BigDecimal haht = mapTVAHA.get(idTaxe).get0();
722
                BigDecimal hattc = mapTVAHA.get(idTaxe).get1();
723
                vals.put("HA_HT", haht);
724
                vals.put("HA_TVA", hattc.subtract(haht));
725
                vals.put("HA_TTC", hattc);
726
                totalHTHA = totalHTHA.add(haht);
727
                totalTTCHA = totalTTCHA.add(hattc);
728
                mapTVAHA.remove(idTaxe);
729
            }
730
            styleTVA.put(listValuesTVA.size(), "Normal");
731
            listValuesTVA.add(vals);
732
        }
733
        for (Integer idTaxe : mapTVAHA.keySet()) {
734
            Map<String, Object> vals = new HashMap<String, Object>();
735
            BigDecimal haht = mapTVAHA.get(idTaxe).get0();
736
            BigDecimal hattc = mapTVAHA.get(idTaxe).get1();
737
            vals.put("VT_TAUX_TVA", TaxeCache.getCache().getTauxFromId(idTaxe));
738
            vals.put("HA_HT", haht);
739
            vals.put("HA_TVA", hattc.subtract(haht));
740
            vals.put("HA_TTC", hattc);
741
            totalHTHA = totalHTHA.add(haht);
742
            totalTTCHA = totalTTCHA.add(hattc);
743
            styleTVA.put(listValuesTVA.size(), "Normal");
744
            listValuesTVA.add(vals);
745
        }
746
        Map<String, Object> vals = new HashMap<String, Object>();
747
        vals.put("VT_TAUX_TVA", "Total");
748
        vals.put("VT_HT", totalHT);
749
        vals.put("VT_TVA", totalTTC.subtract(totalHT));
750
        vals.put("VT_TTC", totalTTC);
751
        vals.put("HA_HT", totalHTHA);
752
        vals.put("HA_TVA", totalTTCHA.subtract(totalHTHA));
753
        vals.put("HA_TTC", totalTTCHA);
754
        styleTVA.put(listValuesTVA.size(), "Titre 1");
755
        listValuesTVA.add(vals);
756
 
757
        Map<String, Object> valuesTVA = new HashMap<String, Object>();
758
        valuesTVA.put("TOTAL_TVA", totalTTC.subtract(totalHT).subtract(totalTTCHA.subtract(totalHTHA)));
759
        this.listAllSheetValues.put(3, listValuesTVA);
760
        valuesTVA.put("DATE", periode);
761
        this.mapAllSheetValues.put(3, valuesTVA);
174 ilm 762
 
763
        this.listAllSheetValues.put(4, listValuesStock);
764
        this.styleAllSheetValues.put(4, styleStock);
765
        this.mapAllSheetValues.put(4, values);
18 ilm 766
    }
767
 
142 ilm 768
    public static SQLRow rowDefaultCptService, rowDefaultCptProduit;
769
    static {
770
        final SQLTable tablePrefCompte = Configuration.getInstance().getRoot().findTable("PREFS_COMPTE");
771
        final SQLRow rowPrefsCompte = tablePrefCompte.getRow(2);
772
        rowDefaultCptService = rowPrefsCompte.getForeign("ID_COMPTE_PCE_VENTE_SERVICE");
773
        if (rowDefaultCptService == null || rowDefaultCptService.isUndefined()) {
774
            try {
775
                rowDefaultCptService = ComptePCESQLElement.getRowComptePceDefault("VentesServices");
776
            } catch (Exception e) {
777
                e.printStackTrace();
778
            }
779
        }
780
 
781
        rowDefaultCptProduit = rowPrefsCompte.getForeign("ID_COMPTE_PCE_VENTE_PRODUIT");
782
        if (rowDefaultCptProduit == null || rowDefaultCptProduit.isUndefined()) {
783
            try {
784
                rowDefaultCptProduit = ComptePCESQLElement.getRowComptePceDefault("VentesProduits");
785
            } catch (Exception e) {
786
                e.printStackTrace();
787
            }
788
        }
789
    }
790
 
58 ilm 791
    class ArticleVendu {
792
        public String code, nom;
142 ilm 793
        public int qte, tvaID;
794
        public BigDecimal ht, ha, ttc, tva;
795
        public String numeroCompte;
58 ilm 796
 
142 ilm 797
        public ArticleVendu(String code, String nom, int qte, BigDecimal ht, BigDecimal ha, BigDecimal ttc, int tvaID, SQLRow rowArticle) {
58 ilm 798
            this.code = code;
799
            this.nom = nom;
800
            this.qte = qte;
801
            this.ht = ht;
802
            this.ha = ha;
803
            this.ttc = ttc;
142 ilm 804
            this.tvaID = tvaID;
805
            this.tva = new BigDecimal(TaxeCache.getCache().getTauxFromId(tvaID));
806
 
807
            SQLRowAccessor rowTVA = TaxeCache.getCache().getRowFromId(tvaID);
808
            boolean service = rowArticle.getBoolean("SERVICE");
809
            SQLRowAccessor rowCpt;
810
            // Total Service
811
            if (service) {
812
                rowCpt = rowDefaultCptService;
813
                if (rowTVA != null && !rowTVA.isForeignEmpty("ID_COMPTE_PCE_VENTE_SERVICE")) {
814
                    rowCpt = rowTVA.getForeign("ID_COMPTE_PCE_VENTE_SERVICE");
815
                }
816
            } else {
817
                rowCpt = rowDefaultCptProduit;
818
                // Compte defini par défaut dans la TVA
819
                if (rowTVA != null && !rowTVA.isForeignEmpty("ID_COMPTE_PCE_VENTE")) {
820
                    rowCpt = rowTVA.getForeign("ID_COMPTE_PCE_VENTE");
821
                }
822
 
823
            }
824
 
825
            if (rowArticle != null && !rowArticle.isUndefined()) {
826
                SQLRowAccessor compteArticle = rowArticle.getForeign("ID_COMPTE_PCE");
827
                if (compteArticle != null && !compteArticle.isUndefined()) {
828
                    rowCpt = compteArticle;
829
                } else {
830
                    SQLRowAccessor familleArticle = rowArticle.getForeign("ID_FAMILLE_ARTICLE");
831
                    Set<SQLRowAccessor> unique = new HashSet<SQLRowAccessor>();
832
                    while (familleArticle != null && !familleArticle.isUndefined() && !unique.contains(familleArticle)) {
833
 
834
                        unique.add(familleArticle);
835
                        SQLRowAccessor compteFamilleArticle = familleArticle.getForeign("ID_COMPTE_PCE");
836
                        if (compteFamilleArticle != null && !compteFamilleArticle.isUndefined()) {
837
                            rowCpt = compteFamilleArticle;
838
                            break;
839
                        }
840
 
841
                        familleArticle = familleArticle.getForeign("ID_FAMILLE_ARTICLE_PERE");
842
                    }
843
                }
844
            }
845
            if (rowCpt != null) {
846
 
847
                this.numeroCompte = rowCpt.getString("NUMERO");
848
            }
58 ilm 849
        }
850
    }
18 ilm 851
}