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

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011-2019 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.supplychain.stock.element;

import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowAccessor;
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.Where;
import org.openconcerto.utils.DecimalUtils;
import org.openconcerto.utils.ExceptionHandler;
import org.openconcerto.utils.cc.ITransformer;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EtatStockSnapshotCreator {

    private final Date d;
    private final DBRoot root;
    private final SQLRowAccessor depot;
    private final boolean withAllProducts;

    public EtatStockSnapshotCreator(SQLRowAccessor depot, Date d, DBRoot root, boolean withAllProducts) {
        this.d = d;
        this.depot = depot;
        this.root = root;
        this.withAllProducts = withAllProducts;
    }

    public void create() {
        // Récupération des inventaires
        SQLTable tableEtatStock = this.root.getTable("ETAT_STOCK");
        SQLSelect sel = new SQLSelect();
        sel.addSelectStar(tableEtatStock);
        Where wEtat = new Where(tableEtatStock.getField("INVENTAIRE"), "=", Boolean.TRUE);
        wEtat = wEtat.and(new Where(tableEtatStock.getField("ID_DEPOT_STOCK"), "=", this.depot.getID()));
        sel.setWhere(wEtat);
        List<SQLRow> rowsEtatStock = SQLRowListRSH.execute(sel);
        Map<Integer, Integer> mapEtatStock = new HashMap<Integer, Integer>();
        for (SQLRow sqlRow : rowsEtatStock) {
            SQLTable tableMvtStock = this.root.getTable("MOUVEMENT_STOCK");
            SQLTable tableStock = this.root.getTable("STOCK");
            SQLSelect selMvt = new SQLSelect();
            selMvt.addSelect(tableMvtStock.getKey(), "MIN");
            Where wMvt = new Where(tableMvtStock.getField("OUVERTURE"), "=", Boolean.TRUE);
            wMvt = wMvt.and(new Where(tableMvtStock.getField("ID_ETAT_STOCK"), "=", sqlRow.getID()));
            wMvt = wMvt.and(new Where(tableMvtStock.getField("ID_STOCK"), "=", tableStock.getKey()));
            wMvt = wMvt.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", depot.getID()));
            selMvt.setWhere(wMvt);
            Integer idMvt = (Integer) tableMvtStock.getDBSystemRoot().getDataSource().executeScalar(selMvt.asString());
            if (idMvt != null) {
                mapEtatStock.put(sqlRow.getID(), idMvt);
            }
        }

        Map<Integer, EtatStock> mapStockSnap = new HashMap<Integer, EtatStock>();
        {
            final SQLTable tableMvtStock = this.root.getTable("MOUVEMENT_STOCK");

            final SQLRowValues vals = new SQLRowValues(tableMvtStock);

            vals.put("QTE", null);
            if (tableMvtStock.contains("PRICE")) {
                vals.put("PRICE", null);
            }
            vals.put("ID_ARTICLE", null);
            vals.putRowValues("ID_STOCK").putNulls("QTE_REEL").putRowValues("ID_DEPOT_STOCK").putNulls("ID", "NOM", "CODE");

            // Calendar cal0116 = Calendar.getInstance();
            // cal0116.set(2016, Calendar.JANUARY, 1, 0, 0, 0);
            // final Date dateDeb = cal0116.getTime();

            // Récupération du dernier etat de stock
            SQLSelect selEtatD = new SQLSelect();
            selEtatD.addSelectStar(tableEtatStock);
            Where wEtatD = new Where(tableEtatStock.getField("INVENTAIRE"), "=", Boolean.TRUE);
            wEtatD = wEtatD.and(new Where(tableEtatStock.getField("ID_DEPOT_STOCK"), "=", this.depot.getID()));
            selEtatD.setWhere(wEtatD);
            List<SQLRow> rowsEtatStockD = SQLRowListRSH.execute(selEtatD);
            SQLRow rowEtatStockDeb = null;
            for (SQLRow sqlRow : rowsEtatStockD) {

                if (sqlRow.getDate("DATE").getTime().before(this.d)) {
                    if (rowEtatStockDeb == null || rowEtatStockDeb.getDate("DATE").before(sqlRow.getDate("DATE"))) {
                        rowEtatStockDeb = sqlRow;
                    }
                }

            }
            final Date dateDeb;
            final Integer idMvtStockDeb;
            if (rowEtatStockDeb != null) {
                dateDeb = rowEtatStockDeb.getDate("DATE").getTime();
                idMvtStockDeb = mapEtatStock.get(rowEtatStockDeb.getID());
            } else {
                dateDeb = null;
                idMvtStockDeb = null;
            }

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

                @Override
                public SQLSelect transformChecked(SQLSelect sel) {

                    Where w = (new Where(tableMvtStock.getField("DATE"), "<=", d));

                    if (dateDeb != null) {
                        w = w.and(new Where(tableMvtStock.getField("DATE"), ">=", dateDeb));
                        w = w.and(new Where(tableMvtStock.getKey(), ">=", idMvtStockDeb));
                        w = w.and(new Where(tableMvtStock.getField("CLOTURE"), "!=", Boolean.TRUE));
                    }
                    w = w.and(new Where(tableMvtStock.getField("REEL"), "=", Boolean.TRUE));
                    w = w.and(new Where(sel.getJoin(tableMvtStock.getField("ID_STOCK")).getJoinedTable().getField("ID_DEPOT_STOCK"), "=", depot.getID()));

                    sel.setWhere(w);
                    return sel;
                }
            });

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

            BigDecimal totalHT = BigDecimal.ZERO;
            for (int i = 0; i < list.size(); i++) {
                SQLRowValues rowVF = list.get(i);
                if (!rowVF.isForeignEmpty("ID_ARTICLE") && rowVF.getForeignID("ID_ARTICLE") > rowVF.getForeign("ID_ARTICLE").getTable().getUndefinedID()) {
                    final int foreignIDArt = rowVF.getForeignID("ID_ARTICLE");
                    if (!mapStockSnap.containsKey(foreignIDArt)) {
                        mapStockSnap.put(foreignIDArt, new EtatStock(rowVF.getForeign("ID_ARTICLE")));
                    }
                    EtatStock et = mapStockSnap.get(foreignIDArt);
                    et.setQte(et.getQte().add(new BigDecimal(rowVF.getFloat("QTE"))));
                    BigDecimal bigDecimal = BigDecimal.ZERO;
                    if (tableMvtStock.contains("PRICE")) {
                        bigDecimal = rowVF.getBigDecimal("PRICE");
                    }
                    et.setPa(bigDecimal);
                    totalHT = totalHT.add(bigDecimal.multiply(new BigDecimal(rowVF.getFloat("QTE"), DecimalUtils.HIGH_PRECISION)));
                }
            }

            // Ajout des articles sans mouvement de stock sur la période
            final SQLTable tableStock = tableMvtStock.getTable("STOCK");
            final SQLRowValues valsStock = new SQLRowValues(tableStock);
            valsStock.putRowValues("ID_ARTICLE").putNulls("CODE", "NOM", "OBSOLETE");
            valsStock.putNulls("QTE_REEL").putRowValues("ID_DEPOT_STOCK").putNulls("ID", "NOM", "CODE");
            SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(valsStock);
            SQLTable tableArt = tableStock.getForeignTable("ID_ARTICLE");
            fetcherStock.addSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
                @Override
                public SQLSelect transformChecked(SQLSelect input) {
                    Where w = new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", depot.getID());
                    w = w.and(new Where(input.getAlias(tableArt).getField("OBSOLETE"), "=", Boolean.FALSE));
                    input.setWhere(w);
                    System.err.println(input.asString());
                    return input;
                }
            }, 0);
            List<SQLRowValues> resultAllStock = fetcherStock.fetch();

            for (SQLRowValues sqlRowValues : resultAllStock) {
                final int foreignIDArt = sqlRowValues.getForeignID("ID_ARTICLE");
                if (!mapStockSnap.containsKey(foreignIDArt)) {
                    mapStockSnap.put(foreignIDArt, new EtatStock(sqlRowValues.getForeign("ID_ARTICLE")));
                }
            }

            if (this.withAllProducts) {
                // Ajout de tous les articles non obsoletes
                final SQLRowValues valsArt = new SQLRowValues(tableArt);
                valsArt.putNulls("CODE", "NOM", "OBSOLETE");
                SQLRowValuesListFetcher fetcherArt = SQLRowValuesListFetcher.create(valsArt);
                fetcherArt.addSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
                    @Override
                    public SQLSelect transformChecked(SQLSelect input) {
                        Where w = new Where(tableArt.getField("VIRTUEL"), "=", Boolean.FALSE);
                        w = w.and(new Where(tableArt.getField("OBSOLETE"), "=", Boolean.FALSE));
                        input.setWhere(w);
                        System.err.println(input.asString());
                        return input;
                    }
                }, 0);
                List<SQLRowValues> resultArt = fetcherArt.fetch();

                for (SQLRowValues sqlRowValues : resultArt) {
                    final int idArt = sqlRowValues.getID();
                    if (!mapStockSnap.containsKey(idArt)) {
                        mapStockSnap.put(idArt, new EtatStock(sqlRowValues));
                    }
                }

            }

            SQLRowValues rowVals = new SQLRowValues(tableEtatStock);
            rowVals.put("DATE", d);
            rowVals.put("MONTANT_HA", totalHT);
            rowVals.put("ID_DEPOT_STOCK", depot.getID());

            for (EtatStock etatItem : mapStockSnap.values()) {
                SQLRowValues rowValsItem = new SQLRowValues(tableEtatStock.getTable("ETAT_STOCK_ELEMENT"));
                rowValsItem.put("ID_ETAT_STOCK", rowVals);
                rowValsItem.put("PA", etatItem.getPa());
                rowValsItem.put("PV", etatItem.getPv());
                rowValsItem.put("QTE", etatItem.getQte());
                rowValsItem.put("T_PA", etatItem.getTotalPA());
                rowValsItem.put("T_PV", etatItem.getTotalPV());
                rowValsItem.put("CODE", etatItem.getArticle().getString("CODE"));
                rowValsItem.put("NOM", etatItem.getArticle().getString("NOM"));
                rowValsItem.put("ID_ARTICLE", etatItem.getArticle().getID());
            }
            try {
                rowVals.commit();
            } catch (SQLException e) {
                ExceptionHandler.handle("Erreur lors de la création de l'état", e);
            }
        }
    }

}