OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 149 | 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 
 
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 
}