OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 149 | Blame | Compare with Previous | Last modification | View Log | RSS feed

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.erp.generationDoc.gestcomm;

import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
import org.openconcerto.erp.core.finance.tax.model.TaxeCache;
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
import org.openconcerto.erp.preferences.PrinterNXProps;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.element.SQLElement;
import org.openconcerto.sql.element.SQLElementDirectory;
import org.openconcerto.sql.model.AliasedField;
import org.openconcerto.sql.model.AliasedTable;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowAccessor;
import org.openconcerto.sql.model.SQLRowValues;
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLSelectJoin;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.Tuple2;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

/**
 * Statistique des ventes d'articles
 * 
 */
public class EtatVentesXmlSheet extends AbstractListeSheetXml {

    private static final String MODE2 = "mod2";

    private static final String MODE1 = "mod1";

    public static final String TEMPLATE_ID = "EtatVentes";

    public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;

    private Timestamp du, au;
    public boolean ticketCaisse = false;
    public boolean facture = false;

    public EtatVentesXmlSheet(Date du, Date au, boolean ticketCaisse, boolean facture) {
        super();
        this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
        this.ticketCaisse = ticketCaisse;
        this.facture = facture;
        if (du != null) {
            final Calendar c1 = Calendar.getInstance();
            c1.setTime(du);
            c1.set(Calendar.HOUR_OF_DAY, 0);
            c1.set(Calendar.MINUTE, 0);
            c1.set(Calendar.SECOND, 0);
            this.du = new Timestamp(c1.getTimeInMillis());
        }
        if (au != null) {
            final Calendar c2 = Calendar.getInstance();
            c2.setTime(au);
            c2.set(Calendar.HOUR_OF_DAY, 23);
            c2.set(Calendar.MINUTE, 59);
            c2.set(Calendar.SECOND, 59);
            this.au = new Timestamp(c2.getTimeInMillis());
        }
    }

    @Override
    public String getDefaultTemplateId() {
        return TEMPLATE_ID;
    }

    @Override
    protected String getStoragePathP() {
        return "Etat Ventes";
    }

    Date d;

    @Override
    public String getName() {
        if (d == null) {
            d = new Date();
        }
        return "EtatVentes" + d.getTime();
    }

