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.customerrelationship.customer.report;

import org.openconcerto.sql.model.DBRoot;

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.SQLTable;

import org.openconcerto.sql.model.Where;

import org.openconcerto.utils.cc.ITransformer;

import java.math.BigDecimal;

import java.util.Collection;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class ReportingCommercialFournisseurCreator {

    final private Date deb, fin;

    final private DBRoot root;

    public ReportingCommercialFournisseurCreator(Date deb, Date fin, DBRoot root) {

        this.deb = deb;

        this.fin = fin;

        this.root = root;

    }

    public Collection<ReportingCommercial> getValues() {

        // Facture

        final SQLTable tableFacture = this.root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");

        SQLRowValues rowValues = new SQLRowValues(tableFacture);

        rowValues.putRowValues("ID_SAISIE_VENTE_FACTURE").putRowValues("ID_COMMERCIAL").putNulls("PRENOM", "NOM");

        rowValues.putRowValues("ID_ARTICLE").putRowValues("ID_FOURNISSEUR").putNulls("CODE", "NOM");

        rowValues.putNulls("T_PV_HT", "T_PV_TTC");

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowValues);

        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

            @Override

            public SQLSelect transformChecked(SQLSelect input) {

                Where w = new Where(input.getJoinFromField(tableFacture.getField("ID_SAISIE_VENTE_FACTURE")).getJoinedTable().getField("DATE"), deb, fin);

                input.setWhere(w);

                return input;

            }

        });

        Map<Integer, ReportingCommercial> mapCommercial = new HashMap<>();

        List<SQLRowValues> result = fetcher.fetch();

        for (SQLRowValues sqlRowValues : result) {

            if (!sqlRowValues.isForeignEmpty("ID_ARTICLE")) {

                SQLRowAccessor rowArticle = sqlRowValues.getForeign("ID_ARTICLE");

                if (!rowArticle.isForeignEmpty("ID_FOURNISSEUR")) {

                    SQLRowAccessor rowFournisseur = rowArticle.getForeign("ID_FOURNISSEUR");

                    int commercialID = sqlRowValues.getForeign("ID_SAISIE_VENTE_FACTURE").getForeignID("ID_COMMERCIAL");

                    if (!mapCommercial.containsKey(commercialID)) {

                        SQLRowAccessor commercialRow = sqlRowValues.getForeign("ID_SAISIE_VENTE_FACTURE").getForeign("ID_COMMERCIAL");

                        mapCommercial.put(commercialID, new ReportingCommercial(commercialRow.getString("PRENOM") + " " + commercialRow.getString("NOM"), deb, fin));

                    }

                    ReportingCommercial r = mapCommercial.get(commercialID);

                    BigDecimal bigDecimal = new BigDecimal(sqlRowValues.getLong("T_PV_HT")).movePointLeft(2);

                    r.add(rowFournisseur.getString("NOM"), bigDecimal);

                }

            }

        }

        // Avoir

        final SQLTable tableAvoir = this.root.getTable("AVOIR_CLIENT_ELEMENT");

        SQLRowValues rowValuesAvoir = new SQLRowValues(tableAvoir);

        rowValuesAvoir.putRowValues("ID_AVOIR_CLIENT").putRowValues("ID_COMMERCIAL").putNulls("PRENOM", "NOM");

        rowValuesAvoir.putRowValues("ID_ARTICLE").putRowValues("ID_FOURNISSEUR").putNulls("CODE", "NOM");

        rowValuesAvoir.putNulls("T_PV_HT", "T_PV_TTC");

        SQLRowValuesListFetcher fetcherAvoir = SQLRowValuesListFetcher.create(rowValuesAvoir);

        fetcherAvoir.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

            @Override

            public SQLSelect transformChecked(SQLSelect input) {

                Where w = new Where(input.getJoinFromField(tableAvoir.getField("ID_AVOIR_CLIENT")).getJoinedTable().getField("DATE"), deb, fin);

                input.setWhere(w);

                return input;

            }

        });

        List<SQLRowValues> resultA = fetcherAvoir.fetch();

        for (SQLRowValues sqlRowValues : resultA) {

            if (!sqlRowValues.isForeignEmpty("ID_ARTICLE")) {

                SQLRowAccessor rowArticle = sqlRowValues.getForeign("ID_ARTICLE");

                if (!rowArticle.isForeignEmpty("ID_FOURNISSEUR")) {

                    SQLRowAccessor rowFournisseur = rowArticle.getForeign("ID_FOURNISSEUR");

                    int commercialID = sqlRowValues.getForeign("ID_AVOIR_CLIENT").getForeignID("ID_COMMERCIAL");

                    if (!mapCommercial.containsKey(commercialID)) {

                        SQLRowAccessor commercialRow = sqlRowValues.getForeign("ID_AVOIR_CLIENT").getForeign("ID_COMMERCIAL");

                        mapCommercial.put(commercialID, new ReportingCommercial(commercialRow.getString("PRENOM") + " " + commercialRow.getString("NOM"), deb, fin));

                    }

                    ReportingCommercial r = mapCommercial.get(commercialID);

                    BigDecimal bigDecimal = new BigDecimal(sqlRowValues.getLong("T_PV_HT")).movePointLeft(2).negate();

                    r.add(rowFournisseur.getString("NOM"), bigDecimal);

                }

            }

        }

        return mapCommercial.values();

    }

}