Dépôt officiel du code source de l'ERP OpenConcerto
svn://code.openconcerto.org/openconcerto
(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/sales/invoice/report/ReportingVenteXmlSheet.java @ 156 - Rev 144
Go to most recent revision | 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.core.sales.invoice.report;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.model.SQLRow;import org.openconcerto.sql.model.SQLRowListRSH;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 java.math.BigDecimal;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.TreeMap;import javax.swing.JProgressBar;public class ReportingVenteXmlSheet extends AbstractListeSheetXml {private final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");public static final String TEMPLATE_ID = "ReportingVentes";public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;private Date du, au;private Date date;private JProgressBar bar;private SQLElement eltFact = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE");private SQLElement eltFactItem = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE_ELEMENT");private List<Integer> idS;public ReportingVenteXmlSheet(List<Integer> idS, Date du, Date au, JProgressBar bar, boolean commande) {super();eltFact = Configuration.getInstance().getDirectory().getElement(commande ? "COMMANDE_CLIENT" : "SAISIE_VENTE_FACTURE");eltFactItem = Configuration.getInstance().getDirectory().getElement(commande ? "COMMANDE_CLIENT_ELEMENT" : "SAISIE_VENTE_FACTURE_ELEMENT");this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");this.du = du;this.au = au;this.bar = bar;this.idS = idS;}@Overridepublic String getStoragePathP() {return "Autres";}@Overridepublic String getDefaultTemplateId() {return TEMPLATE_ID;};@Overridepublic String getName() {if (this.date == null) {this.date = new Date();}return "ReportingVentes" + this.date.getTime();}protected void createListeValues() {SQLSelect sel = new SQLSelect();sel.addRawSelect("SUM(\"QTE\"*\"QTE_UNITAIRE\")", "q");final SQLTable tableItemFact = eltFactItem.getTable();sel.addSelect(tableItemFact.getField("T_PA_HT"), "SUM");sel.addSelect(tableItemFact.getField("T_PV_HT"), "SUM");sel.addSelect(tableItemFact.getField("T_PV_TTC"), "SUM");final SQLTable tableArt = eltFactItem.getForeignElement("ID_ARTICLE").getTable();sel.addSelect(tableItemFact.getField("ID_ARTICLE"));final SQLSelectJoin joinFact = sel.addJoin("LEFT", tableItemFact.getField("ID_" + eltFact.getTable().getName()));Where wA = new Where(tableArt.getKey(), "=", tableItemFact.getField("ID_ARTICLE"));Where w = new Where(joinFact.getJoinedTable().getField("DATE"), this.du, this.au);if (idS != null && idS.size() > 0) {sel.addSelect(joinFact.getJoinedTable().getField("ID_COMMERCIAL"));sel.addGroupBy(joinFact.getJoinedTable().getField("ID_COMMERCIAL"));w = w.and(new Where(joinFact.getJoinedTable().getField("ID_COMMERCIAL"), idS));}sel.setWhere(wA.and(w));sel.addGroupBy(tableItemFact.getField("ID_ARTICLE"));List<Object[]> result = eltFact.getTable().getDBSystemRoot().getDataSource().executeA(sel.asString());SQLSelect selCom = new SQLSelect();final SQLTable tableCom = eltFact.getTable().getTable("COMMERCIAL");selCom.addSelect(tableCom.getKey());selCom.addSelect(tableCom.getField("NOM"));selCom.addSelect(tableCom.getField("PRENOM"));List<SQLRow> coms = SQLRowListRSH.execute(selCom);Map<Integer, SQLRow> mapCom = new HashMap<Integer, SQLRow>();for (SQLRow sqlRow : coms) {mapCom.put(sqlRow.getID(), sqlRow);}SQLSelect selArt = new SQLSelect();selArt.addSelect(tableArt.getKey());selArt.addSelect(tableArt.getField("NOM"));selArt.addSelect(tableArt.getField("CODE"));selArt.addSelect(tableArt.getField("ID_FAMILLE_ARTICLE"));List<SQLRow> art = SQLRowListRSH.execute(selArt);Map<Integer, SQLRow> mapA = new HashMap<Integer, SQLRow>();for (SQLRow sqlRow : art) {mapA.put(sqlRow.getID(), sqlRow);}final SQLTable tableF = eltFactItem.getTable().getTable("FAMILLE_ARTICLE");SQLSelect selFam = new SQLSelect();selFam.addSelect(tableF.getKey());selFam.addSelect(tableF.getField("NOM"));selFam.addSelect(tableF.getField("ID_FAMILLE_ARTICLE_PERE"));List<SQLRow> fam = SQLRowListRSH.execute(selFam);Map<Integer, SQLRow> mapF = new HashMap<Integer, SQLRow>();for (SQLRow sqlRow : fam) {mapF.put(sqlRow.getID(), sqlRow);}Map<String, Line> linesFamilles = new HashMap<String, Line>();Map<String, Line> linesCommercial = new HashMap<String, Line>();Map<String, Map<Line, Map<Line, List<Line>>>> myValues = new TreeMap<String, Map<Line, Map<Line, List<Line>>>>();Line lineTotal = new Line("Total", "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO);final HashMap<Integer, String> style = new HashMap<Integer, String>();for (Object[] vals : result) {BigDecimal qte = (BigDecimal) vals[0];BigDecimal ha = (BigDecimal) vals[1];BigDecimal ht = (BigDecimal) vals[2];BigDecimal ttc = (BigDecimal) vals[3];int idArt = (Integer) vals[4];SQLRow rowArt = mapA.get(idArt);int idFamille = rowArt.getForeignID("ID_FAMILLE_ARTICLE");SQLRow rowF = mapF.get(idFamille);Line lineArt = new Line(rowArt.getString("NOM"), rowArt.getString("CODE"), ha, ttc, ht, qte);// Init des lines famillesfinal Line lineF, lineSF;if (rowF == null) {if (!linesFamilles.containsKey("Undef")) {linesFamilles.put("Undef", new Line("Sans famille", "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));linesFamilles.put("Undef-Undef", new Line("", "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));}lineF = linesFamilles.get("Undef");lineSF = linesFamilles.get("Undef-Undef");} else if (rowF.getObject("ID_FAMILLE_ARTICLE_PERE") == null || rowF.isForeignEmpty("ID_FAMILLE_ARTICLE_PERE")) {final String idRowF = String.valueOf(rowF.getID());if (!linesFamilles.containsKey(idRowF)) {linesFamilles.put(idRowF, new Line(rowF.getString("NOM"), "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));linesFamilles.put(idRowF + "-Undef", new Line("", "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));}lineF = linesFamilles.get(idRowF);if (!linesFamilles.containsKey(idRowF + "-Undef")) {linesFamilles.put(idRowF + "-Undef", new Line("", "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));}lineSF = linesFamilles.get(idRowF + "-Undef");if (lineSF == null) {System.err.println("null SF");}if (lineF == null) {System.err.println("null F");}} else {final String idRowF = String.valueOf(rowF.getID());if (!linesFamilles.containsKey(idRowF)) {linesFamilles.put(idRowF, new Line(rowF.getString("NOM"), "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));}if (!linesFamilles.containsKey(String.valueOf(rowF.getForeignID("ID_FAMILLE_ARTICLE_PERE")))) {SQLRow rowSF = mapF.get(rowF.getForeignID("ID_FAMILLE_ARTICLE_PERE"));linesFamilles.put(String.valueOf(rowF.getForeignID("ID_FAMILLE_ARTICLE_PERE")),new Line(rowSF.getString("NOM"), "", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));}lineF = linesFamilles.get(String.valueOf(rowF.getForeignID("ID_FAMILLE_ARTICLE_PERE")));lineSF = linesFamilles.get(idRowF);if (lineSF == null) {System.err.println("null SF");}if (lineF == null) {System.err.println("null F");}}// init valuesfinal String commercial;if (idS != null && idS.size() > 0) {SQLRow rowCom = mapCom.get(vals[5]);commercial = rowCom.getString("PRENOM") + " " + rowCom.getString("NOM");if (!linesCommercial.containsKey(commercial)) {linesCommercial.put(commercial, new Line(commercial, "COMMERCIAL", BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));}linesCommercial.get(commercial).add(lineArt);} else {commercial = "";}Map<Line, Map<Line, List<Line>>> mapCommercial;if (!myValues.containsKey(commercial)) {myValues.put(commercial, new TreeMap<Line, Map<Line, List<Line>>>(new Comparator<Line>() {@Overridepublic int compare(Line o1, Line o2) {return o1.getNomArt().compareTo(o2.getNomArt());}}));}mapCommercial = myValues.get(commercial);if (!mapCommercial.containsKey(lineF)) {mapCommercial.put(lineF, new TreeMap<Line, List<Line>>(new Comparator<Line>() {@Overridepublic int compare(Line o1, Line o2) {return o1.getNomArt().compareTo(o2.getNomArt());}}));mapCommercial.get(lineF).put(lineSF, new ArrayList<Line>());}Map<Line, List<Line>> mapSF = mapCommercial.get(lineF);if (!mapSF.containsKey(lineSF)) {mapSF.put(lineSF, new ArrayList<Line>());}// Store valuesList<Line> lines = mapSF.get(lineSF);lines.add(lineArt);lineTotal.add(lineArt);lineF.add(lineArt);lineSF.add(lineArt);}// Sort ValuesList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>();for (Entry<String, Map<Line, Map<Line, List<Line>>>> commercial : myValues.entrySet()) {if (linesCommercial.get(commercial.getKey()) != null) {listValues.add(linesCommercial.get(commercial.getKey()).getMapXMLSheet());style.put(style.keySet().size(), "Titre 1");}for (Line f : commercial.getValue().keySet()) {listValues.add(f.getMapXMLSheet());style.put(style.keySet().size(), "Titre 1");Map<Line, List<Line>> sfs = commercial.getValue().get(f);for (Line sf : sfs.keySet()) {listValues.add(sf.getMapXMLSheet());style.put(style.keySet().size(), "Titre 2");List<Line> vals = sfs.get(sf);Collections.sort(vals, new Comparator<Line>() {@Overridepublic int compare(Line o1, Line o2) {return o1.getNomArt().compareTo(o2.getNomArt());}});for (Line line : vals) {if (line.getQte().signum() > 0) {listValues.add(line.getMapXMLSheet());style.put(style.keySet().size(), "Normal");}}}}}listValues.add(lineTotal.getMapXMLSheet());style.put(style.keySet().size(), "Titre 1");// SwingUtilities.invokeLater(new Runnable() {// public void run() {// ReportingVenteXmlSheet.this.bar.setMaximum(ReportingVenteXmlSheet.this.listeIds.size());// }// });// List<Map<String, Object>> listValues = new ArrayList<Map<String,// Object>>(this.listeIds.size());// int i = 1;// for (SQLRow rowFacture : this.listeIds) {// Map<String, Object> mValues = new HashMap<String, Object>();// final String dateFacture = dateFormat.format((Date) rowFacture.getObject("DATE"));// mValues.put("DATE", dateFacture);// mValues.put("NUMERO", rowFacture.getObject("NUMERO"));// if (rowFacture.getTable().getName().equalsIgnoreCase(this.eltAvoir.getTable().getName()))// {// mValues.put("MONTANT_HT", new Double(-((Number)// rowFacture.getObject("MONTANT_HT")).longValue() / 100.0));// mValues.put("MONTANT_TVA", new Double(-((Number)// rowFacture.getObject("MONTANT_TVA")).longValue() / 100.0));// mValues.put("MONTANT_TTC", new Double(-((Number)// rowFacture.getObject("MONTANT_TTC")).longValue() / 100.0));// } else {// mValues.put("MONTANT_HT", new Double(((Number) rowFacture.getObject("T_HT")).longValue()// / 100.0));// mValues.put("MONTANT_TVA", new Double(((Number)// rowFacture.getObject("T_TVA")).longValue() / 100.0));// mValues.put("MONTANT_TTC", new Double(((Number)// rowFacture.getObject("T_TTC")).longValue() / 100.0));// }//// // Client// SQLRow rowCli;// // #if gestionnx// if (((ComptaPropsConfiguration) Configuration.getInstance()).customerUseControle()) {// rowCli = rowFacture.getForeignRow("ID_CLIENT").getForeignRow("ID_CLIENT");// } else {// // #endif// rowCli = rowFacture.getForeignRow("ID_CLIENT");// // #if gestionnx// }// // #endif// String libClient = rowCli.getString("FORME_JURIDIQUE") + " " + rowCli.getString("NOM");// mValues.put("CLIENT", libClient.trim());//// // Mode de reglement// SQLRow rowMode = rowFacture.getForeignRow("ID_MODE_REGLEMENT");// final int typeReglement = rowMode.getInt("ID_TYPE_REGLEMENT");// if (rowMode.getBoolean("COMPTANT") && typeReglement <= TypeReglementSQLElement.TRAITE) {//// final SQLRow foreignRow = rowMode.getForeignRow("ID_TYPE_REGLEMENT");// Date d = (Date) rowFacture.getObject("DATE");// if (foreignRow.getID() == TypeReglementSQLElement.TRAITE) {// Calendar c = (rowMode.getDate("DATE_VIREMENT"));// if (c != null) {// d = c.getTime();// }// } else if (foreignRow.getID() == TypeReglementSQLElement.CHEQUE) {// Calendar c = (rowMode.getDate("DATE"));// if (c != null) {// d = c.getTime();// }// }//// mValues.put("DATE_REGLEMENT", dateFormat.format(d));// mValues.put("TYPE_REGLEMENT", foreignRow.getString("NOM"));// } else {//// SQLRow rowMvt = rowFacture.getForeignRow("ID_MOUVEMENT");// SQLRow rowPiece = rowMvt.getForeignRow("ID_PIECE");//// SQLSelect sel = new SQLSelect(rowFacture.getTable().getBase());//// sel.addSelect(this.eltEnc.getTable().getKey());// sel.addSelect(this.eltEnc.getTable().getField("ID_MODE_REGLEMENT"));//// Where w = new Where(rowMvt.getTable().getField("ID_PIECE"), "=", rowPiece.getID());// w = w.and(new Where(rowMvt.getTable().getKey(), "=",// this.eltEncElt.getTable().getField("ID_MOUVEMENT_ECHEANCE")));// w = w.and(new Where(this.eltEncElt.getTable().getField("ID_ENCAISSER_MONTANT"), "=",// this.eltEnc.getTable().getKey()));//// sel.setWhere(w);//// List<SQLRow> l = (List<SQLRow>)// Configuration.getInstance().getBase().getDataSource().execute(sel.asString(),// SQLRowListRSH.createFromSelect(sel, eltEnc.getTable()));// for (SQLRow sqlRow : l) {// final SQLRow foreignRow = sqlRow.getForeignRow("ID_MODE_REGLEMENT");// SQLRow rowTypeRegl = foreignRow.getForeignRow("ID_TYPE_REGLEMENT");// Calendar cDate = foreignRow.getDate("DATE");// Calendar cDateVirement = foreignRow.getDate("DATE_VIREMENT");// if (cDate != null) {// mValues.put("DATE_REGLEMENT", dateFormat.format(cDate.getTime()));// } else if (cDateVirement != null) {// mValues.put("DATE_REGLEMENT", dateFormat.format(cDateVirement.getTime()));// } else {// mValues.put("DATE_REGLEMENT", dateFormat.format(sqlRow.getDate("DATE").getTime()));// }//// mValues.put("TYPE_REGLEMENT", rowTypeRegl.getString("NOM"));// }// }//// listValues.add(mValues);// final int value = i++;// SwingUtilities.invokeLater(new Runnable() {// public void run() {// ReportingVenteXmlSheet.this.bar.setValue(value);// }// });// }final Map<String, Object> values = new HashMap<String, Object>();values.put("DATE", "Du " + dateFormat.format(this.du) + " au " + dateFormat.format(this.au));values.put("NOM", "Reporting des " + (eltFact.getTable().getName().startsWith("COMMANDE") ? "commandes" : "factures"));//this.listAllSheetValues.put(0, listValues);this.styleAllSheetValues.put(0, style);this.mapAllSheetValues.put(0, values);}class Line {final private String nomArt, codeArt;private BigDecimal totalTTC, totalHT, qte, totalHA;public Line(String nomArt, String codeArt, BigDecimal totalHA, BigDecimal totalTTC, BigDecimal totalHT, BigDecimal qte) {this.nomArt = nomArt;this.codeArt = codeArt;this.totalHA = totalHA;this.totalHT = totalHT;this.totalTTC = totalTTC;this.qte = qte;}public BigDecimal getTotalHA() {return totalHA;}public BigDecimal getQte() {return qte;}public String getCodeArt() {return codeArt;}public String getNomArt() {return nomArt;}public BigDecimal getTotalHT() {return totalHT;}public BigDecimal getTotalTTC() {return totalTTC;}public void add(Line l) {this.qte = this.qte.add(l.getQte());this.totalHA = this.totalHA.add(l.getTotalHA());this.totalHT = this.totalHT.add(l.getTotalHT());this.totalTTC = this.totalTTC.add(l.getTotalTTC());}public Map<String, Object> getMapXMLSheet() {Map<String, Object> m = new HashMap<String, Object>();m.put("CODE", getCodeArt());m.put("NOM", getNomArt());m.put("QTE", getQte());m.put("TOTAL_HA", getTotalHA());m.put("TOTAL_HT", getTotalHT());m.put("TOTAL_TTC", getTotalTTC());return m;}}}