    protected void createListeValues() {
        Map<Integer, Tuple2<BigDecimal, BigDecimal>> mapTVAVT = new HashMap<Integer, Tuple2<BigDecimal, BigDecimal>>();
        Map<Integer, Tuple2<BigDecimal, BigDecimal>> mapTVAHA = new HashMap<Integer, Tuple2<BigDecimal, BigDecimal>>();
        final SQLElementDirectory directory = Configuration.getInstance().getDirectory();
        final SQLElement eltVenteFacutreElement = directory.getElement("SAISIE_VENTE_FACTURE_ELEMENT");
        final SQLElement eltVenteFacture = directory.getElement("SAISIE_VENTE_FACTURE");
        final SQLElement eltEncaissement = directory.getElement("ENCAISSER_MONTANT");
        final SQLElement eltTicketCaisse = directory.getElement("TICKET_CAISSE");
        final SQLElement eltModeReglement = directory.getElement("MODE_REGLEMENT");
        final SQLTable tableModeReglement = eltModeReglement.getTable();
        final SQLTable tableFactureElement = eltVenteFacutreElement.getTable();
        final SQLTable tableFacture = eltVenteFacture.getTable();
        BigDecimal totalTPVTTC = BigDecimal.ZERO;
        BigDecimal totalTPA = BigDecimal.ZERO;

        // Liste des valeurs de la feuille OO
        ArrayList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>();
        Map<Integer, String> style = styleAllSheetValues.get(0);
        if (style == null) {
            style = new HashMap<Integer, String>();
            styleAllSheetValues.put(0, style);
        }

        final ArrayList<Map<String, Object>> listValuesStock = new ArrayList<>();
        final Map<Integer, String> styleStock = new HashMap<>();

        // Ventes
        final SQLTable foreignTableArticle = tableFactureElement.getForeignTable("ID_ARTICLE");
        {

            final AliasedTable tableModeReglement1 = new AliasedTable(tableModeReglement, MODE1);
            final AliasedTable tableModeReglement2 = new AliasedTable(tableModeReglement, MODE2);
            final AliasedTable tableTicket = new AliasedTable(eltTicketCaisse.getTable(), "ticket");

            // Stock
            SQLRowValues rowValsArtStock = new SQLRowValues(foreignTableArticle);
            rowValsArtStock.putNulls("ID", "CODE", "NOM");
            rowValsArtStock.putRowValues("ID_STOCK").putNulls("QTE_REEL", "QTE_TH", "QTE_MIN", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
            SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(rowValsArtStock);
            List<SQLRowValues> resultStock = fetcherStock.fetch();
            Map<Integer, SQLRowValues> mapStock = new HashMap<>();
            for (SQLRowValues sqlRowValues : resultStock) {
                mapStock.put(sqlRowValues.getID(), sqlRowValues);
            }

            // Requete Pour obtenir les quantités pour chaque type de réglement
            SQLSelect sel = new SQLSelect();

            sel.addSelect(tableFactureElement.getField("CODE"));
            sel.addSelect(tableFactureElement.getField("NOM"));
            sel.addSelect(tableFactureElement.getField("ID_ARTICLE"));
            sel.addSelect(tableFactureElement.getField("ID_TAXE"));

            // Elements assosciés à une facture
            Where w = new Where(tableFactureElement.getField("ID_TICKET_CAISSE"), "=", tableTicket.getTable().getUndefinedID());
            sel.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
            SQLSelectJoin joinArt = sel.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
            SQLSelectJoin joinFamArt = sel.addJoin("LEFT", joinArt.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
            sel.addSelect(joinFamArt.getJoinedTable().getField("NOM"));

            // Elements associés à un ticket de caisse
            Where w2 = new Where(tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE"), "=", 1);
            sel.addJoin("LEFT", tableFacture.getField("ID_MODE_REGLEMENT"), MODE1);
            sel.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);

            sel.addBackwardJoin("LEFT", "enc", eltEncaissement.getTable().getField("ID_TICKET_CAISSE"), "ticket");
            sel.addJoin("LEFT", new AliasedField(eltEncaissement.getTable().getField("ID_MODE_REGLEMENT"), "enc"), MODE2);

            final String idTypeReglement1 = tableModeReglement1.getField("ID_TYPE_REGLEMENT").getFieldRef();
            final String idTypeReglement2 = tableModeReglement2.getField("ID_TYPE_REGLEMENT").getFieldRef();

            final String qte = sel.getAlias(tableFactureElement.getField("QTE")).getFieldRef();
            sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=2 OR " + idTypeReglement2 + "=2 THEN " + qte + " ELSE 0 END)", "Cheque");
            sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=3 OR " + idTypeReglement2 + "=3 THEN " + qte + " ELSE 0 END)", "CB");
            sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=4 OR " + idTypeReglement2 + "=4 THEN " + qte + " ELSE 0 END)", "Especes");

            Where w3 = new Where(tableTicket.getField("DATE"), this.du, this.au);
            Where w4 = new Where(tableFacture.getField("DATE"), this.du, this.au);
            Where wN = new Where(tableFactureElement.getField("NIVEAU"), "=", 1);
            if (this.du != null && this.au != null) {
                sel.setWhere(w3.or(w4).and(wN));
            } else {
                sel.setWhere(wN);
            }
            // FIXME traiter le cas du!=null et au==null et vice versa
            sel.addGroupBy(tableFactureElement.getField("NOM"));
            sel.addGroupBy(tableFactureElement.getField("CODE"));
            sel.addGroupBy(tableFactureElement.getField("ID_ARTICLE"));
            sel.addGroupBy(tableFactureElement.getField("ID_TAXE"));
            sel.addGroupBy(joinFamArt.getJoinedTable().getField("NOM"));
            sel.addFieldOrder(joinFamArt.getJoinedTable().getField("NOM"));
            System.err.println(sel.asString());

            // Requete pour obtenir les quantités vendus
            SQLSelect selQte = new SQLSelect();
            selQte.addSelect(tableFactureElement.getField("CODE"));
            selQte.addSelect(tableFactureElement.getField("NOM"));
            selQte.addSelect(tableFactureElement.getField("QTE"), "SUM");
            selQte.addSelect(tableFactureElement.getField("T_PA_HT"), "SUM");
            selQte.addSelect(tableFactureElement.getField("T_PV_HT"), "SUM");
            selQte.addSelect(tableFactureElement.getField("T_PV_TTC"), "SUM");
            selQte.addSelect(tableFactureElement.getField("ID_TAXE"));
            selQte.addSelect(tableFactureElement.getField("ID_ARTICLE"));
            if (this.ticketCaisse) {
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(Where.FALSE);
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
            } else if (this.facture) {
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(Where.FALSE);
            } else {
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
                selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
            }
            SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
            SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
            selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
            // FIXME traiter le cas du!=null et au==null et vice
            // versa
            if (this.du != null && this.au != null) {
                selQte.setWhere(w3.or(w4).and(wN));
            } else {
                selQte.setWhere(wN);
            }
            selQte.addGroupBy(tableFactureElement.getField("NOM"));
            selQte.addGroupBy(tableFactureElement.getField("CODE"));
            selQte.addGroupBy(tableFactureElement.getField("ID_TAXE"));
            selQte.addGroupBy(tableFactureElement.getField("ID_ARTICLE"));
            selQte.addGroupBy(joinFamArt2.getJoinedTable().getField("NOM"));

            List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selQte.asString(), new ArrayListHandler());

