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

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

import java.util.Arrays;

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 int 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.putRowValues("ID_ARTICLE").putNulls("CODE", "NOM");

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

                    }

                }

            }

            SQLInsert insertEtat = new SQLInsert();

            insertEtat.add(tableEtatStock.getField("DATE"), d);

            insertEtat.add(tableEtatStock.getField("MONTANT_HA"), totalHT);

            insertEtat.add(tableEtatStock.getField("ID_DEPOT_STOCK"), depot.getID());

            try {

                int idEtat = SQLInsert.executeSimilarInserts(tableEtatStock.getDBSystemRoot(), Arrays.asList(insertEtat), true).get(0).intValue();

                List<SQLInsert> inserts = new ArrayList<SQLInsert>();

                final SQLTable tableElt = tableEtatStock.getTable("ETAT_STOCK_ELEMENT");

                for (EtatStock etatItem : mapStockSnap.values()) {

                    SQLInsert insertItem = new SQLInsert();

                    insertItem.add(tableElt.getField("ID_ETAT_STOCK"), idEtat);

                    insertItem.add(tableElt.getField("PA"), etatItem.getPa());

                    insertItem.add(tableElt.getField("PV"), etatItem.getPv());

                    insertItem.add(tableElt.getField("QTE"), etatItem.getQte());

                    insertItem.add(tableElt.getField("T_PA"), etatItem.getTotalPA());

                    insertItem.add(tableElt.getField("T_PV"), etatItem.getTotalPV());

                    insertItem.add(tableElt.getField("CODE"), etatItem.getArticle().getString("CODE"));

                    insertItem.add(tableElt.getField("NOM"), etatItem.getArticle().getString("NOM"));

                    insertItem.add(tableElt.getField("ID_ARTICLE"), etatItem.getArticle().getID());

                    inserts.add(insertItem);

                }

                if (!inserts.isEmpty()) {

                    // TODO voir avec Guillaume si il faut spliter par tranche de 1000

                    SQLInsert.executeSimilarInserts(tableElt.getDBSystemRoot(), inserts, false);

                }

                return idEtat;

            } catch (SQLException e) {

                ExceptionHandler.handle("Erreur lors de la création de l'état", e);

                return SQLRow.NONEXISTANT_ID;

            }

        }

    }

}