Dépôt officiel du code source de l'ERP OpenConcerto
svn://code.openconcerto.org/openconcerto
(root)/trunk/OpenConcerto/src/org/openconcerto/erp/generationDoc/gestcomm/EtatVentesXmlSheet.java @ 174 - Rev 182
Rev 174 | Blame | Compare with Previous | Last modification | View Log | RSS feed
/** DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.** Copyright 2011-2019 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.ListMap;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());}}@Overridepublic String getDefaultTemplateId() {return TEMPLATE_ID;}@Overrideprotected String getStoragePathP() {return "Etat Ventes";}Date d;@Overridepublic 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 OOArrayList<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<>();// Ventesfinal 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");// StockSQLRowValues rowValsArtStock = new SQLRowValues(foreignTableArticle);rowValsArtStock.putNulls("ID", "CODE", "NOM");SQLRowValues rowValsStock = new SQLRowValues(tableFactureElement.getTable("STOCK"));rowValsStock.putNulls("QTE_REEL", "QTE_TH", "QTE_MIN", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE").put("ID_ARTICLE", rowValsArtStock);SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(rowValsArtStock);List<SQLRowValues> resultStock = fetcherStock.fetch();ListMap<Integer, SQLRowValues> mapStock = new ListMap<>();for (SQLRowValues sqlRowValues : resultStock) {final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableFactureElement.getTable("STOCK").getField("ID_ARTICLE"));for (SQLRowValues sqlRowValues2 : referentRows) {mapStock.add(sqlRowValues.getID(), sqlRowValues2);}}// Requete Pour obtenir les quantités pour chaque type de réglementSQLSelect 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 factureWhere 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 caisseWhere 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 versasel.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 vendusSQLSelect 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// versaif (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 articleMap<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)) {List<SQLRowValues> rowValsArt = mapStock.get(articleID);BigDecimal totalTh = BigDecimal.ZERO;BigDecimal totalReel = BigDecimal.ZERO;BigDecimal min = BigDecimal.ZERO;BigDecimal attentR = BigDecimal.ZERO;BigDecimal attenteL = BigDecimal.ZERO;for (SQLRowValues rowStock : rowValsArt) {totalTh = totalTh.add(new BigDecimal(rowStock.getFloat("QTE_TH")));totalReel = totalReel.add(new BigDecimal(rowStock.getFloat("QTE_REEL")));min = min.add(new BigDecimal(rowStock.getFloat("QTE_MIN")));attentR = attentR.add(new BigDecimal(rowStock.getFloat("QTE_RECEPT_ATTENTE")));attenteL = attenteL.add(new BigDecimal(rowStock.getFloat("QTE_LIV_ATTENTE")));}mValuesStock.put("QTE_TH", totalTh);mValuesStock.put("QTE_REEL", totalReel);mValuesStock.put("QTE_MIN", min);mValuesStock.put("QTE_RECEPT_ATTENTE", attentR);mValuesStock.put("QTE_LIV_ATTENTE", attenteL);styleStock.put(listValuesStock.size(), "Normal");listValuesStock.add(mValuesStock);}}// System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);}}// Liste des ventes comptoirsfinal 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 versaselVC.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>();// NommValues.put("NOM", rowVenteComptoir[0]);// HTfinal long ht = ((Number) rowVenteComptoir[1]).longValue();mValues.put("T_PV_HT", ht / 100.0D);// TTCfinal 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églementSQLSelect 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 facturesel.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 versasel.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 vendusSQLSelect 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// versaif (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 articleMap<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 Achatsfinal 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 Achatlong 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écapitulatifMap<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 TVAMap<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 Serviceif (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 TVAif (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");}}}}