            // Récupération des quantités et des montant totaux pour chaque article
            Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>();
            for (Object[] sqlRow : listeQte) {
                String code = (String) sqlRow[0];
                String nom = (String) sqlRow[1];
                Number qteVendu = (Number) sqlRow[2];
                Number ha = (Number) sqlRow[3];
                Number ht = (Number) sqlRow[4];
                BigDecimal ttc = (BigDecimal) sqlRow[5];
                Number tvaID = (Number) sqlRow[6];
                if (!mapTVAVT.containsKey(tvaID.intValue())) {
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create((BigDecimal) ht, ttc));
                } else {
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().add((BigDecimal) ht), t.get1().add(ttc)));
                }
                Number articleID = (Number) sqlRow[7];
                ArticleVendu a = new ArticleVendu(code, nom, qteVendu.intValue(), (BigDecimal) ht, (BigDecimal) ha, ttc, tvaID.intValue(), foreignTableArticle.getRow(articleID.intValue()));

                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);

            }

            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());

            if (listeIds == null) {
                return;
            }

            String famille = null;

            for (Object[] obj : listeIds) {
                final Object valueFam = obj[4];
                if ((valueFam == null && famille == null) || (famille != null && !famille.equalsIgnoreCase("Non classés") && (valueFam == null || valueFam.toString().trim().length() == 0))) {
                    famille = "Non classés";
                    Map<String, Object> mValues = new HashMap<String, Object>();
                    mValues.put("NOM", famille);
                    style.put(listValues.size(), "Titre 1");
                    listValues.add(mValues);
                    styleStock.put(listValuesStock.size(), "Titre 1");
                    listValuesStock.add(mValues);
                } else if (valueFam != null && !valueFam.toString().equalsIgnoreCase(famille)) {
                    famille = valueFam.toString();
                    Map<String, Object> mValues = new HashMap<String, Object>();
                    mValues.put("NOM", famille);
                    style.put(listValues.size(), "Titre 1");
                    listValues.add(mValues);
                    styleStock.put(listValuesStock.size(), "Titre 1");
                    listValuesStock.add(mValues);
                }

                Map<String, Object> mValues = new HashMap<String, Object>();

                String code = (String) obj[0];
                String nom = (String) obj[1];
                Number articleID = (Number) obj[2];
                Number taxeID = (Number) obj[3];
                ArticleVendu a = map.get(articleID + "##" + code + "##" + nom + "##" + taxeID);
                if (a == null) {
                    System.err.println("Aucune correspondance pour l'article " + articleID + "##" + code + "##" + nom + "##" + taxeID);
                }
                if (a != null && a.ttc != null && (a.ttc.signum() != 0 || (articleID != null && articleID.intValue() > 1))) {
                    mValues.put("CODE", code);
                    mValues.put("NOM", nom);
                    mValues.put("QTE", a.qte);
                    mValues.put("T_PA", a.ha);
                    mValues.put("T_PV_HT", a.ht);
                    mValues.put("TVA_TAUX", a.tva);
                    mValues.put("NUMERO_COMPTE", a.numeroCompte);
                    mValues.put("T_PV_TTC", a.ttc);
                    mValues.put("NB_CHEQUE", obj[5]);
                    mValues.put("NB_CB", obj[6]);
                    mValues.put("NB_ESPECES", obj[7]);
                    mValues.put("FAMILLE", valueFam);
                    totalTPA = totalTPA.add(a.ha);
                    totalTPVTTC = totalTPVTTC.add(a.ttc);
                    style.put(listValues.size(), "Normal");
                    listValues.add(mValues);

                    Map<String, Object> mValuesStock = new HashMap<String, Object>();
                    mValuesStock.put("CODE", code);
                    mValuesStock.put("NOM", nom);
                    mValuesStock.put("QTE", a.qte);
                    if (mapStock.containsKey(articleID)) {
                        SQLRowValues rowValsArt = mapStock.get(articleID);
                        if (rowValsArt.getObject("ID_STOCK") != null && !rowValsArt.isForeignEmpty("ID_STOCK")) {
                            SQLRowAccessor rowValsStock = rowValsArt.getForeign("ID_STOCK");
                            mValuesStock.put("QTE_TH", rowValsStock.getObject("QTE_TH"));
                            mValuesStock.put("QTE_REEL", rowValsStock.getObject("QTE_REEL"));
                            mValuesStock.put("QTE_MIN", rowValsStock.getObject("QTE_MIN"));
                            mValuesStock.put("QTE_RECEPT_ATTENTE", rowValsStock.getObject("QTE_RECEPT_ATTENTE"));
                            mValuesStock.put("QTE_LIV_ATTENTE", rowValsStock.getObject("QTE_LIV_ATTENTE"));
                        }
                        styleStock.put(listValuesStock.size(), "Normal");
                        listValuesStock.add(mValuesStock);
                    }

                }
                // System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
            }
        }
        // Liste des ventes comptoirs
        final SQLTable venteComptoirT = directory.getElement("SAISIE_VENTE_COMPTOIR").getTable();
        SQLSelect selVC = new SQLSelect();
        selVC.addSelect(venteComptoirT.getField("NOM"));
        selVC.addSelect(venteComptoirT.getField("MONTANT_HT"), "SUM");
        selVC.addSelect(venteComptoirT.getField("MONTANT_TTC"), "SUM");
        selVC.addSelect(venteComptoirT.getField("NOM"), "COUNT");
        selVC.addSelect(venteComptoirT.getField("ID_TAXE"));

        if (this.du != null && this.au != null) {
            Where wVC = new Where(venteComptoirT.getField("DATE"), this.du, this.au);
            wVC = wVC.and(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
            selVC.setWhere(wVC);
        } else {
            selVC.setWhere(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
        }
        // FIXME traiter le cas du!=null et au==null et vice versa
        selVC.addGroupBy(venteComptoirT.getField("NOM"));
        selVC.addGroupBy(venteComptoirT.getField("ID_TAXE"));
        List<Object[]> listVC = (List<Object[]>) venteComptoirT.getDBSystemRoot().getDataSource().execute(selVC.asString(), new ArrayListHandler());
        long totalVCInCents = 0;
        if (listVC.size() > 0) {
            Map<String, Object> mValues = new HashMap<String, Object>();
            mValues.put("NOM", " ");
            listValues.add(mValues);

            Map<String, Object> mValues2 = new HashMap<String, Object>();
            if (listVC.size() > 1) {
                mValues2.put("NOM", "VENTES COMPTOIR");
            } else {
                mValues2.put("NOM", "VENTE COMPTOIR");
            }

            style.put(listValues.size(), "Titre 1");

            listValues.add(mValues2);

        }
        for (Object[] rowVenteComptoir : listVC) {
            final Map<String, Object> mValues = new HashMap<String, Object>();
            // Nom
            mValues.put("NOM", rowVenteComptoir[0]);
            // HT
            final long ht = ((Number) rowVenteComptoir[1]).longValue();
            mValues.put("T_PV_HT", ht / 100.0D);
            // TTC
            final long ttcInCents = ((Number) rowVenteComptoir[2]).longValue();
            mValues.put("T_PV_TTC", ttcInCents / 100.0D);
            totalVCInCents += ttcInCents;

            final Integer idTaxe = ((Number) rowVenteComptoir[4]).intValue();
            mValues.put("ID_TAXE", idTaxe);
            if (!mapTVAVT.containsKey(idTaxe)) {
                mapTVAVT.put(idTaxe, Tuple2.create(new BigDecimal(ht).movePointLeft(2), new BigDecimal(ttcInCents).movePointLeft(2)));
            } else {
                Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(idTaxe);
                mapTVAVT.put(idTaxe, Tuple2.create(t.get0().add(new BigDecimal(ht).movePointLeft(2)), t.get1().add(new BigDecimal(ttcInCents).movePointLeft(2))));
            }

            // Quantité
            mValues.put("QTE", rowVenteComptoir[3]);
            listValues.add(mValues);
        }
        // Avoir
        {

            SQLTable tableAvoirElement = directory.getElement("AVOIR_CLIENT_ELEMENT").getTable();

            // Requete Pour obtenir les quantités pour chaque type de réglement
            SQLSelect sel = new SQLSelect();

            sel.addSelect(tableAvoirElement.getField("CODE"));
            sel.addSelect(tableAvoirElement.getField("NOM"));
            sel.addSelect(tableAvoirElement.getField("ID_ARTICLE"));
            sel.addSelect(tableAvoirElement.getField("ID_TAXE"));

            // Elements assosciés à une facture
            sel.addJoin("LEFT", tableAvoirElement.getField("ID_AVOIR_CLIENT"));
            SQLSelectJoin joinArt = sel.addJoin("LEFT", tableAvoirElement.getField("ID_ARTICLE"));
            SQLSelectJoin joinFamArt = sel.addJoin("LEFT", joinArt.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
            sel.addSelect(joinFamArt.getJoinedTable().getField("NOM"));

            Where w4 = new Where(tableAvoirElement.getTable("AVOIR_CLIENT").getField("DATE"), this.du, this.au);
            Where wN = new Where(tableAvoirElement.getField("NIVEAU"), "=", 1);
            if (this.du != null && this.au != null) {
                sel.setWhere(w4.and(wN));
            } else {
                sel.setWhere(wN);
            }
            // FIXME traiter le cas du!=null et au==null et vice versa
            sel.addGroupBy(tableAvoirElement.getField("NOM"));
            sel.addGroupBy(tableAvoirElement.getField("CODE"));
            sel.addGroupBy(tableAvoirElement.getField("ID_ARTICLE"));
            sel.addGroupBy(tableAvoirElement.getField("ID_TAXE"));
            sel.addGroupBy(joinFamArt.getJoinedTable().getField("NOM"));
            sel.addFieldOrder(joinFamArt.getJoinedTable().getField("NOM"));
            System.err.println(sel.asString());

            // Requete pour obtenir les quantités vendus
            SQLSelect selQte = new SQLSelect();
            selQte.addSelect(tableAvoirElement.getField("CODE"));
            selQte.addSelect(tableAvoirElement.getField("NOM"));
            selQte.addSelect(tableAvoirElement.getField("QTE"), "SUM");
            selQte.addSelect(tableAvoirElement.getField("T_PA_HT"), "SUM");
            selQte.addSelect(tableAvoirElement.getField("T_PV_HT"), "SUM");
            selQte.addSelect(tableAvoirElement.getField("T_PV_TTC"), "SUM");
            selQte.addSelect(tableAvoirElement.getField("ID_TAXE"));
            selQte.addSelect(tableAvoirElement.getField("ID_ARTICLE"));
            selQte.addJoin("LEFT", tableAvoirElement.getField("ID_AVOIR_CLIENT"));
            SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableAvoirElement.getField("ID_ARTICLE"));
            SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
            selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
            // FIXME traiter le cas du!=null et au==null et vice
            // versa
            if (this.du != null && this.au != null) {
                selQte.setWhere(w4.and(wN));
            } else {
                selQte.setWhere(wN);
            }
            selQte.addGroupBy(tableAvoirElement.getField("NOM"));
            selQte.addGroupBy(tableAvoirElement.getField("CODE"));
            selQte.addGroupBy(tableAvoirElement.getField("ID_TAXE"));
            selQte.addGroupBy(tableAvoirElement.getField("ID_ARTICLE"));
            selQte.addGroupBy(joinFamArt2.getJoinedTable().getField("NOM"));

            List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selQte.asString(), new ArrayListHandler());

            // Récupération des quantités et des montant totaux pour chaque article
            Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>();
            for (Object[] sqlRow : listeQte) {
                String code = (String) sqlRow[0];
                String nom = (String) sqlRow[1];
                Number qteVendu = (Number) sqlRow[2];
                Number ha = (Number) sqlRow[3];
                Number ht = (Number) sqlRow[4];
                BigDecimal ttc = (BigDecimal) sqlRow[5];
                Number tvaID = (Number) sqlRow[6];
                if (!mapTVAVT.containsKey(tvaID.intValue())) {
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(((BigDecimal) ht).negate(), ttc.negate()));
                } else {
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
                    mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().subtract((BigDecimal) ht), t.get1().subtract(ttc)));
                }
                Number articleID = (Number) sqlRow[7];
                ArticleVendu a = new ArticleVendu(code, nom, -qteVendu.intValue(), ((BigDecimal) ht).negate(), ((BigDecimal) ha).negate(), ttc.negate(), tvaID.intValue(),
                        foreignTableArticle.getRow(articleID.intValue()));
                map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);

            }

            List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());

            if (listeIds == null) {
                return;
            }

            Map<String, Object> mValuesA = new HashMap<String, Object>();
            mValuesA.put("NOM", "AVOIRS");
            style.put(listValues.size(), "Titre 1");
            listValues.add(mValuesA);

            for (Object[] obj : listeIds) {
                final Object valueFam = obj[4];

                Map<String, Object> mValues = new HashMap<String, Object>();

                String code = (String) obj[0];
                String nom = (String) obj[1];
                Number articleID = (Number) obj[2];
                Number taxeID = (Number) obj[3];
                ArticleVendu a = map.get(articleID + "##" + code + "##" + nom + "##" + taxeID);
                if (a.ttc != null && a.ttc.signum() != 0) {
                    mValues.put("CODE", code);
                    mValues.put("NOM", nom);
                    mValues.put("QTE", a.qte);
                    mValues.put("T_PA", a.ha);
                    mValues.put("T_PV_HT", a.ht);
                    mValues.put("TVA_TAUX", a.tva);
                    mValues.put("NUMERO_COMPTE", a.numeroCompte);
                    mValues.put("T_PV_TTC", a.ttc);
                    mValues.put("FAMILLE", valueFam);
                    totalTPA = totalTPA.add(a.ha);
                    totalTPVTTC = totalTPVTTC.add(a.ttc);
                    style.put(listValues.size(), "Normal");
                    listValues.add(mValues);
                }
                // System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
            }
        }
        totalTPVTTC = totalTPVTTC.add(new BigDecimal(totalVCInCents).movePointLeft(2));

        // Liste des Achats
        final ArrayList<Map<String, Object>> listValuesAchat = new ArrayList<Map<String, Object>>();
        Map<String, Object> valuesAchat = this.mapAllSheetValues.get(1);
        if (valuesAchat == null) {
            valuesAchat = new HashMap<String, Object>();
        }
        // Saisie Achat
        long totalAchatInCents = 0;
        {
            final SQLElement eltAchat = directory.getElement("SAISIE_ACHAT");
            final SQLTable tableAchat = eltAchat.getTable();
            final SQLSelect selAchat = new SQLSelect();

            selAchat.addSelect(tableAchat.getField("NOM"));
            selAchat.addSelect(tableAchat.getField("MONTANT_HT"), "SUM");
            selAchat.addSelect(tableAchat.getField("MONTANT_TTC"), "SUM");
            selAchat.addSelect(tableAchat.getField("ID_TAXE"));
            final Where wHA = new Where(tableAchat.getField("DATE"), this.du, this.au);
            selAchat.setWhere(wHA);
            selAchat.addGroupBy(tableAchat.getField("NOM"));
            selAchat.addGroupBy(tableAchat.getField("ID_TAXE"));
            List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());

            for (Object[] row : listAchat) {
                Map<String, Object> mValues = new HashMap<String, Object>();
                mValues.put("NOM", row[0]);
                final Integer tvaID = ((Number) row[3]).intValue();
                mValues.put("TAXE", tvaID);
                long ht = ((Number) row[1]).longValue();
                long pA = ((Number) row[2]).longValue();
                if (!mapTVAHA.containsKey(tvaID.intValue())) {
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(new BigDecimal(ht).movePointLeft(2), new BigDecimal(pA).movePointLeft(2)));
                } else {
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAHA.get(tvaID.intValue());
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(t.get0().add(new BigDecimal(ht).movePointLeft(2)), t.get1().add(new BigDecimal(pA).movePointLeft(2))));
                }
                mValues.put("T_PV_HT", -ht / 100.0D);
                mValues.put("T_PV_TTC", -pA / 100.0D);
                totalAchatInCents -= pA;
                listValuesAchat.add(mValues);
            }
        }
        // Facture Fournisseur
        {
            final SQLElement eltFactElt = directory.getElement("FACTURE_FOURNISSEUR_ELEMENT");
            final SQLTable tableFactElt = eltFactElt.getTable();
            final SQLTable tableFactF = eltFactElt.getTable().getForeignTable("ID_FACTURE_FOURNISSEUR");
            final SQLSelect selAchat = new SQLSelect();
            selAchat.addSelect(tableFactElt.getField("ID_ARTICLE"));
            selAchat.addSelect(tableFactElt.getField("CODE"));
            selAchat.addSelect(tableFactElt.getField("NOM"));
            selAchat.addSelect(tableFactElt.getField("T_PA_HT"), "SUM");
            selAchat.addSelect(tableFactElt.getField("T_PA_TTC"), "SUM");
            selAchat.addSelect(tableFactElt.getField("ID_TAXE"));
            selAchat.addSelect(tableFactElt.getField("QTE"), "SUM");
            selAchat.addJoin("LEFT", tableFactElt.getField("ID_FACTURE_FOURNISSEUR"));
            selAchat.addGroupBy(tableFactElt.getField("ID_ARTICLE"));
            selAchat.addGroupBy(tableFactElt.getField("CODE"));
            selAchat.addGroupBy(tableFactElt.getField("NOM"));
            selAchat.addGroupBy(tableFactElt.getField("ID_TAXE"));

            Where wFactF = new Where(tableFactF.getField("DATE"), this.du, this.au);
            selAchat.setWhere(wFactF.and(new Where(tableFactElt.getField("NIVEAU"), "=", 1)));
            List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());

            for (Object[] row : listAchat) {
                Map<String, Object> mValues = new HashMap<String, Object>();
                mValues.put("NOM", row[2]);
                final Integer tvaID = ((Number) row[5]).intValue();
                mValues.put("TAXE", tvaID);
                BigDecimal ht = ((BigDecimal) row[3]);
                BigDecimal pA = ((BigDecimal) row[4]);
                if (!mapTVAHA.containsKey(tvaID.intValue())) {
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(ht, pA));
                } else {
                    Tuple2<BigDecimal, BigDecimal> t = mapTVAHA.get(tvaID.intValue());
                    mapTVAHA.put(tvaID.intValue(), Tuple2.create(t.get0().add(ht), t.get1().add(pA)));
                }
                mValues.put("T_PV_HT", ht.negate());
                mValues.put("T_PV_TTC", pA.negate());
                mValues.put("QTE", row[6]);
                totalAchatInCents -= pA.movePointRight(2).setScale(0, RoundingMode.HALF_UP).longValue();
                listValuesAchat.add(mValues);
            }
        }

        // Récapitulatif
        Map<String, Object> valuesE = this.mapAllSheetValues.get(2);
        if (valuesE == null) {
            valuesE = new HashMap<String, Object>();
        }
        SQLElement eltE = directory.getElement("ENCAISSER_MONTANT");
        SQLElement eltM = directory.getElement("MODE_REGLEMENT");
        SQLElement eltT = directory.getElement("TYPE_REGLEMENT");
        SQLSelect selE = new SQLSelect();
        selE.addSelect(eltT.getTable().getField("NOM"));
        selE.addSelect(eltT.getTable().getField("NOM"), "COUNT");
        selE.addSelect(eltE.getTable().getField("MONTANT"), "SUM");
        Where wE = new Where(eltE.getTable().getField("DATE"), this.du, this.au);
        wE = wE.and(new Where(eltE.getTable().getField("ID_MODE_REGLEMENT"), "=", eltM.getTable().getKey()));
        wE = wE.and(new Where(eltM.getTable().getField("ID_TYPE_REGLEMENT"), "=", eltT.getTable().getKey()));
        selE.setWhere(wE);
        selE.addGroupBy(eltT.getTable().getField("NOM"));
        selE.addFieldOrder(eltT.getTable().getField("NOM"));
        List<Object[]> listE = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selE.asString(), new ArrayListHandler());
        ArrayList<Map<String, Object>> listValuesE = new ArrayList<Map<String, Object>>();
        long totalEInCents = 0;

        for (Object[] o : listE) {
            Map<String, Object> mValues = new HashMap<String, Object>();

            mValues.put("NOM", o[0]);

            final long pA = ((Number) o[2]).longValue();
            mValues.put("QTE", o[1]);
            mValues.put("TOTAL", pA / 100.0D);

            totalEInCents += pA;
            listValuesE.add(mValues);
        }

        Map<String, Object> values = this.mapAllSheetValues.get(0);
        if (values == null) {
            values = new HashMap<String, Object>();
        }
        valuesAchat.put("TOTAL", totalAchatInCents / 100f);
        valuesE.put("TOTAL_HA", totalAchatInCents / 100f);
        valuesE.put("TOTAL", totalEInCents / 100f);
        valuesE.put("TOTAL_VT", totalTPVTTC);
        values.put("TOTAL", totalVCInCents / 100f);
        values.put("TOTAL_MARGE", totalTPVTTC.subtract(totalTPA));
        valuesE.put("TOTAL_GLOBAL", totalTPVTTC.add(new BigDecimal(totalAchatInCents).movePointLeft(2)));
        values.put("TOTAL_PA", totalTPA);
        values.put("TOTAL_PV_TTC", totalTPVTTC);
        String periode = "";
        final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
        if (this.du != null && this.au != null) {
            periode = "Période du " + dateFormat.format(this.du) + " au " + dateFormat.format(this.au);
        } else if (du == null && au != null) {
            periode = "Période jusqu'au " + dateFormat.format(this.au);
        } else if (du != null && au == null) {
            periode = "Période depuis le " + dateFormat.format(this.du);
        }

        values.put("DATE", periode);
        valuesAchat.put("DATE", periode);
        valuesE.put("DATE", periode);

        Map<String, Object> valuesTotalVT = new HashMap<String, Object>();
        valuesTotalVT.put("T_MARGE", "Total");
        valuesTotalVT.put("T_PV_TTC", totalTPVTTC);
        style.put(listValues.size(), "Titre 2");
        listValues.add(valuesTotalVT);
        System.err.println(this.du);
        System.err.println(this.au);
        this.listAllSheetValues.put(0, listValues);
        this.mapAllSheetValues.put(0, values);

        this.listAllSheetValues.put(1, listValuesAchat);
        this.mapAllSheetValues.put(1, valuesAchat);

        this.listAllSheetValues.put(2, listValuesE);
        this.mapAllSheetValues.put(2, valuesE);

        // Recap TVA
        Map<Integer, String> styleTVA = styleAllSheetValues.get(3);
        if (styleTVA == null) {
            styleTVA = new HashMap<Integer, String>();
            styleAllSheetValues.put(3, styleTVA);
        }
        ArrayList<Map<String, Object>> listValuesTVA = new ArrayList<Map<String, Object>>();
        BigDecimal totalHT = BigDecimal.ZERO;
        BigDecimal totalTTC = BigDecimal.ZERO;
        BigDecimal totalHTHA = BigDecimal.ZERO;
        BigDecimal totalTTCHA = BigDecimal.ZERO;
        for (Integer idTaxe : mapTVAVT.keySet()) {
            Map<String, Object> vals = new HashMap<String, Object>();
            vals.put("VT_TAUX_TVA", TaxeCache.getCache().getTauxFromId(idTaxe));
            BigDecimal ht = mapTVAVT.get(idTaxe).get0();
            BigDecimal ttc = mapTVAVT.get(idTaxe).get1();
            totalHT = totalHT.add(ht);
            totalTTC = totalTTC.add(ttc);
            vals.put("VT_HT", ht);
            vals.put("VT_TVA", ttc.subtract(ht));
            vals.put("VT_TTC", ttc);
            if (mapTVAHA.containsKey(idTaxe)) {
                BigDecimal haht = mapTVAHA.get(idTaxe).get0();
                BigDecimal hattc = mapTVAHA.get(idTaxe).get1();
                vals.put("HA_HT", haht);
                vals.put("HA_TVA", hattc.subtract(haht));
                vals.put("HA_TTC", hattc);
                totalHTHA = totalHTHA.add(haht);
                totalTTCHA = totalTTCHA.add(hattc);
                mapTVAHA.remove(idTaxe);
            }
            styleTVA.put(listValuesTVA.size(), "Normal");
            listValuesTVA.add(vals);
        }
        for (Integer idTaxe : mapTVAHA.keySet()) {
            Map<String, Object> vals = new HashMap<String, Object>();
            BigDecimal haht = mapTVAHA.get(idTaxe).get0();
            BigDecimal hattc = mapTVAHA.get(idTaxe).get1();
            vals.put("VT_TAUX_TVA", TaxeCache.getCache().getTauxFromId(idTaxe));
            vals.put("HA_HT", haht);
            vals.put("HA_TVA", hattc.subtract(haht));
            vals.put("HA_TTC", hattc);
            totalHTHA = totalHTHA.add(haht);
            totalTTCHA = totalTTCHA.add(hattc);
            styleTVA.put(listValuesTVA.size(), "Normal");
            listValuesTVA.add(vals);
        }
        Map<String, Object> vals = new HashMap<String, Object>();
        vals.put("VT_TAUX_TVA", "Total");
        vals.put("VT_HT", totalHT);
        vals.put("VT_TVA", totalTTC.subtract(totalHT));
        vals.put("VT_TTC", totalTTC);
        vals.put("HA_HT", totalHTHA);
        vals.put("HA_TVA", totalTTCHA.subtract(totalHTHA));
        vals.put("HA_TTC", totalTTCHA);
        styleTVA.put(listValuesTVA.size(), "Titre 1");
        listValuesTVA.add(vals);

        Map<String, Object> valuesTVA = new HashMap<String, Object>();
        valuesTVA.put("TOTAL_TVA", totalTTC.subtract(totalHT).subtract(totalTTCHA.subtract(totalHTHA)));
        this.listAllSheetValues.put(3, listValuesTVA);
        valuesTVA.put("DATE", periode);
        this.mapAllSheetValues.put(3, valuesTVA);

        this.listAllSheetValues.put(4, listValuesStock);
        this.styleAllSheetValues.put(4, styleStock);
        this.mapAllSheetValues.put(4, values);
    }

    public static SQLRow rowDefaultCptService, rowDefaultCptProduit;
    static {
        final SQLTable tablePrefCompte = Configuration.getInstance().getRoot().findTable("PREFS_COMPTE");
        final SQLRow rowPrefsCompte = tablePrefCompte.getRow(2);
        rowDefaultCptService = rowPrefsCompte.getForeign("ID_COMPTE_PCE_VENTE_SERVICE");
        if (rowDefaultCptService == null || rowDefaultCptService.isUndefined()) {
            try {
                rowDefaultCptService = ComptePCESQLElement.getRowComptePceDefault("VentesServices");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        rowDefaultCptProduit = rowPrefsCompte.getForeign("ID_COMPTE_PCE_VENTE_PRODUIT");
        if (rowDefaultCptProduit == null || rowDefaultCptProduit.isUndefined()) {
            try {
                rowDefaultCptProduit = ComptePCESQLElement.getRowComptePceDefault("VentesProduits");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    class ArticleVendu {
        public String code, nom;
        public int qte, tvaID;
        public BigDecimal ht, ha, ttc, tva;
        public String numeroCompte;

        public ArticleVendu(String code, String nom, int qte, BigDecimal ht, BigDecimal ha, BigDecimal ttc, int tvaID, SQLRow rowArticle) {
            this.code = code;
            this.nom = nom;
            this.qte = qte;
            this.ht = ht;
            this.ha = ha;
            this.ttc = ttc;
            this.tvaID = tvaID;
            this.tva = new BigDecimal(TaxeCache.getCache().getTauxFromId(tvaID));

            SQLRowAccessor rowTVA = TaxeCache.getCache().getRowFromId(tvaID);
            boolean service = rowArticle.getBoolean("SERVICE");
            SQLRowAccessor rowCpt;
            // Total Service
            if (service) {
                rowCpt = rowDefaultCptService;
                if (rowTVA != null && !rowTVA.isForeignEmpty("ID_COMPTE_PCE_VENTE_SERVICE")) {
                    rowCpt = rowTVA.getForeign("ID_COMPTE_PCE_VENTE_SERVICE");
                }
            } else {
                rowCpt = rowDefaultCptProduit;
                // Compte defini par défaut dans la TVA
                if (rowTVA != null && !rowTVA.isForeignEmpty("ID_COMPTE_PCE_VENTE")) {
                    rowCpt = rowTVA.getForeign("ID_COMPTE_PCE_VENTE");
                }

            }

            if (rowArticle != null && !rowArticle.isUndefined()) {
                SQLRowAccessor compteArticle = rowArticle.getForeign("ID_COMPTE_PCE");
                if (compteArticle != null && !compteArticle.isUndefined()) {
                    rowCpt = compteArticle;
                } else {
                    SQLRowAccessor familleArticle = rowArticle.getForeign("ID_FAMILLE_ARTICLE");
                    Set<SQLRowAccessor> unique = new HashSet<SQLRowAccessor>();
                    while (familleArticle != null && !familleArticle.isUndefined() && !unique.contains(familleArticle)) {

                        unique.add(familleArticle);
                        SQLRowAccessor compteFamilleArticle = familleArticle.getForeign("ID_COMPTE_PCE");
                        if (compteFamilleArticle != null && !compteFamilleArticle.isUndefined()) {
                            rowCpt = compteFamilleArticle;
                            break;
                        }

                        familleArticle = familleArticle.getForeign("ID_FAMILLE_ARTICLE_PERE");
                    }
                }
            }
            if (rowCpt != null) {

                this.numeroCompte = rowCpt.getString("NUMERO");
            }
        }
    }
}