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

/*

 * 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;

        }

    }

}