WebSVN - OpenConcerto - Rev 180 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/sales/invoice/report/ReportingStockXmlSheet.java

/*
 * 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.config.ComptaPropsConfiguration;
import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
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.SQLRowValues;
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.TableRef;
import org.openconcerto.sql.model.Where;
import org.openconcerto.utils.cc.ITransformer;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
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.TreeMap;

public class ReportingStockXmlSheet extends AbstractListeSheetXml {

    private final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");

    public static final String TEMPLATE_ID = "EtatStocks";
    public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
    private int intIDDepot = DepotStockSQLElement.DEFAULT_ID;
    private boolean bTousLesDepots = false;
    private Date date;
    private SQLElement eltArticle = Configuration.getInstance().getDirectory().getElement("ARTICLE");
    private SQLElement eltStock = Configuration.getInstance().getDirectory().getElement("STOCK");

    public ReportingStockXmlSheet(ComptaPropsConfiguration conf, Integer intIDDepot, boolean bTousLesDepots) {
        this.eltArticle = conf.getDirectory().getElement("ARTICLE");
        this.eltStock = conf.getDirectory().getElement("STOCK");
        if (intIDDepot != null) {
            this.intIDDepot = intIDDepot;
        }
        this.bTousLesDepots = bTousLesDepots;
    }

    @Override
    public String getStoragePathP() {
        return "Autres";
    }

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

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

    protected void createListeValues() {

        final SQLTable tableArt = this.eltArticle.getTable();
        SQLRowValues rowvArticle = new SQLRowValues(tableArt);
        rowvArticle.put("ID_FOURNISSEUR", null);
        rowvArticle.put("ID_FAMILLE_ARTICLE", null);
        rowvArticle.put("CODE", null);
        rowvArticle.put("NOM", null);
        rowvArticle.put("PA_HT", null);
        rowvArticle.put("PV_HT", null);

        SQLRowValues rowvStock = new SQLRowValues(this.eltStock.getTable());
        rowvStock.put("QTE_REEL", null).put("ID_ARTICLE", rowvArticle);

        SQLRowValuesListFetcher fetch = SQLRowValuesListFetcher.create(rowvArticle);

        if (!this.bTousLesDepots) {
            fetch.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
                @Override
                public SQLSelect transformChecked(SQLSelect input) {
                    TableRef join = input.getAlias(ReportingStockXmlSheet.this.eltArticle.getTable().getTable("STOCK"));

                    input.setWhere(new Where(join.getField("ID_DEPOT_STOCK"), "=", ReportingStockXmlSheet.this.intIDDepot));
                    System.out.println(input.toString());
                    return input;
                }
            });
        }

        List<SQLRowValues> values = fetch.fetch();

        final SQLTable tableF = tableArt.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);
        }

        final SQLTable tableFourn = tableArt.getTable("FOURNISSEUR");
        SQLSelect selFourn = new SQLSelect();
        selFourn.addSelect(tableFourn.getKey());
        selFourn.addSelect(tableFourn.getField("NOM"));

        List<SQLRow> fourn = SQLRowListRSH.execute(selFourn);
        Map<Integer, SQLRow> mapFourn = new HashMap<Integer, SQLRow>();
        for (SQLRow sqlRow : fourn) {
            mapFourn.put(sqlRow.getID(), sqlRow);
        }

        Map<String, Line> linesFamilles = new HashMap<String, Line>();
        Map<Line, Map<Line, List<Line>>> myValues = new TreeMap<Line, Map<Line, List<Line>>>(new Comparator<Line>() {
            @Override
            public int compare(Line o1, Line o2) {
                return o1.getNomArt().compareTo(o2.getNomArt());
            }
        });
        Line lineTotal = new Line("Total", "", BigDecimal.ZERO, 0F);
        final HashMap<Integer, String> style = new HashMap<Integer, String>();
        for (SQLRowValues vals : values) {
            Collection<SQLRowValues> stocks = vals.getReferentRows(this.eltStock.getTable());
            Float qte = 0f;
            for (SQLRowValues stock : stocks) {
                qte += stock.getFloat("QTE_REEL");
            }
            BigDecimal ha = BigDecimal.ZERO;
            if (qte > 0) {
                final BigDecimal puHA = vals.getBigDecimal("PA_HT");
                ha = puHA.multiply(new BigDecimal(qte));

                int idFamille = vals.getForeignID("ID_FAMILLE_ARTICLE");
                SQLRow rowF = mapF.get(idFamille);
                Line lineArt = new Line(vals.getString("NOM"), vals.getString("CODE"), puHA, ha, qte);

                // Init des lines familles

                final Line lineF, lineSF;
                if (rowF == null) {
                    if (!linesFamilles.containsKey("Undef")) {
                        linesFamilles.put("Undef", new Line("Sans famille", "", BigDecimal.ZERO, 0F));
                        linesFamilles.put("Undef-Undef", new Line("", "", BigDecimal.ZERO, 0F));
                    }
                    lineF = linesFamilles.get("Undef");
                    lineSF = linesFamilles.get("Undef-Undef");
                } else if (rowF.getObject("ID_FAMILLE_ARTICLE_PERE") == null || rowF.isForeignEmpty("ID_FAMILLE_ARTICLE_PERE")) {
                    if (!linesFamilles.containsKey(String.valueOf(rowF.getID()))) {
                        linesFamilles.put(String.valueOf(rowF.getID()), new Line(rowF.getString("NOM"), "", BigDecimal.ZERO, 0F));
                        linesFamilles.put(String.valueOf(rowF.getID()) + "-Undef", new Line("", "", BigDecimal.ZERO, 0F));
                    }
                    if (!linesFamilles.containsKey(String.valueOf(rowF.getID()) + "-Undef")) {
                        linesFamilles.put(String.valueOf(rowF.getID()) + "-Undef", new Line("", "", BigDecimal.ZERO, 0F));
                    }
                    lineF = linesFamilles.get(String.valueOf(rowF.getID()));
                    lineSF = linesFamilles.get(String.valueOf(rowF.getID()) + "-Undef");
                } else {
                    if (!linesFamilles.containsKey(String.valueOf(rowF.getID()))) {
                        linesFamilles.put(String.valueOf(rowF.getID()), new Line(rowF.getString("NOM"), "", BigDecimal.ZERO, 0F));
                    }
                    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, 0F));
                    }
                    lineF = linesFamilles.get(String.valueOf(rowF.getForeignID("ID_FAMILLE_ARTICLE_PERE")));
                    lineSF = linesFamilles.get(String.valueOf(rowF.getID()));
                }

                // init values
                if (!myValues.containsKey(lineF)) {
                    myValues.put(lineF, new TreeMap<Line, List<Line>>(new Comparator<Line>() {
                        @Override
                        public int compare(Line o1, Line o2) {
                            return o1.getNomArt().compareTo(o2.getNomArt());
                        }
                    }));
                    myValues.get(lineF).put(lineSF, new ArrayList<Line>());
                }
                Map<Line, List<Line>> mapSF = myValues.get(lineF);
                if (!mapSF.containsKey(lineSF)) {
                    mapSF.put(lineSF, new ArrayList<Line>());
                }

                // Store values
                List<Line> lines = mapSF.get(lineSF);
                lines.add(lineArt);
                lineTotal.add(lineArt);
                lineF.add(lineArt);
                lineSF.add(lineArt);
            }
        }

        // Sort Values
        List<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>();

        for (Line f : myValues.keySet()) {
            listValues.add(f.getMapXMLSheet());
            style.put(style.keySet().size(), "Titre 1");
            Map<Line, List<Line>> sfs = myValues.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>() {
                    @Override
                    public int compare(Line o1, Line o2) {
                        return o1.getNomArt().compareTo(o2.getNomArt());
                    }
                });
                for (Line line : vals) {
                    listValues.add(line.getMapXMLSheet());
                    style.put(style.keySet().size(), "Normal");
                }
            }
        }

        listValues.add(lineTotal.getMapXMLSheet());
        style.put(style.keySet().size(), "Titre 1");

        final Map<String, Object> valuesSheet = new HashMap<String, Object>();
        valuesSheet.put("DATE", "Au " + this.dateFormat.format(new Date()));
        //
        this.listAllSheetValues.put(0, listValues);

        this.styleAllSheetValues.put(0, style);
        this.mapAllSheetValues.put(0, valuesSheet);
    }

    class Line {
        final private String nomArt, codeArt;
        private BigDecimal totalHA, puHA;
        private Float qte;

        public Line(String nomArt, String codeArt, BigDecimal totalHA, Float qte) {
            this(nomArt, codeArt, BigDecimal.ZERO, totalHA, qte);
        }

        public Line(String nomArt, String codeArt, BigDecimal puHA, BigDecimal totalHA, Float qte) {
            this.nomArt = nomArt;
            this.codeArt = codeArt;
            this.totalHA = totalHA;
            this.puHA = puHA;
            this.qte = qte;
        }

        public Float getQte() {
            return this.qte;
        }

        public String getCodeArt() {
            return this.codeArt;
        }

        public BigDecimal getPuHA() {
            return this.puHA;
        }

        public String getNomArt() {
            return this.nomArt;
        }

        public BigDecimal getTotalHA() {
            return this.totalHA;
        }

        public void add(Line l) {
            this.totalHA = this.totalHA.add(l.getTotalHA());
            if (l.getQte() > 0) {
                this.qte = this.qte + l.getQte();
            }
        }

        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("PU_HA", getPuHA());
            m.put("TOTAL_HA", getTotalHA());
            return m;
        }

    }

}
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/ReportingStockXmlSheet.java - Rev 180
