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 ReportingCommercialCreator {

    final private Date deb, fin;
    final private DBRoot root;

    public ReportingCommercialCreator(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");
        SQLRowValues rowValues = new SQLRowValues(tableFacture);
        rowValues.putRowValues("ID_COMMERCIAL").putNulls("PRENOM", "NOM");
        rowValues.putRowValues("ID_CLIENT").putNulls("CODE", "NOM");
        rowValues.putNulls("T_HT", "T_TTC");

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowValues);
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                Where w = new Where(tableFacture.getField("DATE"), deb, fin);
                input.setWhere(w);
                return input;
            }
        });

        Map<Integer, ReportingCommercial> mapCommercial = new HashMap<>();
        List<SQLRowValues> result = fetcher.fetch();
        for (SQLRowValues sqlRowValues : result) {
            int commercialID = sqlRowValues.getForeignID("ID_COMMERCIAL");
            if (!mapCommercial.containsKey(commercialID)) {
                SQLRowAccessor commercialRow = sqlRowValues.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_HT")).movePointLeft(2);
            r.add(sqlRowValues.getForeign("ID_CLIENT").getString("NOM"), bigDecimal);
        }

        // Avoir
        final SQLTable tableAvoir = this.root.getTable("AVOIR_CLIENT");
        SQLRowValues rowValuesAvoir = new SQLRowValues(tableAvoir);
        rowValuesAvoir.putRowValues("ID_COMMERCIAL").putNulls("PRENOM", "NOM");
        rowValuesAvoir.putRowValues("ID_CLIENT").putNulls("CODE", "NOM");
        rowValuesAvoir.putNulls("MONTANT_HT", "MONTANT_TTC");

        SQLRowValuesListFetcher fetcherAvoir = SQLRowValuesListFetcher.create(rowValuesAvoir);
        fetcherAvoir.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                Where w = new Where(tableAvoir.getField("DATE"), deb, fin);
                input.setWhere(w);
                return input;
            }
        });

        List<SQLRowValues> resultA = fetcherAvoir.fetch();
        for (SQLRowValues sqlRowValues : resultA) {
            int commercialID = sqlRowValues.getForeignID("ID_COMMERCIAL");
            if (!mapCommercial.containsKey(commercialID)) {
                SQLRowAccessor commercialRow = sqlRowValues.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("MONTANT_HT")).movePointLeft(2).negate();
            r.add(sqlRowValues.getForeign("ID_CLIENT").getString("NOM"), bigDecimal);
        }

        return mapCommercial.values();
    }

}